VIEW: APPS.ARFV_COLLECTION_CALLS

Source

View Text - Preformatted

SELECT /* UNIQUE ATTRIBUTES */ CCA.CUSTOMER_CALL_ID , /* REGULAR ATTRIBUTES */ CCA.CALL_DATE , (CCA.CALL_DATE - TRUNC(CCA.CALL_DATE)) , '_LA:DECODE(CCA.STATUS,   ''OPEN'',   ''OP'',   ''CL''):AR_LOOKUPS:PAYMENT_SCHEDULE_STATUS:MEANING' , SUA.LOCATION , '_LA:CCA.REASON_CODE:AR_LOOKUPS:CUSTOMER_RESPONSE_REASON:MEANING' , '_LA:CCA.CALL_OUTCOME:AR_LOOKUPS:CALL_OUTCOME:MEANING' , NS.TEXT , '_LA:CCA.FOLLOW_UP_ACTION:AR_LOOKUPS:FOLLOW_UP:MEANING' , CCA.FOLLOW_UP_DATE , '_LA:CCA.COMPLETE_FLAG:AR_LOOKUPS:YES/NO:MEANING' , CCA.PROMISE_DATE , CCA.PROMISE_AMOUNT , CCA.COLLECTION_FORECAST , CCA.FORECAST_DATE , /* RESOLVED FK RESOLUTIONS */ substrb(PARTY.PARTY_NAME,1,50), CON.FIRST_NAME || ' ' || CON.LAST_NAME , COL.NAME , AOU.NAME , /* IDS */ CCA.CUSTOMER_ID , CCA.CONTACT_ID , CCA.COLLECTOR_ID , CCA.ORG_ID , /* WHO COLUMNS */ CCA.LAST_UPDATE_DATE , CCA.LAST_UPDATED_BY , CCA.CREATION_DATE , CCA.CREATED_BY FROM AR_CUSTOMER_CALLS CCA, HZ_CUST_SITE_USES SUA, AR_NOTES NS, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, /* RA_CONTACTS CON, */ (select ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID, PARTY.PERSON_FIRST_NAME FIRST_NAME, PARTY.PERSON_LAST_NAME LAST_NAME from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT where ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) CON, AR_COLLECTORS COL, HR_ALL_ORGANIZATION_UNITS AOU WHERE CCA.CUSTOMER_CALL_ID = NS.CUSTOMER_CALL_ID(+) AND CCA.SITE_USE_ID = SUA.SITE_USE_ID(+) AND CCA.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID(+) AND CCA.CONTACT_ID = CON.CONTACT_ID(+) AND CCA.COLLECTOR_ID = COL.COLLECTOR_ID(+) AND CCA.ORG_ID = AOU.ORGANIZATION_ID (+) AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID WITH READ ONLY

View Text - HTML Formatted

SELECT /* UNIQUE ATTRIBUTES */ CCA.CUSTOMER_CALL_ID
, /* REGULAR ATTRIBUTES */ CCA.CALL_DATE
, (CCA.CALL_DATE - TRUNC(CCA.CALL_DATE))
, '_LA:DECODE(CCA.STATUS
, ''OPEN''
, ''OP''
, ''CL''):AR_LOOKUPS:PAYMENT_SCHEDULE_STATUS:MEANING'
, SUA.LOCATION
, '_LA:CCA.REASON_CODE:AR_LOOKUPS:CUSTOMER_RESPONSE_REASON:MEANING'
, '_LA:CCA.CALL_OUTCOME:AR_LOOKUPS:CALL_OUTCOME:MEANING'
, NS.TEXT
, '_LA:CCA.FOLLOW_UP_ACTION:AR_LOOKUPS:FOLLOW_UP:MEANING'
, CCA.FOLLOW_UP_DATE
, '_LA:CCA.COMPLETE_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, CCA.PROMISE_DATE
, CCA.PROMISE_AMOUNT
, CCA.COLLECTION_FORECAST
, CCA.FORECAST_DATE
, /* RESOLVED FK RESOLUTIONS */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CON.FIRST_NAME || ' ' || CON.LAST_NAME
, COL.NAME
, AOU.NAME
, /* IDS */ CCA.CUSTOMER_ID
, CCA.CONTACT_ID
, CCA.COLLECTOR_ID
, CCA.ORG_ID
, /* WHO COLUMNS */ CCA.LAST_UPDATE_DATE
, CCA.LAST_UPDATED_BY
, CCA.CREATION_DATE
, CCA.CREATED_BY
FROM AR_CUSTOMER_CALLS CCA
, HZ_CUST_SITE_USES SUA
, AR_NOTES NS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, /* RA_CONTACTS CON
, */ (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) CON
, AR_COLLECTORS COL
, HR_ALL_ORGANIZATION_UNITS AOU
WHERE CCA.CUSTOMER_CALL_ID = NS.CUSTOMER_CALL_ID(+)
AND CCA.SITE_USE_ID = SUA.SITE_USE_ID(+)
AND CCA.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CCA.CONTACT_ID = CON.CONTACT_ID(+)
AND CCA.COLLECTOR_ID = COL.COLLECTOR_ID(+)
AND CCA.ORG_ID = AOU.ORGANIZATION_ID (+)
AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID WITH READ ONLY