VIEW: APPS.IEX_TRX_SUMMARY_V

Source

View Text - Preformatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , CT.TRX_NUMBER TRX_NUMBER , PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_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.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING , PS.STATUS CUST_TRX_STATUS_CODE , AL_STATUS.MEANING CUST_TRX_STATUS_NAME , PS.CLASS CUST_TRX_CLASS_CODE , PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , PS.CUSTOMER_ID CUSTOMER_ID , PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.ORG_ID ORG_ID , CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER , CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE , 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 , '' SALES_ORDER , PS.GL_DATE GL_DATE , CT.COMMENTS COMMENTS , CT.TERM_ID TERM_ID , CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID , ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID,'NAME',CT.ORG_ID) PRIMARY_SALESREP_NAME , CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID , CUST_ACCT.PARTY_ID PARTY_ID , CT.CREATION_DATE TRX_CREATION_DATE , PS.CONS_INV_ID CONS_INV_ID , CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER , PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID , AL_CLASS.MEANING CUST_TRX_CLASS_NAME , PS.DISPUTE_DATE DISPUTE_DATE , PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE , '' PROMISED_FLAG , '' PAID_FLAG , RAT.NAME TERM_NAME FROM AR_LOOKUPS AL_STATUS, AR_PAYMENT_SCHEDULES PS, RA_CUSTOMER_TRX CT, HZ_CUST_ACCOUNTS CUST_ACCT, IEX_DELINQUENCIES DEL, IEX_LOOKUPS_V DEL_LK, AR_CONS_INV CONS, RA_TERMS_VL RAT, AR_LOOKUPS AL_CLASS WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND PS.STATUS = AL_STATUS.LOOKUP_CODE AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS' 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 CONS.CONS_INV_ID(+) = PS.CONS_INV_ID AND PS.CLASS = AL_CLASS.LOOKUP_CODE AND AL_CLASS.LOOKUP_TYPE = 'INV/CM' AND CT.TERM_ID = RAT.TERM_ID(+)

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, CT.TRX_NUMBER TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_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.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, PS.STATUS CUST_TRX_STATUS_CODE
, AL_STATUS.MEANING CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PS.CUSTOMER_ID CUSTOMER_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 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
, '' SALES_ORDER
, PS.GL_DATE GL_DATE
, CT.COMMENTS COMMENTS
, CT.TERM_ID TERM_ID
, CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
, 'NAME'
, CT.ORG_ID) PRIMARY_SALESREP_NAME
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CUST_ACCT.PARTY_ID PARTY_ID
, CT.CREATION_DATE TRX_CREATION_DATE
, PS.CONS_INV_ID CONS_INV_ID
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, AL_CLASS.MEANING CUST_TRX_CLASS_NAME
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, '' PROMISED_FLAG
, '' PAID_FLAG
, RAT.NAME TERM_NAME
FROM AR_LOOKUPS AL_STATUS
, AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX CT
, HZ_CUST_ACCOUNTS CUST_ACCT
, IEX_DELINQUENCIES DEL
, IEX_LOOKUPS_V DEL_LK
, AR_CONS_INV CONS
, RA_TERMS_VL RAT
, AR_LOOKUPS AL_CLASS
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND PS.STATUS = AL_STATUS.LOOKUP_CODE
AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
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 CONS.CONS_INV_ID(+) = PS.CONS_INV_ID
AND PS.CLASS = AL_CLASS.LOOKUP_CODE
AND AL_CLASS.LOOKUP_TYPE = 'INV/CM'
AND CT.TERM_ID = RAT.TERM_ID(+)