Validasi PO/Purchase Order di SAP Business One

Halo Semua,
wah sudah lama juga ya gw gak pernah posting di Blog ini ehehehe. Kali ini saya mau coba upload coretan saya sewaktu membuat Validasi untuk PO/Purchase Order di SAP Business One yang menggunakan database SQL Server 2008.

Ide awalnya kenapa mau posting disini yaitu supaya kalian bisa baca-baca dan siapa tahu membantu, karena dulu pas buatnya utak-atik sana sini dan karena tidak ada background spesialisasi ke database alias otodidak sehingga gw sedikit pusing juga hehehe, selain itu buat arsip gw juga, jadi sewaktu-waktu butuh bisa buka disini :D tomzquad.blogspot.com

Query yang saya share disini langsung potongannya aja ya, bukan dari atas sampe bawahnya, kalo atas sama bawah mah kalian aja ya heee...

Silahkan ya dibaca-baca Query gw yg jauh dari sempurna ini :D

----Validasi PO Tidak Boleh di add tanpa isi Business Unit (Created by Thomas 26-05-2015)
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.U_HAN_BusinessUnit from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T0.U_HAN_BusinessUnit is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Business Unit cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Purchase Type (Created by Thomas 26-05-2015)
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.U_HAN_POType from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T0.U_HAN_POType is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Purchase Type cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Partial Delivery (Created by Thomas 26-05-2015)
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.U_MIS_PartialDeliver from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T0.U_MIS_PartialDeliver is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Partial Delivery cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Buyer Name (Created by Thomas 26-05-2015)
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.SlpCode from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T0.SlpCode is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Buyer Name cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Promise Date From Principal (Created by Thomas 26-05-2015)~~
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T1.U_Test_PromDtFrPrinc from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T1.U_Test_PromDtFrPrinc is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Column Promise Date From Principal cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Promise Date To Customer (Created by Thomas 26-05-2015)~~
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T1.U_Test_PromDtToCus from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T1.U_Test_PromDtToCus is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Column Promise Date To Customer cannot be empty! Please fill it.'
End

-----Validasi PO Tidak Boleh di add tanpa isi Customer Name (Created by Thomas 26-05-2015)~~
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T1.U_Han_CustName from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T1.U_Han_CustName is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Column Customer Name cannot be empty! Please fill it.'
End

--Validasi PO Tidak Boleh di add tanpa isi Item Remarks (Created by Thomas 26-05-2015)~~
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T1.U_MIS_ItemRemark from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T1.U_MIS_ItemRemark is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Column Item Remarks cannot be empty! Please fill it.'
End

----Validasi PO Tidak Boleh di add tanpa isi Vendor Ref. No. (Created Thomas 26-05-2015
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.NumAtCard from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '103' AND T0.NumAtCard is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Vendor Ref. No. cannot be empty! Please fill it.'
End

--Validasi PO Tidak Boleh di add tanpa isi Project (Created by Thomas 26-05-2015)
IF (@object_type= '112') and @transaction_type in ('A')
BEGIN
IF EXISTS (select T0.U_HAN_ProjectName from ODRF T0
INNER JOIN DRF1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OWDD T2 ON T2.DocEntry = T0.DocEntry
where T0.ObjType = 22 AND T0.Series = '271' AND T0.U_HAN_ProjectName is null AND T0.DocEntry = @list_of_cols_val_tab_del)
Select @error=1, @error_message='Field Project cannot be empty! Please fill it.'
End