Master OracleApps: PO Conversion

PO Spec:
========================================================

create or replace PACKAGE XXSSPO_PO_CONV_PKG
IS
-- $Header$ 115.1.0
-- +==========================================================================================================================+
-- +==========================================================================================================================+

TYPE request_id_rec IS RECORD ( request_id NUMBER );

TYPE request_id_tbl
IS
TABLE OF request_id_rec
INDEX BY BINARY_INTEGER;

g_request_id_tbl request_id_tbl;

-- Define Global Constants
g_pkg_name CONSTANT VARCHAR2(30) := 'XXSSPO_PO_CONV_PKG';
gn_conc_error CONSTANT NUMBER := '2';
gn_conc_success CONSTANT NUMBER := '0';
gn_conc_warning CONSTANT NUMBER := '1';
gv_app_short_name CONSTANT fnd_application_vl.application_short_name%TYPE := 'PO';
gv_conc_prog_name CONSTANT fnd_concurrent_programs.concurrent_program_name%TYPE := 'POXPOPDOI';
g_entity CONSTANT VARCHAR2(10) := 'PO';
gv_wave_name CONSTANT VARCHAR2(240) := 'WAVE1';
gv_entity_name CONSTANT VARCHAR2(240) := 'Purchase Orders';
gv_header_tabname VARCHAR2 (30) := 'XXSSPO_PO_HEADERS_STG';
gv_lines_tabname VARCHAR2 (30) := 'XXSSPO_PO_LINES_STG';
gv_v VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_validated;
gv_ve VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_validation_error;
gv_i VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_moved_to_interface;
gv_ie VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_interface_error;
gv_p VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_imported;
gv_pe VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_import_error;
gv_np VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_not_processed;
gv_dr VARCHAR2(2) := xxssdm_fnd_common_pkg.gv_duplicate_record;

-- Define Global Variables

gv_debug_flag VARCHAR2(10); --Debug Flag
gn_child_req_id NUMBER; -- child program request id
gv_program_name VARCHAR2(200) := 'PO Conversion'; -- Variable to hold the program name
gn_conc_request_id NUMBER := fnd_global.conc_request_id; -- Variable used to hold the concurrent request id
gn_conc_program_id NUMBER := fnd_global.conc_program_id; -- Variable used to hold the concurrent program id
gn_login_id NUMBER := fnd_global.login_id; -- Variable used to hold the login id
gd_date DATE := SYSDATE;
gn_user_id VARCHAR2(50) := fnd_profile.VALUE('USER_ID');
gn_recon_req_id NUMBER;
gn_load_req_id NUMBER;
ln_batch_size NUMBER;
gv_hdr_ctl_file_name VARCHAR2(100);
gv_line_ctl_file_name VARCHAR2(100);
gv_ctl_file_path VARCHAR2(100);
gv_hdr_data_file_name VARCHAR2(100);
gv_line_data_file_name VARCHAR2(100);
gv_data_file_path VARCHAR2(100);
gv_arch_file_path VARCHAR2(100);
gn_data_set_id NUMBER;
gn_tl_hdr_loaded_records NUMBER := 0;
gn_tl_lines_loaded_records NUMBER := 0;
gn_tl_hdr_validation_err_cnt NUMBER := 0;
gn_tl_lines_validation_err_cnt NUMBER := 0;
gn_tl_hdr_validated_cnt NUMBER := 0;
gn_tl_lines_validated_cnt NUMBER := 0;
gn_dist_interfaced_cnt NUMBER := 0;
gn_lines_interfaced_cnt NUMBER := 0;
gn_hdr_interfaced_cnt NUMBER := 0;
gn_tl_hdr_duplicate_records NUMBER := 0;
gn_tl_lines_duplicate_records NUMBER := 0;
gn_tl_hdr_interf_err_cnt NUMBER := 0;
gn_tl_lines_interf_err_cnt NUMBER := 0;
gn_tl_dist_interf_err_cnt NUMBER := 0;
gn_tl_hdr_processed_cnt NUMBER := 0;
gn_tl_lines_processed_cnt NUMBER := 0;
gn_tl_dist_processed_cnt NUMBER := 0;
gn_tl_hdr_processing_err_cnt NUMBER := 0;
gn_tl_lines_processing_err_cnt NUMBER := 0;
gn_tl_dist_processing_err_cnt NUMBER := 0;
gn_tl_rcv_hdr_processed_cnt NUMBER := 0;
gn_tl_rcv_lines_processed_cnt NUMBER := 0;
gn_tl_rcv_hdr_interf_err_cnt NUMBER := 0;
gn_tl_rcv_lines_interf_err_cnt NUMBER := 0;
gn_tl_rcv_hdr_err_cnt NUMBER := 0;
gn_tl_rcv_lines_err_cnt NUMBER := 0;
gn_rcv_lines_interfaced_cnt NUMBER := 0;
gn_rcv_hdr_interfaced_cnt NUMBER := 0;

FUNCTION get_record_id RETURN NUMBER;
-- +===================================================================+
-- | Name: get_request_id |
-- | |
-- | Description: Function To get request ID |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | Returns : Request ID - NUMBER |
-- +===================================================================+
FUNCTION get_request_id RETURN NUMBER;
-- +===================================================================+
-- | Name: get_data_set_id |
-- | |
-- | Description: Function To get Data set ID |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | Returns : Data Set ID - NUMBER |
-- +===================================================================+
FUNCTION get_data_set_id RETURN NUMBER;

--+=================================================================================+
-- | Name: Main |
-- | |
-- | Description: This is a wrapper procedure that call all other |
-- | procedures |
-- | |
-- | |
-- | Parameters: pv_in_debug_flag - VARCHAR2 - Debug Flag |
-- | pv_in_run_mode - VARCHAR2 - Run Mode |
-- | pn_in_data_set_id - NUMBER - Data set id |
-- | pv_in_dummy - VARCHAR2 - Dummy parameter |
-- | pn_in_po_batch_size - NUMBER - Batch size |
-- | pv_in_hdr_ctl_file - VARCHAR2 - Header Control file name |
-- | pv__in_line_ctl_file - VARCHAR2 - Line Control file name |
-- | pv_ctl_file_path - VARCHAR2 - CTL file path |
-- | pv_in_hdr_data_file - VARCHAR2 - Header Data file |
-- | pv_in_line_data_file - VARCHAR2 - line Data file |
-- | pv_data_file_path - VARCHAR2 - Data file path |
-- | pv_arch_file_path - VARCHAR2 - Archival file path |
-- | |
-- | RETURNs : pv_out_ret_code - NUMBER - Return Code |
-- | pv_out_err_buf - VARCHAR2 - Error Buffer |
-- +================================================================================+
PROCEDURE main
(
pv_out_err_buf OUT VARCHAR2
,pv_out_ret_code OUT NUMBER
,pv_in_debug_flag IN VARCHAR2
,pv_in_run_mode IN VARCHAR2
,pn_in_data_set_id IN NUMBER
,pv_in_dummy1 IN VARCHAR2
,pv_in_dummy2 IN VARCHAR2
,pn_in_po_batch_size IN NUMBER
,pv_in_hdr_ctl_file IN VARCHAR2
,pv_in_line_ctl_file IN VARCHAR2
,pv_ctl_file_path IN VARCHAR2
,pv_in_hdr_data_file IN VARCHAR2
,pv_in_line_data_file IN VARCHAR2
,pv_data_file_path IN VARCHAR2
,pv_arch_file_path IN VARCHAR2
);
-- +===================================================================+
-- | Name: load_hdr_records_stg |
-- | |
-- | Description: Load records to header staging table FROM data file |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE load_hdr_records_stg
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT NUMBER
);

-- +===================================================================+
-- | Name: load_lines_records_stg |
-- | |
-- | Description: Load records to lines staging table FROM data file |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE load_lines_records_stg
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT NUMBER
);
-- +===================================================================+
-- | Name: Create_batch |
-- | |
-- | Description: Create batch procedure for creating batches |
-- | |
-- | |
-- | Parameters: pn_in_batch_size - NUMBER - Batch size |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE create_batch
(
pn_out_status OUT NUMBER
,pv_out_error_message OUT VARCHAR2
,pn_in_batch_size IN NUMBER
);
-- +====================================================================================================+
-- | Name: update_error |
-- | |
-- | Description: This procedure will update the staging tables with Status as |
-- | validation error and call the procedure to insert the error |
-- | details INTO the conversion error |
-- | table. |
-- | |
-- | Parameters: pv_in_error_message - VARCHAR2 - Error Message |
-- | pv_in_error_col_name - VARCHAR2 - Error Column Name |
-- | pv_in_error_col_value - VARCHAR2 - Error column value |
-- | pv_in_tabname - VARCHAR2 - Staging table name |
-- | pn_in_record_id - NUMBER - record ID |
-- | pv_in_po_number - VARCHAR2 - Po Number |
-- | pv_in_line_num - VARCHAR2 - Po Line Number |
-- | pv_in_status - VARCHAR2 - Input Status |
-- | |
-- | RETURNs : NONE |
-- | |
-- +====================================================================================================+
PROCEDURE update_error
(
pv_in_error_message IN VARCHAR2
,pv_in_error_col_name IN VARCHAR2
,pv_in_error_col_value IN VARCHAR2
,pv_in_tabname IN VARCHAR2
,pn_in_record_id IN NUMBER
,pv_in_po_number IN VARCHAR2
,pv_in_line_num IN VARCHAR2
,pv_in_status IN VARCHAR2
);

-- +===================================================================+
-- | Name: do_gen_validations |
-- | |
-- | Description: Do data validations on the Customer data |
-- | |
-- | |
-- | Parameters: pn_in_batch_id - NUMBER - record ID |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE do_gen_validations
(
pn_in_batch_id IN NUMBER
,pv_out_error_message OUT VARCHAR2
,pn_out_status OUT NUMBER
);
-- +===================================================================+
-- | Name: validate_data |
-- | |
-- | Description: Validation of the records. |
-- | This is a wrapper procedure to call other validation |
-- | procedure. |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE validate_data
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT NUMBER
);
-- +===================================================================+
-- | Name: Transfer_record |
-- | |
-- | Description: This procedure will load the Purchase order data |
-- | FROM staging table to interface tables and |
-- | FROM interface table to base tables using |
-- | import program. |
-- | |
-- | |
-- | |
-- | Parameters: pn_in_batch_id - NUMBER - Batch number |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +====================================================================+
PROCEDURE Transfer_record
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT NUMBER );

-- +============================================================================+
-- | Name: generate_report |
-- | |
-- | Description: This procedure will write report to output file |
-- | |
-- | Parameters: None |
-- | |
-- | Returns: pv_out_error_message - VARCHAR2 - Error Buffer |
-- | pn_out_status - VARCHAR2 - Return code of Error |
-- +============================================================================+

PROCEDURE generate_report
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT VARCHAR2
);

-- +====================================================================+
-- | Name: submit_recon_report |
-- | |
-- | Description: Procedure to submit recon report |
-- | |
-- | Parameters: pv_in_application_top - VARCHAR2 |
-- | pv_in_request_short_name - VARCHAR2 |
-- | pv_in_request_description - VARCHAR2 |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +====================================================================+

PROCEDURE submit_recon_report
(
pv_out_error_message OUT VARCHAR2
,pn_out_status OUT VARCHAR2
);

END XXSSPO_PO_CONV_PKG;

=================================================

PO Body:
=================================================

