View: AS_ASSIGNED_LIST_ENTRIES_V

View Text

SELECT LU.ROWID ROW_ID
, LE.LIST_ENTRY_ID
, LE.LIST_ID
, AL.NAME LIST_NAME
, AL.PUBLIC_FLAG
, AL.ENABLED_FLAG LIST_ENABLED_FLAG
, AL.TYPE LIST_TYPE
, LE.CUSTOMER_ID
, LE.ADDRESS_ID
, LE.CONTACT_ID
, LE.PHONE_ID
, LE.LEAD_ID
, LE.LETTER_ID
, LE.EVENT_ID
, LE.PICKING_HEADER_ID
, LE.BATCH_ID
, LE.MARKED_FLAG
, LE.USE_PVT_MAILING_ADDRESS
, LE.TRANSFER_FLAG
, LE.TRANSFER_STATUS
, LE.COMPANY_NAME
, LE.ADDRESS1
, LE.ADDRESS2
, LE.ADDRESS3
, LE.ADDRESS4
, LE.CITY
, LE.COUNTY
, LE.STATE
, LE.PROVINCE
, LE.POSTAL_CODE
, LE.COUNTRY_CODE
, TERR1.TERRITORY_SHORT_NAME COUNTRY
, LE.FIRST_NAME
, LE.LAST_NAME
, LE.TITLE_CODE
, LE.JOB_TITLE_CODE
, JOBS.MEANING JOB_TITLE_MEANING
, LE.JOB_TITLE
, LE.MAIL_STOP
, LE.EMAIL_ADDRESS
, LE.PHONE_TYPE_CODE
, LE.AREA_CODE
, LE.PHONE_NUMBER
, LE.AREA_CODE||LE.PHONE_NUMBER FULL_NUMBER
, LE.EXTENSION
, LE.CONTACT_SUFFIX
, LE.CUSTOMER_ORIG_SYSTEM_REFERENCE
, LE.ADDRESS_ORIG_SYSTEM_REFERENCE
, LE.CONTACT_ORIG_SYSTEM_REFERENCE
, LE.NUM_OF_EMPLOYEES
, LE.PARENT_CUSTOMER_ID
, LE.PARENT_COMPANY_NAME
, LE.POTENTIAL_REVENUE_CURR_FY
, LE.POTENTIAL_REVENUE_NEXT_FY
, LE.FISCAL_YEAREND_MONTH
, LE.ANALYSIS_FY
, LE.MISSION_STATEMENT
, LE.TAX_REFERENCE
, LE.NET_WORTH
, LE.YEAR_ESTABLISHED
, LE.DEPARTMENT_CODE
, LOOK1.MEANING DEPARTMENT_MEANING
, LE.DEPARTMENT
, LE.RANK
, LE.MANAGED_BY
, LE.MANAGER_FIRST_NAME
, LE.MANAGER_LAST_NAME
, LE.NATIVE_LANGUAGE
, LOOK2.MEANING NATIVE_LANGUAGE_MEANING
, LE.OTHER_LANGUAGE_1
, LOOK3.MEANING OTHER_LANGUAGE1_MEANING
, LE.OTHER_LANGUAGE_2
, LOOK4.MEANING OTHER_LANGUAGE2_MEANING
, LE.ADDRESS_PHONE_TYPE_CODE1
, LE.ADDRESS_PHONE_TYPE_CODE2
, LE.ADDRESS_PHONE_TYPE_CODE3
, LE.ADDRESS_PHONE_TYPE_CODE4
, LE.ADDRESS_PHONE_TYPE_CODE5
, LE.ADDRESS_PHONE_AREA_CODE1
, LE.ADDRESS_PHONE_AREA_CODE2
, LE.ADDRESS_PHONE_AREA_CODE3
, LE.ADDRESS_PHONE_AREA_CODE4
, LE.ADDRESS_PHONE_AREA_CODE5
, LE.ADDRESS_PHONE_NUMBER1
, LE.ADDRESS_PHONE_NUMBER2
, LE.ADDRESS_PHONE_NUMBER3
, LE.ADDRESS_PHONE_NUMBER4
, LE.ADDRESS_PHONE_NUMBER5
, LE.ADDRESS_PHONE_AREA_CODE1|| LE.ADDRESS_PHONE_NUMBER1 ADDRESS_FULL_NUMBER1
, LE.ADDRESS_PHONE_AREA_CODE2|| LE.ADDRESS_PHONE_NUMBER2 ADDRESS_FULL_NUMBER2
, LE.ADDRESS_PHONE_AREA_CODE3|| LE.ADDRESS_PHONE_NUMBER3 ADDRESS_FULL_NUMBER3
, LE.ADDRESS_PHONE_AREA_CODE4|| LE.ADDRESS_PHONE_NUMBER4 ADDRESS_FULL_NUMBER4
, LE.ADDRESS_PHONE_AREA_CODE5|| LE.ADDRESS_PHONE_NUMBER5 ADDRESS_FULL_NUMBER5
, LE.ADDRESS_PHONE_EXTENSION1
, LE.ADDRESS_PHONE_EXTENSION2
, LE.ADDRESS_PHONE_EXTENSION3
, LE.ADDRESS_PHONE_EXTENSION4
, LE.ADDRESS_PHONE_EXTENSION5
, LE.CONTACT_PHONE_TYPE_CODE1
, LE.CONTACT_PHONE_TYPE_CODE2
, LE.CONTACT_PHONE_TYPE_CODE3
, LE.CONTACT_PHONE_TYPE_CODE4
, LE.CONTACT_PHONE_TYPE_CODE5
, LE.CONTACT_PHONE_AREA_CODE1
, LE.CONTACT_PHONE_AREA_CODE2
, LE.CONTACT_PHONE_AREA_CODE3
, LE.CONTACT_PHONE_AREA_CODE4
, LE.CONTACT_PHONE_AREA_CODE5
, LE.CONTACT_PHONE_NUMBER1
, LE.CONTACT_PHONE_NUMBER2
, LE.CONTACT_PHONE_NUMBER3
, LE.CONTACT_PHONE_NUMBER4
, LE.CONTACT_PHONE_NUMBER5
, LE.CONTACT_PHONE_AREA_CODE1|| LE.CONTACT_PHONE_NUMBER1 CONTACT_FULL_NUMBER1
, LE.CONTACT_PHONE_AREA_CODE2|| LE.CONTACT_PHONE_NUMBER2 CONTACT_FULL_NUMBER2
, LE.CONTACT_PHONE_AREA_CODE3|| LE.CONTACT_PHONE_NUMBER3 CONTACT_FULL_NUMBER3
, LE.CONTACT_PHONE_AREA_CODE4|| LE.CONTACT_PHONE_NUMBER4 CONTACT_FULL_NUMBER4
, LE.CONTACT_PHONE_AREA_CODE5|| LE.CONTACT_PHONE_NUMBER5 CONTACT_FULL_NUMBER5
, LE.CONTACT_PHONE_EXTENSION1
, LE.CONTACT_PHONE_EXTENSION2
, LE.CONTACT_PHONE_EXTENSION3
, LE.CONTACT_PHONE_EXTENSION4
, LE.CONTACT_PHONE_EXTENSION5
, LE.CUSTOMER_USER_DEFINED1
, LE.CUSTOMER_USER_DEFINED2
, LE.CUSTOMER_USER_DEFINED3
, LE.CUSTOMER_USER_DEFINED4
, LE.CUSTOMER_USER_DEFINED5
, LE.CUSTOMER_USER_DEFINED6
, LE.CUSTOMER_USER_DEFINED7
, LE.CUSTOMER_USER_DEFINED8
, LE.CUSTOMER_USER_DEFINED9
, LE.CUSTOMER_USER_DEFINED10
, LE.CUSTOMER_USER_DEFINED11
, LE.CUSTOMER_USER_DEFINED12
, LE.CUSTOMER_USER_DEFINED13
, LE.CUSTOMER_USER_DEFINED14
, LE.CUSTOMER_USER_DEFINED15
, LE.ADDRESS_USER_DEFINED1
, LE.ADDRESS_USER_DEFINED2
, LE.ADDRESS_USER_DEFINED3
, LE.ADDRESS_USER_DEFINED4
, LE.ADDRESS_USER_DEFINED5
, LE.ADDRESS_USER_DEFINED6
, LE.ADDRESS_USER_DEFINED7
, LE.ADDRESS_USER_DEFINED8
, LE.ADDRESS_USER_DEFINED9
, LE.ADDRESS_USER_DEFINED10
, LE.ADDRESS_USER_DEFINED11
, LE.ADDRESS_USER_DEFINED12
, LE.ADDRESS_USER_DEFINED13
, LE.ADDRESS_USER_DEFINED14
, LE.ADDRESS_USER_DEFINED15
, LE.CONTACT_USER_DEFINED1
, LE.CONTACT_USER_DEFINED2
, LE.CONTACT_USER_DEFINED3
, LE.CONTACT_USER_DEFINED4
, LE.CONTACT_USER_DEFINED5
, LE.CONTACT_USER_DEFINED6
, LE.CONTACT_USER_DEFINED7
, LE.CONTACT_USER_DEFINED8
, LE.CONTACT_USER_DEFINED9
, LE.CONTACT_USER_DEFINED10
, LE.CONTACT_USER_DEFINED11
, LE.CONTACT_USER_DEFINED12
, LE.CONTACT_USER_DEFINED13
, LE.CONTACT_USER_DEFINED14
, LE.CONTACT_USER_DEFINED15
, LE.CONTACT_USER_DEFINED16
, LE.CONTACT_USER_DEFINED17
, LE.CONTACT_USER_DEFINED18
, LE.CONTACT_USER_DEFINED19
, LE.CONTACT_USER_DEFINED20
, LE.CONTACT_USER_DEFINED21
, LE.CONTACT_USER_DEFINED22
, LE.CONTACT_USER_DEFINED23
, LE.CONTACT_USER_DEFINED24
, LE.CONTACT_USER_DEFINED25
, LU.PROMOTION_ID
, LU.LAST_UPDATE_DATE
, LU.LAST_UPDATED_BY
, LU.CREATION_DATE
, LU.CREATED_BY
, LU.LAST_UPDATE_LOGIN
, LU.REQUEST_ID
, LU.PROGRAM_APPLICATION_ID
, LU.PROGRAM_ID
, LU.PROGRAM_UPDATE_DATE
, LU.ASSIGNED_PERSON_ID
, SF.EMPLOYEE_FIRST_NAME
, SF.EMPLOYEE_LAST_NAME
, SF.SALES_GROUP_NAME
, LU.DUE_DATE
, LU.DUE_TIME
, LU.STATUS
, LU.USER_DEFINED1
, LU.USER_DEFINED2
, LU.USER_DEFINED3
, LU.USER_DEFINED4
, LU.USER_DEFINED5
, LU.USER_DEFINED6
, LU.USER_DEFINED7
, LU.USER_DEFINED8
, LU.USER_DEFINED9
, LU.USER_DEFINED10
, LU.USER_DEFINED11
, LU.USER_DEFINED12
, LU.USER_DEFINED13
, LU.USER_DEFINED14
, LU.USER_DEFINED15
, LU.USER_COMMENTS
, PM.CODE PROMOTION_CODE
, PM.NAME PROMOTION_NAME
, PM.TYPE PROMOTION_TYPE
, LOOK5.MEANING PROMOTION_TYPE_MEANING
, PM.ENABLED_FLAG PROMOTION_ENABLED_FLAG
, PM.SCRIPT_TYPE PROMOTION_SCRIPT_TYPE
, PM.EVENT_NAME_ID EVENT_NAME_ID
FROM FND_TERRITORIES_TL TERR1
, AS_LIST_ENTRIES LE
, AS_LIST_ENTRY_USAGES LU
, AS_PROMOTIONS PM
, AS_EMP_SALESFORCE_V SF
, AS_LISTS AL
, AR_LOOKUPS LOOK1
, AS_LOOKUPS LOOK2
, AS_LOOKUPS LOOK3
, AS_LOOKUPS LOOK4
, AS_LOOKUPS LOOK5
, AR_LOOKUPS JOBS
WHERE LE.COUNTRY_CODE = TERR1.TERRITORY_CODE(+)
AND TERR1.LANGUAGE(+) = USERENV('LANG')
AND AL.LIST_ID = LE.LIST_ID
AND LE.LIST_ENTRY_ID = LU.LIST_ENTRY_ID
AND LU.PROMOTION_ID = PM.PROMOTION_ID(+)
AND LU.ASSIGNED_PERSON_ID = SF.EMPLOYEE_PERSON_ID
AND LOOK1.LOOKUP_CODE(+) = LE.DEPARTMENT_CODE
AND LOOK1.LOOKUP_TYPE(+) = 'DEPARTMENT'
AND LOOK2.LOOKUP_CODE(+) = LE.NATIVE_LANGUAGE
AND LOOK2.LOOKUP_TYPE(+) = 'LANGUAGE'
AND LOOK3.LOOKUP_CODE(+) = LE.OTHER_LANGUAGE_1
AND LOOK3.LOOKUP_TYPE(+) = 'LANGUAGE'
AND LOOK4.LOOKUP_CODE(+) = LE.OTHER_LANGUAGE_2
AND LOOK4.LOOKUP_TYPE(+) = 'LANGUAGE'
AND JOBS.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND JOBS.ENABLED_FLAG(+) = 'Y'
AND SYSDATE BETWEEN JOBS.START_DATE_ACTIVE(+)
AND NVL(JOBS.END_DATE_ACTIVE(+)
, SYSDATE + 1)
AND JOBS.LOOKUP_CODE(+) = LE.JOB_TITLE_CODE
AND LOOK5.LOOKUP_CODE(+) = PM.TYPE
AND LOOK5.LOOKUP_TYPE(+) = 'PROMOTION_TYPE'

Columns