VIEW: APPS.IEX_PAYING_DUNNINGS_BALI_V

Source

View Text - Preformatted

SELECT dun.delinquency_id delinquency_id, dun.dunning_id dunning_id, dun.status status, dun_lk3.meaning status_meaning, dun.creation_date creation_date, fm.template_name template_name, del.aging_bucket_line_id aging_bucket_line_id, del.party_cust_id party_cust_id, h.party_name, del.cust_account_id account_id, del.customer_site_use_id customer_site_use_id, aging.report_heading1 || aging.report_heading2 bucket_line, dun.dunning_method dunning_method, dun_lk2.meaning dunning_method_meaning, dun.amount_due_remaining amount_due_remaining, dun.currency_code currency_code, dun.object_id object_id, dun.object_type object_type, dun.dunning_object_id dunning_object_id, dun.dunning_level dunning_level, dun_lk.meaning dunning_level_meaning, dun.ffm_request_id ffm_request_id, ffm.outcome_code ffm_status, del.payment_schedule_id, tr.trx_number, tr.terms_sequence_number, tr.due_date, tr.purchase_order, tr.SALES_ORDER, tr.CUST_TRX_CLASS_NAME, dun.financial_charge, dun.letter_name, dun.interest_amt, tr.cons_billing_number cons_billing_number, tr.org_id, tr.operating_unit, objects.name object_type_meaning FROM iex_delinquencies del, iex_dunnings dun, jtf_fm_template fm, ar_aging_bucket_lines_vl aging, jtf_fm_request_history ffm, iex_transactions_v tr, IEX_LOOKUPS_V DUN_LK, IEX_LOOKUPS_V DUN_LK2, IEX_LOOKUPS_V DUN_LK3, jtf_objects_vl objects, hz_parties h WHERE del.delinquency_id = dun.delinquency_id AND fm.template_id (+) = dun.template_id AND aging.aging_bucket_line_id (+)= del.aging_bucket_line_id AND tr.payment_schedule_id (+)= del.payment_schedule_id AND dun.dunning_level = 'DELINQUENCY' AND ffm.hist_req_id (+)= dun.ffm_request_id AND NVL(to_char(ffm.submit_dt_tm,'yymmddhhmi'),'x') = NVL( to_char(( select max(ffmb.submit_dt_tm) from jtf_Fm_request_history ffmb where ffmb.hist_req_id = dun.ffm_request_id), 'yymmddhhmi'), 'x') AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL' AND dun.dunning_level = DUN_LK.LOOKUP_CODE(+) AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD' AND dun.dunning_method = DUN_LK2.LOOKUP_CODE(+) AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS' AND dun.status = DUN_LK3.LOOKUP_CODE(+) AND objects.object_code (+) = dun.object_type AND nvl(objects.START_DATE_ACTIVE, sysdate) <= nvl(objects.END_DATE_ACTIVE,SYSDATE) AND h.party_id = del.party_cust_id UNION ALL SELECT to_number('') delinquency_id, dun.dunning_id dunning_id, dun.status status, dun_lk3.meaning status_meaning, dun.creation_date creation_date, fm.template_name template_name, to_number('') aging_bucket_line_id, ha.party_id party_cust_id, h.party_name, dun.dunning_object_id account_id, to_number('') customer_site_use_ID, ' ' bucket_line, dun.dunning_method dunning_method, dun_lk2.meaning dunning_method_meaning, dun.amount_due_remaining amount_due_remaining, dun.currency_code currency_code, dun.object_id object_id, dun.object_type object_type, dun.dunning_object_id dunning_object_id, dun.dunning_level dunning_level, dun_lk.meaning dunning_level_meaning, dun.ffm_request_id ffm_request_id, ffm.outcome_code ffm_status, to_number('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, to_number('') terms_sequence_number, to_date('','yymmdd') due_date, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, dun.financial_charge, dun.letter_name, dun.interest_amt, '' CONS_BILLING_NUMBER, to_number('') ORG_ID, '' OPERATING_UNIT, objects.name object_type_meaning FROM iex_dunnings dun, jtf_fm_template fm, jtf_fm_request_history ffm, IEX_LOOKUPS_V DUN_LK, IEX_LOOKUPS_V DUN_LK2, IEX_LOOKUPS_V DUN_LK3, jtf_objects_vl objects, hz_cust_accounts ha, hz_parties h WHERE fm.template_id (+) = dun.template_id AND dun.dunning_level = 'ACCOUNT' AND ffm.hist_req_id (+)= dun.ffm_request_id AND NVL(to_char(ffm.submit_dt_tm,'yymmddhhmi'),'x') = NVL( to_char(( select max(ffmb.submit_dt_tm) from jtf_Fm_request_history ffmb where ffmb.hist_req_id = dun.ffm_request_id), 'yymmddhhmi'), 'x') AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL' AND dun.dunning_level = DUN_LK.LOOKUP_CODE(+) AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD' AND dun.dunning_method = DUN_LK2.LOOKUP_CODE(+) AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS' AND dun.status = DUN_LK3.LOOKUP_CODE(+) AND objects.object_code (+) = dun.object_type AND nvl(objects.START_DATE_ACTIVE, sysdate) <= nvl(objects.END_DATE_ACTIVE,SYSDATE) AND ha.cust_account_id = dun.dunning_object_id AND h.party_id = ha.party_id UNION ALL SELECT to_number('') delinquency_id, dun.dunning_id dunning_id, dun.status status, dun_lk3.meaning status_meaning, dun.creation_date creation_date, fm.template_name template_name, to_number('') aging_bucket_line_id, dun.dunning_object_id party_cust_id, h.party_name, to_number('') account_id, to_number('') customer_site_use_ID, '' bucket_line, dun.dunning_method dunning_method, dun_lk2.meaning dunning_method_meaning, dun.amount_due_remaining amount_due_remaining, dun.currency_code currency_code, dun.object_id object_id, dun.object_type object_type, dun.dunning_object_id dunning_object_id, dun.dunning_level dunning_level, dun_lk.meaning dunning_level_meaning, dun.ffm_request_id ffm_request_id, ffm.outcome_code ffm_status, to_number('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, to_number('') terms_sequence_number, to_date('','yymmdd') due_date, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, dun.financial_charge, dun.letter_name, dun.interest_amt, '' CONS_BILLING_NUMBER, to_number('') ORG_ID, '' OPERATING_UNIT, objects.name object_type_meaning FROM iex_dunnings dun, jtf_fm_template fm, jtf_fm_request_history ffm, IEX_LOOKUPS_V DUN_LK, IEX_LOOKUPS_V DUN_LK2, IEX_LOOKUPS_V DUN_LK3, jtf_objects_vl objects, hz_parties h WHERE fm.template_id (+) = dun.template_id AND dun.dunning_level = 'CUSTOMER' AND ffm.hist_req_id (+)= dun.ffm_request_id AND NVL(to_char(ffm.submit_dt_tm,'yymmddhhmi'),'x') = NVL( to_char(( select max(ffmb.submit_dt_tm) from jtf_Fm_request_history ffmb where ffmb.hist_req_id = dun.ffm_request_id), 'yymmddhhmi'), 'x') AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL' AND dun.dunning_level = DUN_LK.LOOKUP_CODE(+) AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD' AND dun.dunning_method = DUN_LK2.LOOKUP_CODE(+) AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS' AND dun.status = DUN_LK3.LOOKUP_CODE(+) AND objects.object_code (+) = dun.object_type AND nvl(objects.START_DATE_ACTIVE, sysdate) <= nvl(objects.END_DATE_ACTIVE,SYSDATE) AND h.party_id = dun.dunning_object_id UNION ALL SELECT to_number('') delinquency_id, dun.dunning_id dunning_id, dun.status status, dun_lk3.meaning status_meaning, dun.creation_date creation_date, fm.template_name template_name, to_number('') aging_bucket_line_id, ha.party_id party_cust_id, h.party_name, cs.cust_account_id account_id, dun.dunning_object_id customer_site_use_id, ' ' bucket_line, dun.dunning_method dunning_method, dun_lk2.meaning dunning_method_meaning, dun.amount_due_remaining amount_due_remaining, dun.currency_code currency_code, dun.object_id object_id, dun.object_type object_type, dun.dunning_object_id dunning_object_id, dun.dunning_level dunning_level, dun_lk.meaning dunning_level_meaning, dun.ffm_request_id ffm_request_id, ffm.outcome_code ffm_status, to_number('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, to_number('') terms_sequence_number, to_date('','yymmdd') due_date, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, dun.financial_charge, dun.letter_name, dun.interest_amt, '' CONS_BILLING_NUMBER, to_number('') ORG_ID, '' OPERATING_UNIT, objects.name object_type_meaning FROM iex_dunnings dun, jtf_fm_template fm, jtf_fm_request_history ffm, IEX_LOOKUPS_V DUN_LK, IEX_LOOKUPS_V DUN_LK2, IEX_LOOKUPS_V DUN_LK3, IEX_CUST_SITE_USES_V cs, jtf_objects_vl objects, hz_cust_accounts ha, hz_parties h WHERE fm.template_id (+) = dun.template_id AND dun.dunning_level = 'BILL_TO' AND dun.dunning_object_id = cs.customer_site_use_id AND ffm.hist_req_id (+)= dun.ffm_request_id AND NVL(to_char(ffm.submit_dt_tm,'yymmddhhmi'),'x') = NVL( to_char(( select max(ffmb.submit_dt_tm) from jtf_Fm_request_history ffmb where ffmb.hist_req_id = dun.ffm_request_id), 'yymmddhhmi'), 'x') AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL' AND dun.dunning_level = DUN_LK.LOOKUP_CODE(+) AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD' AND dun.dunning_method = DUN_LK2.LOOKUP_CODE(+) AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS' AND dun.status = DUN_LK3.LOOKUP_CODE(+) AND objects.object_code (+) = dun.object_type AND nvl(objects.START_DATE_ACTIVE, sysdate) <= nvl(objects.END_DATE_ACTIVE,SYSDATE) AND ha.cust_account_id = cs.cust_account_id AND h.party_id = ha.party_id union all ( select null delinquency_id, null dunning_id, 'CLOSE' status, 'Close' status_meaning, a.dunning_date creation_date, null template_name, null aging_bucket_line_id, e.party_id party_cust_id, h.party_name, c.customer_id account_id, c.site_use_id customer_site_use_id, null bucket_line, 'PRINTER' dunning_method, 'Print' dunning_method_meaning, sum(b.amount_due_remaining) amount_due_remaining, d.invoice_currency_code currency_code, a.correspondence_id object_id, 'From AR' object_type, decode(c.site_use_id, null, c.site_use_id, c.customer_id) dunning_object_id, decode(c.site_use_id, null, 'ACCOUNT', 'BILL TO') dunning_level, decode(c.site_use_id, null, 'Account', 'Bill To') dunning_level_meaning, null ffm_request_id, null ffm_status, a.payment_schedule_id payment_schedule_id, null trx_number, null terms_sequence_number, null due_date, null purchase_order, null sales_order, null cust_trx_class_name, sum(b.amount_accrue) financial_charge, a.letter_name letter_name, a.interest_amt interest_amt, g.cons_billing_number, c.org_id org_id, f.name operating_unit, '' object_type_meaning from ar_dunning_history_v a, ar_correspondence_pay_sched b , ar_correspondences c, ar_payment_schedules d, hz_cust_accounts e, hr_all_organization_units_vl f, ar_cons_inv g, hz_parties h where a.correspondence_id = b.correspondence_id and a.payment_schedule_id = b.payment_schedule_id and a.correspondence_id = c.correspondence_id and a.payment_schedule_id = d.payment_schedule_id and c.customer_id = e.cust_account_id and c.org_id = f.organization_id(+) and d.cons_inv_id = g.cons_inv_id(+) and h.party_id = e.party_id group by a.dunning_date, e.party_id, h.party_name, a.interest_amt, a.letter_name, a.payment_schedule_id, a.correspondence_id, d.invoice_currency_code, c.customer_id, c.site_use_id, c.org_id, f.name, g.cons_billing_number )

