details: https://code.openbravo.com/erp/devel/pi/rev/f06b2e72932e
changeset: 16879:f06b2e72932e
user: Naiara Martinez <naiara.martinez <at> openbravo.com>
date: Tue Jun 19 16:18:44 2012 +0200
summary: Fixed bug 20771: automatic payment completing the invoice not
working properly
diffstat:
modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
| 100 ++++++---
1 files changed, 62 insertions(+), 38 deletions(-)
diffs (183 lines):
diff -r 899980d88f09 -r f06b2e72932e
modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
---
a/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
Wed Jun 20 17:21:28 2012 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-db/database/model/functions/APRM_GEN_PAYMENTSCHEDULE_INV.xml
Tue Jun 19 16:18:44 2012 +0200
@@ -16,11 +16,12 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2010-2011 Openbravo SLU
+* All portions are Copyright (C) 2010-2012 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
+
p_message VARCHAR(2000);
p_record_id VARCHAR(60);
p_docAction VARCHAR(60);
@@ -70,11 +71,12 @@
v_creditmultiplier NUMBER:= 1;
v_lang AD_User.Default_AD_Language%TYPE;
v_orderno C_Order.DocumentNo%TYPE:=null;
-v_finpayment_desc FIN_Payment.Description%TYPE;
+v_finpayment_desc VARCHAR2(2000);
v_orderno_trl AD_Message.MsgText%TYPE;
v_invoiceno_trl AD_Message.MsgText%TYPE;
v_payment_desc AD_OrgInfo.em_aprm_payment_desc%TYPE;
v_fin_orig_payment_schedule_id
Fin_Orig_Payment_Schedule.Fin_Orig_Payment_Schedule_Id%Type;
+v_description_first BOOLEAN := TRUE;
TYPE RECORD IS REF CURSOR;
Cur_Params RECORD;
@@ -82,6 +84,7 @@
cur_finaccpaymentmethod RECORD;
cur_PaymentScheduleOrder RECORD;
cur_paymentscheduledetail RECORD;
+cur_linktopayment_scheddetails RECORD;
BEGIN
@@ -570,28 +573,20 @@
FOR cur_paymentschedule IN (
SELECT ps.fin_paymentmethod_id, ps.duedate, ps.c_currency_id,
ps.ad_org_id, ps.fin_payment_schedule_id,
- coalesce(sum(psd.amount),0) as outstandingamt,
psd.fin_payment_schedule_order
+ coalesce(sum(psd.amount),0) as outstandingamt
FROM fin_payment_schedule ps
INNER JOIN fin_payment_scheduledetail psd ON
ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice
AND
psd.fin_payment_detail_id is null
WHERE ps.c_invoice_id = p_record_id
AND ps.isactive = 'Y'
- GROUP BY ps.fin_paymentmethod_id, ps.duedate, ps.c_currency_id,
ps.ad_org_id, ps.fin_payment_schedule_id, psd.fin_payment_schedule_order
+ GROUP BY ps.fin_paymentmethod_id, ps.duedate, ps.c_currency_id,
ps.ad_org_id, ps.fin_payment_schedule_id
ORDER BY ps.duedate, ps.fin_payment_schedule_id
)
LOOP
-- Create FIN_Payment description
SELECT ad_message_get2('InvoiceDocumentno', v_lang) INTO
v_invoiceno_trl FROM DUAL;
v_finpayment_desc:=v_invoiceno_trl || ': ' || v_documentno;
- SELECT MIN(o.DocumentNo) INTO v_orderno
- FROM fin_payment_schedule ps INNER JOIN c_order o ON (o.C_Order_ID =
ps.C_Order_ID)
- WHERE
fin_payment_schedule_id=cur_paymentschedule.fin_payment_schedule_order;
- IF (v_orderno IS NOT NULL) THEN
- v_finpayment_desc:=v_finpayment_desc || chr(13);
- SELECT ad_message_get2('OrderDocumentno', v_lang) INTO
v_orderno_trl FROM DUAL;
- v_finpayment_desc:=v_finpayment_desc || v_orderno_trl || ': ' ||
v_orderno;
- END IF;
-
+
FOR cur_finaccpaymentmethod IN (
SELECT CASE WHEN v_IsSOTrx='Y' THEN AUTOMATIC_RECEIPT ELSE
AUTOMATIC_PAYMENT END AS CreatePayment,
CASE WHEN v_IsSOTrx='Y' THEN AUTOMATIC_DEPOSIT ELSE
AUTOMATIC_WITHDRAWN END AS CreateFinTransaction,
@@ -637,20 +632,35 @@
'N', 'N', 'N', v_finpayment_desc,
v_Financial_Account_ID, v_PaymentDocType_ID, v_project_id,
v_campaign_id,
v_activity_id, v_user1, v_user2, 'N', 'N', 'N');
- v_FIN_PAYMENT_DETAIL_ID := GET_UUID();
- INSERT INTO fin_payment_detail(
- fin_payment_detail_id, ad_client_id, ad_org_id, created,
createdby,
- updated, updatedby, fin_payment_id, amount, refund, isactive,
- writeoffamt, c_glitem_id, isprepayment)
- VALUES (v_FIN_PAYMENT_DETAIL_ID, v_client_id,
cur_paymentschedule.AD_ORG_ID, now(), p_user,
- now(), p_user, v_FIN_PAYMENT_ID,
cur_paymentschedule.OUTSTANDINGAMT, 'N', 'Y',
- 0, NULL, 'N');
+
+ v_description_first := TRUE;
+ -- link to the new payment all the pending payment schedule
details
+ FOR cur_linktopayment_scheddetails IN (
+ SELECT FIN_PAYMENT_SCHEDULEDETAIL_ID, AMOUNT
+ FROM FIN_PAYMENT_SCHEDULEDETAIL
+ WHERE FIN_PAYMENT_SCHEDULE_INVOICE =
cur_paymentschedule.FIN_PAYMENT_SCHEDULE_ID
+ AND FIN_PAYMENT_DETAIL_ID IS NULL
+ )
+ LOOP
+ v_FIN_PAYMENT_DETAIL_ID := GET_UUID();
+ INSERT INTO fin_payment_detail(
+ fin_payment_detail_id, ad_client_id, ad_org_id, created,
createdby,
+ updated, updatedby, fin_payment_id, amount, refund, isactive,
+ writeoffamt, c_glitem_id, isprepayment)
+ VALUES (v_FIN_PAYMENT_DETAIL_ID, v_client_id,
cur_paymentschedule.AD_ORG_ID, now(), p_user,
+ now(), p_user, v_FIN_PAYMENT_ID,
cur_linktopayment_scheddetails.AMOUNT, 'N', 'Y',
+ 0, NULL, 'N');
+
+ UPDATE FIN_PAYMENT_SCHEDULEDETAIL
+ SET FIN_PAYMENT_DETAIL_ID = v_FIN_PAYMENT_DETAIL_ID
+ WHERE FIN_PAYMENT_SCHEDULEDETAIL_ID =
cur_linktopayment_scheddetails.FIN_PAYMENT_SCHEDULEDETAIL_ID
+ AND FIN_PAYMENT_DETAIL_ID IS NULL;
+ END LOOP;
--Fixes 14403 defect
-- Payment document id is added in success message when invoice
is completed with auto receipt is checked
v_Message:='@APRM_PaymentDocumentno@ ' || v_PaymentDocumentNo ||
' @beenCreated@';
UPDATE ad_ep_instance_para SET p_text= v_Message WHERE
ad_ep_instance_id= p_ep_instance AND parametername LIKE 'Message';
-
IF (cur_finaccpaymentmethod.deferred = 'N'
AND cur_finaccpaymentmethod.ExecutionType='A') THEN
INSERT INTO aprm_pending_paymentinvoice (
@@ -662,11 +672,7 @@
now(), p_user, now(), p_user,
'N', v_fin_payment_id, p_record_id,
cur_finaccpaymentmethod.execution_process_id);
END IF;
-
- UPDATE FIN_PAYMENT_SCHEDULEDETAIL
- SET FIN_PAYMENT_DETAIL_ID = v_FIN_PAYMENT_DETAIL_ID
- WHERE FIN_PAYMENT_SCHEDULE_INVOICE =
cur_paymentschedule.FIN_PAYMENT_SCHEDULE_ID
- AND FIN_PAYMENT_DETAIL_ID IS NULL;
+
UPDATE FIN_PAYMENT
SET PROCESSED = 'Y',
EM_APRM_PROCESS_PAYMENT = 'R'
@@ -674,15 +680,27 @@
-- Update payment schedule amounts and payment monitor in case
that the payment is not left in awaiting
-- execution status.
- IF (cur_finaccpaymentmethod.ExecutionType<>'A') THEN
- FOR cur_PaymentScheduleOrder IN (
- SELECT FIN_PAYMENT_SCHEDULE_ORDER
- FROM FIN_PAYMENT_SCHEDULEDETAIL
- WHERE FIN_PAYMENT_SCHEDULE_INVOICE =
cur_paymentschedule.FIN_PAYMENT_SCHEDULE_ID
- AND FIN_PAYMENT_SCHEDULE_ORDER IS NOT NULL
- GROUP BY FIN_PAYMENT_SCHEDULE_ORDER
- ) LOOP
-
+ FOR cur_PaymentScheduleOrder IN (
+ SELECT FIN_PAYMENT_SCHEDULE_ORDER
+ FROM FIN_PAYMENT_SCHEDULEDETAIL
+ WHERE FIN_PAYMENT_SCHEDULE_INVOICE =
cur_paymentschedule.FIN_PAYMENT_SCHEDULE_ID
+ AND FIN_PAYMENT_SCHEDULE_ORDER IS NOT NULL
+ GROUP BY FIN_PAYMENT_SCHEDULE_ORDER
+ ) LOOP
+ SELECT MIN(o.DocumentNo) INTO v_orderno
+ FROM fin_payment_schedule ps INNER JOIN c_order o ON
(o.C_Order_ID = ps.C_Order_ID)
+ WHERE
fin_payment_schedule_id=cur_PaymentScheduleOrder.FIN_PAYMENT_SCHEDULE_ORDER;
+ IF (v_orderno IS NOT NULL) THEN
+ IF (v_description_first) THEN
+ v_finpayment_desc:=v_finpayment_desc || chr(13);
+ SELECT ad_message_get2('OrderDocumentno', v_lang) INTO
v_orderno_trl FROM DUAL;
+ v_finpayment_desc:=v_finpayment_desc || v_orderno_trl ||
': ' || v_orderno;
+ v_description_first := FALSE;
+ ELSE
+ v_finpayment_desc:=v_finpayment_desc || ', ' || v_orderno;
+ END IF;
+ END IF;
+ IF (cur_finaccpaymentmethod.ExecutionType<>'A') THEN
UPDATE FIN_PAYMENT_SCHEDULE
SET PAIDAMT = (SELECT COALESCE(sum(amount +
COALESCE(writeoffamt, 0)), 0)
FROM fin_payment_scheduledetail
@@ -703,8 +721,14 @@
and
fin_payment.processed='Y' and fin_payment.status = 'RPAE'
and
fin_payment_detail.fin_payment_detail_id =
fin_payment_scheduledetail.fin_payment_detail_id)))
WHERE FIN_PAYMENT_SCHEDULE_ID =
cur_PaymentScheduleOrder.FIN_PAYMENT_SCHEDULE_ORDER;
- END LOOP;
-
+ END IF;
+ END LOOP;
+ IF (length(v_finpayment_desc) > 255) THEN
+ SELECT SUBSTR(v_finpayment_desc, 1, 252) || '...' INTO
v_finpayment_desc FROM DUAL;
+ END IF;
+ UPDATE FIN_PAYMENT SET DESCRIPTION = v_finpayment_desc
+ WHERE FIN_PAYMENT_ID = v_FIN_PAYMENT_ID;
+ IF (cur_finaccpaymentmethod.ExecutionType<>'A') THEN
UPDATE FIN_PAYMENT_SCHEDULE
SET PAIDAMT = PAIDAMT +
COALESCE(cur_paymentschedule.OUTSTANDINGAMT,0),
OUTSTANDINGAMT = OUTSTANDINGAMT -
COALESCE(cur_paymentschedule.OUTSTANDINGAMT, 0)
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
