· 7 years ago · Jan 02, 2019, 10:16 AM
1-- Function: public.c_order_post1(character varying, character varying, character varying)
2
3-- DROP FUNCTION public.c_order_post1(character varying, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION public.c_order_post1(
6 p_pinstance_id character varying,
7 p_order_id character varying,
8 p_recalculatediscounts character varying)
9 RETURNS void AS
10$BODY$ DECLARE
11/*************************************************************************
12* The contents of this file are subject to the Openbravo Public License
13* Version 1.1 (the "License"), being the Mozilla Public License
14* Version 1.1 with a permitted attribution clause; you may not use this
15* file except in compliance with the License. You may obtain a copy of
16* the License at http://www.openbravo.com/legal/license.html
17* Software distributed under the License is distributed on an "AS IS"
18* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
19* License for the specific language governing rights and limitations
20* under the License.
21* The Original Code is Openbravo ERP.
22* The Initial Developer of the Original Code is Openbravo SLU
23* All portions are Copyright (C) 2001-2017 Openbravo SLU
24* All Rights Reserved.
25* Contributor(s): ______________________________________.
26************************************************************************/
27 -- Logistics
28 v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
29 v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
30 v_Record_ID VARCHAR(32); --OBTG:VARCHAR2--
31 v_User VARCHAR(32); --OBTG:VARCHAR2--
32 v_IsProcessing CHAR(1) ;
33 v_IsProcessed VARCHAR(60) ;
34 v_Result NUMERIC:=1; -- Success
35 v_is_included NUMERIC:=0;
36 v_is_ready AD_Org.IsReady%TYPE;
37 v_is_tr_allow AD_OrgType.IsTransactionsAllowed%TYPE;
38 -- Parameter
39 --TYPE RECORD IS REFCURSOR;
40 Cur_Parameter RECORD;
41 Cur_line RECORD;
42 Cur_Order RECORD;
43 -- Record Info
44 v_Client_ID VARCHAR(32); --OBTG:VARCHAR2--
45 v_Org_ID VARCHAR(32); --OBTG:VARCHAR2--
46 v_Warehouse_Org VARCHAR(32); --OBTG:VARCHAR2--
47 v_Org_Name VARCHAR(60); --OBTG:VARCHAR2--
48 v_UpdatedBy VARCHAR(32); --OBTG:VARCHAR2--
49 v_DocAction VARCHAR(60) ;
50 v_DocStatus VARCHAR(60) ;
51 v_InvoiceRule VARCHAR(60) ;
52 v_M_Warehouse_ID VARCHAR(32); --OBTG:VARCHAR2--
53 v_DocType_ID VARCHAR(32); --OBTG:VARCHAR2--
54 v_DocTypeTarget_ID VARCHAR(32); --OBTG:VARCHAR2--
55 v_DocSubTypeSO VARCHAR(60) ;
56 v_DocSubTypeSOTarget VARCHAR(60) ;
57 v_IsReturnDocType CHAR(1);
58 v_IsBinding CHAR(1):='Y';
59 v_istaxincluded CHAR(1);
60 --
61 ToDeliver NUMERIC:=0;
62 ToInvoice NUMERIC:=0;
63 ToDeliverOrToInvoice NUMERIC:=0;
64 --
65 InOut_ID VARCHAR(32); --OBTG:VARCHAR2--
66 Invoice_ID VARCHAR(32); --OBTG:VARCHAR2--
67 --Added by P.SAROBE
68 v_documentno_Settlement VARCHAR(40); --OBTG:VARCHAR2--
69 v_dateSettlement TIMESTAMP;
70 v_Cancel_Processed VARCHAR(60);
71 v_nameBankstatement VARCHAR (60); --OBTG:VARCHAR2--
72 v_dateBankstatement TIMESTAMP;
73 v_nameCash VARCHAR (60); --OBTG:VARCHAR2--
74 v_dateCash TIMESTAMP;
75 v_Bankstatementline_ID VARCHAR(32); --OBTG:VARCHAR2--
76 --Finish added by P.Sarobe
77 v_CashLine_ID VARCHAR(32); --OBTG:VARCHAR2--
78 v_ispaid CHAR(1);
79 v_Settlement_Cancel_ID VARCHAR(32); --OBTG:VARCHAR2--
80 v_Cash_ID VARCHAR(32):=NULL; --OBTG:VARCHAR2--
81 v_Line NUMERIC:=0;
82 v_CashBook_ID VARCHAR(32):=NULL; --OBTG:VARCHAR2--
83 v_Debtpayment_ID VARCHAR(32); --OBTG:VARCHAR2--
84 v_ISO_Code VARCHAR(10) ;
85 v_DocumentNo VARCHAR(200) ; --OBTG:VARCHAR2--
86 v_GrandTotal NUMERIC;
87 v_Multiplier NUMERIC:=1;
88 v_Date TIMESTAMP;
89 v_WarehouseName VARCHAR(60) ; --OBTG:VARCHAR2--
90 v_count NUMERIC;
91 v_isSoTrx CHAR(1) ;
92 v_Aux NUMERIC;
93 v_c_Bpartner_ID VARCHAR(32); --OBTG:VARCHAR2--
94 v_c_currency_ID VARCHAR(32); --OBTG:VARCHAR2--
95 v_C_PROJECT_ID VARCHAR(32); --OBTG:VARCHAR2--
96 FINISH_PROCESS BOOLEAN:=FALSE;
97 END_PROCESSING BOOLEAN:=FALSE;
98 v_CBPartner_ID VARCHAR(32); --OBTG:VARCHAR2--
99 rowcount NUMERIC;
100
101 v_CumDiscount NUMERIC;
102 v_OldCumDiscount NUMERIC;
103 v_OrderLineSeqNo NUMERIC;
104 Cur_COrderDiscount RECORD;
105 Cur_TaxDiscount RECORD;
106 v_OrderLine VARCHAR(32); --OBTG:VARCHAR2--
107 v_Discount NUMERIC;
108 v_pricePrecision C_Currency.PricePrecision%TYPE;
109 v_stdPrecision C_Currency.StdPrecision%TYPE;
110 Cur_OrderLine RECORD;
111 v_DiscountExist NUMERIC;
112 v_gross_unit_price NUMERIC;
113 v_line_gross_amount NUMERIC;
114 v_acctAmount NUMERIC;
115 v_reject_reason VARCHAR(32);
116 v_dummy VARCHAR(2000); --OBTG:VARCHAR2--
117 v_bpartner_blocked VARCHAR(1):='N'; --OBTG:VARCHAR2--
118 v_orderBlocking VARCHAR(1):='N'; --OBTG:VARCHAR2--
119 v_bpartner_name c_bpartner.name%TYPE;
120 v_productname m_product.name%TYPE;
121
122 v_iscashvat C_Order.IsCashVat%TYPE;
123 v_recalculateDiscounts VARCHAR(1):='Y';
124 v_isactive VARCHAR(1):='N'; --OBTG:VARCHAR2--
125
126 BEGIN
127 IF (p_PInstance_ID IS NOT NULL) THEN
128 -- Update AD_PInstance
129 RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
130 v_ResultStr:='PInstanceNotFound';
131 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
132 -- Get Parameters
133 v_ResultStr:='ReadingParameters';
134 FOR Cur_Parameter IN
135 (SELECT i.Record_ID, i.AD_User_ID, p.ParameterName, p.P_String,
136 p.P_Number, p.P_Date
137 FROM AD_PINSTANCE i
138 LEFT JOIN AD_PINSTANCE_PARA p
139 ON i.AD_PInstance_ID=p.AD_PInstance_ID
140 WHERE i.AD_PInstance_ID=p_PInstance_ID
141 ORDER BY p.SeqNo
142 )
143 LOOP
144 v_Record_ID:=Cur_Parameter.Record_ID;
145 v_User:=Cur_Parameter.AD_User_ID;
146 IF (Cur_Parameter.ParameterName = 'recalculateDiscounts') THEN
147 v_recalculateDiscounts := Cur_Parameter.P_String;
148 END IF;
149 END LOOP; -- Get Parameter
150 ELSE
151 v_Record_ID:=p_Order_ID;
152 v_recalculateDiscounts := p_recalculateDiscounts;
153 SELECT CREATEDBY INTO v_User FROM C_ORDER WHERE C_ORDER_ID=p_Order_ID;
154 END IF;
155 RAISE NOTICE '%',' Record_ID=' || v_Record_ID ;
156 BEGIN --BODY
157
158 SELECT o.ISSOTRX, o.c_bpartner_id, o.DocAction, c.PricePrecision, c.StdPrecision, p.IsTaxIncluded
159 INTO v_IsSOTrx, v_c_Bpartner_ID, v_DocAction, v_pricePrecision, v_stdPrecision, v_isTaxIncluded
160 FROM C_ORDER o
161 JOIN C_CURRENCY c
162 ON o.C_CURRENCY_ID = c.C_CURRENCY_ID
163 JOIN M_PRICELIST p
164 ON o.M_PRICELIST_ID = p.M_PRICELIST_ID
165 WHERE o.C_ORDER_ID = v_Record_ID;
166
167 /*Orderline acct dimension*/
168 IF (v_IsSOTrx = 'N') THEN
169 FOR Cur_line IN
170 (SELECT C_ORDERLINE.C_OrderLine_ID,
171 C_ORDERLINE.LinenetAmt
172 FROM C_ORDERLINE
173 WHERE C_Order_ID = v_Record_ID
174 )
175 LOOP
176 SELECT SUM(Amt) INTO v_acctAmount
177 FROM C_ORDERLINE_ACCTDIMENSION
178 WHERE C_OrderLine_ID = Cur_line.C_OrderLine_ID;
179 IF (v_acctAmount <> Cur_line.LinenetAmt) THEN
180 v_Message:='@QuantitiesNotMatch@';
181 RAISE EXCEPTION '%', '@QuantitiesNotMatch@' ; --OBTG:-20000--
182 END IF;
183 END LOOP;
184 ELSE
185 IF (v_DocAction IN ('CO', 'PR')) THEN
186 SELECT COUNT(1)
187 INTO v_Count
188 FROM DUAL
189 WHERE EXISTS(SELECT 1
190 FROM C_ORDERLINE, M_PRODUCT
191 WHERE C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
192 AND ISLINKEDTOPRODUCT = 'Y'
193 AND C_ORDER_ID = v_Record_ID
194 AND NOT EXISTS (SELECT 1 FROM C_ORDERLINE_SERVICERELATION WHERE C_ORDERLINE_SERVICERELATION.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID));
195 IF (v_Count <> 0) THEN
196 v_Message:='@MissingServiceRelation@';
197 RAISE EXCEPTION '%', '@MissingServiceRelation@' ; --OBTG:-20000--
198 END IF;
199 END IF;
200 END IF;
201
202 /* Check active business partner*/
203 SELECT isactive INTO v_isactive
204 FROM C_Bpartner
205 WHERE C_Bpartner_ID = v_C_Bpartner_ID;
206
207 IF(v_isactive = 'N') THEN
208 RAISE EXCEPTION '%', '@InActiveBusinessPartner@'; --OBTG:-20000--
209 END IF;
210
211 /**
212 * Read Order
213 */
214 v_ResultStr:='ReadingOrder';
215 SELECT Processing, Processed, DocAction, DocStatus,
216 C_DocType_ID, C_DocTypeTarget_ID, c_order.AD_Client_ID,
217 c_order.AD_Org_ID, c_order.UpdatedBy, M_Warehouse_ID, TRUNC(DateOrdered),
218 Issotrx, c_Bpartner_Id, c_order.c_currency_id, C_PROJECT_ID,
219 C_BPartner_ID, invoicerule, c_order.IsCashVat
220 INTO v_IsProcessing, v_IsProcessed, v_DocAction, v_DocStatus,
221 v_DocType_ID, v_DocTypeTarget_ID, v_Client_ID,
222 v_Org_ID, v_UpdatedBy, v_M_Warehouse_ID, v_Date,
223 v_isSoTrx, v_c_Bpartner_Id, v_c_currency_id, v_C_PROJECT_ID,
224 v_CBPartner_ID, v_invoicerule, v_iscashvat
225 FROM C_ORDER
226 WHERE C_Order_ID=v_Record_ID FOR UPDATE; --OBTG: --
227
228 -- Get current DocSubTypeSO
229 SELECT DocSubTypeSO
230 INTO v_DocSubTypeSO
231 FROM C_DOCTYPE
232 WHERE C_DocType_ID=v_DocType_ID;
233 SELECT DocSubTypeSO, isreturn
234 INTO v_DocSubTypeSOTarget, v_isreturndoctype
235 FROM C_DOCTYPE
236 WHERE C_DocType_ID=v_DocTypetarget_ID;
237
238 IF(v_isreturndoctype='Y' AND v_DocAction='CO') THEN
239 DECLARE
240 v_RefReturnQty NUMERIC:=0;
241 v_RefMovementQty NUMERIC:=0;
242 BEGIN
243 v_Message:=NULL;
244 FOR Cur_line IN
245 (SELECT OL.M_INOUTLINE_ID, OL.LINE, OL.QTYORDERED
246 FROM C_ORDERLINE OL
247 WHERE OL.C_Order_ID = v_Record_ID
248 AND OL.M_INOUTLINE_ID IS NOT NULL
249 )
250 LOOP
251 SELECT COALESCE(SUM(OL.QtyOrdered),0) INTO v_RefReturnQty
252 FROM C_ORDERLINE OL, C_ORDER O
253 WHERE OL.M_INOUTLINE_ID = Cur_line.M_INOUTLINE_ID
254 AND OL.C_ORDER_ID = O.C_ORDER_ID
255 AND O.PROCESSED='Y';
256 SELECT MovementQty INTO v_RefMovementQty
257 FROM M_INOUTLINE WHERE M_INOUTLINE_ID = Cur_line.M_INOUTLINE_ID;
258 v_RefReturnQty:=ABS(v_RefReturnQty) + ABS(Cur_Line.QtyOrdered);
259 IF(v_RefReturnQty > v_RefMovementQty) THEN
260 IF(v_Message IS NULL) THEN
261 v_Message:=Cur_line.LINE;
262 ELSE
263 v_Message:=v_Message||', '||Cur_line.LINE;
264 END IF;
265 END IF;
266 END LOOP;
267 IF(v_Message IS NOT NULL) THEN
268 RAISE EXCEPTION '%','@ReturnQtyMismatch@'||v_Message; --OBTG:-20000--
269 END IF;
270 END;
271 END IF;
272
273 --Check whether warehouse belongs to the organization.
274 SELECT count(AD_ORG_ID)
275 INTO v_count
276 FROM AD_Org_Warehouse
277 WHERE M_Warehouse_ID=v_M_Warehouse_ID
278 AND AD_Org_ID = v_Org_ID;
279
280 IF v_count = 0 AND v_IsSOTrx = 'Y' THEN
281 RAISE EXCEPTION '%','@WrongWarehouse@' ; --OBTG:-20000--
282 END IF;
283
284 SELECT AD_Org_ID
285 INTO v_Warehouse_Org
286 FROM M_Warehouse
287 WHERE M_Warehouse_ID = v_M_Warehouse_ID;
288
289 IF(ad_org_isinnaturaltree(v_Warehouse_Org, v_Org_ID, v_Client_ID) = 'N' AND v_isSoTrx = 'N') THEN
290 RAISE EXCEPTION '%','@WrongWarehouse@'; --OBTG:-20000--
291 END IF;
292
293 SELECT CASE WHEN (m.ISSOTRX='Y') THEN customer_blocking ELSE vendor_blocking END ,
294 CASE WHEN (m.ISSOTRX='Y') THEN so_order_blocking ELSE po_order_blocking END, name, DocAction
295 INTO v_bpartner_blocked, v_orderBlocking, v_bpartner_name, v_DocAction
296 FROM C_ORDER m, C_BPartner bp
297 WHERE m.c_bpartner_id=bp.c_bpartner_id
298 AND m.C_ORDER_ID=v_Record_ID
299 AND m.C_BPARTNER_ID=v_c_Bpartner_ID;
300 IF (v_DocAction = 'CO' AND v_bpartner_blocked = 'Y' AND v_orderBlocking = 'Y' AND v_isreturndoctype='N' ) THEN
301 RAISE EXCEPTION '%','@ThebusinessPartner@'||' '|| v_bpartner_name ||' '||'@BusinessPartnerBlocked@'; --OBTG:-20000--
302 END IF;
303 -- Get the name of the org of the Order. Added by P.Sarobe
304 SELECT name INTO v_Org_Name FROM AD_ORG WHERE ad_org_id = v_Org_ID;
305 RAISE NOTICE '%','DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus || ', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID || ', DocSubTypeSO=' || v_DocSubTypeSO ;
306
307 SELECT count(*) INTO v_count
308 FROM dual
309 WHERE EXISTS (
310 SELECT 1
311 FROM c_orderline ol JOIN m_product p ON ol.m_product_id = p.m_product_id
312 WHERE ol.c_order_id = v_record_id
313 AND p.isgeneric = 'Y');
314 IF (v_count > 0) THEN
315 SELECT max(p.name) INTO v_productname
316 FROM c_orderline ol JOIN m_product p ON ol.m_product_id = p.m_product_id
317 WHERE ol.c_order_id = v_record_id
318 AND p.isgeneric = 'Y';
319 RAISE EXCEPTION '%', '@CannotUseGenericProduct@ ' || v_productName; --OBTG:-20000--
320 END IF;
321
322 /**
323 * Quotations
324 */
325 IF (v_DocSubTypeSOTarget = 'OB'AND v_DocAction = 'RJ') THEN
326 SELECT c_reject_reason_id
327 INTO v_reject_reason
328 FROM C_ORDER
329 WHERE C_Order_ID=v_Record_ID;
330 IF (v_reject_reason IS NULL) THEN
331 RAISE EXCEPTION '%', '@NoRejectReason@' ; --OBTG:-20000--
332 END IF;
333 /*
334 * Undo inventory reservation
335 */
336 BEGIN
337 v_ResultStr:='ReserveInventory';
338 -- Set reserved quantity to 0
339 UPDATE C_ORDERLINE
340 SET QtyReserved = 0,
341 Updated=TO_DATE(NOW()),
342 UpdatedBy=v_User
343 WHERE c_orderline_id IN (select c_orderline_id
344 from c_orderline
345 where c_order_id = v_Record_id);
346 END;
347 UPDATE C_ORDER
348 SET DocStatus='CJ',
349 DocAction='--',
350 Processed='Y',
351 Updated=TO_DATE(NOW()),
352 UpdatedBy=v_User
353 WHERE C_Order_ID=v_Record_ID;
354 IF (p_PInstance_ID IS NOT NULL) THEN
355 -- Update AD_PInstance
356 RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message ;
357 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
358 END IF;
359 RETURN;
360 END IF;
361
362 /**
363 * Check if order has lines
364 */
365 IF (v_DocAction = 'CO' OR v_DocAction = 'PR') THEN
366 SELECT COUNT(*)
367 INTO v_Aux
368 FROM C_ORDERLINE
369 WHERE C_ORDER_ID = v_Record_ID;
370 IF (v_Aux = 0) THEN
371 RAISE EXCEPTION '%', '@OrderWithoutLines@'; --OBTG:-20000--
372 END IF;
373 END IF;
374
375 /**
376 * Check if already processed / reactivated
377 */
378 IF (v_DocAction = 'CO' OR v_DocAction = 'PR') THEN
379 IF (v_IsProcessed = 'Y') THEN
380 RAISE EXCEPTION '%','@AlreadyPosted@'; --OBTG:-20000--
381 END IF;
382 ELSIF (v_DocAction='RE') THEN
383 IF (v_IsProcessed = 'N') THEN
384 RAISE EXCEPTION '%','@ActionNotSupported@'; --OBTG:-20000--
385 END IF;
386 END IF;
387
388 -- Check the cash vat flag for all the taxes matches the order one
389 IF (v_DocAction IN ('CO', 'PR')) THEN
390 select count(1)
391 into v_Aux
392 from c_ordertax ot inner join c_tax t on (ot.c_tax_id = t.c_tax_id)
393 where ot.c_order_id = v_Record_ID
394 and t.iswithholdingtax = 'N'
395 and t.rate <> 0
396 and t.IsCashVat <> v_iscashvat;
397
398 IF (v_Aux > 0) THEN
399 RAISE EXCEPTION '%', '@CashVATNotMatch@'; --OBTG:-20000--
400 END IF;
401 END IF;
402
403 /**
404 * Order Closed, Voided or Reversed - No action possible
405 */
406 IF(v_DocStatus IN('CL', 'VO', 'RE')) THEN
407 RAISE EXCEPTION '%', '@AlreadyPosted@' ; --OBTG:-20000--
408 END IF;
409
410 /**
411 * Waiting on Prepayment can only be closed
412 */
413 IF(v_DocStatus='WP' AND v_DocAction<>'CL') THEN
414 RAISE EXCEPTION '%', '@WaitingPayment@' ; --OBTG:-20000--
415 END IF;
416
417 IF (v_DocSubTypeSOTarget='PR' AND v_invoicerule <> 'I') THEN
418 RAISE EXCEPTION '%', '@PrepayMustImmediate@'; --OBTG:-20000--
419 END IF;
420 /**
421 * Unlock
422 */
423 IF(v_DocAction='XL') THEN
424 UPDATE C_ORDER
425 SET Processing='N',
426 DocAction='--',
427 Updated=TO_DATE(NOW()),
428 UpdatedBy=v_User
429 WHERE C_Order_ID=v_Record_ID;
430 FINISH_PROCESS:=TRUE;
431 END IF;
432 IF(NOT FINISH_PROCESS) THEN
433 IF(v_IsProcessing='Y') THEN
434 RAISE EXCEPTION '%', '@OtherProcessActive@' ; --OBTG:-20000--
435 END IF;
436
437 /**
438 * Re-activate
439 */
440 IF (v_DocAction='RE') THEN
441 IF (v_DocSubTypeSO IN ('WR', 'WI')) THEN
442 RAISE EXCEPTION '%', '@ActionNotSupported@'; --OBTG:-20000--
443 END IF;
444 --Verify not managed debtPayments added by ALO
445 --Added by P.Sarobe. New messages
446 SELECT max(c_debt_payment_id), COUNT(*)
447 INTO v_Debtpayment_ID, v_Aux
448 FROM C_DEBT_PAYMENT
449 WHERE C_Order_ID=v_Record_ID
450 AND C_Debt_Payment_Status(C_Settlement_Cancel_ID, Cancel_Processed, Generate_Processed, IsPaid, IsValid, C_CashLine_ID, C_BankStatementLine_ID)!='P';
451 IF (v_Aux != 0) THEN
452 --Added by P.Sarobe. New messages
453 SELECT c_Bankstatementline_Id, c_cashline_id, c_settlement_cancel_id, ispaid, cancel_processed
454 INTO v_Bankstatementline_ID, v_CashLine_ID, v_Settlement_Cancel_ID, v_ispaid, v_Cancel_Processed
455 FROM C_DEBT_PAYMENT WHERE C_Debt_Payment_ID = v_Debtpayment_ID;
456
457 IF (v_Bankstatementline_ID IS NOT NULL) THEN
458 SELECT C_BANKSTATEMENT.NAME, C_BANKSTATEMENT.STATEMENTDATE
459 INTO v_nameBankstatement, v_dateBankstatement
460 FROM C_BANKSTATEMENT, C_BANKSTATEMENTLINE
461 WHERE C_BANKSTATEMENT.C_BANKSTATEMENT_ID = C_BANKSTATEMENTLINE.C_BANKSTATEMENT_ID
462 AND C_BANKSTATEMENTLINE.C_BANKSTATEMENTLINE_ID = v_Bankstatementline_ID;
463 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderBank@'||v_nameBankstatement||' '||'@Bydate@'||v_dateBankstatement ; --OBTG:-20000--
464 END IF;
465 IF (v_CashLine_ID IS NOT NULL) THEN
466 SELECT C_CASH.NAME, C_CASH.STATEMENTDATE
467 INTO v_nameCash, v_dateCash
468 FROM C_CASH, C_CASHLINE
469 WHERE C_CASH.C_CASH_ID = C_CASHLINE.C_CASH_ID
470 AND C_CASHLINE.C_CASHLINE_ID = v_CashLine_ID;
471 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderCash@'||v_nameCash||' '||'@Bydate@'||v_dateCash ; --OBTG:-20000--
472 END IF;
473 IF (v_Cancel_Processed='Y' AND v_ispaid='N') THEN
474 SELECT documentno, datetrx
475 INTO v_documentno_Settlement, v_dateSettlement
476 FROM C_SETTLEMENT
477 WHERE C_SETTLEMENT_ID = v_Settlement_Cancel_ID;
478 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderCancel@'||v_documentno_Settlement||' '||'@Bydate@'||v_dateSettlement ; --OBTG:-20000--
479 END IF;
480 END IF;
481
482 RAISE NOTICE '%','Re-Activating ' || v_DocSubTypeSO || ': ' || v_Record_ID ;
483 IF(v_DocSubTypeSO IN ('WI', 'WP', 'WR')) THEN
484 -- Cancel existing Deli very + Invoice Documents
485 PERFORM M_INOUT_CANCEL(NULL, v_Record_ID) ;
486 IF (v_DocSubTypeSO<>'WP') THEN
487 PERFORM C_INVOICE_CANCEL(NULL, v_Record_ID);
488 END IF;
489 END IF;
490 -- Update Order
491 v_ResultStr:='ReActivate';
492 UPDATE C_ORDER
493 SET DocStatus='IP', -- In Progress
494 DocAction='CO',
495 Processing='N',
496 Processed='N',
497 Updated=TO_DATE(NOW()),
498 UpdatedBy=v_User
499 WHERE C_Order_ID=v_Record_ID;
500 UPDATE M_INOUTLINE SET C_ORDERLINE_ID = NULL
501 WHERE (SELECT DISTINCT A.DOCSTATUS FROM M_INOUT A, M_INOUTLINE B, C_ORDERLINE C
502 WHERE A.M_INOUT_ID = B.M_INOUT_ID AND B.C_ORDERLINE_ID = C.C_ORDERLINE_ID
503 AND C.C_ORDER_DISCOUNT_ID IS NOT NULL AND C.C_ORDER_ID = v_Record_ID) = 'VO'
504 AND C_ORDERLINE_ID IN (SELECT C_ORDERLINE_ID FROM C_ORDERLINE WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
505 AND C_ORDER_ID = v_Record_ID);
506 DELETE
507 FROM C_ORDERLINETAX
508 WHERE EXISTS (SELECT 1
509 FROM C_ORDERLINE
510 WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
511 AND C_ORDER_ID = v_Record_ID
512 AND C_ORDERLINE.C_ORDERLINE_ID = C_ORDERLINETAX.C_ORDERLINE_ID);
513 DELETE
514 FROM C_ORDERLINE
515 WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
516 AND C_ORDER_ID=v_Record_ID;
517 --ADDED BY E.ARGAL
518 --Invalidate debt payments added by ALO
519 UPDATE C_DEBT_PAYMENT
520 SET IsValid='N',
521 Updated=TO_DATE(NOW()),
522 UpdatedBy=v_User
523 WHERE C_Order_ID=v_Record_ID
524 AND IsAutomaticGenerated='N';
525
526 DELETE
527 FROM C_CASHLINE
528 WHERE C_Debt_Payment_Id IN
529 (SELECT C_DEBT_PAYMENT_ID
530 FROM C_DEBT_PAYMENT
531 WHERE C_Order_ID=v_Record_ID
532 AND COALESCE(IsAutomaticGenerated, 'Y')='Y'
533 );
534 DELETE
535 FROM C_DEBT_PAYMENT
536 WHERE C_Order_ID=v_Record_ID
537 AND COALESCE(IsAutomaticGenerated, 'Y')='Y';
538 /*
539 * Undo inventory reservation
540 */
541 DECLARE
542 Cur_ResLine RECORD;
543 v_QtySO NUMERIC; -- Reserved
544 v_QtyOrderSO NUMERIC;
545 v_QtyPO NUMERIC; -- Ordered
546 v_QtyOrderPO NUMERIC;
547 v_UOM_ID VARCHAR(32); --OBTG:VARCHAR2--
548 BEGIN
549 v_ResultStr:='ReserveInventory';
550 -- For all lines needing reservation
551 FOR Cur_ResLine IN
552 (SELECT l.M_Warehouse_ID, l.M_Product_ID, l.M_AttributeSetInstance_ID, l.C_OrderLine_ID,
553 l.QtyOrdered AS Qty, l.QUANTITYORDER, l.qtyreserved, l.qtydelivered,
554 l.C_UOM_ID, l.M_PRODUCT_UOM_ID, l.C_AUM
555 FROM C_ORDERLINE l, M_PRODUCT p
556 WHERE l.C_Order_ID=v_Record_ID -- Reserve Products (not: services, null products)
557 AND l.M_Product_ID=p.M_Product_ID
558 AND p.IsStocked='Y'
559 AND p.ProductType='I'
560 )
561 LOOP
562 -- Qty corrected for SO/PO
563 IF (v_DocSubTypeSO IS NULL) THEN
564 v_QtySO:=0;
565 v_QtyOrderSO:=NULL;
566 v_QtyPO:=Cur_ResLine.qtydelivered-Cur_ResLine.qty;
567 v_QtyOrderPO:=NULL;
568 IF (Cur_ResLine.QtyDelivered=0) THEN
569 v_QtyOrderPO := -Cur_ResLine.QuantityOrder;
570 ELSIF Cur_ResLine.C_AUM IS NULL AND Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
571 SELECT c_uom_id
572 INTO v_UOM_ID
573 FROM m_product_uom
574 WHERE m_product_uom_id = Cur_ResLine.M_Product_UOM_ID;
575 v_QtyOrderPO := -C_Uom_Convert(v_QtyPO, Cur_ResLine.C_UOM_ID, v_UOM_ID, 'Y');
576 END IF;
577 ELSE
578 v_QtySO:=-Cur_ResLine.QtyReserved;
579 IF (Cur_ResLine.QtyReserved=Cur_ResLine.Qty) THEN
580 v_QtyOrderSO := -Cur_ResLine.QuantityOrder;
581 ELSIF Cur_ResLine.C_AUM IS NULL AND Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
582 SELECT c_uom_id
583 INTO v_UOM_ID
584 FROM m_product_uom
585 WHERE m_product_uom_id = Cur_ResLine.M_Product_UOM_ID;
586 v_QtyOrderSO := -C_Uom_Convert(v_QtySO, Cur_ResLine.C_UOM_ID, v_UOM_ID, 'Y');
587 END IF;
588 v_QtyPO:=0;
589 v_QtyOrderPO:=NULL;
590 END IF;
591 IF ((v_DocStatus<>'IP' OR v_DocAction<>'CO') AND COALESCE(v_DocSubTypeSO, '')<>'OB') THEN
592 PERFORM M_UPDATE_STORAGE_PENDING(v_Client_ID, v_Org_ID, v_UpdatedBy, Cur_ResLine.M_Product_ID, Cur_ResLine.M_Warehouse_ID, Cur_ResLine.M_AttributeSetInstance_ID, Cur_ResLine.C_UOM_ID, Cur_ResLine.M_PRODUCT_UOM_ID, v_QtySO, v_QtyOrderSO, v_QtyPO, v_QtyOrderPO) ;
593 END IF;
594 END LOOP;
595 -- Set reserved quantity to 0
596 UPDATE C_ORDERLINE
597 SET QtyReserved = 0,
598 Updated=TO_DATE(NOW()),
599 UpdatedBy=v_User
600 WHERE c_orderline_id IN (select c_orderline_id
601 from c_orderline
602 where c_order_id = v_Record_id);
603 END;
604 /**
605 * Manage Stock Reservations
606 */
607 SELECT COUNT(1) INTO v_count
608 FROM ad_preference
609 WHERE property = 'StockReservations';
610 IF (v_count > 1) THEN
611 v_dummy := AD_GET_PREFERENCE_VALUE('StockReservations', 'Y', v_client_id, v_org_id, NULL, NULL, NULL);
612 ELSIF (v_count = 1) THEN
613 UPDATE c_orderline
614 SET so_res_status = NULL
615 WHERE c_order_id = v_Record_id;
616 UPDATE c_order
617 SET so_res_status = NULL
618 WHERE c_order_id = v_record_id;
619 END IF;
620
621 --ADDED BY P.SAROBE but to be deprecated 26052007
622 SELECT MAX(C_CASHLINE_ID)
623 INTO v_CashLine_ID
624 FROM C_CASHLINE
625 WHERE C_ORDER_ID=v_Record_ID;
626 IF (v_CashLine_ID IS NOT NULL) THEN
627 SELECT PROCESSED
628 INTO v_IsProcessed
629 FROM C_CASH, C_CASHLINE
630 WHERE C_CASH.C_CASH_ID=C_CASHLINE.C_CASH_ID
631 AND C_CASHLINE_ID=v_CashLine_ID;
632 IF (v_IsProcessed='N') THEN
633 DELETE FROM C_CASHLINE WHERE C_CASHLINE_ID=v_CashLine_ID;
634 ELSE
635 SELECT C_CASH.NAME, C_CASH.STATEMENTDATE, C_CASHLINE.LINE
636 INTO v_nameCash, v_dateCash, v_Line
637 FROM C_CASH, C_CASHLINE
638 WHERE C_CASH.C_CASH_ID = C_CASHLINE.C_CASH_ID
639 AND C_CASHLINE.C_CASHLINE_ID = v_CashLine_ID;
640 RAISE EXCEPTION '%', '@Ordercahslineprocessed@'||v_nameCash||' '||'@Bydate@'||v_dateCash||' '||'@line@'||v_Line ; --OBTG:-20000--
641 END IF;
642 END IF;
643
644 UPDATE C_ORDER
645 SET DocStatus='DR', -- Draft
646 DocAction='CO',
647 Processing='N',
648 Updated=TO_DATE(NOW()),
649 UpdatedBy=v_User
650 WHERE C_Order_ID=v_Record_ID;
651
652 FINISH_PROCESS:=TRUE;
653 END IF;
654 END IF;--FINISH_PROCESS
655 IF (NOT FINISH_PROCESS) THEN
656 SELECT COUNT(*)
657 INTO v_Count
658 FROM C_ORDER C, C_DOCTYPE
659 WHERE C_DocType.DocBaseType IN ('SOO', 'POO')
660 AND C_DocType.IsSOTrx=C.ISSOTRX
661 AND AD_ISORGINCLUDED(C.AD_Org_ID,C_DocType.AD_Org_ID, C.AD_Client_ID) <> -1
662 AND C.C_DOCTYPETARGET_ID = C_DOCTYPE.C_DOCTYPE_ID
663 AND C.C_ORDER_ID = v_Record_ID;
664 IF (v_Count=0) THEN
665 RAISE EXCEPTION '%', '@NotCorrectOrgDoctypeOrder@' ; --OBTG:-20000--
666 END IF;
667
668 SELECT COUNT(*)
669 INTO v_Count
670 FROM C_ORDER C, C_ORDERLINE OL
671 WHERE C.C_ORDER_ID = OL.C_ORDER_ID
672 AND AD_ISORGINCLUDED(OL.AD_Org_ID, C.AD_Org_ID, C.AD_Client_ID) = -1
673 AND C.C_ORDER_ID = v_Record_ID;
674 IF (v_Count>0) THEN
675 RAISE EXCEPTION '%', '@NotCorrectOrgLines@' ; --OBTG:-20000--
676 END IF;
677
678
679 /**
680 * Close Order - prepare
681 */
682 DECLARE
683 Cur_Inventory RECORD;
684 v_QtyOrdered NUMERIC;
685 v_QtyAum NUMERIC;
686 v_QuantityOrder NUMERIC;
687 v_linenetamt NUMERIC;
688 v_linegrossamt NUMERIC;
689 v_ProductUOM M_PRODUCT_UOM.C_UOM_ID%TYPE;
690 BEGIN
691
692 -- When closing the order it calculates the difference between the ordered and received/delivered quantities ant it
693 -- updates the m_storage_pending.
694 IF (v_DocAction='CL') THEN
695 -- Cancel undelivered Items
696 IF (v_isSoTrx='Y') THEN --Sales orders
697 FOR Cur_Inventory IN (
698 SELECT QtyInvoiced, QtyDelivered ,QtyOrdered, QuantityOrder, priceactual, gross_unit_price,
699
700 C_ORDERLINE_ID AS ID,
701 M_Product_ID,
702 M_Warehouse_ID,
703 M_AttributeSetInstance_ID,
704 C_UOM_ID,
705 C_AUM,
706 M_PRODUCT_UOM_ID,
707 C_Currency_ID
708 FROM C_ORDERLINE
709 WHERE C_Order_ID=v_Record_ID
710 AND QtyOrdered <> (SELECT (CASE WHEN (qtyinvoiced = 0) THEN QtyDelivered ELSE
711 (CASE WHEN (QtyDelivered = 0) THEN qtyinvoiced ELSE
712 (CASE WHEN (QtyDelivered < 0 AND qtyinvoiced < 0) THEN LEAST(QtyDelivered, qtyinvoiced) ELSE GREATEST(QtyDelivered, qtyinvoiced) END) END) END)
713 FROM C_ORDERLINE COL
714 WHERE COL.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
715 AND m_product_id IS NOT NULL
716 )
717 LOOP
718 v_QtyOrdered := CASE WHEN (Cur_Inventory.QtyDelivered < 0) THEN LEAST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) ELSE GREATEST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) END;
719 v_QtyAum := M_GET_CONVERTED_AUMQTY(Cur_Inventory.M_Product_ID, v_QtyOrdered, Cur_Inventory.C_AUM);
720 v_linenetamt := ROUND(v_QtyOrdered * Cur_Inventory.priceactual, C_GET_CURRENCY_PRECISION(Cur_Inventory.C_Currency_ID, 'A'));
721 v_linegrossamt := ROUND(v_QtyOrdered * Cur_Inventory.gross_unit_price, C_GET_CURRENCY_PRECISION(Cur_Inventory.C_Currency_ID, 'A'));
722 SELECT MAX(UOM.C_UOM_ID)
723 INTO v_ProductUOM
724 FROM M_PRODUCT_UOM UOM
725 WHERE UOM.M_PRODUCT_UOM_ID=Cur_Inventory.M_PRODUCT_UOM_ID;
726 v_QuantityOrder := CASE WHEN Cur_Inventory.C_AUM IS NOT NULL OR (Cur_Inventory.C_AUM IS NULL AND Cur_Inventory.M_PRODUCT_UOM_ID IS NULL) THEN Cur_Inventory.QuantityOrder
727 ELSE (c_uom_convert((CASE WHEN (Cur_Inventory.QtyDelivered < 0) THEN LEAST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) ELSE GREATEST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) END),
728 Cur_Inventory.C_UOM_ID, v_ProductUOM,'Y')) END;
729
730 IF (Cur_Inventory.QtyOrdered <> v_QtyOrdered) THEN
731 PERFORM M_UPDATE_STORAGE_PENDING(v_Client_ID, v_Org_ID, v_UpdatedBy, Cur_Inventory.M_Product_ID, Cur_Inventory.M_Warehouse_ID, Cur_Inventory.M_AttributeSetInstance_ID,
732 Cur_Inventory.C_UOM_ID, Cur_Inventory.M_PRODUCT_UOM_ID, -(Cur_Inventory.QtyOrdered - v_QtyOrdered), -(Cur_Inventory.QuantityOrder - v_QuantityOrder), 0, null);
733 END IF;
734
735 -- UPDATE C_ORDERLINE
736 UPDATE C_ORDERLINE
737 SET QtyOrdered=v_QtyOrdered,
738 AumQty = v_QtyAum,
739 linenetamt=v_linenetamt,
740 line_gross_amount=v_linegrossamt,
741 QuantityOrder=v_QuantityOrder,
742 Updated=TO_DATE(NOW())
743 WHERE C_ORDERLINE_ID = Cur_Inventory.ID;
744 END LOOP;
745 -- For Purchase orders, M_MatchPO table used. Notice that only delivered lines(C_Invoiceline_Id is null) using
746 ELSE
747 FOR Cur_Inventory IN (
748 SELECT
749 COALESCE((SELECT SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)
750 FROM M_MATCHPO
751 WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID), 0) AS QtyDelivered,
752 COALESCE((SELECT SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)
753 FROM M_MATCHPO
754 WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID), 0) AS QtyInvoiced,
755 QtyOrdered,
756 QuantityOrder,
757 priceactual,
758 gross_unit_price,
759 linenetamt,
760 C_ORDERLINE_ID AS ID,
761 M_Product_ID,
762 M_Warehouse_ID,
763 M_AttributeSetInstance_ID,
764 C_UOM_ID,
765 C_AUM,
766 M_PRODUCT_UOM_ID,
767 C_Currency_ID
768 FROM C_ORDERLINE
769 WHERE C_ORDERLINE.C_ORDER_ID=v_Record_ID
770 AND qtyordered <> COALESCE((
771 SELECT (CASE WHEN (SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END) = 0)
772 THEN SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END) ELSE
773 (CASE WHEN (SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END) = 0)
774 THEN SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)
775 ELSE (CASE WHEN (SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END) < 0 AND SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END) < 0)
776 THEN LEAST(SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END),SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END))
777 ELSE GREATEST(SUM(CASE WHEN M_MATCHPO.M_INOUTLINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END),SUM(CASE WHEN M_MATCHPO.C_INVOICELINE_ID IS NULL THEN M_MATCHPO.QTY ELSE 0 END)) END)
778 END)
779 END)
780 FROM M_MATCHPO
781 WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID
782 ), 0)
783 )
784 LOOP
785 v_QtyOrdered := COALESCE(CASE WHEN (Cur_Inventory.QtyDelivered < 0) THEN LEAST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) ELSE CASE WHEN (Cur_Inventory.QtyDelivered = 0) THEN Cur_Inventory.QtyInvoiced ELSE GREATEST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) END END, 0);
786 v_QtyAum := M_GET_CONVERTED_AUMQTY(Cur_Inventory.M_Product_ID, v_QtyOrdered, Cur_Inventory.C_AUM);
787 v_linenetamt := ROUND(COALESCE(v_QtyOrdered, 0) * Cur_Inventory.priceactual, C_GET_CURRENCY_PRECISION(Cur_Inventory.C_Currency_ID, 'A'));
788 v_linegrossamt := ROUND(COALESCE(v_QtyOrdered, 0) * Cur_Inventory.gross_unit_price, C_GET_CURRENCY_PRECISION(Cur_Inventory.C_Currency_ID, 'A'));
789
790 SELECT MAX(UOM.C_UOM_ID)
791 INTO v_ProductUOM
792 FROM M_PRODUCT_UOM UOM
793 WHERE UOM.M_PRODUCT_UOM_ID=Cur_Inventory.M_PRODUCT_UOM_ID;
794 v_QuantityOrder := CASE WHEN Cur_Inventory.C_AUM IS NOT NULL OR (Cur_Inventory.C_AUM IS NULL AND Cur_Inventory.M_PRODUCT_UOM_ID IS NULL) THEN Cur_Inventory.QuantityOrder
795 ELSE (COALESCE(c_uom_convert((CASE WHEN (Cur_Inventory.QtyDelivered < 0) THEN LEAST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) ELSE GREATEST(Cur_Inventory.QtyDelivered, Cur_Inventory.QtyInvoiced) END),
796 Cur_Inventory.C_UOM_ID, v_ProductUOM,'Y'), 0)) END;
797 IF (Cur_Inventory.QtyOrdered <> v_QtyOrdered) THEN
798 PERFORM M_UPDATE_STORAGE_PENDING(v_Client_ID, v_Org_ID, v_UpdatedBy, Cur_Inventory.M_Product_ID, Cur_Inventory.M_Warehouse_ID, Cur_Inventory.M_AttributeSetInstance_ID,
799 Cur_Inventory.C_UOM_ID, Cur_Inventory.M_PRODUCT_UOM_ID, 0, null, -(Cur_Inventory.QtyOrdered - v_QtyOrdered), -(Cur_Inventory.QuantityOrder - v_QuantityOrder));
800 END IF;
801
802 -- UPDATE C_ORDERLINE
803 UPDATE C_ORDERLINE
804 SET QtyOrdered=v_QtyOrdered,
805 AumQty = v_QtyAum,
806 linenetamt=v_linenetamt,
807 line_gross_amount=v_linegrossamt,
808 QuantityOrder=v_QuantityOrder,
809 Updated=TO_DATE(NOW())
810 WHERE C_ORDERLINE_ID = Cur_Inventory.ID;
811 END LOOP;
812 END IF;
813 -- if there is no change, the tax calculation, etc. is not needed.
814 -- potential problem, if posted (i.e. encumbered) for full amount
815 -- and the rest then cancelled out.
816 END IF;
817 END;
818
819 /**
820 *Update Product purchasing Plan Table
821 * Return Material orders do not update the last price.
822 */
823 IF (v_isSoTrx ='N' AND v_isreturndoctype = 'N') THEN
824 FOR Cur_OrderLine IN (SELECT * FROM C_ORDERLINE WHERE C_Order_Id = v_Record_ID)
825 LOOP
826 UPDATE M_PRODUCT_PO SET PriceLastPO=Cur_OrderLine.PriceActual
827 Where C_BPARTNER_ID = v_CBPartner_ID AND M_PRODUCT_ID = Cur_OrderLine.M_PRODUCT_ID
828 AND Ad_Isorgincluded(Cur_OrderLine.AD_ORG_ID,AD_ORG_ID, Cur_OrderLine.AD_Client_ID) <> -1;
829 END LOOP;
830 END IF;
831 /**
832 * Void Order - prepare
833 */
834 IF (v_DocAction='VO') THEN
835 -- Cancel all Items
836 UPDATE C_ORDERLINE
837 SET QtyOrdered=0,
838 --MODIFIED BY F.IRIAZABAL
839 QuantityOrder = CASE WHEN C_AUM IS NOT NULL OR (C_AUM IS NULL AND M_PRODUCT_UOM_ID IS NULL) THEN NULL ELSE 0 END,
840 LineNetAmt=0,
841 Updated=TO_DATE(NOW())
842 WHERE C_Order_ID=v_Record_ID
843 AND QtyOrdered<>0;
844 END IF;
845
846 /**************************************************************************
847 * Start Processing ------------------------------------------------------
848 *************************************************************************/
849 -- Check the header belongs to a organization where transactions are posible and ready to use
850 SELECT AD_Org.IsReady, Ad_OrgType.IsTransactionsAllowed
851 INTO v_is_ready, v_is_tr_allow
852 FROM C_ORDER, AD_Org, AD_OrgType
853 WHERE AD_Org.AD_Org_ID=C_ORDER.AD_Org_ID
854 AND AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
855 AND C_ORDER.C_ORDER_ID=v_Record_ID;
856 IF (v_is_ready='N') THEN
857 RAISE EXCEPTION '%', '@OrgHeaderNotReady@'; --OBTG:-20000--
858 END IF;
859 IF (v_is_tr_allow='N') THEN
860 RAISE EXCEPTION '%', '@OrgHeaderNotTransAllowed@'; --OBTG:-20000--
861 END IF;
862 SELECT AD_ORG_CHK_DOCUMENTS('C_ORDER', 'C_ORDERLINE', v_Record_ID, 'C_ORDER_ID', 'C_ORDER_ID') INTO v_is_included FROM dual;
863 IF (v_is_included=-1) THEN
864 RAISE EXCEPTION '%', '@LinesAndHeaderDifferentLEorBU@'; --OBTG:-20000--
865 END IF;
866
867 IF (p_PInstance_ID IS NOT NULL) THEN
868 v_ResultStr:='LockingOrder';
869 UPDATE C_ORDER SET Processing='Y' WHERE C_Order_ID=v_Record_ID;
870 -- COMMIT;
871 -- Now, needs to go to END_PROCESSING to unlock
872 END IF;
873 /**
874 * Allowed Actions: AProve, COmplete, PRocess, CLose, VOid
875 */
876 IF (v_DocAction IN('AP', 'CO', 'PR', 'CL', 'VO')) THEN
877 NULL;
878 ELSE
879 RAISE EXCEPTION '%', '@ActionNotAllowedHere@' ; --OBTG:-20000--
880 END IF;
881
882 SELECT COUNT(*)
883 INTO v_count
884 FROM AD_CLIENTINFO
885 WHERE AD_CLIENT_ID=v_Client_ID
886 AND CHECKORDERORG='Y';
887 IF (v_count > 0) THEN
888 v_ResultStr:='CheckingRestrictions - C_ORDER ORG IS IN C_BPARTNER ORG TREE';
889 SELECT COUNT(*)
890 INTO v_count
891 FROM C_ORDER c, C_BPARTNER bp
892 WHERE c.C_Order_ID=v_Record_ID
893 AND c.C_BPARTNER_ID=bp.C_BPARTNER_ID
894 AND Ad_Isorgincluded(c.AD_ORG_ID, bp.AD_ORG_ID, bp.AD_CLIENT_ID)=-1;
895 IF (v_count > 0) THEN
896 RAISE EXCEPTION '%', '@NotCorrectOrgBpartnerOrder@' ; --OBTG:-20000--
897 END IF;
898 END IF;
899
900 /**************************************************************************
901 * Calculate promotions
902 *************************************************************************/
903 IF (v_DocAction = 'CO' AND v_isreturndoctype = 'N' AND v_recalculateDiscounts = 'Y') THEN
904 PERFORM M_PROMOTION_CALCULATE('O', v_Record_ID, v_User);
905 END IF;
906
907 /**************************************************************************
908 * Calculate Discounts
909 *************************************************************************/
910
911 -- if sales order was created from quotation with "firm quotation" check
912 -- then discounts are not recalculated
913 IF (v_recalculateDiscounts = 'Y') THEN
914 -- Delete first previous discounts (if possible) and then recalculate them
915 UPDATE C_ORDER
916 SET DocStatus='IP', -- In progress
917 Processing='N',
918 Processed='N',
919 Updated=TO_DATE(NOW()),
920 UpdatedBy=v_User
921 WHERE C_Order_ID=v_Record_ID;
922
923 DELETE
924 FROM C_ORDERLINE
925 WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
926 AND C_ORDER_ID = v_Record_ID
927 AND NOT EXISTS (SELECT C_INVOICELINE_ID FROM C_INVOICELINE WHERE C_INVOICELINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
928 AND NOT EXISTS (SELECT M_INOUTLINE_ID FROM M_INOUTLINE WHERE M_INOUTLINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
929 AND NOT EXISTS (SELECT M_MATCHPO_ID FROM M_MATCHPO WHERE M_MATCHPO.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID);
930
931 UPDATE C_ORDERLINE
932 SET pricelist = 0, priceactual = 0, pricelimit = 0, linenetamt = 0, pricestd = 0
933 WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
934 AND C_ORDER_ID=v_Record_ID;
935
936 UPDATE C_ORDER
937 SET DocStatus=v_DocStatus, -- restore
938 Processing=v_IsProcessing,
939 Processed=v_IsProcessed,
940 Updated=TO_DATE(NOW()),
941 UpdatedBy=v_User
942 WHERE C_Order_ID=v_Record_ID;
943
944 v_CumDiscount:=0;
945 v_OldCumDiscount:=0;
946 v_Line:=10;
947 SELECT MAX(LINE)
948 INTO v_OrderLineSeqNo
949 FROM C_ORDERLINE
950 WHERE C_ORDER_ID=v_Record_ID;
951 FOR Cur_COrderDiscount IN
952 (SELECT C_ORDER_DISCOUNT.C_ORDER_DISCOUNT_ID, C_DISCOUNT.DISCOUNT, C_DISCOUNT.M_PRODUCT_ID, C_DISCOUNT.NAME,
953 C_ORDER_DISCOUNT.CASCADE, C_DISCOUNT.C_DISCOUNT_ID, M_PRODUCT.C_UOM_ID
954 FROM C_ORDER_DISCOUNT, C_DISCOUNT, M_PRODUCT
955 WHERE C_ORDER_DISCOUNT.C_DISCOUNT_ID=C_DISCOUNT.C_DISCOUNT_ID
956 AND C_DISCOUNT.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
957 AND C_ORDER_DISCOUNT.C_ORDER_ID=v_Record_ID
958 AND C_ORDER_DISCOUNT.ISACTIVE='Y'
959 AND C_DISCOUNT.ISACTIVE='Y'
960 ORDER BY C_ORDER_DISCOUNT.LINE
961 )
962 LOOP
963 v_CumDiscount:=(1-v_OldCumDiscount) * Cur_COrderDiscount.Discount/100;
964 v_OldCumDiscount:=v_OldCumDiscount + v_CumDiscount;
965 FOR Cur_TaxDiscount IN
966 (SELECT C_ORDERLINE.C_TAX_ID,
967 SUM(C_ORDERLINE.LINENETAMT) AS LINENETAMT,
968 SUM(C_ORDERLINE.LINE_GROSS_AMOUNT) AS LINEGROSSAMT
969 FROM C_ORDERLINE
970 WHERE C_ORDER_ID=v_Record_ID
971 AND C_ORDERLINE.LINENETAMT<>0
972 AND C_ORDER_DISCOUNT_ID IS NULL
973 GROUP BY C_TAX_ID
974 )
975 LOOP
976 IF (v_istaxincluded = 'Y') THEN
977 IF (Cur_COrderDiscount.CASCADE='Y') THEN
978 v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * v_CumDiscount;
979 ELSE
980 v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * Cur_COrderDiscount.Discount/100;
981 END IF;
982 v_Discount:= C_GET_NET_AMOUNT_FROM_GROSS(Cur_TaxDiscount.C_TAX_ID, v_line_gross_amount, 0, v_stdPrecision);
983 v_gross_unit_price:= v_line_gross_amount;
984 ELSE
985 IF (Cur_COrderDiscount.CASCADE='Y') THEN
986 v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * v_CumDiscount;
987 ELSE
988 v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * Cur_COrderDiscount.Discount/100;
989 END IF;
990 v_gross_unit_price:= 0;
991 v_line_gross_amount:= 0;
992 END IF;
993
994 SELECT COUNT(*) INTO v_DiscountExist FROM C_ORDERLINE
995 WHERE C_ORDERLINE.C_ORDER_DISCOUNT_ID = Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
996 AND C_ORDERLINE.C_TAX_ID = Cur_TaxDiscount.C_TAX_ID;
997
998 IF (v_DiscountExist = 0) THEN
999 v_OrderLineSeqNo:=10 + v_OrderLineSeqNo;
1000 v_OrderLine:=get_uuid();
1001 INSERT INTO c_orderline
1002 (
1003 c_orderline_id, ad_client_id, ad_org_id, isactive, created, createdby,
1004 updated, updatedby, c_order_id, line, c_bpartner_id, c_bpartner_location_id,
1005 dateordered, datepromised, datedelivered, dateinvoiced, description,
1006 m_product_id, m_warehouse_id, directship, c_uom_id, qtyordered,
1007 qtyreserved, qtydelivered, qtyinvoiced, m_shipper_id, c_currency_id,
1008 pricelist, priceactual, pricelimit, linenetamt, discount, freightamt,
1009 c_charge_id, chargeamt, c_tax_id, s_resourceassignment_id, ref_orderline_id,
1010 m_attributesetinstance_id, isdescription, quantityorder, m_product_uom_id, aumqty, c_aum,
1011 m_offer_id, pricestd, C_ORDER_DISCOUNT_ID,
1012 gross_unit_price, taxbaseamt,line_gross_amount
1013 )
1014 VALUES
1015 (
1016 v_OrderLine, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy,
1017 TO_DATE(NOW()), v_UpdatedBy, v_Record_ID, v_OrderLineSeqNo, NULL, NULL,
1018 TO_DATE(NOW()), TO_DATE(NOW()), TO_DATE(NOW()), TO_DATE(NOW()), Cur_COrderDiscount.NAME,
1019 Cur_COrderDiscount.M_PRODUCT_ID, v_M_Warehouse_ID, 'N', Cur_COrderDiscount.C_UOM_ID, 1,
1020 0, 0, 0, NULL, v_c_currency_id,
1021 ROUND(v_Discount, v_pricePrecision), ROUND(v_Discount, v_pricePrecision), ROUND(v_Discount, v_pricePrecision), ROUND(v_Discount, v_pricePrecision), 0, 0,
1022 NULL, NULL, Cur_TaxDiscount.C_TAX_ID, NULL, NULL,
1023 NULL, 'N', NULL, NULL, NULL, NULL,
1024 NULL, ROUND(v_Discount, v_pricePrecision), NULL,
1025 ROUND(v_gross_unit_price, v_pricePrecision), ROUND(v_Discount, v_pricePrecision),ROUND(v_line_gross_amount, v_pricePrecision)
1026 );
1027
1028 UPDATE C_ORDERLINE
1029 SET C_ORDER_DISCOUNT_ID=Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
1030 WHERE C_ORDERLINE_ID=v_OrderLine;
1031 ELSE
1032 UPDATE C_ORDERLINE
1033 SET pricelist = ROUND(v_Discount, v_pricePrecision), priceactual = ROUND(v_Discount, v_pricePrecision), pricelimit = ROUND(v_Discount, v_pricePrecision), linenetamt = ROUND(v_Discount, v_pricePrecision), pricestd = ROUND(v_Discount, v_pricePrecision)
1034 WHERE C_ORDERLINE.C_ORDER_DISCOUNT_ID = Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
1035 AND C_ORDERLINE.C_TAX_ID = Cur_TaxDiscount.C_TAX_ID;
1036 END IF;
1037 END LOOP;
1038 v_Line:=v_Line + 10;
1039 END LOOP;
1040 END IF;
1041
1042 /**
1043 * Convert to Target DocType
1044 */
1045 DECLARE
1046 v_DocSubTypeSO_Target VARCHAR(60) ;
1047 BEGIN
1048 v_ResultStr:='ConvertingDocType';
1049 IF (v_DocType_ID <> v_DocTypeTarget_ID) THEN
1050 -- New
1051 IF (v_DocStatus='DR' OR v_DocType_ID='0') THEN
1052 -- Update to Target Document Type
1053 WHILE(v_DocType_ID<>v_DocTypeTarget_ID)
1054 LOOP
1055 BEGIN
1056 v_ResultStr:='UpdateDocType';
1057 UPDATE C_ORDER
1058 SET C_DocType_ID=v_DocTypeTarget_ID,
1059 Updated=TO_DATE(NOW()),
1060 UpdatedBy=v_User
1061 WHERE C_Order_ID=v_Record_ID;
1062 v_DocType_ID:=v_DocTypeTarget_ID;
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 v_ResultStr:='UpdateDocumentNo';
1066 UPDATE C_ORDER SET DocumentNo=DocumentNo || '.' WHERE C_Order_ID=v_Record_ID;
1067 END;
1068 END LOOP;
1069 ELSE
1070 v_ResultStr:='GetTargetDocType';
1071 SELECT DocSubTypeSO
1072 INTO v_DocSubTypeSO_Target
1073 FROM C_DOCTYPE
1074 WHERE C_DocType_ID=v_DocTypeTarget_ID;
1075 RAISE NOTICE '%','Changing DocType from ' || v_DocSubTypeSO || ' to ' || v_DocSubTypeSO_Target ;
1076 -- Change Offer to anything, Change InProcess to anything
1077 IF (v_DocSubTypeSO IN('ON', 'OB') OR v_DocStatus='IP') THEN
1078 -- Update to Target Document Type
1079 WHILE(v_DocType_ID<>v_DocTypeTarget_ID)
1080 LOOP
1081 BEGIN
1082 v_ResultStr:='UpdateDocType';
1083 UPDATE C_ORDER
1084 SET C_DocType_ID=v_DocTypeTarget_ID,
1085 Updated=TO_DATE(NOW()),
1086 UpdatedBy=v_User
1087 WHERE C_Order_ID=v_Record_ID;
1088 v_DocType_ID:=v_DocTypeTarget_ID;
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 v_ResultStr:='UpdateDocumentNo';
1092 UPDATE C_ORDER SET DocumentNo=DocumentNo || '.' WHERE C_Order_ID=v_Record_ID;
1093 END;
1094 END LOOP;
1095 ELSE
1096 -- Change Back
1097 UPDATE C_ORDER
1098 SET C_DocTypeTarget_ID=v_DocType_ID
1099 WHERE C_Order_ID=v_Record_ID;
1100 RAISE EXCEPTION '%', '@CannotChangeDocType@' ; --OBTG:-20000--
1101 END IF;
1102 END IF;
1103 END IF; -- C_DocType_ID <> C_DocTypeTarget_ID
1104 END; -- Conversion
1105
1106 /**
1107 * Get DocSubTypeSO + Is it Binding :1:2
1108 */
1109 v_ResultStr:='TestBinding DocType_ID=' || v_DocType_ID;
1110 SELECT CASE DocSubTypeSO WHEN 'ON' THEN 'N' ELSE 'Y' END, DocSubTypeSO
1111 INTO v_IsBinding, v_DocSubTypeSO
1112 FROM C_DOCTYPE
1113 WHERE C_DocType_ID=v_DocType_ID;
1114 RAISE NOTICE '%','DocSubTypeSO=' || v_DocSubTypeSO || ' IsBinding=' || v_IsBinding ;
1115
1116 /**************************************************************************
1117 * Resolve not-stocked BOMs
1118 *************************************************************************/
1119 DECLARE
1120 -- Order Lines with non-stocked BOMs
1121 CUR_BOM_Line RECORD;
1122 BEGIN
1123 FOR CUR_BOM_Line IN
1124 (SELECT l.c_orderline_id
1125 FROM C_ORDERLINE l
1126 WHERE l.C_Order_ID=v_Record_ID
1127 AND l.IsActive='Y'
1128 AND l.explode='N'
1129 AND EXISTS
1130 (SELECT *
1131 FROM M_PRODUCT p
1132 WHERE l.M_Product_ID=p.M_Product_ID
1133 AND p.IsBOM='Y'
1134 AND p.IsStocked='N'
1135 )
1136 ORDER BY l.Line
1137 )
1138 LOOP
1139 PERFORM M_EXPLODEBOMNOTSTOCK(null, CUR_BOM_Line.c_orderline_ID);
1140 END LOOP;
1141 END;
1142 /**************************************************************************
1143 * Always check and (un) Reserve Inventory (counterpart: M_InOut_Post)
1144 *************************************************************************/
1145 IF (v_DocAction<>'CL') THEN
1146 DECLARE
1147 Cur_ResLine RECORD;
1148
1149 v_QtySO NUMERIC; -- Reserved
1150 v_QtyOrderSO NUMERIC;
1151 v_QtyPO NUMERIC; -- Ordered
1152 v_QtyOrderPO NUMERIC;
1153 v_UOM_ID VARCHAR(32); --OBTG:VARCHAR2--
1154 BEGIN
1155 v_ResultStr := 'ReserveInventory';
1156 -- For all lines needing reservation
1157 FOR Cur_ResLine IN (SELECT l.M_Warehouse_ID, l.M_Product_ID, l.M_AttributeSetInstance_ID, l.C_OrderLine_ID,
1158 -- Target Level = 0 if DirectShip='Y' or Binding='N'
1159 (CASE l.DirectShip WHEN 'Y' THEN 0 ELSE (CASE v_IsBinding WHEN 'N' THEN 0 ELSE l.QtyOrdered END) END)
1160 -l.QtyReserved-l.QtyDelivered AS Qty, l.QUANTITYORDER,
1161 l.QtyReserved, l.QtyDelivered, l.DatePromised, l.C_UOM_ID, l.C_AUM,
1162 l.M_PRODUCT_UOM_ID
1163 FROM C_ORDERLINE l, M_PRODUCT p
1164 WHERE l.C_Order_ID=v_Record_ID
1165 -- Reserve Products (not: services, null products) --
1166 AND l.M_Product_ID=p.M_Product_ID
1167 AND p.IsStocked='Y' AND p.ProductType='I'
1168 -- Target Level = 0 if DirectShip='Y' or Binding='N'
1169 AND (CASE l.DirectShip WHEN 'Y' THEN 0 ELSE (CASE v_IsBinding WHEN 'N' THEN 0 ELSE l.QtyOrdered END)END)
1170 -l.QtyReserved-l.QtyDelivered <> 0)
1171 LOOP
1172
1173 -- Qty corrected for SO/PO
1174 IF (v_DocSubTypeSO IS NOT NULL) THEN
1175 v_QtySO := Cur_ResLine.Qty;
1176 v_QtyOrderSO := NULL;
1177 IF (Cur_ResLine.QtyReserved = 0 AND Cur_ResLine.QtyDelivered = 0) THEN
1178 v_QtyOrderSO := Cur_ResLine.QuantityOrder;
1179 ELSIF (Cur_ResLine.C_AUM IS NULL AND Cur_ResLine.M_Product_UOM_ID IS NOT NULL) THEN
1180 SELECT c_uom_id
1181 INTO v_UOM_ID
1182 FROM m_product_uom
1183 WHERE m_product_uom_id = Cur_ResLine.M_Product_UOM_ID;
1184 v_QtyOrderSO := C_Uom_Convert(v_QtySO, Cur_ResLine.C_UOM_ID, v_UOM_ID, 'Y');
1185 END IF;
1186 v_QtyPO := 0;
1187 v_QtyOrderPO := NULL;
1188 ELSE -- PO
1189 v_QtySO := 0;
1190 v_QtyOrderSO := NULL;
1191 v_QtyPO := Cur_ResLine.Qty;
1192 v_QtyOrderPO := NULL;
1193 IF (Cur_ResLine.QtyReserved = 0 AND Cur_ResLine.QtyDelivered = 0) THEN
1194 v_QtyOrderPO := Cur_ResLine.QuantityOrder;
1195 ELSIF (Cur_ResLine.C_AUM IS NULL AND Cur_ResLine.M_Product_UOM_ID IS NOT NULL) THEN
1196 SELECT c_uom_id
1197 INTO v_UOM_ID
1198 FROM m_product_uom
1199 WHERE m_product_uom_id = Cur_ResLine.M_Product_UOM_ID;
1200 v_QtyOrderPO := C_Uom_Convert(v_QtyPO, Cur_ResLine.C_UOM_ID, v_UOM_ID, 'Y');
1201 END IF;
1202 END IF;
1203 IF ((v_DocStatus<>'IP' OR v_DocAction<>'CO') AND COALESCE(v_DocSubTypeSO, '')<>'OB') THEN
1204 PERFORM M_UPDATE_STORAGE_PENDING(v_Client_ID, v_Org_ID, v_UpdatedBy, Cur_ResLine.M_Product_ID, Cur_ResLine.M_Warehouse_ID, Cur_ResLine.M_AttributeSetInstance_ID,
1205 Cur_ResLine.C_UOM_ID, Cur_ResLine.M_PRODUCT_UOM_ID, v_QtySO, v_QtyOrderSO, v_QtyPO, v_QtyOrderPO);
1206 END IF;
1207 RAISE NOTICE '%','Reserved Warehouse=' || Cur_ResLine.M_Warehouse_ID || ', Product=' || Cur_ResLine.M_Product_ID || ', Attrib=' || Cur_ResLine.M_AttributeSetInstance_ID || ', Qty=' || v_QtySO || '/' || v_QtyPO;
1208
1209 -- Update Order Line
1210 IF (v_DocSubTypeSO IS NOT NULL) THEN
1211 UPDATE C_ORDERLINE
1212 SET QtyReserved = QtyReserved + v_QtySO
1213 WHERE C_OrderLine_ID = Cur_ResLine.C_OrderLine_ID;
1214 END IF;
1215 GET DIAGNOSTICS rowcount:=ROW_COUNT;
1216 IF (rowcount <> 1) THEN
1217 IF (p_PInstance_ID IS NOT NULL) THEN
1218 -- ROLLBACK;
1219 v_ResultStr := 'LockingOrder';
1220 UPDATE C_ORDER
1221 SET Processing = 'N',
1222 Updated=TO_DATE(NOW()),
1223 UpdatedBy=v_User
1224 WHERE C_Order_ID = v_Record_ID;
1225 RAISE EXCEPTION '%','DATA_EXCEPTION';
1226 -- COMMIT;
1227 END IF;
1228 RAISE EXCEPTION '%', '@20011@'; --OBTG:-20000--
1229 END IF;
1230 END LOOP; -- For all lines needing reservation
1231 END;
1232 END IF; -- Reserve Inventory
1233
1234 /**************************************************************************
1235 * Stock Reservations management.
1236 *************************************************************************/
1237 SELECT COUNT(1) INTO v_count
1238 FROM ad_preference
1239 WHERE property = 'StockReservations';
1240 IF (v_count > 0) THEN
1241 IF (v_count > 1) THEN
1242 v_dummy := AD_GET_PREFERENCE_VALUE('StockReservations', 'Y', v_client_id, v_org_id, NULL, NULL, NULL);
1243 END IF;
1244 DECLARE
1245 v_reservation_id VARCHAR(32); --OBTG:VARCHAR2--
1246 v_quantity NUMERIC;
1247 v_reservedqty NUMERIC;
1248 v_releasedqty NUMERIC;
1249 v_allocated NUMERIC;
1250 v_pendingtounreserve NUMERIC;
1251 v_qtyaux NUMERIC;
1252 v_res_status M_RESERVATION.RES_STATUS%TYPE;
1253 v_linecount NUMERIC;
1254 v_creservedcount NUMERIC;
1255 v_preservedcount NUMERIC;
1256
1257 cur_res_stock RECORD;
1258 BEGIN
1259 IF (v_issotrx = 'Y') THEN
1260 FOR cur_orderline IN (
1261 SELECT ol.c_orderline_id, ol.create_reservation, ol.qtyordered
1262 FROM c_orderline ol
1263 JOIN m_product p ON ol.m_product_id = p.m_product_id
1264 LEFT JOIN m_reservation r ON ol.c_orderline_id = r.c_orderline_id
1265 WHERE ol.c_order_id = v_record_id
1266 AND ((
1267 ol.qtyordered > 0
1268 AND p.isstocked = 'Y'
1269 AND p.producttype = 'I'
1270 ) OR (
1271 r.m_reservation_id IS NOT NULL
1272 )
1273 )
1274 ) LOOP
1275 SELECT count(*), max(m_reservation_id)
1276 INTO v_aux, v_reservation_id
1277 FROM m_reservation
1278 WHERE c_orderline_id = cur_orderline.c_orderline_id
1279 AND res_status <> 'CL';
1280 -- Initialize so_res_status
1281 UPDATE c_orderline
1282 SET so_res_status = 'NR'
1283 WHERE c_orderline_id = cur_orderline.c_orderline_id;
1284 IF (v_aux > 1) THEN
1285 RAISE EXCEPTION '%', '@SOLineWithMoreThanOneOpenReservation@'; --OBTG:-20000--
1286 ELSIF (v_aux = 1) THEN
1287 -- Update reservation when possible.
1288 -- Read reservation.
1289 SELECT r.quantity, r.reservedqty, r.releasedqty, r.res_status,
1290 COALESCE(SUM(CASE rs.isallocated WHEN 'Y' THEN rs.quantity - COALESCE(rs.releasedqty, 0) ELSE 0 END), 0)
1291 INTO v_quantity, v_reservedqty, v_releasedqty, v_res_status,
1292 v_allocated
1293 FROM m_reservation r
1294 LEFT JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id
1295 WHERE r.m_reservation_id = v_reservation_id
1296 GROUP BY r.quantity, r.reservedqty, r.releasedqty, r.res_status;
1297 IF (v_quantity != cur_orderline.qtyordered) THEN
1298 IF (v_allocated <> 0) THEN
1299 RAISE EXCEPTION '%', '@ThereIsMoreAllocatedQtyThanOrdered@'; --OBTG:-20000--
1300 END IF;
1301 IF (cur_orderline.qtyordered < v_releasedqty) THEN
1302 RAISE EXCEPTION '%', '@CannotOrderLessThanReleasedQty@'; --OBTG:-20000--
1303 END IF;
1304 IF (cur_orderline.qtyordered < v_reservedqty) OR (v_releasedqty = 0 AND cur_orderline.qtyordered > 0) THEN
1305 --Reservation quantity to decrease with more reserved quantity than new quantity, unreserve stock
1306 v_pendingtounreserve := v_reservedqty - cur_orderline.qtyordered;
1307 FOR cur_res_stock IN (
1308 SELECT m_reservation_stock_id, quantity - COALESCE(releasedqty, 0) AS reservedqty
1309 FROM m_reservation_stock
1310 WHERE m_reservation_id = v_reservation_id
1311 ORDER BY COALESCE(releasedqty, 0), quantity - COALESCE(releasedqty, 0)
1312 ) LOOP
1313 v_qtyaux := LEAST(v_pendingtounreserve, cur_res_stock.reservedqty);
1314 UPDATE m_reservation_stock
1315 SET quantity = quantity - v_qtyaux,
1316 updated = TO_DATE(NOW()),
1317 updatedby = v_user
1318 WHERE m_reservation_stock_id = cur_res_stock.m_reservation_stock_id;
1319 v_reservedqty := v_reservedqty - v_qtyaux;
1320 v_pendingtounreserve := v_pendingtounreserve - v_qtyaux;
1321 IF (v_pendingtounreserve = 0) THEN
1322 EXIT;
1323 END IF;
1324 END LOOP;
1325 IF (v_pendingtounreserve > 0) THEN
1326 RAISE EXCEPTION '%', '@CouldNotUnreserveNeededQty@'; --OBTG:-20000--
1327 END IF;
1328 IF (v_DocAction <> 'CO') THEN
1329 -- Delete reservation lines with zero releasedqty
1330 DELETE FROM m_reservation_stock
1331 WHERE COALESCE(releasedqty, 0) = 0
1332 AND m_reservation_id = v_reservation_id;
1333 END IF;
1334 END IF;
1335 -- Order line orderedqty greater than reservation releasedqty
1336 IF (cur_orderline.qtyordered > v_releasedqty AND v_DocAction <> 'CO') THEN
1337 UPDATE m_reservation_stock
1338 SET quantity = releasedqty,
1339 updated = TO_DATE(NOW()),
1340 updatedby = v_user
1341 WHERE m_reservation_id = v_reservation_id;
1342 END IF;
1343 UPDATE m_reservation
1344 SET quantity = cur_orderline.qtyordered,
1345 res_status = CASE WHEN v_DocAction <> 'CO' THEN 'CL' ELSE res_status END,
1346 updated = TO_DATE(NOW()),
1347 updatedby = v_user
1348 WHERE m_reservation_id = v_reservation_id;
1349 IF (v_quantity < cur_orderline.qtyordered AND v_res_status = 'CO' AND v_DocAction <> 'CO') THEN
1350 --Reservation processed with higher quantity. Try to reserve the new quantity.
1351 SELECT * INTO v_reservedqty FROM M_RESERVE_STOCK_AUTO(v_reservation_id, v_user);
1352 END IF;
1353 END IF;
1354
1355 IF (v_res_status <> 'DR') THEN
1356 -- Update so_res_status
1357 UPDATE c_orderline
1358 SET so_res_status = CASE WHEN cur_orderline.qtyordered = v_reservedqty THEN 'CR'
1359 WHEN cur_orderline.qtyordered > v_reservedqty AND v_reservedqty > 0 THEN 'PR'
1360 ELSE 'NR'
1361 END
1362 WHERE c_orderline_id = cur_orderline.c_orderline_id;
1363 END IF;
1364 ELSE
1365 SELECT count(*)
1366 INTO v_aux
1367 FROM dual
1368 WHERE EXISTS (
1369 SELECT 1
1370 FROM m_reservation
1371 WHERE c_orderline_id = cur_orderline.c_orderline_id
1372 );
1373 IF (v_aux = 0 AND cur_orderline.create_reservation = 'CRP') THEN
1374 SELECT * INTO v_reservation_id FROM M_CREATE_RESERVE_FROM_SOL(cur_orderline.c_orderline_id, 'Y', v_user);
1375 ELSIF (v_aux = 0 AND cur_orderline.create_reservation = 'CR') THEN
1376 SELECT * INTO v_reservation_id FROM M_CREATE_RESERVE_FROM_SOL(cur_orderline.c_orderline_id, 'N', v_user);
1377 END IF;
1378 END IF;
1379 END LOOP;
1380 SELECT COUNT(*), SUM(CASE ol.so_res_status WHEN 'CR' THEN 1 ELSE 0 END), SUM(CASE ol.so_res_status WHEN 'PR' THEN 1 ELSE 0 END)
1381 INTO v_linecount, v_creservedcount, v_preservedcount
1382 FROM c_orderline ol
1383 JOIN m_product p ON ol.m_product_id = p.m_product_id
1384 WHERE ol.c_order_id = v_record_id
1385 AND ol.qtyordered > 0
1386 AND p.isstocked = 'Y'
1387 AND p.producttype = 'I';
1388 UPDATE c_order
1389 SET so_res_status = CASE WHEN v_linecount = v_creservedcount THEN 'CR'
1390 WHEN v_creservedcount + v_preservedcount > 0 THEN 'PR'
1391 ELSE 'NR'
1392 END
1393 WHERE c_order_id = v_record_id;
1394 END IF;
1395 END;
1396 END IF;
1397
1398 -- Synchronize Client/Org Ownership
1399 UPDATE C_ORDERLINE
1400 SET AD_Client_ID=v_Client_ID
1401 WHERE C_Order_ID=v_Record_ID
1402 AND (AD_Client_ID<>v_Client_ID) ;
1403
1404 IF (v_docaction = 'CO' AND v_issotrx = 'N') THEN
1405 UPDATE m_transaction
1406 SET checkpricedifference = 'Y'
1407 WHERE m_transaction_id IN (
1408 SELECT trx.m_transaction_id
1409 FROM c_orderline ol
1410 JOIN m_matchpo mpo ON mpo.c_orderline_id = ol.c_orderline_id
1411 JOIN m_transaction trx ON mpo.m_inoutline_id = trx.m_inoutline_id
1412 WHERE trx.iscostcalculated = 'Y' AND ol.c_order_id = v_record_id);
1413 END IF;
1414
1415 /**************************************************************************
1416 * Order Complete:5 - Something to do:6
1417 */
1418 BEGIN
1419 v_ResultStr:='OrderCompleteCheck';
1420 SELECT COUNT(*) INTO ToDeliverOrToInvoice FROM DUAL
1421 WHERE 0 <> ANY (select QtyOrdered - QtyDelivered from c_orderline where c_order_id = v_Record_ID)
1422 OR 0 <> ANY (select QtyOrdered - QtyInvoiced from c_orderline where c_order_id = v_Record_ID);
1423 -- If something to deliver or to invoice, then ToDeliverOrToInvoice = 1
1424 IF (ToDeliverOrToInvoice = 0) THEN
1425 RAISE NOTICE '%','OrderComplete' ;
1426 IF (v_DocAction='CL') THEN
1427 END_PROCESSING:=TRUE;
1428 ELSIF (v_DocAction='VO') THEN
1429 UPDATE C_ORDER
1430 SET DocStatus='VO',
1431 DocAction='--',
1432 Processed='Y',
1433 Updated=TO_DATE(NOW()),
1434 UpdatedBy=v_User
1435 WHERE C_Order_ID=v_Record_ID;
1436 END_PROCESSING:=TRUE;
1437 ELSE
1438 UPDATE C_ORDER
1439 SET DocStatus='CO',
1440 DocAction='--',
1441 Processed='Y',
1442 Updated=TO_DATE(NOW()),
1443 UpdatedBy=v_User
1444 WHERE C_Order_ID=v_Record_ID;
1445 END_PROCESSING:=TRUE;
1446 END IF;
1447 IF (NOT END_PROCESSING) THEN
1448 RAISE EXCEPTION '%', '@AlreadyPosted@'; --OBTG:-20000--
1449 END IF;--END_PROCESSING
1450 END IF;
1451 END;
1452 END IF;--FINISH_PROCESS
1453 IF (NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1454 /**
1455 * In Progress -----------------------------------------------------------
1456 */
1457 UPDATE C_ORDER
1458 SET DocStatus='IP',
1459 DateAcct=DateOrdered,
1460 Updated=TO_DATE(NOW()),
1461 UpdatedBy=v_User
1462 WHERE C_Order_ID=v_Record_ID;
1463 IF (p_PInstance_ID IS NOT NULL) THEN
1464 -- COMMIT;
1465 END IF;
1466
1467 /**
1468 * Finished with processing
1469 */
1470 IF (v_DocAction='PR') THEN
1471 v_ResultStr:='FinishProcessing';
1472 UPDATE C_ORDER
1473 SET DocStatus='IP',
1474 DocAction='CO',
1475 Processed='N',
1476 Updated=TO_DATE(NOW()),
1477 UpdatedBy=v_User
1478 WHERE C_Order_ID=v_Record_ID;
1479 -- C_Order_PickList(NULL, v_Record_ID); -- Print PickList
1480 END_PROCESSING:=TRUE;
1481 END IF;
1482 END IF;--FINISH_PROCESS
1483 IF (NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1484 /**************************************************************************
1485 * Prepayment Order Create Invoice
1486 *************************************************************************/
1487 IF (v_DocSubTypeSO='PR' AND v_DocStatus<>'WP') THEN
1488 RAISE NOTICE '%','Create PreInvoice - ' || v_Record_ID ;
1489 v_ResultStr:='CreatePreInvoice';
1490 SELECT * INTO Invoice_ID FROM C_Invoice_Create(NULL, v_Record_ID) ;
1491 RAISE NOTICE '%',' PreInvoice - ' || Invoice_ID ;
1492 IF (Invoice_ID='0') THEN
1493 RAISE EXCEPTION '%', '@PreInvoiceCreateFailed@'; --OBTG:-20000--
1494 END IF;
1495 PERFORM C_INVOICE_POST(NULL, Invoice_ID) ;
1496 --
1497 UPDATE C_ORDER
1498 SET DocStatus='WP',
1499 DocAction='--',
1500 Processed='Y',
1501 Updated=TO_DATE(NOW()),
1502 UpdatedBy=v_User
1503 WHERE C_Order_ID=v_Record_ID;
1504 --
1505 END_PROCESSING:=TRUE;
1506 END IF;
1507 IF (NOT END_PROCESSING) THEN
1508 /**
1509 * Deliver Direct Shipments
1510 */
1511 v_ResultStr:='NonInventoryDelivery';
1512 UPDATE C_ORDERLINE
1513 SET QtyDelivered=QtyOrdered
1514 WHERE DirectShip='Y'
1515 AND C_Order_ID=v_Record_ID;
1516 END IF;--END_PROCESSING
1517 END IF;--FINISH_PROCESS
1518 IF (NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1519 /**************************************************************************
1520 * Will-Call + Walk In Processing
1521 * --
1522 * (W)illCall(I)nvoice - (W)illCall(P)ickup - (W)alkIn(R)eceipt
1523 * --
1524 *************************************************************************/
1525 IF (v_DocSubTypeSO IN('WI', 'WP', 'WR')) THEN
1526 /************
1527 * Shipment
1528 */
1529 RAISE NOTICE '%','Create Shipment - ' || v_Record_ID ;
1530 v_ResultStr:='CreateShipment';
1531
1532 SELECT * INTO InOut_ID FROM M_Inout_Create(NULL, v_Record_ID, NULL, 'Y') ; -- Force Delivery
1533
1534 RAISE NOTICE '%',' Shipment - ' || InOut_ID ;
1535 IF (InOut_ID='0') THEN
1536 RAISE EXCEPTION '%', '@InOutCreateFailed@'; --OBTG:-20000--
1537 ELSE
1538 SELECT documentno
1539 INTO v_DocumentNo
1540 FROM M_INOUT
1541 WHERE M_INOUT_ID = InOut_ID;
1542 v_Message:='@InoutDocumentno@ ' || v_DocumentNo || ' @beenCreated@';
1543 END IF;
1544 IF (v_DocSubTypeSO IN('WI', 'WR')) THEN
1545 /************
1546 * Invoice
1547 */
1548 RAISE NOTICE '%','Create Invoice - ' || v_Record_ID ;
1549 v_ResultStr:='CreateInvoice';
1550 SELECT * INTO Invoice_ID FROM C_Invoice_Create(NULL, v_Record_ID) ;
1551 RAISE NOTICE '%',' Invoice - ' || Invoice_ID ;
1552 IF (Invoice_ID IS NULL OR Invoice_ID='0') THEN
1553 RAISE EXCEPTION '%', '@InvoiceCreateFailed@'; --OBTG:-20000--
1554 ELSE
1555 SELECT documentno
1556 INTO v_DocumentNo
1557 FROM C_INVOICE
1558 WHERE C_INVOICE_ID = Invoice_ID;
1559 v_Message:=v_Message||' , '||'@InvoiceDocumentno@ ' || v_DocumentNo || ' @invbeenCreated@';
1560 END IF;
1561 END IF;
1562 END IF;
1563
1564 /**
1565 * Final Completeness check
1566 */
1567 SELECT COUNT(*) INTO ToDeliver FROM DUAL
1568 WHERE 0 <> ANY (select QtyOrdered - QtyDelivered from c_orderline where c_order_id = v_Record_ID);
1569 SELECT COUNT(*) INTO ToInvoice FROM DUAL
1570 WHERE 0 <> ANY (select QtyOrdered - QtyInvoiced from c_orderline where c_order_id = v_Record_ID);
1571 RAISE NOTICE '%','To deliver - ' || ToDeliver ;
1572 RAISE NOTICE '%','ToInvoice - ' || ToInvoice ;
1573 RAISE NOTICE '%','v_DocSubTypeSO - ' || v_DocSubTypeSO ;
1574 -- Nothing to Deliver + Invoice for (W)illCall(I)nvoice and (W)alkIn(R)eceipt
1575 IF (v_DocSubTypeSO IN ('WI', 'WR') AND ToDeliver=0 AND ToInvoice=0) THEN
1576 UPDATE C_ORDER
1577 SET DocStatus='CO',
1578 DocAction='--',
1579 IsDelivered='Y',
1580 IsInvoiced='Y',
1581 Processed='Y',
1582 Updated=TO_DATE(NOW()),
1583 UpdatedBy=v_User
1584 WHERE C_Order_ID=v_Record_ID;
1585 RAISE NOTICE '%','DocAction - ' || v_DocAction ;
1586 IF (v_DocAction='VO') THEN
1587 UPDATE C_ORDER SET DocStatus='VO' WHERE C_Order_ID=v_Record_ID;
1588 END IF;
1589 END IF;
1590 -- Nothing to Deliver for (W)illCall(P)ickup (Invoice generated independently)
1591 IF (v_DocSubTypeSO='WP' AND ToDeliver=0) THEN
1592 UPDATE C_ORDER
1593 SET DocStatus='CO',
1594 DocAction='--',
1595 IsDelivered='Y',
1596 Processed='Y',
1597 Updated=TO_DATE(NOW()),
1598 UpdatedBy=v_User
1599 WHERE C_Order_ID=v_Record_ID;
1600 IF (v_DocAction='VO') THEN
1601 UPDATE C_ORDER SET DocStatus='VO' WHERE C_Order_ID=v_Record_ID;
1602 END IF;
1603 END IF;
1604
1605 -- If there is at least one line with different Quantity Ordered than Quantity Delivered, set the IsDelivered flag as N
1606 -- By default this flag is already N for new Orders, this can happen when the Order has been cloned from another one
1607 -- that has been already delivered, and then the lines information has changed
1608 IF (ToDeliver <> 0) THEN
1609 UPDATE C_ORDER
1610 SET IsDelivered='N',
1611 Updated=TO_DATE(NOW()),
1612 UpdatedBy=v_User
1613 WHERE C_Order_ID=v_Record_ID;
1614 END IF;
1615
1616 -- We are done with standard sales orders
1617 IF (v_DocSubTypeSO = 'RM' OR v_isreturndoctype = 'Y') THEN
1618
1619 FOR Cur_Order IN( SELECT ol.qtyordered, ol.c_order_discount_id FROM C_order o,C_orderline ol
1620 WHERE o.C_Order_ID = v_Record_ID
1621 AND ol.C_Order_ID = o.C_Order_ID)
1622 LOOP
1623 IF (Cur_Order.qtyordered >0 AND Cur_Order.c_order_discount_id IS NULL) THEN
1624 RAISE EXCEPTION '%', '@ReturnMaterialOrderType@' ; --OBTG:-20000--
1625 END IF;
1626 END LOOP;
1627 END IF;
1628 IF (v_DocAction IN('CO', 'CL', 'VO') AND v_DocSubTypeSO IN('SO','RM')) THEN
1629 UPDATE C_ORDER
1630 SET DocStatus='CO',
1631 DocAction='--',
1632 Processed='Y',
1633 Updated=TO_DATE(NOW()),
1634 UpdatedBy=v_User
1635 WHERE C_Order_ID=v_Record_ID;
1636 END IF;
1637 -- Purchase Orders
1638 IF (v_DocAction IN('CO', 'CL', 'VO') AND v_DocSubTypeSO IS NULL) THEN
1639 UPDATE C_ORDER
1640 SET DocStatus='CO',
1641 DocAction='--',
1642 Processed='Y',
1643 Updated=TO_DATE(NOW()),
1644 UpdatedBy=v_User
1645 WHERE C_Order_ID=v_Record_ID;
1646 END IF;
1647 IF (v_DocAction IN('CO') AND v_DocSubTypeSO IN('OB')) THEN
1648 UPDATE C_ORDER
1649 SET DocStatus='UE',
1650 DocAction='--',
1651 Processed='Y',
1652 Updated=TO_DATE(NOW()),
1653 UpdatedBy=v_User
1654 WHERE C_Order_ID=v_Record_ID;
1655 END IF;
1656 -- Only create cash entry if docAction is Complete
1657 IF (v_DocAction NOT IN('CO')) THEN
1658 END_PROCESSING:=TRUE;
1659 END IF;
1660 END IF;--FINISH_PROCESS
1661 IF (NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1662 /**************************************************************************
1663 * Create default Cash entry
1664 *************************************************************************/
1665 DECLARE
1666 v_PaymentRule VARCHAR(60) ;
1667 CUR_CB RECORD;
1668 v_debtPaymentID VARCHAR(32); --OBTG:varchar2--
1669 v_totalCash NUMERIC;
1670 v_CB_Curr VARCHAR(32); --OBTG:varchar2--
1671 BEGIN
1672 /* ALO
1673 */
1674 UPDATE C_DEBT_PAYMENT SET IsValid='Y' WHERE C_Order_ID=v_Record_ID;
1675 SELECT C_ORDER.PAYMENTRULE,
1676 (CASE
1677 WHEN (length(C_ORDER.DOCUMENTNO||' - '||C_BPARTNER.NAME||' - '||C_ORDER.GRANDTOTAL) > 200)
1678 THEN substr(C_ORDER.DOCUMENTNO||' - '||C_BPARTNER.NAME||' - '||C_ORDER.GRANDTOTAL,1,197)||'...'
1679 ELSE
1680 C_ORDER.DOCUMENTNO||' - '||C_BPARTNER.NAME||' - '||C_ORDER.GRANDTOTAL
1681 END) AS CONCATENATION,
1682 C_ORDER.GRANDTOTAL,
1683 C_ORDER.M_WAREHOUSE_ID
1684 INTO v_PaymentRule,
1685 v_DocumentNo,
1686 v_GrandTotal,
1687 v_M_Warehouse_ID
1688 FROM C_ORDER, C_BPARTNER
1689 WHERE C_ORDER.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
1690 AND C_ORDER_ID=v_Record_ID;
1691 SELECT MAX(NAME)
1692 INTO v_WarehouseName
1693 FROM M_WAREHOUSE
1694 WHERE M_WAREHOUSE_ID=v_M_Warehouse_ID;
1695
1696 --PaymentRule C, WI and WR have already created DP, this won't be inserted in cashline
1697 --because it will processed with the invoice.
1698 IF (v_PaymentRule='C') AND (v_DocSubTypeSO NOT IN ('WI', 'WR') OR v_DocSubTypeSO IS NULL) THEN
1699 IF (v_CashLine_ID IS NULL OR v_CashLine_ID='0') THEN
1700 -- Create CashLine
1701 -- Find Defaylt CashBook
1702 v_ResultStr:='Find C_CashBook Org_ID=' || v_Org_ID;
1703 BEGIN
1704 -- First active default Book of Org
1705 FOR CUR_CB IN
1706 (SELECT cb.C_CashBook_ID, c.ISO_Code, cb.NAME, cb.c_currency_id
1707 FROM C_CASHBOOK cb, C_CURRENCY c
1708 WHERE cb.AD_Org_ID=v_Org_ID
1709 AND cb.C_Currency_ID=c.C_Currency_ID
1710 AND cb.IsActive='Y'
1711 ORDER BY cb.IsDefault DESC
1712 )
1713 LOOP
1714 IF (v_CashBook_ID IS NULL) THEN
1715 v_CashBook_ID:=CUR_CB.C_CashBook_ID;
1716 v_ISO_Code:=CUR_CB.ISO_Code;
1717 v_CB_Curr:=CUR_CB.C_Currency_ID;
1718 ELSIF (CUR_CB.NAME=v_WarehouseName) THEN
1719 v_CashBook_ID:=CUR_CB.C_CashBook_ID;
1720 v_ISO_Code:=CUR_CB.ISO_Code;
1721 v_CB_Curr:=CUR_CB.C_Currency_ID;
1722 END IF;
1723 END LOOP;
1724 END;
1725 IF (v_CashBook_ID IS NULL) THEN
1726 RAISE EXCEPTION '%', '@CashBookPRSCnotfoundOrg@' || v_Org_Name ; --OBTG:-20000--
1727 END IF;
1728 RAISE NOTICE '%','CashBook_ID=' || v_CashBook_ID ;
1729 -- Find/Create Cash Journal
1730 v_ResultStr:='Find C_Cash for ' || v_Date;
1731 DECLARE
1732 Cur_CashId RECORD;
1733 BEGIN
1734 FOR Cur_CashId IN
1735 (SELECT C_Cash_ID AS Cash_ID
1736 FROM C_CASH
1737 WHERE C_CashBook_ID=v_CashBook_ID
1738 AND TRUNC(StatementDate)=v_Date
1739 AND Processed='N'
1740 )
1741 LOOP
1742 v_Cash_ID:=Cur_CashId.Cash_ID;
1743 EXIT;
1744 END LOOP;
1745 EXCEPTION
1746 WHEN DATA_EXCEPTION THEN
1747 NULL;
1748 END;
1749 /**************************************************************************
1750 * Credit Multiplier
1751 *************************************************************************/
1752 DECLARE
1753 v_DocBaseType C_DOCTYPE.DocBaseType%TYPE;
1754 BEGIN
1755 -- Is it a Credit Memo?
1756 SELECT DocBaseType
1757 INTO v_DocBaseType
1758 FROM C_DOCTYPE
1759 WHERE C_DocType_ID=v_DocType_ID;
1760 IF (v_DocBaseType IN('ARC', 'API')) THEN
1761 v_Multiplier:=-1;
1762 END IF;
1763 END;
1764
1765 IF (v_Cash_ID IS NULL) THEN
1766 v_ResultStr:='Create C_Cash';
1767 SELECT * INTO v_Cash_ID FROM Ad_Sequence_Next('C_Cash', v_Org_ID) ;
1768 INSERT
1769 INTO C_CASH
1770 (
1771 C_Cash_ID, AD_Client_ID, AD_Org_ID, IsActive,
1772 Created, CreatedBy, Updated, UpdatedBy,
1773 C_CashBook_ID, NAME, StatementDate, DateAcct,
1774 BeginningBalance, EndingBalance, StatementDifference, Processing,
1775 Processed, Posted
1776 )
1777 VALUES
1778 (
1779 v_Cash_ID, v_Client_ID, v_Org_ID, 'Y',
1780 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
1781 v_CashBook_ID, TO_CHAR(v_Date, 'YYYY-MM-DD') || ' ' || v_ISO_Code, v_Date, v_Date,
1782 0, 0, 0, 'N',
1783 'N', 'N'
1784 )
1785 ;
1786 END IF;
1787 /*
1788 Create a debt payment for paymentrule=C
1789 Note: for WI and WR we have already created an invoice and its DP, we only have to link it
1790 */
1791 SELECT COALESCE(SUM(C_Currency_Round(C_Currency_Convert((Amount + WriteOffAmt), C_Currency_ID, v_CB_Curr, v_Date, NULL, v_Client_ID, v_Org_ID), v_c_Currency_ID, NULL)), 0)
1792 INTO v_totalCash
1793 FROM C_DEBT_PAYMENT_V dp
1794 WHERE C_Order_ID=v_Record_ID;
1795
1796 SELECT * INTO v_debtPaymentID FROM Ad_Sequence_Next('C_Debt_Payment', v_Record_ID) ;
1797 INSERT INTO C_DEBT_PAYMENT
1798 (C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
1799 CREATED, CREATEDBY, UPDATED, UPDATEDBY,
1800 ISRECEIPT, C_SETTLEMENT_CANCEL_ID, C_SETTLEMENT_GENERATE_ID, DESCRIPTION,
1801 C_ORDER_ID, C_BPARTNER_ID, C_CURRENCY_ID, C_CASHLINE_ID,
1802 C_BANKACCOUNT_ID, C_CASHBOOK_ID, PAYMENTRULE, ISPAID,
1803 AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL,
1804 ISVALID, C_BANKSTATEMENTLINE_ID, CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED,
1805 GENERATE_PROCESSED, c_project_id,IsAutomaticGenerated, STATUS_INITIAL)
1806 VALUES
1807 (v_debtPaymentID, v_Client_ID, v_Org_ID, 'Y',
1808 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
1809 v_isSoTrx, NULL, NULL, '',
1810 v_Record_ID, v_c_Bpartner_Id, v_c_currency_id, null,
1811 NULL, v_CashBook_ID, 'C', 'N',
1812 (v_GrandTotal-v_totalCash), 0, v_Date, 'N',
1813 'Y', NULL, 'N', 'N',
1814 'N', v_C_PROJECT_ID,'Y', 'DE');
1815
1816 RAISE NOTICE '%',' Cash_ID=' || v_Cash_ID ;
1817 -- Create CashJournal Line in invoice currency
1818 v_ResultStr:='Create C_CashLine';
1819 SELECT * INTO v_CashLine_ID FROM Ad_Sequence_Next('C_CashLine', v_Org_ID) ;
1820
1821 SELECT COALESCE(MAX(Line), 0) +10
1822 INTO v_Line
1823 FROM C_CASHLINE
1824 WHERE C_Cash_ID=v_Cash_ID;
1825 --
1826 INSERT
1827 INTO C_CASHLINE
1828 (
1829 C_CashLine_ID, AD_Client_ID, AD_Org_ID, IsActive,
1830 Created, CreatedBy, Updated, UpdatedBy,
1831 C_Cash_ID, C_Debt_Payment_ID, Line, Description,
1832 Amount, CashType, DiscountAmt, WriteOffAmt,
1833 IsGenerated
1834 )
1835 VALUES
1836 (
1837 v_CashLine_ID, v_Client_ID, v_Org_ID, 'Y',
1838 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
1839 v_Cash_ID, v_debtPaymentID, v_Line, v_DocumentNo,
1840 (v_GrandTotal-v_totalCash) * (CASE WHEN v_isSoTrx='N' THEN -1 ELSE 1 END), 'P', 0, 0,
1841 'Y'
1842 )
1843 ;
1844 RAISE NOTICE '%',' CashLine_ID=' || v_CashLine_ID ;
1845
1846 END IF; -- CashLine_ID IS NULL OR CashLine_ID = '0'
1847 END IF; -- v_PaymentRule = 'C'
1848 END;
1849 END IF;--FINISH_PROCESS
1850 IF (NOT FINISH_PROCESS) THEN
1851 -- End Processing --------------------------------------------------------
1852 ---- <<END_PROCESSING>>
1853 -- Cloase Order
1854 IF (v_DocAction='CL') THEN
1855 UPDATE C_ORDER
1856 SET DocStatus='CL',
1857 DocAction='--',
1858 Processed='Y'
1859 WHERE C_Order_ID=v_Record_ID;
1860 END IF;
1861 END IF;--FINISH_PROCESS
1862
1863 -- Round and Adjust taxes when 'CO' and Recalculate amounts and taxes when 'RE'
1864 IF (v_isTaxIncluded = 'Y' AND v_DocAction IN ('CO', 'RE')) THEN
1865 PERFORM C_ORDERTAX_ADJUSTMENT(v_Record_ID, v_stdPrecision, v_DocAction);
1866 END IF;
1867
1868 --C_Order_Post - Finish_Process Extension Point
1869 SELECT count(*) INTO v_count
1870 FROM DUAL
1871 where exists (select 1 from ad_ep_procedures where ad_extension_points_id = 'CB68FC0E8A4547D9943C785761977E77');
1872 IF (v_count=1) THEN
1873 DECLARE
1874 v_ep_instance VARCHAR(32); --OBTG:VARCHAR2--
1875 v_extension_point_id VARCHAR(32) := 'CB68FC0E8A4547D9943C785761977E77'; --OBTG:VARCHAR2--
1876 BEGIN
1877 v_ep_instance := get_uuid();
1878 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Record_ID',
1879 v_record_id, NULL, NULL, NULL, NULL, NULL, NULL);
1880 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'DocAction',
1881 v_DocAction, NULL, NULL, NULL, NULL, NULL, NULL);
1882 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'User',
1883 v_User, NULL, NULL, NULL, NULL, NULL, NULL);
1884 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Message',
1885 NULL, NULL, NULL, NULL, NULL, NULL, v_Message);
1886 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Result',
1887 NULL, NULL, v_result, NULL, NULL, NULL, NULL);
1888 PERFORM AD_EXTENSION_POINT_HANDLER(v_ep_instance, v_extension_point_id);
1889 SELECT p_number INTO v_Result
1890 FROM ad_ep_instance_para
1891 WHERE ad_ep_instance_id = v_ep_instance
1892 AND parametername LIKE 'Result';
1893 SELECT p_text INTO v_Message
1894 FROM ad_ep_instance_para
1895 WHERE ad_ep_instance_id = v_ep_instance
1896 AND parametername LIKE 'Message';
1897
1898 DELETE FROM ad_ep_instance_para
1899 WHERE ad_ep_instance_id = v_ep_instance;
1900 END;
1901 END IF;
1902
1903 IF (NOT FINISH_PROCESS) THEN
1904 IF (p_PInstance_ID IS NOT NULL) THEN
1905 v_ResultStr:='UnLockingOrder';
1906 UPDATE C_ORDER
1907 SET Processing='N',
1908 Updated=TO_DATE(NOW()),
1909 UpdatedBy=v_User
1910 WHERE C_Order_ID=v_Record_ID;
1911 -- COMMIT;
1912 END IF;
1913 END IF;--FINISH_PROCESS
1914
1915 ---- <<FINISH_PROCESS>>
1916 IF (p_PInstance_ID IS NOT NULL) THEN
1917 -- Update AD_PInstance
1918 RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message ;
1919 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
1920 END IF;
1921 RETURN;
1922 END; --BODY
1923EXCEPTION
1924WHEN OTHERS THEN
1925 RAISE NOTICE '%',v_ResultStr ;
1926 v_ResultStr:= '@ERROR=' || SQLERRM;
1927 IF(p_PInstance_ID IS NOT NULL) THEN
1928 -- ROLLBACK;
1929 --Inserted by Carlos Romero 062706
1930 UPDATE C_ORDER SET Processing='N' WHERE C_Order_ID=v_Record_ID;
1931 RAISE NOTICE '%',v_ResultStr ;
1932 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
1933 ELSE
1934 RAISE EXCEPTION '%', SQLERRM;
1935 END IF;
1936 RETURN;
1937END ; $BODY$
1938 LANGUAGE plpgsql VOLATILE
1939 COST 100;
1940ALTER FUNCTION public.c_order_post1(character varying, character varying, character varying) SET search_path="$user", public;
1941
1942ALTER FUNCTION public.c_order_post1(character varying, character varying, character varying)
1943 OWNER TO postgres;