SELECT /* Fix for bug # 4751315 */
1 umarker,
qot.creation_date transaction_date, --Cart Creation Date is considered
qot.minisite_id site_id,
qot.cust_party_id customer_id,
count(qot.quote_header_id) carts, --Count of Carts both Registered and Anonymous
count(qot.order_id) orders, --Count of Orders is taken for Cart Conversion Ratio.
null anonymous_carts,
null lead_rank,
qot.marketing_source_code_id_hdr marketing_source_code_id_hdr,
null source_primary_reference,
count(*) d_total_cnt
FROM
ibe_bi_carts_mv qot
WHERE
published_quote_flag= 'N' --Only Published Carts are to be picked up.
GROUP BY
qot.creation_date,
qot.minisite_id,
qot.cust_party_id,
qot.marketing_source_code_id_hdr
UNION ALL
--This Query is for Fetching the Anonymous Carts.
--The ibw_guest_party Table holds the Guest Party and all the Carts which are not associated with any party id
--would be assigned the cust_party_id as the Customer id of this table and this enables us in calculating the
--Anonymous Carts count.
select
2 umarker,
qot.creation_date transaction_date, --Cart Creation date
qot.minisite_id site_id,
qot.cust_party_id customer_id, --Guest Party Id
null carts,
null orders,
count(qot.quote_header_id) anonymous_carts, --Anonymous Carts count
null lead_rank,
null marketing_source_code_id_hdr,
null source_primary_reference,
count(*) d_total_cnt
FROM
ibe_bi_carts_mv qot
,IBW.IBW_GUEST_PARTY gst
WHERE
published_quote_flag= 'N' --Published Quotes are to be excluded.
and qot.cust_party_id=gst.customer_id
GROUP BY
qot.creation_date,
qot.minisite_id,
qot.cust_party_id
UNION ALL
--This Query is for Fetching the A Leads
select
3 umarker,
bim.lead_creation_date transaction_date, --Lead Creation Date is to be considered and hence bim.lead_creation_date is used
qot.minisite_id site_id,
qot.cust_party_id customer_id,
null carts,
null orders,
null anonymous_carts,
1 lead_rank, --A Leads Count is captured COUNT(lead_rank_id) is changed to 1 for Bug#:4940826
qot.marketing_source_code_id_hdr marketing_source_code_id_hdr,
bim.source_primary_reference source_primary_reference, --Bug#:4940826
COUNT(*) d_total_cnt
FROM
ibe_bi_carts_mv qot
,BIM.BIM_I_LEAD_FACTS bim
,BIM.BIM_R_CODE_DEFINITIONS code
WHERE
qot.published_quote_flag= 'N' --Published Quotes are to be excluded.
and bim.source_secondary_reference = to_char(qot.minisite_id) --4929780
and bim.source_primary_reference = to_char(qot.quote_header_id) --4929780
AND code.object_type = 'RANK_DBI' --This condition is necessary for DBI reports.
and bim.lead_rank_id = code.object_def
and code.column_name='A' --Only A Leads are needed.
and bim.lead_source in ('NEW','STORE') --4929780
GROUP BY
bim.lead_creation_date,
qot.minisite_id,
qot.cust_party_id,
qot.marketing_source_code_id_hdr,
bim.source_primary_reference --Bug#:4940826
[Home] [Help]