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;