Oracle Applications & Oracle Cloud Fusion HCM : PO RETURN RECEIPTS Conversions

-- * *****************************************************************************
-- * NAME : XXPOCNV03a.ctl *
-- * PURPOSE : Control File for loading RCV Header/Line *
-- * Staging Tables *
-- * *
-- *******************************************************************************

LOAD DATA
TRUNCATE

INTO TABLE XX_PO_OPEN_RECEIPTS_HDR_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 ,
BATCH_NUM INTEGER EXTERNAL,
TRANSACTION_TYPE CHAR ,
STATUS CONSTANT 'NW' ,
HEADER_ID "XX_PO_RECEIPTS_HDR_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_OPEN_RECEIPTS_HDR_STG
WHEN REC_TYPE = 'H'AND TRANSACTION_TYPE = 'RECEIPT'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RECEIPT_NUM CHAR ,
RECEIPT_DATE ,
TRANSACTION_TYPE CHAR ,
ORG_CODE CHAR ,
VENDOR_NUM CHAR ,
STATUS CONSTANT 'NW' ,
HEADER_ID "XX_PO_RECEIPTS_HDR_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_OPEN_RECEIPTS_HDR_STG
WHEN REC_TYPE = 'H'AND TRANSACTION_TYPE = 'RETURN'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RETURN_NUMBER INTEGER EXTERNAL,
RETURN_DATE ,
TRANSACTION_TYPE CHAR ,
ORG_CODE CHAR ,
STATUS CONSTANT 'NW' ,
HEADER_ID "XX_PO_RECEIPTS_HDR_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_OPEN_RCPTS_LINE_STG
WHEN REC_TYPE = 'L' AND TRANSACTION_TYPE ='RECEIPT'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RECEIPT_NUM CHAR ,
PO_NUM CHAR ,
PO_LINE_NUM INTEGER EXTERNAL,
SHIPMENT_LINE_NUM INTEGER EXTERNAL,
DISTRIBUTION_NUM INTEGER EXTERNAL,
EXPECTED_RECEIPT_DATE ,
QUANTITY DECIMAL EXTERNAL,
TRANSACTION_TYPE CHAR ,
SUBINVENTORY CHAR ,
STATUS CONSTANT 'NW' ,
LINE_ID "XX_PO_OPEN_RCPTS_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_OPEN_RCPTS_LINE_STG
WHEN REC_TYPE = 'L' AND TRANSACTION_TYPE ='RETURN'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REC_TYPE POSITION(1:1) CHAR,
RETURN_NUM INTEGER EXTERNAL,
PO_NUM CHAR ,
PO_LINE_NUM INTEGER EXTERNAL,
SHIPMENT_LINE_NUM INTEGER EXTERNAL,
QUANTITY DECIMAL EXTERNAL,
TRANSACTION_TYPE CHAR,
RECEIPT_NUM CHAR,
RETURN_DATE ,
RETURN_LINE INTEGER EXTERNAL,
RETURN_REASON CHAR,
STATUS CONSTANT 'NW',
LINE_ID "XX_PO_OPEN_RCPTS_LINE_STG_S.NEXTVAL",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id"
)

CREATE OR REPLACE PACKAGE BODY XX_POCNV03_RECTRANS_IMPORT_PKG
AS

/********************************************************************************************
Package Name : XX_POCNV03_RECTRANS_IMPORT_PKG
Purpose : Package Body
Program Style :
--
***************************************************************************************************/

l_header_id xx_emf_message_headers.header_id%TYPE; -- EMF Header ID
l_org_id hr_operating_units.organization_id%TYPE;
l_group_id NUMBER;

/*******************************************************************************
* Procedure Name : get_po_details
* Purpose : This procedure will fetch the PO details for the PO Receipt
*
*
*******************************************************************************/
PROCEDURE get_po_details (
p_rcv_tranc_rec IN OUT xx_po_rcv_trans_pre_interface%ROWTYPE
,p_error_flag OUT VARCHAR2
);

/*******************************************************************************
* Procedure Name : get_receipt_details
* Purpose : This procedure will fetch the details of the the receiving
* transaction for PO Return
*
*******************************************************************************/
PROCEDURE get_receipt_details (
p_rcv_tranc_rec IN OUT xx_po_rcv_trans_pre_interface%ROWTYPE
,p_receipt_num IN VARCHAR2
,p_error_flag OUT VARCHAR2
);

/*******************************************************************************************************
Procedure Name : MAIN
Purpose : This program will read the records from the staging tables
for Receiving transaction Header and Line 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 order receipts/returns
*******************************************************************************************************/
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_rcpts_header_stg (p_org_code IN VARCHAR2) IS
SELECT header_id
,rec_type
,status
,org_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,recordcnt
,batch_num
,receipt_num
,receipt_date
,transaction_type
,return_number
,return_date
,vendor_num
FROM xx_po_open_receipts_hdr_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_rcpts_line_stg (p_receipt_number IN VARCHAR2) IS
SELECT line_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_type
,transaction_date
,quantity
,po_line_num
,auto_transact_code
,po_num
,expected_receipt_date
,receipt_num
,status
,shipment_line_num
,return_num
,return_date
,return_line
,subinventory
,return_reason
FROM xx_po_open_rcpts_line_stg
WHERE Decode (transaction_type,'RECEIPT', receipt_num,return_num) = p_receipt_number ;

-- Cursor to select PO Headers data from Pre-Interface table
CURSOR c_rcv_hdr_preint(p_request_id IN NUMBER) IS
SELECT *
FROM xx_po_rcv_header_pre_interface
WHERE status = 'IP'
AND processing_request_id = p_request_id;

-- Cursor to select PO Lines data from Pre-Interface table
CURSOR c_rcv_lines_preint(p_header_id IN NUMBER) IS
SELECT *
FROM xx_po_rcv_trans_pre_interface
WHERE header_interface_id = p_header_id
AND status ='IP';
--

/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
-- General Variables
--
l_standard_request_id fnd_concurrent_requests.request_id%TYPE:= 0;
l_org_code org_organization_definitions.organization_code%TYPE;
l_int_err_flag VARCHAR2(10):='N';
l_batch_num NUMBER;
l_transaction_type VARCHAR2(20):=NULL;
l_period_count 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_po_error_flag VARCHAR2(10) :=NULL;
l_bill_err_flag VARCHAR2(10) :=NULL;
l_count NUMBER :=0;
l_completed BOOLEAN;
l_days_early NUMBER;
l_days_late NUMBER;
l_need_by_date DATE :=NULL;
l_valid_sub VARCHAR2(10):='N';
l_valid_line VARCHAR2(10):='N';
l_value VARCHAR2(20);
v_ship_qty NUMBER :=0;
l_ctrl_err_flag VARCHAR2(1):='N';
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_int_header_id NUMBER;
v_interface_line_id NUMBER;

--
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE:= 'XX_POCNV03_RECTRANS_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_03';--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_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 := 'Receipt Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Operating Unit'; --Fifth Error Header
l_error_rec.identifier6 := 'Receipt/Return Line Number'; --Sixth Error Header
l_error_rec.identifier7 := 'Return Number'; --Seventh Error Header
l_error_rec.identifier8 := 'PO 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_rcv_header_pre_interface
SET processing_request_id =l_request_id
,last_update_date =SYSDATE
,last_updated_by = l_user_id
WHERE status = 'IP';

