VIEW: APPS.AR_ACTIONS_V

Source

View Text - Preformatted

SELECT /*+ INDEX(CCT) */ CCT.ROWID , CCT.CUSTOMER_CALL_TOPIC_ID , CCT.CUSTOMER_CALL_ID , CA.ROWID , CA.CALL_ACTION_ID , CA.CUSTOMER_CALL_TOPIC_ID , CA.CUSTOMER_CALL_ID , TO_DATE(NULL) , NULL , NULL , NULL , TRUNC(CA.ACTION_DATE) /* Bug 773573. */ , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACTION',CA.ACTION_CODE) , CA.COMPLETE_FLAG , CA.ACTION_AMOUNT , NO.TEXT , FU.USER_NAME , CCT.CUSTOMER_CALL_ID , COL.NAME , COL.COLLECTOR_ID , TRUNC(CCT.CALL_DATE) ,/* BUG FIX : 597362*/ (cct.call_date - trunc(cct.call_date))*86400 , /* BUG FIX : 366390 */ NULL , CUST_ACCT.CUST_ACCOUNT_ID , PARTY.PARTY_NAME , CUST_ACCT.ACCOUNT_NUMBER , SITE_USES.LOCATION , ARPT_SQL_FUNC_UTIL.get_address_details(SITE_USES.CUST_ACCT_SITE_ID,'ADD1') , SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME,1,40) , SUBSTRB(PARTY_CONT.PERSON_LAST_NAME,1,50) , ORG_CONT.JOB_TITLE , ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'AREA_CODE') , ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'PHONE_NUMBER') , ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'EXTENSION') , NULL , NULL , CCT.PROMISE_DATE , PS.INVOICE_CURRENCY_CODE , CCT.PROMISE_AMOUNT , CCT.FORECAST_DATE , CCT.COLLECTION_FORECAST , CCT.ATTRIBUTE_CATEGORY , CCT.ATTRIBUTE1 , CCT.ATTRIBUTE2 , CCT.ATTRIBUTE3 , CCT.ATTRIBUTE4 , CCT.ATTRIBUTE5 , CCT.ATTRIBUTE6 , CCT.ATTRIBUTE7 , CCT.ATTRIBUTE8 , CCT.ATTRIBUTE9 , CCT.ATTRIBUTE10 , CCT.ATTRIBUTE11 , CCT.ATTRIBUTE12 , CCT.ATTRIBUTE13 , CCT.ATTRIBUTE14 , CCT.ATTRIBUTE15 , PS.CUSTOMER_TRX_ID , PS.TRX_NUMBER , PS.DUE_DATE , ROUND( TRUNC(SYSDATE) - PS.DUE_DATE, 0) , PS.AMOUNT_DUE_REMAINING , RACTL.LINE_NUMBER , RACTL.EXTENDED_AMOUNT , ARNO.TEXT , NULL , NULL , PS.CLASS , /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID FROM AR_NOTES NO, AR_ACTION_NOTIFICATIONS ANO, FND_USER FU, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, AR_PAYMENT_SCHEDULES PS, RA_CUSTOMER_TRX_LINES RACTL, AR_NOTES ARNO, AR_CUSTOMER_CALL_TOPICS CCT, AR_COLLECTORS COL, AR_CALL_ACTIONS CA WHERE NO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID AND ANO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID AND FU.USER_ID (+) = ANO.EMPLOYEE_ID AND CCT.CUSTOMER_CALL_TOPIC_ID = CA.CUSTOMER_CALL_TOPIC_ID AND COL.COLLECTOR_ID = CCT.COLLECTOR_ID AND CUST_ACCT.CUST_ACCOUNT_ID = CCT.CUSTOMER_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID (+) = CCT.SITE_USE_ID AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCT.CONTACT_ID AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG = 'F' AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID AND PS.PAYMENT_SCHEDULE_ID (+) = CCT.PAYMENT_SCHEDULE_ID AND RACTL.CUSTOMER_TRX_LINE_ID (+) = CCT.CUSTOMER_TRX_LINE_ID AND ARNO.CUSTOMER_CALL_TOPIC_ID (+) = CA.CUSTOMER_CALL_TOPIC_ID UNION ALL SELECT /*+INDEX(CCL) */ CHARTOROWID(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), CA.ROWID, CA.CALL_ACTION_ID, CA.CUSTOMER_CALL_TOPIC_ID, CA.CUSTOMER_CALL_ID, TO_DATE(NULL), NULL, NULL, NULL, TRUNC(CA.ACTION_DATE), /* Bug 773573 */ ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACTION',CA.ACTION_CODE), CA.COMPLETE_FLAG, CA.ACTION_AMOUNT, NO.TEXT, FU.USER_NAME, CCL.CUSTOMER_CALL_ID, COL.NAME, COL.COLLECTOR_ID, TRUNC(CCL.CALL_DATE), /* BUG FIX : 366390 */ (ccl.call_date - trunc(ccl.call_date))*86400, /*BUGFIX:597362*/ CCL.STATUS, /*BUGFIX:1414268*/ CUST_ACCT.CUST_ACCOUNT_ID, PARTY.PARTY_NAME, CUST_ACCT.ACCOUNT_NUMBER, SITE_USES.LOCATION, ARPT_SQL_FUNC_UTIL.get_address_details(SITE_USES.CUST_ACCT_SITE_ID,'ADD1'), SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME,1,40), SUBSTRB(PARTY_CONT.PERSON_LAST_NAME,1,50), ORG_CONT.JOB_TITLE, ARPT_SQL_FUNC_UTIL.get_phone_details(CCL.PHONE_ID,'AREA_CODE'), ARPT_SQL_FUNC_UTIL.get_phone_details(CCL.PHONE_ID,'PHONE_NUMBER'), ARPT_SQL_FUNC_UTIL.get_phone_details(CCL.PHONE_ID,'EXTENSION'), NULL, NULL, CCL.PROMISE_DATE, CCL.CURRENCY_CODE, CCL.PROMISE_AMOUNT, CCL.FORECAST_DATE, CCL.COLLECTION_FORECAST, CCL.ATTRIBUTE_CATEGORY, CCL.ATTRIBUTE1, CCL.ATTRIBUTE2, CCL.ATTRIBUTE3, CCL.ATTRIBUTE4, CCL.ATTRIBUTE5, CCL.ATTRIBUTE6, CCL.ATTRIBUTE7, CCL.ATTRIBUTE8, CCL.ATTRIBUTE9, CCL.ATTRIBUTE10, CCL.ATTRIBUTE11, CCL.ATTRIBUTE12, CCL.ATTRIBUTE13, CCL.ATTRIBUTE14, CCL.ATTRIBUTE15, TO_NUMBER(NULL), NULL, TO_DATE(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), ARNO.TEXT, ARPT_SQL_FUNC_UTIL.get_phone_details(CCL.FAX_ID,'AREA_CODE'), ARPT_SQL_FUNC_UTIL.get_phone_details(CCL.FAX_ID,'PHONE_NUMBER'), NULL, /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID FROM AR_NOTES NO, AR_ACTION_NOTIFICATIONS ANO, FND_USER FU, AR_COLLECTORS COL, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, AR_NOTES ARNO, AR_CUSTOMER_CALLS CCL, AR_CALL_ACTIONS CA WHERE NO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID AND ANO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID AND FU.USER_ID (+) = ANO.EMPLOYEE_ID AND CCL.CUSTOMER_CALL_ID = CA.CUSTOMER_CALL_ID AND COL.COLLECTOR_ID = CCL.COLLECTOR_ID AND CUST_ACCT.CUST_ACCOUNT_ID = CCL.CUSTOMER_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID (+) = CCL.SITE_USE_ID AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCL.CONTACT_ID AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG = 'F' AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID AND ARNO.CUSTOMER_CALL_ID (+) = CA.CUSTOMER_CALL_ID UNION ALL SELECT /*+ INDEX(CCT) */ CCT.ROWID, CCT.CUSTOMER_CALL_TOPIC_ID, CCT.CUSTOMER_CALL_ID, CHARTOROWID(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), CCT.FOLLOW_UP_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('FOLLOW_UP',CCT.FOLLOW_UP_ACTION), CCT.FOLLOW_UP_ACTION, DECODE(CCT.COMPLETE_FLAG, NULL, 'N', CCT.COMPLETE_FLAG), TO_DATE(NULL), NULL, NULL, TO_NUMBER(NULL), NULL, NULL, CCT.CUSTOMER_CALL_ID, COL.NAME, COL.COLLECTOR_ID, TRUNC(CCT.CALL_DATE), /* BUG FIX : 366390 */ (cct.call_date - trunc(cct.call_date))*86400,/*BUG FIX:597362*/ NULL, CUST_ACCT.CUST_ACCOUNT_ID, PARTY.PARTY_NAME, CUST_ACCT.ACCOUNT_NUMBER, SITE_USES.LOCATION, ARPT_SQL_FUNC_UTIL.get_address_details(SITE_USES.CUST_ACCT_SITE_ID,'ADD1'), SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME,1,40), SUBSTRB(PARTY_CONT.PERSON_LAST_NAME,1,50), ORG_CONT.JOB_TITLE, ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'AREA_CODE'), ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'PHONE_NUMBER'), ARPT_SQL_FUNC_UTIL.get_phone_details(CCT.PHONE_ID,'EXTENSION'), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CALL_OUTCOME',CCT.CALL_OUTCOME), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CUSTOMER_RESPONSE_REASON',CCT.REASON_CODE), CCT.PROMISE_DATE, PS.INVOICE_CURRENCY_CODE, CCT.PROMISE_AMOUNT, CCT.FORECAST_DATE, CCT.COLLECTION_FORECAST, CCT.ATTRIBUTE_CATEGORY, CCT.ATTRIBUTE1, CCT.ATTRIBUTE2, CCT.ATTRIBUTE3, CCT.ATTRIBUTE4, CCT.ATTRIBUTE5, CCT.ATTRIBUTE6, CCT.ATTRIBUTE7, CCT.ATTRIBUTE8, CCT.ATTRIBUTE9, CCT.ATTRIBUTE10, CCT.ATTRIBUTE11, CCT.ATTRIBUTE12, CCT.ATTRIBUTE13, CCT.ATTRIBUTE14, CCT.ATTRIBUTE15, PS.CUSTOMER_TRX_ID, PS.TRX_NUMBER, PS.DUE_DATE, ROUND(TRUNC(SYSDATE) - DUE_DATE,0), PS.AMOUNT_DUE_REMAINING, RACTL.LINE_NUMBER, RACTL.EXTENDED_AMOUNT, ARNO.TEXT, NULL, NULL, PS.CLASS, /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID FROM AR_COLLECTORS COL, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, AR_PAYMENT_SCHEDULES PS, RA_CUSTOMER_TRX_LINES RACTL, AR_NOTES ARNO, AR_CUSTOMER_CALL_TOPICS CCT WHERE COL.COLLECTOR_ID = CCT.COLLECTOR_ID AND CUST_ACCT.CUST_ACCOUNT_ID = CCT.CUSTOMER_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID (+) = CCT.SITE_USE_ID AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCT.CONTACT_ID AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG = 'F' AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID AND PS.PAYMENT_SCHEDULE_ID (+) = CCT.PAYMENT_SCHEDULE_ID AND RACTL.CUSTOMER_TRX_LINE_ID (+) = CCT.CUSTOMER_TRX_LINE_ID AND ARNO.CUSTOMER_CALL_TOPIC_ID (+) = CCT.CUSTOMER_CALL_TOPIC_ID AND CCT.FOLLOW_UP_ACTION IS NOT NULL UNION ALL SELECT /*+INDEX(CC) */ CC.ROWID, TO_NUMBER(NULL), CC.CUSTOMER_CALL_ID, CHARTOROWID(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), CC.FOLLOW_UP_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('FOLLOW_UP',CC.FOLLOW_UP_ACTION), CC.FOLLOW_UP_ACTION, DECODE(CC.COMPLETE_FLAG, NULL, 'N',CC.COMPLETE_FLAG), TO_DATE(NULL), NULL, NULL, TO_NUMBER(NULL), NULL, NULL, CC.CUSTOMER_CALL_ID, COL.NAME, COL.COLLECTOR_ID, TRUNC(CC.CALL_DATE), /* BUG FIX : 366390 */ (cc.call_date - trunc(cc.call_date))*86400,/*BUG FIX:597362*/ CC.STATUS, CUST_ACCT.CUST_ACCOUNT_ID, PARTY.PARTY_NAME, CUST_ACCT.ACCOUNT_NUMBER, SITE_USES.LOCATION, ARPT_SQL_FUNC_UTIL.get_address_details(SITE_USES.CUST_ACCT_SITE_ID,'ADD1'), SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME,1,40), SUBSTRB(PARTY_CONT.PERSON_LAST_NAME,1,50), ORG_CONT.JOB_TITLE, ARPT_SQL_FUNC_UTIL.get_phone_details(CC.PHONE_ID,'AREA_CODE'), ARPT_SQL_FUNC_UTIL.get_phone_details(CC.PHONE_ID,'PHONE_NUMBER'), ARPT_SQL_FUNC_UTIL.get_phone_details(CC.PHONE_ID,'EXTENSION'), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CALL_OUTCOME',CC.CALL_OUTCOME), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CUSTOMER_RESPONSE_REASON',CC.REASON_CODE), CC.PROMISE_DATE, NULL, CC.PROMISE_AMOUNT, CC.FORECAST_DATE, CC.COLLECTION_FORECAST, CC.ATTRIBUTE_CATEGORY, CC.ATTRIBUTE1, CC.ATTRIBUTE2, CC.ATTRIBUTE3, CC.ATTRIBUTE4, CC.ATTRIBUTE5, CC.ATTRIBUTE6, CC.ATTRIBUTE7, CC.ATTRIBUTE8, CC.ATTRIBUTE9, CC.ATTRIBUTE10, CC.ATTRIBUTE11, CC.ATTRIBUTE12, CC.ATTRIBUTE13, CC.ATTRIBUTE14, CC.ATTRIBUTE15, TO_NUMBER(NULL), NULL, TO_DATE(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), ARNO.TEXT, NULL, NULL, NULL, SITE_USES.SITE_USE_ID FROM AR_COLLECTORS COL, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, AR_NOTES ARNO, AR_CUSTOMER_CALLS CC WHERE COL.COLLECTOR_ID = CC.COLLECTOR_ID AND CUST_ACCT.CUST_ACCOUNT_ID = CC.CUSTOMER_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID (+) = CC.SITE_USE_ID AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CC.CONTACT_ID AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG = 'F' AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID AND ARNO.CUSTOMER_CALL_ID (+) = CC.CUSTOMER_CALL_ID AND CC.FOLLOW_UP_ACTION IS NOT NULL