View Text - HTML Formatted

SELECT DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, DUN_LK3.MEANING STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, FM.TEMPLATE_NAME TEMPLATE_NAME
, DEL.AGING_BUCKET_LINE_ID AGING_BUCKET_LINE_ID
, DEL.PARTY_CUST_ID PARTY_CUST_ID
, H.PARTY_NAME
, DEL.CUST_ACCOUNT_ID ACCOUNT_ID
, DEL.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, AGING.REPORT_HEADING1 || AGING.REPORT_HEADING2 BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, DUN_LK2.MEANING DUNNING_METHOD_MEANING
, DUN.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, DUN.CURRENCY_CODE CURRENCY_CODE
, DUN.OBJECT_ID OBJECT_ID
, DUN.OBJECT_TYPE OBJECT_TYPE
, DUN.DUNNING_OBJECT_ID DUNNING_OBJECT_ID
, DUN.DUNNING_LEVEL DUNNING_LEVEL
, DUN_LK.MEANING DUNNING_LEVEL_MEANING
, DUN.FFM_REQUEST_ID FFM_REQUEST_ID
, FFM.OUTCOME_CODE FFM_STATUS
, DEL.PAYMENT_SCHEDULE_ID
, TR.TRX_NUMBER
, TR.TERMS_SEQUENCE_NUMBER
, TR.DUE_DATE
, TR.PURCHASE_ORDER
, TR.SALES_ORDER
, TR.CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE
, DUN.LETTER_NAME
, DUN.INTEREST_AMT
, TR.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, TR.ORG_ID
, TR.OPERATING_UNIT
, OBJECTS.NAME OBJECT_TYPE_MEANING
FROM IEX_DELINQUENCIES DEL
, IEX_DUNNINGS DUN
, JTF_FM_TEMPLATE FM
, AR_AGING_BUCKET_LINES_VL AGING
, JTF_FM_REQUEST_HISTORY FFM
, IEX_TRANSACTIONS_V TR
, IEX_LOOKUPS_V DUN_LK
, IEX_LOOKUPS_V DUN_LK2
, IEX_LOOKUPS_V DUN_LK3
, JTF_OBJECTS_VL OBJECTS
, HZ_PARTIES H
WHERE DEL.DELINQUENCY_ID = DUN.DELINQUENCY_ID
AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_ID
AND AGING.AGING_BUCKET_LINE_ID (+)= DEL.AGING_BUCKET_LINE_ID
AND TR.PAYMENT_SCHEDULE_ID (+)= DEL.PAYMENT_SCHEDULE_ID
AND DUN.DUNNING_LEVEL = 'DELINQUENCY'
AND FFM.HIST_REQ_ID (+)= DUN.FFM_REQUEST_ID
AND NVL(TO_CHAR(FFM.SUBMIT_DT_TM
, 'YYMMDDHHMI')
, 'X') = NVL( TO_CHAR(( SELECT MAX(FFMB.SUBMIT_DT_TM)
FROM JTF_FM_REQUEST_HISTORY FFMB
WHERE FFMB.HIST_REQ_ID = DUN.FFM_REQUEST_ID)
, 'YYMMDDHHMI')
, 'X')
AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL'
AND DUN.DUNNING_LEVEL = DUN_LK.LOOKUP_CODE(+)
AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD'
AND DUN.DUNNING_METHOD = DUN_LK2.LOOKUP_CODE(+)
AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS'
AND DUN.STATUS = DUN_LK3.LOOKUP_CODE(+)
AND OBJECTS.OBJECT_CODE (+) = DUN.OBJECT_TYPE
AND NVL(OBJECTS.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS.END_DATE_ACTIVE
, SYSDATE)
AND H.PARTY_ID = DEL.PARTY_CUST_ID UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, DUN_LK3.MEANING STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, FM.TEMPLATE_NAME TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, HA.PARTY_ID PARTY_CUST_ID
, H.PARTY_NAME
, DUN.DUNNING_OBJECT_ID ACCOUNT_ID
, TO_NUMBER('') CUSTOMER_SITE_USE_ID
, ' ' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, DUN_LK2.MEANING DUNNING_METHOD_MEANING
, DUN.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, DUN.CURRENCY_CODE CURRENCY_CODE
, DUN.OBJECT_ID OBJECT_ID
, DUN.OBJECT_TYPE OBJECT_TYPE
, DUN.DUNNING_OBJECT_ID DUNNING_OBJECT_ID
, DUN.DUNNING_LEVEL DUNNING_LEVEL
, DUN_LK.MEANING DUNNING_LEVEL_MEANING
, DUN.FFM_REQUEST_ID FFM_REQUEST_ID
, FFM.OUTCOME_CODE FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE
, DUN.LETTER_NAME
, DUN.INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS.NAME OBJECT_TYPE_MEANING
FROM IEX_DUNNINGS DUN
, JTF_FM_TEMPLATE FM
, JTF_FM_REQUEST_HISTORY FFM
, IEX_LOOKUPS_V DUN_LK
, IEX_LOOKUPS_V DUN_LK2
, IEX_LOOKUPS_V DUN_LK3
, JTF_OBJECTS_VL OBJECTS
, HZ_CUST_ACCOUNTS HA
, HZ_PARTIES H
WHERE FM.TEMPLATE_ID (+) = DUN.TEMPLATE_ID
AND DUN.DUNNING_LEVEL = 'ACCOUNT'
AND FFM.HIST_REQ_ID (+)= DUN.FFM_REQUEST_ID
AND NVL(TO_CHAR(FFM.SUBMIT_DT_TM
, 'YYMMDDHHMI')
, 'X') = NVL( TO_CHAR(( SELECT MAX(FFMB.SUBMIT_DT_TM)
FROM JTF_FM_REQUEST_HISTORY FFMB
WHERE FFMB.HIST_REQ_ID = DUN.FFM_REQUEST_ID)
, 'YYMMDDHHMI')
, 'X')
AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL'
AND DUN.DUNNING_LEVEL = DUN_LK.LOOKUP_CODE(+)
AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD'
AND DUN.DUNNING_METHOD = DUN_LK2.LOOKUP_CODE(+)
AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS'
AND DUN.STATUS = DUN_LK3.LOOKUP_CODE(+)
AND OBJECTS.OBJECT_CODE (+) = DUN.OBJECT_TYPE
AND NVL(OBJECTS.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS.END_DATE_ACTIVE
, SYSDATE)
AND HA.CUST_ACCOUNT_ID = DUN.DUNNING_OBJECT_ID
AND H.PARTY_ID = HA.PARTY_ID UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, DUN_LK3.MEANING STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, FM.TEMPLATE_NAME TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, DUN.DUNNING_OBJECT_ID PARTY_CUST_ID
, H.PARTY_NAME
, TO_NUMBER('') ACCOUNT_ID
, TO_NUMBER('') CUSTOMER_SITE_USE_ID
, '' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, DUN_LK2.MEANING DUNNING_METHOD_MEANING
, DUN.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, DUN.CURRENCY_CODE CURRENCY_CODE
, DUN.OBJECT_ID OBJECT_ID
, DUN.OBJECT_TYPE OBJECT_TYPE
, DUN.DUNNING_OBJECT_ID DUNNING_OBJECT_ID
, DUN.DUNNING_LEVEL DUNNING_LEVEL
, DUN_LK.MEANING DUNNING_LEVEL_MEANING
, DUN.FFM_REQUEST_ID FFM_REQUEST_ID
, FFM.OUTCOME_CODE FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE
, DUN.LETTER_NAME
, DUN.INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS.NAME OBJECT_TYPE_MEANING
FROM IEX_DUNNINGS DUN
, JTF_FM_TEMPLATE FM
, JTF_FM_REQUEST_HISTORY FFM
, IEX_LOOKUPS_V DUN_LK
, IEX_LOOKUPS_V DUN_LK2
, IEX_LOOKUPS_V DUN_LK3
, JTF_OBJECTS_VL OBJECTS
, HZ_PARTIES H
WHERE FM.TEMPLATE_ID (+) = DUN.TEMPLATE_ID
AND DUN.DUNNING_LEVEL = 'CUSTOMER'
AND FFM.HIST_REQ_ID (+)= DUN.FFM_REQUEST_ID
AND NVL(TO_CHAR(FFM.SUBMIT_DT_TM
, 'YYMMDDHHMI')
, 'X') = NVL( TO_CHAR(( SELECT MAX(FFMB.SUBMIT_DT_TM)
FROM JTF_FM_REQUEST_HISTORY FFMB
WHERE FFMB.HIST_REQ_ID = DUN.FFM_REQUEST_ID)
, 'YYMMDDHHMI')
, 'X')
AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL'
AND DUN.DUNNING_LEVEL = DUN_LK.LOOKUP_CODE(+)
AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD'
AND DUN.DUNNING_METHOD = DUN_LK2.LOOKUP_CODE(+)
AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS'
AND DUN.STATUS = DUN_LK3.LOOKUP_CODE(+)
AND OBJECTS.OBJECT_CODE (+) = DUN.OBJECT_TYPE
AND NVL(OBJECTS.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS.END_DATE_ACTIVE
, SYSDATE)
AND H.PARTY_ID = DUN.DUNNING_OBJECT_ID UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, DUN_LK3.MEANING STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, FM.TEMPLATE_NAME TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, HA.PARTY_ID PARTY_CUST_ID
, H.PARTY_NAME
, CS.CUST_ACCOUNT_ID ACCOUNT_ID
, DUN.DUNNING_OBJECT_ID CUSTOMER_SITE_USE_ID
, ' ' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, DUN_LK2.MEANING DUNNING_METHOD_MEANING
, DUN.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, DUN.CURRENCY_CODE CURRENCY_CODE
, DUN.OBJECT_ID OBJECT_ID
, DUN.OBJECT_TYPE OBJECT_TYPE
, DUN.DUNNING_OBJECT_ID DUNNING_OBJECT_ID
, DUN.DUNNING_LEVEL DUNNING_LEVEL
, DUN_LK.MEANING DUNNING_LEVEL_MEANING
, DUN.FFM_REQUEST_ID FFM_REQUEST_ID
, FFM.OUTCOME_CODE FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE
, DUN.LETTER_NAME
, DUN.INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS.NAME OBJECT_TYPE_MEANING
FROM IEX_DUNNINGS DUN
, JTF_FM_TEMPLATE FM
, JTF_FM_REQUEST_HISTORY FFM
, IEX_LOOKUPS_V DUN_LK
, IEX_LOOKUPS_V DUN_LK2
, IEX_LOOKUPS_V DUN_LK3
, IEX_CUST_SITE_USES_V CS
, JTF_OBJECTS_VL OBJECTS
, HZ_CUST_ACCOUNTS HA
, HZ_PARTIES H
WHERE FM.TEMPLATE_ID (+) = DUN.TEMPLATE_ID
AND DUN.DUNNING_LEVEL = 'BILL_TO'
AND DUN.DUNNING_OBJECT_ID = CS.CUSTOMER_SITE_USE_ID
AND FFM.HIST_REQ_ID (+)= DUN.FFM_REQUEST_ID
AND NVL(TO_CHAR(FFM.SUBMIT_DT_TM
, 'YYMMDDHHMI')
, 'X') = NVL( TO_CHAR(( SELECT MAX(FFMB.SUBMIT_DT_TM)
FROM JTF_FM_REQUEST_HISTORY FFMB
WHERE FFMB.HIST_REQ_ID = DUN.FFM_REQUEST_ID)
, 'YYMMDDHHMI')
, 'X')
AND DUN_LK.LOOKUP_TYPE(+) = 'IEX_RUNNING_LEVEL'
AND DUN.DUNNING_LEVEL = DUN_LK.LOOKUP_CODE(+)
AND DUN_LK2.LOOKUP_TYPE(+) = 'IEX_FULFILLMENT_SEND_METHOD'
AND DUN.DUNNING_METHOD = DUN_LK2.LOOKUP_CODE(+)
AND DUN_LK3.LOOKUP_TYPE(+) = 'IEX_DUNNING_STATUS'
AND DUN.STATUS = DUN_LK3.LOOKUP_CODE(+)
AND OBJECTS.OBJECT_CODE (+) = DUN.OBJECT_TYPE
AND NVL(OBJECTS.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS.END_DATE_ACTIVE
, SYSDATE)
AND HA.CUST_ACCOUNT_ID = CS.CUST_ACCOUNT_ID
AND H.PARTY_ID = HA.PARTY_ID UNION ALL ( SELECT NULL DELINQUENCY_ID
, NULL DUNNING_ID
, 'CLOSE' STATUS
, 'CLOSE' STATUS_MEANING
, A.DUNNING_DATE CREATION_DATE
, NULL TEMPLATE_NAME
, NULL AGING_BUCKET_LINE_ID
, E.PARTY_ID PARTY_CUST_ID
, H.PARTY_NAME
, C.CUSTOMER_ID ACCOUNT_ID
, C.SITE_USE_ID CUSTOMER_SITE_USE_ID
, NULL BUCKET_LINE
, 'PRINTER' DUNNING_METHOD
, 'PRINT' DUNNING_METHOD_MEANING
, SUM(B.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, D.INVOICE_CURRENCY_CODE CURRENCY_CODE
, A.CORRESPONDENCE_ID OBJECT_ID
, 'FROM AR' OBJECT_TYPE
, DECODE(C.SITE_USE_ID
, NULL
, C.SITE_USE_ID
, C.CUSTOMER_ID) DUNNING_OBJECT_ID
, DECODE(C.SITE_USE_ID
, NULL
, 'ACCOUNT'
, 'BILL TO') DUNNING_LEVEL
, DECODE(C.SITE_USE_ID
, NULL
, 'ACCOUNT'
, 'BILL TO') DUNNING_LEVEL_MEANING
, NULL FFM_REQUEST_ID
, NULL FFM_STATUS
, A.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, NULL TRX_NUMBER
, NULL TERMS_SEQUENCE_NUMBER
, NULL DUE_DATE
, NULL PURCHASE_ORDER
, NULL SALES_ORDER
, NULL CUST_TRX_CLASS_NAME
, SUM(B.AMOUNT_ACCRUE) FINANCIAL_CHARGE
, A.LETTER_NAME LETTER_NAME
, A.INTEREST_AMT INTEREST_AMT
, G.CONS_BILLING_NUMBER
, C.ORG_ID ORG_ID
, F.NAME OPERATING_UNIT
, '' OBJECT_TYPE_MEANING
FROM AR_DUNNING_HISTORY_V A
, AR_CORRESPONDENCE_PAY_SCHED B
, AR_CORRESPONDENCES C
, AR_PAYMENT_SCHEDULES D
, HZ_CUST_ACCOUNTS E
, HR_ALL_ORGANIZATION_UNITS_VL F
, AR_CONS_INV G
, HZ_PARTIES H
WHERE A.CORRESPONDENCE_ID = B.CORRESPONDENCE_ID
AND A.PAYMENT_SCHEDULE_ID = B.PAYMENT_SCHEDULE_ID
AND A.CORRESPONDENCE_ID = C.CORRESPONDENCE_ID
AND A.PAYMENT_SCHEDULE_ID = D.PAYMENT_SCHEDULE_ID
AND C.CUSTOMER_ID = E.CUST_ACCOUNT_ID
AND C.ORG_ID = F.ORGANIZATION_ID(+)
AND D.CONS_INV_ID = G.CONS_INV_ID(+)
AND H.PARTY_ID = E.PARTY_ID GROUP BY A.DUNNING_DATE
, E.PARTY_ID
, H.PARTY_NAME
, A.INTEREST_AMT
, A.LETTER_NAME
, A.PAYMENT_SCHEDULE_ID
, A.CORRESPONDENCE_ID
, D.INVOICE_CURRENCY_CODE
, C.CUSTOMER_ID
, C.SITE_USE_ID
, C.ORG_ID
, F.NAME
, G.CONS_BILLING_NUMBER )