create or replace PACKAGE BODY XXSSPO_PO_CONV_PKG
IS
-- $Header$ 115.1.0
-- +==========================================================================================================================+
-- +==========================================================================================================================+
--+=================================================================================+
-- | Name: Main |
-- | |
-- | Description: This is a wrapper procedure that call all other |
-- | procedures |
-- | |
-- | |
-- | Parameters: pv_in_debug_flag - VARCHAR2 - Debug Flag |
-- | pv_in_run_mode - VARCHAR2 - Run Mode |
-- | pv_in_dummy1 - VARCHAR2 - Dummy parameter 1 |
-- | pn_in_data_set_id - NUMBER - Data set id |
-- | pv_in_dummy2 - VARCHAR2 - Dummy parameter 2 |
-- | pn_in_po_batch_size - NUMBER - Batch size |
-- | pv_in_hdr_ctl_file - VARCHAR2 - Header Control file name |
-- | pv_in_line_ctl_file - VARCHAR2 - Line Control file name |
-- | pv_ctl_file_path - VARCHAR2 - CTL file path |
-- | pv_in_hdr_data_file - VARCHAR2 - Header Data file |
-- | pv_in_line_data_file - VARCHAR2 - line Data file |
-- | pv_data_file_path - VARCHAR2 - Data file path |
-- | pv_arch_file_path - VARCHAR2 - Archival file path |
-- | |
-- | RETURNs : pv_out_ret_code - NUMBER - Return Code |
-- | pv_out_err_buf - VARCHAR2 - Error Buffer |
-- +================================================================================+
PROCEDURE main(
pv_out_err_buf OUT VARCHAR2 ,
pv_out_ret_code OUT NUMBER ,
pv_in_debug_flag IN VARCHAR2 ,
pv_in_run_mode IN VARCHAR2 ,
pn_in_data_set_id IN NUMBER ,
pv_in_dummy1 IN VARCHAR2 ,
pv_in_dummy2 IN VARCHAR2 ,
pn_in_po_batch_size IN NUMBER ,
pv_in_hdr_ctl_file IN VARCHAR2 ,
pv_in_line_ctl_file IN VARCHAR2 ,
pv_ctl_file_path IN VARCHAR2 ,
pv_in_hdr_data_file IN VARCHAR2 ,
pv_in_line_data_file IN VARCHAR2 ,
pv_data_file_path IN VARCHAR2 ,
pv_arch_file_path IN VARCHAR2 )
IS
-- Error variables
lv_err_buf VARCHAR2 (2000);
ln_ret_code NUMBER;
-- Count variables
ln_line_count NUMBER :=0;
ln_check_btch_id NUMBER;
-- Variable to hold exception
lexc_exit_program EXCEPTION;
lexc_exception EXCEPTION;
ln_status NUMBER;
ln_headers_count NUMBER;
ln_lines_count NUMBER;
ln_batch_size NUMBER;
lv_procedure_name VARCHAR2(50) := NULL;
BEGIN
lv_err_buf := NULL;
ln_ret_code := gn_conc_success;
gv_debug_flag := pv_in_debug_flag;
gn_data_set_id := pn_in_data_set_id;
gv_header_tabname := 'XXSSPO_PO_HEADERS_STG';
gv_lines_tabname := 'XXSSPO_PO_LINES_STG';
ln_batch_size := pn_in_po_batch_size;
-- load program parameters
gv_hdr_ctl_file_name := pv_in_hdr_ctl_file;
gv_line_ctl_file_name := pv_in_line_ctl_file;
gv_ctl_file_path := pv_ctl_file_path;
gv_hdr_data_file_name := pv_in_hdr_data_file;
gv_line_data_file_name := pv_in_line_data_file;
gv_data_file_path := pv_data_file_path;
gv_arch_file_path := pv_arch_file_path;
xxssdm_fnd_common_pkg.write_trace_message ( 'Program Name :' || gv_program_name || ' Start Date : ' || TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) );
xxssdm_fnd_common_pkg.write_trace_message ( 'Debug Flag :' || gv_debug_flag );
xxssdm_fnd_common_pkg.write_trace_message ( 'Data Set Id :' || pn_in_data_set_id );
IF UPPER (pv_in_run_mode) = 'LOAD' THEN
BEGIN
pv_out_ret_code := gn_conc_success;
pv_out_err_buf := NULL;
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Running in Load Mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Begining procedure to load Purchase Order data into staging table');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
IF gv_hdr_ctl_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Header Control file name cannot be NULL. Please provide the parameter Purchase Order Header Control File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_line_ctl_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Lines Control file name cannot be NULL. Please provide the parameter Purchase Order Lines Control File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_ctl_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Control file path cannot be NULL. Please provide the parameter Control file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_hdr_data_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Header Data file name cannot be NULL. Please provide the parameter Purchase Order Header Data File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_line_data_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Lines Data file name cannot be NULL. Please provide the parameter Purchase Order Lines Data File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_data_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Data file path cannot be NULL.Please provide the parameter Data file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_arch_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Archive file path cannot be NULL.Please provide the parameter Archive file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
lv_procedure_name := 'load_hdr_records_stg';
load_hdr_records_stg (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
BEGIN
SELECT COUNT (1)
INTO ln_headers_count
FROM xxsspo_po_headers_stg
WHERE data_set_id = gn_data_set_id;
EXCEPTION
WHEN OTHERS THEN
ln_headers_count := 0;
xxssdm_fnd_common_pkg.write_trace_message ('Error in getting the count of records in the header staging table.' );
END;
IF (ln_headers_count = 0) THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error in load_hdr_records_stg : No data uploaded from Header data file to the staging table');
ELSE
xxssdm_fnd_common_pkg.write_output_message('Header Records are loaded successfully');
END IF;
lv_procedure_name := 'load_lines_records_stg';
load_lines_records_stg (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
BEGIN
SELECT COUNT (1)
INTO ln_lines_count
FROM xxsspo_po_lines_stg
WHERE data_set_id = gn_data_set_id;
EXCEPTION
WHEN OTHERS THEN
ln_lines_count := 0;
xxssdm_fnd_common_pkg.write_trace_message ('Error in getting the count of records in the lines staging table.' );
END;
IF (ln_lines_count = 0) THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error in load_lines_records_stg : No data uploaded from lines data file to the staging table');
ELSE
xxssdm_fnd_common_pkg.write_output_message('Line Records are loaded successfully');
END IF;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message('Error occurred while loading data to staging tables.');
END;
ELSIF UPPER (pv_in_run_mode) = 'CREATE_BATCH' THEN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Running in Create Batch Mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
lv_procedure_name := 'Create_Batch';
IF pn_in_po_batch_size IS NULL THEN
pv_out_err_buf := 'Error in create_batch : pn_in_po_batch_size cannot be NULL. Please provide the parameter Batch Size';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
Create_Batch (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status ,pn_in_batch_size => ln_batch_size);
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
ELSE
xxssdm_fnd_common_pkg.write_output_message('Batches are created successfully');
END IF;
ELSIF UPPER (pv_in_run_mode) = 'VALIDATE' THEN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Running in Validate Mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
lv_procedure_name := 'validate_data';
validate_data (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
ELSE
xxssdm_fnd_common_pkg.write_output_message('Records are validated successfully');
END IF;
ELSIF UPPER (pv_in_run_mode) = 'TRANSFER' THEN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Running in Transfer Mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
lv_procedure_name := 'transfer_record';
transfer_record ( pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
ELSE
xxssdm_fnd_common_pkg.write_output_message('Transfer procedure is completed successfully');
END IF;
xxssdm_fnd_common_pkg.write_trace_message ('Transfer complete');
-- Query to generate reconciliation report
submit_recon_report ( pv_out_error_message => lv_err_buf ,pn_out_status => ln_status);
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
ELSIF UPPER (pv_in_run_mode) = 'FULL' THEN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Running in FULL Mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
BEGIN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Begining procedure to load Purchase Order data into staging table');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
IF gv_hdr_ctl_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Header Control file name cannot be NULL. Please provide the parameter Purchase Order Header Control File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_line_ctl_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Lines Control file name cannot be NULL. Please provide the parameter Purchase Order Lines Control File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_ctl_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Control file path cannot be NULL. Please provide the parameter Control file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_hdr_data_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Header Data file name cannot be NULL. Please provide the parameter Purchase Order Header Data File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_line_data_file_name IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Purchase Order Lines Data file name cannot be NULL. Please provide the parameter Purchase Order Lines Data File Name';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_data_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Data file path cannot be NULL.Please provide the parameter Data file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
IF gv_arch_file_path IS NULL THEN
pv_out_err_buf := 'Error in load_records_staging : Archive file path cannot be NULL.Please provide the parameter Archive file path';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
lv_procedure_name := 'load_hdr_records_stg';
load_hdr_records_stg (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
BEGIN
SELECT COUNT (1)
INTO ln_headers_count
FROM xxsspo_po_headers_stg
WHERE data_set_id = gn_data_set_id;
EXCEPTION
WHEN OTHERS THEN
ln_headers_count := 0;
xxssdm_fnd_common_pkg.write_trace_message ('Error in getting the count of records in the header staging table.' );
END;
IF (ln_headers_count = 0) THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error in load_hdr_records_stg : No data uploaded from Header data file to the staging table');
ELSE
xxssdm_fnd_common_pkg.write_output_message('Header Records are loaded successfully');
END IF;
lv_procedure_name := 'load_lines_records_stg';
load_lines_records_stg (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
BEGIN
SELECT COUNT (1)
INTO ln_lines_count
FROM xxsspo_po_lines_stg
WHERE data_set_id = gn_data_set_id;
EXCEPTION
WHEN OTHERS THEN
ln_lines_count := 0;
xxssdm_fnd_common_pkg.write_trace_message ('Error in getting the count of records in the lines staging table.' );
END;
IF (ln_lines_count = 0) THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error in load_lines_records_stg : No data uploaded from lines data file to the staging table');
ELSE
xxssdm_fnd_common_pkg.write_output_message('Line Records are loaded successfully');
END IF;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message('Error occurred while loading data to staging tables.');
END;
lv_procedure_name := 'Create_Batch';
IF pn_in_po_batch_size IS NULL THEN
pv_out_err_buf := 'Error in create_batch : pn_in_po_batch_size cannot be NULL. Please provide the parameter Batch Size';
pv_out_ret_code := gn_conc_error;
RAISE lexc_exception;
END IF;
Create_Batch (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status ,pn_in_batch_size => ln_batch_size);
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
ELSE
xxssdm_fnd_common_pkg.write_output_message('Batches are created successfully');
END IF;
lv_procedure_name := 'validate_data';
validate_data (pv_out_error_message => lv_err_buf ,pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
ELSE
xxssdm_fnd_common_pkg.write_output_message('Records are validated successfully');
END IF;
lv_procedure_name := 'transfer_record';
transfer_record (pv_out_error_message => lv_err_buf, pn_out_status => ln_status);
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
-- Query to generate reconciliation report
submit_recon_report ( pv_out_error_message => lv_err_buf ,pn_out_status => ln_status);
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
END IF; -- End of If clause for different Modes
-- Generate report
generate_report (pv_out_error_message => lv_err_buf, pn_out_status => ln_status );
IF ln_status <> gn_conc_success THEN
pv_out_err_buf := lv_err_buf;
pv_out_ret_code := ln_status;
RAISE lexc_exit_program;
END IF;
EXCEPTION
WHEN lexc_exception THEN
xxssdm_fnd_common_pkg.write_trace_message (pv_out_err_buf);
xxssdm_fnd_common_pkg.write_trace_message ( SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200) );
WHEN lexc_exit_program THEN
pv_out_ret_code := ln_status;
pv_out_err_buf := lv_err_buf;
xxssdm_fnd_common_pkg.write_trace_message ('Error in processing in Procedure: '||lv_procedure_name);
xxssdm_fnd_common_pkg.write_trace_message ('Error :'||lv_err_buf);
xxssdm_fnd_common_pkg.write_trace_message ( SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200) );
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('END OF main program');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
WHEN OTHERS THEN
ROLLBACK;
pv_out_ret_code := ln_status;
pv_out_err_buf := lv_err_buf;
xxssdm_fnd_common_pkg.write_trace_message ('Error occured while calling procedures in different mode.');
xxssdm_fnd_common_pkg.write_trace_message ( SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200) );
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('END OF main program');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
END main;
-- +===================================================================+
-- | Name: load_hdr_records_stg |
-- | |
-- | Description: Load records to header staging table FROM data file |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE load_hdr_records_stg(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT NUMBER )
IS
lv_load_pgm_name VARCHAR2(240) := 'SS Conversions Load Program';
ln_data_set_exists NUMBER :=0;
BEGIN
pn_out_status := gn_conc_success;
BEGIN
-- Check if input data_set_id already exists in stg table
-- If yes, then throw an error as existing data_set_id can not be used in case of LOAD
-- If Not found, then submit SQLLOADER program
SELECT 1
INTO ln_data_set_exists
FROM DUAL
WHERE EXISTS
( SELECT 1 FROM xxsspo_po_headers_stg WHERE DATA_SET_ID = gn_data_set_id
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_data_set_exists := 0;
WHEN OTHERS THEN
ln_data_set_exists := 0;
END;
IF ln_data_set_exists = 1 THEN
xxssdm_fnd_common_pkg.write_trace_message ('Please input correct value of Data Set Id. Entered data Set Id already exists');
pn_out_status := gn_conc_error;
pv_out_error_message := 'Please input correct value of Data Set Id. Entered data Set Id already exists';
ELSE
xxssdm_fnd_common_pkg.write_trace_message ('Calling Load Program - SS Conversions Load Program');
xxssdm_fnd_common_pkg.submit_conc_request ( errbuf => pv_out_error_message ,retcode => pn_out_status ,pn_out_child_request_id => gn_load_req_id ,pv_out_child_pgm_name => lv_load_pgm_name ,pv_in_application_top => 'XXSS' ,pv_in_request_short_name => 'XXSSDM_CONV_LOAD' ,pv_in_request_description => 'SS Conversions Load Program' ,pv_in_parameter1 => gv_hdr_ctl_file_name ,pv_in_parameter2 => gv_ctl_file_path ,pv_in_parameter3 => gv_hdr_data_file_name ,pv_in_parameter4 => gv_data_file_path ,pv_in_parameter5 => gv_arch_file_path );
xxssdm_fnd_common_pkg.write_trace_message ('Request submitted '||gn_load_req_id);
xxssdm_fnd_common_pkg.write_trace_message ('SQL Loader Program completed for Po Headers data ');
END IF;
EXCEPTION
WHEN OTHERS THEN
pv_out_error_message := 'Error in load_hdr_records_stg :'||SUBSTR(SQLERRM,1,200);
pn_out_status := gn_conc_error;
END load_hdr_records_stg;
-- +===================================================================+
-- | Name: load_lines_records_stg |
-- | |
-- | Description: Load records to lines staging table FROM data file |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE load_lines_records_stg(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT NUMBER )
IS
lv_load_pgm_name VARCHAR2(240) := 'SS Conversions Load Program';
ln_data_set_exists NUMBER :=0;
BEGIN
pn_out_status := gn_conc_success;
BEGIN
-- Check if input data_set_id already exists in stg table
-- If yes, then throw an error as existing data_set_id can not be used in case of LOAD
-- If Not found, then submit SQLLOADER program
SELECT 1
INTO ln_data_set_exists
FROM DUAL
WHERE EXISTS
( SELECT 1 FROM xxsspo_po_lines_stg WHERE DATA_SET_ID = gn_data_set_id
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_data_set_exists := 0;
WHEN OTHERS THEN
ln_data_set_exists := 0;
END;
IF ln_data_set_exists = 1 THEN
xxssdm_fnd_common_pkg.write_trace_message ('Please input correct value of Data Set Id. Entered data Set Id already exists');
pn_out_status := gn_conc_error;
pv_out_error_message := 'Please input correct value of Data Set Id. Entered data Set Id already exists';
ELSE
xxssdm_fnd_common_pkg.write_trace_message ('Calling Load Program - SS Conversions Load Program');
xxssdm_fnd_common_pkg.submit_conc_request ( errbuf => pv_out_error_message ,retcode => pn_out_status ,pn_out_child_request_id => gn_load_req_id ,pv_out_child_pgm_name => lv_load_pgm_name ,pv_in_application_top => 'XXSS' ,pv_in_request_short_name => 'XXSSDM_CONV_LOAD' ,pv_in_request_description => 'SS Conversions Load Program' ,pv_in_parameter1 => gv_line_ctl_file_name ,pv_in_parameter2 => gv_ctl_file_path ,pv_in_parameter3 => gv_line_data_file_name ,pv_in_parameter4 => gv_data_file_path ,pv_in_parameter5 => gv_arch_file_path );
xxssdm_fnd_common_pkg.write_trace_message ('Request submitted '||gn_load_req_id);
xxssdm_fnd_common_pkg.write_trace_message ('SQL Loader Program completed for Po lines data ');
END IF;
EXCEPTION
WHEN OTHERS THEN
pv_out_error_message := 'Error in load_lines_records_stg :'||SUBSTR(SQLERRM,1,200);
pn_out_status := gn_conc_error;
END load_lines_records_stg;
-- +===================================================================+
-- | Name: Create_batch |
-- | |
-- | Description: Create batch procedure for creating batches |
-- | |
-- | |
-- | Parameters: pn_in_batch_size - NUMBER - Batch size |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE create_batch(
pn_out_status OUT NUMBER ,
pv_out_error_message OUT VARCHAR2 ,
pn_in_batch_size IN NUMBER )
IS
lv_error_message VARCHAR2 (4000) := 0;
ln_batch_size NUMBER := 0;
ln_max_batch_id NUMBER := 0;
ln_total_count NUMBER := 0;
ln_num_of_batches NUMBER := 0;
BEGIN
ln_batch_size := pn_in_batch_size;
xxssdm_fnd_common_pkg.write_trace_message ('Provided batch size:' || ln_batch_size);
--Define total count
BEGIN
SELECT COUNT (1) total_count
INTO ln_total_count
FROM xxsspo_po_headers_stg stg
WHERE stg.status IN (gv_np,gv_pe,gv_ie,gv_ve)
AND stg.batch_id IS NULL
AND stg.data_set_id = gn_data_set_id
AND EXISTS
( SELECT 1 FROM xxsspo_po_lines_stg stl WHERE stg.po_number = stl.po_number
);
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Errror while getting count of records in Create_Batch procedure');
END;
xxssdm_fnd_common_pkg.write_trace_message ('Total number of records in headers Staging table:'|| ln_total_count);
xxssdm_fnd_common_pkg.write_trace_message ( 'Batch Size for each batch of header records: '|| ln_batch_size);
ln_num_of_batches := CEIL (ln_total_count / ln_batch_size);
xxssdm_fnd_common_pkg.write_trace_message ( 'No of batches for header records : '|| ln_num_of_batches);
FOR i IN 1 .. ln_num_of_batches
LOOP
xxssdm_fnd_common_pkg.write_trace_message ('Inside loop number , i=' || i);
BEGIN
SELECT XXSSDM_BATCH_ID_SEQ.NEXTVAL INTO ln_max_batch_id FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ( 'Error while deriving sequnce for batch id:' || SQLERRM );
END;
xxssdm_fnd_common_pkg.write_trace_message ( 'Batch ID for the current group of records:' || ln_max_batch_id);
BEGIN
UPDATE xxsspo_po_headers_stg hdr
SET hdr.batch_id = ln_max_batch_id
WHERE hdr.batch_id IS NULL
AND hdr.data_set_id = gn_data_set_id
AND ROWNUM <= ln_batch_size
AND EXISTS
( SELECT 1 FROM xxsspo_po_lines_stg l WHERE hdr.po_number = l.po_number
);
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ( 'Error while updating batch_id for purchase order headers' || SQLERRM);
END;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Exception in CREATE BATCH procedure: ' || SQLERRM);
END create_batch;
-- +====================================================================================================+
-- | Name: update_error |
-- | |
-- | Description: This procedure will update the staging tables with Status as |
-- | validation error and call the procedure to insert the error |
-- | details INTO the conversion error |
-- | table. |
-- | |
-- | Parameters: pv_in_error_message - VARCHAR2 - Error Message |
-- | pv_in_error_col_name - VARCHAR2 - Error Column Name |
-- | pv_in_error_col_value - VARCHAR2 - Error column value |
-- | pv_in_tabname - VARCHAR2 - Staging table name |
-- | pn_in_record_id - NUMBER - record ID |
-- | pv_in_po_number - VARCHAR2 - Po Number |
-- | pv_in_line_num - VARCHAR2 - Po Line Number |
-- | pv_in_status - VARCHAR2 - Input Status |
-- | |
-- | RETURNs : NONE |
-- | |
-- +====================================================================================================+
PROCEDURE update_error(
pv_in_error_message IN VARCHAR2 ,
pv_in_error_col_name IN VARCHAR2 ,
pv_in_error_col_value IN VARCHAR2 ,
pv_in_tabname IN VARCHAR2 ,
pn_in_record_id IN NUMBER ,
pv_in_po_number IN VARCHAR2 ,
pv_in_line_num IN VARCHAR2 ,
pv_in_status IN VARCHAR2 )
IS
lv_err_buf VARCHAR2(2000);
ln_ret_code VARCHAR2 (10);
lrec_in_conv_err xxssdm_fnd_common_pkg.grec_conversion_errors;
BEGIN
-- Conversion error table record
lrec_in_conv_err.data_set_id := gn_data_set_id;
lrec_in_conv_err.request_id := gn_conc_request_id;
lrec_in_conv_err.record_id := pn_in_record_id;
lrec_in_conv_err.wave_name := gv_wave_name;
lrec_in_conv_err.entity_name := gv_entity_name;
lrec_in_conv_err.error_description := pv_in_error_message;
lrec_in_conv_err.segment1 := pv_in_tabname;
lrec_in_conv_err.segment2 := pv_in_error_col_name;
lrec_in_conv_err.segment3 := pv_in_error_col_value;
lrec_in_conv_err.segment4 := pv_in_po_number;
lrec_in_conv_err.segment5 := pv_in_line_num;
lrec_in_conv_err.last_update_date := gd_date;
lrec_in_conv_err.last_updated_by := gn_user_id;
lrec_in_conv_err.creation_date := gd_date;
lrec_in_conv_err.created_by := gn_user_id;
lrec_in_conv_err.last_update_login := gn_login_id;
xxssdm_fnd_common_pkg.insert_error_table ( pv_out_errbuf => lv_err_buf ,pv_out_ret_code => ln_ret_code ,pv_in_error_rec => lrec_in_conv_err );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error occurred in updating update_error procedure for record id : '|| pn_in_record_id );
END update_error;
-- +===================================================================+
-- | Name: do_gen_validations |
-- | |
-- | Description: Do data validations on the Customer data |
-- | |
-- | |
-- | Parameters: pn_in_batch_id - NUMBER - record ID |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE do_gen_validations(
pn_in_batch_id IN NUMBER ,
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT NUMBER )
IS
----Variable Declaration---
lv_hdr_error VARCHAR2(2) := NULL; -- 18-Jan-lv_hdr_error
ln_int_header_id NUMBER;
ln_term_id NUMBER;
ln_agent_id NUMBER;
ln_freight_terms NUMBER;
ln_fob_code NUMBER;
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_bill_to_location_id NUMBER;
ln_ship_to_location_id NUMBER;
ln_int_line_id NUMBER;
ln_item_id NUMBER;
ln_int_distribution_id NUMBER;
line_num NUMBER;
ln_opco NUMBER :=0;
lv_uom_code VARCHAR2(10);
lv_vendor_name VARCHAR2(50);
lv_currency_code VARCHAR2(30);
lv_tmp_error_flag VARCHAR2(2) := 'N';
lv_error_flag VARCHAR2(1) := 'N';
lv_error_message VARCHAR2(2000) := NULL;
lv_err_msg_stg VARCHAR2(32767) := NULL;
l_int_line_id po_lines_interface.interface_line_id%TYPE;
l_int_hdr_id po_headers_interface.interface_header_id%TYPE;
ln_po_exists NUMBER := 0;
/* ********************************************************************
Open Cursor For Header Level Data
Cursor : lcur_po_header
This cursor will pick up the records from the staging table for Header
********************************************************************** */
CURSOR lcur_po_header (cp_batch_id IN NUMBER )
IS
SELECT STG.INTERFACE_HEADER_ID ,
STG.BATCH_ID ,
STG.RECORD_ID ,
STG.ACTION ,
STG.DOCUMENT_TYPE_CODE ,
STG.po_number ,
STG.PO_CREATION_DATE ,
STG.TERMS_ID ,
STG.PAYMENT_TERMS ,
STG.CURRENCY_CODE ,
STG.RATE_TYPE ,
STG.EXCHANGE_RATE ,
STG.EXCHANGE_RATE_DATE ,
STG.fob ,
STG.FREIGHT_TERMS ,
STG.Fob_lookup_code ,
STG.Freight_terms_lookup_code ,
STG.CARRIER ,
STG.AGENT_ID ,
STG.AGENT_NAME ,
STG.OPCO ,
STG.COMPANY_NUM ,
STG.VENDOR_ID ,
STG.VENDOR_NUMBER ,
STG.VENDOR_NAME ,
STG.VENDOR_SITE_CODE ,
STG.VENDOR_SITE_ID ,
STG.SHIP_TO_LOCATION ,
STG.BILL_TO_LOCATION ,
STG.bill_to_location_id ,
STG.Ship_to_location_id ,
STG.approval_status ,
STG.reference_num
FROM xxsspo_po_headers_stg STG
WHERE STG.Status IN (gv_np,gv_ve,gv_ie,gv_pe)
AND STG.data_set_id = gn_data_set_id
AND STG.batch_id = cp_batch_id
ORDER BY po_number; -- 30-mar-17 - item error fix
/* *******************************************************************
Cursor : lcur_po_lines
This cursor will pick up the records from the staging table for Lines
********************************************************************** */
CURSOR lcur_po_lines ( cp_data_set_id xxsspo_po_headers_stg.data_set_id%TYPE ,cp_po_number xxsspo_po_headers_stg.po_number%TYPE ,cp_po_creation_date xxsspo_po_headers_stg.po_creation_date%TYPE ,cp_interface_header_id xxsspo_po_headers_stg.interface_header_id%TYPE)
IS
SELECT STG.INTERFACE_LINE_ID ,
STG.INTERFACE_HEADER_ID ,
STG.po_number ,
STG.LINE_NUM ,
STG.Item_Id ,
STG.ITEM ,
STG.ITEM_DESCRIPTION ,
STG.quantity_ordered ,
STG.UNIT_PRICE ,
STG.Unit_Of_Measure ,
STG.UOM_CODE ,
STG.LINE_TYPE ,
STG.NEED_BY_DATE ,
STG.PROMISED_DATE ,
STG.INSPECTION_REQUIRED_FLAG ,
STG.RECEIPT_REQUIRED_FLAG ,
STG.SHIPMENT_TYPE ,
STG.EXPENSE_CODE ,
STG.CHARGE_ACCOUNT_ID ,
STG.COST_CODE ,
STG.interface_distribution_id ,
STG.DISTRIBUTION_NUM ,
STG.DESTINATION_TYPE ,
STG.DATA_SET_ID ,
STG.RECORD_ID ,
STG.OPCO ,
STG.LEGACY_SOURCE
FROM xxsspo_po_lines_stg STG
WHERE STG.Status IN (gv_np ,gv_v ,gv_ve)
AND STG.data_set_id = cp_data_set_id
AND STG.po_number = cp_po_number
ORDER BY po_number,Line_num; -- 30-mar-17 - item error fix
BEGIN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('Start of generic validations program for batch_id '||pn_in_batch_id);
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
pn_out_status := gn_conc_success; -- Setting the variable pn_out_status to zero
pv_out_error_message := NULL; -- Setting the variable pv_out_error_message to NULL
FOR header_rec IN lcur_po_header( pn_in_batch_id ) -- Header validation cursor is opening
LOOP
lv_error_flag := 'N';
lv_error_message := NULL;
lv_err_msg_stg := NULL;
ln_po_exists := 0;
ln_term_id := NULL;
lv_currency_code := NULL;
ln_fob_code := NULL;
ln_freight_terms := NULL;
ln_agent_id := NULL;
ln_opco := NULL;
ln_vendor_site_id := NULL;
ln_bill_to_location_id := NULL;
ln_Ship_to_location_id := NULL;
BEGIN
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message =>'+---------------------------------------------------------------------------+' ,pv_in_debug_flag => gv_debug_flag );
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating for record Id : '|| header_rec.record_id ,pv_in_debug_flag => gv_debug_flag );
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message =>'+---------------------------------------------------------------------------+' ,pv_in_debug_flag => gv_debug_flag );
-- CHECKING PO_NUMBER FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the po_number for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.po_number IS NULL THEN
lv_error_message := 'Mandatory field - Po_number is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- CALLING UPDATE LINES_ERROR PROCEDURE
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'po_number' ,pv_in_error_col_value => header_rec.po_number ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
ELSE
-- Check if PO# already exists
-- Added on 30-Jun
BEGIN
SELECT 1
INTO ln_po_exists
FROM po_headers_all pha
WHERE attribute1 = header_rec.po_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_po_exists := 0;
WHEN OTHERS THEN
ln_po_exists := 0;
END;
IF ln_po_exists > 0 THEN
lv_error_message := 'PO# already exists ';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- CALLING UPDATE LINES_ERROR PROCEDURE
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'PO_NUMBER' ,pv_in_error_col_value => header_rec.po_number ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
END IF;
-- CHECKING PO_CREATION_DATE FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the po creation date for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.PO_CREATION_DATE IS NULL THEN
lv_error_message := 'Po creation date is not provided in the extract';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Po_creation_date' ,pv_in_error_col_value => header_rec.po_creation_date ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
ELSE
IF header_rec.PO_CREATION_DATE > SYSDATE THEN
lv_error_message := 'Invalid po creation date provided ';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Po_creation_date' ,pv_in_error_col_value => header_rec.po_creation_date ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
END IF;
-- VALIDATE PAYMENT TERMS
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the payment terms :' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.PAYMENT_TERMS IS NOT NULL THEN
BEGIN
SELECT term_id
INTO ln_term_id
FROM ap_terms
WHERE NAME = header_rec.payment_terms
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Payment terms - '||header_rec.payment_terms||', is not present in Oracle';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'payment_terms' ,pv_in_error_col_value => header_rec.payment_terms ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the payment terms';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'payment_terms' ,pv_in_error_col_value => header_rec.payment_terms ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message || 'Error Details : '||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
ELSE
BEGIN
SELECT terms_id
INTO ln_term_id
FROM ap_suppliers
WHERE (attribute10 = header_rec.vendor_number or segment1 = header_rec.vendor_number) -- added on 25-may-17 for internal suppliers CR by veena
AND enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the payment terms';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'payment_terms' ,pv_in_error_col_value => header_rec.payment_terms ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message || ' : ' || SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
END IF;
-- VALIDATE CURRENCY CODE
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the currency code' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.currency_code IS NOT NULL THEN
BEGIN
SELECT DISTINCT cur.currency_code
INTO lv_currency_code
FROM fnd_currencies cur
WHERE UPPER(cur.currency_code) = UPPER(header_rec.currency_code)
AND cur.currency_flag = 'Y'
AND cur.enabled_flag = 'Y' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Currency code - '||header_rec.currency_code||', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Currency Code' ,pv_in_error_col_value => header_rec.currency_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => NULL ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message(lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Currency code ';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Currency Code' ,pv_in_error_col_value => header_rec.currency_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => NULL ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message||'Error message :' ||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
END IF;
-- VALIDATE EXCHANGE RATE
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the exchange rate :' , pv_in_debug_flag => gv_debug_flag );
IF header_rec.exchange_rate IS NULL THEN
lv_error_message := 'Mandatory field : Exchange rate is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'exchange_rate' ,pv_in_error_col_value => header_rec.exchange_rate ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
-- VALIDATING FOB
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Fob ' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.fob IS NOT NULL THEN
BEGIN
SELECT polc.lookup_code
INTO ln_fob_code
FROM fnd_lookup_values polc
WHERE polc.lookup_type = 'FOB'
AND polc.language = 'US'
AND polc.enabled_flag = 'Y'
AND NVL ( polc.end_date_active, SYSDATE) >= SYSDATE
AND polc.meaning = header_rec.fob;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_error_message := 'FOB - '||header_rec.FOB||', is not present in Oracle';
lv_error_flag := 'Y';
ln_fob_code := NULL;
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'fob' ,pv_in_error_col_value => header_rec.fob ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message(lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Fob ';
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'fob' ,pv_in_error_col_value => header_rec.fob ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||'Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
END IF;
-- Validating Freight terms
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Freight terms ' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.freight_terms IS NOT NULL THEN
BEGIN
SELECT polc.lookup_code
INTO ln_freight_terms
FROM po_lookup_codes polc
WHERE polc.lookup_code = header_rec.freight_terms
AND polc.lookup_type = 'FREIGHT TERMS'
AND enabled_flag = 'Y'
AND NVL (inactive_date, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_error_message := 'Freight terms - '||header_rec.FREIGHT_TERMS||', is not present in Oracle';
lv_error_flag := 'Y';
ln_freight_terms := NULL;
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'freight_terms' ,pv_in_error_col_value => header_rec.freight_terms ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message(lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Freight terms ';
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'freight_terms' ,pv_in_error_col_value => header_rec.freight_terms ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
END IF;
-- VALIDATE Agent name
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the agent name :' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.agent_name IS NOT NULL THEN
BEGIN
SELECT pa.agent_id
INTO ln_agent_id
FROM per_people_f ppx,
po_agents pa
WHERE pa.agent_id = ppx.person_id
AND UPPER (ppx.full_name) = UPPER (header_rec.agent_name)
AND ppx.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppx.EFFECTIVE_START_DATE AND ppx.EFFECTIVE_END_DATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Agent name - '||header_rec.agent_name||', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_agent_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'agent_name' ,pv_in_error_col_value => header_rec.agent_name ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
/* BEGIN -- added this default picking on 10-jan-17
SELECT pa.agent_id
INTO ln_agent_id
FROM per_people_f ppx, po_agents pa
WHERE pa.agent_id = ppx.person_id
AND UPPER (ppx.full_name) = UPPER ('McGough, Miss Siobhan')
AND ppx.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppx.EFFECTIVE_START_DATE
AND ppx.EFFECTIVE_END_DATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--ln_ret_code := '2';
lv_error_message := 'Agent name - '||'McGough, Miss Siobhan'||', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_agent_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'agent_name'
,pv_in_error_col_value => header_rec.agent_name
,pv_in_tabname => gv_header_tabname
,pn_in_record_id => header_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => NULL
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN OTHERS
THEN
lv_error_message := 'Error occured while validating the agent name';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'agent_name'
,pv_in_error_col_value => header_rec.agent_name
,pv_in_tabname => gv_header_tabname
,pn_in_record_id => header_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => NULL
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message
(lv_error_message ||' ## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END; */
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the agent name';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'agent_name' ,pv_in_error_col_value => header_rec.agent_name ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' ## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
ELSE
lv_error_message := 'Mandatory filed : Agent name is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'agent_name' ,pv_in_error_col_value => header_rec.agent_name ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
-- Checking Company number for Null values
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the company_num'||header_rec.company_num ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.company_num IS NULL THEN
lv_error_message := 'Mandatory field - Company num is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'company_num' ,pv_in_error_col_value => header_rec.company_num ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
ELSE
-- Added below -- 23-Jun
ln_opco :=0;
IF REPLACE(header_rec.company_num,chr(13)) = '94' THEN
SELECT organization_id
INTO ln_opco
FROM hr_operating_units
WHERE name='Specsavers Wholesale Ltd - OU';
END IF;
END IF;
-- validating for vendor number and fetching vendor id and vendor name
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the vendor number :' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.vendor_number IS NOT NULL THEN
BEGIN
SELECT pa.vendor_id,
pa.vendor_name
INTO ln_vendor_id,
lv_vendor_name
FROM ap_suppliers pa
WHERE ( pa.attribute10 = header_rec.vendor_number
or pa.segment1 = header_rec.vendor_number ) -- added on 25-may-17 for internal suppliers CR by veena
-- AND pa.org_id = header_rec.opco -- Open issue sanjay
AND NVL (end_date_active, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Vendor number - '||header_rec.vendor_number||', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_agent_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_number' ,pv_in_error_col_value => header_rec.vendor_number ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the vendor number';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_number' ,pv_in_error_col_value => header_rec.vendor_number ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' ## Error message: ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
ELSE
lv_error_message := 'Mandatory filed : Vendor number is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_number' ,pv_in_error_col_value => header_rec.vendor_number ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
-- validating for vendor site code and fetching vendor site id
-- Validating if supplier have at least one purchasing site linked to it (Too_many_rows exception handle's this)
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the vendor site code :' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.vendor_site_code IS NULL THEN
BEGIN
SELECT ssa.vendor_site_id
INTO ln_vendor_site_id
FROM ap_supplier_sites_all ssa
WHERE ssa.purchasing_site_flag = 'Y'
AND ssa.org_id = ln_opco -- Open issue sanjay
AND ssa.vendor_id = ln_vendor_id
AND NVL (ssa.inactive_date, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Vendor_site_code '||'is not present in oracle for vendor '||header_rec.vendor_number; -- 30-mar-17 - vendor site error message
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_agent_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_site_code' ,pv_in_error_col_value => header_rec.vendor_site_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN TOO_MANY_ROWS THEN
--ln_ret_code := '2';
lv_error_message := 'Too many rows error while deriving vendor site id in oracle for vendor '||header_rec.vendor_number; -- 30-mar-17 - vendor site error message
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_agent_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_site_code' ,pv_in_error_col_value => header_rec.vendor_site_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while fetching the vendor_site_id in oracle for vendor '||header_rec.vendor_number; -- 30-mar-17 - vendor site error message
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_site_code' ,pv_in_error_col_value => header_rec.vendor_site_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' ## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;

-- Added as per new requirement on supplier sites - 25 May - veena
ELSE
BEGIN
SELECT ssa.vendor_site_id
INTO ln_vendor_site_id
FROM ap_supplier_sites_all ssa
WHERE ssa.vendor_site_code = header_rec.vendor_site_code
AND ssa.purchasing_site_flag = 'Y'
AND ssa.org_id = ln_opco
AND ssa.vendor_id = ln_vendor_id
AND NVL (ssa.inactive_date, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN OTHERS THEN
lv_error_message := 'Error occured while fetching the vendor_site_id in oracle for vendor '||header_rec.vendor_number||' and vendor site code '||header_rec.vendor_site_code; -- 30-mar-17 - vendor site error message
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'vendor_site_code' ,pv_in_error_col_value => header_rec.vendor_site_code ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' ## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
END IF;
-- Validating Bill to location
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Bill to location :' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.BILL_TO_LOCATION IS NOT NULL THEN
BEGIN
SELECT location_id
INTO ln_bill_to_location_id
FROM hr_locations_all hrl
WHERE hrl.location_code = header_rec.bill_to_location
AND hrl.bill_to_site_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Bill to location value - ' ||header_rec.BILL_TO_LOCATION|| ', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_bill_to_location_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'BILL_TO_LOCATION' ,pv_in_error_col_value => header_rec.BILL_TO_LOCATION ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message );
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Bill to location';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'BILL_TO_LOCATION' ,pv_in_error_col_value => header_rec.BILL_TO_LOCATION ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||'## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
END IF;
-- Validating Ship to location code
xxssdm_fnd_common_pkg.write_debug_message (pv_in_debug_message => 'Validating the Ship to location code :', pv_in_debug_flag => gv_debug_flag);
IF header_rec.Ship_to_location IS NOT NULL THEN
BEGIN
SELECT location_id
INTO ln_Ship_to_location_id
FROM hr_locations_all hrl
WHERE hrl.location_code = header_rec.Ship_to_location
AND hrl.bill_to_site_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Ship to location value - ' ||header_rec.SHIP_TO_LOCATION|| ', is not present in Oracle';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_Ship_to_location_id := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Ship_to_location' ,pv_in_error_col_value => header_rec.Ship_to_location ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Ship to location code';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Ship_to_location' ,pv_in_error_col_value => header_rec.Ship_to_location ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => header_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||' ## Error message : ' ||SQLCODE|| '-'|| SUBSTR(SQLERRM,1,200));
END;
END IF;
-- Updating Staging table with errors at the end of Header validation
UPDATE xxsspo_po_headers_stg XAIHC
SET XAIHC.request_id = gn_conc_request_id ,
XAIHC.program_id = gn_conc_program_id ,
XAIHC.program_update_date = gd_date ,
XAIHC.last_updated_by = gn_user_id ,
XAIHC.last_update_date = gd_date ,
XAIHC.last_update_login = gn_login_id ,
XAIHC.creation_date = gd_date ,
XAIHC.created_by = gn_user_id ,
XAIHC.TERMS_ID = ln_term_id ,
XAIHC.Fob_lookup_code = ln_fob_code ,
XAIHC.Freight_terms_lookup_code = ln_freight_terms ,
XAIHC.bill_to_location_id = ln_bill_to_location_id ,
XAIHC.Ship_to_location_id = ln_Ship_to_location_id ,
XAIHC.vendor_name = lv_vendor_name ,
XAIHC.agent_id = ln_agent_id ,
XAIHC.VENDOR_ID = ln_vendor_id ,
XAIHC.VENDOR_SITE_ID = ln_vendor_site_id ,
XAIHC.Action = 'ORIGINAL' ,
XAIHC.Document_Type_code = 'STANDARD' ,
XAIHC.RATE_TYPE = 'User' ,
XAIHC.Approval_Status ='APPROVED' ,
XAIHC.status = DECODE(lv_error_flag,'Y',gv_ve,gv_v) ,
XAIHC.error_message = DECODE(lv_error_flag,'Y',lv_err_msg_stg,NULL) ,
XAIHC.opco = ln_opco
WHERE XAIHC.po_number = header_rec.po_number
AND XAIHC.data_set_id = gn_data_set_id
AND XAIHC.record_id = header_rec.record_id;
COMMIT;
/* ********************************************************************
Open Cursor For Line Level Data
*********************************************************************** */
lv_hdr_error := NULL; -- 30-mar-17 - line error for item issue
FOR Line_rec IN lcur_po_lines (gn_data_set_id,header_rec.po_number,header_rec.po_creation_date,ln_int_header_id)
LOOP
lv_error_flag := 'N';
lv_error_message := NULL;
lv_err_msg_stg := NULL;
-- lv_hdr_error := NULL; -- 30-mar-17 - line error for item issue -- commented as to capture line in between has item issue

lv_uom_code := NULL;
BEGIN
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating in lines cursor for record id :'||line_rec.record_id ,pv_in_debug_flag => gv_debug_flag );
-- CHECKING po_number FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the po_number for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.po_number IS NULL THEN
lv_error_message := 'Mandatory field : Po_number is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'po_number' ,pv_in_error_col_value => line_rec.po_number ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message );
END IF;
-- Checking for Po line number
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the line_num for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.line_num IS NULL THEN
lv_error_message := 'Mandatory field : line_num is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error := 'Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'line_num' ,pv_in_error_col_value => line_rec.line_num ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF;
-- validating for Item and fetching Item id
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Item :' ,pv_in_debug_flag => gv_debug_flag );
-- IF UPPER(LINE_REC.LEGACY_SOURCE) <> UPPER('OPEN ACCOUNTS') -- commneted on 7-jan-17
-- THEN
IF line_rec.Item IS NOT NULL THEN
BEGIN
SELECT msi.inventory_item_id
INTO ln_item_id
FROM mtl_system_items_b msi,
mtl_parameters mp
WHERE msi.organization_id = mp.organization_id
--AND UPPER(mp.organization_code) = UPPER(Line_rec.opco) -- this was commented before only
AND UPPER(mp.organization_code) = 'SWL' -- added hardcoded value on 8-jan-17 SIT --
AND msi.segment1 = line_rec.item
AND msi.purchasing_enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Item - '||line_rec.Item||', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
ln_item_id := NULL;
lv_hdr_error :='Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Item' ,pv_in_error_col_value => line_rec.Item ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Item';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Item' ,pv_in_error_col_value => line_rec.Item ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message || '## Error message :' ||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
/* -- commneted on 7-jan-17
ELSE
lv_error_message := 'Mandatory field : Item is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'Item'
,pv_in_error_col_value => line_rec.Item
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => NULL
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message); */
END IF;
-- END IF;
-- CHECKING ITEM_DESCRIPTION FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the item description for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.ITEM_DESCRIPTION IS NULL -- description was checked when item is null also in the standard po import program
THEN
lv_error_message := 'Mandatory field : Item desciption is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'ITEM_DESCRIPTION' ,pv_in_error_col_value => line_rec.ITEM_DESCRIPTION ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
-- CHECKING quantity_ordered FOR NULL VALUES
BEGIN
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the quantity_ordered for null values' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.quantity_ordered IS NULL THEN
lv_error_message := 'Mandatory field : Quanitity is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'quantity_ordered' ,pv_in_error_col_value => line_rec.quantity_ordered ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
ELSIF line_rec.quantity_ordered < 0 THEN
lv_error_message := 'quantity_ordered should be greater then zero';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'quantity_ordered' ,pv_in_error_col_value => line_rec.quantity_ordered ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the quantity_ordered';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'quantity_ordered' ,pv_in_error_col_value => line_rec.quantity_ordered ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||'## Error message :'||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
-- CHECKING UNIT PRICE FOR NULL VALUES
BEGIN
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Unit price for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.UNIT_PRICE IS NULL THEN
lv_error_message := 'Mandatory field : Unit price is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'UNIT_PRICE' ,pv_in_error_col_value => line_rec.UNIT_PRICE ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
ELSIF line_rec.UNIT_PRICE < 0 THEN
lv_error_message := 'Unit price should be greater then zero';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'UNIT_PRICE' ,pv_in_error_col_value => line_rec.UNIT_PRICE ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Unit price';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'UNIT_PRICE' ,pv_in_error_col_value => line_rec.UNIT_PRICE ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message ||'## Error message :'|| SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
-- Validating for Unit Of Measure
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the Unit Of Measure :' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.unit_of_measure IS NOT NULL THEN
BEGIN
SELECT uom_code
INTO lv_uom_code
FROM mtl_units_of_measure
WHERE UPPER(unit_of_measure) = UPPER(line_rec.unit_of_measure)
AND (disable_date > SYSDATE
OR disable_date IS NULL)
UNION
SELECT uom_code
FROM mtl_units_of_measure
WHERE UPPER(uom_code) = UPPER(line_rec.unit_of_measure)
AND (disable_date > SYSDATE
OR disable_date IS NULL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
--ln_ret_code := '2';
lv_error_message := 'Unit of Measure - '||line_rec.unit_of_measure|| ', is not present in Oracle' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_uom_code := NULL;
lv_hdr_error :='Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'Unit_Of_Measure'
,pv_in_error_col_value => line_rec.Unit_Of_Measure
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => line_rec.line_num
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Error occured while validating the Unit Of Measure';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
--ln_ret_code := '2';
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'Unit_Of_Measure'
,pv_in_error_col_value => line_rec.Unit_Of_Measure
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => line_rec.line_num
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message ||'## Error message :'|| SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
END;
IF line_rec.Item IS NOT NULL AND ln_item_id IS NOT NULL -- added on 9-Feb-17
THEN
BEGIN
SELECT DISTINCT primary_unit_of_measure --primary_uom_code
INTO lv_uom_code
FROM mtl_system_items_b msi
WHERE msi.segment1 = line_rec.item
-- AND UPPER(msi.primary_unit_of_measure) = UPPER(line_rec.unit_of_measure);
AND UPPER(msi.primary_uom_code) = UPPER(line_rec.unit_of_measure); -- modified on 7-jan-17
EXCEPTION
WHEN OTHERS THEN
lv_uom_code := NULL;
lv_hdr_error :='Y';
lv_error_message := line_rec.unit_of_measure ||' UOM code doesnot exists for the item for the Organization';
--lv_error_message := 'Invalid Unit of Measure/Item combination is provided' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_uom_code := NULL;
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Unit_Of_Measure' ,pv_in_error_col_value => line_rec.Unit_Of_Measure ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END;
END IF;
ELSE
IF line_rec.Item IS NOT NULL AND ln_item_id IS NOT NULL -- added on 9-feb-17
THEN
BEGIN
SELECT DISTINCT primary_unit_of_measure --primary_uom_code
INTO lv_uom_code
FROM mtl_system_items_b msi
WHERE msi.segment1 = line_rec.item;
EXCEPTION
WHEN OTHERS THEN
lv_uom_code := NULL;
lv_hdr_error :='Y';
lv_error_message := line_rec.unit_of_measure ||' UOM code doesnot exists for the item for the Organization';
--lv_error_message := 'Invalid Unit of Measure/Item combination is provided' ;
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'Unit_Of_Measure' ,pv_in_error_col_value => line_rec.Unit_Of_Measure ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END;
ELSE
-- lv_error_message := 'Mandatory field : Unit of measure is not provided';
lv_error_message := 'Mandatory field : Unit of measure is not provided, hence defaulted with value EA';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_uom_code := NULL;
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update_ error procedure for updating error in conversion error table
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'Unit_Of_Measure'
,pv_in_error_col_value => line_rec.Unit_Of_Measure
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => line_rec.line_num
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message);
END IF;
END IF;

-- 15-jun-17 veena added as need by date or promise date is must for po approval. if both are null raise validation error
-- CHECKING NEED_BY_DATE FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the need by date/promise date for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF line_rec.promised_date IS NULL AND
line_rec.need_by_date IS NULL -- Condition If Need_by_date and po_creation_date value not provided, then log error message
THEN
lv_error_message := 'Mandatory field : Need_by_date/Promise date is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'NEED_BY_DATE' ,pv_in_error_col_value => line_rec.NEED_BY_DATE ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF;

-- 14-jun-17 veena commented as need by can left blank if not found in data extract. and it should not be populated with PO Creation date.
-- CHECKING NEED_BY_DATE FOR NULL VALUES
/* xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Validating the need by date for null values ' ,pv_in_debug_flag => gv_debug_flag );
IF header_rec.po_creation_date IS NULL AND
line_rec.need_by_date IS NULL -- Condition If Need_by_date and po_creation_date value not provided, then log error message
THEN
lv_error_message := 'Mandatory field : Need_by_date is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
lv_hdr_error :='Y';
-- Calling update lines_error procedure
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => 'NEED_BY_DATE' ,pv_in_error_col_value => line_rec.NEED_BY_DATE ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => line_rec.record_id ,pv_in_po_number => header_rec.po_number ,pv_in_line_num => line_rec.line_num ,pv_in_status => gv_ve );
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF; */
-- Commented for test run of Extracts ( To be uncommented later )
/* -- CHECKING EXPENSE_CODE FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message
(
pv_in_debug_message => 'Validating the expense code for null values '
,pv_in_debug_flag => gv_debug_flag
);
IF line_rec.EXPENSE_CODE IS NULL
THEN
lv_error_message := 'Mandatory field : Expense code is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update lines_error procedure
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'EXPENSE_CODE'
,pv_in_error_col_value => line_rec.EXPENSE_CODE
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => line_rec.line_num
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF;
-- CHECKING Cost_CODE FOR NULL VALUES
xxssdm_fnd_common_pkg.write_debug_message (pv_in_debug_message => 'Validating the Cost code for null values ', pv_in_debug_flag => gv_debug_flag);
IF line_rec.Cost_CODE IS NULL
THEN
lv_error_message := 'Mandatory field : Cost code is not provided';
lv_err_msg_stg := SUBSTR(lv_error_message||'|'||lv_err_msg_stg,1,4000);
lv_error_flag := 'Y';
-- Calling update lines_error procedure
update_error
(
pv_in_error_message => lv_error_message
,pv_in_error_col_name => 'Cost_CODE'
,pv_in_error_col_value => line_rec.Cost_CODE
,pv_in_tabname => gv_lines_tabname
,pn_in_record_id => line_rec.record_id
,pv_in_po_number => header_rec.po_number
,pv_in_line_num => line_rec.line_num
,pv_in_status => gv_ve
);
xxssdm_fnd_common_pkg.write_trace_message ( lv_error_message);
END IF; */
-- << Derivation of CHARGE_ACCOUNT_ID >> -- Open issue sanjay
-- Updating Staging table with errors at the end of Line validation
UPDATE xxsspo_po_lines_stg XAILC
SET XAILC.request_id = gn_conc_request_id ,
XAILC.program_id = gn_conc_program_id ,
XAILC.program_update_date = gd_date ,
XAILC.last_updated_by = gn_user_id ,
XAILC.last_update_date = gd_date ,
XAILC.last_update_login = gn_login_id ,
XAILC.creation_date = gd_date ,
XAILC.created_by = gn_user_id ,
XAILC.Item_id = ln_item_id ,
XAILC.interface_header_id = ln_int_header_id ,
XAILC.LINE_TYPE = 'Goods' ,
XAILC.SHIPMENT_TYPE = 'STANDARD' ,
XAILC.DISTRIBUTION_NUM = 1 ,
XAILC.DESTINATION_TYPE = DECODE(line_rec.item,NULL,'Expense','Inventory'),
-- ,XAILC.CHARGE_ACCOUNT_ID = 'NA' -- Open issue sanjay
--,XAILC.NEED_BY_DATE = DECODE(line_rec.NEED_BY_DATE,NULL,header_rec.po_creation_date,line_rec.NEED_BY_DATE) , -- veena commented on 14-jun-17 as no change done to need_by_date
XAILC.status = DECODE(lv_error_flag,'Y',gv_ve,gv_v) ,
XAILC.error_message = DECODE(lv_error_flag,'Y',lv_err_msg_stg,NULL) ,
XAILC.UOM_CODE = lv_uom_code
WHERE XAILC.po_number = header_rec.po_number
AND XAILC.data_set_id = gn_data_set_id
AND XAILC.record_id = line_rec.record_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_debug_message('Error occurred in Lines Cursor validation' ||SUBSTR(SQLERRM,1,200) ,pv_in_debug_flag => gv_debug_flag);
END; -- Ending lines cursor
END LOOP; -- Ending lines loop
IF lv_hdr_error ='Y' THEN

UPDATE xxsspo_po_headers_stg XAILC
SET XAILC.status = DECODE(lv_hdr_error,'Y',gv_ve,XAILC.status) ,
XAILC.error_message = DECODE(lv_hdr_error,'Y',XAILC.error_message||' And One or more lines has errors',XAILC.error_message) -- 30-mar-17 - line error for item issue
WHERE XAILC.po_number = header_rec.po_number
AND XAILC.data_set_id = gn_data_set_id;

END IF;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_debug_message('Error occurred in Header Cursor validation'||SUBSTR(SQLERRM,1,200) ,pv_in_debug_flag => gv_debug_flag);
END; -- ending header cursor
END LOOP;
xxssdm_fnd_common_pkg.write_trace_message(' Do_gen_validations procedure completed successfully for Batch id '|| pn_in_batch_id);
EXCEPTION
WHEN OTHERS THEN
pn_out_status := gn_conc_error;
pv_out_error_message := 'Error when others in do_gen_validations procedure for batch id'||pn_in_batch_id;
xxssdm_fnd_common_pkg.write_trace_message ( 'Unexpected error occured during generic validation for batch id' ||pn_in_batch_id||'## Error message :'||SQLCODE|| '-'|| SUBSTR (SQLERRM, 1, 200) );
END do_gen_validations; -- End of generic data validation procedure
-- +===================================================================+
-- | Name: validate_data |
-- | |
-- | Description: Validation of the records. |
-- | This is a wrapper procedure to call other validation |
-- | procedure. |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +===================================================================+
PROCEDURE validate_data(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT NUMBER )
IS
CURSOR lcur_count(pn_in_data_set_id IN NUMBER)
IS
SELECT DISTINCT STG.Batch_id
FROM xxsspo_po_headers_stg STG
WHERE STG.data_set_id = pn_in_data_set_id
AND UPPER( STG.status) IN (gv_np,gv_ve,gv_ie,gv_pe);
lv_error_message VARCHAR2(2000);
ln_out_status NUMBER;
lexc_exception EXCEPTION;
lv_error_flag VARCHAR2(1) := 'N';
BEGIN
pn_out_status := gn_conc_success;
gn_tl_hdr_validated_cnt := 0;
gn_tl_lines_validated_cnt :=0;
gn_tl_hdr_validation_err_cnt := 0;
gn_tl_lines_validation_err_cnt := 0;
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message('Begining procedure to validate data');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
-- Updating status and error message to Not processed and Null for every fresh run of Validation procedure
UPDATE xxsspo_po_headers_stg
SET status = gv_np ,
error_message = NULL
WHERE Status IN (gv_np,gv_ve,gv_v,gv_pe,gv_ie)
AND data_set_id = gn_data_set_id;
COMMIT;
UPDATE xxsspo_po_lines_stg
SET status = gv_np ,
error_message = NULL
WHERE Status IN (gv_np,gv_ve,gv_v,gv_pe,gv_ie)
AND data_set_id = gn_data_set_id;
COMMIT;
-- MARK ALL DUPLICATE po_number records -- TO BE CHECKED AND TESTED
UPDATE xxsspo_po_headers_stg
SET request_id = gn_conc_request_id ,
last_updated_by = gn_user_id ,
last_update_date = gd_date ,
last_update_login = gn_login_id ,
creation_date = gd_date ,
created_by = gn_user_id ,
status = gv_dr ,
error_message = 'Duplicate PO numbers in extract'
WHERE po_number IN
(SELECT po_number
FROM
(SELECT po_number po_number,
ROW_NUMBER() OVER ( PARTITION BY po_number ORDER BY ROWID) AS ROW_NUMBER
FROM xxsspo_po_headers_stg
WHERE status = gv_np
AND data_set_id = gn_data_set_id
)
WHERE ROW_NUMBER <> 1
)
AND( status = gv_np
OR status = gv_ve )
AND data_set_id = gn_data_set_id;
COMMIT;
-- MARK ALL DUPLICATE LINE_NUM records -- TO BE CHECKED AND TESTED
UPDATE xxsspo_po_lines_stg
SET request_id = gn_conc_request_id ,
last_updated_by = gn_user_id ,
last_update_date = gd_date ,
last_update_login = gn_login_id ,
creation_date = gd_date ,
created_by = gn_user_id ,
status = gv_dr ,
error_message = 'Duplicate line num in extract'
WHERE (po_number,line_num) IN
(SELECT po_number,
line_num
FROM
(SELECT po_number,
line_num,
ROW_NUMBER() OVER ( PARTITION BY line_num ,Po_number ORDER BY ROWID) AS ROW_NUMBER
FROM xxsspo_po_lines_stg
WHERE status = gv_np
AND legacy_source = 'Open Accounts'
AND data_set_id = gn_data_set_id
)
WHERE ROW_NUMBER <> 1
)
AND( status = gv_np
OR status = gv_ve )
AND legacy_source = 'Open Accounts'
AND data_set_id = gn_data_set_id;
COMMIT;
FOR count_rec IN lcur_count(gn_data_set_id )
LOOP
lv_error_flag := 'N';
lv_error_message := NULL;
BEGIN
do_gen_validations(pn_in_batch_id => count_rec.batch_id ,pv_out_error_message => lv_error_message ,pn_out_status => ln_out_status);
IF ln_out_status = gn_conc_error THEN
RAISE lexc_exception;
END IF;
ln_out_status := gn_conc_success;
EXCEPTION
WHEN OTHERS THEN
pv_out_error_message := 'Error in validate_data :'||SUBSTR(SQLERRM,1,200);
pn_out_status := gn_conc_error;
END;
END LOOP;
EXCEPTION
WHEN lexc_exception THEN
pv_out_error_message := lv_error_message;
pn_out_status := ln_out_status;
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message('Error while running in validate mode');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
WHEN OTHERS THEN
pv_out_error_message := 'Error in validate_data :'||SUBSTR(SQLERRM,1,200);
pn_out_status := gn_conc_error;
END validate_data;
-- +===================================================================+
-- | Name: update_status |
-- | |
-- | Description: Procedure to update staging table for records |
-- | |
-- | |
-- | Returns : pv_out_error_message - VARCHAR2 - Error Message |
-- | pv_out_error_flag - VARCHAR2 - Error FLag |
-- +===================================================================+
PROCEDURE update_status(
pv_out_error_status_message OUT VARCHAR2 ,
pn_out_update_status OUT NUMBER)
IS
BEGIN
UPDATE xxsspo_po_lines_stg stg
SET stg.attribute2 = gn_child_req_id ,
stg.last_update_date = gd_date ,
stg.last_updated_by = gn_user_id ,
stg.attribute4 = gv_p ,
stg.error_message = NULL
WHERE attribute3 NOT IN
(SELECT mti.transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.process_flag = 3
)
AND attribute4 = gv_i
AND data_set_id = gn_data_set_id;
UPDATE xxsspo_po_lines_stg stg
SET stg.attribute2 = gn_child_req_id ,
stg.last_update_date = gd_date ,
stg.last_updated_by = gn_user_id ,
stg.attribute4 = gv_pe ,
stg.error_message =
(SELECT error_code
FROM mtl_transactions_interface mti
WHERE stg.attribute3 =mti.transaction_interface_id
)
WHERE attribute3 IN
(SELECT mti.transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.process_flag = 3
)
AND attribute4 = gv_i
AND data_set_id = gn_data_set_id;
xxssdm_fnd_common_pkg.write_trace_message('Updated staging table for the records transferred to base tables '||SQLERRM);
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message('Error in updating staging table '||SQLERRM);
pv_out_error_status_message := 'Error in updating staging table ';
pn_out_update_status := gn_conc_error;
END update_status;
-- +===================================================================+
-- | Name: Transfer_record |
-- | |
-- | Description: This procedure will load the Purchase order data |
-- | FROM staging table to interface tables and |
-- | FROM interface table to base tables using |
-- | import program. |
-- | |
-- | |
-- | |
-- | Parameters: |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +====================================================================+
PROCEDURE Transfer_record(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT NUMBER )
IS
--- VARIABLE DECLARATION ---
lv_error_message VARCHAR2(4000) := NULL;
lv_status VARCHAR2(5) := gv_v;
lv_child_pgm_name VARCHAR2 (100);
lv_error_flag VARCHAR2(1) := 'N';
ln_num_index NUMBER :=0;
ln_request_id NUMBER;
lv_boo_wait_hold BOOLEAN;
lv_chr_phase VARCHAR2 (20);
lv_chr_status VARCHAR2 (30);
lv_chr_dev_phase VARCHAR2 (30);
lv_chr_dev_status VARCHAR2 (30);
lv_chr_message_ji VARCHAR2 (200);
ln_num_wait_request_count NUMBER := 0;
ln_num_complete_request_count NUMBER := 0;
lv_func_currency VARCHAR2(20) := NULL;
ln_rcv_header_id NUMBER;
ln_rcv_group_iD NUMBER;
ln_rcv_transaction_id NUMBER;
ln_rcv_count NUMBER:=0;

lv_sub_inv fnd_lookup_values.attribute1%TYPE;
---Cursor for submitting standard import with required parameters.
CURSOR cr_po_import
IS
SELECT DISTINCT stg.batch_id,
stg.opco
FROM xxsspo_po_headers_stg stg
WHERE 1 =1
AND stg.request_id = gn_conc_request_id
AND stg.Status = 'I'
AND stg.batch_id IS NOT NULL
AND stg.po_number IN
(SELECT stgl.po_number
FROM xxsspo_po_lines_stg stgl
WHERE 1 =1
AND stgl.request_id = stg.request_id
AND stgl.status = 'I'
AND stgl.po_number = stg.po_number
);
CURSOR cr_receive_hdr_import
IS
SELECT poh.attribute1,
poh.PO_HEADER_ID ,
poh.VENDOR_ID ,
poh.vendor_site_id,
poh.SEGMENT1 ,
poh.ORG_ID,
poh.agent_id ,
poh.ship_to_location_id ,
stg.po_number ,
a.interface_header_id
FROM xxsspo_po_headers_stg STG,
po_headers_all poh ,
po_headers_interface a
WHERE STG.data_set_id = gn_data_set_id
AND STG.status = 'P'
AND a.interface_header_id = stg.interface_header_id
AND a.po_header_id = poh.po_header_id ;
CURSOR cr_receive_tran_import (cp_po_header_id po_headers_all.po_header_id%TYPE)
IS
SELECT pol.po_header_id,
pol.org_id,
Pol.ITEM_ID ,
POL.PO_LINE_ID ,
POL.LINE_NUM ,
PLL.QUANTITY ,
POL.UNIT_MEAS_LOOKUP_CODE ,
PLL.LINE_LOCATION_ID ,
PLL.CLOSED_CODE ,
PLL.QUANTITY_RECEIVED ,
PLL.CANCEL_FLAG,
PLL.SHIPMENT_NUM ,
pll.ship_to_organization_id,
pod.po_distribution_id ,
b.interface_header_id,
b.interface_line_id,
stg.quantity_delivered
FROM po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
po_lines_interface b ,
xxsspo_po_lines_stg stg
WHERE pol.po_header_id = cp_po_header_id
AND pol.po_line_id = pll.po_line_id
AND pol.po_line_id = pod.po_line_id
AND b.po_header_id = pol.po_header_id
AND b.po_line_id =pol.po_line_id
AND stg.interface_header_id = b.interface_header_id
AND stg.interface_line_id = b.interface_line_id
AND NVL(stg.quantity_delivered,0) > 0;
CURSOR lcur_count
IS
SELECT DISTINCT STG.Batch_id
FROM xxsspo_po_headers_stg STG
WHERE STG.data_set_id = gn_data_set_id
AND UPPER( STG.status) IN (gv_v);
CURSOR hdr_cur (cp_batch_id IN NUMBER )
IS
SELECT STG.interface_header_id ,
STG.batch_id ,
STG.action ,
STG.document_type_code ,
STG.po_number ,
STG.po_creation_date ,
STG.terms_id ,
STG.payment_terms ,
STG.currency_code ,
STG.rate_type ,
STG.exchange_rate ,
STG.exchange_rate_date ,
STG.fob ,
STG.freight_terms ,
STG.fob_lookup_code ,
STG.freight_terms_lookup_code ,
STG.freight_carrier ,
STG.agent_id ,
STG.agent_name ,
STG.opco ,
STG.vendor_id ,
STG.vendor_number ,
STG.vendor_name ,
STG.vendor_site_code ,
STG.vendor_site_id ,
STG.ship_to_location ,
STG.bill_to_location ,
STG.bill_to_location_id ,
STG.ship_to_location_id ,
STG.approval_status ,
STG.reference_num
FROM xxsspo_po_headers_stg STG
WHERE STG.data_set_id = gn_data_set_id
AND STG.status = gv_v
AND STG.batch_id = cp_batch_id;
CURSOR lines_cur ( cp_data_set_id xxsspo_po_headers_stg.data_set_id%TYPE ,cp_po_number xxsspo_po_headers_stg.po_number%TYPE )
IS
SELECT STG.interface_line_id ,
STG.interface_header_id ,
STG.line_num ,
STG.item_id ,
STG.item ,
STG.item_description ,
STG.quantity_ordered ,
STG.unit_price ,
STG.unit_of_measure ,
STG.uom_code ,
STG.line_type ,
STG.need_by_date ,
STG.promised_date ,
STG.inspection_required_flag ,
STG.receipt_required_flag ,
STG.shipment_type ,
stg.quantity_delivered
FROM xxsspo_po_lines_stg STG
WHERE STG.data_set_id = cp_data_set_id
AND STG.po_number = cp_po_number
AND STG.status = gv_v;
CURSOR dist_cur -- Separate cursor to enable parallel processing of batches
( cp_data_set_id xxsspo_po_headers_stg.data_set_id%TYPE ,cp_po_number xxsspo_po_headers_stg.po_number%TYPE ,cp_line_num xxsspo_po_lines_stg.line_num%TYPE )
IS
SELECT STG.record_id ,
STG.interface_distribution_id ,
STG.CHARGE_ACCOUNT_ID ,
STG.DISTRIBUTION_NUM ,
STG.DESTINATION_TYPE
FROM xxsspo_po_lines_stg STG
WHERE STG.data_set_id = gn_data_set_id
AND STG.po_number = cp_po_number
AND stg.line_num = cp_line_num
AND STG.status = gv_v;
BEGIN
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message ('Start OF Transfer record program for inserting in interface and base tables');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
pn_out_status := gn_conc_success; -- Setting the variable pn_out_status to zero
pv_out_error_message := NULL; -- Setting the variable pv_out_error_message to NULL
FOR count_rec IN lcur_count -- Separate cursor to enable parallel processing of batches
LOOP
FOR hdr_rec IN hdr_cur (count_rec.batch_id)
LOOP
lv_func_currency := NULL;
BEGIN
SELECT gl.currency_code
INTO lv_func_currency
FROM hr_operating_units ood ,
gl_ledgers gl
WHERE ood.ORGANIZATION_ID= hdr_rec.opco
AND ood.SET_OF_BOOKS_ID = gl.ledger_id;
EXCEPTION
WHEN OTHERS THEN
lv_func_currency := NULL;
END;
BEGIN
INSERT
INTO PO_HEADERS_INTERFACE
(
INTERFACE_HEADER_ID ,
BATCH_ID ,
ACTION ,
ORG_ID ,
DOCUMENT_TYPE_CODE ,
DOCUMENT_NUM ,
CURRENCY_CODE ,
RATE_TYPE ,
RATE_DATE ,
RATE
-- ,AGENT_NAME
,
AGENT_ID ,
VENDOR_NAME ,
VENDOR_ID ,
VENDOR_SITE_CODE ,
VENDOR_SITE_ID ,
SHIP_TO_LOCATION ,
BILL_TO_LOCATION ,
SHIP_TO_LOCATION_ID ,
BILL_TO_LOCATION_ID ,
PAYMENT_TERMS ,
TERMS_ID ,
FREIGHT_CARRIER ,
FOB ,
FREIGHT_TERMS ,
APPROVAL_STATUS ,
CREATION_DATE ,
REFERENCE_NUM ,
VENDOR_NUM ,
ATTRIBUTE1 -- to store legacy PO# -- Added on 30-Jun
)
VALUES
(
apps.po_headers_interface_s.NEXTVAL ,
hdr_rec.batch_id ,
hdr_rec.action ,
hdr_rec.opco ,
hdr_rec.document_type_code ,
hdr_rec.po_number ,
hdr_rec.currency_code ,
DECODE(lv_func_currency, hdr_rec.currency_code,NULL, hdr_rec.rate_type) ,
DECODE(lv_func_currency, hdr_rec.currency_code,NULL, hdr_rec.exchange_rate_date) ,
DECODE(lv_func_currency, hdr_rec.currency_code,NULL, hdr_rec.exchange_rate)
-- ,hdr_rec.agent_name
,
hdr_rec.agent_id ,
hdr_rec.vendor_name ,
hdr_rec.vendor_id ,
hdr_rec.vendor_site_code ,
hdr_rec.vendor_site_id ,
hdr_rec.ship_to_location ,
hdr_rec.bill_to_location ,
hdr_rec.ship_to_location_id ,
hdr_rec.bill_to_location_id ,
hdr_rec.payment_terms ,
hdr_rec.terms_id ,
hdr_rec.freight_carrier ,
hdr_rec.fob_lookup_code ,
hdr_rec.freight_terms_lookup_code ,
hdr_rec.approval_status ,
hdr_rec.po_creation_date ,
hdr_rec.reference_num ,
hdr_rec.vendor_number ,
hdr_rec.po_number -- Added on 30-Jun
);
gn_hdr_interfaced_cnt := gn_hdr_interfaced_cnt+ SQL%ROWCOUNT;
FOR lines_rec IN lines_cur
(
gn_data_set_id,hdr_rec.po_number
)
LOOP
BEGIN
INSERT
INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID ,
INTERFACE_HEADER_ID ,
LINE_NUM ,
ITEM_ID ,
ITEM ,
ITEM_DESCRIPTION ,
quantity ,
UNIT_PRICE,
UOM_CODE ,
LINE_TYPE ,
NEED_BY_DATE ,
PROMISED_DATE ,
INSPECTION_REQUIRED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
SHIPMENT_TYPE
-- ,SHIP_TO_ORGANIZATION_ID -- this is picked from shipment detials of ship to location -- not required to add this AS STD PROGRAM ERRORS WITH Error: PO_PDOI_TXN_FLOW_API_ERROR (COLUMN_NAME=SHIP_TO_ORGANIZATION_CODE ) (VALUE=)
)
VALUES
(
apps.po_lines_interface_s.NEXTVAL ,
apps.po_headers_interface_s.CURRVAL ,
lines_rec.line_num ,
lines_rec.item_id ,
lines_rec.ITEM ,
lines_rec.ITEM_DESCRIPTION ,
lines_rec.quantity_ordered ,
lines_rec.UNIT_PRICE ,
lines_rec.UNIT_OF_MEASURE,
lines_rec.LINE_TYPE ,
lines_rec.NEED_BY_DATE ,
lines_rec.PROMISED_DATE ,
lines_rec.INSPECTION_REQUIRED_FLAG ,
lines_rec.RECEIPT_REQUIRED_FLAG ,
lines_rec.SHIPMENT_TYPE
-- ,hdr_rec.opco -- commented as it is picked by std program.
);
gn_lines_interfaced_cnt := gn_lines_interfaced_cnt+ SQL%ROWCOUNT;
FOR dist_rec IN dist_cur
(
gn_data_set_id,hdr_rec.po_number,lines_rec.line_num
)
LOOP
BEGIN
INSERT
INTO PO_DISTRIBUTIONS_INTERFACE
(
INTERFACE_HEADER_ID ,
INTERFACE_LINE_ID ,
INTERFACE_DISTRIBUTION_ID ,
CHARGE_ACCOUNT_ID ,
DISTRIBUTION_NUM ,
DESTINATION_TYPE ,
quantity_ordered ,
quantity_delivered
-- ,destination_type
)
VALUES
(
apps.po_headers_interface_s.CURRVAL ,
apps.po_lines_interface_s.CURRVAL ,
apps.po_distributions_interface_s.NEXTVAL
-- ,28015 -- veena
,
DECODE(dist_rec.DESTINATION_TYPE,'Inventory',NULL,dist_rec.CHARGE_ACCOUNT_ID) ,
dist_rec.DISTRIBUTION_NUM ,
dist_rec.DESTINATION_TYPE ,
lines_rec.quantity_ordered ,
lines_rec.quantity_delivered
--,DECODE(lines_rec.ITEM,NULL,'Expense','Inventory')
);
gn_dist_interfaced_cnt := gn_dist_interfaced_cnt+ SQL%ROWCOUNT;
-- Updating Staging table with errors at the end of interface table insertion
UPDATE xxsspo_po_headers_stg XAIHC
SET XAIHC.request_id = gn_conc_request_id ,
XAIHC.last_updated_by = gn_user_id ,
XAIHC.last_update_date = gd_date ,
XAIHC.last_update_login = gn_login_id ,
XAIHC.creation_date = gd_date ,
XAIHC.created_by = gn_user_id ,
XAIHC.status = gv_i ,
XAIHC.error_message = NULL ,
XAIHC.INTERFACE_HEADER_ID = apps.po_headers_interface_s.CURRVAL
WHERE XAIHC.po_number = Hdr_rec.po_number
AND XAIHC.data_set_id = gn_data_set_id;
UPDATE xxsspo_po_lines_stg XAILC
SET XAILC.request_id = gn_conc_request_id ,
XAILC.last_updated_by = gn_user_id ,
XAILC.last_update_date = gd_date ,
XAILC.last_update_login = gn_login_id ,
XAILC.creation_date = gd_date ,
XAILC.created_by = gn_user_id ,
XAILC.status = gv_i ,
XAILC.error_message = NULL ,
XAILC.INTERFACE_HEADER_ID = apps.po_headers_interface_s.CURRVAL ,
XAILC.INTERFACE_LINE_ID = apps.po_lines_interface_s.CURRVAL ,
XAILC.INTERFACE_DISTRIBUTION_ID = apps.po_distributions_interface_s.CURRVAL
WHERE XAILC.po_number = Hdr_rec.po_number
AND XAILC.line_num = lines_rec.line_num
AND XAILC.data_set_id = gn_data_set_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
gn_tl_dist_interf_err_cnt := gn_tl_dist_interf_err_cnt+ SQL%ROWCOUNT;
ROLLBACK;
lv_error_message := 'Insertion into purchase order distribution interface table fails';
lv_status := gv_ie;
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => NULL ,pn_in_record_id => dist_rec.record_id ,pv_in_po_number => hdr_rec.po_number ,pv_in_line_num => lines_rec.line_num ,pv_in_status => gv_ie );
-- Writing trace/ output/ log message
xxssdm_fnd_common_pkg.write_trace_message ('Error occurred while inserting the record into purchase order distribution interface table ' );
xxssdm_fnd_common_pkg.write_trace_message (SQLCODE || '-' || SUBSTR(SQLERRM,1,200));
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
gn_tl_lines_interf_err_cnt := gn_tl_lines_interf_err_cnt+ SQL%ROWCOUNT;
ROLLBACK;
lv_error_message := 'Insertion into purchase order lines interface table fails for purchase order ' ||hdr_rec.po_number ||' line num '||lines_rec.line_num||' error - '||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 );
lv_status := gv_ie;
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => NULL ,pn_in_record_id => NULL ,pv_in_po_number => hdr_rec.po_number ,pv_in_line_num => lines_rec.line_num ,pv_in_status => gv_ie );
-- Writing trace/ output/ log message
xxssdm_fnd_common_pkg.write_trace_message (lv_error_message );
xxssdm_fnd_common_pkg.write_trace_message (SQLCODE || '-' || SUBSTR(SQLERRM,1,200));
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
gn_tl_hdr_interf_err_cnt := gn_tl_hdr_interf_err_cnt+ SQL%ROWCOUNT;
ROLLBACK;
lv_error_message := 'Insertion failed for PO header'||hdr_rec.po_number ||' with error '|| SUBSTR(SQLERRM,1,200);
lv_status := gv_ie;
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => NULL ,pn_in_record_id => NULL ,pv_in_po_number => hdr_rec.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_ie );
-- Updating Staging table status flag
UPDATE xxsspo_po_headers_stg
SET status = gv_ie,
error_message = lv_error_message
WHERE po_number = hdr_rec.po_number
AND data_set_id = gn_data_set_id;
-- Writing trace/ output/ log message
xxssdm_fnd_common_pkg.write_trace_message ('Error occurred while inserting the record into purchase order header interface table');
xxssdm_fnd_common_pkg.write_trace_message (SQLCODE || '-' || SUBSTR(SQLERRM,1,200));
END;
END LOOP;
END LOOP;
COMMIT;
-- UPDATING STAGING TABLE PROCESS FLAG AND ERROR MESSAGE COLUMNS
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message ('Records successfully inserted in Interface tables');
xxssdm_fnd_common_pkg.write_trace_message ('Total Header Records '|| gn_hdr_interfaced_cnt);
xxssdm_fnd_common_pkg.write_trace_message ('Total Lines Records '|| gn_lines_interfaced_cnt);
xxssdm_fnd_common_pkg.write_trace_message ('Total distributions Records '|| gn_dist_interfaced_cnt);
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
-- Calling the Standard program 'Purchase order import program for inserting interface table data INTO base tables'
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message ('Calling Purchase order import program for inserting interface table data INTO base tables');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
IF gn_hdr_interfaced_cnt > 0 THEN
FOR rec_po_import IN cr_po_import
LOOP
ln_request_id := NULL;
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'In po import standard program ' ,pv_in_debug_flag => gv_debug_flag );
--Submit Import standard purchase order program
BEGIN
xxssdm_fnd_common_pkg.submit_conc_request_nowait ( errbuf => pv_out_error_message
,retcode => pn_out_status
,pn_out_child_request_id => gn_child_req_id
,pv_out_child_pgm_name => lv_child_pgm_name
,pv_in_application_top => gv_app_short_name -- application
,pv_in_request_short_name => gv_conc_prog_name -- program
,pv_in_request_description => 'Import Standard Purchase Orders' -- description
--,start_time => NULL,
--,sub_request => FALSE,
,pv_in_parameter1 => NULL -- Default Buyer
,pv_in_parameter2 => 'STANDARD' --Document Type
,pv_in_parameter3 => NULL --Document SubType
,pv_in_parameter4 => 'N' --Create or Update Items ( Made N because we are importing )
,pv_in_parameter5 => NULL --Create Sourcing Rules
,pv_in_parameter6 => 'APPROVED' --Approval Status
,pv_in_parameter7 => NULL ,pv_in_parameter8 => rec_po_import.batch_id --Batch Id
,pv_in_parameter9 => rec_po_import.opco --Operating Unit
,pv_in_parameter10 => NULL ,pv_in_parameter11 => NULL ,pv_in_parameter12 => NULL ,pv_in_parameter13 => NULL ,pv_in_parameter14 => NULL ,pv_in_parameter15 => 'N' ,pv_in_parameter16 => 'N' ,pv_in_parameter17 => 5000 ,pv_in_parameter18 => 'N' );
ln_request_id := gn_child_req_id;
IF ln_request_id = 0 THEN
xxssdm_fnd_common_pkg.write_trace_message('The Concurrent Manager was unable to submit the standard PO');
ELSE
ln_num_index := ln_num_index + 1;
g_request_id_tbl (ln_num_index).request_id := ln_request_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
pn_out_status := gn_conc_warning;
xxssdm_fnd_common_pkg.write_trace_message( 'Error in submit Process transaction interface program: ' || SQLERRM || ' Request id: ' || ln_request_id);
END;
COMMIT;
END LOOP;
xxssdm_fnd_common_pkg.write_trace_message( ' Number of requests submitted ' || g_request_id_tbl.COUNT);
--Loop to wait for all submitted requests
IF g_request_id_tbl.COUNT > 0 THEN
FOR i IN g_request_id_tbl.FIRST .. g_request_id_tbl.LAST
LOOP
lv_chr_dev_phase := NULL;
lv_chr_dev_status := NULL;
xxssdm_fnd_common_pkg.write_trace_message( ' Waiting for Request ID ' || g_request_id_tbl (i).request_id );
lv_boo_wait_hold := fnd_concurrent.wait_for_request ( g_request_id_tbl (i).request_id ,2 ,60000 ,lv_chr_phase ,lv_chr_status ,lv_chr_dev_phase ,lv_chr_dev_status ,lv_chr_message_ji );
xxssdm_fnd_common_pkg.write_trace_message( ' Concurrent Request ID - ' || g_request_id_tbl (i).request_id || ': Phase ' || lv_chr_dev_phase || ' and Status ' || lv_chr_dev_status );
xxssdm_fnd_common_pkg.write_trace_message( 'ln_num_wait_request_count : ' || ln_num_wait_request_count );
xxssdm_fnd_common_pkg.write_trace_message( 'g_request_id_tbl.COUNT : ' || g_request_id_tbl.COUNT );
IF lv_chr_dev_phase = 'COMPLETE' THEN
xxssdm_fnd_common_pkg.write_trace_message( 'ln_num_wait_request_count' || ln_num_wait_request_count);
xxssdm_fnd_common_pkg.write_trace_message( 'The Import PO Program has completed successfully for request_id: ' || g_request_id_tbl (i).request_id);
ln_num_complete_request_count := ln_num_complete_request_count + 1;
END IF;
END LOOP;
COMMIT;
END IF;
END IF;
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_trace_message ('END OF Transfer record program');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+' );
xxssdm_fnd_common_pkg.write_output_message('Transfer_record Successfully Completed');
-- Update status to PE
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID,
b.ERROR_MESSAGE
FROM PO_HEADERS_INTERFACE a,
PO_INTERFACE_ERRORS b
WHERE a.INTERFACE_HEADER_ID = b.INTERFACE_HEADER_ID
AND a.BATCH_ID = b.BATCH_ID
AND a.PROCESS_CODE = 'REJECTED'
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE stg.batch_id = a.batch_id
AND stg.data_set_id = gn_data_set_id
AND stg.interface_header_id= a.interface_header_id
)
)
LOOP
UPDATE xxsspo_po_headers_stg stg
SET stg.status = gv_pe ,
stg.error_message = I.error_message
||'|'
||stg.error_message
WHERE stg.po_number = I.po_number
AND data_set_id = gn_data_set_id
--AND status <> 'I'
;
update_error ( pv_in_error_message => i.error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => NULL ,pv_in_po_number => i.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_pe );
END LOOP;
COMMIT;
FOR I IN
(SELECT a.DOCUMENT_NUM,
a.LINE_NUM line_num,
a.ITEM,
a.ITEM_DESCRIPTION,
b.ERROR_MESSAGE
FROM PO_LINES_INTERFACE a,
PO_INTERFACE_ERRORS b
WHERE a.INTERFACE_HEADER_ID = b.INTERFACE_HEADER_ID
AND a.INTERFACE_LINE_ID = b.INTERFACE_LINE_ID
AND a.PROCESS_CODE = 'REJECTED'
AND (a.line_NUM, a.document_num) IN
(SELECT line_num,
po_number
FROM xxsspo_po_lines_stg stg
WHERE stg.data_set_id = gn_data_set_id
AND stg.interface_line_id = a.interface_line_id
AND stg.interface_header_id = a.interface_header_id
)
AND a.request_id= gn_child_req_id
)
LOOP
UPDATE xxsspo_po_lines_stg stg
SET stg.status = gv_pe ,
stg.error_message = I.error_message
||'|'
||stg.error_message
WHERE stg.line_num = I.line_num
AND stg.po_number = i.document_num
AND data_set_id = gn_data_set_id ;
update_error ( pv_in_error_message => i.error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => NULL ,pv_in_po_number => NULL ,pv_in_line_num => i.line_num ,pv_in_status => gv_pe );
END LOOP;
COMMIT;
-- Update status to P
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID
FROM PO_HEADERS_INTERFACE a
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE data_set_id = gn_data_set_id
AND a.batch_id = stg.batch_id
AND a.interface_header_id= stg.interface_header_id
)
)
LOOP
UPDATE xxsspo_po_headers_stg stg
SET stg.status = gv_p
--,stg.error_message = I.error_message||'|'||stg.error_message
WHERE stg.po_number = I.po_number
AND data_set_id = gn_data_set_id;
-- AND status <> 'I'
END LOOP;
COMMIT;
FOR I IN
(SELECT b.line_num,
a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID
FROM PO_HEADERS_INTERFACE a ,
po_lines_interface b
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND b.interface_header_id = a.interface_header_id
AND ( a.DOCUMENT_NUM, b.line_num ) IN
(SELECT stgl.po_number,
line_num
FROM xxsspo_po_lines_stg stgl,
xxsspo_po_headers_stg stgh
WHERE stgh.data_set_id = gn_data_set_id
AND stgh.po_number =stgl.po_number
AND a.batch_id = stgh.batch_id
AND a.interface_header_id= stgh.interface_header_id
AND b.interface_line_id = stgl.interface_line_id
)
)
LOOP
UPDATE xxsspo_po_lines_stg stg
SET stg.status = gv_p
-- ,stg.error_message = I.error_message||'|'||stg.error_message
WHERE stg.line_num = I.line_num
AND stg.po_number = I.po_number
AND data_set_id = gn_data_set_id
--AND status <> 'I'
;
END LOOP;
COMMIT;
-- Receiving code
-- vts begin
BEGIN
lv_sub_inv := 'STORE'; -- default
SELECT flv.attribute1 -- STORE
INTO lv_sub_inv
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'XX_INV_SUBINVENTORY_CODE'
AND flv.lookup_code = 'A' -- available
AND sysdate BETWEEN flv.start_Date_active AND NVL(flv.end_date_active,sysdate+1)
AND flv.enabled_flag = 'Y'
AND flv.language = userenv('LANG');
xxssdm_fnd_common_pkg.write_trace_message (' subinventory_code '|| lv_sub_inv);
EXCEPTION
WHEN OTHERS THEN
lv_error_message := 'Error while fetching subinventory_code from lookup XX_INV_SUBINVENTORY_CODE '|| SUBSTR(SQLERRM,1,200);
lv_status := gv_ie;
lv_error_flag := 'Y';
update_error ( pv_in_error_message => lv_error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => NULL ,pn_in_record_id => NULL ,pv_in_po_number => NULL ,pv_in_line_num => NULL ,pv_in_status => gv_ie );
END;
FOR rec_receive_hdr_import IN cr_receive_hdr_import
LOOP
xxssdm_fnd_common_pkg.write_trace_message ('inserting into rcv_headers_interface ');

SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO ln_rcv_header_id
FROM DUAL;
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO ln_rcv_group_id FROM DUAL;
ln_rcv_count:=0;
SELECT COUNT(1)
INTO ln_rcv_count
FROM XXSSPO_PO_LINES_STG
WHERE po_number =rec_receive_hdr_import.segment1
AND NVL(quantity_delivered,0) > 0
AND data_Set_id = gn_data_set_id;
xxssdm_fnd_common_pkg.write_trace_message('ln_rcv_count '||ln_rcv_count|| ' if it is 0 then rec and mis issue would not be processed');
IF ln_rcv_count > 0 THEN -- lines with delivered_quantity exists
BEGIN

INSERT
INTO rcv_headers_interface (header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
expected_receipt_date,
validation_flag,
comments
)
VALUES (ln_rcv_header_id,
ln_rcv_group_id,
'PENDING',
'VENDOR',
'NEW',
SYSDATE,
gn_user_id,
gn_user_id,
rec_receive_hdr_import.vendor_id ,
SYSDATE,
'Y',
'RECEIVING FROM CONVERSION'
);
gn_rcv_hdr_interfaced_cnt := gn_rcv_hdr_interfaced_cnt+ SQL%ROWCOUNT;
FOR rec_receive_tran_import IN cr_receive_tran_import
(
rec_receive_hdr_import.po_header_id
)
LOOP
BEGIN
SELECT rcv_transactions_interface_s.NEXTVAL
INTO ln_rcv_transaction_id
FROM DUAL;
xxssdm_fnd_common_pkg.write_trace_message ('inserting into rcv_transactions_interface ');
xxssdm_fnd_common_pkg.write_trace_message ('inserting into rcv_transactions_interface po_header_id '|| rec_receive_tran_import.po_header_id);
INSERT
INTO rcv_transactions_interface (
interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
quantity,
po_line_location_id,
po_distribution_id,
auto_transact_code,
receipt_source_code,
to_organization_id,
source_document_code,
subinventory,
header_interface_id,
destination_type_code,
validation_flag)
VALUES (rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL,
SYSDATE,
gn_user_id,
SYSDATE,
gn_user_id,
gn_login_id,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
rec_receive_tran_import.po_header_id,
rec_receive_tran_import.po_line_id,
rec_receive_tran_import.quantity_delivered,
rec_receive_tran_import.line_location_id,
rec_receive_tran_import.po_distribution_id,
'DELIVER',
'VENDOR',
rec_receive_tran_import.ship_to_organization_id ,
'PO',
lv_sub_inv,
rcv_headers_interface_s.CURRVAL,
'INVENTORY', --DESTINATION_TYPE_CODE
'Y');
gn_rcv_lines_interfaced_cnt := gn_rcv_lines_interfaced_cnt+ SQL%ROWCOUNT;
-- Updating Staging table with errors at the end of interface table insertion
UPDATE xxsspo_po_headers_stg XAIHC
SET XAIHC.rcv_status = gv_i ,
XAIHC.rcv_header_interface_id = ln_rcv_header_id
WHERE XAIHC.po_number = rec_receive_hdr_import.po_number
AND XAIHC.data_set_id = gn_data_set_id;
UPDATE xxsspo_po_lines_stg XAILC
SET XAILC.rcv_status = gv_i ,
XAILC.rcv_header_interface_id = ln_rcv_header_id ,
XAILC.rcv_interface_tran_id = ln_rcv_transaction_id
WHERE XAILC.po_number = rec_receive_hdr_import.po_number
AND XAILC.interface_header_id = rec_receive_tran_import.interface_header_id
AND XAILC.interface_line_id = rec_receive_tran_import.interface_line_id
AND XAILC.data_set_id = gn_data_set_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
gn_tl_rcv_lines_interf_err_cnt := gn_tl_rcv_lines_interf_err_cnt+ SQL%ROWCOUNT;
ROLLBACK;
lv_error_message := 'Insertion into RCV_TRANSACTIONS_INTERFACE table fails';
lv_status := gv_ie;
lv_error_flag := 'Y';
-- Writing trace/ output/ log message
xxssdm_fnd_common_pkg.write_trace_message ('Error occurred while inserting the record into RCV_TRANSACTIONS_INTERFACEtable ' );
xxssdm_fnd_common_pkg.write_trace_message (SQLCODE || '-' || SUBSTR(SQLERRM,1,200));
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
gn_tl_rcv_hdr_interf_err_cnt := gn_tl_rcv_hdr_interf_err_cnt+ SQL%ROWCOUNT;
ROLLBACK;
lv_error_message := 'Insertion into rcv_headers_interface table fails';
lv_status := gv_ie;
lv_error_flag := 'Y';
-- Writing trace/ output/ log message
xxssdm_fnd_common_pkg.write_trace_message ('Error occurred while inserting the record into rcv_headers_interface table ' );
xxssdm_fnd_common_pkg.write_trace_message (SQLCODE || '-' || SUBSTR(SQLERRM,1,200));
END;
-- UPDATING STAGING TABLE PROCESS FLAG AND ERROR MESSAGE COLUMNS
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message ('Records successfully inserted in Recipt Interface tables');
xxssdm_fnd_common_pkg.write_trace_message ('Total RCV Header Records '|| gn_rcv_hdr_interfaced_cnt);
xxssdm_fnd_common_pkg.write_trace_message ('Total RCV Lines Records '|| gn_rcv_lines_interfaced_cnt);
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
-- Calling the Standard program 'Purchase order import program for inserting interface table data INTO base tables'
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
xxssdm_fnd_common_pkg.write_trace_message ('Calling Receiving Transaction Processor program for inserting interface table data INTO base tables');
xxssdm_fnd_common_pkg.write_trace_message ('+---------------------------------------------------------------------------+');
-- submit request
ln_request_id := NULL;
xxssdm_fnd_common_pkg.write_debug_message ( pv_in_debug_message => 'Submitting Receiving Transaction Processor program ' ,pv_in_debug_flag => gv_debug_flag );

