VIEW: APPS.IEX_DUNNINGS_BALI_V

Source

View Text - Preformatted

SELECT TO_NUMBER('') DELINQUENCY_ID, DUN.DUNNING_ID DUNNING_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, DUN.CREATION_DATE CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, TO_NUMBER('') AGING_BUCKET_LINE_ID, HZP1.PARTY_ID PARTY_CUST_ID, DUN.DUNNING_OBJECT_ID ACCOUNT_ID, TO_NUMBER('') CUSTOMER_SITE_USE_ID, ' ' BUCKET_LINE, DUN.DUNNING_METHOD DUNNING_METHOD, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD',DUN.DUNNING_METHOD) 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, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL',DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING, NVL(DUN.FFM_REQUEST_ID,DUN.xml_request_id) FFM_REQUEST_ID, NVL(FFM.OUTCOME_CODE,initcap(xml.status)) FFM_STATUS, TO_NUMBER('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, TO_NUMBER('') TERMS_SEQUENCE_NUMBER, TO_DATE('','YYMMDD') DUE_DATE, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE, DUN.LETTER_NAME LETTER_NAME, DUN.INTEREST_AMT INTEREST_AMT, '' CONS_BILLING_NUMBER, TO_NUMBER('') ORG_ID, '' OPERATING_UNIT, OBJECTS_T.NAME OBJECT_TYPE_MEANING, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, DUN.CONTACT_DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID FROM IEX_DUNNINGS DUN, JTF_OBJECTS_B OBJECTS_B, JTF_OBJECTS_TL OBJECTS_T, HZ_CUST_ACCOUNTS HA, JTF_FM_TEMPLATE_all FM, JTF_FM_REQUEST_HISTORY_all FFM, XDO_TEMPLATES_B XDOB, XDO_TEMPLATES_TL XDOT, IEX_XML_REQUEST_HISTORIES XML, IEX_DUNNING_PLANS_tL DPL, HZ_PARTIES HZP , HZ_PARTIES HZP1 WHERE DUN.DUNNING_LEVEL = 'ACCOUNT' AND HA.CUST_ACCOUNT_ID = DUN.DUNNING_OBJECT_ID AND OBJECTS_B.OBJECT_CODE (+) = DUN.OBJECT_TYPE AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+) and OBJECTS_T.LANGUAGE(+) = userenv('LANG') AND NVL(OBJECTS_B.START_DATE_ACTIVE, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE,SYSDATE) AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE AND XDOT.LANGUAGE(+) = USERENV('LANG') AND dun.xml_request_id = xml.xml_request_id(+) AND (dun.template_id is not null or dun.request_id is not null or dun.xml_request_id is not null or dun.xml_template_id is not null) AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+) and dpl.language(+)=userenv('LANG') AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID AND HA.PARTY_ID=HZP1.PARTY_ID UNION ALL SELECT DUN.DELINQUENCY_ID DELINQUENCY_ID, DUN.DUNNING_ID DUNNING_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS',DUN.STATUS) STATUS_MEANING, DUN.CREATION_DATE CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, DEL.AGING_BUCKET_LINE_ID AGING_BUCKET_LINE_ID, hzp1.party_id PARTY_CUST_ID, hca.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, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD',DUN.DUNNING_METHOD) 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, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL',DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING, NVL(DUN.FFM_REQUEST_ID,DUN.xml_request_id) FFM_REQUEST_ID, NVL(FFM.OUTCOME_CODE,initcap(xml.status)) FFM_STATUS, DEL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, ct.TRX_NUMBER TRX_NUMBER, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, ps.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, ps.DUE_DATE DUE_DATE, ct.PURCHASE_ORDER PURCHASE_ORDER, '' SALES_ORDER, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS) CUST_TRX_CLASS_NAME, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE, DUN.LETTER_NAME LETTER_NAME, DUN.INTEREST_AMT INTEREST_AMT, cons.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, ps.ORG_ID ORG_ID, op_unit.name OPERATING_UNIT, OBJECTS_T.NAME OBJECT_TYPE_MEANING, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, DUN.CONTACT_DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID FROM IEX_DUNNINGS DUN, IEX_DELINQUENCIES DEL, AR_AGING_BUCKET_LINES_TL AGING, JTF_OBJECTS_B OBJECTS_B, JTF_OBJECTS_TL OBJECTS_T, JTF_FM_TEMPLATE_all FM, JTF_FM_REQUEST_HISTORY_all FFM, XDO_TEMPLATES_B XDOB, XDO_TEMPLATES_TL XDOT, iex_xml_request_histories xml, IEX_DUNNING_PLANS_tL DPL, HZ_PARTIES HZP , AR_PAYMENT_SCHEDULES PS , RA_CUSTOMER_TRX CT , AR_CONS_INV CONS , HR_ALL_ORGANIZATION_UNITS op_unit, hz_parties hzp1, hz_cust_accounts hca WHERE DUN.DUNNING_LEVEL = 'DELINQUENCY' AND DEL.DELINQUENCY_ID = DUN.DELINQUENCY_ID AND AGING.AGING_BUCKET_LINE_ID(+) = DEL.AGING_BUCKET_LINE_ID AND AGING.LANGUAGE(+) = userenv('LANG') AND ps.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+) and OBJECTS_T.LANGUAGE(+) = userenv('LANG') AND NVL(OBJECTS_B.START_DATE_ACTIVE, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE,SYSDATE) AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE AND XDOT.LANGUAGE(+) = USERENV('LANG') AND dun.xml_request_id = xml.xml_request_id(+) AND (dun.template_id is not null or dun.request_id is not null or dun.xml_request_id is not null or dun.xml_template_id is not null) AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+) and dpl.language(+)=userenv('LANG') AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID and PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID AND PS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+) AND HZP1.PARTY_ID=DEL.PARTY_CUST_ID and HZP1.PARTY_ID=DEL.PARTY_CUST_ID+0 and hca.cust_account_id=del.cust_account_id and hca.cust_account_id=del.cust_account_id+0 UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID, DUN.DUNNING_ID DUNNING_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, DUN.CREATION_DATE CREATION_DATE, COALESCE(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, TO_NUMBER('') AGING_BUCKET_LINE_ID, DUN.DUNNING_OBJECT_ID PARTY_CUST_ID, TO_NUMBER('') ACCOUNT_ID, TO_NUMBER('') CUSTOMER_SITE_USE_ID, '' BUCKET_LINE, DUN.DUNNING_METHOD DUNNING_METHOD, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD',DUN.DUNNING_METHOD) 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, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL',DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING, NVL(DUN.FFM_REQUEST_ID,DUN.xml_request_id) FFM_REQUEST_ID, NVL(FFM.OUTCOME_CODE,initcap(xml.status)) FFM_STATUS, TO_NUMBER('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, TO_NUMBER('') TERMS_SEQUENCE_NUMBER, TO_DATE('','YYMMDD') DUE_DATE, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE, DUN.LETTER_NAME LETTER_NAME, DUN.INTEREST_AMT INTEREST_AMT, '' CONS_BILLING_NUMBER, TO_NUMBER('') ORG_ID, '' OPERATING_UNIT, OBJECTS_T.NAME OBJECT_TYPE_MEANING, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, DUN.CONTACT_DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID FROM IEX_DUNNINGS DUN, JTF_OBJECTS_B OBJECTS_B, JTF_OBJECTS_TL OBJECTS_T, JTF_FM_TEMPLATE_all FM, JTF_FM_REQUEST_HISTORY_all FFM, XDO_TEMPLATES_B XDOB, XDO_TEMPLATES_TL XDOT, iex_xml_request_histories xml, IEX_DUNNING_PLANS_tL DPL, HZ_PARTIES HZP WHERE DUN.DUNNING_LEVEL = 'CUSTOMER' AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+) and OBJECTS_T.LANGUAGE(+) = userenv('LANG') AND NVL(OBJECTS_B.START_DATE_ACTIVE, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE,SYSDATE) AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE AND XDOT.LANGUAGE(+) = USERENV('LANG') AND dun.xml_request_id = xml.xml_request_id(+) AND (dun.template_id is not null or dun.request_id is not null or dun.xml_request_id is not null or dun.xml_template_id is not null) AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+) and dpl.language(+)=userenv('LANG') AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID, DUN.DUNNING_ID DUNNING_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, DUN.CREATION_DATE CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, TO_NUMBER('') AGING_BUCKET_LINE_ID, HZP1.PARTY_ID PARTY_CUST_ID, HA1.CUST_ACCOUNT_ID ACCOUNT_ID, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID, ' ' BUCKET_LINE, DUN.DUNNING_METHOD DUNNING_METHOD, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD',DUN.DUNNING_METHOD) 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, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL',DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING, NVL(DUN.FFM_REQUEST_ID,DUN.xml_request_id) FFM_REQUEST_ID, NVL(FFM.OUTCOME_CODE,initcap(xml.status)) FFM_STATUS, TO_NUMBER('') PAYMENT_SCHEDULE_ID, '' TRX_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, TO_NUMBER('') TERMS_SEQUENCE_NUMBER, TO_DATE('','YYMMDD') DUE_DATE, '' PURCHASE_ORDER, '' SALES_ORDER, '' CUST_TRX_CLASS_NAME, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE, DUN.LETTER_NAME LETTER_NAME, DUN.INTEREST_AMT INTEREST_AMT, '' CONS_BILLING_NUMBER, TO_NUMBER('') ORG_ID, '' OPERATING_UNIT, OBJECTS_T.NAME OBJECT_TYPE_MEANING, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, DUN.CONTACT_DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID FROM JTF_OBJECTS_B OBJECTS_B, JTF_OBJECTS_TL OBJECTS_T, HZ_CUST_ACCOUNTS HA1, JTF_FM_TEMPLATE_all FM, JTF_FM_REQUEST_HISTORY_all FFM, XDO_TEMPLATES_B XDOB, XDO_TEMPLATES_TL XDOT, iex_xml_request_histories xml, IEX_DUNNING_PLANS_tL DPL, HZ_PARTIES HZP , HZ_CUST_ACCT_SITES ACCT_SITE , HZ_CUST_SITE_USES SITE_USES, HZ_PARTIES HZP1, IEX_DUNNINGS DUN WHERE DUN.DUNNING_LEVEL = 'BILL_TO' AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID AND HA1.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID AND HA1.PARTY_ID = HZP1.PARTY_ID AND DUN.DUNNING_OBJECT_ID = SITE_USES.SITE_USE_ID AND SITE_USES.SITE_USE_CODE = 'BILL_TO' AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+) and OBJECTS_T.LANGUAGE(+) = userenv('LANG') AND NVL(OBJECTS_B.START_DATE_ACTIVE, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE,SYSDATE) AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE AND XDOT.LANGUAGE(+) = USERENV('LANG') AND dun.xml_request_id = xml.xml_request_id(+) AND (dun.template_id is not null or dun.request_id is not null or dun.xml_request_id is not null or dun.xml_template_id is not null) AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+) and dpl.language(+)=userenv('LANG') AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID UNION ALL (SELECT NULL DELINQUENCY_ID, NULL DUNNING_ID, 'CLOSE' STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_AR_DUNNING', 'CLOSE') STATUS_MEANING, COR.CORRESPONDENCE_DATE CREATION_DATE, NULL TEMPLATE_NAME, NULL AGING_BUCKET_LINE_ID, E.PARTY_ID PARTY_CUST_ID, cor.CUSTOMER_ID ACCOUNT_ID, COR.SITE_USE_ID CUSTOMER_SITE_USE_ID, NULL BUCKET_LINE, 'PRINTER' DUNNING_METHOD, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_AR_DUNNING', 'PRINTER') DUNNING_METHOD_MEANING, SUM(cps.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, cor.CORRESPONDENCE_ID OBJECT_ID, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_AR_DUNNING','FROM AR') OBJECT_TYPE, DECODE(COR.SITE_USE_ID, NULL, COR.SITE_USE_ID, cor.CUSTOMER_ID) DUNNING_OBJECT_ID, DECODE(COR.SITE_USE_ID, NULL, 'ACCOUNT', 'BILL TO') DUNNING_LEVEL, DECODE(COR.SITE_USE_ID, NULL, 'ACCOUNT', 'BILL TO') DUNNING_LEVEL_MEANING, To_Number(NULL) FFM_REQUEST_ID, NULL FFM_STATUS, ps.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, NULL TRX_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, NULL TERMS_SEQUENCE_NUMBER, NULL DUE_DATE, NULL PURCHASE_ORDER, NULL SALES_ORDER, NULL CUST_TRX_CLASS_NAME, SUM(cps.AMOUNT_ACCRUE) FINANCIAL_CHARGE, dl.LETTER_NAME LETTER_NAME, DECODE (SP.ACCRUE_INTEREST, 'Y',CPS.AMOUNT_ACCRUE, 'N', CPS.AMOUNT_UNACCRUE ) INTEREST_AMT, G.CONS_BILLING_NUMBER, cor.ORG_ID ORG_ID, F.NAME OPERATING_UNIT, '' OBJECT_TYPE_MEANING, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL CONTACT_DESTINATION, NULL CONTACT_PARTY_ID, NULL CONTACT_PARTY_NAME, NULL PARENT_DUNNING_ID FROM AR_CORRESPONDENCES COR , AR_DUNNING_LETTERS DL , AR_CORRESPONDENCE_PAY_SCHED CPS , AR_SYSTEM_PARAMETERS SP , AR_PAYMENT_SCHEDULES PS , HZ_CUST_ACCOUNTS E, HR_ALL_ORGANIZATION_UNITS_tL F, AR_CONS_INV G WHERE DL.DUNNING_LETTER_ID = COR.REFERENCE2 AND CPS.CORRESPONDENCE_ID = COR.CORRESPONDENCE_ID AND PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID AND COR.PRELIMINARY_FLAG = 'N' and cor.CUSTOMER_ID = E.CUST_ACCOUNT_ID AND cor.ORG_ID = F.ORGANIZATION_ID(+) AND f.LANGUAGE(+) = USERENV('LANG') and ps.CONS_INV_ID = G.CONS_INV_ID(+) and ps.org_id=sp.org_id GROUP BY COR.CORRESPONDENCE_DATE, E.PARTY_ID, DECODE (SP.ACCRUE_INTEREST, 'Y',CPS.AMOUNT_ACCRUE, 'N', CPS.AMOUNT_UNACCRUE ), dl.LETTER_NAME, ps.PAYMENT_SCHEDULE_ID, cor.CORRESPONDENCE_ID, PS.INVOICE_CURRENCY_CODE, cor.CUSTOMER_ID, COR.SITE_USE_ID, cor.ORG_ID, F.NAME, G.CONS_BILLING_NUMBER )

