08/04/2013 Monday
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;
/
If this notes is useful for any one please submit your comments
thank you
-----------------------
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;
/
If this notes is useful for any one please submit your comments
thank you
DILLI
No comments:
Post a Comment