UPDATE xx_po_rcv_trans_pre_interface
SET request_id =l_request_id
,last_update_date =SYSDATE
,last_updated_by = l_user_id
WHERE 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,transaction_type,batch_num
INTO v_record_cnt,l_transaction_type,l_batch_num
FROM xx_po_open_receipts_hdr_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type ='C';

SELECT Count(*)
INTO v_file_hdr_count
FROM xx_po_open_receipts_hdr_stg
WHERE status = 'NW'
AND org_code = l_org_code
AND rec_type <> 'C';

SELECT Count(*)
INTO v_file_line_count
FROM xx_po_open_rcpts_line_stg xps
WHERE Decode (xps.transaction_type,'RECEIPT',receipt_num,return_num) IN (
SELECT Decode (xps.transaction_type,'RECEIPT',receipt_num,return_number)
FROM xx_po_open_receipts_hdr_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 THEN
l_ctrl_err_flag := 'Y';
xx_trace.l ('Record count mismatch in the 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-00001',
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
);
END IF;

IF l_transaction_type IS NULL THEN
l_ctrl_err_flag := 'Y';
l_ctrl_err_flag := 'Y';
xx_trace.l ('File Type should be present in Ctrl Record');
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 => 'File Type should be present in Ctrl Record',
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
);

END IF;

IF l_ctrl_err_flag = 'Y' THEN

UPDATE xx_po_open_receipts_hdr_stg
SET status = 'ER'
WHERE status = 'NW'
AND org_code = l_org_code;

COMMIT ;
-- Raise record count error --

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-00003',
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_receipts_header_rec IN c_rcpts_header_stg(l_org_code)
LOOP
l_error_flag := 'N';

l_proc_stg_recs := l_proc_stg_recs + 1;

IF c_receipts_header_rec.transaction_type IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Transaction 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-00004'
, p_identifier2 => 'Transaction Type cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type = 'RECEIPT' AND c_receipts_header_rec.receipt_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Receipt 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-00005'
, p_identifier2 => 'Receipt Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type = 'RECEIPT' AND c_receipts_header_rec.receipt_date IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Receipt Date 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 => 'Receipt Date cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type = 'RECEIPT' AND c_receipts_header_rec.vendor_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Vendor 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 => 'Vendor Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type <>'RECEIPT' AND l_transaction_type = 'RECEIPT')
OR (c_receipts_header_rec.transaction_type <>'RETURN' AND l_transaction_type = 'RETURN') THEN

l_error_flag := 'Y';
xx_trace.l ('Both receipt and return records cannot be present in a batch ');
--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 receipt and return records cannot be present in a batch'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type ='RETURN' AND c_receipts_header_rec.return_number IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Return 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-00009'
, p_identifier2 => 'Return Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_header_rec.transaction_type ='RETURN' AND c_receipts_header_rec.return_date IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Return Date 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 => 'Return Date cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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 l_error_flag ='N' THEN
-- Line Level Control Validations --

l_line_count := 0; -- Intialising Line Counts --
IF c_receipts_header_rec.transaction_type ='RECEIPT' THEN
l_doc_type := c_receipts_header_rec.receipt_num ;
ELSE
l_doc_type := c_receipts_header_rec.return_number;
END IF;

FOR c_receipts_line_stg_rec IN c_rcpts_line_stg (l_doc_type)
LOOP

l_line_count := l_line_count + 1;

