VIEW: APPS.IEX_TRANSACTIONS_QUERY_V

Source

View Text - Preformatted

SELECT PS.PAYMENT_SCHEDULE_ID ,CT.TRX_NUMBER ,PS.TERMS_SEQUENCE_NUMBER ,CT.PURCHASE_ORDER ,PS.LAST_UPDATE_DATE ,PS.LAST_UPDATED_BY ,PS.CREATION_DATE ,PS.CREATED_BY ,PS.LAST_UPDATE_LOGIN ,PS.DUE_DATE ,CT.TRX_DATE ,PS.INVOICE_CURRENCY_CODE ,PS.AMOUNT_DUE_ORIGINAL ,PS.AMOUNT_DUE_REMAINING ,PS.ACCTD_AMOUNT_DUE_REMAINING ,PS.STATUS ,AL_STATUS.MEANING ,PS.CLASS CUST_TRX_CLASS_CODE ,AL_CLASS.MEANING ,PS.CUST_TRX_TYPE_ID ,CTT.NAME ,PS.CUSTOMER_ID ,PS.CUSTOMER_SITE_USE_ID ,PS.CUSTOMER_TRX_ID ,PS.EXCHANGE_RATE_TYPE ,PS.EXCHANGE_DATE ,PS.EXCHANGE_RATE ,PS.ORG_ID ,substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME ,CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER ,CUST_ACCT.CUST_ACCOUNT_ID ACCOUNT_ID ,SU.LOCATION CUST_TRX_LOCATION ,CT.BILL_TO_CONTACT_ID ,CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE ,PS.SELECTED_FOR_RECEIPT_BATCH_ID ,DEL.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 ,DECODE(PS.DISPUTE_DATE, null, 'N', 'Y') DISPUTED_FLAG ,PS.DISPUTE_DATE ,PS.AMOUNT_IN_DISPUTE ,IEX_TRX_VIEW_PKG.IS_PROMISED(DEL.DELINQUENCY_ID) PROMISED_FLAG ,IEX_TRX_VIEW_PKG.IS_PAID(PS.PAYMENT_SCHEDULE_ID) PAID_FLAG ,IEX_TRX_VIEW_PKG.GET_SALES_ORDER(CT.CUSTOMER_TRX_ID) SALES_ORDER ,IEX_TRX_VIEW_PKG.GET_SCORE(PS.PAYMENT_SCHEDULE_ID) TRX_SCORE ,IEX_TRX_VIEW_PKG.GET_STRATEGY_NAME(DEL.DELINQUENCY_ID) STRATEGY_NAME ,PS.GL_DATE GL_DATE ,PS.AMOUNT_LINE_ITEMS_ORIGINAL ,PS.AMOUNT_LINE_ITEMS_REMAINING ,PS.TAX_ORIGINAL ,ps.TAX_REMAINING ,PS.FREIGHT_ORIGINAL ,PS.FREIGHT_REMAINING ,PS.RECEIVABLES_CHARGES_CHARGED ,PS.LAST_CHARGE_DATE ,DUN.CREATION_DATE ,DUN.DUNNING_ID FROM AR_LOOKUPS AL_STATUS, AR_PAYMENT_SCHEDULES_ALL PS, RA_CUST_TRX_TYPES_ALL CTT, RA_CUSTOMER_TRX_ALL CT, HZ_CUST_SITE_USES_ALL SU, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, AR_LOOKUPS AL_CLASS, IEX_DELINQUENCIES_ALL DEL, IEX_LOOKUPS_V DEL_LK, IEX_DUNNINGS DUN WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_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 PS.ORG_ID = CTT.ORG_ID AND CT.ORG_ID = PS.ORG_ID AND SU.ORG_ID = PS.ORG_ID AND DUN.DELINQUENCY_ID (+)= DEL.DELINQUENCY_ID AND NVL(TO_CHAR(DUN.CREATION_DATE,'YYMMDDHHMISS'),'X') = NVL(TO_CHAR((SELECT MAX(DUN2.CREATION_DATE) FROM IEX_DUNNINGS DUN2 WHERE DUN2.DELINQUENCY_ID = DEL.DELINQUENCY_ID),'YYMMDDHHMISS'),'X')

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID
, CT.TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER
, CT.PURCHASE_ORDER
, PS.LAST_UPDATE_DATE
, PS.LAST_UPDATED_BY
, PS.CREATION_DATE
, PS.CREATED_BY
, PS.LAST_UPDATE_LOGIN
, PS.DUE_DATE
, CT.TRX_DATE
, PS.INVOICE_CURRENCY_CODE
, PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.STATUS
, AL_STATUS.MEANING
, PS.CLASS CUST_TRX_CLASS_CODE
, AL_CLASS.MEANING
, PS.CUST_TRX_TYPE_ID
, CTT.NAME
, PS.CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID
, PS.EXCHANGE_RATE_TYPE
, PS.EXCHANGE_DATE
, PS.EXCHANGE_RATE
, PS.ORG_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, CUST_ACCT.CUST_ACCOUNT_ID ACCOUNT_ID
, SU.LOCATION CUST_TRX_LOCATION
, CT.BILL_TO_CONTACT_ID
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, DEL.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
, DECODE(PS.DISPUTE_DATE
, NULL
, 'N'
, 'Y') DISPUTED_FLAG
, PS.DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE
, IEX_TRX_VIEW_PKG.IS_PROMISED(DEL.DELINQUENCY_ID) PROMISED_FLAG
, IEX_TRX_VIEW_PKG.IS_PAID(PS.PAYMENT_SCHEDULE_ID) PAID_FLAG
, IEX_TRX_VIEW_PKG.GET_SALES_ORDER(CT.CUSTOMER_TRX_ID) SALES_ORDER
, IEX_TRX_VIEW_PKG.GET_SCORE(PS.PAYMENT_SCHEDULE_ID) TRX_SCORE
, IEX_TRX_VIEW_PKG.GET_STRATEGY_NAME(DEL.DELINQUENCY_ID) STRATEGY_NAME
, PS.GL_DATE GL_DATE
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_REMAINING
, PS.TAX_ORIGINAL
, PS.TAX_REMAINING
, PS.FREIGHT_ORIGINAL
, PS.FREIGHT_REMAINING
, PS.RECEIVABLES_CHARGES_CHARGED
, PS.LAST_CHARGE_DATE
, DUN.CREATION_DATE
, DUN.DUNNING_ID
FROM AR_LOOKUPS AL_STATUS
, AR_PAYMENT_SCHEDULES_ALL PS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_CUSTOMER_TRX_ALL CT
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS AL_CLASS
, IEX_DELINQUENCIES_ALL DEL
, IEX_LOOKUPS_V DEL_LK
, IEX_DUNNINGS DUN
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_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 PS.ORG_ID = CTT.ORG_ID
AND CT.ORG_ID = PS.ORG_ID
AND SU.ORG_ID = PS.ORG_ID
AND DUN.DELINQUENCY_ID (+)= DEL.DELINQUENCY_ID
AND NVL(TO_CHAR(DUN.CREATION_DATE
, 'YYMMDDHHMISS')
, 'X') = NVL(TO_CHAR((SELECT MAX(DUN2.CREATION_DATE)
FROM IEX_DUNNINGS DUN2
WHERE DUN2.DELINQUENCY_ID = DEL.DELINQUENCY_ID)
, 'YYMMDDHHMISS')
, 'X')