Oracle Applications & Oracle Cloud Fusion HCM : GL

CREATE OR REPLACE PACKAGE xx_gl_je_cnv_pkb AUTHID CURRENT_USER AS
--Version Draft 1a
/******************************************************************************************************************
* Package Name : xx_gl_je_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the GL JE data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create the journal information in base tables. *
* *
* *
* 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_gl_je_cnv : S,I,U,D
* xx_gl_int_preint : S,I,U *
* gl_interface : I,U *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *

* Draft 1A 18-Dec-2015 Madhu Dhare Original Code *
********************************************************************************************************************/

/*-----------------------------------------------------------
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_gl_je_cnv_pkb;
/
SHOW ERROR;
=======================================================================================================================
CREATE OR REPLACE PACKAGE BODY xx_gl_je_cnv_pkb AS
--Version Draft 1a
/*****************************************************************************************************************
* Package Name : xx_gl_je_cnv_pkb *
* Purpose : This program checks for pre-requisites, validates the GL JE data in the staging table. If *
* all the records are through the validations, then populate the standard interface *
* to create the journal information in base tables. *
* *
* 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_gl_je_cnv : S,I,U,D
* xx_gl_je_cont_rec_cnv : S,I,U,D
* xx_gl_int_preint : S,I,U *
* gl_interface : I,U *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 18-Dec-2015 Madhu Dhare Original Code *
**********************************************************************************************************************/
l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
l_batch_source gl_je_sources_tl.je_source_name%TYPE := 'Transfer' ;
l_je_category gl_je_categories_tl.user_je_category_name%TYPE := 'Transfer' ;

--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 ;
l_error_recs NUMBER;

PROCEDURE validate_setups(l_header_id IN NUMBER) ;

PROCEDURE validate_records ( l_total_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_gl_je_pre_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_gl_je_pre_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_gl_je_pre_rec_cnt OUT NOCOPY PLS_INTEGER
, l_header_id IN NUMBER
, p_run_mode IN VARCHAR2
);

PROCEDURE process_records ( l_processed_gl_je_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_gl_je_cnv : S,I,U,D *
* xx_gl_int_preint : S,I,U,D *
* gl_interface : S,I,U *
* *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 18-Dec-2015 Original Code *
*******************************************************************************************************************/
IS

l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_GL_JE_CNV_PKB';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name
|| '.'
|| 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'GL_CNV_01';
--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_gl_je_rec_cnt PLS_INTEGER ;
l_valid_gl_je_rec_cnt PLS_INTEGER ;
l_error_gl_je_rec_cnt PLS_INTEGER ;
l_total_gl_je_pre_rec_cnt PLS_INTEGER ;
l_valid_gl_je_pre_rec_cnt PLS_INTEGER ;
l_error_gl_je_pre_rec_cnt PLS_INTEGER ;
l_processed_gl_je_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 := 'Line 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_gl_je_rec_cnt
, l_valid_gl_je_rec_cnt
, l_error_gl_je_rec_cnt
, l_total_gl_je_pre_rec_cnt
, l_valid_gl_je_pre_rec_cnt
, l_error_gl_je_pre_rec_cnt
, l_header_id
, p_run_mode
);

process_records ( l_processed_gl_je_rec_cnt
, l_header_id
);

BEGIN
DELETE xx_gl_int_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_total_gl_je_rec_cnt
, p_successful_recs => l_valid_gl_je_rec_cnt
, p_error_recs => l_error_gl_je_rec_cnt
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;

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_total_gl_je_pre_rec_cnt
, p_successful_recs => l_processed_gl_je_rec_cnt
, p_error_recs => l_error_gl_je_pre_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 := 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_total_gl_je_rec_cnt
, p_successful_recs => l_valid_gl_je_rec_cnt
, p_error_recs => l_error_gl_je_rec_cnt
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;

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_total_gl_je_pre_rec_cnt
, p_successful_recs => l_processed_gl_je_rec_cnt
, p_error_recs => l_error_gl_je_pre_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
--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 *
* *
* Parameters Type Description *
* ----------------------------------------------------------------------------- *
* NONE *
* *
* Tables Accessed *
* ----------------- *
* *
* ----------------- *
* Change History *
* *
* Ver Date Author CR/BUG # Description *
* ------ ----------- ----------------- --------------- --------------------------- *
* Draft 1A 18-Dec-2015 Madhu Dhare Original Code *
* *
********************************************************************************************************************/

