VIEW: APPS.CSC_INV_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , CT.TRX_NUMBER TRX_NUMBER , CT.PURCHASE_ORDER PURCHASE_ORDER , CT.LAST_UPDATE_DATE LAST_UPDATE_DATE , CT.LAST_UPDATED_BY LAST_UPDATED_BY , CT.CREATION_DATE CREATION_DATE, CT.CREATED_BY CREATED_BY , CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , PS.DUE_DATE DUE_DATE , CT.TRX_DATE TRX_DATE , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING , PS.STATUS CUST_TRX_STATUS_CODE , AL_STATUS.MEANING CUST_TRX_STATUS_NAME , PS.CLASS CUST_TRX_CLASS_CODE , AL_CLASS.MEANING CUST_TRX_CLASS_NAME , PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , CTT.NAME CUST_TRX_TYPE_NAME , PS.CUSTOMER_ID CUSTOMER_ID , PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.ORG_ID ORG_ID , ORG.NAME OPERATING_UNIT, SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER , DEL.DELINQUENCY_ID DELINQUENCY_ID , DEL.STATUS DELINQUENCY_STATUS , DEL_LK.MEANING DEL_STATUS_DSP , DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE , PS.DISPUTE_DATE DISPUTE_DATE , PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE , NULL, PS.GL_DATE GL_DATE, PS.AMOUNT_APPLIED AMOUNT_APPLIED, PS.LAST_CHARGE_DATE LAST_CHARGE_DATE , CT.COMMENTS COMMENTS , CT.TERM_ID TERM_ID, RAT.NAME TERM_NAME , CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID, CTS.NAME PRIMARY_SALESREP_NAME, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID, CT.COMPLETE_FLAG COMPLETE_FLAG , PARTY.PARTY_ID PARTY_ID FROM AR_LOOKUPS AL_STATUS, AR_PAYMENT_SCHEDULES_ALL PS, RA_CUST_TRX_TYPES_ALL CTT, RA_CUSTOMER_TRX CT, RA_SALESREPS_ALL CTS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, AR_LOOKUPS AL_CLASS, IEX_DELINQUENCIES_ALL DEL, IEX_LOOKUPS_V DEL_LK, RA_TERMS_TL RAT , HR_ORGANIZATION_UNITS ORG WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND PS.CUSTOMER_ID+0 = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.PRIMARY_SALESREP_ID = CTS.SALESREP_ID (+) AND PS.STATUS = AL_STATUS.LOOKUP_CODE AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS' AND PS.CLASS = AL_CLASS.LOOKUP_CODE AND AL_CLASS.LOOKUP_TYPE = 'INV/CM' AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+) AND DEL.STATUS = DEL_LK.LOOKUP_CODE(+) AND DEL_LK.LOOKUP_TYPE(+) = 'IEX_DELINQUENCY_STATUS' AND CT.TERM_ID = RAT.TERM_ID(+) AND PS.ORG_ID =CT.ORG_ID AND CT.ORG_ID = CTT.ORG_ID AND CTT.ORG_ID = PS.ORG_ID AND PS.ORG_ID = ORG.ORGANIZATION_ID AND CTS.ORG_ID (+) = CT.ORG_ID AND DEL.ORG_ID (+) =PS.ORG_ID AND RAT.LANGUAGE(+) = USERENV('LANG')

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, CT.TRX_NUMBER TRX_NUMBER
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY LAST_UPDATED_BY
, CT.CREATION_DATE CREATION_DATE
, CT.CREATED_BY CREATED_BY
, CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.DUE_DATE DUE_DATE
, CT.TRX_DATE TRX_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS.STATUS CUST_TRX_STATUS_CODE
, AL_STATUS.MEANING CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, AL_CLASS.MEANING CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CTT.NAME CUST_TRX_TYPE_NAME
, PS.CUSTOMER_ID CUSTOMER_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, ORG.NAME OPERATING_UNIT
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, DEL.DELINQUENCY_ID DELINQUENCY_ID
, DEL.STATUS DELINQUENCY_STATUS
, DEL_LK.MEANING DEL_STATUS_DSP
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, NULL
, PS.GL_DATE GL_DATE
, PS.AMOUNT_APPLIED AMOUNT_APPLIED
, PS.LAST_CHARGE_DATE LAST_CHARGE_DATE
, CT.COMMENTS COMMENTS
, CT.TERM_ID TERM_ID
, RAT.NAME TERM_NAME
, CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, CTS.NAME PRIMARY_SALESREP_NAME
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CT.COMPLETE_FLAG COMPLETE_FLAG
, PARTY.PARTY_ID PARTY_ID
FROM AR_LOOKUPS AL_STATUS
, AR_PAYMENT_SCHEDULES_ALL PS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_CUSTOMER_TRX CT
, RA_SALESREPS_ALL CTS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS AL_CLASS
, IEX_DELINQUENCIES_ALL DEL
, IEX_LOOKUPS_V DEL_LK
, RA_TERMS_TL RAT
, HR_ORGANIZATION_UNITS ORG
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND PS.CUSTOMER_ID+0 = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.PRIMARY_SALESREP_ID = CTS.SALESREP_ID (+)
AND PS.STATUS = AL_STATUS.LOOKUP_CODE
AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
AND PS.CLASS = AL_CLASS.LOOKUP_CODE
AND AL_CLASS.LOOKUP_TYPE = 'INV/CM'
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+)
AND DEL.STATUS = DEL_LK.LOOKUP_CODE(+)
AND DEL_LK.LOOKUP_TYPE(+) = 'IEX_DELINQUENCY_STATUS'
AND CT.TERM_ID = RAT.TERM_ID(+)
AND PS.ORG_ID =CT.ORG_ID
AND CT.ORG_ID = CTT.ORG_ID
AND CTT.ORG_ID = PS.ORG_ID
AND PS.ORG_ID = ORG.ORGANIZATION_ID
AND CTS.ORG_ID (+) = CT.ORG_ID
AND DEL.ORG_ID (+) =PS.ORG_ID
AND RAT.LANGUAGE(+) = USERENV('LANG')