Following scripts used to test Payable open Interface for Invoices contains Project Data:
Query for to get PO details:
--select * from po_headers_all where PO_HEADER_ID=63845
--AND segment1='NEX060378-002'
--select * from po_lines_all where po_header_id=63845
--select * from po_vendors where vendor_id=11813
--select * from po_line_locations_all where po_header_id=63845
--select * from PO_DISTRIBUTIONS_ALL
--select * from ap_invoice_lines_interface
--commit
Query to Set Org_id:
--begin
--dbms_application_info.set_client_info('169');
--end;
--select * from ap_invoices_interface where invoice_num like 'COL%'
--select * from ap_invoice_lines_interface
-- select * from AP_TERMS
--select * from PO_DISTRIBUTIONS_ALL
DECLARE
p_invoice_id NUMBER;
i NUMBER;
BEGIN
Query to Generate invoice_id --
select AP_INVOICES_INTERFACE_S.nextval
into p_invoice_id
from dual;
Query to Insert an invoice header --
insert into ap_invoices_interface(INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
PO_NUMBER,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
--TERMS_ID,
SOURCE,
GROUP_ID,
PAYMENT_METHOD_LOOKUP_CODE,
ORG_ID)
VALUES (p_invoice_id, --INVOICE_ID,
'COL012', --INVOICE_NUM,
'STANDARD', --INVOICE_TYPE_LOOKUP_CODE,
sysdate, --INVOICE_DATE,
'NEX060378-002', --v_PO_NUMBER,
11934, --v_VENDOR_ID, 11790
14667, --v_VENDOR_SITE_ID, 14506
10, --v_INVOICE_AMOUNT, (Sanjiv Wrong Amt)10
'USD', -- v_INVOICE_CURRENCY_CODE, usd
--10011, --v_TERMS_ID, (Sanjiv)
'CONVERSION',--'INVOICE GATEWAY', --v_SOURCE,
'USM', -- v_GROUP_ID,
'', -- v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
169); --v_ORG_ID
-- Insert invoice line --
for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
ACCOUNTING_DATE,
FINAL_MATCH_FLAG,
PO_HEADER_ID,
PO_NUMBER,
PO_LINE_ID,
PO_LINE_NUMBER,
PO_LINE_LOCATION_ID,
PO_SHIPMENT_NUM,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
QUANTITY_INVOICED,
SHIP_TO_LOCATION_CODE,
UNIT_PRICE,
PO_RELEASE_ID,
RELEASE_NUM,
PRICE_CORRECTION_FLAG,
RECEIPT_NUMBER,
MATCH_OPTION,
RCV_TRANSACTION_ID,
DIST_CODE_COMBINATION_ID,
PO_DISTRIBUTION_ID,
PO_DISTRIBUTION_NUM,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PA_QUANTITY,
EXPENDITURE_ITEM_DATE
)
VALUES (
p_invoice_id, --v_INVOICE_ID,
AP_INVOICE_LINES_INTERFACE_S.nextval, --v_INVOICE_LINE_ID,
i, --v_LINE_NUMBER, 1
'ITEM', -- v_LINE_TYPE_LOOKUP_CODE,
10, --v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
sysdate, -- v_ACCOUNTING_DATE,
'', --v_FINAL_MATCH_FLAG,
63845, --v_PO_HEADER_ID,
'NEX060378-002', -- v_PO_NUMBER,
63846, --v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
1, --v_PO_LINE_NUMBER,
63846, --v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
'', --v_PO_SHIPMENT_NUM,
'', --v_INVENTORY_ITEM_ID,
'', --v_ITEM_DESCRIPTION,
1, --v_QUANTITY_INVOICED,
'', --v_SHIP_TO_LOCATION_CODE,
'', --v_UNIT_PRICE,
'', --v_PO_RELEASE_ID,
'', --v_RELEASE_NUM,
'', --v_PRICE_CORRECTION_FLAG,
'', --v_RECEIPT_NUMBER,
'', --v_MATCH_OPTION,
'', --v_RCV_TRANSACTION_ID
'', --v_DIST_CODE_COMBINATION_ID
64302, --PO_DISTRIBUTION_ID = 64302
1, --PO_DISTRIBUTION_NUM
'',--1087, --PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
'',--1551, --TASK_ID,
'',--'Awrd Ovation Point Redemption',--EXPENDITURE_TYPE,
'',--169, --EXPENDITURE_ORGANIZATION_ID,
'',--'', --PA_QUANTITY, (Sanjiv Wrong Qty)10
'' );-- sysdate ); --EXPENDITURE_ITEM_DATE
end loop;
commit;
END;
Queries to debugged and trace Interface errors:
--select * from ap_invoices_interface where invoice_id=13634
--select * from ap_invoice_lines_interface where invoice_id=13634
--select * from user_errors where name like'USM%';
-- select * from pa_projects_all where project_id=1172 --1087
--select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number='2.4')
--10437
--select * from usm_po_codes
--select * from pa_projects_all where segment1 ='10437'
No comments:
Post a Comment