Oracle Applications & Oracle Cloud Fusion HCM : RECEIVABLES RECEIPTS INTERFACE

LOAD DATA
REPLACE
INTO TABLE XX_AR_REC_STAGE
WHEN RECORD_TYPE <>'0'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( STAGING_ID "XX_AR_REC_STAGE_S.nextval",
STATUS CONSTANT 'NW',
SOURCE_SYSTEM CONSTANT 'RECEIPT CENTRAL',
RECORD_TYPE POSITION(1) INTEGER EXTERNAL,
ITEM_NUMBER CHAR,
PAYMENT_NUMBER CHAR,
LOCKBOX_NUMBER CHAR,
INVOICE1 CHAR,
REMITTANCE_AMOUNT CHAR,
CURRENCY_CODE CHAR,
ORIGINATION CHAR,
DEPOSIT_DATE CHAR,
CREATED_BY "fnd_global.user_id",
CREATION_DATE "SYSDATE",
LAST_UPDATED_LOGIN "fnd_global.login_id",
LAST_UPDATED_BY "fnd_global.user_id",
LAST_UPDATED_DATE "SYSDATE"
)
INTO TABLE XX_AR_REC_STAGE
WHEN RECORD_TYPE ='0'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( STAGING_ID "XX_AR_REC_STAGE_S.nextval",
STATUS CONSTANT 'NW',
SOURCE_SYSTEM CONSTANT 'CONTROL COUNT',
RECORD_TYPE POSITION(1),
ITEM_NUMBER ,
INVOICE1 ,
CREATED_BY "fnd_global.user_id",
CREATION_DATE "SYSDATE",
LAST_UPDATED_LOGIN "fnd_global.login_id",
LAST_UPDATED_BY "fnd_global.user_id",
LAST_UPDATED_DATE "SYSDATE"
)

CREATE OR REPLACE PACKAGE xx_arcnv03_lockbox_pkg
AS
/*---------------------------------------------------------------------------------------------------
Public Constant Declaration Section
----------------------------------------------------------------------------------------------------*/
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
/*---------------------------------------------------------------------------------------------------
Public 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 := 10;
-- 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 := 30;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 10;
g_err_col5_width NUMBER := 10;
g_err_col6_width NUMBER := 30;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;

/*----------------------------------------------------------------------------------------------------
Public Procedure/Function Declaration Section
------------------------------------------------------------------------------------------------------
in_ricewid --Object Name
in_parentid --header id of the calling driver shell program
in_requestid --concurrent program request ID
in_filetag --File Name with no time stamp
in_run_option --Valid values are F(Perform External Table data validations and Pre-Interface table load )or E
p_source --interface specific parameters
p_complete_run --interface specific parameters
----------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
);
END xx_arcnv03_lockbox_pkg;
/

SHOW errors

CREATE OR REPLACE PACKAGE BODY xx_arcnv03_lockbox_pkg
AS
/*------------------------------------------------------------------------------------------------------
Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
--Cursor to select from Staging table xx_ar_rec_stage
CURSOR c_ar_rec_control
IS
SELECT *
FROM xx_ar_rec_stage
WHERE record_type <> '0';

--Cursor to select from Pre-Interface table xx_ar_pre_payments_interface
CURSOR c_ar_rec_validate
IS
SELECT *
FROM xx_ar_pre_payments_interface
WHERE status = 'IP';

/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/

--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_inv_items_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_AR_LOCKBOX_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_03';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_user_id NUMBER;
l_transmission_id NUMBER;
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';
--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_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_receipt_number_count NUMBER;
-- Extra Variables
l_control_stage_count NUMBER;
l_control_trans_count NUMBER;
l_stg_proc_recs NUMBER;
l_stg_err_recs NUMBER;
l_stg_succ_recs NUMBER;
x_lockbox_id NUMBER;
l_trx_number VARCHAR2 (100);
l_amount NUMBER;
l_transmission_amount NUMBER;
l_cust_trx_id NUMBER;
x_standard_request_id NUMBER;
x_transmission_id NUMBER;
x_request_id xx_emf_message_headers.request_id%TYPE;
x_trans_name VARCHAR2 (100);
x_trans_format_id NUMBER;
x_submit_validation VARCHAR2 (10);
x_deposit_date DATE;
x_post_cash VARCHAR2 (10);
x_org_id NUMBER;
x_transm_count NUMBER;
x_file_lockbox_no VARCHAR2 (20);
x_total_payment NUMBER;
l_rec_err_count NUMBER := 0;
l_master_err_count NUMBER := 0;
-- Common Validations Variables
-- 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_cntrl_fail EXCEPTION;
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
l_organization_id := fnd_profile.VALUE ('ORG_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 := 'Lockbox Number'; --Fifth Error Header
l_error_rec.identifier6 := 'Invoice Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Remittance Amount'; --Seventh Error Header
--l_error_rec.identifier8 := ''; --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;

--

/*---------------------------------------------------------------------------------------------------
Perform Control Transmission Count Validation
---------------------------------------------------------------------------------------------------*/
IF (p_run_mode = 'F')
THEN
SELECT COUNT (*)
INTO l_control_stage_count
FROM xx_ar_rec_stage
WHERE record_type <> '0';

