View: JL_BR_AR_BT_AEL_GL_V

View Text

SELECT JL.JE_HEADER_ID JE_HEADER_ID
, JL.JE_LINE_NUM JE_LINE_NUM
, (DECODE(OV.ACCT_LINE_TYPE
, 'ENDOCR'
, OV.ENDORSEMENT_CREDIT_AMOUNT
, 'BCCR'
, OV.BANK_CHARGES_CREDIT_AMOUNT)) ACCOUNTED_CR
, (DECODE(OV.ACCT_LINE_TYPE
, 'ENDODR'
, OV.ENDORSEMENT_DEBIT_AMOUNT
, 'BCDR'
, OV.BANK_CHARGES_DEBIT_AMOUNT) * NVL(CT.EXCHANGE_RATE
, 1)) ACCOUNTED_DR
, OV.GL_DATE ACCOUNTING_DATE
, OV.OCCURRENCE_ID AEL_ID
, 'OV' AEL_TABLE
, OV.ACCT_LINE_TYPE_NAME AE_LINE_REFERENCE
, OV.ACCT_LINE_TYPE ACCT_LINE_TYPE
, OV.ACCT_LINE_TYPE_NAME ACCT_LINE_TYPE_NAME
, 7004 APPLICATION_ID
, DECODE(OV.ACCT_LINE_TYPE
, 'ENDOCR'
, OV.ENDORSEMENT_CREDIT_CCID
, 'ENDODR'
, OV.ENDORSEMENT_DEBIT_CCID
, 'BCCR'
, OV.BANK_CHARGES_CREDIT_CCID
, 'BCDR'
, OV.BANK_CHARGES_DEBIT_CCID) CODE_COMBINATION_ID
, NULL COMMENTS
, CT.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CT.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CT.EXCHANGE_RATE CURRENCY_CONVERSION_RATE
, CT.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, TO_NUMBER(NULL) DOC_SEQUENCE_ID
, NULL DOC_SEQUENCE_NAME
, TO_NUMBER(NULL) DOC_SEQUENCE_VALUE
, DECODE(OV.ACCT_LINE_TYPE
, 'ENDOCR'
, OV.ENDORSEMENT_CREDIT_AMOUNT
, 'BCCR'
, OV.BANK_CHARGES_CREDIT_AMOUNT) ENTERED_CR
, DECODE(OV.ACCT_LINE_TYPE
, 'ENDODR'
, OV.ENDORSEMENT_DEBIT_AMOUNT
, 'BCDR'
, OV.BANK_CHARGES_DEBIT_AMOUNT) ENTERED_DR
, DECODE(NVL(OV.FLAG_POST_GL
, 'N')
, 'Y'
, 'Y'
, 'N') GL_TRANSFER_STATUS
, L2.MEANING GL_TRANSFER_STATUS_NAME
, OV.ORG_ID ORG_ID
, DECODE(OV.ORG_ID
, SP.ORG_ID
, SP.SET_OF_BOOKS_ID) SET_OF_BOOKS_ID
, CD.DOCUMENT_ID SOURCE_ID
, 'CD' SOURCE_TABLE
, NULL TAX_CODE
, CT.BILL_TO_CUSTOMER_ID THIRD_PARTY_ID
, P.PARTY_NAME THIRD_PARTY_NAME
, C.ACCOUNT_NUMBER THIRD_PARTY_NUMBER
, CT.BILL_TO_SITE_USE_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, 'C' THIRD_PARTY_TYPE
, 'BRBT' TRX_CLASS
, SUBSTR(JL_BR_AR_REMIT_COLL_OCCUR.GET_TRX_CLASS_NAME('BRBT')
, 1
, 80)TRX_CLASS_NAME
, OV.OCCURRENCE_DATE TRX_DATE
, CD.DOCUMENT_ID TRX_HDR_ID
, 'CD' TRX_HDR_TABLE
, TO_CHAR(CD.DOCUMENT_ID) TRX_NUMBER_DISPLAYED
, TO_CHAR(CD.DOCUMENT_ID) TRX_NUMBER_C
, TO_NUMBER(NULL) TRX_NUMBER_N
, BO.STD_OCCURRENCE_CODE TRX_TYPE_C
, TO_NUMBER(NULL) TRX_TYPE_N
, L3.MEANING TRX_TYPE_NAME
, OV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OV.LAST_UPDATED_BY LAST_UPDATED_BY
, OV.CREATION_DATE CREATION_DATE
, OV.CREATED_BY CREATED_BY
, OV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, OV.REQUEST_ID REQUEST_ID
, TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
, TO_NUMBER(NULL) PROGRAM_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, NULL ACCOUNTING_RULE_NAME
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) TRX_QUANTITY
, NULL SALESREP_NAME
, NULL SALES_ORDER_NUMBER
, NULL TAX_EXEMPT_NUMBER
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, TO_NUMBER(NULL) TRX_DETAIL_LINE_NUMBER
, NULL TRX_LINE_TYPE
, NULL TRX_LINE_TYPE_NAME
, NULL TRX_UOM
, BS.NAME TRX_SOURCE_NAME
, CT.BATCH_SOURCE_ID TRX_SOURCE_ID
, TO_NUMBER(NULL) UNIT_SELLING_PRICE
, 'ACCRUAL' ACCOUNTING_BASIS
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_LINK_ID
, TO_NUMBER(NULL) TAXABLE_ENTERED_DR
, TO_NUMBER(NULL) TAXABLE_ENTERED_CR
, TO_NUMBER(NULL) TAXABLE_ACCOUNTED_DR
, TO_NUMBER(NULL) TAXABLE_ACCOUNTED_CR
, NULL TRX_HDR_CURRENCY
, APBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, NULL REVERSAL_COMMENTS
, TO_DATE(NULL) REVERSAL_DATE
, TO_DATE(NULL) APPLICATION_DATE
, TO_DATE(NULL) PAYMENT_DEPOSIT_DATE
, NULL AR_ACTIVITY_NAME
, NULL DISTRIBUTION_SET_NAME
, NULL AR_ADJUSTMENT_CREATION_TYPE
, NULL AR_ADJUSTMENT_TYPE
, NULL CHARGEBACK_NUMBER
, 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 FND_LOOKUPS L2
, FND_LOOKUPS L3
, GL_IMPORT_REFERENCES IR
, GL_JE_LINES JL
, AR_SYSTEM_PARAMETERS SP
, GL_DAILY_CONVERSION_TYPES GLCT
, RA_BATCH_SOURCES BS
, RA_CUSTOMER_TRX CT
, HZ_CUST_ACCOUNTS C
, HZ_PARTIES P
, HZ_CUST_SITE_USES SU
, AP_BANK_ACCOUNTS APBA
, JL_BR_AR_BORDEROS B
, JL_BR_AR_SELECT_ACCOUNTS SA
, JL_BR_AR_BANK_OCCURRENCES BO
, JL_BR_AR_BT_BASE_V OV
, JL_BR_AR_COLLECTION_DOCS CD
WHERE DECODE(NVL(OV.FLAG_POST_GL
, 'N')
, 'Y'
, 'Y'
, 'N') = L2.LOOKUP_CODE
AND L2.LOOKUP_TYPE = 'YES_NO'
AND CT.EXCHANGE_RATE_TYPE = GLCT.CONVERSION_TYPE(+)
AND NVL(CT.ORG_ID
, -99) = NVL(BS.ORG_ID
, -99)
AND CT. BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CD.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND C.CUST_ACCOUNT_ID = CT.BILL_TO_CUSTOMER_ID
AND C.PARTY_ID = P.PARTY_ID
AND SU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID
AND OV.DOCUMENT_ID = CD.DOCUMENT_ID(+)
AND OV.BANK_OCCURRENCE_CODE = BO.BANK_OCCURRENCE_CODE
AND OV.BANK_NUMBER = BO.BANK_NUMBER
AND OV.BANK_OCCURRENCE_TYPE = BO.BANK_OCCURRENCE_TYPE
AND BO.STD_OCCURRENCE_CODE = L3.LOOKUP_CODE
AND L3.LOOKUP_TYPE IN ('JLBR_AR_REMIT_BANK_OCCURRENCE'
, 'JLBR_AR_RETURN_BANK_OCCURRENCE')
AND CD.BORDERO_ID = B.BORDERO_ID
AND B.SELECT_ACCOUNT_ID= SA.SELECT_ACCOUNT_ID(+)
AND SA.BANK_ACCOUNT_ID = APBA.BANK_ACCOUNT_ID
AND TO_CHAR(CD.PAYMENT_SCHEDULE_ID) = IR.REFERENCE_3
AND TO_CHAR(CD.BORDERO_ID) = IR.REFERENCE_2
AND IR.REFERENCE_10 = 'JL_BR_AR_OCCURRENCE_DOCS'
AND IR.JE_HEADER_ID = JL.JE_HEADER_ID
AND IR.JE_LINE_NUM = JL.JE_LINE_NUM
AND DECODE(OV.ACCT_LINE_TYPE
, 'ENDOCR'
, OV.ENDORSEMENT_CREDIT_CCID
, 'ENDODR'
, OV.ENDORSEMENT_DEBIT_CCID
, 'BCCR'
, OV.BANK_CHARGES_CREDIT_CCID
, 'BCDR'
, OV.BANK_CHARGES_DEBIT_CCID ) = JL.CODE_COMBINATION_ID

Columns