PayInAdvanceExample

Pay in advance

Why?

Let's go to create a new process to make easier the management of payments in advance. This workflow in Openbravo 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 in the BD of type button. Assign to this column the new process PayInAdvance_Create

Create a System element called PayInAdvance

Create a button in the CashLine Tab with 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;