DROP PROCEDURE SBO_SP_TransactionNotification;

Create PROCEDURE SBO_SP_TransactionNotification

(
in object_type nvarchar(20), -- SBO Object Type
in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete,
[C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255)
)
LANGUAGE SQLSCRIPT
AS
-- Return values
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed

cnt int;

begin

error := 0;
error_message := N'Ok';

-----------------------------------------------------------------------------------
---------------------------------------------

-- ADD YOUR CODE HERE

-----------------------------------------------------------------------------------
---------------------------------------------
--START SALES
--SALES ORDER DRAFT
if :object_type='112' and (:transaction_type='A' or :transaction_type='U')
then
select STRING_AGG(RDR1."VisOrder"+1, ',')
into error_message
from DRF1 RDR1 inner join ODRF ORDR on ORDR."DocEntry" = RDR1."DocEntry"
where
round(
ifnull(RDR1."U_ftPrice",0)*
(1-(ifnull(RDR1."U_ftDisc1",0)/100))*
(1-(ifnull(RDR1."U_ftDisc2",0)/100))*
(1-(ifnull(RDR1."U_ftDisc3",0)/100))*
(1-(ifnull(RDR1."U_ftDisc4",0)/100)),2) <> ifnull(RDR1."PriceAfVAT",0)
and RDR1."DocEntry" = :list_of_cols_val_tab_del
and ORDR."ObjType" = 17;

if error_message <> N'Ok'

then
error := -11201;
error_message := 'Cannot add document incorrect net price on row no/s.
' || :error_message;

end if;

end if;

if :object_type='17' and (:transaction_type='A' or :transaction_type='U')

 then
select STRING_AGG(RDR1."VisOrder"+1, ',')
into error_message
from RDR1 inner join ORDR on ORDR."DocEntry" = RDR1."DocEntry"
where
round(
ifnull(RDR1."U_ftPrice",0)*
(1-(ifnull(RDR1."U_ftDisc1",0)/100))*
(1-(ifnull(RDR1."U_ftDisc2",0)/100))*
(1-(ifnull(RDR1."U_ftDisc3",0)/100))*
(1-(ifnull(RDR1."U_ftDisc4",0)/100)),2) <> ifnull(RDR1."PriceAfVAT",0)
and RDR1."DocEntry" = :list_of_cols_val_tab_del
and ORDR."ObjType" = 17;

if error_message <> N'Ok'

then
error := -1701;
error_message := 'Cannot add document incorrect net price on row no/s.
' || :error_message;

end if;

end if;

--DELIVERY
if :object_type='15' and (:transaction_type='A' or :transaction_type='U')
then
--Delivery should have a base document
select count(*) into cnt from DLN1 where DLN1."DocEntry"
= :list_of_cols_val_tab_del and DLN1."BaseType" = -1;
if :cnt>0
then
error := -1501;
error_message := 'Cannot add delivery without base document';
end if;

cnt := 0;

--Delivered Amount > Qty in sales order

select count(*) into cnt
from "DLN1" a
left join
( select "DLN1"."BaseEntry", "DLN1"."BaseLine",
ifnull(sum(ifnull("Quantity",0)),0) - ifnull(sum(ifnull("Amt",0)),0) as "Quantity"
from "DLN1" inner join "ODLN" on "DLN1"."DocEntry" = "ODLN"."DocEntry"
left join
(
select "BaseEntry", "BaseLine",sum("Quantity") as "Amt" from "RDN1"
inner join "ORDN" on "RDN1"."DocEntry" = "ORDN"."DocEntry"
where "ORDN"."CANCELED" = 'N'
group by "BaseEntry", "BaseLine"
) "RDN1" on "DLN1"."DocEntry" = "RDN1"."BaseEntry" and "DLN1"."LineNum"
= "RDN1"."BaseLine"
where "ODLN"."CANCELED" = 'N'
group by "DLN1"."BaseEntry","DLN1"."BaseLine"
) b on a."BaseEntry" = b."BaseEntry" and a."BaseLine"=b."BaseLine"
left join "RDR1" c on a."BaseEntry" = c."DocEntry" and a."ItemCode" =
c."ItemCode"
 and c."LineNum" = a."BaseLine"