IF c_receipts_line_stg_rec.receipt_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Receipt 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-00011'
, p_identifier2 => 'Receipt Number cannot be null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_receipts_line_stg_rec.quantity IS NULL OR c_receipts_line_stg_rec.quantity<0 THEN
l_error_flag := 'Y';
xx_trace.l ('Line Quantity cannot be null or negative');
--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 => 'Line Quantity cannot be null or negative'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
-- 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_receipts_line_stg_rec.po_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('PO Number is 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-00013'
, p_identifier2 => 'PO Number is null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
-- p_identifier7 => NULL -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_receipts_line_stg_rec.po_line_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('PO Line Number is 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-00014'
, p_identifier2 => 'PO Line Number is null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
-- p_identifier7 => NULL -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_receipts_line_stg_rec.shipment_line_num IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Shipment Line Number is 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 => 'Shipment Line Number is null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
-- p_identifier7 => NULL -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF (c_receipts_header_rec.transaction_type = 'RECEIPT' AND c_receipts_line_stg_rec.transaction_type <> 'RECEIPT')
OR (c_receipts_header_rec.transaction_type = 'RETURN' AND c_receipts_line_stg_rec.transaction_type IS NULL ) THEN
l_error_flag := 'Y';
xx_trace.l ('Invalid or Missing Line transaction 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-00016'
, p_identifier2 => 'Invalid or Missing Line transaction type'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
-- p_identifier6 => NULL
-- p_identifier7 => NULL -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_receipts_header_rec.transaction_type = 'RETURN' AND c_receipts_line_stg_rec.return_date IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Return Date is 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 => 'Return Date is null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
, p_identifier7 => c_receipts_line_stg_rec.return_num -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_receipts_header_rec.transaction_type = 'RETURN' AND c_receipts_line_stg_rec.return_line IS NULL THEN
l_error_flag := 'Y';
xx_trace.l ('Return Line is 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-00018'
, p_identifier2 => 'Return Line is null'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_header_rec.org_code
, p_identifier6 => c_receipts_line_stg_rec.return_line
, p_identifier7 => c_receipts_line_stg_rec.return_num -- Can be utilized for displaying more information
, p_identifier8 => c_receipts_line_stg_rec.po_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

END LOOP ; -- End of Line Level validations --
END IF;

-- Check for Receipts with no line records --
IF l_line_count = 0 THEN
l_error_flag := 'Y';
xx_trace.l ('Receipt 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-00019'
, p_identifier2 => 'Receipt has no lines'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_receipts_header_rec.receipt_num
, p_identifier5 => c_receipts_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_open_receipts_hdr_stg
SET status = 'ER'
WHERE header_id = c_receipts_header_rec.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_rcv_header_pre_interface (
header_interface_id,
GROUP_ID,
vendor_num,
processing_status_code,
receipt_source_code,
transaction_type,
auto_transact_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
receipt_num,
expected_receipt_date,
validation_flag,
processing_request_id,
status
)
VALUES
(
c_receipts_header_rec.header_id
,l_batch_num
,c_receipts_header_rec.vendor_num
,'PENDING'
,'VENDOR'
,c_receipts_header_rec.transaction_type
,'RECEIVE'
,SYSDATE
,l_user_id
,sysdate
,l_user_id
,c_receipts_header_rec.receipt_num
,c_receipts_header_rec.receipt_date
,'Y'
,l_request_id
,'IP'
);

EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into RCV 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-00020'
, p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_receipts_header_rec.receipt_num
, 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_rcv_trans_pre_interface (
interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
auto_transact_code,
receipt_source_code,
source_document_code,
destination_type_code,
subinventory,
header_interface_id,
document_num,
document_line_num,
document_shipment_line_num,
document_distribution_num,
reason_name,
validation_flag,
status,
attribute1
)
SELECT line_id
,l_batch_num
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_request_id
,transaction_type
, sysdate
,'PENDING'
,'BATCH'
,'PENDING'
,quantity
,Decode(transaction_type,'RECEIPT','DELIVER',NULL)
,'VENDOR'
,'PO'
,'INVENTORY'
,subinventory
,c_receipts_header_rec.header_id
,po_num
,po_line_num
,shipment_line_num
,distribution_num
,return_reason
,'Y'
,'IP'
,receipt_num
FROM xx_po_open_rcpts_line_stg
WHERE Decode(transaction_type,'RECEIPT',receipt_num,return_num)=l_doc_type;

EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into RCV 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-00021'
, p_identifier2 => 'Exception While Inserting Into Line Pre-Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_receipts_header_rec.receipt_num
, 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;

IF l_error_flag <> 'Y' THEN
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO l_group_id
FROM DUAL;
END IF;
/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/
BEGIN

FOR c_rcv_hdr_preint_rec IN c_rcv_hdr_preint(l_request_id)
LOOP

l_int_err_flag := 'N' ;
l_processed_recs := l_processed_recs + 1;

IF l_transaction_type = 'RECEIPT' THEN
BEGIN

BEGIN
SELECT 'N'
INTO l_vendor_error_flag
FROM po_vendors
WHERE segment1= c_rcv_hdr_preint_rec.vendor_num
AND enabled_flag ='Y';
EXCEPTION
WHEN OTHERS THEN
l_int_err_flag :='Y';
xx_trace.h ('Invalid Vendor');
-- 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 => 'Invalid Vendor'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- 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 l_int_err_flag <> 'Y' THEN
-- Header records are inserted in the Interface Table --
SELECT rcv_headers_interface_s.NEXTVAL
INTO v_int_header_id
FROM dual ;

INSERT INTO rcv_headers_interface(
header_interface_id,
GROUP_ID,
edi_control_num,
processing_status_code,
receipt_source_code,
asn_type,
transaction_type,
auto_transact_code,
test_flag,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
notice_creation_date,
shipment_num,
receipt_num,
receipt_header_id,
vendor_name,
vendor_num,
vendor_id,
vendor_site_code,
vendor_site_id,
from_organization_code,
from_organization_id,
ship_to_organization_code,
ship_to_organization_id,
location_code,
location_id,
bill_of_lading,
packing_slip,
shipped_date,
freight_carrier_code,
expected_receipt_date,
receiver_id,
num_of_containers,
waybill_airbill_num,
comments,
gross_weight,
gross_weight_uom_code,
net_weight,
net_weight_uom_code,
tar_weight,
tar_weight_uom_code,
packaging_code,
carrier_method,
carrier_equipment,
special_handling_code,
hazard_code,
hazard_class,
hazard_description,
freight_terms,
freight_bill_number,
invoice_num,
invoice_date,
total_invoice_amount,
tax_name,
tax_amount,
freight_amount,
currency_code,
conversion_rate_type,
conversion_rate,
conversion_rate_date,
payment_terms_name,
payment_terms_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
usggl_transaction_code,
employee_name,
employee_id,
invoice_status_code,
validation_flag,
processing_request_id,
customer_account_number,
customer_id,
customer_site_id,
customer_party_name,
remit_to_site_id
)
VALUES (
v_int_header_id,
l_group_id,
c_rcv_hdr_preint_rec.edi_control_num,
c_rcv_hdr_preint_rec.processing_status_code,
c_rcv_hdr_preint_rec.receipt_source_code,
c_rcv_hdr_preint_rec.asn_type,
'NEW',
c_rcv_hdr_preint_rec.auto_transact_code,
c_rcv_hdr_preint_rec.test_flag,
c_rcv_hdr_preint_rec.last_update_date,
c_rcv_hdr_preint_rec.last_updated_by,
c_rcv_hdr_preint_rec.last_update_login,
c_rcv_hdr_preint_rec.creation_date,
c_rcv_hdr_preint_rec.created_by,
c_rcv_hdr_preint_rec.notice_creation_date,
c_rcv_hdr_preint_rec.shipment_num,
c_rcv_hdr_preint_rec.receipt_num,
c_rcv_hdr_preint_rec.receipt_header_id,
c_rcv_hdr_preint_rec.vendor_name,
c_rcv_hdr_preint_rec.vendor_num,
c_rcv_hdr_preint_rec.vendor_id,
c_rcv_hdr_preint_rec.vendor_site_code,
c_rcv_hdr_preint_rec.vendor_site_id,
c_rcv_hdr_preint_rec.from_organization_code,
c_rcv_hdr_preint_rec.from_organization_id,
c_rcv_hdr_preint_rec.ship_to_organization_code,
c_rcv_hdr_preint_rec.ship_to_organization_id,
c_rcv_hdr_preint_rec.location_code,
c_rcv_hdr_preint_rec.location_id,
c_rcv_hdr_preint_rec.bill_of_lading,
c_rcv_hdr_preint_rec.packing_slip,
c_rcv_hdr_preint_rec.shipped_date,
c_rcv_hdr_preint_rec.freight_carrier_code,
c_rcv_hdr_preint_rec.expected_receipt_date,
c_rcv_hdr_preint_rec.receiver_id,
c_rcv_hdr_preint_rec.num_of_containers,
c_rcv_hdr_preint_rec.waybill_airbill_num,
c_rcv_hdr_preint_rec.comments,
c_rcv_hdr_preint_rec.gross_weight,
c_rcv_hdr_preint_rec.gross_weight_uom_code,
c_rcv_hdr_preint_rec.net_weight,
c_rcv_hdr_preint_rec.net_weight_uom_code,
c_rcv_hdr_preint_rec.tar_weight,
c_rcv_hdr_preint_rec.tar_weight_uom_code,
c_rcv_hdr_preint_rec.packaging_code,
c_rcv_hdr_preint_rec.carrier_method,
c_rcv_hdr_preint_rec.carrier_equipment,
c_rcv_hdr_preint_rec.special_handling_code,
c_rcv_hdr_preint_rec.hazard_code,
c_rcv_hdr_preint_rec.hazard_class,
c_rcv_hdr_preint_rec.hazard_description,
c_rcv_hdr_preint_rec.freight_terms,
c_rcv_hdr_preint_rec.freight_bill_number,
c_rcv_hdr_preint_rec.invoice_num,
c_rcv_hdr_preint_rec.invoice_date,
c_rcv_hdr_preint_rec.total_invoice_amount,
c_rcv_hdr_preint_rec.tax_name,
c_rcv_hdr_preint_rec.tax_amount,
c_rcv_hdr_preint_rec.freight_amount,
c_rcv_hdr_preint_rec.currency_code,
c_rcv_hdr_preint_rec.conversion_rate_type,
c_rcv_hdr_preint_rec.conversion_rate,
c_rcv_hdr_preint_rec.conversion_rate_date,
c_rcv_hdr_preint_rec.payment_terms_name,
c_rcv_hdr_preint_rec.payment_terms_id,
c_rcv_hdr_preint_rec.attribute_category,
c_rcv_hdr_preint_rec.attribute1,
c_rcv_hdr_preint_rec.attribute2,
c_rcv_hdr_preint_rec.attribute3,
c_rcv_hdr_preint_rec.attribute4,
c_rcv_hdr_preint_rec.attribute5,
c_rcv_hdr_preint_rec.attribute6,
c_rcv_hdr_preint_rec.attribute7,
c_rcv_hdr_preint_rec.attribute8,
c_rcv_hdr_preint_rec.attribute9,
c_rcv_hdr_preint_rec.attribute10,
c_rcv_hdr_preint_rec.attribute11,
c_rcv_hdr_preint_rec.attribute12,
c_rcv_hdr_preint_rec.attribute13,
c_rcv_hdr_preint_rec.attribute14,
c_rcv_hdr_preint_rec.attribute15,
c_rcv_hdr_preint_rec.usggl_transaction_code,
c_rcv_hdr_preint_rec.employee_name,
c_rcv_hdr_preint_rec.employee_id,
c_rcv_hdr_preint_rec.invoice_status_code,
c_rcv_hdr_preint_rec.validation_flag,
NULL,
c_rcv_hdr_preint_rec.customer_account_number,
c_rcv_hdr_preint_rec.customer_id,
c_rcv_hdr_preint_rec.customer_site_id,
c_rcv_hdr_preint_rec.customer_party_name,
c_rcv_hdr_preint_rec.remit_to_site_id
);
END IF;
EXCEPTION
WHEN OTHERS THEN
l_int_err_flag :='Y';
xx_trace.h ('Error inserting into RCV Interface Header Table');
-- 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 => 'Error inserting into RCV Interface Header Table'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- 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;

END IF;

FOR c_rcv_line_preint_rec IN c_rcv_lines_preint(c_rcv_hdr_preint_rec.header_interface_id)
LOOP

BEGIN
SELECT Count(*)
INTO l_period_count
FROM gl_period_statuses gps
,fnd_application fa
WHERE fa.application_short_name IN ('PO','SQLGL')
AND fa.application_id = gps.application_id
AND gps.set_of_books_id = fnd_profile.Value('GL_SET_OF_BKS_ID')
AND gps.start_date<=To_Date(c_rcv_line_preint_rec.transaction_date,'DD MM RRRR')
AND gps.end_date >= To_Date(c_rcv_line_preint_rec.transaction_date,'DD-MM-RRRR')
AND closing_status ='O';
EXCEPTION
WHEN OTHERS THEN
l_period_count :=0;
xx_trace.l ('Error fetching period count');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00025'
, p_identifier2 => 'Error fetching period count'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

IF l_period_count < 2 THEN
l_int_err_flag :='Y';
xx_trace.l ('Transaction date not lie in an open period');
--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-00026'
, p_identifier2 => 'Transaction date not lie in an open period'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_rcv_hdr_preint_rec.transaction_type = 'RECEIPT' THEN
BEGIN
get_po_details (c_rcv_line_preint_rec
,l_po_error_flag);
IF l_po_error_flag ='Y' THEN
l_int_err_flag :='Y';
END IF;

IF l_int_err_flag<>'Y' THEN
IF c_rcv_line_preint_rec.document_distribution_num IS NULL THEN
SELECT quantity - quantity_received
INTO v_ship_qty
FROM po_line_locations_all
WHERE line_location_id = c_rcv_line_preint_rec.po_line_location_id;
ELSE
SELECT quantity_ordered-quantity_delivered
INTO v_ship_qty
FROM po_distributions_all
WHERE po_distribution_id = c_rcv_line_preint_rec.po_distribution_id;
END IF;

IF c_rcv_line_preint_rec.quantity > v_ship_qty THEN
l_int_err_flag :='Y';
xx_trace.l ('Transaction Quantity greater than line quantity');
-- 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 => 'Transaction Quantity greater than line quantity'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF c_rcv_line_preint_rec.to_organization_id IS NOT NULL THEN
BEGIN
SELECT 'Y'
INTO l_valid_sub
FROM mtl_secondary_inventories
WHERE organization_id= c_rcv_line_preint_rec.to_organization_id
AND secondary_inventory_name = c_rcv_line_preint_rec.subinventory;
EXCEPTION
WHEN OTHERS THEN
l_int_err_flag :='Y';
xx_trace.l ('Invalid Subinventory');
-- 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-00028'
, p_identifier2 => 'Invalid Subinventory'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
END IF;

IF c_rcv_line_preint_rec.expected_receipt_date IS NOT NULL THEN
BEGIN
SELECT Nvl(days_early_receipt_allowed,0),Nvl(days_late_receipt_allowed,0),Nvl(need_by_date,promised_date)
INTO l_days_early,l_days_late,l_need_by_date
FROM po_line_locations_all
WHERE line_location_id = c_rcv_line_preint_rec.po_line_location_id;

IF NOT(c_rcv_line_preint_rec.expected_receipt_date > (l_need_by_date - l_days_early)
AND c_rcv_line_preint_rec.expected_receipt_date < (l_need_by_date + l_days_late)) THEN
l_int_err_flag :='Y';
xx_trace.l ('Receipt Date is not within the tolerance range');
-- 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 => 'Receipt Date is not within the tolerance range'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

EXCEPTION
WHEN OTHERS THEN
l_int_err_flag :='Y';
xx_trace.l ('Error fetching receipt date tolerance');
-- 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-00030'
, p_identifier2 => 'Error fetching receipt date tolerance'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => c_rcv_line_preint_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

END IF;
END IF;
END ;
IF l_int_err_flag ='N' THEN
BEGIN
INSERT INTO rcv_transactions_interface (
interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
processing_request_id,
transaction_status_code,
category_id,
quantity,
unit_of_measure,
interface_source_code,
interface_source_line_id,
inv_transaction_id, item_id,
item_description,
item_revision, uom_code,
employee_id,
auto_transact_code,
shipment_header_id,
shipment_line_id,
ship_to_location_id,
primary_quantity,
primary_unit_of_measure,
receipt_source_code,
vendor_id,
vendor_site_id,
from_organization_id,
from_subinventory,
to_organization_id,
intransit_owning_org_id,
routing_header_id,
routing_step_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_revision_num,
po_release_id, po_line_id,
po_line_location_id, po_unit_price,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
requisition_line_id,
req_distribution_id,
charge_account_id,
substitute_unordered_code,
receipt_exception_flag,
accrual_status_code,
inspection_status_code,
inspection_quality_code,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id, subinventory,
locator_id,
wip_entity_id,
wip_line_id,
department_code,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
shipment_num,
freight_carrier_code,
bill_of_lading,
packing_slip,
shipped_date,
expected_receipt_date,
actual_cost,
transfer_cost,
transportation_cost,
transportation_account_id,
num_of_containers,
waybill_airbill_num,
vendor_item_num,
vendor_lot_num,
rma_reference,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ship_head_attribute_category,
ship_head_attribute1,
ship_head_attribute2,
ship_head_attribute3,
ship_head_attribute4,
ship_head_attribute5,
ship_head_attribute6,
ship_head_attribute7,
ship_head_attribute8,
ship_head_attribute9,
ship_head_attribute10,
ship_head_attribute11,
ship_head_attribute12,
ship_head_attribute13,
ship_head_attribute14,
ship_head_attribute15,
ship_line_attribute_category,
ship_line_attribute1,
ship_line_attribute2,
ship_line_attribute3,
ship_line_attribute4,
ship_line_attribute5,
ship_line_attribute6,
ship_line_attribute7,
ship_line_attribute8,
ship_line_attribute9,
ship_line_attribute10,
ship_line_attribute11,
ship_line_attribute12,
ship_line_attribute13,
ship_line_attribute14,
ship_line_attribute15,
ussgl_transaction_code,
government_context,
reason_id,
destination_context,
source_doc_quantity,
source_doc_unit_of_measure,
movement_id,
header_interface_id,
vendor_cum_shipped_qty,
item_num,
document_num,
document_line_num,
truck_num,
ship_to_location_code,
container_num,
substitute_item_num,
notice_unit_price,
item_category,
location_code,
vendor_name,
vendor_num,
vendor_site_code,
from_organization_code,
to_organization_code,
intransit_owning_org_code,
routing_code,
routing_step,
release_num,
document_shipment_line_num,
document_distribution_num,
deliver_to_person_name,
deliver_to_location_code,
use_mtl_lot,
use_mtl_serial,
LOCATOR,
reason_name, validation_flag,
substitute_item_id,
quantity_shipped,
quantity_invoiced,
tax_name,
tax_amount,
req_num,
req_line_num,
req_distribution_num,
wip_entity_name,
wip_line_code,
resource_code,
shipment_line_status_code,
barcode_label,
transfer_percentage,
qa_collection_id,
country_of_origin_code,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
customer_item_num,
create_debit_memo_flag,
put_away_rule_id,
put_away_strategy_id,
lpn_id,
transfer_lpn_id,
cost_group_id,
mobile_txn,
mmtt_temp_id,
transfer_cost_group_id,
secondary_quantity,
secondary_unit_of_measure,
secondary_uom_code,
qc_grade,
from_locator,
from_locator_id,
parent_source_transaction_num,
interface_available_qty,
interface_transaction_qty,
interface_available_amt,
interface_transaction_amt,
license_plate_number,
source_transaction_num,
transfer_license_plate_number,
lpn_group_id,
order_transaction_id,
customer_account_number,
customer_party_name,
oe_order_line_num,
oe_order_num,
parent_interface_txn_id,
customer_item_id,
amount,
job_id,
timecard_id,
timecard_ovn,
erecord_id,
project_id,
task_id

)
VALUES ( rcv_transactions_interface_s.NEXTVAL,
l_group_id,
c_rcv_line_preint_rec.last_update_date,
c_rcv_line_preint_rec.last_updated_by,
c_rcv_line_preint_rec.creation_date,
c_rcv_line_preint_rec.created_by,
c_rcv_line_preint_rec.last_update_login,
NULL,
c_rcv_line_preint_rec.program_application_id,
c_rcv_line_preint_rec.program_id,
c_rcv_line_preint_rec.program_update_date,
'RECEIVE',
c_rcv_line_preint_rec.transaction_date,
c_rcv_line_preint_rec.processing_status_code,
c_rcv_line_preint_rec.processing_mode_code,
c_rcv_line_preint_rec.processing_request_id,
c_rcv_line_preint_rec.transaction_status_code,
c_rcv_line_preint_rec.category_id,
c_rcv_line_preint_rec.quantity,
c_rcv_line_preint_rec.unit_of_measure,
c_rcv_line_preint_rec.interface_source_code,
c_rcv_line_preint_rec.interface_source_line_id,
c_rcv_line_preint_rec.inv_transaction_id,
c_rcv_line_preint_rec.item_id,
c_rcv_line_preint_rec.item_description,
c_rcv_line_preint_rec.item_revision,
c_rcv_line_preint_rec.uom_code,
c_rcv_line_preint_rec.employee_id,
c_rcv_line_preint_rec.auto_transact_code,
c_rcv_line_preint_rec.shipment_header_id,
c_rcv_line_preint_rec.shipment_line_id,
c_rcv_line_preint_rec.ship_to_location_id,
c_rcv_line_preint_rec.primary_quantity,
c_rcv_line_preint_rec.primary_unit_of_measure,
c_rcv_line_preint_rec.receipt_source_code,
c_rcv_line_preint_rec.vendor_id,
c_rcv_line_preint_rec.vendor_site_id,
c_rcv_line_preint_rec.from_organization_id,
c_rcv_line_preint_rec.from_subinventory,
c_rcv_line_preint_rec.to_organization_id,
c_rcv_line_preint_rec.intransit_owning_org_id,
c_rcv_line_preint_rec.routing_header_id,
c_rcv_line_preint_rec.routing_step_id,
c_rcv_line_preint_rec.source_document_code,
c_rcv_line_preint_rec.parent_transaction_id,
c_rcv_line_preint_rec.po_header_id,
c_rcv_line_preint_rec.po_revision_num,
c_rcv_line_preint_rec.po_release_id,
c_rcv_line_preint_rec.po_line_id,
c_rcv_line_preint_rec.po_line_location_id,
c_rcv_line_preint_rec.po_unit_price,
c_rcv_line_preint_rec.currency_code,
c_rcv_line_preint_rec.currency_conversion_type,
c_rcv_line_preint_rec.currency_conversion_rate,
c_rcv_line_preint_rec.currency_conversion_date,
c_rcv_line_preint_rec.po_distribution_id,
c_rcv_line_preint_rec.requisition_line_id,
c_rcv_line_preint_rec.req_distribution_id,
c_rcv_line_preint_rec.charge_account_id,
c_rcv_line_preint_rec.substitute_unordered_code,
c_rcv_line_preint_rec.receipt_exception_flag,
c_rcv_line_preint_rec.accrual_status_code,
c_rcv_line_preint_rec.inspection_status_code,
c_rcv_line_preint_rec.inspection_quality_code,
'NULL',
c_rcv_line_preint_rec.deliver_to_person_id,
c_rcv_line_preint_rec.location_id,
c_rcv_line_preint_rec.deliver_to_location_id,
c_rcv_line_preint_rec.subinventory,
c_rcv_line_preint_rec.locator_id,
c_rcv_line_preint_rec.wip_entity_id,
c_rcv_line_preint_rec.wip_line_id,
c_rcv_line_preint_rec.department_code,
c_rcv_line_preint_rec.wip_repetitive_schedule_id,
c_rcv_line_preint_rec.wip_operation_seq_num,
c_rcv_line_preint_rec.wip_resource_seq_num,
c_rcv_line_preint_rec.bom_resource_id,
c_rcv_line_preint_rec.shipment_num,
c_rcv_line_preint_rec.freight_carrier_code,
c_rcv_line_preint_rec.bill_of_lading,
c_rcv_line_preint_rec.packing_slip,
c_rcv_line_preint_rec.shipped_date,
c_rcv_line_preint_rec.expected_receipt_date,
c_rcv_line_preint_rec.actual_cost,
c_rcv_line_preint_rec.transfer_cost,
c_rcv_line_preint_rec.transportation_cost,
c_rcv_line_preint_rec.transportation_account_id,
c_rcv_line_preint_rec.num_of_containers,
c_rcv_line_preint_rec.waybill_airbill_num,
c_rcv_line_preint_rec.vendor_item_num,
c_rcv_line_preint_rec.vendor_lot_num,
c_rcv_line_preint_rec.rma_reference,
c_rcv_line_preint_rec.comments,
c_rcv_line_preint_rec.attribute_category,
c_rcv_line_preint_rec.attribute1,
c_rcv_line_preint_rec.attribute2,
c_rcv_line_preint_rec.attribute3,
c_rcv_line_preint_rec.attribute4,
c_rcv_line_preint_rec.attribute5,
c_rcv_line_preint_rec.attribute6,
c_rcv_line_preint_rec.attribute7,
c_rcv_line_preint_rec.attribute8,
c_rcv_line_preint_rec.attribute9,
c_rcv_line_preint_rec.attribute10,
c_rcv_line_preint_rec.attribute11,
c_rcv_line_preint_rec.attribute12,
c_rcv_line_preint_rec.attribute13,
c_rcv_line_preint_rec.attribute14,
c_rcv_line_preint_rec.attribute15,
c_rcv_line_preint_rec.ship_head_attribute_category,
c_rcv_line_preint_rec.ship_head_attribute1,
c_rcv_line_preint_rec.ship_head_attribute2,
c_rcv_line_preint_rec.ship_head_attribute3,
c_rcv_line_preint_rec.ship_head_attribute4,
c_rcv_line_preint_rec.ship_head_attribute5,
c_rcv_line_preint_rec.ship_head_attribute6,
c_rcv_line_preint_rec.ship_head_attribute7,
c_rcv_line_preint_rec.ship_head_attribute8,
c_rcv_line_preint_rec.ship_head_attribute9,
c_rcv_line_preint_rec.ship_head_attribute10,
c_rcv_line_preint_rec.ship_head_attribute11,
c_rcv_line_preint_rec.ship_head_attribute12,
c_rcv_line_preint_rec.ship_head_attribute13,
c_rcv_line_preint_rec.ship_head_attribute14,
c_rcv_line_preint_rec.ship_head_attribute15,
c_rcv_line_preint_rec.ship_line_attribute_category,
c_rcv_line_preint_rec.ship_line_attribute1,
c_rcv_line_preint_rec.ship_line_attribute2,
c_rcv_line_preint_rec.ship_line_attribute3,
c_rcv_line_preint_rec.ship_line_attribute4,
c_rcv_line_preint_rec.ship_line_attribute5,
c_rcv_line_preint_rec.ship_line_attribute6,
c_rcv_line_preint_rec.ship_line_attribute7,
c_rcv_line_preint_rec.ship_line_attribute8,
c_rcv_line_preint_rec.ship_line_attribute9,
c_rcv_line_preint_rec.ship_line_attribute10,
c_rcv_line_preint_rec.ship_line_attribute11,
c_rcv_line_preint_rec.ship_line_attribute12,
c_rcv_line_preint_rec.ship_line_attribute13,
c_rcv_line_preint_rec.ship_line_attribute14,
c_rcv_line_preint_rec.ship_line_attribute15,
c_rcv_line_preint_rec.ussgl_transaction_code,
c_rcv_line_preint_rec.government_context,
c_rcv_line_preint_rec.reason_id,
c_rcv_line_preint_rec.destination_context,
c_rcv_line_preint_rec.source_doc_quantity,
c_rcv_line_preint_rec.source_doc_unit_of_measure,
c_rcv_line_preint_rec.movement_id,
v_int_header_id,
c_rcv_line_preint_rec.vendor_cum_shipped_qty,
c_rcv_line_preint_rec.item_num,
c_rcv_line_preint_rec.document_num,
c_rcv_line_preint_rec.document_line_num,
c_rcv_line_preint_rec.truck_num,
c_rcv_line_preint_rec.ship_to_location_code,
c_rcv_line_preint_rec.container_num,
c_rcv_line_preint_rec.substitute_item_num,
c_rcv_line_preint_rec.notice_unit_price,
c_rcv_line_preint_rec.item_category,
c_rcv_line_preint_rec.location_code,
c_rcv_line_preint_rec.vendor_name,
c_rcv_line_preint_rec.vendor_num,
c_rcv_line_preint_rec.vendor_site_code,
c_rcv_line_preint_rec.from_organization_code,
c_rcv_line_preint_rec.to_organization_code,
c_rcv_line_preint_rec.intransit_owning_org_code,
c_rcv_line_preint_rec.routing_code,
c_rcv_line_preint_rec.routing_step,
c_rcv_line_preint_rec.release_num,
c_rcv_line_preint_rec.document_shipment_line_num,
c_rcv_line_preint_rec.document_distribution_num,
c_rcv_line_preint_rec.deliver_to_person_name,
c_rcv_line_preint_rec.deliver_to_location_code,
c_rcv_line_preint_rec.use_mtl_lot,
c_rcv_line_preint_rec.use_mtl_serial,
c_rcv_line_preint_rec.LOCATOR,
c_rcv_line_preint_rec.reason_name,
c_rcv_line_preint_rec.validation_flag,
c_rcv_line_preint_rec.substitute_item_id,
c_rcv_line_preint_rec.quantity_shipped,
c_rcv_line_preint_rec.quantity_invoiced,
c_rcv_line_preint_rec.tax_name,
c_rcv_line_preint_rec.tax_amount,
c_rcv_line_preint_rec.req_num,
c_rcv_line_preint_rec.req_line_num,
c_rcv_line_preint_rec.req_distribution_num,
c_rcv_line_preint_rec.wip_entity_name,
c_rcv_line_preint_rec.wip_line_code,
c_rcv_line_preint_rec.resource_code,
c_rcv_line_preint_rec.shipment_line_status_code,
c_rcv_line_preint_rec.barcode_label,
c_rcv_line_preint_rec.transfer_percentage,
c_rcv_line_preint_rec.qa_collection_id,
c_rcv_line_preint_rec.country_of_origin_code,
c_rcv_line_preint_rec.oe_order_header_id,
c_rcv_line_preint_rec.oe_order_line_id,
c_rcv_line_preint_rec.customer_id,
c_rcv_line_preint_rec.customer_site_id,
c_rcv_line_preint_rec.customer_item_num,
c_rcv_line_preint_rec.create_debit_memo_flag,
c_rcv_line_preint_rec.put_away_rule_id,
c_rcv_line_preint_rec.put_away_strategy_id,
c_rcv_line_preint_rec.lpn_id,
c_rcv_line_preint_rec.transfer_lpn_id,
c_rcv_line_preint_rec.cost_group_id,
c_rcv_line_preint_rec.mobile_txn,
c_rcv_line_preint_rec.mmtt_temp_id,
c_rcv_line_preint_rec.transfer_cost_group_id,
c_rcv_line_preint_rec.secondary_quantity,
c_rcv_line_preint_rec.secondary_unit_of_measure,
c_rcv_line_preint_rec.secondary_uom_code,
c_rcv_line_preint_rec.qc_grade,
c_rcv_line_preint_rec.from_locator,
c_rcv_line_preint_rec.from_locator_id,
c_rcv_line_preint_rec.parent_source_transaction_num,
c_rcv_line_preint_rec.interface_available_qty,
c_rcv_line_preint_rec.interface_transaction_qty,
c_rcv_line_preint_rec.interface_available_amt,
c_rcv_line_preint_rec.interface_transaction_amt,
c_rcv_line_preint_rec.license_plate_number,
c_rcv_line_preint_rec.source_transaction_num,
c_rcv_line_preint_rec.transfer_license_plate_number,
c_rcv_line_preint_rec.lpn_group_id,
c_rcv_line_preint_rec.order_transaction_id,
c_rcv_line_preint_rec.customer_account_number,
c_rcv_line_preint_rec.customer_party_name,
c_rcv_line_preint_rec.oe_order_line_num,
c_rcv_line_preint_rec.oe_order_num,
c_rcv_line_preint_rec.parent_interface_txn_id,
c_rcv_line_preint_rec.customer_item_id,
c_rcv_line_preint_rec.amount,
c_rcv_line_preint_rec.job_id,
c_rcv_line_preint_rec.timecard_id,
c_rcv_line_preint_rec.timecard_ovn,
c_rcv_line_preint_rec.erecord_id,
c_rcv_line_preint_rec.project_id,
c_rcv_line_preint_rec.task_id
) ;
EXCEPTION
WHEN OTHERS THEN
xx_trace.h('Exception While Inserting Into RCV Interface Transaction 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-00031'
, p_identifier2 => 'Exception While Inserting Into PO Interface table.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
, p_identifier4 => c_rcv_hdr_preint_rec.receipt_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
l_int_err_flag:='Y';

END;
END IF;

ELSIF c_rcv_hdr_preint_rec.transaction_type = 'RETURN' THEN
get_receipt_details (c_rcv_line_preint_rec,
c_rcv_line_preint_rec.attribute1,
l_po_error_flag);
IF l_po_error_flag ='Y' THEN
l_int_err_flag :='Y';
ROLLBACK;
ELSE
COMMIT;
END IF;
END IF;

END LOOP; --End of Line Level Business Validations --

IF l_int_err_flag ='Y' THEN
BEGIN
l_error_flag := l_int_err_flag;
ROLLBACK;
UPDATE xx_po_rcv_header_pre_interface
SET status = 'ER'
WHERE header_interface_id = c_rcv_hdr_preint_rec.header_interface_id
AND status ='IP';

UPDATE xx_po_rcv_trans_pre_interface
SET status ='ER'
WHERE header_interface_id = c_rcv_hdr_preint_rec.header_interface_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_rcv_hdr_preint_rec.receipt_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;
ELSE
l_successful_recs := l_successful_recs + 1;
END IF;
COMMIT;
END LOOP;

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_rcv_header_pre_interface
SET status = 'PR'
WHERE status = 'IP'
AND processing_request_id = l_request_id ;

UPDATE xx_po_rcv_trans_pre_interface
SET status ='PR'
WHERE status = 'IP'
AND request_id =l_request_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-00032'
, 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_rcv_header_pre_interface
WHERE status = 'PR'
AND processing_request_id = l_request_id ;

IF l_count > 0
THEN
/*---------------------------------------------------------------------------------------
-- This request submits the Receiving Transactions Processor concurrent program
----------------------------------------------------------------------------------------*/

l_standard_request_id := fnd_request.submit_request
( application => 'PO'
, program => 'RVCTP'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => 'BATCH' --'Processing Mode'
, argument2 => l_group_id --'Group ID'
, argument3 => CHR (0)
, argument4 => CHR (0)
, argument5 => CHR (0)
, argument6 => CHR (0)
, argument7 => CHR (0)
, argument8 => CHR (0)
);

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-00033'
, 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-00034'
, 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_rcv_trans_pre_interface
WHERE header_interface_id IN (
SELECT header_interface_id
FROM xx_po_rcv_header_pre_interface
WHERE request_id = l_request_id
AND status = 'PR'
);

DELETE FROM xx_po_rcv_header_pre_interface
WHERE processing_request_id = l_request_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 : get_po_details
* Purpose : This procedure will fetch the PO details for the PO Receipt
*
*******************************************************************************/

PROCEDURE get_po_details (
p_rcv_tranc_rec IN OUT xx_po_rcv_trans_pre_interface%ROWTYPE
,p_error_flag OUT VARCHAR2
)
IS

l_po_header_id po_headers_all.po_header_id%TYPE;
l_po_line_id po_lines_all.po_line_id%TYPE;
l_po_line_loc_id po_line_locations_all.line_location_id%TYPE;
l_return_value NUMBER;
l_output_message VARCHAR2 (1000);

BEGIN
p_error_flag :='N';

BEGIN
SELECT po_header_id
INTO l_po_header_id
FROM po_headers_all
WHERE segment1 = p_rcv_tranc_rec.document_num
AND org_id = l_org_id;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid PO Number');
-- 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-00035'
, p_identifier2 => 'Invalid PO Number'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_number
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

BEGIN
SELECT po_line_id
INTO l_po_line_id
FROM po_lines_all
WHERE po_header_id = l_po_header_id
AND line_num =p_rcv_tranc_rec.document_line_num;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid PO Line');
-- 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-00036'
, p_identifier2 => 'Invalid PO Line'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

BEGIN
SELECT line_location_id
INTO l_po_line_loc_id
FROM po_line_locations_all
WHERE po_header_id = l_po_header_id
AND po_line_id = l_po_line_id
AND shipment_num = p_rcv_tranc_rec.document_shipment_line_num;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid PO Shipment');
-- 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 => 'Invalid PO Shipment'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

IF l_po_header_id IS NOT NULL AND l_po_line_id IS NOT NULL AND l_po_line_loc_id IS NOT NULL THEN

p_rcv_tranc_rec.po_header_id := l_po_header_id;
p_rcv_tranc_rec.po_line_id := l_po_line_id;
p_rcv_tranc_rec.po_line_location_id := l_po_line_loc_id;

IF p_rcv_tranc_rec.document_distribution_num IS NOT NULL THEN
BEGIN
SELECT po_distribution_id
INTO p_rcv_tranc_rec.po_distribution_id
FROM po_distributions_all
WHERE po_header_id = l_po_header_id
AND po_line_id = l_po_line_id
AND distribution_num = p_rcv_tranc_rec.document_distribution_num;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid Distribution Number');
-- 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 => 'Invalid Distribution Number'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_rcv_tranc_rec.attribute1
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- 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;
END IF;

BEGIN
SELECT vendor_id,agent_id
INTO p_rcv_tranc_rec.vendor_id,p_rcv_tranc_rec.employee_id
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
EXCEPTION
WHEN OTHERS THEN
p_error_flag :='Y';
RAISE;
END;

IF p_rcv_tranc_rec.vendor_id IS NULL THEN
p_error_flag :='Y';
xx_trace.l ('Vendor Name missing in PO');
-- 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 => 'Vendor Name missing in PO'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

BEGIN
SELECT item_id ,item_description,unit_meas_lookup_code
INTO p_rcv_tranc_rec.item_id,p_rcv_tranc_rec.item_description,p_rcv_tranc_rec.unit_of_measure
FROM po_lines_all
WHERE po_line_id = l_po_line_id;
EXCEPTION
WHEN OTHERS THEN
p_error_flag :='Y';
RAISE;
END;

IF p_rcv_tranc_rec.item_description IS NULL THEN
p_error_flag :='Y';
xx_trace.l ('Item description is missing in PO Line');
-- 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 => 'Item description is missing in PO Line'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF p_rcv_tranc_rec.unit_of_measure IS NULL THEN
p_error_flag :='Y';
xx_trace.l ('UOM Code is missing in PO');
-- 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 => 'UOM Code is missing in PO'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

BEGIN
SELECT ship_to_organization_id
,ship_to_location_id
INTO p_rcv_tranc_rec.to_organization_id
,p_rcv_tranc_rec.ship_to_location_id
FROM po_line_locations_all pll
,po_lines_all pl
WHERE pll.line_location_id = l_po_line_loc_id
AND pl.po_line_id = l_po_line_id
AND pll.po_line_id = pl.po_line_id;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END IF;

EXCEPTION
WHEN OTHERS THEN
p_error_flag :='Y';
xx_trace.h ('Error in validating PO Details'||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 in validating PO Details'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
--, p_identifier4 => p_rcv_tranc_rec.receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

PROCEDURE get_receipt_details (
p_rcv_tranc_rec IN OUT xx_po_rcv_trans_pre_interface%ROWTYPE
,p_receipt_num IN VARCHAR2
,p_error_flag OUT VARCHAR2
)
IS
CURSOR c_receipt_details( cp_po_number IN NUMBER,cp_po_line_num IN NUMBER) IS
SELECT rsh.receipt_num receipt_num, ph.segment1 po_number,
rt.transaction_id transaction_id,
rt.transaction_type transaction_type,
rt.transaction_date transaction_date, rt.quantity quantity,
rt.unit_of_measure unit_of_measure,
rt.shipment_header_id shipment_header_id,
rt.shipment_line_id shipment_line_id,
rt.source_document_code source_document_code,
rt.destination_type_code destination_type_code,
rt.employee_id employee_id,
rt.transaction_id parent_transaction_id,
rt.po_header_id po_header_id, rt.po_line_id po_line_id,
pl.line_num line_num, pl.item_id item_id,
pl.item_description item_description, pl.unit_price unit_price,
rt.po_line_location_id po_line_location_id,
rt.po_distribution_id po_distribution_id,
rt.routing_header_id routing_header_id,
rt.routing_step_id routing_step_id,
rt.deliver_to_person_id deliver_to_person_id,
rt.deliver_to_location_id deliver_to_location_id,
rt.vendor_id vendor_id, rt.vendor_site_id vendor_site_id,
rt.organization_id organization_id, rt.subinventory subinventory,
rt.locator_id locator_id, rt.location_id location_id,
rsh.ship_to_org_id ship_to_org_id
FROM rcv_transactions rt,
rcv_shipment_headers rsh,
po_headers_all ph,
po_lines_all pl
WHERE rsh.receipt_num = p_receipt_num
AND ph.segment1 LIKE cp_po_number
AND ph.po_header_id = pl.po_header_id
AND rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND pl.line_num = cp_po_line_num
AND rt.transaction_type = 'DELIVER';

l_return_value NUMBER;
l_output_message VARCHAR2(1000);
l_po_header_id NUMBER;
l_po_line_id NUMBER;
receipt_detail_rec c_receipt_details%ROWTYPE;
l_vendor_id po_headers_all.vendor_id%TYPE;
l_trans_id NUMBER;
l_receipt_header_id NUMBER;

BEGIN

p_error_flag :='N';

BEGIN
SELECT po_header_id,vendor_id
INTO l_po_header_id,l_vendor_id
FROM po_headers_all
WHERE segment1 = p_rcv_tranc_rec.document_num
AND org_id = l_org_id;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid PO Number');
-- 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 => 'Invalid PO Number'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

BEGIN
SELECT shipment_header_id
INTO l_receipt_header_id
FROM rcv_shipment_headers
WHERE receipt_num = p_receipt_num
AND vendor_id =l_vendor_id;
EXCEPTION
WHEN OTHERS THEN
p_error_flag :='Y';
xx_trace.l ('Invalid Receipt Number');
-- 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-00044'
, p_identifier2 => 'Invalid Receipt Number'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

BEGIN
SELECT po_line_id
INTO l_po_line_id
FROM po_lines_all
WHERE po_header_id = l_po_header_id
AND line_num =p_rcv_tranc_rec.document_line_num;
EXCEPTION
WHEN No_Data_Found THEN
p_error_flag :='Y';
xx_trace.l ('Invalid PO Line');
-- 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-00045'
, p_identifier2 => 'Invalid PO Line'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;

OPEN c_receipt_details( p_rcv_tranc_rec.document_num,p_rcv_tranc_rec.document_line_num);
FETCH c_receipt_details INTO receipt_detail_rec;
CLOSE c_receipt_details;

IF p_rcv_tranc_rec.quantity > receipt_detail_rec.quantity THEN
p_error_flag :='Y';
xx_trace.l ('Return Quantity greater than line quantity');
-- 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-00046'
, p_identifier2 => 'Return Quantity greater than line quantity'
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;

IF p_error_flag <>'Y' THEN

SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_trans_id
FROM dual;

BEGIN
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
VALUES (
l_trans_id,
l_group_id,
sysdate,
p_rcv_tranc_rec.last_updated_by,
sysdate,
p_rcv_tranc_rec.created_by,
p_rcv_tranc_rec.last_update_login,
'RETURN TO VENDOR',
sysdate,
p_rcv_tranc_rec.processing_status_code,
p_rcv_tranc_rec.processing_mode_code,
p_rcv_tranc_rec.transaction_status_code,
p_rcv_tranc_rec.quantity,
receipt_detail_rec.unit_of_measure,
receipt_detail_rec.item_id,
receipt_detail_rec.employee_id,
l_receipt_header_id,
receipt_detail_rec.shipment_line_id,
p_rcv_tranc_rec.receipt_source_code,
receipt_detail_rec.vendor_id,
receipt_detail_rec.organization_id,
receipt_detail_rec.subinventory,
receipt_detail_rec.locator_id,
p_rcv_tranc_rec.source_document_code,
receipt_detail_rec.parent_transaction_id,
l_po_header_id,
l_po_line_id,
receipt_detail_rec.po_line_location_id,
receipt_detail_rec.po_distribution_id,
'INVENTORY',
NULL,
NULL,
receipt_detail_rec.deliver_to_location_id,
'Y'
);
EXCEPTION
WHEN OTHERS THEN
p_error_flag :='Y';
xx_trace.h ('Error in inserting Receipt Details'||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-00047'
, p_identifier2 => 'Error in inserting Receipt Details'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_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 in validating Receipt Details'||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-00048'
, p_identifier2 => 'Error in validating Receipt Details'||SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_identifier4 => p_receipt_num
--, p_identifier5 => l_org_code
-- , p_identifier6 => c_rcv_line_preint_rec. -- Can be utilized for displaying more information --
--p_identifier7 => NULL -- Can be utilized for displaying more information --
, p_identifier8 => p_rcv_tranc_rec.document_num
, p_process_status => l_return_value
, p_error_message => l_output_message
);

END;

END XX_POCNV03_RECTRANS_IMPORT_PKG;
/

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Sunday 8 January 2017

PO RETURN RECEIPTS Conversions

No comments :

Contact us