l_set_of_book_name gl_sets_of_books.name%TYPE:='Vision Operations (USA)' ;
-- 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 JE BATCH_SOURCE --------
BEGIN
SELECT je_source_name
INTO l_batch_source
FROM gl_je_sources_tl
WHERE je_source_name = l_batch_source
;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('JE 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 => 'JE 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;

-------- Validate JE CATEGORIES --------
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_je_category
FROM gl_je_categories_tl
WHERE USER_JE_CATEGORY_NAME = l_je_category
;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_flag := 'Y';
xx_trace.l ('JE Category Name 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 => 'JE JE Category Name 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_je_category);
--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_je_category
, 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_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER
, l_total_gl_je_pre_rec_cnt OUT NOCOPY PLS_INTEGER
, l_valid_gl_je_pre_rec_cnt OUT NOCOPY PLS_INTEGER
, l_error_gl_je_pre_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_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_valid_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER *
* l_error_gl_je_rec_cnt OUT NOCOPY PLS_INTEGER *
* *
* Tables Accessed *
* ----------------- *
* xx_gl_je_cnv : S,I,U,D *
* xx_gl_je_cont_rec_cnv : S,I,U,D *
* xx_gl_int_preint : S,I,U *
* gl_interface : I,U *
* *
* *
*Change History *
* *
*Ver Date Author CR/BUG # Description *
*------ ----------- ----------------- --------------- --------------------------- *
*Draft 1A 18-Dec-2015 Madhu Dhare Original Code *
*******************************************************************************************************************/
IS

l_conc_seg_cnt NUMBER := 0;
l_record_error_flag NUMBER ;
l_rec_id VARCHAR2(50) := NULL;
l_sum_rec NUMBER;
l_rec_count NUMBER;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID ;
l_currency_code fnd_currencies.currency_code%TYPE ;
l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
l_process_status NUMBER := 0;
l_date DATE ;
l_error_message VARCHAR2(500):= NULL ;
l_first_day gl_period_statuses.start_date%TYPE ;
l_last_day gl_period_statuses.end_date%TYPE ;
l_closing_status VARCHAR2(20);
l_ccid_ext VARCHAR2(20) := NULL;
l_ccid NUMBER := NULL;
l_period_name gl_period_statuses.period_name%TYPE ;

--Cursor to fetch GL JE information from the staging table
CURSOR cur_gl_je IS
SELECT *
FROM xx_gl_je_cnv
WHERE status ='NW';

CURSOR cur_gl_je_vl IS
SELECT *
FROM xx_gl_je_cnv
WHERE status ='NW';

CURSOR cur_gl_je_preint IS
SELECT *
FROM xx_gl_int_preint
WHERE status ='IP';

BEGIN
l_total_gl_je_rec_cnt := 0;
l_valid_gl_je_rec_cnt := 0;
l_error_gl_je_rec_cnt := 0;

-- To get the line count from staging table

BEGIN
SELECT COUNT(1)
INTO l_sum_rec
FROM xx_gl_je_cnv
WHERE status ='NW'
;
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_gl_je_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 AND p_run_mode = 'F' THEN
l_record_error_flag := 1;
xx_trace.l ('Gl JE record 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 => 'Gl JE record 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_gl_je_cnv
SET status = 'ER' ;

UPDATE xx_gl_je_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_gl_je IN cur_gl_je
LOOP
l_record_error_flag := 0;
l_total_gl_je_rec_cnt := l_total_gl_je_rec_cnt + 1 ;

l_rec_id:= lcu_gl_je.recid ;

-- 1 Check if mandatory field Actual Flag has value
IF lcu_gl_je.actual_flag IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Actual Flag 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 => 'Actual Flag can not be null.'
, 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
);
END IF;

-- 1 Check if mandatory field transaction_date has value
IF lcu_gl_je.transaction_date IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Transaction Date 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 => 'Creation Date can not be null.'
, 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
);
END IF;

-- 1 Check if mandatory field GL segments has value
IF lcu_gl_je.segment1 IS NULL
OR lcu_gl_je.segment2 IS NULL
OR lcu_gl_je.segment3 IS NULL
OR lcu_gl_je.segment4 IS NULL
OR lcu_gl_je.segment5 IS NULL THEN

l_record_error_flag := 1;
xx_trace.l ('Either of the GL segments 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 => 'Either of the GL segments can not be null.'
, p_identifier3 => l_rec_id
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more informa
--p_identifier8 => NULL -- Can be utilized for displaying more informa
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

-- 1 Check if mandatory field Currency Conversion Type has value
IF lcu_gl_je.user_curr_conv_type IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Currency Conversion 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 => 'Currency Conversion Type can not be null.'
, 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
);
END IF;

-- 1 Check if mandatory field set_of_books_id has value
IF lcu_gl_je.set_of_books_id IS NULL THEN
l_record_error_flag := 1;
xx_trace.l ('Set of books_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 => 'Set of books id can not be null.'
, 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
);
END IF;

-- 1 Check if mandatory field currency_code has value
IF lcu_gl_je.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 => 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 transaction record in staging table as ER if error
BEGIN
UPDATE xx_gl_je_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_gl_je.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_gl_je_rec_cnt := l_error_gl_je_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_gl_je_rec_cnt := l_valid_gl_je_rec_cnt + 1;
END IF;
END LOOP;
-- END IF;
-- END IF;

-- inserting data into pre interface tables
BEGIN
FOR lcu_gl_je_vl IN cur_gl_je_vl
LOOP
BEGIN
INSERT INTO xx_gl_int_preint(
status
, set_of_books_id
, accounting_date
, currency_code
, recid
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, actual_flag
, user_je_category_name
, user_je_source_name
, currency_conversion_date
, user_currency_conversion_type
, currency_conversion_rate
, segment1
, segment2
, segment3
, segment4
, segment5
, entered_dr
, entered_cr
, accounted_dr
, accounted_cr
-- , transaction_date
, reference1
, reference2
, reference3
, reference4
, reference5
, reference6
, reference7
, reference8
, reference9
, reference10
)
VALUES (
'IP'
, lcu_gl_je_vl.set_of_books_id
, TO_DATE(lcu_gl_je_vl.transaction_date,'MM/DD/YYYY')
, lcu_gl_je_vl.currency_code
, XX_GL_INT_PREINT_S.NEXTVAL
, lcu_gl_je_vl.last_update_date
, lcu_gl_je_vl.last_updated_by
, lcu_gl_je_vl.creation_date
, lcu_gl_je_vl.created_by
, lcu_gl_je_vl.last_update_login
, lcu_gl_je_vl.actual_flag
, l_je_category --lcu_gl_je_vl.user_je_category_name
, l_batch_source --lcu_gl_je_vl.user_je_source_name
, TO_DATE(lcu_gl_je_vl.currency_conv_date,'MM/DD/YYYY')
, lcu_gl_je_vl.user_curr_conv_type
, NULL
, lcu_gl_je_vl.segment1
, lcu_gl_je_vl.segment2
, lcu_gl_je_vl.segment3
, lcu_gl_je_vl.segment4
, lcu_gl_je_vl.segment5
, lcu_gl_je_vl.entered_dr
, lcu_gl_je_vl.entered_cr
, lcu_gl_je_vl.accounted_dr
, lcu_gl_je_vl.accounted_cr
-- , NULL --lcu_gl_je_vl.transaction_date
, lcu_gl_je_vl.reference1
, lcu_gl_je_vl.reference2
, lcu_gl_je_vl.reference3
, lcu_gl_je_vl.reference4
, lcu_gl_je_vl.reference5
, lcu_gl_je_vl.reference6
, lcu_gl_je_vl.reference7
, lcu_gl_je_vl.reference8
, lcu_gl_je_vl.reference9
, lcu_gl_je_vl.reference10
);
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 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;

--Updating record in staging table as ER if error
BEGIN
UPDATE xx_gl_je_cnv
SET status = DECODE(l_record_error_flag,1,'ER','NW')
WHERE recid = lcu_gl_je_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 ;
END IF;

-- Validating the data in pre interface tables
BEGIN
l_total_gl_je_pre_rec_cnt :=0;
l_valid_gl_je_pre_rec_cnt :=0;
l_error_gl_je_pre_rec_cnt :=0;

FOR lcu_gl_je_preint IN cur_gl_je_preint
LOOP

l_total_gl_je_pre_rec_cnt := l_total_gl_je_pre_rec_cnt + 1;

l_rec_id:=lcu_gl_je_preint.recid;
l_record_error_flag := 0;

-------- Validate actual flag --------
IF lcu_gl_je_preint.actual_flag <>'A'
-- OR lcu_gl_je_preint.actual_flag <>'B'
-- OR lcu_gl_je_preint.actual_flag <>'E'
THEN

l_record_error_flag :=1;
xx_trace.l ('Actual Flag is not correct'||'-'||lcu_gl_je_preint.actual_flag);
--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 => 'Actual Flag is not correct'||'-'||lcu_gl_je_preint.actual_flag
, 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;

-------- Validate currency 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_gl_je_preint.currency_code;

EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Currency Code not defined'||'-'||lcu_gl_je_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_gl_je_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_gl_je_preint.currency_code;
l_record_error_flag := 1;
xx_trace.l(l_error_msg);
END;

