PROCEDURE checkRegPJAgr
(reg_comp_id_in VARCHAR2,
reg_cc_id_in VARCHAR2,
reg_po_no_in VARCHAR2,
reg_po_sheet_no_in VARCHAR2,
ret_flg_out out varchar2
--tok_err_pj1_out out varchar2,
--tok_err_pj2_out out varchar2
) IS
CMP_ID VARCHAR2(3) :='YEA';
t_cc_id tbpj_agreement_head.cc_id%type;
t_doc_no tbpj_agreement_head.doc_no%type;
t_bupfpl_list varchar2(100);
t_total_ord_amt_aft_disc number;
t_total_ord_amt_bef_disc number;
t_total_po_amt_aft_disc number;
t_total_po_amt_bef_disc number;
t_80_max_tp_amt number;
t_opd_email varchar2(100);
t_requester_email tbpj_agreement_head.email_acct%type;
t_count_Tok_PO number;
--err_msg1 varchar2(2000) := null;
--err_msg2 varchar2(2000) := null;
Cursor cur_Tokuchu_no is
select distinct tokuchu_no
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and tokuchu_no is not null;
c_cur_tokuchu_no cur_tokuchu_no%rowtype;
Cursor cur_Reg_PJ_Item (document_no in varchar2) is
select comp_id,
cc_id,
doc_no,
item_no,
item_desc,
max_tp_amt,
disc_pct
from tbpj_agreement_item
where comp_id = CMP_ID
and cc_id = t_cc_id
and doc_no = document_no
and date_cancel is null
and status = 'APPR';
c_cur_reg_pj_item cur_reg_pj_item%rowtype;
BEGIN
/*-- Hardcoding OPD email address. Assumption service dept will not use regional project agreement
if reg_comp_id_in = 'YIN' or reg_comp_id_in = 'SIK' then
t_opd_email := 'stacy.loh@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YEM' or reg_comp_id_in = 'YKM' then
t_opd_email := 'venna.tan@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YTH' then
t_opd_email := 'kiaeng.lee@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YPI' then
t_opd_email := 'alicia.koh@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YME' then
t_opd_email := 'kimchoo.ong@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
end if;*/
Open cur_Tokuchu_no;
LOOP
exit when cur_Tokuchu_no%notfound;
Fetch cur_Tokuchu_no into c_cur_tokuchu_no;
Begin
select doc_no, cc_id
into t_doc_no, t_cc_id
from tbpj_agreement_head
where comp_id = CMP_ID
and sales_office = reg_comp_id_in
and rso_agreement_no = c_cur_tokuchu_no.tokuchu_no
and rso_rev_no = (select max(to_number(rso_rev_no))
from tbpj_agreement_head a
where a.comp_id = CMP_ID
and rso_agreement_no = c_cur_tokuchu_no.tokuchu_no
and date_cancel is null
and status = 'APPR')
and sysdate between date_effective and nvl(date_expiry, sysdate);
exception
when no_data_found then
t_doc_no := null;
t_cc_id := null;
when too_many_rows then
t_doc_no := null;
t_cc_id := null;
when others then
t_doc_no := null;
t_cc_id := null;
end;
if t_doc_no is not null then
/* -- Retrieve the requester email address
begin
select email_acct
into t_requester_email
from tbpj_agreement_head
where comp_id = CMP_ID
and cc_id = t_cc_id
and doc_no = t_doc_no;
exception
when no_data_found then
t_requester_email := null;
when too_many_rows then
t_requester_email := null;
when others then
t_requester_email := null;
end;*/
Open cur_Reg_PJ_Item (t_doc_no);
loop
exit when cur_Reg_PJ_item%notfound;
fetch cur_Reg_PJ_item into c_cur_reg_pj_item;
begin
select count(*)
into t_count_Tok_PO
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and date_co_trans is null
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_count_Tok_PO := 0;
when others then
t_count_Tok_PO := 0;
end;
if t_count_Tok_PO > 0 then
-- Retrieving total order amount in YEA CO using the Project Agreement No
begin
select nvl(sum(cost_order_from_contr * qty_order), 0)
into t_total_ord_amt_aft_disc
from tbco_item
where comp_id = CMP_ID
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and date_cancel is null
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_total_ord_amt_aft_disc := 0;
when too_many_rows then
t_total_ord_amt_aft_disc := 0;
when others then
t_total_ord_amt_aft_disc := 0;
end;
t_total_ord_amt_bef_disc := round(t_total_ord_amt_aft_disc * 100 / (100 - c_cur_reg_pj_item.disc_pct), 2);
-- Retrieving total order amount in the regional PO using the Project Agreement No
begin
select nvl(sum(purchase_contr), 0)
into t_total_po_amt_aft_disc
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and date_co_trans is null
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_total_po_amt_aft_disc := 0;
when too_many_rows then
t_total_po_amt_aft_disc := 0;
when others then
t_total_po_amt_aft_disc := 0;
end;
t_total_po_amt_bef_disc := round(t_total_po_amt_aft_disc * 100 / (100 - c_cur_reg_pj_item.disc_pct), 2);
-- Calculate 80% of the Max TP Amount from the Agreement Item (in this case the 80% will be hardcoded for calculation purpose)
t_80_max_tp_amt := round(c_cur_reg_pj_item.max_tp_amt * 0.8, 2);
-- If total order amount (existing + new PO) bigger than max_tp_amt, the PO will not get converted.
if (t_total_ord_amt_bef_disc + t_total_po_amt_bef_disc) > c_cur_reg_pj_item.max_tp_amt then
ret_flg_out := '1';
/*err_msg1 := t_requester_email||'~'||t_opd_email||'~'||reg_po_no_in||' '||reg_po_sheet_no_in||'~'||c_cur_tokuchu_no.tokuchu_no||'~'||
c_cur_reg_pj_item.item_desc||'~'||c_cur_reg_pj_item.max_tp_amt||'~'||c_cur_reg_pj_item.disc_pct||'~'||t_total_ord_amt_bef_disc
||'~'||t_total_po_amt_bef_disc||'~100%';*/
-- If total order amount (existing + new PO) bigger than 80% but less than 100% than max_tp_amt, the PO will get converted but get warning
elsif (t_total_ord_amt_bef_disc + t_total_po_amt_bef_disc) > t_80_max_tp_amt then
/*err_msg2 := t_requester_email||'~'||t_opd_email||'~'||reg_po_no_in||' '||reg_po_sheet_no_in||'~'||c_cur_tokuchu_no.tokuchu_no||'~'||
c_cur_reg_pj_item.item_desc||'~'||c_cur_reg_pj_item.max_tp_amt||'~'||c_cur_reg_pj_item.disc_pct||'~'||t_total_ord_amt_bef_disc
||'~'||t_total_po_amt_bef_disc||'~80%';*/
end if;
end if;
end loop;
close cur_Reg_PJ_Item;
end if;
End Loop;
Close cur_Tokuchu_no;
/*tok_err_pj1_out := err_msg1;
tok_err_pj2_out := err_msg2;*/
END;
(reg_comp_id_in VARCHAR2,
reg_cc_id_in VARCHAR2,
reg_po_no_in VARCHAR2,
reg_po_sheet_no_in VARCHAR2,
ret_flg_out out varchar2
--tok_err_pj1_out out varchar2,
--tok_err_pj2_out out varchar2
) IS
CMP_ID VARCHAR2(3) :='YEA';
t_cc_id tbpj_agreement_head.cc_id%type;
t_doc_no tbpj_agreement_head.doc_no%type;
t_bupfpl_list varchar2(100);
t_total_ord_amt_aft_disc number;
t_total_ord_amt_bef_disc number;
t_total_po_amt_aft_disc number;
t_total_po_amt_bef_disc number;
t_80_max_tp_amt number;
t_opd_email varchar2(100);
t_requester_email tbpj_agreement_head.email_acct%type;
t_count_Tok_PO number;
--err_msg1 varchar2(2000) := null;
--err_msg2 varchar2(2000) := null;
Cursor cur_Tokuchu_no is
select distinct tokuchu_no
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and tokuchu_no is not null;
c_cur_tokuchu_no cur_tokuchu_no%rowtype;
Cursor cur_Reg_PJ_Item (document_no in varchar2) is
select comp_id,
cc_id,
doc_no,
item_no,
item_desc,
max_tp_amt,
disc_pct
from tbpj_agreement_item
where comp_id = CMP_ID
and cc_id = t_cc_id
and doc_no = document_no
and date_cancel is null
and status = 'APPR';
c_cur_reg_pj_item cur_reg_pj_item%rowtype;
BEGIN
/*-- Hardcoding OPD email address. Assumption service dept will not use regional project agreement
if reg_comp_id_in = 'YIN' or reg_comp_id_in = 'SIK' then
t_opd_email := 'stacy.loh@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YEM' or reg_comp_id_in = 'YKM' then
t_opd_email := 'venna.tan@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YTH' then
t_opd_email := 'kiaeng.lee@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YPI' then
t_opd_email := 'alicia.koh@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
elsif reg_comp_id_in = 'YME' then
t_opd_email := 'kimchoo.ong@sg.yokogawa.com; alfred.ng@sg.yokogawa.com';
end if;*/
Open cur_Tokuchu_no;
LOOP
exit when cur_Tokuchu_no%notfound;
Fetch cur_Tokuchu_no into c_cur_tokuchu_no;
Begin
select doc_no, cc_id
into t_doc_no, t_cc_id
from tbpj_agreement_head
where comp_id = CMP_ID
and sales_office = reg_comp_id_in
and rso_agreement_no = c_cur_tokuchu_no.tokuchu_no
and rso_rev_no = (select max(to_number(rso_rev_no))
from tbpj_agreement_head a
where a.comp_id = CMP_ID
and rso_agreement_no = c_cur_tokuchu_no.tokuchu_no
and date_cancel is null
and status = 'APPR')
and sysdate between date_effective and nvl(date_expiry, sysdate);
exception
when no_data_found then
t_doc_no := null;
t_cc_id := null;
when too_many_rows then
t_doc_no := null;
t_cc_id := null;
when others then
t_doc_no := null;
t_cc_id := null;
end;
if t_doc_no is not null then
/* -- Retrieve the requester email address
begin
select email_acct
into t_requester_email
from tbpj_agreement_head
where comp_id = CMP_ID
and cc_id = t_cc_id
and doc_no = t_doc_no;
exception
when no_data_found then
t_requester_email := null;
when too_many_rows then
t_requester_email := null;
when others then
t_requester_email := null;
end;*/
Open cur_Reg_PJ_Item (t_doc_no);
loop
exit when cur_Reg_PJ_item%notfound;
fetch cur_Reg_PJ_item into c_cur_reg_pj_item;
begin
select count(*)
into t_count_Tok_PO
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and date_co_trans is null
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_count_Tok_PO := 0;
when others then
t_count_Tok_PO := 0;
end;
if t_count_Tok_PO > 0 then
-- Retrieving total order amount in YEA CO using the Project Agreement No
begin
select nvl(sum(cost_order_from_contr * qty_order), 0)
into t_total_ord_amt_aft_disc
from tbco_item
where comp_id = CMP_ID
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and date_cancel is null
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_total_ord_amt_aft_disc := 0;
when too_many_rows then
t_total_ord_amt_aft_disc := 0;
when others then
t_total_ord_amt_aft_disc := 0;
end;
t_total_ord_amt_bef_disc := round(t_total_ord_amt_aft_disc * 100 / (100 - c_cur_reg_pj_item.disc_pct), 2);
-- Retrieving total order amount in the regional PO using the Project Agreement No
begin
select nvl(sum(purchase_contr), 0)
into t_total_po_amt_aft_disc
from tbpo_item_reg
where comp_id = reg_comp_id_in
and cc_id = reg_cc_id_in
and po_no = reg_po_no_in
and po_sheet_no = reg_po_sheet_no_in
and date_cancel is null
and date_co_trans is null
and tokuchu_no = c_cur_tokuchu_no.tokuchu_no
and lms_l||lms_m||lms_s in (select lms_l||lms_m||lms_s
from tbpj_agreement_dtl
where comp_id = CMP_ID
and doc_no = t_doc_no
and cc_id = t_cc_id
and item_no = c_cur_reg_pj_item.item_no
and date_cancel is null
and status = 'APPR');
exception
when no_data_found then
t_total_po_amt_aft_disc := 0;
when too_many_rows then
t_total_po_amt_aft_disc := 0;
when others then
t_total_po_amt_aft_disc := 0;
end;
t_total_po_amt_bef_disc := round(t_total_po_amt_aft_disc * 100 / (100 - c_cur_reg_pj_item.disc_pct), 2);
-- Calculate 80% of the Max TP Amount from the Agreement Item (in this case the 80% will be hardcoded for calculation purpose)
t_80_max_tp_amt := round(c_cur_reg_pj_item.max_tp_amt * 0.8, 2);
-- If total order amount (existing + new PO) bigger than max_tp_amt, the PO will not get converted.
if (t_total_ord_amt_bef_disc + t_total_po_amt_bef_disc) > c_cur_reg_pj_item.max_tp_amt then
ret_flg_out := '1';
/*err_msg1 := t_requester_email||'~'||t_opd_email||'~'||reg_po_no_in||' '||reg_po_sheet_no_in||'~'||c_cur_tokuchu_no.tokuchu_no||'~'||
c_cur_reg_pj_item.item_desc||'~'||c_cur_reg_pj_item.max_tp_amt||'~'||c_cur_reg_pj_item.disc_pct||'~'||t_total_ord_amt_bef_disc
||'~'||t_total_po_amt_bef_disc||'~100%';*/
-- If total order amount (existing + new PO) bigger than 80% but less than 100% than max_tp_amt, the PO will get converted but get warning
elsif (t_total_ord_amt_bef_disc + t_total_po_amt_bef_disc) > t_80_max_tp_amt then
/*err_msg2 := t_requester_email||'~'||t_opd_email||'~'||reg_po_no_in||' '||reg_po_sheet_no_in||'~'||c_cur_tokuchu_no.tokuchu_no||'~'||
c_cur_reg_pj_item.item_desc||'~'||c_cur_reg_pj_item.max_tp_amt||'~'||c_cur_reg_pj_item.disc_pct||'~'||t_total_ord_amt_bef_disc
||'~'||t_total_po_amt_bef_disc||'~80%';*/
end if;
end if;
end loop;
close cur_Reg_PJ_Item;
end if;
End Loop;
Close cur_Tokuchu_no;
/*tok_err_pj1_out := err_msg1;
tok_err_pj2_out := err_msg2;*/
END;