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, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, NULL 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, dpb.dunning_type dunning_type, iex_utilities.get_lookup_meaning('IEX_DUNNING_PLAN_TYPE', dpb.dunning_type) dunning_type_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, NULL 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, XML.DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID , DECODE(DUN.DUNNING_MODE, 'DRAFT', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')) DUNNING_MODE, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE',DECODE(DUN.DUNNING_MODE, 'DRAFT',(DECODE(DUN.CONFIRMATION_MODE,NULL,'DRAFT','CONFIRMED','FINAL')) ,'FINAL')) CONFIRMATION_MODE, FND_MESSAGE.GET_STRING('IEX',DUN.DELIVERY_STATUS) DUNNING_ERROR , xml.document_type document_type_code, iex_utilities.get_lookup_meaning('IEX_EMAIL_MODE', xml.document_type) document_type_meaning, initcap(xml.addt_status) addt_status 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, iex_dunning_plans_b dpb, 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 dun.dunning_plan_id = dpb.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, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, NULL 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, dpb.dunning_type dunning_type, iex_utilities.get_lookup_meaning('IEX_DUNNING_PLAN_TYPE', dpb.dunning_type) dunning_type_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, NULL 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, XML.DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID, DECODE(DUN.DUNNING_MODE, 'DRAFT', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')) DUNNING_MODE, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE',DECODE(DUN.DUNNING_MODE, 'DRAFT',(DECODE(DUN.CONFIRMATION_MODE,NULL,'DRAFT','CONFIRMED','FINAL')) ,'FINAL')) CONFIRMATION_MODE, FND_MESSAGE.GET_STRING('IEX',DUN.DELIVERY_STATUS) DUNNING_ERROR, xml.document_type document_type_code, iex_utilities.get_lookup_meaning('IEX_EMAIL_MODE', xml.document_type) document_type_meaning, initcap(xml.addt_status) addt_status 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, iex_dunning_plans_b dpb, 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 dun.dunning_plan_id = dpb.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, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) CREATION_DATE, COALESCE(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, NULL 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, dpb.dunning_type dunning_type, iex_utilities.get_lookup_meaning('IEX_DUNNING_PLAN_TYPE', dpb.dunning_type) dunning_type_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, NULL 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, XML.DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID, DECODE(DUN.DUNNING_MODE, 'DRAFT', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')) DUNNING_MODE, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE',DECODE(DUN.DUNNING_MODE, 'DRAFT',(DECODE(DUN.CONFIRMATION_MODE,NULL,'DRAFT','CONFIRMED','FINAL')) ,'FINAL')) CONFIRMATION_MODE, FND_MESSAGE.GET_STRING('IEX',DUN.DELIVERY_STATUS) DUNNING_ERROR , xml.document_type document_type_code, iex_utilities.get_lookup_meaning('IEX_EMAIL_MODE', xml.document_type) document_type_meaning, initcap(xml.addt_status) addt_status 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, iex_dunning_plans_b dpb, 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 dun.dunning_plan_id = dpb.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, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) CREATION_DATE, NVL(FM.TEMPLATE_NAME,XDOT.TEMPLATE_NAME) TEMPLATE_NAME, NULL 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, dpb.dunning_type dunning_type, iex_utilities.get_lookup_meaning('IEX_DUNNING_PLAN_TYPE', dpb.dunning_type) dunning_type_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, NULL 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, XML.DESTINATION CONTACT_DESTINATION, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID, HZP.PARTY_NAME CONTACT_PARTY_NAME, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID, DECODE(DUN.DUNNING_MODE, 'DRAFT', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')) DUNNING_MODE, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE',DECODE(DUN.DUNNING_MODE, 'DRAFT',(DECODE(DUN.CONFIRMATION_MODE,NULL,'DRAFT','CONFIRMED','FINAL')) ,'FINAL')) CONFIRMATION_MODE, FND_MESSAGE.GET_STRING('IEX',DUN.DELIVERY_STATUS) DUNNING_ERROR, xml.document_type document_type_code, iex_utilities.get_lookup_meaning('IEX_EMAIL_MODE', xml.document_type) document_type_meaning, initcap(xml.addt_status) addt_status 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, iex_dunning_plans_b dpb, 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 dun.dunning_plan_id = dpb.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, dl.LETTER_NAME 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, TO_CHAR(NULL) dunning_type, TO_CHAR(NULL) dunning_type_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, NULL 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 , NULL DUNNING_MODE, NULL CONFIRMATION_MODE, NULL DUNNING_ERROR , NULL document_type_code, NULL document_type_meaning, NULL addt_status 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
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, NULL 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
, DPB.DUNNING_TYPE DUNNING_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_PLAN_TYPE'
, DPB.DUNNING_TYPE) DUNNING_TYPE_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
, NULL 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
, XML.DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'Y')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'N')) DUNNING_MODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE'
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, (DECODE(DUN.CONFIRMATION_MODE
, NULL
, 'DRAFT'
, 'CONFIRMED'
, 'FINAL'))
, 'FINAL')) CONFIRMATION_MODE
, FND_MESSAGE.GET_STRING('IEX'
, DUN.DELIVERY_STATUS) DUNNING_ERROR
, XML.DOCUMENT_TYPE DOCUMENT_TYPE_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_EMAIL_MODE'
, XML.DOCUMENT_TYPE) DOCUMENT_TYPE_MEANING
, INITCAP(XML.ADDT_STATUS) ADDT_STATUS
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
, IEX_DUNNING_PLANS_B DPB
, 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 DUN.DUNNING_PLAN_ID = DPB.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
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, NULL 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
, DPB.DUNNING_TYPE DUNNING_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_PLAN_TYPE'
, DPB.DUNNING_TYPE) DUNNING_TYPE_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
, NULL 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
, XML.DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'Y')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'N')) DUNNING_MODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE'
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, (DECODE(DUN.CONFIRMATION_MODE
, NULL
, 'DRAFT'
, 'CONFIRMED'
, 'FINAL'))
, 'FINAL')) CONFIRMATION_MODE
, FND_MESSAGE.GET_STRING('IEX'
, DUN.DELIVERY_STATUS) DUNNING_ERROR
, XML.DOCUMENT_TYPE DOCUMENT_TYPE_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_EMAIL_MODE'
, XML.DOCUMENT_TYPE) DOCUMENT_TYPE_MEANING
, INITCAP(XML.ADDT_STATUS) ADDT_STATUS
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
, IEX_DUNNING_PLANS_B DPB
, 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 DUN.DUNNING_PLAN_ID = DPB.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
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) CREATION_DATE
, COALESCE(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, NULL 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
, DPB.DUNNING_TYPE DUNNING_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_PLAN_TYPE'
, DPB.DUNNING_TYPE) DUNNING_TYPE_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
, NULL 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
, XML.DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'Y')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'N')) DUNNING_MODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE'
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, (DECODE(DUN.CONFIRMATION_MODE
, NULL
, 'DRAFT'
, 'CONFIRMED'
, 'FINAL'))
, 'FINAL')) CONFIRMATION_MODE
, FND_MESSAGE.GET_STRING('IEX'
, DUN.DELIVERY_STATUS) DUNNING_ERROR
, XML.DOCUMENT_TYPE DOCUMENT_TYPE_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_EMAIL_MODE'
, XML.DOCUMENT_TYPE) DOCUMENT_TYPE_MEANING
, INITCAP(XML.ADDT_STATUS) ADDT_STATUS
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
, IEX_DUNNING_PLANS_B DPB
, 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 DUN.DUNNING_PLAN_ID = DPB.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
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) CREATION_DATE
, NVL(FM.TEMPLATE_NAME
, XDOT.TEMPLATE_NAME) TEMPLATE_NAME
, NULL 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
, DPB.DUNNING_TYPE DUNNING_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_PLAN_TYPE'
, DPB.DUNNING_TYPE) DUNNING_TYPE_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
, NULL 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
, XML.DESTINATION CONTACT_DESTINATION
, DUN.CONTACT_PARTY_ID CONTACT_PARTY_ID
, HZP.PARTY_NAME CONTACT_PARTY_NAME
, DUN.PARENT_DUNNING_ID PARENT_DUNNING_ID
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'Y')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, 'N')) DUNNING_MODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_MODE'
, DECODE(DUN.DUNNING_MODE
, 'DRAFT'
, (DECODE(DUN.CONFIRMATION_MODE
, NULL
, 'DRAFT'
, 'CONFIRMED'
, 'FINAL'))
, 'FINAL')) CONFIRMATION_MODE
, FND_MESSAGE.GET_STRING('IEX'
, DUN.DELIVERY_STATUS) DUNNING_ERROR
, XML.DOCUMENT_TYPE DOCUMENT_TYPE_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_EMAIL_MODE'
, XML.DOCUMENT_TYPE) DOCUMENT_TYPE_MEANING
, INITCAP(XML.ADDT_STATUS) ADDT_STATUS
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
, IEX_DUNNING_PLANS_B DPB
, 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 DUN.DUNNING_PLAN_ID = DPB.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
, DL.LETTER_NAME 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
, TO_CHAR(NULL) DUNNING_TYPE
, TO_CHAR(NULL) DUNNING_TYPE_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
, NULL 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
, NULL DUNNING_MODE
, NULL CONFIRMATION_MODE
, NULL DUNNING_ERROR
, NULL DOCUMENT_TYPE_CODE
, NULL DOCUMENT_TYPE_MEANING
, NULL ADDT_STATUS
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 )