inner join ODLN d on a."DocEntry" = d."DocEntry"
where
b."Quantity" > c."Quantity" and
a."BaseType" = 17
and d."CANCELED" = 'N'
and a."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -1502;
select ('Delivered quantity is greater than sales order quantity for
item no./s ' ||
(select STRING_AGG(a."VisOrder"+1, ',')
from "DLN1" a
left join
( select "DLN1"."BaseEntry", "DLN1"."BaseLine",
ifnull(sum(ifnull("Quantity",0)),0) - ifnull(sum(ifnull("Amt",0)),0) as "Quantity"
from "DLN1"
left join
(
select "BaseEntry", "BaseLine",sum("Quantity") as "Amt" from
"RDN1"
group by "BaseEntry", "BaseLine"
) "RDN1" on "DLN1"."DocEntry" = "RDN1"."BaseEntry" and
"DLN1"."LineNum" = "RDN1"."BaseLine"
group by "DLN1"."BaseEntry","DLN1"."BaseLine"
) b on a."BaseEntry" = b."BaseEntry" and a."BaseLine"=b."BaseLine"
left join "RDR1" c on a."BaseEntry" = c."DocEntry" and a."ItemCode" =
c."ItemCode"
inner join ODLN d on a."DocEntry" = d."DocEntry"
where
b."Quantity" > c."Quantity" and
a."BaseType" = 17
and d."CANCELED" = 'N'
and a."DocEntry" = :list_of_cols_val_tab_del)
) into error_message from "DUMMY";
end if;

end if;

--SALES RETURN
if :object_type='16' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt from RDN1 inner join ORDN on RDN1."DocEntry" =
ORDN."DocEntry"
where ifnull(ORDN."U_ftPullOutNum",'') = '' and RDN1."BaseType" = -1
and ORDN."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -1601;
error_message := 'Cannot add stand alone sales return without pull out
reference no.';
end if;

end if;
 --AR CREDIT MEMO
if :object_type='14' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt
from RIN1
inner join ORIN on RIN1."DocEntry" = ORIN."DocEntry"
inner join OITM on RIN1."ItemCode" = OITM."ItemCode"
where
(
(RIN1."BaseType" = 13 and RIN1."NoInvtryMv" <> 'Y')
or
RIN1."BaseType" = -1
) and RIN1."DocEntry" = :list_of_cols_val_tab_del
and ORIN."DocType" = 'I'
and OITM."SellItem" = 'Y'
and OITM."InvntItem" = 'Y'
and OITM."PrchseItem" = 'Y';

if :cnt>0
then
error := -1401;
error_message := 'Cannot Add A/R Credit Memo w/o Sales Return';
end if;
end if;

--END SALES

--START PURCHARSInG
--PURCHASE ORDER
if :object_type='22' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt from POR1 where "DocEntry"
= :list_of_cols_val_tab_del and "Price" = 0;
if :cnt>0
then
error := -2201;
error_message := 'Cannot add purchase order with 0 priced items.';
end if;
end if;

--PURCHASE ORDER DRAFT

if :object_type='112' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt from DRF1 inner join ODRF on DRF1."DocEntry" =
ODRF."DocEntry"
where DRF1."DocEntry" = :list_of_cols_val_tab_del and "Price" = 0 and
ODRF."ObjType" = 22;
if :cnt>0
then
error := -11202;
error_message := 'Cannot add purchase order with 0 priced items.';
end if;
end if;

--GRPO
 if :object_type='20' and (:transaction_type='A' or :transaction_type='U')
then

--GRPO should have a base document

select count(*) into cnt from PDN1 where PDN1."DocEntry"
= :list_of_cols_val_tab_del and PDN1."BaseType" = -1;
if :cnt>0
then
error := -2018;
error_message := 'Cannot add GRPO without PO';
end if;

cnt := 0;

--Items in GRPO with no PO reference should have item cost

--select count(*) into cnt from PDN1 where PDN1."DocEntry"
= :list_of_cols_val_tab_del and PDN1."BaseType" = -1 and PDN1."Price" = 0;
--if :cnt>0
--then
--error := -2001;
--error_message := 'Cannot add GRPO if additional items unit price is
0.';
--end if;

--cnt := 0;

