-- * *****************************************************************************
-- * NAME : XXPOCNV02a.ctl *
-- * PURPOSE : Control File for loading PO Header/Line/Dist *
-- * Staging Tables *
-- * *
-- * *
-- *******************************************************************************
LOAD DATA
TRUNCATE
INTO TABLE XX_PO_LOAD_STG
WHEN REC_TYPE = 'H'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
AGENT_NUM CHAR ,
DOCUMENT_TYPE_CODE CHAR ,
VENDOR_NAME CHAR ,
VENDOR_SITE_CODE CHAR ,
INTERFACE_SOURCE_CODE CHAR ,
CURRENCY_CODE CHAR ,
ORG_CODE CHAR ,
SHIP_TO_LOCATION CHAR ,
BILL_TO_LOCATION CHAR ,
PO_STATUS CHAR ,
STATUS CONSTANT 'NW' ,
STG_HEADER_ID "XX_PO_LOAD_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_LOAD_STG
WHEN REC_TYPE = 'C'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RECORDCNT INTEGER EXTERNAL,
ORG_CODE CHAR ,
STATUS CONSTANT 'NW' ,
STG_HEADER_ID "XX_PO_LOAD_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_LOAD_LINE_STG
WHEN REC_TYPE = 'L'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
LINE_NUM INTEGER EXTERNAL,
SHIPMENT_NUM INTEGER EXTERNAL,
ITEM_DESCRIPTION CHAR ,
QUANTITY DECIMAL EXTERNAL,
LINE_TYPE CHAR ,
ITEM CHAR ,
UNIT_OF_MEASURE CHAR ,
SHIP_TO_LOCATION CHAR ,
UNIT_PRICE DECIMAL EXTERNAL,
PROMISED_DATE ,
STG_PO_LINE_ID "XX_PO_LOAD_LINE_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_DIST_STG
WHEN REC_TYPE = 'D'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
LINE_NUM INTEGER EXTERNAL ,
DISTRIBUTION_NUM INTEGER EXTERNAL ,
SET_OF_BOOKS_NAME CHAR ,
CHARGE_ACCOUNT_SEG1 CHAR ,
CHARGE_ACCOUNT_SEG2 CHAR ,
CHARGE_ACCOUNT_SEG3 CHAR ,
CHARGE_ACCOUNT_SEG4 CHAR ,
CHARGE_ACCOUNT_SEG5 CHAR ,
QUANTITY_ORDERED DECIMAL EXTERNAL,
STG_DISTRIBUTION_ID "XX_PO_DIST_STG_S.NEXTVAL",
CHARGE_ACCOUNT_SEG6 CONSTANT '',
CHARGE_ACCOUNT_SEG7 CONSTANT '',
CHARGE_ACCOUNT_SEG8 CONSTANT '',
CHARGE_ACCOUNT_SEG9 CONSTANT '',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
CREATE OR REPLACE PACKAGE XX_POCNV02_PO_IMPORT_PKG
AS
/*
********************************************************************
Package Name : XX_POCNV02_PO_IMPORT_PKG
Purpose : Package Specification
Program Style :
/*---------------------------------------------------------------------
Public Constant Declaration Section
-----------------------------------------------------------------------*/
--
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
--
/*---------------------------------------------------------------------
Global Variable Declaration Section
-----------------------------------------------------------------------*/
g_debug_level NUMBER:= 50;-- 50 is default,i.e. all messages(logged at level 10,20..50) would be visible
g_retention_period NUMBER:= 30;-- No of days error records are to be retained in the EMF tables
g_err_col1_width NUMBER:= 10;
--
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
--
g_err_col2_width NUMBER:= 30;
g_err_col3_width NUMBER:= 10;
g_err_col4_width NUMBER:= 10;
g_err_col5_width NUMBER:= 10;
g_err_col6_width NUMBER:= 10;
g_err_col7_width NUMBER:= 10;
g_err_col8_width NUMBER:= 10;
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose- Main calling Procedure to Import Purchase Orders
-------------------------------------------------------------*/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
);
END XX_POCNV02_PO_IMPORT_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_POCNV02_PO_IMPORT_PKG
AS
/********************************************************************************************
Package Name : XX_POCNV02_PO_IMPORT_PKG
Purpose : Package Body
Program Style :
***************************************************************************************************/
l_header_id xx_emf_message_headers.header_id%TYPE; -- EMF Header ID
/*******************************************************************************
* Procedure Name : VALIDATE_VENDOR
* Purpose : This program will validate vendor and vendor site
* for the Purchase Order
*
*******************************************************************************/
PROCEDURE validate_vendor
(
p_po_hdr_preint_rec IN xx_po_headers_pre_interface%ROWTYPE ,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************
* Procedure Name : VALIDATE_SHIP_BILL_LOC
* Purpose : This program will validate the ship to or bill to
* location for the purchase order depending on the
* value passed in p_loc_type
*
*******************************************************************************/
PROCEDURE validate_ship_bill_loc
(
p_loc_type IN VARCHAR2,
p_loc IN VARCHAR2,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************
* Procedure Name : validate_set_of_books
* Purpose : This procedure will validate set of books if provided else
* derives it from the OU
*
*******************************************************************************/
PROCEDURE validate_set_of_books
(
p_po_dist_preint_rec IN OUT xx_po_dist_pre_interface%ROWTYPE,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Purchase Order Header ,Line and Distributions and perform
control and business level validations .
It will load the data into the interface tables and call the
Oracle Import Program to create purchase orders
*******************************************************************************************************/
PROCEDURE main (
p_errbuf OUT VARCHAR2 ,
p_retcode OUT VARCHAR2 ,
p_run_mode IN VARCHAR2
)
IS
-- Cursor to select PO Headers data from Staging table
--
CURSOR c_po_stg_header (p_org_code IN VARCHAR2) IS
SELECT stg_header_id
, po_number
, agent_num
, document_type_code
, vendor_name
, vendor_site_code
, interface_source_code
, currency_code
, org_code
, ship_to_location
, bill_to_location
, status
, po_status
FROM xx_po_load_stg
WHERE status = 'NW'
AND rec_type <> 'C'
AND org_code = p_org_code;
-- Cursor to select PO Lines data from Staging table
CURSOR c_po_stg_line (p_header_num IN VARCHAR2) IS
SELECT stg_po_line_id
, po_number
, line_num
, shipment_num
, item_description
, quantity
, line_type
, item
, unit_of_measure
, ship_to_location
, unit_price
, promised_date
FROM xx_po_load_line_stg
WHERE po_number = p_header_num ;
-- Cursor to select PO Distributions data from Staging table
CURSOR c_po_dist_stg ( p_header_num IN VARCHAR2 ,
p_line_num IN VARCHAR2
) IS
SELECT stg_distribution_id
, line_num
, po_number
, distribution_num
, set_of_books_name
, charge_account_seg1
, charge_account_seg2
, charge_account_seg3
, charge_account_seg4
, charge_account_seg5
, charge_account_seg6
, charge_account_seg7
, charge_account_seg8
, charge_account_seg9
, quantity_ordered
FROM xx_po_dist_stg
WHERE po_number = p_header_num
AND line_num = p_line_num ;
-- Cursor to select PO Headers data from Pre-Interface table
CURSOR c_po_hdr_preint(p_org_id IN NUMBER) IS
SELECT *
FROM xx_po_headers_pre_interface
WHERE status = 'IP'
AND org_id = p_org_id;
-- Cursor to select PO Lines data from Pre-Interface table
CURSOR c_po_line_preint(p_header_id IN NUMBER) IS
SELECT *
FROM xx_po_lines_pre_interface
WHERE interface_header_id = p_header_id;
-- Cursor to select PO Distributions data from Pre-Interface table
CURSOR c_po_dist_preint( p_header_id IN NUMBER
,p_line_id IN NUMBER
) IS
SELECT *
FROM xx_po_dist_pre_interface
WHERE interface_header_id = p_header_id
AND interface_line_id = p_line_id ;
-- Cursor to Insert PO Distributions data into Interface table
CURSOR c_po_dist_int ( p_header_id IN NUMBER) IS
SELECT *
FROM xx_po_dist_pre_interface
WHERE interface_header_id = p_header_id;
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
-- General Variables
--
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0;
l_org_id org_organization_definitions.organization_id%TYPE;
l_org_code org_organization_definitions.organization_code%TYPE;
l_int_err_flag VARCHAR2(10):='N';
l_po_exists VARCHAR2(10):='N';
l_emp_id NUMBER := NULL;
l_process_status NUMBER;
l_error_message VARCHAR2(1000);
l_curr_exist VARCHAR2(10) := NULL;
l_vendor_error_flag VARCHAR2(10) :=NULL;
l_ship_err_flag VARCHAR2(10) :=NULL;
l_bill_err_flag VARCHAR2(10) :=NULL;
l_count NUMBER :=0;
l_completed BOOLEAN;
l_ccid_exists VARCHAR2(10):='N';
l_ccid NUMBER;
l_sob_err_flag VARCHAR2(10):='N';
l_valid_line VARCHAR2(10):='N';
l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_value VARCHAR2(20);
l_dist_sum NUMBER :=0;
l_line_count NUMBER :=0;
l_doc_type VARCHAR2(30):=NULL;
v_record_cnt NUMBER:=0;
v_file_hdr_count NUMBER:=0;
v_file_line_count NUMBER:=0;
v_file_dist_count NUMBER:=0;
v_int_header_id NUMBER;
v_interface_line_id NUMBER;
l_inv_planning_code PLS_INTEGER:=0;
--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE:= 'XX_POCNV02_PO_IMPORT_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE:= l_package_name||'.'|| 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE:= 'PO_CNV_02';--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE:= NULL;--used for EMF messages
l_return_value NUMBER:= NULL;--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;--record type variable for assigning width to error section
l_processed_recs NUMBER:= 0;--stores total records for summary report
l_successful_recs NUMBER:= 0;--stores total successful records for summary report
l_error_recs NUMBER:= 0;--stores total error records for the summary report
l_proc_stg_recs NUMBER:= 0;
l_err_stg_recs NUMBER:= 0;
l_succ_stg_recs NUMBER:= 0;
l_status xx_emf_message_headers.status%TYPE;--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1):= 'N';
l_error_cat_flag VARCHAR2 (1):= 'N';--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);--stores the message returned from external routines
--
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
--
-- Common Validations Variables
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_cntrl_fail EXCEPTION;
e_control_record_error EXCEPTION;
--
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
l_org_id := fnd_profile.VALUE ('ORG_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 := 'PO Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Operating Unit'; --Fifth Error Header
l_error_rec.identifier6 := 'Line Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Shipment Number'; --Seventh Error Header
l_error_rec.identifier8 := 'Distribution Number'; --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;
--
BEGIN
SELECT organization_code
INTO l_org_code
FROM org_organization_definitions
WHERE organization_id = l_org_id ;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Error fetching the OU from profile');
END;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'P' THEN
-- Update the new request id in the pre interface record --
BEGIN
UPDATE xx_po_headers_pre_interface
SET request_id =l_request_id
,last_update_date =SYSDATE
,last_updated_by = l_user_id
WHERE org_id = l_org_id
AND status = 'IP';
EXCEPTION
WHEN OTHERS THEN
xx_trace.l('Error updating Pre interface record status');
END;
END IF;
IF p_run_mode ='F' THEN
BEGIN
SELECT recordcnt
INTO v_record_cnt
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type ='C';
SELECT Count(*)
INTO v_file_hdr_count
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C';
SELECT Count(*)
INTO v_file_line_count
FROM xx_po_load_line_stg
WHERE po_number IN (
SELECT po_number
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C'
);
SELECT Count(*)
INTO v_file_dist_count
FROM xx_po_dist_stg
WHERE po_number IN (
SELECT po_number
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C'
);
IF v_record_cnt <> v_file_hdr_count + v_file_line_count + v_file_dist_count THEN
UPDATE xx_po_load_stg
SET status = 'ER'
WHERE status = 'NW'
AND org_code = l_org_code;
COMMIT ;
-- Raise record count error --
xx_trace.l ('Record count mismatch in the data file');
--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-11111',
p_identifier2 => 'Record count mismatch in the data file.',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
--p_identifier7 => NULL,
--p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_control_record_error;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l ('No Control Record in Data File');
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-22222',
p_identifier2 => 'No Control Record in Data File',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
--p_identifier7 => NULL,
--p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_control_record_error;
WHEN e_control_record_error THEN
xx_trace.l('Exiting the program..');
RAISE;
WHEN OTHERS THEN
RAISE;
END;
-- Control level validations --
BEGIN --
FOR c_po_stg_header_rec IN c_po_stg_header(l_org_code)
LOOP
l_error_flag := 'N';
l_proc_stg_recs := l_proc_stg_recs + 1;
IF c_po_stg_header_rec.po_number IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('PO Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Number cannot be Null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.document_type_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Document type code cannot be null');
--Write to Error
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Document type code cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.vendor_name IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Vendor 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-00003'
, p_identifier2 => 'Vendor Name cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.vendor_site_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Vendor Site 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-00004'
, p_identifier2 => 'Vendor site cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.currency_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Currency Code cannot be null');
--Write to Error
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00005'
, p_identifier2 => 'Currency code cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.agent_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Agent Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00006'
, p_identifier2 => 'Agent Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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;
-- Line Level Control Validations --
l_line_count := 0; -- Intialising Line Counts --
FOR c_po_stg_line_rec IN c_po_stg_line (c_po_stg_header_rec.po_number)
LOOP
l_line_count := l_line_count + 1;
IF c_po_stg_line_rec.line_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Line Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00007'
, p_identifier2 => 'Line Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.item IS NULL AND c_po_stg_line_rec.item_description IS NULL )THEN
l_error_flag := 'Y';
xx_trace.l ('Both Item and Item Description 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-00008'
, p_identifier2 => 'Both Item and Item Description cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.quantity IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('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-00009'
, p_identifier2 => 'Quantity cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.unit_price IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Unit 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-00010'
, p_identifier2 => 'Unit Price cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.line_type IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type 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-00011'
, p_identifier2 => 'Line Type cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- p_identifier7 => NULL -- Can be utilized for displaying more information
-- p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- Check if Line and Distribution Quantity Totals are in sync --
BEGIN
SELECT Nvl(Sum(quantity_ordered),0)
INTO l_dist_sum
FROM xx_po_dist_stg
WHERE po_number = c_po_stg_header_rec.po_number
AND line_num = c_po_stg_line_rec.line_num ;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.h ('Error occured while fetching quantity from Distribution'||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-00012'
, p_identifier2 => 'Error occured while fetching quantity from Distribution'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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 c_po_stg_line_rec.quantity <> l_dist_sum AND c_po_stg_header_rec.po_status = 'APPROVED' THEN
l_error_flag := 'Y';
xx_trace.l ('Line and Distribution Totals do not match');
--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-00013'
, p_identifier2 => 'Line and Distribution Totals do not match'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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
);
ELSIF c_po_stg_line_rec.quantity < l_dist_sum AND c_po_stg_header_rec.po_status <> 'APPROVED' THEN
l_error_flag := 'Y';
xx_trace.l ('Line and Distribution Totals do not match');
--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-00014'
, p_identifier2 => 'Line and Distribution Totals do not match'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Distribution Level Validations --
FOR c_po_dist_stg_rec IN c_po_dist_stg(c_po_stg_header_rec.po_number,
c_po_stg_line_rec.line_num
)
LOOP
IF c_po_dist_stg_rec.distribution_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Distribution Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00015'
, p_identifier2 => 'Distribution Number be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_po_dist_stg_rec.charge_account_seg1 IS NULL OR
c_po_dist_stg_rec.charge_account_seg2 IS NULL OR
c_po_dist_stg_rec.charge_account_seg3 IS NULL OR
c_po_dist_stg_rec.charge_account_seg4 IS NULL OR
c_po_dist_stg_rec.charge_account_seg5 IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Charge Account Segment 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-00016'
, p_identifier2 => 'Charge Account Segment cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_po_dist_stg_rec.quantity_ordered IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Quantity Ordered 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-00017'
, p_identifier2 => 'Quantity Ordered cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END LOOP; -- End of Distribution level validation
END LOOP ; -- End of Line Level validations
-- Check for PO's with no line records --
IF l_line_count = 0 THEN
l_error_flag := 'Y';
xx_trace.l ('PO has no lines');
--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-00018'
, p_identifier2 => 'PO has no lines'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- p_identifier6 => NULL -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
--p_identifier8 => NULL -- Can be utilized for displaying more information --
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- If Control level validation fails update the staging table status field to'ER' --
IF l_error_flag = 'Y' THEN
BEGIN
UPDATE xx_po_load_stg
SET status = 'ER'
WHERE stg_header_id = c_po_stg_header_rec.stg_header_id;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.l('Exception While Updating Header Staging Table Oracle Error :'
|| SQLERRM
);
END;
ELSE
-- If no errors in Control level validations insert into pre interface tables --
BEGIN
-- Insert into Header level pre interface --
INSERT INTO xx_po_headers_pre_interface (
interface_header_id
,interface_source_code
,action
,org_id
,document_type_code
,document_num
,currency_code
,agent_name
,vendor_name
,vendor_site_code
,ship_to_location
,bill_to_location
,status
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,approval_status
)
VALUES
(
c_po_stg_header_rec.stg_header_id
,c_po_stg_header_rec.interface_source_code
,'ORIGINAL'
,l_org_id
,c_po_stg_header_rec.document_type_code
,c_po_stg_header_rec.po_number
,c_po_stg_header_rec.currency_code
,c_po_stg_header_rec.agent_num
,c_po_stg_header_rec.vendor_name
,c_po_stg_header_rec.vendor_site_code
,c_po_stg_header_rec.ship_to_location
,c_po_stg_header_rec.bill_to_location
,'IP'
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,Nvl(c_po_stg_header_rec.po_status,'INCOMPLETE')
);
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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 => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00019'
, p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
BEGIN
-- Insert into Line level pre interface --
INSERT INTO xx_po_lines_pre_interface (
interface_header_id
,interface_line_id
,document_num
,line_num
,shipment_num
,line_type
,item
,item_description
,unit_of_measure
,ship_to_location
,quantity
,unit_price
,promised_date
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
SELECT c_po_stg_header_rec.stg_header_id
,stg_po_line_id
,po_number
,line_num
,shipment_num
,line_type
,item
,item_description
,unit_of_measure
,ship_to_location
,quantity
,unit_price
,promised_date
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
FROM xx_po_load_line_stg
WHERE po_number =c_po_stg_header_rec.po_number;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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 => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00020'
, p_identifier2 => 'Exception While Inserting Into Line Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
BEGIN
-- Insert into Distribution level pre interface --
INSERT INTO xx_po_dist_pre_interface (
interface_header_id
,interface_line_id
,interface_distribution_id
,distribution_num
,org_id
,quantity_ordered
,set_of_books
,charge_account_segment1
,charge_account_segment2
,charge_account_segment3
,charge_account_segment4
,charge_account_segment5
,charge_account_segment6
,charge_account_segment7
,charge_account_segment8
,charge_account_segment9
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
SELECT c_po_stg_header_rec.stg_header_id
,stg_po_line_id
,stg_distribution_id
,DISTRIBUTION_NUM
,l_org_id
,quantity_ordered
,set_of_books_name
,charge_account_seg1
,charge_account_seg2
,charge_account_seg3
,charge_account_seg4
,charge_account_seg5
,charge_account_seg6
,charge_account_seg7
,charge_account_seg8
,charge_account_seg9
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
FROM xx_po_load_line_stg xpls
,xx_po_dist_stg xpds
WHERE xpls.po_number = c_po_stg_header_rec.po_number
AND xpls.line_num = xpds.line_num
AND xpls.po_number= xpds.po_number;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO Distribution Pre-Interface table. Oracle Error:'
|| SQLERRM);
xx_emf.call_store_message(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00021'
, p_identifier2 => 'Exception While Inserting Into Distribution Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
l_succ_stg_recs := l_succ_stg_recs + 1;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.h ('Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00022'
, p_identifier2 => 'Error in Control Validation.'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => c_item_rec.item_number
-- ,p_identifier5 => c_item_rec.description
-- ,p_identifier6 => c_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF; --1st Begin
--END of Control Level Validation --
l_err_stg_recs := l_proc_stg_recs - l_succ_stg_recs;
/*----------------------------------------------------------------------------
--Inserting Summary Counts for Staging Table Records
----------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||': Staging'
, p_total_recs => l_proc_stg_recs
, p_successful_recs => l_succ_stg_recs
, p_error_recs => l_err_stg_recs
);
IF l_return_value = 1 THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN
FOR c_po_hdr_preint_rec IN c_po_hdr_preint(l_org_id)
LOOP
l_int_err_flag := 'N' ;
l_processed_recs := l_processed_recs + 1;
-- Check for Duplicate PO --
BEGIN
SELECT 'Y'
INTO l_po_exists
FROM po_headers_all
WHERE type_lookup_code = c_po_hdr_preint_rec.document_type_code
AND segment1= c_po_hdr_preint_rec.document_num;
IF l_po_exists = 'Y' THEN
l_int_err_flag := 'Y';
xx_trace.l ('Duplicate PO: This PO exists in the system');
--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-00023'
, p_identifier2 => 'Duplicate PO: This PO exists in the system'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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 NO_DATA_FOUND THEN
l_int_err_flag :='N';
l_po_exists :='N';
END;
-- Validate the Buyer --
BEGIN
SELECT employee_id
INTO l_emp_id
FROM po_buyers_val_v
WHERE employee_num = c_po_hdr_preint_rec.agent_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_emp_id :=NULL;
xx_trace.l ('Not a valid buyer');
--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-00024'
, p_identifier2 => 'Not a valid buyer'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate the Document Type --
BEGIN
SELECT document_type_code
INTO l_doc_type
FROM po_document_types_all
WHERE document_type_code = 'PO'
AND document_subtype = c_po_hdr_preint_rec.document_type_code
AND org_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_doc_type :=NULL;
xx_trace.l ('Not a valid Docuement Type');
--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-00025'
, p_identifier2 => 'Not a valid Docuement Type'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate the currency code --
l_curr_exist := xx_common_validations_pkg.validate_currency_code
(
p_currency_code => c_po_hdr_preint_rec.currency_code
,p_process_status => l_process_status
,p_error_message => l_error_message
);
IF l_process_status = 1 THEN
l_int_err_flag :='Y';
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-00026'
,p_identifier2 => 'Invalid Currency Code: '||c_po_hdr_preint_rec.currency_code
,p_identifier3 => NULL -- Can be utilized to store record serial number
,p_identifier4 => c_po_hdr_preint_rec.document_num
,p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate vendor and Vendor Site --
validate_vendor (
p_po_hdr_preint_rec => c_po_hdr_preint_rec
,p_error_flag => l_vendor_error_flag
);
-- Validate Ship to Location --
IF c_po_hdr_preint_rec.ship_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'SHIP_TO'
,p_po_num => c_po_hdr_preint_rec.document_num
,p_loc => c_po_hdr_preint_rec.ship_to_location
,p_error_flag => l_ship_err_flag
);
END IF;
-- Validate Bill to location --
IF c_po_hdr_preint_rec.bill_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'BILL_TO'
,p_po_num => c_po_hdr_preint_rec.document_num
,p_loc => c_po_hdr_preint_rec.bill_to_location
,p_error_flag => l_bill_err_flag
);
END IF;
IF l_vendor_error_flag = 'Y' OR l_ship_err_flag ='Y' OR l_bill_err_flag ='Y' THEN
l_int_err_flag :='Y';
END IF;
IF l_int_err_flag = 'N' THEN
-- Line Level Validations --
FOR c_po_line_preint_rec IN c_po_line_preint( c_po_hdr_preint_rec.interface_header_id)
LOOP
BEGIN
SELECT 'Y'
INTO l_valid_line
FROM po_line_types
WHERE UPPER(line_type) = UPPER(c_po_line_preint_rec.line_type);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_valid_line:='N';
xx_trace.l ('Not a valid Line Type');
--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-00027'
, p_identifier2 => 'Not a valid Line Type'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF c_po_line_preint_rec.unit_of_measure IS NOT NULL THEN
-- Validate uom_code --
l_process_status := 0;
l_value := NULL;
xx_common_validations_pkg.validate_uom
( p_unit_of_measure => c_po_line_preint_rec.unit_of_measure
, p_uom_code => l_value
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_process_status = 1 THEN
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00028'
, p_identifier2 => 'Error in validating UOM'|| l_output_message
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag :='Y';
END IF;
END IF;
IF c_po_line_preint_rec.ship_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'SHIP_TO'
,p_po_num => c_po_line_preint_rec.document_num
,p_loc => c_po_line_preint_rec.ship_to_location
,p_error_flag => l_ship_err_flag
);
END IF;
IF l_ship_err_flag ='Y' THEN
l_int_err_flag :='Y';
END IF;
IF c_po_line_preint_rec.item IS NOT NULL THEN
BEGIN
SELECT inventory_item_id,
inventory_planning_code
INTO l_inventory_item_id
,l_inv_planning_code
FROM mtl_system_items_b
WHERE segment1 = c_po_line_preint_rec.item
AND purchasing_enabled_flag ='Y'
AND inventory_item_status_code ='Active'
AND organization_id = (
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = l_org_id
AND ROWNUM <2 );
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
xx_trace.l ('Invalid Item');
--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-00029'
, p_identifier2 => 'Invalid Item '||c_po_line_preint_rec.item
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF c_po_hdr_preint_rec.approval_status = 'APPROVED' THEN
IF l_inv_planning_code =6 AND (c_po_line_preint_rec.promised_date IS NULL OR Trunc(c_po_line_preint_rec.promised_date) < Trunc(SYSDATE) ) THEN
l_int_err_flag :='Y';
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-00030'
,p_identifier2 => 'Promise date is null or invalid Promise Date : '
,p_identifier3 => NULL -- Can be utilized to store record serial number
,p_identifier4 => c_po_hdr_preint_rec.document_num
,p_identifier5 => l_org_code
,p_identifier6 => c_po_line_preint_rec.line_num
-- 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 IF;
END IF;
IF l_int_err_flag = 'N' THEN
-- Distribution Level Validations --
FOR c_po_dist_preint_rec IN c_po_dist_preint( c_po_line_preint_rec.interface_header_id
,c_po_line_preint_rec.interface_line_id )
LOOP
l_ccid_exists:=xx_common_validations_pkg.get_coa_ccid
( p_segment1 => c_po_dist_preint_rec.charge_account_segment1
, p_segment2 => c_po_dist_preint_rec.charge_account_segment2
, p_segment3 => c_po_dist_preint_rec.charge_account_segment3
, p_segment4 => c_po_dist_preint_rec.charge_account_segment4
, p_segment5 => c_po_dist_preint_rec.charge_account_segment5
, p_process_status => l_process_status
, p_error_message => l_output_message
, p_ccid => l_ccid
);
IF l_process_status = 1 THEN
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00030'
, p_identifier2 => 'Error in validating Account'|| l_output_message
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
, p_identifier8 => c_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag :='Y';
END IF;
validate_set_of_books (
p_po_dist_preint_rec => c_po_dist_preint_rec,
p_po_num => c_po_hdr_preint_rec.document_num,
p_error_flag => l_sob_err_flag
);
IF l_sob_err_flag = 'Y' THEN
l_int_err_flag :='Y';
END IF;
END LOOP ; -- End of Distribution Level Business Validations --
END IF;
END LOOP; --End of Line Level Business Validations --
END IF;
IF l_int_err_flag ='N' THEN
l_successful_recs := l_successful_recs + 1;
BEGIN
-- Successful records are inserted in the Interface Table --
SELECT po_headers_interface_s.NEXTVAL
INTO v_int_header_id
FROM dual ;
INSERT INTO po_headers_interface(
interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
group_code,
org_id,
document_type_code,
document_subtype,
document_num,
po_header_id,
release_num,
po_release_id,
release_date,
currency_code,
rate_type,
rate_type_code,
rate_date,
rate,
agent_name,
agent_id,
vendor_name,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact,
vendor_contact_id,
ship_to_location,
ship_to_location_id,
bill_to_location,
bill_to_location_id,
payment_terms,
terms_id,
freight_carrier,
fob,
freight_terms,
approval_status,
approved_date,
revised_date,
revision_num,
note_to_vendor,
note_to_receiver,
confirming_order_flag,
comments,
acceptance_required_flag,
acceptance_due_date,
amount_agreed,
amount_limit,
min_release_amount,
effective_date,
expiration_date,
print_count,
printed_date,
firm_flag,
frozen_flag,
closed_code,
closed_date,
reply_date,
reply_method,
rfq_close_date,
quote_warning_delay,
vendor_doc_num,
approval_required_flag,
vendor_list,
vendor_list_header_id,
from_header_id,
from_type_lookup_code,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
reference_num,
load_sourcing_rules_flag,
vendor_num,
from_rfq_num,
wf_group_id,
pcard_id,
pay_on_code,
global_agreement_flag,
consume_req_demand_flag,
shipping_control,
encumbrance_required_flag,
amount_to_encumber,
change_summary,
budget_account_segment1,
budget_account_segment2,
budget_account_segment3,
budget_account_segment4,
budget_account_segment5,
budget_account_segment6,
budget_account_segment7,
budget_account_segment8,
budget_account_segment9,
budget_account,
budget_account_id,
gl_encumbered_date,
gl_encumbered_period_name
)
VALUES (
v_int_header_id,
c_po_hdr_preint_rec.batch_id,
c_po_hdr_preint_rec.interface_source_code,
c_po_hdr_preint_rec.process_code,
c_po_hdr_preint_rec.action,
c_po_hdr_preint_rec.group_code,
c_po_hdr_preint_rec.org_id,
c_po_hdr_preint_rec.document_type_code,
c_po_hdr_preint_rec.document_subtype,
c_po_hdr_preint_rec.document_num,
c_po_hdr_preint_rec.po_header_id,
c_po_hdr_preint_rec.release_num,
c_po_hdr_preint_rec.po_release_id,
c_po_hdr_preint_rec.release_date,
c_po_hdr_preint_rec.currency_code,
c_po_hdr_preint_rec.rate_type,
c_po_hdr_preint_rec.rate_type_code,
c_po_hdr_preint_rec.rate_date,
c_po_hdr_preint_rec.rate,
NULL,
l_emp_id,
c_po_hdr_preint_rec.vendor_name,
c_po_hdr_preint_rec.vendor_id,
c_po_hdr_preint_rec.vendor_site_code,
c_po_hdr_preint_rec.vendor_site_id,
c_po_hdr_preint_rec.vendor_contact,
c_po_hdr_preint_rec.vendor_contact_id,
c_po_hdr_preint_rec.ship_to_location,
c_po_hdr_preint_rec.ship_to_location_id,
c_po_hdr_preint_rec.bill_to_location,
c_po_hdr_preint_rec.bill_to_location_id,
c_po_hdr_preint_rec.payment_terms,
c_po_hdr_preint_rec.terms_id,
c_po_hdr_preint_rec.freight_carrier,
c_po_hdr_preint_rec.fob,
c_po_hdr_preint_rec.freight_terms,
c_po_hdr_preint_rec.approval_status,
c_po_hdr_preint_rec.approved_date,
c_po_hdr_preint_rec.revised_date,
c_po_hdr_preint_rec.revision_num,
c_po_hdr_preint_rec.note_to_vendor,
c_po_hdr_preint_rec.note_to_receiver,
c_po_hdr_preint_rec.confirming_order_flag,
c_po_hdr_preint_rec.comments,
c_po_hdr_preint_rec.acceptance_required_flag,
c_po_hdr_preint_rec.acceptance_due_date,
c_po_hdr_preint_rec.amount_agreed,
c_po_hdr_preint_rec.amount_limit,
c_po_hdr_preint_rec.min_release_amount,
c_po_hdr_preint_rec.effective_date,
c_po_hdr_preint_rec.expiration_date,
c_po_hdr_preint_rec.print_count,
c_po_hdr_preint_rec.printed_date,
c_po_hdr_preint_rec.firm_flag,
c_po_hdr_preint_rec.frozen_flag,
c_po_hdr_preint_rec.closed_code,
c_po_hdr_preint_rec.closed_date,
c_po_hdr_preint_rec.reply_date,
c_po_hdr_preint_rec.reply_method,
c_po_hdr_preint_rec.rfq_close_date,
c_po_hdr_preint_rec.quote_warning_delay,
c_po_hdr_preint_rec.vendor_doc_num,
c_po_hdr_preint_rec.approval_required_flag,
c_po_hdr_preint_rec.vendor_list,
c_po_hdr_preint_rec.vendor_list_header_id,
c_po_hdr_preint_rec.from_header_id,
c_po_hdr_preint_rec.from_type_lookup_code,
c_po_hdr_preint_rec.ussgl_transaction_code,
c_po_hdr_preint_rec.attribute_category,
c_po_hdr_preint_rec.attribute1,
c_po_hdr_preint_rec.attribute2,
c_po_hdr_preint_rec.attribute3,
c_po_hdr_preint_rec.attribute4,
c_po_hdr_preint_rec.attribute5,
c_po_hdr_preint_rec.attribute6,
c_po_hdr_preint_rec.attribute7,
c_po_hdr_preint_rec.attribute8,
c_po_hdr_preint_rec.attribute9,
c_po_hdr_preint_rec.attribute10,
c_po_hdr_preint_rec.attribute11,
c_po_hdr_preint_rec.attribute12,
c_po_hdr_preint_rec.attribute13,
c_po_hdr_preint_rec.attribute14,
c_po_hdr_preint_rec.attribute15,
sysdate,
l_user_id,
sysdate,
l_user_id,
c_po_hdr_preint_rec.last_update_login,
c_po_hdr_preint_rec.request_id,
c_po_hdr_preint_rec.program_application_id,
c_po_hdr_preint_rec.program_id,
c_po_hdr_preint_rec.program_update_date,
c_po_hdr_preint_rec.reference_num,
c_po_hdr_preint_rec.load_sourcing_rules_flag,
c_po_hdr_preint_rec.vendor_num,
c_po_hdr_preint_rec.from_rfq_num,
c_po_hdr_preint_rec.wf_group_id,
c_po_hdr_preint_rec.pcard_id,
c_po_hdr_preint_rec.pay_on_code,
c_po_hdr_preint_rec.global_agreement_flag,
c_po_hdr_preint_rec.consume_req_demand_flag,
c_po_hdr_preint_rec.shipping_control,
c_po_hdr_preint_rec.encumbrance_required_flag,
c_po_hdr_preint_rec.amount_to_encumber,
c_po_hdr_preint_rec.change_summary,
c_po_hdr_preint_rec.budget_account_segment1,
c_po_hdr_preint_rec.budget_account_segment2,
c_po_hdr_preint_rec.budget_account_segment3,
c_po_hdr_preint_rec.budget_account_segment4,
c_po_hdr_preint_rec.budget_account_segment5,
c_po_hdr_preint_rec.budget_account_segment6,
c_po_hdr_preint_rec.budget_account_segment7,
c_po_hdr_preint_rec.budget_account_segment8,
c_po_hdr_preint_rec.budget_account_segment9,
c_po_hdr_preint_rec.budget_account,
c_po_hdr_preint_rec.budget_account_id,
c_po_hdr_preint_rec.gl_encumbered_date,
c_po_hdr_preint_rec.gl_encumbered_period_name
);
INSERT INTO po_lines_interface (
interface_line_id,
interface_header_id,
action,
group_code,
line_num,
po_line_id,
shipment_num,
line_location_id,
shipment_type,
requisition_line_id,
document_num,
release_num,
po_header_id,
po_release_id,
source_shipment_id,
contract_num,
line_type,
line_type_id,
item, item_id,
item_revision,
CATEGORY,
category_id,
item_description,
vendor_product_num,
uom_code,
unit_of_measure,
quantity,
committed_amount,
min_order_quantity,
max_order_quantity,
unit_price,
list_price_per_unit,
market_price,
allow_price_override_flag,
not_to_exceed_price,
negotiated_by_preparer_flag,
un_number,
un_number_id,
hazard_class,
hazard_class_id,
note_to_vendor,
transaction_reason_code,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
inspection_required_flag,
receipt_required_flag,
payment_terms,
terms_id,
price_type,
min_release_amount,
price_break_lookup_code,
ussgl_transaction_code,
closed_code,
closed_reason,
closed_date,
closed_by,
invoice_close_tolearnce,
receive_close_tolerance,
firm_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing,
receiving_routing_id,
qty_rcv_tolerance,
over_tolerance_error_flag,
qty_rcv_exception_code,
receipt_days_exception_code,
ship_to_organization_code,
ship_to_organization_id,
ship_to_location,
ship_to_location_id,
need_by_date,
promised_date,
accrue_on_receipt_flag,
lead_time,
lead_time_unit,
price_discount,
freight_carrier,
fob, freight_terms,
effective_date,
expiration_date,
from_header_id,
from_line_id,
from_line_location_id,
line_attribute_category_lines,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
shipment_attribute_category,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
shipment_attribute7,
shipment_attribute8,
shipment_attribute9,
shipment_attribute10,
shipment_attribute11,
shipment_attribute12,
shipment_attribute13,
shipment_attribute14,
shipment_attribute15,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
invoice_close_tolerance,
organization_id,
item_attribute_category,
item_attribute1,
item_attribute2,
item_attribute3,
item_attribute4,
item_attribute5,
item_attribute6,
item_attribute7,
item_attribute8,
item_attribute9,
item_attribute10,
item_attribute11,
item_attribute12,
item_attribute13,
item_attribute14,
item_attribute15,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
template_id,
template_name,
line_reference_num,
sourcing_rule_name,
tax_status_indicator,
process_code,
price_chg_accept_flag,
price_break_flag,
price_update_tolerance,
tax_user_override_flag,
tax_code_id,
note_to_receiver,
oke_contract_header_id,
oke_contract_header_num,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_uom_code,
secondary_quantity,
preferred_grade,
vmi_flag,
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
orig_from_req_flag,
consigned_flag,
supplier_ref_number,
contract_id,
job_id,
amount,
job_name,
contractor_first_name,
contractor_last_name,
drop_ship_flag,
base_unit_price,
transaction_flow_header_id,
job_business_group_id,
job_business_group_name
)
SELECT po_lines_interface_s.NEXTVAL,
v_int_header_id,
action,
group_code,
line_num,
po_line_id,
shipment_num,
line_location_id,
shipment_type,
requisition_line_id,
document_num,
release_num,
po_header_id,
po_release_id,
source_shipment_id,
contract_num,
line_type,
line_type_id,
item,
item_id,
item_revision,
CATEGORY,
category_id,
item_description,
vendor_product_num,
uom_code,
unit_of_measure,
quantity,
committed_amount,
min_order_quantity,
max_order_quantity,
unit_price,
list_price_per_unit,
market_price,
allow_price_override_flag,
not_to_exceed_price,
negotiated_by_preparer_flag,
un_number,
un_number_id,
hazard_class,
hazard_class_id,
note_to_vendor,
transaction_reason_code,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
inspection_required_flag,
receipt_required_flag,
payment_terms,
terms_id,
price_type,
min_release_amount,
price_break_lookup_code,
ussgl_transaction_code,
closed_code,
closed_reason,
closed_date,
closed_by,
invoice_close_tolearnce,
receive_close_tolerance,
firm_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing,
receiving_routing_id,
qty_rcv_tolerance,
over_tolerance_error_flag,
qty_rcv_exception_code,
receipt_days_exception_code,
ship_to_organization_code,
ship_to_organization_id,
ship_to_location,
ship_to_location_id,
need_by_date,
promised_date,
accrue_on_receipt_flag,
lead_time,
lead_time_unit,
price_discount,
freight_carrier,
fob,
freight_terms,
effective_date,
expiration_date,
from_header_id,
from_line_id,
from_line_location_id,
line_attribute_category_lines,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
shipment_attribute_category,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
shipment_attribute7,
shipment_attribute8,
shipment_attribute9,
shipment_attribute10,
shipment_attribute11,
shipment_attribute12,
shipment_attribute13,
shipment_attribute14,
shipment_attribute15,
sysdate,
l_user_id,
last_update_login,
sysdate,
l_user_id,
request_id,
program_application_id,
program_id,
program_update_date,
invoice_close_tolerance,
organization_id,
item_attribute_category,
item_attribute1,
item_attribute2,
item_attribute3,
item_attribute4,
item_attribute5,
item_attribute6,
item_attribute7,
item_attribute8,
item_attribute9,
item_attribute10,
item_attribute11,
item_attribute12,
item_attribute13,
item_attribute14,
item_attribute15,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
template_id,
template_name,
line_reference_num,
sourcing_rule_name,
tax_status_indicator,
process_code,
price_chg_accept_flag,
price_break_flag,
price_update_tolerance,
tax_user_override_flag,
tax_code_id,
note_to_receiver,
oke_contract_header_id,
oke_contract_header_num,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_uom_code,
secondary_quantity,
preferred_grade,
vmi_flag,
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
orig_from_req_flag,
consigned_flag,
supplier_ref_number,
contract_id,
job_id,
amount,
job_name,
contractor_first_name,
contractor_last_name,
drop_ship_flag,
base_unit_price,
transaction_flow_header_id,
job_business_group_id,
job_business_group_name
FROM xx_po_lines_pre_interface
WHERE interface_header_id = c_po_hdr_preint_rec.interface_header_id;
-- Insert into PO Distributions Interface --
FOR c_po_dist_rec IN c_po_dist_int (c_po_hdr_preint_rec.interface_header_id)
LOOP
BEGIN
SELECT poli.interface_line_id
INTO v_interface_line_id
FROM po_lines_interface poli
,xx_po_lines_pre_interface xpl
WHERE poli.interface_header_id = v_int_header_id
AND xpl.interface_header_id = c_po_hdr_preint_rec.interface_header_id
AND xpl.interface_line_id = c_po_dist_rec.interface_line_id
AND poli.line_num =xpl.line_num
AND ROWNUM < 2;
INSERT INTO po_distributions_interface (
interface_header_id,
interface_line_id,
interface_distribution_id,
po_header_id,
po_release_id,
po_line_id,
line_location_id,
po_distribution_id,
distribution_num,
source_distribution_id,
org_id,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
rate_date,
rate,
deliver_to_location,
deliver_to_location_id,
deliver_to_person_full_name,
deliver_to_person_id,
destination_type,
destination_type_code,
destination_organization,
destination_organization_id,
destination_subinventory,
destination_context,
set_of_books,
set_of_books_id,
charge_account,
charge_account_id,
budget_account,
budget_account_id,
accural_account,
accrual_account_id,
variance_account,
variance_account_id,
amount_billed,
accrue_on_receipt_flag,
accrued_flag,
prevent_encumbrance_flag,
encumbered_flag,
encumbered_amount,
unencumbered_quantity,
unencumbered_amount,
failed_funds,
failed_funds_lookup_code,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
gl_closed_date,
req_header_reference_num,
req_line_reference_num,
req_distribution_id,
wip_entity,
wip_entity_id,
wip_operation_seq_num,
wip_resource_seq_num,
wip_repetitive_schedule,
wip_repetitive_schedule_id,
wip_line_code,
wip_line_id,
bom_resource_code,
bom_resource_id,
ussgl_transaction_code,
government_context,
project,
project_id,
task,
task_id,
expenditure,
expenditure_type,
project_accounting_context,
expenditure_organization,
expenditure_organization_id,
project_releated_flag,
expenditure_item_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
end_item_unit_number,
recoverable_tax,
nonrecoverable_tax,
recovery_rate,
tax_recovery_override_flag,
award_id,
charge_account_segment1,
charge_account_segment2,
charge_account_segment3,
charge_account_segment4,
charge_account_segment5,
charge_account_segment6,
charge_account_segment7,
charge_account_segment8,
charge_account_segment9,
charge_account_segment10,
charge_account_segment11,
charge_account_segment12,
charge_account_segment13,
charge_account_segment14,
charge_account_segment15,
oke_contract_line_id,
oke_contract_line_num,
oke_contract_deliverable_id,
oke_contract_deliverable_num,
award_number, amount_ordered,
invoice_adjustment_flag,
dest_charge_account_id,
dest_variance_account_id
)
VALUES (
v_int_header_id
,v_interface_line_id
,po_distributions_interface_s.NEXTVAL
,c_po_dist_rec.po_header_id
,c_po_dist_rec.po_release_id
,c_po_dist_rec.po_line_id
,c_po_dist_rec.line_location_id
,c_po_dist_rec.po_distribution_id
,c_po_dist_rec.distribution_num
,c_po_dist_rec.source_distribution_id
,c_po_dist_rec.org_id
,c_po_dist_rec.quantity_ordered
,c_po_dist_rec.quantity_delivered
,c_po_dist_rec.quantity_billed
,c_po_dist_rec.quantity_cancelled
,c_po_dist_rec.rate_date
,c_po_dist_rec.rate
,c_po_dist_rec.deliver_to_location
,c_po_dist_rec.deliver_to_location_id
,c_po_dist_rec.deliver_to_person_full_name
,c_po_dist_rec.deliver_to_person_id
,c_po_dist_rec.destination_type
,c_po_dist_rec.destination_type_code
,c_po_dist_rec.destination_organization
,c_po_dist_rec.destination_organization_id
,c_po_dist_rec.destination_subinventory
,c_po_dist_rec.destination_context
,c_po_dist_rec.set_of_books
,c_po_dist_rec.set_of_books_id
,c_po_dist_rec.charge_account
,c_po_dist_rec.charge_account_id
,c_po_dist_rec.budget_account
,c_po_dist_rec.budget_account_id
,c_po_dist_rec.accural_account
,c_po_dist_rec.accrual_account_id
,c_po_dist_rec.variance_account
,c_po_dist_rec.variance_account_id
,c_po_dist_rec.amount_billed
,c_po_dist_rec.accrue_on_receipt_flag
,c_po_dist_rec.accrued_flag
,c_po_dist_rec.prevent_encumbrance_flag
,c_po_dist_rec.encumbered_flag
,c_po_dist_rec.encumbered_amount
,c_po_dist_rec.unencumbered_quantity
,c_po_dist_rec.unencumbered_amount
,c_po_dist_rec.failed_funds
,c_po_dist_rec.failed_funds_lookup_code
,c_po_dist_rec.gl_encumbered_date
,c_po_dist_rec.gl_encumbered_period_name
,c_po_dist_rec.gl_cancelled_date
,c_po_dist_rec.gl_closed_date
,c_po_dist_rec.req_header_reference_num
,c_po_dist_rec.req_line_reference_num
,c_po_dist_rec.req_distribution_id
,c_po_dist_rec.wip_entity
,c_po_dist_rec.wip_entity_id
,c_po_dist_rec.wip_operation_seq_num
,c_po_dist_rec.wip_resource_seq_num
,c_po_dist_rec.wip_repetitive_schedule
,c_po_dist_rec.wip_repetitive_schedule_id
,c_po_dist_rec.wip_line_code
,c_po_dist_rec.wip_line_id
,c_po_dist_rec.bom_resource_code
,c_po_dist_rec.bom_resource_id
,c_po_dist_rec.ussgl_transaction_code
,c_po_dist_rec.government_context
,c_po_dist_rec.project
,c_po_dist_rec.project_id
,c_po_dist_rec.task
,c_po_dist_rec.task_id
,c_po_dist_rec.expenditure
,c_po_dist_rec.expenditure_type
,c_po_dist_rec.project_accounting_context
,c_po_dist_rec.expenditure_organization
,c_po_dist_rec.expenditure_organization_id
,c_po_dist_rec.project_releated_flag
,c_po_dist_rec.expenditure_item_date
,c_po_dist_rec.attribute_category
,c_po_dist_rec.attribute1
,c_po_dist_rec.attribute2
,c_po_dist_rec.attribute3
,c_po_dist_rec.attribute4
,c_po_dist_rec.attribute5
,c_po_dist_rec.attribute6
,c_po_dist_rec.attribute7
,c_po_dist_rec.attribute8
,c_po_dist_rec.attribute9
,c_po_dist_rec.attribute10
,c_po_dist_rec.attribute11
,c_po_dist_rec.attribute12
,c_po_dist_rec.attribute13
,c_po_dist_rec.attribute14
,c_po_dist_rec.attribute15
,c_po_dist_rec.last_update_date
,c_po_dist_rec.last_updated_by
,c_po_dist_rec.last_update_login
,c_po_dist_rec.creation_date
,c_po_dist_rec.created_by
,c_po_dist_rec.request_id
,c_po_dist_rec.program_application_id
,c_po_dist_rec.program_id
,c_po_dist_rec.program_update_date
,c_po_dist_rec.end_item_unit_number
,c_po_dist_rec.recoverable_tax
,c_po_dist_rec.nonrecoverable_tax
,c_po_dist_rec.recovery_rate
,c_po_dist_rec.tax_recovery_override_flag
,c_po_dist_rec.award_id
,c_po_dist_rec.charge_account_segment1
,c_po_dist_rec.charge_account_segment2
,c_po_dist_rec.charge_account_segment3
,c_po_dist_rec.charge_account_segment4
,c_po_dist_rec.charge_account_segment5
,c_po_dist_rec.charge_account_segment6
,c_po_dist_rec.charge_account_segment7
,c_po_dist_rec.charge_account_segment8
,c_po_dist_rec.charge_account_segment9
,c_po_dist_rec.charge_account_segment10
,c_po_dist_rec.charge_account_segment11
,c_po_dist_rec.charge_account_segment12
,c_po_dist_rec.charge_account_segment13
,c_po_dist_rec.charge_account_segment14
,c_po_dist_rec.charge_account_segment15
,c_po_dist_rec.oke_contract_line_id
,c_po_dist_rec.oke_contract_line_num
,c_po_dist_rec.oke_contract_deliverable_id
,c_po_dist_rec.oke_contract_deliverable_num
,c_po_dist_rec.award_number
,c_po_dist_rec.amount_ordered
,c_po_dist_rec.invoice_adjustment_flag
,c_po_dist_rec.dest_charge_account_id
,c_po_dist_rec.dest_variance_account_id
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Error deriving the interface line');
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-00031'
, p_identifier2 => 'Error deriving the interface line-'
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
, p_identifier8 => c_po_dist_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO Dist 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-00032'
, p_identifier2 => 'Exception While Inserting Into PO Dist Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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-00033'
, p_identifier2 => 'Exception While Inserting Into PO Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
END;
END IF;
IF l_int_err_flag ='Y' THEN
BEGIN
l_error_flag := l_int_err_flag;
UPDATE xx_po_headers_pre_interface
SET status = 'ER'
WHERE interface_header_id = c_po_hdr_preint_rec.interface_header_id
AND status ='IP';
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Error updating Status of pre - interface');
--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-00034'
, p_identifier2 => 'Error updating Status of pre - interface'
, p_identifier3 => NULL
-- Can be utilized to store record serial number --
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h
('Unexpected Exception in Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--End of Business Validation Section
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN
UPDATE xx_po_headers_pre_interface
SET status = 'PR'
WHERE status = 'IP'
AND org_id = l_org_id ;
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-00035'
, 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
SELECT COUNT (*)
INTO l_count
FROM xx_po_headers_pre_interface
WHERE status = 'PR'
AND org_id = l_org_id
AND request_id = l_request_id ;
IF l_count > 0
THEN
/*---------------------------------------------------------------------------------------
-- This concurrent program is used for creating Standard Purchase Orders .For importing
-- Blanket Purchase Orders/Quotations the Program - Import Price Catalogs is to be used
----------------------------------------------------------------------------------------*/
l_standard_request_id := fnd_request.submit_request
( application => 'PO'
, program => 'POXPOPDOI'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => NULL --'Default Buyer'
, argument2 => 'STANDARD' --'Document Type'
, argument3 => NULL --'Document subtype'
, argument4 => 'N' --'Create/Replace Item -YES/NO'
, argument5 => NULL --'Create Sourcing Rules -Yes/No'
--, argument6 => 'APPROVED' --'PO Status'
,argument6 =>NULL
, argument7 => NULL
, argument8 => NULL
, argument9 => NULL
);
IF l_standard_request_id > 0 THEN
COMMIT;
ELSE
l_error_flag := 'Y';
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-00036'
, p_identifier2 => 'Error in submitting concurrent request'
, 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;
--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-00036'
, p_identifier2 => 'No records To Process in Interface Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END;
-- Delete the processed records from the interface table --
BEGIN
DELETE FROM xx_po_dist_pre_interface
WHERE interface_header_id IN (
SELECT interface_header_id
FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR'
);
DELETE FROM xx_po_lines_pre_interface
WHERE interface_header_id IN (
SELECT interface_header_id
FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR'
);
DELETE FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
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_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-00035'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
/*******************************************************************************
* Procedure Name : VALIDATE_VENDOR
* Purpose : This program will validate vendor and vendor site
* for the Purchase Order
*
*******************************************************************************/
PROCEDURE validate_vendor (
p_po_hdr_preint_rec IN xx_po_headers_pre_interface%ROWTYPE
,p_error_flag OUT VARCHAR2
)
IS
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_valid_vendor VARCHAR2(1):='N';
l_valid_site VARCHAR2(1):='N';
BEGIN
p_error_flag := 'N';
BEGIN
SELECT 'Y'
INTO l_valid_vendor
FROM po_vendors
WHERE vendor_name = p_po_hdr_preint_rec.vendor_name
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Vendor is not valid');
--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-00037'
, p_identifier2 => 'Vendor is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
IF p_error_flag = 'N' THEN
BEGIN
SELECT 'Y'
INTO l_valid_site
FROM po_vendor_sites_all
WHERE vendor_site_code = p_po_hdr_preint_rec.vendor_site_code
AND org_id = p_po_hdr_preint_rec.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Vendor Site is not valid');
--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-00038'
, p_identifier2 => 'Vendor Site is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating vendor'||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-00039'
, p_identifier2 => 'Error occurred while validating vendor'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
/*******************************************************************************
* Procedure Name : VALIDATE_SHIP_BILL_LOC
* Purpose : This program will validate the ship to or bill to
* location for the purchase order depending on the
* value passed in p_loc_type
*
*******************************************************************************/
PROCEDURE validate_ship_bill_loc (
p_loc_type IN VARCHAR2,
p_loc IN VARCHAR2,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
)
IS
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_loc_valid VARCHAR2(1):='N';
BEGIN
p_error_flag := 'N';
IF p_loc_type = 'SHIP_TO' THEN
BEGIN
SELECT 'Y'
INTO l_loc_valid
FROM hr_locations_all
WHERE ship_to_site_flag = 'Y'
AND sysdate < NVL(inactive_date, sysdate+1)
AND location_code = p_loc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Ship to location is not valid');
--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-00040'
, p_identifier2 => 'Ship to location is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
ELSE
BEGIN
SELECT 'Y'
INTO l_loc_valid
FROM hr_locations_all
WHERE bill_to_site_flag = 'Y'
AND location_code = p_loc
AND sysdate < NVL(inactive_date, sysdate+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Bill to location is not valid');
--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-00041'
, p_identifier2 => 'Bill to location is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating location'||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-00042'
, p_identifier2 => 'Error occurred while validating location'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
/*******************************************************************************
* Procedure Name : validate_set_of_books
* Purpose : This procedure will validate set of books if provided else
* derives it from the OU
*
*******************************************************************************/
PROCEDURE validate_set_of_books (
p_po_dist_preint_rec IN OUT xx_po_dist_pre_interface%ROWTYPE,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
)
IS
l_set_of_books gl_sets_of_books.name%TYPE;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
BEGIN
IF p_po_dist_preint_rec.set_of_books IS NULL THEN
BEGIN
SELECT gsob.name
INTO l_set_of_books
FROM gl_sets_of_books gsob
,hr_operating_units hou
WHERE hou.organization_id = p_po_dist_preint_rec.org_id
AND hou.set_of_books_id = gsob.set_of_books_id;
p_po_dist_preint_rec.set_of_books := l_set_of_books;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
ELSE
BEGIN
SELECT gsob.name
INTO l_set_of_books
FROM gl_sets_of_books gsob
,hr_operating_units hou
WHERE gsob.name = p_po_dist_preint_rec.set_of_books
AND hou.set_of_books_id = gsob.set_of_books_id
AND hou.organization_id = p_po_dist_preint_rec.org_id ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Set of books is not valid');
--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-00043'
, p_identifier2 => 'Set of books is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => p_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating Set of books'||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-00043'
, p_identifier2 => 'Error occurred while validating Set of books'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => p_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END XX_POCNV02_PO_IMPORT_PKG;
/
-- * NAME : XXPOCNV02a.ctl *
-- * PURPOSE : Control File for loading PO Header/Line/Dist *
-- * Staging Tables *
-- * *
-- * *
-- *******************************************************************************
LOAD DATA
TRUNCATE
INTO TABLE XX_PO_LOAD_STG
WHEN REC_TYPE = 'H'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
AGENT_NUM CHAR ,
DOCUMENT_TYPE_CODE CHAR ,
VENDOR_NAME CHAR ,
VENDOR_SITE_CODE CHAR ,
INTERFACE_SOURCE_CODE CHAR ,
CURRENCY_CODE CHAR ,
ORG_CODE CHAR ,
SHIP_TO_LOCATION CHAR ,
BILL_TO_LOCATION CHAR ,
PO_STATUS CHAR ,
STATUS CONSTANT 'NW' ,
STG_HEADER_ID "XX_PO_LOAD_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_LOAD_STG
WHEN REC_TYPE = 'C'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RECORDCNT INTEGER EXTERNAL,
ORG_CODE CHAR ,
STATUS CONSTANT 'NW' ,
STG_HEADER_ID "XX_PO_LOAD_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_LOAD_LINE_STG
WHEN REC_TYPE = 'L'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
LINE_NUM INTEGER EXTERNAL,
SHIPMENT_NUM INTEGER EXTERNAL,
ITEM_DESCRIPTION CHAR ,
QUANTITY DECIMAL EXTERNAL,
LINE_TYPE CHAR ,
ITEM CHAR ,
UNIT_OF_MEASURE CHAR ,
SHIP_TO_LOCATION CHAR ,
UNIT_PRICE DECIMAL EXTERNAL,
PROMISED_DATE ,
STG_PO_LINE_ID "XX_PO_LOAD_LINE_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
INTO TABLE XX_PO_DIST_STG
WHEN REC_TYPE = 'D'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
PO_NUMBER CHAR ,
LINE_NUM INTEGER EXTERNAL ,
DISTRIBUTION_NUM INTEGER EXTERNAL ,
SET_OF_BOOKS_NAME CHAR ,
CHARGE_ACCOUNT_SEG1 CHAR ,
CHARGE_ACCOUNT_SEG2 CHAR ,
CHARGE_ACCOUNT_SEG3 CHAR ,
CHARGE_ACCOUNT_SEG4 CHAR ,
CHARGE_ACCOUNT_SEG5 CHAR ,
QUANTITY_ORDERED DECIMAL EXTERNAL,
STG_DISTRIBUTION_ID "XX_PO_DIST_STG_S.NEXTVAL",
CHARGE_ACCOUNT_SEG6 CONSTANT '',
CHARGE_ACCOUNT_SEG7 CONSTANT '',
CHARGE_ACCOUNT_SEG8 CONSTANT '',
CHARGE_ACCOUNT_SEG9 CONSTANT '',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)
CREATE OR REPLACE PACKAGE XX_POCNV02_PO_IMPORT_PKG
AS
/*
********************************************************************
Package Name : XX_POCNV02_PO_IMPORT_PKG
Purpose : Package Specification
Program Style :
/*---------------------------------------------------------------------
Public Constant Declaration Section
-----------------------------------------------------------------------*/
--
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
--
/*---------------------------------------------------------------------
Global Variable Declaration Section
-----------------------------------------------------------------------*/
g_debug_level NUMBER:= 50;-- 50 is default,i.e. all messages(logged at level 10,20..50) would be visible
g_retention_period NUMBER:= 30;-- No of days error records are to be retained in the EMF tables
g_err_col1_width NUMBER:= 10;
--
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
--
g_err_col2_width NUMBER:= 30;
g_err_col3_width NUMBER:= 10;
g_err_col4_width NUMBER:= 10;
g_err_col5_width NUMBER:= 10;
g_err_col6_width NUMBER:= 10;
g_err_col7_width NUMBER:= 10;
g_err_col8_width NUMBER:= 10;
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose- Main calling Procedure to Import Purchase Orders
-------------------------------------------------------------*/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
);
END XX_POCNV02_PO_IMPORT_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_POCNV02_PO_IMPORT_PKG
AS
/********************************************************************************************
Package Name : XX_POCNV02_PO_IMPORT_PKG
Purpose : Package Body
Program Style :
***************************************************************************************************/
l_header_id xx_emf_message_headers.header_id%TYPE; -- EMF Header ID
/*******************************************************************************
* Procedure Name : VALIDATE_VENDOR
* Purpose : This program will validate vendor and vendor site
* for the Purchase Order
*
*******************************************************************************/
PROCEDURE validate_vendor
(
p_po_hdr_preint_rec IN xx_po_headers_pre_interface%ROWTYPE ,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************
* Procedure Name : VALIDATE_SHIP_BILL_LOC
* Purpose : This program will validate the ship to or bill to
* location for the purchase order depending on the
* value passed in p_loc_type
*
*******************************************************************************/
PROCEDURE validate_ship_bill_loc
(
p_loc_type IN VARCHAR2,
p_loc IN VARCHAR2,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************
* Procedure Name : validate_set_of_books
* Purpose : This procedure will validate set of books if provided else
* derives it from the OU
*
*******************************************************************************/
PROCEDURE validate_set_of_books
(
p_po_dist_preint_rec IN OUT xx_po_dist_pre_interface%ROWTYPE,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
);
/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Purchase Order Header ,Line and Distributions and perform
control and business level validations .
It will load the data into the interface tables and call the
Oracle Import Program to create purchase orders
*******************************************************************************************************/
PROCEDURE main (
p_errbuf OUT VARCHAR2 ,
p_retcode OUT VARCHAR2 ,
p_run_mode IN VARCHAR2
)
IS
-- Cursor to select PO Headers data from Staging table
--
CURSOR c_po_stg_header (p_org_code IN VARCHAR2) IS
SELECT stg_header_id
, po_number
, agent_num
, document_type_code
, vendor_name
, vendor_site_code
, interface_source_code
, currency_code
, org_code
, ship_to_location
, bill_to_location
, status
, po_status
FROM xx_po_load_stg
WHERE status = 'NW'
AND rec_type <> 'C'
AND org_code = p_org_code;
-- Cursor to select PO Lines data from Staging table
CURSOR c_po_stg_line (p_header_num IN VARCHAR2) IS
SELECT stg_po_line_id
, po_number
, line_num
, shipment_num
, item_description
, quantity
, line_type
, item
, unit_of_measure
, ship_to_location
, unit_price
, promised_date
FROM xx_po_load_line_stg
WHERE po_number = p_header_num ;
-- Cursor to select PO Distributions data from Staging table
CURSOR c_po_dist_stg ( p_header_num IN VARCHAR2 ,
p_line_num IN VARCHAR2
) IS
SELECT stg_distribution_id
, line_num
, po_number
, distribution_num
, set_of_books_name
, charge_account_seg1
, charge_account_seg2
, charge_account_seg3
, charge_account_seg4
, charge_account_seg5
, charge_account_seg6
, charge_account_seg7
, charge_account_seg8
, charge_account_seg9
, quantity_ordered
FROM xx_po_dist_stg
WHERE po_number = p_header_num
AND line_num = p_line_num ;
-- Cursor to select PO Headers data from Pre-Interface table
CURSOR c_po_hdr_preint(p_org_id IN NUMBER) IS
SELECT *
FROM xx_po_headers_pre_interface
WHERE status = 'IP'
AND org_id = p_org_id;
-- Cursor to select PO Lines data from Pre-Interface table
CURSOR c_po_line_preint(p_header_id IN NUMBER) IS
SELECT *
FROM xx_po_lines_pre_interface
WHERE interface_header_id = p_header_id;
-- Cursor to select PO Distributions data from Pre-Interface table
CURSOR c_po_dist_preint( p_header_id IN NUMBER
,p_line_id IN NUMBER
) IS
SELECT *
FROM xx_po_dist_pre_interface
WHERE interface_header_id = p_header_id
AND interface_line_id = p_line_id ;
-- Cursor to Insert PO Distributions data into Interface table
CURSOR c_po_dist_int ( p_header_id IN NUMBER) IS
SELECT *
FROM xx_po_dist_pre_interface
WHERE interface_header_id = p_header_id;
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
-- General Variables
--
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0;
l_org_id org_organization_definitions.organization_id%TYPE;
l_org_code org_organization_definitions.organization_code%TYPE;
l_int_err_flag VARCHAR2(10):='N';
l_po_exists VARCHAR2(10):='N';
l_emp_id NUMBER := NULL;
l_process_status NUMBER;
l_error_message VARCHAR2(1000);
l_curr_exist VARCHAR2(10) := NULL;
l_vendor_error_flag VARCHAR2(10) :=NULL;
l_ship_err_flag VARCHAR2(10) :=NULL;
l_bill_err_flag VARCHAR2(10) :=NULL;
l_count NUMBER :=0;
l_completed BOOLEAN;
l_ccid_exists VARCHAR2(10):='N';
l_ccid NUMBER;
l_sob_err_flag VARCHAR2(10):='N';
l_valid_line VARCHAR2(10):='N';
l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_value VARCHAR2(20);
l_dist_sum NUMBER :=0;
l_line_count NUMBER :=0;
l_doc_type VARCHAR2(30):=NULL;
v_record_cnt NUMBER:=0;
v_file_hdr_count NUMBER:=0;
v_file_line_count NUMBER:=0;
v_file_dist_count NUMBER:=0;
v_int_header_id NUMBER;
v_interface_line_id NUMBER;
l_inv_planning_code PLS_INTEGER:=0;
--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE:= 'XX_POCNV02_PO_IMPORT_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE:= l_package_name||'.'|| 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE:= 'PO_CNV_02';--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE:= NULL;--used for EMF messages
l_return_value NUMBER:= NULL;--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;--record type variable for assigning width to error section
l_processed_recs NUMBER:= 0;--stores total records for summary report
l_successful_recs NUMBER:= 0;--stores total successful records for summary report
l_error_recs NUMBER:= 0;--stores total error records for the summary report
l_proc_stg_recs NUMBER:= 0;
l_err_stg_recs NUMBER:= 0;
l_succ_stg_recs NUMBER:= 0;
l_status xx_emf_message_headers.status%TYPE;--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1):= 'N';
l_error_cat_flag VARCHAR2 (1):= 'N';--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);--stores the message returned from external routines
--
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
--
-- Common Validations Variables
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_cntrl_fail EXCEPTION;
e_control_record_error EXCEPTION;
--
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
l_org_id := fnd_profile.VALUE ('ORG_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 := 'PO Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Operating Unit'; --Fifth Error Header
l_error_rec.identifier6 := 'Line Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Shipment Number'; --Seventh Error Header
l_error_rec.identifier8 := 'Distribution Number'; --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;
--
BEGIN
SELECT organization_code
INTO l_org_code
FROM org_organization_definitions
WHERE organization_id = l_org_id ;
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Error fetching the OU from profile');
END;
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
IF p_run_mode = 'P' THEN
-- Update the new request id in the pre interface record --
BEGIN
UPDATE xx_po_headers_pre_interface
SET request_id =l_request_id
,last_update_date =SYSDATE
,last_updated_by = l_user_id
WHERE org_id = l_org_id
AND status = 'IP';
EXCEPTION
WHEN OTHERS THEN
xx_trace.l('Error updating Pre interface record status');
END;
END IF;
IF p_run_mode ='F' THEN
BEGIN
SELECT recordcnt
INTO v_record_cnt
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type ='C';
SELECT Count(*)
INTO v_file_hdr_count
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C';
SELECT Count(*)
INTO v_file_line_count
FROM xx_po_load_line_stg
WHERE po_number IN (
SELECT po_number
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C'
);
SELECT Count(*)
INTO v_file_dist_count
FROM xx_po_dist_stg
WHERE po_number IN (
SELECT po_number
FROM xx_po_load_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C'
);
IF v_record_cnt <> v_file_hdr_count + v_file_line_count + v_file_dist_count THEN
UPDATE xx_po_load_stg
SET status = 'ER'
WHERE status = 'NW'
AND org_code = l_org_code;
COMMIT ;
-- Raise record count error --
xx_trace.l ('Record count mismatch in the data file');
--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-11111',
p_identifier2 => 'Record count mismatch in the data file.',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
--p_identifier7 => NULL,
--p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_control_record_error;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l ('No Control Record in Data File');
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-22222',
p_identifier2 => 'No Control Record in Data File',
p_identifier3 => NULL,
p_identifier4 => NULL,
p_identifier5 => NULL,
p_identifier6 => NULL,
--p_identifier7 => NULL,
--p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_control_record_error;
WHEN e_control_record_error THEN
xx_trace.l('Exiting the program..');
RAISE;
WHEN OTHERS THEN
RAISE;
END;
-- Control level validations --
BEGIN --
FOR c_po_stg_header_rec IN c_po_stg_header(l_org_code)
LOOP
l_error_flag := 'N';
l_proc_stg_recs := l_proc_stg_recs + 1;
IF c_po_stg_header_rec.po_number IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('PO Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Number cannot be Null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.document_type_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Document type code cannot be null');
--Write to Error
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00002'
, p_identifier2 => 'Document type code cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.vendor_name IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Vendor 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-00003'
, p_identifier2 => 'Vendor Name cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.vendor_site_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Vendor Site 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-00004'
, p_identifier2 => 'Vendor site cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.currency_code IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Currency Code cannot be null');
--Write to Error
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00005'
, p_identifier2 => 'Currency code cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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_po_stg_header_rec.agent_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Agent Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00006'
, p_identifier2 => 'Agent Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- 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;
-- Line Level Control Validations --
l_line_count := 0; -- Intialising Line Counts --
FOR c_po_stg_line_rec IN c_po_stg_line (c_po_stg_header_rec.po_number)
LOOP
l_line_count := l_line_count + 1;
IF c_po_stg_line_rec.line_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Line Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00007'
, p_identifier2 => 'Line Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.item IS NULL AND c_po_stg_line_rec.item_description IS NULL )THEN
l_error_flag := 'Y';
xx_trace.l ('Both Item and Item Description 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-00008'
, p_identifier2 => 'Both Item and Item Description cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.quantity IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('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-00009'
, p_identifier2 => 'Quantity cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.unit_price IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Unit 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-00010'
, p_identifier2 => 'Unit Price cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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_po_stg_line_rec.line_type IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type 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-00011'
, p_identifier2 => 'Line Type cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- p_identifier7 => NULL -- Can be utilized for displaying more information
-- p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- Check if Line and Distribution Quantity Totals are in sync --
BEGIN
SELECT Nvl(Sum(quantity_ordered),0)
INTO l_dist_sum
FROM xx_po_dist_stg
WHERE po_number = c_po_stg_header_rec.po_number
AND line_num = c_po_stg_line_rec.line_num ;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.h ('Error occured while fetching quantity from Distribution'||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-00012'
, p_identifier2 => 'Error occured while fetching quantity from Distribution'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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 c_po_stg_line_rec.quantity <> l_dist_sum AND c_po_stg_header_rec.po_status = 'APPROVED' THEN
l_error_flag := 'Y';
xx_trace.l ('Line and Distribution Totals do not match');
--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-00013'
, p_identifier2 => 'Line and Distribution Totals do not match'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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
);
ELSIF c_po_stg_line_rec.quantity < l_dist_sum AND c_po_stg_header_rec.po_status <> 'APPROVED' THEN
l_error_flag := 'Y';
xx_trace.l ('Line and Distribution Totals do not match');
--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-00014'
, p_identifier2 => 'Line and Distribution Totals do not match'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Distribution Level Validations --
FOR c_po_dist_stg_rec IN c_po_dist_stg(c_po_stg_header_rec.po_number,
c_po_stg_line_rec.line_num
)
LOOP
IF c_po_dist_stg_rec.distribution_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Distribution Number cannot be null');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00015'
, p_identifier2 => 'Distribution Number be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_po_dist_stg_rec.charge_account_seg1 IS NULL OR
c_po_dist_stg_rec.charge_account_seg2 IS NULL OR
c_po_dist_stg_rec.charge_account_seg3 IS NULL OR
c_po_dist_stg_rec.charge_account_seg4 IS NULL OR
c_po_dist_stg_rec.charge_account_seg5 IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Charge Account Segment 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-00016'
, p_identifier2 => 'Charge Account Segment cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_po_dist_stg_rec.quantity_ordered IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Quantity Ordered 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-00017'
, p_identifier2 => 'Quantity Ordered cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
, p_identifier6 => c_po_stg_line_rec.line_num
, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END LOOP; -- End of Distribution level validation
END LOOP ; -- End of Line Level validations
-- Check for PO's with no line records --
IF l_line_count = 0 THEN
l_error_flag := 'Y';
xx_trace.l ('PO has no lines');
--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-00018'
, p_identifier2 => 'PO has no lines'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_stg_header_rec.po_number
, p_identifier5 => c_po_stg_header_rec.org_code
-- p_identifier6 => NULL -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
--p_identifier8 => NULL -- Can be utilized for displaying more information --
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- If Control level validation fails update the staging table status field to'ER' --
IF l_error_flag = 'Y' THEN
BEGIN
UPDATE xx_po_load_stg
SET status = 'ER'
WHERE stg_header_id = c_po_stg_header_rec.stg_header_id;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.l('Exception While Updating Header Staging Table Oracle Error :'
|| SQLERRM
);
END;
ELSE
-- If no errors in Control level validations insert into pre interface tables --
BEGIN
-- Insert into Header level pre interface --
INSERT INTO xx_po_headers_pre_interface (
interface_header_id
,interface_source_code
,action
,org_id
,document_type_code
,document_num
,currency_code
,agent_name
,vendor_name
,vendor_site_code
,ship_to_location
,bill_to_location
,status
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,approval_status
)
VALUES
(
c_po_stg_header_rec.stg_header_id
,c_po_stg_header_rec.interface_source_code
,'ORIGINAL'
,l_org_id
,c_po_stg_header_rec.document_type_code
,c_po_stg_header_rec.po_number
,c_po_stg_header_rec.currency_code
,c_po_stg_header_rec.agent_num
,c_po_stg_header_rec.vendor_name
,c_po_stg_header_rec.vendor_site_code
,c_po_stg_header_rec.ship_to_location
,c_po_stg_header_rec.bill_to_location
,'IP'
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,Nvl(c_po_stg_header_rec.po_status,'INCOMPLETE')
);
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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 => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00019'
, p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
BEGIN
-- Insert into Line level pre interface --
INSERT INTO xx_po_lines_pre_interface (
interface_header_id
,interface_line_id
,document_num
,line_num
,shipment_num
,line_type
,item
,item_description
,unit_of_measure
,ship_to_location
,quantity
,unit_price
,promised_date
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
SELECT c_po_stg_header_rec.stg_header_id
,stg_po_line_id
,po_number
,line_num
,shipment_num
,line_type
,item
,item_description
,unit_of_measure
,ship_to_location
,quantity
,unit_price
,promised_date
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
FROM xx_po_load_line_stg
WHERE po_number =c_po_stg_header_rec.po_number;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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 => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00020'
, p_identifier2 => 'Exception While Inserting Into Line Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
BEGIN
-- Insert into Distribution level pre interface --
INSERT INTO xx_po_dist_pre_interface (
interface_header_id
,interface_line_id
,interface_distribution_id
,distribution_num
,org_id
,quantity_ordered
,set_of_books
,charge_account_segment1
,charge_account_segment2
,charge_account_segment3
,charge_account_segment4
,charge_account_segment5
,charge_account_segment6
,charge_account_segment7
,charge_account_segment8
,charge_account_segment9
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
SELECT c_po_stg_header_rec.stg_header_id
,stg_po_line_id
,stg_distribution_id
,DISTRIBUTION_NUM
,l_org_id
,quantity_ordered
,set_of_books_name
,charge_account_seg1
,charge_account_seg2
,charge_account_seg3
,charge_account_seg4
,charge_account_seg5
,charge_account_seg6
,charge_account_seg7
,charge_account_seg8
,charge_account_seg9
,l_request_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
FROM xx_po_load_line_stg xpls
,xx_po_dist_stg xpds
WHERE xpls.po_number = c_po_stg_header_rec.po_number
AND xpls.line_num = xpds.line_num
AND xpls.po_number= xpds.po_number;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO Distribution Pre-Interface table. Oracle Error:'
|| SQLERRM);
xx_emf.call_store_message(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00021'
, p_identifier2 => 'Exception While Inserting Into Distribution Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_stg_header_rec.po_number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE;
END;
l_succ_stg_recs := l_succ_stg_recs + 1;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
xx_trace.h ('Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00022'
, p_identifier2 => 'Error in Control Validation.'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => c_item_rec.item_number
-- ,p_identifier5 => c_item_rec.description
-- ,p_identifier6 => c_item_rec.organization_code
--p_identifier7 => NULL -- Can be utilized for displaying more information
--p_identifier8 => NULL -- Can be utilized for displaying more information
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF; --1st Begin
--END of Control Level Validation --
l_err_stg_recs := l_proc_stg_recs - l_succ_stg_recs;
/*----------------------------------------------------------------------------
--Inserting Summary Counts for Staging Table Records
----------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name||': Staging'
, p_total_recs => l_proc_stg_recs
, p_successful_recs => l_succ_stg_recs
, p_error_recs => l_err_stg_recs
);
IF l_return_value = 1 THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN
FOR c_po_hdr_preint_rec IN c_po_hdr_preint(l_org_id)
LOOP
l_int_err_flag := 'N' ;
l_processed_recs := l_processed_recs + 1;
-- Check for Duplicate PO --
BEGIN
SELECT 'Y'
INTO l_po_exists
FROM po_headers_all
WHERE type_lookup_code = c_po_hdr_preint_rec.document_type_code
AND segment1= c_po_hdr_preint_rec.document_num;
IF l_po_exists = 'Y' THEN
l_int_err_flag := 'Y';
xx_trace.l ('Duplicate PO: This PO exists in the system');
--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-00023'
, p_identifier2 => 'Duplicate PO: This PO exists in the system'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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 NO_DATA_FOUND THEN
l_int_err_flag :='N';
l_po_exists :='N';
END;
-- Validate the Buyer --
BEGIN
SELECT employee_id
INTO l_emp_id
FROM po_buyers_val_v
WHERE employee_num = c_po_hdr_preint_rec.agent_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_emp_id :=NULL;
xx_trace.l ('Not a valid buyer');
--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-00024'
, p_identifier2 => 'Not a valid buyer'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate the Document Type --
BEGIN
SELECT document_type_code
INTO l_doc_type
FROM po_document_types_all
WHERE document_type_code = 'PO'
AND document_subtype = c_po_hdr_preint_rec.document_type_code
AND org_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_doc_type :=NULL;
xx_trace.l ('Not a valid Docuement Type');
--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-00025'
, p_identifier2 => 'Not a valid Docuement Type'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate the currency code --
l_curr_exist := xx_common_validations_pkg.validate_currency_code
(
p_currency_code => c_po_hdr_preint_rec.currency_code
,p_process_status => l_process_status
,p_error_message => l_error_message
);
IF l_process_status = 1 THEN
l_int_err_flag :='Y';
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-00026'
,p_identifier2 => 'Invalid Currency Code: '||c_po_hdr_preint_rec.currency_code
,p_identifier3 => NULL -- Can be utilized to store record serial number
,p_identifier4 => c_po_hdr_preint_rec.document_num
,p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
-- 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;
-- Validate vendor and Vendor Site --
validate_vendor (
p_po_hdr_preint_rec => c_po_hdr_preint_rec
,p_error_flag => l_vendor_error_flag
);
-- Validate Ship to Location --
IF c_po_hdr_preint_rec.ship_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'SHIP_TO'
,p_po_num => c_po_hdr_preint_rec.document_num
,p_loc => c_po_hdr_preint_rec.ship_to_location
,p_error_flag => l_ship_err_flag
);
END IF;
-- Validate Bill to location --
IF c_po_hdr_preint_rec.bill_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'BILL_TO'
,p_po_num => c_po_hdr_preint_rec.document_num
,p_loc => c_po_hdr_preint_rec.bill_to_location
,p_error_flag => l_bill_err_flag
);
END IF;
IF l_vendor_error_flag = 'Y' OR l_ship_err_flag ='Y' OR l_bill_err_flag ='Y' THEN
l_int_err_flag :='Y';
END IF;
IF l_int_err_flag = 'N' THEN
-- Line Level Validations --
FOR c_po_line_preint_rec IN c_po_line_preint( c_po_hdr_preint_rec.interface_header_id)
LOOP
BEGIN
SELECT 'Y'
INTO l_valid_line
FROM po_line_types
WHERE UPPER(line_type) = UPPER(c_po_line_preint_rec.line_type);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
l_valid_line:='N';
xx_trace.l ('Not a valid Line Type');
--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-00027'
, p_identifier2 => 'Not a valid Line Type'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF c_po_line_preint_rec.unit_of_measure IS NOT NULL THEN
-- Validate uom_code --
l_process_status := 0;
l_value := NULL;
xx_common_validations_pkg.validate_uom
( p_unit_of_measure => c_po_line_preint_rec.unit_of_measure
, p_uom_code => l_value
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_process_status = 1 THEN
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00028'
, p_identifier2 => 'Error in validating UOM'|| l_output_message
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag :='Y';
END IF;
END IF;
IF c_po_line_preint_rec.ship_to_location IS NOT NULL THEN
validate_ship_bill_loc (
p_loc_type => 'SHIP_TO'
,p_po_num => c_po_line_preint_rec.document_num
,p_loc => c_po_line_preint_rec.ship_to_location
,p_error_flag => l_ship_err_flag
);
END IF;
IF l_ship_err_flag ='Y' THEN
l_int_err_flag :='Y';
END IF;
IF c_po_line_preint_rec.item IS NOT NULL THEN
BEGIN
SELECT inventory_item_id,
inventory_planning_code
INTO l_inventory_item_id
,l_inv_planning_code
FROM mtl_system_items_b
WHERE segment1 = c_po_line_preint_rec.item
AND purchasing_enabled_flag ='Y'
AND inventory_item_status_code ='Active'
AND organization_id = (
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = l_org_id
AND ROWNUM <2 );
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_int_err_flag :='Y';
xx_trace.l ('Invalid Item');
--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-00029'
, p_identifier2 => 'Invalid Item '||c_po_line_preint_rec.item
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
IF c_po_hdr_preint_rec.approval_status = 'APPROVED' THEN
IF l_inv_planning_code =6 AND (c_po_line_preint_rec.promised_date IS NULL OR Trunc(c_po_line_preint_rec.promised_date) < Trunc(SYSDATE) ) THEN
l_int_err_flag :='Y';
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-00030'
,p_identifier2 => 'Promise date is null or invalid Promise Date : '
,p_identifier3 => NULL -- Can be utilized to store record serial number
,p_identifier4 => c_po_hdr_preint_rec.document_num
,p_identifier5 => l_org_code
,p_identifier6 => c_po_line_preint_rec.line_num
-- 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 IF;
END IF;
IF l_int_err_flag = 'N' THEN
-- Distribution Level Validations --
FOR c_po_dist_preint_rec IN c_po_dist_preint( c_po_line_preint_rec.interface_header_id
,c_po_line_preint_rec.interface_line_id )
LOOP
l_ccid_exists:=xx_common_validations_pkg.get_coa_ccid
( p_segment1 => c_po_dist_preint_rec.charge_account_segment1
, p_segment2 => c_po_dist_preint_rec.charge_account_segment2
, p_segment3 => c_po_dist_preint_rec.charge_account_segment3
, p_segment4 => c_po_dist_preint_rec.charge_account_segment4
, p_segment5 => c_po_dist_preint_rec.charge_account_segment5
, p_process_status => l_process_status
, p_error_message => l_output_message
, p_ccid => l_ccid
);
IF l_process_status = 1 THEN
xx_emf.call_store_message
( p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00030'
, p_identifier2 => 'Error in validating Account'|| l_output_message
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
, p_identifier6 => c_po_line_preint_rec.line_num
, p_identifier7 => c_po_line_preint_rec.shipment_num
, p_identifier8 => c_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag :='Y';
END IF;
validate_set_of_books (
p_po_dist_preint_rec => c_po_dist_preint_rec,
p_po_num => c_po_hdr_preint_rec.document_num,
p_error_flag => l_sob_err_flag
);
IF l_sob_err_flag = 'Y' THEN
l_int_err_flag :='Y';
END IF;
END LOOP ; -- End of Distribution Level Business Validations --
END IF;
END LOOP; --End of Line Level Business Validations --
END IF;
IF l_int_err_flag ='N' THEN
l_successful_recs := l_successful_recs + 1;
BEGIN
-- Successful records are inserted in the Interface Table --
SELECT po_headers_interface_s.NEXTVAL
INTO v_int_header_id
FROM dual ;
INSERT INTO po_headers_interface(
interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
group_code,
org_id,
document_type_code,
document_subtype,
document_num,
po_header_id,
release_num,
po_release_id,
release_date,
currency_code,
rate_type,
rate_type_code,
rate_date,
rate,
agent_name,
agent_id,
vendor_name,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact,
vendor_contact_id,
ship_to_location,
ship_to_location_id,
bill_to_location,
bill_to_location_id,
payment_terms,
terms_id,
freight_carrier,
fob,
freight_terms,
approval_status,
approved_date,
revised_date,
revision_num,
note_to_vendor,
note_to_receiver,
confirming_order_flag,
comments,
acceptance_required_flag,
acceptance_due_date,
amount_agreed,
amount_limit,
min_release_amount,
effective_date,
expiration_date,
print_count,
printed_date,
firm_flag,
frozen_flag,
closed_code,
closed_date,
reply_date,
reply_method,
rfq_close_date,
quote_warning_delay,
vendor_doc_num,
approval_required_flag,
vendor_list,
vendor_list_header_id,
from_header_id,
from_type_lookup_code,
ussgl_transaction_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
reference_num,
load_sourcing_rules_flag,
vendor_num,
from_rfq_num,
wf_group_id,
pcard_id,
pay_on_code,
global_agreement_flag,
consume_req_demand_flag,
shipping_control,
encumbrance_required_flag,
amount_to_encumber,
change_summary,
budget_account_segment1,
budget_account_segment2,
budget_account_segment3,
budget_account_segment4,
budget_account_segment5,
budget_account_segment6,
budget_account_segment7,
budget_account_segment8,
budget_account_segment9,
budget_account,
budget_account_id,
gl_encumbered_date,
gl_encumbered_period_name
)
VALUES (
v_int_header_id,
c_po_hdr_preint_rec.batch_id,
c_po_hdr_preint_rec.interface_source_code,
c_po_hdr_preint_rec.process_code,
c_po_hdr_preint_rec.action,
c_po_hdr_preint_rec.group_code,
c_po_hdr_preint_rec.org_id,
c_po_hdr_preint_rec.document_type_code,
c_po_hdr_preint_rec.document_subtype,
c_po_hdr_preint_rec.document_num,
c_po_hdr_preint_rec.po_header_id,
c_po_hdr_preint_rec.release_num,
c_po_hdr_preint_rec.po_release_id,
c_po_hdr_preint_rec.release_date,
c_po_hdr_preint_rec.currency_code,
c_po_hdr_preint_rec.rate_type,
c_po_hdr_preint_rec.rate_type_code,
c_po_hdr_preint_rec.rate_date,
c_po_hdr_preint_rec.rate,
NULL,
l_emp_id,
c_po_hdr_preint_rec.vendor_name,
c_po_hdr_preint_rec.vendor_id,
c_po_hdr_preint_rec.vendor_site_code,
c_po_hdr_preint_rec.vendor_site_id,
c_po_hdr_preint_rec.vendor_contact,
c_po_hdr_preint_rec.vendor_contact_id,
c_po_hdr_preint_rec.ship_to_location,
c_po_hdr_preint_rec.ship_to_location_id,
c_po_hdr_preint_rec.bill_to_location,
c_po_hdr_preint_rec.bill_to_location_id,
c_po_hdr_preint_rec.payment_terms,
c_po_hdr_preint_rec.terms_id,
c_po_hdr_preint_rec.freight_carrier,
c_po_hdr_preint_rec.fob,
c_po_hdr_preint_rec.freight_terms,
c_po_hdr_preint_rec.approval_status,
c_po_hdr_preint_rec.approved_date,
c_po_hdr_preint_rec.revised_date,
c_po_hdr_preint_rec.revision_num,
c_po_hdr_preint_rec.note_to_vendor,
c_po_hdr_preint_rec.note_to_receiver,
c_po_hdr_preint_rec.confirming_order_flag,
c_po_hdr_preint_rec.comments,
c_po_hdr_preint_rec.acceptance_required_flag,
c_po_hdr_preint_rec.acceptance_due_date,
c_po_hdr_preint_rec.amount_agreed,
c_po_hdr_preint_rec.amount_limit,
c_po_hdr_preint_rec.min_release_amount,
c_po_hdr_preint_rec.effective_date,
c_po_hdr_preint_rec.expiration_date,
c_po_hdr_preint_rec.print_count,
c_po_hdr_preint_rec.printed_date,
c_po_hdr_preint_rec.firm_flag,
c_po_hdr_preint_rec.frozen_flag,
c_po_hdr_preint_rec.closed_code,
c_po_hdr_preint_rec.closed_date,
c_po_hdr_preint_rec.reply_date,
c_po_hdr_preint_rec.reply_method,
c_po_hdr_preint_rec.rfq_close_date,
c_po_hdr_preint_rec.quote_warning_delay,
c_po_hdr_preint_rec.vendor_doc_num,
c_po_hdr_preint_rec.approval_required_flag,
c_po_hdr_preint_rec.vendor_list,
c_po_hdr_preint_rec.vendor_list_header_id,
c_po_hdr_preint_rec.from_header_id,
c_po_hdr_preint_rec.from_type_lookup_code,
c_po_hdr_preint_rec.ussgl_transaction_code,
c_po_hdr_preint_rec.attribute_category,
c_po_hdr_preint_rec.attribute1,
c_po_hdr_preint_rec.attribute2,
c_po_hdr_preint_rec.attribute3,
c_po_hdr_preint_rec.attribute4,
c_po_hdr_preint_rec.attribute5,
c_po_hdr_preint_rec.attribute6,
c_po_hdr_preint_rec.attribute7,
c_po_hdr_preint_rec.attribute8,
c_po_hdr_preint_rec.attribute9,
c_po_hdr_preint_rec.attribute10,
c_po_hdr_preint_rec.attribute11,
c_po_hdr_preint_rec.attribute12,
c_po_hdr_preint_rec.attribute13,
c_po_hdr_preint_rec.attribute14,
c_po_hdr_preint_rec.attribute15,
sysdate,
l_user_id,
sysdate,
l_user_id,
c_po_hdr_preint_rec.last_update_login,
c_po_hdr_preint_rec.request_id,
c_po_hdr_preint_rec.program_application_id,
c_po_hdr_preint_rec.program_id,
c_po_hdr_preint_rec.program_update_date,
c_po_hdr_preint_rec.reference_num,
c_po_hdr_preint_rec.load_sourcing_rules_flag,
c_po_hdr_preint_rec.vendor_num,
c_po_hdr_preint_rec.from_rfq_num,
c_po_hdr_preint_rec.wf_group_id,
c_po_hdr_preint_rec.pcard_id,
c_po_hdr_preint_rec.pay_on_code,
c_po_hdr_preint_rec.global_agreement_flag,
c_po_hdr_preint_rec.consume_req_demand_flag,
c_po_hdr_preint_rec.shipping_control,
c_po_hdr_preint_rec.encumbrance_required_flag,
c_po_hdr_preint_rec.amount_to_encumber,
c_po_hdr_preint_rec.change_summary,
c_po_hdr_preint_rec.budget_account_segment1,
c_po_hdr_preint_rec.budget_account_segment2,
c_po_hdr_preint_rec.budget_account_segment3,
c_po_hdr_preint_rec.budget_account_segment4,
c_po_hdr_preint_rec.budget_account_segment5,
c_po_hdr_preint_rec.budget_account_segment6,
c_po_hdr_preint_rec.budget_account_segment7,
c_po_hdr_preint_rec.budget_account_segment8,
c_po_hdr_preint_rec.budget_account_segment9,
c_po_hdr_preint_rec.budget_account,
c_po_hdr_preint_rec.budget_account_id,
c_po_hdr_preint_rec.gl_encumbered_date,
c_po_hdr_preint_rec.gl_encumbered_period_name
);
INSERT INTO po_lines_interface (
interface_line_id,
interface_header_id,
action,
group_code,
line_num,
po_line_id,
shipment_num,
line_location_id,
shipment_type,
requisition_line_id,
document_num,
release_num,
po_header_id,
po_release_id,
source_shipment_id,
contract_num,
line_type,
line_type_id,
item, item_id,
item_revision,
CATEGORY,
category_id,
item_description,
vendor_product_num,
uom_code,
unit_of_measure,
quantity,
committed_amount,
min_order_quantity,
max_order_quantity,
unit_price,
list_price_per_unit,
market_price,
allow_price_override_flag,
not_to_exceed_price,
negotiated_by_preparer_flag,
un_number,
un_number_id,
hazard_class,
hazard_class_id,
note_to_vendor,
transaction_reason_code,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
inspection_required_flag,
receipt_required_flag,
payment_terms,
terms_id,
price_type,
min_release_amount,
price_break_lookup_code,
ussgl_transaction_code,
closed_code,
closed_reason,
closed_date,
closed_by,
invoice_close_tolearnce,
receive_close_tolerance,
firm_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing,
receiving_routing_id,
qty_rcv_tolerance,
over_tolerance_error_flag,
qty_rcv_exception_code,
receipt_days_exception_code,
ship_to_organization_code,
ship_to_organization_id,
ship_to_location,
ship_to_location_id,
need_by_date,
promised_date,
accrue_on_receipt_flag,
lead_time,
lead_time_unit,
price_discount,
freight_carrier,
fob, freight_terms,
effective_date,
expiration_date,
from_header_id,
from_line_id,
from_line_location_id,
line_attribute_category_lines,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
shipment_attribute_category,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
shipment_attribute7,
shipment_attribute8,
shipment_attribute9,
shipment_attribute10,
shipment_attribute11,
shipment_attribute12,
shipment_attribute13,
shipment_attribute14,
shipment_attribute15,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
invoice_close_tolerance,
organization_id,
item_attribute_category,
item_attribute1,
item_attribute2,
item_attribute3,
item_attribute4,
item_attribute5,
item_attribute6,
item_attribute7,
item_attribute8,
item_attribute9,
item_attribute10,
item_attribute11,
item_attribute12,
item_attribute13,
item_attribute14,
item_attribute15,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
template_id,
template_name,
line_reference_num,
sourcing_rule_name,
tax_status_indicator,
process_code,
price_chg_accept_flag,
price_break_flag,
price_update_tolerance,
tax_user_override_flag,
tax_code_id,
note_to_receiver,
oke_contract_header_id,
oke_contract_header_num,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_uom_code,
secondary_quantity,
preferred_grade,
vmi_flag,
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
orig_from_req_flag,
consigned_flag,
supplier_ref_number,
contract_id,
job_id,
amount,
job_name,
contractor_first_name,
contractor_last_name,
drop_ship_flag,
base_unit_price,
transaction_flow_header_id,
job_business_group_id,
job_business_group_name
)
SELECT po_lines_interface_s.NEXTVAL,
v_int_header_id,
action,
group_code,
line_num,
po_line_id,
shipment_num,
line_location_id,
shipment_type,
requisition_line_id,
document_num,
release_num,
po_header_id,
po_release_id,
source_shipment_id,
contract_num,
line_type,
line_type_id,
item,
item_id,
item_revision,
CATEGORY,
category_id,
item_description,
vendor_product_num,
uom_code,
unit_of_measure,
quantity,
committed_amount,
min_order_quantity,
max_order_quantity,
unit_price,
list_price_per_unit,
market_price,
allow_price_override_flag,
not_to_exceed_price,
negotiated_by_preparer_flag,
un_number,
un_number_id,
hazard_class,
hazard_class_id,
note_to_vendor,
transaction_reason_code,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
inspection_required_flag,
receipt_required_flag,
payment_terms,
terms_id,
price_type,
min_release_amount,
price_break_lookup_code,
ussgl_transaction_code,
closed_code,
closed_reason,
closed_date,
closed_by,
invoice_close_tolearnce,
receive_close_tolerance,
firm_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing,
receiving_routing_id,
qty_rcv_tolerance,
over_tolerance_error_flag,
qty_rcv_exception_code,
receipt_days_exception_code,
ship_to_organization_code,
ship_to_organization_id,
ship_to_location,
ship_to_location_id,
need_by_date,
promised_date,
accrue_on_receipt_flag,
lead_time,
lead_time_unit,
price_discount,
freight_carrier,
fob,
freight_terms,
effective_date,
expiration_date,
from_header_id,
from_line_id,
from_line_location_id,
line_attribute_category_lines,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
shipment_attribute_category,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
shipment_attribute7,
shipment_attribute8,
shipment_attribute9,
shipment_attribute10,
shipment_attribute11,
shipment_attribute12,
shipment_attribute13,
shipment_attribute14,
shipment_attribute15,
sysdate,
l_user_id,
last_update_login,
sysdate,
l_user_id,
request_id,
program_application_id,
program_id,
program_update_date,
invoice_close_tolerance,
organization_id,
item_attribute_category,
item_attribute1,
item_attribute2,
item_attribute3,
item_attribute4,
item_attribute5,
item_attribute6,
item_attribute7,
item_attribute8,
item_attribute9,
item_attribute10,
item_attribute11,
item_attribute12,
item_attribute13,
item_attribute14,
item_attribute15,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
template_id,
template_name,
line_reference_num,
sourcing_rule_name,
tax_status_indicator,
process_code,
price_chg_accept_flag,
price_break_flag,
price_update_tolerance,
tax_user_override_flag,
tax_code_id,
note_to_receiver,
oke_contract_header_id,
oke_contract_header_num,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_uom_code,
secondary_quantity,
preferred_grade,
vmi_flag,
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
orig_from_req_flag,
consigned_flag,
supplier_ref_number,
contract_id,
job_id,
amount,
job_name,
contractor_first_name,
contractor_last_name,
drop_ship_flag,
base_unit_price,
transaction_flow_header_id,
job_business_group_id,
job_business_group_name
FROM xx_po_lines_pre_interface
WHERE interface_header_id = c_po_hdr_preint_rec.interface_header_id;
-- Insert into PO Distributions Interface --
FOR c_po_dist_rec IN c_po_dist_int (c_po_hdr_preint_rec.interface_header_id)
LOOP
BEGIN
SELECT poli.interface_line_id
INTO v_interface_line_id
FROM po_lines_interface poli
,xx_po_lines_pre_interface xpl
WHERE poli.interface_header_id = v_int_header_id
AND xpl.interface_header_id = c_po_hdr_preint_rec.interface_header_id
AND xpl.interface_line_id = c_po_dist_rec.interface_line_id
AND poli.line_num =xpl.line_num
AND ROWNUM < 2;
INSERT INTO po_distributions_interface (
interface_header_id,
interface_line_id,
interface_distribution_id,
po_header_id,
po_release_id,
po_line_id,
line_location_id,
po_distribution_id,
distribution_num,
source_distribution_id,
org_id,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
rate_date,
rate,
deliver_to_location,
deliver_to_location_id,
deliver_to_person_full_name,
deliver_to_person_id,
destination_type,
destination_type_code,
destination_organization,
destination_organization_id,
destination_subinventory,
destination_context,
set_of_books,
set_of_books_id,
charge_account,
charge_account_id,
budget_account,
budget_account_id,
accural_account,
accrual_account_id,
variance_account,
variance_account_id,
amount_billed,
accrue_on_receipt_flag,
accrued_flag,
prevent_encumbrance_flag,
encumbered_flag,
encumbered_amount,
unencumbered_quantity,
unencumbered_amount,
failed_funds,
failed_funds_lookup_code,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
gl_closed_date,
req_header_reference_num,
req_line_reference_num,
req_distribution_id,
wip_entity,
wip_entity_id,
wip_operation_seq_num,
wip_resource_seq_num,
wip_repetitive_schedule,
wip_repetitive_schedule_id,
wip_line_code,
wip_line_id,
bom_resource_code,
bom_resource_id,
ussgl_transaction_code,
government_context,
project,
project_id,
task,
task_id,
expenditure,
expenditure_type,
project_accounting_context,
expenditure_organization,
expenditure_organization_id,
project_releated_flag,
expenditure_item_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
end_item_unit_number,
recoverable_tax,
nonrecoverable_tax,
recovery_rate,
tax_recovery_override_flag,
award_id,
charge_account_segment1,
charge_account_segment2,
charge_account_segment3,
charge_account_segment4,
charge_account_segment5,
charge_account_segment6,
charge_account_segment7,
charge_account_segment8,
charge_account_segment9,
charge_account_segment10,
charge_account_segment11,
charge_account_segment12,
charge_account_segment13,
charge_account_segment14,
charge_account_segment15,
oke_contract_line_id,
oke_contract_line_num,
oke_contract_deliverable_id,
oke_contract_deliverable_num,
award_number, amount_ordered,
invoice_adjustment_flag,
dest_charge_account_id,
dest_variance_account_id
)
VALUES (
v_int_header_id
,v_interface_line_id
,po_distributions_interface_s.NEXTVAL
,c_po_dist_rec.po_header_id
,c_po_dist_rec.po_release_id
,c_po_dist_rec.po_line_id
,c_po_dist_rec.line_location_id
,c_po_dist_rec.po_distribution_id
,c_po_dist_rec.distribution_num
,c_po_dist_rec.source_distribution_id
,c_po_dist_rec.org_id
,c_po_dist_rec.quantity_ordered
,c_po_dist_rec.quantity_delivered
,c_po_dist_rec.quantity_billed
,c_po_dist_rec.quantity_cancelled
,c_po_dist_rec.rate_date
,c_po_dist_rec.rate
,c_po_dist_rec.deliver_to_location
,c_po_dist_rec.deliver_to_location_id
,c_po_dist_rec.deliver_to_person_full_name
,c_po_dist_rec.deliver_to_person_id
,c_po_dist_rec.destination_type
,c_po_dist_rec.destination_type_code
,c_po_dist_rec.destination_organization
,c_po_dist_rec.destination_organization_id
,c_po_dist_rec.destination_subinventory
,c_po_dist_rec.destination_context
,c_po_dist_rec.set_of_books
,c_po_dist_rec.set_of_books_id
,c_po_dist_rec.charge_account
,c_po_dist_rec.charge_account_id
,c_po_dist_rec.budget_account
,c_po_dist_rec.budget_account_id
,c_po_dist_rec.accural_account
,c_po_dist_rec.accrual_account_id
,c_po_dist_rec.variance_account
,c_po_dist_rec.variance_account_id
,c_po_dist_rec.amount_billed
,c_po_dist_rec.accrue_on_receipt_flag
,c_po_dist_rec.accrued_flag
,c_po_dist_rec.prevent_encumbrance_flag
,c_po_dist_rec.encumbered_flag
,c_po_dist_rec.encumbered_amount
,c_po_dist_rec.unencumbered_quantity
,c_po_dist_rec.unencumbered_amount
,c_po_dist_rec.failed_funds
,c_po_dist_rec.failed_funds_lookup_code
,c_po_dist_rec.gl_encumbered_date
,c_po_dist_rec.gl_encumbered_period_name
,c_po_dist_rec.gl_cancelled_date
,c_po_dist_rec.gl_closed_date
,c_po_dist_rec.req_header_reference_num
,c_po_dist_rec.req_line_reference_num
,c_po_dist_rec.req_distribution_id
,c_po_dist_rec.wip_entity
,c_po_dist_rec.wip_entity_id
,c_po_dist_rec.wip_operation_seq_num
,c_po_dist_rec.wip_resource_seq_num
,c_po_dist_rec.wip_repetitive_schedule
,c_po_dist_rec.wip_repetitive_schedule_id
,c_po_dist_rec.wip_line_code
,c_po_dist_rec.wip_line_id
,c_po_dist_rec.bom_resource_code
,c_po_dist_rec.bom_resource_id
,c_po_dist_rec.ussgl_transaction_code
,c_po_dist_rec.government_context
,c_po_dist_rec.project
,c_po_dist_rec.project_id
,c_po_dist_rec.task
,c_po_dist_rec.task_id
,c_po_dist_rec.expenditure
,c_po_dist_rec.expenditure_type
,c_po_dist_rec.project_accounting_context
,c_po_dist_rec.expenditure_organization
,c_po_dist_rec.expenditure_organization_id
,c_po_dist_rec.project_releated_flag
,c_po_dist_rec.expenditure_item_date
,c_po_dist_rec.attribute_category
,c_po_dist_rec.attribute1
,c_po_dist_rec.attribute2
,c_po_dist_rec.attribute3
,c_po_dist_rec.attribute4
,c_po_dist_rec.attribute5
,c_po_dist_rec.attribute6
,c_po_dist_rec.attribute7
,c_po_dist_rec.attribute8
,c_po_dist_rec.attribute9
,c_po_dist_rec.attribute10
,c_po_dist_rec.attribute11
,c_po_dist_rec.attribute12
,c_po_dist_rec.attribute13
,c_po_dist_rec.attribute14
,c_po_dist_rec.attribute15
,c_po_dist_rec.last_update_date
,c_po_dist_rec.last_updated_by
,c_po_dist_rec.last_update_login
,c_po_dist_rec.creation_date
,c_po_dist_rec.created_by
,c_po_dist_rec.request_id
,c_po_dist_rec.program_application_id
,c_po_dist_rec.program_id
,c_po_dist_rec.program_update_date
,c_po_dist_rec.end_item_unit_number
,c_po_dist_rec.recoverable_tax
,c_po_dist_rec.nonrecoverable_tax
,c_po_dist_rec.recovery_rate
,c_po_dist_rec.tax_recovery_override_flag
,c_po_dist_rec.award_id
,c_po_dist_rec.charge_account_segment1
,c_po_dist_rec.charge_account_segment2
,c_po_dist_rec.charge_account_segment3
,c_po_dist_rec.charge_account_segment4
,c_po_dist_rec.charge_account_segment5
,c_po_dist_rec.charge_account_segment6
,c_po_dist_rec.charge_account_segment7
,c_po_dist_rec.charge_account_segment8
,c_po_dist_rec.charge_account_segment9
,c_po_dist_rec.charge_account_segment10
,c_po_dist_rec.charge_account_segment11
,c_po_dist_rec.charge_account_segment12
,c_po_dist_rec.charge_account_segment13
,c_po_dist_rec.charge_account_segment14
,c_po_dist_rec.charge_account_segment15
,c_po_dist_rec.oke_contract_line_id
,c_po_dist_rec.oke_contract_line_num
,c_po_dist_rec.oke_contract_deliverable_id
,c_po_dist_rec.oke_contract_deliverable_num
,c_po_dist_rec.award_number
,c_po_dist_rec.amount_ordered
,c_po_dist_rec.invoice_adjustment_flag
,c_po_dist_rec.dest_charge_account_id
,c_po_dist_rec.dest_variance_account_id
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_trace.l('Error deriving the interface line');
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-00031'
, p_identifier2 => 'Error deriving the interface line-'
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => NULL
, p_identifier6 => NULL
, p_identifier7 => NULL
, p_identifier8 => c_po_dist_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO Dist 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-00032'
, p_identifier2 => 'Exception While Inserting Into PO Dist Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into PO 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-00033'
, p_identifier2 => 'Exception While Inserting Into PO Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';
END;
END IF;
IF l_int_err_flag ='Y' THEN
BEGIN
l_error_flag := l_int_err_flag;
UPDATE xx_po_headers_pre_interface
SET status = 'ER'
WHERE interface_header_id = c_po_hdr_preint_rec.interface_header_id
AND status ='IP';
EXCEPTION
WHEN OTHERS THEN
xx_trace.l ('Error updating Status of pre - interface');
--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-00034'
, p_identifier2 => 'Error updating Status of pre - interface'
, p_identifier3 => NULL
-- Can be utilized to store record serial number --
, p_identifier4 => c_po_hdr_preint_rec.document_num
, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h
('Unexpected Exception in Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-12300'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
--End of Business Validation Section
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN
UPDATE xx_po_headers_pre_interface
SET status = 'PR'
WHERE status = 'IP'
AND org_id = l_org_id ;
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-00035'
, 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
SELECT COUNT (*)
INTO l_count
FROM xx_po_headers_pre_interface
WHERE status = 'PR'
AND org_id = l_org_id
AND request_id = l_request_id ;
IF l_count > 0
THEN
/*---------------------------------------------------------------------------------------
-- This concurrent program is used for creating Standard Purchase Orders .For importing
-- Blanket Purchase Orders/Quotations the Program - Import Price Catalogs is to be used
----------------------------------------------------------------------------------------*/
l_standard_request_id := fnd_request.submit_request
( application => 'PO'
, program => 'POXPOPDOI'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => NULL --'Default Buyer'
, argument2 => 'STANDARD' --'Document Type'
, argument3 => NULL --'Document subtype'
, argument4 => 'N' --'Create/Replace Item -YES/NO'
, argument5 => NULL --'Create Sourcing Rules -Yes/No'
--, argument6 => 'APPROVED' --'PO Status'
,argument6 =>NULL
, argument7 => NULL
, argument8 => NULL
, argument9 => NULL
);
IF l_standard_request_id > 0 THEN
COMMIT;
ELSE
l_error_flag := 'Y';
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-00036'
, p_identifier2 => 'Error in submitting concurrent request'
, 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;
--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-00036'
, p_identifier2 => 'No records To Process in Interface Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END;
-- Delete the processed records from the interface table --
BEGIN
DELETE FROM xx_po_dist_pre_interface
WHERE interface_header_id IN (
SELECT interface_header_id
FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR'
);
DELETE FROM xx_po_lines_pre_interface
WHERE interface_header_id IN (
SELECT interface_header_id
FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR'
);
DELETE FROM xx_po_headers_pre_interface
WHERE org_id = l_org_id
AND status = 'PR';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
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_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-00035'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
/*******************************************************************************
* Procedure Name : VALIDATE_VENDOR
* Purpose : This program will validate vendor and vendor site
* for the Purchase Order
*
*******************************************************************************/
PROCEDURE validate_vendor (
p_po_hdr_preint_rec IN xx_po_headers_pre_interface%ROWTYPE
,p_error_flag OUT VARCHAR2
)
IS
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_valid_vendor VARCHAR2(1):='N';
l_valid_site VARCHAR2(1):='N';
BEGIN
p_error_flag := 'N';
BEGIN
SELECT 'Y'
INTO l_valid_vendor
FROM po_vendors
WHERE vendor_name = p_po_hdr_preint_rec.vendor_name
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Vendor is not valid');
--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-00037'
, p_identifier2 => 'Vendor is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
IF p_error_flag = 'N' THEN
BEGIN
SELECT 'Y'
INTO l_valid_site
FROM po_vendor_sites_all
WHERE vendor_site_code = p_po_hdr_preint_rec.vendor_site_code
AND org_id = p_po_hdr_preint_rec.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Vendor Site is not valid');
--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-00038'
, p_identifier2 => 'Vendor Site is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating vendor'||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-00039'
, p_identifier2 => 'Error occurred while validating vendor'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_hdr_preint_rec.document_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
/*******************************************************************************
* Procedure Name : VALIDATE_SHIP_BILL_LOC
* Purpose : This program will validate the ship to or bill to
* location for the purchase order depending on the
* value passed in p_loc_type
*
*******************************************************************************/
PROCEDURE validate_ship_bill_loc (
p_loc_type IN VARCHAR2,
p_loc IN VARCHAR2,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
)
IS
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
l_loc_valid VARCHAR2(1):='N';
BEGIN
p_error_flag := 'N';
IF p_loc_type = 'SHIP_TO' THEN
BEGIN
SELECT 'Y'
INTO l_loc_valid
FROM hr_locations_all
WHERE ship_to_site_flag = 'Y'
AND sysdate < NVL(inactive_date, sysdate+1)
AND location_code = p_loc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Ship to location is not valid');
--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-00040'
, p_identifier2 => 'Ship to location is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
ELSE
BEGIN
SELECT 'Y'
INTO l_loc_valid
FROM hr_locations_all
WHERE bill_to_site_flag = 'Y'
AND location_code = p_loc
AND sysdate < NVL(inactive_date, sysdate+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Bill to location is not valid');
--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-00041'
, p_identifier2 => 'Bill to location is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
WHEN OTHERS THEN
RAISE;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating location'||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-00042'
, p_identifier2 => 'Error occurred while validating location'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
--, p_identifier8 => c_po_dist_stg_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
/*******************************************************************************
* Procedure Name : validate_set_of_books
* Purpose : This procedure will validate set of books if provided else
* derives it from the OU
*
*******************************************************************************/
PROCEDURE validate_set_of_books (
p_po_dist_preint_rec IN OUT xx_po_dist_pre_interface%ROWTYPE,
p_po_num IN VARCHAR2,
p_error_flag OUT VARCHAR2
)
IS
l_set_of_books gl_sets_of_books.name%TYPE;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);
BEGIN
IF p_po_dist_preint_rec.set_of_books IS NULL THEN
BEGIN
SELECT gsob.name
INTO l_set_of_books
FROM gl_sets_of_books gsob
,hr_operating_units hou
WHERE hou.organization_id = p_po_dist_preint_rec.org_id
AND hou.set_of_books_id = gsob.set_of_books_id;
p_po_dist_preint_rec.set_of_books := l_set_of_books;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
ELSE
BEGIN
SELECT gsob.name
INTO l_set_of_books
FROM gl_sets_of_books gsob
,hr_operating_units hou
WHERE gsob.name = p_po_dist_preint_rec.set_of_books
AND hou.set_of_books_id = gsob.set_of_books_id
AND hou.organization_id = p_po_dist_preint_rec.org_id ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_error_flag := 'Y';
xx_trace.l ('Set of books is not valid');
--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-00043'
, p_identifier2 => 'Set of books is not valid'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
--, p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => p_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error_flag := 'Y';
xx_trace.h ('Error occurred while validating Set of books'||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-00043'
, p_identifier2 => 'Error occurred while validating Set of books'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_po_num
-- , p_identifier5 => l_org_code
--, p_identifier6 => c_po_stg_line_rec.line_num
--, p_identifier7 => c_po_stg_line_rec.shipment_num
, p_identifier8 => p_po_dist_preint_rec.distribution_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END XX_POCNV02_PO_IMPORT_PKG;
/
No comments :
Post a Comment