Monday 1 April 2013

SQL TO FIND MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH


SELECT POV.VENDOR_NAME
, AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
, AIA.INVOICE_NUM
, AIA.INVOICE_DATE
, AID.ACCOUNTING_DATE AS GL_DATE
, AIA.INVOICE_CURRENCY_CODE
, PHA.SEGMENT1 AS PO_NUMBER
, RSH.RECEIPT_NUM
, NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
, GCC.CONCATENATED_SEGMENTS
, AID.AMOUNT AS ENTERED_AMOUNT
, AID.BASE_AMOUNT AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AIA
, PO_VENDORS POV
, GL_CODE_COMBINATIONS_KFV GCC
, PO_DISTRIBUTIONS_ALL PDA
, PO_HEADERS_ALL PHA
, (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
, RCV_SHIPMENT_HEADERS RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND AID.PO_DISTRIBUTION_ID IS NULL
AND AID.BASE_AMOUNT IS NOT NULL
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = POV.VENDOR_ID
AND AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID (+)
AND AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
, AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
, AIA.INVOICE_NUM
, AIA.INVOICE_DATE
, AID.ACCOUNTING_DATE AS GL_DATE
, AIA.INVOICE_CURRENCY_CODE
, PHA.SEGMENT1 AS PO_NUMBER
, RSH.RECEIPT_NUM
, NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
, GCC.CONCATENATED_SEGMENTS
, AID.AMOUNT AS ENTERED_AMOUNT
, AID.AMOUNT AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AIA
, PO_VENDORS POV
, GL_CODE_COMBINATIONS_KFV GCC
, PO_DISTRIBUTIONS_ALL PDA
, PO_HEADERS_ALL PHA
, (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
, RCV_SHIPMENT_HEADERS RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND AID.PO_DISTRIBUTION_ID IS NULL
AND AID.BASE_AMOUNT IS NULL
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = POV.VENDOR_ID
AND AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID (+)
AND AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE

No comments:

Post a Comment