Wednesday, May 15, 2013
ap invoice interface procedure
AP_INVOICES_INTERFACE
-----------------------
1)INVOICE_ID not null
Required, Primary key. This value is assigned in
the Invoice Gateway by the
AP_INVOICES_INTERFACE_S sequence.
2) INVOICE_NUM Required if
there is more than one invoice for the
supplier during import
3)VENDOR_ID or VENDOR_NAME
or VENDOR_NUM is reqd if not matched to PO
4)VENDOR_SITE_ID or
VENDOR_SITE_CODE is reqd if not matched to PO
5)INVOICE_AMOUNT
6)PO_NUMBER
6)INVOICE_CURRENCY_CODE:if
not eneterd defaulted to functional currency
7)EXCHANGE_RATE_TYPE
:User,Spot, Corporate, EMU Fixed, and user–defined.for Spot, Corporate, or any
user–defined rate
type, the value you enter
here is validated against
the GL Daily Rates table.If
you use EMU Fixed,Payables will provide the exchange rate during import. If you
use User as the
exchange rate type,you must
enter a value for EXCHANGE_RATE orthe record will be rejected during import.
8)TERMS_NAME or
TERMS_ID.:Payables searches1)invoice record header 2)purchase order terms if
invoice is matched to PO
3)supplier site.
9)SOURCE
10)DOC_CATEGORY_CODE :
If you are using automatic
sequential numbering, then Payables Open
Interface Import uses this
column to assign a document category to the
invoice it creates.If the
Sequential Numbering profile value is ”Always” and you do not
enter a value in this
column, then during import Payables will use STANDARD as the category if the
invoice amount is zero or
positive,and CREDIT if the
invoice amount is negative.
If you enable the Allow
Document Category Override Payables option,you can enter the document category
you want Open
Interface Import to assign
to the invoice created from this record
10)VOUCHER_NUM:If you use
manual sequential numbering, then enter a unique value
11)PAYMENT_CURRENCY_CODE:Currency
code for the payment. If you do not provide a value, then
during import
PAYMENT_CURRENCY_CODE will be set to the same value as the INVOICE_CURRENCY_CODE,
12)PAYMENT_METHOD_LOOKUP_CODE:Method
that will be used to pay the invoice.
The value must be a valid
value for the PAYMENT_METHOD lookup code: Validation:
13)ACCTS_PAY_CODE_COMBINATION_ID:Liability
account.
14)ORG_ID
AP_INVOICE_LINES_INTERFACE
----------------------------
1)INVOICE_ID :required
2)INVOICE_LINE_ID :not
reqd,use AP_INVOICE_LINES_INTERFACE_S
3)LINE_NUMBER:unique ,reqd
4)LINE_TYPE_LOOKUP_CODE:valid
values ITEM, TAX,MISCELLANEOUS, or FREIGHT.
5)AMOUNT :Invoice
distribution amount
6)ACCOUNTING_DATE:The GL
Date for the invoice distributions. The date must be in an open
or future period.
7)PO_HEADER_ID or PO_NUMBER
8)PO_LINE_ID or
PO_LINE_NUMBER
9)dist_code_combination_id
or DIST_CODE_CONCATENATED
10)ORG_ID
Document Category Assignment
and Document Sequence Setup:
from
fnd_doc_sequence_assignment,fnd_document_sequences
Now create both headers and
line in one package:
Create or Replace Procedure
m_ap_invoices(ERRBUF out VARCHAR2, RETCODE out VARCHAR2)
IS
--starts the declaring
variables for headers
v_invoice_id
ap_invoices_interface.INVOICE_ID%TYPE ;
v_type
ap_invoices_interface.INVOICE_TYPE_LOOKUP_CODE %TYPE ;
v_supplier
ap_invoices_interface.VENDOR_NAME %TYPE ;
v_invoice_date
ap_invoices_interface.INVOICE_DATE %TYPE;
v_invoice_num
ap_invoices_interface.INVOICE_NUM %TYPE;
v_invoice_curr
ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;
v_invoice_amount
ap_invoices_interface.INVOICE_AMOUNT %TYPE;
v_payment_cross_rate
ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;
v_cross_payment_curr
ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;
v_pay_rate_date
ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;
v_payment_rate_type
ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;
v_description
ap_invoices_interface.DESCRIPTION%TYPE;
v_rate_type
ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;
v_exchange_date
ap_invoices_interface.EXCHANGE_DATE%TYPE;
v_exchange_rate
ap_invoices_interface.EXCHANGE_RATE%TYPE;
v_terms_date
ap_invoices_interface.TERMS_DATE%TYPE;
v_terms ap_invoices_interface.TERMS_ID%TYPE;
v_payment_method
ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt
ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_invoice_recieved_date
ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;
v_recieved_goods_date
ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id
ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;
v_status
ap_invoices_interface.STATUS%TYPE;
v_po_number
ap_invoices_interface.PO_NUMBER%TYPE := NULL;
v_attribute1 ap_invoices_interface.attribute1%TYPE;
v_attribute2
ap_invoices_interface.attribute2%TYPE;
v_attribute3
ap_invoices_interface.attribute3%TYPE;
v_attribute4
ap_invoices_interface.attribute4%TYPE;
v_attribute5
ap_invoices_interface.attribute5%TYPE;
v_attribute6
ap_invoices_interface.attribute6%TYPE;
v_attribute7
ap_invoices_interface.attribute7%TYPE;
v_attribute8
ap_invoices_interface.attribute8%TYPE;
v_attribute9
ap_invoices_interface.attribute9%TYPE;
v_attribute10
ap_invoices_interface.attribute10%TYPE;
v_vendor_id
po_vendors.vendor_id%TYPE;
v_vendor_site_cd
po_vendor_sites.vendor_site_code%TYPE;
v_vendor_site_id
po_vendor_sites.vendor_site_id%TYPE;
v_currency_code
fnd_currencies.currency_code%TYPE;
v_lookup_code
ap_lookup_codes.lookup_code%TYPE;
v_code_comb
gl_code_combinations.code_combination_id%TYPE;
v_segment1
po_headers_all.segment1%TYPE;
v_term_name
ap_terms.name%TYPE;
v_term_id
ap_terms.term_id%TYPE;
v_closed_date
po_headers_all.closed_date%type;
v_process_flag char(1) :=
NULL;
v_last_date DATE;
v_count number;
--ends the declaring
variables for headers
--statrts the declaring
variables for lines
--v_invoice_id
ap_invoice_lines_interface.invoice_id%type;
v_line_no
ap_invoice_lines_interface.line_number%type;
v_amount
ap_invoice_lines_interface.amount%type;
v_expence_account
ap_invoice_lines_interface.dist_code_combination_id%type;
v_expence_Account1
ap_invoice_lines_interface.dist_code_concatenated%type;
v_line_type
ap_invoice_lines_interface.line_type_lookup_code%type;
v_accounting_date ap_invoice_lines_interface.accounting_date%type;
v_invoice_line_id
ap_invoice_lines_interface.invoice_line_id%type;
v_line_attribute1
ap_invoice_lines_interface.attribute1%TYPE;
v_line_attribute2
ap_invoice_lines_interface.attribute2%TYPE;
v_line_attribute3
ap_invoice_lines_interface.attribute3%TYPE;
v_line_attribute4
ap_invoice_lines_interface.attribute4%TYPE;
v_line_attribute5
ap_invoice_lines_interface.attribute5%TYPE;
v_line_attribute6
ap_invoice_lines_interface.attribute6%TYPE;
v_line_attribute7
ap_invoice_lines_interface.attribute7%TYPE;
v_line_attribute8
ap_invoice_lines_interface.attribute8%TYPE;
v_line_attribute9
ap_invoice_lines_interface.attribute9%TYPE;
v_line_attribute10
ap_invoice_lines_interface.attribute10%TYPE;
v_receipt_number
ap_invoice_lines_interface.receipt_number%TYPE;
/*following variables for PO
related invoices*/
v_found char(1) := NULL;
v_po_header_id
ap_invoice_lines_interface.po_header_id%TYPE;
v_po_line_id
ap_invoice_lines_interface.po_line_id%TYPE;
v_po_line_num ap_invoice_lines_interface.po_line_number%TYPE;
v_po_segment
ap_invoice_lines_interface.po_number%TYPE;
v_line_location_id
ap_invoice_lines_interface.po_line_location_id%TYPE;
v_shipment_num
ap_invoice_lines_interface.po_shipment_num%TYPE;
v_po_distribution_id
ap_invoice_lines_interface.po_distribution_id%TYPE;
v_po_distribution_num
ap_invoice_lines_interface.po_distribution_num%TYPE;
--ends the declaring
variables for lines
--declaring cursor for
fetching header,Lines values from staging table.
CURSOR invoice_cur
IS
SELECT INVH.REC_ID,
INVH.TYPE,
INVH.SUPPLIER,
INVH.SITE,
INVH.INVOICE_DATE ,
INVH.INVOICE_NUM
INVOICE_NUM,
INVH.INVOICE_CURR,
INVH.INVOICE_AMOUNT,
INVH.AMOUNT_PAID,
INVH.PAYMENT_CROSS_RATE,
INVH.PAYMENT_CROSS_CURR,
INVH.PAY_RATE_DATE,
INVH.PAYMENT_RATE_TYPE,
INVH.PAYMENT_RATE,
INVH.PAYMENT_AMT,
INVH.DESCRIPTION,
INVH.TRANCTION_CODE ,
INVH.RATE_TYPE,
INVH.EXCHANGE_DATE,
INVH.EXCHANGE_RATE,
INVH.FUNCTIONAL_AMOUNT,
INVH.TERMS_DATE,
INVH.TERMS,
INVH.PAYMENT_METHOD,
INVH.DISCOUNTABLE_AMT,
INVH.INVOICE_RECIEVED_DATE,
INVH.RECIEVED_GOODS_DATE,
INVH.ACCTS_PAY_CODE_COMBINATION_ID,
INVH.STATUS,
INVH.PO_NUMBER,
INVH.ATTRIBUTE1 HEADATTR1,
INVH.ATTRIBUTE2 HEADATTR2,
INVH.ATTRIBUTE3 HEADATTR3,
INVH.ATTRIBUTE4 HEADATTR4,
INVH.ATTRIBUTE5 HEADATTR5,
INVH.ATTRIBUTE6 HEADATTR6,
INVH.ATTRIBUTE7 HEADATTR7,
INVH.ATTRIBUTE8 HEADATTR8,
INVH.ATTRIBUTE9 HEADATTR9,
INVH.ATTRIBUTE10 HEADATTR10,
INVH.PROCESS_FLAG,
INVL.INVOICE_NUM
LINE_INVOICE_NUM,
INVL.LINE_NO,
INVL.AMOUNT,
INVL.EXPENCE_ACCOUNT,
INVL.EXPENCE_ACCOUNT1,
INVL.ACCOUNTING_DATE,
INVL.ATTRIBUTE1 LINEATTR1,
INVL.ATTRIBUTE2 LINEATTR2,
INVL.ATTRIBUTE3 LINEATTR3,
INVL.ATTRIBUTE4 LINEATTR4,
INVL.ATTRIBUTE5 LINEATTR5,
INVL.ATTRIBUTE6 LINEATTR6,
INVL.ATTRIBUTE7 LINEATTR7,
INVL.ATTRIBUTE8 LINEATTR8,
INVL.ATTRIBUTE9 LINEATTR9,
INVL.ATTRIBUTE10 LINEATTR10
FROM M_INVOICE_HEADERS_INT
INVH, M_INVOICE_LINES_INT INVL
WHERE INVH.REC_ID =
INVL.REC_ID;
BEGIN
FOR cur_invoice IN
invoice_cur
LOOP
begin
--Loop starts here to
process invoice headers data
--fetching sequence value
for invoice_id
SELECT
ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;
v_type := cur_invoice.type ;
v_supplier :=
cur_invoice.supplier;
v_invoice_date :=
cur_invoice.invoice_date;
v_invoice_num :=
cur_invoice.invoice_num;
v_invoice_curr :=
cur_invoice.invoice_curr;
v_invoice_amount :=
cur_invoice.invoice_amount;
v_payment_cross_rate :=
cur_invoice.payment_cross_rate;
v_cross_payment_curr :=
cur_invoice.payment_cross_curr;
v_pay_rate_date :=
cur_invoice.pay_rate_date;
v_payment_rate_type :=
cur_invoice.payment_rate_type;
v_description :=
cur_invoice.description;
v_rate_type :=
cur_invoice.rate_type;
v_exchange_date :=
cur_invoice.exchange_date;
v_exchange_rate :=
cur_invoice.exchange_rate;
v_terms_date :=
cur_invoice.terms_date;
v_terms :=
cur_invoice.terms;
v_payment_method :=
cur_invoice.payment_method;
v_discountable_amt :=
cur_invoice.discountable_amt;
v_invoice_recieved_date :=
cur_invoice.invoice_recieved_date;
v_recieved_goods_date :=
cur_invoice.recieved_goods_date;
v_pay_code_combination_id :=
cur_invoice.accts_pay_code_combination_id;
v_status :=
cur_invoice.status;
v_po_number :=
cur_invoice.po_number;
v_attribute1 :=
cur_invoice.HEADATTR1;
v_attribute2 :=
cur_invoice.HEADATTR2;
v_attribute3 :=
cur_invoice.HEADATTR3;
v_attribute4 :=
cur_invoice.HEADATTR4;
v_attribute5 :=
cur_invoice.HEADATTR5;
v_attribute6 :=
cur_invoice.HEADATTR6;
v_attribute7 :=
cur_invoice.HEADATTR7;
v_attribute8 :=
cur_invoice.HEADATTR8;
v_attribute9 := cur_invoice.HEADATTR9;
v_attribute10 :=
cur_invoice.HEADATTR10;
v_process_flag :=
cur_invoice.process_flag;
/* Validate invoice number
if null then assign invoice number equal to week_end_date.*/
IF v_invoice_num is null
THEN
SELECT
NEXT_DAY(SYSDATE,'FRIDAY')
INTO V_LAST_DATE
FROM DUAL;
v_invoice_num :=
v_last_date;
END IF;
/* validate invoice type*/
IF v_type<>'STANDARD'
OR v_type <>'CREDIT' OR v_type <>'DEBIT' THEN
V_TYPE :=NULL ;
END IF;
/* validating vendor id */
BEGIN
select VENDOR_ID
into v_vendor_id
from PO_VENDORS
where vendor_name =
ltrim(rtrim(upper(v_supplier)))
and sysdate >=
start_date_active
AND sysdate <
end_date_active;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'No
vendor id found OR vendor status is not active = ');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* Validation for
VENDOR_SITE_CODE */
BEGIN
select vendor_site_code,
vendor_site_id into v_vendor_site_cd,v_vendor_site_id from PO_VENDOR_SITES
where VENDOR_ID
= v_vendor_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'No
vendor site code found for this vendor - '||v_supplier);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* Invoice currecy code */
BEGIN
SELECT currency_code
INTO v_currency_code
FROM FND_CURRENCIES
WHERE currency_code =
ltrim(rtrim(upper(v_invoice_curr)))
and sysdate between
nvl(start_date_Active,sysdate) and nvl(end_date_active,sysdate);
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Currency
Code Does not Exist for '||v_invoice_curr);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* validating term id */
BEGIN
SELECT TERM_ID ,NAME
INTO V_TERM_ID ,V_TERM_NAME
FROM AP_TERMS
WHERE TERM_ID = v_terms
and v_terms_date between
nvl(start_date_Active,sysdate) and nvl(end_date_active,sysdate); -- v_term_date
not in ver 11.5.0
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Term
id OR Term name Does not Exist for '||v_terms);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/*validating payment method
*/
BEGIN
SELECT lookup_code
INTO v_lookup_code
FROM ap_lookup_codes
WHERE lookup_code
=upper(v_payment_method) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
Payment Method are not exist
in lookup table');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* validating GL Code
Combination validation */
BEGIN
SELECT code_combination_id
INTO v_code_comb
FROM gl_code_combinations
WHERE code_combination_id =
v_pay_code_combination_id ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
Code Combination id not exist in table');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* validating po number */
BEGIN
IF v_po_number is not null
THEN
SELECT segment1 ,
closed_date
INTO v_segment1 ,
v_closed_date
FROM po_headers_all
WHERE segment1 =
v_po_number;
IF v_closed_date is not null
THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
P O Staus is inactive');
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
P O number not found');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/*Validating Payment cross
rate */
BEGIN
IF v_invoice_curr =
v_cross_payment_curr THEN
v_payment_cross_rate := 1;
ELSE
SELECT conversion_rate
INTO v_payment_cross_rate
--v_conversion_rate
FROM gl_daily_rates
WHERE from_currency =
v_invoice_curr
and to_currency=v_cross_payment_curr
and conversion_date=
v_pay_rate_date
and
conversion_type=v_payment_rate_type;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
Conversion rates not found');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
END;
/* validating exchange rate
*/
IF v_rate_type = 'USER' and
v_exchange_rate is not null THEN
NULL;
ELSIF v_rate_type = 'USER'
and v_exchange_rate is null THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
Exchange rate is null for the invoice number -'||v_invoice_num);
ELSIF v_rate_type <>
'USER' and v_exchange_rate is not null THEN
v_exchange_rate :=0 ;
END IF;
/* validating duplication of
invoice */
SELECT COUNT(*)
INTO v_count
FROM AP_INVOICES_ALL
WHERE INVOICE_NUM =
V_INVOICE_NUM
AND VENDOR_ID = v_vendor_id;
IF v_count > 0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'
Invocie already Exists - '||v_invoice_num);
UPDATE M_INVOICE_HEADERS_INT
SET process_flag ='N'
WHERE invoice_num =
v_invoice_num
and supplier = v_supplier;
Else
--inserting validated values
to interface table(headers)
INSERT INTO
ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
po_number,
vendor_id,
vendor_name,
vendor_site_id,
vendor_site_code,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
terms_name,
terms_date ,
description,
status,
source,
payment_cross_rate_type,
payment_cross_rate_date,
payment_cross_rate,
payment_currency_code,
payment_method_lookup_code,
goods_received_date,
invoice_received_date,
gl_date,
accts_pay_code_combination_id,
amount_applicable_to_discount,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
v_invoice_id,
v_invoice_num,
v_type,
v_invoice_date,
v_po_number,
v_vendor_id,
v_supplier,
v_vendor_site_id,
v_vendor_site_cd,
nvl(v_invoice_amount,0),
v_invoice_curr,
v_exchange_rate,
v_rate_type,
v_exchange_date,
v_terms,
v_term_name,
v_terms_date,
v_description,
v_status,
'InvoiceGateway',
v_payment_rate_type,
v_pay_rate_date,
v_payment_cross_rate,
v_cross_payment_curr,
v_payment_method,
v_recieved_goods_date,
v_invoice_recieved_date,
'30-NOV-2003',
v_pay_code_combination_id,
v_discountable_amt,
v_attribute1,
v_attribute2,
v_attribute3,
v_attribute4,
v_attribute5,
v_attribute6,
v_attribute7,
v_attribute8,
v_attribute9,
v_attribute10,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
--starts here to process
invoice lines data
--fetching sequence value
for invoice_line_id
select
ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;
--assigning values from
staging table(lines)
--v_invoice_num
:=cur_invoice.invoice_num;
v_line_no
:=cur_invoice.line_no;
v_amount
:=cur_invoice.amount;
v_expence_account
:=cur_invoice.expence_account;
v_expence_account1
:=cur_invoice.expence_account1;
v_accounting_date
:=cur_invoice.accounting_date;
v_line_type
:='Item';--(Item, Fright,Tax,Miscellaneous)
v_line_attribute1
:=cur_invoice.LINEATTR1;
v_line_attribute2
:=cur_invoice.LINEATTR2;
v_line_attribute3
:=cur_invoice.LINEATTR3;
v_line_attribute4 :=cur_invoice.LINEATTR4;
v_line_attribute5
:=cur_invoice.LINEATTR5;
v_line_attribute6
:=cur_invoice.LINEATTR6;
v_line_attribute7
:=cur_invoice.LINEATTR7;
v_line_attribute8
:=cur_invoice.LINEATTR8;
v_line_attribute9
:=cur_invoice.LINEATTR9;
v_line_attribute10 :=cur_invoice.LINEATTR10;
-- Validate Line Level
Accounting Date
BEGIN
SELECT 'Y'
INTO v_found
FROM gl_period_statuses
WHERE application_id =
200--ASSUMTION
AND set_of_books_id =
3--ASSUMTION
AND SYSDATE between
start_date AND end_date
AND closing_status IN ('O',
'F')
AND
NVL(adjustment_period_flag, 'N') = 'N'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.log,'Accounting
Date Is Not In Open');
END;
/*------start to Validate
Line Level for po_number matching/validating----------*/
-- get the info of header
(if invoice is related to po_number)
BEGIN
Select po_header_id,segment1
into v_po_header_id,
v_po_segment
From po_headers_all
where Vendor_ID = vendor_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.log,'PO
not found in Headers');
END;
-- get the info line(if
invoice is related to po_number)
BEGIN
Select po_line_id,line_num
into v_po_line_id,
v_po_line_num
From po_lines_all
where
Po_Header_ID =
v_po_header_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.log,'PO
not found in Lines');
END;
-- get the info location (if
invoice is related to po_number)
BEGIN
Select
line_location_id,shipment_num
into v_line_location_id,
v_shipment_num
From po_line_locations_all
where
Po_Header_ID =
v_po_header_id
and
Po_Line_ID = v_po_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.log,'PO
not found in Locations');
END;
-- get the info of
distributions(if invoice is related to po_number)
BEGIN
Select po_distribution_id,distribution_num
into v_po_distribution_id,
v_po_distribution_num
From po_distributions_all
where
Po_Header_ID =
v_po_header_id
and
Po_Line_ID = v_po_line_id
and
Line_Location_ID =
v_line_location_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.log,'PO
not found in distributionss');
END;
/*------end to Validate Line
Level for po_number matching/validating----------*/
/*start to inserting invoice
line*/
INSERT INTO
ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
dist_code_concatenated,
dist_code_combination_id,
po_header_id,
po_number,
po_line_id,
po_line_number,
po_line_location_id,
po_shipment_num,
po_distribution_id,
po_distribution_num,
receipt_number,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES(v_invoice_id,
v_invoice_line_id,
v_line_no,
v_line_type,
v_amount,
v_accounting_date,
v_expence_account1,
v_expence_account,
v_po_header_id,
v_po_segment,
v_po_line_id,
v_po_line_num,
v_line_location_id,
v_shipment_num,
v_po_distribution_id,
v_po_distribution_num,
v_receipt_number,
v_line_attribute1,
v_line_attribute2,
v_line_attribute3,
v_line_attribute4,
v_line_attribute5,
v_line_attribute6,
v_line_attribute7,
v_line_attribute8,
v_line_attribute9,
v_line_attribute10,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
/*end to inserting invoice
line*/
-- ends here to process
invoice lines data
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
commit;
exit;
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception'||SQLERRM);
rollback;
exit;
end;
END LOOP;--Loop ends here to
process invoice headers data
--FND_FILE.PUT_LINE(FND_FILE.log,'Data
Transfer is Successfull');
END;
/
AP Invoice Interface
This interface
helps us to import vendor invoices into Oracle applications from external
systems into Oracle Applications.
Interface tables:
1]
AP_INVOICES_INTERFACE
This is the open
interface table for importing AP Invoices from external sources and stores
header information about invoices.
Invoice data
comes from sources including:
Σ EDI invoices from suppliers that are loaded
through Oracle e-Commerce Gateway
Σ Supplier invoices that are transferred
through the Oracle XML Gateway
Σ Invoices that are loaded using Oracle
SQL*Loader
Σ Lease invoices from Oracle Property Manager
Σ Lease payments from Oracle Assets
Σ Credit card transaction data that are
loaded using the Credit Card Invoice Interface Summary
Σ Expense Report invoices from Oracle
Internet Expenses
Σ Payment Requests from Receivables
Σ Invoices that are entered through the
Invoice Gateway.
There is one row
for each invoice you import. Oracle Payables application uses this information
to create invoice header information
when Payables
Open Interface program is submitted.
Data in the
AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE
table to create Payables
Invoice, Invoice
lines, Distributions and Schedule payments. Data in this table can be viewed
and edited using ‘Open Interface
Invoices’
window. The Payables Open Interface program validates each record in this
interface table selected for import, and if the
record contains
valid data then the program creates a Payables Invoice.
Important
columns:
INVOICE_ID (Required)
: Unique identifier for this invoice within this batch. Same value should be
populated in invoice’s lines in the
AP_INVOICE_LINES_INTERFACE
table to identify the data as belonging to the same invoice.
INVOICE_NUM (Required)
: Enter the invoice number that needs to be assigned to the invoice created in
Payables from this
record.
INVOICE_TYPE_LOOKUP_CODE
(Optional) : Type of invoice: Credit or Standard.
INVOICE DATE (Optional)
: Date of the invoice. If you do not enter a value, the system uses the date
you submit Payables Open
Interface Import
as the invoice date.
PO_NUMBER (Optional)
: Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be
populated if invoice
to be matched
with an purchase order.
VENDOR_ID &
VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and
VENDOR_SITE_ID is Internal
supplier site
identifier. Supplier of the invoice to be derived by value in one of the
following columns in this table: VENDOR_ID,
VENDOR_NUM,
VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.
VENDOR_NUM &
VENDOR_NAME (Optional) : Supplier number and name. You must identify the
supplier by entering a value for
one of the
following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID,
VENDOR_SITE CODE, or
PO_NUMBER.
INVOICE_AMOUNT (Required)
: Amount of the invoice.
INVOICE_CURRENCY_CODE
(Optional) : Currency code for the invoice. If you want to create foreign
currency invoices, enter a
currency code
that is different from your functional currency.
EXCHANGE_RATE (Optional)
: This column is required if you enter a foreign currency code in the
INVOICE_CURRENCY_CODE
column and you
enter User as the EXCHANGE_RATE_TYPE.
TERMS_ID (Optional)
: Internal identifier for the payment terms.
DESCRIPTION (Optional)
: Enter the description that you want to assign to the invoice created from
this record.
SOURCE (Required)
: Source of the invoice data. If you import EDI invoices from the Oracle EDI
Gateway, the source is EDI
Gateway. For
invoices you import using SQL*Loader, use a QuickCode with the type Source that
you have defined in the
QuickCodes
window in Payables.
2]
AP_INVOICE_LINES_INTERFACE
This is the
lines interface table for the AP Invoice Open Interface and it is used in
conjunction with AP_INVOICE_INTERFACE table.
AP_INVOICE_LINES_INTERFACE
stores information used to create one or more invoice distributions. Note that
one row in this
table may
create, during the import process, more than one invoice distribution.
Important
columns:
INVOICE_ID (Required)
:Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE
table.
INVOICE_LINE_ID
: This value is not required. You can enter a unique number for each invoice
line of an invoice.
LINE_NUMBER (Optional)
: You can enter a unique number to identify the line.
LINE_TYPE_LOOKUP_CODE
(Required) : Enter the lookup code for the type of invoice distribution that
you want Payables Open
Interface Import
to create from this record. The code you enter must be ITEM, TAX,
MISCELLANEOUS, or FREIGHT. These lookup
codes are stored
in the AP_LOOKUP_CODES table.
AMOUNT (Required)
: The invoice distribution amount. If you are matching to a purchase order, the
AMOUNT =
QUANTITY_INVOICED
x UNIT PRICE. If the total amount of all the invoice distributions does not
equal the amount of the invoice
that has the
same INVOICE_ID, then Payables Open Interface Import will reject the invoice.
Concurrent program:
Payables Open
Interface Import
Parameters:
Source: Choose
the source of the invoices from the list of values. Use EDI Gateway, Credit
Card, or a Source type QuickCode you
defined in the
Payables QuickCodes window.
Group: To limit
the import to invoices with a particular Group ID, enter the Group ID. The
Group must exactly match the GROUP_ID
in the Payables
Open Interface tables.
Batch Name: Payables
groups the invoices created from the invoices you import and creates an invoice
batch with the batch name
you enter. You
can enter a batch name only if you have enabled the Use Batch Control Payables
option, and if you have enabled
the Use Batch Control
Payables option, you must enter a batch name. If you use a batch name and some
invoices are rejected
during the
import process, you can import the corrected invoices into the same batch if
you enter the exact batch name during the
subsequent
import.
Hold Name: If
you want to place all invoices on hold at the time of import, enter an Invoice
Hold Reason. You can define your own
hold reasons in
the Invoice Approvals window.
Hold Reason: Payables
displays the Invoice Hold Reason Description.
GL Date: If you
want to assign a specific GL Date to all invoices, enter a GL Date. If you do
not enter a value here, the system will
assign a GL Date
based on the GL Date Payables option.
Purge: Enter Yes
if you want Payables to delete all successfully imported invoice records that
match the Source and Group ID of
this import.
Payables does not delete any invoice data for which it has not yet created
invoices. If you want to purge later, you can
use the Payables
Open Interface Purge Program.
Steps:
1] Firstly,
let’s get a unique number to be used as INVOICE_ID to the invoice to be
imported. This method ensures that each
invoice has a
unique INVOICE_ID assignment.
1 select ap_invoices_interface_s.nextval from dual;
2 NEXTVAL
3 -
4 132277
2] Then, create
records in the Invoice Open Interface tables through SQL queries.
01 insert into AP_INVOICES_INTERFACE
(
02 invoice_id,
03 invoice_num,
04 vendor_id,
05 vendor_site_id,
06 invoice_amount,
07 INVOICE_CURRENCY_CODE,
08 invoice_date,
09 DESCRIPTION,
10 PAY_GROUP_LOOKUP_CODE,
11 source,
12 org_id
13 )
14 values (
15 132277,
16 'INV100',
17 '7124',
18 '11792',
19 1200.00,
20 'USD',
21
to_date('01-31-
2010','mm-dd-yyyy'),
22
'This Invoice is
created for test purpose',
23
'WUFS
SUPPLIER',
24
'Manual
Invoice Entry',
25 81
26 );
27
28 insert into AP_INVOICE_LINES_INTERFACE
(
29 invoice_id,
30 line_number,
31 line_type_lookup_code,
32 amount
33 )
34 values (
35 132277,
36 1,
37 'ITEM',
38 1200.00
39 );
3] You can go to
Payables > Invoices > Entry > Open Interface Invoices to check the
details of Invoice and Invoice Lines from the
front end. If
required you can do any modifications here. Alternatively you can use these
forms to put invoice data in
AP_INVOICES_INTERFACE
and AP_INVOICE_LINES_INTERFACE tables.
4] Go to the
front end and run the concurrent program “Payables Open Interface Import” to
submit a request for Invoice Import.
Output:
output of
Payables Open Interface Import
5] The imported
invoice becomes available for review in Invoices Workbench.
Interface
Invoice through Invoice Banch
AP INTERFACE
CREATE OR REPLACE PACKAGE
BODY xxte_apinvoice_pay_intf
AS
PROCEDURE
xxte_apinvoice_main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
BEGIN
xxte_apinvoice_ins
(lv_errbuff, lv_retcode);
xxte_apinvoice_comm
(lv_errbuff, lv_retcode);
EXCEPTION
WHEN OTHERS
THEN
lv_retcode := 1;
lv_errbuff := 'Error: In
Main Procudure.' || SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_errbuff);
END;
PROCEDURE
xxte_apinvoice_comm (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR stg_cur
IS
SELECT a.*, a.ROWID
FROM xxte_apinv_comm_st a
WHERE process_flag = 'N';
-- declare local variables.
lv_user_id NUMBER;
lv_resp_id NUMBER;
lv_resp_appl_id NUMBER;
lv_vendor_id NUMBER;
lv_vendor_no NUMBER;
lv_error_message VARCHAR2
(2400);
lv_error_code NUMBER := 0;
lv_vendor_site_id NUMBER;
lv_invoice_date VARCHAR2
(1);
lv_gl_date VARCHAR2 (1);
lv_exist_rec NUMBER;
lv_invoice_amt NUMBER;
lv_acc NUMBER;
lv_source VARCHAR2 (30);
lv_invoice_type VARCHAR2
(50);
lv_dist_ccid NUMBER;
BEGIN
lv_user_id :=
fnd_profile.VALUE ('USER_ID');
lv_resp_id :=
fnd_profile.VALUE ('RESP_ID');
lv_resp_appl_id :=
fnd_profile.VALUE ('RESP_APPL_ID');
BEGIN
fnd_global.apps_initialize
(lv_user_id, lv_resp_id, lv_resp_appl_id);
mo_global.init ('AP');
END;
-- Purge the previous
processed data in the stage/interface table
BEGIN
DELETE FROM
ap_invoice_lines_interface apil
WHERE apil.invoice_id IN
(SELECT invoice_id
FROM ap_invoices_interface
WHERE status = 'PROCESSED');
DELETE FROM
ap_invoices_interface
WHERE status = 'PROCESSED';
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Delete The
Invoice Stage/Interface Table.'
|| SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
fnd_file.put_line
(2,
'****************************Agent
Commission Invoice***************************'
);
fnd_file.put_line
(2,
'-------------------------------------------------------------------------------
'
);
FOR i IN stg_cur
LOOP
lv_error_message := NULL;
lv_error_code := 0;
lv_source := 'TE INSIS';
lv_dist_ccid := 2106;
-- Validation for Invoice
Type
BEGIN
SELECT invoice_type
INTO lv_invoice_type
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_invoice_type IS NULL
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invoice Type Is Null';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invalid Invoice Type';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor
name
BEGIN
SELECT vendor_id
INTO lv_vendor_id
FROM po_vendors
WHERE vendor_name =
i.vendor_name;
IF lv_vendor_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Name '
|| ' '
|| i.vendor_name
|| ' '
|| 'is Not Found';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Name '
|| ' '
|| i.vendor_name
|| ' '
|| 'is Not Found';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor no
BEGIN
SELECT segment1
INTO lv_vendor_no
FROM po_vendors
WHERE segment1 = i.vendor_no
AND vendor_name = i.vendor_name;
IF lv_vendor_no IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor No '
|| ' '
|| i.vendor_no
|| ' '
|| ' is Not Found for the
vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor No '
|| ' '
|| i.vendor_no
|| ' '
|| ' is Not Found for the
vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for vendor
site id
BEGIN
SELECT vendor_site_id
INTO lv_vendor_site_id
FROM po_vendor_sites_all
WHERE UPPER
(vendor_site_code) = UPPER (i.vendor_site)
AND vendor_id IN (SELECT
vendor_id
FROM po_vendors
WHERE vendor_name =
i.vendor_name);
IF lv_vendor_site_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Site Name '
|| ' '
|| i.vendor_site
|| ' '
|| 'is Not found for the
Vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Vendor Site Name '
|| ' '
|| i.vendor_site
|| ' '
|| 'is Not found for the
Vendor'
|| ' '
|| i.vendor_name;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for invoice
date
BEGIN
SELECT 'x'
INTO lv_invoice_date
FROM gl_period_statuses
WHERE application_id = 200
---For Oracle Payables.
AND set_of_books_id = 2022
AND (i.invoice_date >=
start_date
AND i.invoice_date <=
end_date
)
AND closing_status = 'O';
IF lv_invoice_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invoice Date Is NULL ';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for
the Invoice Date '
|| ' '
|| i.invoice_date;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for GL date
BEGIN
SELECT 'x'
INTO lv_gl_date
FROM gl_period_statuses
WHERE application_id = 200
---For Oracle Payables.
AND set_of_books_id = 2022
AND (i.gl_date >=
start_date AND i.gl_date <= end_date)
AND closing_status = 'O';
IF lv_gl_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'GL Date Is NULL ';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for
the GL Date '
|| ' '
|| i.gl_date;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for currency
code
BEGIN
IF i.invoice_currency !=
'AED'
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Invalid Currency Code '
|| ' '
|| i.invoice_currency;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
END;
-- Validation for Invoice
amount
BEGIN
SELECT invoice_amount
INTO lv_invoice_amt
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_invoice_amt IS NULL
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invoice Amount Is Null';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invoice Amount Is Null';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for Account
BEGIN
SELECT dist_account
INTO lv_acc
FROM xxte_apinv_comm_st
WHERE ROWID = i.ROWID;
IF lv_acc IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: ' || 'Distribution
Account Is Null';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: ' || 'Distribution
Account Is Null';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
--Validation for excisting
record in base table
BEGIN
SELECT COUNT (*)
INTO lv_exist_rec
FROM ap_invoices_all
WHERE invoice_amount =
i.invoice_amount
AND invoice_date =
i.invoice_date
AND vendor_id IN (SELECT
vendor_id
FROM po_vendors
WHERE vendor_name =
i.vendor_name);
IF lv_exist_rec > 0
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Record Already Exists';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Record Already Exists';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
IF lv_invoice_type IN
('PAYABLE', 'COMMISION ADV',
'CLAIM', 'SURRENDER',
'MATURITY', 'RETAKAFUL EXP',
'RETAKAFUL PAYABLE',
'CLAIM PAYABLE', 'DEATH
CLAIM EXP')
AND lv_invoice_amt > 0
THEN
lv_invoice_type :=
'STANDARD';
ELSIF lv_invoice_type IN
('PAYABLE', 'COMMISION ADV',
'REFUND', 'RETAKAFUL EXP',
'RETAKAFUL PAYABLE',
'RETAFUL COMMISION',
'RETAKAFUL SHARE IN EXP',
'RECEIVABLE FROM RETAKAFUL')
AND lv_invoice_amt < 0
THEN
lv_invoice_type := 'DEBIT';
ELSE
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Invalid Invoice Type';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
IF lv_error_code = 0
THEN
INSERT INTO
ap_invoices_interface
(invoice_id,
invoice_num,
gl_date,
invoice_type_lookup_code, invoice_date,
vendor_id, vendor_site_id,
invoice_amount,
invoice_currency_code,
description, SOURCE, org_id
)
VALUES
(ap_invoices_interface_s.NEXTVAL,
'INSIS_'
|| i.vendor_site
|| '_'
|| ap_invoices_interface_s.CURRVAL,
i.gl_date, lv_invoice_type,
-- will change
i.invoice_date,
lv_vendor_id,
lv_vendor_site_id, i.invoice_amount,
i.invoice_currency,
i.description, -- will change
lv_source, 81
);
INSERT INTO
ap_invoice_lines_interface
(invoice_id, line_number,
line_type_lookup_code,
amount,
dist_code_combination_id,
-- if value dist id is
entered here no need to enter value in dist_code_concatenated
org_id
)
VALUES
(ap_invoices_interface_s.CURRVAL, i.line_no, 'ITEM',
i.amount, lv_dist_ccid, --
will update
81
);
fnd_file.put_line (2,
'Payable Invoice Is Created');
UPDATE xxte_apinv_comm_st
SET process_flag = 'S'
WHERE ROWID = i.ROWID;
ELSE
fnd_file.put_line (2,
'Please Check The Log File For Error(s)');
UPDATE xxte_apinv_comm_st
SET process_flag = 'E',
error_message =
lv_error_message
WHERE ROWID = i.ROWID;
END IF;
END LOOP;
COMMIT;
fnd_file.put_line
(2,
'****************************
END ***************************'
);
fnd_file.put_line
(2,
'-------------------------------------------------------------------------------
'
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
fnd_file.put_line
(fnd_file.LOG, 'Error :' || SQLERRM);
COMMIT;
END;
PROCEDURE xxte_apinvoice_ins
(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR ins_c
IS
SELECT 'PAYABLE'
invoice_type, a.vendor_name, a.vendor_no,
a.vendor_site,
(SELECT invoice_date
FROM xxte_apinv_pay_temp
WHERE vendor_name =
a.vendor_name
AND vendor_site =
a.vendor_site
AND ROWNUM = 1)
invoice_date,
(SELECT invoice_currency
FROM xxte_apinv_pay_temp
WHERE vendor_name =
a.vendor_name
AND vendor_site =
a.vendor_site
AND ROWNUM = 1)
invoice_currency,
SUM (a.invoice_amount)
invoice_amount,
(SELECT gl_date
FROM xxte_apinv_pay_temp
WHERE vendor_name =
a.vendor_name
AND vendor_site =
a.vendor_site
AND ROWNUM = 1) gl_date,
(SELECT description
FROM xxte_apinv_pay_temp
WHERE vendor_name =
a.vendor_name
AND vendor_site =
a.vendor_site
AND ROWNUM = 1) description,
SUM (a.amount) amount,
(SELECT dist_account
FROM xxte_apinv_pay_temp
WHERE vendor_name =
a.vendor_name
AND vendor_site =
a.vendor_site
AND ROWNUM = 1) dist_account
FROM xxte_apinv_pay_temp a
WHERE a.invoice_type IN
('COMMISION ADV', 'PAYABLE')
GROUP BY a.vendor_name,
a.vendor_site, a.vendor_no
HAVING SUM
(a.invoice_amount) != 0
UNION
SELECT *
FROM xxte_apinv_pay_temp
WHERE invoice_type NOT IN
('COMMISION ADV', 'PAYABLE');
lv_error_message VARCHAR2
(2400);
lv_error_code NUMBER := 0;
BEGIN
BEGIN
DELETE FROM
xxte_apinv_comm_st
WHERE process_flag != 'N';
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Delete
Record(s) In Staging Table.'
|| SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
FOR i IN ins_c
LOOP
INSERT INTO
xxte_apinv_comm_st
(invoice_type, vendor_name,
vendor_no,
vendor_site, invoice_date,
invoice_currency,
invoice_amount, gl_date,
description, amount,
dist_account
)
VALUES (i.invoice_type,
i.vendor_name, i.vendor_no,
i.vendor_site,
i.invoice_date, i.invoice_currency,
i.invoice_amount, i.gl_date,
i.description, i.amount,
i.dist_account
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Insert
Data In Staging Table.' || SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
END xxte_apinvoice_pay_intf;
AR INTERFACE
CREATE OR REPLACE PACKAGE
BODY XXTE_AR_INVOICE_INTF
AS
PROCEDURE
XXTE_ARINVOICE_MAIN (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
BEGIN
xxte_arinvoice_ins
(lv_errbuff, lv_retcode);
EXCEPTION
WHEN OTHERS
THEN
lv_retcode := 1;
lv_errbuff := 'Error: In
Main Procudure.' || SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_errbuff);
END;
PROCEDURE xxte_arinvoice_ins
(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
CURSOR stg_cur
IS
SELECT a.*, a.ROWID
FROM xxte_ar_invoice_st a
WHERE process_flag = 'N';
p_api_version NUMBER;
p_init_msg_list VARCHAR2
(200);
p_commit VARCHAR2 (200);
p_batch_source_rec
apps.ar_invoice_api_pub.batch_source_rec_type;
p_trx_header_tbl
apps.ar_invoice_api_pub.trx_header_tbl_type;
p_trx_lines_tbl
apps.ar_invoice_api_pub.trx_line_tbl_type;
p_trx_dist_tbl
apps.ar_invoice_api_pub.trx_dist_tbl_type;
p_trx_salescredits_tbl
apps.ar_invoice_api_pub.trx_salescredits_tbl_type;
p_trx_contingencies_tbl
apps.ar_invoice_api_pub.trx_contingencies_tbl_type;
x_customer_trx_id NUMBER;
x_return_status VARCHAR2
(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
h_trx_header_id
ra_customer_trx_all.customer_trx_id%TYPE;
l_trx_ln_hdr_id ra_customer_trx_all.customer_trx_id%TYPE;
l_trx_line_id
ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
lv_errbuff VARCHAR2 (1000);
lv_retcode NUMBER;
lv_user_id NUMBER;
lv_resp_id NUMBER;
lv_resp_appl_id NUMBER;
lv_source NUMBER;
lv_gl_date VARCHAR2(1);
lv_trx_date VARCHAR2(1);
lv_class NUMBER;
lv_customer_id NUMBER;
lv_error_message VARCHAR2
(2400);
lv_error_code NUMBER := 0;
BEGIN
lv_user_id :=
fnd_profile.VALUE ('USER_ID');
lv_resp_id :=
fnd_profile.VALUE ('RESP_ID');
lv_resp_appl_id :=
fnd_profile.VALUE ('RESP_APPL_ID');
BEGIN
fnd_global.apps_initialize
(lv_user_id, lv_resp_id, lv_resp_appl_id);
mo_global.init ('AR');
END;
-- Purge the previous
processed data in the stage table
BEGIN
DELETE FROM
xxte_ar_invoice_st
WHERE process_flag != 'N';
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Delete
Record(s) In Staging Table.'
|| SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
fnd_file.put_line
(2,
'****************************AR
Invoice Process***************************'
);
fnd_file.put_line
(2,
'-------------------------------------------------------------------------------
'
);
FOR i IN stg_cur
LOOP
lv_error_message := NULL;
lv_error_code := 0;
lv_source := 1002;--'TE
INSIS';
--lv_class := 1080;
-- Validation for GL date
BEGIN
SELECT 'X'
INTO lv_gl_date
FROM gl_period_statuses
WHERE application_id = 222
---For Oracle Receivable
AND set_of_books_id = 2022
AND (i.gl_date >=
start_date AND i.gl_date <= end_date)
AND closing_status = 'O';
IF lv_gl_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'GL Date Is NULL or Period is Not Open for the GL Date ';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for
the GL Date or GL Date IS Null'
|| ' '
|| i.gl_date;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for Trx date
BEGIN
SELECT 'X'
INTO lv_trx_date
FROM gl_period_statuses
WHERE application_id = 222
---For Oracle Receivable
AND set_of_books_id = 2022
AND (i.trx_date >=
start_date AND i.trx_date <= end_date)
AND closing_status = 'O';
IF lv_trx_date IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_code := 1;
lv_error_message := 'Error:
' || 'Transaction Date Is NULL or Period is Not Open for the Transaction Date
';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Period is Not Open for
the Transaction Date or Transaction Date IS Null'
|| ' '
|| i.trx_date;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
-- Validation for customer
name
BEGIN
SELECT customer_id
INTO lv_customer_id
FROM ar_customers
WHERE customer_name =
i.bill_to_name;
IF lv_customer_id IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Customer Name '
|| ' '
|| i.bill_to_name
|| ' '
|| 'is Not Found';
fnd_file.put_line (fnd_file.LOG,
lv_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Customer Name '
|| ' '
|| i.bill_to_name
|| ' '
|| 'is Not Found';
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
--- Validation for
description
BEGIN
IF i.description IS NULL
THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Description is null' ;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
END;
--- Validation for unit
price
BEGIN
IF i.unit_price IS NULL THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Unit Price is null' ;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
END;
--- Validation for invoicing
rule
BEGIN
IF i.class = '1020' THEN
IF i.rule_end_date IS NULL
and i.rule_start_date IS NULL THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Rule Start Date/Rule End
Date Is Null' ;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
ELSIF i.rule_start_date >
i.rule_end_date THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'Rule End Date Is Greater
Then Rule Start Date' ;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
ELSIF i.rule_end_date
<> i.gl_date and i.rule_end_date <> i.trx_date THEN
lv_error_code := 1;
lv_error_message :=
'Error: '
|| 'GL Date, Transaction
Date and Rule End Date Must Be Same' ;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END IF;
END IF;
END;
IF lv_error_code = 0 THEN
/* API initialize */
p_api_version := 1.0;
p_init_msg_list :=
fnd_api.g_false;
p_commit := fnd_api.g_true;
p_batch_source_rec.batch_source_id
:= lv_source;
/*Header Part*/
SELECT
ra_customer_trx_s.NEXTVAL
INTO h_trx_header_id
FROM DUAL;
p_trx_header_tbl
(1).trx_header_id := h_trx_header_id;
p_trx_header_tbl
(1).trx_date := i.trx_date;
-- p_trx_header_tbl
(1).trx_currency := 'AED';
-- p_trx_header_tbl
(1).trx_class := 'INV';
p_trx_header_tbl
(1).cust_trx_type_id := i.class;
p_trx_header_tbl (1).gl_date
:= i.gl_date;
p_trx_header_tbl (1).bill_to_customer_id
:= lv_customer_id;
IF i.class = '1020' THEN
p_trx_header_tbl
(1).invoicing_rule_id := -2;
END IF;
--p_trx_header_tbl
(1).term_id := 5;
/*Line Part*/
SELECT
ra_customer_trx_s.CURRVAL
INTO l_trx_ln_hdr_id
FROM DUAL;
p_trx_lines_tbl (1).trx_header_id
:= l_trx_ln_hdr_id;
SELECT
ra_customer_trx_lines_s.NEXTVAL
INTO l_trx_line_id
FROM DUAL;
p_trx_lines_tbl
(1).trx_line_id := l_trx_line_id;
p_trx_lines_tbl
(1).line_number := 1;
p_trx_lines_tbl
(1).description := i.description;
p_trx_lines_tbl (1).quantity_invoiced
:= 1;
p_trx_lines_tbl
(1).unit_selling_price := i.unit_price;
p_trx_lines_tbl
(1).line_type := 'LINE';
IF i.class = '1020' THEN
p_trx_lines_tbl
(1).accounting_rule_id := 2000;
p_trx_lines_tbl
(1).rule_start_date := i.rule_start_date;
p_trx_lines_tbl
(1).rule_end_date := i.rule_end_date;
END IF;
x_customer_trx_id := NULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
BEGIN
apps.ar_invoice_api_pub.create_single_invoice
(p_api_version,
p_init_msg_list,
p_commit,
p_batch_source_rec,
p_trx_header_tbl,
p_trx_lines_tbl,
p_trx_dist_tbl,
p_trx_salescredits_tbl,
p_trx_contingencies_tbl,
x_customer_trx_id,
x_return_status,
x_msg_count,
x_msg_data
);
fnd_file.put_line (2,
'Receivable Invoice Is Created. Customer Trx ID:'|| x_customer_trx_id);
UPDATE xxte_ar_invoice_st
SET process_flag = 'S'
WHERE ROWID =
i.ROWID;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Insert
Data In Base Table.' || SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
ELSE
fnd_file.put_line (2,
'Please Check The Log File For Error(s)');
UPDATE xxte_ar_invoice_st
SET process_flag = 'E',
error_message =
lv_error_message
WHERE ROWID = i.ROWID;
END IF;
END LOOP;
COMMIT;
fnd_file.put_line
(2,
'****************************
END ***************************'
);
fnd_file.put_line
(2,
'-------------------------------------------------------------------------------
'
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := 1;
lv_error_message :=
'Error: Unable To Insert
Data In Base Table.' || SQLERRM;
fnd_file.put_line
(fnd_file.LOG, lv_error_message);
END;
END xxte_ar_invoice_intf;
GL INTERFACE
CREATE OR REPLACE PACKAGE
BODY APPS.INSIS_GL_INTERFACE
/************************************************************************************************************************************
******************************************************************************
******************************************************************************
**************************************************************************************************************************************/
IS
gn_user_id NUMBER :=
Fnd_Profile.VALUE ('USER_ID');
gn_interface_batch_id
NUMBER;
gn_request_id NUMBER := fnd_global.conc_request_id;
gn_group_id NUMBER;
gn_records_inserted NUMBER
:= 0;
gn_records_validated NUMBER
:= 0;
gn_total_records NUMBER :=
0;
gn_error_records NUMBER :=
0;
gv_exception VARCHAR2 (240);
gn_org_id NUMBER := 82;
/* Moving Data From
Warehouse Table To GL Staging Table */
PROCEDURE
load_into_gl_stage_table
IS
CURSOR c1
IS
SELECT GL.ROWID, GL.*
FROM GL_TRANSACTIONS_TEMP
GL;
--where GL.transfer_status =
'N'
BEGIN
FOR rec IN c1
LOOP
INSERT INTO GL_STAGE_TABLE (
STATUS,
LEDGER_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE ,
USER_CURRENCY_CONVERSION_TYPE
,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
ENTERED_DR,
ENTERED_CR,
REFERENCE4,
REFERENCE5,
PERIOD_NAME,
PROCESS_FLAG,
ERROR_DESC,
REQUEST_ID,
PROCESS_DATE,
VALIDATION_STATUS
)
VALUES (
rec.STATUS,
rec.LEDGER_ID,
rec.ACCOUNTING_DATE,
rec.CURRENCY_CODE,
REC.CURRENCY_CONVERSION_RATE
,
REC.USER_CURRENCY_CONVERSION_TYPE
,
sysdate,
gn_user_id,
'A',
rec.USER_JE_CATEGORY_NAME,
rec.USER_JE_SOURCE_NAME,
NVL (rec.SEGMENT1, '00'),
NVL (rec.SEGMENT2, '000'),
NVL (rec.SEGMENT3, '000'),
NVL (rec.SEGMENT4,
'000000'),
NVL (rec.SEGMENT5, '000'),
NVL (rec.SEGMENT6, '00'),
NVL (rec.SEGMENT7, '000'),
NVL (rec.SEGMENT8, '000'),
(CASE
WHEN rec.ENTERED_DR < 0
THEN NULL
ELSE ABS (rec.ENTERED_DR)
END),
(CASE
WHEN rec.ENTERED_DR > 0
THEN NULL
ELSE ABS (rec.ENTERED_DR)
END),
rec.REFERENCE4,
rec.REFERENCE5,
rec.PERIOD_NAME,
'I',
rec.ERROR_DESC,
gn_request_id,
SYSDATE,
'N'
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END
load_into_gl_stage_table;
/* Validating GL Staging
Table */
PROCEDURE
validate_gl_records
IS
lc_validation_status
VARCHAR2 (10) := NULL;
lv_error_message VARCHAR2
(2000);
CURSOR lcu
IS
SELECT DISTINCT REFERENCE4
FROM GL_STAGE_TABLE spst
WHERE NVL (process_flag,
'I') = 'I';
CURSOR lcu_sub (ledger_name
VARCHAR2)
IS
SELECT ROWID, spst.*
FROM GL_STAGE_TABLE spst
WHERE NVL (process_flag,
'I') = 'I' AND REFERENCE4 = ledger_name;
ln_org_id NUMBER;
ln_exist NUMBER;
code_combination_count
NUMBER;
CONCATENATED_SEGMENTS_TEMP
VARCHAR2 (100);
code_invalied_count NUMBER;
temp_error_message VARCHAR2
(500);
OPEN_PERIOD_COUNT number;
BEGIN
FOR rec IN lcu
LOOP
/*
fnd_file.put_line(fnd_file.log, 'Validation Status' || lc_validation_status);
If rec.patient_number is
null then
lv_error_message :=
lv_error_message ||'Patient Number not found,';
lc_validation_status := 'E';
End If;
If lc_validation_status is
null then
End If;
*/
code_invalied_count := 0;
FOR rec_sub IN lcu_sub
(rec.REFERENCE4)
LOOP
code_combination_count := 0;
OPEN_PERIOD_COUNT := 0;
temp_error_message := NULL;
SELECT rec_sub.SEGMENT1
|| '.'
|| rec_sub.SEGMENT2
|| '.'
|| rec_sub.SEGMENT3
|| '.'
|| rec_sub.SEGMENT4
|| '.'
|| rec_sub.SEGMENT5
|| '.'
|| rec_sub.SEGMENT6
|| '.'
|| rec_sub.SEGMENT7
|| '.'
|| rec_sub.SEGMENT8
INTO
CONCATENATED_SEGMENTS_TEMP
FROM DUAL;
SELECT COUNT ( * )
INTO code_combination_count
FROM
gl_code_combinations_kfv
WHERE CONCATENATED_SEGMENTS
= CONCATENATED_SEGMENTS_TEMP;
SELECT COUNT(*) INTO
OPEN_PERIOD_COUNT FROM GL_PERIOD_STATUSES
WHERE CLOSING_STATUS LIKE
'O'
AND APPLICATION_ID = 101
AND PERIOD_NAME =
to_char(rec_sub.ACCOUNTING_DATE,'MON-YY');
If rec_sub.CURRENCY_CODE !=
'AED'
then
If
rec_sub.USER_CURRENCY_CONVERSION_TYPE = 'User' THEN
IF
rec_sub.CURRENCY_CONVERSION_RATE IS NULL THEN
code_invalied_count :=
code_invalied_count + 1;
temp_error_message :=
'CURRENCY CONVERSION RATE is
not provided'
|| ' for '
|| rec_sub.REFERENCE4;
UPDATE GL_STAGE_TABLE spst
SET --
spst.validation_status = NVL (lc_validation_status, 'E'),
spst.error_desc =
spst.error_desc|| '...'||temp_error_message
WHERE spst.rowid =
rec_sub.rowid;
end if;
end if;
end if;
IF (OPEN_PERIOD_COUNT = 0)
THEN
code_invalied_count :=
code_invalied_count + 1;
temp_error_message :=
'Period Is Closed'
|| ' for '
|| rec_sub.REFERENCE4;
UPDATE GL_STAGE_TABLE spst
SET --
spst.validation_status = NVL (lc_validation_status, 'E'),
spst.error_desc =
spst.error_desc|| '...'||temp_error_message
WHERE spst.rowid =
rec_sub.rowid;
end if;
IF (code_combination_count =
0)
THEN
code_invalied_count :=
code_invalied_count + 1;
temp_error_message :=
'Account Code is invalied '
|| rec_sub.SEGMENT1
|| '.'
|| rec_sub.SEGMENT2
|| '.'
|| rec_sub.SEGMENT3
|| '.'
|| rec_sub.SEGMENT4
|| '.'
|| rec_sub.SEGMENT5
|| '.'
|| rec_sub.SEGMENT6
|| '.'
|| rec_sub.SEGMENT7
|| '.'
|| rec_sub.SEGMENT8
|| ' for '
|| rec_sub.REFERENCE4;
UPDATE GL_STAGE_TABLE spst
SET --
spst.validation_status = NVL (lc_validation_status, 'E'),
spst.error_desc =
spst.error_desc|| '...'||temp_error_message
WHERE spst.rowid =
rec_sub.rowid;
END IF;
END LOOP;
IF (code_invalied_count
>= 1)
THEN
UPDATE GL_STAGE_TABLE spst
SET spst.validation_status =
NVL (lc_validation_status, 'E')
-- spst.error_desc =
temp_error_message
WHERE SPST.REFERENCE4 =
rec.reference4;
ELSE
UPDATE GL_STAGE_TABLE spst
SET spst.validation_status =
NVL (lc_validation_status, 'S')
-- spst.error_desc =
lv_error_message
WHERE SPST.REFERENCE4 =
rec.reference4;
END IF;
gn_records_validated :=
gn_records_validated + 1;
END LOOP;
-- fnd_file.put_line
(fnd_file.LOG,
-- 'Total records validated
:' || gn_records_validated);
-- fnd_file.put_line
(fnd_file.output,
-- 'Total records validated
:' || gn_records_validated);
END validate_gl_records;
/* Moving data from GL
Staging Table to GL Interface Table */
PROCEDURE
load_into_gl_interface
IS
ln_result NUMBER;
lv_error_message VARCHAR2
(2000);
lc_return_status VARCHAR2
(30);
l_msg_count INTEGER;
l_msg_data VARCHAR2 (200);
l_msg_data_out VARCHAR2
(200);
l_count INTEGER;
l_return_status VARCHAR2
(20);
coun NUMBER := 0;
l_mesg VARCHAR2 (2000);
CONCATENATED_ID_TEMP NUMBER;
CURSOR lcu1
IS
SELECT *
FROM GL_STAGE_TABLE
WHERE validation_status =
'S';
BEGIN
lv_error_message := NULL;
FOR rec IN lcu1
LOOP
SELECT CODE_COMBINATION_ID
INTO CONCATENATED_ID_TEMP
FROM
GL_CODE_COMBINATIONS_KFV
WHERE CONCATENATED_SEGMENTS
=
rec.SEGMENT1
|| '.'
|| rec.SEGMENT2
|| '.'
|| rec.SEGMENT3
|| '.'
|| rec.SEGMENT4
|| '.'
|| rec.SEGMENT5
|| '.'
|| rec.SEGMENT6
|| '.'
|| rec.SEGMENT7
|| '.'
|| rec.SEGMENT8;
INSERT INTO gl_interface (
status,
ledger_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
reference4,
reference5
)
VALUES (
rec.status,
rec.ledger_id,
rec.accounting_date,
rec.currency_code,
rec.date_created,
rec.created_by,
rec.actual_flag,
rec.user_je_category_name,
rec.user_je_source_name,
concatenated_id_temp,
rec.entered_dr,
rec.entered_cr,
rec.reference4
|| ' '
|| TO_CHAR (SYSDATE,
'DD-MON-YY HH24:MI:SS'),
rec.reference5
);
gn_records_inserted :=
gn_records_inserted + 1;
END LOOP;
COMMIT;
fnd_file.put_line (
fnd_file.LOG,
'GL Records Successfully
Inserted: ' || gn_records_inserted
);
fnd_file.put_line
(fnd_file.LOG,
'GL Interface Error' ||
l_msg_data_out);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG, 'API has Failed');
END load_into_gl_interface;
/* Displaying The Message */
PROCEDURE display_log
IS
ln_error_records NUMBER;
ln_success_records NUMBER;
CURSOR c1
IS
SELECT *
FROM GL_STAGE_TABLE
WHERE request_id =
gn_request_id AND VALIDATION_STATUS = 'E';
CURSOR c2
IS
SELECT *
FROM GL_STAGE_TABLE
WHERE request_id =
gn_request_id AND VALIDATION_STATUS = 'S';
BEGIN
SELECT COUNT (1)
INTO ln_error_records
FROM GL_STAGE_TABLE
WHERE request_id =
gn_request_id AND VALIDATION_STATUS = 'E';
SELECT COUNT (1)
INTO ln_success_records
FROM GL_STAGE_TABLE
WHERE request_id =
gn_request_id AND VALIDATION_STATUS = 'S';
fnd_file.put_line
(fnd_file.LOG,
'Total Process records : '
|| ln_success_records);
fnd_file.put_line
(fnd_file.output,
'Total Process records : '
|| ln_success_records);
fnd_file.put_line
(fnd_file.LOG,
'Total Error records : ' ||
ln_error_records);
fnd_file.put_line
(fnd_file.output,
'Total Error records : ' ||
ln_error_records);
IF ln_error_records > 0
THEN
fnd_file.put_line
(fnd_file.LOG, 'Error record details.');
fnd_file.put_line
(fnd_file.output, 'Error record details.');
fnd_file.put_line (
fnd_file.LOG,
' -------------
-------------- ------------ ---------------------------------------'
);
fnd_file.put_line (
fnd_file.output,
' -------------
-------------- ------------ ---------------------------------------'
);
FOR err IN c1
LOOP
fnd_file.put_line(fnd_file.log,
' '|| err.ERROR_DESC||'..........');
fnd_file.put_line(fnd_file.output,
' '|| err.ERROR_DESC||'..........');
NULL;
END LOOP;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'Successfully created
following Gernal Entries');
fnd_file.put_line
(fnd_file.output,
'Successfully created
following Gernal Entries');
fnd_file.put_line
(fnd_file.LOG,
' -------------
-------------- ------------');
fnd_file.put_line
(fnd_file.output,
' -------------
-------------- ------------');
FOR poc IN c2
LOOP
fnd_file.put_line(fnd_file.log,
' '|| poc.USER_JE_SOURCE_NAME||'..........');
fnd_file.put_line(fnd_file.output,
' '|| poc.USER_JE_SOURCE_NAME||'..........');
NULL;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END display_log;
/* Final Procedure Calling
All Procedure */
PROCEDURE load_gl (errbuf
OUT VARCHAR2, retcode OUT NUMBER)
IS
BEGIN
gn_org_id := 82;
load_into_gl_stage_table;
validate_gl_records;
COMMIT;
load_into_gl_interface;
COMMIT;
display_log;
COMMIT;
--Removing one month old
records from staging table
--
DELETE FROM GL_STAGE_TABLE
WHERE TRUNC (process_date)
<= TRUNC (ADD_MONTHS (SYSDATE, -1))
AND process_flag = 'P';
--
--Deleting the errored out
record
--
/*****
delete
from src_po_staging_table
where process_flag = 'E';
*******/
END load_gl;
END INSIS_GL_INTERFACE;
/
CURRENCY RATE
CREATE OR REPLACE FUNCTION
"CURR_RATE" (x_from_curr varchar2,x_to_curr varchar2,x_conv_date
date) return
number IS
x_rate number;
x_max_date date;
BEGIN
if
x_from_curr<>x_to_curr then
select max(conversion_rate)
into x_rate from gl_daily_rates
where
from_currency=x_from_curr and to_currency=x_to_curr
and
conversion_date=to_date(to_char(x_conv_date,'DD-MON-YYYY'),'DD-MON-YYYY');
if x_rate is null then
select max(conversion_date)
into x_max_date from gl_daily_rates
where
from_currency=x_from_curr and to_currency=x_to_curr;
select max(conversion_rate)
into x_rate from gl_daily_rates
where
from_currency=x_from_curr and to_currency=x_to_curr
and conversion_date=
to_date(to_char(x_max_date,'DD-MON-YYYY'),'DD-MON-YYYY');
end if;
end if;
return(nvl(x_rate,1));
END CURR_RATE;
/
SUPPLIER AND SUPPLIER SITES
INTERFACES
************************************************
RDBMS : 9.2.0.8.0
Oracle Applications :
11.5.10.2
Interface Tables :-
a) ap_suppliers_int
b) ap_supplier_sites_int
Import Program :-
1) Supplier Open Interface
Import
2) Supplier Sites Open
Interface Import
Steps
1) Preapare the data
template :-
LEGACY_SUPP_CODE,
VENDOR_NAME,VENDOR_TYPE,
PAYMENT_TERMS,INVOICE_CURRENCY,PAYMENT_CURRENCY,
ACCTS_PAY_CODE_COMBINATION,PREPAY_CODE_COMBINATION,
VENDOR_SITE_CODE,ADDRESS1,ADDRESS2
,ADDRESS3 ,ADDRESS4 ,
CITY,STATE
,COUNTRY,ZIP,CONTACTS_NAME,PHONE ,EMAIL ,
SHIP_TO_LOC,BILL_TO_LOC
Here all are not mandatory
fields.
Pls customize the template
according to your requriements.
2) Now create the staging
table :-
CREATE TABLE
XXX_AP_SUPPLIERS_STG
(LEGACY_SUPP_CODE
NUMBER(10),
VENDOR_NAME VARCHAR2(100),
VENDOR_TYPE VARCHAR2(30),
PAYMENT_TERMS VARCHAR2(100),
INVOICE_CURRENCY
VARCHAR2(10),
PAYMENT_CURRENCY
VARCHAR2(10),
ACCTS_PAY_CODE_COMBINATION
VARCHAR2(50),
PREPAY_CODE_COMBINATION
VARCHAR2(50),
VENDOR_SITE_CODE
VARCHAR2(50),
ADDRESS1 VARCHAR2(100),
ADDRESS2 VARCHAR2(100),
ADDRESS3 VARCHAR2(100),
ADDRESS4 VARCHAR2(100),
CITY VARCHAR2(50),
STATE VARCHAR2(50),
COUNTRY VARCHAR2(30),
ZIP VARCHAR2(10),
CONTACTS_NAME VARCHAR2(30),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
SHIP_TO_LOC VARCHAR2(100),
BILL_TO_LOC VARCHAR2(100),
PAN_NO VARCHAR2(30),
VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE
VARCHAR2(2500))
3) Import data from data
template ie. excel file to staging table
You can import data through
different ways.
a) By using control file and
sqlloader
b) Through Toad.(Version 8.5
and above)
4) Execute the following
Procedure in Toad
->Open the procedure
editior
-> Paste the following
procedure
->Make changes according
to your requirement
-> Press Ctl+Enter.
Now the procdure created.
-> Go to schema browser
-> Procedure
->
XXX_create_supplier_api
->right button
-> execute procedure.
The below procedure will do
the basic validation required
and upload data into
interface tables. The importance of this script
is you will get your
customized error message in stagingtable and
you can easily identify and
rectify the errors.
CREATE OR REPLACE PROCEDURE
xxx_create_supplier_api
AS
l_vendor_type varchar2(30);
l_verify_flag varchar(1);
l_error_message
varchar2(2500);
l_invoice_currency
varchar2(10);
l_payment_currency
varchar2(10);
l_term_id number(10);
l_pay_code_combination_id
number(10);
l_prepay_code_combination_id
number(10);
l_org_id number(10);
l_territory_code
varchar2(10);
l_cnt number(3);
l_location_id number(10);
l_vendor_name varchar2(150);l
_vendor_site_code
varchar2(100);
CURSOR c_supp IS
SELECT distinct vendor_type
,payment_terms
,vendor_name
FROM xxx_ap_suppliers_stg
where nvl(verify_flag,'N') =
'N';
CURSOR c_supp_site
(p_supp_name varchar2) IS
SELECT *
FROM xxx_ap_suppliers_stg
WHERE vendor_name =
p_supp_name;
BEGIN
FOR H1 IN c_supp
LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL ;
l_cnt := 0 ;
l_vendor_name := NULL;
BEGIN
select count(*)
into l_cnt
from po_vendors
where
trim(upper(vendor_name)) = trim(upper(H1.vendor_name));
IF l_cnt > 0 then
l_verify_flag := 'N';
l_error_message:= 'Vendor is
already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating
Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message'Operating Unit is Invalid';
END;
BEGIN
select location_id
into l_location_id
from hr_locations
where location_code = 'xxx
Main Store Location';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message'Location is Not Valid';
END;
BEGIN
SELECT lookup_code
INTO l_vendor_type
FROM po_lookup_codes
WHERE lookup_type(+) =
'VENDOR TYPE'
AND UPPER(lookup_code) =
UPPER(TRIM(H1.vendor_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message'Vendor Type Lookup Code not existing';
END;
BEGIN
select currency_code
into l_invoice_currency
from fnd_currencies
where currency_code =
trim(H1.invoice_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message'Invoice Currency Code is not Valid';
END;
BEGIN
select currency_codeinto
l_payment_currency
from fnd_currencies
where currency_code =
trim(H1.payment_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:=l_error_message'Payament
Currency Cocde is not valid';
END;
BEGIN
select term_id
into l_term_id
from ap_terms
where upper(name) =
upper(trim(H1.payment_terms)) ;
EXCEPTION
when others then
l_verify_flag := 'N';
l_error_message := l_error_message'Payment
Term is not valid';
END;
BEGIN
select code_combination_id
into
l_pay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'
egment3'.'segment4'.'
segment5'.'segment6=
H1.accts_pay_code_combination ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message
'Accounts Pay
CodeCombination is Not Valid';
END;
BEGIN
select code_combination_id
into
l_prepay_code_combination_id
from gl_code_combinations
where
segment1'.'segment2'.'segment3'.'
segment4'.'segment5'.'
segment6=
H1.prepay_code_combination;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message'Pre-Pay Code Combination is Not Valid';
END;
IF H1.vendor_name is null
then
l_verify_flag := 'N';
l_error_message :=
l_error_message'Vendor Name is not existing';
end if;
savepoint A;
IF l_verify_flag <>
'N' THEN
BEGIN
INSERT INTO
ap.ap_suppliers_int
(
vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
invoice_currency_code,
payment_currency_code,
terms_id,
accts_pay_code_combination_id,
prepay_code_combination_id,
bill_to_location_id,
ship_to_location_id,
receiving_routing_id,
inspection_required_flag,
receipt_required_flag
)
VALUES
(
ap_suppliers_int_s.nextval,
trim(H1.vendor_name),
trim(H1.vendor_name),
l_vendor_type,
l_invoice_currency,
l_payment_currency,
l_term_id,
l_pay_code_combination_id,
l_prepay_code_combination_id,
l_location_id,
l_location_id,1,'N','Y'
) ;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'Y'
WHERE vendor_name =
H1.vendor_name;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N',
error_message =
l_error_message
WHERE vendor_name =
H1.vendor_name;
GOTO next_supp;
END;
FOR L1 IN
c_supp_site(H1.vendor_name)
LOOP
l_vendor_site_code := NULL;
BEGIN
select territory_code
into l_territory_code
from fnd_territories
where nls_territory =
trim(L1.country) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message
'Invalid Country';
NULL;
END;
BEGIN
select vendor_site_code
into l_vendor_site_code
from po_vendor_sites_all a,
po_vendors b
where org_id = l_org_id
and upper(vendor_site_code)
= trim(upper(L1.vendor_site_code))
and a.vendor_id =
b.vendor_id
and upper(b.vendor_name) =
trim(upper(L1.vendor_name)) ;
IF l_vendor_site_code is not
null then
l_verify_flag := 'N';
l_error_message :=
l_error_message 'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF l_verify_flag <> 'N'
then
BEGIN
INSERT INTO
ap.ap_supplier_sites_int
(
vendor_interface_id,
vendor_site_code,
vendor_site_code_alt,
address_line1,
address_line2,
address_line3,
city,
state,
country,
zip,
phone,
accts_pay_code_combination_id
,
prepay_code_combination_id ,
org_id,
attribute_category,
attribute10,
ship_to_location_id,
bill_to_location_id,
hold_unmatched_invoices_flag
)
VALUES
(
ap_suppliers_int_s.CURRVAL,
trim(L1.vendor_site_code),
trim(L1.vendor_site_code),
trim(L1.address1),
trim(L1.address2),
trim(L1.address3),
trim(L1.city),
trim(L1.state),
l_territory_code,
trim(L1.zip),
trim(L1.phone),
l_pay_code_combination_id,
l_prepay_code_combination_id
,
l_org_id,
Legacy Supplier Code',
trim(L1.Baan_Supp_code),
l_location_id,
l_location_id,
'N'
) ;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'Y'
WHERE vendor_name =
L1.vendor_name
AND vendor_site_code =
L1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
rollback to savepoint A;
l_error_message := SQLERRM;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message =
l_error_message
WHERE vendor_name =
L1.vendor_name
AND vendor_site_code =
L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name =
L1.vendor_name;
GOTO next_supp;
END;
ELSE
rollback to savepoint A;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message =
l_error_message
WHERE vendor_name =
L1.vendor_name
AND vendor_site_code =
L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name =
L1.vendor_name;
GOTO next_supp;
END IF;
END LOOP ;
ELSE
UPDATE xxx_ap_suppliers_stg
SET error_message =
l_error_message,
verify_flag = 'N'
WHERE vendor_name =
H1.vendor_name;
END IF;
<<>>
Commit;
END LOOP ;
END xxx_create_supplier_api;
Go to payable responsibility
and run the following concurrent programs
1) Supplier Open Interface
Import
2) Sites Open Interface
Import
After completing the first
concurrent pgm, run the second one.
And you can see the imported
no of records and rejected no of records in View output.
TO MIGRATE ITEM ON HAND
QUANTITY , LOT and SERIAL NO
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Interface Tables are :-
------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface
How to run the Import Pgm :
-------------------------------
Inventory Super User ->
Oracle Inventory ->
Setup ->
Transactions ->
Interface Manager ->
SubInventory ->
Material Transaction ->
Tool from Menu ->
Launch Manager ->
Process Transaction
Interface
-----------------------------------
If error is occurring go to
Transactions -> Open Transaction Interface -> Error -> See error
After correcting the error
apply the below code
/* update
mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3);
*/
----------------------------------------------------------------
The script to migrate legacy
data into interface tables
-----------------------------------------------------
CREATE OR REPLACE procedure
xxx_onhand_qty_update
as
x number:=null;
l_err_msg varchar2(2000);
l_row_status varchar2(3);
l_inventory_item_id number:=null;
l_organization_id number;
l_count number:=null;
l_inventory_location_id
number := null;
cursor c_onhand is
select *
from xxx_tab_onhand_stg
where nvl(row_status,'N') =
'N'
order by from_serial;
begin
for c_onhand_rec in c_onhand
loop
l_row_status := 'Y';
begin
select organization_id
into l_organization_id
from
org_organization_definitions
where organization_code =
c_onhand_rec.sub_inventory_code;
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No
Organization.'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
end;
select count(*) into l_count
from mtl_system_items_b
where upper(segment1)
=upper(c_onhand_rec.item_name)
and organization_id
=l_organization_id
and inventory_item_status_code='Active';
if l_count=0 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No
item existing in Given organization.'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count>1 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Multiple
items existing Given organization.'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count=1 then
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where
upper(segment1)=upper(c_onhand_rec.item_name)
and
organization_id=l_organization_id
and
inventory_item_status_code='Active';
if l_inventory_item_id is
not null then
update xxx_tab_onhand_stg
set transaction_uom=(select
distinct primary_uom_code
from mtl_system_items_b
where
upper(segment1)=upper(c_onhand_rec.item_name))
,inventory_item_id=(select
distinct(inventory_item_id)
from mtl_system_items_b
where
upper(segment1)=upper(c_onhand_rec.item_name)
and
organization_id=l_organization_id)
,dist_account=(select
code_combination_id
from gl_code_combinations
where segment4 = 1327108)
,userid= (select user_id
from fnd_user
where user_name = 'XXXX')
,transaction_interface_id=mtl_material_transactions_s.nextval
,transaction_type_id =
(select transaction_type_id
from mtl_transaction_types
where transaction_type_name
= 'Miscellaneous receipt')
,row_status='Y'
,organization_id = (select
organization_id
from
org_organization_definitions
where organization_code =
c_onhand_rec.sub_inventory_code)
where item_name =
c_onhand_rec.item_name;
commit;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Inventory
Item Id is not Existing'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Item
Name or Organization ID is not Valid'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
commit;
begin
select inventory_location_id
into l_inventory_location_id
from mtl_item_locations
where segment1 =
c_onhand_rec.locator
and subinventory_code =
trim(c_onhand_rec.sub_inventory_code);
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Invalid
Locator'
where item_name =
c_onhand_rec.item_name;
l_row_status := 'N';
end;
if l_row_status = 'Y' then
--On hand quantity Migration
begin
insert into
mtl_transactions_interface
(
transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,lock_flag
,flow_schedule
,scheduled_flag
,transaction_header_id
,inventory_item_id
,transaction_interface_id
,subinventory_code
,distribution_account_id
,transaction_cost
,locator_id
,transaction_reference
)
select
transaction_type_id
,transaction_uom
,'30-DEC-2007'
,organization_id
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,3
,1
,transaction_interface_id
,transaction_interface_id
,'inv'
,2
,'Y'
,2
,transaction_interface_id
,inventory_item_id
,transaction_interface_id
,sub_inventory_code
,dist_account
,transaction_cost
, l_inventory_location_id
,'Migration'
from xxx_tab_onhand_stg
where
upper(item_name)=upper(c_onhand_rec.item_name)
and
organization_id=l_organization_id;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message =
'Exception While Inserting to mtl_transactions_interface : '||l_err_msg
,row_status = 'N'
where item_name =
c_onhand_rec.item_name;
end;
---- END
-----ON HAND QUANTITY LOTS
MIGRATION
begin
insert into
mtl_transaction_lots_interface
(
transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_code
,last_update_login
,product_transaction_id
,primary_quantity
,lot_expiration_date)
select
transaction_interface_id
,lot_number
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,'inv'
,userid
,transaction_interface_id
,transaction_quantity
, expiry_date
from xxx_tab_onhand_stg
where item_name =
c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message =
'Exception While Inserting to mtl_transaction_lots_interface : '||l_err_msg
,row_status = 'N'
where item_name =
c_onhand_rec.item_name;
end;
--- END
---- SERIAL NUMBER MIGRATION
-----------
begin
insert into
mtl_serial_numbers_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
select
transaction_interface_id,
sysdate,
userid,
sysdate,
userid,
userid,
from_serial,
to_serial,
'inv',
transaction_interface_id
from xxx_tab_onhand_stg
where item_name =
c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message =
'Exception While Inserting to MTL_SERIAL_NUMBERS_INTERFACE : '||l_err_msg
,row_status = 'N'
where item_name =
c_onhand_rec.item_name;
end;
end if;
end loop;
END xxx_onhand_qty_update;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment