VIEW: APPS.IEX_DUNNING_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT ps.payment_schedule_id payment_schedule_id ,ps.trx_number trx_number , ps.cash_receipt_id cash_receipt_id ,ps.terms_sequence_number terms_sequence_number ,ps.due_date due_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 ,arpt_sql_func_util.get_lookup_meaning ('INVOICE_TRX_STATUS' ,ps.status) cust_trx_status_name ,ps.class cust_trx_class_code ,arpt_sql_func_util.get_lookup_meaning ('INV/CM' ,ps.class) cust_trx_class_name ,ps.cust_trx_type_id cust_trx_type_id ,ps.customer_id customer_id ,site_uses.site_use_id customer_site_use_id ,ps.customer_trx_id customer_trx_id ,ps.org_id org_id ,substrb (party.party_name ,1 ,50) customer_name ,cust_acct.account_number account_number ,site_uses.location bill_to_location ,del.delinquency_id delinquency_id ,del.status delinquency_status ,iex_utilities.get_lookup_meaning ('IEX_DELINQUENCY_STATUS' ,del.status) del_status_dsp ,decode (ps.amount_due_remaining ,0 ,to_number (NULL) ,trunc (SYSDATE) - trunc (ps.due_date)) days_past_due ,decode (ps.dispute_date ,NULL ,'N' ,'Y') disputed_flag ,ps.dispute_date dispute_date ,ps.amount_in_dispute * - 1 amount_in_dispute ,ps.gl_date gl_date ,party.party_id party_id ,party_site.party_site_number party_site_number ,ps.cons_inv_id cons_inv_id ,op_unit.name operating_unit ,iex_utilities.get_lookup_meaning ('IEX_UWQ_NODE_STATUS' ,del.uwq_status) uwq_status , iex_utilities.get_lookup_meaning ('IEX_UNPAID_REASON' ,del.unpaid_reason_code) unpaid_reason_meaning ,del.staged_dunning_level staged_dunning_level ,dun.DUNNING_TRX_ID Dunning_trx_id ,dun.DUNNING_ID Dunning_id FROM ar_payment_schedules ps ,hz_cust_site_uses site_uses ,hz_cust_accounts cust_acct ,hz_parties party ,iex_delinquencies del ,hz_cust_acct_sites acct_site ,hz_party_sites party_site ,hr_operating_units op_unit ,IEX_DUNNING_TRANSACTIONS dun WHERE ps.customer_id = cust_acct.cust_account_id AND cust_acct.party_id = party.party_id AND site_uses.site_use_id = ps.customer_site_use_id and dun.payment_schedule_id = ps.payment_schedule_id AND ps.payment_schedule_id = del.payment_schedule_id(+) AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND acct_site.cust_account_id = cust_acct.cust_account_id AND acct_site.party_site_id = party_site.party_site_id AND ps.org_id = op_unit.organization_id (+)

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.CASH_RECEIPT_ID CASH_RECEIPT_ID
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE DUE_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
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('INVOICE_TRX_STATUS'
, PS.STATUS) CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('INV/CM'
, PS.CLASS) CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PS.CUSTOMER_ID CUSTOMER_ID
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, SUBSTRB (PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SITE_USES.LOCATION BILL_TO_LOCATION
, DEL.DELINQUENCY_ID DELINQUENCY_ID
, DEL.STATUS DELINQUENCY_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_DELINQUENCY_STATUS'
, DEL.STATUS) DEL_STATUS_DSP
, DECODE (PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER (NULL)
, TRUNC (SYSDATE) - TRUNC (PS.DUE_DATE)) DAYS_PAST_DUE
, DECODE (PS.DISPUTE_DATE
, NULL
, 'N'
, 'Y') DISPUTED_FLAG
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE * - 1 AMOUNT_IN_DISPUTE
, PS.GL_DATE GL_DATE
, PARTY.PARTY_ID PARTY_ID
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, PS.CONS_INV_ID CONS_INV_ID
, OP_UNIT.NAME OPERATING_UNIT
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_UWQ_NODE_STATUS'
, DEL.UWQ_STATUS) UWQ_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_UNPAID_REASON'
, DEL.UNPAID_REASON_CODE) UNPAID_REASON_MEANING
, DEL.STAGED_DUNNING_LEVEL STAGED_DUNNING_LEVEL
, DUN.DUNNING_TRX_ID DUNNING_TRX_ID
, DUN.DUNNING_ID DUNNING_ID
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, IEX_DELINQUENCIES DEL
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HR_OPERATING_UNITS OP_UNIT
, IEX_DUNNING_TRANSACTIONS DUN
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID
AND DUN.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+)
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PS.ORG_ID = OP_UNIT.ORGANIZATION_ID (+)