----============ PO TO INVOICE, PAYMENT QUERY ========================---
SELECT 'PHA', PHA.PO_HEADER_ID,PHA.SEGMENT1 PO_NUM, PHA.VENDOR_ID, PHA.VENDOR_SITE_ID,PHA.SHIP_TO_LOCATION_ID,PHA.BILL_TO_LOCATION_ID, PHA.CURRENCY_CODE,PHA.AUTHORIZATION_STATUS,
'PLA', PLA.PO_LINE_ID, PLA.ITEM_ID, PLA.ITEM_DESCRIPTION, PLA.UNIT_MEAS_LOOKUP_CODE,PLA.UNIT_PRICE, PLA.QUANTITY,PLA.ORG_ID,
'PDA', PDA.PO_DISTRIBUTION_ID, PDA.SET_OF_BOOKS_ID, PDA.CODE_COMBINATION_ID, PDA.QUANTITY_BILLED, PDA.QUANTITY_CANCELLED, PDA.QUANTITY_DELIVERED, PDA.QUANTITY_ORDERED, PDA.AMOUNT_BILLED, PDA.DESTINATION_ORGANIZATION_ID,
'PLLA',PLLA.LINE_LOCATION_ID, PLLA.QUANTITY, PLLA.QUANTITY_ACCEPTED, PLLA.QUANTITY_BILLED, PLLA.QUANTITY_CANCELLED, PLLA.QUANTITY_REJECTED, PLLA.QUANTITY_SHIPPED, PLLA.QTY_RCV_TOLERANCE, PLLA.SHIP_TO_LOCATION_ID, PLLA.SHIP_TO_ORGANIZATION_ID,
'APS', APS.VENDOR_ID, APS.VENDOR_NAME,APS.VENDOR_TYPE_LOOKUP_CODE,APS.PARTY_ID, APS.TCA_SYNC_VENDOR_NAME,
'APSS',APSS.VENDOR_ID, APSS.VENDOR_SITE_ID, APSS.VENDOR_SITE_CODE, APSS.ADDRESS_LINE1, APSS.ADDRESS_LINE2, APSS.ADDRESS_LINE3, APSS.ADDRESS_LINE4, APSS.CITY, APSS.STATE, APSS.COUNTRY, APSS.LOCATION_ID, APSS.PARTY_SITE_ID,
'RSH', RSH.SHIPMENT_HEADER_ID, RSH.VENDOR_ID,RSH.RECEIPT_NUM, RSH.SHIP_TO_LOCATION_ID, RSH.SHIP_FROM_LOCATION_ID,RSH.SHIP_TO_ORG_ID,
'RSL', RSL.SHIPMENT_LINE_ID,RSL.SHIPMENT_HEADER_ID, RSL.QUANTITY_SHIPPED, RSL.QUANTITY_RECEIVED, RSL.ITEM_ID, RSL.ITEM_DESCRIPTION,RSL.SHIP_TO_LOCATION_ID,
'RT', RT.TRANSACTION_DATE, RT.TRANSACTION_ID, RT.TRANSACTION_TYPE, RT.QUANTITY, RT.QUANTITY_BILLED, RT.UNIT_OF_MEASURE, RT.PRIMARY_QUANTITY,RT.AMOUNT, RT.AMOUNT_BILLED,
'API', API.INVOICE_ID, API.INVOICE_NUM, API.INVOICE_AMOUNT, API.AMOUNT_PAID,API.INVOICE_DATE, API.GL_DATE, API.LEGAL_ENTITY_ID, API.PAYMENT_METHOD_CODE,
'APILA',APILA.INVENTORY_ITEM_ID, APILA.ITEM_DESCRIPTION,APILA.QUANTITY_INVOICED,
'APIDA', APIDA.ACCOUNTING_DATE, APIDA.ACCRUAL_POSTED_FLAG, APIDA.INVOICE_ID, APIDA.LINE_TYPE_LOOKUP_CODE, APIDA.RCV_TRANSACTION_ID, APIDA.ACCOUNTING_EVENT_ID,
'APIPA', APIPA.ACCOUNTING_EVENT_ID,APIPA.CHECK_ID,APIPA.INVOICE_PAYMENT_ID , APIPA.BANK_ACCOUNT_NUM,
'APCHK', APCHK.AMOUNT, APCHK.BANK_ACCOUNT_NAME, APCHK.CHECK_DATE, APCHK.CHECK_ID, APCHK.CHECK_NUMBER, APCHK.VENDOR_NAME, APCHK.PAYMENT_ID,
'XLATE', XLATE.*
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSS,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT,
AP_INVOICES_ALL API,
AP_INVOICE_LINES_ALL APILA,
AP_INVOICE_DISTRIBUTIONS_ALL APIDA,
AP_INVOICE_PAYMENTS_ALL APIPA,
AP_CHECKS_ALL APCHK,
XLA.XLA_TRANSACTION_ENTITIES XLATE ---xla_transaction_entities
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID= RSL.SHIPMENT_HEADER_ID
AND APILA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND APILA.PO_LINE_ID = PLA.PO_LINE_ID
AND APILA.INVOICE_ID = API.INVOICE_ID
AND APIDA.INVOICE_ID = APILA.INVOICE_ID
AND APIDA.INVOICE_LINE_NUMBER = APILA.LINE_NUMBER
AND APIPA.INVOICE_ID = API.INVOICE_ID
AND APCHK.CHECK_ID = APIPA.CHECK_ID
AND XLATE.SECURITY_ID_INT_1 = API.ORG_ID
AND XLATE.SOURCE_ID_INT_1 = APIPA.CHECK_ID
AND PHA.SEGMENT1 = 12130009--12130008
SELECT 'PHA', PHA.PO_HEADER_ID,PHA.SEGMENT1 PO_NUM, PHA.VENDOR_ID, PHA.VENDOR_SITE_ID,PHA.SHIP_TO_LOCATION_ID,PHA.BILL_TO_LOCATION_ID, PHA.CURRENCY_CODE,PHA.AUTHORIZATION_STATUS,
'PLA', PLA.PO_LINE_ID, PLA.ITEM_ID, PLA.ITEM_DESCRIPTION, PLA.UNIT_MEAS_LOOKUP_CODE,PLA.UNIT_PRICE, PLA.QUANTITY,PLA.ORG_ID,
'PDA', PDA.PO_DISTRIBUTION_ID, PDA.SET_OF_BOOKS_ID, PDA.CODE_COMBINATION_ID, PDA.QUANTITY_BILLED, PDA.QUANTITY_CANCELLED, PDA.QUANTITY_DELIVERED, PDA.QUANTITY_ORDERED, PDA.AMOUNT_BILLED, PDA.DESTINATION_ORGANIZATION_ID,
'PLLA',PLLA.LINE_LOCATION_ID, PLLA.QUANTITY, PLLA.QUANTITY_ACCEPTED, PLLA.QUANTITY_BILLED, PLLA.QUANTITY_CANCELLED, PLLA.QUANTITY_REJECTED, PLLA.QUANTITY_SHIPPED, PLLA.QTY_RCV_TOLERANCE, PLLA.SHIP_TO_LOCATION_ID, PLLA.SHIP_TO_ORGANIZATION_ID,
'APS', APS.VENDOR_ID, APS.VENDOR_NAME,APS.VENDOR_TYPE_LOOKUP_CODE,APS.PARTY_ID, APS.TCA_SYNC_VENDOR_NAME,
'APSS',APSS.VENDOR_ID, APSS.VENDOR_SITE_ID, APSS.VENDOR_SITE_CODE, APSS.ADDRESS_LINE1, APSS.ADDRESS_LINE2, APSS.ADDRESS_LINE3, APSS.ADDRESS_LINE4, APSS.CITY, APSS.STATE, APSS.COUNTRY, APSS.LOCATION_ID, APSS.PARTY_SITE_ID,
'RSH', RSH.SHIPMENT_HEADER_ID, RSH.VENDOR_ID,RSH.RECEIPT_NUM, RSH.SHIP_TO_LOCATION_ID, RSH.SHIP_FROM_LOCATION_ID,RSH.SHIP_TO_ORG_ID,
'RSL', RSL.SHIPMENT_LINE_ID,RSL.SHIPMENT_HEADER_ID, RSL.QUANTITY_SHIPPED, RSL.QUANTITY_RECEIVED, RSL.ITEM_ID, RSL.ITEM_DESCRIPTION,RSL.SHIP_TO_LOCATION_ID,
'RT', RT.TRANSACTION_DATE, RT.TRANSACTION_ID, RT.TRANSACTION_TYPE, RT.QUANTITY, RT.QUANTITY_BILLED, RT.UNIT_OF_MEASURE, RT.PRIMARY_QUANTITY,RT.AMOUNT, RT.AMOUNT_BILLED,
'API', API.INVOICE_ID, API.INVOICE_NUM, API.INVOICE_AMOUNT, API.AMOUNT_PAID,API.INVOICE_DATE, API.GL_DATE, API.LEGAL_ENTITY_ID, API.PAYMENT_METHOD_CODE,
'APILA',APILA.INVENTORY_ITEM_ID, APILA.ITEM_DESCRIPTION,APILA.QUANTITY_INVOICED,
'APIDA', APIDA.ACCOUNTING_DATE, APIDA.ACCRUAL_POSTED_FLAG, APIDA.INVOICE_ID, APIDA.LINE_TYPE_LOOKUP_CODE, APIDA.RCV_TRANSACTION_ID, APIDA.ACCOUNTING_EVENT_ID,
'APIPA', APIPA.ACCOUNTING_EVENT_ID,APIPA.CHECK_ID,APIPA.INVOICE_PAYMENT_ID , APIPA.BANK_ACCOUNT_NUM,
'APCHK', APCHK.AMOUNT, APCHK.BANK_ACCOUNT_NAME, APCHK.CHECK_DATE, APCHK.CHECK_ID, APCHK.CHECK_NUMBER, APCHK.VENDOR_NAME, APCHK.PAYMENT_ID,
'XLATE', XLATE.*
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSS,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT,
AP_INVOICES_ALL API,
AP_INVOICE_LINES_ALL APILA,
AP_INVOICE_DISTRIBUTIONS_ALL APIDA,
AP_INVOICE_PAYMENTS_ALL APIPA,
AP_CHECKS_ALL APCHK,
XLA.XLA_TRANSACTION_ENTITIES XLATE ---xla_transaction_entities
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID= RSL.SHIPMENT_HEADER_ID
AND APILA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND APILA.PO_LINE_ID = PLA.PO_LINE_ID
AND APILA.INVOICE_ID = API.INVOICE_ID
AND APIDA.INVOICE_ID = APILA.INVOICE_ID
AND APIDA.INVOICE_LINE_NUMBER = APILA.LINE_NUMBER
AND APIPA.INVOICE_ID = API.INVOICE_ID
AND APCHK.CHECK_ID = APIPA.CHECK_ID
AND XLATE.SECURITY_ID_INT_1 = API.ORG_ID
AND XLATE.SOURCE_ID_INT_1 = APIPA.CHECK_ID
AND PHA.SEGMENT1 = 12130009--12130008
No comments:
Post a Comment