BEGIN
SELECT TO_NUMBER (item_number)
INTO l_control_trans_count
FROM xx_ar_rec_stage
WHERE record_type = '0';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_control_trans_count := 0;
END;

IF (l_control_stage_count <> l_control_trans_count)
THEN
l_error_flag := 'Y';
xx_trace.l ('Control count and Tranmission count do not match');
ELSE
l_error_flag := 'N';
END IF;

/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
BEGIN -- 1st Begin
FOR c_ar_rec IN c_ar_rec_control
LOOP
l_error_flag := 'Y';
l_stg_proc_recs := l_stg_proc_recs + 1;

IF c_ar_rec.payment_number IS NULL
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
l_stg_err_recs := l_stg_err_recs + 1;
xx_trace.l ('Receipt Number value cannot be 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-00001',
p_identifier2 => 'Receipt Number value cannot be null.',
p_identifier3 => c_ar_rec.staging_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.amount_applied1
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
l_error_flag := 'N';
END IF;

IF c_ar_rec.lockbox_number IS NULL
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
l_stg_err_recs := l_stg_err_recs + 1;
xx_trace.l ('Lockbox Number value cannot be 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 => 'Lockbox Number value cannot be null',
p_identifier3 => c_ar_rec.staging_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.amount_applied1
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
l_error_flag := 'N';
END IF;

IF c_ar_rec.invoice1 IS NULL
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
l_stg_err_recs := l_stg_err_recs + 1;
xx_trace.l ('Invoice Number value cannot be 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 => 'Invoice Number value cannot be null',
p_identifier3 => c_ar_rec.staging_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.amount_applied1
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
l_error_flag := 'N';
END IF;

IF c_ar_rec.remittance_amount IS NULL
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
l_stg_err_recs := l_stg_err_recs + 1;
xx_trace.l
('Remittance Amount for the receipt cannot be 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 => 'Remittance Amount for the receipt cannot be null',
p_identifier3 => c_ar_rec.staging_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.amount_applied1
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
l_error_flag := 'N';
END IF;

IF (l_error_flag = 'Y')
THEN
UPDATE xx_ar_rec_stage
SET status = 'ER'
WHERE status = 'NW'
AND staging_id = c_ar_rec.staging_id;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
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-00100',
p_identifier2 => 'Error in Control Validation.'
|| SQLERRM,
-- p_identifier3 => c_ar_rec.staging_id,
-- , p_identifier4 => c_ar_rec.payment_number
-- , p_identifier5 => c_ar_rec.lockbox_number
-- , p_identifier6 => c_ar_rec.invoice1
-- , p_identifier7 => c_ar_rec.amount_applied1
-- p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --1st Begin
END IF;

l_master_err_count := l_master_err_count + l_rec_err_count;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count for Staging
------------------------------------------------------------------------------------*/
l_stg_succ_recs := l_stg_proc_recs - l_stg_err_recs;
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_stg_proc_recs,
p_successful_recs => l_stg_succ_recs,
p_error_recs => l_stg_err_recs
);

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

/* -- Update records in staging table to set status as new(NW) - Not required
UPDATE XX_AR_REC_STAGE
SET status = 'NW'
where status = NULL;
*/

/*---------------------------------------------------------------------------------------------------
Load Data into Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
SELECT ar_transmissions_s.NEXTVAL
INTO l_transmission_id
FROM DUAL;

FOR rec1 IN c_ar_rec_control
LOOP
INSERT INTO xx_ar_pre_payments_interface
(preinterface_id, transmission_record_id, creation_date, created_by,
last_update_login, last_updated_by,
last_update_date, record_type, status,
transmission_request_id, destination_account,
origination, deposit_date, deposit_time,
transmission_record_count, transmission_amount,
lockbox_number, lockbox_batch_count,
lockbox_record_count,
lockbox_amount, batch_name,
batch_amount,
batch_record_count,
item_number, payment_number,
currency_code, exchange_rate,
exchange_rate_type,
remittance_amount,
check_number, customer_number,
overflow_indicator,
overflow_sequence,
bill_to_location,
receipt_date,
receipt_method,
invoice1, invoice2, invoice3,
invoice4, invoice5, invoice6, invoice7,
invoice8, customer_bank_name,
customer_bank_branch_name,
amount_applied1,
amount_applied2,
amount_applied3,
amount_applied4, amount_applied5, amount_applied6,
amount_applied7, amount_applied8,
invoice_currency_code1, invoice_currency_code2,
invoice_currency_code3, invoice_currency_code4,
invoice_currency_code5, invoice_currency_code6,
invoice_currency_code7, invoice_currency_code8,
transmission_id, org_id
)
VALUES (XX_AR_PRE_PAYMENTS_INTERFACE_S.nextval,
NULL --transmission_record_id
, SYSDATE, l_user_id,
NULL, l_user_id,
SYSDATE, TO_CHAR (rec1.record_type), rec1.status,
l_request_id, rec1.destination_account,
NULL, TO_DATE (rec1.deposit_date), NULL,
NULL, l_transmission_amount --transmission_amount
,
rec1.lockbox_number, NULL --lockbox_batch_count
,
TO_NUMBER (rec1.lockbox_record_count),
TO_NUMBER (rec1.lockbox_amount), rec1.batch_name,
TO_NUMBER (rec1.batch_amount),
TO_NUMBER (rec1.batch_record_count),
TO_NUMBER (rec1.item_number), rec1.payment_number,
rec1.currency_code, NULL -- exchange_rate derived
,
NULL -- exchange_type derived
,
TO_NUMBER (rec1.remittance_amount),
rec1.check_number, NULL --customer lockbox_process
,
NULL -- rec1.overflow_indicator
,
NULL -- TO_NUMBER (rec1.overflow_sequence)
,
NULL --bill to location lockbox process
,
TO_DATE (rec1.deposit_date),
NULL --derived from batch receipt method
,
rec1.invoice1, rec1.invoice2, rec1.invoice3,
rec1.invoice4, NULL, NULL, NULL,
NULL --other invoices
, NULL,
NULL --customer bank lockbox process
,
TO_NUMBER (rec1.amount_applied1),
TO_NUMBER (rec1.amount_applied2),
TO_NUMBER (rec1.amount_applied3),
TO_NUMBER (rec1.amount_applied4), NULL, NULL,
NULL, NULL,
rec1.currency_code, rec1.currency_code,
rec1.currency_code, rec1.currency_code,
NULL, NULL,
NULL, NULL,
l_transmission_id, l_organization_id
);
END LOOP;
-- COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting Into Pre-Interface table. Oracle Error:'
|| SQLERRM
);
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-00101',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --2nd Begin

-- Set Transmission Amount to be the sum of Receipt Amounts
SELECT SUM (remittance_amount)
INTO l_transmission_amount
FROM xx_ar_pre_payments_interface
WHERE status = 'NW';

UPDATE xx_ar_pre_payments_interface
SET transmission_amount = l_transmission_amount
WHERE status = 'NW';

/*-----------------------------------------------------------------------------
Updating pre interface table with status 'IP' where record_status is New
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_ar_pre_payments_interface
SET status = 'IP'
WHERE status = 'NW';

xx_trace.l ('Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM
);
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-00102',
p_identifier2 => 'Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --3rdBegin

/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN --4 th Begin
l_rec_err_count := 0;

FOR c_ar_rec IN c_ar_rec_validate
LOOP
--
l_processed_recs := l_processed_recs + 1;
--Counter for total records
x_transm_count := x_transm_count + 1;
x_file_lockbox_no := c_ar_rec.lockbox_number;
x_total_payment :=
x_total_payment + TO_NUMBER (c_ar_rec.remittance_amount);

IF (c_ar_rec.payment_number IS NOT NULL)
THEN
--Validating Receipt Number
BEGIN --4.1 th Begin
--
SELECT NVL (COUNT (receipt_number), 0)
INTO l_receipt_number_count
FROM ar_cash_receipts_all
WHERE org_id = l_organization_id
AND receipt_number = c_ar_rec.payment_number;

IF l_receipt_number_count <> 0
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h
( ' Error- Receipt Number already exists within the organization'
|| SQLERRM
);
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 => 'Error- Receipt Number already exists within the organization'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
END; --4.1 th Begin
ELSE
l_error_flag := 'N';
END IF;

--
--Validating Lockbox Number
--
IF (c_ar_rec.lockbox_number IS NOT NULL)
THEN
--Validate Lockbox Number exists
BEGIN --4.2 th Begin
--
SELECT lockbox_id
INTO x_lockbox_id
FROM ar_lockboxes_all
WHERE lockbox_number = c_ar_rec.lockbox_number
AND org_id = l_organization_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h ( 'Error- Lockbox Number does not exist'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00006',
p_identifier2 => 'Error- Lockbox Number does not exist'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.2a th Begin
ELSE
l_error_flag := 'N';
END IF;

--
-- Validation Lockbox for the organization
--
IF (c_ar_rec.lockbox_number IS NOT NULL)
THEN
--Validate Lockbox Number exists
BEGIN --4.2b th Begin
--
SELECT lockbox_id
INTO x_lockbox_id
FROM ar_lockboxes_all
WHERE lockbox_number = c_ar_rec.lockbox_number
AND org_id = l_organization_id
AND status = 'A';
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h
( 'Error- Lockbox Number either does not exist within the organization or is Inactive'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00007',
p_identifier2 => 'Error- Lockbox Number either does not exist within the organization or is Inactive'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.2b th Begin
ELSE
l_error_flag := 'N';
END IF;

--
--Validating Invoice Number
--
IF (c_ar_rec.invoice1 IS NOT NULL)
THEN
BEGIN --4.3 th Begin
--
SELECT trx_number, customer_trx_id
INTO l_trx_number, l_cust_trx_id
FROM ra_customer_trx_all
WHERE trx_number = c_ar_rec.invoice1
AND org_id = l_organization_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h ( 'Error- Invoice Number does not exist'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00008',
p_identifier2 => 'Error- Invoice Number does not exist'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.3 th Begin
ELSE
l_error_flag := 'N';
END IF;

--
--Validating Remittance Amount less than Invoice Amount
--
IF (c_ar_rec.remittance_amount IS NOT NULL)
THEN
BEGIN --4.4 th Begin
--
SELECT amount_due_original
INTO l_amount
FROM ar_payment_schedules_all
WHERE customer_trx_id = l_cust_trx_id
AND org_id = l_organization_id;

IF c_ar_rec.remittance_amount > l_amount
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h
('Receipt Amount is greater than Invoice Amount');
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-00009',
p_identifier2 => 'Receipt Amount is greater than Invoice Amount',
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h
( 'Exception Occured While Validating Invoice Amount .Sqlerrm-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00103',
p_identifier2 => 'Exception Occured While Validating Invoice Amount .Sqlerrm-'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END; --4.4 th Begin
ELSE
l_error_flag := 'N';
END IF;

-- Validation Deposit Date
IF (TO_DATE (c_ar_rec.deposit_date) > SYSDATE)
THEN
l_error_flag := 'Y';
l_rec_err_count := l_rec_err_count + 1;
xx_trace.h ('Deposit Date is greater than Todays Date');
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-000010',
p_identifier2 => 'Deposit Date is greater than Todays Date',
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
l_error_flag := 'N';
END IF;

/*------------------------------------------------------------------------------
Updating pre interface table with status 'IP' where record_status is 'NW'
-------------------------------------------------------------------------------*/
IF (l_rec_err_count > 0)
THEN
BEGIN --4.5 th Begin
UPDATE xx_ar_pre_payments_interface
SET status = 'ER'
WHERE item_number = TO_NUMBER (c_ar_rec.item_number)
AND lockbox_number = c_ar_rec.lockbox_number
AND invoice1 = c_ar_rec.invoice1;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
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-00104',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_ar_rec.preinterface_id,
p_identifier4 => c_ar_rec.payment_number,
p_identifier5 => c_ar_rec.lockbox_number,
p_identifier6 => c_ar_rec.invoice1,
p_identifier7 => c_ar_rec.remittance_amount
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.5 th Begin
ELSE
l_successful_recs := l_successful_recs + 1;

UPDATE xx_ar_pre_payments_interface
SET status = 'IP'
WHERE item_number = TO_NUMBER (c_ar_rec.item_number)
AND lockbox_number = c_ar_rec.lockbox_number
AND invoice1 = c_ar_rec.invoice1;
END IF;

l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
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-00105',
p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.0 th Begin

--End of Business Validation Section
l_master_err_count := l_master_err_count + l_rec_err_count;

/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN --5th Begin
-- FOR c_ar_rec IN c_ar_rec_validate
-- LOOP
INSERT INTO ar_payments_interface_all
(transmission_record_id, creation_date, created_by,
last_update_login, last_updated_by, last_update_date,
record_type, status, transmission_request_id,
destination_account, origination, deposit_date,
transmission_record_count, transmission_amount,
lockbox_number, lockbox_batch_count,
lockbox_record_count, lockbox_amount, batch_name,
batch_amount, batch_record_count, item_number,
currency_code, exchange_rate, exchange_rate_type,
remittance_amount, check_number, customer_number,
overflow_indicator, overflow_sequence,
bill_to_location, receipt_date, receipt_method,
invoice1, invoice2, invoice3, invoice4, invoice5,
invoice6, invoice7, invoice8, customer_bank_name,
customer_bank_branch_name, amount_applied1,
amount_applied2, amount_applied3, amount_applied4,
amount_applied5, amount_applied6, amount_applied7,
amount_applied8, transmission_id, org_id)
SELECT ar_payments_interface_s.NEXTVAL, creation_date,
created_by, last_update_login, last_updated_by,
last_update_date, record_type, 'AR_PLB_NEW_RECORD',
transmission_request_id, destination_account, origination,
deposit_date, transmission_record_count,
transmission_amount, lockbox_number, lockbox_batch_count,
lockbox_record_count, lockbox_amount, batch_name,
batch_amount, batch_record_count, item_number,
currency_code, exchange_rate, exchange_rate_type,
remittance_amount, payment_number --SH
,
customer_number, overflow_indicator, overflow_sequence,
bill_to_location, receipt_date, receipt_method, invoice1,
invoice2, invoice3, invoice4, invoice5, invoice6,
invoice7, invoice8, customer_bank_name,
customer_bank_branch_name, amount_applied1,
amount_applied2, amount_applied3, amount_applied4,
amount_applied5, amount_applied6, amount_applied7,
amount_applied8, transmission_id, org_id
FROM xx_ar_pre_payments_interface
WHERE status = 'IP';
-- END LOOP;

-- COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into AR_PAYMENTS_INTERFACE_ALL.Oracle Error-'
|| SQLERRM
);
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-00105',
p_identifier2 => 'Exception While Inserting into AR_PAYMENTS_INTERFACE_ALL.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --5 th Begin

/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN --6 th Begin
UPDATE xx_ar_pre_payments_interface
SET status = 'PR'
WHERE status = 'IP';

xx_trace.l ('Pre-Interface Table status updated to PR');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
);
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-00106',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --6th Begin

/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN --7th Begin
SELECT COUNT (*)
INTO l_count
FROM xx_ar_pre_payments_interface
WHERE status = 'PR';

-- Initialize Program parameters

--Inserting Into AR_TRANSMISSIONS_ALL
x_trans_name := x_file_lockbox_no || l_request_id;

BEGIN
INSERT INTO ar_transmissions_all
(transmission_request_id, created_by, creation_date,
last_updated_by, last_update_date, trans_date,
COUNT, amount, origin, destination,
transmission_name, transmission_id, org_id
)
VALUES (l_request_id, l_user_id, SYSDATE,
l_user_id, SYSDATE, x_deposit_date,
x_transm_count, x_total_payment, NULL, NULL,
x_trans_name, l_transmission_id, l_organization_id
);
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Inserting into AR Table.Oracle Error-'
|| SQLERRM
);
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-00107',
p_identifier2 => 'Exception While Inserting into AR Table.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;

