VIEW: APPS.IEX_DUNNING_UWQ_V

Source

View Text - Preformatted

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION , OBJB. OBJECT_PARAMETERS||' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID='||DEL.ORG_ID IEU_OBJECT_PARAMETERS , '' IEU_MEDIA_TYPE_UUID , 'DUNNING_ID' IEU_PARAM_PK_COL , TO_CHAR(DUN.DUNNING_ID) IEU_PARAM_PK_VALUE , 1 RESOURCE_ID , DEL.PARTY_CUST_ID CUSTOMER_ID , PARTY.PARTY_NAME PARTY_NAME , PARTY.PERSON_FIRST_NAME FIRST_NAME , PARTY.PERSON_LAST_NAME LAST_NAME , PARTY.PARTY_TYPE PARTY_TYPE , IACC.ACCOUNT_NUMBER ACCOUNT_NUMBER , DECODE(RS.CATEGORY ,'EMPLOYEE', 'RS_INDIVIDUAL' ,'PARTNER','RS_PARTNER',NULL) RESOURCE_TYPE , DUN.DUNNING_ID , DUN.TEMPLATE_ID , DUN.DELINQUENCY_ID , DUN.CREATION_DATE DATE_TIME , DUN.STATUS STATUS , DUN.CAMPAIGN_SCHED_ID SOURCE_CODE_ID , DUN.LAST_UPDATE_DATE , DUN.CALLBACK_DATE , AGING.AGING_BUCKET_LINE_ID AGING_BUCKET , AABLV.REPORT_HEADING1 || AABLV.REPORT_HEADING2 BUCKET_NAME , AGING.FM_METHOD DUNNING_TYPE , TMP.TEMPLATE_NAME LETTER_NAME , DECODE(DUN.CALLBACK_YN, 'Y', 'CALLBACK', NULL) NEXT_ACTION, DEL.ORG_ID ORG_ID, HU.NAME OPERATING_UNIT_NAME FROM JTF_OBJECTS_B OBJB , IEX_DELINQUENCIES_ALL DEL ,HR_OPERATING_UNITS HU , IEX_DUNNINGS DUN , IEX_AG_DN_XREF AGING , (SELECT PARTY_ID , SCORE_VALUE , MAX(CREATION_DATE) CREATION_DATE FROM IEX_SCORE_HISTORIES GROUP BY PARTY_ID, SCORE_VALUE) SCORE , HZ_PARTIES PARTY , HZ_CUST_ACCOUNTS IACC , JTF_FM_TEMPLATE TMP , AR_AGING_BUCKET_LINES_VL AABLV , JTF_RS_RESOURCE_EXTNS RS WHERE DUN.DELINQUENCY_ID = DEL.DELINQUENCY_ID AND DEL.ORG_ID = HU.ORGANIZATION_ID AND RS.USER_ID = DUN.LAST_UPDATED_BY AND TMP.TEMPLATE_ID = DUN.TEMPLATE_ID AND DUN.STATUS = 'OPEN' AND DEL.AGING_BUCKET_LINE_ID = AGING.AGING_BUCKET_LINE_ID AND DEL.PARTY_CUST_ID = PARTY.PARTY_ID AND DEL.CUST_ACCOUNT_ID = IACC.CUST_ACCOUNT_ID(+) AND DEL.AGING_BUCKET_LINE_ID = AABLV.AGING_BUCKET_LINE_ID AND SCORE.PARTY_ID = DEL.PARTY_CUST_ID AND SCORE.SCORE_VALUE BETWEEN AGING.SCORE_RANGE_LOW AND AGING.SCORE_RANGE_HIGH AND OBJB.OBJECT_CODE = 'IEX_DUNNING'

View Text - HTML Formatted

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB. OBJECT_PARAMETERS||' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID='||DEL.ORG_ID IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, 'DUNNING_ID' IEU_PARAM_PK_COL
, TO_CHAR(DUN.DUNNING_ID) IEU_PARAM_PK_VALUE
, 1 RESOURCE_ID
, DEL.PARTY_CUST_ID CUSTOMER_ID
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, PARTY.PARTY_TYPE PARTY_TYPE
, IACC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, DECODE(RS.CATEGORY
, 'EMPLOYEE'
, 'RS_INDIVIDUAL'
, 'PARTNER'
, 'RS_PARTNER'
, NULL) RESOURCE_TYPE
, DUN.DUNNING_ID
, DUN.TEMPLATE_ID
, DUN.DELINQUENCY_ID
, DUN.CREATION_DATE DATE_TIME
, DUN.STATUS STATUS
, DUN.CAMPAIGN_SCHED_ID SOURCE_CODE_ID
, DUN.LAST_UPDATE_DATE
, DUN.CALLBACK_DATE
, AGING.AGING_BUCKET_LINE_ID AGING_BUCKET
, AABLV.REPORT_HEADING1 || AABLV.REPORT_HEADING2 BUCKET_NAME
, AGING.FM_METHOD DUNNING_TYPE
, TMP.TEMPLATE_NAME LETTER_NAME
, DECODE(DUN.CALLBACK_YN
, 'Y'
, 'CALLBACK'
, NULL) NEXT_ACTION
, DEL.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT_NAME
FROM JTF_OBJECTS_B OBJB
, IEX_DELINQUENCIES_ALL DEL
, HR_OPERATING_UNITS HU
, IEX_DUNNINGS DUN
, IEX_AG_DN_XREF AGING
, (SELECT PARTY_ID
, SCORE_VALUE
, MAX(CREATION_DATE) CREATION_DATE
FROM IEX_SCORE_HISTORIES GROUP BY PARTY_ID
, SCORE_VALUE) SCORE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS IACC
, JTF_FM_TEMPLATE TMP
, AR_AGING_BUCKET_LINES_VL AABLV
, JTF_RS_RESOURCE_EXTNS RS
WHERE DUN.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND DEL.ORG_ID = HU.ORGANIZATION_ID
AND RS.USER_ID = DUN.LAST_UPDATED_BY
AND TMP.TEMPLATE_ID = DUN.TEMPLATE_ID
AND DUN.STATUS = 'OPEN'
AND DEL.AGING_BUCKET_LINE_ID = AGING.AGING_BUCKET_LINE_ID
AND DEL.PARTY_CUST_ID = PARTY.PARTY_ID
AND DEL.CUST_ACCOUNT_ID = IACC.CUST_ACCOUNT_ID(+)
AND DEL.AGING_BUCKET_LINE_ID = AABLV.AGING_BUCKET_LINE_ID
AND SCORE.PARTY_ID = DEL.PARTY_CUST_ID
AND SCORE.SCORE_VALUE BETWEEN AGING.SCORE_RANGE_LOW
AND AGING.SCORE_RANGE_HIGH
AND OBJB.OBJECT_CODE = 'IEX_DUNNING'