eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IEX_AGING_TRANSACTIONS_V

Source

View Text - Preformatted


SELECT substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME ,CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER ,CT.TRX_NUMBER TRX_NUMBER ,PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER ,SITE_USES.LOCATION BILL_TO_LOCATION ,SUBSTR(LOC.ADDRESS1,1,25) ||' ' || SUBSTR(LOC.ADDRESS2,1,25) ||' ' || SUBSTR(LOC.ADDRESS3,1,25) ||' ' || SUBSTR(LOC.ADDRESS4,1,25) ||' ' || LOC.CITY|| ',' || ' ' || NVL(LOC.STATE, LOC.PROVINCE) ||' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS ,PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL ,PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING ,PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE ,DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE ,AABLVL.REPORT_HEADING1 || ' ' || AABLVL.REPORT_HEADING2 Aging_Bucket_line ,PS.DUE_DATE DUE_DATE ,PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER ,CT.TRX_DATE TRX_DATE ,CT.CREATION_DATE ,AL_CLASS.MEANING CUST_TRX_CLASS_NAME ,CTT.NAME ,PS.status ,PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE ,PS.DISPUTE_DATE DISPUTE_DATE ,(SELECT DECODE(COUNT(1), 0, 0, 1) FROM iex_promise_details WHERE delinquency_id = del.delinquency_id) PROMISED_FLAG ,(SELECT DECODE(COUNT(1), 0, 0, 1) FROM ar_cash_receipts acr, ar_receivable_applications ara, ar_cash_receipt_history acrh WHERE ara.applied_payment_schedule_id = ps.payment_schedule_id AND ara.cash_receipt_id = acr.cash_receipt_id AND acr.cash_receipt_id = acrh.cash_receipt_id AND nvl(acr.confirmed_flag, 'Y') = 'Y' AND acr.reversal_date is null AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED', 'N', ' '), 'REVERSED') AND acrh.current_record_flag = 'Y') PAID_FLAG ,DEL_LK.MEANING DEL_STATUS_DSP ,CT.PURCHASE_ORDER PURCHASE_ORDER ,(SELECT sales_order FROM ra_customer_trx_lines WHERE customer_trx_id = ct.customer_trx_id AND line_number = 1 AND rownum < 1 ) SALES_ORDER ,(SELECT str_temp.strategy_name FROM iex_strategies str, iex_strategy_templates_vl str_temp WHERE str.delinquency_id = del.delinquency_id AND str.strategy_template_id = str_temp.strategy_temp_id AND str_temp.category_type = 'DELINQUENT' AND str.status_code IN ('OPEN', 'ONHOLD')) STRATEGY_NAME ,(SELECT a.score_value FROM iex_score_histories a WHERE a.creation_date = (SELECT MAX(creation_date) FROM iex_Score_histories WHERE score_object_code = 'IEX_INVOICES' AND score_object_id = ps.payment_schedule_id) AND a.score_object_code = 'IEX_INVOICES' AND a.score_object_id = ps.payment_schedule_id) TRX_SCORE ,PS.GL_DATE GL_DATE ,RAT.NAME TERM_NAME ,PS.CLASS ,PS.STATUS ,CT.INTERFACE_HEADER_CONTEXT ,PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID ,PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID ,PS.CUSTOMER_ID ,PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID ,DEL.DELINQUENCY_ID DELINQUENCY_ID ,PARTY.PARTY_ID PARTY_ID ,AABLVL.AGING_BUCKET_ID ,AABLVL.AGING_BUCKET_LINE_ID ,DEL.ORG_ID ORG_ID ,CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER ,OP_UNIT.NAME OPEATING_UNIT FROM AR_LOOKUPS AL_STATUS, AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX CT, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, AR_LOOKUPS AL_CLASS, IEX_DELINQUENCIES DEL, IEX_LOOKUPS_V DEL_LK, RA_TERMS_VL RAT, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN, HZ_LOCATIONS LOC, FND_TERRITORIES_VL TERR, AR_AGING_BUCKET_LINES_VL AABLVL, AR_CONS_INV CONS, HR_OPERATING_UNITS OP_UNIT 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_SITE_USE_ID = SITE_USES.SITE_USE_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.ORG_ID=CTT.ORG_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.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 ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) AND LOC.COUNTRY = TERR.TERRITORY_CODE AND TRUNC(SYSDATE)-TRUNC(PS.due_date) BETWEEN AABLVL.days_start and AABLVL.days_to AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID AND DEL.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)

View Text - HTML Formatted

SELECT SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, CT.TRX_NUMBER TRX_NUMBER
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, SITE_USES.LOCATION BILL_TO_LOCATION
, SUBSTR(LOC.ADDRESS1
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS2
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS3
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS4
, 1
, 25) ||' ' || LOC.CITY|| '
, ' || ' ' || NVL(LOC.STATE
, LOC.PROVINCE) ||' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE
, AABLVL.REPORT_HEADING1 || ' ' || AABLVL.REPORT_HEADING2 AGING_BUCKET_LINE
, PS.DUE_DATE DUE_DATE
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, CT.TRX_DATE TRX_DATE
, CT.CREATION_DATE
, AL_CLASS.MEANING CUST_TRX_CLASS_NAME
, CTT.NAME
, PS.STATUS
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS.DISPUTE_DATE DISPUTE_DATE
, (SELECT DECODE(COUNT(1)
, 0
, 0
, 1)
FROM IEX_PROMISE_DETAILS
WHERE DELINQUENCY_ID = DEL.DELINQUENCY_ID) PROMISED_FLAG
, (SELECT DECODE(COUNT(1)
, 0
, 0
, 1)
FROM AR_CASH_RECEIPTS ACR
, AR_RECEIVABLE_APPLICATIONS ARA
, AR_CASH_RECEIPT_HISTORY ACRH
WHERE ARA.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND NVL(ACR.CONFIRMED_FLAG
, 'Y') = 'Y'
AND ACR.REVERSAL_DATE IS NULL
AND ACRH.STATUS NOT IN (DECODE (ACRH.FACTOR_FLAG
, 'Y'
, 'RISK_ELIMINATED'
, 'N'
, ' ')
, 'REVERSED')
AND ACRH.CURRENT_RECORD_FLAG = 'Y') PAID_FLAG
, DEL_LK.MEANING DEL_STATUS_DSP
, CT.PURCHASE_ORDER PURCHASE_ORDER
, (SELECT SALES_ORDER
FROM RA_CUSTOMER_TRX_LINES
WHERE CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND LINE_NUMBER = 1
AND ROWNUM < 1 ) SALES_ORDER
, (SELECT STR_TEMP.STRATEGY_NAME
FROM IEX_STRATEGIES STR
, IEX_STRATEGY_TEMPLATES_VL STR_TEMP
WHERE STR.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND STR.STRATEGY_TEMPLATE_ID = STR_TEMP.STRATEGY_TEMP_ID
AND STR_TEMP.CATEGORY_TYPE = 'DELINQUENT'
AND STR.STATUS_CODE IN ('OPEN'
, 'ONHOLD')) STRATEGY_NAME
, (SELECT A.SCORE_VALUE
FROM IEX_SCORE_HISTORIES A
WHERE A.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM IEX_SCORE_HISTORIES
WHERE SCORE_OBJECT_CODE = 'IEX_INVOICES'
AND SCORE_OBJECT_ID = PS.PAYMENT_SCHEDULE_ID)
AND A.SCORE_OBJECT_CODE = 'IEX_INVOICES'
AND A.SCORE_OBJECT_ID = PS.PAYMENT_SCHEDULE_ID) TRX_SCORE
, PS.GL_DATE GL_DATE
, RAT.NAME TERM_NAME
, PS.CLASS
, PS.STATUS
, CT.INTERFACE_HEADER_CONTEXT
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, DEL.DELINQUENCY_ID DELINQUENCY_ID
, PARTY.PARTY_ID PARTY_ID
, AABLVL.AGING_BUCKET_ID
, AABLVL.AGING_BUCKET_LINE_ID
, DEL.ORG_ID ORG_ID
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, OP_UNIT.NAME OPEATING_UNIT
FROM AR_LOOKUPS AL_STATUS
, AR_PAYMENT_SCHEDULES PS
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX CT
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS AL_CLASS
, IEX_DELINQUENCIES DEL
, IEX_LOOKUPS_V DEL_LK
, RA_TERMS_VL RAT
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_LOCATIONS LOC
, FND_TERRITORIES_VL TERR
, AR_AGING_BUCKET_LINES_VL AABLVL
, AR_CONS_INV CONS
, HR_OPERATING_UNITS OP_UNIT
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_SITE_USE_ID = SITE_USES.SITE_USE_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID=CTT.ORG_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.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 ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99)
AND LOC.COUNTRY = TERR.TERRITORY_CODE
AND TRUNC(SYSDATE)-TRUNC(PS.DUE_DATE) BETWEEN AABLVL.DAYS_START
AND AABLVL.DAYS_TO
AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID
AND DEL.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)