Oracle Applications & Oracle Cloud Fusion HCM : RECEIVABLES RECEIPTS API

-- * *****************************************************************************
-- * NAME : xarcnv04a.ctl *
-- * PURPOSE : Control File for loading Receipt Data *
-- * into Staging Tables *

-- *******************************************************************************

LOAD DATA
TRUNCATE

into table XX_AR_RECEIPT_FILE_STG
when rec_type='CRC'
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(REC_TYPE position(1:3) char,
RECORD_COUNT integer external,
DESCRIPTION char,
STATUS CONSTANT 'NW',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id",
LAST_UPDATE_LOGIN "fnd_global.login_id"
)

into table XX_AR_RECEIPT_STG
when rec_type='REC'
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(REC_TYPE position(1:3) char,
RECEIPT_NUMBER integer external,
RECEIPT_DATE date,
GL_DATE date,
CUSTOMER_ACCOUNT_ID char,
DEPOSIT_DATE date,
RECEIPT_STATUS char,
RECEIPT_AMOUNT integer external,
APPLIED_AMOUNT integer external,
INVOICE_NUMBER char,
RECEIPT_TYPE char,
RECEIPT_METHOD_NAME char,
CURRENCY char,
MISC_ACTIVITY char,
RECORD_ID "XX_AR_RECEIPT_STG_S.nextval",
STATUS CONSTANT 'NW',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id",
LAST_UPDATE_LOGIN "fnd_global.login_id"
)

/* Formatted on 2007/02/22 16:49 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PACKAGE xx_arcnv04_receipt_load_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ARCNV04_RECEIPT_LOAD_PKG
Purpose : Package Specification
Program Style :
--------------------------------------------------------------
*/

/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
--
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
--
/*-----------------------------------------------------------
Global Variable Declaration Section
-------------------------------------------------------------*/
g_debug_level NUMBER := 50;
-- 50 is default, that is all messages(logged at level 10,20..50)
-- would be visible
g_retention_period NUMBER := 30;
-- This is the number of days for which error records are going
-- to be retained in the EMF tables
g_err_col1_width NUMBER := 12;
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
g_err_col2_width NUMBER := 42;
g_err_col3_width NUMBER := 12;
g_err_col4_width NUMBER := 12;
g_err_col5_width NUMBER := 22;
g_err_col6_width NUMBER := 0;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;

/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Main calling Procedure to Import Customer
--
-------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
);
END xx_arcnv04_receipt_load_pkg;
/

/* Formatted on 2007/02/22 16:49 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PACKAGE BODY xx_arcnv04_receipt_load_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ARCNV04_RECEIPT_LOAD_PKG
Purpose : Package Body
Program Style :

/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Procedure to Update Custom Tables
--
-------------------------------------------------------------*/
PROCEDURE update_custom_table (
p_record_id IN NUMBER,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
);

/* Cursor declare section*/
--Cursor to select receipts from Staging table xx_ar_receipt_stg for control validation
CURSOR c_receipt_stg
IS
SELECT *
FROM xx_ar_receipt_stg
WHERE status = 'NW';

--Cursor to select from pre-interface table xx_ar_receipt_stg to process receipts
CURSOR c_receipt_preint
IS
SELECT *
FROM xx_ar_receipt_preint
WHERE status = 'IP';

