Oracle Applications & Oracle Cloud Fusion HCM : ITEM Conversions

-- File Name : xopmcnv01a.ctl
-- File Type : SQL*Loader Control file.
-- RICEW Object id : XX_OPM_CONV_01
-- Description : This SQL*Loader file is used to load data
-- from flat file to the staging table XX_AP_BANKS_STG
-- Maintenance History:
--
-- ===================================================================================================================

OPTIONS (SKIP = 1)
LOAD DATA-- (SKIP = 1)
--
--INFILE 'd:\oravis\viscustom\11.5.0\bin\xopmcnv01a.csv'
BADFILE 'd:\oravis\viscustom\11.5.0\bin\xopmcnv01a.bad'
REPLACE
INTO TABLE XX_OPM_ITEM_STG
WHEN REC_TYPE != 'REC_TYPE'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( STATUS CONSTANT 'NW'
,REC_TYPE CHAR
,REC_COUNT CHAR
,ITEM_NO CHAR
,ITEM_DESC1 CHAR
,ITEM_DESC2 CHAR
,ALT_ITEMA CHAR
,ALT_ITEMB CHAR
,ITEM_UM CHAR
,DUALUM_IND CHAR
,ITEM_UM2 CHAR
,DEVIATION_LO CHAR
,DEVIATION_HI CHAR
,LEVEL_CODE CHAR
,LOT_CTL CHAR
,LOT_INDIVISIBLE CHAR
,SUBLOT_CTL CHAR
,LOCT_CTL CHAR
,NONINV_IND CHAR
,MATCH_TYPE CHAR
,INACTIVE_IND CHAR
,INV_TYPE CHAR
,SHELF_LIFE CHAR
,RETEST_INTERVAL CHAR
,GL_CLASS CHAR
,INV_CLASS CHAR
,SALES_CLASS CHAR
,SHIP_CLASS CHAR
,FRT_CLASS CHAR
,PRICE_CLASS CHAR
,STORAGE_CLASS CHAR
,PURCH_CLASS CHAR
,TAX_CLASS CHAR
,CUSTOMS_CLASS CHAR
,ALLOC_CLASS CHAR
,PLANNING_CLASS CHAR
,ITEMCOST_CLASS CHAR
,COST_MTHD_CODE CHAR
,UPC_CODE CHAR
,GRADE_CTL CHAR
,STATUS_CTL CHAR
,QC_GRADE CHAR
,LOT_STATUS CHAR
,BULK_ID CHAR
,PKG_ID CHAR
,QCITEM_ID CHAR
,QCHOLD_RES_CODE CHAR
,EXPACTION_CODE CHAR
,FILL_QTY CHAR
,FILL_UM CHAR
,EXPACTION_INTERVAL CHAR
,PHANTOM_TYPE CHAR
,WHSE_ITEM_ID CHAR
,EXPERIMENTAL_IND CHAR
,EXPORTED_DATE CHAR
,TRANS_CNT CHAR
,TEXT_CODE CHAR
,SEQ_DPND_CLASS CHAR
,COMMODITY_CODE CHAR
,ATTRIBUTE1 CHAR
,ATTRIBUTE2 CHAR
,ATTRIBUTE3 CHAR
,ATTRIBUTE4 CHAR
,ATTRIBUTE5 CHAR
,ATTRIBUTE6 CHAR
,ATTRIBUTE7 CHAR
,ATTRIBUTE8 CHAR
,ATTRIBUTE9 CHAR
,ATTRIBUTE10 CHAR
,ATTRIBUTE11 CHAR
,ATTRIBUTE12 CHAR
,ATTRIBUTE13 CHAR
,ATTRIBUTE14 CHAR
,ATTRIBUTE15 CHAR
,ATTRIBUTE16 CHAR
,ATTRIBUTE17 CHAR
,ATTRIBUTE18 CHAR
,ATTRIBUTE19 CHAR
,ATTRIBUTE20 CHAR
,ATTRIBUTE21 CHAR
,ATTRIBUTE22 CHAR
,ATTRIBUTE23 CHAR
,ATTRIBUTE24 CHAR
,ATTRIBUTE25 CHAR
,ATTRIBUTE26 CHAR
,ATTRIBUTE27 CHAR
,ATTRIBUTE28 CHAR
,ATTRIBUTE29 CHAR
,ATTRIBUTE30 CHAR
,ATTRIBUTE_CATEGORY CHAR
,ITEM_ABCCODE CHAR
,ONT_PRICING_QTY_SOURCE CHAR
,AUTOLOT_ACTIVE_INDICATOR CHAR
,LOT_PREFIX CHAR
,LOT_SUFFIX CHAR
,SUBLOT_PREFIX CHAR
,SUBLOT_SUFFIX CHAR
,STG_ID "XX_OPM_ITEM_STG_S.NEXTVAL"
,CREATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
)