--The items in GRPO per line should not exceed the base PO qty
select count(*) into cnt
from "PDN1" a
left join
( select "PDN1"."BaseEntry", "PDN1"."BaseLine",
ifnull(sum(ifnull("Quantity",0)),0) - ifnull(sum(ifnull("Amt",0)),0) as "Quantity"
from "PDN1" inner join "OPDN" on "PDN1"."DocEntry" = "OPDN"."DocEntry"
left join
(
select "BaseEntry", "BaseLine",sum("Quantity") as "Amt"
from "RDN1" inner join "ORDN" on "RDN1"."DocEntry" =
"ORDN"."DocEntry"
where "ORDN"."CANCELED" = 'N'
group by "BaseEntry", "BaseLine"
) "RDN1" on "PDN1"."DocEntry" = "RDN1"."BaseEntry" and "PDN1"."LineNum"
= "RDN1"."BaseLine"
where "OPDN"."CANCELED" = 'N'
group by "PDN1"."BaseEntry","PDN1"."BaseLine"
) b on a."BaseEntry" = b."BaseEntry" and a."BaseLine"=b."BaseLine"
left join "POR1" c on a."BaseEntry" = c."DocEntry" and a."ItemCode" =
c."ItemCode"
and c."LineNum" = a."BaseLine"
inner join OPDN d on a."DocEntry" = d."DocEntry"
where
b."Quantity" > c."Quantity" and
a."BaseType" = 22
and d."CANCELED" = 'N'
and a."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -2002;
select ('The items in GRPO per line should not exceed the base PO qty
for item no./s ' ||
 (select STRING_AGG(a."VisOrder"+1, ',')
from "PDN1" a
left join
( select "PDN1"."BaseEntry", "PDN1"."BaseLine",
ifnull(sum(ifnull("Quantity",0)),0) - ifnull(sum(ifnull("Amt",0)),0) as "Quantity"
from "PDN1"
left join
(
select "BaseEntry", "BaseLine",sum("Quantity") as "Amt" from
"RDN1"
group by "BaseEntry", "BaseLine"
) "RDN1" on "PDN1"."DocEntry" = "RDN1"."BaseEntry" and
"PDN1"."LineNum" = "RDN1"."BaseLine"
group by "PDN1"."BaseEntry","PDN1"."BaseLine"
) b on a."BaseEntry" = b."BaseEntry" and a."BaseLine"=b."BaseLine"
left join "POR1" c on a."BaseEntry" = c."DocEntry" and a."ItemCode" =
c."ItemCode"
where
b."Quantity" > c."Quantity" and
a."BaseType" = 22
and a."DocEntry" = :list_of_cols_val_tab_del)
) into error_message from "DUMMY";
end if;
end if;

--LANDED COSTS
if :object_type='69' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt
from OIPF
where
OIPF."DocEntry" = :list_of_cols_val_tab_del
and ifnull(OIPF."AgentCode",'') = '';
if :cnt>0
then
error := -6918;
error_message := 'Cannot add landed cost document without Broker.';
end if;
end if;

--AP DOWNPAYMENT
if :object_type='204' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt
from ODPO left join DPO1 on ODPO."DocEntry" = DPO1."DocEntry"
where ODPO."DocType" = 'I'
and DPO1."BaseType" <> '22'
and ODPO."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -1801;
error_message := 'Cannot Add A/P Downpayment without PO';
end if;
end if;

--AP INVOICE
if :object_type='18' and (:transaction_type='A' or :transaction_type='U')
then
 select count(*) into cnt
from OPCH left join PCH1 on OPCH."DocEntry" = PCH1."DocEntry"
where OPCH."DocType" = 'I'
and PCH1."BaseType" <> '20'
and OPCH."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -1801;
error_message := 'Cannot Add A/P Invoice without GRPO';
end if;
end if;

--AP DEBIT MEMO

if :object_type='19' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt
from RPC1 inner join ORPC on RPC1."DocEntry" = ORPC."DocEntry"
where
RPC1."BaseType" in(-1,18)
and ORPC."DocEntry" = :list_of_cols_val_tab_del
and ORPC."DocType" = 'I';
if :cnt>0
then
error := -1901;
error_message := 'Cannot Add A/P Debit Memo without Goods Return.';
end if;
end if;

