CREATE OR REPLACE PACKAGE BODY xx_invcnv01_item_load_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : XX_INVCNV01_ITEM_LOAD_PKG
Author's Name : Madhu Dhare
Date Written : 13-Dec-2006
RICEW Object id : INV_CNV_01
Purpose : Package Body
Program Style :
--
Maintenance History
Date Issue# Name Remarks
----------- ---------------- ----------------- -------------
18-Dec-2015 1.0 Madhu Dhare Initial Version
--------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------
Procedure Name : XX_VALIDATE_ITEM_PRC
RICEW Object id : INV_CNV_01
Purpose : This program will validate item data
to be inserted into the Item
Interface tables.
-------------------------------------------------------------------------------------------------------*/
PROCEDURE xx_validate_item_prc (
p_validate_item_rec IN OUT xx_inv_items_preint%ROWTYPE
, p_header_id IN NUMBER
, p_org_code IN VARCHAR2
, p_error_flag OUT VARCHAR2
);
/*------------------------------------------------------------------------------------------------------
Procedure Name : XX_VALIDATE_CATEGORY_PRC
RICEW Object id : INV_CNV_01
Purpose : This program will validate category data
to be inserted into the category
Interface tables.
-------------------------------------------------------------------------------------------------------*/
PROCEDURE xx_validate_category_prc (
p_validate_cat_rec IN xx_inv_cat_preint%ROWTYPE
, p_header_id IN NUMBER
, p_error_flag OUT VARCHAR2
);
/*------------------------------------------------------------------------------------------------------
Procedure Name : MAIN
RICEW Object id : INV_CNV_01
Purpose : This program will read item and category data
from staging tables, for validation and manipulating
data to be inserted into the Item and Category
Interface tables.
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_org_code IN VARCHAR2
, p_run_mode IN VARCHAR2
)
IS
--Cursor to select item data from Staging table
CURSOR c_item_control (cp_org_code IN VARCHAR2)
IS
SELECT organization_code
, item_number
, description
, category_set_name
, category_name
, primary_uom_code
, template_name
, material_cost
, material_sub_elem
, material_oh_rate
, material_oh_sub_elem
, status
, rec_type
, record_count
, record_id
FROM xx_inv_items_stg
WHERE NVL (organization_code, cp_org_code) = cp_org_code
AND status = 'NW'
AND rec_type ='ITM';
--Cursor to Insert Item data into pre-Interface
CURSOR c_item_insert (cp_org_code IN VARCHAR2)
IS
SELECT organization_code
, item_number
, description
, primary_uom_code
, template_name
, material_cost
, material_sub_elem
, material_oh_rate
, material_oh_sub_elem
, 'CREATE' transaction_type
--Valid values are 'CREATE' and 'UPDATE'.Can be changed to 'UPDATE' for updating existing item information if desired
, status
FROM xx_inv_items_stg
WHERE NVL (organization_code, cp_org_code) = cp_org_code
AND status = 'NW'
AND rec_type = 'ITM'
GROUP BY organization_code
, item_number
, description
, primary_uom_code
, template_name
, material_cost
, material_sub_elem
, material_oh_rate
, material_oh_sub_elem
, 'CREATE'
, status;
--Cursor to select category assignments for an item
CURSOR c_cat_control (cp_org_code VARCHAR2, cp_item_number VARCHAR2)
IS
SELECT category_set_name
, category_name
, organization_code
, item_number
, 'CREATE' transaction_type
--Valid values are CREATE and DELETE.An Updated transaction can only be
--performed as a combination of DELETE assignment, then CREATE.
, status
FROM xx_inv_items_stg
WHERE organization_code = cp_org_code
AND item_number = cp_item_number
AND (category_set_name IS NOT NULL
OR category_name IS NOT NULL)
AND status = 'NW'
AND rec_type = 'ITM';
--Cursor to select item data from Pre-Interface table xx_inv_items_preint with status IP
CURSOR c_item_validate (cp_org_code IN VARCHAR)
IS
SELECT *
FROM xx_inv_items_preint
WHERE status = 'IP'
AND organization_code = cp_org_code;
--Cursor to select category assignments for an item from Pre-Interface table xx_inv_cat_preint with status IP
CURSOR c_cat_validate (cp_org_code VARCHAR2, cp_item_number VARCHAR2)
IS
SELECT *
FROM xx_inv_cat_preint
WHERE organization_code = cp_org_code
AND item_number = cp_item_number
AND status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_inv_items_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER := 0;
l_cat_count NUMBER;
l_set_process_count NUMBER;
l_item_rec_count NUMBER;
l_process_set_id_rec xx_invcnv01_item_load_pkg.process_set_id_tbl;
l_item_insert_rec_count NUMBER := 0;
l_cntrl_record_count NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_INVCNV01_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
:= 'INV_CNV_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--record type variable for assigning width to error section
l_cnt_processed_recs NUMBER := 0;
--stores total records for summary report for control validation
l_cnt_successful_recs NUMBER := 0;
--stores total successful records for summary report for control validation
l_cnt_error_recs NUMBER := 0;
--stores total error records for the summary report for control validation
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
l_error_cat_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_cntrl_count NUMBER;
-- Common Validations Variables
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_cntrl_fail EXCEPTION;
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
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 Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Description'; --Fifth Error Header
l_error_rec.identifier6 := 'Organization Code'; --Sixth Error Header
l_error_rec.identifier7 := 'Category Name'; --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;
/*--Can be used to purge records with status 'ER ' from pre-interface based on retention period if desired.
BEGIN
--
DELETE FROM xx_inv_items_preint
WHERE status='ER'
AND last_update_date <SYSDATE- g_retention_period;
--
DELETE FROM xx_inv_items_preint
WHERE status='ER'
AND last_update_date <SYSDATE- g_retention_period;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception while deleting records from pre-interface table'||SQLERRM);
END;*/--
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF UPPER (p_run_mode) = 'F'
THEN
--Check for control record count
BEGIN
--Fetch the record count for control record
SELECT record_count
INTO l_cntrl_record_count
FROM xx_inv_items_stg
WHERE rec_type = 'CRC'
AND status = 'NW';
--Fetch the record count for item records
SELECT COUNT (*)
INTO l_cntrl_count
FROM xx_inv_items_stg
WHERE rec_type = 'ITM'
AND status = 'NW'; -- ITM -Record Type for Item records
IF l_cntrl_count != NVL (l_cntrl_record_count, -1)
THEN
l_error_flag := 'Y';
xx_trace.l
('Control Record Count doesnot match with Staging table record count.'
);
--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 => 'Control Record Count doesnot match with Staging table record count.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--Update Staging Table Status
UPDATE xx_inv_items_stg
SET status = 'ER';
--Raise exception to terminate the program
RAISE e_cntrl_fail;
END IF; --If l_cntrl
EXCEPTION
WHEN e_cntrl_fail
THEN
RAISE;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
('Error while Checking Control Record Count .Oracle Error-'
|| 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 => 'Error while Checking Control Record Count .Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
--
BEGIN -- 1st Begin
FOR c_item_rec IN c_item_control (p_org_code)
LOOP
l_cnt_processed_recs := l_cnt_processed_recs
+ 1;
-- Processed records in Control Validation
l_error_flag := 'N';
--Check if item number is null
IF c_item_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Number cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Item Number value cannot be null.'
, p_identifier3 => c_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_number
, p_identifier5 => c_item_rec.description
, p_identifier6 => c_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Check if organization_code is NULL
IF c_item_rec.organization_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Organization Code value cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Organization Code value cannot be null'
, p_identifier3 => c_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_number
, p_identifier5 => c_item_rec.description
, p_identifier6 => c_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Check if Category Set and Category are provided
IF (c_item_rec.category_set_name IS NULL
AND c_item_rec.category_name IS NOT NULL
)
OR (c_item_rec.category_set_name IS NOT NULL
AND c_item_rec.category_name IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l
('Both Category Name and Category Set Name Needs to be provided.'
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Both Category Name and Category Set Name '
, p_identifier3 => c_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_number
, p_identifier5 => c_item_rec.description
, p_identifier6 => c_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Check if material cost and Material sub-element are provided
IF (c_item_rec.material_cost IS NULL
AND c_item_rec.material_sub_elem IS NOT NULL
)
OR (c_item_rec.material_cost IS NOT NULL
AND c_item_rec.material_sub_elem IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l
('Both Material Cost and Material Subelement Needs to be provided.'
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Both Material Cost and Material Subelement Needs to be provided.'
, p_identifier3 => c_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_number
, p_identifier5 => c_item_rec.description
, p_identifier6 => c_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--Check if material overhead rate and Material overhead sub-element are provided
IF (c_item_rec.material_oh_sub_elem IS NULL
AND c_item_rec.material_oh_rate IS NOT NULL
)
OR (c_item_rec.material_oh_sub_elem IS NOT NULL
AND c_item_rec.material_oh_rate IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l
('Both Material Overhead Rate and Material Overhead Subelement Needs to be provided.'
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Both Material Overhead Rate and Material Overhead Subelement Needs to be provided.'
, p_identifier3 => c_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_item_rec.item_number
, p_identifier5 => c_item_rec.description
, p_identifier6 => c_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--If Control level validation fails update the staging table status field to'ER'
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_inv_items_stg
SET status = 'ER'
WHERE record_id = c_item_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
ELSE
--Increment the successful record count
l_cnt_successful_recs := l_cnt_successful_recs
+ 1;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ('Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Error in Control Validation.'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => c_item_rec.item_number
-- ,p_identifier5 => c_item_rec.description
-- ,p_identifier6 => c_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END; --1st Begin
--END of control Level Validation
/*---------------------------------------------------------------------------------------------------
Load Data into Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
FOR c_item_control_rec IN c_item_insert (p_org_code)
LOOP
SELECT xx_inv_items_preint_s.NEXTVAL
INTO l_seq_val
FROM DUAL;
--Insert into the Items Pre-Interface Table
INSERT INTO xx_inv_items_preint
(status, record_id, process_flag
, transaction_type
, organization_code
, item_number
, description
, primary_uom_code
, template_name
, material_cost
, material_sub_elem
, material_oh_rate
, material_oh_sub_elem, request_id
, last_update_date, last_updated_by, creation_date
, created_by
)
VALUES ('IP', l_seq_val, 1 --Propcess Flag has to be set to 1
, c_item_control_rec.transaction_type
, c_item_control_rec.organization_code
, c_item_control_rec.item_number
, c_item_control_rec.description
, c_item_control_rec.primary_uom_code
, c_item_control_rec.template_name
, c_item_control_rec.material_cost
, c_item_control_rec.material_sub_elem
, c_item_control_rec.material_oh_rate
, c_item_control_rec.material_oh_sub_elem, l_request_id
, SYSDATE, l_user_id, SYSDATE
, l_user_id
);
--Insert Into the Categories Pre-Interface Table
FOR c_cat_control_rec IN
c_cat_control (c_item_control_rec.organization_code
, c_item_control_rec.item_number
)
LOOP
INSERT INTO xx_inv_cat_preint
(category_set_name
, category_name
, organization_code
, item_number
, process_flag
, transaction_type, request_id
, status, record_id
)
VALUES (c_cat_control_rec.category_set_name
, c_cat_control_rec.category_name
, c_cat_control_rec.organization_code
, c_cat_control_rec.item_number
, 1 --Propcess Flag has to be set to 1
, c_cat_control_rec.transaction_type, l_request_id
, 'IP', l_seq_val
);
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Exception While Inserting Into Pre-Interface table. Oracle Error:'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-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
);
ROLLBACK;
RAISE;
END;
END IF; --IF p_run_mode='F'
/*-------------------------------------------------------------------------------------------
--Business Validations--To be carried out for both full run mode(F) and partial run mode (P)
-------------------------------------------------------------------------------------------*/
BEGIN
FOR c_validate_item_rec IN c_item_validate (p_org_code)
LOOP
--
l_processed_recs := l_processed_recs
+ 1; --Counter for total records
--Call to validate item
xx_validate_item_prc (c_validate_item_rec
, l_header_id
, p_org_code
, l_error_flag
);
l_organization_id := c_validate_item_rec.organization_id;
--Open Cursor For Category Records
FOR c_validate_cat_rec IN
c_cat_validate (c_validate_item_rec.organization_code
, c_validate_item_rec.item_number
)
LOOP
--Call to validate category
xx_validate_category_prc (c_validate_cat_rec
, l_header_id
, l_error_cat_flag
);
--Set error flag
IF l_error_cat_flag = 'Y'
THEN
l_error_flag := 'Y';
END IF;
END LOOP;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' where record_status in('NW','PF')
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Items Pre-Interface with error status
UPDATE xx_inv_items_preint
SET status = 'ER'
WHERE item_number = c_validate_item_rec.item_number
AND organization_code =
c_validate_item_rec.organization_code;
--Update Category Pre-Interface with error status
UPDATE xx_inv_cat_preint
SET status = 'ER'
WHERE item_number = c_validate_item_rec.item_number
AND organization_code =
c_validate_item_rec.organization_code;
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-00003'
, p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
, p_identifier3 => c_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => c_validate_item_rec.item_number
, p_identifier5 => c_validate_item_rec.description
, p_identifier6 => c_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
ELSE
l_successful_recs := l_successful_recs
+ 1;
END IF;
l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--End of Business Validation Section
/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN
FOR c_interface_stg_rec IN c_item_validate (p_org_code)
LOOP
--Select Set_Process_id Sequence value
--This value will tie the categories interface rows back to the MTL_SYSTEM_ITEMS_INTERFACE rows
--when item category assignments are imported together with Items
--Select count of number of records to be inserted
SELECT COUNT (*)
INTO l_item_rec_count
FROM xx_inv_items_preint
WHERE status = 'IP'
AND organization_code = p_org_code;
-- Set the number of batches for Import
SELECT CEIL (l_item_rec_count / 1000)
INTO l_set_process_count
FROM DUAL;
-- For the first record for insert, set set_process_id
IF l_item_insert_rec_count = 0
THEN
SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_process_id
FROM DUAL;
l_process_set_id_rec (1).process_set_id := l_process_id;
-- set set_process_id in batches of 1000
ELSIF l_item_insert_rec_count >= 1000
THEN
FOR i IN 1 .. l_set_process_count
LOOP
IF l_item_insert_rec_count = 1000 * i
THEN
SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_process_id
FROM DUAL;
xx_trace.LOG ('Set Process Id Value:'
|| l_process_id);
l_process_set_id_rec (i
+ 1).process_set_id := l_process_id;
END IF;
END LOOP;
END IF;
INSERT INTO mtl_system_items_interface
(inventory_item_id
, organization_id, summary_flag, enabled_flag
, start_date_active
, end_date_active
, description
, buyer_id
, accounting_rule_id
, invoicing_rule_id
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, segment7
, segment8
, segment9
, segment10
, segment11
, segment12
, segment13
, segment14
, segment15
, segment16
, segment17
, segment18
, segment19
, segment20
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, purchasing_item_flag
, shippable_item_flag
, customer_order_flag
, internal_order_flag
, service_item_flag
, inventory_item_flag
, eng_item_flag
, inventory_asset_flag
, purchasing_enabled_flag
, customer_order_enabled_flag
, internal_order_enabled_flag
, so_transactions_flag
, mtl_transactions_enabled_flag
, stock_enabled_flag
, bom_enabled_flag
, build_in_wip_flag
, revision_qty_control_code
, item_catalog_group_id
, catalog_status_flag
, returnable_flag
, default_shipping_org
, collateral_flag
, taxable_flag
, qty_rcv_exception_code
, allow_item_desc_update_flag
, inspection_required_flag
, receipt_required_flag
, market_price
, hazard_class_id
, rfq_required_flag
, qty_rcv_tolerance
, list_price_per_unit
, un_number_id
, price_tolerance_percent
, asset_category_id
, rounding_factor
, unit_of_issue
, enforce_ship_to_location_code
, allow_substitute_receipts_flag
, allow_unordered_receipts_flag
, allow_express_delivery_flag
, days_early_receipt_allowed
, days_late_receipt_allowed
, receipt_days_exception_code
, receiving_routing_id
, invoice_close_tolerance
, receive_close_tolerance
, auto_lot_alpha_prefix
, start_auto_lot_number
, lot_control_code
, shelf_life_code
, shelf_life_days
, serial_number_control_code
, start_auto_serial_number
, auto_serial_alpha_prefix
, source_type
, source_organization_id
, source_subinventory
, expense_account
, encumbrance_account
, restrict_subinventories_code
, unit_weight
, weight_uom_code
, volume_uom_code
, unit_volume
, restrict_locators_code
, location_control_code
, shrinkage_rate
, acceptable_early_days
, planning_time_fence_code
, demand_time_fence_code
, lead_time_lot_size
, std_lot_size
, cum_manufacturing_lead_time
, overrun_percentage
, mrp_calculate_atp_flag
, acceptable_rate_increase
, acceptable_rate_decrease
, cumulative_total_lead_time
, planning_time_fence_days
, demand_time_fence_days
, end_assembly_pegging_flag
, repetitive_planning_flag
, planning_exception_set
, bom_item_type
, pick_components_flag
, replenish_to_order_flag
, base_item_id
, atp_components_flag
, atp_flag
, fixed_lead_time
, variable_lead_time
, wip_supply_locator_id
, wip_supply_type
, wip_supply_subinventory
, primary_uom_code
, primary_unit_of_measure
, allowed_units_lookup_code
, cost_of_sales_account
, sales_account
, default_include_in_rollup_flag
, inventory_item_status_code
, inventory_planning_code
, planner_code
, planning_make_buy_code
, fixed_lot_multiplier
, rounding_control_type
, carrying_cost
, postprocessing_lead_time
, preprocessing_lead_time
, full_lead_time
, order_cost
, mrp_safety_stock_percent
, mrp_safety_stock_code
, min_minmax_quantity
, max_minmax_quantity
, minimum_order_quantity
, fixed_order_quantity
, fixed_days_supply
, maximum_order_quantity
, atp_rule_id
, picking_rule_id
, reservable_type
, positive_measurement_error
, negative_measurement_error
, engineering_ecn_code
, engineering_item_id
, engineering_date
, service_starting_delay
, vendor_warranty_flag
, serviceable_component_flag
, serviceable_product_flag
, base_warranty_service_id
, payment_terms_id
, preventive_maintenance_flag
, primary_specialist_id
, secondary_specialist_id
, serviceable_item_class_id
, time_billable_flag
, material_billable_flag
, expense_billable_flag
, prorate_service_flag
, coverage_schedule_id
, service_duration_period_code
, service_duration
, warranty_vendor_id
, max_warranty_amount
, response_time_period_code
, response_time_value
, new_revision_code
, invoiceable_item_flag
, tax_code
, invoice_enabled_flag
, must_use_approved_vendor_flag
, request_id
, program_application_id
, program_id
, program_update_date
, outside_operation_flag
, outside_operation_uom_type
, safety_stock_bucket_days
, auto_reduce_mps
, costing_enabled_flag
, cycle_count_enabled_flag
, demand_source_line
, copy_item_id
, set_id
, revision
, auto_created_config_flag
, item_type
, model_config_clause_name
, ship_model_complete_flag
, mrp_planning_code
, return_inspection_requirement
, demand_source_type
, demand_source_header_id
, transaction_id, process_flag
, organization_code
, item_number
, copy_item_number
, template_id
, template_name
, copy_organization_id
, copy_organization_code
, ato_forecast_control
, transaction_type
, material_cost
, material_sub_elem
, material_oh_rate
, material_oh_sub_elem
, material_sub_elem_id
, material_oh_sub_elem_id
, auto_rel_time_fence_code
, auto_rel_time_fence_days
, container_item_flag
, vehicle_item_flag
, maximum_load_weight
, minimum_fill_percent
, container_type_code
, internal_volume, set_process_id
, check_shortages_flag
, release_time_fence_code
, release_time_fence_days
, wh_update_date
, product_family_item_id
, purchasing_tax_code
, overcompletion_tolerance_type
, overcompletion_tolerance_value
, effectivity_control
, global_attribute_category
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, over_shipment_tolerance
, under_shipment_tolerance
, over_return_tolerance
, under_return_tolerance
, equipment_type
, recovered_part_disp_code
, defect_tracking_on_flag
, usage_item_flag
, event_flag
, electronic_flag
, downloadable_flag
, vol_discount_exempt_flag
, coupon_exempt_flag
, comms_nl_trackable_flag
, asset_creation_code
, comms_activation_reqd_flag
, orderable_on_web_flag
, back_orderable_flag
, web_status
, indivisible_flag
, long_description
, dimension_uom_code
, unit_length
, unit_width
, unit_height
, cartonization_group_id
, bulk_picked_flag
, lot_status_enabled
, default_lot_status_id
, serial_status_enabled
, default_serial_status_id
, lot_split_enabled
, lot_merge_enabled
, inventory_carry_penalty
, operation_slack_penalty
, financing_allowed_flag
, eam_item_type
, eam_activity_type_code
, eam_activity_cause_code
, eam_act_notification_flag
, eam_act_shutdown_status
, dual_uom_control
, secondary_uom_code
, dual_uom_deviation_high
, dual_uom_deviation_low
, contract_item_type_code
, subscription_depend_flag
, serv_req_enabled_code
, serv_billing_enabled_flag
, serv_importance_level
, planned_inv_point_flag
, lot_translate_enabled
, default_so_source_type
, create_supply_flag
, substitution_window_code
, substitution_window_days
, ib_item_instance_class
, config_model_type
, lot_substitution_enabled
, minimum_license_quantity
, eam_activity_source_code
, lifecycle_id
, current_phase_id
)
VALUES (c_interface_stg_rec.inventory_item_id
, l_organization_id, 'Y', 'Y'
, c_interface_stg_rec.start_date_active
, c_interface_stg_rec.end_date_active
, c_interface_stg_rec.description
, c_interface_stg_rec.buyer_id
, c_interface_stg_rec.accounting_rule_id
, c_interface_stg_rec.invoicing_rule_id
, c_interface_stg_rec.segment1
, c_interface_stg_rec.segment2
, c_interface_stg_rec.segment3
, c_interface_stg_rec.segment4
, c_interface_stg_rec.segment5
, c_interface_stg_rec.segment6
, c_interface_stg_rec.segment7
, c_interface_stg_rec.segment8
, c_interface_stg_rec.segment9
, c_interface_stg_rec.segment10
, c_interface_stg_rec.segment11
, c_interface_stg_rec.segment12
, c_interface_stg_rec.segment13
, c_interface_stg_rec.segment14
, c_interface_stg_rec.segment15
, c_interface_stg_rec.segment16
, c_interface_stg_rec.segment17
, c_interface_stg_rec.segment18
, c_interface_stg_rec.segment19
, c_interface_stg_rec.segment20
, c_interface_stg_rec.attribute_category
, c_interface_stg_rec.attribute1
, c_interface_stg_rec.attribute2
, c_interface_stg_rec.attribute3
, c_interface_stg_rec.attribute4
, c_interface_stg_rec.attribute5
, c_interface_stg_rec.attribute6
, c_interface_stg_rec.attribute7
, c_interface_stg_rec.attribute8
, c_interface_stg_rec.attribute9
, c_interface_stg_rec.attribute10
, c_interface_stg_rec.attribute11
, c_interface_stg_rec.attribute12
, c_interface_stg_rec.attribute13
, c_interface_stg_rec.attribute14
, c_interface_stg_rec.attribute15
, c_interface_stg_rec.purchasing_item_flag
, c_interface_stg_rec.shippable_item_flag
, c_interface_stg_rec.customer_order_flag
, c_interface_stg_rec.internal_order_flag
, c_interface_stg_rec.service_item_flag
, c_interface_stg_rec.inventory_item_flag
, c_interface_stg_rec.eng_item_flag
, c_interface_stg_rec.inventory_asset_flag
, c_interface_stg_rec.purchasing_enabled_flag
, c_interface_stg_rec.customer_order_enabled_flag
, c_interface_stg_rec.internal_order_enabled_flag
, c_interface_stg_rec.so_transactions_flag
, c_interface_stg_rec.mtl_transactions_enabled_flag
, c_interface_stg_rec.stock_enabled_flag
, c_interface_stg_rec.bom_enabled_flag
, c_interface_stg_rec.build_in_wip_flag
, c_interface_stg_rec.revision_qty_control_code
, c_interface_stg_rec.item_catalog_group_id
, c_interface_stg_rec.catalog_status_flag
, c_interface_stg_rec.returnable_flag
, c_interface_stg_rec.default_shipping_org
, c_interface_stg_rec.collateral_flag
, c_interface_stg_rec.taxable_flag
, c_interface_stg_rec.qty_rcv_exception_code
, c_interface_stg_rec.allow_item_desc_update_flag
, c_interface_stg_rec.inspection_required_flag
, c_interface_stg_rec.receipt_required_flag
, c_interface_stg_rec.market_price
, c_interface_stg_rec.hazard_class_id
, c_interface_stg_rec.rfq_required_flag
, c_interface_stg_rec.qty_rcv_tolerance
, c_interface_stg_rec.list_price_per_unit
, c_interface_stg_rec.un_number_id
, c_interface_stg_rec.price_tolerance_percent
, c_interface_stg_rec.asset_category_id
, c_interface_stg_rec.rounding_factor
, c_interface_stg_rec.unit_of_issue
, c_interface_stg_rec.enforce_ship_to_location_code
, c_interface_stg_rec.allow_substitute_receipts_flag
, c_interface_stg_rec.allow_unordered_receipts_flag
, c_interface_stg_rec.allow_express_delivery_flag
, c_interface_stg_rec.days_early_receipt_allowed
, c_interface_stg_rec.days_late_receipt_allowed
, c_interface_stg_rec.receipt_days_exception_code
, c_interface_stg_rec.receiving_routing_id
, c_interface_stg_rec.invoice_close_tolerance
, c_interface_stg_rec.receive_close_tolerance
, c_interface_stg_rec.auto_lot_alpha_prefix
, c_interface_stg_rec.start_auto_lot_number
, c_interface_stg_rec.lot_control_code
, c_interface_stg_rec.shelf_life_code
, c_interface_stg_rec.shelf_life_days
, c_interface_stg_rec.serial_number_control_code
, c_interface_stg_rec.start_auto_serial_number
, c_interface_stg_rec.auto_serial_alpha_prefix
, c_interface_stg_rec.source_type
, c_interface_stg_rec.source_organization_id
, c_interface_stg_rec.source_subinventory
, c_interface_stg_rec.expense_account
, c_interface_stg_rec.encumbrance_account
, c_interface_stg_rec.restrict_subinventories_code
, c_interface_stg_rec.unit_weight
, c_interface_stg_rec.weight_uom_code
, c_interface_stg_rec.volume_uom_code
, c_interface_stg_rec.unit_volume
, c_interface_stg_rec.restrict_locators_code
, c_interface_stg_rec.location_control_code
, c_interface_stg_rec.shrinkage_rate
, c_interface_stg_rec.acceptable_early_days
, c_interface_stg_rec.planning_time_fence_code
, c_interface_stg_rec.demand_time_fence_code
, c_interface_stg_rec.lead_time_lot_size
, c_interface_stg_rec.std_lot_size
, c_interface_stg_rec.cum_manufacturing_lead_time
, c_interface_stg_rec.overrun_percentage
, c_interface_stg_rec.mrp_calculate_atp_flag
, c_interface_stg_rec.acceptable_rate_increase
, c_interface_stg_rec.acceptable_rate_decrease
, c_interface_stg_rec.cumulative_total_lead_time
, c_interface_stg_rec.planning_time_fence_days
, c_interface_stg_rec.demand_time_fence_days
, c_interface_stg_rec.end_assembly_pegging_flag
, c_interface_stg_rec.repetitive_planning_flag
, c_interface_stg_rec.planning_exception_set
, c_interface_stg_rec.bom_item_type
, c_interface_stg_rec.pick_components_flag
, c_interface_stg_rec.replenish_to_order_flag
, c_interface_stg_rec.base_item_id
, c_interface_stg_rec.atp_components_flag
, c_interface_stg_rec.atp_flag
, c_interface_stg_rec.fixed_lead_time
, c_interface_stg_rec.variable_lead_time
, c_interface_stg_rec.wip_supply_locator_id
, c_interface_stg_rec.wip_supply_type
, c_interface_stg_rec.wip_supply_subinventory
, c_interface_stg_rec.primary_uom_code
, c_interface_stg_rec.primary_unit_of_measure
, c_interface_stg_rec.allowed_units_lookup_code
, c_interface_stg_rec.cost_of_sales_account
, c_interface_stg_rec.sales_account
, c_interface_stg_rec.default_include_in_rollup_flag
, c_interface_stg_rec.inventory_item_status_code
, c_interface_stg_rec.inventory_planning_code
, c_interface_stg_rec.planner_code
, c_interface_stg_rec.planning_make_buy_code
, c_interface_stg_rec.fixed_lot_multiplier
, c_interface_stg_rec.rounding_control_type
, c_interface_stg_rec.carrying_cost
, c_interface_stg_rec.postprocessing_lead_time
, c_interface_stg_rec.preprocessing_lead_time
, c_interface_stg_rec.full_lead_time
, c_interface_stg_rec.order_cost
, c_interface_stg_rec.mrp_safety_stock_percent
, c_interface_stg_rec.mrp_safety_stock_code
, c_interface_stg_rec.min_minmax_quantity
, c_interface_stg_rec.max_minmax_quantity
, c_interface_stg_rec.minimum_order_quantity
, c_interface_stg_rec.fixed_order_quantity
, c_interface_stg_rec.fixed_days_supply
, c_interface_stg_rec.maximum_order_quantity
, c_interface_stg_rec.atp_rule_id
, c_interface_stg_rec.picking_rule_id
, c_interface_stg_rec.reservable_type
, c_interface_stg_rec.positive_measurement_error
, c_interface_stg_rec.negative_measurement_error
, c_interface_stg_rec.engineering_ecn_code
, c_interface_stg_rec.engineering_item_id
, c_interface_stg_rec.engineering_date
, c_interface_stg_rec.service_starting_delay
, c_interface_stg_rec.vendor_warranty_flag
, c_interface_stg_rec.serviceable_component_flag
, c_interface_stg_rec.serviceable_product_flag
, c_interface_stg_rec.base_warranty_service_id
, c_interface_stg_rec.payment_terms_id
, c_interface_stg_rec.preventive_maintenance_flag
, c_interface_stg_rec.primary_specialist_id
, c_interface_stg_rec.secondary_specialist_id
, c_interface_stg_rec.serviceable_item_class_id
, c_interface_stg_rec.time_billable_flag
, c_interface_stg_rec.material_billable_flag
, c_interface_stg_rec.expense_billable_flag
, c_interface_stg_rec.prorate_service_flag
, c_interface_stg_rec.coverage_schedule_id
, c_interface_stg_rec.service_duration_period_code
, c_interface_stg_rec.service_duration
, c_interface_stg_rec.warranty_vendor_id
, c_interface_stg_rec.max_warranty_amount
, c_interface_stg_rec.response_time_period_code
, c_interface_stg_rec.response_time_value
, c_interface_stg_rec.new_revision_code
, c_interface_stg_rec.invoiceable_item_flag
, c_interface_stg_rec.tax_code
, c_interface_stg_rec.invoice_enabled_flag
, c_interface_stg_rec.must_use_approved_vendor_flag
, c_interface_stg_rec.request_id
, c_interface_stg_rec.program_application_id
, c_interface_stg_rec.program_id
, c_interface_stg_rec.program_update_date
, c_interface_stg_rec.outside_operation_flag
, c_interface_stg_rec.outside_operation_uom_type
, c_interface_stg_rec.safety_stock_bucket_days
, c_interface_stg_rec.auto_reduce_mps
, c_interface_stg_rec.costing_enabled_flag
, c_interface_stg_rec.cycle_count_enabled_flag
, c_interface_stg_rec.demand_source_line
, c_interface_stg_rec.copy_item_id
, c_interface_stg_rec.set_id
, c_interface_stg_rec.revision
, c_interface_stg_rec.auto_created_config_flag
, c_interface_stg_rec.item_type
, c_interface_stg_rec.model_config_clause_name
, c_interface_stg_rec.ship_model_complete_flag
, c_interface_stg_rec.mrp_planning_code
, c_interface_stg_rec.return_inspection_requirement
, c_interface_stg_rec.demand_source_type
, c_interface_stg_rec.demand_source_header_id
, c_interface_stg_rec.transaction_id, 1
, c_interface_stg_rec.organization_code
, c_interface_stg_rec.item_number
, c_interface_stg_rec.copy_item_number
, c_interface_stg_rec.template_id
, c_interface_stg_rec.template_name
, c_interface_stg_rec.copy_organization_id
, c_interface_stg_rec.copy_organization_code
, c_interface_stg_rec.ato_forecast_control
, c_interface_stg_rec.transaction_type
, c_interface_stg_rec.material_cost
, c_interface_stg_rec.material_sub_elem
, c_interface_stg_rec.material_oh_rate
, c_interface_stg_rec.material_oh_sub_elem
, c_interface_stg_rec.material_sub_elem_id
, c_interface_stg_rec.material_oh_sub_elem_id
, c_interface_stg_rec.auto_rel_time_fence_code
, c_interface_stg_rec.auto_rel_time_fence_days
, c_interface_stg_rec.container_item_flag
, c_interface_stg_rec.vehicle_item_flag
, c_interface_stg_rec.maximum_load_weight
, c_interface_stg_rec.minimum_fill_percent
, c_interface_stg_rec.container_type_code
, c_interface_stg_rec.internal_volume, l_process_id
, c_interface_stg_rec.check_shortages_flag
, c_interface_stg_rec.release_time_fence_code
, c_interface_stg_rec.release_time_fence_days
, c_interface_stg_rec.wh_update_date
, c_interface_stg_rec.product_family_item_id
, c_interface_stg_rec.purchasing_tax_code
, c_interface_stg_rec.overcompletion_tolerance_type
, c_interface_stg_rec.overcompletion_tolerance_value
, c_interface_stg_rec.effectivity_control
, c_interface_stg_rec.global_attribute_category
, c_interface_stg_rec.global_attribute1
, c_interface_stg_rec.global_attribute2
, c_interface_stg_rec.global_attribute3
, c_interface_stg_rec.global_attribute4
, c_interface_stg_rec.global_attribute5
, c_interface_stg_rec.global_attribute6
, c_interface_stg_rec.global_attribute7
, c_interface_stg_rec.global_attribute8
, c_interface_stg_rec.global_attribute9
, c_interface_stg_rec.global_attribute10
, c_interface_stg_rec.over_shipment_tolerance
, c_interface_stg_rec.under_shipment_tolerance
, c_interface_stg_rec.over_return_tolerance
, c_interface_stg_rec.under_return_tolerance
, c_interface_stg_rec.equipment_type
, c_interface_stg_rec.recovered_part_disp_code
, c_interface_stg_rec.defect_tracking_on_flag
, c_interface_stg_rec.usage_item_flag
, c_interface_stg_rec.event_flag
, c_interface_stg_rec.electronic_flag
, c_interface_stg_rec.downloadable_flag
, c_interface_stg_rec.vol_discount_exempt_flag
, c_interface_stg_rec.coupon_exempt_flag
, c_interface_stg_rec.comms_nl_trackable_flag
, c_interface_stg_rec.asset_creation_code
, c_interface_stg_rec.comms_activation_reqd_flag
, c_interface_stg_rec.orderable_on_web_flag
, c_interface_stg_rec.back_orderable_flag
, c_interface_stg_rec.web_status
, c_interface_stg_rec.indivisible_flag
, c_interface_stg_rec.long_description
, c_interface_stg_rec.dimension_uom_code
, c_interface_stg_rec.unit_length
, c_interface_stg_rec.unit_width
, c_interface_stg_rec.unit_height
, c_interface_stg_rec.cartonization_group_id
, c_interface_stg_rec.bulk_picked_flag
, c_interface_stg_rec.lot_status_enabled
, c_interface_stg_rec.default_lot_status_id
, c_interface_stg_rec.serial_status_enabled
, c_interface_stg_rec.default_serial_status_id
, c_interface_stg_rec.lot_split_enabled
, c_interface_stg_rec.lot_merge_enabled
, c_interface_stg_rec.inventory_carry_penalty
, c_interface_stg_rec.operation_slack_penalty
, c_interface_stg_rec.financing_allowed_flag
, c_interface_stg_rec.eam_item_type
, c_interface_stg_rec.eam_activity_type_code
, c_interface_stg_rec.eam_activity_cause_code
, c_interface_stg_rec.eam_act_notification_flag
, c_interface_stg_rec.eam_act_shutdown_status
, c_interface_stg_rec.dual_uom_control
, c_interface_stg_rec.secondary_uom_code
, c_interface_stg_rec.dual_uom_deviation_high
, c_interface_stg_rec.dual_uom_deviation_low
, c_interface_stg_rec.contract_item_type_code
, c_interface_stg_rec.subscription_depend_flag
, c_interface_stg_rec.serv_req_enabled_code
, c_interface_stg_rec.serv_billing_enabled_flag
, c_interface_stg_rec.serv_importance_level
, c_interface_stg_rec.planned_inv_point_flag
, c_interface_stg_rec.lot_translate_enabled
, c_interface_stg_rec.default_so_source_type
, c_interface_stg_rec.create_supply_flag
, c_interface_stg_rec.substitution_window_code
, c_interface_stg_rec.substitution_window_days
, c_interface_stg_rec.ib_item_instance_class
, c_interface_stg_rec.config_model_type
, c_interface_stg_rec.lot_substitution_enabled
, c_interface_stg_rec.minimum_license_quantity
, c_interface_stg_rec.eam_activity_source_code
, c_interface_stg_rec.lifecycle_id
, c_interface_stg_rec.current_phase_id
);
--Insert into MTL_ITEM_CATEGORIES_INTERFACE table
FOR c_interface_cat_rec IN
c_cat_validate (c_interface_stg_rec.organization_code
, c_interface_stg_rec.item_number
)
LOOP
INSERT INTO mtl_item_categories_interface
(category_set_name
, category_name
, organization_code
, organization_id, item_number
, process_flag
, transaction_type
, set_process_id, request_id
)
VALUES (c_interface_cat_rec.category_set_name
, c_interface_cat_rec.category_name
, c_interface_cat_rec.organization_code
, l_organization_id, c_interface_cat_rec.item_number
, 1 --process_flag
, c_interface_cat_rec.transaction_type --transaction_type
, l_process_id, c_interface_cat_rec.request_id
);
END LOOP;
l_item_insert_rec_count := l_item_insert_rec_count
+ 1;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
('Exception While Inserting into MTL_SYSTEM_ITEMS_INTERFACE.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-00004'
, p_identifier2 => 'Exception While Inserting into MTL_SYSTEM_ITEMS_INTERFACE.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 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN
--Update Items Pre-Interface table status
UPDATE xx_inv_items_preint
SET status = 'PR'
WHERE status = 'IP'
AND organization_code = p_org_code;
--Update Category Pre-Interface table status
UPDATE xx_inv_cat_preint
SET status = 'PR'
WHERE status = 'IP'
AND organization_code = p_org_code;
xx_trace.l ('Pre-Interface Table status updated to PR');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
(' Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.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
);
END;
--Initialize apps
--fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN
IF l_item_rec_count > 0
THEN
FOR i IN 1 .. l_set_process_count
LOOP
l_process_id := l_process_set_id_rec (i).process_set_id;
l_standard_request_id :=
fnd_request.submit_request
(application => 'INV'
, program => 'INCOIN'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_organization_id --Organization id
, argument2 => 1 --All organizations
, argument3 => 1 --Validate Items
, argument4 => 1 --Process Items
, argument5 => 1 --Delete Processed Rows
, argument6 => l_process_id --Item Set to be processed
, argument7 => 1
--CREATE new Items(1) or UPDATE existing Items(2)
);
COMMIT;
--Wait for the completion of the concurrent request (if submitted successfully)
l_completed :=
fnd_concurrent.wait_for_request
(request_id => l_standard_request_id
, INTERVAL => 60
, max_wait => 0
, phase => l_phase
, status => l_vstatus
, dev_phase => l_dev_phase
, dev_status => l_dev_status
, MESSAGE => l_message
);
xx_trace.l ('Request submitted with request id-'
|| l_standard_request_id
);
END LOOP;
ELSE
xx_trace.l ('No records To Process in Interface Table');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 --Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'No records To Process in Interface Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END;
/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_inv_items_preint
WHERE status = 'PR'
AND organization_code = p_org_code;
--
DELETE FROM xx_inv_cat_preint
WHERE status = 'PR'
AND organization_code = p_org_code;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
('Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs
- l_successful_recs;
l_cnt_error_recs := l_cnt_processed_recs
- l_cnt_successful_recs;
l_return_value := NULL;
--Call Insert Summary Count For Control Validation
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Control Validation'
, p_total_recs => l_cnt_processed_recs
, p_successful_recs => l_cnt_successful_recs
, p_error_recs => l_cnt_error_recs
);
l_return_value := NULL;
--Call Insert Summary Count For Business Validation
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Business Validation'
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
--
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
END IF;
EXCEPTION --Main Exception
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value := xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40
--High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
/*------------------------------------------------------------------------------------------------------
Procedure Name : XX_VALIDATE_ITEM_PRC
RICEW Object id : INV_CNV_01
Purpose : This program will validate item data
to be inserted into the Item
Interface tables.
-------------------------------------------------------------------------------------------------------*/
PROCEDURE xx_validate_item_prc (
p_validate_item_rec IN OUT xx_inv_items_preint%ROWTYPE
, p_header_id IN NUMBER
, p_org_code IN VARCHAR2
, p_error_flag OUT VARCHAR2
)
IS
l_master_org VARCHAR2 (1) := 'N';
l_return_value NUMBER;
l_master_item VARCHAR2 (1) := 'N';
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_inv_items_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_sub_element VARCHAR2 (1) := 'N';
BEGIN
p_error_flag := 'N';
IF (p_validate_item_rec.organization_code IS NOT NULL)
THEN
l_value := NULL;
--Validating Organization Code
xx_common_validations_pkg.validate_organization
(p_organization_name => l_value
, p_organization_code => p_validate_item_rec.organization_code
, p_organization_id => p_validate_item_rec.organization_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_process_status = 1
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in Validating Org Code'
|| l_output_message
, p_identifier3 => p_validate_item_rec.record_id
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END IF;
--
IF p_error_flag = 'N'
THEN
--Validating Organization Code and Checking Whether The Organization is Master Org
BEGIN
SELECT 'Y'
, b.organization_id
INTO l_master_org
, p_validate_item_rec.organization_id
FROM org_organization_definitions a
, mtl_parameters b
WHERE a.organization_id = b.organization_id
AND UPPER (b.organization_code) = UPPER (p_org_code)
AND a.organization_id = b.master_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_master_org := 'N';
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
(' Exception occured while validating for master organization.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception occured while validating for master organization.Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
--
END;
END IF; --If p_error_flag
END IF;
--Validating if item present in master org of the given child org
IF (p_validate_item_rec.item_number IS NOT NULL)
THEN
IF l_master_org = 'N'
THEN
BEGIN
SELECT 'Y'
INTO l_master_item
FROM mtl_system_items a
, org_organization_definitions b
, mtl_parameters c
WHERE a.organization_id = c.master_organization_id
AND b.organization_id = c.organization_id
AND c.organization_id = p_validate_item_rec.organization_id
AND a.segment1 = p_validate_item_rec.item_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_master_item := 'N';
p_error_flag := 'Y';
xx_trace.h
(' This Item has no Master Item record in MTL_SYSTEM_ITEMS for the given child Organization.'
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => ' This Item has no Master Item record in MTL_SYSTEM_ITEMS for the given child Organization.'
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
--
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
(' Exception occured while validating if item present in master org.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception occured while validating if item present in master org.Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
--Validating if item present
BEGIN
--
SELECT inventory_item_id
INTO p_validate_item_rec.inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = p_validate_item_rec.item_number
AND organization_id = p_validate_item_rec.organization_id;
--
p_error_flag := 'Y';
xx_trace.l ('Item Already Exists For the Organization given.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Item Already Exists for the Organization given'
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
--
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Occured While Validating Item Segment .Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Occured While Validating Item Segment .Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
--
END;
END IF;
--Validating unit of measure
IF (p_validate_item_rec.primary_uom_code IS NOT NULL)
THEN
--Validate primary_uom_code
l_process_status := 0;
l_value := NULL;
xx_common_validations_pkg.validate_uom
(p_unit_of_measure => l_value
, p_uom_code => p_validate_item_rec.primary_uom_code
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_process_status = 1
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in validating UOM '
|| l_output_message
, p_identifier3 => p_validate_item_rec.record_id
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END IF;
END IF;
--Validating template name
IF (p_validate_item_rec.template_name IS NOT NULL)
THEN
BEGIN
--
SELECT template_id
INTO p_validate_item_rec.template_id
FROM mtl_item_templates
WHERE template_name = p_validate_item_rec.template_name;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Ocurred While Validating Template Name.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Ocurred While Validating Template Name.Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
--Validating Material Cost Sub Element
IF (p_validate_item_rec.material_sub_elem IS NOT NULL)
THEN
BEGIN
--
SELECT 'Y'
INTO l_sub_element
FROM bom_resources a
, cst_cost_elements b
WHERE a.cost_element_id = b.cost_element_id
AND a.organization_id = p_validate_item_rec.organization_id
AND a.resource_code = p_validate_item_rec.material_sub_elem
AND b.cost_element = 'Material';
--
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Ocurred While Validating Material Subelement.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Ocurred While Validating Material Subelement.Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
--Validating Sub Element for Material Overhead
IF (p_validate_item_rec.material_oh_sub_elem IS NOT NULL)
THEN
BEGIN
--
SELECT 'Y'
INTO l_sub_element
FROM bom_resources a
, cst_cost_elements b
WHERE a.cost_element_id = b.cost_element_id
AND a.organization_id = p_validate_item_rec.organization_id
AND a.resource_code = p_validate_item_rec.material_oh_sub_elem
AND b.cost_element = 'Material Overhead';
--
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Ocurred While Validating Material Overhead Subelement.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Ocurred While Validating Material Overhead Subelement.Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_item_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_item_rec.item_number
, p_identifier5 => p_validate_item_rec.description
, p_identifier6 => p_validate_item_rec.organization_code
,
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h ('Exception in validate item procedure.Oracle Error'
|| SQLERRM
);
END xx_validate_item_prc;
/*------------------------------------------------------------------------------------------------------
Procedure Name : XX_VALIDATE_CATEGORY_PRC
RICEW Object id : INV_CNV_01
Purpose : This program will validate category data
to be inserted into the category
Interface tables.
-------------------------------------------------------------------------------------------------------*/
PROCEDURE xx_validate_category_prc (
p_validate_cat_rec IN xx_inv_cat_preint%ROWTYPE
, p_header_id IN NUMBER
, p_error_flag OUT VARCHAR2
)
IS
l_category_set_id NUMBER;
l_category_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_interface_stg_rec xx_inv_cat_preint%ROWTYPE;
BEGIN
p_error_flag := 'N';
--Validate Category Set Name
BEGIN
IF p_validate_cat_rec.category_set_name IS NOT NULL
THEN
SELECT category_set_id
INTO l_category_set_id
FROM mtl_category_sets_vl
WHERE UPPER (category_set_name) =
UPPER (p_validate_cat_rec.category_set_name);
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Occured While Validating Category Set .Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Occured While Validating Category Set .Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_cat_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_cat_rec.item_number
, p_identifier6 => p_validate_cat_rec.organization_code
, p_identifier7 => p_validate_cat_rec.category_name
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--Validate Category Name
BEGIN
SELECT a.category_id
INTO l_category_id
FROM mtl_categories_b_kfv a
, mtl_category_sets b
WHERE a.structure_id = b.structure_id
AND b.structure_id =
(SELECT structure_id
FROM mtl_category_sets
WHERE UPPER (category_set_name) =
UPPER (p_validate_cat_rec.category_set_name))
AND UPPER (concatenated_segments) =
UPPER (p_validate_cat_rec.category_name)
GROUP BY a.category_id;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
('Exception Occured While Validating Category Name .Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception Occured While Validating Category Name .Oracle Error-'
|| SQLERRM
, p_identifier3 => p_validate_cat_rec.record_id
-- Can be utilized to store record serial number
, p_identifier4 => p_validate_cat_rec.item_number
, p_identifier6 => p_validate_cat_rec.organization_code
, p_identifier7 => p_validate_cat_rec.category_name
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h ('Exception in validate category procedure.Oracle Error'
|| SQLERRM
);
END xx_validate_category_prc;
END xx_invcnv01_item_load_pkg;
/
No comments :
Post a Comment