VIEW: APPS.IEX_TRX_DUNNINGS_BALI_V

Source

View Text - Preformatted

SELECT IDT.DUNNING_TRX_ID, 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, COALESCE(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, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID, SITE_USES.LOCATION LOCATION, NULL 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, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, PS.TRX_NUMBER TRX_NUMBER, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, PS.DUE_DATE DUE_DATE, NULL PURCHASE_ORDER, NULL 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, NULL CONS_BILLING_NUMBER, PS.ORG_ID ORG_ID, NULL 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_DUNNING_TRANSACTIONS IDT, IEX_DUNNINGS DUN, AR_PAYMENT_SCHEDULES PS , 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, HZ_PARTIES HZP1, HZ_CUST_ACCOUNTS HCA, HZ_CUST_ACCT_SITES ACCT_SITE , HZ_CUST_SITE_USES SITE_USES WHERE PS.PAYMENT_SCHEDULE_ID = IDT.PAYMENT_SCHEDULE_ID AND DUN.DUNNING_PLAN_ID = DPB.DUNNING_PLAN_ID AND IDT.DUNNING_ID = DECODE (DPB.DUNNING_TYPE,'STAGED_DUNNING' ,NVL (DUN.PARENT_DUNNING_ID ,DUN.DUNNING_ID) , DUN.DUNNING_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 HCA.PARTY_ID = HZP1.PARTY_ID AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID AND SITE_USES.CUST_ACCT_SITE_ID= ACCT_SITE.CUST_ACCT_SITE_ID AND PS.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID

View Text - HTML Formatted

SELECT IDT.DUNNING_TRX_ID
, 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
, COALESCE(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
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, SITE_USES.LOCATION LOCATION
, NULL 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
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE DUE_DATE
, NULL PURCHASE_ORDER
, NULL 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
, NULL CONS_BILLING_NUMBER
, PS.ORG_ID ORG_ID
, NULL 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_DUNNING_TRANSACTIONS IDT
, IEX_DUNNINGS DUN
, AR_PAYMENT_SCHEDULES PS
, 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
, HZ_PARTIES HZP1
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES SITE_USES
WHERE PS.PAYMENT_SCHEDULE_ID = IDT.PAYMENT_SCHEDULE_ID
AND DUN.DUNNING_PLAN_ID = DPB.DUNNING_PLAN_ID
AND IDT.DUNNING_ID = DECODE (DPB.DUNNING_TYPE
, 'STAGED_DUNNING'
, NVL (DUN.PARENT_DUNNING_ID
, DUN.DUNNING_ID)
, DUN.DUNNING_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 HCA.PARTY_ID = HZP1.PARTY_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE_USES.CUST_ACCT_SITE_ID= ACCT_SITE.CUST_ACCT_SITE_ID
AND PS.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID