Blancket Purchase Order(Invoice) Single Insert In Oracle 11i

SELECT * FROM fnd_user WHERE user_id = 1012240;

SELECT table_name FROM all_tables WHERE table_name LIKE 'AP%REJEC%';

SELECT * FROM po_vendor_sites_all WHERE vendor_site_id = 4599;

SELECT * FROM po_vendors WHERE vendor_id = 1925;

SELECT * FROM ap_terms WHERE name LIKE 'Immediate';

DESC ap_invoice_lines_interface;

SELECT * FROM gl_code_combinations_kfv WHERE concatenated_segments = '01-495-7420-000';

SELECT * FROM ap_invoices_interface WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT * FROM ap_invoice_lines_interface WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT * FROM ap_invoices_all

WHERE TRUNC (creation_date) = TRUNC (SYSDATE) AND invoice_id = '987654'

AND TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT * FROM ap_invoice_distributions_all WHERE invoice_id = '63593' AND TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT * FROM ap_invoice_lines_all WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT * FROM AP_INTERFACE_REJECTIONS WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

DELETE FROM ap_invoices_interface WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

DELETE FROM ap_invoice_lines_interface WHERE TRUNC (creation_date) = TRUNC (SYSDATE) AND invoice_id = 356789;

COMMIT;

======> Inserting into Headers <===============

INSERT INTO ap_invoices_interface (invoice_id,

invoice_num,

invoice_type_lookup_code,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

source,

GROUP_ID,

creation_date,

created_by,

PO_NUMBER,

VENDOR_NAME,

VENDOR_SITE_CODE,

DESCRIPTION,

AWT_GROUP_ID,

AWT_GROUP_NAME,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

PAYMENT_CROSS_RATE_TYPE,

PAYMENT_CROSS_RATE,

WORKFLOW_FLAG,

DOC_CATEGORY_CODE,

VOUCHER_NUM,

PAYMENT_METHOD_LOOKUP_CODE,

PAY_GROUP_LOOKUP_CODE,

GOODS_RECEIVED_DATE,

INVOICE_RECEIVED_DATE,

ACCTS_PAY_CODE_COMBINATION_ID,

USSGL_TRANSACTION_CODE,

EXCLUSIVE_PAYMENT_FLAG,

AMOUNT_APPLICABLE_TO_DISCOUNT,

PREPAY_NUM,

PREPAY_DIST_NUM,

PREPAY_APPLY_AMOUNT,

PREPAY_GL_DATE,

INVOICE_INCLUDES_PREPAY_FLAG,

NO_XRATE_BASE_AMOUNT,

VENDOR_EMAIL_ADDRESS,

TERMS_DATE,

REQUESTER_ID,

SHIP_TO_LOCATION,

EXTERNAL_DOC_REF)

VALUES (987654,

'INV0ICE-3',

'STANDARD',

1925,

4599,

1000,

'USD',

'Intercompany',

'testcase1',

SYSDATE,

1007906,

NULL,

'AA supplier',

'AA SITE',

'Advertising',

NULL,

NULL,

SYSDATE,

1007906,

NULL,

1,

NULL,

NULL,

NULL,

'CHECK',

'Standard',

NULL,

NULL,

12854,

NULL,

'N',

200,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

SYSDATE,

NULL,

NULL,

NULL);

COMMIT;

==========> Inserting Into Lines <============

INSERT INTO ap_invoice_lines_interface (invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

dist_code_combination_id,

LINE_GROUP_NUMBER,

AMOUNT,

ACCOUNTING_DATE,

DESCRIPTION,

AMOUNT_INCLUDES_TAX_FLAG,

PRORATE_ACROSS_FLAG,

FINAL_MATCH_FLAG,

PO_HEADER_ID,

PO_NUMBER,

PO_LINE_ID,

PO_LINE_NUMBER,

PO_LINE_LOCATION_ID,

PO_SHIPMENT_NUM,

PO_DISTRIBUTION_ID,

PO_DISTRIBUTION_NUM,

PO_UNIT_OF_MEASURE,

INVENTORY_ITEM_ID,

ITEM_DESCRIPTION,

QUANTITY_INVOICED,

SHIP_TO_LOCATION_CODE,

UNIT_PRICE,

DISTRIBUTION_SET_ID,

DISTRIBUTION_SET_NAME,

DIST_CODE_CONCATENATED,

PROJECT_ID,

TASK_ID,

TAX_CODE_ID,

TAXABLE_FLAG,

CREATION_DATE,

CREATED_BY,

expenditure_type,

expenditure_item_date,

expenditure_organization_id)