x_transmission_id := l_transmission_id;
x_request_id := l_request_id;
x_submit_validation := 'Y';
x_deposit_date := SYSDATE;
x_post_cash := 'Y';
x_org_id := l_organization_id;

---To get transmission format id
SELECT transmission_format_id
INTO x_trans_format_id
FROM ar_transmission_formats
WHERE format_name = 'MYDEFAULT';
-- To be changed specific to Business setup

IF (l_count > 0) AND (l_master_err_count = 0)
THEN
x_standard_request_id :=
fnd_request.submit_request
(application => 'AR',
program => 'ARLPLB',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => 'N' --New Transmission
,
argument2 => x_transmission_id
--LB Transmission Id
,
argument3 => x_request_id --Request Id
,
argument4 => x_trans_name --Transmission Name
,
argument5 => 'N' --Submit Import
,
argument6 => NULL --Data File
,
argument7 => NULL --Cntrl File
,
argument8 => x_trans_format_id
--Transmission Format Id
,
argument9 => x_submit_validation
--Submit validation
,
argument10 => 'N' --Pay Unrelated Invoices
,
argument11 => x_lockbox_id --Lockbox id
,
argument12 => x_deposit_date --Gl Date
,
argument13 => 'R' --Report Format
,
argument14 => 'N' --Complete Batches only
,
argument15 => x_post_cash --Submit Post Batch
,
argument16 => 'N' --Alternate Name search
,
argument17 => 'N' --Ignore Invalid Trx Numbers
,
argument18 => NULL --Ussgl Transaction code
,
argument19 => x_org_id --Org Id
);
COMMIT;

