VIEW: APPS.AML_LEAD_PROCESS_REPT_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)