View Text - HTML Formatted

SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, HZP1.PARTY_ID PARTY_CUST_ID
, DUN.DUNNING_OBJECT_ID ACCOUNT_ID
, TO_NUMBER('') CUSTOMER_SITE_USE_ID
, ' ' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD'
, DUN.DUNNING_METHOD) 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
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL'
, DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING
, NVL(DUN.FFM_REQUEST_ID
, DUN.XML_REQUEST_ID) FFM_REQUEST_ID
, NVL(FFM.OUTCOME_CODE
, INITCAP(XML.STATUS)) FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE
, DUN.LETTER_NAME LETTER_NAME
, DUN.INTEREST_AMT INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS_T.NAME OBJECT_TYPE_MEANING
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, DUN.CONTACT_DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
FROM IEX_DUNNINGS DUN
, JTF_OBJECTS_B OBJECTS_B
, JTF_OBJECTS_TL OBJECTS_T
, HZ_CUST_ACCOUNTS HA
, JTF_FM_TEMPLATE_ALL FM
, JTF_FM_REQUEST_HISTORY_ALL FFM
, XDO_TEMPLATES_B XDOB
, XDO_TEMPLATES_TL XDOT
, IEX_XML_REQUEST_HISTORIES XML
, IEX_DUNNING_PLANS_TL DPL
, HZ_PARTIES HZP
, HZ_PARTIES HZP1
WHERE DUN.DUNNING_LEVEL = 'ACCOUNT'
AND HA.CUST_ACCOUNT_ID = DUN.DUNNING_OBJECT_ID
AND OBJECTS_B.OBJECT_CODE (+) = DUN.OBJECT_TYPE
AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+)
AND OBJECTS_T.LANGUAGE(+) = USERENV('LANG')
AND NVL(OBJECTS_B.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE
, SYSDATE)
AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID
AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME
AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE
AND XDOT.LANGUAGE(+) = USERENV('LANG')
AND DUN.XML_REQUEST_ID = XML.XML_REQUEST_ID(+)
AND (DUN.TEMPLATE_ID IS NOT NULL OR DUN.REQUEST_ID IS NOT NULL OR DUN.XML_REQUEST_ID IS NOT NULL OR DUN.XML_TEMPLATE_ID IS NOT NULL)
AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+)=USERENV('LANG')
AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID
AND HA.PARTY_ID=HZP1.PARTY_ID UNION ALL SELECT DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, DEL.AGING_BUCKET_LINE_ID AGING_BUCKET_LINE_ID
, HZP1.PARTY_ID PARTY_CUST_ID
, HCA.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
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD'
, DUN.DUNNING_METHOD) 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
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL'
, DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING
, NVL(DUN.FFM_REQUEST_ID
, DUN.XML_REQUEST_ID) FFM_REQUEST_ID
, NVL(FFM.OUTCOME_CODE
, INITCAP(XML.STATUS)) FFM_STATUS
, DEL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, CT.TRX_NUMBER TRX_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE DUE_DATE
, CT.PURCHASE_ORDER PURCHASE_ORDER
, '' SALES_ORDER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE
, DUN.LETTER_NAME LETTER_NAME
, DUN.INTEREST_AMT INTEREST_AMT
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, PS.ORG_ID ORG_ID
, OP_UNIT.NAME OPERATING_UNIT
, OBJECTS_T.NAME OBJECT_TYPE_MEANING
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, DUN.CONTACT_DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
FROM IEX_DUNNINGS DUN
, IEX_DELINQUENCIES DEL
, AR_AGING_BUCKET_LINES_TL AGING
, JTF_OBJECTS_B OBJECTS_B
, JTF_OBJECTS_TL OBJECTS_T
, JTF_FM_TEMPLATE_ALL FM
, JTF_FM_REQUEST_HISTORY_ALL FFM
, XDO_TEMPLATES_B XDOB
, XDO_TEMPLATES_TL XDOT
, IEX_XML_REQUEST_HISTORIES XML
, IEX_DUNNING_PLANS_TL DPL
, HZ_PARTIES HZP
, AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX CT
, AR_CONS_INV CONS
, HR_ALL_ORGANIZATION_UNITS OP_UNIT
, HZ_PARTIES HZP1
, HZ_CUST_ACCOUNTS HCA
WHERE DUN.DUNNING_LEVEL = 'DELINQUENCY'
AND DEL.DELINQUENCY_ID = DUN.DELINQUENCY_ID
AND AGING.AGING_BUCKET_LINE_ID(+) = DEL.AGING_BUCKET_LINE_ID
AND AGING.LANGUAGE(+) = USERENV('LANG')
AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID
AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE
AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+)
AND OBJECTS_T.LANGUAGE(+) = USERENV('LANG')
AND NVL(OBJECTS_B.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE
, SYSDATE)
AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID
AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME
AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE
AND XDOT.LANGUAGE(+) = USERENV('LANG')
AND DUN.XML_REQUEST_ID = XML.XML_REQUEST_ID(+)
AND (DUN.TEMPLATE_ID IS NOT NULL OR DUN.REQUEST_ID IS NOT NULL OR DUN.XML_REQUEST_ID IS NOT NULL OR DUN.XML_TEMPLATE_ID IS NOT NULL)
AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+)=USERENV('LANG')
AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID
AND PS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND HZP1.PARTY_ID=DEL.PARTY_CUST_ID
AND HZP1.PARTY_ID=DEL.PARTY_CUST_ID+0
AND HCA.CUST_ACCOUNT_ID=DEL.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID=DEL.CUST_ACCOUNT_ID+0 UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, COALESCE(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, DUN.DUNNING_OBJECT_ID PARTY_CUST_ID
, TO_NUMBER('') ACCOUNT_ID
, TO_NUMBER('') CUSTOMER_SITE_USE_ID
, '' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD'
, DUN.DUNNING_METHOD) 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
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL'
, DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING
, NVL(DUN.FFM_REQUEST_ID
, DUN.XML_REQUEST_ID) FFM_REQUEST_ID
, NVL(FFM.OUTCOME_CODE
, INITCAP(XML.STATUS)) FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE
, DUN.LETTER_NAME LETTER_NAME
, DUN.INTEREST_AMT INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS_T.NAME OBJECT_TYPE_MEANING
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, DUN.CONTACT_DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
FROM IEX_DUNNINGS DUN
, JTF_OBJECTS_B OBJECTS_B
, JTF_OBJECTS_TL OBJECTS_T
, JTF_FM_TEMPLATE_ALL FM
, JTF_FM_REQUEST_HISTORY_ALL FFM
, XDO_TEMPLATES_B XDOB
, XDO_TEMPLATES_TL XDOT
, IEX_XML_REQUEST_HISTORIES XML
, IEX_DUNNING_PLANS_TL DPL
, HZ_PARTIES HZP
WHERE DUN.DUNNING_LEVEL = 'CUSTOMER'
AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE
AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+)
AND OBJECTS_T.LANGUAGE(+) = USERENV('LANG')
AND NVL(OBJECTS_B.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE
, SYSDATE)
AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID
AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME
AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE
AND XDOT.LANGUAGE(+) = USERENV('LANG')
AND DUN.XML_REQUEST_ID = XML.XML_REQUEST_ID(+)
AND (DUN.TEMPLATE_ID IS NOT NULL OR DUN.REQUEST_ID IS NOT NULL OR DUN.XML_REQUEST_ID IS NOT NULL OR DUN.XML_TEMPLATE_ID IS NOT NULL)
AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+)=USERENV('LANG')
AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID UNION ALL SELECT TO_NUMBER('') DELINQUENCY_ID
, DUN.DUNNING_ID DUNNING_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, DUN.CREATION_DATE CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, TO_NUMBER('') AGING_BUCKET_LINE_ID
, HZP1.PARTY_ID PARTY_CUST_ID
, HA1.CUST_ACCOUNT_ID ACCOUNT_ID
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, ' ' BUCKET_LINE
, DUN.DUNNING_METHOD DUNNING_METHOD
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_FULFILLMENT_SEND_METHOD'
, DUN.DUNNING_METHOD) 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
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RUNNING_LEVEL'
, DUN.DUNNING_LEVEL) DUNNING_LEVEL_MEANING
, NVL(DUN.FFM_REQUEST_ID
, DUN.XML_REQUEST_ID) FFM_REQUEST_ID
, NVL(FFM.OUTCOME_CODE
, INITCAP(XML.STATUS)) FFM_STATUS
, TO_NUMBER('') PAYMENT_SCHEDULE_ID
, '' TRX_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, TO_NUMBER('') TERMS_SEQUENCE_NUMBER
, TO_DATE(''
, 'YYMMDD') DUE_DATE
, '' PURCHASE_ORDER
, '' SALES_ORDER
, '' CUST_TRX_CLASS_NAME
, DUN.FINANCIAL_CHARGE FINANCIAL_CHARGE
, DUN.LETTER_NAME LETTER_NAME
, DUN.INTEREST_AMT INTEREST_AMT
, '' CONS_BILLING_NUMBER
, TO_NUMBER('') ORG_ID
, '' OPERATING_UNIT
, OBJECTS_T.NAME OBJECT_TYPE_MEANING
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, DUN.CONTACT_DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
FROM JTF_OBJECTS_B OBJECTS_B
, JTF_OBJECTS_TL OBJECTS_T
, HZ_CUST_ACCOUNTS HA1
, JTF_FM_TEMPLATE_ALL FM
, JTF_FM_REQUEST_HISTORY_ALL FFM
, XDO_TEMPLATES_B XDOB
, XDO_TEMPLATES_TL XDOT
, IEX_XML_REQUEST_HISTORIES XML
, IEX_DUNNING_PLANS_TL DPL
, HZ_PARTIES HZP
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES SITE_USES
, HZ_PARTIES HZP1
, IEX_DUNNINGS DUN
WHERE DUN.DUNNING_LEVEL = 'BILL_TO'
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND HA1.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND HA1.PARTY_ID = HZP1.PARTY_ID
AND DUN.DUNNING_OBJECT_ID = SITE_USES.SITE_USE_ID
AND SITE_USES.SITE_USE_CODE = 'BILL_TO'
AND OBJECTS_B.OBJECT_CODE(+) = DUN.OBJECT_TYPE
AND OBJECTS_B.OBJECT_CODE = OBJECTS_T.OBJECT_CODE(+)
AND OBJECTS_T.LANGUAGE(+) = USERENV('LANG')
AND NVL(OBJECTS_B.START_DATE_ACTIVE
, SYSDATE) <= NVL(OBJECTS_B.END_DATE_ACTIVE
, SYSDATE)
AND FM.TEMPLATE_ID (+) = DUN.TEMPLATE_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 XDOB.TEMPLATE_ID(+) = DUN.XML_TEMPLATE_ID
AND XDOT.APPLICATION_SHORT_NAME(+) = XDOB.APPLICATION_SHORT_NAME
AND XDOT.TEMPLATE_CODE(+) = XDOB.TEMPLATE_CODE
AND XDOT.LANGUAGE(+) = USERENV('LANG')
AND DUN.XML_REQUEST_ID = XML.XML_REQUEST_ID(+)
AND (DUN.TEMPLATE_ID IS NOT NULL OR DUN.REQUEST_ID IS NOT NULL OR DUN.XML_REQUEST_ID IS NOT NULL OR DUN.XML_TEMPLATE_ID IS NOT NULL)
AND DUN.DUNNING_PLAN_ID=DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+)=USERENV('LANG')
AND HZP.PARTY_ID(+) = DUN.CONTACT_PARTY_ID UNION ALL (SELECT NULL DELINQUENCY_ID
, NULL DUNNING_ID
, 'CLOSE' STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_AR_DUNNING'
, 'CLOSE') STATUS_MEANING
, COR.CORRESPONDENCE_DATE CREATION_DATE
, NULL TEMPLATE_NAME
, NULL AGING_BUCKET_LINE_ID
, E.PARTY_ID PARTY_CUST_ID
, COR.CUSTOMER_ID ACCOUNT_ID
, COR.SITE_USE_ID CUSTOMER_SITE_USE_ID
, NULL BUCKET_LINE
, 'PRINTER' DUNNING_METHOD
, IEX_UTILITIES.GET_LOOKUP_MEANING( 'IEX_AR_DUNNING'
, 'PRINTER') DUNNING_METHOD_MEANING
, SUM(CPS.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, COR.CORRESPONDENCE_ID OBJECT_ID
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_AR_DUNNING'
, 'FROM AR') OBJECT_TYPE
, DECODE(COR.SITE_USE_ID
, NULL
, COR.SITE_USE_ID
, COR.CUSTOMER_ID) DUNNING_OBJECT_ID
, DECODE(COR.SITE_USE_ID
, NULL
, 'ACCOUNT'
, 'BILL TO') DUNNING_LEVEL
, DECODE(COR.SITE_USE_ID
, NULL
, 'ACCOUNT'
, 'BILL TO') DUNNING_LEVEL_MEANING
, TO_NUMBER(NULL) FFM_REQUEST_ID
, NULL FFM_STATUS
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, NULL TRX_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, NULL TERMS_SEQUENCE_NUMBER
, NULL DUE_DATE
, NULL PURCHASE_ORDER
, NULL SALES_ORDER
, NULL CUST_TRX_CLASS_NAME
, SUM(CPS.AMOUNT_ACCRUE) FINANCIAL_CHARGE
, DL.LETTER_NAME LETTER_NAME
, DECODE (SP.ACCRUE_INTEREST
, 'Y'
, CPS.AMOUNT_ACCRUE
, 'N'
, CPS.AMOUNT_UNACCRUE ) INTEREST_AMT
, G.CONS_BILLING_NUMBER
, COR.ORG_ID ORG_ID
, F.NAME OPERATING_UNIT
, '' OBJECT_TYPE_MEANING
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL CONTACT_DESTINATION
, NULL CONTACT_PARTY_ID
, NULL CONTACT_PARTY_NAME
, NULL PARENT_DUNNING_ID
FROM AR_CORRESPONDENCES COR
, AR_DUNNING_LETTERS DL
, AR_CORRESPONDENCE_PAY_SCHED CPS
, AR_SYSTEM_PARAMETERS SP
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS E
, HR_ALL_ORGANIZATION_UNITS_TL F
, AR_CONS_INV G
WHERE DL.DUNNING_LETTER_ID = COR.REFERENCE2
AND CPS.CORRESPONDENCE_ID = COR.CORRESPONDENCE_ID
AND PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID
AND COR.PRELIMINARY_FLAG = 'N'
AND COR.CUSTOMER_ID = E.CUST_ACCOUNT_ID
AND COR.ORG_ID = F.ORGANIZATION_ID(+)
AND F.LANGUAGE(+) = USERENV('LANG')
AND PS.CONS_INV_ID = G.CONS_INV_ID(+)
AND PS.ORG_ID=SP.ORG_ID GROUP BY COR.CORRESPONDENCE_DATE
, E.PARTY_ID
, DECODE (SP.ACCRUE_INTEREST
, 'Y'
, CPS.AMOUNT_ACCRUE
, 'N'
, CPS.AMOUNT_UNACCRUE )
, DL.LETTER_NAME
, PS.PAYMENT_SCHEDULE_ID
, COR.CORRESPONDENCE_ID
, PS.INVOICE_CURRENCY_CODE
, COR.CUSTOMER_ID
, COR.SITE_USE_ID
, COR.ORG_ID
, F.NAME
, G.CONS_BILLING_NUMBER )