Datanory Production » Datanory - GOLDCREST

select * from txn_mobile_collection_header where col_id like 'd264571c-4cff-478d-844d-0d5633733ab7'
select * from txn_mobile_collection_header_AT where col_id like 'd264571c-4cff-478d-844d-0d5633733ab7' ORDER BY AT_DATE DESC
select * from txn_mobile_collection_detail where col_id like 'd264571c-4cff-478d-844d-0d5633733ab7'
select * from txn_mobile_collection_invoice where inv_id= '6697e247-9ea9-4050-b46e-735a5432fbd2' ORDER BY CREATED_DATE DESC
select * from txn_mobile_collection_invoice_AT where inv_id= '6697e247-9ea9-4050-b46e-735a5432fbd2' ORDER BY AT_DATE DESC
select * from txn_invoice_history_header where invoice_no='INJNL001833-240514'

exec sp_rpt_salesman_activity_report @check_in_start_date='2024-05-14 00:00:00',@check_in_end_date='2024-05-14 00:00:00',
 @branch=N'3E565D280EA448A0A4F212F580279DDE,A7510E3E8DD44308B8DA55EB6628EC14,87F15B26421B4FD283A1C4947971C12E,ECB5D76222194F0E9176D6FC48F249BF,71DFE074-9074-449E-A61A-BC39010A60B8,1ee72bfc1c314c99ac14e18eb768a45d,08D26C39EC1D477EA1DEA9CBADE6DDB5,406eb6e6c006411bad7ae014b4a3c3af,E73A4F82497E433CB2D6DAF53FDA1C7D,45454bbc537340619b7860c58cfaf5ca,2492baa10aee4fc586582f08d5b1b97c,777e95017b7e4e6d9d9f21acc5807b59,7b488a718c6742acabc2121665b59d7b,e534eacb758a45a296beecd25cb75491,a263d06e9a504ebe9f52a430f298b1ad,0a05ab339b0b402bb9912affe5772e41,4C911E458E95411F87E8C88FDD6F87EE',
 @area=N'EE20A69370DA4C70A5B7256868A1B536,750D3DDE152E4C4F9A4ED9F6348E3AA8,9C96E509103849EDB45AAD0320DE9B76',
 @salesman=N'afa9160a6eff4963b8a8bb53322f8d57',@planned_route=N'Yes,No',@tenant_id=N'GOLDCREST'

declare

@check_in_start_date date,
@check_in_end_date date,
@branch varchar (max),
@area varchar (max),
@salesman varchar (max),
@planned_route varchar (50),
@tenant_id varchar (50)
select
@check_in_start_date='2024-05-14 00:00:00',@check_in_end_date='2024-05-14 00:00:00',
@branch=N'3E565D280EA448A0A4F212F580279DDE,A7510E3E8DD44308B8DA55EB6628EC14,87F15B26421B4FD283A1C4947971C12E,ECB5D76222194F0E9176D6FC48F249BF,71DFE074-9074-449E-A61A-BC39010A60B8,1ee72bfc1c314c99ac14e18eb768a45d,08D26C39EC1D477EA1DEA9CBADE6DDB5,406eb6e6c006411bad7ae014b4a3c3af,E73A4F82497E433CB2D6DAF53FDA1C7D,45454bbc537340619b7860c58cfaf5ca,2492baa10aee4fc586582f08d5b1b97c,777e95017b7e4e6d9d9f21acc5807b59,7b488a718c6742acabc2121665b59d7b,e534eacb758a45a296beecd25cb75491,a263d06e9a504ebe9f52a430f298b1ad,0a05ab339b0b402bb9912affe5772e41,4C911E458E95411F87E8C88FDD6F87EE',
@area=N'EE20A69370DA4C70A5B7256868A1B536,750D3DDE152E4C4F9A4ED9F6348E3AA8,9C96E509103849EDB45AAD0320DE9B76',
@salesman=N'afa9160a6eff4963b8a8bb53322f8d57',@planned_route=N'Yes,No',@tenant_id=N'GOLDCREST'

