-- ======================================================================================
-- File Name : xapcnv03a.ctl
-- File Type : SQL*Loader Control file.
-- RICEW Object id : AP_CNV_03
-- Description : This SQL*Loader file is used to load data
-- from flat file to the staging table XX_AP_OPEN_INVOICE_STG
-- Maintenance History:
--
-- Date Version Name Remarks
-- ----------- --------- ----------------- ---------------------------------------
-- 27-Dec-14 1.0 Madhu dhare Draft Version.
--
-- =====================================================================================
OPTIONS (ROWS=1)
LOAD DATA
--INFILE 'd:\oravis\viscustom\11.5.0\bin\xap01a.dat'
BADFILE 'd:\oravis\viscustom\11.5.0\bin\xap01a.bad'
TRUNCATE
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'BC'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, BATCH_NUMBER CHAR
, BATCH_AMOUNT CHAR
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'IN'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, INVOICE_ID "AP_INVOICES_INTERFACE_S.NEXTVAL"
, INVOICE_NUM CHAR
, INVOICE_TYPE_LOOKUP_CODE CHAR
, INVOICE_DATE DATE
, PO_NUMBER
, PO_HEADER_ID
, VENDOR_NUM CHAR
, VENDOR_SITE_CODE CHAR
, INVOICE_AMOUNT
, INVOICE_CURRENCY_CODE CHAR
, TERMS_NAME CHAR
, HDR_DESCRIPTION CHAR
, SOURCE CHAR
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'LN'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, INVOICE_ID "AP_INVOICES_INTERFACE_S.CURRVAL"
, INVOICE_LINE_ID "AP_INVOICE_LINES_INTERFACE_S.NEXTVAL"
, LINE_NUMBER CHAR
, LINE_TYPE_LOOKUP_CODE CHAR
, AMOUNT
, ACCOUNTING_DATE DATE
, LINE_DESCRIPTION CHAR
, PO_LINE_ID
, PO_LINE_LOCATION_ID
, PO_DISTRIBUTION_ID
, DIST_CODE_CONCATENATED CHAR
, RCV_TRANSACTION_ID
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
CREATE OR REPLACE PACKAGE xx_ap_invoice_conv_pkg
AS
/*
-------------------------------------------------------------
Package Name : xx_ap_invoice_conv_pkg
Author's Name : Madhu Dhare
Date Written :18-Dec-2014
RICEW Object id : AP_CNV_03
Program Style : Package Specification
Purpose : AP Open Invoice Conversion
Maintenance History:
Date: Name Remarks
----------- ------------- ------------------
19-Dec-2006 Madhu Dhare Draft Version
--------------------------------------------------------------
*/
-----------------------------------------------------------
-- 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 := 40;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 20;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 0;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
--
-- Code Combination Segment Delimeter used in Private Function "validate_ccsegment"
g_cc_seg_delimeter VARCHAR2(1) := '.';
-------------------------------------------------------------------
-- Public Procedure/Function Declaration Section
-- Purpose-Main calling Procedure for AP Open Invoice Conversion
-------------------------------------------------------------------
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_runmode IN VARCHAR2);
--
END xx_ap_invoice_conv_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_ap_invoice_conv_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : xx_ap_invoice_conv_pkg
Author's Name : Madhu Dhare
Date Written :18-Dec-2014
RICEW Object id : AP_CNV_03
Program Style : Package Body
Purpose : AP Open Invoice Conversion
--
Maintenance History
Date Issue# Name Remarks
----------- ---------------- ----------------- -------------
19-Dec-14 1.0 Madhu Dhare Draft Version
--------------------------------------------------------------------------------------------------------*/
-------------------------------------------------------------------------------------------------------
--Function : validate_ccsegment
--Purpose : validates the concatenated code combination segments.
-------------------------------------------------------------------------------------------------------
FUNCTION validate_ccsegment (
p_ccsegment IN xx_ap_pre_inv_lines_interface.dist_code_concatenated%TYPE
, p_ccid OUT NUMBER
, p_error_message OUT VARCHAR2
)
RETURN VARCHAR2;
-------------------------------------------------------------------------------------------------------
--Procedure : main
--Purpose : ap open invoice conversion
-------------------------------------------------------------------------------------------------------
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_runmode IN VARCHAR2
)
IS
----------------------------------------------------------------------------------------------------
--Cursor Declaration Section
----------------------------------------------------------------------------------------------------
--Cursor to select Batch data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_bc_stg
IS
SELECT record_id
, transaction_code
, batch_number
, batch_amount
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'BC';
--Cursor to select Invoice data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_in_stg
IS
SELECT record_id
, transaction_code
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, hdr_description
, SOURCE
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'IN';
--Cursor to select Invoice Line data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_ln_stg (
cp_invoice_id xx_ap_open_invoice_stg.invoice_id%TYPE
)
IS
SELECT record_id
, transaction_code
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, line_description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'LN' AND invoice_id = cp_invoice_id;
--Cursor to select Invoice data from Pre Interface table XX_AP_PRE_INVOICE_INTERFACE
CURSOR c_invoice_insert
IS
SELECT *
FROM xx_ap_pre_invoice_interface
WHERE status = 'IP';
-- FOR UPDATE;
--Cursor to select Invoice Line data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_line_insert (
cp_invoice_id xx_ap_pre_invoice_interface.invoice_id%TYPE
)
IS
SELECT *
FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = cp_invoice_id;
--Cursor to select Processed Invoice data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_purge
IS
SELECT invoice_id
FROM xx_ap_pre_invoice_interface
WHERE status = 'PR';
--Cursor to select Invoice Line data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_line_purge (
cp_invoice_id xx_ap_pre_invoice_interface.invoice_id%TYPE
)
IS
SELECT invoice_line_id
FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = cp_invoice_id;
----------------------------------------------------------------------------------------------------
--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_error_message VARCHAR2 (2000);
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
l_processed_rec_cnt NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_AP_INVOICE_CONV_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'AP_CNV_03';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
-- Control Validations Variables
l_control_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at control validation (Y=ERROR/N=SUCCESS)
l_batch_total xx_ap_open_invoice_stg.batch_amount%TYPE
:= 0;
l_invoice_hdr_total xx_ap_open_invoice_stg.invoice_amount%TYPE := 0;
l_invoice_line_total xx_ap_open_invoice_stg.amount%TYPE := 0;
l_group_id xx_ap_open_invoice_stg.batch_number%TYPE;
--
l_po_origin BOOLEAN := FALSE;
-- Flag to indicate the origin of Invoice. (TRUE-"generated from PO" / FALSE-"Not generated from PO")
-- Business Validations Variables (Invoice)
l_source fnd_lookup_values.meaning%TYPE;
l_business_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at business validation (Y=ERROR/N=SUCCESS)
l_business_line_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at business validation at line level(Y=ERROR/N=SUCCESS)
l_currency_exist VARCHAR2 (1);
l_currency_code ap_invoices_interface.invoice_currency_code%TYPE;
l_vendor_exist NUMBER;
l_site_code_exist NUMBER;
l_terms_exist NUMBER;
--l_org_exist NUMBER;
l_po_header_exist NUMBER;
l_acc_date DATE;
l_date_status NUMBER;
l_date_message VARCHAR2(200);
l_invoice_num NUMBER;
-- Business Validations Variables (Invoice Lines)
l_ccid_exist VARCHAR2 (20);
l_ccid gl_code_combinations.code_combination_id%TYPE;
l_err_message VARCHAR2 (2000);
l_line_lookup_exist NUMBER;
l_po_line_exists NUMBER;
l_po_location_exists NUMBER;
l_po_dist_exists NUMBER;
l_po_number po_headers_all.segment1%TYPE;
l_rcv_trans_exists NUMBER;
l_match_option po_line_locations_all.match_option%TYPE;
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_control_valid_fail EXCEPTION;
--Stop the program if Control level validation fails at Staging Table.
--
BEGIN -- Main Begin
-- Get the Concurrent Request ID.
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
--Initialize Trace
--Purpose : Set the program environment for Tracing
xx_trace.set_program_environment (p_program_name => l_program_name);
-- Initialize EMF Section
-- Purpose : EMF is initialized so that error can be logged.
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name
, p_program_type => g_program_type
, p_ricew_id => l_ricewid
, p_request_id => l_request_id
);
IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;
--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
--Insert EMF Header
--Purpose : To tag the Error labels to EMF columns
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Error Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Record ID'; --Third Error Header
l_error_rec.identifier4 := 'Invoice ID'; --Fourth Error Header
l_error_rec.identifier5 := 'Invoice Line ID'; --Fifth Error Header
-- l_error_rec.identifier6 := NULL; --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;
-- p_runmode determines the execution of code.
-- valid values for p_runmode are 'F' - FULL / 'P' - PARTIAL
-- On FULL mode data is processed from Staging table
-- On PARTIAL mode rectified data will be processed from Preinterface tables.
IF p_runmode = 'F'
THEN
----------------------------------------------------------------------------------------------------
-- Fetch Staging Table Data and Perform Control Validation.
----------------------------------------------------------------------------------------------------
BEGIN -- Control Validation Begin
-- Initialize the Amount totals.
l_invoice_hdr_total := 0;
l_invoice_line_total := 0;
FOR c_invoice_bc_rec IN c_invoice_bc_stg
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_bc_rec.batch_number IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Batch Number value cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Batch Number value cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_group_id := c_invoice_bc_rec.batch_number;
END IF;
IF c_invoice_bc_rec.batch_amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Batch Amount value cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Batch Amount value cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_batch_total := c_invoice_bc_rec.batch_amount;
END IF;
IF l_control_error_flag = 'Y'
THEN
RAISE e_control_valid_fail;
-- Stop Execution if Control Validation fails at any of the above levels.
END IF;
END LOOP; -- End of Cursor c_invoice_bc_stg
FOR c_invoice_in_rec IN c_invoice_in_stg
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_in_rec.invoice_num IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Number cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Invoice Number cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_in_rec.invoice_amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Amount 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 => 'Invoice Amount cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_invoice_hdr_total :=
l_invoice_hdr_total + c_invoice_in_rec.invoice_amount;
END IF;
IF c_invoice_in_rec.source IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Source 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 => 'Source cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_in_rec.invoice_date IS NOT NULL
THEN
xx_common_validations_pkg.validate_date_format
(p_input_string => c_invoice_in_rec.invoice_date
, p_date_format => 'DD-MON-YYYY' -- Date Format
, p_date => l_acc_date
, p_process_status => l_date_status
, p_error_message => l_date_message
);
-- Possible values of l_date_status are (0-valid / 1-Invalid)
IF l_date_status = 1
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Date is not in Proper Format "DD-MON-YYYY".');
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Date is not in Proper Format "DD-MON-YYYY".'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
IF c_invoice_in_rec.invoice_type_lookup_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Type Lookup 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 => 'Invoice Type Lookup Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--------------------------------------------------------------------------------------
-- Check the Invoice is PO Originated or not.
--------------------------------------------------------------------------------------
IF c_invoice_in_rec.po_header_id IS NULL
THEN
l_po_origin := FALSE;
ELSE
l_po_origin := TRUE;
END IF;
IF l_po_origin = FALSE
THEN
IF c_invoice_in_rec.vendor_num IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Vendor Num 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 => 'Vendor Num cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_in_rec.vendor_site_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Vendor Site 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 => 'Vendor Site Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Initializing the Invoice Line Amount Total.
l_invoice_line_total := 0;
FOR c_invoice_ln_rec IN
c_invoice_ln_stg (c_invoice_in_rec.invoice_id)
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_ln_rec.line_number IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Line Number cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Invoice Line Number cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_ln_rec.amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Line Amount 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 => 'Invoice Line Amount cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
-- Sum Invoice Line Amount
l_invoice_line_total :=
l_invoice_line_total + c_invoice_ln_rec.amount;
END IF;
IF c_invoice_ln_rec.line_type_lookup_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Type Lookup 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 => 'Invoice Line Type Lookup Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_ln_rec.accounting_date IS NOT NULL
THEN
xx_common_validations_pkg.validate_date_format
(p_input_string => c_invoice_ln_rec.accounting_date
, p_date_format => 'DD-MON-YYYY' -- Date Format
, p_date => l_acc_date
, p_process_status => l_date_status
, p_error_message => l_date_message
);
-- Possible values of l_date_status are (0-valid / 1-Invalid)
IF l_date_status = 1
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Accounting Date is not in Proper Format "DD-MON-YYYY".');
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Accounting Date is not in Proper Format "DD-MON-YYYY".'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF; -- c_invoice_ln_rec.accounting_date IS NOT NULL
IF l_po_origin = FALSE
THEN -- dist_code_concatenated is not required if Invoice is PO Orginated
IF c_invoice_ln_rec.dist_code_concatenated IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Dist Code Concatenated 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 => 'Invoice Line Dist Code Concatenated cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
IF l_po_origin = TRUE
THEN
IF c_invoice_ln_rec.po_line_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Line ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Line ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_ln_rec.po_line_location_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Line Location ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Line Location ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_ln_rec.po_distribution_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Distribution ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Distribution ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
END LOOP; -- End of Cursor c_invoice_ln_stg
-- Checking the Invoice Amount with Invoice Line Amount Total.
IF c_invoice_in_rec.invoice_amount != l_invoice_line_total
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Amounts do not sum up to Invoice Amount.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Line Amounts do not sum up to Invoice Amount.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF l_control_error_flag = 'Y'
THEN
EXIT;
-- If Control Validation falis at Invoice Level,
-- EXIT without validating rest invoices.
END IF;
END LOOP; -- End of Cursor c_invoice_in_stg
IF l_batch_total != l_invoice_hdr_total
THEN
l_control_error_flag := 'Y';
END IF;
IF l_control_error_flag = 'Y'
THEN
xx_trace.l
('Control Validation Failed.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Control Validation Failed.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE e_control_valid_fail;
-- Stop Execution if Control Validation fails at any of the above levels.
END IF;
EXCEPTION -- Control Validation Exception Block
WHEN OTHERS
THEN
-- l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
( 'Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Error in Control Validation.'
|| SQLERRM
, p_identifier3 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- Initialize Following variable for Summary Report.
-- Purpose: Display a summary of records processed, successful and in error
l_error_recs := l_processed_recs;
-- All records are Errored Out.
l_successful_recs := 0;
-- Delete Records from Staging Table xx_ap_open_invoice_stg.
-- Not required if SQL * Loader Control file is run with "TRUNCATE" option.
DELETE FROM xx_ap_open_invoice_stg;
--
COMMIT;
--
RAISE;
-- Raising exception "e_control_valid_fail" to outer most block.
END; -- End of Control Validation.
--
----------------------------------------------------------------------------------------------------
--Load Data into Pre-Interface Table
----------------------------------------------------------------------------------------------------
BEGIN -- Pre Interface Load Begin
INSERT INTO xx_ap_pre_invoice_interface
(status
, record_id -- Primary Key
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, description
, SOURCE
, request_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
)
SELECT 'NW'
, xx_ap_cnv_03_pre_s.nextval -- Primary Key
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, hdr_description
, SOURCE
, l_request_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'IN';
UPDATE xx_ap_pre_invoice_interface
SET GROUP_ID = l_group_id
WHERE status = 'NW';
INSERT INTO xx_ap_pre_inv_lines_interface
(record_id -- Primary Key
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
)
SELECT xx_ap_cnv_03_line_s.nextval -- Primary Key
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, line_description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'LN';
COMMIT; -- Commiting Pre Interface Load.
--
EXCEPTION -- Pre Interface Exception Block
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
);
--
RAISE;
-- Raising Exception to outer most block
END; -- End of Pre Interface Load Begin
----------------------------------------------------------------------------------------------------
--Updating pre interface table with status 'IP' where record_status in('NW')
----------------------------------------------------------------------------------------------------
BEGIN -- Pre Interface Status Change Block
UPDATE xx_ap_pre_invoice_interface
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; -- End of Pre Interface Status Change Block
END IF; -- End of Conditional checking of p_runmode.
-- Re Initializing the Summary Count Variables.
l_processed_recs := 0;
l_error_recs := 0;
l_successful_recs := 0;
--
----------------------------------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------------------------------
--
BEGIN -- Business Validation Block
FOR c_invoice_rec IN c_invoice_insert
LOOP
--
l_processed_recs := l_processed_recs + 1;
-- Reset Business Error Flag
l_business_error_flag := 'N';
-- validation of source
-- Assuming all invoice records from data file will contain one source only.
BEGIN
SELECT meaning
INTO l_source
FROM fnd_lookup_values
WHERE lookup_type = 'SOURCE'
AND lookup_code = c_invoice_rec.SOURCE
AND view_application_id = 200; -- Payables
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Invoice Source.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception occured while validating Invoice Source.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validation of Invoice Type Lookup Code and Invoice Amount
IF c_invoice_rec.invoice_type_lookup_code = 'STANDARD'
THEN
IF c_invoice_rec.invoice_amount < 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Amount must be Positive when Invoice Type Lookup Code is Standard.'
);
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Amount must be Positive when Invoice Type Lookup Code is Standard.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSIF c_invoice_rec.invoice_type_lookup_code = 'CREDIT'
THEN
IF c_invoice_rec.invoice_amount > 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Amount must be Negative when Invoice Type Lookup Code is Credit.'
);
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Amount must be Negative when Invoice Type Lookup Code is Credit.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSE
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Invoice Type Lookup Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Type Lookup Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- Validation of invoice currency
IF c_invoice_rec.invoice_currency_code IS NOT NULL
THEN
l_currency_exist :=
xx_common_validations_pkg.validate_currency_code
(p_currency_code => c_invoice_rec.invoice_currency_code
, p_process_status => l_process_status
, p_error_message => l_error_message
);
IF TO_NUMBER (l_currency_exist) != 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Invoice Currency Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Currency Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSE
--l_currency_code :='USD';
UPDATE xx_ap_pre_invoice_interface
SET invoice_currency_code = 'USD'
WHERE record_id = c_invoice_rec.record_id;
END IF;
--------------------------------------------------------------------------------
-- Checking Invoice is PO Originated or Not.
--------------------------------------------------------------------------------
IF c_invoice_rec.po_header_id IS NOT NULL
THEN
l_po_origin := TRUE;
ELSE
l_po_origin := FALSE;
END IF;
-- Validation of PO Header ID
IF l_po_origin = TRUE
THEN
l_po_header_exist := 0;
SELECT COUNT ('X')
INTO l_po_header_exist
FROM DUAL
WHERE c_invoice_rec.po_header_id = ANY (SELECT po_header_id
FROM po_headers_all
WHERE approved_flag = 'Y'
AND closed_date IS NULL);
IF l_po_header_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Header ID for PO Matched Invoice.');
xx_emf.call_store_message
(p_message_group => '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 PO Header ID for PO Matched Invoice.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE -- If PO header ID is valid
-- Update xx_ap_pre_invoice_interface with appropriate po_number
BEGIN
SELECT segment1
INTO l_po_number
FROM po_headers_all
WHERE po_header_id = c_invoice_rec.po_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Error in getting po_number. Oracle Error - ' || SQLERRM);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
,
-- low level debugging
p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in getting po_number. Oracle Error - ' || SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
UPDATE xx_ap_pre_invoice_interface
SET po_number = l_po_number
WHERE record_id = c_invoice_rec.record_id;
END IF;
END IF;
-- Validation of Vendor Num
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.vendor_num IS NOT NULL
THEN
l_vendor_exist := 0;
SELECT COUNT ('X')
INTO l_vendor_exist
FROM DUAL
WHERE c_invoice_rec.vendor_num =
(SELECT pv.segment1
FROM po_vendors pv, po_headers_all pha
WHERE pv.vendor_id = pha.vendor_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_vendor_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Vendor Number is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Vendor Number is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
l_vendor_exist := 0;
SELECT COUNT ('X')
INTO l_vendor_exist
FROM DUAL
WHERE c_invoice_rec.vendor_num = ANY (SELECT segment1
FROM po_vendors);
IF l_vendor_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Vendor Number.');
xx_emf.call_store_message
(p_message_group => '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 Vendor Number.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validation of Unique Invoice Number for each Supplier
BEGIN
SELECT COUNT('X')
INTO l_invoice_num
FROM ap_invoices_all inv
WHERE inv.invoice_num = c_invoice_rec.invoice_num
AND inv.vendor_id = (SELECT pv.vendor_id
FROM po_vendors pv
WHERE pv.segment1 = c_invoice_rec.vendor_num);
--
IF l_invoice_num >= 1
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Number already Exists for this Supplier. Use Unique Invoice number for each supplier.');
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Number already Exists for this Supplier. Use Unique Invoice number for each supplier.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Error at getting Invoice number.Oracle Error - ' || SQLERRM);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error at getting Invoice number.Oracle Error - ' || SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- Validation of Vendor Site Code
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.vendor_site_code IS NOT NULL
THEN
l_site_code_exist := 0;
SELECT COUNT ('X')
INTO l_site_code_exist
FROM DUAL
WHERE c_invoice_rec.vendor_site_code = ANY (
SELECT pvs.vendor_site_code
FROM po_vendors pv
, po_headers_all pha
, po_vendor_sites_all pvs
WHERE pv.vendor_id = pha.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_site_code_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Vendor Site Code is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Vendor Site Code is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
l_site_code_exist := 0;
SELECT COUNT ('X')
INTO l_site_code_exist
FROM DUAL
WHERE c_invoice_rec.vendor_site_code = ANY (
SELECT pvs.vendor_site_code
FROM po_vendor_sites_all pvs
, po_vendors pv
WHERE pv.vendor_id = pvs.vendor_id
AND pv.segment1 = c_invoice_rec.vendor_num);
IF l_site_code_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Vendor Site Code.');
xx_emf.call_store_message
(p_message_group => '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 Vendor Site Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validation of Payment Terms
IF c_invoice_rec.terms_name IS NOT NULL
THEN
l_terms_exist := 0;
SELECT COUNT ('X')
INTO l_terms_exist
FROM DUAL
WHERE c_invoice_rec.terms_name = ANY (SELECT NAME
FROM ap_terms);
IF l_terms_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Payment Terms.');
xx_emf.call_store_message
(p_message_group => '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 Payment Terms.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
/* Operating Unit is not validated as org id is removed from Data file
-- Validation of Operating Unit
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.org_id IS NOT NULL
THEN
l_org_exist := 0;
SELECT COUNT ('X')
INTO l_org_exist
FROM DUAL
WHERE c_invoice_rec.org_id =
(SELECT org.organization_id
FROM org_organization_definitions org
, po_headers_all pha
WHERE org.organization_id = pha.org_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_org_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Organization ID is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => '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 => 'Organization ID is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
IF c_invoice_rec.org_id IS NOT NULL
THEN
l_org_exist := 0;
SELECT COUNT ('X')
INTO l_org_exist
FROM DUAL
WHERE c_invoice_rec.org_id = ANY (
SELECT organization_id
FROM org_organization_definitions);
IF l_org_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Organization ID is not valid.');
xx_emf.call_store_message
(p_message_group => '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 => 'Organization ID is not valid.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
END IF;
Operating Unit is not validated as org id is removed from Data file*/
IF l_business_error_flag = 'Y'
THEN
-- update xx_ap_pre_invoice_interface with status = 'ER'
UPDATE xx_ap_pre_invoice_interface
SET status = 'ER'
WHERE record_id = c_invoice_rec.record_id;
--
ELSE
-- Reset Business Line Error Flag
l_business_line_error_flag := 'N';
-- Processing the Invoice line Cursor
FOR c_invoice_line_rec IN
c_invoice_line_insert (c_invoice_rec.invoice_id)
LOOP
l_processed_recs := l_processed_recs + 1;
--
-- Validating Line Type Lookup Code.
l_line_lookup_exist := 0;
SELECT COUNT ('X')
INTO l_line_lookup_exist
FROM DUAL
WHERE c_invoice_line_rec.line_type_lookup_code = ANY (
SELECT lookup_code
FROM ap_lookup_codes
WHERE lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND lookup_code = 'ITEM');
IF l_line_lookup_exist = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid Invoice Line Type Lookup Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Line Type Lookup Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
/*-- Matching PO Header ID at Preinterface - Invoice and Line Level
IF l_po_origin = TRUE
THEN
IF c_invoice_line_rec.po_header_id != c_invoice_rec.po_header_id
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('PO Header ID is not Matching at Preinterface - Invoice and Line Level.');
xx_emf.call_store_message
(p_message_group => '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 => 'PO Header ID is not Matching at Preinterface - Invoice and Line Level.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;*/
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
-- Validation of PO Line ID
l_po_line_exists := 0;
SELECT COUNT ('X')
INTO l_po_line_exists
FROM DUAL
WHERE c_invoice_line_rec.po_line_id = ANY (
SELECT pla.po_line_id
FROM po_lines_all pla
, po_headers_all pha
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_line_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Line ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Line ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
-- Validation of PO Line Location ID
l_po_location_exists := 0;
SELECT COUNT ('X')
INTO l_po_location_exists
FROM DUAL
WHERE c_invoice_line_rec.po_line_location_id = ANY (
SELECT pll.line_location_id
FROM po_lines_all pla
, po_headers_all pha
, po_line_locations_all pll
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_location_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Line Location ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Line Location ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
-- Validation of PO Distribution ID
l_po_dist_exists := 0;
SELECT COUNT ('X')
INTO l_po_dist_exists
FROM DUAL
WHERE c_invoice_line_rec.po_distribution_id = ANY (
SELECT pda.po_distribution_id
FROM po_lines_all pla
, po_headers_all pha
, po_line_locations_all pll
, po_distributions_all pda
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.line_location_id = pll.line_location_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_dist_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Distribution ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Distribution ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validate RCV Transaction ID if PO is 3 way Matched
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
l_rcv_trans_exists := 0;
-- Get Match Option from po_line_locations_all
BEGIN
SELECT pll.match_option
INTO l_match_option
FROM po_line_locations_all pll
, po_lines_all pla
, po_headers_all pha
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pla.po_line_id = c_invoice_line_rec.po_line_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL;
EXCEPTION
WHEN OTHERS
THEN
l_match_option := NULL;
END;
IF l_match_option = 'R' -- If Po is Receipt Matched
THEN
IF c_invoice_line_rec.rcv_transaction_id IS NULL
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('RCV Transaction ID is NULL while PO is 3 way matched.');
xx_emf.call_store_message
(p_message_group => '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 => 'RCV Transaction ID is NULL while PO is 3 way matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
-- Validate RCV Transaction ID
SELECT COUNT ('X')
INTO l_rcv_trans_exists
FROM DUAL
WHERE c_invoice_line_rec.rcv_transaction_id = ANY (
SELECT rt.transaction_id
FROM rcv_transactions rt
, po_headers_all pha
WHERE rt.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_rcv_trans_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid RCV Transaction ID though PO is 3 way matched.'
);
xx_emf.call_store_message
(p_message_group => '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 RCV Transaction ID though PO is 3 way matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF; -- c_invoice_line_rec.rcv_transaction_id IS NULL
END IF; -- l_match_option = 'R'
END IF; -- l_po_origin = TRUE
IF l_po_origin = TRUE
THEN
-- Update the dist_code_combination_id column of xx_ap_pre_inv_lines_interface table
-- with NULL because of System Setup in VISION.
UPDATE xx_ap_pre_inv_lines_interface
SET dist_code_combination_id = NULL
WHERE invoice_line_id =
c_invoice_line_rec.invoice_line_id
AND invoice_id = c_invoice_rec.invoice_id;
--
ELSE -- If Invoice is not PO Originated
-- Validate Dist Code Concatenated Segments and get CCID
l_ccid_exist :=
validate_ccsegment
(p_ccsegment => c_invoice_line_rec.dist_code_concatenated
, p_ccid => l_ccid
, p_error_message => l_err_message
);
IF l_ccid_exist != 'VALID'
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid Dist Code Concatenated Segment.');
xx_emf.call_store_message
(p_message_group => '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 Dist Code Concatenated Segment.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE -- if l_ccid_exist = 'VALID'
--
-- Update the dist_code_combination_id column of xx_ap_pre_inv_lines_interface table
-- with ccid generated from dist_code_concatenated.
UPDATE xx_ap_pre_inv_lines_interface
SET dist_code_combination_id = l_ccid
WHERE invoice_line_id =
c_invoice_line_rec.invoice_line_id
AND invoice_id = c_invoice_rec.invoice_id;
--
END IF;
END IF; -- l_po_origin = TRUE
--
END LOOP; -- End of Cursor c_invoice_line_insert
IF l_business_line_error_flag = 'Y'
THEN
-- update xx_ap_pre_invoice_interface with status = 'ER'
-- if invoice line is errored out.
UPDATE xx_ap_pre_invoice_interface
SET status = 'ER'
WHERE record_id = c_invoice_rec.record_id;
END IF;
END IF; -- l_business_error_flag = 'Y'
END LOOP; -- End of Cursor c_invoice_insert
--
-- COMMIT;
--
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-00004'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE;
-- Raise error to outermost exception block
END; -- End of Business Validation Block
-----------------------------------------------------------------------------------------------
--Move Data to Interface Tables
-----------------------------------------------------------------------------------------------
BEGIN -- Insert into Interface
--
FOR c_invoice_rec IN c_invoice_insert
LOOP
--
l_successful_recs := l_successful_recs + 1;
--
INSERT INTO ap_invoices_interface
(invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, po_number
, vendor_id
, vendor_num
, vendor_name
, vendor_site_id
, vendor_site_code
, invoice_amount
, invoice_currency_code
, exchange_rate
, exchange_rate_type
, exchange_date
, terms_id
, terms_name
, description
, awt_group_id
, awt_group_name
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, global_attribute_category
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, status
, SOURCE
, GROUP_ID
, request_id
, payment_cross_rate_type
, payment_cross_rate_date
, payment_cross_rate
, payment_currency_code
, workflow_flag
, doc_category_code
, voucher_num
, payment_method_lookup_code
, pay_group_lookup_code
, goods_received_date
, invoice_received_date
, gl_date
, accts_pay_code_combination_id
, ussgl_transaction_code
, exclusive_payment_flag
, org_id
, amount_applicable_to_discount
, prepay_num
, prepay_dist_num
, prepay_apply_amount
, prepay_gl_date
, invoice_includes_prepay_flag
, no_xrate_base_amount
, vendor_email_address
, terms_date
, requester_id
, ship_to_location
, external_doc_ref
)
VALUES (c_invoice_rec.invoice_id
, c_invoice_rec.invoice_num
, c_invoice_rec.invoice_type_lookup_code
, c_invoice_rec.invoice_date
, c_invoice_rec.po_number
, c_invoice_rec.vendor_id
, c_invoice_rec.vendor_num
, c_invoice_rec.vendor_name
, c_invoice_rec.vendor_site_id
, c_invoice_rec.vendor_site_code
, c_invoice_rec.invoice_amount
, c_invoice_rec.invoice_currency_code
, c_invoice_rec.exchange_rate
, c_invoice_rec.exchange_rate_type
, c_invoice_rec.exchange_date
, c_invoice_rec.terms_id
, c_invoice_rec.terms_name
, c_invoice_rec.description
, c_invoice_rec.awt_group_id
, c_invoice_rec.awt_group_name
, c_invoice_rec.last_update_date
, c_invoice_rec.last_updated_by
, c_invoice_rec.last_update_login
, c_invoice_rec.creation_date
, c_invoice_rec.created_by
, c_invoice_rec.attribute_category
, c_invoice_rec.attribute1
, c_invoice_rec.attribute2
, c_invoice_rec.attribute3
, c_invoice_rec.attribute4
, c_invoice_rec.attribute5
, c_invoice_rec.attribute6
, c_invoice_rec.attribute7
, c_invoice_rec.attribute8
, c_invoice_rec.attribute9
, c_invoice_rec.attribute10
, c_invoice_rec.attribute11
, c_invoice_rec.attribute12
, c_invoice_rec.attribute13
, c_invoice_rec.attribute14
, c_invoice_rec.attribute15
, c_invoice_rec.global_attribute_category
, c_invoice_rec.global_attribute1
, c_invoice_rec.global_attribute2
, c_invoice_rec.global_attribute3
, c_invoice_rec.global_attribute4
, c_invoice_rec.global_attribute5
, c_invoice_rec.global_attribute6
, c_invoice_rec.global_attribute7
, c_invoice_rec.global_attribute8
, c_invoice_rec.global_attribute9
, c_invoice_rec.global_attribute10
, c_invoice_rec.global_attribute11
, c_invoice_rec.global_attribute12
, c_invoice_rec.global_attribute13
, c_invoice_rec.global_attribute14
, c_invoice_rec.global_attribute15
, c_invoice_rec.global_attribute16
, c_invoice_rec.global_attribute17
, c_invoice_rec.global_attribute18
, c_invoice_rec.global_attribute19
, c_invoice_rec.global_attribute20
, NULL -- STATUS
, c_invoice_rec.SOURCE
, c_invoice_rec.GROUP_ID
, NULL -- REQUEST ID
, c_invoice_rec.payment_cross_rate_type
, c_invoice_rec.payment_cross_rate_date
, c_invoice_rec.payment_cross_rate
, c_invoice_rec.payment_currency_code
, c_invoice_rec.workflow_flag
, c_invoice_rec.doc_category_code
, c_invoice_rec.voucher_num
, c_invoice_rec.payment_method_lookup_code
, c_invoice_rec.pay_group_lookup_code
, c_invoice_rec.goods_received_date
, c_invoice_rec.invoice_received_date
, c_invoice_rec.gl_date
, c_invoice_rec.accts_pay_code_combination_id
, c_invoice_rec.ussgl_transaction_code
, c_invoice_rec.exclusive_payment_flag
, c_invoice_rec.org_id
, c_invoice_rec.amount_applicable_to_discount
, c_invoice_rec.prepay_num
, c_invoice_rec.prepay_dist_num
, c_invoice_rec.prepay_apply_amount
, c_invoice_rec.prepay_gl_date
, c_invoice_rec.invoice_includes_prepay_flag
, c_invoice_rec.no_xrate_base_amount
, c_invoice_rec.vendor_email_address
, c_invoice_rec.terms_date
, c_invoice_rec.requester_id
, c_invoice_rec.ship_to_location
, c_invoice_rec.external_doc_ref
);
--
-- Move data to Invoice Interface Line Table.
FOR c_invoice_line_rec IN
c_invoice_line_insert (c_invoice_rec.invoice_id)
LOOP
--
l_successful_recs := l_successful_recs + 1;
--
INSERT INTO ap_invoice_lines_interface
(invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, line_group_number
, amount
, accounting_date
, description
, amount_includes_tax_flag
, prorate_across_flag
, tax_code
, final_match_flag
, po_header_id
, po_number
, po_line_id
, po_line_number
, po_line_location_id
, po_shipment_num
, po_distribution_id
, po_distribution_num
, po_unit_of_measure
, inventory_item_id
, item_description
, quantity_invoiced
, ship_to_location_code
, unit_price
, distribution_set_id
, distribution_set_name
, dist_code_concatenated
, dist_code_combination_id
, awt_group_id
, awt_group_name
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, global_attribute_category
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, po_release_id
, release_num
, account_segment
, balancing_segment
, cost_center_segment
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
, pa_quantity
, ussgl_transaction_code
, stat_amount
, type_1099
, income_tax_region
, assets_tracking_flag
, price_correction_flag
, org_id
, receipt_number
, receipt_line_number
, match_option
, packing_slip
, rcv_transaction_id
, pa_cc_ar_invoice_id
, pa_cc_ar_invoice_line_num
, reference_1
, reference_2
, pa_cc_processed_code
, tax_recovery_rate
, tax_recovery_override_flag
, tax_recoverable_flag
, tax_code_override_flag
, tax_code_id
, credit_card_trx_id
, award_id
, vendor_item_num
, taxable_flag
, price_correct_inv_num
, external_doc_line_ref
)
VALUES (c_invoice_line_rec.invoice_id
, c_invoice_line_rec.invoice_line_id
, c_invoice_line_rec.line_number
, c_invoice_line_rec.line_type_lookup_code
, c_invoice_line_rec.line_group_number
, c_invoice_line_rec.amount
, c_invoice_line_rec.accounting_date
, c_invoice_line_rec.description
, c_invoice_line_rec.amount_includes_tax_flag
, c_invoice_line_rec.prorate_across_flag
, c_invoice_line_rec.tax_code
, c_invoice_line_rec.final_match_flag
, c_invoice_line_rec.po_header_id
, c_invoice_line_rec.po_number
, c_invoice_line_rec.po_line_id
, c_invoice_line_rec.po_line_number
, c_invoice_line_rec.po_line_location_id
, c_invoice_line_rec.po_shipment_num
, c_invoice_line_rec.po_distribution_id
, c_invoice_line_rec.po_distribution_num
, c_invoice_line_rec.po_unit_of_measure
, c_invoice_line_rec.inventory_item_id
, c_invoice_line_rec.item_description
, c_invoice_line_rec.quantity_invoiced
, c_invoice_line_rec.ship_to_location_code
, c_invoice_line_rec.unit_price
, c_invoice_line_rec.distribution_set_id
, c_invoice_line_rec.distribution_set_name
, NULL -- dist_code_concatenated is passed NULL to avoid Account Overlay.
, c_invoice_line_rec.dist_code_combination_id
, c_invoice_line_rec.awt_group_id
, c_invoice_line_rec.awt_group_name
, c_invoice_line_rec.last_updated_by
, c_invoice_line_rec.last_update_date
, c_invoice_line_rec.last_update_login
, c_invoice_line_rec.created_by
, c_invoice_line_rec.creation_date
, c_invoice_line_rec.attribute_category
, c_invoice_line_rec.attribute1
, c_invoice_line_rec.attribute2
, c_invoice_line_rec.attribute3
, c_invoice_line_rec.attribute4
, c_invoice_line_rec.attribute5
, c_invoice_line_rec.attribute6
, c_invoice_line_rec.attribute7
, c_invoice_line_rec.attribute8
, c_invoice_line_rec.attribute9
, c_invoice_line_rec.attribute10
, c_invoice_line_rec.attribute11
, c_invoice_line_rec.attribute12
, c_invoice_line_rec.attribute13
, c_invoice_line_rec.attribute14
, c_invoice_line_rec.attribute15
, c_invoice_line_rec.global_attribute_category
, c_invoice_line_rec.global_attribute1
, c_invoice_line_rec.global_attribute2
, c_invoice_line_rec.global_attribute3
, c_invoice_line_rec.global_attribute4
, c_invoice_line_rec.global_attribute5
, c_invoice_line_rec.global_attribute6
, c_invoice_line_rec.global_attribute7
, c_invoice_line_rec.global_attribute8
, c_invoice_line_rec.global_attribute9
, c_invoice_line_rec.global_attribute10
, c_invoice_line_rec.global_attribute11
, c_invoice_line_rec.global_attribute12
, c_invoice_line_rec.global_attribute13
, c_invoice_line_rec.global_attribute14
, c_invoice_line_rec.global_attribute15
, c_invoice_line_rec.global_attribute16
, c_invoice_line_rec.global_attribute17
, c_invoice_line_rec.global_attribute18
, c_invoice_line_rec.global_attribute19
, c_invoice_line_rec.global_attribute20
, c_invoice_line_rec.po_release_id
, c_invoice_line_rec.release_num
, c_invoice_line_rec.account_segment
, c_invoice_line_rec.balancing_segment
, c_invoice_line_rec.cost_center_segment
, c_invoice_line_rec.project_id
, c_invoice_line_rec.task_id
, c_invoice_line_rec.expenditure_type
, c_invoice_line_rec.expenditure_item_date
, c_invoice_line_rec.expenditure_organization_id
, c_invoice_line_rec.project_accounting_context
, c_invoice_line_rec.pa_addition_flag
, c_invoice_line_rec.pa_quantity
, c_invoice_line_rec.ussgl_transaction_code
, c_invoice_line_rec.stat_amount
, c_invoice_line_rec.type_1099
, c_invoice_line_rec.income_tax_region
, c_invoice_line_rec.assets_tracking_flag
, c_invoice_line_rec.price_correction_flag
, c_invoice_line_rec.org_id
, c_invoice_line_rec.receipt_number
, c_invoice_line_rec.receipt_line_number
, c_invoice_line_rec.match_option
, c_invoice_line_rec.packing_slip
, c_invoice_line_rec.rcv_transaction_id
, c_invoice_line_rec.pa_cc_ar_invoice_id
, c_invoice_line_rec.pa_cc_ar_invoice_line_num
, c_invoice_line_rec.reference_1
, c_invoice_line_rec.reference_2
, c_invoice_line_rec.pa_cc_processed_code
, c_invoice_line_rec.tax_recovery_rate
, c_invoice_line_rec.tax_recovery_override_flag
, c_invoice_line_rec.tax_recoverable_flag
, c_invoice_line_rec.tax_code_override_flag
, c_invoice_line_rec.tax_code_id
, c_invoice_line_rec.credit_card_trx_id
, c_invoice_line_rec.award_id
, c_invoice_line_rec.vendor_item_num
, c_invoice_line_rec.taxable_flag
, c_invoice_line_rec.price_correct_inv_num
, c_invoice_line_rec.external_doc_line_ref
);
END LOOP; -- End of Cursor c_invoice_line_insert
--
END LOOP; -- End of Cursor c_invoice_insert
--
COMMIT;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into AP Open Invoice Interface Tables.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception While Inserting into AP Open Invoice Interface Tables.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE;
-- Raise error to outermost block
END; -- End of Insert into Interface Block
----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status is 'IP'
----------------------------------------------------------------------------------
BEGIN
SELECT COUNT(invoice_id)
INTO l_processed_rec_cnt
FROM xx_ap_pre_invoice_interface
WHERE status = 'IP';
UPDATE xx_ap_pre_invoice_interface
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-00005'
, p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END; -- End of Preinterface update block
--Initialize apps
--fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_processed_rec_cnt > 0
THEN -- If Pre Interface table has Processed records to be imported
--
---------------------------------------------------------------------------------------
--Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------
BEGIN
l_standard_request_id :=
fnd_request.submit_request (application => 'SQLAP'
, program => 'APXIIMPT'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_source -- SOURCE
, argument2 => l_group_id -- GROUP
, argument3 => l_group_id -- BATCH NAME
, argument4 => NULL -- HOLD NAME
, argument5 => NULL -- HOLD REASON
, argument6 => NULL -- GL_DATE
, argument7 => NULL -- PURGE
, argument8 => 'Y' -- SUMMARIZE REPORT
);
--
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
);
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Running Standard Concurrent Program.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-00006'
, p_identifier2 => 'Exception While Running Standard Concurrent Program.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END; -- End of Launch Concurrent Program Block
END IF; -- If Pre Interface table has Processed records to be imported
--
------------------------------------------------------------------------------------
-- Purge All Processed Records from Pre Interface Tables.
------------------------------------------------------------------------------------
BEGIN
FOR c_invoice_purge_rec IN c_invoice_purge
LOOP
--
FOR c_invoice_line_purge_rec IN c_invoice_line_purge(c_invoice_purge_rec.invoice_id)
LOOP
DELETE FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = c_invoice_purge_rec.invoice_id;
END LOOP;
--
DELETE FROM xx_ap_pre_invoice_interface
WHERE invoice_id = c_invoice_purge_rec.invoice_id;
END LOOP;
--
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h('Exception while purging records from pre-interface table - '|| SQLERRM);
END;
--
------------------------------------------------------------------------------------
-- Call Insert Summary Count
-- Purpose: Display a summary of records processed, successful and in error
------------------------------------------------------------------------------------
l_error_recs := l_processed_recs - l_successful_recs;
--
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
--
--------------------------------------------------------------------------------------------
-- Call flush PL/SQL table section
-- Purpose : Flush data from pl/sql table isrespective of the count.
-- Special Logic : record's message group is passed as 'ALL' and rest all columns are null
--------------------------------------------------------------------------------------------
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
----------------------------------------------------------------------------
-- Set display width for output
-- Purpose: Call to set EMF error and report section column widths
----------------------------------------------------------------------------
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
----------------------------------------------------------------
-- Call to xx_emf.display_output_messages
-- Purpose--Call to generate output file (Errors/Messages)
----------------------------------------------------------------
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
----------------------------------------------------
-- Call To EMF 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 for Error.');
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 for Warning.');
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 Block
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 --High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
-------------------------------------------------------------------------------------
-- Call To Insert Summary Count
-------------------------------------------------------------------------------------
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
------------------------------------------------------------------------------------------
-- Call flush PL/SQL table section
-- Purpose : Flush data from pl/sql table isrespective of the count.
-- Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
----------------------------------------------------------------------------
-- Purpose: Call to set EMF error and report section column widths
----------------------------------------------------------------------------
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
----------------------------------------------------------------
-- Purpose--Call to generate output file (Errors/Messages)
----------------------------------------------------------------
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
--
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
-------------------------------------------------------------------
-- Call update program status
-------------------------------------------------------------------
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
-------------------------------------------------------------------------------------------------------
--Function : validate_ccsegment
--Purpose : validates the concatenated code combination segments.
-------------------------------------------------------------------------------------------------------
FUNCTION validate_ccsegment (
p_ccsegment IN xx_ap_pre_inv_lines_interface.dist_code_concatenated%TYPE
, p_ccid OUT NUMBER
, p_error_message OUT VARCHAR2
)
RETURN VARCHAR2
IS
-- common variables
l_cc_length NUMBER;
l_dot_occurence NUMBER := 0;
l_cnt_dot_occur NUMBER := 1;
l_str_start NUMBER := 0;
p_segment1 gl_code_combinations.segment1%TYPE;
p_segment2 gl_code_combinations.segment2%TYPE;
p_segment3 gl_code_combinations.segment3%TYPE;
p_segment4 gl_code_combinations.segment4%TYPE;
p_segment5 gl_code_combinations.segment5%TYPE;
-- xx_common_validations_pkg.get_coa_ccid related variables
l_process_status NUMBER;
l_ccid_exist VARCHAR2 (20);
BEGIN
BEGIN
l_cc_length := LENGTH (p_ccsegment);
-- Assuming there are 5 segments and 4 segment delimeter
FOR l_cnt_dot_occur IN 1 .. 5
LOOP
-- get starting point of Substring
l_str_start := l_dot_occurence + 1;
--
-- get the position of segment deimeter.
-- Assuming that segment delimeter is '.'
-- g_cc_seg_delimeter is '.'
--
l_dot_occurence := INSTR (p_ccsegment, g_cc_seg_delimeter, 1, l_cnt_dot_occur);
--
CASE l_cnt_dot_occur
WHEN 1
THEN
p_segment1 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 2
THEN
p_segment2 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 3
THEN
p_segment3 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 4
THEN
p_segment4 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
ELSE
p_segment5 :=
SUBSTR (p_ccsegment, l_str_start, l_cc_length);
END CASE;
END LOOP;
--
l_ccid_exist :=
xx_common_validations_pkg.get_coa_ccid
(p_segment1 => p_segment1
, p_segment2 => p_segment2
, p_segment3 => p_segment3
, p_segment4 => p_segment4
, p_segment5 => p_segment5
, p_process_status => l_process_status
, p_error_message => p_error_message
, p_ccid => p_ccid
);
--
EXCEPTION
WHEN OTHERS
THEN
l_ccid_exist := 'INVALID';
END;
RETURN l_ccid_exist;
END validate_ccsegment;
--
END xx_ap_invoice_conv_pkg;
/
-- File Name : xapcnv03a.ctl
-- File Type : SQL*Loader Control file.
-- RICEW Object id : AP_CNV_03
-- Description : This SQL*Loader file is used to load data
-- from flat file to the staging table XX_AP_OPEN_INVOICE_STG
-- Maintenance History:
--
-- Date Version Name Remarks
-- ----------- --------- ----------------- ---------------------------------------
-- 27-Dec-14 1.0 Madhu dhare Draft Version.
--
-- =====================================================================================
OPTIONS (ROWS=1)
LOAD DATA
--INFILE 'd:\oravis\viscustom\11.5.0\bin\xap01a.dat'
BADFILE 'd:\oravis\viscustom\11.5.0\bin\xap01a.bad'
TRUNCATE
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'BC'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, BATCH_NUMBER CHAR
, BATCH_AMOUNT CHAR
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'IN'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, INVOICE_ID "AP_INVOICES_INTERFACE_S.NEXTVAL"
, INVOICE_NUM CHAR
, INVOICE_TYPE_LOOKUP_CODE CHAR
, INVOICE_DATE DATE
, PO_NUMBER
, PO_HEADER_ID
, VENDOR_NUM CHAR
, VENDOR_SITE_CODE CHAR
, INVOICE_AMOUNT
, INVOICE_CURRENCY_CODE CHAR
, TERMS_NAME CHAR
, HDR_DESCRIPTION CHAR
, SOURCE CHAR
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
INTO TABLE XX_AP_OPEN_INVOICE_STG
WHEN TRANSACTION_CODE = 'LN'
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRANSACTION_CODE POSITION(1)
, RECORD_ID "XX_AP_CNV_03_STG_S.NEXTVAL"
, INVOICE_ID "AP_INVOICES_INTERFACE_S.CURRVAL"
, INVOICE_LINE_ID "AP_INVOICE_LINES_INTERFACE_S.NEXTVAL"
, LINE_NUMBER CHAR
, LINE_TYPE_LOOKUP_CODE CHAR
, AMOUNT
, ACCOUNTING_DATE DATE
, LINE_DESCRIPTION CHAR
, PO_LINE_ID
, PO_LINE_LOCATION_ID
, PO_DISTRIBUTION_ID
, DIST_CODE_CONCATENATED CHAR
, RCV_TRANSACTION_ID
, LAST_UPDATE_DATE "TO_CHAR(SYSDATE)"
, LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
, LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID"
, CREATION_DATE "TO_CHAR(SYSDATE)"
, CREATED_BY "FND_GLOBAL.USER_ID"
)
CREATE OR REPLACE PACKAGE xx_ap_invoice_conv_pkg
AS
/*
-------------------------------------------------------------
Package Name : xx_ap_invoice_conv_pkg
Author's Name : Madhu Dhare
Date Written :18-Dec-2014
RICEW Object id : AP_CNV_03
Program Style : Package Specification
Purpose : AP Open Invoice Conversion
Maintenance History:
Date: Name Remarks
----------- ------------- ------------------
19-Dec-2006 Madhu Dhare Draft Version
--------------------------------------------------------------
*/
-----------------------------------------------------------
-- 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 := 40;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 20;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 0;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
--
-- Code Combination Segment Delimeter used in Private Function "validate_ccsegment"
g_cc_seg_delimeter VARCHAR2(1) := '.';
-------------------------------------------------------------------
-- Public Procedure/Function Declaration Section
-- Purpose-Main calling Procedure for AP Open Invoice Conversion
-------------------------------------------------------------------
PROCEDURE MAIN (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_runmode IN VARCHAR2);
--
END xx_ap_invoice_conv_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_ap_invoice_conv_pkg
AS
--------------------------------------------------------------------------------------------------------
/*
Package Name : xx_ap_invoice_conv_pkg
Author's Name : Madhu Dhare
Date Written :18-Dec-2014
RICEW Object id : AP_CNV_03
Program Style : Package Body
Purpose : AP Open Invoice Conversion
--
Maintenance History
Date Issue# Name Remarks
----------- ---------------- ----------------- -------------
19-Dec-14 1.0 Madhu Dhare Draft Version
--------------------------------------------------------------------------------------------------------*/
-------------------------------------------------------------------------------------------------------
--Function : validate_ccsegment
--Purpose : validates the concatenated code combination segments.
-------------------------------------------------------------------------------------------------------
FUNCTION validate_ccsegment (
p_ccsegment IN xx_ap_pre_inv_lines_interface.dist_code_concatenated%TYPE
, p_ccid OUT NUMBER
, p_error_message OUT VARCHAR2
)
RETURN VARCHAR2;
-------------------------------------------------------------------------------------------------------
--Procedure : main
--Purpose : ap open invoice conversion
-------------------------------------------------------------------------------------------------------
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_runmode IN VARCHAR2
)
IS
----------------------------------------------------------------------------------------------------
--Cursor Declaration Section
----------------------------------------------------------------------------------------------------
--Cursor to select Batch data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_bc_stg
IS
SELECT record_id
, transaction_code
, batch_number
, batch_amount
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'BC';
--Cursor to select Invoice data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_in_stg
IS
SELECT record_id
, transaction_code
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, hdr_description
, SOURCE
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'IN';
--Cursor to select Invoice Line data from staging table XX_AP_OPEN_INVOICE_STG
CURSOR c_invoice_ln_stg (
cp_invoice_id xx_ap_open_invoice_stg.invoice_id%TYPE
)
IS
SELECT record_id
, transaction_code
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, line_description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'LN' AND invoice_id = cp_invoice_id;
--Cursor to select Invoice data from Pre Interface table XX_AP_PRE_INVOICE_INTERFACE
CURSOR c_invoice_insert
IS
SELECT *
FROM xx_ap_pre_invoice_interface
WHERE status = 'IP';
-- FOR UPDATE;
--Cursor to select Invoice Line data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_line_insert (
cp_invoice_id xx_ap_pre_invoice_interface.invoice_id%TYPE
)
IS
SELECT *
FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = cp_invoice_id;
--Cursor to select Processed Invoice data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_purge
IS
SELECT invoice_id
FROM xx_ap_pre_invoice_interface
WHERE status = 'PR';
--Cursor to select Invoice Line data from Pre Interface Line table XX_AP_PRE_INV_LINES_INTERFACE
CURSOR c_invoice_line_purge (
cp_invoice_id xx_ap_pre_invoice_interface.invoice_id%TYPE
)
IS
SELECT invoice_line_id
FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = cp_invoice_id;
----------------------------------------------------------------------------------------------------
--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_error_message VARCHAR2 (2000);
l_organization_id NUMBER;
l_process_id NUMBER;
l_count NUMBER;
l_processed_rec_cnt NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_AP_INVOICE_CONV_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'AP_CNV_03';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_phase VARCHAR2 (200);
l_vstatus VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
-- Control Validations Variables
l_control_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at control validation (Y=ERROR/N=SUCCESS)
l_batch_total xx_ap_open_invoice_stg.batch_amount%TYPE
:= 0;
l_invoice_hdr_total xx_ap_open_invoice_stg.invoice_amount%TYPE := 0;
l_invoice_line_total xx_ap_open_invoice_stg.amount%TYPE := 0;
l_group_id xx_ap_open_invoice_stg.batch_number%TYPE;
--
l_po_origin BOOLEAN := FALSE;
-- Flag to indicate the origin of Invoice. (TRUE-"generated from PO" / FALSE-"Not generated from PO")
-- Business Validations Variables (Invoice)
l_source fnd_lookup_values.meaning%TYPE;
l_business_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at business validation (Y=ERROR/N=SUCCESS)
l_business_line_error_flag VARCHAR2 (1) := 'N';
--Flag to capture error at business validation at line level(Y=ERROR/N=SUCCESS)
l_currency_exist VARCHAR2 (1);
l_currency_code ap_invoices_interface.invoice_currency_code%TYPE;
l_vendor_exist NUMBER;
l_site_code_exist NUMBER;
l_terms_exist NUMBER;
--l_org_exist NUMBER;
l_po_header_exist NUMBER;
l_acc_date DATE;
l_date_status NUMBER;
l_date_message VARCHAR2(200);
l_invoice_num NUMBER;
-- Business Validations Variables (Invoice Lines)
l_ccid_exist VARCHAR2 (20);
l_ccid gl_code_combinations.code_combination_id%TYPE;
l_err_message VARCHAR2 (2000);
l_line_lookup_exist NUMBER;
l_po_line_exists NUMBER;
l_po_location_exists NUMBER;
l_po_dist_exists NUMBER;
l_po_number po_headers_all.segment1%TYPE;
l_rcv_trans_exists NUMBER;
l_match_option po_line_locations_all.match_option%TYPE;
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_control_valid_fail EXCEPTION;
--Stop the program if Control level validation fails at Staging Table.
--
BEGIN -- Main Begin
-- Get the Concurrent Request ID.
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
--Initialize Trace
--Purpose : Set the program environment for Tracing
xx_trace.set_program_environment (p_program_name => l_program_name);
-- Initialize EMF Section
-- Purpose : EMF is initialized so that error can be logged.
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name
, p_program_type => g_program_type
, p_ricew_id => l_ricewid
, p_request_id => l_request_id
);
IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;
--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
--Insert EMF Header
--Purpose : To tag the Error labels to EMF columns
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Error Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Record ID'; --Third Error Header
l_error_rec.identifier4 := 'Invoice ID'; --Fourth Error Header
l_error_rec.identifier5 := 'Invoice Line ID'; --Fifth Error Header
-- l_error_rec.identifier6 := NULL; --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;
-- p_runmode determines the execution of code.
-- valid values for p_runmode are 'F' - FULL / 'P' - PARTIAL
-- On FULL mode data is processed from Staging table
-- On PARTIAL mode rectified data will be processed from Preinterface tables.
IF p_runmode = 'F'
THEN
----------------------------------------------------------------------------------------------------
-- Fetch Staging Table Data and Perform Control Validation.
----------------------------------------------------------------------------------------------------
BEGIN -- Control Validation Begin
-- Initialize the Amount totals.
l_invoice_hdr_total := 0;
l_invoice_line_total := 0;
FOR c_invoice_bc_rec IN c_invoice_bc_stg
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_bc_rec.batch_number IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Batch Number value cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Batch Number value cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_group_id := c_invoice_bc_rec.batch_number;
END IF;
IF c_invoice_bc_rec.batch_amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Batch Amount value cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Batch Amount value cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_batch_total := c_invoice_bc_rec.batch_amount;
END IF;
IF l_control_error_flag = 'Y'
THEN
RAISE e_control_valid_fail;
-- Stop Execution if Control Validation fails at any of the above levels.
END IF;
END LOOP; -- End of Cursor c_invoice_bc_stg
FOR c_invoice_in_rec IN c_invoice_in_stg
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_in_rec.invoice_num IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Number cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Invoice Number cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_in_rec.invoice_amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Amount 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 => 'Invoice Amount cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
l_invoice_hdr_total :=
l_invoice_hdr_total + c_invoice_in_rec.invoice_amount;
END IF;
IF c_invoice_in_rec.source IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Source 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 => 'Source cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_in_rec.invoice_date IS NOT NULL
THEN
xx_common_validations_pkg.validate_date_format
(p_input_string => c_invoice_in_rec.invoice_date
, p_date_format => 'DD-MON-YYYY' -- Date Format
, p_date => l_acc_date
, p_process_status => l_date_status
, p_error_message => l_date_message
);
-- Possible values of l_date_status are (0-valid / 1-Invalid)
IF l_date_status = 1
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Date is not in Proper Format "DD-MON-YYYY".');
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Date is not in Proper Format "DD-MON-YYYY".'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
IF c_invoice_in_rec.invoice_type_lookup_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Type Lookup 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 => 'Invoice Type Lookup Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--------------------------------------------------------------------------------------
-- Check the Invoice is PO Originated or not.
--------------------------------------------------------------------------------------
IF c_invoice_in_rec.po_header_id IS NULL
THEN
l_po_origin := FALSE;
ELSE
l_po_origin := TRUE;
END IF;
IF l_po_origin = FALSE
THEN
IF c_invoice_in_rec.vendor_num IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Vendor Num 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 => 'Vendor Num cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_in_rec.vendor_site_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Vendor Site 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 => 'Vendor Site Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Initializing the Invoice Line Amount Total.
l_invoice_line_total := 0;
FOR c_invoice_ln_rec IN
c_invoice_ln_stg (c_invoice_in_rec.invoice_id)
LOOP
l_processed_recs := l_processed_recs + 1;
----------------------------------------------------------------------------------------------------
-- Mandatory Columns Check.
----------------------------------------------------------------------------------------------------
IF c_invoice_ln_rec.line_number IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Line Number cannot be null.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Invoice Line Number cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_ln_rec.amount IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l ('Invoice Line Amount 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 => 'Invoice Line Amount cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
-- Sum Invoice Line Amount
l_invoice_line_total :=
l_invoice_line_total + c_invoice_ln_rec.amount;
END IF;
IF c_invoice_ln_rec.line_type_lookup_code IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Type Lookup 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 => 'Invoice Line Type Lookup Code cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF c_invoice_ln_rec.accounting_date IS NOT NULL
THEN
xx_common_validations_pkg.validate_date_format
(p_input_string => c_invoice_ln_rec.accounting_date
, p_date_format => 'DD-MON-YYYY' -- Date Format
, p_date => l_acc_date
, p_process_status => l_date_status
, p_error_message => l_date_message
);
-- Possible values of l_date_status are (0-valid / 1-Invalid)
IF l_date_status = 1
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Accounting Date is not in Proper Format "DD-MON-YYYY".');
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Accounting Date is not in Proper Format "DD-MON-YYYY".'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF; -- c_invoice_ln_rec.accounting_date IS NOT NULL
IF l_po_origin = FALSE
THEN -- dist_code_concatenated is not required if Invoice is PO Orginated
IF c_invoice_ln_rec.dist_code_concatenated IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Dist Code Concatenated 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 => 'Invoice Line Dist Code Concatenated cannot be null.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
IF l_po_origin = TRUE
THEN
IF c_invoice_ln_rec.po_line_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Line ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Line ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_ln_rec.po_line_location_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Line Location ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Line Location ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
IF c_invoice_ln_rec.po_distribution_id IS NULL
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('PO Distribution ID cannot be null if Invoice is PO Matched.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'PO Distribution ID cannot be null if Invoice is PO Matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => c_invoice_ln_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
END LOOP; -- End of Cursor c_invoice_ln_stg
-- Checking the Invoice Amount with Invoice Line Amount Total.
IF c_invoice_in_rec.invoice_amount != l_invoice_line_total
THEN
l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.l
('Invoice Line Amounts do not sum up to Invoice Amount.'
);
--Write to Error
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Line Amounts do not sum up to Invoice Amount.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_in_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
IF l_control_error_flag = 'Y'
THEN
EXIT;
-- If Control Validation falis at Invoice Level,
-- EXIT without validating rest invoices.
END IF;
END LOOP; -- End of Cursor c_invoice_in_stg
IF l_batch_total != l_invoice_hdr_total
THEN
l_control_error_flag := 'Y';
END IF;
IF l_control_error_flag = 'Y'
THEN
xx_trace.l
('Control Validation Failed.');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Control Validation Failed.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => NULL
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
RAISE e_control_valid_fail;
-- Stop Execution if Control Validation fails at any of the above levels.
END IF;
EXCEPTION -- Control Validation Exception Block
WHEN OTHERS
THEN
-- l_control_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
( 'Exception in Control Validation. Oracle Error :'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 -- high level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00001'
, p_identifier2 => 'Error in Control Validation.'
|| SQLERRM
, p_identifier3 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
-- Initialize Following variable for Summary Report.
-- Purpose: Display a summary of records processed, successful and in error
l_error_recs := l_processed_recs;
-- All records are Errored Out.
l_successful_recs := 0;
-- Delete Records from Staging Table xx_ap_open_invoice_stg.
-- Not required if SQL * Loader Control file is run with "TRUNCATE" option.
DELETE FROM xx_ap_open_invoice_stg;
--
COMMIT;
--
RAISE;
-- Raising exception "e_control_valid_fail" to outer most block.
END; -- End of Control Validation.
--
----------------------------------------------------------------------------------------------------
--Load Data into Pre-Interface Table
----------------------------------------------------------------------------------------------------
BEGIN -- Pre Interface Load Begin
INSERT INTO xx_ap_pre_invoice_interface
(status
, record_id -- Primary Key
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, description
, SOURCE
, request_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
)
SELECT 'NW'
, xx_ap_cnv_03_pre_s.nextval -- Primary Key
, invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_num
, vendor_site_code
, po_number
, po_header_id
, invoice_amount
, invoice_currency_code
, terms_name
, hdr_description
, SOURCE
, l_request_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'IN';
UPDATE xx_ap_pre_invoice_interface
SET GROUP_ID = l_group_id
WHERE status = 'NW';
INSERT INTO xx_ap_pre_inv_lines_interface
(record_id -- Primary Key
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
)
SELECT xx_ap_cnv_03_line_s.nextval -- Primary Key
, invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, amount
, accounting_date
, line_description
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, dist_code_concatenated
, rcv_transaction_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
FROM xx_ap_open_invoice_stg
WHERE transaction_code = 'LN';
COMMIT; -- Commiting Pre Interface Load.
--
EXCEPTION -- Pre Interface Exception Block
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
);
--
RAISE;
-- Raising Exception to outer most block
END; -- End of Pre Interface Load Begin
----------------------------------------------------------------------------------------------------
--Updating pre interface table with status 'IP' where record_status in('NW')
----------------------------------------------------------------------------------------------------
BEGIN -- Pre Interface Status Change Block
UPDATE xx_ap_pre_invoice_interface
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; -- End of Pre Interface Status Change Block
END IF; -- End of Conditional checking of p_runmode.
-- Re Initializing the Summary Count Variables.
l_processed_recs := 0;
l_error_recs := 0;
l_successful_recs := 0;
--
----------------------------------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------------------------------
--
BEGIN -- Business Validation Block
FOR c_invoice_rec IN c_invoice_insert
LOOP
--
l_processed_recs := l_processed_recs + 1;
-- Reset Business Error Flag
l_business_error_flag := 'N';
-- validation of source
-- Assuming all invoice records from data file will contain one source only.
BEGIN
SELECT meaning
INTO l_source
FROM fnd_lookup_values
WHERE lookup_type = 'SOURCE'
AND lookup_code = c_invoice_rec.SOURCE
AND view_application_id = 200; -- Payables
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
( ' Exception occured while validating Invoice Source.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Exception occured while validating Invoice Source.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- validation of Invoice Type Lookup Code and Invoice Amount
IF c_invoice_rec.invoice_type_lookup_code = 'STANDARD'
THEN
IF c_invoice_rec.invoice_amount < 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Amount must be Positive when Invoice Type Lookup Code is Standard.'
);
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Amount must be Positive when Invoice Type Lookup Code is Standard.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSIF c_invoice_rec.invoice_type_lookup_code = 'CREDIT'
THEN
IF c_invoice_rec.invoice_amount > 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Amount must be Negative when Invoice Type Lookup Code is Credit.'
);
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Amount must be Negative when Invoice Type Lookup Code is Credit.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSE
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Invoice Type Lookup Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Type Lookup Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
-- Validation of invoice currency
IF c_invoice_rec.invoice_currency_code IS NOT NULL
THEN
l_currency_exist :=
xx_common_validations_pkg.validate_currency_code
(p_currency_code => c_invoice_rec.invoice_currency_code
, p_process_status => l_process_status
, p_error_message => l_error_message
);
IF TO_NUMBER (l_currency_exist) != 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Invoice Currency Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Currency Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
ELSE
--l_currency_code :='USD';
UPDATE xx_ap_pre_invoice_interface
SET invoice_currency_code = 'USD'
WHERE record_id = c_invoice_rec.record_id;
END IF;
--------------------------------------------------------------------------------
-- Checking Invoice is PO Originated or Not.
--------------------------------------------------------------------------------
IF c_invoice_rec.po_header_id IS NOT NULL
THEN
l_po_origin := TRUE;
ELSE
l_po_origin := FALSE;
END IF;
-- Validation of PO Header ID
IF l_po_origin = TRUE
THEN
l_po_header_exist := 0;
SELECT COUNT ('X')
INTO l_po_header_exist
FROM DUAL
WHERE c_invoice_rec.po_header_id = ANY (SELECT po_header_id
FROM po_headers_all
WHERE approved_flag = 'Y'
AND closed_date IS NULL);
IF l_po_header_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Header ID for PO Matched Invoice.');
xx_emf.call_store_message
(p_message_group => '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 PO Header ID for PO Matched Invoice.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE -- If PO header ID is valid
-- Update xx_ap_pre_invoice_interface with appropriate po_number
BEGIN
SELECT segment1
INTO l_po_number
FROM po_headers_all
WHERE po_header_id = c_invoice_rec.po_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Error in getting po_number. Oracle Error - ' || SQLERRM);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
,
-- low level debugging
p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error in getting po_number. Oracle Error - ' || SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
UPDATE xx_ap_pre_invoice_interface
SET po_number = l_po_number
WHERE record_id = c_invoice_rec.record_id;
END IF;
END IF;
-- Validation of Vendor Num
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.vendor_num IS NOT NULL
THEN
l_vendor_exist := 0;
SELECT COUNT ('X')
INTO l_vendor_exist
FROM DUAL
WHERE c_invoice_rec.vendor_num =
(SELECT pv.segment1
FROM po_vendors pv, po_headers_all pha
WHERE pv.vendor_id = pha.vendor_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_vendor_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Vendor Number is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Vendor Number is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
l_vendor_exist := 0;
SELECT COUNT ('X')
INTO l_vendor_exist
FROM DUAL
WHERE c_invoice_rec.vendor_num = ANY (SELECT segment1
FROM po_vendors);
IF l_vendor_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Vendor Number.');
xx_emf.call_store_message
(p_message_group => '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 Vendor Number.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validation of Unique Invoice Number for each Supplier
BEGIN
SELECT COUNT('X')
INTO l_invoice_num
FROM ap_invoices_all inv
WHERE inv.invoice_num = c_invoice_rec.invoice_num
AND inv.vendor_id = (SELECT pv.vendor_id
FROM po_vendors pv
WHERE pv.segment1 = c_invoice_rec.vendor_num);
--
IF l_invoice_num >= 1
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invoice Number already Exists for this Supplier. Use Unique Invoice number for each supplier.');
xx_emf.call_store_message
(p_message_group => '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 => 'Invoice Number already Exists for this Supplier. Use Unique Invoice number for each supplier.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Error at getting Invoice number.Oracle Error - ' || SQLERRM);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Error at getting Invoice number.Oracle Error - ' || SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END;
-- Validation of Vendor Site Code
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.vendor_site_code IS NOT NULL
THEN
l_site_code_exist := 0;
SELECT COUNT ('X')
INTO l_site_code_exist
FROM DUAL
WHERE c_invoice_rec.vendor_site_code = ANY (
SELECT pvs.vendor_site_code
FROM po_vendors pv
, po_headers_all pha
, po_vendor_sites_all pvs
WHERE pv.vendor_id = pha.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_site_code_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Vendor Site Code is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20
-- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00003'
, p_identifier2 => 'Vendor Site Code is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
l_site_code_exist := 0;
SELECT COUNT ('X')
INTO l_site_code_exist
FROM DUAL
WHERE c_invoice_rec.vendor_site_code = ANY (
SELECT pvs.vendor_site_code
FROM po_vendor_sites_all pvs
, po_vendors pv
WHERE pv.vendor_id = pvs.vendor_id
AND pv.segment1 = c_invoice_rec.vendor_num);
IF l_site_code_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Vendor Site Code.');
xx_emf.call_store_message
(p_message_group => '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 Vendor Site Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validation of Payment Terms
IF c_invoice_rec.terms_name IS NOT NULL
THEN
l_terms_exist := 0;
SELECT COUNT ('X')
INTO l_terms_exist
FROM DUAL
WHERE c_invoice_rec.terms_name = ANY (SELECT NAME
FROM ap_terms);
IF l_terms_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Invalid Payment Terms.');
xx_emf.call_store_message
(p_message_group => '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 Payment Terms.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
/* Operating Unit is not validated as org id is removed from Data file
-- Validation of Operating Unit
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
IF c_invoice_rec.org_id IS NOT NULL
THEN
l_org_exist := 0;
SELECT COUNT ('X')
INTO l_org_exist
FROM DUAL
WHERE c_invoice_rec.org_id =
(SELECT org.organization_id
FROM org_organization_definitions org
, po_headers_all pha
WHERE org.organization_id = pha.org_id
AND pha.po_header_id =
c_invoice_rec.po_header_id);
--
IF l_org_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Organization ID is not Matching with PO Details.');
xx_emf.call_store_message
(p_message_group => '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 => 'Organization ID is not Matching with PO Details.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
ELSE -- IF Invoice is NOT PO Originated
--
IF c_invoice_rec.org_id IS NOT NULL
THEN
l_org_exist := 0;
SELECT COUNT ('X')
INTO l_org_exist
FROM DUAL
WHERE c_invoice_rec.org_id = ANY (
SELECT organization_id
FROM org_organization_definitions);
IF l_org_exist = 0
THEN
l_business_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h ('Organization ID is not valid.');
xx_emf.call_store_message
(p_message_group => '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 => 'Organization ID is not valid.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => NULL
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
END IF;
Operating Unit is not validated as org id is removed from Data file*/
IF l_business_error_flag = 'Y'
THEN
-- update xx_ap_pre_invoice_interface with status = 'ER'
UPDATE xx_ap_pre_invoice_interface
SET status = 'ER'
WHERE record_id = c_invoice_rec.record_id;
--
ELSE
-- Reset Business Line Error Flag
l_business_line_error_flag := 'N';
-- Processing the Invoice line Cursor
FOR c_invoice_line_rec IN
c_invoice_line_insert (c_invoice_rec.invoice_id)
LOOP
l_processed_recs := l_processed_recs + 1;
--
-- Validating Line Type Lookup Code.
l_line_lookup_exist := 0;
SELECT COUNT ('X')
INTO l_line_lookup_exist
FROM DUAL
WHERE c_invoice_line_rec.line_type_lookup_code = ANY (
SELECT lookup_code
FROM ap_lookup_codes
WHERE lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND lookup_code = 'ITEM');
IF l_line_lookup_exist = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid Invoice Line Type Lookup Code.');
xx_emf.call_store_message
(p_message_group => '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 Invoice Line Type Lookup Code.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
/*-- Matching PO Header ID at Preinterface - Invoice and Line Level
IF l_po_origin = TRUE
THEN
IF c_invoice_line_rec.po_header_id != c_invoice_rec.po_header_id
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('PO Header ID is not Matching at Preinterface - Invoice and Line Level.');
xx_emf.call_store_message
(p_message_group => '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 => 'PO Header ID is not Matching at Preinterface - Invoice and Line Level.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;*/
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
-- Validation of PO Line ID
l_po_line_exists := 0;
SELECT COUNT ('X')
INTO l_po_line_exists
FROM DUAL
WHERE c_invoice_line_rec.po_line_id = ANY (
SELECT pla.po_line_id
FROM po_lines_all pla
, po_headers_all pha
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_line_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Line ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Line ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
-- Validation of PO Line Location ID
l_po_location_exists := 0;
SELECT COUNT ('X')
INTO l_po_location_exists
FROM DUAL
WHERE c_invoice_line_rec.po_line_location_id = ANY (
SELECT pll.line_location_id
FROM po_lines_all pla
, po_headers_all pha
, po_line_locations_all pll
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_location_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Line Location ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Line Location ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
--
-- Validation of PO Distribution ID
l_po_dist_exists := 0;
SELECT COUNT ('X')
INTO l_po_dist_exists
FROM DUAL
WHERE c_invoice_line_rec.po_distribution_id = ANY (
SELECT pda.po_distribution_id
FROM po_lines_all pla
, po_headers_all pha
, po_line_locations_all pll
, po_distributions_all pda
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.line_location_id = pll.line_location_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_po_dist_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid PO Distribution ID.');
xx_emf.call_store_message
(p_message_group => '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 PO Distribution ID.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF;
-- Validate RCV Transaction ID if PO is 3 way Matched
IF l_po_origin = TRUE
THEN -- If Invoice is PO Originated
l_rcv_trans_exists := 0;
-- Get Match Option from po_line_locations_all
BEGIN
SELECT pll.match_option
INTO l_match_option
FROM po_line_locations_all pll
, po_lines_all pla
, po_headers_all pha
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.po_header_id = c_invoice_rec.po_header_id
AND pla.po_line_id = c_invoice_line_rec.po_line_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL;
EXCEPTION
WHEN OTHERS
THEN
l_match_option := NULL;
END;
IF l_match_option = 'R' -- If Po is Receipt Matched
THEN
IF c_invoice_line_rec.rcv_transaction_id IS NULL
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('RCV Transaction ID is NULL while PO is 3 way matched.');
xx_emf.call_store_message
(p_message_group => '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 => 'RCV Transaction ID is NULL while PO is 3 way matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE
-- Validate RCV Transaction ID
SELECT COUNT ('X')
INTO l_rcv_trans_exists
FROM DUAL
WHERE c_invoice_line_rec.rcv_transaction_id = ANY (
SELECT rt.transaction_id
FROM rcv_transactions rt
, po_headers_all pha
WHERE rt.po_header_id = c_invoice_rec.po_header_id
AND pha.approved_flag = 'Y'
AND pha.closed_date IS NULL);
IF l_rcv_trans_exists = 0
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid RCV Transaction ID though PO is 3 way matched.'
);
xx_emf.call_store_message
(p_message_group => '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 RCV Transaction ID though PO is 3 way matched.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END IF;
END IF; -- c_invoice_line_rec.rcv_transaction_id IS NULL
END IF; -- l_match_option = 'R'
END IF; -- l_po_origin = TRUE
IF l_po_origin = TRUE
THEN
-- Update the dist_code_combination_id column of xx_ap_pre_inv_lines_interface table
-- with NULL because of System Setup in VISION.
UPDATE xx_ap_pre_inv_lines_interface
SET dist_code_combination_id = NULL
WHERE invoice_line_id =
c_invoice_line_rec.invoice_line_id
AND invoice_id = c_invoice_rec.invoice_id;
--
ELSE -- If Invoice is not PO Originated
-- Validate Dist Code Concatenated Segments and get CCID
l_ccid_exist :=
validate_ccsegment
(p_ccsegment => c_invoice_line_rec.dist_code_concatenated
, p_ccid => l_ccid
, p_error_message => l_err_message
);
IF l_ccid_exist != 'VALID'
THEN
l_business_line_error_flag := 'Y';
l_error_flag := 'Y';
xx_trace.h
('Invalid Dist Code Concatenated Segment.');
xx_emf.call_store_message
(p_message_group => '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 Dist Code Concatenated Segment.'
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_identifier4 => c_invoice_rec.invoice_id
, p_identifier5 => c_invoice_line_rec.invoice_line_id
, p_process_status => l_return_value
, p_error_message => l_output_message
);
ELSE -- if l_ccid_exist = 'VALID'
--
-- Update the dist_code_combination_id column of xx_ap_pre_inv_lines_interface table
-- with ccid generated from dist_code_concatenated.
UPDATE xx_ap_pre_inv_lines_interface
SET dist_code_combination_id = l_ccid
WHERE invoice_line_id =
c_invoice_line_rec.invoice_line_id
AND invoice_id = c_invoice_rec.invoice_id;
--
END IF;
END IF; -- l_po_origin = TRUE
--
END LOOP; -- End of Cursor c_invoice_line_insert
IF l_business_line_error_flag = 'Y'
THEN
-- update xx_ap_pre_invoice_interface with status = 'ER'
-- if invoice line is errored out.
UPDATE xx_ap_pre_invoice_interface
SET status = 'ER'
WHERE record_id = c_invoice_rec.record_id;
END IF;
END IF; -- l_business_error_flag = 'Y'
END LOOP; -- End of Cursor c_invoice_insert
--
-- COMMIT;
--
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-00004'
, p_identifier2 => 'Unexpected Exception While Business Validation.Oracle Error-'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE;
-- Raise error to outermost exception block
END; -- End of Business Validation Block
-----------------------------------------------------------------------------------------------
--Move Data to Interface Tables
-----------------------------------------------------------------------------------------------
BEGIN -- Insert into Interface
--
FOR c_invoice_rec IN c_invoice_insert
LOOP
--
l_successful_recs := l_successful_recs + 1;
--
INSERT INTO ap_invoices_interface
(invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, po_number
, vendor_id
, vendor_num
, vendor_name
, vendor_site_id
, vendor_site_code
, invoice_amount
, invoice_currency_code
, exchange_rate
, exchange_rate_type
, exchange_date
, terms_id
, terms_name
, description
, awt_group_id
, awt_group_name
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, global_attribute_category
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, status
, SOURCE
, GROUP_ID
, request_id
, payment_cross_rate_type
, payment_cross_rate_date
, payment_cross_rate
, payment_currency_code
, workflow_flag
, doc_category_code
, voucher_num
, payment_method_lookup_code
, pay_group_lookup_code
, goods_received_date
, invoice_received_date
, gl_date
, accts_pay_code_combination_id
, ussgl_transaction_code
, exclusive_payment_flag
, org_id
, amount_applicable_to_discount
, prepay_num
, prepay_dist_num
, prepay_apply_amount
, prepay_gl_date
, invoice_includes_prepay_flag
, no_xrate_base_amount
, vendor_email_address
, terms_date
, requester_id
, ship_to_location
, external_doc_ref
)
VALUES (c_invoice_rec.invoice_id
, c_invoice_rec.invoice_num
, c_invoice_rec.invoice_type_lookup_code
, c_invoice_rec.invoice_date
, c_invoice_rec.po_number
, c_invoice_rec.vendor_id
, c_invoice_rec.vendor_num
, c_invoice_rec.vendor_name
, c_invoice_rec.vendor_site_id
, c_invoice_rec.vendor_site_code
, c_invoice_rec.invoice_amount
, c_invoice_rec.invoice_currency_code
, c_invoice_rec.exchange_rate
, c_invoice_rec.exchange_rate_type
, c_invoice_rec.exchange_date
, c_invoice_rec.terms_id
, c_invoice_rec.terms_name
, c_invoice_rec.description
, c_invoice_rec.awt_group_id
, c_invoice_rec.awt_group_name
, c_invoice_rec.last_update_date
, c_invoice_rec.last_updated_by
, c_invoice_rec.last_update_login
, c_invoice_rec.creation_date
, c_invoice_rec.created_by
, c_invoice_rec.attribute_category
, c_invoice_rec.attribute1
, c_invoice_rec.attribute2
, c_invoice_rec.attribute3
, c_invoice_rec.attribute4
, c_invoice_rec.attribute5
, c_invoice_rec.attribute6
, c_invoice_rec.attribute7
, c_invoice_rec.attribute8
, c_invoice_rec.attribute9
, c_invoice_rec.attribute10
, c_invoice_rec.attribute11
, c_invoice_rec.attribute12
, c_invoice_rec.attribute13
, c_invoice_rec.attribute14
, c_invoice_rec.attribute15
, c_invoice_rec.global_attribute_category
, c_invoice_rec.global_attribute1
, c_invoice_rec.global_attribute2
, c_invoice_rec.global_attribute3
, c_invoice_rec.global_attribute4
, c_invoice_rec.global_attribute5
, c_invoice_rec.global_attribute6
, c_invoice_rec.global_attribute7
, c_invoice_rec.global_attribute8
, c_invoice_rec.global_attribute9
, c_invoice_rec.global_attribute10
, c_invoice_rec.global_attribute11
, c_invoice_rec.global_attribute12
, c_invoice_rec.global_attribute13
, c_invoice_rec.global_attribute14
, c_invoice_rec.global_attribute15
, c_invoice_rec.global_attribute16
, c_invoice_rec.global_attribute17
, c_invoice_rec.global_attribute18
, c_invoice_rec.global_attribute19
, c_invoice_rec.global_attribute20
, NULL -- STATUS
, c_invoice_rec.SOURCE
, c_invoice_rec.GROUP_ID
, NULL -- REQUEST ID
, c_invoice_rec.payment_cross_rate_type
, c_invoice_rec.payment_cross_rate_date
, c_invoice_rec.payment_cross_rate
, c_invoice_rec.payment_currency_code
, c_invoice_rec.workflow_flag
, c_invoice_rec.doc_category_code
, c_invoice_rec.voucher_num
, c_invoice_rec.payment_method_lookup_code
, c_invoice_rec.pay_group_lookup_code
, c_invoice_rec.goods_received_date
, c_invoice_rec.invoice_received_date
, c_invoice_rec.gl_date
, c_invoice_rec.accts_pay_code_combination_id
, c_invoice_rec.ussgl_transaction_code
, c_invoice_rec.exclusive_payment_flag
, c_invoice_rec.org_id
, c_invoice_rec.amount_applicable_to_discount
, c_invoice_rec.prepay_num
, c_invoice_rec.prepay_dist_num
, c_invoice_rec.prepay_apply_amount
, c_invoice_rec.prepay_gl_date
, c_invoice_rec.invoice_includes_prepay_flag
, c_invoice_rec.no_xrate_base_amount
, c_invoice_rec.vendor_email_address
, c_invoice_rec.terms_date
, c_invoice_rec.requester_id
, c_invoice_rec.ship_to_location
, c_invoice_rec.external_doc_ref
);
--
-- Move data to Invoice Interface Line Table.
FOR c_invoice_line_rec IN
c_invoice_line_insert (c_invoice_rec.invoice_id)
LOOP
--
l_successful_recs := l_successful_recs + 1;
--
INSERT INTO ap_invoice_lines_interface
(invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, line_group_number
, amount
, accounting_date
, description
, amount_includes_tax_flag
, prorate_across_flag
, tax_code
, final_match_flag
, po_header_id
, po_number
, po_line_id
, po_line_number
, po_line_location_id
, po_shipment_num
, po_distribution_id
, po_distribution_num
, po_unit_of_measure
, inventory_item_id
, item_description
, quantity_invoiced
, ship_to_location_code
, unit_price
, distribution_set_id
, distribution_set_name
, dist_code_concatenated
, dist_code_combination_id
, awt_group_id
, awt_group_name
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, global_attribute_category
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, po_release_id
, release_num
, account_segment
, balancing_segment
, cost_center_segment
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
, pa_quantity
, ussgl_transaction_code
, stat_amount
, type_1099
, income_tax_region
, assets_tracking_flag
, price_correction_flag
, org_id
, receipt_number
, receipt_line_number
, match_option
, packing_slip
, rcv_transaction_id
, pa_cc_ar_invoice_id
, pa_cc_ar_invoice_line_num
, reference_1
, reference_2
, pa_cc_processed_code
, tax_recovery_rate
, tax_recovery_override_flag
, tax_recoverable_flag
, tax_code_override_flag
, tax_code_id
, credit_card_trx_id
, award_id
, vendor_item_num
, taxable_flag
, price_correct_inv_num
, external_doc_line_ref
)
VALUES (c_invoice_line_rec.invoice_id
, c_invoice_line_rec.invoice_line_id
, c_invoice_line_rec.line_number
, c_invoice_line_rec.line_type_lookup_code
, c_invoice_line_rec.line_group_number
, c_invoice_line_rec.amount
, c_invoice_line_rec.accounting_date
, c_invoice_line_rec.description
, c_invoice_line_rec.amount_includes_tax_flag
, c_invoice_line_rec.prorate_across_flag
, c_invoice_line_rec.tax_code
, c_invoice_line_rec.final_match_flag
, c_invoice_line_rec.po_header_id
, c_invoice_line_rec.po_number
, c_invoice_line_rec.po_line_id
, c_invoice_line_rec.po_line_number
, c_invoice_line_rec.po_line_location_id
, c_invoice_line_rec.po_shipment_num
, c_invoice_line_rec.po_distribution_id
, c_invoice_line_rec.po_distribution_num
, c_invoice_line_rec.po_unit_of_measure
, c_invoice_line_rec.inventory_item_id
, c_invoice_line_rec.item_description
, c_invoice_line_rec.quantity_invoiced
, c_invoice_line_rec.ship_to_location_code
, c_invoice_line_rec.unit_price
, c_invoice_line_rec.distribution_set_id
, c_invoice_line_rec.distribution_set_name
, NULL -- dist_code_concatenated is passed NULL to avoid Account Overlay.
, c_invoice_line_rec.dist_code_combination_id
, c_invoice_line_rec.awt_group_id
, c_invoice_line_rec.awt_group_name
, c_invoice_line_rec.last_updated_by
, c_invoice_line_rec.last_update_date
, c_invoice_line_rec.last_update_login
, c_invoice_line_rec.created_by
, c_invoice_line_rec.creation_date
, c_invoice_line_rec.attribute_category
, c_invoice_line_rec.attribute1
, c_invoice_line_rec.attribute2
, c_invoice_line_rec.attribute3
, c_invoice_line_rec.attribute4
, c_invoice_line_rec.attribute5
, c_invoice_line_rec.attribute6
, c_invoice_line_rec.attribute7
, c_invoice_line_rec.attribute8
, c_invoice_line_rec.attribute9
, c_invoice_line_rec.attribute10
, c_invoice_line_rec.attribute11
, c_invoice_line_rec.attribute12
, c_invoice_line_rec.attribute13
, c_invoice_line_rec.attribute14
, c_invoice_line_rec.attribute15
, c_invoice_line_rec.global_attribute_category
, c_invoice_line_rec.global_attribute1
, c_invoice_line_rec.global_attribute2
, c_invoice_line_rec.global_attribute3
, c_invoice_line_rec.global_attribute4
, c_invoice_line_rec.global_attribute5
, c_invoice_line_rec.global_attribute6
, c_invoice_line_rec.global_attribute7
, c_invoice_line_rec.global_attribute8
, c_invoice_line_rec.global_attribute9
, c_invoice_line_rec.global_attribute10
, c_invoice_line_rec.global_attribute11
, c_invoice_line_rec.global_attribute12
, c_invoice_line_rec.global_attribute13
, c_invoice_line_rec.global_attribute14
, c_invoice_line_rec.global_attribute15
, c_invoice_line_rec.global_attribute16
, c_invoice_line_rec.global_attribute17
, c_invoice_line_rec.global_attribute18
, c_invoice_line_rec.global_attribute19
, c_invoice_line_rec.global_attribute20
, c_invoice_line_rec.po_release_id
, c_invoice_line_rec.release_num
, c_invoice_line_rec.account_segment
, c_invoice_line_rec.balancing_segment
, c_invoice_line_rec.cost_center_segment
, c_invoice_line_rec.project_id
, c_invoice_line_rec.task_id
, c_invoice_line_rec.expenditure_type
, c_invoice_line_rec.expenditure_item_date
, c_invoice_line_rec.expenditure_organization_id
, c_invoice_line_rec.project_accounting_context
, c_invoice_line_rec.pa_addition_flag
, c_invoice_line_rec.pa_quantity
, c_invoice_line_rec.ussgl_transaction_code
, c_invoice_line_rec.stat_amount
, c_invoice_line_rec.type_1099
, c_invoice_line_rec.income_tax_region
, c_invoice_line_rec.assets_tracking_flag
, c_invoice_line_rec.price_correction_flag
, c_invoice_line_rec.org_id
, c_invoice_line_rec.receipt_number
, c_invoice_line_rec.receipt_line_number
, c_invoice_line_rec.match_option
, c_invoice_line_rec.packing_slip
, c_invoice_line_rec.rcv_transaction_id
, c_invoice_line_rec.pa_cc_ar_invoice_id
, c_invoice_line_rec.pa_cc_ar_invoice_line_num
, c_invoice_line_rec.reference_1
, c_invoice_line_rec.reference_2
, c_invoice_line_rec.pa_cc_processed_code
, c_invoice_line_rec.tax_recovery_rate
, c_invoice_line_rec.tax_recovery_override_flag
, c_invoice_line_rec.tax_recoverable_flag
, c_invoice_line_rec.tax_code_override_flag
, c_invoice_line_rec.tax_code_id
, c_invoice_line_rec.credit_card_trx_id
, c_invoice_line_rec.award_id
, c_invoice_line_rec.vendor_item_num
, c_invoice_line_rec.taxable_flag
, c_invoice_line_rec.price_correct_inv_num
, c_invoice_line_rec.external_doc_line_ref
);
END LOOP; -- End of Cursor c_invoice_line_insert
--
END LOOP; -- End of Cursor c_invoice_insert
--
COMMIT;
--
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( 'Exception While Inserting into AP Open Invoice Interface Tables.Oracle Error-'
|| SQLERRM
);
xx_emf.call_store_message
(p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 20 -- low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception While Inserting into AP Open Invoice Interface Tables.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
,
-- Can be utilized to store record serial number
p_process_status => l_return_value
, p_error_message => l_output_message
);
--
RAISE;
-- Raise error to outermost block
END; -- End of Insert into Interface Block
----------------------------------------------------------------------------------
--Updating pre interface table with status 'PR' where record_status is 'IP'
----------------------------------------------------------------------------------
BEGIN
SELECT COUNT(invoice_id)
INTO l_processed_rec_cnt
FROM xx_ap_pre_invoice_interface
WHERE status = 'IP';
UPDATE xx_ap_pre_invoice_interface
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-00005'
, p_identifier2 => 'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END; -- End of Preinterface update block
--Initialize apps
--fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id
, p_process_status => l_process_status
, p_error_message => l_output_message
);
IF l_processed_rec_cnt > 0
THEN -- If Pre Interface table has Processed records to be imported
--
---------------------------------------------------------------------------------------
--Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
----------------------------------------------------------------------------------------
BEGIN
l_standard_request_id :=
fnd_request.submit_request (application => 'SQLAP'
, program => 'APXIIMPT'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_source -- SOURCE
, argument2 => l_group_id -- GROUP
, argument3 => l_group_id -- BATCH NAME
, argument4 => NULL -- HOLD NAME
, argument5 => NULL -- HOLD REASON
, argument6 => NULL -- GL_DATE
, argument7 => NULL -- PURGE
, argument8 => 'Y' -- SUMMARIZE REPORT
);
--
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
);
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
xx_trace.h
( ' Exception While Running Standard Concurrent Program.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-00006'
, p_identifier2 => 'Exception While Running Standard Concurrent Program.Oracle Error-'
|| SQLERRM
, p_identifier3 => NULL
-- Can be utilized to store record serial number
, p_process_status => l_return_value
, p_error_message => l_output_message
);
END; -- End of Launch Concurrent Program Block
END IF; -- If Pre Interface table has Processed records to be imported
--
------------------------------------------------------------------------------------
-- Purge All Processed Records from Pre Interface Tables.
------------------------------------------------------------------------------------
BEGIN
FOR c_invoice_purge_rec IN c_invoice_purge
LOOP
--
FOR c_invoice_line_purge_rec IN c_invoice_line_purge(c_invoice_purge_rec.invoice_id)
LOOP
DELETE FROM xx_ap_pre_inv_lines_interface
WHERE invoice_id = c_invoice_purge_rec.invoice_id;
END LOOP;
--
DELETE FROM xx_ap_pre_invoice_interface
WHERE invoice_id = c_invoice_purge_rec.invoice_id;
END LOOP;
--
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h('Exception while purging records from pre-interface table - '|| SQLERRM);
END;
--
------------------------------------------------------------------------------------
-- Call Insert Summary Count
-- Purpose: Display a summary of records processed, successful and in error
------------------------------------------------------------------------------------
l_error_recs := l_processed_recs - l_successful_recs;
--
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;
--
--------------------------------------------------------------------------------------------
-- Call flush PL/SQL table section
-- Purpose : Flush data from pl/sql table isrespective of the count.
-- Special Logic : record's message group is passed as 'ALL' and rest all columns are null
--------------------------------------------------------------------------------------------
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
----------------------------------------------------------------------------
-- Set display width for output
-- Purpose: Call to set EMF error and report section column widths
----------------------------------------------------------------------------
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
----------------------------------------------------------------
-- Call to xx_emf.display_output_messages
-- Purpose--Call to generate output file (Errors/Messages)
----------------------------------------------------------------
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;
--
l_return_value := NULL;
----------------------------------------------------
-- Call To EMF 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 for Error.');
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 for Warning.');
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 Block
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
WHEN OTHERS
THEN
--Write to error
l_error_flag := 'Y';
xx_emf.call_store_message (p_message_group => 'ERR_DTL'
, p_header_id => l_header_id
, p_debug_value => 40 --High level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'E-00004'
, p_identifier2 => 'Exception in Main.'
|| SQLERRM
, p_process_status => l_return_value
, p_error_message => l_output_message
);
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
-------------------------------------------------------------------------------------
-- Call To Insert Summary Count
-------------------------------------------------------------------------------------
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id
, p_display_name => l_program_name
, p_total_recs => l_processed_recs
, p_successful_recs => l_successful_recs
, p_error_recs => l_error_recs
);
IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
------------------------------------------------------------------------------------------
-- Call flush PL/SQL table section
-- Purpose : Flush data from pl/sql table isrespective of the count.
-- Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------
xx_emf.call_store_message (p_message_group => 'ALL'
, p_header_id => l_header_id
, p_debug_value => 20
--Low level debugging
, p_global_debug => g_debug_level
, p_identifier1 => 'Flushing Data into EMF'
, p_process_status => l_return_value
, p_error_message => l_output_message
);
IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;
l_return_value := NULL;
----------------------------------------------------------------------------
-- Purpose: Call to set EMF error and report section column widths
----------------------------------------------------------------------------
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
----------------------------------------------------------------
-- Purpose--Call to generate output file (Errors/Messages)
----------------------------------------------------------------
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id
, p_err_width_rec => l_err_width_rec
);
--
IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;
l_return_value := NULL;
-------------------------------------------------------------------
-- Call update program status
-------------------------------------------------------------------
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id
, p_status => l_status
);
--
IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;
-------------------------------------------------------------------------------------------------------
--Function : validate_ccsegment
--Purpose : validates the concatenated code combination segments.
-------------------------------------------------------------------------------------------------------
FUNCTION validate_ccsegment (
p_ccsegment IN xx_ap_pre_inv_lines_interface.dist_code_concatenated%TYPE
, p_ccid OUT NUMBER
, p_error_message OUT VARCHAR2
)
RETURN VARCHAR2
IS
-- common variables
l_cc_length NUMBER;
l_dot_occurence NUMBER := 0;
l_cnt_dot_occur NUMBER := 1;
l_str_start NUMBER := 0;
p_segment1 gl_code_combinations.segment1%TYPE;
p_segment2 gl_code_combinations.segment2%TYPE;
p_segment3 gl_code_combinations.segment3%TYPE;
p_segment4 gl_code_combinations.segment4%TYPE;
p_segment5 gl_code_combinations.segment5%TYPE;
-- xx_common_validations_pkg.get_coa_ccid related variables
l_process_status NUMBER;
l_ccid_exist VARCHAR2 (20);
BEGIN
BEGIN
l_cc_length := LENGTH (p_ccsegment);
-- Assuming there are 5 segments and 4 segment delimeter
FOR l_cnt_dot_occur IN 1 .. 5
LOOP
-- get starting point of Substring
l_str_start := l_dot_occurence + 1;
--
-- get the position of segment deimeter.
-- Assuming that segment delimeter is '.'
-- g_cc_seg_delimeter is '.'
--
l_dot_occurence := INSTR (p_ccsegment, g_cc_seg_delimeter, 1, l_cnt_dot_occur);
--
CASE l_cnt_dot_occur
WHEN 1
THEN
p_segment1 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 2
THEN
p_segment2 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 3
THEN
p_segment3 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
WHEN 4
THEN
p_segment4 :=
SUBSTR (p_ccsegment
, l_str_start
, (l_dot_occurence - l_str_start)
);
ELSE
p_segment5 :=
SUBSTR (p_ccsegment, l_str_start, l_cc_length);
END CASE;
END LOOP;
--
l_ccid_exist :=
xx_common_validations_pkg.get_coa_ccid
(p_segment1 => p_segment1
, p_segment2 => p_segment2
, p_segment3 => p_segment3
, p_segment4 => p_segment4
, p_segment5 => p_segment5
, p_process_status => l_process_status
, p_error_message => p_error_message
, p_ccid => p_ccid
);
--
EXCEPTION
WHEN OTHERS
THEN
l_ccid_exist := 'INVALID';
END;
RETURN l_ccid_exist;
END validate_ccsegment;
--
END xx_ap_invoice_conv_pkg;
/
No comments :
Post a Comment