How to create a database task to create manual vouchers automatically
Best answer by Marcel.Ausan
View original
Userlevel 6
+15
I don’t think there’s any Database Task for creating manual vouchers. So, if you really need this as Database TAsk that could be scheduled I guess a modification would be required.
Below are the standard tasks that contain the word %Voucher% in IFS Cloud. Nothing for Manual Voucher creation:
Userlevel 6
+15
1st event is triggered when ProjectInvoice is Released and creates the Manual Voucher Header + calls a 2nd event to create the voucher rows
DECLARE
info_ VARCHAR2(3200);
objid_ VARCHAR2(3200);
objversion_ VARCHAR2(3200);
attr_ VARCHAR2(3200);
acc_yr NUMBER;
acc_pr NUMBER;
rec_inv_count_ NUMBER;
C_rec Company_Finance_API.Public_Rec;
vou_ty_rec_ Voucher_Type_API.Public_Rec;
voucher_no_ VARCHAR2(3000);
company_ VARCHAR2(400);
cur_ VARCHAR2(200);
event_id_ VARCHAR2(8000):='C_CREATE_MANUAL_VOUCHER_BJ';
lu_name_ VARCHAR2(80):='ProjectInvoice';
em_ VARCHAR2(8000);
attr1_ VARCHAR2(32000);
proj_id_ VARCHAR2(400);
Cursor get_inv(invoice_id_ NUMBER,company_ VARCHAR2) IS
select count(*)
from invoicing_plan_cfv i
where i.invoice_id=invoice_id_
and i.company =company_
and Cf$_Deferred_Income<>0;
Cursor get_project_invoice_det(invoice_id_ NUMBER,company_ VARCHAR2) is
Select * from Project_Invoice p
where p.invoice_id=invoice_id_
and p.company=company_;
Cursor get_act_seq(project_id_ IN VARCHAR2) IS
select max(activity_seq)
from activity
where project_id=project_id_
group by project_id;
rec_inv_ get_project_invoice_det%rowtype;
act_seq_ VARCHAR2(2000);
pragma AUTONOMOUS_TRANSACTION;
BEGIN
company_:='&NEW:COMPANY';
vou_ty_rec_:=Voucher_Type_API.Get(company_,'M');
C_rec :=Company_Finance_API.Get(company_);
cur_:='&NEW:CURRENCY' ;
OPEN get_inv('&NEW:INVOICE_ID',company_);
FETCH get_inv into rec_inv_count_;
IF rec_inv_count_>0 THEN
OPEN get_project_invoice_det('&NEW:INVOICE_ID',company_);
FETCH get_project_invoice_det into rec_inv_;
User_Group_Period_API.Get_Period(acc_yr,acc_pr,company_,'AC',rec_inv_.invoice_date,'00');
client_sys.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('USER_GROUP','AC',attr_);
Client_SYS.Add_To_Attr('USERID','#USER_ID#',attr_);
Client_SYS.Add_To_Attr('COMPANY',company_,attr_);
Client_SYS.Add_To_Attr('VOUCHER_DATE',rec_inv_.invoice_date,attr_);
Client_SYS.Add_To_Attr('VOUCHER_TYPE','M',attr_);
Client_SYS.Add_To_Attr('FUNCTION_GROUP','M',attr_);
Client_SYS.Add_To_Attr('AUTOMATIC','Y',attr_);
Client_SYS.Add_To_Attr('AUTOMATIC_VOUCHER','MANUAL',attr_);
Voucher_No_Serial_API.Get_Next_Voucher_No(voucher_no_,company_,'M',rec_inv_.invoice_date,acc_yr,acc_pr);
Client_SYS.Add_To_Attr('VOUCHER_NO',voucher_no_,attr_);
Client_SYS.Add_To_Attr('DATE_REG',SYSDATE,attr_);
Client_SYS.Add_To_Attr('DESC_VOUCHER_TYPE',vou_ty_rec_.description,attr_);
Client_SYS.Add_To_Attr('ACCOUNTING_YEAR',acc_yr,attr_);
Client_SYS.Add_To_Attr('ACCOUNTING_PERIOD',acc_pr,attr_);
Client_SYS.Add_To_Attr('CURRENCY_CODE',cur_,attr_);
Client_SYS.Add_To_Attr('ENTERED_BY_USER_GROUP','AC',attr_);
Client_SYS.Add_To_Attr('USE_CORRECTION_ROWS','FALSE',attr_);
Client_SYS.Add_To_Attr('AMOUNT_METHOD',Def_Amount_Method_API.Decode(C_rec.def_amount_method),attr_);
Client_SYS.Add_To_Attr('VOUCHER_TEXT2','&NEW:INVOICE_NO' || '-' || '&NEW:SERIES_ID',attr_);
Voucher_API.New__(info_,objid_,objversion_,attr_,'DO');
voucher_no_ := Client_SYS.Get_Item_Value('VOUCHER_NO',attr_);
proj_id_ := rec_inv_.project_id;
OPEN get_act_seq(proj_id_);
FETCH get_act_seq INTO act_seq_;
em_ := Message_sys.Construct('C_CREATE_MANUAL_VOUCHER');
Message_sys.Add_Attribute(em_,'VOUCHER_NO',voucher_no_);
Message_sys.Add_Attribute(em_,'COMPANY',company_);
Message_sys.Add_Attribute(em_,'CURRENCY_CODE',cur_);
Message_sys.Add_Attribute(em_,'INVOICE_ID','&NEW:INVOICE_ID');
Message_sys.Add_Attribute(em_,'INVOICE_NO','&NEW:INVOICE_NO');
Message_sys.Add_Attribute(em_,'SERIES_ID','&NEW:SERIES_ID');
Message_sys.Add_Attribute(em_,'PROJECT_ID',proj_id_);
Message_sys.Add_Attribute(em_,'PROJECT_ACT',act_seq_);
Client_sys.Clear_Attr(attr1_);
Client_SYS.Add_To_Attr('EVENT_DATA_',em_,attr1_);
Client_SYS.Add_To_Attr('EVENT_ID_',event_id_,attr1_);
Client_SYS.Add_To_Attr('EVENT_LU_NAME_',lu_name_,attr1_);
attr1_ := Replace(attr1_,chr(31) || chr(31),chr(31));
Transaction_Sys.Deferred_Call('Event_sys.Event_Execute','PARAMETER',attr1_,'Creating voucher rows for manual voucher');
CLOSE get_act_seq;
CLOSE get_project_invoice_det;
COMMIT;
END IF;
CLOSE get_inv;
END;
then 2nd event is executed as a Background Job and created the Voucher rows based on some IAL object which is reading some GL Automatic PostinG Rule:
declare
info_ VARCHAR2(3200);
pcp_ VARCHAR2(1);
i_ VARCHAR2(3200);
v_ VARCHAR2(3200);
a_ VARCHAR2(3200);
ay_ VARCHAR2(4);
inv_ VARCHAR2(100):='&INVOICE_ID';
ap_ VARCHAR2(4);
ac_deamt NUMBER;
ac_cramt NUMBER;
thcur_cr NUMBER;
thcur_de NUMBER;
cf_rec_ Company_Finance_API.Public_Rec;
cp_ varchar2(200):='&COMPANY';
ir_ varchar2(20);
cda_ NUMBER;
cca_ NUMBER;
dr_ NUMBER;
cf_ NUMBER;
curi_ VARCHAR2(100);
cur_rate_ NUMBER;
acc_cur_ VARCHAR2(3);
par_cur VARCHAR2(3);
Vo_No_ VARCHAR2(200):='&VOUCHER_NO';
Cur_ VARCHAR2(30):='&CURRENCY_CODE';
amt_ NUMBER;pcd_ NUMBER;
CURSOR get_auto_post(inv_ VARCHAR2,cp_ VARCHAR2,it_id_ VARCHAR2) IS
select *
from ifsinfo.C_M_VOU_AUTO_POST1 p
where p.invoice_id=inv_
and p.company = cp_
and p.item_id = it_id_;
Cursor get_inv(inv_ VARCHAR2,cp_ VARCHAR2) IS
select *
from invoicing_plan_cfv i
where i.invoice_id=inv_
and i.company=cp_
and Cf$_Deferred_Income<>0;
PROCEDURE Add_(n_ IN VARCHAR2,v_ IN VARCHAR2,a_ IN OUT VARCHAR2) IS
BEGIN
Client_SYS.Add_To_Attr(n_,v_,a_);
END;
PROCEDURE Add_(n_ IN VARCHAR2,v_ IN DATE,a_ IN OUT VARCHAR2) IS
BEGIN
Client_SYS.Add_To_Attr(n_,v_,a_);
END;
PROCEDURE Add_(n_ IN VARCHAR2,v_ IN NUMBER,a_ IN OUT VARCHAR2) IS
BEGIN
Client_SYS.Add_To_Attr(n_,v_,a_);
END;
pragma AUTONOMOUS_TRANSACTION;
BEGIN
FOR rec_inv_ in get_inv(inv_,cp_) LOOP
FOR rec_ in get_auto_post(inv_,cp_,rec_inv_.item_id) LOOP
User_Group_Period_API.Get_Period (ay_,ap_,cp_,'AC',rec_.inv_dt,'00');
client_sys.Clear_Attr(a_);
Add_('PROJECT_ID',rec_.pro_id,a_);
Add_('ACCOUNT',rec_.acc,a_);
IF rec_.pro_id IS NOT NULL
THEN Add_('PROJECT_ACTIVITY_ID',rec_.proj_act_id,a_);
END IF;
Add_('CODE_B',rec_.co_b,a_);
Add_('CODE_C',rec_.co_c,a_);
Add_('CODE_D',rec_.co_d,a_);
Add_('CODE_E',rec_.co_e,a_);
Add_('CODE_F',rec_.co_f,a_);
Add_('CODE_G',rec_.co_g,a_);
Add_('CODE_H',rec_.co_h,a_);
Add_('CODE_I',rec_.co_i,a_);
Add_('CODE_J',rec_.co_j,a_);
Add_('COMPANY',cp_,a_);
Add_('TRANS_CODE','MANUAL',a_);
Add_('VOUCHER_DATE',rec_.inv_dt,a_);
Add_('VOUCHER_NO',Vo_No_,a_);
Add_('VOUCHER_TYPE','M',a_);
Add_('ACCOUNTING_YEAR',ay_,a_);
Add_('ACCOUNTING_PERIOD',ap_,a_);
Add_('CURRENCY_CODE',Cur_,a_);
Add_('CURRENCY_TYPE',rec_.curt, a_);
cf_rec_:=company_Finance_API.Get(cp_);
acc_cur_:=cf_rec_.currency_code;
par_cur:=cf_rec_.PARALLEL_ACC_CURRENCY;
Currency_Rate_API.Fetch_Currency_Rate_Base(cf_,cur_rate_,ir_,cp_,Cur_,acc_cur_,rec_.curt,rec_.inv_dt,'DUMMY');
Currency_Code_API.Get_No_Of_Decimals_In_Rate_(dr_,cp_,Cur_);
Add_('CURRENCY_RATE', ROUND(cur_rate_, dr_), a_);
Add_('PARALLEL_CURRENCY',par_cur,a_);
Add_('PARALLEL_CURR_RATE_TYPE',cf_rec_.parallel_rate_type, a_);
IF par_cur IS NOT NULL
THEN
currency_rate_API.Get_Parallel_Currency_Rate(cur_rate_,cf_,curi_,cp_,Cur_,rec_.inv_dt);
pcd_:=ROUND(cur_rate_,dr_);
Add_('PARALLEL_CURRENCY_RATE',pcd_,a_);
END IF;
Add_('REFERENCE_SERIE','&SERIES_ID',a_);
Add_('REFERENCE_NUMBER','&INVOICE_NO', a_);
Add_('AUTO_TAX_VOU_ENTRY','FALSE',a_);
IF (rec_.rslt_fact<0)
THEN
cda_:=-Round((rec_inv_.Cf$_Deferred_Income*(rec_.rslt_fact/100)),2);
ac_deamt:=CURRENCY_AMOUNT_PUB_API.Calc_Base_Amount(cp_,Cur_,cda_,rec_.inv_dt);
thcur_de:=CURRENCY_AMOUNT_API.Calculate_Parallel_Curr_Amount(cp_,rec_.inv_dt,ac_deamt,cda_,acc_cur_,Cur_);
Add_('CURRENCY_DEBET_AMOUNT',cda_,a_);
Add_('DEBET_AMOUNT',ac_deamt,a_);
Add_('THIRD_CURRENCY_DEBIT_AMOUNT',thcur_de,a_);
Add_('AMOUNT',ac_deamt,a_);
Add_('CURRENCY_AMOUNT',cda_,a_);
Add_('THIRD_CURRENCY_AMOUNT',thcur_de,a_);
ELSE
cca_:=Round(rec_inv_.Cf$_Deferred_Income*(rec_.rslt_fact/100),2);
ac_cramt:=CURRENCY_AMOUNT_PUB_API.Calc_Base_Amount(cp_,Cur_,cca_,rec_.inv_dt);
thcur_cr:=CURRENCY_AMOUNT_API.Calculate_Parallel_Curr_Amount(cp_,rec_.inv_dt,ac_cramt,cca_,acc_cur_,Cur_);
Add_('CURRENCY_CREDIT_AMOUNT',cca_,a_);
Add_('CREDIT_AMOUNT',ac_cramt,a_);
Add_('THIRD_CURRENCY_CREDIT_AMOUNT',thcur_cr,a_);
Add_('AMOUNT',ac_cramt,a_);
Add_('CURRENCY_AMOUNT',cca_,a_);
Add_('THIRD_CURRENCY_AMOUNT',thcur_cr,a_);
END IF;
voucher_row_api.New__(info_,i_,v_,a_,'DO');
END LOOP;
END LOOP;
COMMIT;
END;
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.