-------- Validate set of books ID --------
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM gl_sets_of_books
WHERE set_of_books_id = lcu_gl_je_preint.set_of_books_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Set of books ID not defined'||'-'||lcu_gl_je_preint.set_of_books_id);
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 => 'Set of books ID not defined'||'-'||lcu_gl_je_preint.set_of_books_id
, 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_record_error_flag :=1;
xx_trace.l (SQLERRM||'-'||lcu_gl_je_preint.set_of_books_id );
--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_gl_je_preint.set_of_books_id
, 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 currency converson type --------
IF lcu_gl_je_preint.user_currency_conversion_type <> 'Corporate'
-- OR lcu_gl_je_preint.user_currency_conversion_type <> 'User'
-- OR lcu_gl_je_preint.user_currency_conversion_type <> 'Spot'
THEN

l_record_error_flag := 1;
xx_trace.l ('Currency Conversion Type is not defined'||' '||lcu_gl_je_preint.user_currency_conversion_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 => 'Currency Conversion Type is not defined'||' '||lcu_gl_je_preint.user_currency_conversion_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;

-- Check whether period is open for the transaction date or not
BEGIN

SELECT period_name, closing_status
INTO l_period_name, l_closing_status
FROM gl_period_statuses
WHERE application_id = 101
AND TO_DATE(TO_CHAR(lcu_gl_je_preint.accounting_date,'MM/DD/YYYY'),'MM/DD/YYYY') BETWEEN start_date AND end_date
AND set_of_books_id = lcu_gl_je_preint.set_of_books_id;

IF l_closing_status <> 'O' THEN

l_record_error_flag := 1;
xx_trace.l ('Period'||'-'||l_period_name ||' '||'is not opened');
--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 => 'Period'||'-'||l_period_name ||' '||'is not opened'
, 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
l_record_error_flag := 1;
xx_trace.l ('Period'||'-'||l_period_name ||' '||'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 => 'Period'||'-'||l_period_name ||' '||'is 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);
--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
);

l_process_status :=0;
l_error_message := NULL ;
END ;

-- check CCID for the gl segments
BEGIN
l_ccid_ext:= xx_common_validations_pkg.get_coa_ccid (
p_segment1 =>lcu_gl_je_preint.segment1
,p_segment2 =>lcu_gl_je_preint.segment2
,p_segment3 =>lcu_gl_je_preint.segment3
,p_segment4 =>lcu_gl_je_preint.segment4
,p_segment5 =>lcu_gl_je_preint.segment5
-- ,p_segment6 =>
-- ,p_segment7 =>
,p_process_status => l_process_status
,p_error_message => l_error_message
,p_ccid => l_ccid
);
IF l_ccid_ext <> 'VALID' THEN
l_record_error_flag := 1;
xx_trace.l (l_error_message);
--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_message
, 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;
l_process_status :=0;
l_error_message := NULL ;
END ;
-- update pre interface table
BEGIN
UPDATE xx_gl_int_preint
SET status = DECODE(l_record_error_flag,1,'ER','IP')
WHERE recid = lcu_gl_je_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;
IF l_record_error_flag = 1 THEN
l_error_gl_je_pre_rec_cnt := l_error_gl_je_pre_rec_cnt + 1;
ELSIF l_record_error_flag = 0 THEN
l_valid_gl_je_pre_rec_cnt := l_valid_gl_je_pre_rec_cnt + 1;
END IF;

END LOOP;
END ;
END IF;
END validate_records;