create table #area(
area_id varchar(50) collate DATABASE_DEFAULT )
INSERT into #area select * from mcc_fn_splitMVParam(@area,',')

create table #branch(
branch_id varchar (50) collate DATABASE_DEFAULT)
INSERT into #branch select * from mcc_fn_splitMVParam(@branch,',')

create table #salesman(
salesman_id varchar (50) collate DATABASE_DEFAULT)
INSERT INTO #salesman
exec sp_rpt_split_salesman_value @salesman
--select * from mcc_fn_splitMVParam(@salesman,',')

create table #planned_route(
planned_status varchar (50) collate DATABASE_DEFAULT)
INSERT INTO #planned_route select * from mcc_fn_splitMVParam(@planned_route,',')

--select customer_code, customer_name, branch_desc, area_description, branch_code,
--salesman_code, salesman_name, Format(check_in_date, 'dd-MM-yyyy') as check_in_date, cast(check_in_date as time) as check_in_time,
--planned_route,
--case when len(activity) >0 then
--left(activity, len(activity) - 1) else '' end as activity
---- add on 2.12.2021 YQ
--, Format(check_out_date, 'dd-MM-yyyy') as check_out_date, check_out_date as check_out_time, customer_class_description
---- add on 2.12.2021 YQ

--from (

--select c.customer_code, c.customer_name, b.branch_desc, a.area_description,
--m.member_id, mh.tenant_id, a.area_id, b.branch_id,b.branch_code,
--m.member_cd as salesman_code, CONCAT (m.first_name, m.last_name) AS salesman_name,
--ci.check_in_date, cc.customer_class_description,
--CONCAT (z1.activity, z2.activity, z3.activity, z4.activity) AS activity ,
--CASE WHEN exists (select * from txn_mobile_header mhh inner join customer cc on mhh.customer_id= cc.customer_id
-- inner join member mm on mm.member_id= mhh.salesman_id inner join mst_route r
-- ON mh.salesman_id= r.member_id and mh.customer_id = r.customer_id and cast( mh.created_date as date) = r.date) THEN 'Yes'
--ELSE 'No'
--END AS planned_route,

---- add on 2.12.2021 YQ
--co.check_out_date, check_out_date as check_out_time
---- add on 2.12.2021 YQ

--from txn_mobile_header mh
--INNER JOIN customer c
--ON c.customer_id= mh.customer_id
--inner join mst_customer_class cc on cc.customer_class_id = c.customer_class_id
--INNER JOIN mst_branch b
--ON c.branch_id= b.branch_id
--INNER JOIN mst_area a
--ON c.area_id= a.area_id
--INNER JOIN txn_mobile_check_in ci
--ON mh.txn_id= ci.txn_id

---- add on 2.12.2021 YQ
--INNER JOIN txn_mobile_check_out co
--ON co.txn_id = mh.txn_id
---- add on 2.12.2021 YQ

--INNER JOIN member m
--ON mh.salesman_id= m.member_id
----LEFT JOIN mst_route r
----ON mh.salesman_id= r.member_id and mh.customer_id = r.customer_id and mh.created_date = r.date

--LEFT JOIN (select mh.txn_id ,mh.customer_id, mh.salesman_id, --Stock Count
--CASE
--WHEN sc.txn_id IS NOT NULL THEN 'Stock Count, '
--ELSE NULL
--END AS activity
--from txn_mobile_header mh
--left join txn_mobile_stock_count sc
--on mh.txn_id = sc.txn_id

--group by mh.txn_id ,mh.customer_id, mh.salesman_id,
--CASE
--WHEN sc.txn_id IS NOT NULL THEN 'Stock Count, '
--ELSE NULL
--END ) z1
--ON mh.txn_id= z1.txn_id

--LEFT JOIN (SELECT mh.txn_id, mh.customer_id, mh.salesman_id, --Collection
--CASE
--WHEN ch.txn_id is NOT NULL THEN 'Collection, '
--ELSE NULL
--END AS activity
--from txn_mobile_header mh
--LEFT JOIN txn_mobile_collection_header ch
--ON mh.txn_id= ch.txn_id

--group by mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE
--WHEN ch.txn_id is NOT NULL THEN 'Collection, '
--ELSE NULL
--END) z2
--ON mh.txn_id= z2.txn_id

--LEFT JOIN (SELECT mh.txn_id, mh.customer_id, mh.salesman_id, --Goods Return
--CASE
--WHEN rh.txn_id is NOT NULL THEN 'Goods Return, '
--ELSE NULL
--END AS activity
--from txn_mobile_header mh
--LEFT JOIN txn_return_header rh
--ON mh.txn_id= rh.txn_id

--group by mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE
--WHEN rh.txn_id is NOT NULL THEN 'Goods Return, '
--ELSE NULL
--END) z3
--ON mh.txn_id= z3.txn_id --what is this z3 txn_id

--LEFT JOIN (select mh.txn_id, mh.customer_id, mh.salesman_id, --sales order
--CASE
--WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order,'
--ELSE NULL
--END AS activity
--from txn_mobile_header mh
--INNER JOIN txn_sales_order_header oh
--ON mh.txn_id= oh.mobile_hdr_id

--group by mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE
--WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order,'
--ELSE NULL
--END) z4
--ON mh.txn_id= z4.txn_id
--)x

