SALES ORDER CONVERSION
SALES ORDER CONVERSION
CREATE OR REPLACE PACKAGE BODY xx_ont_oe_orders_imp_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ONT_OE_ORDERS_IMP_PKG
RICEW Object id : XX_ONT_01
Purpose : Package Specification
--------------------------------------------------------------
*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
)
IS
--Cursor to select Order Headers from Staging table xx_items_stg
CURSOR c_order_control
IS
SELECT *
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH' AND status = 'NW';
--Cursor to select Order Lines from Staging table xx_items_stg
CURSOR c_order_line_control (p_header VARCHAR2)
IS
SELECT *
FROM xx_ont_order_stg
WHERE rec_type = 'ONTL'
AND orig_sys_document_ref = p_header
AND status = 'NW';
--Cursor to select from Pre-Interface Order table XX_ONT_ORDER_PREINT
CURSOR c_order_validate
IS
SELECT *
FROM xx_ont_order_preint
WHERE status = 'IP';
--Cursor to select from Pre-Interface Line table XX_ONT_ORDER_PREINT
CURSOR c_line_validate (p_header VARCHAR2)
IS
SELECT *
FROM xx_ont_lines_preint
WHERE status = 'IP' AND orig_sys_document_ref = p_header;
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_ont_order_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_ONT_OE_ORDERS_IMP_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'ONT_CNV_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report at header Level
l_processed_line_recs NUMBER := 0;
----stores total records for summary report at line levele
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_cnt_processed_recs NUMBER := 0;
-- stores Total Control record
l_cnt_successful_recs NUMBER := 0;
-- stores total successful control record
l_cnt_error_recs NUMBER := 0;
-- stores total errored control record
l_ord_insert_rec_count NUMBER := 0;
-- stores total Order interface record
l_cntrl_count NUMBER := 0;
-- store total control record
l_order_exist NUMBER := 0;
-- count for existing order
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
l_order_source_id oe_order_sources.order_source_id%TYPE;
l_orig_sys_document_ref oe_headers_iface_all.orig_sys_document_ref%TYPE;
l_sold_to_org_id oe_headers_iface_all.sold_to_org_id%TYPE;
l_site_id hz_cust_site_uses_all.site_use_id%TYPE;
l_cntrl_cnt NUMBER;
l_control_cnt xx_ont_order_stg.record_count%TYPE;
-- 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;
e_control_valid_fail EXCEPTION;
BEGIN
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_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 := 'Order Doc Ref'; --Fourth Error Header
l_error_rec.identifier5 := 'Order Line Ref'; --Fifth Error Header
l_error_rec.identifier6 := 'Order Ship Ref'; --Sixth Error Header
-- l_error_rec.identifier7 := NULL; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid
, (SYSDATE - g_retention_period)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
IF UPPER (p_run_mode) = 'F'
THEN
BEGIN -- 1st Begin
--
BEGIN
SELECT COUNT (*)
INTO l_cntrl_count
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH';
SELECT record_count
INTO l_control_cnt
FROM xx_ont_order_stg
WHERE rec_type = 'CRC';
IF l_cntrl_count <> l_control_cnt
THEN
l_error_flag := 'Y';
xx_trace.l
('Order Header Record Count does not match with Control Record Level.'
);
--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 => 'Order Header Record Count does not match with Control Record Level.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Order Header Record Count does not match with Control Record Level.'
|| 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 => 'Order Header Record Count does not match with Control Record Level.'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
FOR c_order_rec IN c_order_control
LOOP
l_cnt_processed_recs := l_cnt_processed_recs + 1;
-- Processed records in Control Validation
l_cntrl_cnt := l_cntrl_cnt + 1;
-- for Control Count.
l_error_flag := 'N';
IF c_order_rec.orig_sys_document_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Order Document Reference 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 => 'Order Document Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_orig_sys_document_ref :=
c_order_rec.orig_sys_document_ref;
END IF;
IF c_order_rec.sold_from_org IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Sold from organization cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Sold from organization cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_rec.customer_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Customer Name 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 => 'Customer Name cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_rec.ship_to_location IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Customer Ship to Location 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 => 'Customer Ship to Location cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
FOR c_order_line_rec IN
c_order_line_control (c_order_rec.orig_sys_document_ref)
LOOP
l_cnt_processed_recs := l_cnt_processed_recs + 1;
IF c_order_line_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Number 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 Number value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.orig_sys_line_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Line Reference 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 => 'Order Line Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.orig_sys_shipment_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Order shipment Reference 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 => 'Order Shipment Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.ordered_quantity IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Quantity 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 => 'Order Quantity cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.ordered_quantity_uom IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Quantity UOM 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 => 'Order Quantity UOM cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.unit_selling_price IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Unit Selling price 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 => 'Unit Selling price cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF l_error_flag <> 'Y'
THEN
--Increment the successfull record count
l_cnt_successful_recs := l_cnt_successful_recs + 1;
END IF;
END LOOP;
IF l_error_flag = 'Y'
THEN
EXIT;
ELSE
--Increment the successfull record count
l_cnt_successful_recs := l_cnt_successful_recs + 1;
END IF;
END LOOP;
IF l_error_flag = 'Y'
THEN
xx_trace.l ('Control Validation Failed.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Control Validation Failed.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE e_control_valid_fail;
END IF;
--
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
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_recs := l_processed_recs;
l_successful_recs := 0;
DELETE FROM xx_ont_order_stg;
COMMIT;
RAISE;
END; --1st Begin
/*---------------------------------------------------------------------------------------------------
Load Data into Header Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_ont_order_preint
(record_id
, status
, order_source
, orig_sys_document_ref
, order_type
, price_list
, ship_from_org
, customer_name
, ship_to_org_id
, operation_code
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
, request_id
)
SELECT xx_ont_order_preint_s.NEXTVAL
, 'NW'
, order_source
, orig_sys_document_ref
, order_type
, price_list
, sold_from_org
, customer_name
, ship_to_location
, operation_code
, NVL (created_by, -1)
, NVL (creation_date, SYSDATE)
, last_updated_login
, NVL (last_updated_by, -1)
, NVL (last_updated_date, SYSDATE)
, l_request_id
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting Into Pre-Interface Header 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 Header 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;
/*---------------------------------------------------------------------------------------------------
Load Data into Line Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_ont_lines_preint
(record_id
, status
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, inventory_item
, ordered_quantity
, order_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
SELECT xx_ont_lines_preint_s.NEXTVAL
, 'NW'
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, item_number
, ordered_quantity
, ordered_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, NVL (created_by, -1)
, NVL (creation_date, SYSDATE)
, last_updated_login
, NVL (last_updated_by, -1)
, NVL (last_updated_date, SYSDATE)
FROM xx_ont_order_stg
WHERE rec_type = 'ONTL';
COMMIT;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting Into Pre-Interface Line 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 Line 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; --2nd Begin
/*-----------------------------------------------------------------------------
Updating Header pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_ont_order_preint
SET status = 'IP'
WHERE status IN ('NW', 'PF');
xx_trace.l ('Header Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Status of Header 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 => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Updating Status of Header 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; --3rdBegin
/*-----------------------------------------------------------------------------
Updating Line pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_ont_lines_preint
SET status = 'IP'
WHERE status IN ('NW', 'PF');
xx_trace.l ('Line Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Status of Line 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 => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Updating Status of Line 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; --4rdBegin
END IF; --IF p_run_mode='F'
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN --5 th Begin
FOR c_order_validate_rec IN c_order_validate
LOOP
--
l_processed_recs := l_processed_recs + 1;
--Counter for total records
BEGIN --5.0 th end
SELECT COUNT (*)
INTO l_order_exist
FROM oe_order_headers_all
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
IF l_order_exist > 0
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating order already exist.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-00003'
, p_identifier2 => 'Exception occured while validating Order already exist.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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;
END; --5.0th end
IF c_order_validate_rec.order_source IS NOT NULL
THEN
BEGIN -- 5.1 begin
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_sources
WHERE NAME = c_order_validate_rec.order_source;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating order source.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-00003'
, p_identifier2 => 'Exception occured while validating Order Source.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.1 th end
ELSE
l_error_flag := 'Y';
END IF;
IF (c_order_validate_rec.ship_from_org IS NOT NULL)
THEN
--Validating Organization Code
BEGIN --4.1 th Begin
--
SELECT organization_id
INTO c_order_validate_rec.ship_from_org_id
FROM org_organization_definitions
WHERE UPPER (organization_code) =
UPPER (c_order_validate_rec.ship_from_org);
--
EXCEPTION
--
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating organization code.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-00003'
, p_identifier2 => 'Exception occured while validating organization code.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.ship_from_org
,
--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; --4.1 th Begin
ELSE
l_error_flag := 'Y';
END IF;
IF c_order_validate_rec.customer_name IS NOT NULL
THEN
BEGIN -- 5.2 begin
SELECT cust_account_id
INTO c_order_validate_rec.sold_to_org_id
FROM hz_cust_accounts hca, hz_parties hp
WHERE hca.party_id = hp.party_id
AND hp.party_name = c_order_validate_rec.customer_name;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Customer name.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-00003'
, p_identifier2 => 'Exception occured while validating Customer Name.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.customer_name
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.2 th end
ELSE
l_error_flag := 'Y';
END IF;
BEGIN -- 5.2.1
SELECT hcsu.site_use_id
INTO l_site_id
FROM hz_cust_site_uses_all hcsu
, hz_cust_acct_sites_all hcas
, hz_cust_accounts hca
, hz_party_sites hps
WHERE hcsu.primary_flag = 'Y'
AND hcsu.org_id = 204
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hca.cust_account_id =
c_order_validate_rec.sold_to_org_id
AND hps.party_id = hca.party_id
AND hps.party_site_id = hcas.party_site_id
AND ROWNUM < 2;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Customer site.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-00003'
, p_identifier2 => 'Exception occured while validating Customer site.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.customer_name
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.2 th end
IF c_order_validate_rec.price_list IS NOT NULL
THEN
BEGIN -- 5.3 begin
SELECT NAME
INTO c_order_validate_rec.price_list
FROM qp_list_headers
WHERE list_type_code = 'PRL'
AND NAME = c_order_validate_rec.price_list
AND NVL (end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Price List.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-00003'
, p_identifier2 => 'Exception occured while validating Price List.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.price_list
,
--p_identifier6 => c_order_validate_rec.ship_from_org,
--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; --5.3 th end
END IF;
FOR c_line_validate_rec IN
c_line_validate (c_order_validate_rec.orig_sys_document_ref)
LOOP
--
l_processed_line_recs := l_processed_line_recs + 1;
l_processed_recs := l_processed_recs + 1;
IF (c_line_validate_rec.inventory_item IS NOT NULL)
THEN
BEGIN
--5.3th Begin
--
SELECT inventory_item_id
INTO c_line_validate_rec.inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = c_line_validate_rec.inventory_item
AND organization_id =
c_order_validate_rec.ship_from_org_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ('Item invalid for organization.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Item invalid for organization'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_line_validate_rec.orig_sys_document_ref
, p_identifier5 => c_line_validate_rec.orig_sys_line_ref
, p_identifier6 => c_line_validate_rec.orig_sys_shipment_ref
, p_identifier7 => c_line_validate_rec.inventory_item
, p_identifier8 => c_order_validate_rec.ship_from_org
--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; -- 5.3th Begin
ELSE
l_error_flag := 'Y';
END IF;
IF (c_line_validate_rec.order_quantity_uom IS NOT NULL)
THEN
--Validate primary_unit_of_measure
BEGIN --5.4 th Begin
--
SELECT uom_code
INTO c_line_validate_rec.order_quantity_uom
FROM mtl_units_of_measure
WHERE uom_code = c_line_validate_rec.order_quantity_uom;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Invalid Unit of Measure.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Invalid Unit of Measure.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_line_validate_rec.orig_sys_document_ref
, p_identifier5 => c_line_validate_rec.orig_sys_line_ref
, p_identifier6 => c_line_validate_rec.orig_sys_shipment_ref
, p_identifier7 => c_line_validate_rec.order_quantity_uom
, p_identifier8 => c_order_validate_rec.ship_from_org
,
--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; --5.4 th Begin
END IF;
IF l_error_flag = 'N'
THEN
UPDATE xx_ont_lines_preint
SET inventory_item_id =
c_line_validate_rec.inventory_item_id
WHERE orig_sys_line_ref =
c_line_validate_rec.orig_sys_line_ref;
END IF;
IF l_error_flag <> 'Y'
THEN
l_successful_recs := l_successful_recs + 1;
END IF;
END LOOP;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' where record_status in('NW','PF')
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN -- 5.5th begin
--Update Items Pre-Interface
UPDATE xx_ont_order_preint
SET status = 'ER'
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
--Update Category Pre-Interface
UPDATE xx_ont_lines_preint
SET status = 'ER'
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
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 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; -- 5.5th end
ELSE
l_successful_recs := l_successful_recs + 1;
UPDATE xx_ont_order_preint
SET order_source_id = l_order_source_id
, ship_from_org_id = c_order_validate_rec.ship_from_org_id
, sold_to_org_id = c_order_validate_rec.sold_to_org_id
, ship_to_org_id = c_order_validate_rec.ship_to_org_id
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
l_sold_to_org_id := c_order_validate_rec.sold_to_org_id;
END IF;
l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--End of Business Validation Section
END; --5.0 th Begin
/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN
--6th Begin
FOR c_order_validate_rec IN c_order_validate
LOOP
INSERT INTO oe_headers_iface_all
(order_source_id
, orig_sys_document_ref
, order_type
, price_list
, ship_from_org_id
, customer_name
, sold_to_org_id
, ship_to_org_id
, operation_code
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
VALUES (c_order_validate_rec.order_source_id
, c_order_validate_rec.orig_sys_document_ref
, c_order_validate_rec.order_type
, c_order_validate_rec.price_list
, c_order_validate_rec.ship_from_org_id
, c_order_validate_rec.customer_name
, c_order_validate_rec.sold_to_org_id
, c_order_validate_rec.ship_to_org_id
, c_order_validate_rec.operation_code
, c_order_validate_rec.created_by
, c_order_validate_rec.creation_date
, c_order_validate_rec.last_update_login
, c_order_validate_rec.last_updated_by
, c_order_validate_rec.last_update_date
);
FOR c_line_validate_rec IN
c_line_validate (c_order_validate_rec.orig_sys_document_ref)
LOOP
INSERT INTO oe_lines_iface_all
(order_source_id
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, ship_from_org_id
, sold_to_org_id
, ship_to_org_id
, inventory_item_id
, ordered_quantity
, order_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
VALUES (c_order_validate_rec.order_source_id
, c_line_validate_rec.orig_sys_document_ref
, c_line_validate_rec.orig_sys_line_ref
, c_line_validate_rec.orig_sys_shipment_ref
, c_order_validate_rec.ship_from_org_id
, c_order_validate_rec.sold_to_org_id
, c_order_validate_rec.ship_to_org_id
, c_line_validate_rec.inventory_item_id
, c_line_validate_rec.ordered_quantity
, c_line_validate_rec.order_quantity_uom
, c_line_validate_rec.unit_selling_price
, c_line_validate_rec.delivery_lead_time
, c_line_validate_rec.delivery_id
, c_line_validate_rec.request_date
, c_line_validate_rec.created_by
, c_line_validate_rec.creation_date
, c_line_validate_rec.last_update_login
, c_line_validate_rec.last_updated_by
, c_line_validate_rec.last_update_date
);
l_ord_insert_rec_count := l_ord_insert_rec_count + 1;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into ORDER INTERFACE TABLES.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 ORDER INTERFACE TABLES.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; --6th end
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN --6 th Begin
UPDATE xx_ont_order_preint
SET status = 'PR'
WHERE status = 'IP';
UPDATE xx_ont_lines_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;
--Initialize apps
--fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN
IF l_ord_insert_rec_count > 0
THEN
l_standard_request_id :=
fnd_request.submit_request (application => 'ONT'
, program => 'OEOIMP'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_order_source_id
, argument2 => NULL
--SB l_orig_sys_document_ref
, argument3 => NULL
, argument4 => 'N'
, argument5 => 1
, argument6 => 4
, argument7 => NULL
--SB l_sold_to_org_id
, argument8 => NULL
, argument9 => NULL
, argument10 => 'Y'
, argument11 => 'N'
);
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;
/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_ont_order_preint
WHERE status = 'PR';
--
DELETE FROM xx_ont_lines_preint
WHERE status = 'PR';
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_cnt_error_recs := l_cnt_processed_recs - l_cnt_successful_recs;
l_return_value := NULL;
--Call Insert Summary Count For Control Validation
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Control Validation'
, p_total_recs => l_cnt_processed_recs
, p_successful_recs => l_cnt_successful_recs
, p_error_recs => l_cnt_error_recs
);
l_return_value := NULL;
--Call Insert Summary Count For Business Validation
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Business Validation'
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
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
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
END xx_ont_oe_orders_imp_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_ont_oe_orders_imp_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ONT_OE_ORDERS_IMP_PKG
RICEW Object id : XX_ONT_01
Purpose : Package Specification
--------------------------------------------------------------
*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
)
IS
--Cursor to select Order Headers from Staging table xx_items_stg
CURSOR c_order_control
IS
SELECT *
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH' AND status = 'NW';
--Cursor to select Order Lines from Staging table xx_items_stg
CURSOR c_order_line_control (p_header VARCHAR2)
IS
SELECT *
FROM xx_ont_order_stg
WHERE rec_type = 'ONTL'
AND orig_sys_document_ref = p_header
AND status = 'NW';
--Cursor to select from Pre-Interface Order table XX_ONT_ORDER_PREINT
CURSOR c_order_validate
IS
SELECT *
FROM xx_ont_order_preint
WHERE status = 'IP';
--Cursor to select from Pre-Interface Line table XX_ONT_ORDER_PREINT
CURSOR c_line_validate (p_header VARCHAR2)
IS
SELECT *
FROM xx_ont_lines_preint
WHERE status = 'IP' AND orig_sys_document_ref = p_header;
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_interface_stg_rec xx_ont_order_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_ONT_OE_ORDERS_IMP_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'ONT_CNV_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report at header Level
l_processed_line_recs NUMBER := 0;
----stores total records for summary report at line levele
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_cnt_processed_recs NUMBER := 0;
-- stores Total Control record
l_cnt_successful_recs NUMBER := 0;
-- stores total successful control record
l_cnt_error_recs NUMBER := 0;
-- stores total errored control record
l_ord_insert_rec_count NUMBER := 0;
-- stores total Order interface record
l_cntrl_count NUMBER := 0;
-- store total control record
l_order_exist NUMBER := 0;
-- count for existing order
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
l_order_source_id oe_order_sources.order_source_id%TYPE;
l_orig_sys_document_ref oe_headers_iface_all.orig_sys_document_ref%TYPE;
l_sold_to_org_id oe_headers_iface_all.sold_to_org_id%TYPE;
l_site_id hz_cust_site_uses_all.site_use_id%TYPE;
l_cntrl_cnt NUMBER;
l_control_cnt xx_ont_order_stg.record_count%TYPE;
-- 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;
e_control_valid_fail EXCEPTION;
BEGIN
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_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 := 'Order Doc Ref'; --Fourth Error Header
l_error_rec.identifier5 := 'Order Line Ref'; --Fifth Error Header
l_error_rec.identifier6 := 'Order Ship Ref'; --Sixth Error Header
-- l_error_rec.identifier7 := NULL; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid
, (SYSDATE - g_retention_period)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
IF UPPER (p_run_mode) = 'F'
THEN
BEGIN -- 1st Begin
--
BEGIN
SELECT COUNT (*)
INTO l_cntrl_count
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH';
SELECT record_count
INTO l_control_cnt
FROM xx_ont_order_stg
WHERE rec_type = 'CRC';
IF l_cntrl_count <> l_control_cnt
THEN
l_error_flag := 'Y';
xx_trace.l
('Order Header Record Count does not match with Control Record Level.'
);
--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 => 'Order Header Record Count does not match with Control Record Level.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l
( 'Order Header Record Count does not match with Control Record Level.'
|| 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 => 'Order Header Record Count does not match with Control Record Level.'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
FOR c_order_rec IN c_order_control
LOOP
l_cnt_processed_recs := l_cnt_processed_recs + 1;
-- Processed records in Control Validation
l_cntrl_cnt := l_cntrl_cnt + 1;
-- for Control Count.
l_error_flag := 'N';
IF c_order_rec.orig_sys_document_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Order Document Reference 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 => 'Order Document Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_orig_sys_document_ref :=
c_order_rec.orig_sys_document_ref;
END IF;
IF c_order_rec.sold_from_org IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Sold from organization cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Sold from organization cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_rec.customer_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Customer Name 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 => 'Customer Name cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_rec.ship_to_location IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Customer Ship to Location 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 => 'Customer Ship to Location cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
FOR c_order_line_rec IN
c_order_line_control (c_order_rec.orig_sys_document_ref)
LOOP
l_cnt_processed_recs := l_cnt_processed_recs + 1;
IF c_order_line_rec.item_number IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Number 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 Number value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.orig_sys_line_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Line Reference 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 => 'Order Line Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.orig_sys_shipment_ref IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l
('Order shipment Reference 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 => 'Order Shipment Reference value cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.ordered_quantity IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Quantity 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 => 'Order Quantity cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.ordered_quantity_uom IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Order Quantity UOM 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 => 'Order Quantity UOM cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_order_line_rec.unit_selling_price IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Unit Selling price 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 => 'Unit Selling price cannot be null.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_rec.orig_sys_document_ref
, p_identifier5 => c_order_line_rec.orig_sys_line_ref
, p_identifier6 => c_order_line_rec.orig_sys_shipment_ref
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF l_error_flag <> 'Y'
THEN
--Increment the successfull record count
l_cnt_successful_recs := l_cnt_successful_recs + 1;
END IF;
END LOOP;
IF l_error_flag = 'Y'
THEN
EXIT;
ELSE
--Increment the successfull record count
l_cnt_successful_recs := l_cnt_successful_recs + 1;
END IF;
END LOOP;
IF l_error_flag = 'Y'
THEN
xx_trace.l ('Control Validation Failed.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Control Validation Failed.'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => NULL
, p_identifier5 => NULL
, p_identifier6 => NULL
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE e_control_valid_fail;
END IF;
--
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
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_error_recs := l_processed_recs;
l_successful_recs := 0;
DELETE FROM xx_ont_order_stg;
COMMIT;
RAISE;
END; --1st Begin
/*---------------------------------------------------------------------------------------------------
Load Data into Header Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_ont_order_preint
(record_id
, status
, order_source
, orig_sys_document_ref
, order_type
, price_list
, ship_from_org
, customer_name
, ship_to_org_id
, operation_code
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
, request_id
)
SELECT xx_ont_order_preint_s.NEXTVAL
, 'NW'
, order_source
, orig_sys_document_ref
, order_type
, price_list
, sold_from_org
, customer_name
, ship_to_location
, operation_code
, NVL (created_by, -1)
, NVL (creation_date, SYSDATE)
, last_updated_login
, NVL (last_updated_by, -1)
, NVL (last_updated_date, SYSDATE)
, l_request_id
FROM xx_ont_order_stg
WHERE rec_type = 'ONTH';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting Into Pre-Interface Header 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 Header 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;
/*---------------------------------------------------------------------------------------------------
Load Data into Line Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_ont_lines_preint
(record_id
, status
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, inventory_item
, ordered_quantity
, order_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
SELECT xx_ont_lines_preint_s.NEXTVAL
, 'NW'
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, item_number
, ordered_quantity
, ordered_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, NVL (created_by, -1)
, NVL (creation_date, SYSDATE)
, last_updated_login
, NVL (last_updated_by, -1)
, NVL (last_updated_date, SYSDATE)
FROM xx_ont_order_stg
WHERE rec_type = 'ONTL';
COMMIT;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting Into Pre-Interface Line 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 Line 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; --2nd Begin
/*-----------------------------------------------------------------------------
Updating Header pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_ont_order_preint
SET status = 'IP'
WHERE status IN ('NW', 'PF');
xx_trace.l ('Header Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Status of Header 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 => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Updating Status of Header 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; --3rdBegin
/*-----------------------------------------------------------------------------
Updating Line pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_ont_lines_preint
SET status = 'IP'
WHERE status IN ('NW', 'PF');
xx_trace.l ('Line Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Updating Status of Line 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 => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Exception While Updating Status of Line 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; --4rdBegin
END IF; --IF p_run_mode='F'
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN --5 th Begin
FOR c_order_validate_rec IN c_order_validate
LOOP
--
l_processed_recs := l_processed_recs + 1;
--Counter for total records
BEGIN --5.0 th end
SELECT COUNT (*)
INTO l_order_exist
FROM oe_order_headers_all
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
IF l_order_exist > 0
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating order already exist.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-00003'
, p_identifier2 => 'Exception occured while validating Order already exist.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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;
END; --5.0th end
IF c_order_validate_rec.order_source IS NOT NULL
THEN
BEGIN -- 5.1 begin
SELECT order_source_id
INTO l_order_source_id
FROM oe_order_sources
WHERE NAME = c_order_validate_rec.order_source;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating order source.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-00003'
, p_identifier2 => 'Exception occured while validating Order Source.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.1 th end
ELSE
l_error_flag := 'Y';
END IF;
IF (c_order_validate_rec.ship_from_org IS NOT NULL)
THEN
--Validating Organization Code
BEGIN --4.1 th Begin
--
SELECT organization_id
INTO c_order_validate_rec.ship_from_org_id
FROM org_organization_definitions
WHERE UPPER (organization_code) =
UPPER (c_order_validate_rec.ship_from_org);
--
EXCEPTION
--
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating organization code.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-00003'
, p_identifier2 => 'Exception occured while validating organization code.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.ship_from_org
,
--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; --4.1 th Begin
ELSE
l_error_flag := 'Y';
END IF;
IF c_order_validate_rec.customer_name IS NOT NULL
THEN
BEGIN -- 5.2 begin
SELECT cust_account_id
INTO c_order_validate_rec.sold_to_org_id
FROM hz_cust_accounts hca, hz_parties hp
WHERE hca.party_id = hp.party_id
AND hp.party_name = c_order_validate_rec.customer_name;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Customer name.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-00003'
, p_identifier2 => 'Exception occured while validating Customer Name.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.customer_name
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.2 th end
ELSE
l_error_flag := 'Y';
END IF;
BEGIN -- 5.2.1
SELECT hcsu.site_use_id
INTO l_site_id
FROM hz_cust_site_uses_all hcsu
, hz_cust_acct_sites_all hcas
, hz_cust_accounts hca
, hz_party_sites hps
WHERE hcsu.primary_flag = 'Y'
AND hcsu.org_id = 204
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hca.cust_account_id =
c_order_validate_rec.sold_to_org_id
AND hps.party_id = hca.party_id
AND hps.party_site_id = hcas.party_site_id
AND ROWNUM < 2;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Customer site.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-00003'
, p_identifier2 => 'Exception occured while validating Customer site.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.customer_name
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; --5.2 th end
IF c_order_validate_rec.price_list IS NOT NULL
THEN
BEGIN -- 5.3 begin
SELECT NAME
INTO c_order_validate_rec.price_list
FROM qp_list_headers
WHERE list_type_code = 'PRL'
AND NAME = c_order_validate_rec.price_list
AND NVL (end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Price List.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-00003'
, p_identifier2 => 'Exception occured while validating Price List.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.price_list
,
--p_identifier6 => c_order_validate_rec.ship_from_org,
--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; --5.3 th end
END IF;
FOR c_line_validate_rec IN
c_line_validate (c_order_validate_rec.orig_sys_document_ref)
LOOP
--
l_processed_line_recs := l_processed_line_recs + 1;
l_processed_recs := l_processed_recs + 1;
IF (c_line_validate_rec.inventory_item IS NOT NULL)
THEN
BEGIN
--5.3th Begin
--
SELECT inventory_item_id
INTO c_line_validate_rec.inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = c_line_validate_rec.inventory_item
AND organization_id =
c_order_validate_rec.ship_from_org_id;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ('Item invalid for organization.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Item invalid for organization'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_line_validate_rec.orig_sys_document_ref
, p_identifier5 => c_line_validate_rec.orig_sys_line_ref
, p_identifier6 => c_line_validate_rec.orig_sys_shipment_ref
, p_identifier7 => c_line_validate_rec.inventory_item
, p_identifier8 => c_order_validate_rec.ship_from_org
--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; -- 5.3th Begin
ELSE
l_error_flag := 'Y';
END IF;
IF (c_line_validate_rec.order_quantity_uom IS NOT NULL)
THEN
--Validate primary_unit_of_measure
BEGIN --5.4 th Begin
--
SELECT uom_code
INTO c_line_validate_rec.order_quantity_uom
FROM mtl_units_of_measure
WHERE uom_code = c_line_validate_rec.order_quantity_uom;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Invalid Unit of Measure.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Invalid Unit of Measure.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_line_validate_rec.orig_sys_document_ref
, p_identifier5 => c_line_validate_rec.orig_sys_line_ref
, p_identifier6 => c_line_validate_rec.orig_sys_shipment_ref
, p_identifier7 => c_line_validate_rec.order_quantity_uom
, p_identifier8 => c_order_validate_rec.ship_from_org
,
--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; --5.4 th Begin
END IF;
IF l_error_flag = 'N'
THEN
UPDATE xx_ont_lines_preint
SET inventory_item_id =
c_line_validate_rec.inventory_item_id
WHERE orig_sys_line_ref =
c_line_validate_rec.orig_sys_line_ref;
END IF;
IF l_error_flag <> 'Y'
THEN
l_successful_recs := l_successful_recs + 1;
END IF;
END LOOP;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' where record_status in('NW','PF')
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN -- 5.5th begin
--Update Items Pre-Interface
UPDATE xx_ont_order_preint
SET status = 'ER'
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
--Update Category Pre-Interface
UPDATE xx_ont_lines_preint
SET status = 'ER'
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
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 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_order_validate_rec.orig_sys_document_ref
, p_identifier5 => c_order_validate_rec.order_source
, p_identifier6 => c_order_validate_rec.ship_from_org
,
--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; -- 5.5th end
ELSE
l_successful_recs := l_successful_recs + 1;
UPDATE xx_ont_order_preint
SET order_source_id = l_order_source_id
, ship_from_org_id = c_order_validate_rec.ship_from_org_id
, sold_to_org_id = c_order_validate_rec.sold_to_org_id
, ship_to_org_id = c_order_validate_rec.ship_to_org_id
WHERE orig_sys_document_ref =
c_order_validate_rec.orig_sys_document_ref;
l_sold_to_org_id := c_order_validate_rec.sold_to_org_id;
END IF;
l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--End of Business Validation Section
END; --5.0 th Begin
/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN
--6th Begin
FOR c_order_validate_rec IN c_order_validate
LOOP
INSERT INTO oe_headers_iface_all
(order_source_id
, orig_sys_document_ref
, order_type
, price_list
, ship_from_org_id
, customer_name
, sold_to_org_id
, ship_to_org_id
, operation_code
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
VALUES (c_order_validate_rec.order_source_id
, c_order_validate_rec.orig_sys_document_ref
, c_order_validate_rec.order_type
, c_order_validate_rec.price_list
, c_order_validate_rec.ship_from_org_id
, c_order_validate_rec.customer_name
, c_order_validate_rec.sold_to_org_id
, c_order_validate_rec.ship_to_org_id
, c_order_validate_rec.operation_code
, c_order_validate_rec.created_by
, c_order_validate_rec.creation_date
, c_order_validate_rec.last_update_login
, c_order_validate_rec.last_updated_by
, c_order_validate_rec.last_update_date
);
FOR c_line_validate_rec IN
c_line_validate (c_order_validate_rec.orig_sys_document_ref)
LOOP
INSERT INTO oe_lines_iface_all
(order_source_id
, orig_sys_document_ref
, orig_sys_line_ref
, orig_sys_shipment_ref
, ship_from_org_id
, sold_to_org_id
, ship_to_org_id
, inventory_item_id
, ordered_quantity
, order_quantity_uom
, unit_selling_price
, delivery_lead_time
, delivery_id
, request_date
, created_by
, creation_date
, last_update_login
, last_updated_by
, last_update_date
)
VALUES (c_order_validate_rec.order_source_id
, c_line_validate_rec.orig_sys_document_ref
, c_line_validate_rec.orig_sys_line_ref
, c_line_validate_rec.orig_sys_shipment_ref
, c_order_validate_rec.ship_from_org_id
, c_order_validate_rec.sold_to_org_id
, c_order_validate_rec.ship_to_org_id
, c_line_validate_rec.inventory_item_id
, c_line_validate_rec.ordered_quantity
, c_line_validate_rec.order_quantity_uom
, c_line_validate_rec.unit_selling_price
, c_line_validate_rec.delivery_lead_time
, c_line_validate_rec.delivery_id
, c_line_validate_rec.request_date
, c_line_validate_rec.created_by
, c_line_validate_rec.creation_date
, c_line_validate_rec.last_update_login
, c_line_validate_rec.last_updated_by
, c_line_validate_rec.last_update_date
);
l_ord_insert_rec_count := l_ord_insert_rec_count + 1;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into ORDER INTERFACE TABLES.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 ORDER INTERFACE TABLES.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; --6th end
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN --6 th Begin
UPDATE xx_ont_order_preint
SET status = 'PR'
WHERE status = 'IP';
UPDATE xx_ont_lines_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;
--Initialize apps
--fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN
IF l_ord_insert_rec_count > 0
THEN
l_standard_request_id :=
fnd_request.submit_request (application => 'ONT'
, program => 'OEOIMP'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_order_source_id
, argument2 => NULL
--SB l_orig_sys_document_ref
, argument3 => NULL
, argument4 => 'N'
, argument5 => 1
, argument6 => 4
, argument7 => NULL
--SB l_sold_to_org_id
, argument8 => NULL
, argument9 => NULL
, argument10 => 'Y'
, argument11 => 'N'
);
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;
/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_ont_order_preint
WHERE status = 'PR';
--
DELETE FROM xx_ont_lines_preint
WHERE status = 'PR';
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_cnt_error_recs := l_cnt_processed_recs - l_cnt_successful_recs;
l_return_value := NULL;
--Call Insert Summary Count For Control Validation
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Control Validation'
, p_total_recs => l_cnt_processed_recs
, p_successful_recs => l_cnt_successful_recs
, p_error_recs => l_cnt_error_recs
);
l_return_value := NULL;
--Call Insert Summary Count For Business Validation
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_package_name
|| '-Business Validation'
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;
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
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
END xx_ont_oe_orders_imp_pkg;
/
No comments:
Post a Comment