IF x_standard_request_id = 0
THEN
--Write to error
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Calling Autolockbox Master.Oracle Error-'
|| SQLERRM
);
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-00107',
p_identifier2 => 'Exception While Calling Autolockbox Master.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

--Wait for the completion of the concurrent request (if submitted successfully)
l_completed :=
fnd_concurrent.wait_for_request
(request_id => x_standard_request_id,
INTERVAL => 120,
max_wait => 0,
phase => l_phase,
status => l_vstatus,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
MESSAGE => l_message
);
xx_trace.l ( 'Request submitted with request id-'
|| x_standard_request_id
);
ELSE
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-00011',
p_identifier2 => 'No records To Process in Interface Table',
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END; --7 th Begin

-- Delete Pre-Interface table of all records that are processed
BEGIN
DELETE FROM xx_ar_pre_payments_interface
WHERE status = 'PR';
EXCEPTION
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_trace.h
( 'Exception Deleting Pre-Interface Table.Oracle Error-'
|| SQLERRM
);
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-00108',
p_identifier2 => 'Exception Deleting Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;

/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_messages_rec := l_null_rec;
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_processed_recs,
p_successful_recs => l_successful_recs,
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;

--
/*-----------------------------------------------------------------------------------------
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;

COMMIT;
END IF;
EXCEPTION --Main 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');
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
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-00004',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count for Overall Records
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id,
p_display_name => l_program_name
|| ' - Overall',
p_total_recs => l_processed_recs,
p_successful_recs => l_successful_recs,
p_error_recs => l_error_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts for Overall');
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;
END xx_arcnv03_lockbox_pkg;
/

SHO err

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday 8 January 2017

RECEIVABLES RECEIPTS INTERFACE

No comments :

Contact us