CREATE OR REPLACE PACKAGE xx_opmcnv01_item_load_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : XX_OPMCNV01_ITEM_LOAD_PKG
Author's Name : Lokesh Pille
Date Written : 12-Feb-2007
RICEW Object id : OPM_CNV_01
Purpose : Package Specification
Program Style :
--

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

CREATE OR REPLACE PACKAGE BODY xx_opmcnv01_item_load_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : XX_OPMCNV01_ITEM_LOAD_PKG
Author's Name : Lokesh Pille
Date Written : 12-Feb-2007
RICEW Object id : OPM_CNV_01
Purpose : Package Body
Program Style :
--

/*------------------------------------------------------------------------------------------------------
Private Procedure to be called to validate _CLASS columns
-------------------------------------------------------------------------------------------------------*/
PROCEDURE validate_classes(p_item_rec IN xx_opm_item_preint%rowtype
,p_header_id IN NUMBER
,p_error_flag OUT VARCHAR2)
IS
--variables
l_error_message VARCHAR2(2000);
l_preint_id NUMBER;
l_item_no VARCHAR2(2000);
l_item_desc1 VARCHAR2(2000);
l_rank_count NUMBER;
l_row_count NUMBER;
l_header_id NUMBER;
-- cursor to validate item_abccode
CURSOR Get_rank
IS
SELECT count(*)
FROM ic_rank_mst
WHERE abc_code=p_item_rec.item_abccode AND delete_mark = 0;
--
PROCEDURE insert_error
AS
l_return_value NUMBER;
l_output_message VARCHAR2(2000);
BEGIN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => l_error_message
, p_identifier3 => l_preint_Id
, p_identifier4 => l_item_no
, p_identifier5 => l_item_desc1
, p_identifier6 => NULL
, p_identifier7 => NULL
, p_identifier8 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--invalid_value EXCEPTION;
BEGIN
-- variables to populate the error table
l_header_id := p_header_id;
l_preint_id := p_item_rec.preint_id;
l_item_no := p_item_rec.item_no;
l_item_desc1 := p_item_rec.item_desc1;
--
IF (p_item_rec.alloc_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_allc_cls
WHERE alloc_class=RTRIM(p_item_rec.alloc_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for ALLOC_CLASS: '||p_item_rec.alloc_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.itemcost_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_cost_cls
WHERE itemcost_class=RTRIM(p_item_rec.itemcost_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for ITEMCOST_CLASS: '||p_item_rec.itemcost_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.customs_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_ctms_cls
WHERE iccustoms_class=RTRIM(p_item_rec.customs_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for CUSTOMS_CLASS: '||p_item_rec.customs_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.frt_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_frgt_cls
WHERE icfrt_class=RTRIM(p_item_rec.frt_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for FRT_CLASS: '||p_item_rec.frt_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.gl_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_gled_cls
WHERE icgl_class=RTRIM(p_item_rec.gl_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for GL_CLASS: '||p_item_rec.gl_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.inv_class IS NOT NULL) THEN
SELECT count(*) into l_row_count FROM ic_invn_cls
WHERE icinv_class=RTRIM(p_item_rec.inv_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for INV_CLASS: '||p_item_rec.inv_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.price_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_prce_cls
WHERE icprice_class=RTRIM(p_item_rec.price_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for PRICE_CLASS: '||p_item_rec.price_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.purch_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_prch_cls
WHERE icpurch_class=RTRIM(p_item_rec.purch_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for PURCH_CLASS: '||p_item_rec.purch_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.item_abccode IS NOT NULL) THEN
OPEN Get_rank;
FETCH Get_rank INTO l_rank_count;
CLOSE Get_rank;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for ITEM_ABCCODE: '||p_item_rec.item_abccode;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.sales_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_sale_cls
WHERE icsales_class=RTRIM(p_item_rec.sales_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for SALES_CLASS: '||p_item_rec.sales_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.ship_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_ship_cls
WHERE icship_class=RTRIM(p_item_rec.ship_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for SHIP_CLASS: '||p_item_rec.ship_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.storage_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_stor_cls
WHERE icstorage_class=RTRIM(p_item_rec.storage_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for STORAGE_CLASS: '||p_item_rec.storage_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.tax_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_taxn_cls
WHERE ictax_class=RTRIM(p_item_rec.tax_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for TAX_CLASS: '||p_item_rec.tax_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.planning_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ps_plng_cls
WHERE planning_class=RTRIM(p_item_rec.planning_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for PLANNING_CLASS: '||p_item_rec.planning_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.qchold_res_code IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM qc_hres_mst
WHERE qchold_res_code=RTRIM(p_item_rec.qchold_res_code) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for QCHOLD_RES_CODE: '||p_item_rec.qchold_res_code;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.seq_dpnd_class IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM cr_sqdt_cls
WHERE seq_dpnd_class=RTRIM(p_item_rec.seq_dpnd_class) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for SEQ_DPND_CLASS: '||p_item_rec.seq_dpnd_class;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.inv_type IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM ic_invn_typ
WHERE inv_type=RTRIM(p_item_rec.inv_type) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for INV_TYPE: '||p_item_rec.inv_type;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
IF (p_item_rec.cost_mthd_code IS NOT NULL) THEN
SELECT count(*) INTO l_row_count FROM cm_mthd_mst
WHERE cost_mthd_code=RTRIM(p_item_rec.cost_mthd_code) AND delete_mark = 0;
--
IF (l_row_count <> 1) THEN
l_error_message := 'Invalid value for COST_MTHD_CODE: '||p_item_rec.cost_mthd_code;
p_error_flag := 'Y';
insert_error;
END IF;
END IF;
--
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Exception in CLASS validation ';
p_error_flag := 'Y';
insert_error;
END;
/*------------------------------------------------------------------------------------------------------
Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2)
IS
--Cursor to select from Staging table xx_opm_item_stg
CURSOR c_item_control
IS
SELECT *
FROM xx_opm_item_stg
WHERE status = 'NW'
AND rec_type = 'D';

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

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

--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_counter NUMBER; --counter to check duplicates
l_item_details GMIGAPI.item_rec_typ;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_OPMCNV01_ITEM_LOAD_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name
|| '.'
|| 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'OPM_CNV_01';
--Object Name
l_data VARCHAR2(4000);
l_message_out VARCHAR2(4000);
l_request_id xx_emf_message_headers.request_id%TYPE :=0;
l_user_id fnd_user.user_id%TYPE :=0;
l_user_name fnd_user.user_name%TYPE :=0;
l_error_rec xx_emf_message_details%ROWTYPE;
l_ic_item_mst_row ic_item_mst%ROWTYPE;
l_ic_item_cpg_row ic_item_cpg%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
l_return_status BOOLEAN;
--stores number returned by EMF API's
l_data_count NUMBER;
--stores total records of the data file
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs_c NUMBER := 0;
--stores total records for Control validation
l_successful_recs_c NUMBER := 0;
--stores total successful records for Control validation
l_error_recs_c NUMBER := 0;
--stores total error records for Control validation
l_processed_recs_b NUMBER := 0;
--stores total records for Business validation
l_successful_recs_b NUMBER := 0;
--stores total successful records for Business validation
l_error_recs_b NUMBER := 0;
--stores total error records for Business validation
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
-- Common Validations Variables
-- Exception Variables
e_count_mismatch EXCEPTION;
--Stop the program if the Count mismatch occurs
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_cntrl_fail EXCEPTION;
BEGIN --Main Begin
l_request_id := NVL(fnd_profile.VALUE ('CONC_REQUEST_ID'),0);
l_user_id := NVL(fnd_profile.VALUE('USER_ID'),0);
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program '
|| l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name
, p_program_type => g_program_type
, p_ricew_id => l_ricewid
, p_request_id => l_request_id
);

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

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

--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid
, (SYSDATE
- g_retention_period)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
--
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
IF p_run_mode = 'F'
THEN
--BEGIN
--Checking for record count mis-match
BEGIN
SELECT COUNT(*)
INTO l_data_count
FROM xx_opm_item_stg
WHERE rec_type = 'D';
--
SELECT DECODE(rec_count,l_data_count,0,1)
INTO l_data_count
FROM xx_opm_item_stg
WHERE rec_type = 'C';
--
IF l_data_count != 0
THEN
xx_trace.l ('The received data file is not a complete data file. Exiting the program');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'The received data file is not a complete data file. Exiting the program'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE e_count_mismatch; --Propagate the exception to Main Exception
END IF;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE;--Propagate the Exception to the Main Exception
END;
--Control validations
FOR c_item_rec IN c_item_control
LOOP
l_error_flag := 'N';
l_processed_recs_c := l_processed_recs_c + 1;
IF ((c_item_rec.item_no IS NULL)
OR (c_item_rec.item_desc1 IS NULL)
OR (c_item_rec.item_um IS NULL)
OR (c_item_rec.dualum_ind IS NULL)
OR (c_item_rec.lot_ctl IS NULL)
OR (c_item_rec.ont_pricing_qty_source IS NULL)
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Item No/Item Desc/Item UOM/Dual Uom Ind/Lot Ctl/ONT pricing qty soucre cann''t be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => ('Item No/Item Desc/Item UOM/Dual Uom Ind/Lot Ctl/ONT pricing qty soucre cann''t be null.')
, p_identifier3 => c_item_rec.stg_id-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_no
, p_identifier5 => c_item_rec.item_desc1
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
UPDATE xx_opm_item_stg
SET status = 'ER'
,last_update_date = SYSDATE
,last_updated_by = l_user_id
,request_id = l_request_id
WHERE stg_id = c_item_rec.stg_id;
--
ELSE
l_successful_recs_c := l_successful_recs_c + 1;
END IF;
END LOOP;
--End of Control validations
/*---------------------------------------------------------------------------------------------------
Load Data into Pre-Interface Table(s)
---------------------------------------------------------------------------------------------------*/
BEGIN
--Loading data into xx_opm_item_preint table
INSERT INTO xx_opm_item_preint
(preint_id
,item_no
,item_desc1
,item_desc2
,alt_itema
,alt_itemb
,item_um
,dualum_ind
,item_um2
,deviation_lo
,deviation_hi
,level_code
,lot_ctl
,lot_indivisible
,sublot_ctl
,loct_ctl
,noninv_ind
,match_type
,inactive_ind
,inv_type
,shelf_life
,retest_interval
,gl_class
,inv_class
,sales_class
,ship_class
,frt_class
,price_class
,storage_class
,purch_class
,tax_class
,customs_class
,alloc_class
,planning_class
,itemcost_class
,cost_mthd_code
,upc_code
,grade_ctl
,status_ctl
,qc_grade
,lot_status
,bulk_id
,pkg_id
,qcitem_id
,qchold_res_code
,expaction_code
,fill_qty
,fill_um
,expaction_interval
,phantom_type
,whse_item_id
,experimental_ind
,exported_date
,trans_cnt
,text_code
,seq_dpnd_class
,commodity_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,request_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category
,item_abccode
,ont_pricing_qty_source
,autolot_active_indicator
,lot_prefix
,lot_suffix
,sublot_prefix
,sublot_suffix
,status)
SELECT xx_opm_item_preint_s.nextval
,item_no
,item_desc1
,item_desc2
,alt_itema
,alt_itemb
,item_um
,dualum_ind
,item_um2
,deviation_lo
,deviation_hi
,level_code
,lot_ctl
,lot_indivisible
,sublot_ctl
,loct_ctl
,noninv_ind
,match_type
,inactive_ind
,inv_type
,shelf_life
,retest_interval
,gl_class
,inv_class
,sales_class
,ship_class
,frt_class
,price_class
,storage_class
,purch_class
,tax_class
,customs_class
,alloc_class
,planning_class
,itemcost_class
,cost_mthd_code
,upc_code
,grade_ctl
,status_ctl
,qc_grade
,lot_status
,bulk_id
,pkg_id
,qcitem_id
,qchold_res_code
,expaction_code
,fill_qty
,fill_um
,expaction_interval
,phantom_type
,whse_item_id
,experimental_ind
,exported_date
,trans_cnt
,text_code
,seq_dpnd_class
,commodity_code
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_request_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category
,item_abccode
,ont_pricing_qty_source
,autolot_active_indicator
,lot_prefix
,lot_suffix
,sublot_prefix
,sublot_suffix
,'NW'
FROM xx_opm_item_stg
WHERE status = 'NW'
AND rec_type = 'D';
--END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Exception While Inserting Into Pre-Interface table(s). Oracle Error:'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL -- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
/*-----------------------------------------------------------------------------
Updating pre interface table with status 'IP' where record_status in('NW')
------------------------------------------------------------------------------*/
BEGIN
UPDATE xx_opm_item_preint
SET status = 'IP'
,last_update_date = SYSDATE
,last_updated_by = l_user_id
,request_id = l_request_id
WHERE status = 'NW';
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
(' Exception While Updating Status of Pre-Interface Table(s).Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Updating Status of Pre-Interface Table(s).Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL -- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
END IF; --p_run_mode = 'F'
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN
l_error_flag := 'N';
FOR c_validate_item_rec IN c_item_validate
LOOP
--
l_processed_recs_b := l_processed_recs_b + 1; --Counter for total records
--
validate_classes(c_validate_item_rec,l_header_id,l_error_flag);
/*---------------------------------------------------------------------------------------
Move data into Oracle Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle Base table from pre interface table
l_item_details.item_no := c_validate_item_rec.item_no;
l_item_details.item_desc1 := c_validate_item_rec.item_desc1;
l_item_details.item_desc2 := c_validate_item_rec.item_desc2;
l_item_details.alt_itema := c_validate_item_rec.alt_itema;
l_item_details.alt_itemb := c_validate_item_rec.alt_itemb;
l_item_details.item_um := c_validate_item_rec.item_um;
l_item_details.dualum_ind := c_validate_item_rec.dualum_ind;
l_item_details.item_um2 := c_validate_item_rec.item_um2;
l_item_details.deviation_lo := c_validate_item_rec.deviation_lo;
l_item_details.deviation_hi := c_validate_item_rec.deviation_hi;
l_item_details.level_code := c_validate_item_rec.level_code;
l_item_details.lot_ctl := c_validate_item_rec.lot_ctl;
l_item_details.lot_indivisible := c_validate_item_rec.lot_indivisible;
l_item_details.sublot_ctl := c_validate_item_rec.sublot_ctl;
l_item_details.loct_ctl := c_validate_item_rec.loct_ctl;
l_item_details.noninv_ind := c_validate_item_rec.noninv_ind;
l_item_details.match_type := c_validate_item_rec.match_type;
l_item_details.inactive_ind := c_validate_item_rec.inactive_ind;
l_item_details.inv_type := c_validate_item_rec.inv_type;
l_item_details.shelf_life := c_validate_item_rec.shelf_life;
l_item_details.retest_interval := c_validate_item_rec.retest_interval;
l_item_details.gl_class := c_validate_item_rec.gl_class;
l_item_details.inv_class := c_validate_item_rec.inv_class;
l_item_details.sales_class := c_validate_item_rec.sales_class;
l_item_details.ship_class := c_validate_item_rec.ship_class;
l_item_details.frt_class := c_validate_item_rec.frt_class;
l_item_details.price_class := c_validate_item_rec.price_class;
l_item_details.storage_class := c_validate_item_rec.storage_class;
l_item_details.purch_class := c_validate_item_rec.purch_class;
l_item_details.tax_class := c_validate_item_rec.tax_class;
l_item_details.customs_class := c_validate_item_rec.customs_class;
l_item_details.alloc_class := c_validate_item_rec.alloc_class;
l_item_details.planning_class := c_validate_item_rec.planning_class;
l_item_details.itemcost_class := c_validate_item_rec.itemcost_class;
l_item_details.cost_mthd_code := c_validate_item_rec.cost_mthd_code;
l_item_details.upc_code := c_validate_item_rec.upc_code;
l_item_details.grade_ctl := c_validate_item_rec.grade_ctl;
l_item_details.status_ctl := c_validate_item_rec.status_ctl;
l_item_details.qc_grade := c_validate_item_rec.qc_grade;
l_item_details.lot_status := c_validate_item_rec.lot_status;
l_item_details.bulk_id := c_validate_item_rec.bulk_id;
l_item_details.pkg_id := c_validate_item_rec.pkg_id;
--l_item_details.qcitem_id := c_validate_item_rec.qcitem_id;
l_item_details.qchold_res_code := c_validate_item_rec.qchold_res_code;
l_item_details.expaction_code := c_validate_item_rec.expaction_code;
l_item_details.fill_qty := c_validate_item_rec.fill_qty;
l_item_details.fill_um := c_validate_item_rec.fill_um;
l_item_details.expaction_interval := c_validate_item_rec.expaction_interval;
l_item_details.phantom_type := c_validate_item_rec.phantom_type;
--l_item_details.whse_item_id := c_validate_item_rec.whse_item_id;
l_item_details.experimental_ind := c_validate_item_rec.experimental_ind;
l_item_details.exported_date := c_validate_item_rec.exported_date;
--l_item_details.trans_cnt := c_validate_item_rec.trans_cnt;
--l_item_details.text_code := c_validate_item_rec.text_code;
l_item_details.seq_dpnd_class := c_validate_item_rec.seq_dpnd_class;
l_item_details.commodity_code := c_validate_item_rec.commodity_code;
l_item_details.attribute1 := c_validate_item_rec.attribute1;
l_item_details.attribute2 := c_validate_item_rec.attribute2;
l_item_details.attribute3 := c_validate_item_rec.attribute3;
l_item_details.attribute4 := c_validate_item_rec.attribute4;
l_item_details.attribute5 := c_validate_item_rec.attribute5;
l_item_details.attribute6 := c_validate_item_rec.attribute6;
l_item_details.attribute7 := c_validate_item_rec.attribute7;
l_item_details.attribute8 := c_validate_item_rec.attribute8;
l_item_details.attribute9 := c_validate_item_rec.attribute9;
l_item_details.attribute10 := c_validate_item_rec.attribute10;
l_item_details.attribute11 := c_validate_item_rec.attribute11;
l_item_details.attribute12 := c_validate_item_rec.attribute12;
l_item_details.attribute13 := c_validate_item_rec.attribute13;
l_item_details.attribute14 := c_validate_item_rec.attribute14;
l_item_details.attribute15 := c_validate_item_rec.attribute15;
l_item_details.attribute16 := c_validate_item_rec.attribute16;
l_item_details.attribute17 := c_validate_item_rec.attribute17;
l_item_details.attribute18 := c_validate_item_rec.attribute18;
l_item_details.attribute19 := c_validate_item_rec.attribute19;
l_item_details.attribute20 := c_validate_item_rec.attribute20;
l_item_details.attribute21 := c_validate_item_rec.attribute21;
l_item_details.attribute22 := c_validate_item_rec.attribute22;
l_item_details.attribute23 := c_validate_item_rec.attribute23;
l_item_details.attribute24 := c_validate_item_rec.attribute24;
l_item_details.attribute25 := c_validate_item_rec.attribute25;
l_item_details.attribute26 := c_validate_item_rec.attribute26;
l_item_details.attribute27 := c_validate_item_rec.attribute27;
l_item_details.attribute28 := c_validate_item_rec.attribute28;
l_item_details.attribute29 := c_validate_item_rec.attribute29;
l_item_details.attribute30 := c_validate_item_rec.attribute30;
l_item_details.attribute_category := c_validate_item_rec.attribute_category;
l_item_details.item_abccode := c_validate_item_rec.item_abccode ;
l_item_details.ont_pricing_qty_source := c_validate_item_rec.ont_pricing_qty_source;
--l_item_details.autolot_active_indicator := c_validate_item_rec.autolot_active_indicator;
--l_item_details.lot_prefix := c_validate_item_rec.lot_prefix;
--l_item_details.lot_suffix := c_validate_item_rec.lot_suffix;
--l_item_details.sublot_prefix := c_validate_item_rec.sublot_prefix;
--l_item_details.sublot_suffix := c_validate_item_rec.sublot_suffix;
--
l_user_name := FND_GLOBAL.USER_NAME;
--Make call to gmigutl.setup
l_return_status := GMIGUTL.SETUP (l_user_name);
-- IF setup successful call Item Create API
IF l_return_status
THEN
--API GMIPAPI.Create_Item is called to load OPM item information in Oracle tables
GMIPAPI.create_item (p_api_version => 3.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, p_validation_level => FND_API.G_VALID_LEVEL_NONE
, p_item_rec => l_item_details
, x_ic_item_mst_row => l_ic_item_mst_row
, x_ic_item_cpg_row => l_ic_item_cpg_row
, x_return_status => l_status
, x_msg_count => l_counter
, x_msg_data => l_data
);
ELSE
xx_trace.h('Error in calling GMIGUTL.SETUP');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Error in calling GMIGUTL.SETUP'
, p_identifier3 => c_validate_item_rec.preint_id
, p_identifier4 => c_validate_item_rec.item_no
, p_identifier5 => c_validate_item_rec.item_desc1
, p_identifier6 => NULL
, p_identifier7 => NULL
, p_identifier8 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF; --Error in Setup
--Check The AP return Status and update staging table
IF l_status <> 'S'
THEN
l_error_flag := 'Y';
--l_successful_recs_b := l_successful_recs_b - 1;
--Fetch the error message using Fnd_Msg_Pub
FOR i IN 1 .. l_counter
LOOP
FND_MSG_PUB.get (i, 'F', l_message_out, l_counter);
IF TRIM(l_message_out) != 'IC_API_INVALID_CLASS' THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => l_message_out
, p_identifier3 => c_validate_item_rec.preint_id
, p_identifier4 => c_validate_item_rec.item_no
, p_identifier5 => c_validate_item_rec.item_desc1
, p_identifier6 => NULL
, p_identifier7 => NULL
, p_identifier8 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END LOOP;
END IF;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' for error records
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_opm_item_preint
SET status = 'ER'
,last_update_date = SYSDATE
,last_updated_by = l_user_id
,request_id = l_request_id
WHERE preint_id = c_validate_item_rec.preint_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
(' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
, p_identifier3 => c_validate_item_rec.preint_id
, p_identifier4 => c_validate_item_rec.item_no
, p_identifier5 => c_validate_item_rec.item_desc1
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_b := l_successful_recs_b + 1;
END IF;
--
--END IF;
l_error_flag := 'N';
l_status := 'S';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Unexpected Exception While Business Validation/API Calling.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Unexpected Exception While Business Validation/API Calling.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------------
--Updating pre interface table(s) with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN
UPDATE xx_opm_item_preint
SET status = 'PR'
,last_update_date = SYSDATE
,last_updated_by = l_user_id
,request_id = l_request_id
WHERE status = 'IP';
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
(' Exception While Updating Pre-Interface Table(s) Status to PR.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception While Updating Pre-Interface Table(s) Status to PR.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------
Delete the Processed records from the Pre-interface table
----------------------------------------------------------------------------*/
BEGIN
DELETE FROM xx_opm_item_preint
WHERE status = 'PR';
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
(' Exception While deleting processed records from Pre-Interface Table'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => ' Exception While deleting processed records from Pre-Interface Table'
|| SQLERRM
, p_identifier3 => NULL -- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
--Summary Count for Control validations
l_error_recs_c := l_processed_recs_c - l_successful_recs_c;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||'(Control Validations)'
, p_total_recs => l_processed_recs_c
, p_successful_recs => l_successful_recs_c
, p_error_recs => l_error_recs_c
);
--Summary Count for Business validations
l_error_recs_b := l_processed_recs_b - l_successful_recs_b;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||'(Business Validations)'
, p_total_recs => l_processed_recs_b
, p_successful_recs => l_successful_recs_b
, p_error_recs => l_error_recs_b
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for OPM Item Interface');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);

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

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

--
l_return_value := NULL;

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

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

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

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

COMMIT;
END IF;
EXCEPTION --Main Exception
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value := xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
--Summary Count for Control validations
l_error_recs_c := l_processed_recs_c - l_successful_recs_c;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||'(Control Validations)'
, p_total_recs => l_processed_recs_c
, p_successful_recs => l_successful_recs_c
, p_error_recs => l_error_recs_c
);
--Summary Count for Business validations
l_error_recs_b := l_processed_recs_b - l_successful_recs_b;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||'(Business Validations)'
, p_total_recs => l_processed_recs_b
, p_successful_recs => l_successful_recs_b
, p_error_recs => l_error_recs_b
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for OPM Item Interface');
END IF;
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 --High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20 --Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);

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

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

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

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

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

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday, 8 January 2017

ITEM Conversions

No comments :

Contact us