[Home] [Help]
SELECT R.je_header_id JE_HEADER_ID,
R.je_line_num JE_LINE_NUM,
200 APPLICATION_ID ,
D.set_of_books_id SET_OF_BOOKS_ID ,
D.org_id ORG_ID ,
'INV' TRX_CLASS ,
null 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 ,
null COMMENTS ,
R.subledger_doc_sequence_id DOC_SEQUENCE_ID ,
null DOC_SEQUENCE_NAME ,
R.subledger_doc_sequence_value DOC_SEQUENCE_VALUE,
jel.ACCOUNTED_DR ACCOUNTED_DR,
jel.ACCOUNTED_CR ACCOUNTED_CR,
to_date(null) ACCOUNTING_DATE,
null ACCT_LINE_TYPE,
null ACCT_LINE_TYPE_NAME,
to_number(null) AEH_ID,
to_number(null) AEL_ID,
null AEL_TABLE,
null AE_LINE_REFERENCE,
jel.code_combination_id CODE_COMBINATION_ID,
jeh.currency_code CURRENCY_CODE,
jeh.currency_conversion_date CURRENCY_CONVERSION_DATE,
jeh.currency_conversion_rate CURRENCY_CONVERSION_RATE,
jeh.currency_conversion_type CURRENCY_CONVERSION_TYPE,
null CURRENCY_USER_CONVERSION_TYPE,
jel.entered_cr ENTERED_CR,
jel.entered_dr ENTERED_DR,
null GL_TRANSFER_STATUS,
null GL_TRANSFER_STATUS_NAME,
null TRANSFER_STATUS_DETAIL_NAME,
to_number(null) SOURCE_ID,
null SOURCE_TABLE,
APT.NAME TAX_CODE,
'SUPPLIER' THIRD_PARTY_TYPE,
to_number(null) THIRD_PARTY_ID,
V.segment1 THIRD_PARTY_NUMBER,
V.vendor_name THIRD_PARTY_NAME,
to_number(null) THIRD_PARTY_SUB_ID,
null THIRD_PARTY_SUB_NAME,
I.invoice_date TRX_DATE ,
I.invoice_id TRX_HDR_ID,
null TRX_HDR_TABLE,
null JE_CATEGORY,
null 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,
L.displayed_field TRX_LINE_TYPE_NAME,
to_number(null) 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,
to_number(null) PO_ORDER_RELEASE_NUM,
null RCV_RECEIPT_NUM
FROM GL_LOOKUPS L2, AP_LOOKUP_CODES L,
PO_HEADERS_ALL PH, PO_DISTRIBUTIONS_ALL PD,
PO_VENDORS V, AP_INVOICE_DISTRIBUTIONS_ALL D,
AP_INVOICES_ALL I, GL_JE_HEADERS JEH,
GL_JE_LINES JEL, GL_IMPORT_REFERENCES R,
AP_LOOKUP_CODES L1, AP_TAX_CODES_ALL APT
WHERE L2.LOOKUP_TYPE = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND L2.LOOKUP_CODE = NVL(R.REFERENCE_10,'EXPENSE')
AND L.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND L.LOOKUP_CODE = D.LINE_TYPE_LOOKUP_CODE
AND L1.lookup_code = I.invoice_type_lookup_code
AND L1.lookup_type = 'INVOICE TYPE'
AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND I.VENDOR_ID = V.VENDOR_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL.JE_HEADER_ID = R.JE_HEADER_ID
AND JEL.JE_LINE_NUM = R.JE_LINE_NUM
AND APT.TAX_ID(+) = D.TAX_CODE_ID
AND NVL(D.OLD_DIST_LINE_NUMBER,D.DISTRIBUTION_LINE_NUMBER) =
TO_NUMBER(R.REFERENCE_3)
AND I.INVOICE_ID = TO_NUMBER(R.REFERENCE_2)
SELECT R.JE_HEADER_ID JE_HEADER_ID
,
R.JE_LINE_NUM JE_LINE_NUM
,
200 APPLICATION_ID
,
D.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
,
D.ORG_ID ORG_ID
,
'INV' TRX_CLASS
,
NULL 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
,
NULL COMMENTS
,
R.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
,
NULL DOC_SEQUENCE_NAME
,
R.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
,
JEL.ACCOUNTED_DR ACCOUNTED_DR
,
JEL.ACCOUNTED_CR ACCOUNTED_CR
,
TO_DATE(NULL) ACCOUNTING_DATE
,
NULL ACCT_LINE_TYPE
,
NULL ACCT_LINE_TYPE_NAME
,
TO_NUMBER(NULL) AEH_ID
,
TO_NUMBER(NULL) AEL_ID
,
NULL AEL_TABLE
,
NULL AE_LINE_REFERENCE
,
JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
,
JEH.CURRENCY_CODE CURRENCY_CODE
,
JEH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
,
JEH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
,
JEH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
,
NULL CURRENCY_USER_CONVERSION_TYPE
,
JEL.ENTERED_CR ENTERED_CR
,
JEL.ENTERED_DR ENTERED_DR
,
NULL GL_TRANSFER_STATUS
,
NULL GL_TRANSFER_STATUS_NAME
,
NULL TRANSFER_STATUS_DETAIL_NAME
,
TO_NUMBER(NULL) SOURCE_ID
,
NULL SOURCE_TABLE
,
APT.NAME TAX_CODE
,
'SUPPLIER' THIRD_PARTY_TYPE
,
TO_NUMBER(NULL) THIRD_PARTY_ID
,
V.SEGMENT1 THIRD_PARTY_NUMBER
,
V.VENDOR_NAME THIRD_PARTY_NAME
,
TO_NUMBER(NULL) THIRD_PARTY_SUB_ID
,
NULL THIRD_PARTY_SUB_NAME
,
I.INVOICE_DATE TRX_DATE
,
I.INVOICE_ID TRX_HDR_ID
,
NULL TRX_HDR_TABLE
,
NULL JE_CATEGORY
,
NULL 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
,
L.DISPLAYED_FIELD TRX_LINE_TYPE_NAME
,
TO_NUMBER(NULL) 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
,
TO_NUMBER(NULL) PO_ORDER_RELEASE_NUM
,
NULL RCV_RECEIPT_NUM
FROM GL_LOOKUPS L2
, AP_LOOKUP_CODES L
,
PO_HEADERS_ALL PH
, PO_DISTRIBUTIONS_ALL PD
,
PO_VENDORS V
, AP_INVOICE_DISTRIBUTIONS_ALL D
,
AP_INVOICES_ALL I
, GL_JE_HEADERS JEH
,
GL_JE_LINES JEL
, GL_IMPORT_REFERENCES R
,
AP_LOOKUP_CODES L1
, AP_TAX_CODES_ALL APT
WHERE L2.LOOKUP_TYPE = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND L2.LOOKUP_CODE = NVL(R.REFERENCE_10
, 'EXPENSE')
AND L.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND L.LOOKUP_CODE = D.LINE_TYPE_LOOKUP_CODE
AND L1.LOOKUP_CODE = I.INVOICE_TYPE_LOOKUP_CODE
AND L1.LOOKUP_TYPE = 'INVOICE TYPE'
AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND I.VENDOR_ID = V.VENDOR_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL.JE_HEADER_ID = R.JE_HEADER_ID
AND JEL.JE_LINE_NUM = R.JE_LINE_NUM
AND APT.TAX_ID(+) = D.TAX_CODE_ID
AND NVL(D.OLD_DIST_LINE_NUMBER
, D.DISTRIBUTION_LINE_NUMBER) =
TO_NUMBER(R.REFERENCE_3)
AND I.INVOICE_ID = TO_NUMBER(R.REFERENCE_2)
|
|
|
|