[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT from_currency from_currency,
decode(conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),lead_creation_date) lead_creation_date
FROM bim_i_lead_facts_stg
WHERE (conversion_rate < 0
OR conversion_rate IS NULL)
AND from_currency is not null
AND lead_creation_date >= p_start_date
ORDER BY from_currency;
SELECT COUNT(*) INTO l_cnt_miss_rate FROM bim_i_lead_facts_stg
WHERE
(conversion_rate < 0
OR conversion_rate IS NULL)
AND from_currency is not null
AND lead_creation_date >= p_start_date;
SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
FROM dual;
INSERT /*+ append parallel */
INTO bim_i_lead_facts_stg LDF
(
lead_id
,lead_line_id
,group_id
,resource_id
,lead_rank_id
,lead_source
,lead_status
,lead_country
,source_code_id
,interest_type_id
,primary_interest_code_id
,secondary_interest_code_id
,item_id
,organization_id
,lead_creation_date
,lead_touched_date
,lead_dead_date
,channel_code
-- ,lead_amount
-- ,currency_code
,close_reason
,accept_flag
,qualified_flag
,source_primary_reference
,source_secondary_reference
,customer_id
,cust_category
,status_open_flag
,lead_rank_score
,expiration_date
-- ,conversion_rate
-- ,from_currency
,product_category_id
,CUSTOMER_FLAG
,lead_name
)
SELECT
x.sales_lead_id lead_id
,y.sales_lead_line_id lead_line_id
,x.assign_sales_group_id group_id
,x.assign_to_salesforce_id resource_id
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,x.country lead_country
,x.source_promotion_id source_code_id
,y.interest_type_id interest_type_id
,y.primary_interest_code_id primary_interest_code_id
,y.secondary_interest_code_id secondary_interest_code_id
,nvl(y.inventory_item_id,-1) item_id
,decode(y.inventory_item_id,null,-1,nvl(y.organization_id,-1)) organization_id
,trunc(x.creation_date) lead_creation_date
,(CASE
WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
ELSE null
END
) lead_touched_date
,decode(x.status_code,l_dead_status,trunc(x.last_update_date),null) lead_dead_date
,x.channel_code channel_code
-- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
-- ,x.currency_code currency_code
,x.close_reason close_reason
,x.accept_flag accept_flag
,x.qualified_flag qualified_flag
,x.source_primary_reference source_primary_reference
,x.source_secondary_reference source_secondary_reference
,x.customer_id customer_id
,NULL cust_category
,x.status_open_flag status_open_flag
,x.lead_rank_score lead_rank_score
,x.expiration_date expiration_date
-- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
-- ,nvl(x.currency_code,'USD') from_currency
,y.category_id product_category_id
,'N' CUSTOMER_FLAG
,x.DESCRIPTION
FROM
as_sales_leads X
,as_sales_lead_lines Y
WHERE
X.creation_date between p_start_date and l_sysdate
AND X.sales_lead_id = Y.sales_lead_id(+) ;
/*update bim.bim_i_lead_facts_stg stg
set stg.cust_category
= (select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A')
where
exists
(select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A');
update bim_i_lead_facts_stg stg
set stg.cust_category
= (select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A'--Active
and b.START_DATE_ACTIVE =
( select max(START_DATE_ACTIVE) from hz_code_assignments c
where
c.OWNER_TABLE_NAME='HZ_PARTIES'
and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and c.Primary_flag = 'Y'
and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and c.status='A'
and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID)
)
where
exists
(select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A'--Active
and b.START_DATE_ACTIVE =
( select max(START_DATE_ACTIVE) from hz_code_assignments c
where
c.OWNER_TABLE_NAME='HZ_PARTIES'
and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and c.Primary_flag = 'Y'
and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and c.status='A'
and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID) );
UPDATE bim_i_lead_facts_stg stg SET CUSTOMER_FLAG='Y'
WHERE
EXISTS
(SELECT 1 from HZ_CUST_ACCOUNTS a,hz_parties b
WHERE a.party_id=stg.customer_id
AND stg.lead_creation_date >= trunc(a.creation_date)
AND a.party_id=b.party_id
AND b.certification_level is not null);
UPDATE bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
WHERE
EXISTS
(SELECT 1 from HZ_CUST_ACCOUNTS a
WHERE a.party_id=stg.customer_id
AND stg.lead_creation_date >= trunc(a.creation_date)
);
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_LEADS');
INSERT /*+ append parallel */
INTO bim_i_lead_facts LDF
(
creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,lead_id
,lead_line_id
,group_id
,resource_id
,lead_rank_id
,lead_source
,lead_status
,lead_region
,lead_country
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,object_region
,object_country
,object_status
,object_purpose
,child_object_region
,child_object_country
,child_object_status
,child_object_purpose
,object_category_id
,business_unit_id
,lead_creation_date
,lead_touched_date
,lead_dead_date
,item_id
,organization_id
,channel_code
,lead_amount
,close_reason
,accept_flag
,qualified_flag
,source_primary_reference
,source_secondary_reference
,customer_id
,cust_category
,status_open_flag
,lead_rank_score
,expiration_date
,product_category_id
,CUSTOMER_FLAG
,lead_name
)
SELECT
sysdate creation_date
,sysdate last_update_date
,-1 created_by
,-1 last_updated_by
,-1 last_update_login
,x.lead_id lead_id
,x.lead_line_id lead_line_id
,x.group_id group_id
,x.resource_id resource_id
,x.lead_rank_id lead_rank_id
,x.lead_source lead_source
,x.lead_status lead_status
,t.parent_territory_code lead_region
,x.lead_country lead_country
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,a.object_region object_region
,a.object_country object_country
,a.object_status object_status
,a.object_purpose object_purpose
,a.child_object_region child_object_region
,a.child_object_country child_object_country
,a.child_object_status child_object_status
,a.child_object_purpose child_object_purpose
,a.category_id object_category_id
,a.business_unit_id business_unit_id
,x.lead_creation_date lead_creation_date
,x.lead_touched_date lead_touched_date
,x.lead_dead_date lead_dead_date
,x.item_id item_id
,x.organization_id organization_id
,x.channel_code channel_code
,x.lead_amount lead_amount
,x.close_reason close_reason
,x.accept_flag accept_flag
,x.qualified_flag qualified_flag
,x.source_primary_reference source_primary_reference
,x.source_secondary_reference source_secondary_reference
,x.customer_id customer_id
,x.cust_category cust_category
,x.status_open_flag status_open_flag
,x.lead_rank_score lead_rank_score
,x.expiration_date expiration_date
,nvl(x.product_category_id,-1) product_category_id
,x.customer_flag customer_flag
,x.lead_name Lead_name
FROM
bim_i_lead_facts_stg X
,bim_i_source_codes A
,bis_territory_hierarchies T
WHERE
X.source_code_id = A.source_code_id(+)
AND T.parent_territory_type(+) = 'AREA'
AND T.child_territory_type(+) = 'COUNTRY'
AND T.child_territory_code(+) = X.lead_country
;
UPDATE bim_i_lead_facts facts
SET (facts.lead_converted_date, facts.lead_touched_date)
= (SELECT TRUNC(MIN(slo.creation_date)), TRUNC(MIN(slo.creation_date))
FROM
as_sales_lead_opportunity slo
WHERE
slo.creation_date between p_start_date and l_sysdate
AND slo.sales_lead_id = facts.lead_id
AND facts.lead_dead_date is null
)
WHERE
EXISTS (SELECT 1
FROM
as_sales_lead_opportunity slo
WHERE
slo.creation_date between p_start_date and l_sysdate
AND slo.sales_lead_id = facts.lead_id
AND facts.lead_dead_date is null
);
UPDATE bim_i_lead_facts facts
SET (facts.lead_closed_date, facts.lead_touched_date)
= (SELECT TRUNC(MIN(hist.creation_date)), TRUNC(MIN(hist.creation_date))
FROM
as_sales_leads_log hist
,as_statuses_b st
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code not in (l_conv_opp_status, l_dead_status)
AND hist.status_code = st.status_code
AND st.opp_open_status_flag = 'N'
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_converted_date is null
AND facts.lead_dead_date is null
)
WHERE
EXISTS (SELECT 1
FROM
as_sales_leads_log hist
,as_statuses_b st
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code not in (l_conv_opp_status, l_dead_status)
AND hist.status_code = st.status_code
AND st.opp_open_status_flag = 'N'
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_converted_date is null
AND facts.lead_dead_date is null
);
UPDATE bim_i_lead_facts facts
SET facts.lead_touched_date
=(CASE
WHEN lead_dead_date is not null THEN lead_dead_date
WHEN lead_converted_date is not null THEN lead_converted_date
WHEN lead_closed_date is not null THEN lead_closed_date
ELSE null
END
)
where lead_touched_date is null;
SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
FROM dual;
INSERT /*+ append parallel */
INTO bim_i_lead_facts_stg LDF
(
lead_id
,group_id
,resource_id
,lead_rank_id
,lead_source
,lead_status
,lead_country
,source_code_id
,lead_creation_date
,lead_touched_date
,channel_code--
-- ,lead_amount
-- ,currency_code
,close_reason
,accept_flag
,qualified_flag
,source_primary_reference
,source_secondary_reference
,customer_id
,cust_category
,status_open_flag
,lead_rank_score
,expiration_date
-- ,conversion_rate
-- ,from_currency
,CUSTOMER_FLAG
,lead_name
)
SELECT
x.sales_lead_id lead_id
,x.assign_sales_group_id group_id
,x.assign_to_salesforce_id resource_id
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,x.country lead_country
,x.source_promotion_id source_code_id
,trunc(x.creation_date) lead_creation_date
,(CASE
WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
ELSE null
END
) lead_touched_date
,x.channel_code channel_code
-- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
-- ,x.currency_code currency_code
,x.close_reason close_reason
,x.accept_flag accept_flag
,x.qualified_flag qualified_flag
,x.source_primary_reference source_primary_reference
,x.source_secondary_reference source_secondary_reference
,x.customer_id customer_id
,NULL cust_category
,x.status_open_flag status_open_flag
,x.lead_rank_score lead_rank_score
,x.expiration_date expiration_date
-- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
-- ,nvl(x.currency_code,'USD') from_currency
,'N' CUSTOMER_FLAG
,x.DESCRIPTION
FROM
as_sales_leads X
WHERE
X.last_update_date between p_start_date and l_sysdate
AND X.creation_date >=p_global_date ;
INSERT /*+ append parallel */
INTO bim_i_lead_facts_stg LDF
(
lead_id
,lead_line_id
,group_id
,resource_id
,lead_rank_id
,lead_source
,lead_status
,lead_country
,source_code_id
,interest_type_id
,primary_interest_code_id
,secondary_interest_code_id
,item_id
,organization_id
,lead_creation_date
,lead_touched_date
,channel_code
-- ,lead_amount
-- ,currency_code
,close_reason
,accept_flag
,qualified_flag
,source_primary_reference
,source_secondary_reference
,customer_id
,cust_category
,status_open_flag
,lead_rank_score
,expiration_date
-- ,conversion_rate
-- ,from_currency
,product_category_id
,CUSTOMER_FLAG
,lead_name
)
SELECT
x.sales_lead_id lead_id
,y.sales_lead_line_id lead_line_id
,x.assign_sales_group_id group_id
,x.assign_to_salesforce_id resource_id
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,x.country lead_country
,x.source_promotion_id source_code_id
,y.interest_type_id interest_type_id
,y.primary_interest_code_id primary_interest_code_id
,y.secondary_interest_code_id secondary_interest_code_id
,nvl(y.inventory_item_id,-1) item_id
,decode(y.inventory_item_id,null,-1,nvl(y.organization_id,-1)) organization_id
,trunc(x.creation_date) lead_creation_date
,(CASE
WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
ELSE null
END
) lead_touched_date
,x.channel_code channel_code
-- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
-- ,x.currency_code currency_code
,x.close_reason close_reason
,x.accept_flag accept_flag
,x.qualified_flag qualified_flag
,x.source_primary_reference source_primary_reference
,x.source_secondary_reference source_secondary_reference
,x.customer_id customer_id
,NULL cust_category
,x.status_open_flag status_open_flag
,x.lead_rank_score lead_rank_score
,x.expiration_date expiration_date
-- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
-- ,nvl(x.currency_code,'USD') from_currency
,y.category_id product_category_id
,'N' CUSTOMER_FLAG
,x.DESCRIPTION
FROM
as_sales_leads X
,as_sales_lead_lines Y
WHERE
(X.last_update_date between p_start_date and l_sysdate OR Y.last_update_date between p_start_date and l_sysdate)
AND X.sales_lead_id = Y.sales_lead_id
AND X.creation_date >=p_global_date ;
update bim_i_lead_facts_stg stg
set stg.cust_category
= (select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A'--Active
and b.START_DATE_ACTIVE =
( select max(START_DATE_ACTIVE) from hz_code_assignments c
where
c.OWNER_TABLE_NAME='HZ_PARTIES'
and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and c.Primary_flag = 'Y'
and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and c.status='A'
and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID)
)
where
exists
(select b.class_code from hz_code_assignments b
where
stg.customer_id=b.OWNER_TABLE_ID
and b.OWNER_TABLE_NAME='HZ_PARTIES'
and b.Primary_flag = 'Y'
and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and b.status='A'--Active
and b.START_DATE_ACTIVE =
( select max(START_DATE_ACTIVE) from hz_code_assignments c
where
c.OWNER_TABLE_NAME='HZ_PARTIES'
and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
and c.Primary_flag = 'Y'
and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
and c.status='A'
and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID) );
update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
where
exists
(select 1 from HZ_CUST_ACCOUNTS a,hz_parties b
where a.party_id=stg.customer_id
and stg.lead_creation_date >= trunc(a.creation_date)
and a.party_id=b.party_id
and b.certification_level is not null);
update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
where
exists
(select 1 from HZ_CUST_ACCOUNTS a
where a.party_id=stg.customer_id
and stg.lead_creation_date >= trunc(a.creation_date));
SELECT
sysdate creation_date
,sysdate last_update_date
,-1 created_by
,-1 last_updated_by
,-1 last_update_login
,x.lead_id lead_id
,x.lead_line_id lead_line_id
,x.group_id group_id
,x.resource_id resource_id
,x.lead_rank_id lead_rank_id
,x.lead_source lead_source
,x.lead_status lead_status
--,x.status_open_flag open_flag
,t.parent_territory_code lead_region
,x.lead_country lead_country
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,a.object_region object_region
,a.object_country object_country
,a.object_status object_status
,a.object_purpose object_purpose
,a.child_object_region child_object_region
,a.child_object_country child_object_country
,a.child_object_status child_object_status
,a.child_object_purpose child_object_purpose
,a.category_id object_category_id
,a.business_unit_id business_unit_id
,x.lead_creation_date lead_creation_date
,x.lead_touched_date lead_touched_date
,nvl(x.item_id,-1) item_id
,nvl(x.organization_id,-1) organization_id
,x.channel_code channel_code
,x.lead_amount lead_amount
,x.close_reason close_reason
,x.accept_flag accept_flag
,x.qualified_flag qualified_flag
,x.source_primary_reference source_primary_reference
,x.source_secondary_reference source_secondary_reference
,x.customer_id customer_id
,x.cust_category cust_category
,x.status_open_flag status_open_flag
,x.lead_rank_score lead_rank_score
,x.expiration_date expiration_date
,nvl(x.product_category_id,-1) product_category_id
,x.customer_flag customer_flag
,x.lead_name lead_name
FROM
bim_i_lead_facts_stg X
,bim_i_source_codes A
,bis_territory_hierarchies T
WHERE
X.source_code_id = A.source_code_id(+)
AND T.parent_territory_type(+) = 'AREA'
AND T.child_territory_type(+) = 'COUNTRY'
AND T.child_territory_code(+) = X.lead_country
) changes
ON (facts.lead_id = changes.lead_id
AND nvl(facts.lead_line_id,-1) = nvl(changes.lead_line_id,-1)
)
WHEN MATCHED THEN UPDATE SET
facts.last_update_date = changes.last_update_date
,facts.group_id = changes.group_id
,facts.resource_id = changes.resource_id
,facts.lead_rank_id = changes.lead_rank_id
,facts.lead_source = changes.lead_source
,facts.lead_status = changes.lead_status
--,facts.open_flag = changes.open_flag
,facts.lead_region = changes.lead_region
,facts.lead_country = changes.lead_country
,facts.source_code_id = changes.source_code_id
,facts.object_type = changes.object_type
,facts.object_id = changes.object_id
,facts.child_object_type = changes.child_object_type
,facts.child_object_id = changes.child_object_id
,facts.object_region = changes.object_region
,facts.object_country = changes.object_country
,facts.object_status = changes.object_status
,facts.object_purpose = changes.object_purpose
,facts.child_object_region = changes.child_object_region
,facts.child_object_country = changes.child_object_country
,facts.child_object_status = changes.child_object_status
,facts.child_object_purpose = changes.child_object_purpose
,facts.business_unit_id = changes.business_unit_id
,facts.object_category_id = changes.object_category_id
,facts.lead_touched_date = decode(facts.lead_touched_date, null, decode(changes.lead_touched_date,null,null,changes.lead_touched_date),facts.lead_touched_date)
,facts.item_id = changes.item_id
,facts.organization_id = changes.organization_id
,facts.channel_code = changes.channel_code
,facts.lead_amount = changes.lead_amount
,facts.close_reason = changes.close_reason
,facts.accept_flag = changes.accept_flag
,facts.qualified_flag = changes.qualified_flag
,facts.source_primary_reference = changes.source_primary_reference
,facts.source_secondary_reference = changes.source_secondary_reference
,facts.customer_id = changes.customer_id
,facts.cust_category = changes.cust_category
,facts.status_open_flag = changes.status_open_flag
,facts.lead_rank_score = changes.lead_rank_score
,facts.expiration_date = changes.expiration_date
,facts.product_category_id = changes.product_category_id
,facts.CUSTOMER_FLAG = changes.CUSTOMER_FLAG
,facts.lead_name = changes.lead_name
WHEN NOT MATCHED THEN INSERT
(
facts.creation_date
,facts.last_update_date
,facts.created_by
,facts.last_updated_by
,facts.last_update_login
,facts.lead_id
,facts.lead_line_id
,facts.group_id
,facts.resource_id
,facts.lead_rank_id
,facts.lead_source
,facts.lead_status
--,facts.open_flag
,facts.lead_region
,facts.lead_country
,facts.source_code_id
,facts.object_type
,facts.object_id
,facts.child_object_type
,facts.child_object_id
,facts.object_region
,facts.object_country
,facts.object_status
,facts.object_purpose
,facts.child_object_region
,facts.child_object_country
,facts.child_object_status
,facts.child_object_purpose
,facts.business_unit_id
,facts.object_category_id
,facts.lead_creation_date
,facts.lead_touched_date
,facts.item_id
,facts.organization_id
,facts.channel_code
,facts.lead_amount
,facts.close_reason
,facts.accept_flag
,facts.qualified_flag
,facts.source_primary_reference
,facts.source_secondary_reference
,facts.customer_id
,facts.cust_category
,facts.status_open_flag
,facts.lead_rank_score
,facts.expiration_date
,facts.product_category_id
,facts.customer_flag
,facts.lead_name
)
VALUES
(
changes.creation_date
,changes.last_update_date
,changes.created_by
,changes.last_updated_by
,changes.last_update_login
,changes.lead_id
,changes.lead_line_id
,changes.group_id
,changes.resource_id
,changes.lead_rank_id
,changes.lead_source
,changes.lead_status
--,changes.open_flag
,changes.lead_region
,changes.lead_country
,changes.source_code_id
,changes.object_type
,changes.object_id
,changes.child_object_type
,changes.child_object_id
,changes.object_region
,changes.object_country
,changes.object_status
,changes.object_purpose
,changes.child_object_region
,changes.child_object_country
,changes.child_object_status
,changes.child_object_purpose
,changes.business_unit_id
,changes.object_category_id
,changes.lead_creation_date
,changes.lead_touched_date
,changes.item_id
,changes.organization_id
,changes.channel_code
,changes.lead_amount
,changes.close_reason
,changes.accept_flag
,changes.qualified_flag
,changes.source_primary_reference
,changes.source_secondary_reference
,changes.customer_id
,changes.cust_category
,changes.status_open_flag
,changes.lead_rank_score
,changes.expiration_date
,changes.product_category_id
,changes.customer_flag
,changes.lead_name
);
UPDATE bim_i_lead_facts facts
SET facts.lead_dead_date
= (SELECT TRUNC(MIN(hist.creation_date))
FROM
as_sales_leads_log hist
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code = l_dead_status
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
)
WHERE
EXISTS (SELECT 1
FROM
as_sales_leads_log hist
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code = l_dead_status
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
);
UPDATE bim_i_lead_facts facts
SET facts.lead_converted_date
= (SELECT TRUNC(MIN(slo.creation_date))
FROM
as_sales_lead_opportunity slo
WHERE
slo.creation_date between p_start_date and l_sysdate
AND slo.sales_lead_id = facts.lead_id
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
)
WHERE
EXISTS (SELECT 1
FROM
as_sales_lead_opportunity slo
WHERE
slo.creation_date between p_start_date and l_sysdate
AND slo.sales_lead_id = facts.lead_id
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
);
UPDATE bim_i_lead_facts facts
SET facts.lead_closed_date
= (SELECT TRUNC(MIN(hist.creation_date))
FROM
as_sales_leads_log hist
,as_statuses_b st
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code not in (l_conv_opp_status, l_dead_status)
AND hist.status_code = st.status_code
AND st.opp_open_status_flag = 'N'
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
)
WHERE
EXISTS (SELECT 1
FROM
as_sales_leads_log hist
,as_statuses_b st
WHERE
hist.last_update_date between p_start_date and l_sysdate
AND hist.status_code not in (l_conv_opp_status, l_dead_status)
AND hist.status_code = st.status_code
AND st.opp_open_status_flag = 'N'
AND hist.sales_lead_id = facts.lead_id
AND hist.status_code = facts.lead_status
AND facts.lead_dead_date is null
AND facts.lead_converted_date is null
AND facts.lead_closed_date is null
);
UPDATE bim_i_lead_facts facts
SET facts.lead_touched_date
=(CASE
WHEN lead_dead_date is not null THEN lead_dead_date
WHEN lead_converted_date is not null THEN lead_converted_date
WHEN lead_closed_date is not null THEN lead_closed_date
ELSE null
END
)
where lead_touched_date is null
and last_update_date between p_start_date and l_sysdate;
DELETE
FROM bim_i_lead_facts
WHERE lead_line_id IS NULL
AND lead_id in (SELECT
lead_id
FROM bim_i_lead_facts_stg
WHERE lead_line_id is NOT NULL);