--OUTGOING PAYMENT
if :object_type='46' and (:transaction_type='A' or :transaction_type='U')
then
select count(*) into cnt
from OVPM inner join VPM1 on OVPM."DocEntry" = VPM1."DocNum"
where ifnull(OVPM."Address",'') = ''
and VPM1."CheckSum" > 0
and OVPM."DocEntry" = :list_of_cols_val_tab_del;
if :cnt>0
then
error := -4618;
error_message := 'Cannot add outgoing payment without Payee';
end if;

end if;

--GOODS RETURN
--if :object_type='21' and (:transaction_type='A' or :transaction_type='U')
--then
--select count(*) into cnt
--from ORPD
--inner join RPD1 on ORPD."DocEntry" = RPD1."DocEntry"
--left join ITM2 on RPD1."ItemCode" = ITM2."ItemCode" and ITM2."VendorCode"
= ORPD."CardCode"
--where ORPD."DocEntry" = :list_of_cols_val_tab_del and
ifnull(ITM2."ItemCode",'')= '';
--if :cnt>0
--then
--error := -2101;
 --select 'Item/s ' ||
--(
--select
--STRING_AGG(RPD1."ItemCode", ',')
--from ORPD
--inner join RPD1 on ORPD."DocEntry" = RPD1."DocEntry"
--left join ITM2 on RPD1."ItemCode" = ITM2."ItemCode" and
ITM2."VendorCode" = ORPD."CardCode"
--where ORPD."DocEntry" = :list_of_cols_val_tab_del and
ifnull(ITM2."ItemCode",'')= ''
--) ||
--' are not under the current vendor' into error_message from "DUMMY";

--end if;
--end if;

--END PURCHARSING

--START INVENTORY
--GOODS RECEIPT
if :object_type='59' and (:transaction_type='A' or :transaction_type='U')
then
--Goods Issue amount <> Goods Receipt Amount
select count(*) into cnt
from OIGN
where
OIGN."U_ftTransType" = 'A'
and OIGN."DocEntry" = :list_of_cols_val_tab_del
and OIGN."DocTotal" <> ifnull(OIGN."U_ftGIAmt",0);

if :cnt>0
then
error := -5901;
error_message := 'Goods Receipt Total is not equal to Goods Issue
total';
end if;

cnt := 0;

--Reference 2 should not be reused

select count(*) into cnt
from OIGN
where exists (select * from OIGN OIGN2 where OIGN2."Ref2" = OIGN."Ref2" and
OIGN2."CANCELED" = 'N' and OIGN2."DocEntry" <> :list_of_cols_val_tab_del)
and OIGN."DocEntry" = :list_of_cols_val_tab_del;

if :cnt>0
then
error := -5902;
error_message := 'Cannot use duplicate Goods Issue Ref No.';
end if;

end if;

-- INVENTORY TRANSFER REQUEST

if :object_type='1250000001' and (:transaction_type='A'
or :transaction_type='U')
then
if (:transaction_type='A')
 then
-- Item with Pending ITR
select count(*) into cnt
from WTQ1 A inner join OWTQ B on B."DocEntry" = A."DocEntry"
where A."ItemCode"||'-'||A."WhsCode" in
(SELECT C."ItemCode"||'-'||C."WhsCode" from WTQ1 C inner join OWTQ
D
on C."DocEntry" = D."DocEntry"
where C."TargetType" = '-1'
and C."LineStatus" = 'O'
and D."DocEntry" <> :list_of_cols_val_tab_del)
and B."U_ftSOrder" = 'N'
and B."DocEntry" = :list_of_cols_val_tab_del;

if :cnt>0
then
error := -12501;
select 'Cannot add document with pending itemcode in row no/s ' ||
(select STRING_AGG(A."VisOrder"+1, ',')
from WTQ1 A inner join OWTQ B on B."DocEntry" = A."DocEntry"
where A."ItemCode"||'-'||A."WhsCode" in
(SELECT C."ItemCode"||'-'||C."WhsCode" from WTQ1 C inner
join OWTQ D
on C."DocEntry" = D."DocEntry"
where C."TargetType" = '-1'
and C."LineStatus" = 'O'
and D."DocEntry" <> :list_of_cols_val_tab_del)
and B."U_ftSOrder" = 'N'
and B."DocEntry" = :list_of_cols_val_tab_del
) into error_message from "DUMMY";
end if;
end if;
cnt := 0;

-- Item that will result to negative qty