--Submit Receiving Transaction Processor program
BEGIN
xxssdm_fnd_common_pkg.submit_conc_request_nowait ( errbuf => pv_out_error_message
,retcode => pn_out_status
,pn_out_child_request_id => gn_child_req_id
,pv_out_child_pgm_name => lv_child_pgm_name
,pv_in_application_top => gv_app_short_name -- application
,pv_in_request_short_name => 'RVCTP' --gv_conc_prog_name -- program
,pv_in_request_description => 'Receiving Transaction Processor' -- description
--,start_time => NULL,
--,sub_request => FALSE,
,pv_in_parameter1 => 'BATCH' ,pv_in_parameter2 => ln_rcv_group_id -- Group id
-- ,pv_in_parameter3 => rec_receive_hdr_import.org_id -- org_id of operating unit -- not processing if we pass this 11-jan-17
);
ln_request_id := gn_child_req_id;
ln_num_index := 0;
IF ln_request_id = 0 THEN
xxssdm_fnd_common_pkg.write_trace_message('The Concurrent Manager was unable to submit the standard RVCTP');
ELSE
ln_num_index := ln_num_index + 1;
g_request_id_tbl (ln_num_index).request_id := ln_request_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
pn_out_status := gn_conc_warning;
xxssdm_fnd_common_pkg.write_trace_message( 'Error in submit Receiving Transaction Processor program: ' || SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) || ' Request id: ' || ln_request_id);
END;
COMMIT;
END IF;
END LOOP;
xxssdm_fnd_common_pkg.write_trace_message( ' Number of requests submitted ' || g_request_id_tbl.COUNT);
--Loop to wait for all submitted requests
IF g_request_id_tbl.COUNT > 0 THEN
FOR i IN g_request_id_tbl.FIRST .. g_request_id_tbl.LAST
LOOP
lv_chr_dev_phase := NULL;
lv_chr_dev_status := NULL;
xxssdm_fnd_common_pkg.write_trace_message( ' Waiting for Request ID ' || g_request_id_tbl (i).request_id );
lv_boo_wait_hold := fnd_concurrent.wait_for_request ( g_request_id_tbl (i).request_id ,2 ,60000 ,lv_chr_phase ,lv_chr_status ,lv_chr_dev_phase ,lv_chr_dev_status ,lv_chr_message_ji );
xxssdm_fnd_common_pkg.write_trace_message( ' Concurrent Request ID - ' || g_request_id_tbl (i).request_id || ': Phase ' || lv_chr_dev_phase || ' and Status ' || lv_chr_dev_status );
xxssdm_fnd_common_pkg.write_trace_message( 'ln_num_wait_request_count : ' || ln_num_wait_request_count );
xxssdm_fnd_common_pkg.write_trace_message( 'g_request_id_tbl.COUNT : ' || g_request_id_tbl.COUNT );
IF lv_chr_dev_phase = 'COMPLETE' THEN
xxssdm_fnd_common_pkg.write_trace_message( 'ln_num_wait_request_count' || ln_num_wait_request_count);
xxssdm_fnd_common_pkg.write_trace_message( 'The Receiving Transaction Processor has completed successfully for request_id: ' || g_request_id_tbl (i).request_id);
ln_num_complete_request_count := ln_num_complete_request_count + 1;
END IF;
END LOOP;
END IF;
COMMIT;
--update for errored rows receive_status with PE.
IF ln_rcv_count > 0 THEN -- check rows inserted into recieving interface tables
-- Update status to PE
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID
FROM PO_HEADERS_INTERFACE a
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE data_set_id = gn_data_set_id
AND a.batch_id = stg.batch_id
AND a.interface_header_id= stg.interface_header_id
AND EXISTS
(SELECT 1
FROM rcv_headers_interface
WHERE header_interface_id IN
(SELECT header_interface_id
FROM rcv_transactions_interface
WHERE PO_HEADER_ID = a.po_header_id
AND processing_status_code = 'ERROR'
)
)
)
)
LOOP
UPDATE xxsspo_po_headers_stg stg
SET stg.rcv_status = gv_pe ,
stg.status = gv_pe ,
stg.error_message = 'Receiving error '
||'|'
||stg.error_message
WHERE stg.po_number = I.po_number
AND data_set_id = gn_data_set_id;
update_error ( pv_in_error_message =>'Receiving error' ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => gv_header_tabname ,pn_in_record_id => NULL ,pv_in_po_number => i.po_number ,pv_in_line_num => NULL ,pv_in_status => gv_pe );
END LOOP;
COMMIT;
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID ,
b.interface_header_id,
b.interface_line_id,
c.error_message,
b.line_num
FROM PO_HEADERS_INTERFACE a ,
po_interface_errors c,
po_lines_interface b
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND a.interface_header_id = b.interface_header_id
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE data_set_id = gn_data_set_id
AND a.batch_id = stg.batch_id
AND a.interface_header_id= stg.interface_header_id
AND c.request_id =
(SELECT processing_request_id
FROM rcv_headers_interface
WHERE header_interface_id IN
(SELECT header_interface_id
FROM rcv_transactions_interface
WHERE PO_HEADER_ID = a.po_header_id
AND processing_status_code = 'ERROR'
)
)
)
)
LOOP
UPDATE xxsspo_po_lines_stg stg
SET stg.rcv_status = gv_pe ,
stg.status = gv_pe ,
stg.error_message = substr(I.error_message,1,100)
||'|'
||stg.error_message
WHERE stg.po_number = I.po_number
AND stg.interface_line_id = i.interface_line_id
AND stg.interface_header_id = i.interface_header_id
AND data_set_id = gn_data_set_id;
update_error ( pv_in_error_message => i.error_message ,pv_in_error_col_name => NULL ,pv_in_error_col_value => NULL ,pv_in_tabname => gv_lines_tabname ,pn_in_record_id => NULL ,pv_in_po_number => NULL ,pv_in_line_num => i.line_num ,pv_in_status => gv_pe );
END LOOP;
COMMIT;
-- Update status to P
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID,
a.interface_header_id
FROM PO_HEADERS_INTERFACE a
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE data_set_id = gn_data_set_id
AND a.batch_id = stg.batch_id
AND a.interface_header_id= stg.interface_header_id
AND EXISTS
(SELECT 1
FROM rcv_headers_interface
WHERE 1 = 1
AND NOT EXISTS
(SELECT header_interface_id
FROM rcv_transactions_interface
WHERE PO_HEADER_ID = a.po_header_id
)
)
)
)
LOOP
UPDATE xxsspo_po_headers_stg stg
SET stg.rcv_status = gv_p,
stg.status = gv_p ,
stg.error_message = NULL
WHERE stg.po_number = I.po_number
AND stg.interface_header_id = I.interface_header_id
AND data_set_id = gn_data_set_id;
END LOOP;
COMMIT;
FOR I IN
(SELECT a.PO_HEADER_ID,
a.DOCUMENT_NUM po_number,
a.ORG_ID,
a.VENDOR_NAME,
a.VENDOR_ID,
stg.interface_header_id,
stg.interface_line_id
FROM PO_HEADERS_INTERFACE a ,
po_lines_interface b,
xxsspo_po_lines_stg stg
WHERE 1 =1
AND a.PROCESS_CODE = 'ACCEPTED'
AND a.interface_header_id = stg.interface_header_id
AND b.interface_header_id = stg.interface_header_id
AND b.interface_line_id = stg.interface_line_id
AND a.DOCUMENT_NUM IN
(SELECT po_number
FROM xxsspo_po_headers_stg stg
WHERE data_set_id = gn_data_set_id
AND a.batch_id = stg.batch_id
AND a.interface_header_id= stg.interface_header_id
AND EXISTS
(SELECT 1
FROM rcv_headers_interface
WHERE header_interface_id in
(SELECT header_interface_id
FROM rcv_transactions
WHERE PO_HEADER_ID = a.po_header_id
AND po_line_id = b.po_line_id
)
)
)
)
LOOP
UPDATE xxsspo_po_lines_stg stg
SET stg.rcv_status = gv_p ,
stg.status = gv_p
WHERE stg.interface_header_id = I.interface_header_id
AND stg.interface_line_id = I.interface_line_id
AND stg.po_number = I.po_number
AND data_set_id = gn_data_set_id ;
END LOOP;
COMMIT;
-- Miscllaneous issue reversal for po receipts created from our program
DECLARE
ln_type_id NUMBER;
ln_source_type_id NUMBER;
lv_tmp_error_flag VARCHAR2(10);
ln_transaction_id NUMBER;
CURSOR lcur_receipts
IS
SELECT stg.record_id,
rt.po_unit_price,
rt.organization_id,
rt.subinventory,
rt.uom_code,
rsl.item_id,
rsl.quantity_received,
rsl.charge_Account_id,
stg.data_set_id,
stg.interface_header_id
FROM RCV_SHIPMENT_LINES rsl,
rcv_transactions rt ,
xxsspo_po_headers_stg STG,
po_headers_all poh ,
po_headers_interface a
WHERE rsl.po_header_id = poh.po_header_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND STG.data_set_id = gn_data_set_id -- 18
AND STG.status = 'P'
AND a.interface_header_id = stg.interface_header_id
-- and stg.interface_header_id = 2012-- comment this later
AND a.po_header_id = poh.po_header_id
AND rt.po_header_id = rsl.po_header_id
AND rt.po_line_id = rsl.po_line_id
AND rt.destination_type_code = 'INVENTORY';
BEGIN
--Validating if transaction type defined for the record exists .
lv_tmp_error_flag := 'N';
xxssdm_fnd_common_pkg.write_trace_message ( 'Validating Transaction type ' );
xxssdm_fnd_common_pkg.validate_transaction_type( pv_in_type => 'Miscellaneous issue' -- hardcoded for SWL
,pn_out_type_id => ln_type_id ,pn_out_source_type_id => ln_source_type_id ,pv_out_error_message => lv_error_message ,pv_out_error_flag => lv_tmp_error_flag );
IF lv_tmp_error_flag ='Y' THEN
lv_error_flag := 'Y';
lv_error_message :='Transaction Id doesnot exists for Transaction_type Miscellaneous issue ';
END IF;
FOR lrec_receipts IN lcur_receipts
LOOP
SELECT mtl_material_transactions_s.NEXTVAL INTO ln_transaction_id FROM DUAL;
lv_error_flag :='N';
xxssdm_fnd_common_pkg.write_trace_message ( 'Inserting data into mtl_transactions_interface Table for record id '||lrec_receipts.record_id );
BEGIN
INSERT
INTO mtl_transactions_interface
(
transaction_interface_id ,
transaction_header_id ,
transaction_type_id ,
transaction_source_type_id ,
source_code ,
organization_id ,
transaction_reference ,
transaction_quantity ,
transaction_uom ,
transaction_date ,
source_header_id ,
source_line_id ,
transaction_mode ,
process_flag ,
created_by ,
last_updated_by ,
last_update_date ,
creation_date ,
last_update_login ,
inventory_item_id ,
subinventory_code ,
transaction_source_id ,
distribution_account_id ,
lock_flag ,
transaction_cost
)
VALUES
(
ln_transaction_id ,
ln_transaction_id ,
ln_type_id ,
ln_source_type_id ,
'PO Receipts Reversal' ,
lrec_receipts.organization_id ,
lrec_receipts.record_id ,
lrec_receipts.quantity_received * (-1) , -- Transaction quantity has an incorrect sign for the given transaction action when passed +ve
lrec_receipts.uom_code ,
sysdate ,
1 ,
1 ,
3 ,
1 ,
gn_user_id ,
gn_user_id ,
sysdate ,
sysdate ,
gn_login_id ,
lrec_receipts.item_id ,
lrec_receipts.subinventory, -- ?
NULL ,
lrec_receipts.charge_Account_id, --12190 pod.code_combination_id
2 ,
lrec_receipts.po_unit_price --pol.unit_price
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
lv_error_flag :='Y';
pn_out_status := gn_conc_warning;
lv_error_message := 'Error while inserting record in mtl_transactions_interface interface table';
xxssdm_fnd_common_pkg.write_trace_message ('Error while entering records into mtl_transactions_interface table for record id: '||lrec_receipts.record_id);
xxssdm_fnd_common_pkg.write_trace_message('Error Message :'||substr(SQLERRM,1,200));
END;
--Updating Staging Table for records successfully transferred to Interface Table
BEGIN
UPDATE xxsspo_po_lines_stg stg
SET stg.attribute3 = ln_transaction_id ,
stg.last_update_date = gd_date ,
stg.last_updated_by = gn_user_id ,
stg.attribute4 = DECODE(lv_error_flag,'Y',gv_ie,gv_i) ,
stg.error_message = DECODE(lv_error_flag,'Y',lv_error_message,NULL)
WHERE interface_header_id IN
(SELECT interface_header_id
FROM xxsspo_po_headers_stg
WHERE record_id = lrec_receipts.record_id
AND interface_header_id = lrec_receipts.interface_header_id
AND data_set_id = lrec_receipts.data_set_id
)
AND data_set_id = lrec_receipts.data_set_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error while updating xxsspo_po_lines_stg table after validations for xxsspo_po_headers_stg.record id:'||lrec_receipts.record_id);
END;
BEGIN
UPDATE xxsspo_po_headers_stg stg
SET stg.last_update_date = gd_date ,
stg.last_updated_by = gn_user_id ,
stg.attribute4 = DECODE(lv_error_flag,'Y',gv_ie,gv_i) ,
stg.error_message = DECODE(lv_error_flag,'Y',lv_error_message,NULL)
WHERE record_id = lrec_receipts.record_id
AND data_set_id = lrec_receipts.data_set_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Error while updating xxsspo_po_headers_stg table after validations for record id:'||lrec_receipts.record_id);
END;
END LOOP;
-- code to submit the concurrent request here
--Calling Standard Program to transfer records to base table
BEGIN
xxssdm_fnd_common_pkg.submit_conc_request (errbuf => pv_out_error_message
,retcode => pn_out_status
,pn_out_child_request_id => gn_child_req_id
,pv_out_child_pgm_name => lv_child_pgm_name
,pv_in_application_top => 'INV'
,pv_in_request_short_name => 'INCTCM'
,pv_in_request_description => ' ' );
COMMIT;
xxssdm_fnd_common_pkg.write_trace_message ('+**************************************************************************+' );
xxssdm_fnd_common_pkg.write_trace_message ('Miscellaneous issue material transaction Standard Program completed Sucessfully.The request Id for the Concurrent Program Is '||gn_child_req_id);
xxssdm_fnd_common_pkg.write_trace_message ('+**************************************************************************+' );
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message ('Unexpected error while calling Standard load Program to load data in base tables' );
xxssdm_fnd_common_pkg.write_trace_message ( SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ) );
pv_out_error_message := SUBSTR (SQLERRM, 1,200);
pn_out_status := gn_conc_error;
END;
dbms_lock.sleep(120); --Added on 18 OCT
--Procedure to update staging table for the records updated in base table
update_status( pv_out_error_status_message => pv_out_error_message ,pn_out_update_status => pn_out_status);
END;
END IF; -- ln_rcv_count
-- Receiving code end
-- Counting number/percentage of records with processing error
SELECT COUNT (1)
INTO gn_tl_hdr_processing_err_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) = gv_pe;
SELECT COUNT (1)
INTO gn_tl_lines_processing_err_cnt
FROM xxsspo_po_lines_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) = gv_pe;
gn_tl_dist_processing_err_cnt := gn_tl_lines_processing_err_cnt;
SELECT COUNT (1)
INTO gn_tl_rcv_hdr_err_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.rcv_status) = gv_pe;
SELECT COUNT (1)
INTO gn_tl_rcv_lines_err_cnt
FROM xxsspo_po_lines_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.rcv_status) = gv_pe;
COMMIT;
-- Counting number/percentage of processed records
SELECT COUNT (1)
INTO gn_tl_hdr_processed_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) = gv_p;
SELECT COUNT (1)
INTO gn_tl_lines_processed_cnt
FROM xxsspo_po_lines_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) = gv_p;
gn_tl_dist_processed_cnt := gn_tl_lines_processed_cnt;
SELECT COUNT (1)
INTO gn_tl_rcv_hdr_processed_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.rcv_status) = gv_p
AND upper(XAIH.status) = gv_p ;
SELECT COUNT (1)
INTO gn_tl_rcv_lines_processed_cnt
FROM xxsspo_po_lines_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.rcv_status) = gv_p
AND upper(XAIH.status) = gv_p;
EXCEPTION
WHEN OTHERS THEN
pv_out_error_message := 'Exception error while loading data using Transfer_record procedure ';
pn_out_status := gn_conc_error;
-- xxssdm_fnd_common_pkg.write_output_message('Error: When Others at Transfer_record Program');
xxssdm_fnd_common_pkg.write_trace_message ('Unexpected error while calling Transfer_record program'||SQLCODE|| SUBSTR(SQLERRM,1,200));
END Transfer_record;
-- +============================================================================+
-- | Name: generate_report |
-- | |
-- | Description: This procedure will write report to output file |
-- | |
-- | Parameters: None |
-- | |
-- | Returns: pv_out_error_message - VARCHAR2 - Error Buffer |
-- | pn_out_status - VARCHAR2 - Return code of Error |
-- +============================================================================+
PROCEDURE generate_report(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT VARCHAR2 )
IS
BEGIN
pn_out_status := '0';
pv_out_error_message := NULL;
-- Number/Percentage header records loaded in Staging Table
SELECT COUNT (1)
INTO gn_tl_hdr_loaded_records
FROM XXSSPO_PO_HEADERS_STG XAIH
WHERE XAIH.data_set_id = gn_data_set_id;
-- Number/Percentage lines records loaded in Staging Table
SELECT COUNT (1)
INTO gn_tl_lines_loaded_records
FROM XXSSPO_PO_LINES_STG XAIL
WHERE XAIL.data_set_id = gn_data_set_id;
-- Number/Percentage Duplicate header records loaded in Staging Table
SELECT COUNT (1)
INTO gn_tl_hdr_duplicate_records
FROM XXSSPO_PO_HEADERS_STG XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND XAIH.Status = gv_dr;
-- Number/Percentage duplicate lines records loaded in Staging Table
SELECT COUNT (1)
INTO gn_tl_lines_duplicate_records
FROM XXSSPO_PO_LINES_STG XAIL
WHERE XAIL.data_set_id = gn_data_set_id
AND XAIL.Status = gv_dr;
/* Queries for generating Output file data */
-- Number/Percentage of records passed validations
SELECT COUNT (1)
INTO gn_tl_hdr_validated_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) IN (gv_v,gv_i,gv_ie,gv_p,gv_pe) ;
SELECT COUNT (1)
INTO gn_tl_lines_validated_cnt
FROM xxsspo_po_lines_stg XAIL
WHERE XAIL.data_set_id = gn_data_set_id
AND upper(XAIL.status) IN (gv_v,gv_i,gv_ie,gv_p,gv_pe);
-- Number/Percentage of records failed validations
SELECT COUNT (1)
INTO gn_tl_hdr_validation_err_cnt
FROM xxsspo_po_headers_stg XAIH
WHERE XAIH.data_set_id = gn_data_set_id
AND upper(XAIH.status) = gv_ve ;
SELECT COUNT(1)
INTO gn_tl_lines_validation_err_cnt
FROM
(SELECT po_number,
line_num
FROM xxsspo_po_lines_stg XAIL
WHERE XAIL.data_set_id = gn_data_set_id
AND upper(XAIL.status) = gv_ve
UNION ALL
SELECT po_number,
line_num
FROM xxsspo_po_lines_stg XAIL
WHERE XAIL.data_set_id = gn_data_set_id
AND upper(XAIL.status) <> gv_ve
AND EXISTS
(SELECT 1
FROM xxsspo_po_headers_stg XAIH
WHERE xaih.po_number= xail.po_number
AND xaih.data_set_id= xail.data_set_id
AND xaih.status = gv_ve
)
);
xxssdm_fnd_common_pkg.write_output_message ( 'Program Name :' || gv_program_name || ' Start Date : ' || TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) );
xxssdm_fnd_common_pkg.write_output_message (' ');
xxssdm_fnd_common_pkg.write_output_message (RPAD ('*', 200, '*'));
xxssdm_fnd_common_pkg.write_output_message ( ' Purchase Order Conversion Report' );
xxssdm_fnd_common_pkg.write_output_message (RPAD ('*', 200, '*'));
xxssdm_fnd_common_pkg.write_output_message (' ');
xxssdm_fnd_common_pkg.write_output_message (RPAD ('-', 200, '-'));
xxssdm_fnd_common_pkg.write_output_message ( ' Summary of records processed' );
xxssdm_fnd_common_pkg.write_output_message (RPAD ('-', 200, '-'));
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records loaded in Staging table :' || gn_tl_hdr_loaded_records );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Lines records loaded in Staging table :' || gn_tl_lines_loaded_records );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Duplicate Purchase order Header records loaded in Staging table :' || gn_tl_hdr_duplicate_records );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Duplicate Lines records loaded in Staging table :' || gn_tl_lines_duplicate_records );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records in Validation Error :' || gn_tl_hdr_validation_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records in Validation Error :' || gn_tl_lines_validation_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records Validated :' || gn_tl_hdr_validated_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records Validated :' || gn_tl_lines_validated_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records successfully inserted in interface tables :' || gn_hdr_interfaced_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records successfully inserted in interface tables :' || gn_lines_interfaced_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order distributions records successfully inserted in interface tables :' || gn_dist_interfaced_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records errored out in Interface stage :' || gn_tl_hdr_interf_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records errored out in Interface stage :' || gn_tl_lines_interf_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order distributions records errored out in Interface stage :' || gn_tl_dist_interf_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Header records successfully inserted in interface tables :' || gn_rcv_hdr_interfaced_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Transaction records successfully inserted in interface tables :' || gn_rcv_lines_interfaced_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Header records errored out in Interface stage :' || gn_tl_rcv_hdr_interf_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Transaction records errored out in Interface stage :' || gn_tl_rcv_lines_interf_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records successfully inserted in base tables :' || gn_tl_hdr_processed_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records successfully inserted in base tables :' || gn_tl_lines_processed_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order distributions records successfully inserted in base tables :' || gn_tl_dist_processed_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Header records successfully inserted in base tables :' || gn_tl_rcv_hdr_processed_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt transaction records successfully inserted in base tables :' || gn_tl_rcv_lines_processed_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order Header records with Processing error while inserting in base tables :' || gn_tl_hdr_processing_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order line records with Processing error while inserting in base tables :' || gn_tl_lines_processing_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Purchase order distributions records with Processing error while inserting in base tables :' || gn_tl_dist_processing_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Header records with Processing error while inserting in base tables :' || gn_tl_rcv_hdr_err_cnt );
xxssdm_fnd_common_pkg.write_output_message ( 'Total Receipt Transaction records with Processing error while inserting in base tables :' || gn_tl_rcv_lines_err_cnt );
xxssdm_fnd_common_pkg.write_output_message (' ');
xxssdm_fnd_common_pkg.write_output_message (' ');
xxssdm_fnd_common_pkg.write_output_message (RPAD ('-', 200, '-'));
EXCEPTION
WHEN OTHERS THEN
pn_out_status := gn_conc_error;
pv_out_error_message := 'Error occurred while generating output file using generate_report';
xxssdm_fnd_common_pkg.write_trace_message ('Error ocurred while generating output file using generate_report '|| SUBSTR (SQLERRM,1,200));
END generate_report;
-- +====================================================================+
-- | Name: submit_recon_report |
-- | |
-- | Description: Procedure to submit reconciliation report |
-- | |
-- | Parameters: pv_in_application_top - VARCHAR2 |
-- | pv_in_request_short_name - VARCHAR2 |
-- | pv_in_request_description - VARCHAR2 |
-- | |
-- | RETURNs : pv_out_error_message - VARCHAR2 - Error Message |
-- | pn_out_status - NUMBER - RETURN Code |
-- +====================================================================+
PROCEDURE submit_recon_report(
pv_out_error_message OUT VARCHAR2 ,
pn_out_status OUT VARCHAR2 )
IS
lv_recon_pgm_name VARCHAR2(100) := 'XXSSDM_CONV_RECON';
lv_load_pgm_name VARCHAR2(100);
ln_load_req_id NUMBER;
BEGIN
xxssdm_fnd_common_pkg.submit_conc_request_nowait ( errbuf => pv_out_error_message ,retcode => pn_out_status ,pn_out_child_request_id => ln_load_req_id ,pv_out_child_pgm_name => lv_load_pgm_name ,pv_in_application_top => 'XXSS' ,pv_in_request_short_name => lv_recon_pgm_name ,pv_in_request_description => '' ,pv_in_parameter1 => gv_wave_name ,pv_in_parameter2 => gv_entity_name ,pv_in_parameter3 => gn_data_set_id ,pv_in_parameter4 => gn_conc_request_id );
EXCEPTION
WHEN OTHERS THEN
xxssdm_fnd_common_pkg.write_trace_message('Error in submit_recon_report procedure '||SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200 ));
RAISE;
END submit_recon_report;
-- +====================================================================+
-- | Name: get_record_id |
-- | |
-- | Description: This Function will fetch record_id |
-- | |
-- | Parameters: None |
-- | |
-- | RETURNs : ln_record_id - NUMBER |
-- +====================================================================+
FUNCTION get_record_id
RETURN NUMBER
IS
ln_record_id NUMBER;
BEGIN
SELECT XXSSDM_record_ID_SEQ.NEXTVAL INTO ln_record_id FROM DUAL;
RETURN ln_record_id;
END get_record_id;
-- +====================================================================+
-- | Name: get_request_id |
-- | |
-- | Description: This Function will be generating Request_id |
-- | to be used by CTL file |
-- | |
-- | Parameters: None |
-- | |
-- | RETURNs : ln_sqlldr_req_id - NUMBER |
-- +====================================================================+
FUNCTION get_request_id
RETURN NUMBER
IS
ln_sqlldr_req_id NUMBER :=0;
BEGIN
--gn_conc_request_id := fnd_global.conc_request_id;
SELECT MAX(request_id)
INTO ln_sqlldr_req_id
FROM fnd_concurrent_requests
WHERE concurrent_program_id IN
(SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE concurrent_program_name = 'XXSSPO_PO_WAVE1_CONV_C'
) ;
RETURN ln_sqlldr_req_id;
END get_request_id;
-- +====================================================================+
-- | Name: get_data_set_id |
-- | |
-- | Description: This Function will be generating data_set_id |
-- | for CTL file purpose |
-- | |
-- | Parameters: None |
-- | |
-- | RETURNs : ln_data_set_id - NUMBER |
-- +====================================================================+
FUNCTION get_data_set_id
RETURN NUMBER
IS
ln_data_set_id NUMBER:=0;
BEGIN
SELECT to_number(argument3)
INTO ln_data_set_id
FROM fnd_concurrent_requests
WHERE request_id = XXSSPO_PO_CONV_PKG.get_request_id;
RETURN ln_data_set_id;
END get_data_set_id;
END XXSSPO_PO_CONV_PKG;

Master OracleApps

Pages

Wednesday, May 9, 2018

PO Conversion

No comments:

Post a Comment