CREATE OR REPLACE PACKAGE BODY xx_bom_import_pkg
AS
/*--------------------------------------------------------------------------------------------------------
Package Name : xx_bom_import_pkg
Author's Name : Madhu Dhare
Date Written : 23-Feb-2014
RICEW Object id : BOM_CNV_01
Purpose : Package Body
Program Style :
--
Maintenance History
Date Issue# Name Remarks
----------- ---------------- ----------------- -------------
23-Feb-2014 1.0 Madhu Dhare Initial Version
--------------------------------------------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------------
Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE validate_org_item (
p_org_code IN org_organization_definitions.organization_code%TYPE
, p_org_id OUT org_organization_definitions.organization_id%TYPE
, p_assembly_item_number IN mtl_system_items_b.segment1%TYPE
, p_comp_item_number IN mtl_system_items_b.segment1%TYPE
, p_emf_header_id IN xx_emf_message_headers.header_id%TYPE
, p_error_flag IN OUT VARCHAR2
);
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
)
IS
--Cursor to select BOM data from Staging table xx_bom_import_stg
--for validation of data
CURSOR c_bom_stg
IS
SELECT record_id
, DECODE (rec_type, 'HDR', 1, 'DTL', 2) order_by
, rec_type
, org_code
, assembly_item_number
, revision
, component_item_number
, component_quantity
, effectivity_date
, assembly_type
, process_flag
, transaction_type
, supply_subinventory
, supply_locator_id
, location_name
, operation_seq_num
, wip_supply_type
, planning_factor
, optional
, mutually_exclusive_options
, include_on_ship_docs
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
FROM xx_bom_import_stg
WHERE status = 'NW'
AND (rec_type = 'HDR'
OR rec_type = 'DTL')
ORDER BY order_by
, record_id;
--Cursor to select from BOM Pre-Interface header table
CURSOR c_bom_bill_of_mtls_preint
IS
SELECT *
FROM xx_bom_bill_of_mtls_preint
WHERE status = 'IP';
--Cursor to select from BOM Pre-Interface item details table
CURSOR c_bom_inv_comps_preint (p_bom_preint_id IN NUMBER)
IS
SELECT *
FROM xx_bom_inv_comps_preint
WHERE status = 'IP'
AND bom_preint_id = p_bom_preint_id;
-- Cursor for displaying error during BOM import process
CURSOR c_import_errors (p_request_id IN NUMBER)
IS
SELECT mie.error_message
FROM mtl_interface_errors mie
WHERE mie.request_id = p_request_id;
/*--------------------------------------------------------------------------------------------------------
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_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_BOM_IMPORT_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name
|| '.'
|| 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'BOM_CNV_01';
l_request_id xx_emf_message_headers.request_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
l_messages_rec xx_emf_message_details%ROWTYPE;
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
l_header_id xx_emf_message_headers.header_id%TYPE;
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;
--stores total error records for the summary report
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_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
-- Interface specific variables -- Krishanu
l_bom_preint_id NUMBER;
l_value VARCHAR2 (100);
l_item_num NUMBER := 0;
l_sub_inv_name mtl_secondary_inventories.secondary_inventory_name%TYPE;
l_locator_id mtl_item_locations_kfv.inventory_location_id%TYPE;
l_return_status VARCHAR2 (10);
l_errorcode NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_trans_count NUMBER;
l_cntrl_count NUMBER;
l_cntrl_record_count NUMBER;
e_cntrl_fail EXCEPTION;
l_cntrl_processed_recs NUMBER := 0;
l_cntrl_successful_recs NUMBER := 0;
l_cntrl_error_recs NUMBER := 0;
l_return BOOLEAN;
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 := 'Organization'; --Third Error Header
l_error_rec.identifier4 := 'Assembly Item Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Component Item Number'; --Fifth 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
---------------------------------------------------------------------------------------------------*/
--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_bom_import_stg
WHERE rec_type = 'CRC'
AND status = 'NW';
--Fetch the record count for item records
SELECT COUNT (*)
INTO l_cntrl_count
FROM xx_bom_import_stg
WHERE (rec_type = 'HDR'
OR rec_type = 'DTL')
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
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--Update Staging Table Status
UPDATE xx_bom_import_stg
SET status = 'ER';
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
FOR c_bom_stg_rec IN c_bom_stg
LOOP
l_error_flag := 'N';
l_cntrl_processed_recs := l_cntrl_processed_recs
+ 1; --Counter for total records
IF c_bom_stg_rec.org_code IS NULL
THEN
xx_trace.l ('Organization 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-00002'
, p_identifier2 => 'Organization cannot be null.'
, p_identifier3 => c_bom_stg_rec.org_code
, p_identifier4 => c_bom_stg_rec.assembly_item_number
, p_identifier5 => c_bom_stg_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END IF;
IF c_bom_stg_rec.assembly_item_number IS NULL
THEN
xx_trace.l ('Assembly item 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-00002'
, p_identifier2 => ('Assembly item cannot be null.'
)
, p_identifier3 => c_bom_stg_rec.org_code
, p_identifier4 => c_bom_stg_rec.assembly_item_number
, p_identifier5 => c_bom_stg_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END IF;
IF c_bom_stg_rec.rec_type = 'DTL'
THEN
IF c_bom_stg_rec.component_item_number IS NULL
THEN
xx_trace.l ('Component item 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-00002'
, p_identifier2 => ('Component item cannot be null.'
)
, p_identifier3 => c_bom_stg_rec.org_code
, p_identifier4 => c_bom_stg_rec.assembly_item_number
, p_identifier5 => c_bom_stg_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END IF;
IF NVL (c_bom_stg_rec.component_quantity, 0) <= 0
THEN
xx_trace.l ('Component quantity should be possitive.');
--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-00002'
, p_identifier2 => ('Component quantity should be possitive.'
)
, p_identifier3 => c_bom_stg_rec.org_code
, p_identifier4 => c_bom_stg_rec.assembly_item_number
, p_identifier5 => c_bom_stg_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END IF;
END IF;
IF l_error_flag = 'Y'
THEN
UPDATE xx_bom_import_stg
SET status = 'ER'
WHERE record_id = c_bom_stg_rec.record_id;
ELSE
l_cntrl_successful_recs := l_cntrl_successful_recs
+ 1; --Counter for total records
/*-------------------------------------------------------------------------------
Load Data into Pre-Interface Tables
-------------------------------------------------------------------------------*/
IF c_bom_stg_rec.rec_type = 'HDR'
THEN
SELECT xx_bom_import_preint_s.NEXTVAL
INTO l_bom_preint_id
FROM DUAL;
INSERT INTO xx_bom_bill_of_mtls_preint
(status, bom_preint_id, organization_code
, item_number
, revision
)
VALUES ('IP', l_bom_preint_id, c_bom_stg_rec.org_code
, c_bom_stg_rec.assembly_item_number
, c_bom_stg_rec.revision
);
ELSIF c_bom_stg_rec.rec_type = 'DTL'
THEN
-- Find the header preint id of this BOM item detail
SELECT bom_preint_id
INTO l_bom_preint_id
FROM xx_bom_bill_of_mtls_preint
WHERE item_number = c_bom_stg_rec.assembly_item_number
AND status = 'IP';
INSERT INTO xx_bom_inv_comps_preint
(status, bom_preint_id, organization_code
, assembly_item_number
, component_item_number
, component_quantity
, effectivity_date
, supply_subinventory, item_num
, supply_locator_id
, location_name
, planning_factor
, optional
, mutually_exclusive_options
, include_on_ship_docs
)
VALUES ('IP', l_bom_preint_id, c_bom_stg_rec.org_code
, c_bom_stg_rec.assembly_item_number
, c_bom_stg_rec.component_item_number
, c_bom_stg_rec.component_quantity
, c_bom_stg_rec.effectivity_date
, c_bom_stg_rec.supply_subinventory, NULL
, c_bom_stg_rec.supply_locator_id
, c_bom_stg_rec.location_name
, c_bom_stg_rec.planning_factor
, c_bom_stg_rec.optional
, c_bom_stg_rec.mutually_exclusive_options
, c_bom_stg_rec.include_on_ship_docs
);
END IF;
END IF;
BEGIN
UPDATE xx_bom_import_stg
SET status = 'PR'
WHERE record_id = c_bom_stg_rec.record_id;
END;
COMMIT;
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-00002'
, p_identifier2 => 'Error in Control Validation. '
|| SQLERRM
, p_identifier3 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF; -- p_run_mode = 'F'
/*----------------------------------------------------------------------------
--Business Validations on Pre interface data and supplying some values
----------------------------------------------------------------------------*/
BEGIN
FOR c_bom_bill_of_mtls_preint_rec IN c_bom_bill_of_mtls_preint
LOOP
l_processed_recs := l_processed_recs
+ 1;
l_error_flag := 'N';
---------------------------------------------------------------------
--Validating Item and Organization
---------------------------------------------------------------------
validate_org_item
(p_org_code => c_bom_bill_of_mtls_preint_rec.organization_code
, p_org_id => c_bom_bill_of_mtls_preint_rec.organization_id
, p_assembly_item_number => c_bom_bill_of_mtls_preint_rec.item_number
, p_comp_item_number => NULL
, p_emf_header_id => l_header_id
, p_error_flag => l_error_flag
);
IF l_error_flag = 'Y'
THEN
UPDATE xx_bom_bill_of_mtls_preint
SET status = 'ER'
WHERE bom_preint_id = c_bom_bill_of_mtls_preint_rec.bom_preint_id;
ELSE
l_successful_recs := l_successful_recs
+ 1;
c_bom_bill_of_mtls_preint_rec.process_flag := 1;
c_bom_bill_of_mtls_preint_rec.assembly_type := 1;
c_bom_bill_of_mtls_preint_rec.transaction_type := 'CREATE';
INSERT INTO bom_bill_of_mtls_interface
(organization_code
, item_number
, assembly_type
, process_flag
, transaction_type
)
VALUES (c_bom_bill_of_mtls_preint_rec.organization_code
, c_bom_bill_of_mtls_preint_rec.item_number
, c_bom_bill_of_mtls_preint_rec.assembly_type
, c_bom_bill_of_mtls_preint_rec.process_flag
, c_bom_bill_of_mtls_preint_rec.transaction_type
);
UPDATE xx_bom_bill_of_mtls_preint
SET status = 'PR'
WHERE bom_preint_id = c_bom_bill_of_mtls_preint_rec.bom_preint_id;
END IF;
l_item_num := 0;
FOR c_bom_inv_comps_preint_rec IN
c_bom_inv_comps_preint (c_bom_bill_of_mtls_preint_rec.bom_preint_id)
LOOP
l_error_flag := 'N';
l_processed_recs := l_processed_recs
+ 1;
validate_org_item
(p_org_code => c_bom_inv_comps_preint_rec.organization_code
, p_org_id => c_bom_inv_comps_preint_rec.organization_id
, p_assembly_item_number => c_bom_inv_comps_preint_rec.assembly_item_number
, p_comp_item_number => c_bom_inv_comps_preint_rec.component_item_number
, p_emf_header_id => l_header_id
, p_error_flag => l_error_flag
);
---------------------------------------------------------------------
--Validating Sub Inventory
---------------------------------------------------------------------
IF c_bom_inv_comps_preint_rec.supply_subinventory IS NOT NULL
THEN
BEGIN
SELECT msi.secondary_inventory_name
INTO l_sub_inv_name
FROM mtl_secondary_inventories msi
, org_organization_definitions org
WHERE msi.organization_id = org.organization_id
AND org.organization_code =
c_bom_inv_comps_preint_rec.organization_code
AND secondary_inventory_name =
LTRIM
(RTRIM
(c_bom_inv_comps_preint_rec.supply_subinventory)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
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-00003'
, p_identifier2 => 'Invalid Sub Inventory'
, p_identifier3 => c_bom_inv_comps_preint_rec.organization_code
, p_identifier4 => c_bom_inv_comps_preint_rec.assembly_item_number
, p_identifier5 => c_bom_inv_comps_preint_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
WHEN OTHERS
THEN
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-00003'
, p_identifier2 => 'Other Exception while validating Sub Inventory - Oracle Error : '
|| SQLERRM
, p_identifier3 => c_bom_inv_comps_preint_rec.organization_code
, p_identifier4 => c_bom_inv_comps_preint_rec.assembly_item_number
, p_identifier5 => c_bom_inv_comps_preint_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END;
END IF;
---------------------------------------------------------------------
--Validating Locator
---------------------------------------------------------------------
IF c_bom_inv_comps_preint_rec.supply_locator_id IS NOT NULL
THEN
l_output_message := NULL;
BEGIN
SELECT milk.inventory_location_id
INTO l_locator_id
FROM mtl_item_locations_kfv milk
, org_organization_definitions org
WHERE milk.organization_id = org.organization_id
AND org.organization_code =
c_bom_inv_comps_preint_rec.organization_code
AND milk.inventory_location_id =
c_bom_inv_comps_preint_rec.supply_locator_id
AND milk.subinventory_code =
c_bom_inv_comps_preint_rec.supply_subinventory;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
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-00003'
, p_identifier2 => 'No locator found for the given supply locator , '
|| 'inventory org and supply sub inventory combination'
, p_identifier3 => c_bom_inv_comps_preint_rec.organization_code
, p_identifier4 => c_bom_inv_comps_preint_rec.assembly_item_number
, p_identifier5 => c_bom_inv_comps_preint_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
WHEN TOO_MANY_ROWS
THEN
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-00003'
, p_identifier2 => 'More than one locator found for the given supply locator , inventory org and supply sub inventory combination'
, p_identifier3 => c_bom_inv_comps_preint_rec.organization_code
, p_identifier4 => c_bom_inv_comps_preint_rec.assembly_item_number
, p_identifier5 => c_bom_inv_comps_preint_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
WHEN OTHERS
THEN
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-00003'
, p_identifier2 => 'Other Exception while validating Locator , Oracle Error : '
, p_identifier3 => c_bom_inv_comps_preint_rec.organization_code
, p_identifier4 => c_bom_inv_comps_preint_rec.assembly_item_number
, p_identifier5 => c_bom_inv_comps_preint_rec.component_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_flag := 'Y';
END;
END IF;
IF l_error_flag = 'Y'
THEN
UPDATE xx_bom_inv_comps_preint
SET status = 'ER'
WHERE bom_preint_id = c_bom_inv_comps_preint_rec.bom_preint_id
AND assembly_item_number =
c_bom_inv_comps_preint_rec.assembly_item_number
AND component_item_number =
c_bom_inv_comps_preint_rec.component_item_number;
ELSIF l_error_flag = 'N'
THEN
---------------------------------------------------------------------------------------
--Move BOM Header data into Interface Table
---------------------------------------------------------------------------------------
l_item_num := l_item_num
+ 1;
l_successful_recs := l_successful_recs
+ 1;
c_bom_inv_comps_preint_rec.process_flag := 1;
c_bom_inv_comps_preint_rec.assembly_type := 1;
c_bom_inv_comps_preint_rec.transaction_type := 'CREATE';
INSERT INTO bom_inventory_comps_interface
(organization_code
, assembly_item_number
, component_item_number
, component_quantity
, effectivity_date
, item_num
, assembly_type
, process_flag
, transaction_type
)
VALUES (c_bom_inv_comps_preint_rec.organization_code
, c_bom_inv_comps_preint_rec.assembly_item_number
, c_bom_inv_comps_preint_rec.component_item_number
, c_bom_inv_comps_preint_rec.component_quantity
, c_bom_inv_comps_preint_rec.effectivity_date
, l_item_num
, c_bom_inv_comps_preint_rec.assembly_type
, c_bom_inv_comps_preint_rec.process_flag
, c_bom_inv_comps_preint_rec.transaction_type
);
UPDATE xx_bom_inv_comps_preint
SET status = 'PR'
WHERE bom_preint_id = c_bom_inv_comps_preint_rec.bom_preint_id
AND assembly_item_number =
c_bom_inv_comps_preint_rec.assembly_item_number
AND component_item_number =
c_bom_inv_comps_preint_rec.component_item_number;
END IF;
END LOOP; -- bom component
END LOOP; -- bom header
EXCEPTION
WHEN OTHERS
THEN
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;
----------------------------------------------------------------------------
--Initialize apps
----------------------------------------------------------------------------
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
);
----------------------------------------------------------------------------
--Run concurrent program "Import Bills an Routings"
----------------------------------------------------------------------------
IF l_successful_recs > 0
THEN
BEGIN
l_request_id :=
fnd_request.submit_request
(application => 'BOM'
, program => 'BMCOIN'
, description => 'Import Bills and Routings'
, argument1 => 1
, argument2 => 1
, argument3 => 2
, argument4 => 1
);
COMMIT;
l_return :=
fnd_concurrent.wait_for_request (request_id => l_request_id
, INTERVAL => 60
, max_wait => 60
, phase => l_phase
, status => l_vstatus
, dev_phase => l_dev_phase
, dev_status => l_dev_status
, MESSAGE => l_message
);
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
('Unexpected Exception While Submitting BOM Import API , 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 => 'Unexpected Exception While Submitting BOM Import API , Oracle Error : '
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- Display Errors occurred in BOM Import process
BEGIN
FOR c_import_errors_rec IN c_import_errors (l_request_id)
LOOP
xx_trace.h (' Error in BOM import process : '
|| c_import_errors_rec.error_message
);
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 => ' Error during BOM import process : '
|| c_import_errors_rec.error_message
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END LOOP;
END;
END IF;
------------------------------------------------------------------------------
--Purge Pre interface table after successfull import
------------------------------------------------------------------------------
BEGIN
DELETE FROM xx_bom_bill_of_mtls_preint
WHERE status = 'PR';
DELETE FROM xx_bom_inv_comps_preint
WHERE status = 'PR';
END;
----------------------------------------------------------------------------
-- Call Insert Summary Count
-- Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------
l_cntrl_error_recs := l_cntrl_processed_recs
- l_cntrl_successful_recs;
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_cntrl_processed_recs
, p_successful_recs => l_cntrl_successful_recs
, p_error_recs => l_cntrl_error_recs
);
l_return_value := NULL;
IF l_processed_recs > 0
THEN
l_error_recs := l_processed_recs
- l_successful_recs;
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| ' - Business Validations '
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
END IF;
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 To EMF 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');
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00006'
, 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
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*--------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
-----------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*---------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
PROCEDURE validate_org_item (
p_org_code IN org_organization_definitions.organization_code%TYPE
, p_org_id OUT org_organization_definitions.organization_id%TYPE
, p_assembly_item_number IN mtl_system_items_b.segment1%TYPE
, p_comp_item_number IN mtl_system_items_b.segment1%TYPE
, p_emf_header_id IN xx_emf_message_headers.header_id%TYPE
, p_error_flag IN OUT VARCHAR2
)
IS
l_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_item_type VARCHAR2 (10);
l_return_value NUMBER := NULL;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER;
l_org_name org_organization_definitions.organization_name%TYPE;
l_org_code org_organization_definitions.organization_code%TYPE
:= p_org_code;
BEGIN
-------------------------------------------------------------------------------
--Validating Organization
-------------------------------------------------------------------------------
BEGIN
xx_common_validations_pkg.validate_organization
(p_organization_name => l_org_name
, p_organization_code => l_org_code
, p_organization_id => p_org_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_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in Validating Organization Code : '
|| l_output_message
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END IF;
END;
-------------------------------------------------------------------------------
--Validating Assembly Item
-------------------------------------------------------------------------------
BEGIN
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE segment1 = p_assembly_item_number
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Assembly Item is not valid'
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Other Exception occurred during Assembly Item validation , Oracle Error : '
|| SQLERRM
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END;
-------------------------------------------------------------------------------
--Validating Assembly Item BOM enabled
-------------------------------------------------------------------------------
BEGIN
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b msi
, org_organization_definitions org
WHERE msi.segment1 = p_assembly_item_number
AND msi.organization_id = org.organization_id
AND org.organization_code = p_org_code
AND NVL (msi.bom_enabled_flag, 'N') = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Assembly Item is not BOM enabled'
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Other Exception occurred during '
|| l_item_type
|| 'Item validation , Oracle Error : '
|| SQLERRM
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END;
---------------------------------------------------------------------
--Validating Assembly Item in organization
---------------------------------------------------------------------
BEGIN
xx_common_validations_pkg.validate_item_in_organization
(p_item => p_assembly_item_number
, p_organization_code => p_org_code
, p_item_id => l_item_id
, p_organization_id => p_org_id
, p_process_status => l_return_value
, 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_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in Validating Assembly Item for the organization '
|| l_org_code
|| ' - '
|| l_output_message
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END IF;
END;
---------------------------------------------------------------------
--Validating Component Item
---------------------------------------------------------------------
IF p_comp_item_number IS NOT NULL
THEN
BEGIN
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE segment1 = p_comp_item_number
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Component Item is not valid'
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => p_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Other Exception occurred during Component Item validation , Oracle Error : '
|| SQLERRM
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END;
--Validating Component Item in organization
BEGIN
xx_common_validations_pkg.validate_item_in_organization
(p_item => p_comp_item_number
, p_organization_code => p_org_code
, p_item_id => l_item_id
, p_organization_id => p_org_id
, p_process_status => l_return_value
, 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_emf_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in Validating Component Item for the organization '
|| p_org_code
|| ' - '
|| l_output_message
, p_identifier3 => p_org_code
, p_identifier4 => p_assembly_item_number
, p_identifier5 => p_comp_item_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
p_error_flag := 'Y';
END IF;
END;
END IF;
END validate_org_item;
END xx_bom_import_pkg;
/
SHOW error