View: AR_AEL_GL_INV_V_OLD

View Text

SELECT /* $HEADER: ARAEGINV.SQL 115.7 99/09/28 14:16:10 MSABAPAT NOSHIP $ */ /* R.JE_BATCH_ID JE_BATCH_ID
, */ JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CT.ORG_ID ORG_ID
, CTT.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CT.CUST_TRX_TYPE_ID TRX_TYPE_N
, CTT.NAME TRX_TYPE_NAME
, CT.TRX_NUMBER TRX_NUMBER_DISPLAYED
, CT.TRX_NUMBER TRX_NUMBER_C
, CT.TRX_DATE TRX_DATE
, CTLGD.COMMENTS COMMENTS
, CT.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, FD.NAME DOC_SEQUENCE_NAME
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CT' TRX_HDR_TABLE
, CT.CUSTOMER_TRX_ID TRX_HDR_ID
, CTLGD.ACCOUNT_CLASS ACCT_LINE_TYPE
, L3.MEANING ACCT_LINE_TYPE_NAME
, CTLGD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CT.INVOICE_CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.AMOUNT
, 0))
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.AMOUNT
, 0)
, NULL))) ENTERED_DR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.AMOUNT
, 0)
, NULL)
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.AMOUNT
, 0)))) ENTERED_CR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.ACCTD_AMOUNT
, 0))
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.ACCTD_AMOUNT
, 0)
, NULL))) ACCOUNTED_DR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.ACCTD_AMOUNT
, 0)
, NULL)
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.ACCTD_AMOUNT
, 0)))) ACCOUNTED_CR
, CT.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CT.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, CT.EXCHANGE_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, CT.BILL_TO_CUSTOMER_ID THIRD_PARTY_ID
, C.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, C.CUSTOMER_NAME THIRD_PARTY_NAME
, CT.BILL_TO_SITE_USE_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, CTLGD.GL_DATE ACCOUNTING_DATE
, L4.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(CTLGD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, 'CT'
, 'CTLGD') SOURCE_TABLE
, DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, CT.CUSTOMER_TRX_ID
, CTLGD.CUST_TRX_LINE_GL_DIST_ID) SOURCE_ID
, RR.NAME ACCOUNTING_RULE_NAME
, CTLGD.CUST_TRX_LINE_GL_DIST_ID AEL_ID
, DECODE(CTL.LINE_NUMBER
, NULL
, L1.MEANING || ' ' || CT.TRX_NUMBER
, DECODE(CTL2.LINE_NUMBER
, NULL
, DECODE(CTL.LINE_NUMBER
, NULL
, NULL
, L5.MEANING || ' ' || TO_NUMBER(CTL.LINE_NUMBER) )
, L5.MEANING || ' ' || TO_NUMBER(CTL2.LINE_NUMBER) || '
, ' || L6.MEANING || ' ' || CTL.LINE_NUMBER ) ) AE_LINE_REFERENCE
, DECODE(CTL.LINE_NUMBER
, NULL
, RPAD(L1.MEANING
, 80) || ' ' || RPAD(CT.TRX_NUMBER
, 20)
, DECODE(CTL2.LINE_NUMBER
, NULL
, DECODE(CTL.LINE_NUMBER
, NULL
, NULL
, L5.MEANING || ' ' || LPAD(TO_NUMBER(CTL.LINE_NUMBER)
, 15
, '0') )
, L5.MEANING || ' ' || LPAD(TO_NUMBER(CTL2.LINE_NUMBER)
, 15
, '0') || '
, ' || L6.MEANING || ' ' || LPAD(TO_NUMBER(CTL.LINE_NUMBER)
, 15
, '0')) ) AE_LINE_REFERENCE_INTERNAL
, 'CTLGD' AEL_TABLE
, CTLGD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CTLGD.LAST_UPDATED_BY LAST_UPDATED_BY
, CTLGD.CREATION_DATE CREATION_DATE
, CTLGD.CREATED_BY CREATED_BY
, CTLGD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CTLGD.REQUEST_ID REQUEST_ID
, CTLGD.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CTLGD.PROGRAM_ID PROGRAM_ID
, CTLGD.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR TRANSACTION */ BS.NAME TRX_SOURCE_NAME
, CT.BATCH_SOURCE_ID TRX_SOURCE_ID
, CTL.TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER
, CTL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, DECODE(CTL2.LINE_NUMBER
, NULL
, TO_NUMBER(NULL)
, CTL.LINE_NUMBER) TRX_DETAIL_LINE_NUMBER
, DECODE(CTL2.LINE_NUMBER
, NULL
, CTL.LINE_NUMBER
, CTL2.LINE_NUMBER) TRX_LINE_NUMBER
, CTL.LINE_TYPE TRX_LINE_TYPE
, L2.MEANING TRX_LINE_TYPE_NAME
, CTL.QUANTITY_INVOICED TRX_QUANTITY
, CTL.SALES_ORDER SALES_ORDER_NUMBER
, S.NAME SALESREP_NAME
, AVT.TAX_CODE TAX_CODE
, CTL.VAT_TAX_ID TAX_CODE_ID
, CTL.TAX_RATE TAX_RATE
, CTL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, MUOM.UNIT_OF_MEASURE TRX_UOM
, TO_DATE(NULL) APPLICATION_DATE
, NULL APPLIED_TO_TRX_HDR_TABLE
, TO_NUMBER(NULL) APPLIED_TO_TRX_HDR_ID
, NULL APPLIED_TO_TRX_HDR_NUMBER_C
, NULL APPLIED_TO_TRX_HDR_NUMBER_DISP
, NULL APPLIED_TO_TRX_HDR_CURRENCY
, TO_DATE(NULL) APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, TO_NUMBER(NULL) APPLIED_TO_TRX_LINE_NUMBER
FROM AR_LOOKUPS L6
, AR_LOOKUPS L5
, AR_LOOKUPS L3
, AR_VAT_TAX_ALL AVT
, MTL_UNITS_OF_MEASURE MUOM
, RA_RULES RR
, AR_LOOKUPS L2
, RA_SALESREPS_ALL S
, FND_DOCUMENT_SEQUENCES FD
, AR_LOOKUPS L4
, AR_LOOKUPS L1
, RA_CUST_TRX_TYPES_ALL CTT
, GL_DAILY_CONVERSION_TYPES GLCT
, RA_SITE_USES_ALL SU
, RA_CUSTOMERS C
, RA_BATCH_SOURCES_ALL BS
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, RA_CUSTOMER_TRX_LINES_ALL CTL2
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, GL_IMPORT_REFERENCES R
, GL_JE_HEADERS JEH
WHERE L6.LOOKUP_CODE = 'DETAIL_LINE' AND L6.LOOKUP_TYPE = 'VIEW_ACCOUNTING' AND L5.LOOKUP_CODE = 'LINE' AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING' AND L3.LOOKUP_TYPE= DECODE(CTLGD.COLLECTED_TAX_CCID
, NULL
, 'AUTOGL_TYPE'
, 'DISTRIBUTION_SOURCE_TYPE')
AND L3.LOOKUP_CODE = DECODE(CTLGD.COLLECTED_TAX_CCID
, NULL
, NVL(CTLGD.ACCOUNT_CLASS
, 'REV')
, 'DEFERRED_TAX')
AND S.SALESREP_ID(+) = CTLGD.CUST_TRX_LINE_SALESREP_ID AND /* OUTER JOIN WORKAROUND(ORA-1417): SINCE WE CANNOT OUTER JOIN TO ANOTHER TABLE FOR THE ORG_ID
, THE FOLLOWING IS DECODE IS USED */ NVL(CT.ORG_ID
, -99) = DECODE(S.SALESREP_ID
, NULL
, NVL(CT.ORG_ID
, -99)
, NVL(S.ORG_ID
, -99)) AND AVT.VAT_TAX_ID(+) = CTL.VAT_TAX_ID AND NVL(AVT.ORG_ID(+)
, -99) = NVL(CTL.ORG_ID
, -99) AND MUOM.UOM_CODE(+) = CTL.UOM_CODE AND RR.RULE_ID(+) = CTL.ACCOUNTING_RULE_ID AND L4.LOOKUP_CODE = DECODE(CTLGD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') AND L4.LOOKUP_TYPE = 'YES/NO' AND L2.LOOKUP_CODE(+) = CTL.LINE_TYPE AND L2.LOOKUP_TYPE(+) = 'STD_LINE_TYPE' AND CT.DOC_SEQUENCE_ID = FD.DOC_SEQUENCE_ID (+) AND L1.LOOKUP_CODE = CTT.TYPE AND L1.LOOKUP_TYPE = 'INV/CM' AND NVL(CT.ORG_ID
, -99) = NVL(CTT.ORG_ID
, -99) AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.EXCHANGE_RATE_TYPE = GLCT.CONVERSION_TYPE(+) AND CT.BILL_TO_SITE_USE_ID = SU.SITE_USE_ID AND CT.BILL_TO_CUSTOMER_ID = C.CUSTOMER_ID AND NVL(CT.ORG_ID
, -99) = NVL(BS.ORG_ID
, -99) AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CTL.LINK_TO_CUST_TRX_LINE_ID = CTL2.CUSTOMER_TRX_LINE_ID(+) AND NVL(CTL.ORG_ID
, -99) = NVL(CTL2.ORG_ID(+)
, -99) AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID(+) AND NVL(CTLGD.ORG_ID
, -99) = NVL(CTL.ORG_ID(+)
, -99) AND CTLGD.ACCOUNT_SET_FLAG = 'N' AND CT.CUSTOMER_TRX_ID = CTLGD.CUSTOMER_TRX_ID AND NVL(CT.ORG_ID
, -99) = NVL(CTLGD.ORG_ID
, -99) AND CTLGD.CUST_TRX_LINE_GL_DIST_ID = TO_NUMBER(R.REFERENCE_3) AND R.REFERENCE_10 = 'RA_CUST_TRX_LINE_GL_DIST' AND R.JE_HEADER_ID = JEH.JE_HEADER_ID AND JEH.JE_CATEGORY IN ('SALES INVOICES'
, 'CREDIT MEMOS'
, 'DEBIT MEMOS'
, 'CHARGEBACKS') UNION ALL /*------------------------------------------------------------------------+ CREDIT MEMO APPLICATIONS +------------------------------------------------------------------------*/ SELECT JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CTCM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CTCM.ORG_ID ORG_ID
, CTTCM.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CTTCM.CUST_TRX_TYPE_ID TRX_TYPE_N
, CTTCM.NAME TRX_TYPE_NAME
, CTCM.TRX_NUMBER TRX_NUMBER_DISPLAYED
, CTCM.TRX_NUMBER TRX_NUMBER_C
, CTCM.TRX_DATE TRX_DATE
, NULL COMMENTS
, CTCM.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CTCM.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CT' TRX_HDR_TABLE
, CTCM.CUSTOMER_TRX_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L6.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, ARD.THIRD_PARTY_ID THIRD_PARTY_ID
, C.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, C.CUSTOMER_NAME THIRD_PARTY_NAME
, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, RA.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, NULL ACCOUNTING_RULE_NAME
, ARD.LINE_ID AEL_ID
, L1.MEANING||' '||CTCM.TRX_NUMBER || '
, ' || L5.MEANING || ' ' || L4.MEANING || ' ' || CTINV.TRX_NUMBER AE_LINE_REFERENCE
, L1.MEANING||' '||RPAD(CTCM.TRX_NUMBER
, 20) || '
, ' || L5.MEANING || ' ' || RPAD(L4.MEANING
, 80) || ' ' || RPAD(CTINV.TRX_NUMBER
, 20) AE_LINE_REFERENCE_INTERNAL
, 'ARD' AEL_TABLE
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) REQUEST_ID
, TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
, TO_NUMBER(NULL) PROGRAM_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR CREDIT MEMOS */ NULL TRX_SOURCE_NAME
, TO_NUMBER(NULL) TRX_SOURCE_ID
, NULL TAX_EXEMPT_NUMBER
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) TRX_DETAIL_LINE_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, NULL TRX_LINE_TYPE
, NULL TRX_LINE_TYPE_NAME
, TO_NUMBER(NULL) TRX_QUANTITY
, NULL SALES_ORDER_NUMBER
, NULL SALESREP_NAME
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) UNIT_SELLING_PRICE
, NULL TRX_UOM
, RA.APPLY_DATE APPLICATION_DATE
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, CTINV.CUSTOMER_TRX_ID APPLIED_TO_TRX_HDR_ID
, CTINV.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_C
, CTINV.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_DISP
, CTINV.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, CTINV.TRX_DATE APPLIED_TO_TRX_HDR_DATE
, L3.MEANING APPLIED_TO_TRX_LINE_TYPE_NAME
, CTLINV.LINE_TYPE APPLIED_TO_TRX_LINE_TYPE
, CTLINV.LINE_NUMBER APPLIED_TO_TRX_LINE_NUMBER
FROM AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, AR_LOOKUPS L4
, AR_LOOKUPS L5
, AR_LOOKUPS L6
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, RA_SITE_USES_ALL SU
, RA_CUSTOMERS C
, RA_CUST_TRX_TYPES_ALL CTTCM
, RA_CUST_TRX_TYPES_ALL CTTCM2
, RA_CUSTOMER_TRX_LINES_ALL CTLINV
, RA_CUSTOMER_TRX_ALL CTINV
, AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, RA_CUSTOMER_TRX_ALL CTCM
, GL_IMPORT_REFERENCES R
, GL_JE_HEADERS JEH
WHERE L1.LOOKUP_TYPE = 'INV/CM' AND L1.LOOKUP_CODE = CTTCM.TYPE AND L2.LOOKUP_TYPE = 'YES/NO' AND L2.LOOKUP_CODE = DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') AND L3.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
AND L3.LOOKUP_CODE(+) = CTLINV.LINE_TYPE
AND L4.LOOKUP_TYPE = 'INV/CM'
AND L4.LOOKUP_CODE = CTTCM2.TYPE
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L6.LOOKUP_CODE = ARD.SOURCE_TYPE
AND L6.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND RA.APPLICATION_TYPE = 'CM' AND NVL(RA.POSTABLE
, 'Y') = 'Y' AND NVL(RA.CONFIRMED_FLAG
, 'Y') = 'Y' AND RA.CUSTOMER_TRX_ID = CTCM.CUSTOMER_TRX_ID AND NVL(RA.ORG_ID
, -99) = NVL(CTCM.ORG_ID
, -99) AND ARD.SOURCE_TABLE = 'RA' AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID AND NVL(ARD.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99) AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND SEQ.DOC_SEQUENCE_ID(+) = CTCM.DOC_SEQUENCE_ID AND CTINV.CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID AND NVL(CTINV.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99) AND CTLINV.CUSTOMER_TRX_LINE_ID(+) = RA.APPLIED_CUSTOMER_TRX_LINE_ID AND NVL(CTLINV.ORG_ID(+)
, -99) = NVL(RA.ORG_ID
, -99) AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID
AND NVL(ARD.ORG_ID
, -99) = NVL(SU.ORG_ID
, -99) AND ARD.THIRD_PARTY_ID = C.CUSTOMER_ID
AND CTTCM2.CUST_TRX_TYPE_ID = CTINV.CUST_TRX_TYPE_ID
AND NVL(CTTCM2.ORG_ID
, -99) = NVL(CTINV.ORG_ID
, -99)
AND NVL(CTTCM.ORG_ID
, -99) = NVL(CTCM.ORG_ID
, -99) AND CTTCM.CUST_TRX_TYPE_ID = CTCM.CUST_TRX_TYPE_ID AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3) AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY = 'CREDIT MEMO APPLICATIONS'

Columns