create or replace package body ssi_tdcp_rpt_pkg

as
-- package header

/**********************************************************************************
*****
name: ssi_sptc_rpt_pkg
purpose: this package used for process data from tabels of module tdcp
for
report.

revisions:
ver date author description
--------- ---------- ---------------
------------------------------------
1.0 01/12/2021 tampv 1.

***********************************************************************************
*****/

-- SO LUONG DAU KI (sum lai theo ma chung khoan, team, tieu khoan)
function get_quantity_bg(
p_from_date date,
p_to_date date,
p_m_product_id number,
p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select sum(v.movementqty)
into v_return
from ssg_transaction_v v
where v.m_product_id = p_m_product_id
and v.c_salesregion_id = p_c_salesregion_id
and v.s_subaccount_id = p_s_subaccount_id
and ((v.movementdate between p_from_date and p_to_date) or (p_to_date is null
and p_from_date is null))
group by v.m_product_id, v.c_salesregion_id, v.s_subaccount_id;
return v_return;
exception
when others then
return v_return;
end get_quantity_bg;

-- DON GIA DAU KI (sum lai theo ma chung khoan, team, tieu khoan)
function get_costprice_bg(
p_from_date date,
p_to_date date,
p_m_product_id number,
p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select spcd.costprice
into v_return
from s_product_cost_detail spcd join s_product_cost spc
on (spcd.s_product_cost_id = spc.s_product_cost_id
 and spc.m_product_id = p_m_product_id
and spc.s_subaccount_id = p_s_subaccount_id
and spc.c_salesregion_id = p_c_salesregion_id
and ((spcd.created between p_from_date and p_to_date) or (p_to_date is null
and p_from_date is null)))
order by spcd.created desc
fetch first 1 row only;
return v_return;
exception
when others then
return v_return;
end get_costprice_bg;

-- SO LUONG, GIA TRI MUA (sum lai theo ma chung khoan, team, tieu khoan) | lay
phieu nhap != quyen mua
function get_qtyentered_or_lineamount_b(
p_return varchar2,
p_from_date date,
p_to_date date,
p_m_product_id number,
p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select
case
when p_return = 'qtyentered' then sum(mil.qtyentered)
when p_return = 'lineamount' then sum(mil.lineamount)
end
into v_return
from m_inoutline mil
join m_inout mi
on (mil.m_inout_id = mi.m_inout_id
and mi.listed = 'CNY' and mi.c_doctype_id = 1000014 -- phieu nhap
and mil.m_product_id = p_m_product_id
and mil.s_subaccount_id = p_s_subaccount_id
and mil.c_salesregion_id = p_c_salesregion_id
and (extract(year from mi.movementdate) between extract(year from
p_from_date) and extract(year from p_to_date) or (p_from_date is null and p_to_date
is null)))
join (select cpl.c_productrightline_id from c_productrightline cpl join
c_productrightheader cp on (cpl.c_productrightheader_id =
cp.c_productrightheader_id and cp.c_productright_type != '02')) pr
on (pr.c_productrightline_id = mi.c_productrightline_id)
group by mil.m_product_id, mil.s_subaccount_id, mil.c_salesregion_id;
return v_return;
exception
when others then
return v_return;
end get_qtyentered_or_lineamount_b;

-- SO LUONG, GIA TRI BAN (sum lai theo ma chung khoan, team, tieu khoan) | lay
phieu nhap != quyen mua
function get_qtyentered_or_lineamount_s(
p_return varchar2,
p_from_date date,
p_to_date date,
p_m_product_id number,
 p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select
case
when p_return = 'qtyentered' then sum(mil.qtyentered)
when p_return = 'lineamount' then sum(mil.lineamount)
end
into v_return
from m_inoutline mil
join m_inout mi
on (mil.m_inout_id = mi.m_inout_id
and mi.listed = 'CNY' and mi.c_doctype_id = 1000011
and mil.m_product_id = p_m_product_id
and mil.s_subaccount_id = p_s_subaccount_id
and mil.c_salesregion_id = p_c_salesregion_id
and (extract(year from mi.movementdate) between extract(year from
p_from_date) and extract(year from p_to_date) or (p_from_date is null and p_to_date
is null)))
group by mil.m_product_id, mil.s_subaccount_id, mil.c_salesregion_id;
return v_return;
exception
when others then
return v_return;
end get_qtyentered_or_lineamount_s;

-- SO LUONG, GIA TRI CP THUONG/MUA THEO QUYEN (sum lai theo ma chung khoan, team,
tieu khoan) | lay phieu nhap = quyen mua
function get_qtyentered_or_lineamount_prob(
p_return varchar2,
p_from_date date,
p_to_date date,
p_m_product_id number,
p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select
case
when p_return = 'qtyentered' then sum(mil.qtyentered)
when p_return = 'lineamount' then sum(mil.lineamount)
end
into v_return
from m_inoutline mil
join m_inout mi
on (mil.m_inout_id = mi.m_inout_id
and mi.listed = 'CNY' and mi.c_doctype_id = 1000014
and mil.m_product_id = p_m_product_id
and mil.s_subaccount_id = p_s_subaccount_id
and mil.c_salesregion_id = p_c_salesregion_id
and (extract(year from mi.movementdate) between extract(year from
p_from_date) and extract(year from p_to_date) or (p_from_date is null and p_to_date
is null)))
join (select cpl.c_productrightline_id from c_productrightline cpl join
c_productrightheader cp on (cpl.c_productrightheader_id =
cp.c_productrightheader_id and cp.c_productright_type = '02')) pr on
(pr.c_productrightline_id = mi.c_productrightline_id)
group by mil.m_product_id, mil.s_subaccount_id, mil.c_salesregion_id;
return v_return;
exception
when others then
return v_return;
end get_qtyentered_or_lineamount_prob;

-- SO LUONG, GIA TRI CO TUC/TRAI TUC, LAI TUC GIAM GIA VON (sum lai theo ma chung
khoan, team, tieu khoan) | lay phieu nhap = quyen mua
function get_qtyentered_or_lineamount_prostock(
p_return varchar2,
p_from_date date,
p_to_date date,
p_m_product_id number,
p_c_salesregion_id number,
p_s_subaccount_id number
) return number as
v_return number := 0;
begin
select
case
when p_return = 'qtyentered' then sum(mil.qtyentered)
when p_return = 'lineamount' then sum(mil.lineamount)
end
into v_return
from m_inoutline mil
join m_inout mi
on (mil.m_inout_id = mi.m_inout_id
and mi.listed = 'CNY' and mi.c_doctype_id = 1000014
and mil.m_product_id = p_m_product_id
and mil.s_subaccount_id = p_s_subaccount_id
and mil.c_salesregion_id = p_c_salesregion_id
and (extract(year from mi.movementdate) between extract(year from
p_from_date) and extract(year from p_to_date) or (p_from_date is null and p_to_date
is null)))
join (select cpl.c_productrightline_id from c_productrightline cpl join
c_productrightheader cp on (cpl.c_productrightheader_id =
cp.c_productrightheader_id and cp.c_productright_type = '01')) pr on
(pr.c_productrightline_id = mi.c_productrightline_id)
group by mil.m_product_id, mil.s_subaccount_id, mil.c_salesregion_id;
return v_return;
exception
when others then
return v_return;
end get_qtyentered_or_lineamount_prostock;

-- HDTD_RPT_003: BAO CAO THEO DOI TRANG THAI DAU TU CO PHIEU

procedure ssi_tdcp_rpt_003_proc (
p_ad_pinstance_id in number
) is
-- Param process
p_from_date date;
p_to_date date;
p_ad_org_id number := 0;
p_m_product_id number := 0;
p_m_product_category_id number := 0;
p_list_subaccount_ids nvarchar2(255);
p_c_salesregion_id number := 0;
 l_value_bg number := 0;
l_quantity_edcp number := 0;
l_unitprice_edcp number := 0;
l_value_edcp number := 0;
l_interest_sck number := 0;
begin
delete from tdcp_rpt_003_temp;
commit;

-- Parameter tu ngay
select para.p_date
into p_from_date
from ad_pinstance_para para
where ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'From_Date';

-- Parameter den ngay

select para.p_date
into p_to_date
from ad_pinstance_para para
where para.ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'To_Date';

-- Parameter don vi
select para.p_number
into p_ad_org_id
from ad_pinstance_para para
where para.ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'AD_Org_ID';

-- Parameter ma chung khoan

select para.p_number
into p_m_product_id
from ad_pinstance_para para
where ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'M_Product_ID';

-- Parameter tieu khoan

select para.p_string
into p_list_subaccount_ids
from ad_pinstance_para para
where para.ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'List_Subaccount_IDS';

-- Parameter bo phan quan li

select para.p_number
into p_c_salesregion_id
from ad_pinstance_para para
where para.ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'C_Salesregion_ID';

-- Parameter thuoc tinh

select para.p_number
into p_m_product_category_id
from ad_pinstance_para para
where para.ad_pinstance_id = p_ad_pinstance_id
and para.parametername = 'M_Product_Category_ID';
 insert into tdcp_rpt_003_temp (
lineno -- 0. stt
,product_value -- 1. ma chung khoan
,product_name -- 2. ten chung khoan
,org_name -- 3. CN quan ly
,salesregion_name -- 5. Team
,tstc_type -- 6. Loai tstc
,type -- 7. Loai
,attribute -- 8. Thuoc tinh
,quantity_bg -- 12. so luong
,unitprice_bg -- 13. don gia
,value_bg -- 14. gia tri
,quantity_b -- 15. so luong
,value_b -- 16. gia tri
,quantity_s -- 17. so luong
,value_s -- 18. gia tri
,quantity_ns -- 19. so luong
,value_ns -- 20. gia tri
,quantity_dd -- 21. tien
,value_dd -- 22. sl co phieu
,quantity_edcp -- 23. so luong
,unitprice_edcp -- 24. don gia
,value_edcp -- 25. gia tri
,total_s -- 26. tong gia ban trong ki
,reduced_s -- 27. trai tuc/lai tuc giam khi ban
,interest_sck -- 28. lai/ lo ban ck
,dividend_revenue_recognition -- 29. co tuc/ trai tuc ghi nhan doanh thu
,status_invoice -- 30. trang thai hoa don dau vao dau ra
,capital_expenditures -- 31. chi phi von
,m_product_id
,s_subaccount_id
,c_salesregion_id
,ad_org_id
)
select
rownum,
mp.name,
mp.value,
ao.name,
cs.name,
decode(MP.asset_type, 'A', 'AFS', 'F', 'FVTPL'),
'Tự doanh',
mpc.name,
-- dau ki
ssi_tdcp_rpt_pkg.get_quantity_bg(p_from_date, p_to_date ,mp.m_product_id,
cs.c_salesregion_id, ss.s_subaccount_id),
ssi_tdcp_rpt_pkg.get_costprice_bg(p_from_date, p_to_date
,mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
0 value_bg,
-- mua
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_b('qtyentered',
p_from_date, p_to_date, mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_b('lineamount',
p_from_date, p_to_date ,mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
-- ban
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_s('qtyentered',
p_from_date, p_to_date, mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_s('lineamount',
p_from_date, p_to_date ,mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
 -- CP thuong/ mua theo quyen
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_prob('qtyentered',
p_from_date, p_to_date, mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_prob('lineamount',
p_from_date, p_to_date ,mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
-- Co tuc/ Trai tuc/ Lai tuc giam gia von
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_prostock('qtyentered',
p_from_date, p_to_date, mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
ssi_tdcp_rpt_pkg.get_qtyentered_or_lineamount_prostock('lineamount',
p_from_date, p_to_date ,mp.m_product_id, cs.c_salesregion_id, ss.s_subaccount_id),
-- Gia von cuoi ki
0,
0,
0,
0,
0,
0,
0,
null,
0,
mp.m_product_id,
ss.s_subaccount_id,
cs.c_salesregion_id,
ao.ad_org_id
from m_product mp
join (select li.m_product_id, li.c_salesregion_id, li.s_subaccount_id,
li.ad_org_id from m_inoutline li
group by li.m_product_id, li.c_salesregion_id, li.s_subaccount_id,
li.ad_org_id) line
on (line.m_product_id = mp.m_product_id and (mp.m_product_id =
p_m_product_id or p_m_product_id is null)) -- truyen parameter ma chung khoang
join m_product_category mpc
on (mp.m_product_category_id = mpc.m_product_category_id and
(mp.m_product_category_id = p_m_product_category_id or p_m_product_category_id is
null)) -- truyen parameter thuoc tinh
join ad_org ao
on (line.ad_org_id = ao.ad_org_id and (ao.ad_org_id = p_ad_org_id or
p_ad_org_id is null))
join c_salesregion cs
on (line.c_salesregion_id = cs.c_salesregion_id and (cs.c_salesregion_id
= p_c_salesregion_id or p_c_salesregion_id is null)) -- truyen parameter team
join s_subaccount ss
on (line.s_subaccount_id = ss.s_subaccount_id and line.s_subaccount_id
in (with rws as (select p_list_subaccount_ids str from dual) select regexp_substr
(str,'[^,]+',1,level) value
from rws connect by level <= length (str) - length (replace (str, ',' ))
+ 1)); -- truyen parameter tieu khoan

-- Cap nhat cac line tinh toan tu tham so cua bang tam
for i in (select * from tdcp_rpt_003_temp) loop
-- gia tri dau ki
if (i.quantity_bg != 0 and i.unitprice_bg != 0) then
-- SL (12) * Đơn giá (13)
l_value_bg := i.quantity_bg * i.unitprice_bg;
end if;

-- so luong gia von cuoi ki

if (i.quantity_bg != 0 and i.quantity_b != 0 and i.quantity_s != 0 and
i.quantity_ns != 0 and i.value_dd != 0) then
 -- (12) + (15) - (17) + (19) + (22)
l_quantity_edcp := i.quantity_bg + i.quantity_b - i.quantity_s +
i.quantity_ns + i.value_dd;
end if;

-- gia tri gia von cuoi ki

if (i.value_bg != 0 and i.value_b != 0 and i.value_s != 0 and i.value_ns !
= 0 and i.quantity_dd != 0) then
-- (14) + (16) - (18) + (20) + (21)
l_value_edcp := i.value_bg + i.value_b - i.value_s + i.value_ns +
i.quantity_dd;
end if;

-- don gia gia von cuoi ki

if (l_value_edcp != 0 and l_quantity_edcp != 0) then
-- (25) / (23)
l_unitprice_edcp := l_value_edcp / l_quantity_edcp;
end if;

-- lai lo ban/ ck
-- (26) - (18) - (27)
l_interest_sck := nvl(i.total_s, 0) - nvl(i.value_s, 0) - nvl(i.reduced_s,
0);

-- Cap nhat cac line

update tdcp_rpt_003_temp rpt
set
rpt.value_bg = l_value_bg, -- 14. gia tri dau ki
rpt.quantity_edcp = l_quantity_edcp, -- 23. so luong gia von cuoi
ki
rpt.unitprice_edcp = l_unitprice_edcp, -- 24. don gia gia von cuoi
ki
rpt.value_edcp = l_value_edcp, -- 25. gia tri gia von cuoi
ki
rpt.interest_sck = l_interest_sck -- 28. lai lo ban/ ck
where rpt.m_product_id = i.m_product_id
and rpt.s_subaccount_id = i.s_subaccount_id
and rpt.c_salesregion_id = i.c_salesregion_id;
end loop;
end ssi_tdcp_rpt_003_proc;

end ssi_tdcp_rpt_pkg;