View Text - HTML Formatted

SELECT /*+ INDEX(CCT) */ CCT.ROWID
, CCT.CUSTOMER_CALL_TOPIC_ID
, CCT.CUSTOMER_CALL_ID
, CA.ROWID
, CA.CALL_ACTION_ID
, CA.CUSTOMER_CALL_TOPIC_ID
, CA.CUSTOMER_CALL_ID
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, TRUNC(CA.ACTION_DATE) /* BUG 773573. */
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTION'
, CA.ACTION_CODE)
, CA.COMPLETE_FLAG
, CA.ACTION_AMOUNT
, NO.TEXT
, FU.USER_NAME
, CCT.CUSTOMER_CALL_ID
, COL.NAME
, COL.COLLECTOR_ID
, TRUNC(CCT.CALL_DATE)
, /* BUG FIX : 597362*/ (CCT.CALL_DATE - TRUNC(CCT.CALL_DATE))*86400
, /* BUG FIX : 366390 */ NULL
, CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, SITE_USES.LOCATION
, ARPT_SQL_FUNC_UTIL.GET_ADDRESS_DETAILS(SITE_USES.CUST_ACCT_SITE_ID
, 'ADD1')
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
, ORG_CONT.JOB_TITLE
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'AREA_CODE')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'PHONE_NUMBER')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'EXTENSION')
, NULL
, NULL
, CCT.PROMISE_DATE
, PS.INVOICE_CURRENCY_CODE
, CCT.PROMISE_AMOUNT
, CCT.FORECAST_DATE
, CCT.COLLECTION_FORECAST
, CCT.ATTRIBUTE_CATEGORY
, CCT.ATTRIBUTE1
, CCT.ATTRIBUTE2
, CCT.ATTRIBUTE3
, CCT.ATTRIBUTE4
, CCT.ATTRIBUTE5
, CCT.ATTRIBUTE6
, CCT.ATTRIBUTE7
, CCT.ATTRIBUTE8
, CCT.ATTRIBUTE9
, CCT.ATTRIBUTE10
, CCT.ATTRIBUTE11
, CCT.ATTRIBUTE12
, CCT.ATTRIBUTE13
, CCT.ATTRIBUTE14
, CCT.ATTRIBUTE15
, PS.CUSTOMER_TRX_ID
, PS.TRX_NUMBER
, PS.DUE_DATE
, ROUND( TRUNC(SYSDATE) - PS.DUE_DATE
, 0)
, PS.AMOUNT_DUE_REMAINING
, RACTL.LINE_NUMBER
, RACTL.EXTENDED_AMOUNT
, ARNO.TEXT
, NULL
, NULL
, PS.CLASS
, /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID
FROM AR_NOTES NO
, AR_ACTION_NOTIFICATIONS ANO
, FND_USER FU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX_LINES RACTL
, AR_NOTES ARNO
, AR_CUSTOMER_CALL_TOPICS CCT
, AR_COLLECTORS COL
, AR_CALL_ACTIONS CA
WHERE NO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID
AND ANO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID
AND FU.USER_ID (+) = ANO.EMPLOYEE_ID
AND CCT.CUSTOMER_CALL_TOPIC_ID = CA.CUSTOMER_CALL_TOPIC_ID
AND COL.COLLECTOR_ID = CCT.COLLECTOR_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CCT.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID (+) = CCT.SITE_USE_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCT.CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID
AND PS.PAYMENT_SCHEDULE_ID (+) = CCT.PAYMENT_SCHEDULE_ID
AND RACTL.CUSTOMER_TRX_LINE_ID (+) = CCT.CUSTOMER_TRX_LINE_ID
AND ARNO.CUSTOMER_CALL_TOPIC_ID (+) = CA.CUSTOMER_CALL_TOPIC_ID UNION ALL SELECT /*+INDEX(CCL) */ CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CA.ROWID
, CA.CALL_ACTION_ID
, CA.CUSTOMER_CALL_TOPIC_ID
, CA.CUSTOMER_CALL_ID
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, TRUNC(CA.ACTION_DATE)
, /* BUG 773573 */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTION'
, CA.ACTION_CODE)
, CA.COMPLETE_FLAG
, CA.ACTION_AMOUNT
, NO.TEXT
, FU.USER_NAME
, CCL.CUSTOMER_CALL_ID
, COL.NAME
, COL.COLLECTOR_ID
, TRUNC(CCL.CALL_DATE)
, /* BUG FIX : 366390 */ (CCL.CALL_DATE - TRUNC(CCL.CALL_DATE))*86400
, /*BUGFIX:597362*/ CCL.STATUS
, /*BUGFIX:1414268*/ CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, SITE_USES.LOCATION
, ARPT_SQL_FUNC_UTIL.GET_ADDRESS_DETAILS(SITE_USES.CUST_ACCT_SITE_ID
, 'ADD1')
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
, ORG_CONT.JOB_TITLE
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCL.PHONE_ID
, 'AREA_CODE')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCL.PHONE_ID
, 'PHONE_NUMBER')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCL.PHONE_ID
, 'EXTENSION')
, NULL
, NULL
, CCL.PROMISE_DATE
, CCL.CURRENCY_CODE
, CCL.PROMISE_AMOUNT
, CCL.FORECAST_DATE
, CCL.COLLECTION_FORECAST
, CCL.ATTRIBUTE_CATEGORY
, CCL.ATTRIBUTE1
, CCL.ATTRIBUTE2
, CCL.ATTRIBUTE3
, CCL.ATTRIBUTE4
, CCL.ATTRIBUTE5
, CCL.ATTRIBUTE6
, CCL.ATTRIBUTE7
, CCL.ATTRIBUTE8
, CCL.ATTRIBUTE9
, CCL.ATTRIBUTE10
, CCL.ATTRIBUTE11
, CCL.ATTRIBUTE12
, CCL.ATTRIBUTE13
, CCL.ATTRIBUTE14
, CCL.ATTRIBUTE15
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ARNO.TEXT
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCL.FAX_ID
, 'AREA_CODE')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCL.FAX_ID
, 'PHONE_NUMBER')
, NULL
, /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID
FROM AR_NOTES NO
, AR_ACTION_NOTIFICATIONS ANO
, FND_USER FU
, AR_COLLECTORS COL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_NOTES ARNO
, AR_CUSTOMER_CALLS CCL
, AR_CALL_ACTIONS CA
WHERE NO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID
AND ANO.CALL_ACTION_ID (+) = CA.CALL_ACTION_ID
AND FU.USER_ID (+) = ANO.EMPLOYEE_ID
AND CCL.CUSTOMER_CALL_ID = CA.CUSTOMER_CALL_ID
AND COL.COLLECTOR_ID = CCL.COLLECTOR_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CCL.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID (+) = CCL.SITE_USE_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCL.CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID
AND ARNO.CUSTOMER_CALL_ID (+) = CA.CUSTOMER_CALL_ID UNION ALL SELECT /*+ INDEX(CCT) */ CCT.ROWID
, CCT.CUSTOMER_CALL_TOPIC_ID
, CCT.CUSTOMER_CALL_ID
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CCT.FOLLOW_UP_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('FOLLOW_UP'
, CCT.FOLLOW_UP_ACTION)
, CCT.FOLLOW_UP_ACTION
, DECODE(CCT.COMPLETE_FLAG
, NULL
, 'N'
, CCT.COMPLETE_FLAG)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, CCT.CUSTOMER_CALL_ID
, COL.NAME
, COL.COLLECTOR_ID
, TRUNC(CCT.CALL_DATE)
, /* BUG FIX : 366390 */ (CCT.CALL_DATE - TRUNC(CCT.CALL_DATE))*86400
, /*BUG FIX:597362*/ NULL
, CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, SITE_USES.LOCATION
, ARPT_SQL_FUNC_UTIL.GET_ADDRESS_DETAILS(SITE_USES.CUST_ACCT_SITE_ID
, 'ADD1')
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
, ORG_CONT.JOB_TITLE
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'AREA_CODE')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'PHONE_NUMBER')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CCT.PHONE_ID
, 'EXTENSION')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CALL_OUTCOME'
, CCT.CALL_OUTCOME)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CUSTOMER_RESPONSE_REASON'
, CCT.REASON_CODE)
, CCT.PROMISE_DATE
, PS.INVOICE_CURRENCY_CODE
, CCT.PROMISE_AMOUNT
, CCT.FORECAST_DATE
, CCT.COLLECTION_FORECAST
, CCT.ATTRIBUTE_CATEGORY
, CCT.ATTRIBUTE1
, CCT.ATTRIBUTE2
, CCT.ATTRIBUTE3
, CCT.ATTRIBUTE4
, CCT.ATTRIBUTE5
, CCT.ATTRIBUTE6
, CCT.ATTRIBUTE7
, CCT.ATTRIBUTE8
, CCT.ATTRIBUTE9
, CCT.ATTRIBUTE10
, CCT.ATTRIBUTE11
, CCT.ATTRIBUTE12
, CCT.ATTRIBUTE13
, CCT.ATTRIBUTE14
, CCT.ATTRIBUTE15
, PS.CUSTOMER_TRX_ID
, PS.TRX_NUMBER
, PS.DUE_DATE
, ROUND(TRUNC(SYSDATE) - DUE_DATE
, 0)
, PS.AMOUNT_DUE_REMAINING
, RACTL.LINE_NUMBER
, RACTL.EXTENDED_AMOUNT
, ARNO.TEXT
, NULL
, NULL
, PS.CLASS
, /* BUG FIX : 356100 */ SITE_USES.SITE_USE_ID
FROM AR_COLLECTORS COL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX_LINES RACTL
, AR_NOTES ARNO
, AR_CUSTOMER_CALL_TOPICS CCT
WHERE COL.COLLECTOR_ID = CCT.COLLECTOR_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CCT.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID (+) = CCT.SITE_USE_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CCT.CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID
AND PS.PAYMENT_SCHEDULE_ID (+) = CCT.PAYMENT_SCHEDULE_ID
AND RACTL.CUSTOMER_TRX_LINE_ID (+) = CCT.CUSTOMER_TRX_LINE_ID
AND ARNO.CUSTOMER_CALL_TOPIC_ID (+) = CCT.CUSTOMER_CALL_TOPIC_ID
AND CCT.FOLLOW_UP_ACTION IS NOT NULL UNION ALL SELECT /*+INDEX(CC) */ CC.ROWID
, TO_NUMBER(NULL)
, CC.CUSTOMER_CALL_ID
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CC.FOLLOW_UP_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('FOLLOW_UP'
, CC.FOLLOW_UP_ACTION)
, CC.FOLLOW_UP_ACTION
, DECODE(CC.COMPLETE_FLAG
, NULL
, 'N'
, CC.COMPLETE_FLAG)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, CC.CUSTOMER_CALL_ID
, COL.NAME
, COL.COLLECTOR_ID
, TRUNC(CC.CALL_DATE)
, /* BUG FIX : 366390 */ (CC.CALL_DATE - TRUNC(CC.CALL_DATE))*86400
, /*BUG FIX:597362*/ CC.STATUS
, CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, SITE_USES.LOCATION
, ARPT_SQL_FUNC_UTIL.GET_ADDRESS_DETAILS(SITE_USES.CUST_ACCT_SITE_ID
, 'ADD1')
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
, ORG_CONT.JOB_TITLE
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CC.PHONE_ID
, 'AREA_CODE')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CC.PHONE_ID
, 'PHONE_NUMBER')
, ARPT_SQL_FUNC_UTIL.GET_PHONE_DETAILS(CC.PHONE_ID
, 'EXTENSION')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CALL_OUTCOME'
, CC.CALL_OUTCOME)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CUSTOMER_RESPONSE_REASON'
, CC.REASON_CODE)
, CC.PROMISE_DATE
, NULL
, CC.PROMISE_AMOUNT
, CC.FORECAST_DATE
, CC.COLLECTION_FORECAST
, CC.ATTRIBUTE_CATEGORY
, CC.ATTRIBUTE1
, CC.ATTRIBUTE2
, CC.ATTRIBUTE3
, CC.ATTRIBUTE4
, CC.ATTRIBUTE5
, CC.ATTRIBUTE6
, CC.ATTRIBUTE7
, CC.ATTRIBUTE8
, CC.ATTRIBUTE9
, CC.ATTRIBUTE10
, CC.ATTRIBUTE11
, CC.ATTRIBUTE12
, CC.ATTRIBUTE13
, CC.ATTRIBUTE14
, CC.ATTRIBUTE15
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ARNO.TEXT
, NULL
, NULL
, NULL
, SITE_USES.SITE_USE_ID
FROM AR_COLLECTORS COL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_NOTES ARNO
, AR_CUSTOMER_CALLS CC
WHERE COL.COLLECTOR_ID = CC.COLLECTOR_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CC.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID (+) = CC.SITE_USE_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CC.CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID
AND ARNO.CUSTOMER_CALL_ID (+) = CC.CUSTOMER_CALL_ID
AND CC.FOLLOW_UP_ACTION IS NOT NULL