CREATE OR REPLACE PACKAGE xx_ar_open_inv_cnv_pkb AUTHID CURRENT_USER AS
--Version Draft 1a
/******************************************************************************************************************
* Package Name : xx_ar_open_inv_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the Invoice data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create invoive lines and line distributions. *
* *
* *
* Procedures : *
* --------------------- *
* main : Main procedure registered as the executable file name, which *
* calls the other procedures *
* *
* validate_setups : Validate Certain prerequisite setups in Oracle Apps *
* *
* validate_records : Validate and update records in staging table *
* *
* process_records : Populate the standard interface tables *
* *
* Tables Accessed : *
* Access Type-------- (I - Insert, S - Select, U - Update, D - Delete) *
* *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* * *
********************************************************************************************************************/
/*-----------------------------------------------------------
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 := 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 := 20;
g_err_col7_width NUMBER := 10;
g_err_col8_width NUMBER := 0;
TYPE process_set_id_rec IS RECORD (process_set_id NUMBER);
TYPE process_set_id_tbl IS TABLE OF process_set_id_rec INDEX BY BINARY_INTEGER;
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
PROCEDURE main( x_errbuf OUT VARCHAR2
, x_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
);
g_rec_ins_exception EXCEPTION;
END xx_ar_open_inv_cnv_pkb;
/
SHOW ERROR;
CREATE OR REPLACE PACKAGE BODY xx_ar_open_inv_cnv_pkb AS
--Version Draft 1a
/*****************************************************************************************************************
* Package Name : xx_ar_open_inv_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the invoice data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create invoive lines and line distributions. *
* *
* Procedures : *
* --------------------- *
* main : Main procedure registered as the executable file name, which *
* calls the other procedures *
* *
* validate_setups : Validate Certain prerequisite setups in Oracle Apps *
* *
* validate_records : Validate and update records in staging table *
* *
* process_records : Populate the standard interface tables *
* *
* Tables Accessed : *
* Access Type----------------- (I - Insert, S - Select, U - Update, D - Delete) *
* *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
**********************************************************************************************************************/
l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
l_batch_source ra_batch_sources.NAME%TYPE:='LEGACY' ;
l_trx_numbering_flag ra_batch_sources.auto_trx_numbering_flag%TYPE ;
l_batch_source_id ra_batch_sources.batch_source_id%TYPE ;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N' ;
l_header_id xx_emf_message_headers.header_id%TYPE ;
--EMF ID
l_return_value NUMBER := NULL ;
l_output_message VARCHAR2 (1000) ;
l_error_msg VARCHAR2(2000) ;
l_request_id xx_emf_message_headers.request_id%TYPE:= fnd_profile.VALUE ('CONC_REQUEST_ID');
l_process_status NUMBER ;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0 ;
l_completed BOOLEAN ;
l_phase VARCHAR2 (200) ;
l_vstatus VARCHAR2 (200) ;
l_dev_phase VARCHAR2 (200) ;
l_dev_status VARCHAR2 (200) ;
l_message VARCHAR2 (2000) ;
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 ;
PROCEDURE validate_setups(l_header_id IN NUMBER) ;
PROCEDURE validate_records ( l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
, p_run_mode IN VARCHAR2
);
PROCEDURE process_records ( l_processed_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_processed_line_dis_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
);
PROCEDURE main( x_errbuf OUT VARCHAR2
,x_retcode OUT VARCHAR2
,p_run_mode IN VARCHAR2
)
/******************************************************************************************************************
* Procedure Name : main *
* *
* Description : Calls validate_setups,validate_records, process_records Procedures *
* *
* Called From : Concurrent Program *
* *
* Parameters Type Description *
* ----------------------------------------------------------------------------- *
* x_errbuf OUT Standard Error Buffer *
* x_retcode OUT Standard Error Code *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 19-DEC-2006 Original Code *
*******************************************************************************************************************/
IS
l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_AR_INV_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_02';
--Object Name
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
--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;
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_total_inv_line_rec_cnt PLS_INTEGER ;
l_valid_inv_line_rec_cnt PLS_INTEGER ;
l_error_inv_line_rec_cnt PLS_INTEGER ;
l_total_line_dist_rec_cnt PLS_INTEGER ;
l_valid_line_dist_rec_cnt PLS_INTEGER ;
l_error_line_dist_rec_cnt PLS_INTEGER ;
l_processed_inv_line_rec_cnt PLS_INTEGER ;
l_processed_line_dist_rec_cnt PLS_INTEGER ;
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
/*----------------------------------------------------------------------------------------------------
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 := 'Invoice Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Description'; --Fifth 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;
--Call procedure to validate setups
validate_setups(l_header_id);
IF (l_error_flag <> 'N') THEN
x_retcode := 2;
RETURN;
ELSE
--Call procedure to validate invoice lines and line distributions data
validate_records ( l_total_inv_line_rec_cnt
, l_valid_inv_line_rec_cnt
, l_error_inv_line_rec_cnt
, l_total_line_dist_rec_cnt
, l_valid_line_dist_rec_cnt
, l_error_line_dist_rec_cnt
, l_header_id
, p_run_mode
);
process_records ( l_processed_inv_line_rec_cnt
, l_processed_line_dist_rec_cnt
, l_header_id
);
BEGIN
DELETE xx_open_inv_lines_preint
WHERE status ='PR';
DELETE xx_open_inv_lines_dist_preint
WHERE status ='PR';
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
COMMIT;
END IF;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
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_valid_inv_line_rec_cnt
, p_successful_recs => l_processed_inv_line_rec_cnt
, p_error_recs => l_error_inv_line_rec_cnt
);
--
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
);
fnd_file.put_line(fnd_file.log,'Return Value :'||l_return_value);
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
WHEN g_rec_ins_exception THEN
x_retcode := 2;
ROLLBACK;
RETURN;
--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
null;
--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
------------------------------------------------------------------------------------*/
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
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 validate_setups(l_header_id IN NUMBER)
IS
/****************************************************************************************************************
* Procedure Name : validate_setups *
* *
* Description : Procedure which does the setup validations *
* *
* Called From : main *
* *
********************************************************************************************************************/
l_set_of_book_name gl_sets_of_books.name%TYPE:='Vision Operations (USA)' ;
l_term_name ra_terms.name%type:= 'IMMEDIATE' ;
l_orig_term_id ra_terms.term_id%TYPE ;
-- l_currency_code fnd_currencies.currency_code%TYPE:= 'USD' ;
BEGIN
-------- Validate Set_of_books_id --------
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM gl_sets_of_books
WHERE name = l_set_of_book_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('Set of books ID is not defined .');
--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 => 'Set of books ID is not defined.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => l_set_of_book_name
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
--l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||l_set_of_book_name);
--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 => SQLERRM||'-'||l_set_of_book_name
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-------- Validate BATCH_SOURCE --------
BEGIN
SELECT name,auto_trx_numbering_flag,batch_source_id
INTO l_batch_source, l_trx_numbering_flag,l_batch_source_id
FROM ra_batch_sources
WHERE name = l_batch_source
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('Batch Source is not defined .');
--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 => 'Batch Source is not defined.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => l_batch_source
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
--l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||l_batch_source);
--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 => SQLERRM||'-'||l_batch_source
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END validate_setups;
-- procedure to validate the staging table data
PROCEDURE validate_records ( l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
, p_run_mode IN VARCHAR2
)
/**************************************************************************************************************** *
* Procedure Name : validate_records *
* *
* Description : validate the staging table data *
* *
* Called From : main *
* *
* Parameters Type Description *
* ----------------------------------------------------------------------------- *
* l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* xx_open_inv_cont_rec_cnv : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
*Change History *
* *
*Ver Date Author CR/BUG # Description *
*------ ----------- ----------------- --------------- --------------------------- *
*Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
*******************************************************************************************************************/
IS
l_orig_term_id VARCHAR2(15);
l_trx_number VARCHAR2(20) := NULL;
l_conc_seg_cnt NUMBER := 0;
l_record_error_flag NUMBER ;
l_customer_id NUMBER ;
l_rec_id VARCHAR2(50) := NULL;
l_sum_rec NUMBER;
l_rec_count NUMBER;
l_flex_context_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID ;
l_line_context_count NUMBER;
l_term_name ra_terms_vl.name%type;
l_cust_trx_type ra_cust_trx_types.name%TYPE;
l_adress_id ar_addresses_v.address_id%TYPE;
l_contact_id ar_contacts_v.contact_id%TYPE;
l_currency_code fnd_currencies.currency_code%TYPE ;
--Cursor to fetch Invoice Line information from the staging table
CURSOR cur_inv_lines IS
SELECT *
FROM xx_open_inv_lines_cnv
WHERE status ='NW';
CURSOR cur_inv_lines_vl IS
SELECT *
FROM xx_open_inv_lines_cnv
WHERE status ='NW';
CURSOR cur_inv_lines_preint IS
SELECT *
FROM xx_open_inv_lines_preint
WHERE status ='IP';
--Cursor to fetch Invoice Line distribution information from the staging table
CURSOR cur_inv_lines_dist (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_cnv
WHERE status ='NW'
AND line_context = p_line_context
AND line_attribute1 = p_line_attribute1
AND line_attribute2 = p_line_attribute2
;
CURSOR cur_inv_lines_dist_vl (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_cnv
WHERE status ='NW'
AND line_context = p_line_context
AND line_attribute1 = p_line_attribute1
AND line_attribute2 = p_line_attribute2
;
CURSOR cur_inv_lines_dist_preint (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_preint
WHERE status ='IP'
AND interface_line_context = p_line_context
AND interface_line_attribute1 = p_line_attribute1
AND interface_line_attribute2 = p_line_attribute2
;
BEGIN
l_total_inv_line_rec_cnt := 0;
l_valid_inv_line_rec_cnt := 0;
l_error_inv_line_rec_cnt := 0;
l_total_line_dist_rec_cnt := 0;
l_valid_line_dist_rec_cnt := 0;
l_error_line_dist_rec_cnt := 0;
-- To get the line count from Invoive lines and line data files
BEGIN
SELECT SUM (COUNT(xol.line_context) + COUNT(xld.line_context))
INTO l_sum_rec
FROM xx_open_inv_lines_cnv xol,
xx_open_inv_lines_dist_cnv xld
WHERE xol.STATUS ='NW'
AND xld.STATUS ='NW'
AND xol.line_context = xld.line_context
AND xol.line_attribute1 = xld.line_attribute1
AND xol.line_attribute2 = xld.line_attribute2
GROUP BY xol.line_context, xld.line_context;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END ;
l_error_msg := NULL ;
BEGIN
SELECT rec_count
INTO l_rec_count
FROM xx_open_inv_cont_rec_cnv
WHERE STATUS ='NW' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('No control record found in data file');
l_rec_count :=0;
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 => 'No control record found in data file;'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END ;
l_error_msg := NULL ;
IF l_sum_rec <> l_rec_count THEN
l_record_error_flag := 1;
xx_trace.l ('Line count is not equal to summary record;');
--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 => 'Line count is not equal to summary record;'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = 'ER' ;
UPDATE xx_open_inv_lines_dist_cnv
SET status = 'ER' ;
UPDATE xx_open_inv_cont_rec_cnv
SET status = 'ER' ;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
ELSE
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'F' THEN
FOR lcu_inv_lines IN cur_inv_lines
LOOP
l_record_error_flag := 0;
l_total_inv_line_rec_cnt := l_total_inv_line_rec_cnt + 1 ;
l_rec_id:= lcu_inv_lines.recid ;
-- 1 Check if mandatory field line_context has value
IF lcu_inv_lines.line_context IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line context can not 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 => 'Line context can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- 1 Check if mandatory field line_attribute1 has value
IF lcu_inv_lines.line_attribute1 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Order ID can not 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 => 'Order ID can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.line_attribute2 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Legacy Line ID can not 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 => 'Legacy Line ID can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.line_type IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line type can not 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 => 'Line type can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.description IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line description can not 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 => 'Line description can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.currency_code IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Currency code can not 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 => 'Currency code can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.bill_customer_ref IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Customer Ref can not 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 => 'Bill to Customer Ref can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.bill_address_ref IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Bill address ref can not 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 => 'Bill to Address Ref can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.trx_number IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Transaction Number can not 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 => 'Transaction Number can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Updating transaction record in staging table as ER if error else VL if successfully validated
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_inv_lines.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
-- Incrementing record counts
IF l_record_error_flag = 1 THEN
l_error_inv_line_rec_cnt := l_error_inv_line_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_inv_line_rec_cnt := l_valid_inv_line_rec_cnt + 1;
END IF;
IF l_record_error_flag = 0 THEN
FOR lcu_lines_dist IN cur_inv_lines_dist (lcu_inv_lines.line_context
,lcu_inv_lines.line_attribute1
,lcu_inv_lines.line_attribute2
)
LOOP
l_total_line_dist_rec_cnt := l_total_line_dist_rec_cnt + 1;
l_rec_id :=lcu_lines_dist.recid;
-- 1 Check if mandatory field account_class has value
IF lcu_lines_dist.account_class IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Account class can not 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 => 'Account class in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment1 has value
IF lcu_lines_dist.segment1 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment1 can not 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 => 'Segment1 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment2 has value
IF lcu_lines_dist.segment2 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment2 can not 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 => 'Segment2 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment3 has value
IF lcu_lines_dist.segment3 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment3 can not 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 => 'Segment3 in distribution can not be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment4 has value
IF lcu_lines_dist.segment4 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment4 can not 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 => 'Segment4 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment5 has value
IF lcu_lines_dist.segment5 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment5 can not 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 => 'Segment5 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
--Updating customer record in staging table as ER if error
BEGIN
UPDATE xx_open_inv_lines_dist_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_lines_dist.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
-- Incrementing record counts
IF l_record_error_flag = 1 THEN
l_error_line_dist_rec_cnt := l_error_line_dist_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_line_dist_rec_cnt := l_valid_line_dist_rec_cnt + 1;
END IF;
END LOOP ;
END IF;
END LOOP;
END IF;
END IF;
-- inserting data into pre interface tables
BEGIN
FOR lcu_inv_lines_vl IN cur_inv_lines_vl
LOOP
BEGIN
INSERT INTO xx_open_inv_lines_preint(
recid
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, batch_source_name
, set_of_books_id
, line_type
, description
, currency_code
, amount
, cust_trx_type_name
, term_name
, orig_system_bill_customer_ref
, orig_system_bill_address_ref
, orig_system_bill_contact_ref
, orig_system_ship_customer_ref
, orig_system_ship_address_ref
, conversion_type
, conversion_date
, conversion_rate
, trx_date
, gl_date
, trx_number
, line_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
, status
)
VALUES (
lcu_inv_lines_vl.recid
, lcu_inv_lines_vl.line_context
, lcu_inv_lines_vl.line_attribute1
, lcu_inv_lines_vl.line_attribute2
, l_batch_source
, l_set_of_books_id
, lcu_inv_lines_vl.line_type
, lcu_inv_lines_vl.description
, lcu_inv_lines_vl.currency_code
, lcu_inv_lines_vl.amount
, lcu_inv_lines_vl.cust_trx_type_name
, lcu_inv_lines_vl.term_name
, lcu_inv_lines_vl.bill_customer_ref
, lcu_inv_lines_vl.bill_address_ref
, lcu_inv_lines_vl.bill_contact_ref
, lcu_inv_lines_vl.ship_customer_ref
, lcu_inv_lines_vl.ship_address_ref
, 'User'
, lcu_inv_lines_vl.conversion_date
, lcu_inv_lines_vl.conversion_rate
, TO_DATE(lcu_inv_lines_vl.trx_date,'MM/DD/YYYY')
, TO_DATE(lcu_inv_lines_vl.trx_date,'MM/DD/YYYY')
, DECODE(l_trx_numbering_flag,'N',lcu_inv_lines_vl.trx_number,NULL)
, lcu_inv_lines_vl.line_number
, lcu_inv_lines_vl.created_by
, lcu_inv_lines_vl.creation_date
, lcu_inv_lines_vl.last_updated_by
, lcu_inv_lines_vl.last_update_date
, lcu_inv_lines_vl.last_update_login
, l_org_id
, 'IP'
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line records to pre interface table ');
--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 => 'Error while inserting Invoice Line records to pre interface table .'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => SQLERRM
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- RAISE g_rec_ins_exception;
END;
IF l_record_error_flag = 0 THEN
FOR lcu_valid_lines_dist_vl IN cur_inv_lines_dist_vl (lcu_inv_lines_vl.line_context
,lcu_inv_lines_vl.line_attribute1
,lcu_inv_lines_vl.line_attribute2
)
LOOP
BEGIN
INSERT INTO xx_open_inv_lines_dist_preint (
recid
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, account_class
, amount
, percent
, segment1
, segment2
, segment3
, segment4
, segment5
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
, status
)
VALUES (
lcu_valid_lines_dist_vl.recid
, lcu_valid_lines_dist_vl.line_context
, lcu_valid_lines_dist_vl.line_attribute1
, lcu_valid_lines_dist_vl.line_attribute2
, lcu_valid_lines_dist_vl.account_class
, lcu_valid_lines_dist_vl.amount
, lcu_valid_lines_dist_vl.percent
, lcu_valid_lines_dist_vl.segment1
, lcu_valid_lines_dist_vl.segment2
, lcu_valid_lines_dist_vl.segment3
, lcu_valid_lines_dist_vl.segment4
, lcu_valid_lines_dist_vl.segment5
, lcu_valid_lines_dist_vl.created_by
, lcu_valid_lines_dist_vl.creation_date
, lcu_valid_lines_dist_vl.last_updated_by
, lcu_valid_lines_dist_vl.last_update_date
, lcu_valid_lines_dist_vl.last_update_login
, l_org_id
, 'IP'
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice distribution records to pre interface table ');
--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 => 'Error while inserting Invoice distribution records to pre interface table .'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- RAISE g_rec_ins_exception;
END;
--Updating record in staging table as ER if error else PR if successfully validated
BEGIN
UPDATE xx_open_inv_lines_dist_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_valid_lines_dist_vl.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
END LOOP;
END IF;
--Updating record in staging table as ER if error else PR if successfully validated
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_inv_lines_vl.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
END LOOP;
END ;
-- Validating the data in pre interface tables
BEGIN
FOR lcu_inv_lines_preint IN cur_inv_lines_preint
LOOP
l_rec_id:=lcu_inv_lines_preint.recid;
l_record_error_flag := 0;
-------- Validate TERM_NAME --------
BEGIN
SELECT term_id
INTO l_orig_term_id
FROM ra_terms
WHERE name = lcu_inv_lines_preint.term_name
AND TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE))
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag :=1;
xx_trace.l ('Term Name not defined ');
--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 => 'Term Name not defined.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.term_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-------- Validate currencu code --------
BEGIN
SELECT fc.currency_code
INTO l_currency_code
FROM fnd_currencies fc
WHERE currency_flag = 'Y'
AND enabled_flag = 'Y'
AND nvl(fc.end_date_active,sysdate)>= sysdate
AND fc.currency_code = lcu_inv_lines_preint.currency_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Currency Code not defined'||'-'||lcu_inv_lines_preint.currency_code);
l_record_error_flag := 1;
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 => 'Currency Code not defined'||'-'||lcu_inv_lines_preint.currency_code
, p_identifier3 => l_rec_id
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_error_msg := SQLERRM||'-'||lcu_inv_lines_preint.currency_code;
l_record_error_flag := 1;
xx_trace.l(l_error_msg);
END;
-------- Validate transaction number --------
BEGIN
SELECT trx_number
INTO l_trx_number
FROM ra_customer_trx_v
WHERE trx_number = lcu_inv_lines_preint.trx_number;
IF l_trx_number IS NOT NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Invoice Number already exists in Oracle - '||lcu_inv_lines_preint.trx_number);
--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 => 'Invoice Number already exists in Oracle - '||lcu_inv_lines_preint.trx_number
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.trx_number );
--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 => SQLERRM||'-'||lcu_inv_lines_preint.trx_number
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-------- validate Interface line concext --------
BEGIN
SELECT descriptive_flex_context_name
INTO l_flex_context_name
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flex_context_name =lcu_inv_lines_preint.interface_line_context
AND application_id = 222
AND enabled_flag ='Y' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Interface line context is not defined'||' '||lcu_inv_lines_preint.interface_line_context);
--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 => 'Interface line context is not defined'||' '||lcu_inv_lines_preint.interface_line_context
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.interface_line_context);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.interface_line_context
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- Check whether combination of line lontext and line attributes are already existing or not
BEGIN
SELECT COUNT(1)
INTO l_line_context_count
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE interface_line_context||'-'||
interface_line_attribute1||'-'||
interface_line_attribute2 = lcu_inv_lines_preint.interface_line_context
||'-'||lcu_inv_lines_preint.interface_line_attribute1
||'-'||lcu_inv_lines_preint.interface_line_attribute2
AND org_id =l_org_id ;
IF l_line_context_count >= 1 THEN
l_record_error_flag := 1;
xx_trace.l ('line context, Order ID Legacy Line ID combination IS already existing');
--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 => 'line context, Order ID Legacy Line ID combination IS already existing'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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 OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validating customer transaction type
BEGIN
SELECT NAME
INTO l_cust_trx_type
FROM ra_cust_trx_types
WHERE name = lcu_inv_lines_preint.cust_trx_type_name
AND org_id =l_org_id ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('customer transaction type is not defined'||' '||lcu_inv_lines_preint.cust_trx_type_name);
--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 => 'customer transaction type is not defined'||' '||lcu_inv_lines_preint.cust_trx_type_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.cust_trx_type_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.cust_trx_type_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
--validate term name
BEGIN
SELECT NAME
INTO l_term_name
FROM ra_terms_vl
WHERE NAME =lcu_inv_lines_preint.term_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Term Name is not defined'||' '||lcu_inv_lines_preint.term_name);
--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 => 'Term Name is not defined'||' '||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.term_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validating line type
IF (lcu_inv_lines_preint.line_type <>'LINE') THEN
l_record_error_flag := 1;
xx_trace.l ('Line type is not defined'||' '||lcu_inv_lines_preint.line_type);
--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 => 'Line type is not defined'||' '||lcu_inv_lines_preint.line_type
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-------- GET Bill to Customer ID --------
BEGIN
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
WHERE orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to customer id not found');
--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 => 'Bill to customer id not found'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_customer_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_customer_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- validate bill to address reference
BEGIN
SELECT AA.address_id
INTO l_adress_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='BILL_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_address_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref);
--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 => 'Bill to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validate bill to contact reference
IF lcu_inv_lines_preint.orig_system_bill_contact_ref IS NOT NULL THEN
BEGIN
SELECT AN.contact_id
INTO l_contact_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS,
AR_CONTACTS_V AN,
AR_CONTACT_ROLES_V ACR
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='BILL_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_address_ref
AND AN.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_contact_ref
AND AC.customer_id= AN.customer_id
AND AA.address_id = AN.address_id
AND AN.contact_id=ACR.contact_id
AND ACR.primary_flag ='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Contact Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref);
--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 => 'Bill to Contact Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF ;
-- To validate ship to customer reference
IF lcu_inv_lines_preint.orig_system_ship_customer_ref IS NOT NULL THEN
BEGIN
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
WHERE orig_system_reference = lcu_inv_lines_preint.orig_system_ship_customer_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Ship to customer id not found');
--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 => 'Ship to customer id not found'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_customer_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_customer_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
IF lcu_inv_lines_preint.orig_system_ship_address_ref IS NOT NULL THEN
-- validate bill to address reference
BEGIN
SELECT AA.address_id
INTO l_adress_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='SHIP_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_ship_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_ship_address_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Ship to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref);
--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 => 'Ship to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
END IF ;
-- update pre interface table
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = DECODE(l_record_error_flag,1,'ER','IP')
WHERE recid = lcu_inv_lines_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--RAISE g_rec_ins_exception;
END;
l_error_msg :=NULL ;
IF l_record_error_flag = 0 THEN
FOR lcu_lines_dist_preint IN cur_inv_lines_dist_preint (lcu_inv_lines_preint.interface_line_context
,lcu_inv_lines_preint.interface_line_attribute1
,lcu_inv_lines_preint.interface_line_attribute2
)
LOOP
l_rec_id:=lcu_lines_dist_preint.recid;
l_conc_seg_cnt:=0;
---- validating GL segments code combination----------------
BEGIN
SELECT COUNT(1)
INTO l_conc_seg_cnt
FROM gl_code_combinations_kfv
where concatenated_segments = lcu_lines_dist_preint.segment1
||'-'||lcu_lines_dist_preint.segment2
||'-'||lcu_lines_dist_preint.segment3
||'-'||lcu_lines_dist_preint.segment4
||'-'||lcu_lines_dist_preint.segment5;
IF l_conc_seg_cnt = 0 THEN
l_record_error_flag := 1;
xx_trace.l ('This is not valid code combination for gl segments');
--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 => 'This is not valid code combination for gl segments'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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 OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- to validate account class
BEGIN
IF ( lcu_lines_dist_preint.account_class !='REV'
-- OR lcu_lines_dist_preint.account_class !='FREIGHT' OR
-- lcu_lines_dist_preint.account_class !='TAX' OR
-- lcu_lines_dist_preint.account_class !='REC' OR
-- lcu_lines_dist_preint.account_class !='CHARGES' OR
-- lcu_lines_dist_preint.account_class !='UNEARN' OR
-- lcu_lines_dist_preint.account_class !='UNBILL'
) THEN
l_record_error_flag := 1;
xx_trace.l ('This is not valid account class'||'-'||lcu_lines_dist_preint.account_class);
--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 => 'This is not valid account class'||'-'||lcu_lines_dist_preint.account_class
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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;
IF l_record_error_flag = 1 THEN
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = 'ER'
WHERE recid = lcu_lines_dist_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
l_record_error_flag :=1;
--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 => l_error_msg
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
BEGIN
UPDATE xx_open_inv_lines_dist_preint
SET status = DECODE(l_record_error_flag,1,'ER','IP')
WHERE recid = lcu_lines_dist_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
l_record_error_flag :=1;
--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 => l_error_msg
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END LOOP ;
END IF ;
END LOOP;
END ;
END validate_records;
PROCEDURE process_records ( l_processed_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_processed_line_dis_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER)
/* ***********************************************************************************************************
* Procedure Name : process_records *
* Description : Process validated Invoice records *
* Called From : Concurrent Program *
* *
* Parameters Type Description *
* l_processed_inv_line_rec_cnt OUT To stote total processed invoice lines records *
* l_processed_line_dis_rec_cnt OUT To stote total processed invoice line distribution records *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
* *
* Change History *
* ----------------- *
* Version Date Author Description *
* --------- ------------- --------------- -------------------------- *
* Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
************************************************************************************************************* */
IS
l_record_error_flag NUMBER ;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID ;
int_date DATE ;
--Cursor to fetch Invoice Line information from the staging table
CURSOR cur_valid_inv_lines IS
SELECT *
FROM xx_open_inv_lines_preint
WHERE status = 'IP'
;
--Cursor to fetch Invoice Line distribution information from the staging table
CURSOR cur_valid_inv_lines_dist (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_preint
WHERE status ='IP'
AND interface_line_context = p_line_context
AND interface_line_attribute1 = p_line_attribute1
AND interface_line_attribute2 = p_line_attribute2 ;
BEGIN
l_processed_inv_line_rec_cnt := 0;
l_processed_line_dis_rec_cnt := 0;
FOR lcu_valid_inv_lines IN cur_valid_inv_lines
LOOP
l_record_error_flag := 0;
BEGIN
INSERT INTO ra_interface_lines_all(
interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, batch_source_name
, set_of_books_id
, line_type
, description
, currency_code
, amount
, cust_trx_type_name
, term_name
, orig_system_bill_customer_ref
, orig_system_bill_address_ref
, orig_system_bill_contact_ref
, orig_system_ship_customer_ref
, orig_system_ship_address_ref
, conversion_type
, conversion_date
, conversion_rate
, trx_date
, gl_date
, trx_number
, line_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
)
VALUES (
lcu_valid_inv_lines.interface_line_context
, lcu_valid_inv_lines.interface_line_attribute1
, lcu_valid_inv_lines.interface_line_attribute2
, l_batch_source
, l_set_of_books_id
, lcu_valid_inv_lines.line_type
, lcu_valid_inv_lines.description
, lcu_valid_inv_lines.currency_code
, lcu_valid_inv_lines.amount
, lcu_valid_inv_lines.cust_trx_type_name
, lcu_valid_inv_lines.term_name
, lcu_valid_inv_lines.orig_system_bill_customer_ref
, lcu_valid_inv_lines.orig_system_bill_address_ref
, lcu_valid_inv_lines.orig_system_bill_contact_ref
, lcu_valid_inv_lines.orig_system_ship_customer_ref
, lcu_valid_inv_lines.orig_system_ship_address_ref
, 'User'
, TO_DATE(lcu_valid_inv_lines.conversion_date,'YYYY/MM/DD')
, lcu_valid_inv_lines.conversion_rate
, lcu_valid_inv_lines.trx_date
, lcu_valid_inv_lines.trx_date
, lcu_valid_inv_lines.trx_number
, lcu_valid_inv_lines.line_number
, lcu_valid_inv_lines.created_by
, lcu_valid_inv_lines.creation_date
, lcu_valid_inv_lines.last_updated_by
, lcu_valid_inv_lines.last_update_date
, lcu_valid_inv_lines.last_update_login
, lcu_valid_inv_lines.org_id
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line records');
--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 => 'Error while inserting Invoice Line records'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF l_record_error_flag = 0 THEN
FOR lcu_valid_lines_dist IN cur_valid_inv_lines_dist (lcu_valid_inv_lines.interface_line_context
,lcu_valid_inv_lines.interface_line_attribute1
,lcu_valid_inv_lines.interface_line_attribute2
)
LOOP
BEGIN
INSERT INTO ra_interface_distributions_all (
interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, account_class
, amount
, percent
, segment1
, segment2
, segment3
, segment4
, segment5
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
)
VALUES (
lcu_valid_lines_dist.interface_line_context
, lcu_valid_lines_dist.interface_line_attribute1
, lcu_valid_lines_dist.interface_line_attribute2
, lcu_valid_lines_dist.account_class
, lcu_valid_lines_dist.amount
, lcu_valid_lines_dist.percent
, lcu_valid_lines_dist.segment1
, lcu_valid_lines_dist.segment2
, lcu_valid_lines_dist.segment3
, lcu_valid_lines_dist.segment4
, lcu_valid_lines_dist.segment5
, lcu_valid_lines_dist.created_by
, lcu_valid_lines_dist.creation_date
, lcu_valid_lines_dist.last_updated_by
, lcu_valid_lines_dist.last_update_date
, lcu_valid_lines_dist.last_update_login
, lcu_valid_lines_dist.org_id
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line distribution records ');
--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 => 'Error while inserting Invoice Line distribution records '
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--Updating invoice distribution records in staging table as E if error in site processing else P
IF l_record_error_flag = 0 THEN
l_processed_line_dis_rec_cnt := l_processed_line_dis_rec_cnt + 1 ;
BEGIN
UPDATE xx_open_inv_lines_dist_preint
SET status = 'PR'
WHERE recid =lcu_valid_lines_dist.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
END IF;
END LOOP;
END IF;
--Updating invoice lines records in staging table as E if error in site processing else P
IF l_record_error_flag = 0 THEN
l_processed_inv_line_rec_cnt := l_processed_inv_line_rec_cnt + 1 ;
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = DECODE(l_record_error_flag,1,'ER','PR')
WHERE recid =lcu_valid_inv_lines.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
END IF ;
END LOOP;
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN
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_processed_inv_line_rec_cnt > 0 THEN
-- This concurrent program is used for creating Invoice Transactions
l_standard_request_id := fnd_request.submit_request
(
application => 'AR'
,program => 'RAXMTR'
,description => ''
,start_time => ''
,sub_request => FALSE
,argument1 => '1' -- number of instances
,argument2 => l_batch_source_id -- batch source id for batch source "LEGACY"
,argument3 => '' -- batch source name
,argument4 => sysdate -- default date
,argument5 => '' -- transaction flexfield
,argument6 => '' -- transaction type
,argument7 => '' -- bill to customer number (low)
,argument8 => '' -- bill to customer number (high)
,argument9 => '' -- bill to customer name (low)
,argument10 => '' -- bill to customer name (high)
,argument11 => '' -- gl date(low)
,argument12 => '' -- gl date (high)
,argument13 => '' -- ship date(low)
,argument14 => '' -- ship date(high)
,argument15 => '' -- transaction number(low)
,argument16 => '' -- transaction number(high)
,argument17 => '' -- sales order number (low)
,argument18 => '' -- sales order number (high)
,argument19 => '' -- invoice date (low)
,argument20 => '' -- invoice date (high)
,argument21 => '' -- ship to customer number (low)
,argument22 => '' -- ship to customer number (high)
,argument23 => '' -- ship to customer name (low)
,argument24 => '' -- ship to customer name (high)
,argument25 => 'Y' -- base due date on trx date
,argument26 => '' -- due date adjustment date
,argument27 => l_org_id -- org id
);
IF l_standard_request_id > 0 THEN
COMMIT;
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-00004'
, p_identifier2 => 'Error in submitting concurrent request'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
l_phase := NULL ;
l_vstatus := NULL ;
l_dev_phase := NULL ;
l_dev_status := NULL ;
l_message := NULL ;
--Wait for the completion of the concurrent request (if submitted successfully)
l_completed := fnd_concurrent.wait_for_request
(request_id => l_standard_request_id
, INTERVAL => 60
, 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-'|| l_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-00004'
, p_identifier2 => 'No records To Process in Interface Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END;
END ;
END;
/
SHOW ERROR;
EXIT;
--Version Draft 1a
/******************************************************************************************************************
* Package Name : xx_ar_open_inv_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the Invoice data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create invoive lines and line distributions. *
* *
* *
* Procedures : *
* --------------------- *
* main : Main procedure registered as the executable file name, which *
* calls the other procedures *
* *
* validate_setups : Validate Certain prerequisite setups in Oracle Apps *
* *
* validate_records : Validate and update records in staging table *
* *
* process_records : Populate the standard interface tables *
* *
* Tables Accessed : *
* Access Type-------- (I - Insert, S - Select, U - Update, D - Delete) *
* *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* * *
********************************************************************************************************************/
/*-----------------------------------------------------------
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 := 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 := 20;
g_err_col7_width NUMBER := 10;
g_err_col8_width NUMBER := 0;
TYPE process_set_id_rec IS RECORD (process_set_id NUMBER);
TYPE process_set_id_tbl IS TABLE OF process_set_id_rec INDEX BY BINARY_INTEGER;
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
PROCEDURE main( x_errbuf OUT VARCHAR2
, x_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
);
g_rec_ins_exception EXCEPTION;
END xx_ar_open_inv_cnv_pkb;
/
SHOW ERROR;
CREATE OR REPLACE PACKAGE BODY xx_ar_open_inv_cnv_pkb AS
--Version Draft 1a
/*****************************************************************************************************************
* Package Name : xx_ar_open_inv_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the invoice data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create invoive lines and line distributions. *
* *
* Procedures : *
* --------------------- *
* main : Main procedure registered as the executable file name, which *
* calls the other procedures *
* *
* validate_setups : Validate Certain prerequisite setups in Oracle Apps *
* *
* validate_records : Validate and update records in staging table *
* *
* process_records : Populate the standard interface tables *
* *
* Tables Accessed : *
* Access Type----------------- (I - Insert, S - Select, U - Update, D - Delete) *
* *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
**********************************************************************************************************************/
l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
l_batch_source ra_batch_sources.NAME%TYPE:='LEGACY' ;
l_trx_numbering_flag ra_batch_sources.auto_trx_numbering_flag%TYPE ;
l_batch_source_id ra_batch_sources.batch_source_id%TYPE ;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N' ;
l_header_id xx_emf_message_headers.header_id%TYPE ;
--EMF ID
l_return_value NUMBER := NULL ;
l_output_message VARCHAR2 (1000) ;
l_error_msg VARCHAR2(2000) ;
l_request_id xx_emf_message_headers.request_id%TYPE:= fnd_profile.VALUE ('CONC_REQUEST_ID');
l_process_status NUMBER ;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0 ;
l_completed BOOLEAN ;
l_phase VARCHAR2 (200) ;
l_vstatus VARCHAR2 (200) ;
l_dev_phase VARCHAR2 (200) ;
l_dev_status VARCHAR2 (200) ;
l_message VARCHAR2 (2000) ;
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 ;
PROCEDURE validate_setups(l_header_id IN NUMBER) ;
PROCEDURE validate_records ( l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
, p_run_mode IN VARCHAR2
);
PROCEDURE process_records ( l_processed_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_processed_line_dis_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
);
PROCEDURE main( x_errbuf OUT VARCHAR2
,x_retcode OUT VARCHAR2
,p_run_mode IN VARCHAR2
)
/******************************************************************************************************************
* Procedure Name : main *
* *
* Description : Calls validate_setups,validate_records, process_records Procedures *
* *
* Called From : Concurrent Program *
* *
* Parameters Type Description *
* ----------------------------------------------------------------------------- *
* x_errbuf OUT Standard Error Buffer *
* x_retcode OUT Standard Error Code *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 19-DEC-2006 Original Code *
*******************************************************************************************************************/
IS
l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_AR_INV_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_02';
--Object Name
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
--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;
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_total_inv_line_rec_cnt PLS_INTEGER ;
l_valid_inv_line_rec_cnt PLS_INTEGER ;
l_error_inv_line_rec_cnt PLS_INTEGER ;
l_total_line_dist_rec_cnt PLS_INTEGER ;
l_valid_line_dist_rec_cnt PLS_INTEGER ;
l_error_line_dist_rec_cnt PLS_INTEGER ;
l_processed_inv_line_rec_cnt PLS_INTEGER ;
l_processed_line_dist_rec_cnt PLS_INTEGER ;
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
/*----------------------------------------------------------------------------------------------------
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 := 'Invoice Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Description'; --Fifth 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;
--Call procedure to validate setups
validate_setups(l_header_id);
IF (l_error_flag <> 'N') THEN
x_retcode := 2;
RETURN;
ELSE
--Call procedure to validate invoice lines and line distributions data
validate_records ( l_total_inv_line_rec_cnt
, l_valid_inv_line_rec_cnt
, l_error_inv_line_rec_cnt
, l_total_line_dist_rec_cnt
, l_valid_line_dist_rec_cnt
, l_error_line_dist_rec_cnt
, l_header_id
, p_run_mode
);
process_records ( l_processed_inv_line_rec_cnt
, l_processed_line_dist_rec_cnt
, l_header_id
);
BEGIN
DELETE xx_open_inv_lines_preint
WHERE status ='PR';
DELETE xx_open_inv_lines_dist_preint
WHERE status ='PR';
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
COMMIT;
END IF;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
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_valid_inv_line_rec_cnt
, p_successful_recs => l_processed_inv_line_rec_cnt
, p_error_recs => l_error_inv_line_rec_cnt
);
--
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
);
fnd_file.put_line(fnd_file.log,'Return Value :'||l_return_value);
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
WHEN g_rec_ins_exception THEN
x_retcode := 2;
ROLLBACK;
RETURN;
--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
null;
--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
------------------------------------------------------------------------------------*/
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
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 validate_setups(l_header_id IN NUMBER)
IS
/****************************************************************************************************************
* Procedure Name : validate_setups *
* *
* Description : Procedure which does the setup validations *
* *
* Called From : main *
* *
********************************************************************************************************************/
l_set_of_book_name gl_sets_of_books.name%TYPE:='Vision Operations (USA)' ;
l_term_name ra_terms.name%type:= 'IMMEDIATE' ;
l_orig_term_id ra_terms.term_id%TYPE ;
-- l_currency_code fnd_currencies.currency_code%TYPE:= 'USD' ;
BEGIN
-------- Validate Set_of_books_id --------
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM gl_sets_of_books
WHERE name = l_set_of_book_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('Set of books ID is not defined .');
--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 => 'Set of books ID is not defined.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => l_set_of_book_name
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
--l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||l_set_of_book_name);
--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 => SQLERRM||'-'||l_set_of_book_name
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-------- Validate BATCH_SOURCE --------
BEGIN
SELECT name,auto_trx_numbering_flag,batch_source_id
INTO l_batch_source, l_trx_numbering_flag,l_batch_source_id
FROM ra_batch_sources
WHERE name = l_batch_source
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('Batch Source is not defined .');
--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 => 'Batch Source is not defined.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => l_batch_source
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
--l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||l_batch_source);
--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 => SQLERRM||'-'||l_batch_source
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END validate_setups;
-- procedure to validate the staging table data
PROCEDURE validate_records ( l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
, p_run_mode IN VARCHAR2
)
/**************************************************************************************************************** *
* Procedure Name : validate_records *
* *
* Description : validate the staging table data *
* *
* Called From : main *
* *
* Parameters Type Description *
* ----------------------------------------------------------------------------- *
* l_total_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_valid_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_error_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_total_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_valid_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_error_line_dist_rec_cnt OUT NOCOPY PLS_INTEGER *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_cnv : S,I,U,D *
* xx_open_inv_lines_dist_cnv : S,I,U,D *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* xx_open_inv_cont_rec_cnv : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
*Change History *
* *
*Ver Date Author CR/BUG # Description *
*------ ----------- ----------------- --------------- --------------------------- *
*Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
*******************************************************************************************************************/
IS
l_orig_term_id VARCHAR2(15);
l_trx_number VARCHAR2(20) := NULL;
l_conc_seg_cnt NUMBER := 0;
l_record_error_flag NUMBER ;
l_customer_id NUMBER ;
l_rec_id VARCHAR2(50) := NULL;
l_sum_rec NUMBER;
l_rec_count NUMBER;
l_flex_context_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID ;
l_line_context_count NUMBER;
l_term_name ra_terms_vl.name%type;
l_cust_trx_type ra_cust_trx_types.name%TYPE;
l_adress_id ar_addresses_v.address_id%TYPE;
l_contact_id ar_contacts_v.contact_id%TYPE;
l_currency_code fnd_currencies.currency_code%TYPE ;
--Cursor to fetch Invoice Line information from the staging table
CURSOR cur_inv_lines IS
SELECT *
FROM xx_open_inv_lines_cnv
WHERE status ='NW';
CURSOR cur_inv_lines_vl IS
SELECT *
FROM xx_open_inv_lines_cnv
WHERE status ='NW';
CURSOR cur_inv_lines_preint IS
SELECT *
FROM xx_open_inv_lines_preint
WHERE status ='IP';
--Cursor to fetch Invoice Line distribution information from the staging table
CURSOR cur_inv_lines_dist (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_cnv
WHERE status ='NW'
AND line_context = p_line_context
AND line_attribute1 = p_line_attribute1
AND line_attribute2 = p_line_attribute2
;
CURSOR cur_inv_lines_dist_vl (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_cnv
WHERE status ='NW'
AND line_context = p_line_context
AND line_attribute1 = p_line_attribute1
AND line_attribute2 = p_line_attribute2
;
CURSOR cur_inv_lines_dist_preint (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_preint
WHERE status ='IP'
AND interface_line_context = p_line_context
AND interface_line_attribute1 = p_line_attribute1
AND interface_line_attribute2 = p_line_attribute2
;
BEGIN
l_total_inv_line_rec_cnt := 0;
l_valid_inv_line_rec_cnt := 0;
l_error_inv_line_rec_cnt := 0;
l_total_line_dist_rec_cnt := 0;
l_valid_line_dist_rec_cnt := 0;
l_error_line_dist_rec_cnt := 0;
-- To get the line count from Invoive lines and line data files
BEGIN
SELECT SUM (COUNT(xol.line_context) + COUNT(xld.line_context))
INTO l_sum_rec
FROM xx_open_inv_lines_cnv xol,
xx_open_inv_lines_dist_cnv xld
WHERE xol.STATUS ='NW'
AND xld.STATUS ='NW'
AND xol.line_context = xld.line_context
AND xol.line_attribute1 = xld.line_attribute1
AND xol.line_attribute2 = xld.line_attribute2
GROUP BY xol.line_context, xld.line_context;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END ;
l_error_msg := NULL ;
BEGIN
SELECT rec_count
INTO l_rec_count
FROM xx_open_inv_cont_rec_cnv
WHERE STATUS ='NW' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('No control record found in data file');
l_rec_count :=0;
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 => 'No control record found in data file;'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END ;
l_error_msg := NULL ;
IF l_sum_rec <> l_rec_count THEN
l_record_error_flag := 1;
xx_trace.l ('Line count is not equal to summary record;');
--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 => 'Line count is not equal to summary record;'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = 'ER' ;
UPDATE xx_open_inv_lines_dist_cnv
SET status = 'ER' ;
UPDATE xx_open_inv_cont_rec_cnv
SET status = 'ER' ;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
ELSE
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'F' THEN
FOR lcu_inv_lines IN cur_inv_lines
LOOP
l_record_error_flag := 0;
l_total_inv_line_rec_cnt := l_total_inv_line_rec_cnt + 1 ;
l_rec_id:= lcu_inv_lines.recid ;
-- 1 Check if mandatory field line_context has value
IF lcu_inv_lines.line_context IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line context can not 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 => 'Line context can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- 1 Check if mandatory field line_attribute1 has value
IF lcu_inv_lines.line_attribute1 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Order ID can not 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 => 'Order ID can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.line_attribute2 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Legacy Line ID can not 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 => 'Legacy Line ID can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.line_type IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line type can not 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 => 'Line type can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.description IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Line description can not 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 => 'Line description can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.currency_code IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Currency code can not 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 => 'Currency code can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.bill_customer_ref IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Customer Ref can not 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 => 'Bill to Customer Ref can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.bill_address_ref IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Bill address ref can not 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 => 'Bill to Address Ref can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF lcu_inv_lines.trx_number IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Transaction Number can not 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 => 'Transaction Number can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => lcu_inv_lines.trx_number
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Updating transaction record in staging table as ER if error else VL if successfully validated
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_inv_lines.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
-- Incrementing record counts
IF l_record_error_flag = 1 THEN
l_error_inv_line_rec_cnt := l_error_inv_line_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_inv_line_rec_cnt := l_valid_inv_line_rec_cnt + 1;
END IF;
IF l_record_error_flag = 0 THEN
FOR lcu_lines_dist IN cur_inv_lines_dist (lcu_inv_lines.line_context
,lcu_inv_lines.line_attribute1
,lcu_inv_lines.line_attribute2
)
LOOP
l_total_line_dist_rec_cnt := l_total_line_dist_rec_cnt + 1;
l_rec_id :=lcu_lines_dist.recid;
-- 1 Check if mandatory field account_class has value
IF lcu_lines_dist.account_class IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Account class can not 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 => 'Account class in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment1 has value
IF lcu_lines_dist.segment1 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment1 can not 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 => 'Segment1 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment2 has value
IF lcu_lines_dist.segment2 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment2 can not 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 => 'Segment2 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment3 has value
IF lcu_lines_dist.segment3 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment3 can not 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 => 'Segment3 in distribution can not be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment4 has value
IF lcu_lines_dist.segment4 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment4 can not 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 => 'Segment4 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
-- 1 Check if mandatory field Segment5 has value
IF lcu_lines_dist.segment5 IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Segment5 can not 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 => 'Segment5 in distribution can not be null.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF ;
--Updating customer record in staging table as ER if error
BEGIN
UPDATE xx_open_inv_lines_dist_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_lines_dist.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
-- Incrementing record counts
IF l_record_error_flag = 1 THEN
l_error_line_dist_rec_cnt := l_error_line_dist_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_line_dist_rec_cnt := l_valid_line_dist_rec_cnt + 1;
END IF;
END LOOP ;
END IF;
END LOOP;
END IF;
END IF;
-- inserting data into pre interface tables
BEGIN
FOR lcu_inv_lines_vl IN cur_inv_lines_vl
LOOP
BEGIN
INSERT INTO xx_open_inv_lines_preint(
recid
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, batch_source_name
, set_of_books_id
, line_type
, description
, currency_code
, amount
, cust_trx_type_name
, term_name
, orig_system_bill_customer_ref
, orig_system_bill_address_ref
, orig_system_bill_contact_ref
, orig_system_ship_customer_ref
, orig_system_ship_address_ref
, conversion_type
, conversion_date
, conversion_rate
, trx_date
, gl_date
, trx_number
, line_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
, status
)
VALUES (
lcu_inv_lines_vl.recid
, lcu_inv_lines_vl.line_context
, lcu_inv_lines_vl.line_attribute1
, lcu_inv_lines_vl.line_attribute2
, l_batch_source
, l_set_of_books_id
, lcu_inv_lines_vl.line_type
, lcu_inv_lines_vl.description
, lcu_inv_lines_vl.currency_code
, lcu_inv_lines_vl.amount
, lcu_inv_lines_vl.cust_trx_type_name
, lcu_inv_lines_vl.term_name
, lcu_inv_lines_vl.bill_customer_ref
, lcu_inv_lines_vl.bill_address_ref
, lcu_inv_lines_vl.bill_contact_ref
, lcu_inv_lines_vl.ship_customer_ref
, lcu_inv_lines_vl.ship_address_ref
, 'User'
, lcu_inv_lines_vl.conversion_date
, lcu_inv_lines_vl.conversion_rate
, TO_DATE(lcu_inv_lines_vl.trx_date,'MM/DD/YYYY')
, TO_DATE(lcu_inv_lines_vl.trx_date,'MM/DD/YYYY')
, DECODE(l_trx_numbering_flag,'N',lcu_inv_lines_vl.trx_number,NULL)
, lcu_inv_lines_vl.line_number
, lcu_inv_lines_vl.created_by
, lcu_inv_lines_vl.creation_date
, lcu_inv_lines_vl.last_updated_by
, lcu_inv_lines_vl.last_update_date
, lcu_inv_lines_vl.last_update_login
, l_org_id
, 'IP'
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line records to pre interface table ');
--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 => 'Error while inserting Invoice Line records to pre interface table .'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => SQLERRM
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- RAISE g_rec_ins_exception;
END;
IF l_record_error_flag = 0 THEN
FOR lcu_valid_lines_dist_vl IN cur_inv_lines_dist_vl (lcu_inv_lines_vl.line_context
,lcu_inv_lines_vl.line_attribute1
,lcu_inv_lines_vl.line_attribute2
)
LOOP
BEGIN
INSERT INTO xx_open_inv_lines_dist_preint (
recid
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, account_class
, amount
, percent
, segment1
, segment2
, segment3
, segment4
, segment5
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
, status
)
VALUES (
lcu_valid_lines_dist_vl.recid
, lcu_valid_lines_dist_vl.line_context
, lcu_valid_lines_dist_vl.line_attribute1
, lcu_valid_lines_dist_vl.line_attribute2
, lcu_valid_lines_dist_vl.account_class
, lcu_valid_lines_dist_vl.amount
, lcu_valid_lines_dist_vl.percent
, lcu_valid_lines_dist_vl.segment1
, lcu_valid_lines_dist_vl.segment2
, lcu_valid_lines_dist_vl.segment3
, lcu_valid_lines_dist_vl.segment4
, lcu_valid_lines_dist_vl.segment5
, lcu_valid_lines_dist_vl.created_by
, lcu_valid_lines_dist_vl.creation_date
, lcu_valid_lines_dist_vl.last_updated_by
, lcu_valid_lines_dist_vl.last_update_date
, lcu_valid_lines_dist_vl.last_update_login
, l_org_id
, 'IP'
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice distribution records to pre interface table ');
--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 => 'Error while inserting Invoice distribution records to pre interface table .'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- RAISE g_rec_ins_exception;
END;
--Updating record in staging table as ER if error else PR if successfully validated
BEGIN
UPDATE xx_open_inv_lines_dist_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_valid_lines_dist_vl.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
END LOOP;
END IF;
--Updating record in staging table as ER if error else PR if successfully validated
BEGIN
UPDATE xx_open_inv_lines_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_inv_lines_vl.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
l_error_msg := NULL ;
END LOOP;
END ;
-- Validating the data in pre interface tables
BEGIN
FOR lcu_inv_lines_preint IN cur_inv_lines_preint
LOOP
l_rec_id:=lcu_inv_lines_preint.recid;
l_record_error_flag := 0;
-------- Validate TERM_NAME --------
BEGIN
SELECT term_id
INTO l_orig_term_id
FROM ra_terms
WHERE name = lcu_inv_lines_preint.term_name
AND TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE))
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag :=1;
xx_trace.l ('Term Name not defined ');
--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 => 'Term Name not defined.'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.term_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-------- Validate currencu code --------
BEGIN
SELECT fc.currency_code
INTO l_currency_code
FROM fnd_currencies fc
WHERE currency_flag = 'Y'
AND enabled_flag = 'Y'
AND nvl(fc.end_date_active,sysdate)>= sysdate
AND fc.currency_code = lcu_inv_lines_preint.currency_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Currency Code not defined'||'-'||lcu_inv_lines_preint.currency_code);
l_record_error_flag := 1;
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 => 'Currency Code not defined'||'-'||lcu_inv_lines_preint.currency_code
, p_identifier3 => l_rec_id
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_error_msg := SQLERRM||'-'||lcu_inv_lines_preint.currency_code;
l_record_error_flag := 1;
xx_trace.l(l_error_msg);
END;
-------- Validate transaction number --------
BEGIN
SELECT trx_number
INTO l_trx_number
FROM ra_customer_trx_v
WHERE trx_number = lcu_inv_lines_preint.trx_number;
IF l_trx_number IS NOT NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Invoice Number already exists in Oracle - '||lcu_inv_lines_preint.trx_number);
--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 => 'Invoice Number already exists in Oracle - '||lcu_inv_lines_preint.trx_number
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.trx_number );
--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 => SQLERRM||'-'||lcu_inv_lines_preint.trx_number
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-------- validate Interface line concext --------
BEGIN
SELECT descriptive_flex_context_name
INTO l_flex_context_name
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flex_context_name =lcu_inv_lines_preint.interface_line_context
AND application_id = 222
AND enabled_flag ='Y' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Interface line context is not defined'||' '||lcu_inv_lines_preint.interface_line_context);
--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 => 'Interface line context is not defined'||' '||lcu_inv_lines_preint.interface_line_context
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.interface_line_context);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.interface_line_context
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- Check whether combination of line lontext and line attributes are already existing or not
BEGIN
SELECT COUNT(1)
INTO l_line_context_count
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE interface_line_context||'-'||
interface_line_attribute1||'-'||
interface_line_attribute2 = lcu_inv_lines_preint.interface_line_context
||'-'||lcu_inv_lines_preint.interface_line_attribute1
||'-'||lcu_inv_lines_preint.interface_line_attribute2
AND org_id =l_org_id ;
IF l_line_context_count >= 1 THEN
l_record_error_flag := 1;
xx_trace.l ('line context, Order ID Legacy Line ID combination IS already existing');
--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 => 'line context, Order ID Legacy Line ID combination IS already existing'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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 OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validating customer transaction type
BEGIN
SELECT NAME
INTO l_cust_trx_type
FROM ra_cust_trx_types
WHERE name = lcu_inv_lines_preint.cust_trx_type_name
AND org_id =l_org_id ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('customer transaction type is not defined'||' '||lcu_inv_lines_preint.cust_trx_type_name);
--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 => 'customer transaction type is not defined'||' '||lcu_inv_lines_preint.cust_trx_type_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.cust_trx_type_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.cust_trx_type_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
--validate term name
BEGIN
SELECT NAME
INTO l_term_name
FROM ra_terms_vl
WHERE NAME =lcu_inv_lines_preint.term_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Term Name is not defined'||' '||lcu_inv_lines_preint.term_name);
--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 => 'Term Name is not defined'||' '||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.term_name);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.term_name
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validating line type
IF (lcu_inv_lines_preint.line_type <>'LINE') THEN
l_record_error_flag := 1;
xx_trace.l ('Line type is not defined'||' '||lcu_inv_lines_preint.line_type);
--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 => 'Line type is not defined'||' '||lcu_inv_lines_preint.line_type
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-------- GET Bill to Customer ID --------
BEGIN
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
WHERE orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to customer id not found');
--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 => 'Bill to customer id not found'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_customer_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_customer_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- validate bill to address reference
BEGIN
SELECT AA.address_id
INTO l_adress_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='BILL_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_address_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref);
--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 => 'Bill to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validate bill to contact reference
IF lcu_inv_lines_preint.orig_system_bill_contact_ref IS NOT NULL THEN
BEGIN
SELECT AN.contact_id
INTO l_contact_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS,
AR_CONTACTS_V AN,
AR_CONTACT_ROLES_V ACR
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='BILL_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_address_ref
AND AN.orig_system_reference = lcu_inv_lines_preint.orig_system_bill_contact_ref
AND AC.customer_id= AN.customer_id
AND AA.address_id = AN.address_id
AND AN.contact_id=ACR.contact_id
AND ACR.primary_flag ='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Bill to Contact Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref);
--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 => 'Bill to Contact Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_bill_contact_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF ;
-- To validate ship to customer reference
IF lcu_inv_lines_preint.orig_system_ship_customer_ref IS NOT NULL THEN
BEGIN
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
WHERE orig_system_reference = lcu_inv_lines_preint.orig_system_ship_customer_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Ship to customer id not found');
--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 => 'Ship to customer id not found'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_customer_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_customer_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
IF lcu_inv_lines_preint.orig_system_ship_address_ref IS NOT NULL THEN
-- validate bill to address reference
BEGIN
SELECT AA.address_id
INTO l_adress_id
FROM
ar_customers_v AC,
ar_addresses_v AA,
HZ_SITE_USES_V HS
WHERE AC.customer_id = AA.customer_id
AND AA.address_id = HS.address_id
AND HS.site_use_code='SHIP_TO'
AND AC.orig_system_reference = lcu_inv_lines_preint.orig_system_ship_customer_ref
AND AA.orig_system_reference = lcu_inv_lines_preint.orig_system_ship_address_ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_error_flag := 1;
xx_trace.l ('Ship to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref);
--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 => 'Ship to Address Ref is not valid'||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref);
--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 => SQLERRM||'-'||lcu_inv_lines_preint.orig_system_ship_address_ref
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
END IF ;
-- update pre interface table
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = DECODE(l_record_error_flag,1,'ER','IP')
WHERE recid = lcu_inv_lines_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--RAISE g_rec_ins_exception;
END;
l_error_msg :=NULL ;
IF l_record_error_flag = 0 THEN
FOR lcu_lines_dist_preint IN cur_inv_lines_dist_preint (lcu_inv_lines_preint.interface_line_context
,lcu_inv_lines_preint.interface_line_attribute1
,lcu_inv_lines_preint.interface_line_attribute2
)
LOOP
l_rec_id:=lcu_lines_dist_preint.recid;
l_conc_seg_cnt:=0;
---- validating GL segments code combination----------------
BEGIN
SELECT COUNT(1)
INTO l_conc_seg_cnt
FROM gl_code_combinations_kfv
where concatenated_segments = lcu_lines_dist_preint.segment1
||'-'||lcu_lines_dist_preint.segment2
||'-'||lcu_lines_dist_preint.segment3
||'-'||lcu_lines_dist_preint.segment4
||'-'||lcu_lines_dist_preint.segment5;
IF l_conc_seg_cnt = 0 THEN
l_record_error_flag := 1;
xx_trace.l ('This is not valid code combination for gl segments');
--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 => 'This is not valid code combination for gl segments'
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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 OTHERS THEN
l_record_error_flag :=1;
xx_trace.l (SQLERRM);
--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 => SQLERRM
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END ;
-- to validate account class
BEGIN
IF ( lcu_lines_dist_preint.account_class !='REV'
-- OR lcu_lines_dist_preint.account_class !='FREIGHT' OR
-- lcu_lines_dist_preint.account_class !='TAX' OR
-- lcu_lines_dist_preint.account_class !='REC' OR
-- lcu_lines_dist_preint.account_class !='CHARGES' OR
-- lcu_lines_dist_preint.account_class !='UNEARN' OR
-- lcu_lines_dist_preint.account_class !='UNBILL'
) THEN
l_record_error_flag := 1;
xx_trace.l ('This is not valid account class'||'-'||lcu_lines_dist_preint.account_class);
--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 => 'This is not valid account class'||'-'||lcu_lines_dist_preint.account_class
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--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;
IF l_record_error_flag = 1 THEN
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = 'ER'
WHERE recid = lcu_lines_dist_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
l_record_error_flag :=1;
--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 => l_error_msg
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
BEGIN
UPDATE xx_open_inv_lines_dist_preint
SET status = DECODE(l_record_error_flag,1,'ER','IP')
WHERE recid = lcu_lines_dist_preint.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
l_record_error_flag :=1;
--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 => l_error_msg
, p_identifier3 => l_rec_id
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END LOOP ;
END IF ;
END LOOP;
END ;
END validate_records;
PROCEDURE process_records ( l_processed_inv_line_rec_cnt OUT NOCOPY PLS_INTEGER
, l_processed_line_dis_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER)
/* ***********************************************************************************************************
* Procedure Name : process_records *
* Description : Process validated Invoice records *
* Called From : Concurrent Program *
* *
* Parameters Type Description *
* l_processed_inv_line_rec_cnt OUT To stote total processed invoice lines records *
* l_processed_line_dis_rec_cnt OUT To stote total processed invoice line distribution records *
* *
* Tables Accessed *
* ----------------- *
* xx_open_inv_lines_preint : S,I,U *
* xx_open_inv_lines_dist_preint : S,I,U *
* ra_interface_lines_all : I,U *
* ra_interface_distributions_all : I,U *
* *
* *
* *
* Change History *
* ----------------- *
* Version Date Author Description *
* --------- ------------- --------------- -------------------------- *
* Draft 1A 19-DEC-2006 Pradeep Kumar Original Code *
************************************************************************************************************* */
IS
l_record_error_flag NUMBER ;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID ;
int_date DATE ;
--Cursor to fetch Invoice Line information from the staging table
CURSOR cur_valid_inv_lines IS
SELECT *
FROM xx_open_inv_lines_preint
WHERE status = 'IP'
;
--Cursor to fetch Invoice Line distribution information from the staging table
CURSOR cur_valid_inv_lines_dist (p_line_context IN VARCHAR2
,p_line_attribute1 IN VARCHAR2
,p_line_attribute2 IN VARCHAR2
)
IS
SELECT *
FROM xx_open_inv_lines_dist_preint
WHERE status ='IP'
AND interface_line_context = p_line_context
AND interface_line_attribute1 = p_line_attribute1
AND interface_line_attribute2 = p_line_attribute2 ;
BEGIN
l_processed_inv_line_rec_cnt := 0;
l_processed_line_dis_rec_cnt := 0;
FOR lcu_valid_inv_lines IN cur_valid_inv_lines
LOOP
l_record_error_flag := 0;
BEGIN
INSERT INTO ra_interface_lines_all(
interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, batch_source_name
, set_of_books_id
, line_type
, description
, currency_code
, amount
, cust_trx_type_name
, term_name
, orig_system_bill_customer_ref
, orig_system_bill_address_ref
, orig_system_bill_contact_ref
, orig_system_ship_customer_ref
, orig_system_ship_address_ref
, conversion_type
, conversion_date
, conversion_rate
, trx_date
, gl_date
, trx_number
, line_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
)
VALUES (
lcu_valid_inv_lines.interface_line_context
, lcu_valid_inv_lines.interface_line_attribute1
, lcu_valid_inv_lines.interface_line_attribute2
, l_batch_source
, l_set_of_books_id
, lcu_valid_inv_lines.line_type
, lcu_valid_inv_lines.description
, lcu_valid_inv_lines.currency_code
, lcu_valid_inv_lines.amount
, lcu_valid_inv_lines.cust_trx_type_name
, lcu_valid_inv_lines.term_name
, lcu_valid_inv_lines.orig_system_bill_customer_ref
, lcu_valid_inv_lines.orig_system_bill_address_ref
, lcu_valid_inv_lines.orig_system_bill_contact_ref
, lcu_valid_inv_lines.orig_system_ship_customer_ref
, lcu_valid_inv_lines.orig_system_ship_address_ref
, 'User'
, TO_DATE(lcu_valid_inv_lines.conversion_date,'YYYY/MM/DD')
, lcu_valid_inv_lines.conversion_rate
, lcu_valid_inv_lines.trx_date
, lcu_valid_inv_lines.trx_date
, lcu_valid_inv_lines.trx_number
, lcu_valid_inv_lines.line_number
, lcu_valid_inv_lines.created_by
, lcu_valid_inv_lines.creation_date
, lcu_valid_inv_lines.last_updated_by
, lcu_valid_inv_lines.last_update_date
, lcu_valid_inv_lines.last_update_login
, lcu_valid_inv_lines.org_id
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line records');
--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 => 'Error while inserting Invoice Line records'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF l_record_error_flag = 0 THEN
FOR lcu_valid_lines_dist IN cur_valid_inv_lines_dist (lcu_valid_inv_lines.interface_line_context
,lcu_valid_inv_lines.interface_line_attribute1
,lcu_valid_inv_lines.interface_line_attribute2
)
LOOP
BEGIN
INSERT INTO ra_interface_distributions_all (
interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, account_class
, amount
, percent
, segment1
, segment2
, segment3
, segment4
, segment5
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
)
VALUES (
lcu_valid_lines_dist.interface_line_context
, lcu_valid_lines_dist.interface_line_attribute1
, lcu_valid_lines_dist.interface_line_attribute2
, lcu_valid_lines_dist.account_class
, lcu_valid_lines_dist.amount
, lcu_valid_lines_dist.percent
, lcu_valid_lines_dist.segment1
, lcu_valid_lines_dist.segment2
, lcu_valid_lines_dist.segment3
, lcu_valid_lines_dist.segment4
, lcu_valid_lines_dist.segment5
, lcu_valid_lines_dist.created_by
, lcu_valid_lines_dist.creation_date
, lcu_valid_lines_dist.last_updated_by
, lcu_valid_lines_dist.last_update_date
, lcu_valid_lines_dist.last_update_login
, lcu_valid_lines_dist.org_id
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting Invoice Line distribution records ');
--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 => 'Error while inserting Invoice Line distribution records '
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--Updating invoice distribution records in staging table as E if error in site processing else P
IF l_record_error_flag = 0 THEN
l_processed_line_dis_rec_cnt := l_processed_line_dis_rec_cnt + 1 ;
BEGIN
UPDATE xx_open_inv_lines_dist_preint
SET status = 'PR'
WHERE recid =lcu_valid_lines_dist.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
END IF;
END LOOP;
END IF;
--Updating invoice lines records in staging table as E if error in site processing else P
IF l_record_error_flag = 0 THEN
l_processed_inv_line_rec_cnt := l_processed_inv_line_rec_cnt + 1 ;
BEGIN
UPDATE xx_open_inv_lines_preint
SET status = DECODE(l_record_error_flag,1,'ER','PR')
WHERE recid =lcu_valid_inv_lines.recid
;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
xx_trace.l(l_error_msg);
RAISE g_rec_ins_exception;
END;
END IF ;
END LOOP;
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN
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_processed_inv_line_rec_cnt > 0 THEN
-- This concurrent program is used for creating Invoice Transactions
l_standard_request_id := fnd_request.submit_request
(
application => 'AR'
,program => 'RAXMTR'
,description => ''
,start_time => ''
,sub_request => FALSE
,argument1 => '1' -- number of instances
,argument2 => l_batch_source_id -- batch source id for batch source "LEGACY"
,argument3 => '' -- batch source name
,argument4 => sysdate -- default date
,argument5 => '' -- transaction flexfield
,argument6 => '' -- transaction type
,argument7 => '' -- bill to customer number (low)
,argument8 => '' -- bill to customer number (high)
,argument9 => '' -- bill to customer name (low)
,argument10 => '' -- bill to customer name (high)
,argument11 => '' -- gl date(low)
,argument12 => '' -- gl date (high)
,argument13 => '' -- ship date(low)
,argument14 => '' -- ship date(high)
,argument15 => '' -- transaction number(low)
,argument16 => '' -- transaction number(high)
,argument17 => '' -- sales order number (low)
,argument18 => '' -- sales order number (high)
,argument19 => '' -- invoice date (low)
,argument20 => '' -- invoice date (high)
,argument21 => '' -- ship to customer number (low)
,argument22 => '' -- ship to customer number (high)
,argument23 => '' -- ship to customer name (low)
,argument24 => '' -- ship to customer name (high)
,argument25 => 'Y' -- base due date on trx date
,argument26 => '' -- due date adjustment date
,argument27 => l_org_id -- org id
);
IF l_standard_request_id > 0 THEN
COMMIT;
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-00004'
, p_identifier2 => 'Error in submitting concurrent request'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
l_phase := NULL ;
l_vstatus := NULL ;
l_dev_phase := NULL ;
l_dev_status := NULL ;
l_message := NULL ;
--Wait for the completion of the concurrent request (if submitted successfully)
l_completed := fnd_concurrent.wait_for_request
(request_id => l_standard_request_id
, INTERVAL => 60
, 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-'|| l_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-00004'
, p_identifier2 => 'No records To Process in Interface Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END;
END ;
END;
/
SHOW ERROR;
EXIT;
No comments :
Post a Comment