View: AST_SALESLEAD_UWQ_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_SALESLEAD_UWQ_V

View Text

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, 'SALES_LEAD_ID' IEU_PARAM_PK_COL
, TO_CHAR(LEAD.SALES_LEAD_ID) IEU_PARAM_PK_VALUE
, LEAD.ASSIGN_TO_SALESFORCE_ID RESOURCE_ID
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, LEAD.LEAD_NUMBER LEAD_NUMBER
, LEAD.DESCRIPTION DESCRIPTION
, LEAD.CUSTOMER_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, LEAD.SALES_LEAD_ID SALES_LEAD_ID
, LEAD.STATUS_CODE STATUS_CODE
, STT.MEANING STATUS_MEANING
, LEAD.ASSIGN_DATE ASSIGN_DATE
, LEAD.ASSIGN_SALES_GROUP_ID ASSIGN_SALES_GROUP_ID
, LEAD.BUDGET_AMOUNT BUDGET_AMOUNT
, LEAD.CURRENCY_CODE CURRENCY_CODE
, LEAD.BUDGET_AMOUNT ||' '||LEAD.CURRENCY_CODE
, LEAD.BUDGET_STATUS_CODE BUDGET_STATUS_CODE
, LEAD.CHANNEL_CODE CHANNEL_CODE
, LEAD.PARENT_PROJECT PARENT_PROJECT
, LEAD.DECISION_TIMEFRAME_CODE DECISION_TIMEFRAME_CODE
, LEAD.VEHICLE_RESPONSE_CODE
, LEAD.KEEP_FLAG
, LEAD.IMPORT_FLAG
, LEAD.DELETED_FLAG
, LEAD.REJECT_REASON_CODE
, LEAD.TOTAL_SCORE
, LEAD.CREATION_DATE CREATION_DATE
, LEAD.LAST_UPDATED_BY UPDATED_BY
, LEAD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LEAD.URGENT_FLAG URGENT_FLAG
, LEAD.QUALIFIED_FLAG QUALIFIED_FLAG
, LEAD.ACCEPT_FLAG ACCEPT_FLAG
, LEAD.LEAD_RANK_ID LEAD_RANK_ID
, LEAD.ASSIGN_TO_SALESFORCE_ID
, RANK.MEANING RANK_MEANING
, NULL
, LEAD.PRIMARY_CONTACT_PARTY_ID CONTACT_PARTY_ID
, PRIM_CNT.PERSON_FIRST_NAME PRIMARY_CONTACT_FIRST_NAME
, PRIM_CNT.PERSON_LAST_NAME PRIMARY_CONTACT_LAST_NAME
, PRIM_CNT.PERSON_MIDDLE_NAME PRIMARY_CONTACT_MIDDLE_NAME
, PRIM_CNT.PERSON_FIRST_NAME ||' '||PRIM_CNT.PERSON_LAST_NAME
, CNT_POINT.CONTACT_POINT_ID PHONE_ID
, CNT_POINT.PHONE_COUNTRY_CODE||DECODE(CNT_POINT.PHONE_COUNTRY_CODE
, NULL
, ''
, ' ')||CNT_POINT.PHONE_AREA_CODE||DECODE(CNT_POINT.PHONE_AREA_CODE
, NULL
, ''
, ' ')|| CNT_POINT.PHONE_NUMBER||DECODE(CNT_POINT.PHONE_NUMBER
, NULL
, ''
, ' ')||CNT_POINT.PHONE_EXTENSION
, ORG_CONT.JOB_TITLE CONTACT_JOB_TITLE
, PARTY_REL.EMAIL_ADDRESS CONTACT_EMAIL_ADDRESS
, SC.NAME SOURCE_NAME
, SC.NAME SOURCE_DESC
, LEAD.ORIG_SYSTEM_REFERENCE
, PARTY.CATEGORY_CODE
, SC.SOURCE_CODE
, LEAD.ADDRESS_ID
, LEAD.SOURCE_PROMOTION_ID
, LOCATION.ADDRESS1
, LOCATION.CITY
, LOCATION.STATE
, LOCATION.COUNTRY
, LOCATION.PROVINCE
, LOCATION.POSTAL_CODE
, LOCATION.ADDRESS1||' '||LOCATION.CITY||' '||LOCATION.STATE||' '||LOCATION.COUNTRY||' '||LOCATION.POSTAL_CODE
, PARTY.SIC_CODE
, NULL
, LEAD.LEAD_RANK_SCORE
, LKP1.MEANING
, LKP2.MEANING
, LKP3.MEANING
, LKP4.MEANING
FROM AS_SALES_LEADS LEAD
, HZ_PARTIES PARTY
, JTF_OBJECTS_B OBJB
, AS_STATUSES_B STB
, AS_STATUSES_TL STT
, AS_SALES_LEAD_RANKS_TL RANK
, HZ_PARTIES PRIM_CNT
, HZ_CONTACT_POINTS CNT_POINT
, HZ_ORG_CONTACTS ORG_CONT
, HZ_LOCATIONS LOCATION
, HZ_PARTY_SITES SITES
, HZ_RELATIONSHIPS CNT_REL
, HZ_PARTIES PARTY_REL
, AMS_P_SOURCE_CODES_V SC
, OE_LOOKUPS LKP1
, AS_LOOKUPS LKP2
, AS_LOOKUPS LKP3
, AS_LOOKUPS LKP4
WHERE LEAD.STATUS_CODE = STB.STATUS_CODE
AND STB.OPP_OPEN_STATUS_FLAG = 'Y'
AND STB.STATUS_CODE = STT.STATUS_CODE
AND STT.LANGUAGE = USERENV('LANG')
AND LEAD.CUSTOMER_ID = PARTY.PARTY_ID
AND RANK.RANK_ID(+) = LEAD.LEAD_RANK_ID
AND RANK.LANGUAGE(+) = USERENV('LANG')
AND OBJB.OBJECT_CODE = 'LEAD'
AND LEAD.PRIMARY_CNT_PERSON_PARTY_ID = PRIM_CNT.PARTY_ID (+)
AND LEAD.PRIMARY_CONTACT_PHONE_ID = CNT_POINT.CONTACT_POINT_ID(+)
AND LOCATION.LOCATION_ID (+) = SITES.LOCATION_ID
AND LEAD.ADDRESS_ID = SITES.PARTY_SITE_ID(+)
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = CNT_REL.RELATIONSHIP_ID
AND CNT_REL.OBJECT_ID (+) = LEAD.CUSTOMER_ID
AND CNT_REL.SUBJECT_ID(+) = LEAD.PRIMARY_CNT_PERSON_PARTY_ID
AND CNT_REL.RELATIONSHIP_TYPE(+) = 'CONTACT'
AND CNT_REL.SUBJECT_TYPE (+) = 'PERSON'
AND CNT_REL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND CNT_REL.PARTY_ID (+) = LEAD.PRIMARY_CONTACT_PARTY_ID
AND PARTY_REL.PARTY_ID (+) = CNT_REL.PARTY_ID
AND LEAD.SOURCE_PROMOTION_ID = SC.SOURCE_CODE_ID(+)
AND LEAD.STATUS_OPEN_FLAG = 'Y'
AND LKP1.LOOKUP_CODE(+) = LEAD.CHANNEL_CODE
AND LKP1.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND LKP2.LOOKUP_CODE(+) = LEAD.VEHICLE_RESPONSE_CODE
AND LKP2.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE'
AND LKP3.LOOKUP_CODE(+) = LEAD.DECISION_TIMEFRAME_CODE
AND LKP3.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME'
AND LKP4.LOOKUP_CODE(+) = LEAD.BUDGET_STATUS_CODE
AND LKP4.LOOKUP_TYPE(+) = 'BUDGET_STATUS'

Columns