select count(*) into cnt
from WTQ1 inner join OWTQ on OWTQ."DocEntry" = WTQ1."DocEntry"
inner join OITW on OITW."ItemCode" = WTQ1."ItemCode" and OITW."WhsCode" =
WTQ1."FromWhsCod"
where OITW."OnHand" - WTQ1."Quantity" < 0
and OWTQ."DocEntry" = :list_of_cols_val_tab_del;

if :cnt>0
then
error := -12502;
select 'Cannot add document, will result to negative qty on row no/s. '
|| (
select STRING_AGG(WTQ1."VisOrder"+1, ',')
from WTQ1 inner join OWTQ on OWTQ."DocEntry" = WTQ1."DocEntry"
inner join OITW on OITW."ItemCode" = WTQ1."ItemCode" and
OITW."WhsCode" = WTQ1."FromWhsCod"
where OITW."OnHand" - WTQ1."Quantity" < 0
and OWTQ."DocEntry" = :list_of_cols_val_tab_del
) into error_message from "DUMMY";
end if;

cnt := 0;
 -- Duplicate ItemCode in same ITR
select count(*) into cnt
from(
select WTQ1."ItemCode" || '(' || STRING_AGG(WTQ1."VisOrder"+1, ',') || ')'
from OWTQ inner join WTQ1 on OWTQ."DocEntry" = WTQ1."DocEntry"
where OWTQ."DocEntry" = :list_of_cols_val_tab_del
group by WTQ1."ItemCode"
HAVING count(WTQ1."ItemCode") > 1
)TEMP;
if :cnt>0
then
error :=-12503;
select 'Cannot add document, duplicate item code in row no/s. ' ||

(
select STRING_AGG("DESCR", ' ') from
(

select WTQ1."ItemCode" || '(' || STRING_AGG(WTQ1."VisOrder"+1,

',') || ')' "DESCR"
from OWTQ inner join WTQ1 on OWTQ."DocEntry" = WTQ1."DocEntry"
where OWTQ."DocEntry" = :list_of_cols_val_tab_del
group by WTQ1."ItemCode"
HAVING count(WTQ1."ItemCode") > 1

)STR)
into error_message from "DUMMY";

end if;

end if;

-- INVENTORY TRANSFER
if :object_type='67' and (:transaction_type='A' or :transaction_type='U')
then
-- IT Qty > ITR Qty
select STRING_AGG(WTR1."VisOrder"+1, ',')
into error_message
from WTR1 inner join OWTR on OWTR."DocEntry" = WTR1."DocEntry"
inner join OWTQ on OWTQ."DocEntry" = WTR1."BaseEntry"
inner join WTQ1 on OWTQ."DocEntry" = WTQ1."DocEntry"
and WTR1."ItemCode" = WTQ1."ItemCode"
and WTR1."BaseLine" = WTQ1."LineNum"
where WTR1."Quantity" > WTQ1."Quantity"
and OWTR."DocEntry" = :list_of_cols_val_tab_del;

if error_message <> N'Ok'

then
error := -6701;
error_message := 'Cannot add document, qty exceeds ITR qty in row no/s.
' || :error_message;
end if;

-- Duplicate ItemCode in same IT

select count(*) into cnt
from(
select WTR1."ItemCode" || '(' || STRING_AGG(WTR1."VisOrder"+1, ',') || ')'
from OWTR inner join WTR1 on OWTR."DocEntry" = WTR1."DocEntry"
 where OWTR."DocEntry" = :list_of_cols_val_tab_del
group by WTR1."ItemCode"
HAVING count(WTR1."ItemCode") > 1
)TEMP;
if :cnt>0
then
error :=-6702;
select 'Cannot add document, duplicate item code in row no/s. ' ||

(
select STRING_AGG("DESCR", ' ') from
(

select WTR1."ItemCode" || '(' || STRING_AGG(WTR1."VisOrder"+1,

',') || ')' "DESCR"
from OWTR inner join WTR1 on OWTR."DocEntry" = WTR1."DocEntry"
where OWTR."DocEntry" = :list_of_cols_val_tab_del
group by WTR1."ItemCode"
HAVING count(WTR1."ItemCode") > 1

)STR)
into error_message from "DUMMY";

end if;

end if;

--END INVENTORY

-- Select the return values

select :error, :error_message FROM dummy;

end;