Projects:Advance Payment
Why?
Let's go to create a new process to make easier the management of payments in advance. This workflow in Openbravo ERP it's a little bit tricky because involves "so many steps" and makes it prone to mistakes.
How?
1.- Create a new cash type (Reference->List of values->C_Cash Trx Type)
Identificator: A
Name: PayInAdvance
Traduction (if needed): Anticipo
2.- Create a new process called PayInAdvance_Create
3.- Create the column PayInAdvance of type button on the table C_CashLine. Assign to this column the new process PayInAdvance_Create
In the Application Dictionary create an Application Element called PayInAdvance and assign it to this column.
Create a button in the CashLine Tab using the previous created column and assign it the Logic: @CashType@='A'.
5.- Create the value 'AN' in the Status reference list
Procedure: PayInAdvance_Create
Parameters (defined the same way as in the process C_Debt_Payment_Create)
Business Partner Amount Currency Form of payment Due Date Receipt Description Status_Initial
Output (Executing the process):
1.- Generate Settlement (C_Settlement) 2.- Generate two debtpayments (C_Debt_Payment_Id) A) POSITIVE debtpayment B) NEGATIVE debtpayment 3.- UPDATES (C_Cashline_Id) A) UPDATE the field C_Debt_Payment_Id B) UPDATE the field CashType C) UPDATE the field Amount
Code: PAYINADVANCE_CREATE
CREATE OR REPLACE PROCEDURE "TAD"."PAYINADVANCE_CREATE" ( p_PInstance_ID IN NUMBER ) IS ----------------------------------------------------------------- --Requirements and specification, gdoc: Payinadvance ----------------------------------------------------------------- -- Logistice v_ResultStr VARCHAR2(2000):=; v_Message VARCHAR2(2000):=; v_Result NUMBER:=1; -- 0=failure v_Record_ID NUMBER(10) ; v_AD_User_ID NUMBER(10) ; -- Parameter TYPE RECORD IS REF CURSOR; Cur_Parameter RECORD; --Parameter variables v_C_BPartner_ID NUMBER; v_C_Cashline_ID NUMBER; v_C_Cashbook_Id NUMBER; v_Amount NUMBER; v_C_Currency_ID NUMBER; v_PaymentRule VARCHAR2(1) ; v_DatePlanned DATE; v_IsReceipt VARCHAR2(1) ; v_Description VARCHAR2(60) ; --Local variables v_Client_ID NUMBER; v_AD_Org_ID NUMBER; v_settlementID NUMBER(10):=NULL; v_SettlementDocType_ID NUMBER(10) ; v_SDocumentNo C_SETTLEMENT.DocumentNo%TYPE; v_debtPaymentID NUMBER(10) ; v_CBankAccount_ID C_BankStatement.C_BankAccount_ID%TYPE; v_C_BankCurrency NUMBER(10); v_BS_Date DATE; BEGIN -- Update AD_PInstance DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID) ; v_ResultStr:='PInstanceNotFound'; AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ; BEGIN --BODY
-- Get Parameters v_ResultStr:='ReadingParameters'; FOR Cur_Parameter IN (SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date, i.AD_USER_ID FROM AD_PInstance i LEFT JOIN AD_PInstance_Para p ON i.AD_PInstance_ID=p.AD_PInstance_ID WHERE i.AD_PInstance_ID=p_PInstance_ID ) LOOP v_Record_ID:=Cur_Parameter.Record_ID; v_AD_User_ID:=Cur_Parameter.AD_User_ID; IF(Cur_Parameter.ParameterName='C_BPartner_ID') THEN v_C_BPartner_ID:=Cur_Parameter.P_Number; DBMS_OUTPUT.PUT_LINE(' C_BPartner_ID=' || v_C_BPartner_ID) ; ELSIF(Cur_Parameter.ParameterName='Amount') THEN v_Amount:=Cur_Parameter.P_Number; DBMS_OUTPUT.PUT_LINE(' Amount=' || v_Amount) ; ELSIF(Cur_Parameter.ParameterName='C_Currency_ID') THEN v_C_Currency_ID:=Cur_Parameter.P_Number; DBMS_OUTPUT.PUT_LINE(' C_Currency_ID=' || v_C_Currency_ID) ; ELSIF(Cur_Parameter.ParameterName='PaymentRule') THEN v_PaymentRule:=Cur_Parameter.P_String; DBMS_OUTPUT.PUT_LINE(' v_PaymentRule=' || v_PaymentRule) ; ELSIF(Cur_Parameter.ParameterName='Dateplanned') THEN v_DatePlanned:=Cur_Parameter.P_Date; DBMS_OUTPUT.PUT_LINE(' DatePlanned=' || v_DatePlanned) ; ELSIF(Cur_Parameter.ParameterName='IsReceipt') THEN v_IsReceipt:=Cur_Parameter.p_String; DBMS_OUTPUT.PUT_LINE(' IsReceipt='||v_IsReceipt) ; ELSIF(Cur_Parameter.ParameterName='Description') THEN v_Description:=Cur_Parameter.p_String; DBMS_OUTPUT.PUT_LINE(' Description='||v_Description) ; ELSE DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ; END IF; END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID) ; --Read Cash Line v_ResultStr:='ReadingCashLine '||v_record_Id; --Only for clearness in SELECTs, UPDATEs and INSERTs v_C_Cashline_ID:=v_record_Id;
--Get Client, Org and CashBook SELECT L.AD_Client_ID, L.AD_Org_ID, B.C_Cashbook_ID INTO v_Client_ID, v_AD_Org_ID, v_C_Cashbook_Id FROM C_Cashline L, C_Cash B WHERE L.C_Cashline_ID=v_C_Cashline_ID AND L.C_Cash_ID=B.C_Cash_ID;
--Insert Settlement v_ResultStr:='InsertingSettlement'; v_SettlementDocType_ID:=Ad_Get_DocType(v_Client_ID, v_AD_Org_ID, TO_CHAR('STT')) ; Ad_Sequence_Next('C_Settlement', v_Record_ID, v_settlementID) ; Ad_Sequence_Doctype(v_SettlementDocType_ID, v_Record_ID, 'Y', v_SDocumentNo) ; IF(v_SDocumentNo IS NULL) THEN Ad_Sequence_Doc('DocumentNo_C_Settlement', v_Client_ID, 'Y', v_SDocumentNo) ; END IF; --We do here requirement 1) INSERT INTO C_SETTLEMENT ( C_SETTLEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, DOCUMENTNO, DATETRX, DATEACCT, SETTLEMENTTYPE, C_DOCTYPE_ID, PROCESSING, PROCESSED, POSTED, C_CURRENCY_ID, Description, ISGENERATED ) /*, C_PROJECT_ID, C_CAMPAIGN_ID, C_ACTIVITY_ID, USER1_ID, USER2_ID, CREATEFROM)*/ VALUES ( v_SettlementID, v_Client_ID, v_AD_Org_ID, 'Y', now(), v_AD_User_ID, now(), v_AD_User_ID, '*DPC*'||v_SDocumentNo, trunc(now()), trunc(now()), 'C', v_SettlementDocType_ID, 'N', 'N', 'N', v_C_Currency_ID, v_Description, 'Y' ) ;
--Insert generated debt payment --We do here requirement 2.B) v_ResultStr:='InsertingGeneratedDebtPayement'; Ad_Sequence_Next('C_Debt_Payment', v_Record_ID, v_debtPaymentID) ; INSERT INTO C_DEBT_PAYMENT ( C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, ISRECEIPT, C_SETTLEMENT_GENERATE_ID, DESCRIPTION, C_INVOICE_ID, C_BPARTNER_ID, C_CURRENCY_ID, /*C_CASHLINE_ID, C_BANKACCOUNT_ID, C_CASHBOOK_ID,*/ PAYMENTRULE, ISPAID, AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL, ISVALID, /*C_BANKSTATEMENTLINE_ID,*/ CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED, GENERATE_PROCESSED, STATUS_INITIAL ) VALUES ( v_debtPaymentID, v_Client_ID, v_AD_Org_ID, 'Y', now(), v_AD_User_ID, now(), v_AD_User_ID, v_IsReceipt, v_settlementID, v_Description, null, v_C_BPartner_ID, v_C_Currency_ID, v_PaymentRule, 'N', v_Amount*(-1), 0, v_DatePlanned, 'N', 'Y', 'N', 'N', 'Y', 'AN' ) ; Ad_Sequence_Next('C_Debt_Payment', v_Record_ID, v_debtPaymentID) ;
--We insert it in the positive side of the bank account --We do here requirement 2.A) INSERT INTO C_DEBT_PAYMENT ( C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, ISRECEIPT, C_SETTLEMENT_GENERATE_ID, DESCRIPTION, C_INVOICE_ID, C_BPARTNER_ID, C_CURRENCY_ID, /*C_CASHLINE_ID,*/ /*C_BANKACCOUNT_ID,*/ C_CASHBOOK_ID, PAYMENTRULE, ISPAID, AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL, ISVALID, /*C_BANKSTATEMENTLINE_ID,*/ CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED, GENERATE_PROCESSED, STATUS_INITIAL ) VALUES ( v_debtPaymentID, v_Client_ID, v_AD_Org_ID, 'Y', now(), v_AD_User_ID, now(), v_AD_User_ID, v_IsReceipt, v_settlementID, v_Description, null, v_C_BPartner_ID, v_C_Currency_ID, /*v_C_Cashline_ID,*/ v_C_Cashbook_Id, /*v_CBankAccount_ID,*/ v_PaymentRule, 'N', v_Amount, 0, v_DatePlanned, 'N', 'Y', 'N', 'N', 'Y', 'DE' ) ; c_settlement_post(null, v_settlementID);
--We do here requirements 3.A) and 3.B) and 3.C) UPDATE C_CASHLINE SET C_DEBT_PAYMENT_ID=v_debtPaymentID , CASHTYPE='P', AMOUNT=v_Amount WHERE C_CASHLINE_ID=v_C_Cashline_ID ;
--<<FINISH_PROCESS>> -- Update AD_PInstance DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ; AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ; RETURN; END; --BODY EXCEPTION WHEN OTHERS THEN v_ResultStr:= '@ERROR=' || SQLERRM; DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; ROLLBACK; AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ; RETURN; END PayInAdvance_Create;