/*--------------------------------------------------------------------------------------------------------
Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
-- l_error_api VARCHAR2 (200);
-- l_msg_count VARCHAR2 (2);
-- l_msg_data VARCHAR2 (300);
l_success VARCHAR2 (1);
-- l_completed BOOLEAN;
l_process_status NUMBER;
l_receipt_count NUMBER := 0;
l_success_pr NUMBER := 0;
-- l_set_process_count NUMBER := 0;
l_file_record_count NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_ARCNV04_RECEIPT_LOAD_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'AR_CNV_04';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
l_error_cat_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_misc_receipt_id NUMBER;
l_receipt_id NUMBER;
l_receipt_number_count NUMBER;
l_receivables_trx_id NUMBER;
l_receipt_method_id NUMBER;
l_amount NUMBER;
l_ip_rec NUMBER;
l_trx_id NUMBER;
l_receipt_invalid NUMBER;
p_message_data VARCHAR2 (1000);
p_msg_data VARCHAR2 (1000);
p_return_status VARCHAR2 (5);
p_msg_count VARCHAR2 (5);
p_message_count VARCHAR2 (5);
p_error_api VARCHAR2 (200);
-- Common Validations Variables
l_cust_trx_id NUMBER;
l_trx_number VARCHAR2 (100);
l_org_id NUMBER;
l_counter NUMBER;
l_update_success VARCHAR2 (2);
l_update_message VARCHAR2 (255);
l_proc_stg_recs NUMBER := 0;
l_err_stg_recs NUMBER := 0;
l_succ_stg_recs NUMBER := 0;
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_no_record EXCEPTION;
e_record_count_error EXCEPTION;
e_data_insert_error EXCEPTION;
e_data_update_error EXCEPTION;
e_apps_init_error EXCEPTION;

--------------------------------------------------------------
--Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
BEGIN --Main Begin
BEGIN
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name,
p_program_type => g_program_type,
p_ricew_id => l_ricewid,
p_request_id => l_request_id
);

IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;

--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Error Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Record Id'; --Third Error Header
l_error_rec.identifier4 := 'Receipt Number'; --Fourth Error Header
l_error_rec.identifier5 := 'API Name'; --Fifth Error Header
-- l_error_rec.identifier6 := NULL; --Sixth Error Header
--l_error_rec.identifier7 := NULL; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);

--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;

--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);

--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;

--
--Initialize apps
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);

IF l_process_status <> 0
THEN
--Call EMF and exit from the program
xx_trace.h ('Apps initialize problem');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00000',
p_identifier2 => 'Apps initialize problem.',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => 'xx_common_validations_pkg.init_apps_params',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_apps_init_error;
END IF;

IF p_run_mode = 'F'
THEN
--validate_at_control_level starts
-- File validation starts

BEGIN
SELECT NVL (record_count, 0)
INTO l_file_record_count
FROM xx_ar_receipt_file_stg
WHERE status = 'NW';
EXCEPTION
WHEN OTHERS
THEN
l_file_record_count := 0;
END;

l_receipt_count := 0;

SELECT COUNT (*)
INTO l_receipt_count
FROM xx_ar_receipt_stg
WHERE status = 'NW';

IF l_file_record_count <> l_receipt_count
THEN
UPDATE xx_ar_receipt_file_stg
SET status = 'ER';

--- Raise record count error and exit from the program
xx_trace.l ('Record count mismatch in the data file');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Record count mismatch.',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => 'Record Count check at control level',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);

RAISE e_record_count_error;
END IF;

-- File validation ends
-- Control validation starts
FOR r_receipt_control IN c_receipt_stg
LOOP
l_receipt_invalid := 0;

-- Receipt Type validation starts
IF r_receipt_control.receipt_type IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Type Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Receipt Type Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Type validation ends
-- Receipt Status validation starts
IF r_receipt_control.receipt_status IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Status Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Receipt Status Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Status validation ends
-- Receipt Amount validation starts
IF r_receipt_control.receipt_amount IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Amount Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Receipt Amount Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Amount validation ends
-- Receipt Number validation starts
IF r_receipt_control.receipt_number IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Number Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00005',
p_identifier2 => 'Receipt Number Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Number validation ends
-- Receipt Currency validation starts
IF r_receipt_control.currency IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Currency Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00006',
p_identifier2 => 'Receipt Currency Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Currency validation ends
-- Receipt Method validation starts
IF r_receipt_control.receipt_method_name IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Method Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00007',
p_identifier2 => 'Receipt Method Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Method validation ends
-- GL Date validation starts
IF r_receipt_control.gl_date IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('GL Date Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00008',
p_identifier2 => 'GL Date Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- GL Date validation ends
-- Receipt Date validation starts
IF r_receipt_control.receipt_date IS NULL
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Receipt Date Is Null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000024',
p_identifier2 => 'Receipt Date Is Null',
p_identifier3 => r_receipt_control.record_id,
p_identifier4 => r_receipt_control.receipt_number,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
l_receipt_invalid := 1;
END IF;

-- Receipt Date validation ends
IF l_receipt_invalid = 1
THEN
update_custom_table
(p_record_id => r_receipt_control.record_id,
p_req_id => NULL,
p_status => 'ER',
p_table_type => 'STAGING',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN
xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000009',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
END IF;
END LOOP;
--validate_at_control_level ends

BEGIN --move_data_preint_table starts
FOR r_receipt_stg IN c_receipt_stg
LOOP
INSERT INTO xx_ar_receipt_preint
(status, record_id,
request_id, receipt_number,
receipt_date,
gl_date,
customer_account_id,
deposit_date,
receipt_status,
receipt_amount,
applied_amount,
invoice_number,
receipt_type,
receipt_method_name,
currency,
misc_activity, usr_currency_code,
usr_exchange_rate_type, exchange_rate,
exchange_rate_date, factor_discount_amount,
maturity_date, postmark_date, customer_name,
customer_number, customer_bank_account_id,
customer_bank_account_num,
customer_bank_account_name, LOCATION,
customer_site_use_id,
customer_receipt_reference,
override_remit_account_flag,
remittance_bank_account_id,
remittance_bank_account_num,
remittance_bank_account_name,
receipt_method_id, doc_sequence_value,
ussgl_transaction_code,
anticipated_clearing_date, called_from,
comments, issuer_name, issue_date,
issuer_bank_branch_id, created_receipt_id,
receivables_trx_id, misc_payment_source,
tax_code, vat_tax_id, tax_rate, tax_amount,
reference_type, reference_num, reference_id,
customer_trx_id, trx_number, installment,
applied_payment_schedule_id, amount_applied,
amount_applied_from, trans_to_receipt_rate,
discount, apply_date, apply_gl_date,
app_ussgl_transaction_code,
customer_trx_line_id, line_number,
show_closed_invoices, move_deferred_tax,
link_to_trx_hist_id, global_attribute1,
global_attribute2, global_attribute3,
global_attribute4, global_attribute5,
global_attribute6, global_attribute7,
global_attribute8, global_attribute9,
global_attribute10, global_attribute11,
global_attribute12, global_attribute13,
global_attribute14, global_attribute15,
global_attribute16, global_attribute17,
global_attribute18, global_attribute19,
global_attribute20,
global_attribute_category,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', xx_ar_receipt_preint_s.NEXTVAL,
l_request_id, r_receipt_stg.receipt_number,
r_receipt_stg.receipt_date,
r_receipt_stg.gl_date,
r_receipt_stg.customer_account_id,
r_receipt_stg.deposit_date,
r_receipt_stg.receipt_status,
r_receipt_stg.receipt_amount,
r_receipt_stg.applied_amount,
r_receipt_stg.invoice_number,
r_receipt_stg.receipt_type,
r_receipt_stg.receipt_method_name,
r_receipt_stg.currency,
r_receipt_stg.misc_activity, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL,
NULL,
NULL, NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL, NULL,
NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL,
NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

END LOOP;

EXCEPTION
WHEN OTHERS
THEN
--raise data insert error..exit from the program
xx_trace.h ('Data Insert Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000010',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_insert_error;
END; --move_data_preint_table ends

SELECT COUNT (*)
INTO l_proc_stg_recs
FROM xx_ar_receipt_stg;

SELECT COUNT (*)
INTO l_succ_stg_recs
FROM xx_ar_receipt_stg
WHERE status = 'NW';

l_err_stg_recs := l_proc_stg_recs - l_succ_stg_recs;
/*----------------------------------------------------------------------------
--Inserting Summary Counts for Staging Table Records
----------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id,
p_display_name => l_program_name
|| ': Staging',
p_total_recs => l_proc_stg_recs,
p_successful_recs => l_succ_stg_recs,
p_error_recs => l_err_stg_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
END IF; --p_run_mode='F' ends

l_receipt_count := 0;
l_org_id := fnd_profile.VALUE ('ORG_ID');
fnd_client_info.set_org_context (TO_CHAR (l_org_id));

IF (l_succ_stg_recs > 0 OR p_run_mode='P')
THEN
--validate_business_level_receipts starts
FOR r_receipt_preint IN c_receipt_preint
LOOP
l_error_flag := 0;
l_receipt_count := l_receipt_count + 1;

--- Write all business validations here
IF r_receipt_preint.receipt_type NOT IN ('CASH', 'MISC')
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Type is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000014',
p_identifier2 => 'Receipt Type is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_receipt_preint.receipt_status NOT IN
('APP', 'UAPP', 'UNID')
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Status is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000015',
p_identifier2 => 'Receipt Status is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

BEGIN
SELECT receipt_method_id
INTO l_receipt_method_id
FROM ar_receipt_methods
WHERE NVL (end_date, '31-DEC-4712') > SYSDATE
AND NVL (start_date, SYSDATE) <= SYSDATE
AND NAME = r_receipt_preint.receipt_method_name;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Method is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000016',
p_identifier2 => 'Receipt Method is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;

IF r_receipt_preint.receipt_type = 'MISC'
THEN
BEGIN
SELECT receivables_trx_id
INTO l_receivables_trx_id
FROM ar_receivables_trx
WHERE status = 'A'
AND NVL (end_date_active, '31-DEC-4712') > SYSDATE
AND NVL (start_date_active, SYSDATE) <= SYSDATE
AND NAME = r_receipt_preint.misc_activity;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Activity is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000017',
p_identifier2 => 'Receipt Activity is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;

IF r_receipt_preint.currency <> 'USD'
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Currency is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000018',
p_identifier2 => 'Receipt Currency is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF NVL(r_receipt_preint.receipt_amount,0) < NVL(r_receipt_preint.applied_amount,0)
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Applied amount is greater than receipt amount');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000025',
p_identifier2 => 'Applied amount is greater than receipt amount',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_receipt_preint.customer_account_id IS NOT NULL
THEN
BEGIN
SELECT cust_account_id
INTO l_trx_id
FROM hz_cust_accounts
WHERE account_number =
r_receipt_preint.customer_account_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Account Number is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000027',
p_identifier2 => 'Account Number is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;

SELECT COUNT (receipt_number)
INTO l_receipt_number_count
FROM ar_cash_receipts
WHERE receipt_number = r_receipt_preint.receipt_number;

IF l_receipt_number_count <> 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Receipt Number is duplicate');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000019',
p_identifier2 => 'Receipt Number is duplicate',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_receipt_preint.receipt_status IN ('APP', 'UAPP')
THEN
BEGIN
SELECT trx_number, customer_trx_id
INTO l_trx_number, l_cust_trx_id
FROM ra_customer_trx
WHERE trx_number = r_receipt_preint.invoice_number;
EXCEPTION
WHEN OTHERS
THEN
l_trx_number := 0;
l_cust_trx_id := 0;
END;

IF l_trx_number = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Invoice Number is invalid');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000020',
p_identifier2 => 'Invoice Number is invalid',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

IF ( r_receipt_preint.receipt_status IN ('APP', 'UAPP')
AND l_cust_trx_id <> 0
)
THEN
BEGIN
SELECT amount_due_original
INTO l_amount
FROM ar_payment_schedules_all
WHERE customer_trx_id = l_cust_trx_id;
EXCEPTION
WHEN OTHERS
THEN
l_amount := 0;
END;

IF r_receipt_preint.receipt_amount > l_amount
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l
('Receipt Amount is greater than Invoice Amount');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000021',
p_identifier2 => 'Receipt Amount is greater than Invoice Amount',
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => 'Business Validation Failure',
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

IF l_error_flag = 1
THEN
update_custom_table
(p_record_id => r_receipt_preint.record_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN
xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000011',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

END IF;

END LOOP;
--validate_business_level_receipts ends

l_success_pr := 0;

BEGIN --process_receipts_starts
l_counter := 0;

SELECT COUNT (record_id)
INTO l_ip_rec
FROM xx_ar_receipt_preint
WHERE status = 'IP';

IF l_ip_rec = 0
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000023',
p_identifier2 => 'No record found to import into base tables',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_no_record;
END IF;

FOR r_receipt_preint IN c_receipt_preint
LOOP
l_counter := l_counter + 1;
l_success := 'Y';

--Process receipt
IF r_receipt_preint.receipt_type = 'CASH'
THEN
l_receipt_id := 0;

IF r_receipt_preint.receipt_status <> 'APP'
THEN
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_currency_code => r_receipt_preint.currency,
p_receipt_number => r_receipt_preint.receipt_number,
p_receipt_date => r_receipt_preint.receipt_date,
p_gl_date => r_receipt_preint.gl_date,
p_amount => r_receipt_preint.receipt_amount,
p_customer_id => r_receipt_preint.customer_account_id,
p_receipt_method_name => r_receipt_preint.receipt_method_name,
p_cr_id => l_receipt_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'ar_receipt_api_pub.create_cash';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get
(p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'ar_receipt_api_pub.create_cash';
END IF;

l_success := 'N';
END IF;
ELSE
BEGIN
l_trx_id := 0;

SELECT rct.customer_trx_id
INTO l_trx_id
FROM hz_cust_accounts hca,
ar_payment_schedules aps,
ra_customer_trx rct,ar_open_trx_v ao
WHERE hca.cust_account_id = aps.customer_id
AND aps.customer_trx_id = rct.customer_trx_id
AND ao.trx_number=rct.trx_number
AND ao.status='OP'
AND rct.trx_number=r_receipt_preint.invoice_number
AND hca.cust_account_id = r_receipt_preint.customer_account_id ;

ar_receipt_api_pub.create_and_apply
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_currency_code => r_receipt_preint.currency,
p_receipt_date => r_receipt_preint.receipt_date,
p_receipt_number => r_receipt_preint.receipt_number,
p_gl_date => r_receipt_preint.gl_date,
p_apply_date => r_receipt_preint.receipt_date,
p_amount => r_receipt_preint.receipt_amount,
p_customer_trx_id => l_trx_id,
-- p_trx_number => r_receipt_preint.invoice_number,
p_amount_applied => r_receipt_preint.applied_amount,
p_receipt_method_name => r_receipt_preint.receipt_method_name,
p_cr_id => l_receipt_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'ar_receipt_api_pub.create_and_apply';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get
(p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'ar_receipt_api_pub.create_and_apply';
END IF;

l_success := 'N';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_success := 'N';
p_message_data := 'Trx Number Not Found';
p_error_api := 'Trx Number derrivation';
END;
END IF;
ELSE
ar_receipt_api_pub.create_misc
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data,
p_currency_code => r_receipt_preint.currency,
p_amount => r_receipt_preint.receipt_amount,
p_receipt_number => r_receipt_preint.receipt_number,
p_receipt_date => r_receipt_preint.receipt_date,
p_gl_date => r_receipt_preint.gl_date,
p_activity => r_receipt_preint.misc_activity,
p_deposit_date => r_receipt_preint.deposit_date,
p_receipt_method_name => r_receipt_preint.receipt_method_name,
p_doc_sequence_value => NULL,
p_misc_receipt_id => l_misc_receipt_id
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'ar_receipt_api_pub.create_misc';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get
(p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'ar_receipt_api_pub.create_misc';
END IF;

l_success := 'N';
END IF;
END IF;

IF l_success = 'N'
THEN
--Call EMF
xx_trace.m ('Oracle API Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 30
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000013',
p_identifier2 => p_message_data,
p_identifier3 => r_receipt_preint.record_id,
p_identifier4 => r_receipt_preint.receipt_number,
p_identifier5 => p_error_api,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(p_record_id => r_receipt_preint.record_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN
xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000011',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
-- Update interface tables
ELSE
-- Update interface tables
update_custom_table
(p_record_id => r_receipt_preint.record_id,
p_req_id => l_request_id,
p_status => 'PR',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN
xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000012',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_success_pr := l_success_pr + 1;
END IF;

END LOOP;

END; --process_receipts_ends

/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_ar_receipt_preint
WHERE status = 'PR';

EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Deleting data from preint',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
END;

/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_receipt_count - l_success_pr;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_receipt_count,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
END IF; --if success records in staging table is > 0

--
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;

--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;

--
l_return_value := NULL;

--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;

END IF;
EXCEPTION
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
WHEN e_record_count_error
THEN
l_error_recs := l_receipt_count;
RAISE;
WHEN e_apps_init_error
THEN
RAISE;
WHEN e_data_update_error
THEN
RAISE;
WHEN e_data_insert_error
THEN
RAISE;
WHEN e_no_record
THEN
l_error_recs := l_receipt_count;
RAISE;
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
--High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000041',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
EXCEPTION --Main Exception
--Main Exception Block
WHEN OTHERS
THEN

--Write to error
l_error_flag := 'Y';
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_receipt_count,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;

/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20
--Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;

l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;

l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;

PROCEDURE update_custom_table (
p_record_id IN NUMBER,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
)
AS
BEGIN
p_success := 'S';
p_message_data := NULL;

IF p_table_type = 'STAGING'
THEN
UPDATE xx_ar_receipt_stg
SET status = p_status
WHERE record_id = p_record_id;
ELSIF p_table_type = 'PREINT'
THEN
UPDATE xx_ar_receipt_preint
SET status = p_status,
request_id = p_req_id
WHERE record_id = p_record_id;
ELSE
p_success := 'F';
p_message_data := 'Invalid Input Supplied';
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_success := 'F';
p_message_data := SUBSTR (SQLERRM, 1, 255);
END update_custom_table;
END xx_arcnv04_receipt_load_pkg;
/

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday 8 January 2017

RECEIVABLES RECEIPTS API

No comments :

Contact us