VALUES (987654,

76543, --AP_INVOICE_LINES_INTERFACE_S.nextval,

1,

'ITEM',

12998,

NULL,

200,

SYSDATE,

'advertising',

'N',

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

2780,

3690,

NULL,

'N',

SYSDATE,

1007906,

'Architects Fees',

SYSDATE,

204);

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (item_description,

quantity,

unit_price,

authorization_status,

unit_of_measure,

destination_organization_id,

last_updated_by,

last_update_date,

requisition_header_id,

source_type_code,

requisition_line_id,

need_by_date,

org_id,

amount,

UOM_CODE,

document_type_code,

interface_source_code,

destination_type_code,

CHARGE_ACCOUNT_ID,

PREPARER_ID,

CATEGORY_ID,

DELIVER_TO_REQUESTOR_ID,

DELIVER_TO_LOCATION_ID,

item_id,

batch_id,

line_type_id,

currency_code,

creation_date,

requisition_type,

suggested_buyer_id,

suggested_vendor_id,

autosource_doc_header_id,

autosource_doc_LINE_NUM,

REQ_NUMBER_SEGMENT1,

REQ_DIST_SEQUENCE_ID)

VALUES ('Final Chip Assembly',

1,

100,

'APPROVED', --authorization_status,

'Each',

207, -- destination_organization_id,

1007939, ---- last_updated_by,

SYSDATE, -- last_update_date,

56977, -- requisition_header_id,

'VENDOR', -- source_type_code,

60985, -- requisition_line_id,

SYSDATE + 1, -- need_by_date,

204, -- org_id,

NULL, --amount,

'Ea', -- UOM_CODE,

'RELEASE', -- document_type_code,

'FUTURE', -- interface_source_code,

'EXPENSE', -- destination_type_code,

13528, --CHARGE_ACCOUNT_ID,

13696, --- PREPARER_ID,-----------------------------------------

1, -- CATEGORY_ID,

13696, -- DELIVER_TO_REQUESTOR_ID,

207, --DELIVER_TO_LOCATION_ID,

4113, -- item_id,

555, -- batch_id,

1, -- line_type_id,

'USD', -- currency_code,

SYSDATE, -- creation_date,

'PURCHASE', -- requisition_type,

13696, -- suggested_buyer_id,

3921, -- suggested_vendor_id,

33897, -- autosource_doc_header_id,

1, -- autosource_doc_LINE_NUM,

'99988', -- REQ_NUMBER_SEGMENT1,

1 --REQ_DIST_SEQUENCE_ID

);

COMMIT;

SELECT * FROM PO_HEADERS_ALL WHERE 1 = 1 AND PO_HEADER_ID = 33154;

SELECT * FROM PO_LINES_ALL WHERE 1 = 1 AND PO_HEADER_ID = 33154;
SELECT * FROM PO_HEADERS_ALL WHERE 1 = 1 AND PO_HEADER_ID = 33154;
SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE 1 = 1 AND REQUISITION_HEADER_ID = 56976;
SELECT * FROM PO_REQUISITION_LINES_ALL WHERE 1 = 1 AND REQUISITION_HEADER_ID = 56976;
SELECT * FROM PO_REQUISITIONS_INTERFACE_ALL WHERE 1 = 1 AND PROCESS_FLAG NOT IN ('ERROR', 'FUTURE') ORDER BY CREATION_DATE DESC;

SELECT * FROM MTL_SYSTEM_ITEMS_B;
SELECT * FROM PO_RELEASES_ALL;