PROCEDURE process_records ( l_processed_gl_je_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_gl_je_rec_cnt OUT To stote total processed invoice lines records *
* *
* Tables Accessed *
* ----------------- *
* xx_gl_je_cnv : S,I,U *
* xx_gl_je_cont_rec_cnv : S,I,U *
* xx_gl_int_preint : I,U *
* gl_interface : I,U *
* *
* *
* Change History *
* ----------------- *
* Version Date Author Description *
* --------- ------------- --------------- -------------------------- *
* Draft 1A 18-Dec-2015 Madhu Dhare Original Code *
************************************************************************************************************* */
IS
l_record_error_flag NUMBER;
l_org_id PLS_INTEGER := FND_GLOBAL.ORG_ID;
int_date DATE;
l_interface_run_id NUMBER;
l_gl_application_name CONSTANT VARCHAR2(30):= 'SQLGL';
l_journal_imp_prog_name CONSTANT VARCHAR2(30):= 'GLLEZL';

--Cursor to fetch Invoice Line information from the staging table
CURSOR cur_valid_gl_je IS
SELECT *
FROM xx_gl_int_preint
WHERE status = 'IP'
;

CURSOR c_gl_iface_ctl IS
SELECT gl_journal_import_s.NEXTVAL interface_run_id
FROM DUAL;

BEGIN
l_processed_gl_je_rec_cnt := 0;

FOR lcu_valid_gl_je IN cur_valid_gl_je
LOOP
l_record_error_flag := 0;

BEGIN
INSERT INTO gl_interface(
status
, set_of_books_id
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, user_je_category_name
, user_je_source_name
, currency_conversion_date
, encumbrance_type_id
, budget_version_id
, user_currency_conversion_type
, currency_conversion_rate
, segment1
, segment2
, segment3
, segment4
, segment5
, entered_dr
, entered_cr
, accounted_dr
, accounted_cr
, reference1
, reference2
, reference3
, reference4
, reference5
, reference6
, reference7
, reference8
, reference9
, reference10

)
VALUES (
'NEW'
, lcu_valid_gl_je.set_of_books_id
, lcu_valid_gl_je.accounting_date
, lcu_valid_gl_je.currency_code
, SYSDATE
, lcu_valid_gl_je.created_by
, lcu_valid_gl_je.actual_flag
, lcu_valid_gl_je.user_je_category_name
, lcu_valid_gl_je.user_je_source_name
, lcu_valid_gl_je.currency_conversion_date
, lcu_valid_gl_je.encumbrance_type_id
, lcu_valid_gl_je.budget_version_id
, lcu_valid_gl_je.user_currency_conversion_type
, NULL
, lcu_valid_gl_je.segment1
, lcu_valid_gl_je.segment2
, lcu_valid_gl_je.segment3
, lcu_valid_gl_je.segment4
, lcu_valid_gl_je.segment5
, lcu_valid_gl_je.entered_dr
, lcu_valid_gl_je.entered_cr
, lcu_valid_gl_je.accounted_dr
, lcu_valid_gl_je.accounted_cr
, lcu_valid_gl_je.reference1
, lcu_valid_gl_je.reference2
, lcu_valid_gl_je.reference3
, lcu_valid_gl_je.reference4
, lcu_valid_gl_je.reference5
, lcu_valid_gl_je.reference6
, lcu_valid_gl_je.reference7
, lcu_valid_gl_je.reference8
, lcu_valid_gl_je.reference9
, lcu_valid_gl_je.reference10
);
EXCEPTION
WHEN OTHERS THEN
l_record_error_flag :=1;
xx_trace.l ('Error while inserting records into gl_interface');
--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 records into gl_interface'
, 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 records in pre interface table as ER if error in site processing else PR
IF l_record_error_flag = 0 THEN
l_processed_gl_je_rec_cnt := l_processed_gl_je_rec_cnt + 1 ;
BEGIN
UPDATE xx_gl_int_preint
SET status = DECODE(l_record_error_flag,1,'ER','PR')
WHERE recid =lcu_valid_gl_je.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_gl_je_rec_cnt > 0 THEN

-- This concurrent program is used for creating GL JE Header and Lines

FOR lcu_gl_iface_ctl IN c_gl_iface_ctl LOOP
l_interface_run_id := lcu_gl_iface_ctl.interface_run_id;
END LOOP;

BEGIN
INSERT INTO gl_interface_control
(je_source_name
,status
,interface_run_id
,GROUP_ID
,set_of_books_id
,packet_id
,interface_table_name
,processed_table_code
,request_id
)
VALUES (l_batch_source -- JE_SOURCE_NAME
,'S' -- STATUS
,l_interface_run_id -- INTERFACE_RUN_ID
,NULL -- GROUP_ID
,l_set_of_books_id -- SET_OF_BOOKs_ID
,NULL -- PACKET_ID
,'GL_INTERFACE' -- INTERFACE_TABLE_NAME
,NULL -- PROCESSED_TABLE_CODE
,NULL -- REQUEST_ID
);

EXCEPTION
WHEN OTHERS THEN
xx_trace.l (SQLERRM);
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-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
);

END;

l_standard_request_id := fnd_request.submit_request
(
application => l_gl_application_name
,program => l_journal_imp_prog_name
,description => ''
,start_time => ''
,sub_request => FALSE
,argument1 => l_interface_run_id -- Interface Run ID
,argument2 => l_set_of_books_id -- Set of Books ID
,argument3 => 'N' -- Allow Suspence posting
,argument4 => NULL -- default date
,argument5 => NULL -- transaction flexfield
,argument6 => '' -- transaction type
,argument7 => '' -- bill to customer number (low)
,argument8 => CHR(0) -- 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;

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday, 8 January 2017

GL_INTERFACE

No comments :

Contact us