View: AML_LEAD_PROCESS_REPT_V

Product:	AMS - Marketing
Description:	This view returns lead and its processing rules
Implementation/DBA Data:	APPS.AML_LEAD_PROCESS_REPT_V

View Text

SELECT LEAD.SALES_LEAD_ID
, APP.ENTITY_RULE_APPLIED_ID
, LEAD.LEAD_NUMBER
, LEAD.SOURCE_PROMOTION_ID
, STAGES.MEANING "RESULT"
, ADDRESS.TERRITORY_SHORT_NAME "COUNTRY"
, LEAD.CREATION_DATE
, STATUS.STATUS_CODE
, LK1.MEANING "STAGE"
, STATUS.MEANING "STATUS"
, APP.ACTION_VALUE "EVAL_CODE"
, ADDRESS.COUNTRY "COUNTRY_CODE"
, APP.PARENT_PROCESS_RULE_ID "RULE_ID"
, APP.PROCESS_RULE_ID
, PARENT.PROCESS_RULE_NAME "DOMAIN"
, CHILD.PROCESS_RULE_NAME "SUB_RULE"
, PARENT.RANK "PRECEDENCE"
, LEAD.LEAD_RANK_ID
, LEAD.LEAD_RANK_IND
, LEAD.DESCRIPTION "LEAD_NAME"
, LK1.LOOKUP_CODE "RULE_CODE"
, APP.CREATION_DATE "EVALUATION_DATE"
, APP.PROCESS_STATUS "PROCESS_STATUS_CODE"
, APP.ENTITY_DETAIL
, LK2.MEANING "PROCESS_STATUS"
, APP.WINNING_RULE_FLAG
, APP.LATEST_FLAG
, LEAD.ACCEPT_FLAG
, RANK.MEANING "LEAD_RANK"
, LEAD.CURRENCY_CODE
, LEAD.CHANNEL_CODE
FROM PV_ENTITY_RULES_APPLIED APP
, PV_PROCESS_RULES_VL PARENT
, PV_PROCESS_RULES_VL CHILD
, FND_LOOKUP_VALUES_VL LK1
, FND_LOOKUP_VALUES_VL LK2
, AS_SALES_LEADS LEAD
, AS_STATUSES_VL STATUS
, ( SELECT SITE.PARTY_SITE_ID
, LOCATION.COUNTRY
, TRTY.TERRITORY_SHORT_NAME
FROM HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOCATION
, FND_TERRITORIES_VL TRTY
WHERE SITE.LOCATION_ID = LOCATION.LOCATION_ID
AND LOCATION.COUNTRY = TRTY.TERRITORY_CODE ) ADDRESS
, AML_LEAD_PROCESSING_STAGES_V STAGES
, AML_LEAD_PROCESSING_STAGES_V RANK
WHERE APP.ENTITY = 'SALES_LEAD'
AND APP.ENTITY_ID = LEAD.SALES_LEAD_ID
AND APP.PARENT_PROCESS_RULE_ID = PARENT.PROCESS_RULE_ID (+)
AND APP.PROCESS_RULE_ID = CHILD.PROCESS_RULE_ID (+)
AND APP.PROCESS_TYPE = LK1.LOOKUP_CODE
AND LK1.LOOKUP_TYPE = 'PV_PROCESS_TYPE'
AND APP.PROCESS_STATUS = LK2.LOOKUP_CODE
AND LK2.LOOKUP_TYPE = 'PV_RULES_APPLIED_STATUS'
AND LEAD.STATUS_CODE = STATUS.STATUS_CODE
AND LEAD.ADDRESS_ID = ADDRESS.PARTY_SITE_ID(+)
AND APP.PROCESS_TYPE = STAGES.PROCESS_TYPE
AND APP.ACTION_VALUE = STAGES.LOOKUP_CODE
AND TO_CHAR(LEAD.LEAD_RANK_ID) = RANK.LOOKUP_CODE (+)

Columns