VIEW: APPS.PSA_AP_AEL_GL_BC_INV_V

Source

View Text - Preformatted

SELECT p.je_header_id JE_HEADER_ID, p.je_line_num JE_LINE_NUM, 200 APPLICATION_ID , p.ledger_id SET_OF_BOOKS_ID , I.org_id ORG_ID , 'INV' TRX_CLASS, 'Invoices' TRX_CLASS_NAME , L1.displayed_field TRX_TYPE_NAME , I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C, I.invoice_num TRX_NUMBER_C , I.invoice_num TRX_NUMBER_DISPLAYED , I.description COMMENTS , -999 DOC_SEQUENCE_ID , 'None' DOC_SEQUENCE_NAME , -999 DOC_SEQUENCE_VALUE, p.ACCOUNTED_CR ACCOUNTED_CR, p.ACCOUNTED_DR ACCOUNTED_DR, d.accounting_date ACCOUNTING_DATE, NULL ACCT_LINE_TYPE, 'CHARGE' ACCT_LINE_TYPE_NAME, -1 AEH_ID, -1 AEL_ID, 'APID' AEL_TABLE, 'AP_INVOICES' AE_LINE_REFERENCE, p.code_combination_id CODE_COMBINATION_ID, p.currency_code CURRENCY_CODE, to_date(NULL) CURRENCY_CONVERSION_DATE, to_number(NULL) CURRENCY_CONVERSION_RATE, NULL CURRENCY_CONVERSION_TYPE, NULL CURRENCY_USER_CONVERSION_TYPE, p.entered_cr ENTERED_CR, p.entered_dr ENTERED_DR, 'N' GL_TRANSFER_STATUS, NULL GL_TRANSFER_STATUS_NAME, NULL TRANSFER_STATUS_DETAIL_NAME, I.invoice_id SOURCE_ID, 'AP_INVOICES' SOURCE_TABLE, NULL TAX_CODE, 'SUPPLIER' THIRD_PARTY_TYPE, -1 THIRD_PARTY_ID, V.segment1 THIRD_PARTY_NUMBER, V.vendor_name THIRD_PARTY_NAME, I.vendor_site_id THIRD_PARTY_SUB_ID, VS.vendor_site_code THIRD_PARTY_SUB_NAME, I.invoice_date TRX_DATE , I.invoice_id TRX_HDR_ID, 'API' TRX_HDR_TABLE, p.je_category_name JE_CATEGORY, jc.user_je_category_name USER_JE_CATEGORY_NAME, D.LAST_UPDATE_DATE LAST_UPDATE_DATE, D.LAST_UPDATED_BY LAST_UPDATED_BY, D.CREATION_DATE CREATION_DATE, D.CREATED_BY CREATED_BY, D.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, D.REQUEST_ID REQUEST_ID, D.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, D.PROGRAM_ID PROGRAM_ID, D.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, /*---------------------------------------------------------------*/ /* AP Invoice Specific columns */ /*---------------------------------------------------------------*/ D.distribution_line_number TRX_LINE_NUMBER, D.line_type_lookup_code TRX_LINE_TYPE, L2.displayed_field TRX_LINE_TYPE_NAME, -1 ACCOUNTING_EVENT_NUMBER, NULL ACCOUNTING_EVENT_TYPE, NULL ACCOUNTING_EVENT_TYPE_NAME, TO_NUMBER(NULL) ACCOUNTING_LINE_NUMBER, NULL AEH_ACCOUNTING_ERROR_CODE, NULL AEH_ACCOUNTING_ERROR_NAME, NULL AEL_ACCOUNTING_ERROR_CODE, NULL AEL_ACCOUNTING_ERROR_NAME, I.invoice_currency_code TRX_HDR_CURRENCY, PH.segment1 PO_ORDER_NUMBER, pr.release_num PO_ORDER_RELEASE_NUM, rsh.receipt_num RCV_RECEIPT_NUM FROM gl_bc_packets p, gl_je_categories jc, ap_invoice_distributions_all D, ap_invoices_all I, po_releases_all PR, po_headers_all PH, po_distributions_all PD, rcv_transactions rct, rcv_shipment_headers rsh, po_vendor_sites_all VS, po_vendors V, ap_lookup_codes L1, ap_lookup_codes L2 WHERE p.status_code = 'A' AND p.reference2 = to_char(D.invoice_id) AND p.reference3 = D.distribution_line_number AND D.line_type_lookup_code = L2.lookup_code(+) AND L2.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE' AND jc.je_category_name = p.je_category_name AND rsh.shipment_header_id (+) = rct.shipment_header_id AND D.rcv_transaction_id = rct.transaction_id (+) AND PR.po_release_id(+) = PD.po_release_id AND PH.po_header_id(+) = PD.po_header_id AND PD.po_distribution_id(+) = D.po_distribution_id AND D.invoice_id = I.invoice_id AND I.vendor_id = V.vendor_id AND VS.vendor_site_id = I.vendor_site_id AND VS.org_id = I.org_id AND L1.lookup_code = I.invoice_type_lookup_code AND L1.lookup_type = 'INVOICE TYPE'

View Text - HTML Formatted

