-- File Name : xpocnv01a.ctl
-- File Type : SQL*Loader Control file.
-- RICEW Object id : PO_CNV_01
-- Description : This SQL*Loader file is used to load data
-- from flat file to the staging table XX_PO_REQUISITIONS_STG
================================================================================================================
LOAD DATA
--
TRUNCATE
continueif last != '#'
INTO TABLE XX_PO_REQUISITIONS_STG
when rec_type ='REQ'
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(
REC_TYPE position(1:3) CHAR
, INTERFACE_SOURCE_CODE CHAR
, SOURCE_TYPE_CODE CHAR
, DESTINATION_TYPE_CODE CHAR
, AUTHORIZATION_STATUS CHAR
, ITEM_SEGMENT1 CHAR
, ITEM_DESCRIPTION CHAR
, CATEGORY_SEGMENT1 cHAR
, CATEGORY_SEGMENT2 cHAR
, LINE_TYPE CHAR
, UOM_CODE CHAR
, SOURCE_ORGANIZATION_CODE CHAR
, SOURCE_SUBINVENTORY CHAR
, NEED_BY_DATE DATE
, CURRENCY_CODE CHAR
, QUANTITY INTEGER EXTERNAL
, CHARGE_ACCOUNT_SEGMENT1 CHAR
, CHARGE_ACCOUNT_SEGMENT2 CHAR
, CHARGE_ACCOUNT_SEGMENT3 CHAR
, CHARGE_ACCOUNT_SEGMENT4 CHAR
, CHARGE_ACCOUNT_SEGMENT5 CHAR
, DESTINATION_ORGANIZATION_CODE CHAR
, DESTINATION_SUBINVENTORY CHAR
, DELIVER_TO_LOCATION_CODE CHAR
, DELIVER_TO_REQUESTOR_NAME CHAR
, PREPARER_NAME CHAR
, RATE INTEGER EXTERNAL
, RATE_DATE DATE
, RATE_TYPE CHAR
, CODE_OF_OPERATING_UNIT CHAR
, RECORD_ID "XX_PO_REQ_STG_RECORD_ID.nextval"
, LAST_UPDATE_DATE SYSDATE
, LAST_UPDATED_BY "fnd_global.user_id"
, CREATION_DATE SYSDATE
, CREATED_BY "fnd_global.user_id"
, LAST_UPDATE_LOGIN "fnd_global.login_id"
)
into table xx_po_req_file_stg
when rec_type='CRC'
fields terminated by '~'
trailing nullcols
(REC_TYPE position(1:3) char,
RECORD_COUNT integer external,
DESCRIPTION char,
STATUS CONSTANT 'NW',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id",
LAST_UPDATE_LOGIN "fnd_global.login_id"
)
CREATE OR REPLACE PACKAGE XX_POCNV01_REQ_LOAD_PKG
AS
/*
-------------------------------------------------------------
Package Name : XX_POCNV01_REQ_LOAD_PKG
Purpose : Package Specification
Program Style :
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
--
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
--
/*-----------------------------------------------------------
Global Variable Declaration Section
-------------------------------------------------------------*/
g_debug_level NUMBER := 50;
-- 50 is default, that is all messages(logged at level 10,20..50)
-- would be visible
g_retention_period NUMBER := 30;
-- This is the number of days for which error records are going
-- to be retained in the EMF tables
g_err_col1_width NUMBER := 10;
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
g_err_col2_width NUMBER := 30;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 10;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 20;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Main calling Procedure to Import Purchase Requisition
--
-------------------------------------------------------------*/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2);
--
END XX_POCNV01_REQ_LOAD_PKG;
/
CREATE OR REPLACE PACKAGE BODY xx_pocnv01_req_load_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : XX_POCNV01_REQ_LOAD_PKG
/*------------------------------------------------------------------------------------------------------
Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
--Cursor to select from Staging table xx_po_requisitions_stg
CURSOR c_po_requisition_control
IS
SELECT *
FROM xx_po_requisitions_stg
WHERE status = 'NW';
--Cursor to select from Pre-Interface table xx_po_requisition_preint
CURSOR c_po_requisition_validate
IS
SELECT *
FROM xx_po_requisition_preint
WHERE status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_return_status VARCHAR2 (1) := NULL;
l_stg_rec xx_po_requisitions_stg%ROWTYPE; -- Additional variable included
l_interface_stg_rec xx_po_requisition_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
l_dummy VARCHAR2 (100) := NULL; --Additional variable included
l_validation_status NUMBER := 0;
l_functional_currency VARCHAR2 (15);
l_destination_organization VARCHAR2 (3);
l_req_cnt NUMBER := 0;
l_rec_cnt NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_POCNV01_REQ_LOAD_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_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
l_proc_stg_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
l_succ_stg_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
l_err_stg_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
--l_validation_status VARCHAR2(1) := 'N';
l_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%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;
--Stop the program if record count does not match
e_record_count_error EXCEPTION;
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name,
p_program_type => g_program_type,
p_ricew_id => l_ricewid,
p_request_id => l_request_id
);
IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;
--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Error Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Record ID'; --Third Error Header
l_error_rec.identifier4 := 'Item Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Operating Unit'; --Fifth Error Header
l_error_rec.identifier6 := 'Destination Organization Code'; --Sixth Error Header
--l_error_rec.identifier7 := NULL; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
--
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
----------------------------------------------------------------------------------------------------------
IF p_run_mode = 'F'
THEN
/*---------------------------------------------------------------------------------------------------
File Validation
---------------------------------------------------------------------------------------------------*/
--the total no of requisitions loaded in staging table
SELECT COUNT (*)
INTO l_req_cnt
FROM xx_po_requisitions_stg
WHERE status = 'NW';
--
IF l_req_cnt > 0
THEN
--total records in data file
SELECT record_count
INTO l_rec_cnt
FROM xx_po_req_file_stg
WHERE status = 'NW';
--
END IF;
IF l_req_cnt <> l_rec_cnt
THEN
UPDATE xx_po_req_file_stg
SET status = 'ER';
--- Raise record count error and exit from the program
xx_trace.l ('Record count mismatch in the data file');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Record count mismatch.',
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_record_count_error;
END IF;
-----------------------------------------------------------------------------------------------------------------------
BEGIN -- 1st Begin
FOR c_po_req_rec IN c_po_requisition_control
LOOP
l_error_flag := 'N';
l_proc_stg_recs := l_proc_stg_recs + 1;
--Counter for total records
--
-- Validation of Interface Source Code
--
IF c_po_req_rec.interface_source_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Interface Source Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Interface Source Code cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
-- Validation of Destination Type Code
--
IF c_po_req_rec.destination_type_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Type Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Destination Type Code cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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
);
ELSIF c_po_req_rec.destination_type_code = 'EXPENSE'
THEN
IF c_po_req_rec.source_type_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Type Code cannot be null for EXPENSE Destination 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-00001',
p_identifier2 => 'Source Type Code cannot be null for EXPENSE Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
ELSIF c_po_req_rec.destination_type_code = 'INVENTORY'
THEN
IF c_po_req_rec.item_segment1 IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item cannot be null for INVENTORY Destination 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-00001',
p_identifier2 => 'ITEM cannot be null for INVENTORY Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF c_po_req_rec.destination_subinventory IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Subinventory cannot be null for INVENTORY Destination 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-00001',
p_identifier2 => 'Destination Subinventory cannot be null for INVENTORY Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
-- Validation of Source Type Code
--
IF c_po_req_rec.source_type_code = 'INVENTORY'
THEN
IF c_po_req_rec.item_segment1 IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item cannot be null for INVENTORY source 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-00001',
p_identifier2 => 'Item cannot be null for INVENTORY source Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF c_po_req_rec.source_organization_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Organization code cannot be null for INVENTORY source 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-00001',
p_identifier2 => 'Source Organization code cannot be null for INVENTORY source Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
--Validation of Authorization Status
--
IF c_po_req_rec.authorization_status IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Authorization Status cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Authorization Status cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Preparer name
--
IF c_po_req_rec.preparer_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Preparer Name cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Preparer Name cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Quantity
--
IF c_po_req_rec.quantity IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Quantity cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Quantity cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Charge Account Segments
--
IF ( c_po_req_rec.charge_account_segment1 IS NULL
OR c_po_req_rec.charge_account_segment2 IS NULL
OR c_po_req_rec.charge_account_segment3 IS NULL
OR c_po_req_rec.charge_account_segment4 IS NULL
OR c_po_req_rec.charge_account_segment5 IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Charge account segments should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Charge account segments should not be null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Destination Organization Code
--
IF c_po_req_rec.destination_organization_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Organization Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Destination Organization Code Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Deliver To Location Code
--
IF c_po_req_rec.deliver_to_location_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver To Location Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Deliver To Location Code Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Deliver To Requestor Name
--
IF c_po_req_rec.deliver_to_requestor_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver To requestor Name cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Deliver To requestor Name Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of rate,rate type,rate date for a Currency Code
--
IF c_po_req_rec.currency_code IS NOT NULL
THEN
IF ( c_po_req_rec.rate IS NULL
OR c_po_req_rec.rate_type IS NULL
OR c_po_req_rec.rate_date IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Rate or Rate Type or Rate Date should not be null for a currency.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Rate or Rate Type or Rate Date should not be null for a currency',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
--Validation of operating unit for muti org structure. For a single Org structure please comment out this validation
--
IF c_po_req_rec.code_of_operating_unit IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Operating Unit Should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Operating Unit should not be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
-----------------------------------------------------------------------------------------------
--Validation of line type
-----------------------------------------------------------------------------------------------
IF c_po_req_rec.line_type IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Line Type should not be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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
);
ELSE
BEGIN
--checking the line type whether it is AMOUNT type
l_dummy := NULL;
SELECT UPPER (plt.order_type_lookup_code)
INTO l_dummy
FROM po_line_types plt
WHERE plt.line_type = c_po_req_rec.line_type
AND plt.outside_operation_flag = 'N';
IF l_dummy = 'AMOUNT'
THEN
IF (c_po_req_rec.item_segment1 IS NOT NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('Item should be null for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Item should be null for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF (c_po_req_rec.uom_code IS NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('UOM Code can not be NULL for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'UOM Code can not be NULL for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF ( c_po_req_rec.category_segment1 IS NULL
OR c_po_req_rec.category_segment2 IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Category Segments should not be NULL for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Category Segments should not be NULL for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
-------------------
--Item Description validation
-------------------
IF (c_po_req_rec.item_description IS NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('Item description should not be NULL.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Item description should not be NULL',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error found to validate required fields for Amount based line Type'
|| SQLCODE
|| ''
|| SQLERRM
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Error found to validate required fields for Amount based line Type'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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;
-------------------------------------------------------------------------------------------------------------
--Updating the status of xx_po_requisitions_stg table for the records that fails the control validation
-------------------------------------------------------------------------------------------------------------
IF (l_error_flag = 'Y')
THEN
BEGIN
UPDATE xx_po_requisitions_stg
SET status = 'ER'
WHERE record_id = c_po_req_rec.record_id;
xx_trace.l ('Staging Table status updated to ER');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Status of Staging Table.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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;
ELSE
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-00001',
p_identifier2 => 'Error in Control Validation.'
|| SQLERRM,
p_identifier3 => NULL -- Can be utilized to store record serial number
--, p_identifier4 => c_item_rec.segment1
-- ,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; --1st Begin
--End of Control Validation
/*---------------------------------------------------------------------------------------------------
Load Data into Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_po_requisition_preint
(status, record_id, interface_source_code,
source_type_code, destination_type_code,
authorization_status, item_segment1,
item_description, category_segment1,
category_segment2, line_type, uom_code,
source_organization_code, source_subinventory,
need_by_date, currency_code, quantity,
charge_account_segment1, charge_account_segment2,
charge_account_segment3, charge_account_segment4,
charge_account_segment5,
destination_organization_code,
destination_subinventory, deliver_to_location_code,
deliver_to_requestor_name, preparer_name, rate,
rate_date, rate_type, code_of_operating_unit,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login)
SELECT 'NW', xx_poreq_preint_record_id.NEXTVAL,
interface_source_code, source_type_code,
destination_type_code, authorization_status,
item_segment1, item_description, category_segment1,
category_segment2, line_type, uom_code,
source_organization_code, source_subinventory,
need_by_date, currency_code, quantity,
charge_account_segment1, charge_account_segment2,
charge_account_segment3, charge_account_segment4,
charge_account_segment5, destination_organization_code,
destination_subinventory, deliver_to_location_code,
deliver_to_requestor_name, preparer_name, rate,
rate_date, rate_type, code_of_operating_unit, SYSDATE,
l_user_id, SYSDATE, l_user_id, l_user_id
FROM xx_po_requisitions_stg
WHERE status <> 'ER';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Exception While Inserting Into Pre-Interface table. Oracle Error:'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ROLLBACK;
RAISE;
END;
--2nd Begin
--End of Data Loading in Pre Interface Table
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;
END IF; --IF p_run_mode='F'
/*-----------------------------------------------------------------------------
Updating pre interface table with status 'IP' where record_status is 'NW'
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_po_requisition_preint
SET status = 'IP'
WHERE status = 'NW';
xx_trace.l ('Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --3rdBegin
/*----------------------------------------------------------------------------
--Business Validations--To be carried out for both full run mode(F) and partial run mode (P)
----------------------------------------------------------------------------*/
BEGIN --4 th Begin
FOR c_validate_po_req_rec IN c_po_requisition_validate
LOOP
--
l_error_flag := 'N';
l_processed_recs := l_processed_recs + 1;
--Counter for total records
-----------------------------------------------------------------------------------------------
--Validation of operating unit
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_interface_stg_rec.code_of_operating_unit :=
c_validate_po_req_rec.code_of_operating_unit;
xx_common_validations_pkg.validate_op_unit (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.code_of_operating_unit,
p_organization_id => l_interface_stg_rec.org_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF (l_validation_status = 1)
THEN
l_error_flag := 'Y';
xx_trace.l ( l_message
|| c_validate_po_req_rec.code_of_operating_unit
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message
|| c_validate_po_req_rec.code_of_operating_unit,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
UPDATE xx_po_requisition_preint
SET org_id = l_interface_stg_rec.org_id
WHERE record_id = c_validate_po_req_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('error while updating Org_id in pre interface 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-00003',
p_identifier2 => 'error while updating Org_id in pre interface table',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
COMMIT;
END;
-----------------------------------------------------------------------------------------------
--Validation of Authorization Status
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'AUTHORIZATION STATUS'
AND lookup_code = c_validate_po_req_rec.authorization_status;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Authorization Status is not valid: '
|| c_validate_po_req_rec.authorization_status
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Authorization Status is not valid: '
|| c_validate_po_req_rec.authorization_status,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Authorization Status' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Authorization Status'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of destination organization code
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_interface_stg_rec.destination_organization_code :=
c_validate_po_req_rec.destination_organization_code;
xx_common_validations_pkg.validate_organization (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.destination_organization_code,
p_organization_id => l_interface_stg_rec.destination_organization_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Invalid Destination Organization Code: '
|| c_validate_po_req_rec.destination_organization_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Destination Organization Code: '
|| c_validate_po_req_rec.destination_organization_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END;
-----------------------------------------------------------------------------------------------
--Validation of source organization code
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.source_organization_code IS NOT NULL
THEN
l_interface_stg_rec.source_organization_code :=
c_validate_po_req_rec.source_organization_code;
BEGIN
l_dummy := NULL;
xx_common_validations_pkg.validate_organization (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.source_organization_code,
p_organization_id => l_interface_stg_rec.source_organization_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Invalid Source Organization Code: '
|| c_validate_po_req_rec.source_organization_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Source Organization Code: '
|| c_validate_po_req_rec.source_organization_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Item
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.item_segment1 IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
l_destination_organization :=
l_interface_stg_rec.destination_organization_id;
xx_common_validations_pkg.validate_item_in_organization (p_item => c_validate_po_req_rec.item_segment1,
p_organization_code => c_validate_po_req_rec.destination_organization_code,
p_item_id => l_interface_stg_rec.item_id,
p_organization_id => l_destination_organization,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
--END;
ELSE
---------
--Validation of item Description
---------
IF c_validate_po_req_rec.item_description IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM mtl_system_items_b
WHERE inventory_item_id =
l_interface_stg_rec.item_id
AND organization_id =
l_interface_stg_rec.destination_organization_id
AND description =
l_interface_stg_rec.item_description;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Desciption is Invalid: '
|| c_validate_po_req_rec.item_description
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item Desciption is not valid: '
|| c_validate_po_req_rec.item_description,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Desciption'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Desciption'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
---------
--Validation of Category
---------
IF ( c_validate_po_req_rec.category_segment1 IS NOT NULL
AND c_validate_po_req_rec.category_segment2 IS NOT NULL
)
THEN
BEGIN
SELECT 'X'
INTO l_dummy
FROM mtl_item_categories mic,
mtl_categories_b mcb,
mtl_category_sets mcs
WHERE mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.structure_id = mcb.structure_id
AND mcs.validate_flag = 'Y'
AND mcb.enabled_flag = 'Y'
AND mcb.segment1 =
c_validate_po_req_rec.category_segment1
AND mcb.segment2 =
c_validate_po_req_rec.category_segment2
AND mic.inventory_item_id =
l_interface_stg_rec.item_id
AND mic.organization_id =
l_interface_stg_rec.destination_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category segments are not valid: '
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Category segments are not valid: '
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'category Segments'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Category segments'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
--------------
--Validation of Need by date
--------------
BEGIN
--checking for inventory planned item
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib, mfg_lookups ml
WHERE msib.inventory_planning_code = ml.lookup_code
AND msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (ml.meaning) = UPPER ('Min-max planning')
AND ml.lookup_type = 'MTL_MATERIAL_PLANNING';
IF c_validate_po_req_rec.need_by_date IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date can not be null for inventory planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date can not be null for inventory planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
IF c_validate_po_req_rec.need_by_date < SYSDATE
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date is lower than sysdate for inventory planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date is lower than sysdate for inventory planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
--checking for MRP Planned Item
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib, mfg_lookups ml
WHERE msib.mrp_planning_code = ml.lookup_code
AND msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (ml.meaning) =
UPPER ('MRP planning')
AND ml.lookup_type = 'MRP_PLANNING_CODE';
IF c_validate_po_req_rec.need_by_date IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date can not be null for MRP planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date can not be null for MRP planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
IF c_validate_po_req_rec.need_by_date < SYSDATE
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date is lower than sysdate for MRP planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date is lower than sysdate for MRP planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error in checking of MRP planning'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Error in checking of MRP planning'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error in checking of inventory planning'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Error in checking of inventory planning'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END;
--
ELSE
--Category validation
IF ( c_validate_po_req_rec.category_segment1 IS NOT NULL
AND c_validate_po_req_rec.category_segment2 IS NOT NULL
)
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_categories_b
WHERE segment1 = c_validate_po_req_rec.category_segment1
AND segment2 = c_validate_po_req_rec.category_segment2
AND enabled_flag = 'Y'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category is invalid:'
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Category is invalid:'
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category is invalid'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'category is invalid'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Charge Account
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_dummy :=
xx_common_validations_pkg.get_coa_ccid (p_segment1 => c_validate_po_req_rec.charge_account_segment1,
p_segment2 => c_validate_po_req_rec.charge_account_segment2,
p_segment3 => c_validate_po_req_rec.charge_account_segment3,
p_segment4 => c_validate_po_req_rec.charge_account_segment4,
p_segment5 => c_validate_po_req_rec.charge_account_segment5,
p_process_status => l_validation_status,
p_error_message => l_message,
p_ccid => l_interface_stg_rec.charge_account_id
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
SELECT 'X'
INTO l_dummy
FROM mtl_parameters mp
WHERE mp.organization_id =
l_interface_stg_rec.destination_organization_id
AND mp.material_account =
l_interface_stg_rec.charge_account_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Charge account is invalid');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Charge account is invalid',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ('Can not have more than one charge account for destination organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Can not have more than one charge account for destination organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Charge Account ID '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Charge Account ID '
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END;
-----------------------------------------------------------------------------------------------
--Validation of inventory source type
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.source_type_code IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'REQUISITION SOURCE TYPE'
AND lookup_code = c_validate_po_req_rec.source_type_code;
IF (UPPER (c_validate_po_req_rec.source_type_code) =
'INVENTORY'
)
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_shipping_network_view msnv
WHERE msnv.from_organization_id IN (
SELECT ood.organization_id
FROM org_organization_definitions ood
WHERE ood.organization_id IN (
SELECT msib.organization_id
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.mtl_transactions_enabled_flag =
'Y')
AND ood.operating_unit =
l_interface_stg_rec.org_id)
AND msnv.to_organization_id =
l_interface_stg_rec.destination_organization_id
AND msnv.from_organization_id =
l_interface_stg_rec.source_organization_id;
IF l_dummy IS NOT NULL
THEN
--Checking Item Stock enabled for source Organization
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.source_organization_id
AND msib.stock_enabled_flag = 'Y';
IF (c_validate_po_req_rec.source_subinventory IS NOT NULL
)
THEN
BEGIN
--Checking Source Subinventory
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib,
mfg_lookups ml,
mtl_item_sub_inventories misi
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.inventory_item_id =
misi.inventory_item_id
AND misi.organization_id =
misi.organization_id
AND msib.organization_id =
l_interface_stg_rec.source_organization_id
AND UPPER (misi.secondary_inventory) =
UPPER (c_validate_po_req_rec.source_subinventory
)
AND UPPER (ml.meaning) =
UPPER ('Subinventories not restricted to pre-defined list'
)
AND ml.lookup_type LIKE
'MTL_SUBINVENTORY_RESTRICTIONS'
AND ml.lookup_code =
msib.restrict_subinventories_code;
EXCEPTION
--Exception of Subinventory
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is restricted to 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-00003',
p_identifier2 => 'Item is restricted to subinventory',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Source Subinventory '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item-Subinventory Restriction'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Stock Enabling Exception
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not stock enable in source organization.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item is not stock enable in source organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Stock enabled '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Stock Enabled'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
BEGIN
--Checking Internal Order Enabled for destination Organization
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND msib.internal_order_enabled_flag = 'Y';
BEGIN
--Checking the Deliver to Loaction
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM hr_locations_all hrl
WHERE UPPER (hrl.ship_to_site_flag) = 'Y'
AND hrl.in_organization_flag = 'Y'
AND hrl.inventory_organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (hrl.location_code) =
UPPER (c_validate_po_req_rec.deliver_to_location_code
)
AND NVL (hrl.inactive_date, SYSDATE) >=
SYSDATE;
EXCEPTION
--Exception due to checking the deliver to location
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver to location code does not exist'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Deliver to location code does not exist',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to location code does not exist'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Deliver to location code does not exist'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Exception for Internal Order checking
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Should be internal Order enabled.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item Should be internal Order enabled.',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Should be internal Order enabled.'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Should be internal Order enabled.'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Exception for shipping network
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Shipping network does not exist between source and Destination Organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Shipping network does not exist between source and destination Organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Shipping network does not exist '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Shipping network does not exist'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
ELSIF (UPPER (c_validate_po_req_rec.source_type_code) =
'VENDOR'
)
THEN
IF l_interface_stg_rec.item_id IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND purchasing_enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not purchasing enabled in destination org for VENDOR source 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-00003',
p_identifier2 => 'Item is not purchasing enabled in destination org for VENDOR source type',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is not purchasing enabled for VENDOR source type'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is not purchasing enabled '
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Source Type Code is invalid: '
|| c_validate_po_req_rec.source_type_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Source Type Code is invalid: '
|| c_validate_po_req_rec.source_type_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Type Code' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Source Type Code'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of Destination Type
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'DESTINATION TYPE'
AND lookup_code =
c_validate_po_req_rec.destination_type_code;
IF (UPPER (c_validate_po_req_rec.destination_type_code) =
'INVENTORY'
)
THEN
BEGIN
--checking Stock enabled flag for destination org
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND msib.stock_enabled_flag = 'Y';
IF (c_validate_po_req_rec.destination_subinventory IS NOT NULL
)
THEN
BEGIN
--checking restriction of the item in destination subinventory
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib,
mfg_lookups ml,
mtl_item_sub_inventories misi
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.inventory_item_id =
misi.inventory_item_id
AND misi.organization_id = misi.organization_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (misi.secondary_inventory) =
UPPER (c_validate_po_req_rec.destination_subinventory
)
AND UPPER (ml.meaning) =
UPPER ('Subinventories not restricted to pre-defined list'
)
AND ml.lookup_type LIKE
'MTL_SUBINVENTORY_RESTRICTIONS'
AND ml.lookup_code =
msib.restrict_subinventories_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is restricted in the destination Subinventory:'
|| c_validate_po_req_rec.destination_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-00003',
p_identifier2 => 'Item is restricted in the destination Subinventory:'
|| c_validate_po_req_rec.destination_subinventory,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is restricted in the destination Subinventory'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is restricted in the destination Subinventory'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not stock enable in destination organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item is not stock enable in destination organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is not stock enable in destination organization'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is not stock enable in destination organization'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Destination Type Code is invalid: '
|| c_validate_po_req_rec.destination_type_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Destination Type Code is invalid: '
|| c_validate_po_req_rec.destination_type_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Type Code' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Destination Type Code'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of AMOUNT based line type
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.line_type IS NOT NULL
THEN
BEGIN
--checking the line type whether it is AMOUNT type
l_dummy := NULL;
SELECT UPPER (plt.order_type_lookup_code)
INTO l_dummy
FROM po_line_types plt
WHERE plt.line_type = c_validate_po_req_rec.line_type
AND plt.outside_operation_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type is invalid');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Line Type is invalid',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Line Type is invalid'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Line Type id is invalid'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of UOM Code
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.uom_code IS NOT NULL
THEN
xx_common_validations_pkg.validate_uom (p_unit_of_measure => c_validate_po_req_rec.unit_of_measure,
p_uom_code => c_validate_po_req_rec.uom_code,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Currency
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.currency_code IS NOT NULL
THEN
l_dummy := NULL;
l_dummy :=
xx_common_validations_pkg.validate_currency_code (p_currency_code => c_validate_po_req_rec.currency_code,
p_process_status => l_validation_status,
p_error_message => l_message
);
--Fetching functional Currency
BEGIN
SELECT currency_code
INTO l_functional_currency
FROM gl_sets_of_books
WHERE set_of_books_id =
fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Functional Currency is not defined for the set of Book'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Functional Currency is not defined for the set of Book',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Functional Currency'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Functional Currency'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
--Validating rate type
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM gl_daily_conversion_types
WHERE user_conversion_type =
c_validate_po_req_rec.rate_type;
--validating whether daily rate exists for the given rate tpe and date
IF ( c_validate_po_req_rec.currency_code <>
l_functional_currency
AND c_validate_po_req_rec.rate_type <> 'User'
)
THEN
xx_common_validations_pkg.get_currency_rate (p_currency_from => l_functional_currency,
p_currency_to => c_validate_po_req_rec.currency_code,
p_conversion_date => c_validate_po_req_rec.rate_date,
p_conversion_type => c_validate_po_req_rec.rate_type,
p_rate => l_interface_stg_rec.rate,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END IF;
--
EXCEPTION
--Exception while validating rate tpe
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Rate Type is invalid: '
|| c_validate_po_req_rec.rate_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-00003',
p_identifier2 => 'Rate Type is invalid: '
|| c_validate_po_req_rec.rate_type,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Rate Type Validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Rate Type Validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Preparer Name
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.preparer_name IS NOT NULL
THEN
BEGIN
SELECT hre.employee_id
INTO l_interface_stg_rec.preparer_id
FROM hr_employees hre, po_agents pa
WHERE UPPER (hre.full_name) =
UPPER (TRIM (c_validate_po_req_rec.preparer_name))
AND pa.agent_id = hre.employee_id
AND NVL (pa.end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Preparer Name is invalid:'
|| c_validate_po_req_rec.preparer_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Preparer Name is invalid:'
|| c_validate_po_req_rec.preparer_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'More than one same Preparer name exist:'
|| c_validate_po_req_rec.preparer_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'More than one same Preparer name exist:'
|| c_validate_po_req_rec.preparer_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Preparer Name validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Preparer Name validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of Deliver to Requestor Name
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.deliver_to_requestor_name IS NOT NULL
THEN
BEGIN
SELECT hre.employee_id
INTO l_interface_stg_rec.deliver_to_requestor_id
FROM hr_employees hre, po_agents pa
WHERE UPPER (hre.full_name) =
UPPER (TRIM (c_validate_po_req_rec.deliver_to_requestor_name
)
)
AND pa.agent_id = hre.employee_id
AND NVL (pa.end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to Requestor Name is invalid:'
|| c_validate_po_req_rec.deliver_to_requestor_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Deliver to Requestor Name is invalid:'
|| c_validate_po_req_rec.deliver_to_requestor_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'More than one same Deliver to Requestor Name exist:'
|| c_validate_po_req_rec.deliver_to_requestor_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'More than one same Deliver to Requestor Name exist:'
|| c_validate_po_req_rec.deliver_to_requestor_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to Requestor Name validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Deliver to Requestor Name validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' for errorneous records
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN --4.5 th Begin
UPDATE xx_po_requisition_preint
SET status = 'ER'
WHERE record_id = c_validate_po_req_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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; --4.5 th Begin
ELSE
l_successful_recs := l_successful_recs + 1;
END IF;
l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.0 th Begin
--End of Business Validation Section
/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN --5th Begin
INSERT INTO po_requisitions_interface_all
(transaction_id, process_flag, request_id, program_id,
program_application_id, program_update_date,
last_updated_by, last_update_date, last_update_login,
creation_date, created_by, interface_source_code,
interface_source_line_id, source_type_code,
requisition_header_id, requisition_line_id,
req_distribution_id, requisition_type,
destination_type_code, item_description, quantity,
unit_price, authorization_status, batch_id, group_code,
delete_enabled_flag, update_enabled_flag, approver_id,
approver_name, approval_path_id, note_to_approver,
preparer_id, autosource_flag, req_number_segment1,
req_number_segment2, req_number_segment3,
req_number_segment4, req_number_segment5,
header_description, header_attribute_category,
header_attribute1, header_attribute2,
header_attribute3, header_attribute4,
header_attribute5, header_attribute6,
header_attribute7, header_attribute8,
header_attribute9, header_attribute10,
header_attribute11, header_attribute12,
header_attribute13, header_attribute14, urgent_flag,
header_attribute15, rfq_required_flag, justification,
note_to_buyer, note_to_receiver, item_id,
item_segment1, item_segment2, item_segment3,
item_segment4, item_segment5, item_segment6,
item_segment7, item_segment8, item_segment9,
item_segment10, item_segment11, item_segment12,
item_segment13, item_segment14, item_segment15,
item_segment16, item_segment17, item_segment18,
item_segment19, item_segment20, item_revision,
charge_account_id, charge_account_segment1,
charge_account_segment2, charge_account_segment3,
charge_account_segment4, charge_account_segment5,
charge_account_segment6, charge_account_segment7,
charge_account_segment8, charge_account_segment9,
charge_account_segment10, charge_account_segment11,
charge_account_segment12, charge_account_segment13,
charge_account_segment14, charge_account_segment15,
charge_account_segment16, charge_account_segment17,
charge_account_segment18, charge_account_segment19,
charge_account_segment20, charge_account_segment21,
charge_account_segment22, charge_account_segment23,
charge_account_segment24, charge_account_segment25,
charge_account_segment26, charge_account_segment27,
charge_account_segment28, charge_account_segment29,
charge_account_segment30, category_id,
category_segment1, category_segment2,
category_segment3, category_segment4,
category_segment5, category_segment6,
category_segment7, category_segment8,
category_segment9, category_segment10,
category_segment11, category_segment12,
category_segment13, category_segment14,
category_segment15, category_segment16,
category_segment17, category_segment18,
category_segment19, category_segment20,
unit_of_measure, uom_code, line_type_id, line_type,
un_number_id, un_number, hazard_class_id, hazard_class,
must_use_sugg_vendor_flag, reference_num,
wip_entity_id, wip_line_id, wip_operation_seq_num,
wip_resource_seq_num, wip_repetitive_schedule_id,
project_num, task_num, expenditure_type,
source_organization_id, source_organization_code,
source_subinventory, destination_organization_id,
destination_organization_code,
destination_subinventory, deliver_to_location_id,
deliver_to_location_code, deliver_to_requestor_id,
deliver_to_requestor_name, suggested_buyer_id,
suggested_buyer_name, suggested_vendor_name,
suggested_vendor_id, suggested_vendor_site,
suggested_vendor_site_id, suggested_vendor_contact,
suggested_vendor_contact_id, suggested_vendor_phone,
suggested_vendor_item_num, line_attribute_category,
line_attribute1, line_attribute2, line_attribute3,
line_attribute4, line_attribute5, line_attribute6,
line_attribute7, line_attribute8, line_attribute9,
line_attribute10, line_attribute11, line_attribute12,
line_attribute13, line_attribute14, line_attribute15,
need_by_date, note1_id, note2_id, note3_id, note4_id,
note5_id, note6_id, note7_id, note8_id, note9_id,
note10_id, note1_title, note2_title, note3_title,
note4_title, note5_title, note6_title, note7_title,
note8_title, note9_title, note10_title, gl_date,
dist_attribute_category, distribution_attribute1,
distribution_attribute2, distribution_attribute3,
distribution_attribute4, distribution_attribute5,
distribution_attribute6, distribution_attribute7,
distribution_attribute8, distribution_attribute9,
distribution_attribute10, distribution_attribute11,
distribution_attribute12, distribution_attribute13,
distribution_attribute14, distribution_attribute15,
preparer_name, bom_resource_id, accrual_account_id,
variance_account_id, budget_account_id,
ussgl_transaction_code, government_context,
currency_code, currency_unit_price, rate, rate_date,
rate_type, prevent_encumbrance_flag,
autosource_doc_header_id, autosource_doc_line_num,
project_accounting_context,
expenditure_organization_id, project_id, task_id,
expenditure_item_date, document_type_code, org_id,
transaction_reason_code, allocation_type,
allocation_value, multi_distributions,
req_dist_sequence_id, kanban_card_id, emergency_po_num,
award_id, end_item_unit_number, tax_code_id,
oke_contract_header_id, oke_contract_num,
oke_contract_version_id, oke_contract_line_id,
oke_contract_line_num, oke_contract_deliverable_id,
oke_contract_deliverable_num,
secondary_unit_of_measure, secondary_uom_code,
secondary_quantity, preferred_grade, vmi_flag,
tax_user_override_flag, amount, currency_amount,
ship_method, estimated_pickup_date, base_unit_price,
negotiated_by_preparer_flag)
SELECT transaction_id, process_flag, request_id, program_id,
program_application_id, program_update_date,
last_updated_by, last_update_date, last_update_login,
creation_date, created_by, interface_source_code,
interface_source_line_id, source_type_code,
requisition_header_id, requisition_line_id,
req_distribution_id, requisition_type,
destination_type_code, item_description, quantity,
unit_price, authorization_status, batch_id, group_code,
delete_enabled_flag, update_enabled_flag, approver_id,
approver_name, approval_path_id, note_to_approver,
preparer_id, autosource_flag, req_number_segment1,
req_number_segment2, req_number_segment3,
req_number_segment4, req_number_segment5,
header_description, header_attribute_category,
header_attribute1, header_attribute2, header_attribute3,
header_attribute4, header_attribute5, header_attribute6,
header_attribute7, header_attribute8, header_attribute9,
header_attribute10, header_attribute11,
header_attribute12, header_attribute13,
header_attribute14, urgent_flag, header_attribute15,
rfq_required_flag, justification, note_to_buyer,
note_to_receiver, item_id, item_segment1, item_segment2,
item_segment3, item_segment4, item_segment5,
item_segment6, item_segment7, item_segment8,
item_segment9, item_segment10, item_segment11,
item_segment12, item_segment13, item_segment14,
item_segment15, item_segment16, item_segment17,
item_segment18, item_segment19, item_segment20,
item_revision, charge_account_id, charge_account_segment1,
charge_account_segment2, charge_account_segment3,
charge_account_segment4, charge_account_segment5,
charge_account_segment6, charge_account_segment7,
charge_account_segment8, charge_account_segment9,
charge_account_segment10, charge_account_segment11,
charge_account_segment12, charge_account_segment13,
charge_account_segment14, charge_account_segment15,
charge_account_segment16, charge_account_segment17,
charge_account_segment18, charge_account_segment19,
charge_account_segment20, charge_account_segment21,
charge_account_segment22, charge_account_segment23,
charge_account_segment24, charge_account_segment25,
charge_account_segment26, charge_account_segment27,
charge_account_segment28, charge_account_segment29,
charge_account_segment30, category_id, category_segment1,
category_segment2, category_segment3, category_segment4,
category_segment5, category_segment6, category_segment7,
category_segment8, category_segment9, category_segment10,
category_segment11, category_segment12,
category_segment13, category_segment14,
category_segment15, category_segment16,
category_segment17, category_segment18,
category_segment19, category_segment20, unit_of_measure,
uom_code, line_type_id, line_type, un_number_id,
un_number, hazard_class_id, hazard_class,
must_use_sugg_vendor_flag, reference_num, wip_entity_id,
wip_line_id, wip_operation_seq_num, wip_resource_seq_num,
wip_repetitive_schedule_id, project_num, task_num,
expenditure_type, source_organization_id,
source_organization_code, source_subinventory,
destination_organization_id,
destination_organization_code, destination_subinventory,
deliver_to_location_id, deliver_to_location_code,
deliver_to_requestor_id, deliver_to_requestor_name,
suggested_buyer_id, suggested_buyer_name,
suggested_vendor_name, suggested_vendor_id,
suggested_vendor_site, suggested_vendor_site_id,
suggested_vendor_contact, suggested_vendor_contact_id,
suggested_vendor_phone, suggested_vendor_item_num,
line_attribute_category, line_attribute1, line_attribute2,
line_attribute3, line_attribute4, line_attribute5,
line_attribute6, line_attribute7, line_attribute8,
line_attribute9, line_attribute10, line_attribute11,
line_attribute12, line_attribute13, line_attribute14,
line_attribute15, need_by_date, note1_id, note2_id,
note3_id, note4_id, note5_id, note6_id, note7_id,
note8_id, note9_id, note10_id, note1_title, note2_title,
note3_title, note4_title, note5_title, note6_title,
note7_title, note8_title, note9_title, note10_title,
gl_date, dist_attribute_category, distribution_attribute1,
distribution_attribute2, distribution_attribute3,
distribution_attribute4, distribution_attribute5,
distribution_attribute6, distribution_attribute7,
distribution_attribute8, distribution_attribute9,
distribution_attribute10, distribution_attribute11,
distribution_attribute12, distribution_attribute13,
distribution_attribute14, distribution_attribute15,
preparer_name, bom_resource_id, accrual_account_id,
variance_account_id, budget_account_id,
ussgl_transaction_code, government_context, currency_code,
currency_unit_price, rate, rate_date, rate_type,
prevent_encumbrance_flag, autosource_doc_header_id,
autosource_doc_line_num, project_accounting_context,
expenditure_organization_id, project_id, task_id,
expenditure_item_date, document_type_code, org_id,
transaction_reason_code, allocation_type,
allocation_value, multi_distributions,
req_dist_sequence_id, kanban_card_id, emergency_po_num,
award_id, end_item_unit_number, tax_code_id,
oke_contract_header_id, oke_contract_num,
oke_contract_version_id, oke_contract_line_id,
oke_contract_line_num, oke_contract_deliverable_id,
oke_contract_deliverable_num, secondary_unit_of_measure,
secondary_uom_code, secondary_quantity, preferred_grade,
vmi_flag, tax_user_override_flag, amount, currency_amount,
ship_method, estimated_pickup_date, base_unit_price,
negotiated_by_preparer_flag
FROM xx_po_requisition_preint
WHERE status = 'IP';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Exception While Inserting into PO_REQUISITIONS_INTERFACE_ALL.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Inserting into PO_REQUISITIONS_INTERFACE_ALL.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; --5 th Begin
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN --6 th Begin
UPDATE xx_po_requisition_preint
SET status = 'PR'
WHERE status = 'IP';
xx_trace.l ('Pre-Interface Table status updated to PR');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --6th Begin
/*---------------------------------------------------------------------------------------
Apps Initialisation
----------------------------------------------------------------------------------------*/
xx_common_validations_pkg.init_apps_params (p_request_id => l_request_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF (l_validation_status = 1)
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message--, p_identifier3 => c_validate_po_req_rec.record_id
--, p_identifier4 => c_validate_po_req_rec.item_segment1
--, p_identifier5 => c_validate_po_req_rec.item_id
--, p_identifier6 => c_validate_po_req_rec.org_id
--, p_identifier7 => c_validate_po_req_rec.destination_organization_id
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN --7th Begin
SELECT COUNT (*)
INTO l_count
FROM xx_po_requisition_preint
WHERE status = 'PR';
IF l_count > 0
THEN
l_standard_request_id :=
fnd_request.submit_request (application => 'PO',
program => 'REQIMPORT',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => '',
argument2 => '',
argument3 => 'ITEM',
argument4 => '',
argument5 => 'N',
argument6 => 'Y'
);
COMMIT;
--Wait for the completion of the concurrent request (if submitted successfully)
l_completed :=
fnd_concurrent.wait_for_request (request_id => l_standard_request_id,
INTERVAL => 60,
max_wait => 0,
phase => l_phase,
status => l_vstatus,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
MESSAGE => l_message
);
xx_trace.l ( 'Request submitted with request id-'
|| l_standard_request_id
);
ELSE
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 --Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'No records To Process in Interface Table',
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END; --7 th Begin
END IF;
/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_po_requisition_preint
WHERE status = 'PR';
--
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h ( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
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');
WHEN e_record_count_error
THEN
--Write to error
l_error_flag := 'Y';
--
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_req_cnt,
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;
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 --High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_processed_recs,
p_successful_recs => l_successful_recs,
p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20 --Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
--
END xx_pocnv01_req_load_pkg;
/
-- File Type : SQL*Loader Control file.
-- RICEW Object id : PO_CNV_01
-- Description : This SQL*Loader file is used to load data
-- from flat file to the staging table XX_PO_REQUISITIONS_STG
================================================================================================================
LOAD DATA
--
TRUNCATE
continueif last != '#'
INTO TABLE XX_PO_REQUISITIONS_STG
when rec_type ='REQ'
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(
REC_TYPE position(1:3) CHAR
, INTERFACE_SOURCE_CODE CHAR
, SOURCE_TYPE_CODE CHAR
, DESTINATION_TYPE_CODE CHAR
, AUTHORIZATION_STATUS CHAR
, ITEM_SEGMENT1 CHAR
, ITEM_DESCRIPTION CHAR
, CATEGORY_SEGMENT1 cHAR
, CATEGORY_SEGMENT2 cHAR
, LINE_TYPE CHAR
, UOM_CODE CHAR
, SOURCE_ORGANIZATION_CODE CHAR
, SOURCE_SUBINVENTORY CHAR
, NEED_BY_DATE DATE
, CURRENCY_CODE CHAR
, QUANTITY INTEGER EXTERNAL
, CHARGE_ACCOUNT_SEGMENT1 CHAR
, CHARGE_ACCOUNT_SEGMENT2 CHAR
, CHARGE_ACCOUNT_SEGMENT3 CHAR
, CHARGE_ACCOUNT_SEGMENT4 CHAR
, CHARGE_ACCOUNT_SEGMENT5 CHAR
, DESTINATION_ORGANIZATION_CODE CHAR
, DESTINATION_SUBINVENTORY CHAR
, DELIVER_TO_LOCATION_CODE CHAR
, DELIVER_TO_REQUESTOR_NAME CHAR
, PREPARER_NAME CHAR
, RATE INTEGER EXTERNAL
, RATE_DATE DATE
, RATE_TYPE CHAR
, CODE_OF_OPERATING_UNIT CHAR
, RECORD_ID "XX_PO_REQ_STG_RECORD_ID.nextval"
, LAST_UPDATE_DATE SYSDATE
, LAST_UPDATED_BY "fnd_global.user_id"
, CREATION_DATE SYSDATE
, CREATED_BY "fnd_global.user_id"
, LAST_UPDATE_LOGIN "fnd_global.login_id"
)
into table xx_po_req_file_stg
when rec_type='CRC'
fields terminated by '~'
trailing nullcols
(REC_TYPE position(1:3) char,
RECORD_COUNT integer external,
DESCRIPTION char,
STATUS CONSTANT 'NW',
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id",
LAST_UPDATE_LOGIN "fnd_global.login_id"
)
CREATE OR REPLACE PACKAGE XX_POCNV01_REQ_LOAD_PKG
AS
/*
-------------------------------------------------------------
Package Name : XX_POCNV01_REQ_LOAD_PKG
Purpose : Package Specification
Program Style :
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
--
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
--
/*-----------------------------------------------------------
Global Variable Declaration Section
-------------------------------------------------------------*/
g_debug_level NUMBER := 50;
-- 50 is default, that is all messages(logged at level 10,20..50)
-- would be visible
g_retention_period NUMBER := 30;
-- This is the number of days for which error records are going
-- to be retained in the EMF tables
g_err_col1_width NUMBER := 10;
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
g_err_col2_width NUMBER := 30;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 10;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 20;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Main calling Procedure to Import Purchase Requisition
--
-------------------------------------------------------------*/
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2);
--
END XX_POCNV01_REQ_LOAD_PKG;
/
CREATE OR REPLACE PACKAGE BODY xx_pocnv01_req_load_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : XX_POCNV01_REQ_LOAD_PKG
/*------------------------------------------------------------------------------------------------------
Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
--Cursor to select from Staging table xx_po_requisitions_stg
CURSOR c_po_requisition_control
IS
SELECT *
FROM xx_po_requisitions_stg
WHERE status = 'NW';
--Cursor to select from Pre-Interface table xx_po_requisition_preint
CURSOR c_po_requisition_validate
IS
SELECT *
FROM xx_po_requisition_preint
WHERE status = 'IP';
/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_seq_val NUMBER;
l_standard_request_id fnd_concurrent_requests.request_id%TYPE
:= 0;
l_completed BOOLEAN;
l_process_status NUMBER;
l_return_status VARCHAR2 (1) := NULL;
l_stg_rec xx_po_requisitions_stg%ROWTYPE; -- Additional variable included
l_interface_stg_rec xx_po_requisition_preint%ROWTYPE;
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
l_dummy VARCHAR2 (100) := NULL; --Additional variable included
l_validation_status NUMBER := 0;
l_functional_currency VARCHAR2 (15);
l_destination_organization VARCHAR2 (3);
l_req_cnt NUMBER := 0;
l_rec_cnt NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_POCNV01_REQ_LOAD_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_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
l_proc_stg_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
l_succ_stg_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
l_err_stg_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
--l_validation_status VARCHAR2(1) := 'N';
l_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_user_id fnd_concurrent_requests.requested_by%TYPE;
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%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;
--Stop the program if record count does not match
e_record_count_error EXCEPTION;
BEGIN --Main Begin
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name,
p_program_type => g_program_type,
p_ricew_id => l_ricewid,
p_request_id => l_request_id
);
IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;
--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Error Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Record ID'; --Third Error Header
l_error_rec.identifier4 := 'Item Number'; --Fourth Error Header
l_error_rec.identifier5 := 'Operating Unit'; --Fifth Error Header
l_error_rec.identifier6 := 'Destination Organization Code'; --Sixth Error Header
--l_error_rec.identifier7 := NULL; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);
--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;
--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);
--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;
--
/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/
--Run mode will decide if the conversion will be run in full run mode 'F' or Partial run mode 'P'.
--IF p_run_mode is 'F the coversion starts with control validation for records with Status 'NW'in staging table
--IF p_run_mode is 'P' the coversion starts with business validation for records with Status 'IP''
--in the pre-interface table. The errored out records of previous run can be fixed in the pre-interface table
--if required and then this program can be run in partial mode .
----------------------------------------------------------------------------------------------------------
IF p_run_mode = 'F'
THEN
/*---------------------------------------------------------------------------------------------------
File Validation
---------------------------------------------------------------------------------------------------*/
--the total no of requisitions loaded in staging table
SELECT COUNT (*)
INTO l_req_cnt
FROM xx_po_requisitions_stg
WHERE status = 'NW';
--
IF l_req_cnt > 0
THEN
--total records in data file
SELECT record_count
INTO l_rec_cnt
FROM xx_po_req_file_stg
WHERE status = 'NW';
--
END IF;
IF l_req_cnt <> l_rec_cnt
THEN
UPDATE xx_po_req_file_stg
SET status = 'ER';
--- Raise record count error and exit from the program
xx_trace.l ('Record count mismatch in the data file');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Record count mismatch.',
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_record_count_error;
END IF;
-----------------------------------------------------------------------------------------------------------------------
BEGIN -- 1st Begin
FOR c_po_req_rec IN c_po_requisition_control
LOOP
l_error_flag := 'N';
l_proc_stg_recs := l_proc_stg_recs + 1;
--Counter for total records
--
-- Validation of Interface Source Code
--
IF c_po_req_rec.interface_source_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Interface Source Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Interface Source Code cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
-- Validation of Destination Type Code
--
IF c_po_req_rec.destination_type_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Type Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Destination Type Code cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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
);
ELSIF c_po_req_rec.destination_type_code = 'EXPENSE'
THEN
IF c_po_req_rec.source_type_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Type Code cannot be null for EXPENSE Destination 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-00001',
p_identifier2 => 'Source Type Code cannot be null for EXPENSE Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
ELSIF c_po_req_rec.destination_type_code = 'INVENTORY'
THEN
IF c_po_req_rec.item_segment1 IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item cannot be null for INVENTORY Destination 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-00001',
p_identifier2 => 'ITEM cannot be null for INVENTORY Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF c_po_req_rec.destination_subinventory IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Subinventory cannot be null for INVENTORY Destination 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-00001',
p_identifier2 => 'Destination Subinventory cannot be null for INVENTORY Destination Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
-- Validation of Source Type Code
--
IF c_po_req_rec.source_type_code = 'INVENTORY'
THEN
IF c_po_req_rec.item_segment1 IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Item cannot be null for INVENTORY source 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-00001',
p_identifier2 => 'Item cannot be null for INVENTORY source Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF c_po_req_rec.source_organization_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Organization code cannot be null for INVENTORY source 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-00001',
p_identifier2 => 'Source Organization code cannot be null for INVENTORY source Type.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
--Validation of Authorization Status
--
IF c_po_req_rec.authorization_status IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Authorization Status cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Authorization Status cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Preparer name
--
IF c_po_req_rec.preparer_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Preparer Name cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Preparer Name cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Quantity
--
IF c_po_req_rec.quantity IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Quantity cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Quantity cannot be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Charge Account Segments
--
IF ( c_po_req_rec.charge_account_segment1 IS NULL
OR c_po_req_rec.charge_account_segment2 IS NULL
OR c_po_req_rec.charge_account_segment3 IS NULL
OR c_po_req_rec.charge_account_segment4 IS NULL
OR c_po_req_rec.charge_account_segment5 IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Charge account segments should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Charge account segments should not be null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Destination Organization Code
--
IF c_po_req_rec.destination_organization_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Organization Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Destination Organization Code Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Deliver To Location Code
--
IF c_po_req_rec.deliver_to_location_code IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver To Location Code cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Deliver To Location Code Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of Deliver To Requestor Name
--
IF c_po_req_rec.deliver_to_requestor_name IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver To requestor Name cannot be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Deliver To requestor Name Can not be Null',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
--
--Validation of rate,rate type,rate date for a Currency Code
--
IF c_po_req_rec.currency_code IS NOT NULL
THEN
IF ( c_po_req_rec.rate IS NULL
OR c_po_req_rec.rate_type IS NULL
OR c_po_req_rec.rate_date IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Rate or Rate Type or Rate Date should not be null for a currency.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Rate or Rate Type or Rate Date should not be null for a currency',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
--
--Validation of operating unit for muti org structure. For a single Org structure please comment out this validation
--
IF c_po_req_rec.code_of_operating_unit IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Operating Unit Should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Operating Unit should not be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
-----------------------------------------------------------------------------------------------
--Validation of line type
-----------------------------------------------------------------------------------------------
IF c_po_req_rec.line_type IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type should not be null.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Line Type should not be null.',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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
);
ELSE
BEGIN
--checking the line type whether it is AMOUNT type
l_dummy := NULL;
SELECT UPPER (plt.order_type_lookup_code)
INTO l_dummy
FROM po_line_types plt
WHERE plt.line_type = c_po_req_rec.line_type
AND plt.outside_operation_flag = 'N';
IF l_dummy = 'AMOUNT'
THEN
IF (c_po_req_rec.item_segment1 IS NOT NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('Item should be null for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Item should be null for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF (c_po_req_rec.uom_code IS NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('UOM Code can not be NULL for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'UOM Code can not be NULL for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
IF ( c_po_req_rec.category_segment1 IS NULL
OR c_po_req_rec.category_segment2 IS NULL
)
THEN
l_error_flag := 'Y';
xx_trace.l ('Category Segments should not be NULL for AMOUNT based line type'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Category Segments should not be NULL for AMOUNT based line type',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
-------------------
--Item Description validation
-------------------
IF (c_po_req_rec.item_description IS NULL)
THEN
l_error_flag := 'Y';
xx_trace.l ('Item description should not be NULL.');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Item description should not be NULL',
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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 IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error found to validate required fields for Amount based line Type'
|| SQLCODE
|| ''
|| SQLERRM
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Error found to validate required fields for Amount based line Type'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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;
-------------------------------------------------------------------------------------------------------------
--Updating the status of xx_po_requisitions_stg table for the records that fails the control validation
-------------------------------------------------------------------------------------------------------------
IF (l_error_flag = 'Y')
THEN
BEGIN
UPDATE xx_po_requisitions_stg
SET status = 'ER'
WHERE record_id = c_po_req_rec.record_id;
xx_trace.l ('Staging Table status updated to ER');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Status of Staging Table.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_po_req_rec.record_id,
p_identifier4 => c_po_req_rec.item_segment1,
p_identifier5 => c_po_req_rec.code_of_operating_unit,
p_identifier6 => c_po_req_rec.destination_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;
ELSE
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-00001',
p_identifier2 => 'Error in Control Validation.'
|| SQLERRM,
p_identifier3 => NULL -- Can be utilized to store record serial number
--, p_identifier4 => c_item_rec.segment1
-- ,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; --1st Begin
--End of Control Validation
/*---------------------------------------------------------------------------------------------------
Load Data into Pre-Interface Table
---------------------------------------------------------------------------------------------------*/
BEGIN --2nd Begin
INSERT INTO xx_po_requisition_preint
(status, record_id, interface_source_code,
source_type_code, destination_type_code,
authorization_status, item_segment1,
item_description, category_segment1,
category_segment2, line_type, uom_code,
source_organization_code, source_subinventory,
need_by_date, currency_code, quantity,
charge_account_segment1, charge_account_segment2,
charge_account_segment3, charge_account_segment4,
charge_account_segment5,
destination_organization_code,
destination_subinventory, deliver_to_location_code,
deliver_to_requestor_name, preparer_name, rate,
rate_date, rate_type, code_of_operating_unit,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login)
SELECT 'NW', xx_poreq_preint_record_id.NEXTVAL,
interface_source_code, source_type_code,
destination_type_code, authorization_status,
item_segment1, item_description, category_segment1,
category_segment2, line_type, uom_code,
source_organization_code, source_subinventory,
need_by_date, currency_code, quantity,
charge_account_segment1, charge_account_segment2,
charge_account_segment3, charge_account_segment4,
charge_account_segment5, destination_organization_code,
destination_subinventory, deliver_to_location_code,
deliver_to_requestor_name, preparer_name, rate,
rate_date, rate_type, code_of_operating_unit, SYSDATE,
l_user_id, SYSDATE, l_user_id, l_user_id
FROM xx_po_requisitions_stg
WHERE status <> 'ER';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Exception While Inserting Into Pre-Interface table. Oracle Error:'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ROLLBACK;
RAISE;
END;
--2nd Begin
--End of Data Loading in Pre Interface Table
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;
END IF; --IF p_run_mode='F'
/*-----------------------------------------------------------------------------
Updating pre interface table with status 'IP' where record_status is 'NW'
------------------------------------------------------------------------------*/
BEGIN --3rd Begin
UPDATE xx_po_requisition_preint
SET status = 'IP'
WHERE status = 'NW';
xx_trace.l ('Pre-Interface Table status updated to IP');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00002',
p_identifier2 => 'Exception While Updating Status of Pre-Interface Table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --3rdBegin
/*----------------------------------------------------------------------------
--Business Validations--To be carried out for both full run mode(F) and partial run mode (P)
----------------------------------------------------------------------------*/
BEGIN --4 th Begin
FOR c_validate_po_req_rec IN c_po_requisition_validate
LOOP
--
l_error_flag := 'N';
l_processed_recs := l_processed_recs + 1;
--Counter for total records
-----------------------------------------------------------------------------------------------
--Validation of operating unit
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_interface_stg_rec.code_of_operating_unit :=
c_validate_po_req_rec.code_of_operating_unit;
xx_common_validations_pkg.validate_op_unit (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.code_of_operating_unit,
p_organization_id => l_interface_stg_rec.org_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF (l_validation_status = 1)
THEN
l_error_flag := 'Y';
xx_trace.l ( l_message
|| c_validate_po_req_rec.code_of_operating_unit
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message
|| c_validate_po_req_rec.code_of_operating_unit,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
UPDATE xx_po_requisition_preint
SET org_id = l_interface_stg_rec.org_id
WHERE record_id = c_validate_po_req_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('error while updating Org_id in pre interface 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-00003',
p_identifier2 => 'error while updating Org_id in pre interface table',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
COMMIT;
END;
-----------------------------------------------------------------------------------------------
--Validation of Authorization Status
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'AUTHORIZATION STATUS'
AND lookup_code = c_validate_po_req_rec.authorization_status;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Authorization Status is not valid: '
|| c_validate_po_req_rec.authorization_status
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Authorization Status is not valid: '
|| c_validate_po_req_rec.authorization_status,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Authorization Status' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Authorization Status'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of destination organization code
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_interface_stg_rec.destination_organization_code :=
c_validate_po_req_rec.destination_organization_code;
xx_common_validations_pkg.validate_organization (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.destination_organization_code,
p_organization_id => l_interface_stg_rec.destination_organization_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Invalid Destination Organization Code: '
|| c_validate_po_req_rec.destination_organization_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Destination Organization Code: '
|| c_validate_po_req_rec.destination_organization_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END;
-----------------------------------------------------------------------------------------------
--Validation of source organization code
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.source_organization_code IS NOT NULL
THEN
l_interface_stg_rec.source_organization_code :=
c_validate_po_req_rec.source_organization_code;
BEGIN
l_dummy := NULL;
xx_common_validations_pkg.validate_organization (p_organization_name => l_dummy,
p_organization_code => l_interface_stg_rec.source_organization_code,
p_organization_id => l_interface_stg_rec.source_organization_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Invalid Source Organization Code: '
|| c_validate_po_req_rec.source_organization_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Source Organization Code: '
|| c_validate_po_req_rec.source_organization_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Item
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.item_segment1 IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
l_destination_organization :=
l_interface_stg_rec.destination_organization_id;
xx_common_validations_pkg.validate_item_in_organization (p_item => c_validate_po_req_rec.item_segment1,
p_organization_code => c_validate_po_req_rec.destination_organization_code,
p_item_id => l_interface_stg_rec.item_id,
p_organization_id => l_destination_organization,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
--END;
ELSE
---------
--Validation of item Description
---------
IF c_validate_po_req_rec.item_description IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM mtl_system_items_b
WHERE inventory_item_id =
l_interface_stg_rec.item_id
AND organization_id =
l_interface_stg_rec.destination_organization_id
AND description =
l_interface_stg_rec.item_description;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Desciption is Invalid: '
|| c_validate_po_req_rec.item_description
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item Desciption is not valid: '
|| c_validate_po_req_rec.item_description,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Desciption'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Desciption'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
---------
--Validation of Category
---------
IF ( c_validate_po_req_rec.category_segment1 IS NOT NULL
AND c_validate_po_req_rec.category_segment2 IS NOT NULL
)
THEN
BEGIN
SELECT 'X'
INTO l_dummy
FROM mtl_item_categories mic,
mtl_categories_b mcb,
mtl_category_sets mcs
WHERE mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.structure_id = mcb.structure_id
AND mcs.validate_flag = 'Y'
AND mcb.enabled_flag = 'Y'
AND mcb.segment1 =
c_validate_po_req_rec.category_segment1
AND mcb.segment2 =
c_validate_po_req_rec.category_segment2
AND mic.inventory_item_id =
l_interface_stg_rec.item_id
AND mic.organization_id =
l_interface_stg_rec.destination_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category segments are not valid: '
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Category segments are not valid: '
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'category Segments'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Category segments'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
--------------
--Validation of Need by date
--------------
BEGIN
--checking for inventory planned item
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib, mfg_lookups ml
WHERE msib.inventory_planning_code = ml.lookup_code
AND msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (ml.meaning) = UPPER ('Min-max planning')
AND ml.lookup_type = 'MTL_MATERIAL_PLANNING';
IF c_validate_po_req_rec.need_by_date IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date can not be null for inventory planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date can not be null for inventory planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
IF c_validate_po_req_rec.need_by_date < SYSDATE
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date is lower than sysdate for inventory planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date is lower than sysdate for inventory planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
--checking for MRP Planned Item
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib, mfg_lookups ml
WHERE msib.mrp_planning_code = ml.lookup_code
AND msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (ml.meaning) =
UPPER ('MRP planning')
AND ml.lookup_type = 'MRP_PLANNING_CODE';
IF c_validate_po_req_rec.need_by_date IS NULL
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date can not be null for MRP planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date can not be null for MRP planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
IF c_validate_po_req_rec.need_by_date < SYSDATE
THEN
l_error_flag := 'Y';
xx_trace.l ('Need by date is lower than sysdate for MRP planned item'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Need by date is lower than sysdate for MRP planned item',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error in checking of MRP planning'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Error in checking of MRP planning'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Error in checking of inventory planning'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Error in checking of inventory planning'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END;
--
ELSE
--Category validation
IF ( c_validate_po_req_rec.category_segment1 IS NOT NULL
AND c_validate_po_req_rec.category_segment2 IS NOT NULL
)
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_categories_b
WHERE segment1 = c_validate_po_req_rec.category_segment1
AND segment2 = c_validate_po_req_rec.category_segment2
AND enabled_flag = 'Y'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category is invalid:'
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Category is invalid:'
|| c_validate_po_req_rec.category_segment1
|| '.'
|| c_validate_po_req_rec.category_segment2,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Category is invalid'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'category is invalid'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
--
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Charge Account
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
l_dummy :=
xx_common_validations_pkg.get_coa_ccid (p_segment1 => c_validate_po_req_rec.charge_account_segment1,
p_segment2 => c_validate_po_req_rec.charge_account_segment2,
p_segment3 => c_validate_po_req_rec.charge_account_segment3,
p_segment4 => c_validate_po_req_rec.charge_account_segment4,
p_segment5 => c_validate_po_req_rec.charge_account_segment5,
p_process_status => l_validation_status,
p_error_message => l_message,
p_ccid => l_interface_stg_rec.charge_account_id
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
SELECT 'X'
INTO l_dummy
FROM mtl_parameters mp
WHERE mp.organization_id =
l_interface_stg_rec.destination_organization_id
AND mp.material_account =
l_interface_stg_rec.charge_account_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Charge account is invalid');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Charge account is invalid',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ('Can not have more than one charge account for destination organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Can not have more than one charge account for destination organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Charge Account ID '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Charge Account ID '
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END;
-----------------------------------------------------------------------------------------------
--Validation of inventory source type
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.source_type_code IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'REQUISITION SOURCE TYPE'
AND lookup_code = c_validate_po_req_rec.source_type_code;
IF (UPPER (c_validate_po_req_rec.source_type_code) =
'INVENTORY'
)
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_shipping_network_view msnv
WHERE msnv.from_organization_id IN (
SELECT ood.organization_id
FROM org_organization_definitions ood
WHERE ood.organization_id IN (
SELECT msib.organization_id
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.mtl_transactions_enabled_flag =
'Y')
AND ood.operating_unit =
l_interface_stg_rec.org_id)
AND msnv.to_organization_id =
l_interface_stg_rec.destination_organization_id
AND msnv.from_organization_id =
l_interface_stg_rec.source_organization_id;
IF l_dummy IS NOT NULL
THEN
--Checking Item Stock enabled for source Organization
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.source_organization_id
AND msib.stock_enabled_flag = 'Y';
IF (c_validate_po_req_rec.source_subinventory IS NOT NULL
)
THEN
BEGIN
--Checking Source Subinventory
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib,
mfg_lookups ml,
mtl_item_sub_inventories misi
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.inventory_item_id =
misi.inventory_item_id
AND misi.organization_id =
misi.organization_id
AND msib.organization_id =
l_interface_stg_rec.source_organization_id
AND UPPER (misi.secondary_inventory) =
UPPER (c_validate_po_req_rec.source_subinventory
)
AND UPPER (ml.meaning) =
UPPER ('Subinventories not restricted to pre-defined list'
)
AND ml.lookup_type LIKE
'MTL_SUBINVENTORY_RESTRICTIONS'
AND ml.lookup_code =
msib.restrict_subinventories_code;
EXCEPTION
--Exception of Subinventory
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is restricted to 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-00003',
p_identifier2 => 'Item is restricted to subinventory',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Source Subinventory '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item-Subinventory Restriction'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Stock Enabling Exception
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not stock enable in source organization.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item is not stock enable in source organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Stock enabled '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Stock Enabled'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
BEGIN
--Checking Internal Order Enabled for destination Organization
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND msib.internal_order_enabled_flag = 'Y';
BEGIN
--Checking the Deliver to Loaction
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM hr_locations_all hrl
WHERE UPPER (hrl.ship_to_site_flag) = 'Y'
AND hrl.in_organization_flag = 'Y'
AND hrl.inventory_organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (hrl.location_code) =
UPPER (c_validate_po_req_rec.deliver_to_location_code
)
AND NVL (hrl.inactive_date, SYSDATE) >=
SYSDATE;
EXCEPTION
--Exception due to checking the deliver to location
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Deliver to location code does not exist'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Deliver to location code does not exist',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to location code does not exist'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Deliver to location code does not exist'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Exception for Internal Order checking
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item Should be internal Order enabled.'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item Should be internal Order enabled.',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item Should be internal Order enabled.'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item Should be internal Order enabled.'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
--Exception for shipping network
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Shipping network does not exist between source and Destination Organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Shipping network does not exist between source and destination Organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Shipping network does not exist '
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Shipping network does not exist'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
ELSIF (UPPER (c_validate_po_req_rec.source_type_code) =
'VENDOR'
)
THEN
IF l_interface_stg_rec.item_id IS NOT NULL
THEN
BEGIN
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND purchasing_enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not purchasing enabled in destination org for VENDOR source 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-00003',
p_identifier2 => 'Item is not purchasing enabled in destination org for VENDOR source type',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is not purchasing enabled for VENDOR source type'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is not purchasing enabled '
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Source Type Code is invalid: '
|| c_validate_po_req_rec.source_type_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Source Type Code is invalid: '
|| c_validate_po_req_rec.source_type_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Source Type Code' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Source Type Code'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of Destination Type
-----------------------------------------------------------------------------------------------
BEGIN
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM po_lookup_codes
WHERE lookup_type = 'DESTINATION TYPE'
AND lookup_code =
c_validate_po_req_rec.destination_type_code;
IF (UPPER (c_validate_po_req_rec.destination_type_code) =
'INVENTORY'
)
THEN
BEGIN
--checking Stock enabled flag for destination org
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND msib.stock_enabled_flag = 'Y';
IF (c_validate_po_req_rec.destination_subinventory IS NOT NULL
)
THEN
BEGIN
--checking restriction of the item in destination subinventory
l_dummy := NULL;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msib,
mfg_lookups ml,
mtl_item_sub_inventories misi
WHERE msib.inventory_item_id =
l_interface_stg_rec.item_id
AND msib.inventory_item_id =
misi.inventory_item_id
AND misi.organization_id = misi.organization_id
AND msib.organization_id =
l_interface_stg_rec.destination_organization_id
AND UPPER (misi.secondary_inventory) =
UPPER (c_validate_po_req_rec.destination_subinventory
)
AND UPPER (ml.meaning) =
UPPER ('Subinventories not restricted to pre-defined list'
)
AND ml.lookup_type LIKE
'MTL_SUBINVENTORY_RESTRICTIONS'
AND ml.lookup_code =
msib.restrict_subinventories_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is restricted in the destination Subinventory:'
|| c_validate_po_req_rec.destination_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-00003',
p_identifier2 => 'Item is restricted in the destination Subinventory:'
|| c_validate_po_req_rec.destination_subinventory,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is restricted in the destination Subinventory'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is restricted in the destination Subinventory'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Item is not stock enable in destination organization'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Item is not stock enable in destination organization',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Item is not stock enable in destination organization'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Item is not stock enable in destination organization'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Destination Type Code is invalid: '
|| c_validate_po_req_rec.destination_type_code
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Destination Type Code is invalid: '
|| c_validate_po_req_rec.destination_type_code,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ('Destination Type Code' || SQLCODE || ''
|| 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-00003',
p_identifier2 => 'Destination Type Code'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of AMOUNT based line type
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.line_type IS NOT NULL
THEN
BEGIN
--checking the line type whether it is AMOUNT type
l_dummy := NULL;
SELECT UPPER (plt.order_type_lookup_code)
INTO l_dummy
FROM po_line_types plt
WHERE plt.line_type = c_validate_po_req_rec.line_type
AND plt.outside_operation_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Line Type is invalid');
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Line Type is invalid',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Line Type is invalid'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Line Type id is invalid'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of UOM Code
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.uom_code IS NOT NULL
THEN
xx_common_validations_pkg.validate_uom (p_unit_of_measure => c_validate_po_req_rec.unit_of_measure,
p_uom_code => c_validate_po_req_rec.uom_code,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Currency
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.currency_code IS NOT NULL
THEN
l_dummy := NULL;
l_dummy :=
xx_common_validations_pkg.validate_currency_code (p_currency_code => c_validate_po_req_rec.currency_code,
p_process_status => l_validation_status,
p_error_message => l_message
);
--Fetching functional Currency
BEGIN
SELECT currency_code
INTO l_functional_currency
FROM gl_sets_of_books
WHERE set_of_books_id =
fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ('Functional Currency is not defined for the set of Book'
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Functional Currency is not defined for the set of Book',
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Functional Currency'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Functional Currency'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
ELSE
BEGIN
--Validating rate type
l_dummy := NULL;
SELECT 'x'
INTO l_dummy
FROM gl_daily_conversion_types
WHERE user_conversion_type =
c_validate_po_req_rec.rate_type;
--validating whether daily rate exists for the given rate tpe and date
IF ( c_validate_po_req_rec.currency_code <>
l_functional_currency
AND c_validate_po_req_rec.rate_type <> 'User'
)
THEN
xx_common_validations_pkg.get_currency_rate (p_currency_from => l_functional_currency,
p_currency_to => c_validate_po_req_rec.currency_code,
p_conversion_date => c_validate_po_req_rec.rate_date,
p_conversion_type => c_validate_po_req_rec.rate_type,
p_rate => l_interface_stg_rec.rate,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF l_validation_status = 1
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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 IF;
END IF;
--
EXCEPTION
--Exception while validating rate tpe
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Rate Type is invalid: '
|| c_validate_po_req_rec.rate_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-00003',
p_identifier2 => 'Rate Type is invalid: '
|| c_validate_po_req_rec.rate_type,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Rate Type Validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Rate Type Validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
END IF;
-----------------------------------------------------------------------------------------------
--Validation of Preparer Name
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.preparer_name IS NOT NULL
THEN
BEGIN
SELECT hre.employee_id
INTO l_interface_stg_rec.preparer_id
FROM hr_employees hre, po_agents pa
WHERE UPPER (hre.full_name) =
UPPER (TRIM (c_validate_po_req_rec.preparer_name))
AND pa.agent_id = hre.employee_id
AND NVL (pa.end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Preparer Name is invalid:'
|| c_validate_po_req_rec.preparer_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Preparer Name is invalid:'
|| c_validate_po_req_rec.preparer_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'More than one same Preparer name exist:'
|| c_validate_po_req_rec.preparer_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'More than one same Preparer name exist:'
|| c_validate_po_req_rec.preparer_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Preparer Name validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Preparer Name validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
-----------------------------------------------------------------------------------------------
--Validation of Deliver to Requestor Name
-----------------------------------------------------------------------------------------------
IF c_validate_po_req_rec.deliver_to_requestor_name IS NOT NULL
THEN
BEGIN
SELECT hre.employee_id
INTO l_interface_stg_rec.deliver_to_requestor_id
FROM hr_employees hre, po_agents pa
WHERE UPPER (hre.full_name) =
UPPER (TRIM (c_validate_po_req_rec.deliver_to_requestor_name
)
)
AND pa.agent_id = hre.employee_id
AND NVL (pa.end_date_active, SYSDATE) >= SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to Requestor Name is invalid:'
|| c_validate_po_req_rec.deliver_to_requestor_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Deliver to Requestor Name is invalid:'
|| c_validate_po_req_rec.deliver_to_requestor_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN TOO_MANY_ROWS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'More than one same Deliver to Requestor Name exist:'
|| c_validate_po_req_rec.deliver_to_requestor_name
);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'More than one same Deliver to Requestor Name exist:'
|| c_validate_po_req_rec.deliver_to_requestor_name,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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
);
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.l ( 'Deliver to Requestor Name validation'
|| SQLCODE
|| ''
|| 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-00003',
p_identifier2 => 'Deliver to Requestor Name validation'
|| SQLCODE
|| ''
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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;
/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' for errorneous records
-------------------------------------------------------------------------------*/
IF l_error_flag = 'Y'
THEN
BEGIN --4.5 th Begin
UPDATE xx_po_requisition_preint
SET status = 'ER'
WHERE record_id = c_validate_po_req_rec.record_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status.Oracle Error-'
|| SQLERRM,
p_identifier3 => c_validate_po_req_rec.record_id,
p_identifier4 => c_validate_po_req_rec.item_segment1,
p_identifier5 => c_validate_po_req_rec.code_of_operating_unit,
p_identifier6 => c_validate_po_req_rec.destination_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; --4.5 th Begin
ELSE
l_successful_recs := l_successful_recs + 1;
END IF;
l_error_flag := 'N';
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-12300',
p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --4.0 th Begin
--End of Business Validation Section
/*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/
--Insert all data into oracle interface table from pre interface table
BEGIN --5th Begin
INSERT INTO po_requisitions_interface_all
(transaction_id, process_flag, request_id, program_id,
program_application_id, program_update_date,
last_updated_by, last_update_date, last_update_login,
creation_date, created_by, interface_source_code,
interface_source_line_id, source_type_code,
requisition_header_id, requisition_line_id,
req_distribution_id, requisition_type,
destination_type_code, item_description, quantity,
unit_price, authorization_status, batch_id, group_code,
delete_enabled_flag, update_enabled_flag, approver_id,
approver_name, approval_path_id, note_to_approver,
preparer_id, autosource_flag, req_number_segment1,
req_number_segment2, req_number_segment3,
req_number_segment4, req_number_segment5,
header_description, header_attribute_category,
header_attribute1, header_attribute2,
header_attribute3, header_attribute4,
header_attribute5, header_attribute6,
header_attribute7, header_attribute8,
header_attribute9, header_attribute10,
header_attribute11, header_attribute12,
header_attribute13, header_attribute14, urgent_flag,
header_attribute15, rfq_required_flag, justification,
note_to_buyer, note_to_receiver, item_id,
item_segment1, item_segment2, item_segment3,
item_segment4, item_segment5, item_segment6,
item_segment7, item_segment8, item_segment9,
item_segment10, item_segment11, item_segment12,
item_segment13, item_segment14, item_segment15,
item_segment16, item_segment17, item_segment18,
item_segment19, item_segment20, item_revision,
charge_account_id, charge_account_segment1,
charge_account_segment2, charge_account_segment3,
charge_account_segment4, charge_account_segment5,
charge_account_segment6, charge_account_segment7,
charge_account_segment8, charge_account_segment9,
charge_account_segment10, charge_account_segment11,
charge_account_segment12, charge_account_segment13,
charge_account_segment14, charge_account_segment15,
charge_account_segment16, charge_account_segment17,
charge_account_segment18, charge_account_segment19,
charge_account_segment20, charge_account_segment21,
charge_account_segment22, charge_account_segment23,
charge_account_segment24, charge_account_segment25,
charge_account_segment26, charge_account_segment27,
charge_account_segment28, charge_account_segment29,
charge_account_segment30, category_id,
category_segment1, category_segment2,
category_segment3, category_segment4,
category_segment5, category_segment6,
category_segment7, category_segment8,
category_segment9, category_segment10,
category_segment11, category_segment12,
category_segment13, category_segment14,
category_segment15, category_segment16,
category_segment17, category_segment18,
category_segment19, category_segment20,
unit_of_measure, uom_code, line_type_id, line_type,
un_number_id, un_number, hazard_class_id, hazard_class,
must_use_sugg_vendor_flag, reference_num,
wip_entity_id, wip_line_id, wip_operation_seq_num,
wip_resource_seq_num, wip_repetitive_schedule_id,
project_num, task_num, expenditure_type,
source_organization_id, source_organization_code,
source_subinventory, destination_organization_id,
destination_organization_code,
destination_subinventory, deliver_to_location_id,
deliver_to_location_code, deliver_to_requestor_id,
deliver_to_requestor_name, suggested_buyer_id,
suggested_buyer_name, suggested_vendor_name,
suggested_vendor_id, suggested_vendor_site,
suggested_vendor_site_id, suggested_vendor_contact,
suggested_vendor_contact_id, suggested_vendor_phone,
suggested_vendor_item_num, line_attribute_category,
line_attribute1, line_attribute2, line_attribute3,
line_attribute4, line_attribute5, line_attribute6,
line_attribute7, line_attribute8, line_attribute9,
line_attribute10, line_attribute11, line_attribute12,
line_attribute13, line_attribute14, line_attribute15,
need_by_date, note1_id, note2_id, note3_id, note4_id,
note5_id, note6_id, note7_id, note8_id, note9_id,
note10_id, note1_title, note2_title, note3_title,
note4_title, note5_title, note6_title, note7_title,
note8_title, note9_title, note10_title, gl_date,
dist_attribute_category, distribution_attribute1,
distribution_attribute2, distribution_attribute3,
distribution_attribute4, distribution_attribute5,
distribution_attribute6, distribution_attribute7,
distribution_attribute8, distribution_attribute9,
distribution_attribute10, distribution_attribute11,
distribution_attribute12, distribution_attribute13,
distribution_attribute14, distribution_attribute15,
preparer_name, bom_resource_id, accrual_account_id,
variance_account_id, budget_account_id,
ussgl_transaction_code, government_context,
currency_code, currency_unit_price, rate, rate_date,
rate_type, prevent_encumbrance_flag,
autosource_doc_header_id, autosource_doc_line_num,
project_accounting_context,
expenditure_organization_id, project_id, task_id,
expenditure_item_date, document_type_code, org_id,
transaction_reason_code, allocation_type,
allocation_value, multi_distributions,
req_dist_sequence_id, kanban_card_id, emergency_po_num,
award_id, end_item_unit_number, tax_code_id,
oke_contract_header_id, oke_contract_num,
oke_contract_version_id, oke_contract_line_id,
oke_contract_line_num, oke_contract_deliverable_id,
oke_contract_deliverable_num,
secondary_unit_of_measure, secondary_uom_code,
secondary_quantity, preferred_grade, vmi_flag,
tax_user_override_flag, amount, currency_amount,
ship_method, estimated_pickup_date, base_unit_price,
negotiated_by_preparer_flag)
SELECT transaction_id, process_flag, request_id, program_id,
program_application_id, program_update_date,
last_updated_by, last_update_date, last_update_login,
creation_date, created_by, interface_source_code,
interface_source_line_id, source_type_code,
requisition_header_id, requisition_line_id,
req_distribution_id, requisition_type,
destination_type_code, item_description, quantity,
unit_price, authorization_status, batch_id, group_code,
delete_enabled_flag, update_enabled_flag, approver_id,
approver_name, approval_path_id, note_to_approver,
preparer_id, autosource_flag, req_number_segment1,
req_number_segment2, req_number_segment3,
req_number_segment4, req_number_segment5,
header_description, header_attribute_category,
header_attribute1, header_attribute2, header_attribute3,
header_attribute4, header_attribute5, header_attribute6,
header_attribute7, header_attribute8, header_attribute9,
header_attribute10, header_attribute11,
header_attribute12, header_attribute13,
header_attribute14, urgent_flag, header_attribute15,
rfq_required_flag, justification, note_to_buyer,
note_to_receiver, item_id, item_segment1, item_segment2,
item_segment3, item_segment4, item_segment5,
item_segment6, item_segment7, item_segment8,
item_segment9, item_segment10, item_segment11,
item_segment12, item_segment13, item_segment14,
item_segment15, item_segment16, item_segment17,
item_segment18, item_segment19, item_segment20,
item_revision, charge_account_id, charge_account_segment1,
charge_account_segment2, charge_account_segment3,
charge_account_segment4, charge_account_segment5,
charge_account_segment6, charge_account_segment7,
charge_account_segment8, charge_account_segment9,
charge_account_segment10, charge_account_segment11,
charge_account_segment12, charge_account_segment13,
charge_account_segment14, charge_account_segment15,
charge_account_segment16, charge_account_segment17,
charge_account_segment18, charge_account_segment19,
charge_account_segment20, charge_account_segment21,
charge_account_segment22, charge_account_segment23,
charge_account_segment24, charge_account_segment25,
charge_account_segment26, charge_account_segment27,
charge_account_segment28, charge_account_segment29,
charge_account_segment30, category_id, category_segment1,
category_segment2, category_segment3, category_segment4,
category_segment5, category_segment6, category_segment7,
category_segment8, category_segment9, category_segment10,
category_segment11, category_segment12,
category_segment13, category_segment14,
category_segment15, category_segment16,
category_segment17, category_segment18,
category_segment19, category_segment20, unit_of_measure,
uom_code, line_type_id, line_type, un_number_id,
un_number, hazard_class_id, hazard_class,
must_use_sugg_vendor_flag, reference_num, wip_entity_id,
wip_line_id, wip_operation_seq_num, wip_resource_seq_num,
wip_repetitive_schedule_id, project_num, task_num,
expenditure_type, source_organization_id,
source_organization_code, source_subinventory,
destination_organization_id,
destination_organization_code, destination_subinventory,
deliver_to_location_id, deliver_to_location_code,
deliver_to_requestor_id, deliver_to_requestor_name,
suggested_buyer_id, suggested_buyer_name,
suggested_vendor_name, suggested_vendor_id,
suggested_vendor_site, suggested_vendor_site_id,
suggested_vendor_contact, suggested_vendor_contact_id,
suggested_vendor_phone, suggested_vendor_item_num,
line_attribute_category, line_attribute1, line_attribute2,
line_attribute3, line_attribute4, line_attribute5,
line_attribute6, line_attribute7, line_attribute8,
line_attribute9, line_attribute10, line_attribute11,
line_attribute12, line_attribute13, line_attribute14,
line_attribute15, need_by_date, note1_id, note2_id,
note3_id, note4_id, note5_id, note6_id, note7_id,
note8_id, note9_id, note10_id, note1_title, note2_title,
note3_title, note4_title, note5_title, note6_title,
note7_title, note8_title, note9_title, note10_title,
gl_date, dist_attribute_category, distribution_attribute1,
distribution_attribute2, distribution_attribute3,
distribution_attribute4, distribution_attribute5,
distribution_attribute6, distribution_attribute7,
distribution_attribute8, distribution_attribute9,
distribution_attribute10, distribution_attribute11,
distribution_attribute12, distribution_attribute13,
distribution_attribute14, distribution_attribute15,
preparer_name, bom_resource_id, accrual_account_id,
variance_account_id, budget_account_id,
ussgl_transaction_code, government_context, currency_code,
currency_unit_price, rate, rate_date, rate_type,
prevent_encumbrance_flag, autosource_doc_header_id,
autosource_doc_line_num, project_accounting_context,
expenditure_organization_id, project_id, task_id,
expenditure_item_date, document_type_code, org_id,
transaction_reason_code, allocation_type,
allocation_value, multi_distributions,
req_dist_sequence_id, kanban_card_id, emergency_po_num,
award_id, end_item_unit_number, tax_code_id,
oke_contract_header_id, oke_contract_num,
oke_contract_version_id, oke_contract_line_id,
oke_contract_line_num, oke_contract_deliverable_id,
oke_contract_deliverable_num, secondary_unit_of_measure,
secondary_uom_code, secondary_quantity, preferred_grade,
vmi_flag, tax_user_override_flag, amount, currency_amount,
ship_method, estimated_pickup_date, base_unit_price,
negotiated_by_preparer_flag
FROM xx_po_requisition_preint
WHERE status = 'IP';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( 'Exception While Inserting into PO_REQUISITIONS_INTERFACE_ALL.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Inserting into PO_REQUISITIONS_INTERFACE_ALL.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; --5 th Begin
/*----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status in IP'
----------------------------------------------------------------------------------*/
BEGIN --6 th Begin
UPDATE xx_po_requisition_preint
SET status = 'PR'
WHERE status = 'IP';
xx_trace.l ('Pre-Interface Table status updated to PR');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h ( ' Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END; --6th Begin
/*---------------------------------------------------------------------------------------
Apps Initialisation
----------------------------------------------------------------------------------------*/
xx_common_validations_pkg.init_apps_params (p_request_id => l_request_id,
p_process_status => l_validation_status,
p_error_message => l_message
);
IF (l_validation_status = 1)
THEN
l_error_flag := 'Y';
xx_trace.l (l_message);
--Write to Error
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => l_message--, p_identifier3 => c_validate_po_req_rec.record_id
--, p_identifier4 => c_validate_po_req_rec.item_segment1
--, p_identifier5 => c_validate_po_req_rec.item_id
--, p_identifier6 => c_validate_po_req_rec.org_id
--, p_identifier7 => c_validate_po_req_rec.destination_organization_id
--p_identifier8 => NULL -- Can be utilized for displaying more information
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
ELSE
/*---------------------------------------------------------------------------------------
Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------*/
BEGIN --7th Begin
SELECT COUNT (*)
INTO l_count
FROM xx_po_requisition_preint
WHERE status = 'PR';
IF l_count > 0
THEN
l_standard_request_id :=
fnd_request.submit_request (application => 'PO',
program => 'REQIMPORT',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => '',
argument2 => '',
argument3 => 'ITEM',
argument4 => '',
argument5 => 'N',
argument6 => 'Y'
);
COMMIT;
--Wait for the completion of the concurrent request (if submitted successfully)
l_completed :=
fnd_concurrent.wait_for_request (request_id => l_standard_request_id,
INTERVAL => 60,
max_wait => 0,
phase => l_phase,
status => l_vstatus,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
MESSAGE => l_message
);
xx_trace.l ( 'Request submitted with request id-'
|| l_standard_request_id
);
ELSE
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 --Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'No records To Process in Interface Table',
p_identifier3 => NULL-- Can be utilized to store record serial number
,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END; --7 th Begin
END IF;
/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_po_requisition_preint
WHERE status = 'PR';
--
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h ( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;
/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_processed_recs - l_successful_recs;
l_messages_rec := l_null_rec;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
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');
WHEN e_record_count_error
THEN
--Write to error
l_error_flag := 'Y';
--
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_req_cnt,
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;
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 --High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_processed_recs,
p_successful_recs => l_successful_recs,
p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20 --Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main; --0th Begin
--
END xx_pocnv01_req_load_pkg;
/
No comments :
Post a Comment