CREATE OR REPLACE PACKAGE BODY XX_INVCNV02_COST_UPLOAD_PKG
AS
/********************************************************************************************
Package Name : XX_INVCNV02_COST_UPLOAD_PKG
Author's Name : Madhu Dhare
Date Written : 18-Dec-2014
RICEW Object id : INV_CNV_02
Purpose : Package Body
Program Style :
--
Maintenance History:
Version Date: Name Remarks
----------- ------------- ------------------ ---------------
1.0 18-Dec-14 Madhu Dhare Initial Version
***************************************************************************************************/
--
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate organization code and cost type
* for the Cost Upload
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC(
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate item cost
* for the Cost Upload in Item details table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC(
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate resource cost
* for the Cost Upload in Resource Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate resource overhead cost
* for the Cost Upload in Resource Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate department overhead cost
* for the Cost Upload in Department Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Cost upload in Item,Resource and overhead tables.Perform
control and business level validations .
It will load the data into the interface tables and call the
Oracle Import Program to Upload Cost.
*******************************************************************************************************/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_option IN VARCHAR2,
p_run_mode IN VARCHAR2,
p_cost_type IN VARCHAR2
)
IS
--Cursor to select cost data based on run option from staging table
CURSOR c_cst_details (p_rec_type VARCHAR2)
IS
SELECT *
FROM XX_CST_ITEM_UPLOAD_STG
WHERE status = 'NW'
AND record_type != 'CNT'
AND record_type =
DECODE (p_rec_type,
'ITM', 'ITM',
'RSC', 'RSC',
'OHD', 'OHD',
record_type
);
--FOR UPDATE;
--CURSOR TO INSERT ITEM DATA INTO INTERFACE TABLE
--
--Cursor to insert in Cost Details Interface Table
CURSOR c_details_preint
IS
SELECT *
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Cost Interface Table
CURSOR c_cost_resource
IS
SELECT *
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Overheads Interface Table
CURSOR c_resc_overhead
IS
SELECT *
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'IP';
--Cursor to insert in Department Overheads Interface Table
CURSOR c_dept_overhead
IS
SELECT *
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val1 NUMBER;
l_seq_val2 NUMBER;
l_seq_val3 NUMBER;
l_seq_val4 NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE := 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_organization_id NUMBER;
l_count NUMBER :=0 ;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_inventory_item_id NUMBER;
l_resource_id NUMBER;
l_department_id NUMBER;
l_run_option VARCHAR2 (3);
--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_INVCNV02_COST_UPLOAD_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_02'; --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;
l_data_count NUMBER; --record type variable for assigning width to error section
l_processed_recs_itm NUMBER := 0; --stores total records for summary report of Item Cost Interface
l_successful_recs_itm NUMBER := 0; --stores total successful records for summary report of Item Cost Interface
l_error_recs_itm NUMBER := 0; --stores total error records for the summary report of Item Cost Interface
l_processed_recs_rsc NUMBER := 0; --stores total records for summary report of Resource Cost Interface
l_successful_recs_rsc NUMBER := 0; --stores total successful records for summary report of Resource Cost Interfaace
l_error_recs_rsc NUMBER := 0; --stores total error records for the summary report of Resource Cost Interface
l_processed_recs_rohd NUMBER := 0; --stores total records for summary report of Resource Overhead Cost Interface
l_successful_recs_rohd NUMBER := 0; --stores total successful records for summary report of Resource Overhead Cost Interface
l_error_recs_rohd NUMBER := 0; --stores total error records for the Resource Overhead summary report
l_processed_recs_dohd NUMBER := 0; --stores total records for summary report of Department Overhead Cost Interface
l_successful_recs_dohd NUMBER := 0; --stores total successful records for summary report of Department Overhead Cost Interface
l_error_recs_dohd NUMBER := 0; --stores total error records for the Department Overhead Cost 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
e_cntrl_fail EXCEPTION;
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 := 'Organization Code'; --Fourth Error Header
l_error_rec.identifier5 := 'Cost Type'; --Fifth Error Header
l_error_rec.identifier6 := 'Item Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Resource Code'; --Seventh Error Header
l_error_rec.identifier8 := 'Department Code'; --Eighth Error Header
--
-- Insert error header
--
l_return_value := xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'F'
THEN -- --Checking for record count mis-match
BEGIN
SELECT COUNT(*)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type != 'CNT';
--
SELECT DECODE(rec_count,l_data_count,0,1)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type = 'CNT';
--
IF l_data_count !=0
THEN
xx_trace.l ('The received data file is not a complete data file.Exiting the program');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'The received data file is not a complete data file.Exiting the program'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END IF;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END;
/************* CONTROL LEVEL VALIDATIONS *******************/
BEGIN
FOR c_cst_details_rec IN c_cst_details (p_run_option)
LOOP
l_error_flag :='N';
IF c_cst_details_rec.record_type = p_run_option OR p_run_option = 'ALL'
THEN
-- Validate unconditionally if Organization Code is null
IF c_cst_details_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_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate unconditionally if Cost Type is null
IF c_cst_details_rec.cost_type IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Type 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 => 'Cost Type value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF; --
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END IF;
END LOOP;
--
-- Control validations for Item Cost
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
-- Validations specific for 'ITM' Cost Load
FOR c_cst_details_rec IN c_cst_details ('ITM')
LOOP
-- Validate if Item Number is null
IF c_cst_details_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Segment 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 => 'Item Segment value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Cost Element is null
IF c_cst_details_rec.cost_element IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element 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 => 'Cost Element value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Usage Rate or Amount is null
IF c_cst_details_rec.usage_rate_or_amount IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Usage rate or amount 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 => 'Usage rate or amount value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table Status. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Validations specific for 'RSC' Cost Load
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('RSC')
LOOP
IF c_cst_details_rec.resource_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Code 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 => 'Resource Code cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF c_cst_details_rec.resource_rate IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Rate 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 => 'Resource Rate cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Resource'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type RSC must be Resource.');
--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 => 'Cost Element value for record type RSC must be Resource.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Control Validation for Overhead Cost
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('OHD')
LOOP
IF c_cst_details_rec.resource_code IS NULL AND c_cst_details_rec.department_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Resource Code and Department Code both 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 => 'Resource Code and Department Code both cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.resource_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if resource code exists then overhead code cannnot 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 => 'For record type OHD if resource code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.department_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if department code exists then overhead code cannnot 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 => 'For record type OHD if department code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Overhead'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type OHD must be Overhead.');
--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 => 'Cost Element value for record type OHD must be Overhead.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
COMMIT;
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_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--END of Control Level Validation
/*---------------------------------------------------------------------------------------------------
LOAD DATA INTO PRE-INTERFACE TABLE
---------------------------------------------------------------------------------------------------*/
BEGIN
FOR c_cst_details_rec1 IN c_cst_details (p_run_option)
LOOP
IF p_run_option = 'ITM' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'ITM')
THEN
--
SELECT XX_CST_ITEM_DTLS_PREINT_S.NEXTVAL
INTO l_seq_val1
FROM DUAL;
INSERT INTO XX_CST_ITEM_DTLS_PREINT
(status,
record_id,
process_flag,
item_number,
organization_code,
usage_rate_or_amount,
item_cost,
cost_type,
cost_element,
resource_rate,
resource_code,
department,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val1,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.item_number,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.usage_rate_or_amount,
c_cst_details_rec1.item_cost,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.cost_element,
c_cst_details_rec1.resource_rate,
c_cst_details_rec1.resource_code,
c_cst_details_rec1.department_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF p_run_option = 'RSC' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'RSC')
THEN
--
SELECT XX_CST_RESOURCE_COSTS_PREINT_S.NEXTVAL
INTO l_seq_val2
FROM DUAL;
INSERT INTO XX_CST_RESOURCE_COSTS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
resource_rate,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val2,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.resource_rate,
l_request_id, SYSDATE, l_user_id,
SYSDATE, l_user_id,l_user_id
);
END IF;
IF p_run_option = 'OHD' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'OHD')
THEN
IF c_cst_details_rec1.resource_code IS NOT NULL AND c_cst_details_rec1.overhead_code IS NOT NULL
THEN
--
SELECT XX_CST_RES_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val3
FROM DUAL;
INSERT INTO XX_CST_RES_OVERHEADS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
overhead,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES('IP',
l_seq_val3,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF c_cst_details_rec1.department_code IS NOT NULL
THEN
--
SELECT XX_CST_DEPT_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val4
FROM DUAL;
INSERT INTO XX_CST_DEPT_OVERHEADS_PREINT
(status,
record_id,
process_flag,
department_code,
cost_type,
organization_code,
overhead,
rate_or_amount,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val4,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.department_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
c_cst_details_rec1.rate_or_amount,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
END IF;
END LOOP;
--
COMMIT;
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
);
END;
END IF; --p_run_mode = 'F'
/*----------------------------------------------------------------------------
*********** BUSINESS VALIDATIONS ********************
----------------------------------------------------------------------------*/
BEGIN
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
l_processed_recs_itm := l_processed_recs_itm + 1; --Counter for total records
--
--Call to procedure for Item validation
XX_COMMON_VALIDATION_PRC
(c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_ITM_COST_PRC (c_details_preint_rec,
l_header_id,
l_error_flag
);
-- Updating pre interface table with status 'ER' where record_status in('NW','PF')
-- Update Item Cost Pre-Interface Table
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Details Pre-Interface
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'ER'
WHERE item_number = c_details_preint_rec.item_number
AND organization_code = c_details_preint_rec.organization_code
AND record_id = c_details_preint_rec.record_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
-- p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_itm := l_successful_recs_itm + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
--
-- Businesss Validation for Resource Cost Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
l_processed_recs_rsc := l_processed_recs_rsc + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_cost_resource_rec.organization_code,
c_cost_resource_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_COST_PRC (c_cost_resource_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Cost Pre-Interface Table
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'ER'
WHERE resource_rate = c_cost_resource_rec.resource_rate
AND organization_code = c_cost_resource_rec.organization_code
AND record_id = c_cost_resource_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
p_identifier7 => c_cost_resource_rec.resource_code,
-- p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rsc := l_successful_recs_rsc + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
-- Businesss Validation for Resource Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
IF c_resc_overhead_rec.overhead IS NOT NULL AND c_resc_overhead_rec.resource_code IS NOT NULL
THEN
l_processed_recs_rohd := l_processed_recs_rohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_OHD_PRC (c_resc_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Overhead Pre-Interface Table
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'ER'
WHERE resource_code = c_resc_overhead_rec.resource_code
AND overhead = c_resc_overhead_rec.overhead
AND organization_code = c_resc_overhead_rec.organization_code
AND record_id = c_resc_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
--p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rohd := l_successful_recs_rohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
-- Businesss Validation for Department Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
IF c_dept_overhead_rec.overhead IS NOT NULL AND c_dept_overhead_rec.department_code IS NOT NULL
THEN
l_processed_recs_dohd := l_processed_recs_dohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_DEPT_OHD_PRC (c_dept_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Department Overhead Pre-Interface Table
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'ER'
WHERE department_code = c_dept_overhead_rec.department_code
AND overhead = c_dept_overhead_rec.overhead
AND organization_code = c_dept_overhead_rec.organization_code
AND record_id = c_dept_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
-- p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_dohd := l_successful_recs_dohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
--
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-12300',
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
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
--deriving inventory_item_id
BEGIN
SELECT msi.inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
, mtl_parameters mp
WHERE msi.segment1 = c_details_preint_rec.item_number
AND mp.organization_id = msi.organization_id
AND mp.organization_code = c_details_preint_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
request_id,
organization_code,
cost_type,
inventory_item,
department,
activity,
resource_code,
basis_resource_code,
cost_element,
ERROR_TYPE,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_id,
process_flag,
item_number,
transaction_type,
yielded_cost,
ERROR_CODE,
error_explanation,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
inventory_asset_flag,
error_flag,
group_description
)
VALUES (l_inventory_item_id,
c_details_preint_rec.cost_type_id,
c_details_preint_rec.last_update_date,
c_details_preint_rec.last_updated_by,
c_details_preint_rec.creation_date,
c_details_preint_rec.created_by,
c_details_preint_rec.last_update_login,
c_details_preint_rec.GROUP_ID,
c_details_preint_rec.organization_id,
c_details_preint_rec.operation_sequence_id,
c_details_preint_rec.operation_seq_num,
c_details_preint_rec.department_id,
c_details_preint_rec.level_type,
c_details_preint_rec.activity_id,
c_details_preint_rec.resource_seq_num,
c_details_preint_rec.resource_id,
c_details_preint_rec.resource_rate,
c_details_preint_rec.item_units,
c_details_preint_rec.activity_units,
c_details_preint_rec.usage_rate_or_amount,
c_details_preint_rec.basis_type,
c_details_preint_rec.basis_resource_id,
c_details_preint_rec.basis_factor,
c_details_preint_rec.net_yield_or_shrinkage_factor,
c_details_preint_rec.item_cost,
c_details_preint_rec.cost_element_id,
c_details_preint_rec.rollup_source_type,
c_details_preint_rec.activity_context,
c_details_preint_rec.request_id,
c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
c_details_preint_rec.inventory_item_id,
c_details_preint_rec.department,
c_details_preint_rec.activity,
c_details_preint_rec.resource_code,
c_details_preint_rec.basis_resource_code,
c_details_preint_rec.cost_element,
c_details_preint_rec.ERROR_TYPE,
c_details_preint_rec.program_application_id,
c_details_preint_rec.program_id,
c_details_preint_rec.program_update_date,
c_details_preint_rec.attribute_category,
c_details_preint_rec.attribute1,
c_details_preint_rec.attribute2,
c_details_preint_rec.attribute3,
c_details_preint_rec.attribute4,
c_details_preint_rec.attribute5,
c_details_preint_rec.attribute6,
c_details_preint_rec.attribute7,
c_details_preint_rec.attribute8,
c_details_preint_rec.attribute9,
c_details_preint_rec.attribute10,
c_details_preint_rec.attribute11,
c_details_preint_rec.attribute12,
c_details_preint_rec.attribute13,
c_details_preint_rec.attribute14,
c_details_preint_rec.attribute15,
c_details_preint_rec.transaction_id,
c_details_preint_rec.process_flag,
c_details_preint_rec.item_number,
c_details_preint_rec.transaction_type,
c_details_preint_rec.yielded_cost,
c_details_preint_rec.ERROR_CODE,
c_details_preint_rec.error_explanation,
c_details_preint_rec.lot_size,
c_details_preint_rec.based_on_rollup_flag,
c_details_preint_rec.shrinkage_rate,
c_details_preint_rec.inventory_asset_flag,
c_details_preint_rec.error_flag,
c_details_preint_rec.group_description
);
END LOOP;
END IF;
--
--
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
--deriving resource_id
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND resource_code = c_cost_resource_rec.resource_code
AND ood.organization_code = c_cost_resource_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
--p_identifier7 => c_cost_resource_rec.resource_code,
--p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_RESOURCE_COSTS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
resource_rate,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_cost_resource_rec.resource_code,
c_cost_resource_rec.cost_type_id,
c_cost_resource_rec.cost_type,
c_cost_resource_rec.organization_id,
c_cost_resource_rec.organization_code,
c_cost_resource_rec.last_update_date,
c_cost_resource_rec.last_updated_by,
c_cost_resource_rec.creation_date,
c_cost_resource_rec.created_by,
c_cost_resource_rec.last_update_login,
c_cost_resource_rec.GROUP_ID,
c_cost_resource_rec.transaction_id,
c_cost_resource_rec.process_flag,
c_cost_resource_rec.resource_rate,
c_cost_resource_rec.request_id,
c_cost_resource_rec.ERROR_CODE,
c_cost_resource_rec.error_explanation,
c_cost_resource_rec.program_application_id,
c_cost_resource_rec.program_id,
c_cost_resource_rec.program_update_date,
c_cost_resource_rec.attribute_category,
c_cost_resource_rec.attribute1,
c_cost_resource_rec.attribute2,
c_cost_resource_rec.attribute3,
c_cost_resource_rec.attribute4,
c_cost_resource_rec.attribute5,
c_cost_resource_rec.attribute6,
c_cost_resource_rec.attribute7,
c_cost_resource_rec.attribute8,
c_cost_resource_rec.attribute9,
c_cost_resource_rec.attribute10,
c_cost_resource_rec.attribute11,
c_cost_resource_rec.attribute12,
c_cost_resource_rec.attribute13,
c_cost_resource_rec.attribute14,
c_cost_resource_rec.attribute15,
c_cost_resource_rec.error_flag,
c_cost_resource_rec.group_description
);
END LOOP;
END IF;
--
--
--
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
--deriving resource_id
IF c_resc_overhead_rec.resource_code IS NOT NULL
THEN
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND br.resource_code = c_resc_overhead_rec.resource_code
AND ood.organization_code = c_resc_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
-- p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_RES_OVERHEADS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_resc_overhead_rec.resource_code,
c_resc_overhead_rec.cost_type_id,
c_resc_overhead_rec.cost_type,
c_resc_overhead_rec.organization_id,
c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.overhead_id,
c_resc_overhead_rec.overhead,
c_resc_overhead_rec.last_update_date,
c_resc_overhead_rec.last_updated_by,
c_resc_overhead_rec.creation_date,
c_resc_overhead_rec.created_by,
c_resc_overhead_rec.last_update_login,
c_resc_overhead_rec.GROUP_ID,
c_resc_overhead_rec.transaction_id,
c_resc_overhead_rec.process_flag,
c_resc_overhead_rec.request_id,
c_resc_overhead_rec.ERROR_CODE,
c_resc_overhead_rec.error_explanation,
c_resc_overhead_rec.program_application_id,
c_resc_overhead_rec.program_id,
c_resc_overhead_rec.program_update_date,
c_resc_overhead_rec.attribute_category,
c_resc_overhead_rec.attribute1,
c_resc_overhead_rec.attribute2,
c_resc_overhead_rec.attribute3,
c_resc_overhead_rec.attribute4,
c_resc_overhead_rec.attribute5,
c_resc_overhead_rec.attribute6,
c_resc_overhead_rec.attribute7,
c_resc_overhead_rec.attribute8,
c_resc_overhead_rec.attribute9,
c_resc_overhead_rec.attribute10,
c_resc_overhead_rec.attribute11,
c_resc_overhead_rec.attribute12,
c_resc_overhead_rec.attribute13,
c_resc_overhead_rec.attribute14,
c_resc_overhead_rec.attribute15,
c_resc_overhead_rec.error_flag,
c_resc_overhead_rec.group_description
);
END LOOP;
END IF;
--
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
--deriving resource_id
IF (c_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE bd.organization_id = ood.organization_id
AND bd.department_code = c_dept_overhead_rec.department_code
AND ood.organization_code = c_dept_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
--p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_DEPT_OVERHEADS_INTERFACE
(department_id,
department_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
basis_type,
rate_or_amount,
activity_id,
activity,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_department_id,
c_dept_overhead_rec.department_code,
c_dept_overhead_rec.cost_type_id,
c_dept_overhead_rec.cost_type,
c_dept_overhead_rec.organization_id,
c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.overhead_id,
c_dept_overhead_rec.overhead,
c_dept_overhead_rec.last_update_date,
c_dept_overhead_rec.last_updated_by,
c_dept_overhead_rec.creation_date,
c_dept_overhead_rec.created_by,
c_dept_overhead_rec.last_update_login,
c_dept_overhead_rec.GROUP_ID,
c_dept_overhead_rec.transaction_id,
c_dept_overhead_rec.process_flag,
c_dept_overhead_rec.basis_type,
c_dept_overhead_rec.rate_or_amount,
c_dept_overhead_rec.activity_id,
c_dept_overhead_rec.activity,
c_dept_overhead_rec.request_id,
c_dept_overhead_rec.ERROR_CODE,
c_dept_overhead_rec.error_explanation,
c_dept_overhead_rec.program_application_id,
c_dept_overhead_rec.program_id,
c_dept_overhead_rec.program_update_date,
c_dept_overhead_rec.attribute_category,
c_dept_overhead_rec.attribute1,
c_dept_overhead_rec.attribute2,
c_dept_overhead_rec.attribute3,
c_dept_overhead_rec.attribute5,
c_dept_overhead_rec.attribute6,
c_dept_overhead_rec.attribute7,
c_dept_overhead_rec.attribute8,
c_dept_overhead_rec.attribute9,
c_dept_overhead_rec.attribute10,
c_dept_overhead_rec.attribute11,
c_dept_overhead_rec.attribute12,
c_dept_overhead_rec.attribute13,
c_dept_overhead_rec.attribute14,
c_dept_overhead_rec.attribute15,
c_dept_overhead_rec.error_flag,
c_dept_overhead_rec.group_description
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into 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-00004',
p_identifier2 => 'Exception While Inserting into INTERFACE TABLE.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
--Update Item Cost Details Preinterface Table
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Cost Preinterface Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Department Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--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
SELECT COUNT (*)
INTO l_count
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
--
--
IF l_count > 0
THEN
SELECT DECODE (p_run_option, 'ITM', 1, 'RSC', 2, 'OHD', 3, 4)
INTO l_run_option
FROM DUAL;
--
l_standard_request_id :=
fnd_request.submit_request
(application => 'BOM',
program => 'CSTPCIMP',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => l_run_option, -- Import cost option
argument2 => 2, -- Mode to run this request
argument3 => 2, -- Group ID option
argument4 => NULL, -- Group ID Dummy
argument5 => NULL, -- Group ID
argument6 => p_cost_type, -- Cost type to import to
argument7 => 2 -- Delete successful rows
);
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
);
ELSE
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20, -- Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'No records To Process in Interface Table',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END;
/*----------------------------------------------------------------------------
Delete the Processed records from the Pre-interface table
----------------------------------------------------------------------------*/
BEGIN
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
DELETE FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
DELETE FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
DELETE FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
DELETE FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
END IF;
--
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
(' Exception While deleting processed records from Pre-Interface Table(s)'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => ' Exception While deleting processed records from Pre-Interface Table(s)'
|| 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;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
l_error_recs_itm := l_processed_recs_itm - l_successful_recs_itm;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Item Cost)'
, p_total_recs => l_processed_recs_itm
, p_successful_recs => l_successful_recs_itm
, p_error_recs => l_error_recs_itm
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Item Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
l_error_recs_rsc := l_processed_recs_rsc - l_successful_recs_rsc;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Cost)'
, p_total_recs => l_processed_recs_rsc
, p_successful_recs => l_successful_recs_rsc
, p_error_recs => l_error_recs_rsc
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Resource Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
l_error_recs_rohd := l_processed_recs_rohd - l_successful_recs_rohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Overhead)'
, p_total_recs => l_processed_recs_rohd
, p_successful_recs => l_successful_recs_rohd
, p_error_recs => l_error_recs_rohd
);
l_error_recs_dohd := l_processed_recs_dohd - l_successful_recs_dohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Department Overhead)'
, p_total_recs => l_processed_recs_dohd
, p_successful_recs => l_successful_recs_dohd
, p_error_recs => l_error_recs_dohd
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Overhead Cost');
END IF;
--
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
COMMIT;
END IF;
EXCEPTION --Main Exception
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--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
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20, --Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate data
* to be inserted Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC (
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_organization_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_type_id NUMBER;
--
--
BEGIN
--
p_error_flag := 'N';
--Validation of organization Code
BEGIN
l_value := NULL;
BEGIN
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while retrieving organization id for given organization code.Oracle Error-'
|| SQLERRM
);
END;
--
XX_COMMON_VALIDATIONS_PKG.VALIDATE_ORGANIZATION
(p_organization_name => l_value,
p_organization_code => p_organization_code,
p_organization_id => l_organization_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);
IF l_process_status = 1
THEN
p_error_flag := 'Y';
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 Organization Code'
|| l_output_message,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating for 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 organization.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
--Vilidation Of Cost type
BEGIN
--
SELECT a.cost_type_id
INTO l_cost_type_id
FROM cst_cost_types a
WHERE a.cost_type = p_cost_type;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Cost type does not exist 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 => 'Cost type does not exist for the Organization given',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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 Ocurred While Validating Cost Type.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 Cost Type.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in Organization Code and Cost Type Validation Procedure.
Oracle Error'
|| SQLERRM
);
END XX_COMMON_VALIDATION_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Cost Details tables..
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC (
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_inventory_item_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_element_id NUMBER;
BEGIN
--1 Begin
--p_error_flag := 'N';
--
-- Validation of Item Number
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
,org_organization_definitions ood
WHERE msi.organization_id = ood.organization_id
AND msi.segment1 = p_details_preint_rec.item_number
AND ood.organization_code = p_details_preint_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Item Does Not Exist 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 Does Not Exist for the Organization given',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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
);
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_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
-- Validating Cost Element
BEGIN
--
SELECT DISTINCT a.cost_element_id
INTO l_cost_element_id
FROM cst_cost_elements a,
cst_item_cost_details b,
org_organization_definitions od
WHERE a.cost_element_id = b.cost_element_id
AND od.organization_id = b.organization_id
AND od.organization_code = p_details_preint_rec.organization_code
AND a.cost_element = 'Material'
AND a.cost_element = p_details_preint_rec.cost_element;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--
p_error_flag := 'Y';
xx_trace.l ('Cost Element is not valid for Item Cost Import.');
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 => 'Cost Element is not valid for Item Cost Import',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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 Ocurred While Cost Element Validation.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 Cost Element.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in validate cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_ITM_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_functional_currency_flag NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_resource_costs_preint%ROWTYPE;
l_value VARCHAR2 (50);
BEGIN
--1 Begin
--p_error_flag := 'N';
--
--
--
-- Validation of Resource Code
BEGIN
--
SELECT resource_id,functional_currency_flag
INTO l_resource_id,l_functional_currency_flag
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND organization_code = p_cost_resource_rec.organization_code
AND resource_code = p_cost_resource_rec.resource_code;
--
IF l_functional_currency_flag = 1
THEN
IF p_cost_resource_rec.resource_rate IS NOT NULL
THEN
p_error_flag := 'Y';
xx_trace.l ('Resource rate should be NULL for Functional Currency Flag set to 1.');
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 => 'Resource rate should be NULL for Functional Currency Flag set to 1.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not 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 => 'Resource Code does not exist for the Organization given.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
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.l
( 'Exception in validate Resource Cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_res_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Resource Code
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND br.resource_code = p_resc_overhead_rec.resource_code
AND ood.organization_code = p_resc_overhead_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not exist 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 => 'Resource Code does not exist for the Organization given',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END;
-- Validating Resource Overhead
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_resc_overhead_rec.organization_code
AND bomr.resource_code = p_resc_overhead_rec.overhead
AND bomr.cost_element_id in (2,5);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a resource.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a resource.',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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 Ocurred While Rescource Overhead Validation.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 Rescource Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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.l
( 'Exception in validate Resource Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_OHD_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Department Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_department_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_dept_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Department Code
IF (p_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT bd.department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE ood.organization_id = bd.organization_id
AND bd.department_code = p_dept_overhead_rec.department_code
AND ood.organization_code = p_dept_overhead_rec.organization_code;
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Department does not exist 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 => 'Department does not exists for the Organization given',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Department Code .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 Department Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
-- Validating Department Overhead
IF (p_dept_overhead_rec.overhead IS NOT NULL)
THEN
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_dept_overhead_rec.organization_code
AND bomr.resource_code = p_dept_overhead_rec.overhead
AND bomr.cost_element_id = 5 ;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a department.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a department',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Ocurred While Department Overhead Validation.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 Department Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
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.l
( 'Exception in validate Department Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_DEPT_OHD_PRC;
END XX_INVCNV02_COST_UPLOAD_PKG;
/
AS
/********************************************************************************************
Package Name : XX_INVCNV02_COST_UPLOAD_PKG
Author's Name : Madhu Dhare
Date Written : 18-Dec-2014
RICEW Object id : INV_CNV_02
Purpose : Package Body
Program Style :
--
Maintenance History:
Version Date: Name Remarks
----------- ------------- ------------------ ---------------
1.0 18-Dec-14 Madhu Dhare Initial Version
***************************************************************************************************/
--
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate organization code and cost type
* for the Cost Upload
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC(
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate item cost
* for the Cost Upload in Item details table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC(
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate resource cost
* for the Cost Upload in Resource Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate resource overhead cost
* for the Cost Upload in Resource Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate department overhead cost
* for the Cost Upload in Department Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Cost upload in Item,Resource and overhead tables.Perform
control and business level validations .
It will load the data into the interface tables and call the
Oracle Import Program to Upload Cost.
*******************************************************************************************************/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_option IN VARCHAR2,
p_run_mode IN VARCHAR2,
p_cost_type IN VARCHAR2
)
IS
--Cursor to select cost data based on run option from staging table
CURSOR c_cst_details (p_rec_type VARCHAR2)
IS
SELECT *
FROM XX_CST_ITEM_UPLOAD_STG
WHERE status = 'NW'
AND record_type != 'CNT'
AND record_type =
DECODE (p_rec_type,
'ITM', 'ITM',
'RSC', 'RSC',
'OHD', 'OHD',
record_type
);
--FOR UPDATE;
--CURSOR TO INSERT ITEM DATA INTO INTERFACE TABLE
--
--Cursor to insert in Cost Details Interface Table
CURSOR c_details_preint
IS
SELECT *
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Cost Interface Table
CURSOR c_cost_resource
IS
SELECT *
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Overheads Interface Table
CURSOR c_resc_overhead
IS
SELECT *
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'IP';
--Cursor to insert in Department Overheads Interface Table
CURSOR c_dept_overhead
IS
SELECT *
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val1 NUMBER;
l_seq_val2 NUMBER;
l_seq_val3 NUMBER;
l_seq_val4 NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE := 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_organization_id NUMBER;
l_count NUMBER :=0 ;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_inventory_item_id NUMBER;
l_resource_id NUMBER;
l_department_id NUMBER;
l_run_option VARCHAR2 (3);
--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_INVCNV02_COST_UPLOAD_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_02'; --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;
l_data_count NUMBER; --record type variable for assigning width to error section
l_processed_recs_itm NUMBER := 0; --stores total records for summary report of Item Cost Interface
l_successful_recs_itm NUMBER := 0; --stores total successful records for summary report of Item Cost Interface
l_error_recs_itm NUMBER := 0; --stores total error records for the summary report of Item Cost Interface
l_processed_recs_rsc NUMBER := 0; --stores total records for summary report of Resource Cost Interface
l_successful_recs_rsc NUMBER := 0; --stores total successful records for summary report of Resource Cost Interfaace
l_error_recs_rsc NUMBER := 0; --stores total error records for the summary report of Resource Cost Interface
l_processed_recs_rohd NUMBER := 0; --stores total records for summary report of Resource Overhead Cost Interface
l_successful_recs_rohd NUMBER := 0; --stores total successful records for summary report of Resource Overhead Cost Interface
l_error_recs_rohd NUMBER := 0; --stores total error records for the Resource Overhead summary report
l_processed_recs_dohd NUMBER := 0; --stores total records for summary report of Department Overhead Cost Interface
l_successful_recs_dohd NUMBER := 0; --stores total successful records for summary report of Department Overhead Cost Interface
l_error_recs_dohd NUMBER := 0; --stores total error records for the Department Overhead Cost 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
e_cntrl_fail EXCEPTION;
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 := 'Organization Code'; --Fourth Error Header
l_error_rec.identifier5 := 'Cost Type'; --Fifth Error Header
l_error_rec.identifier6 := 'Item Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Resource Code'; --Seventh Error Header
l_error_rec.identifier8 := 'Department Code'; --Eighth Error Header
--
-- Insert error header
--
l_return_value := xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'F'
THEN -- --Checking for record count mis-match
BEGIN
SELECT COUNT(*)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type != 'CNT';
--
SELECT DECODE(rec_count,l_data_count,0,1)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type = 'CNT';
--
IF l_data_count !=0
THEN
xx_trace.l ('The received data file is not a complete data file.Exiting the program');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'The received data file is not a complete data file.Exiting the program'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END IF;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END;
/************* CONTROL LEVEL VALIDATIONS *******************/
BEGIN
FOR c_cst_details_rec IN c_cst_details (p_run_option)
LOOP
l_error_flag :='N';
IF c_cst_details_rec.record_type = p_run_option OR p_run_option = 'ALL'
THEN
-- Validate unconditionally if Organization Code is null
IF c_cst_details_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_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate unconditionally if Cost Type is null
IF c_cst_details_rec.cost_type IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Type 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 => 'Cost Type value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF; --
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END IF;
END LOOP;
--
-- Control validations for Item Cost
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
-- Validations specific for 'ITM' Cost Load
FOR c_cst_details_rec IN c_cst_details ('ITM')
LOOP
-- Validate if Item Number is null
IF c_cst_details_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Segment 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 => 'Item Segment value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Cost Element is null
IF c_cst_details_rec.cost_element IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element 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 => 'Cost Element value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Usage Rate or Amount is null
IF c_cst_details_rec.usage_rate_or_amount IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Usage rate or amount 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 => 'Usage rate or amount value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table Status. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Validations specific for 'RSC' Cost Load
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('RSC')
LOOP
IF c_cst_details_rec.resource_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Code 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 => 'Resource Code cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF c_cst_details_rec.resource_rate IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Rate 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 => 'Resource Rate cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Resource'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type RSC must be Resource.');
--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 => 'Cost Element value for record type RSC must be Resource.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Control Validation for Overhead Cost
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('OHD')
LOOP
IF c_cst_details_rec.resource_code IS NULL AND c_cst_details_rec.department_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Resource Code and Department Code both 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 => 'Resource Code and Department Code both cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.resource_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if resource code exists then overhead code cannnot 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 => 'For record type OHD if resource code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.department_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if department code exists then overhead code cannnot 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 => 'For record type OHD if department code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Overhead'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type OHD must be Overhead.');
--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 => 'Cost Element value for record type OHD must be Overhead.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
COMMIT;
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_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--END of Control Level Validation
/*---------------------------------------------------------------------------------------------------
LOAD DATA INTO PRE-INTERFACE TABLE
---------------------------------------------------------------------------------------------------*/
BEGIN
FOR c_cst_details_rec1 IN c_cst_details (p_run_option)
LOOP
IF p_run_option = 'ITM' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'ITM')
THEN
--
SELECT XX_CST_ITEM_DTLS_PREINT_S.NEXTVAL
INTO l_seq_val1
FROM DUAL;
INSERT INTO XX_CST_ITEM_DTLS_PREINT
(status,
record_id,
process_flag,
item_number,
organization_code,
usage_rate_or_amount,
item_cost,
cost_type,
cost_element,
resource_rate,
resource_code,
department,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val1,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.item_number,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.usage_rate_or_amount,
c_cst_details_rec1.item_cost,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.cost_element,
c_cst_details_rec1.resource_rate,
c_cst_details_rec1.resource_code,
c_cst_details_rec1.department_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF p_run_option = 'RSC' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'RSC')
THEN
--
SELECT XX_CST_RESOURCE_COSTS_PREINT_S.NEXTVAL
INTO l_seq_val2
FROM DUAL;
INSERT INTO XX_CST_RESOURCE_COSTS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
resource_rate,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val2,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.resource_rate,
l_request_id, SYSDATE, l_user_id,
SYSDATE, l_user_id,l_user_id
);
END IF;
IF p_run_option = 'OHD' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'OHD')
THEN
IF c_cst_details_rec1.resource_code IS NOT NULL AND c_cst_details_rec1.overhead_code IS NOT NULL
THEN
--
SELECT XX_CST_RES_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val3
FROM DUAL;
INSERT INTO XX_CST_RES_OVERHEADS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
overhead,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES('IP',
l_seq_val3,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF c_cst_details_rec1.department_code IS NOT NULL
THEN
--
SELECT XX_CST_DEPT_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val4
FROM DUAL;
INSERT INTO XX_CST_DEPT_OVERHEADS_PREINT
(status,
record_id,
process_flag,
department_code,
cost_type,
organization_code,
overhead,
rate_or_amount,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val4,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.department_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
c_cst_details_rec1.rate_or_amount,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
END IF;
END LOOP;
--
COMMIT;
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
);
END;
END IF; --p_run_mode = 'F'
/*----------------------------------------------------------------------------
*********** BUSINESS VALIDATIONS ********************
----------------------------------------------------------------------------*/
BEGIN
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
l_processed_recs_itm := l_processed_recs_itm + 1; --Counter for total records
--
--Call to procedure for Item validation
XX_COMMON_VALIDATION_PRC
(c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_ITM_COST_PRC (c_details_preint_rec,
l_header_id,
l_error_flag
);
-- Updating pre interface table with status 'ER' where record_status in('NW','PF')
-- Update Item Cost Pre-Interface Table
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Details Pre-Interface
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'ER'
WHERE item_number = c_details_preint_rec.item_number
AND organization_code = c_details_preint_rec.organization_code
AND record_id = c_details_preint_rec.record_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
-- p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_itm := l_successful_recs_itm + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
--
-- Businesss Validation for Resource Cost Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
l_processed_recs_rsc := l_processed_recs_rsc + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_cost_resource_rec.organization_code,
c_cost_resource_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_COST_PRC (c_cost_resource_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Cost Pre-Interface Table
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'ER'
WHERE resource_rate = c_cost_resource_rec.resource_rate
AND organization_code = c_cost_resource_rec.organization_code
AND record_id = c_cost_resource_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
p_identifier7 => c_cost_resource_rec.resource_code,
-- p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rsc := l_successful_recs_rsc + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
-- Businesss Validation for Resource Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
IF c_resc_overhead_rec.overhead IS NOT NULL AND c_resc_overhead_rec.resource_code IS NOT NULL
THEN
l_processed_recs_rohd := l_processed_recs_rohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_OHD_PRC (c_resc_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Overhead Pre-Interface Table
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'ER'
WHERE resource_code = c_resc_overhead_rec.resource_code
AND overhead = c_resc_overhead_rec.overhead
AND organization_code = c_resc_overhead_rec.organization_code
AND record_id = c_resc_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
--p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rohd := l_successful_recs_rohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
-- Businesss Validation for Department Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
IF c_dept_overhead_rec.overhead IS NOT NULL AND c_dept_overhead_rec.department_code IS NOT NULL
THEN
l_processed_recs_dohd := l_processed_recs_dohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_DEPT_OHD_PRC (c_dept_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Department Overhead Pre-Interface Table
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'ER'
WHERE department_code = c_dept_overhead_rec.department_code
AND overhead = c_dept_overhead_rec.overhead
AND organization_code = c_dept_overhead_rec.organization_code
AND record_id = c_dept_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
-- p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_dohd := l_successful_recs_dohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
--
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-12300',
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
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
--deriving inventory_item_id
BEGIN
SELECT msi.inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
, mtl_parameters mp
WHERE msi.segment1 = c_details_preint_rec.item_number
AND mp.organization_id = msi.organization_id
AND mp.organization_code = c_details_preint_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
request_id,
organization_code,
cost_type,
inventory_item,
department,
activity,
resource_code,
basis_resource_code,
cost_element,
ERROR_TYPE,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_id,
process_flag,
item_number,
transaction_type,
yielded_cost,
ERROR_CODE,
error_explanation,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
inventory_asset_flag,
error_flag,
group_description
)
VALUES (l_inventory_item_id,
c_details_preint_rec.cost_type_id,
c_details_preint_rec.last_update_date,
c_details_preint_rec.last_updated_by,
c_details_preint_rec.creation_date,
c_details_preint_rec.created_by,
c_details_preint_rec.last_update_login,
c_details_preint_rec.GROUP_ID,
c_details_preint_rec.organization_id,
c_details_preint_rec.operation_sequence_id,
c_details_preint_rec.operation_seq_num,
c_details_preint_rec.department_id,
c_details_preint_rec.level_type,
c_details_preint_rec.activity_id,
c_details_preint_rec.resource_seq_num,
c_details_preint_rec.resource_id,
c_details_preint_rec.resource_rate,
c_details_preint_rec.item_units,
c_details_preint_rec.activity_units,
c_details_preint_rec.usage_rate_or_amount,
c_details_preint_rec.basis_type,
c_details_preint_rec.basis_resource_id,
c_details_preint_rec.basis_factor,
c_details_preint_rec.net_yield_or_shrinkage_factor,
c_details_preint_rec.item_cost,
c_details_preint_rec.cost_element_id,
c_details_preint_rec.rollup_source_type,
c_details_preint_rec.activity_context,
c_details_preint_rec.request_id,
c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
c_details_preint_rec.inventory_item_id,
c_details_preint_rec.department,
c_details_preint_rec.activity,
c_details_preint_rec.resource_code,
c_details_preint_rec.basis_resource_code,
c_details_preint_rec.cost_element,
c_details_preint_rec.ERROR_TYPE,
c_details_preint_rec.program_application_id,
c_details_preint_rec.program_id,
c_details_preint_rec.program_update_date,
c_details_preint_rec.attribute_category,
c_details_preint_rec.attribute1,
c_details_preint_rec.attribute2,
c_details_preint_rec.attribute3,
c_details_preint_rec.attribute4,
c_details_preint_rec.attribute5,
c_details_preint_rec.attribute6,
c_details_preint_rec.attribute7,
c_details_preint_rec.attribute8,
c_details_preint_rec.attribute9,
c_details_preint_rec.attribute10,
c_details_preint_rec.attribute11,
c_details_preint_rec.attribute12,
c_details_preint_rec.attribute13,
c_details_preint_rec.attribute14,
c_details_preint_rec.attribute15,
c_details_preint_rec.transaction_id,
c_details_preint_rec.process_flag,
c_details_preint_rec.item_number,
c_details_preint_rec.transaction_type,
c_details_preint_rec.yielded_cost,
c_details_preint_rec.ERROR_CODE,
c_details_preint_rec.error_explanation,
c_details_preint_rec.lot_size,
c_details_preint_rec.based_on_rollup_flag,
c_details_preint_rec.shrinkage_rate,
c_details_preint_rec.inventory_asset_flag,
c_details_preint_rec.error_flag,
c_details_preint_rec.group_description
);
END LOOP;
END IF;
--
--
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
--deriving resource_id
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND resource_code = c_cost_resource_rec.resource_code
AND ood.organization_code = c_cost_resource_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
--p_identifier7 => c_cost_resource_rec.resource_code,
--p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_RESOURCE_COSTS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
resource_rate,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_cost_resource_rec.resource_code,
c_cost_resource_rec.cost_type_id,
c_cost_resource_rec.cost_type,
c_cost_resource_rec.organization_id,
c_cost_resource_rec.organization_code,
c_cost_resource_rec.last_update_date,
c_cost_resource_rec.last_updated_by,
c_cost_resource_rec.creation_date,
c_cost_resource_rec.created_by,
c_cost_resource_rec.last_update_login,
c_cost_resource_rec.GROUP_ID,
c_cost_resource_rec.transaction_id,
c_cost_resource_rec.process_flag,
c_cost_resource_rec.resource_rate,
c_cost_resource_rec.request_id,
c_cost_resource_rec.ERROR_CODE,
c_cost_resource_rec.error_explanation,
c_cost_resource_rec.program_application_id,
c_cost_resource_rec.program_id,
c_cost_resource_rec.program_update_date,
c_cost_resource_rec.attribute_category,
c_cost_resource_rec.attribute1,
c_cost_resource_rec.attribute2,
c_cost_resource_rec.attribute3,
c_cost_resource_rec.attribute4,
c_cost_resource_rec.attribute5,
c_cost_resource_rec.attribute6,
c_cost_resource_rec.attribute7,
c_cost_resource_rec.attribute8,
c_cost_resource_rec.attribute9,
c_cost_resource_rec.attribute10,
c_cost_resource_rec.attribute11,
c_cost_resource_rec.attribute12,
c_cost_resource_rec.attribute13,
c_cost_resource_rec.attribute14,
c_cost_resource_rec.attribute15,
c_cost_resource_rec.error_flag,
c_cost_resource_rec.group_description
);
END LOOP;
END IF;
--
--
--
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
--deriving resource_id
IF c_resc_overhead_rec.resource_code IS NOT NULL
THEN
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND br.resource_code = c_resc_overhead_rec.resource_code
AND ood.organization_code = c_resc_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
-- p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_RES_OVERHEADS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_resc_overhead_rec.resource_code,
c_resc_overhead_rec.cost_type_id,
c_resc_overhead_rec.cost_type,
c_resc_overhead_rec.organization_id,
c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.overhead_id,
c_resc_overhead_rec.overhead,
c_resc_overhead_rec.last_update_date,
c_resc_overhead_rec.last_updated_by,
c_resc_overhead_rec.creation_date,
c_resc_overhead_rec.created_by,
c_resc_overhead_rec.last_update_login,
c_resc_overhead_rec.GROUP_ID,
c_resc_overhead_rec.transaction_id,
c_resc_overhead_rec.process_flag,
c_resc_overhead_rec.request_id,
c_resc_overhead_rec.ERROR_CODE,
c_resc_overhead_rec.error_explanation,
c_resc_overhead_rec.program_application_id,
c_resc_overhead_rec.program_id,
c_resc_overhead_rec.program_update_date,
c_resc_overhead_rec.attribute_category,
c_resc_overhead_rec.attribute1,
c_resc_overhead_rec.attribute2,
c_resc_overhead_rec.attribute3,
c_resc_overhead_rec.attribute4,
c_resc_overhead_rec.attribute5,
c_resc_overhead_rec.attribute6,
c_resc_overhead_rec.attribute7,
c_resc_overhead_rec.attribute8,
c_resc_overhead_rec.attribute9,
c_resc_overhead_rec.attribute10,
c_resc_overhead_rec.attribute11,
c_resc_overhead_rec.attribute12,
c_resc_overhead_rec.attribute13,
c_resc_overhead_rec.attribute14,
c_resc_overhead_rec.attribute15,
c_resc_overhead_rec.error_flag,
c_resc_overhead_rec.group_description
);
END LOOP;
END IF;
--
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
--deriving resource_id
IF (c_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE bd.organization_id = ood.organization_id
AND bd.department_code = c_dept_overhead_rec.department_code
AND ood.organization_code = c_dept_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
--p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_DEPT_OVERHEADS_INTERFACE
(department_id,
department_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
basis_type,
rate_or_amount,
activity_id,
activity,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_department_id,
c_dept_overhead_rec.department_code,
c_dept_overhead_rec.cost_type_id,
c_dept_overhead_rec.cost_type,
c_dept_overhead_rec.organization_id,
c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.overhead_id,
c_dept_overhead_rec.overhead,
c_dept_overhead_rec.last_update_date,
c_dept_overhead_rec.last_updated_by,
c_dept_overhead_rec.creation_date,
c_dept_overhead_rec.created_by,
c_dept_overhead_rec.last_update_login,
c_dept_overhead_rec.GROUP_ID,
c_dept_overhead_rec.transaction_id,
c_dept_overhead_rec.process_flag,
c_dept_overhead_rec.basis_type,
c_dept_overhead_rec.rate_or_amount,
c_dept_overhead_rec.activity_id,
c_dept_overhead_rec.activity,
c_dept_overhead_rec.request_id,
c_dept_overhead_rec.ERROR_CODE,
c_dept_overhead_rec.error_explanation,
c_dept_overhead_rec.program_application_id,
c_dept_overhead_rec.program_id,
c_dept_overhead_rec.program_update_date,
c_dept_overhead_rec.attribute_category,
c_dept_overhead_rec.attribute1,
c_dept_overhead_rec.attribute2,
c_dept_overhead_rec.attribute3,
c_dept_overhead_rec.attribute5,
c_dept_overhead_rec.attribute6,
c_dept_overhead_rec.attribute7,
c_dept_overhead_rec.attribute8,
c_dept_overhead_rec.attribute9,
c_dept_overhead_rec.attribute10,
c_dept_overhead_rec.attribute11,
c_dept_overhead_rec.attribute12,
c_dept_overhead_rec.attribute13,
c_dept_overhead_rec.attribute14,
c_dept_overhead_rec.attribute15,
c_dept_overhead_rec.error_flag,
c_dept_overhead_rec.group_description
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into 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-00004',
p_identifier2 => 'Exception While Inserting into INTERFACE TABLE.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
--Update Item Cost Details Preinterface Table
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Cost Preinterface Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Department Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--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
SELECT COUNT (*)
INTO l_count
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
--
--
IF l_count > 0
THEN
SELECT DECODE (p_run_option, 'ITM', 1, 'RSC', 2, 'OHD', 3, 4)
INTO l_run_option
FROM DUAL;
--
l_standard_request_id :=
fnd_request.submit_request
(application => 'BOM',
program => 'CSTPCIMP',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => l_run_option, -- Import cost option
argument2 => 2, -- Mode to run this request
argument3 => 2, -- Group ID option
argument4 => NULL, -- Group ID Dummy
argument5 => NULL, -- Group ID
argument6 => p_cost_type, -- Cost type to import to
argument7 => 2 -- Delete successful rows
);
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
);
ELSE
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20, -- Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'No records To Process in Interface Table',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END;
/*----------------------------------------------------------------------------
Delete the Processed records from the Pre-interface table
----------------------------------------------------------------------------*/
BEGIN
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
DELETE FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
DELETE FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
DELETE FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
DELETE FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
END IF;
--
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
(' Exception While deleting processed records from Pre-Interface Table(s)'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => ' Exception While deleting processed records from Pre-Interface Table(s)'
|| 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;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
l_error_recs_itm := l_processed_recs_itm - l_successful_recs_itm;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Item Cost)'
, p_total_recs => l_processed_recs_itm
, p_successful_recs => l_successful_recs_itm
, p_error_recs => l_error_recs_itm
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Item Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
l_error_recs_rsc := l_processed_recs_rsc - l_successful_recs_rsc;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Cost)'
, p_total_recs => l_processed_recs_rsc
, p_successful_recs => l_successful_recs_rsc
, p_error_recs => l_error_recs_rsc
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Resource Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
l_error_recs_rohd := l_processed_recs_rohd - l_successful_recs_rohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Overhead)'
, p_total_recs => l_processed_recs_rohd
, p_successful_recs => l_successful_recs_rohd
, p_error_recs => l_error_recs_rohd
);
l_error_recs_dohd := l_processed_recs_dohd - l_successful_recs_dohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Department Overhead)'
, p_total_recs => l_processed_recs_dohd
, p_successful_recs => l_successful_recs_dohd
, p_error_recs => l_error_recs_dohd
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Overhead Cost');
END IF;
--
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
COMMIT;
END IF;
EXCEPTION --Main Exception
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--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
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20, --Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate data
* to be inserted Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC (
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_organization_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_type_id NUMBER;
--
--
BEGIN
--
p_error_flag := 'N';
--Validation of organization Code
BEGIN
l_value := NULL;
BEGIN
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while retrieving organization id for given organization code.Oracle Error-'
|| SQLERRM
);
END;
--
XX_COMMON_VALIDATIONS_PKG.VALIDATE_ORGANIZATION
(p_organization_name => l_value,
p_organization_code => p_organization_code,
p_organization_id => l_organization_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);
IF l_process_status = 1
THEN
p_error_flag := 'Y';
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 Organization Code'
|| l_output_message,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating for 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 organization.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
--Vilidation Of Cost type
BEGIN
--
SELECT a.cost_type_id
INTO l_cost_type_id
FROM cst_cost_types a
WHERE a.cost_type = p_cost_type;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Cost type does not exist 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 => 'Cost type does not exist for the Organization given',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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 Ocurred While Validating Cost Type.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 Cost Type.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in Organization Code and Cost Type Validation Procedure.
Oracle Error'
|| SQLERRM
);
END XX_COMMON_VALIDATION_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Cost Details tables..
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC (
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_inventory_item_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_element_id NUMBER;
BEGIN
--1 Begin
--p_error_flag := 'N';
--
-- Validation of Item Number
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
,org_organization_definitions ood
WHERE msi.organization_id = ood.organization_id
AND msi.segment1 = p_details_preint_rec.item_number
AND ood.organization_code = p_details_preint_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Item Does Not Exist 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 Does Not Exist for the Organization given',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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
);
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_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
-- Validating Cost Element
BEGIN
--
SELECT DISTINCT a.cost_element_id
INTO l_cost_element_id
FROM cst_cost_elements a,
cst_item_cost_details b,
org_organization_definitions od
WHERE a.cost_element_id = b.cost_element_id
AND od.organization_id = b.organization_id
AND od.organization_code = p_details_preint_rec.organization_code
AND a.cost_element = 'Material'
AND a.cost_element = p_details_preint_rec.cost_element;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--
p_error_flag := 'Y';
xx_trace.l ('Cost Element is not valid for Item Cost Import.');
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 => 'Cost Element is not valid for Item Cost Import',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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 Ocurred While Cost Element Validation.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 Cost Element.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in validate cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_ITM_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_functional_currency_flag NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_resource_costs_preint%ROWTYPE;
l_value VARCHAR2 (50);
BEGIN
--1 Begin
--p_error_flag := 'N';
--
--
--
-- Validation of Resource Code
BEGIN
--
SELECT resource_id,functional_currency_flag
INTO l_resource_id,l_functional_currency_flag
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND organization_code = p_cost_resource_rec.organization_code
AND resource_code = p_cost_resource_rec.resource_code;
--
IF l_functional_currency_flag = 1
THEN
IF p_cost_resource_rec.resource_rate IS NOT NULL
THEN
p_error_flag := 'Y';
xx_trace.l ('Resource rate should be NULL for Functional Currency Flag set to 1.');
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 => 'Resource rate should be NULL for Functional Currency Flag set to 1.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not 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 => 'Resource Code does not exist for the Organization given.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
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.l
( 'Exception in validate Resource Cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_res_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Resource Code
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND br.resource_code = p_resc_overhead_rec.resource_code
AND ood.organization_code = p_resc_overhead_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not exist 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 => 'Resource Code does not exist for the Organization given',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END;
-- Validating Resource Overhead
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_resc_overhead_rec.organization_code
AND bomr.resource_code = p_resc_overhead_rec.overhead
AND bomr.cost_element_id in (2,5);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a resource.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a resource.',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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 Ocurred While Rescource Overhead Validation.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 Rescource Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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.l
( 'Exception in validate Resource Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_OHD_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Department Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_department_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_dept_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Department Code
IF (p_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT bd.department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE ood.organization_id = bd.organization_id
AND bd.department_code = p_dept_overhead_rec.department_code
AND ood.organization_code = p_dept_overhead_rec.organization_code;
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Department does not exist 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 => 'Department does not exists for the Organization given',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Department Code .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 Department Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
-- Validating Department Overhead
IF (p_dept_overhead_rec.overhead IS NOT NULL)
THEN
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_dept_overhead_rec.organization_code
AND bomr.resource_code = p_dept_overhead_rec.overhead
AND bomr.cost_element_id = 5 ;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a department.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a department',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Ocurred While Department Overhead Validation.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 Department Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
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.l
( 'Exception in validate Department Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_DEPT_OHD_PRC;
END XX_INVCNV02_COST_UPLOAD_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_INVCNV02_COST_UPLOAD_PKG
AS
/********************************************************************************************
Package Name : XX_INVCNV02_COST_UPLOAD_PKG
Author's Name : Madhu Dhare
Date Written : 18-Dec-2006
RICEW Object id : INV_CNV_02
Purpose : Package Body
Program Style :
--
Maintenance History:
Version Date: Name Remarks
----------- ------------- ------------------ ---------------
1.0 18-Dec-06 Madhu Dhare Initial Version
***************************************************************************************************/
--
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate organization code and cost type
* for the Cost Upload
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC(
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate item cost
* for the Cost Upload in Item details table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC(
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate resource cost
* for the Cost Upload in Resource Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate resource overhead cost
* for the Cost Upload in Resource Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate department overhead cost
* for the Cost Upload in Department Overhead Cost table.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
);
--
/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Cost upload in Item,Resource and overhead tables.Perform
control and business level validations .
It will load the data into the interface tables and call the
Oracle Import Program to Upload Cost.
*******************************************************************************************************/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_option IN VARCHAR2,
p_run_mode IN VARCHAR2,
p_cost_type IN VARCHAR2
)
IS
--Cursor to select cost data based on run option from staging table
CURSOR c_cst_details (p_rec_type VARCHAR2)
IS
SELECT *
FROM XX_CST_ITEM_UPLOAD_STG
WHERE status = 'NW'
AND record_type != 'CNT'
AND record_type =
DECODE (p_rec_type,
'ITM', 'ITM',
'RSC', 'RSC',
'OHD', 'OHD',
record_type
);
--FOR UPDATE;
--CURSOR TO INSERT ITEM DATA INTO INTERFACE TABLE
--
--Cursor to insert in Cost Details Interface Table
CURSOR c_details_preint
IS
SELECT *
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Cost Interface Table
CURSOR c_cost_resource
IS
SELECT *
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'IP';
--Cursor to insert in Resource Overheads Interface Table
CURSOR c_resc_overhead
IS
SELECT *
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'IP';
--Cursor to insert in Department Overheads Interface Table
CURSOR c_dept_overhead
IS
SELECT *
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val1 NUMBER;
l_seq_val2 NUMBER;
l_seq_val3 NUMBER;
l_seq_val4 NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE := 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_organization_id NUMBER;
l_count NUMBER :=0 ;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_inventory_item_id NUMBER;
l_resource_id NUMBER;
l_department_id NUMBER;
l_run_option VARCHAR2 (3);
--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE := 'XX_INVCNV02_COST_UPLOAD_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_02'; --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;
l_data_count NUMBER; --record type variable for assigning width to error section
l_processed_recs_itm NUMBER := 0; --stores total records for summary report of Item Cost Interface
l_successful_recs_itm NUMBER := 0; --stores total successful records for summary report of Item Cost Interface
l_error_recs_itm NUMBER := 0; --stores total error records for the summary report of Item Cost Interface
l_processed_recs_rsc NUMBER := 0; --stores total records for summary report of Resource Cost Interface
l_successful_recs_rsc NUMBER := 0; --stores total successful records for summary report of Resource Cost Interfaace
l_error_recs_rsc NUMBER := 0; --stores total error records for the summary report of Resource Cost Interface
l_processed_recs_rohd NUMBER := 0; --stores total records for summary report of Resource Overhead Cost Interface
l_successful_recs_rohd NUMBER := 0; --stores total successful records for summary report of Resource Overhead Cost Interface
l_error_recs_rohd NUMBER := 0; --stores total error records for the Resource Overhead summary report
l_processed_recs_dohd NUMBER := 0; --stores total records for summary report of Department Overhead Cost Interface
l_successful_recs_dohd NUMBER := 0; --stores total successful records for summary report of Department Overhead Cost Interface
l_error_recs_dohd NUMBER := 0; --stores total error records for the Department Overhead Cost 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
e_cntrl_fail EXCEPTION;
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 := 'Organization Code'; --Fourth Error Header
l_error_rec.identifier5 := 'Cost Type'; --Fifth Error Header
l_error_rec.identifier6 := 'Item Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Resource Code'; --Seventh Error Header
l_error_rec.identifier8 := 'Department Code'; --Eighth Error Header
--
-- Insert error header
--
l_return_value := xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'F'
THEN -- --Checking for record count mis-match
BEGIN
SELECT COUNT(*)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type != 'CNT';
--
SELECT DECODE(rec_count,l_data_count,0,1)
INTO l_data_count
FROM xx_cst_item_upload_stg
WHERE record_type = 'CNT';
--
IF l_data_count !=0
THEN
xx_trace.l ('The received data file is not a complete data file.Exiting the program');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'The received data file is not a complete data file.Exiting the program'
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END IF;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Exception while verifying the record count in the data file. Exiting the program'
||SQLERRM
, p_identifier3 => NULL
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
UPDATE XX_CST_ITEM_UPLOAD_STG
SET status='ER';
END;
/************* CONTROL LEVEL VALIDATIONS *******************/
BEGIN
FOR c_cst_details_rec IN c_cst_details (p_run_option)
LOOP
l_error_flag :='N';
IF c_cst_details_rec.record_type = p_run_option OR p_run_option = 'ALL'
THEN
-- Validate unconditionally if Organization Code is null
IF c_cst_details_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_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate unconditionally if Cost Type is null
IF c_cst_details_rec.cost_type IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Type 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 => 'Cost Type value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF; --
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END IF;
END LOOP;
--
-- Control validations for Item Cost
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
-- Validations specific for 'ITM' Cost Load
FOR c_cst_details_rec IN c_cst_details ('ITM')
LOOP
-- Validate if Item Number is null
IF c_cst_details_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Segment 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 => 'Item Segment value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Cost Element is null
IF c_cst_details_rec.cost_element IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element 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 => 'Cost Element value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
-- Validate if Usage Rate or Amount is null
IF c_cst_details_rec.usage_rate_or_amount IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Usage rate or amount 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 => 'Usage rate or amount value cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table Status. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Validations specific for 'RSC' Cost Load
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('RSC')
LOOP
IF c_cst_details_rec.resource_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Code 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 => 'Resource Code cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF c_cst_details_rec.resource_rate IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Resource Rate 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 => 'Resource Rate cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Resource'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type RSC must be Resource.');
--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 => 'Cost Element value for record type RSC must be Resource.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
--
-- Control Validation for Overhead Cost
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_cst_details_rec IN c_cst_details ('OHD')
LOOP
IF c_cst_details_rec.resource_code IS NULL AND c_cst_details_rec.department_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Resource Code and Department Code both 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 => 'Resource Code and Department Code both cannot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.resource_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if resource code exists then overhead code cannnot 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 => 'For record type OHD if resource code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.department_code IS NOT NULL AND c_cst_details_rec.overhead_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('For record type OHD if department code exists then overhead code cannnot 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 => 'For record type OHD if department code exists then overhead code cannnot be null.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.resource_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
IF c_cst_details_rec.cost_element IS NOT NULL AND c_cst_details_rec.cost_element != 'Overhead'
THEN
l_error_flag := 'Y';
xx_trace.l ('Cost Element value for record type OHD must be Overhead.');
--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 => 'Cost Element value for record type OHD must be Overhead.',
p_identifier3 => c_cst_details_rec.record_id,
p_identifier4 => c_cst_details_rec.organization_code,
p_identifier5 => c_cst_details_rec.cost_type,
p_identifier6 => c_cst_details_rec.item_number,
p_identifier7 => c_cst_details_rec.department_code,
p_identifier8 => c_cst_details_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
--
IF l_error_flag = 'Y'
THEN
BEGIN
UPDATE xx_cst_item_upload_stg
SET status = 'ER'
WHERE record_id = c_cst_details_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Exception While Updating Staging Table. Oracle Error :'
|| SQLERRM
);
END;
END IF;
END LOOP;
END IF;
COMMIT;
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_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--END of Control Level Validation
/*---------------------------------------------------------------------------------------------------
LOAD DATA INTO PRE-INTERFACE TABLE
---------------------------------------------------------------------------------------------------*/
BEGIN
FOR c_cst_details_rec1 IN c_cst_details (p_run_option)
LOOP
IF p_run_option = 'ITM' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'ITM')
THEN
--
SELECT XX_CST_ITEM_DTLS_PREINT_S.NEXTVAL
INTO l_seq_val1
FROM DUAL;
INSERT INTO XX_CST_ITEM_DTLS_PREINT
(status,
record_id,
process_flag,
item_number,
organization_code,
usage_rate_or_amount,
item_cost,
cost_type,
cost_element,
resource_rate,
resource_code,
department,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val1,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.item_number,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.usage_rate_or_amount,
c_cst_details_rec1.item_cost,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.cost_element,
c_cst_details_rec1.resource_rate,
c_cst_details_rec1.resource_code,
c_cst_details_rec1.department_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF p_run_option = 'RSC' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'RSC')
THEN
--
SELECT XX_CST_RESOURCE_COSTS_PREINT_S.NEXTVAL
INTO l_seq_val2
FROM DUAL;
INSERT INTO XX_CST_RESOURCE_COSTS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
resource_rate,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val2,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.resource_rate,
l_request_id, SYSDATE, l_user_id,
SYSDATE, l_user_id,l_user_id
);
END IF;
IF p_run_option = 'OHD' OR (p_run_option = 'ALL' AND c_cst_details_rec1.record_type = 'OHD')
THEN
IF c_cst_details_rec1.resource_code IS NOT NULL AND c_cst_details_rec1.overhead_code IS NOT NULL
THEN
--
SELECT XX_CST_RES_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val3
FROM DUAL;
INSERT INTO XX_CST_RES_OVERHEADS_PREINT
(status,
record_id,
process_flag,
resource_code,
cost_type,
organization_code,
overhead,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES('IP',
l_seq_val3,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.resource_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
IF c_cst_details_rec1.department_code IS NOT NULL
THEN
--
SELECT XX_CST_DEPT_OVERHEADS_PREINT_S.NEXTVAL
INTO l_seq_val4
FROM DUAL;
INSERT INTO XX_CST_DEPT_OVERHEADS_PREINT
(status,
record_id,
process_flag,
department_code,
cost_type,
organization_code,
overhead,
rate_or_amount,
request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES ('IP',
l_seq_val4,
1, --Process Flag has to be set to 1 for processing
c_cst_details_rec1.department_code,
c_cst_details_rec1.cost_type,
c_cst_details_rec1.organization_code,
c_cst_details_rec1.overhead_code,
c_cst_details_rec1.rate_or_amount,
l_request_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id
);
END IF;
END IF;
END LOOP;
--
COMMIT;
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
);
END;
END IF; --p_run_mode = 'F'
/*----------------------------------------------------------------------------
*********** BUSINESS VALIDATIONS ********************
----------------------------------------------------------------------------*/
BEGIN
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
l_processed_recs_itm := l_processed_recs_itm + 1; --Counter for total records
--
--Call to procedure for Item validation
XX_COMMON_VALIDATION_PRC
(c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_ITM_COST_PRC (c_details_preint_rec,
l_header_id,
l_error_flag
);
-- Updating pre interface table with status 'ER' where record_status in('NW','PF')
-- Update Item Cost Pre-Interface Table
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Details Pre-Interface
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'ER'
WHERE item_number = c_details_preint_rec.item_number
AND organization_code = c_details_preint_rec.organization_code
AND record_id = c_details_preint_rec.record_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
-- p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_itm := l_successful_recs_itm + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
--
-- Businesss Validation for Resource Cost Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
l_processed_recs_rsc := l_processed_recs_rsc + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_cost_resource_rec.organization_code,
c_cost_resource_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_COST_PRC (c_cost_resource_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Cost Pre-Interface Table
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'ER'
WHERE resource_rate = c_cost_resource_rec.resource_rate
AND organization_code = c_cost_resource_rec.organization_code
AND record_id = c_cost_resource_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
p_identifier7 => c_cost_resource_rec.resource_code,
-- p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rsc := l_successful_recs_rsc + 1;
END IF;
l_error_flag := 'N';
END LOOP;
END IF;
-- Businesss Validation for Resource Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
IF c_resc_overhead_rec.overhead IS NOT NULL AND c_resc_overhead_rec.resource_code IS NOT NULL
THEN
l_processed_recs_rohd := l_processed_recs_rohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_RSC_OHD_PRC (c_resc_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Resource Overhead Pre-Interface Table
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'ER'
WHERE resource_code = c_resc_overhead_rec.resource_code
AND overhead = c_resc_overhead_rec.overhead
AND organization_code = c_resc_overhead_rec.organization_code
AND record_id = c_resc_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
--p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_rohd := l_successful_recs_rohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
-- Businesss Validation for Department Overhead Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
IF c_dept_overhead_rec.overhead IS NOT NULL AND c_dept_overhead_rec.department_code IS NOT NULL
THEN
l_processed_recs_dohd := l_processed_recs_dohd + 1; --Counter for total records
-- Call to procedure for validation
XX_COMMON_VALIDATION_PRC
(c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.cost_type,
l_header_id,
l_error_flag
);
XX_VALIDATE_DEPT_OHD_PRC (c_dept_overhead_rec,
l_header_id,
l_error_flag
);
IF l_error_flag = 'Y'
THEN
BEGIN
--Update Department Overhead Pre-Interface Table
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'ER'
WHERE department_code = c_dept_overhead_rec.department_code
AND overhead = c_dept_overhead_rec.overhead
AND organization_code = c_dept_overhead_rec.organization_code
AND record_id = c_dept_overhead_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
-- p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
ELSE
l_successful_recs_dohd := l_successful_recs_dohd + 1;
END IF;
l_error_flag := 'N';
END IF;
END LOOP;
END IF;
--
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-12300',
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
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
FOR c_details_preint_rec IN c_details_preint
LOOP
--deriving inventory_item_id
BEGIN
SELECT msi.inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
, mtl_parameters mp
WHERE msi.segment1 = c_details_preint_rec.item_number
AND mp.organization_id = msi.organization_id
AND mp.organization_code = c_details_preint_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Inventory item id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_details_preint_rec.record_id,
p_identifier4 => c_details_preint_rec.organization_code,
p_identifier5 => c_details_preint_rec.cost_type,
p_identifier6 => c_details_preint_rec.item_number,
p_identifier7 => c_details_preint_rec.resource_code,
-- p_identifier8 => c_details_preint_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
request_id,
organization_code,
cost_type,
inventory_item,
department,
activity,
resource_code,
basis_resource_code,
cost_element,
ERROR_TYPE,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_id,
process_flag,
item_number,
transaction_type,
yielded_cost,
ERROR_CODE,
error_explanation,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
inventory_asset_flag,
error_flag,
group_description
)
VALUES (l_inventory_item_id,
c_details_preint_rec.cost_type_id,
c_details_preint_rec.last_update_date,
c_details_preint_rec.last_updated_by,
c_details_preint_rec.creation_date,
c_details_preint_rec.created_by,
c_details_preint_rec.last_update_login,
c_details_preint_rec.GROUP_ID,
c_details_preint_rec.organization_id,
c_details_preint_rec.operation_sequence_id,
c_details_preint_rec.operation_seq_num,
c_details_preint_rec.department_id,
c_details_preint_rec.level_type,
c_details_preint_rec.activity_id,
c_details_preint_rec.resource_seq_num,
c_details_preint_rec.resource_id,
c_details_preint_rec.resource_rate,
c_details_preint_rec.item_units,
c_details_preint_rec.activity_units,
c_details_preint_rec.usage_rate_or_amount,
c_details_preint_rec.basis_type,
c_details_preint_rec.basis_resource_id,
c_details_preint_rec.basis_factor,
c_details_preint_rec.net_yield_or_shrinkage_factor,
c_details_preint_rec.item_cost,
c_details_preint_rec.cost_element_id,
c_details_preint_rec.rollup_source_type,
c_details_preint_rec.activity_context,
c_details_preint_rec.request_id,
c_details_preint_rec.organization_code,
c_details_preint_rec.cost_type,
c_details_preint_rec.inventory_item_id,
c_details_preint_rec.department,
c_details_preint_rec.activity,
c_details_preint_rec.resource_code,
c_details_preint_rec.basis_resource_code,
c_details_preint_rec.cost_element,
c_details_preint_rec.ERROR_TYPE,
c_details_preint_rec.program_application_id,
c_details_preint_rec.program_id,
c_details_preint_rec.program_update_date,
c_details_preint_rec.attribute_category,
c_details_preint_rec.attribute1,
c_details_preint_rec.attribute2,
c_details_preint_rec.attribute3,
c_details_preint_rec.attribute4,
c_details_preint_rec.attribute5,
c_details_preint_rec.attribute6,
c_details_preint_rec.attribute7,
c_details_preint_rec.attribute8,
c_details_preint_rec.attribute9,
c_details_preint_rec.attribute10,
c_details_preint_rec.attribute11,
c_details_preint_rec.attribute12,
c_details_preint_rec.attribute13,
c_details_preint_rec.attribute14,
c_details_preint_rec.attribute15,
c_details_preint_rec.transaction_id,
c_details_preint_rec.process_flag,
c_details_preint_rec.item_number,
c_details_preint_rec.transaction_type,
c_details_preint_rec.yielded_cost,
c_details_preint_rec.ERROR_CODE,
c_details_preint_rec.error_explanation,
c_details_preint_rec.lot_size,
c_details_preint_rec.based_on_rollup_flag,
c_details_preint_rec.shrinkage_rate,
c_details_preint_rec.inventory_asset_flag,
c_details_preint_rec.error_flag,
c_details_preint_rec.group_description
);
END LOOP;
END IF;
--
--
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
FOR c_cost_resource_rec IN c_cost_resource
LOOP
--deriving resource_id
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND resource_code = c_cost_resource_rec.resource_code
AND ood.organization_code = c_cost_resource_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_cost_resource_rec.record_id,
p_identifier4 => c_cost_resource_rec.organization_code,
p_identifier5 => c_cost_resource_rec.cost_type,
-- p_identifier6 => c_cost_resource_rec.item_number,
--p_identifier7 => c_cost_resource_rec.resource_code,
--p_identifier8 => c_cost_resource_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
--
INSERT INTO CST_RESOURCE_COSTS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
resource_rate,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_cost_resource_rec.resource_code,
c_cost_resource_rec.cost_type_id,
c_cost_resource_rec.cost_type,
c_cost_resource_rec.organization_id,
c_cost_resource_rec.organization_code,
c_cost_resource_rec.last_update_date,
c_cost_resource_rec.last_updated_by,
c_cost_resource_rec.creation_date,
c_cost_resource_rec.created_by,
c_cost_resource_rec.last_update_login,
c_cost_resource_rec.GROUP_ID,
c_cost_resource_rec.transaction_id,
c_cost_resource_rec.process_flag,
c_cost_resource_rec.resource_rate,
c_cost_resource_rec.request_id,
c_cost_resource_rec.ERROR_CODE,
c_cost_resource_rec.error_explanation,
c_cost_resource_rec.program_application_id,
c_cost_resource_rec.program_id,
c_cost_resource_rec.program_update_date,
c_cost_resource_rec.attribute_category,
c_cost_resource_rec.attribute1,
c_cost_resource_rec.attribute2,
c_cost_resource_rec.attribute3,
c_cost_resource_rec.attribute4,
c_cost_resource_rec.attribute5,
c_cost_resource_rec.attribute6,
c_cost_resource_rec.attribute7,
c_cost_resource_rec.attribute8,
c_cost_resource_rec.attribute9,
c_cost_resource_rec.attribute10,
c_cost_resource_rec.attribute11,
c_cost_resource_rec.attribute12,
c_cost_resource_rec.attribute13,
c_cost_resource_rec.attribute14,
c_cost_resource_rec.attribute15,
c_cost_resource_rec.error_flag,
c_cost_resource_rec.group_description
);
END LOOP;
END IF;
--
--
--
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
FOR c_resc_overhead_rec IN c_resc_overhead
LOOP
--deriving resource_id
IF c_resc_overhead_rec.resource_code IS NOT NULL
THEN
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND br.resource_code = c_resc_overhead_rec.resource_code
AND ood.organization_code = c_resc_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Resource id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_resc_overhead_rec.record_id,
p_identifier4 => c_resc_overhead_rec.organization_code,
p_identifier5 => c_resc_overhead_rec.cost_type,
-- p_identifier6 => c_resc_overhead_rec.item_number,
p_identifier7 => c_resc_overhead_rec.resource_code,
-- p_identifier8 => c_resc_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_RES_OVERHEADS_INTERFACE
(resource_id, resource_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_resource_id,
c_resc_overhead_rec.resource_code,
c_resc_overhead_rec.cost_type_id,
c_resc_overhead_rec.cost_type,
c_resc_overhead_rec.organization_id,
c_resc_overhead_rec.organization_code,
c_resc_overhead_rec.overhead_id,
c_resc_overhead_rec.overhead,
c_resc_overhead_rec.last_update_date,
c_resc_overhead_rec.last_updated_by,
c_resc_overhead_rec.creation_date,
c_resc_overhead_rec.created_by,
c_resc_overhead_rec.last_update_login,
c_resc_overhead_rec.GROUP_ID,
c_resc_overhead_rec.transaction_id,
c_resc_overhead_rec.process_flag,
c_resc_overhead_rec.request_id,
c_resc_overhead_rec.ERROR_CODE,
c_resc_overhead_rec.error_explanation,
c_resc_overhead_rec.program_application_id,
c_resc_overhead_rec.program_id,
c_resc_overhead_rec.program_update_date,
c_resc_overhead_rec.attribute_category,
c_resc_overhead_rec.attribute1,
c_resc_overhead_rec.attribute2,
c_resc_overhead_rec.attribute3,
c_resc_overhead_rec.attribute4,
c_resc_overhead_rec.attribute5,
c_resc_overhead_rec.attribute6,
c_resc_overhead_rec.attribute7,
c_resc_overhead_rec.attribute8,
c_resc_overhead_rec.attribute9,
c_resc_overhead_rec.attribute10,
c_resc_overhead_rec.attribute11,
c_resc_overhead_rec.attribute12,
c_resc_overhead_rec.attribute13,
c_resc_overhead_rec.attribute14,
c_resc_overhead_rec.attribute15,
c_resc_overhead_rec.error_flag,
c_resc_overhead_rec.group_description
);
END LOOP;
END IF;
--
FOR c_dept_overhead_rec IN c_dept_overhead
LOOP
--deriving resource_id
IF (c_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE bd.organization_id = ood.organization_id
AND bd.department_code = c_dept_overhead_rec.department_code
AND ood.organization_code = c_dept_overhead_rec.organization_code;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40, -- High level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Retrieving Department id.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_dept_overhead_rec.record_id,
p_identifier4 => c_dept_overhead_rec.organization_code,
p_identifier5 => c_dept_overhead_rec.cost_type,
-- p_identifier6 => c_dept_overhead_rec.item_number,
--p_identifier7 => c_dept_overhead_rec.resource_code,
p_identifier8 => c_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
--
INSERT INTO CST_DEPT_OVERHEADS_INTERFACE
(department_id,
department_code,
cost_type_id,
cost_type,
organization_id,
organization_code,
overhead_id,
overhead,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
GROUP_ID,
transaction_id,
process_flag,
basis_type,
rate_or_amount,
activity_id,
activity,
request_id,
ERROR_CODE,
error_explanation,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
error_flag,
group_description
)
VALUES (l_department_id,
c_dept_overhead_rec.department_code,
c_dept_overhead_rec.cost_type_id,
c_dept_overhead_rec.cost_type,
c_dept_overhead_rec.organization_id,
c_dept_overhead_rec.organization_code,
c_dept_overhead_rec.overhead_id,
c_dept_overhead_rec.overhead,
c_dept_overhead_rec.last_update_date,
c_dept_overhead_rec.last_updated_by,
c_dept_overhead_rec.creation_date,
c_dept_overhead_rec.created_by,
c_dept_overhead_rec.last_update_login,
c_dept_overhead_rec.GROUP_ID,
c_dept_overhead_rec.transaction_id,
c_dept_overhead_rec.process_flag,
c_dept_overhead_rec.basis_type,
c_dept_overhead_rec.rate_or_amount,
c_dept_overhead_rec.activity_id,
c_dept_overhead_rec.activity,
c_dept_overhead_rec.request_id,
c_dept_overhead_rec.ERROR_CODE,
c_dept_overhead_rec.error_explanation,
c_dept_overhead_rec.program_application_id,
c_dept_overhead_rec.program_id,
c_dept_overhead_rec.program_update_date,
c_dept_overhead_rec.attribute_category,
c_dept_overhead_rec.attribute1,
c_dept_overhead_rec.attribute2,
c_dept_overhead_rec.attribute3,
c_dept_overhead_rec.attribute5,
c_dept_overhead_rec.attribute6,
c_dept_overhead_rec.attribute7,
c_dept_overhead_rec.attribute8,
c_dept_overhead_rec.attribute9,
c_dept_overhead_rec.attribute10,
c_dept_overhead_rec.attribute11,
c_dept_overhead_rec.attribute12,
c_dept_overhead_rec.attribute13,
c_dept_overhead_rec.attribute14,
c_dept_overhead_rec.attribute15,
c_dept_overhead_rec.error_flag,
c_dept_overhead_rec.group_description
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into 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-00004',
p_identifier2 => 'Exception While Inserting into INTERFACE TABLE.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
--Update Item Cost Details Preinterface Table
IF p_run_option = 'ITM' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_ITEM_DTLS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Cost Preinterface Table
IF p_run_option = 'RSC' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RESOURCE_COSTS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Resource Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_RES_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--
--Update Department Overhead Preinterface Table
IF p_run_option = 'OHD' OR p_run_option = 'ALL'
THEN
BEGIN
UPDATE XX_CST_DEPT_OVERHEADS_PREINT
SET status = 'PR'
WHERE status = 'IP';
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;
END IF;
--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
SELECT COUNT (*)
INTO l_count
FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
--
SELECT DECODE(l_count,0,count(*),l_count)
INTO l_count
FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
--
--
IF l_count > 0
THEN
SELECT DECODE (p_run_option, 'ITM', 1, 'RSC', 2, 'OHD', 3, 4)
INTO l_run_option
FROM DUAL;
--
l_standard_request_id :=
fnd_request.submit_request
(application => 'BOM',
program => 'CSTPCIMP',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => l_run_option, -- Import cost option
argument2 => 2, -- Mode to run this request
argument3 => 2, -- Group ID option
argument4 => NULL, -- Group ID Dummy
argument5 => NULL, -- Group ID
argument6 => p_cost_type, -- Cost type to import to
argument7 => 2 -- Delete successful rows
);
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
);
ELSE
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20, -- Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'No records To Process in Interface Table',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END;
/*----------------------------------------------------------------------------
Delete the Processed records from the Pre-interface table
----------------------------------------------------------------------------*/
BEGIN
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
DELETE FROM XX_CST_ITEM_DTLS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
DELETE FROM XX_CST_RESOURCE_COSTS_PREINT
WHERE status = 'PR';
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
DELETE FROM XX_CST_RES_OVERHEADS_PREINT
WHERE status = 'PR';
DELETE FROM XX_CST_DEPT_OVERHEADS_PREINT
WHERE status = 'PR';
END IF;
--
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
(' Exception While deleting processed records from Pre-Interface Table(s)'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => ' Exception While deleting processed records from Pre-Interface Table(s)'
|| 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;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
IF ((p_run_option = 'ALL') OR (p_run_option = 'ITM'))
THEN
l_error_recs_itm := l_processed_recs_itm - l_successful_recs_itm;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Item Cost)'
, p_total_recs => l_processed_recs_itm
, p_successful_recs => l_successful_recs_itm
, p_error_recs => l_error_recs_itm
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Item Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'RSC'))
THEN
l_error_recs_rsc := l_processed_recs_rsc - l_successful_recs_rsc;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Cost)'
, p_total_recs => l_processed_recs_rsc
, p_successful_recs => l_successful_recs_rsc
, p_error_recs => l_error_recs_rsc
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Resource Cost');
END IF;
--
END IF;
--
IF ((p_run_option = 'ALL') OR (p_run_option = 'OHD'))
THEN
l_error_recs_rohd := l_processed_recs_rohd - l_successful_recs_rohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Resource Overhead)'
, p_total_recs => l_processed_recs_rohd
, p_successful_recs => l_successful_recs_rohd
, p_error_recs => l_error_recs_rohd
);
l_error_recs_dohd := l_processed_recs_dohd - l_successful_recs_dohd;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||' (Department Overhead)'
, p_total_recs => l_processed_recs_dohd
, p_successful_recs => l_successful_recs_dohd
, p_error_recs => l_error_recs_dohd
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count for Overhead Cost');
END IF;
--
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
COMMIT;
END IF;
EXCEPTION --Main Exception
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--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
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20, --Low level debugging
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
/*******************************************************************************
* Procedure Name : XX_COMMON_VALIDATION_PRC
* Purpose : This program will validate data
* to be inserted Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_COMMON_VALIDATION_PRC (
p_organization_code IN OUT xx_cst_item_dtls_preint.organization_code%TYPE,
p_cost_type IN OUT xx_cst_item_dtls_preint.cost_type%TYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_organization_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_type_id NUMBER;
--
--
BEGIN
--
p_error_flag := 'N';
--Validation of organization Code
BEGIN
l_value := NULL;
BEGIN
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while retrieving organization id for given organization code.Oracle Error-'
|| SQLERRM
);
END;
--
XX_COMMON_VALIDATIONS_PKG.VALIDATE_ORGANIZATION
(p_organization_name => l_value,
p_organization_code => p_organization_code,
p_organization_id => l_organization_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);
IF l_process_status = 1
THEN
p_error_flag := 'Y';
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 Organization Code'
|| l_output_message,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating for 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 organization.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
--Vilidation Of Cost type
BEGIN
--
SELECT a.cost_type_id
INTO l_cost_type_id
FROM cst_cost_types a
WHERE a.cost_type = p_cost_type;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Cost type does not exist 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 => 'Cost type does not exist for the Organization given',
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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 Ocurred While Validating Cost Type.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 Cost Type.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL, -- Can be utilized to store record serial number
p_identifier4 => p_organization_code,
p_identifier5 => p_cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in Organization Code and Cost Type Validation Procedure.
Oracle Error'
|| SQLERRM
);
END XX_COMMON_VALIDATION_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_ITM_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Cost Details tables..
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_ITM_COST_PRC (
p_details_preint_rec IN OUT xx_cst_item_dtls_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_inventory_item_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_item_dtls_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_cost_element_id NUMBER;
BEGIN
--1 Begin
--p_error_flag := 'N';
--
-- Validation of Item Number
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
,org_organization_definitions ood
WHERE msi.organization_id = ood.organization_id
AND msi.segment1 = p_details_preint_rec.item_number
AND ood.organization_code = p_details_preint_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Item Does Not Exist 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 Does Not Exist for the Organization given',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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
);
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_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
-- Validating Cost Element
BEGIN
--
SELECT DISTINCT a.cost_element_id
INTO l_cost_element_id
FROM cst_cost_elements a,
cst_item_cost_details b,
org_organization_definitions od
WHERE a.cost_element_id = b.cost_element_id
AND od.organization_id = b.organization_id
AND od.organization_code = p_details_preint_rec.organization_code
AND a.cost_element = 'Material'
AND a.cost_element = p_details_preint_rec.cost_element;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--
p_error_flag := 'Y';
xx_trace.l ('Cost Element is not valid for Item Cost Import.');
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 => 'Cost Element is not valid for Item Cost Import',
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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 Ocurred While Cost Element Validation.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 Cost Element.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_details_preint_rec.record_id,
p_identifier4 => p_details_preint_rec.organization_code,
p_identifier5 => p_details_preint_rec.cost_type,
p_identifier6 => p_details_preint_rec.item_number,
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;
EXCEPTION
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.l
( 'Exception in validate cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_ITM_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_COST_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_COST_PRC (
p_cost_resource_rec IN OUT xx_cst_resource_costs_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_functional_currency_flag NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_resource_costs_preint%ROWTYPE;
l_value VARCHAR2 (50);
BEGIN
--1 Begin
--p_error_flag := 'N';
--
--
--
-- Validation of Resource Code
BEGIN
--
SELECT resource_id,functional_currency_flag
INTO l_resource_id,l_functional_currency_flag
FROM bom_resources br
,org_organization_definitions ood
WHERE br.organization_id = ood.organization_id
AND organization_code = p_cost_resource_rec.organization_code
AND resource_code = p_cost_resource_rec.resource_code;
--
IF l_functional_currency_flag = 1
THEN
IF p_cost_resource_rec.resource_rate IS NOT NULL
THEN
p_error_flag := 'Y';
xx_trace.l ('Resource rate should be NULL for Functional Currency Flag set to 1.');
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 => 'Resource rate should be NULL for Functional Currency Flag set to 1.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;
--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not 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 => 'Resource Code does not exist for the Organization given.',
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_cost_resource_rec.record_id,
p_identifier4 => p_cost_resource_rec.organization_code,
p_identifier5 => p_cost_resource_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_cost_resource_rec.resource_code,
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.l
( 'Exception in validate Resource Cost procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_COST_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_RSC_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Resource Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_RSC_OHD_PRC (
p_resc_overhead_rec IN OUT xx_cst_res_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_resource_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_res_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Resource Code
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources br
,org_organization_definitions ood
WHERE ood.organization_id = br.organization_id
AND br.resource_code = p_resc_overhead_rec.resource_code
AND ood.organization_code = p_resc_overhead_rec.organization_code;
--
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Resource Code does not exist 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 => 'Resource Code does not exist for the Organization given',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Resource Code .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 Resource Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
p_identifier8 => NULL, -- Can be utilized for displaying more information
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
END;
-- Validating Resource Overhead
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_resc_overhead_rec.organization_code
AND bomr.resource_code = p_resc_overhead_rec.overhead
AND bomr.cost_element_id in (2,5);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a resource.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a resource.',
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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 Ocurred While Rescource Overhead Validation.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 Rescource Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_resc_overhead_rec.record_id,
p_identifier4 => p_resc_overhead_rec.organization_code,
p_identifier5 => p_resc_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => p_resc_overhead_rec.resource_code,
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.l
( 'Exception in validate Resource Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_RSC_OHD_PRC;
/*******************************************************************************
* Procedure Name : XX_VALIDATE_DEPT_OHD_PRC
* Purpose : This program will validate data
* to be inserted into the Department Overhead Cost Details tables.
*
*******************************************************************************/
PROCEDURE XX_VALIDATE_DEPT_OHD_PRC (
p_dept_overhead_rec IN OUT xx_cst_dept_overheads_preint%ROWTYPE,
p_header_id IN NUMBER,
p_error_flag OUT VARCHAR2
)
IS
l_department_id NUMBER;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_process_status NUMBER := 0;
l_interface_stg_rec xx_cst_dept_overheads_preint%ROWTYPE;
l_value VARCHAR2 (50);
l_overhead_id NUMBER;
BEGIN
--p_error_flag := 'N';
-- Validation of Department Code
IF (p_dept_overhead_rec.department_code IS NOT NULL)
THEN
BEGIN
--
SELECT bd.department_id
INTO l_department_id
FROM bom_departments bd
,org_organization_definitions ood
WHERE ood.organization_id = bd.organization_id
AND bd.department_code = p_dept_overhead_rec.department_code
AND ood.organization_code = p_dept_overhead_rec.organization_code;
EXCEPTION
--
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l
('Department does not exist 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 => 'Department does not exists for the Organization given',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
NULL;
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Occured While Validating Department Code .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 Department Code .Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END;
END IF;
-- Validating Department Overhead
IF (p_dept_overhead_rec.overhead IS NOT NULL)
THEN
BEGIN
--
SELECT bomr.resource_id
INTO l_overhead_id
FROM bom_resources bomr
,org_organization_definitions ood
WHERE ood.organization_id = bomr.organization_id
AND ood.organization_code = p_dept_overhead_rec.organization_code
AND bomr.resource_code = p_dept_overhead_rec.overhead
AND bomr.cost_element_id = 5 ;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_flag := 'Y';
xx_trace.l ('Overhead does not exist for the organization and cannot be assigned to a department.');
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 => 'Overhead does not exist for the organization and cannot be assigned to a department',
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
p_process_status => l_return_value,
p_error_message => l_output_message
);
WHEN OTHERS
THEN
p_error_flag := 'Y';
xx_trace.h
( 'Exception Ocurred While Department Overhead Validation.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 Department Overhead.Oracle Error-'
|| SQLERRM,
p_identifier3 => p_dept_overhead_rec.record_id,
p_identifier4 => p_dept_overhead_rec.organization_code,
p_identifier5 => p_dept_overhead_rec.cost_type,
p_identifier6 => NULL, -- Can be utilized for displaying more information
p_identifier7 => NULL, -- Can be utilized for displaying more information
p_identifier8 => p_dept_overhead_rec.department_code,
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.l
( 'Exception in validate Department Overhead procedure.
Oracle Error'
|| SQLERRM
);
END XX_VALIDATE_DEPT_OHD_PRC;
END XX_INVCNV02_COST_UPLOAD_PKG;
/