SELECT P.JE_HEADER_ID JE_HEADER_ID
, P.JE_LINE_NUM JE_LINE_NUM
, 200 APPLICATION_ID
, P.LEDGER_ID SET_OF_BOOKS_ID
, I.ORG_ID ORG_ID
, 'INV' TRX_CLASS
, 'INVOICES' TRX_CLASS_NAME
, L1.DISPLAYED_FIELD TRX_TYPE_NAME
, I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C
, I.INVOICE_NUM TRX_NUMBER_C
, I.INVOICE_NUM TRX_NUMBER_DISPLAYED
, I.DESCRIPTION COMMENTS
, -999 DOC_SEQUENCE_ID
, 'NONE' DOC_SEQUENCE_NAME
, -999 DOC_SEQUENCE_VALUE
, P.ACCOUNTED_CR ACCOUNTED_CR
, P.ACCOUNTED_DR ACCOUNTED_DR
, D.ACCOUNTING_DATE ACCOUNTING_DATE
, NULL ACCT_LINE_TYPE
, 'CHARGE' ACCT_LINE_TYPE_NAME
, -1 AEH_ID
, -1 AEL_ID
, 'APID' AEL_TABLE
, 'AP_INVOICES' AE_LINE_REFERENCE
, P.CODE_COMBINATION_ID CODE_COMBINATION_ID
, P.CURRENCY_CODE CURRENCY_CODE
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE
, NULL CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_USER_CONVERSION_TYPE
, P.ENTERED_CR ENTERED_CR
, P.ENTERED_DR ENTERED_DR
, 'N' GL_TRANSFER_STATUS
, NULL GL_TRANSFER_STATUS_NAME
, NULL TRANSFER_STATUS_DETAIL_NAME
, I.INVOICE_ID SOURCE_ID
, 'AP_INVOICES' SOURCE_TABLE
, NULL TAX_CODE
, 'SUPPLIER' THIRD_PARTY_TYPE
, -1 THIRD_PARTY_ID
, V.SEGMENT1 THIRD_PARTY_NUMBER
, V.VENDOR_NAME THIRD_PARTY_NAME
, I.VENDOR_SITE_ID THIRD_PARTY_SUB_ID
, VS.VENDOR_SITE_CODE THIRD_PARTY_SUB_NAME
, I.INVOICE_DATE TRX_DATE
, I.INVOICE_ID TRX_HDR_ID
, 'API' TRX_HDR_TABLE
, P.JE_CATEGORY_NAME JE_CATEGORY
, JC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, D.LAST_UPDATE_DATE LAST_UPDATE_DATE
, D.LAST_UPDATED_BY LAST_UPDATED_BY
, D.CREATION_DATE CREATION_DATE
, D.CREATED_BY CREATED_BY
, D.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, D.REQUEST_ID REQUEST_ID
, D.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, D.PROGRAM_ID PROGRAM_ID
, D.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, /*---------------------------------------------------------------*/ /* AP INVOICE SPECIFIC COLUMNS */ /*---------------------------------------------------------------*/ D.DISTRIBUTION_LINE_NUMBER TRX_LINE_NUMBER
, D.LINE_TYPE_LOOKUP_CODE TRX_LINE_TYPE
, L2.DISPLAYED_FIELD TRX_LINE_TYPE_NAME
, -1 ACCOUNTING_EVENT_NUMBER
, NULL ACCOUNTING_EVENT_TYPE
, NULL ACCOUNTING_EVENT_TYPE_NAME
, TO_NUMBER(NULL) ACCOUNTING_LINE_NUMBER
, NULL AEH_ACCOUNTING_ERROR_CODE
, NULL AEH_ACCOUNTING_ERROR_NAME
, NULL AEL_ACCOUNTING_ERROR_CODE
, NULL AEL_ACCOUNTING_ERROR_NAME
, I.INVOICE_CURRENCY_CODE TRX_HDR_CURRENCY
, PH.SEGMENT1 PO_ORDER_NUMBER
, PR.RELEASE_NUM PO_ORDER_RELEASE_NUM
, RSH.RECEIPT_NUM RCV_RECEIPT_NUM
FROM GL_BC_PACKETS P
, GL_JE_CATEGORIES JC
, AP_INVOICE_DISTRIBUTIONS_ALL D
, AP_INVOICES_ALL I
, PO_RELEASES_ALL PR
, PO_HEADERS_ALL PH
, PO_DISTRIBUTIONS_ALL PD
, RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, AP_LOOKUP_CODES L1
, AP_LOOKUP_CODES L2
WHERE P.STATUS_CODE = 'A'
AND P.REFERENCE2 = TO_CHAR(D.INVOICE_ID)
AND P.REFERENCE3 = D.DISTRIBUTION_LINE_NUMBER
AND D.LINE_TYPE_LOOKUP_CODE = L2.LOOKUP_CODE(+)
AND L2.LOOKUP_TYPE(+) = 'INVOICE DISTRIBUTION TYPE'
AND JC.JE_CATEGORY_NAME = P.JE_CATEGORY_NAME
AND RSH.SHIPMENT_HEADER_ID (+) = RCT.SHIPMENT_HEADER_ID
AND D.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID (+)
AND PR.PO_RELEASE_ID(+) = PD.PO_RELEASE_ID
AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID
AND D.INVOICE_ID = I.INVOICE_ID
AND I.VENDOR_ID = V.VENDOR_ID
AND VS.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND VS.ORG_ID = I.ORG_ID
AND L1.LOOKUP_CODE = I.INVOICE_TYPE_LOOKUP_CODE
AND L1.LOOKUP_TYPE = 'INVOICE TYPE'