--WHERE
--cast (check_in_date as date) BETWEEN @check_in_start_date AND @check_in_end_date
--AND exists (select 1 from #branch b1 where x.branch_id = b1.branch_id)
--AND exists (select 1 from #area a1 where x.area_id = a1.area_id)
--AND exists (select 1 from #salesman s1 where x.member_id = s1.salesman_id)
--AND exists (select 1 from #planned_route p1 where x.planned_route = p1.planned_status)
--AND tenant_id = @tenant_id
--order by cast(check_in_date as date) , cast(check_in_date as time) desc

--BEGIN

--WITH [CHECK_WITH_STATUS] AS (
--select 'Missed Call' as visit_status , mc.txn_id
--from txn_mobile_missed_call mc
--where exists(select 1 from txn_mobile_header h where h.txn_id = mc.txn_id)
--union
--select x.visit_status, x.txn_id from (
--select 'Yes' as visit_status , ci.txn_id from txn_mobile_check_in ci
--inner join txn_mobile_check_out co on ci.txn_id = co.txn_id
--where exists
--(select 1 from txn_mobile_header h
--inner join mst_route r on h.salesman_id = r.member_id and h.customer_id = r.customer_id and cast(h.created_date as date) = r.date and ci.txn_id = h.txn_id)
--union
--select 'No'as visit_status, ci.txn_id from txn_mobile_check_in ci
--inner join txn_mobile_check_out co on ci.txn_id = co.txn_id
--where not exists(select 1 from txn_mobile_header h
--inner join mst_route r on h.salesman_id = r.member_id and h.customer_id = r.customer_id and cast(h.created_date as date) = r.date and ci.txn_id = h.txn_id)) x ),
--[planned_route] AS (
--SELECT CASE WHEN EXISTS(
-- SELECT 1 from txn_mobile_header mh
-- left join mst_route r on mhh.salesman_id = r.member_id and mhh.customer_id = r.customer_id and cast(mhh.created_date as date) = r.date
--) THEN 'Yes' ELSE 'No' END AS planned_route_status,mhh.customer_id, mhh.txn_id, mhh.salesman_id, mhh.tenant_id,mhh.created_date
--FROM txn_mobile_header mhh) ,
--[Collection_selection] AS(
--SELECT mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN ch.txn_id IS NOT NULL THEN 'Collection, ' ELSE NULL END AS activity
--from txn_mobile_header mh
--LEFT JOIN txn_mobile_collection_header ch on ch.txn_id = mh.txn_id
--GROUP BY mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN ch.txn_id IS NOT NULL THEN 'Collection, ' ELSE NULL END
--),
--[GoodsReturn_selection] AS (
--SELECT mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN rh.txn_id IS NOT NULL THEN 'Goods Return, ' ELSE NULL END AS activity
--from txn_mobile_header mh
--LEFT JOIN txn_return_header rh on rh.txn_id = mh.txn_id
--group by mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN rh.txn_id is NOT NULL THEN 'Goods Return, ' ELSE NULL END
--),
--[SalesOrder_selection] AS (
--SELECT mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order, ' ELSE NULL END AS activity
--FROM txn_mobile_header mh
--inner join txn_sales_order_header oh on mh.txn_id = oh.mobile_hdr_id
--group by mh.txn_id, mh.customer_id, mh.salesman_id,
--CASE WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order, ' ELSE NULL END
--),
--[StockCount_selection]AS(
--select mh.txn_id ,mh.customer_id, mh.salesman_id, --Stock Count
--CASE
--WHEN sc.txn_id IS NOT NULL THEN 'Stock Count, '
--ELSE NULL
--END AS activity
--from txn_mobile_header mh
--left join txn_mobile_stock_count sc
--on mh.txn_id = sc.txn_id
--group by mh.txn_id ,mh.customer_id, mh.salesman_id,
--CASE
--WHEN sc.txn_id IS NOT NULL THEN 'Stock Count, '
--ELSE NULL END)

--SELECT c.customer_code, c.customer_name, b.branch_desc, a.area_description,
--m.member_id, hh.tenant_id, a.area_id, b.branch_id, b.branch_code,
--m.member_cd as salesman_code, CONCAT as salesman_name,
--ISNULL, ci.check_in_date,121),'') as check_in_date, cc.customer_class_description,
--CONCAT as activty, planned_route_status,
--ssv.visit_status , hh.txn_id
--FROM txn_mobile_header hh
--inner join [CHECK_WITH_STATUS] ssv on ssv.txn_id = hh.txn_id
--left join [planned_route] rr on hh.txn_id = rr.txn_id
--left join customer c on c.customer_id = hh.customer_id
--left join mst_customer_class cc on cc.customer_class_id = c.customer_class_id
--left join mst_branch b on c.branch_id = b.branch_id
--left join mst_area a on a.area_id = c.area_id
--left join txn_mobile_check_in ci on hh.txn_id = ci.txn_id
--left join txn_mobile_check_out co on co.txn_id = hh.txn_id
--left join member m on hh.salesman_id = m.member_id
--left join [Collection_selection] cs on cs.txn_id = hh.txn_id
--left join [GoodsReturn_selection] gs on gs.txn_id = hh.txn_id
--left join [SalesOrder_selection] ss on ss.txn_id = hh.txn_id
--left join [StockCount_selection] scs on scs.txn_id = hh.txn_id
--WHERE
--cast (rr.created_date as date) BETWEEN '2021-01-01 00:00:00' AND '2021-12-30 00:00:00'
--AND exists (select 1 from #branch b1 where b.branch_id = b1.branch_id)
--AND exists (select 1 from #area a1 where a.area_id = a1.area_id)
--AND exists (select 1 from #salesman s1 where m.member_id = s1.salesman_id)
--AND exists (select 1 from #planned_route p1 where rr.planned_route_status = p1.planned_status)
--AND hh.tenant_id = @tenant_id
--order by cast(check_in_date as date) , cast(check_in_date as time) desc
--END

BEGIN

WITH [CHECK_WITH_STATUS] AS (
select 'Missed Call' as visit_status , mc.txn_id
from txn_mobile_missed_call mc
where exists(select 1 from txn_mobile_header h where h.txn_id = mc.txn_id)
union
select x.visit_status, x.txn_id from (
select 'Yes' as visit_status , ci.txn_id from txn_mobile_check_in ci
inner join txn_mobile_check_out co on ci.txn_id = co.txn_id
where exists
(select 1 from txn_mobile_header h
inner join mst_route r on h.salesman_id = r.member_id and h.customer_id = r.customer_id and cast(h.created_date as date) = r.date and ci.txn_id = h.txn_id)
union
select 'No' as visit_status, hh.txn_id from mst_route rr
left join txn_mobile_header hh on hh.salesman_id = rr.member_id and hh.customer_id = rr.customer_id and cast(hh.created_date as date) = rr.date
where
not exists(
select 1 from txn_mobile_check_in ii where ii.txn_id = hh.txn_id
))x),
[planned_route] AS ( -- Planned Route
SELECT CASE WHEN exists (select * from txn_mobile_header mhh inner join customer cc on mhh.customer_id= cc.customer_id
inner join member mm on mm.member_id= mhh.salesman_id inner join mst_route r
ON mh.salesman_id= r.member_id and mh.customer_id = r.customer_id and cast( mh.created_date as date) = r.date) THEN 'Yes'
ELSE 'No'
END AS planned_route, txn_id
from txn_mobile_header mh) ,
[Collection_selection] AS( -- Collection
SELECT mh.txn_id, mh.customer_id, mh.salesman_id, ch.col_no,
CASE WHEN ch.txn_id IS NOT NULL THEN 'Collection' ELSE NULL END AS activity, ch.total_col_amt as CollectionAmount
from txn_mobile_header mh
LEFT JOIN txn_mobile_collection_header ch on ch.txn_id = mh.txn_id
WHERE ch.status != 'Rejected' and ch.status != 'Cancelled'
GROUP BY mh.txn_id, mh.customer_id, mh.salesman_id,ch.total_col_amt, ch.col_no,
CASE WHEN ch.txn_id IS NOT NULL THEN 'Collection' ELSE NULL END
),
[GoodsReturn_selection] AS ( -- Goods Return
SELECT mh.txn_id, mh.customer_id, mh.salesman_id, rh.return_number,
CASE WHEN rh.txn_id IS NOT NULL THEN 'Goods Return' ELSE NULL END AS activity, rd.amount as ReturnAmount
,ch.credit_note_no
from txn_mobile_header mh
LEFT JOIN txn_return_header rh on rh.txn_id = mh.txn_id
inner join txn_return_details rd on rh.return_id = rd.return_id
left join txn_credit_note_history_header ch on ch.return_id = rh.return_id
where rh.status != 'Rejected' and rh.status != 'Cancelled'
group by mh.txn_id, mh.customer_id, mh.salesman_id, rd.amount, rh.return_number,
CASE WHEN rh.txn_id is NOT NULL THEN 'Goods Return' ELSE NULL END
,ch.credit_note_no
),
[SalesOrder_selection] AS ( -- Sales Order
SELECT mh.txn_id, mh.customer_id, mh.salesman_id, oh.order_web_net_amount as SalesOrderAmount, oh.order_no,
CASE WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order' ELSE NULL END AS activity,
ih.invoice_no
FROM txn_mobile_header mh
inner join txn_sales_order_header oh on mh.txn_id = oh.mobile_hdr_id
left join txn_invoice_history_header ih on ih.sales_order_id = oh.ord_hdr_id
where oh.status != 'Rejected' and oh.status != 'Cancelled'
group by mh.txn_id, mh.customer_id, mh.salesman_id, oh.order_web_net_amount, oh.order_no,
CASE WHEN oh.mobile_hdr_id is NOT NULL THEN 'Sales Order' ELSE NULL END, ih.invoice_no
),
[StockCount_selection]AS( -- Stock Count
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN sc.txn_id IS NOT NULL THEN 'Stock Count'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_stock_count sc
on mh.txn_id = sc.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN sc.txn_id IS NOT NULL THEN 'Stock Count'
ELSE NULL END),
[GoodsReceive_selection]AS( -- Goods Receive
select mh.txn_id ,mh.customer_id, mh.salesman_id, gr.goods_receive_number,
CASE
WHEN gr.txn_id IS NOT NULL THEN 'Goods Receive'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_goods_receive_header gr
on mh.txn_id = gr.txn_id
where gr.status = 'Approved'
group by mh.txn_id ,mh.customer_id, mh.salesman_id, gr.goods_receive_number,
CASE
WHEN gr.txn_id IS NOT NULL THEN 'Goods Receive'
ELSE NULL END),
[GoodsRequest_selection]AS( -- Goods Request
select mh.txn_id ,mh.customer_id, mh.salesman_id, grq.goods_request_number,
CASE
WHEN grq.txn_id IS NOT NULL THEN 'Goods Request'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_goods_request_header grq
on mh.txn_id = grq.txn_id
where grq.status = 'Approved'
group by mh.txn_id ,mh.customer_id, mh.salesman_id, grq.goods_request_number,
CASE
WHEN grq.txn_id IS NOT NULL THEN 'Goods Request'
ELSE NULL END),
[GoodsTransfer_selection]AS( -- Goods Transfer
select mh.txn_id ,mh.customer_id, mh.salesman_id, gt.goods_transfer_number,
CASE
WHEN gt.txn_id IS NOT NULL THEN 'Goods Transfer'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_goods_transfer_header gt
on mh.txn_id = gt.txn_id
where gt.status = 'Approved'
group by mh.txn_id ,mh.customer_id, mh.salesman_id, gt.goods_transfer_number,
CASE
WHEN gt.txn_id IS NOT NULL THEN 'Goods Transfer'
ELSE NULL END),
[Survey_selection]AS( -- Survey
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN s.txn_id IS NOT NULL THEN 'Survey'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_survey s
on mh.txn_id = s.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN s.txn_id IS NOT NULL THEN 'Survey'
ELSE NULL END),
[PriceCheck_selection]AS( -- Price Check
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN pc.txn_id IS NOT NULL THEN 'Price Check'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_price_check pc
on mh.txn_id = pc.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN pc.txn_id IS NOT NULL THEN 'Price Check'
ELSE NULL END),
[PhotoTaking_selection]AS( -- PhotoTaking
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN pt.txn_id IS NOT NULL THEN 'Photo Taking'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_photo_header pt
on mh.txn_id = pt.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN pt.txn_id IS NOT NULL THEN 'Photo Taking'
ELSE NULL END),
[SKUSOS&OSA_selection]AS( -- [SKUSOS&OSA_selection]
select mh.txn_id, mh.customer_id, mh.salesman_id,
CASE
WHEN sso.txn_id IS NOT NULL THEN 'SKU SOS & OSA'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_sos_and_osa_by_product sso
on mh.txn_id = sso.txn_id
group by mh.txn_id, mh.customer_id, mh.salesman_id,
CASE WHEN sso.txn_id IS NOT NULL THEN 'SKU SOS & OSA'
ELSE NULL END),
[BrandSOS&OSA_selection] AS( -- [BrandSOS&OSA_selection]
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN bso.txn_id IS NOT NULL THEN 'Brand SOS & OSA'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_sos_and_osa_by_segment bso
on mh.txn_id = bso.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id, bso.txn_id,
CASE
WHEN bso.txn_id IS NOT NULL THEN 'Brand SOS & OSA'
ELSE NULL END),
[ProposeStockRotation_selection] AS( -- [ProposeStockRotation_selection]
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN psr.txn_id IS NOT NULL THEN 'Propose Stock Rotation'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_propose_rotation_detail psr
on mh.txn_id = psr.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN psr.txn_id IS NOT NULL THEN 'Propose Stock Rotation'
ELSE NULL END),
[ShareofShelf_selection] AS( -- [ShareofShelf_selection]
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN sos.txn_id IS NOT NULL THEN 'Share of Shelf'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join txn_mobile_sos_and_osa_by_product sos
on mh.txn_id = sos.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN sos.txn_id IS NOT NULL THEN 'Share of Shelf'
ELSE NULL END),
[StockAvailability_selection] AS( -- [StockAvailability_selection]
select mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN msa.txn_id IS NOT NULL THEN 'Stock Availability'
ELSE NULL
END AS activity
from txn_mobile_header mh
left join di_mobile_txn_stock_availability msa
on mh.txn_id = msa.txn_id
group by mh.txn_id ,mh.customer_id, mh.salesman_id,
CASE
WHEN msa.txn_id IS NOT NULL THEN 'Stock Availability'
ELSE NULL END)

SELECT c.customer_code, c.customer_name, b.branch_desc, a.area_description,
m.member_id, hh.tenant_id, a.area_id, b.branch_id, b.branch_code,
m.member_cd as salesman_code, CONCAT as salesman_name,
CONVERT as check_in_date, CONVERT as check_in_time, hh.created_date as transaction_date, cc.customer_class_description,
CONCAT_WS(', ', scs.activity, ss.activity, cs.activity, gs.activity, grv.activity, grq.activity, gt.activity, pt.activity, pc.activity, s.activity, s3.activity, bs.activity, psr.activity, soss.activity,sas.activity) as activity
,ss.order_no
,ss.invoice_no
,gs.return_number
,gs.credit_note_no
,cs.col_no
,rr.planned_route
,ssv.visit_status
,CASE WHEN planned_route = 'Yes' THEN CONVERT
WHEN planned_route = 'No' THEN CONVERT END as transaction_date, ISNULL AS CollectionAmount, ISNULL AS ReturnAmount ,
isnull(ss.SalesOrderAmount, 0) as SalesOrderAmount, CONVERT as check_out_date,CONVERT as check_out_time,
convert(time, check_out_date-check_in_date) as duration, CONVERT as sorting_date
FROM txn_mobile_header hh
left join [CHECK_WITH_STATUS] ssv on ssv.txn_id = hh.txn_id
left join [planned_route] rr on hh.txn_id = rr.txn_id
left join customer c on c.customer_id = hh.customer_id
left join mst_customer_class cc on cc.customer_class_id = c.customer_class_id
left join mst_branch b on c.branch_id = b.branch_id
left join mst_area a on a.area_id = c.area_id
inner join txn_mobile_check_in ci on hh.txn_id = ci.txn_id
left join txn_mobile_check_out co on co.txn_id = hh.txn_id
left join member m on hh.salesman_id = m.member_id
left join [Collection_selection] cs on cs.txn_id = hh.txn_id
left join [GoodsReturn_selection] gs on gs.txn_id = hh.txn_id
left join [SalesOrder_selection] ss on ss.txn_id = hh.txn_id
left join [StockCount_selection] scs on scs.txn_id = hh.txn_id
left join [GoodsReceive_selection] grv on grv.txn_id = hh.txn_id
left join [GoodsRequest_selection] grq on grq.txn_id = hh.txn_id
left join [GoodsTransfer_selection] gt on gt.txn_id = hh.txn_id
left join [Survey_selection] s on s.txn_id = hh.txn_id
left join [PriceCheck_selection] pc on pc.txn_id = hh.txn_id
left join [PhotoTaking_selection] pt on pt.txn_id = hh.txn_id
left join [SKUSOS&OSA_selection] s3 on s3.txn_id = hh.txn_id
left join [BrandSOS&OSA_selection] bs on bs.txn_id = hh.txn_id
left join [ProposeStockRotation_selection] psr on psr.txn_id = hh.txn_id
left join [StockAvailability_selection] sas on sas.txn_id = hh.txn_id
left join [ShareofShelf_selection] soss on soss.txn_id = hh.txn_id
WHERE
cast (hh.created_date as date) BETWEEN @check_in_start_date AND @check_in_end_date
AND exists (select 1 from #branch b1 where b.branch_id = b1.branch_id)
AND exists (select 1 from #area a1 where a.area_id = a1.area_id)
AND exists (select 1 from #salesman s1 where m.member_id = s1.salesman_id)
AND exists (select 1 from #planned_route p1 where rr.planned_route = p1.planned_status)
AND hh.tenant_id = @tenant_id
order by sorting_date, CONVERT , cast(check_in_date as time) desc
END

--DROP TABLE #area
--DROP TABLE #branch
--DROP TABLE #salesman
--DROP TABLE #planned_route
--GO

As I checked in Sp there is empty

need to check deeply on this

Hi,
"hi, INJNL001833-240514 didn't shown in salesman activity report"
check with you, this customer should be under which branch?

Hi @~Goldcrest Hotline , the mentioned invoice and collection already appear in the salesman activity report. the root cause is due to customer setup issue (branch set to NA, area set to 5JNL-2)