VIEW: APPS.AST_SALESLEAD_UWQ_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'