· 8 years ago · Jan 31, 2018, 11:54 AM
1CREATE OR REPLACE FUNCTION public.c_order_post1(p_pinstance_id character varying, p_order_id character varying)
2 RETURNS void
3 LANGUAGE plpgsql
4AS $function$ DECLARE
5/*************************************************************************
6* The contents of this file are subject to the Openbravo Public License
7* Version 1.0 (the "License"), being the Mozinseilla Public License
8* Version 1.1 with a permitted attribution clause; you may not use this
9* file except in compliance with the License. You may obtain a copy of
10* the License at http://www.openbravo.com/legal/license.html
11* Software distributed under the License is distributed on an "AS IS"
12* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
13* License for the specific language governing rights and limitations
14* under the License.
15* The Original Code is Openbravo ERP.
16* The Initial Developer of the Original Code is Openbravo SL
17* All portions are Copyright (C) 2001-2009 Openbravo SL
18* All Rights Reserved.
19* Contributor(s): ______________________________________.
20************************************************************************/
21 -- Logistics
22 xTotalLines1 NUMERIC:=0;
23 v_TaxBaseAmt1 NUMERIC:=0;
24 xTaxAmt1 NUMERIC:=0;
25 xGrandTotal1 NUMERIC:=0;
26 v_ActualBaseAmt1 NUMERIC;
27 v_lineNo1 NUMERIC:= 0;
28 v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
29 v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
30 v_Record_ID VARCHAR(32); --OBTG:VARCHAR2--
31 v_RecordLINE VARCHAR(32); --OBTG:VARCHAR2--
32 v_User VARCHAR(32); --OBTG:VARCHAR2--
33 v_IsProcessing CHAR(1) ;
34 v_IsProcessed VARCHAR(60) ;
35 v_Result NUMERIC:=1; -- Success
36 v_is_included NUMERIC:=0;
37 v_QtyOrdered NUMERIC:=0;
38 v_QtyPOOrdered NUMERIC:=0;
39 v_stdprecision NUMERIC:=0;
40 v_is_ready AD_Org.IsReady%TYPE;
41 v_is_tr_allow AD_OrgType.IsTransactionsAllowed%TYPE;
42 -- Parameter
43 --TYPE RECORD IS REFCURSOR;
44 Cur_Parameter RECORD;
45 -- Record Info
46 v_Client_ID VARCHAR(32); --OBTG:VARCHAR2--
47 v_Org_ID VARCHAR(32); --OBTG:VARCHAR2--
48 v_Org_Name VARCHAR(60); --OBTG:VARCHAR2--
49 v_UpdatedBy VARCHAR(32); --OBTG:VARCHAR2--
50 v_DocAction VARCHAR(60) ;
51 v_DocStatus VARCHAR(60) ;
52 v_InvoiceRule VARCHAR(60) ;
53 v_M_Warehouse_ID VARCHAR(32); --OBTG:VARCHAR2--
54 v_DocType_ID VARCHAR(32); --OBTG:VARCHAR2--
55 v_DocTypeTarget_ID VARCHAR(32); --OBTG:VARCHAR2--
56 v_DocSubTypeSO VARCHAR(60) ;
57 v_DocSubTypeSOTarget VARCHAR(60) ;
58 v_COrderId VARCHAR(32);
59 v_IsBinding CHAR(1):='Y';
60 --
61 ToDeliver NUMERIC;
62 ToInvoice NUMERIC;
63 --
64 InOut_ID VARCHAR(32); --OBTG:VARCHAR2--
65 Invoice_ID VARCHAR(32); --OBTG:VARCHAR2--
66 --Added by P.SAROBE
67 v_documentno_Settlement VARCHAR(40); --OBTG:VARCHAR2--
68 v_dateSettlement TIMESTAMP;
69 v_Cancel_Processed VARCHAR(60);
70 v_nameBankstatement VARCHAR (60); --OBTG:VARCHAR2--
71 v_dateBankstatement TIMESTAMP;
72 v_nameCash VARCHAR (60); --OBTG:VARCHAR2--
73 v_dateCash TIMESTAMP;
74 v_Bankstatementline_ID VARCHAR(32); --OBTG:VARCHAR2--
75 --Finish added by P.Sarobe
76 v_AuxCashLine_ID VARCHAR(32); --OBTG:VARCHAR2--
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_CashProcessed NUMERIC;
91 v_count NUMERIC;
92 v_isSoTrx CHAR(1) ;
93 v_Aux NUMERIC;
94 v_c_Bpartner_ID VARCHAR(32); --OBTG:VARCHAR2--
95 v_c_currency_ID VARCHAR(32); --OBTG:VARCHAR2--
96 v_C_PROJECT_ID VARCHAR(32); --OBTG:VARCHAR2--
97 v_PriceList_ID VARCHAR(32); --OBTG:VARCHAR2--
98 FINISH_PROCESS BOOLEAN:=FALSE;
99 END_PROCESSING BOOLEAN:=FALSE;
100 v_CBPartner_ID VARCHAR(32); --OBTG:VARCHAR2--
101 rowcount NUMERIC;
102 v_expiry NUMERIC;
103
104 v_CumDiscount NUMERIC;
105 v_OldCumDiscount NUMERIC;
106 v_OrderLineSeqNo NUMERIC;
107 Cur_COrderDiscount RECORD;
108 Cur_TaxDiscount RECORD;
109 v_OrderLine VARCHAR(32); --OBTG:VARCHAR2--
110 v_CPOOrderId VARCHAR(32); --OBTG:VARCHAR2--
111 v_Discount NUMERIC;
112
113 --Q3 Added
114 v_priceactual NUMERIC;
115 v_c_debt_payment varchar(32);
116 v_bpartner varchar(32);
117 v_name varchar(256);
118
119 v_doctypename varchar(256);
120 v_doctypeid varchar(32);
121 v_check varchar(1)='N';
122 Cur_po RECORD;
123 Cur_Payment RECORD;
124
125 Cur_InOutLine RECORD;
126 Cur_orderline RECORD;
127 Cur_Inout RECORD;
128 v_advance NUMERIC=0;
129 v_lineamt NUMERIC=0;
130 v_tempamt NUMERIC=0;
131 v_tempunit NUMERIC=0;
132 v_taxamt NUMERIC=0;
133 v_temptax NUMERIC=0;
134 v_qtyordered1 NUMERIC=0;
135 v_qtyordered2 NUMERIC=0;
136 v_order_id VARCHAR(32);
137 v_bpartnername VARCHAR(100);
138 v_productid varchar(32);
139
140
141 Cur_OrderDup RECORD;
142 Cur_OrderDup1 RECORD;
143 Cur_OrderSel RECORD;
144 v_count1 NUMERIC:=0;
145 v_count2 NUMERIC:=0;
146 v_DateNull TIMESTAMP := TO_DATE('01-01-1900','DD-MM-YYYY');
147 BEGIN
148 IF(p_PInstance_ID IS NOT NULL) THEN
149 -- Update AD_PInstance
150 RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
151 v_ResultStr:='PInstanceNotFound';
152 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
153 -- Get Parameters
154 v_ResultStr:='ReadingParameters';
155 FOR Cur_Parameter IN
156 (SELECT i.Record_ID,
157 i.AD_User_ID,
158 p.ParameterName,
159 p.P_String,
160 p.P_Number,
161 p.P_Date
162 FROM AD_PINSTANCE i
163 LEFT JOIN AD_PINSTANCE_PARA p
164 ON i.AD_PInstance_ID=p.AD_PInstance_ID
165 WHERE i.AD_PInstance_ID=p_PInstance_ID
166 ORDER BY p.SeqNo
167 )
168 LOOP
169 v_Record_ID:=Cur_Parameter.Record_ID;
170 v_User:=Cur_Parameter.AD_User_ID;
171 END LOOP; -- Get Parameter
172 ELSE
173 v_Record_ID:=p_Order_ID;
174 SELECT CREATEDBY INTO v_User FROM C_ORDER WHERE C_ORDER_ID=p_Order_ID;
175 END IF;
176 RAISE NOTICE '%',' Record_ID=' || v_Record_ID ;
177 BEGIN --BODY
178 /**
179 * Read Order
180 */
181 v_ResultStr:='ReadingOrder';
182 SELECT Processing, Processed, DocAction, DocStatus,
183 C_DocType_ID, C_DocTypeTarget_ID, AD_Client_ID,
184 AD_Org_ID, UpdatedBy, M_Warehouse_ID, TRUNC(DateOrdered),
185 Issotrx, c_Bpartner_Id, c_currency_id, C_PROJECT_ID,
186 C_BPartner_ID, M_PriceList_ID, invoicerule
187 INTO v_IsProcessing, v_IsProcessed, v_DocAction, v_DocStatus,
188 v_DocType_ID, v_DocTypeTarget_ID, v_Client_ID,
189 v_Org_ID, v_UpdatedBy, v_M_Warehouse_ID, v_Date,
190 v_isSoTrx, v_c_Bpartner_Id, v_c_currency_id, v_C_PROJECT_ID,
191 v_CBPartner_ID, v_PriceList_ID, v_invoicerule
192 FROM C_ORDER
193 WHERE C_Order_ID=v_Record_ID FOR UPDATE;
194 -- Get current DocSubTypeSO
195 SELECT DocSubTypeSO
196 INTO v_DocSubTypeSO
197 FROM C_DOCTYPE
198 WHERE C_DocType_ID=v_DocType_ID;
199 -- Get the name of the org of the Order. Added by P.Sarobe
200 SELECT name INTO v_Org_Name FROM AD_ORG WHERE ad_org_id = v_Org_ID;
201 RAISE NOTICE '%','DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus || ', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID || ', DocSubTypeSO=' || v_DocSubTypeSO ;
202
203 /**
204 * Check if order has lines
205 */
206 IF (v_DocAction = 'CO') THEN
207 SELECT COUNT(*)
208 INTO v_Aux
209 FROM C_ORDERLINE
210 WHERE C_ORDER_ID = v_Record_ID;
211 IF v_Aux=0 THEN
212 RAISE EXCEPTION '%', '@OrderWithoutLines@'; --OBTG:-20000--
213 END IF;
214 END IF;
215
216
217
218
219/**Q3 Added To Delete A Duplicate Lines ***/
220
221 IF(v_isSoTrx='N') THEN
222 IF (v_DocAction = 'CO') THEN
223 FOR Cur_OrderSel IN
224 select count(*) from c_orderline where c_orderline_id=v_Record_ID
225 LOOP
226 v_count1=v_count1+1;
227 --RAISE NOTICE '%',' v_count1 '||v_count1;
228 IF Cur_OrderSel.count < v_count1 THEN
229 For Cur_OrderDup IN
230
231 select * from
232 (select row_number( ) over(partition by line) as row, * from c_orderline where c_order_id=v_Record_ID)a
233 where a.row=v_count1
234 LOOP
235
236 For Cur_OrderDup1 IN
237
238 select * from
239 (select row_number( ) over(partition by line) as row, * from c_orderline where c_order_id=v_Record_ID)a
240 where a.row>v_count1
241 LOOP
242 -- RAISE NOTICE '%',' Cur_OrderDup ' ||Cur_OrderDup.line|| ' Cur_OrderDup1 ' ||Cur_OrderDup1.line;
243 IF (Cur_OrderDup.line=Cur_OrderDup1.line
244 AND (COALESCE(Cur_OrderDup.c_bpartner_id,'0')=COALESCE(Cur_OrderDup1.c_bpartner_id,'0'))
245 AND (COALESCE(Cur_OrderDup.c_bpartner_location_id,'0')=COALESCE(Cur_OrderDup1.c_bpartner_location_id,'0'))
246 AND (COALESCE(Cur_OrderDup.dateordered,v_DateNull)=COALESCE(Cur_OrderDup1.dateordered,v_DateNull))
247 AND (COALESCE(Cur_OrderDup.datepromised,v_DateNull)=COALESCE(Cur_OrderDup1.datepromised,v_DateNull))
248 AND (COALESCE(Cur_OrderDup.m_product_id,'0')=COALESCE(Cur_OrderDup1.m_product_id,'0'))
249 AND (COALESCE(Cur_OrderDup.m_warehouse_id,'0')=COALESCE(Cur_OrderDup1.m_warehouse_id,'0'))
250 AND (COALESCE(Cur_OrderDup.directship,'0')=COALESCE(Cur_OrderDup1.directship,'0'))
251 AND (COALESCE(Cur_OrderDup.c_uom_id,'0')=COALESCE(Cur_OrderDup1.c_uom_id,'0'))
252 AND (COALESCE(Cur_OrderDup.qtyordered,0)=COALESCE(Cur_OrderDup1.qtyordered,0))
253 AND (COALESCE(Cur_OrderDup.qtyreserved,0)=COALESCE(Cur_OrderDup1.qtyreserved,0))
254 AND (COALESCE(Cur_OrderDup.qtydelivered,0)=COALESCE(Cur_OrderDup1.qtydelivered,0))
255 AND (COALESCE(Cur_OrderDup.qtyinvoiced,0)=COALESCE(Cur_OrderDup1.qtyinvoiced,0))
256 AND (COALESCE(Cur_OrderDup.m_shipper_id,'0')=COALESCE(Cur_OrderDup1.m_shipper_id,'0'))
257 AND (COALESCE(Cur_OrderDup.c_currency_id,'0')=COALESCE(Cur_OrderDup1.c_currency_id,'0')
258 AND (COALESCE(Cur_OrderDup.pricelist,0)=COALESCE(Cur_OrderDup1.pricelist,0))
259 AND (COALESCE(Cur_OrderDup.priceactual,0))=COALESCE(Cur_OrderDup1.priceactual,0))
260 AND (COALESCE(Cur_OrderDup.pricelimit,0)=COALESCE(Cur_OrderDup1.pricelimit,0))
261 AND (COALESCE(Cur_OrderDup.linenetamt,0)=COALESCE(Cur_OrderDup1.linenetamt,0))
262 AND (COALESCE(Cur_OrderDup.discount,0)=COALESCE(Cur_OrderDup1.discount,0))
263 AND (COALESCE(Cur_OrderDup.freightamt,0)=COALESCE(Cur_OrderDup1.freightamt,0))
264 AND (COALESCE(Cur_OrderDup.c_charge_id,'0')=COALESCE(Cur_OrderDup1.c_charge_id,'0'))
265 AND (COALESCE(Cur_OrderDup.chargeamt,0)=COALESCE(Cur_OrderDup1.chargeamt,0))
266 AND (COALESCE(Cur_OrderDup.c_tax_id,'0')=COALESCE(Cur_OrderDup1.c_tax_id,'0'))
267 AND (COALESCE(Cur_OrderDup.s_resourceassignment_id,'0')=COALESCE(Cur_OrderDup1.s_resourceassignment_id,'0')) AND (COALESCE(Cur_OrderDup.ref_orderline_id,'0')=COALESCE(Cur_OrderDup1.ref_orderline_id,'0'))
268 AND (COALESCE(Cur_OrderDup.m_attributesetinstance_id,'0')=COALESCE(Cur_OrderDup1.m_attributesetinstance_id,'0')) AND (COALESCE(Cur_OrderDup.quantityorder,0)=COALESCE(Cur_OrderDup1.quantityorder,0))
269 AND (COALESCE(Cur_OrderDup.m_product_uom_id,'0')=COALESCE(Cur_OrderDup1.m_product_uom_id,'0'))
270 AND (COALESCE(Cur_OrderDup.m_offer_id,'0')=COALESCE(Cur_OrderDup1.m_offer_id,'0'))
271 AND (COALESCE(Cur_OrderDup.pricestd,0)=COALESCE(Cur_OrderDup1.pricestd,0))
272 AND (COALESCE(Cur_OrderDup.cancelpricead,'0')=COALESCE(Cur_OrderDup1.cancelpricead,'0'))
273 AND (COALESCE(Cur_OrderDup.c_order_discount_id,'0')=COALESCE(Cur_OrderDup1.c_order_discount_id,'0'))
274 AND (COALESCE(Cur_OrderDup.em_hs_knitprice,0)=COALESCE(Cur_OrderDup1.em_hs_knitprice,0))
275 AND (COALESCE(Cur_OrderDup.em_hs_orderedbales,0)=COALESCE(Cur_OrderDup1.em_hs_orderedbales,0))
276 --AND (COALESCE(Cur_OrderDup.em_hs_lastunitprice,0)=COALESCE(Cur_OrderDup1.em_hs_lastunitprice,0))
277 --AND (COALESCE(Cur_OrderDup.em_hs_c_bpartner_id,'0')=COALESCE(Cur_OrderDup1.em_hs_c_bpartner_id,'0'))
278 --AND (COALESCE(Cur_OrderDup.em_hs_lastpurchasedate,v_DateNull)=COALESCE(Cur_OrderDup1.em_hs_lastpurchasedate,v_DateNull)) AND (COALESCE(Cur_OrderDup.em_hs_reservedbales,0)=COALESCE(Cur_OrderDup1.em_hs_reservedbales,0)) AND Cur_OrderDup.em_hs_deliveredbales=Cur_OrderDup1.em_hs_deliveredbales
279 AND (COALESCE(Cur_OrderDup.em_hs_invoicedbales,0)=COALESCE(Cur_OrderDup1.em_hs_invoicedbales,0))
280
281 ) THEN
282
283 --RAISE NOTICE '%', 'Inside Loop' || v_count1;
284 delete from c_orderline where c_orderline_id=Cur_OrderDup.c_orderline_id;
285 --v_count1=v_count1+1;
286 END IF;
287
288 --EXIT WHEN v_count1 > 0;
289 END LOOP;
290 END LOOP;
291 END IF;
292
293 END LOOP;
294
295 END IF;
296 END IF;
297
298
299
300
301
302
303
304
305
306
307
308 /** Q3 Added
309 * Check if order has lines
310 */
311 IF (v_DocAction = 'CO') THEN
312
313 SELECT em_hs_creforder
314 INTO v_COrderId
315 FROM C_ORDER
316 WHERE C_ORDER_ID = v_Record_ID;
317
318 RAISE NOTICE '%', 'v_COrderId'||v_COrderId; --OBTG:-20000--
319
320 IF(v_COrderId IS NOT NULL) THEN
321
322 SELECT sum(QtyOrdered)
323 INTO v_QtyOrdered
324 FROM C_ORDERLINE
325 WHERE C_ORDER_ID = v_COrderId;
326
327 RAISE NOTICE '%', 'v_QtyOrdered'||v_QtyOrdered; --OBTG:-20000--
328
329 SELECT sum(QtyOrdered)
330 INTO v_QtyPOOrdered
331 FROM C_ORDERLINE
332 WHERE C_ORDER_ID = v_Record_ID;
333
334 RAISE NOTICE '%', 'v_QtyPOOrdered'||v_QtyPOOrdered; --OBTG:-20000--
335
336 IF v_QtyPOOrdered > v_QtyOrdered THEN
337 RAISE EXCEPTION '%', '@PO Order Quantity Is More Than Sale Order Quantity@'; --OBTG:-20000--
338 END IF;
339
340/**Including pre paid order check
341*/
342 select em_hs_prepaidorder,c_doctype_id into v_check,v_doctypeid from c_order
343 where c_order_id=v_COrderId;
344 select name into v_doctypename from c_doctype
345 where c_doctype_id=v_doctypeid;
346
347 IF v_check = 'Y' AND v_doctypename ilike 'Grey%' THEN
348
349 FOR Cur_orderline IN
350 (SELECT *
351 FROM C_ORDERLINE
352 WHERE C_ORDER_ID=v_record_id
353 FOR UPDATE
354 )
355 LOOP
356
357 FOR Cur_InOutLine IN
358 (SELECT *
359 FROM C_ORDERLINE
360 WHERE C_ORDER_ID=v_COrderId
361 FOR UPDATE
362 )
363 LOOP
364
365IF Cur_orderline.m_product_id = Cur_InoutLine.m_product_id THEN
366
367 select priceactual,qtyordered into v_tempunit, v_qtyordered1 from c_orderline
368 where c_orderline_id=Cur_InOutLine.c_orderline_id;
369
370 select qtyordered into v_qtyordered2 from c_orderline
371 where c_orderline_id=Cur_orderline.c_orderline_id;
372
373 select sum(taxamt) into v_taxamt
374 from hs_c_orderlinetax
375 where c_orderline_id=Cur_InOutLine.c_orderline_id;
376
377 v_taxamt=(v_taxamt/v_qtyordered1)*v_qtyordered2;
378
379 v_tempamt=v_tempunit*v_qtyordered2;
380 v_lineamt=v_lineamt+v_tempamt+v_taxamt;
381
382END IF;
383 END LOOP;
384
385 END LOOP; /*PO lines loop */
386
387select * from c_order INTO Cur_inout where c_order_id=v_corderid;
388select * from c_debt_payment INTO Cur_Payment where c_bpartner_id=Cur_InOut.c_bpartner_id
389and isreceipt='Y' and c_settlement_cancel_id is null and amount<0 and isvalid='Y';
390IF Cur_Payment is null THEN
391v_advance=0;
392ELSE
393v_advance=Cur_Payment.amount;
394END IF;
395 IF(v_lineamt+v_advance > 0) THEN
396 select name into v_bpartnername from c_bpartner where c_bpartner_id=Cur_InOut.C_bpartner_id ;
397 RAISE EXCEPTION '%', '@Advanced Payment not made by @' || v_bpartnername ; --OBTG:-20000--
398 END IF;
399
400
401 END IF;/*grey and y check ends here*/
402
403 END IF;
404
405 END IF;
406
407 /**Q3 End**/
408
409
410 /**
411 * Order Closed, Voided or Reversed - No action possible
412 */
413 IF(v_DocStatus IN('CL', 'VO', 'RE')) THEN
414 RAISE EXCEPTION '%', '@AlreadyPosted@' ; --OBTG:-20000--
415 END IF;
416 IF(NOT FINISH_PROCESS) THEN
417 /**
418 * Waiting on Prepayment can only be closed
419 */
420 IF(v_DocStatus='WP' AND v_DocAction<>'CL') THEN
421 RAISE EXCEPTION '%', '@WaitingPayment@' ; --OBTG:-20000--
422 END IF;
423 END IF;--FINISH_PROCESS
424 SELECT DocSubTypeSO
425 INTO v_DocSubTypeSOTarget
426 FROM C_DOCTYPE
427 WHERE C_DocType_ID=v_DocTypetarget_ID;
428 IF (v_DocSubTypeSOTarget='PR' AND v_invoicerule <> 'I') THEN
429 RAISE EXCEPTION '%', '@PrepayMustImmediate@'; --OBTG:-20000--
430 END IF;
431 IF(NOT FINISH_PROCESS) THEN
432 /**
433 * Unlock
434 */
435 IF(v_DocAction='XL') THEN
436 UPDATE C_ORDER
437 SET Processing='N',
438 DocAction='--',
439 Updated=TO_DATE(NOW())
440 WHERE C_Order_ID=v_Record_ID;
441 FINISH_PROCESS:=TRUE;
442 END IF;
443 END IF;--FINISH_PROCESS
444 IF(NOT FINISH_PROCESS) THEN
445 IF(v_IsProcessing='Y') THEN
446 RAISE EXCEPTION '%', '@OtherProcessActive@' ; --OBTG:-20000--
447 END IF;
448 END IF;--FINISH_PROCESS
449 IF(NOT FINISH_PROCESS) THEN
450 /**
451 * Re-activate
452 */
453 IF(v_DocAction='RE') THEN
454 IF (v_DocSubTypeSO IN ('WR', 'WI')) THEN
455 RAISE EXCEPTION '%', '@ActionNotSupported@'; --OBTG:-20000--
456 END IF;
457 --Verify not managed debtPayments added by ALO
458 --Added by P.Sarobe. New messages
459 SELECT max(c_debt_payment_id), COUNT(*)
460 INTO v_Debtpayment_ID, v_Aux
461 FROM C_DEBT_PAYMENT
462 WHERE C_Order_ID=v_Record_ID
463 AND C_Debt_Payment_Status(C_Settlement_Cancel_ID, Cancel_Processed, Generate_Processed, IsPaid, IsValid, C_CashLine_ID, C_BankStatementLine_ID)!='P';
464 IF v_Aux!=0 THEN
465 --Added by P.Sarobe. New messages
466 SELECT c_Bankstatementline_Id, c_cashline_id, c_settlement_cancel_id, ispaid, cancel_processed
467 INTO v_Bankstatementline_ID, v_CashLine_ID, v_Settlement_Cancel_ID, v_ispaid, v_Cancel_Processed
468 FROM C_DEBT_PAYMENT WHERE C_Debt_Payment_ID = v_Debtpayment_ID;
469 IF v_Bankstatementline_ID IS NOT NULL THEN
470 SELECT C_BANKSTATEMENT.NAME, C_BANKSTATEMENT.STATEMENTDATE
471 INTO v_nameBankstatement, v_dateBankstatement
472 FROM C_BANKSTATEMENT, C_BANKSTATEMENTLINE
473 WHERE C_BANKSTATEMENT.C_BANKSTATEMENT_ID = C_BANKSTATEMENTLINE.C_BANKSTATEMENT_ID
474 AND C_BANKSTATEMENTLINE.C_BANKSTATEMENTLINE_ID = v_Bankstatementline_ID;
475 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderBank@'||v_nameBankstatement||' '||'@Bydate@'||v_dateBankstatement ; --OBTG:-20000--
476 END IF;
477 IF v_CashLine_ID IS NOT NULL THEN
478 SELECT C_CASH.NAME, C_CASH.STATEMENTDATE
479 INTO v_nameCash, v_dateCash
480 FROM C_CASH, C_CASHLINE
481 WHERE C_CASH.C_CASH_ID = C_CASHLINE.C_CASH_ID
482 AND C_CASHLINE.C_CASHLINE_ID = v_CashLine_ID;
483 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderCash@'||v_nameCash||' '||'@Bydate@'||v_dateCash ; --OBTG:-20000--
484 END IF;
485 IF v_Cancel_Processed='Y' AND v_ispaid='N' THEN
486 SELECT documentno, datetrx
487 INTO v_documentno_Settlement, v_dateSettlement
488 FROM C_SETTLEMENT
489 WHERE C_SETTLEMENT_ID = v_Settlement_Cancel_ID;
490 RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderCancel@'||v_documentno_Settlement||' '||'@Bydate@'||v_dateSettlement ; --OBTG:-20000--
491 END IF;
492 END IF;
493 IF(NOT FINISH_PROCESS) THEN
494 RAISE NOTICE '%','Re-Activating ' || v_DocSubTypeSO || ': ' || v_Record_ID ;
495 IF(v_DocSubTypeSO IN('WI', 'WP', 'WR')) THEN
496 -- Cancel existing Deli very + Invoice Documents
497 PERFORM M_INOUT_CANCEL(NULL, v_Record_ID) ;
498 IF(v_DocSubTypeSO<>'WP') THEN
499 PERFORM C_INVOICE_CANCEL(NULL, v_Record_ID);
500 END IF;
501 END IF;
502 -- Update Order
503 v_ResultStr:='ReActivate';
504 UPDATE C_ORDER
505 SET DocStatus='IP', -- In Progress
506 DocAction='CO',
507 Processing='N',
508 Processed='N',
509 Updated=TO_DATE(NOW())
510 WHERE C_Order_ID=v_Record_ID;
511 DELETE
512 FROM C_ORDERLINE
513 WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
514 AND C_ORDER_ID=v_Record_ID;
515 --ADDED BY E.ARGAL
516 --Invalidate debt payments added by ALO
517 UPDATE C_DEBT_PAYMENT
518 SET IsValid='N'
519 WHERE C_Order_ID=v_Record_ID
520 AND IsAutomaticGenerated='N';
521
522 DELETE
523 FROM C_CASHLINE
524 WHERE C_Debt_Payment_Id IN
525 (SELECT C_DEBT_PAYMENT_ID
526 FROM C_DEBT_PAYMENT
527 WHERE C_Order_ID=v_Record_ID
528 AND COALESCE(IsAutomaticGenerated, 'Y')='Y'
529 )
530 ;
531 DELETE
532 FROM C_DEBT_PAYMENT
533 WHERE C_Order_ID=v_Record_ID
534 AND COALESCE(IsAutomaticGenerated, 'Y')='Y';
535 DECLARE
536 Cur_ResLine RECORD;
537 v_QtySO NUMERIC; -- Reserved
538 v_QtyOrderSO NUMERIC;
539 v_QtyPO NUMERIC; -- Ordered
540 v_QtyOrderPO NUMERIC;
541 BEGIN
542 v_ResultStr:='ReserveInventory';
543 -- For all lines needing reservation
544 FOR Cur_ResLine IN
545 (SELECT l.M_Warehouse_ID,
546 l.M_Product_ID,
547 l.M_AttributeSetInstance_ID,
548 l.C_OrderLine_ID,
549 -- Target Level = 0 if DirectShip='Y' or Binding='N'
550 l.QtyOrdered AS Qty,
551 l.QUANTITYORDER,
552 l.qtyreserved,
553 l.qtydelivered,
554 l.C_UOM_ID,
555 l.M_PRODUCT_UOM_ID
556 FROM C_ORDERLINE l,
557 M_PRODUCT p
558 WHERE l.C_Order_ID=v_Record_ID -- Reserve Products (not: services, null products)
559 AND l.M_Product_ID=p.M_Product_ID
560 AND p.IsStocked='Y'
561 AND p.ProductType='I' FOR UPDATE
562 )
563 LOOP
564 -- Qty corrected for SO/PO
565 IF(v_DocSubTypeSO IS NULL) THEN
566 v_QtySO:=0;
567 v_QtyOrderSO:=NULL;
568 v_QtyPO:=Cur_ResLine.qtydelivered-Cur_ResLine.qty;
569 v_QtyOrderPO:=NULL;
570 IF (Cur_ResLine.QtyDelivered=0) THEN
571 v_QtyOrderPO := -Cur_ResLine.QuantityOrder;
572 ELSIF Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
573 v_QtyOrderPO := -C_Uom_Convert(v_QtyPO, Cur_ResLine.C_UOM_ID, Cur_ResLine.M_Product_UOM_ID, 'Y');
574 END IF;
575 ELSE
576 v_QtySO:=-Cur_ResLine.QtyReserved;
577 IF (Cur_ResLine.QtyReserved=Cur_ResLine.Qty) THEN
578 v_QtyOrderSO := -Cur_ResLine.QuantityOrder;
579 ELSIF Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
580 v_QtyOrderSO := -C_Uom_Convert(v_QtySO, Cur_ResLine.C_UOM_ID, Cur_ResLine.M_Product_UOM_ID, 'Y');
581 END IF;
582 v_QtyPO:=0;
583 v_QtyOrderPO:=NULL;
584 END IF;
585 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) ;
586 END LOOP;
587 -- Set reserved quantity to 0
588 UPDATE C_ORDERLINE
589 SET QtyReserved = 0
590 WHERE c_orderline_id IN (select c_orderline_id
591 from c_orderline
592 where c_order_id = v_Record_id);
593
594 END;
595 END IF;--FINISH_PROCESS
596 IF(NOT FINISH_PROCESS) THEN
597 --ADDED BY P.SAROBE but to be deprecated 26052007
598 SELECT MAX(C_CASHLINE_ID)
599 INTO v_CashLine_ID
600 FROM C_CASHLINE
601 WHERE C_ORDER_ID=v_Record_ID;
602 IF(v_CashLine_ID IS NOT NULL) THEN
603 SELECT PROCESSED
604 INTO v_IsProcessed
605 FROM C_CASH,
606 C_CASHLINE
607 WHERE C_CASH.C_CASH_ID=C_CASHLINE.C_CASH_ID
608 AND C_CASHLINE_ID=v_CashLine_ID;
609 IF(v_IsProcessed='N') THEN
610 DELETE FROM C_CASHLINE WHERE C_CASHLINE_ID=v_CashLine_ID;
611 ELSE
612 SELECT C_CASH.NAME, C_CASH.STATEMENTDATE, C_CASHLINE.LINE
613 INTO v_nameCash, v_dateCash, v_Line
614 FROM C_CASH, C_CASHLINE
615 WHERE C_CASH.C_CASH_ID = C_CASHLINE.C_CASH_ID
616 AND C_CASHLINE.C_CASHLINE_ID = v_CashLine_ID;
617 RAISE EXCEPTION '%', '@Ordercahslineprocessed@'||v_nameCash||' '||'@Bydate@'||v_dateCash||' '||'@line@'||v_Line ; --OBTG:-20000--
618
619 END IF;
620 END IF;--FINISH_PROCESS
621 END IF;
622 /*Q3 Added*/
623 IF(v_isSoTrx='N') THEN
624 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID;
625
626 IF(v_c_debt_payment IS NOT NULL) THEN
627 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID and c_cashline_id is null and c_bankstatementline_id is null and c_settlement_cancel_id is null;
628 IF(v_c_debt_payment IS NULL) THEN
629
630 RAISE EXCEPTION '%', '@Payment Is Processed@' ; --OBTG:-20000--
631 ELSE
632 Delete from c_debt_payment where c_debt_payment_id=v_c_debt_payment;
633 END IF;
634 END IF;
635
636 END IF;
637 /*Q3 End*/
638
639
640
641
642
643
644
645
646
647 UPDATE C_ORDER
648 SET DocStatus='DR', -- Draft
649 DocAction='CO',
650 Processing='N',
651 Updated=TO_DATE(NOW())
652 WHERE C_Order_ID=v_Record_ID;
653 FINISH_PROCESS:=TRUE;
654
655 END IF;
656 END IF;--FINISH_PROCESS
657 IF(NOT FINISH_PROCESS) THEN
658 SELECT COUNT(*)
659 INTO v_Count
660 FROM C_ORDER C,
661 C_DOCTYPE
662 WHERE C_DocType.DocBaseType IN ('SOO', 'POO')
663 AND C_DocType.IsSOTrx=C.ISSOTRX
664 AND AD_ISORGINCLUDED(C.AD_Org_ID,C_DocType.AD_Org_ID, C.AD_Client_ID) <> -1
665 AND C.C_DOCTYPETARGET_ID = C_DOCTYPE.C_DOCTYPE_ID
666 AND C.C_ORDER_ID = v_Record_ID;
667 IF v_Count=0 THEN
668 RAISE EXCEPTION '%', '@NotCorrectOrgDoctypeOrder@' ; --OBTG:-20000--
669 END IF;
670
671 -- Set org lines like the headear
672 UPDATE C_ORDERLINE
673 SET AD_ORG_ID = (SELECT AD_ORG_ID FROM C_ORDER WHERE C_ORDER_ID = v_Record_ID)
674 WHERE C_ORDER_ID = v_Record_ID;
675
676 /**
677 * Close Order - prepare
678 */
679 IF(v_DocAction='CL') THEN
680 -- Cancel undelivered Items
681 IF(v_isSoTrx='Y') THEN --Sales orders
682 UPDATE C_ORDERLINE
683 SET QtyOrdered=QtyDelivered,
684 --MODIFIED BY P.SAROBE
685 linenetamt=QtyDelivered*priceactual,
686 --MODIFIED BY F.IRIAZABAL
687 QuantityOrder=QuantityOrder,
688 Updated=TO_DATE(NOW())
689 WHERE C_Order_ID=v_Record_ID
690 AND QtyOrdered<>QtyDelivered;
691 -- For Purchase orders, M_MatchPO table used. Notice that only delivered lines(C_Invoiceline_Id is null) using
692 ELSE
693 UPDATE C_ORDERLINE
694 SET QTYORDERED=COALESCE
695 (
696 (SELECT SUM(M_MATCHPO.QTY)
697 FROM M_MATCHPO
698 WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID
699 AND M_MATCHPO.C_INVOICELINE_ID IS NULL
700 )
701 , 0
702 )
703 ,
704 QuantityOrder=C_ORDERLINE.QuantityOrder,
705 Updated=TO_DATE(NOW()),
706 linenetamt=COALESCE
707 (
708 (SELECT SUM(M_MATCHPO.QTY)
709 FROM M_MATCHPO
710 WHERE M_MATCHPO.C_ORDERLINE_ID=C_ORDERLINE.C_ORDERLINE_ID
711 AND M_MATCHPO.C_INVOICELINE_ID IS NULL
712 )
713 , 0
714 )
715 *priceactual
716 WHERE C_ORDERLINE.C_ORDER_ID=v_Record_ID;
717 END IF;
718 -- if there is no change, the tax calculation, etc. is not needed.
719 -- potential problem, if posted (i.e. encumbered) for full amount
720 -- and the rest then cancelled out.
721 END IF;
722 END IF;--FINISH_PROCESS
723 IF(NOT FINISH_PROCESS) THEN
724 /**
725 * Void Order - prepare
726 */
727 IF(v_DocAction='VO') THEN
728 -- Cancel all Items
729 UPDATE C_ORDERLINE
730 SET QtyOrdered=0,
731 --MODIFIED BY F.IRIAZABAL
732 QuantityOrder=
733 CASE WHEN M_PRODUCT_UOM_ID IS NULL THEN NULL ELSE 0
734 END
735 ,
736 LineNetAmt=0,
737 Updated=TO_DATE(NOW())
738 WHERE C_Order_ID=v_Record_ID
739 AND QtyOrdered<>0;
740 END IF;
741 END IF;--FINISH_PROCESS
742 IF(NOT FINISH_PROCESS) THEN
743 /**************************************************************************
744 * Start Processing ------------------------------------------------------
745 *************************************************************************/
746 /**************************************************************************
747 * Start Processing ------------------------------------------------------
748 *************************************************************************/
749 -- Check the header belongs to a organization where transactions are posible and ready to use
750 SELECT AD_Org.IsReady, Ad_OrgType.IsTransactionsAllowed
751 INTO v_is_ready, v_is_tr_allow
752 FROM C_ORDER, AD_Org, AD_OrgType
753 WHERE AD_Org.AD_Org_ID=C_ORDER.AD_Org_ID
754 AND AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
755 AND C_ORDER.C_ORDER_ID=v_Record_ID;
756 IF (v_is_ready='N') THEN
757 RAISE EXCEPTION '%', '@OrgHeaderNotReady@'; --OBTG:-20000--
758 END IF;
759 IF (v_is_tr_allow='N') THEN
760 RAISE EXCEPTION '%', '@OrgHeaderNotTransAllowed@'; --OBTG:-20000--
761 END IF;
762
763 SELECT AD_ORG_CHK_DOCUMENTS('C_ORDER', 'C_ORDERLINE', v_Record_ID, 'C_ORDER_ID', 'C_ORDER_ID') INTO v_is_included FROM dual;
764 IF (v_is_included=-1) THEN
765 RAISE EXCEPTION '%', '@LinesAndHeaderDifferentLEorBU@'; --OBTG:-20000--
766 END IF;
767
768
769 IF(p_PInstance_ID IS NOT NULL) THEN
770 v_ResultStr:='LockingOrder';
771 UPDATE C_ORDER SET Processing='Y' WHERE C_Order_ID=v_Record_ID;
772 -- COMMIT;
773 END IF;
774 -- Now, needs to go to END_PROCESSING to unlock
775 /**
776 * Allowed Actions: AProve, COmplete, PRocess, CLose, VOid
777 */
778 IF(v_DocAction IN('AP', 'CO', 'PR', 'CL', 'VO')) THEN
779 NULL;
780 ELSE
781 RAISE EXCEPTION '%', '@ActionNotAllowedHere@' ; --OBTG:-20000--
782 END IF;
783 END IF;--END_PROCESSING
784 IF(NOT END_PROCESSING AND NOT FINISH_PROCESS) THEN
785 SELECT COUNT(*)
786 INTO v_count
787 FROM AD_CLIENTINFO
788 WHERE AD_CLIENT_ID=v_Client_ID
789 AND CHECKORDERORG='Y';
790 IF v_count>0 THEN
791 v_ResultStr:='CheckingRestrictions - C_ORDER ORG IS IN C_BPARTNER ORG TREE';
792 SELECT COUNT(*)
793 INTO v_count
794 FROM C_ORDER c,
795 C_BPARTNER bp
796 WHERE c.C_Order_ID=v_Record_ID
797 AND c.C_BPARTNER_ID=bp.C_BPARTNER_ID
798 AND Ad_Isorgincluded(c.AD_ORG_ID, bp.AD_ORG_ID, bp.AD_CLIENT_ID)=-1;
799 IF v_count>0 THEN
800 RAISE EXCEPTION '%', '@NotCorrectOrgBpartnerOrder@' ; --OBTG:-20000--
801 END IF;
802 END IF;
803 END IF;--END_PROCESSING
804 IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
805 /**************************************************************************
806 * Calculate Discounts
807 *************************************************************************/
808 v_CumDiscount:=0;
809 v_OldCumDiscount:=0;
810 v_Line:=10;
811 SELECT MAX(LINE) +10
812 INTO v_OrderLineSeqNo
813 FROM C_ORDERLINE
814 WHERE C_ORDER_ID=v_Record_ID;
815 FOR Cur_COrderDiscount IN
816 (SELECT C_ORDER_DISCOUNT.C_ORDER_DISCOUNT_ID,
817 C_DISCOUNT.DISCOUNT,
818 C_DISCOUNT.M_PRODUCT_ID,
819 C_DISCOUNT.NAME,
820 C_ORDER_DISCOUNT.CASCADE,
821 C_DISCOUNT.C_DISCOUNT_ID,
822 M_PRODUCT.C_UOM_ID
823 FROM C_ORDER_DISCOUNT,
824 C_DISCOUNT,
825 M_PRODUCT
826 WHERE C_ORDER_DISCOUNT.C_DISCOUNT_ID=C_DISCOUNT.C_DISCOUNT_ID
827 AND C_DISCOUNT.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
828 AND C_ORDER_DISCOUNT.C_ORDER_ID=v_Record_ID
829 AND C_ORDER_DISCOUNT.ISACTIVE='Y'
830 ORDER BY C_ORDER_DISCOUNT.LINE
831 )
832 LOOP
833 v_CumDiscount:=(1-v_OldCumDiscount) * Cur_COrderDiscount.Discount/100;
834 v_OldCumDiscount:=v_OldCumDiscount + v_CumDiscount;
835 FOR Cur_TaxDiscount IN
836 (SELECT C_ORDERLINE.C_TAX_ID,
837 SUM(C_ORDERLINE.LINENETAMT) AS LINENETAMT
838 FROM C_ORDERLINE
839 WHERE C_ORDER_ID=v_Record_ID
840 AND C_ORDERLINE.LINENETAMT<>0
841 AND C_ORDER_DISCOUNT_ID IS NULL
842 GROUP BY C_TAX_ID
843 )
844 LOOP
845 IF(Cur_COrderDiscount.CASCADE='Y') THEN
846 v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * v_CumDiscount;
847 ELSE
848 v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * Cur_COrderDiscount.Discount/100;
849 END IF;
850 v_OrderLineSeqNo:=10 + v_OrderLineSeqNo;
851 v_OrderLine:=get_uuid() ;
852
853 INSERT INTO c_orderline
854 (
855 c_orderline_id, ad_client_id, ad_org_id, isactive, created, createdby,
856 updated, updatedby, c_order_id, line, c_bpartner_id, c_bpartner_location_id,
857 dateordered, datepromised, datedelivered, dateinvoiced, description,
858 m_product_id, m_warehouse_id, directship, c_uom_id, qtyordered,
859 qtyreserved, qtydelivered, qtyinvoiced, m_shipper_id, c_currency_id,
860 pricelist, priceactual, pricelimit, linenetamt, discount, freightamt,
861 c_charge_id, chargeamt, c_tax_id, s_resourceassignment_id, ref_orderline_id,
862 m_attributesetinstance_id, isdescription, quantityorder, m_product_uom_id,
863 m_offer_id, pricestd, C_ORDER_DISCOUNT_ID
864 )
865 VALUES
866 (
867 v_OrderLine, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy,
868 TO_DATE(NOW()), v_UpdatedBy, v_Record_ID, v_OrderLineSeqNo, NULL, NULL,
869 TO_DATE(NOW()), TO_DATE(NOW()), TO_DATE(NOW()), TO_DATE(NOW()), Cur_COrderDiscount.NAME,
870 Cur_COrderDiscount.M_PRODUCT_ID, v_M_Warehouse_ID, 'N', Cur_COrderDiscount.C_UOM_ID, 1,
871 0, 0, 0, NULL, v_c_currency_id,
872 v_Discount, v_Discount, v_Discount, v_Discount, 0, 0,
873 NULL, NULL, Cur_TaxDiscount.C_TAX_ID, NULL, NULL,
874 NULL, 'N', NULL, NULL,
875 NULL, v_Discount, NULL
876 );
877
878 UPDATE C_ORDERLINE
879 SET C_ORDER_DISCOUNT_ID=Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
880 WHERE C_ORDERLINE_ID=v_OrderLine;
881
882
883 END LOOP;
884 v_Line:=v_Line + 10;
885 END LOOP;
886 END IF;
887
888 IF(NOT END_PROCESSING AND NOT FINISH_PROCESS) THEN
889 /**
890 * Convert to Target DocType
891 */
892 DECLARE
893 v_DocSubTypeSO_Target VARCHAR(60) ;
894 BEGIN
895 v_ResultStr:='ConvertingDocType';
896 IF(v_DocType_ID<>v_DocTypeTarget_ID) THEN
897 -- New
898 IF(v_DocStatus='DR' OR v_DocType_ID='0') THEN
899 -- Update to Target Document Type
900 WHILE(v_DocType_ID<>v_DocTypeTarget_ID)
901 LOOP
902 BEGIN
903 v_ResultStr:='UpdateDocType';
904 UPDATE C_ORDER
905 SET C_DocType_ID=v_DocTypeTarget_ID
906 WHERE C_Order_ID=v_Record_ID;
907 v_DocType_ID:=v_DocTypeTarget_ID;
908 EXCEPTION
909 WHEN OTHERS THEN
910 v_ResultStr:='UpdateDocumentNo';
911 UPDATE C_ORDER SET DocumentNo=DocumentNo || '.' WHERE C_Order_ID=v_Record_ID;
912 END;
913 END LOOP;
914 ELSE
915 v_ResultStr:='GetTargetDocType';
916 SELECT DocSubTypeSO
917 INTO v_DocSubTypeSO_Target
918 FROM C_DOCTYPE
919 WHERE C_DocType_ID=v_DocTypeTarget_ID;
920 RAISE NOTICE '%','Changing DocType from ' || v_DocSubTypeSO || ' to ' || v_DocSubTypeSO_Target ;
921 -- Change Offer to anything, Change InProcess to anything
922 IF(v_DocSubTypeSO IN('ON', 'OB') OR v_DocStatus='IP') THEN
923 -- Update to Target Document Type
924 WHILE(v_DocType_ID<>v_DocTypeTarget_ID)
925 LOOP
926 BEGIN
927 v_ResultStr:='UpdateDocType';
928 UPDATE C_ORDER
929 SET C_DocType_ID=v_DocTypeTarget_ID
930 WHERE C_Order_ID=v_Record_ID;
931 v_DocType_ID:=v_DocTypeTarget_ID;
932 EXCEPTION
933 WHEN OTHERS THEN
934 v_ResultStr:='UpdateDocumentNo';
935 UPDATE C_ORDER SET DocumentNo=DocumentNo || '.' WHERE C_Order_ID=v_Record_ID;
936 END;
937 END LOOP;
938 ELSE
939 -- Change Back
940 UPDATE C_ORDER
941 SET C_DocTypeTarget_ID=v_DocType_ID
942 WHERE C_Order_ID=v_Record_ID;
943 RAISE EXCEPTION '%', '@CannotChangeDocType@' ; --OBTG:-20000--
944 END IF;
945 END IF;
946 END IF; -- C_DocType_ID <> C_DocTypeTarget_ID
947 END; -- Conversion
948END IF;--FINISH_PROCESS
949IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
950 /**
951 * Get DocSubTypeSO + Is it Binding :1:2
952 */
953 v_ResultStr:='TestBinding DocType_ID=' || v_DocType_ID;
954 SELECT
955 CASE DocSubTypeSO WHEN 'ON' THEN 'N' ELSE 'Y'
956 END
957 ,
958 DocSubTypeSO
959 INTO v_IsBinding,
960 v_DocSubTypeSO
961 FROM C_DOCTYPE
962 WHERE C_DocType_ID=v_DocType_ID;
963 RAISE NOTICE '%','DocSubTypeSO=' || v_DocSubTypeSO || ' IsBinding=' || v_IsBinding ;
964END IF;--FINISH_PROCESS
965IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
966 /**************************************************************************
967 * Resolve not-stocked BOMs
968 *************************************************************************/
969 DECLARE
970 -- Order Lines with non-stocked BOMs
971 CUR_BOM_Line RECORD;
972 -- BOM Product List
973 CUR_BOM RECORD;
974 --
975 CountNo NUMERIC;
976 v_PriceList_Version_ID VARCHAR(32); --OBTG:VARCHAR2--
977 NextNo VARCHAR(32); --OBTG:varchar2--
978 v_Line NUMERIC;
979 v_FreightAmt NUMERIC;
980 v_ChargeAmt NUMERIC;
981 v_Parner_ID VARCHAR(32); --OBTG:VARCHAR2--
982 --v_PriceList_ID VARCHAR2(32);
983 --
984 BEGIN
985 LOOP
986 -- How many BOMs do we have:3
987 SELECT COUNT(*)
988 INTO CountNo
989 FROM C_ORDERLINE l
990 WHERE l.C_Order_ID=v_Record_ID
991 AND EXISTS
992 (SELECT *
993 FROM M_PRODUCT p
994 WHERE l.M_Product_ID=p.M_Product_ID
995 AND p.IsBOM='Y'
996 AND p.IsStocked='N'
997 )
998 ;
999 -- Nothing to do:4
1000 EXIT WHEN CountNo=0;
1001 RAISE NOTICE '%',' BOMs to resolve=' || CountNo ;
1002 DECLARE
1003 Cur_MPriceListVersion RECORD;
1004 BEGIN
1005 -- last shipment
1006 FOR Cur_MPriceListVersion IN
1007 (SELECT COALESCE(v.M_PriceList_Version_ID, '0') AS PriceList_Version_ID, o.M_PriceList_ID
1008 FROM M_PRICELIST_VERSION v,
1009 C_ORDER o
1010 WHERE v.M_PriceList_ID=o.M_PriceList_ID
1011 AND v.ValidFrom<=o.DateOrdered
1012 AND v.IsActive='Y'
1013 AND o.C_Order_ID=v_Record_ID
1014 ORDER BY v.ValidFrom DESC
1015 )
1016 LOOP
1017 v_PriceList_Version_ID:=Cur_MPriceListVersion.PriceList_Version_ID;
1018 -- v_PriceList_ID := Cur_MPriceListVersion.M_PriceList_Id;
1019 EXIT;
1020 END LOOP;
1021 END;
1022 -- Get Price List Version
1023 -- Replace Lines
1024 FOR CUR_BOM_Line IN
1025 (SELECT *
1026 FROM C_ORDERLINE l
1027 WHERE l.C_Order_ID=v_Record_ID
1028 AND IsActive='Y'
1029 AND EXISTS
1030 (SELECT *
1031 FROM M_PRODUCT p
1032 WHERE l.M_Product_ID=p.M_Product_ID
1033 AND p.IsBOM='Y'
1034 AND p.IsStocked='N'
1035 )
1036 ORDER BY l.Line FOR UPDATE
1037 )
1038 LOOP
1039 v_Line:=CUR_BOM_Line.Line;
1040 -- One Time variables
1041 v_FreightAmt:=CUR_BOM_Line.FreightAmt;
1042 v_ChargeAmt:=CUR_BOM_Line.ChargeAmt;
1043 -- Create New Lines
1044 FOR CUR_BOM IN
1045 (SELECT b.M_ProductBOM_ID,
1046 p.C_UOM_ID,
1047 b.BOMQty,
1048 b.Description
1049 FROM M_PRODUCT_BOM b
1050 INNER JOIN M_PRODUCT p
1051 ON(b.M_ProductBOM_ID=p.M_Product_ID)
1052 WHERE b.M_Product_ID=CUR_BOM_Line.M_Product_ID
1053 ORDER BY Line
1054 )
1055 LOOP
1056 SELECT * INTO NextNo FROM Ad_Sequence_Next('C_OrderLine', CUR_BOM_Line.AD_Client_ID) ;
1057 v_Line:=v_Line + 1;
1058 INSERT
1059 INTO C_ORDERLINE
1060 (
1061 C_OrderLine_ID, AD_Client_ID, AD_Org_ID, IsActive,
1062 Created, CreatedBy, Updated, UpdatedBy,
1063 C_Order_ID, Line, C_BPartner_ID, C_BPartner_Location_ID,
1064 DateOrdered, DatePromised, DateDelivered, DateInvoiced,
1065 Description, M_Product_ID, M_Warehouse_ID, DirectShip,
1066 C_UOM_ID, QtyOrdered, QtyReserved, QtyDelivered,
1067 QtyInvoiced, M_Shipper_ID, C_Currency_ID,
1068 PriceList,
1069 PriceActual,
1070 PriceLimit,
1071 LineNetAmt, Discount,
1072 FreightAmt, C_Charge_ID, ChargeAmt, C_Tax_ID,
1073 QuantityOrder, M_Product_Uom_ID, PriceStd
1074 )
1075 VALUES
1076 (
1077 NextNo, CUR_BOM_Line.AD_Client_ID, CUR_BOM_Line.AD_Org_ID, CUR_BOM_Line.IsActive,
1078 TO_DATE(NOW()), '0', TO_DATE(NOW()), '0',
1079 CUR_BOM_Line.C_Order_ID, v_Line, CUR_BOM_Line.C_BPartner_ID, CUR_BOM_Line.C_BPartner_Location_ID,
1080 CUR_BOM_Line.DateOrdered, CUR_BOM_Line.DatePromised, CUR_BOM_Line.DateDelivered, CUR_BOM_Line.DateInvoiced,
1081 CUR_BOM.Description, CUR_BOM.M_ProductBOM_ID, CUR_BOM_Line.M_Warehouse_ID, CUR_BOM_Line.DirectShip,
1082 CUR_BOM.C_UOM_ID, CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty, CUR_BOM_Line.QtyReserved*CUR_BOM.BOMQty, CUR_BOM_Line.QtyDelivered*CUR_BOM.BOMQty,
1083 CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, CUR_BOM_Line.M_Shipper_ID, CUR_BOM_Line.C_Currency_ID,
1084 M_BOM_PriceList(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),
1085 M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID), M_BOM_PriceLimit(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),
1086 M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID) * CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty, CUR_BOM_Line.Discount,
1087 v_FreightAmt, CUR_BOM_Line.C_Charge_ID, v_ChargeAmt, CUR_BOM_Line.C_Tax_ID,
1088 CUR_BOM_Line.QuantityOrder, CUR_BOM_Line.M_Product_UOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID)
1089 )
1090 ; -- no Lot/SerNo for BOMs
1091 -- One Time variables
1092 v_FreightAmt:=0;
1093 v_ChargeAmt:=0;
1094 END LOOP; -- Create New Lines
1095 -- Convert into Comment Line
1096 UPDATE C_ORDERLINE
1097 SET M_Product_ID=NULL,
1098 PriceList=0,
1099 PriceActual=0,
1100 PriceLimit=0,
1101 LineNetAmt=0,
1102 FreightAmt=0,
1103 ChargeAmt=0,
1104 Description=
1105 (SELECT p.NAME || ' ' || C_ORDERLINE.Description
1106 FROM M_PRODUCT p
1107 WHERE p.M_Product_ID=CUR_BOM_Line.M_Product_ID
1108 )
1109 ,
1110 PriceStd=0,
1111 M_Offer_ID=NULL
1112 WHERE C_OrderLine_ID=CUR_BOM_Line.C_OrderLine_ID;
1113 END LOOP; -- Replace Lines
1114 END LOOP; -- BOM Loop
1115 END;
1116END IF;--FINISH_PROCESS
1117IF(NOT END_PROCESSING AND NOT FINISH_PROCESS) THEN
1118 /**************************************************************************
1119 * Always check and (un) Reserve Inventory (counterpart: M_InOut_Post)
1120 *************************************************************************/
1121 DECLARE
1122 Cur_ResLine RECORD;
1123
1124 v_QtySO NUMERIC; -- Reserved
1125 v_QtyOrderSO NUMERIC;
1126 v_QtyPO NUMERIC; -- Ordered
1127 v_QtyOrderPO NUMERIC;
1128 BEGIN
1129 v_ResultStr := 'ReserveInventory';
1130 -- For all lines needing reservation
1131 FOR Cur_ResLine IN (SELECT l.M_Warehouse_ID, l.M_Product_ID, l.M_AttributeSetInstance_ID, l.C_OrderLine_ID,
1132 -- Target Level = 0 if DirectShip='Y' or Binding='N'
1133 (CASE l.DirectShip WHEN 'Y' THEN 0 ELSE (CASE v_IsBinding WHEN 'N' THEN 0 ELSE l.QtyOrdered END) END)
1134 -l.QtyReserved-l.QtyDelivered AS Qty, l.QUANTITYORDER,
1135 l.QtyReserved, l.QtyDelivered, l.DatePromised, l.C_UOM_ID,
1136 l.M_PRODUCT_UOM_ID
1137 FROM C_ORDERLINE l, M_PRODUCT p
1138 WHERE l.C_Order_ID=v_Record_ID
1139 -- Reserve Products (not: services, null products) --
1140 AND l.M_Product_ID=p.M_Product_ID
1141 AND p.IsStocked='Y' AND p.ProductType='I'
1142 -- Target Level = 0 if DirectShip='Y' or Binding='N'
1143 AND (CASE l.DirectShip WHEN 'Y' THEN 0 ELSE (CASE v_IsBinding WHEN 'N' THEN 0 ELSE l.QtyOrdered END)END)
1144 -l.QtyReserved-l.QtyDelivered <> 0
1145 FOR UPDATE) LOOP
1146 -- Qty corrected for SO/PO
1147 IF (v_DocSubTypeSO IS NOT NULL) THEN
1148 v_QtySO := Cur_ResLine.Qty;
1149 v_QtyOrderSO := NULL;
1150 IF Cur_ResLine.QtyReserved=0 AND Cur_ResLine.QtyDelivered=0 THEN
1151 v_QtyOrderSO := Cur_ResLine.QuantityOrder;
1152 ELSIF Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
1153 v_QtyOrderSO := C_Uom_Convert(v_QtySO, Cur_ResLine.C_UOM_ID, Cur_ResLine.M_Product_UOM_ID, 'Y');
1154 END IF;
1155 v_QtyPO := 0;
1156 v_QtyOrderPO := NULL;
1157 ELSE -- PO
1158 v_QtySO := 0;
1159 v_QtyOrderSO := NULL;
1160 v_QtyPO := Cur_ResLine.Qty;
1161 v_QtyOrderPO := NULL;
1162 IF Cur_ResLine.QtyReserved=0 AND Cur_ResLine.QtyDelivered=0 THEN
1163 v_QtyOrderPO := Cur_ResLine.QuantityOrder;
1164 ELSIF Cur_ResLine.M_Product_UOM_ID IS NOT NULL THEN
1165 v_QtyOrderPO := C_Uom_Convert(v_QtyPO, Cur_ResLine.C_UOM_ID, Cur_ResLine.M_Product_UOM_ID, 'Y');
1166 END IF;
1167 END IF;
1168 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,
1169 Cur_ResLine.C_UOM_ID, Cur_ResLine.M_PRODUCT_UOM_ID, v_QtySO, v_QtyOrderSO, v_QtyPO, v_QtyOrderPO);
1170 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;
1171
1172 -- Update Order Line
1173 IF (v_DocSubTypeSO IS NOT NULL) THEN
1174 UPDATE C_ORDERLINE
1175 SET QtyReserved = QtyReserved + v_QtySO
1176 WHERE C_OrderLine_ID = Cur_ResLine.C_OrderLine_ID;
1177 END IF;
1178 GET DIAGNOSTICS rowcount:=ROW_COUNT;
1179 IF (rowcount <> 1) THEN
1180 IF (p_PInstance_ID IS NOT NULL) THEN
1181 -- ROLLBACK;
1182 v_ResultStr := 'LockingOrder';
1183 UPDATE C_ORDER
1184 SET Processing = 'N'
1185 WHERE C_Order_ID = v_Record_ID;
1186 RAISE EXCEPTION '%','DATA_EXCEPTION';
1187 -- COMMIT;
1188 END IF;
1189 RAISE EXCEPTION '%', 'Did not update Line'; --OBTG:-20011--
1190 END IF;
1191 END LOOP; -- For all lines needing reservation
1192 END; -- Reserve Inventory
1193 END IF;--FINISH_PROCESS
1194 IF (NOT FINISH_PROCESS) THEN
1195 /**************************************************************************
1196 * Calculate Taxes and Totals
1197 *************************************************************************/
1198 DECLARE
1199 Cur_Tax RECORD;
1200 Cur_MultiTax RECORD;
1201 xTotalLines NUMERIC:=0;
1202 v_TaxBaseAmt NUMERIC:=0;
1203 xTaxAmt NUMERIC:=0;
1204 xGrandTotal NUMERIC:=0;
1205 HeaderNotAdded BOOLEAN:=TRUE;
1206 v_FirstLine BOOLEAN;
1207 v_ActualBaseAmt NUMERIC;
1208 v_lineNo NUMERIC:= 0;
1209 BEGIN
1210 v_ResultStr:='DeleteOldTaxes';
1211 DELETE FROM C_ORDERTAX WHERE C_Order_ID=v_Record_ID;
1212 -- For all Tax Rates
1213 v_ResultStr:='InsertNewTaxes';
1214 FOR Cur_Tax IN
1215 (SELECT l.C_Tax_ID, o.IsTaxIncluded,
1216 SUM(l.LineNetAmt) + SUM(l.FreightAmt) + SUM(coalesce(l.ChargeAmt,0)) AS LineNet,
1217 SUM(o.FreightAmt) + SUM(coalesce(o.ChargeAmt,0)) AS HeaderNet,
1218 t.Rate, t.IsSummary,
1219 c.StdPrecision
1220
1221 FROM C_ORDER o, C_ORDERLINE l,
1222 C_TAX t, C_CURRENCY c
1223 WHERE o.C_Order_ID=l.C_Order_ID
1224 AND o.C_Order_ID=v_Record_ID -- Parameter
1225 AND l.C_Tax_ID=t.C_Tax_ID
1226 AND o.C_Currency_ID=c.C_Currency_ID
1227 GROUP BY l.C_Tax_ID,
1228 o.IsTaxIncluded,
1229 t.Rate,
1230 t.IsSummary,
1231 c.StdPrecision
1232 ORDER BY 3 DESC
1233 )
1234 LOOP
1235 v_TaxBaseAmt:=Cur_Tax.LineNet;
1236 xTotalLines:=xTotalLines + v_TaxBaseAmt; -- w/o Header Freight/Charge
1237
1238 IF(HeaderNotAdded) THEN -- add header net to first tax
1239 HeaderNotAdded:=FALSE;
1240 v_TaxBaseAmt:=v_TaxBaseAmt + Cur_Tax.HeaderNet;
1241 END IF;
1242 IF(Cur_Tax.IsSummary='N') THEN
1243 v_LineNo := v_LineNo + 10;
1244 xTaxAmt:=ROUND(v_TaxBaseAmt * Cur_Tax.Rate / 100, Cur_Tax.StdPrecision) ;
1245 xGrandTotal:=xGrandTotal + v_TaxBaseAmt + xTaxAmt;
1246 --
1247 INSERT
1248 INTO C_ORDERTAX
1249 (
1250 C_OrderTax_ID, C_Order_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID,
1251 IsActive, Created, CreatedBy, Updated,
1252 UpdatedBy, TaxBaseAmt, TaxAmt, Line
1253 )
1254 VALUES
1255 (
1256 get_uuid(), v_Record_ID, Cur_Tax.C_Tax_ID, v_Client_ID, v_Org_ID,
1257 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()),
1258 v_UpdatedBy, v_TaxBaseAmt, xTaxAmt, v_LineNo
1259 )
1260 ;
1261 ELSE -- Multiple Taxes
1262 xGrandTotal:=xGrandTotal + v_TaxBaseAmt;
1263 v_FirstLine := TRUE;
1264 FOR Cur_MultiTax IN
1265 (SELECT C_Tax_ID, Rate, CASCADE
1266 FROM C_TAX
1267 WHERE Parent_Tax_ID=Cur_Tax.C_Tax_ID
1268 ORDER BY Line)
1269 LOOP
1270 v_LineNo := v_LineNo + 10;
1271 IF ((Cur_MultiTax.CASCADE = 'Y') AND (NOT v_FirstLine)) THEN --cannot apply cascade to the first line
1272 v_ActualBaseAmt := xGrandTotal;
1273 ELSE
1274 v_ActualBaseAmt := v_TaxBaseAmt;
1275 END IF;
1276 xTaxAmt:=ROUND(v_ActualBaseAmt * Cur_MultiTax.Rate / 100, Cur_Tax.StdPrecision) ;
1277 v_FirstLine := FALSE;
1278 xGrandTotal:=xGrandTotal + xTaxAmt;
1279 --
1280 INSERT INTO C_ORDERTAX
1281 (
1282 C_OrderTax_ID, C_Order_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID,
1283 IsActive, Created, CreatedBy, Updated,
1284 UpdatedBy, TaxBaseAmt, TaxAmt, Line
1285 )
1286 VALUES
1287 (
1288 get_uuid(), v_Record_ID, Cur_MultiTax.C_Tax_ID, v_Client_ID, v_Org_ID,
1289 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()),
1290 v_UpdatedBy, v_ActualBaseAmt, xTaxAmt, v_LineNo
1291 );
1292 END LOOP;
1293 END IF;
1294 END LOOP; -- Insert New Taxes
1295 -- Update Header
1296 UPDATE C_ORDER
1297 SET TotalLines=xTotalLines,
1298 GrandTotal=xGrandTotal
1299 WHERE C_Order_ID=v_Record_ID;
1300 RAISE NOTICE '%','GrandTotal=' || xGrandTotal ;
1301 END; -- Calculate Tax and Totals
1302 -- Synchronize Client/Org Ownership
1303 UPDATE C_ORDERLINE
1304 SET AD_Client_ID=v_Client_ID,
1305 AD_Org_ID=v_Org_ID
1306 WHERE C_Order_ID=v_Record_ID
1307 AND(AD_Client_ID<>v_Client_ID
1308 OR AD_Org_ID<>v_Org_ID) ;
1309END IF;--FINISH_PROCESS
1310
1311
1312-- PRAVEEN KUMAR.K CODE START
1313
1314 IF (NOT FINISH_PROCESS) THEN
1315 /**************************************************************************
1316 * Calculate Taxes and Totals
1317 *************************************************************************/
1318
1319 DECLARE
1320 Cur_Tax RECORD;
1321 Cur_MultiTax RECORD;
1322 xTotalLines NUMERIC:=0;
1323 v_TaxBaseAmt NUMERIC:=0;
1324 xTaxAmt NUMERIC:=0;
1325 xGrandTotal NUMERIC:=0;
1326 HeaderNotAdded BOOLEAN:=TRUE;
1327 v_FirstLine BOOLEAN;
1328 v_ActualBaseAmt NUMERIC;
1329 v_lineNo NUMERIC:= 0;
1330 CUR_LINE RECORD;
1331 BEGIN
1332 v_ResultStr:='DeleteOldTaxes';
1333
1334 FOR Cur_line IN
1335 (SELECT C_ORDERLINE.C_ORDERLine_ID
1336 FROM C_ORDERLINE
1337 WHERE C_ORDER_ID = v_RECORD_ID
1338 )
1339 LOOP
1340 SELECT C_ORDERLINE_ID INTO v_RECORDLINE
1341 FROM C_ORDERLINE
1342 WHERE C_ORDERLINE_ID = CUR_LINE.C_ORDERLINE_ID;
1343 RAISE NOTICE '%','PRAVEEN LINE :'|| V_RECORDLINE;
1344
1345 DELETE FROM HS_C_ORDERLINETAX WHERE C_OrderLINE_ID=v_RECORDLINE;
1346 -- For all Tax Rates
1347 v_ResultStr:='InsertNewTaxes';
1348 FOR Cur_Tax IN
1349 (SELECT l.C_Tax_ID, o.IsTaxIncluded,
1350 SUM(l.LineNetAmt) + SUM(l.FreightAmt) + SUM(coalesce(l.ChargeAmt,0)) AS LineNet,
1351 SUM(o.FreightAmt) + SUM(coalesce(o.ChargeAmt,0)) AS HeaderNet,
1352 t.Rate, t.IsSummary,
1353 c.StdPrecision
1354
1355 FROM C_ORDER o, C_ORDERLINE l,
1356 C_TAX t, C_CURRENCY c
1357 WHERE o.C_Order_ID=l.C_Order_ID
1358 AND L.C_OrderLINE_ID=v_RecordLINE -- Parameter
1359 AND l.C_Tax_ID=t.C_Tax_ID
1360 AND o.C_Currency_ID=c.C_Currency_ID
1361 GROUP BY l.C_Tax_ID,
1362 o.IsTaxIncluded,
1363 t.Rate,
1364 t.IsSummary,
1365 c.StdPrecision
1366 ORDER BY 3 DESC
1367 )
1368 LOOP
1369 v_TaxBaseAmt1:=Cur_Tax.LineNet;
1370 xTotalLines1:=xTotalLines1 + v_TaxBaseAmt1; -- w/o Header Freight/Charge
1371
1372 IF(HeaderNotAdded) THEN -- add header net to first tax
1373 HeaderNotAdded:=FALSE;
1374 v_TaxBaseAmt1:=v_TaxBaseAmt1+ Cur_Tax.HeaderNet;
1375 END IF;
1376
1377 IF(Cur_Tax.IsSummary='N') THEN
1378 v_LineNo1 := v_LineNo1 + 10;
1379 xTaxAmt1:=ROUND(v_TaxBaseAmt1 * Cur_Tax.Rate / 100, Cur_Tax.StdPrecision) ;
1380 xGrandTotal1:=xGrandTotal1 + v_TaxBaseAmt1 + xTaxAmt1;
1381 --
1382 INSERT
1383 INTO HS_C_ORDERLINETAX
1384 (
1385 HS_C_ORDERLINETAX_ID, C_OrderLINE_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID,
1386 IsActive, Created, CreatedBy, Updated,
1387 UpdatedBy, TaxBaseAmt, TaxAmt, Line
1388 )
1389 VALUES
1390 (
1391 get_uuid(), v_RecordLINE, Cur_Tax.C_Tax_ID, V_Client_ID, V_Org_ID,
1392 'Y', TO_DATE(NOW()), V_UpdatedBy, TO_DATE(NOW()),
1393 V_UpdatedBy, v_TaxBaseAmt1, xTaxAmt1, v_LineNo1
1394 )
1395 ;
1396 ELSE -- Multiple Taxes
1397 xGrandTotal1:=xGrandTotal1 + v_TaxBaseAmt1;
1398 v_FirstLine := TRUE;
1399 FOR Cur_MultiTax IN
1400 (SELECT C_Tax_ID, Rate, CASCADE
1401 FROM C_TAX
1402 WHERE Parent_Tax_ID=Cur_Tax.C_Tax_ID
1403 ORDER BY Line)
1404 LOOP
1405 v_LineNo1 := v_LineNo1 + 10;
1406 IF ((Cur_MultiTax.CASCADE = 'Y') AND (NOT v_FirstLine)) THEN --cannot apply cascade to the first line
1407 v_ActualBaseAmt1 := xGrandTotal1;
1408 ELSE
1409 v_ActualBaseAmt1 := v_TaxBaseAmt1;
1410 END IF;
1411 xTaxAmt1:=ROUND(v_ActualBaseAmt1 * Cur_MultiTax.Rate / 100, Cur_Tax.StdPrecision) ;
1412 v_FirstLine := FALSE;
1413 xGrandTotal1:=xGrandTotal1 + xTaxAmt1;
1414 --
1415 INSERT INTO HS_C_ORDERLINETAX
1416 (
1417 HS_C_ORDERLINETAX_ID, C_OrderLINE_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID,
1418 IsActive, Created, CreatedBy, Updated,
1419 UpdatedBy, TaxBaseAmt, TaxAmt, Line
1420 )
1421 VALUES
1422 (
1423 get_uuid(), v_RecordLINE, Cur_MultiTax.C_Tax_ID, V_Client_ID, V_Org_ID,
1424 'Y', TO_DATE(NOW()), V_UpdatedBy, TO_DATE(NOW()),
1425 V_UpdatedBy, v_ActualBaseAmt1, xTaxAmt1, v_LineNo1
1426 );
1427 END LOOP;
1428
1429 END IF;
1430
1431 END LOOP;
1432 END LOOP; -- Insert New Taxes
1433 -- Update Header
1434 UPDATE C_ORDER
1435 SET TotalLines=xTotalLines1,
1436 GrandTotal=xGrandTotal1
1437 WHERE C_Order_ID=v_RECORD_ID;
1438 RAISE NOTICE '%','GrandTotal12345=' || xGrandTotal1 ;
1439 END; -- Calculate Tax and Totals
1440 -- Synchronize Client/Org Ownership
1441 UPDATE C_ORDER
1442 SET AD_Client_ID=V_Client_ID,
1443 AD_Org_ID=V_Org_ID
1444 WHERE C_Order_ID=v_RECORD_ID
1445 AND(AD_Client_ID<>V_Client_ID
1446 OR AD_Org_ID<>V_Org_ID) ;
1447
1448END IF;--FINISH_PROCESS
1449
1450
1451--PRAVEEN KUMAR. k END
1452
1453
1454
1455IF(NOT END_PROCESSING AND NOT FINISH_PROCESS) THEN
1456 /**************************************************************************
1457 * Order Complete:5 - Something to do:6
1458 */
1459 BEGIN
1460 v_ResultStr:='OrderCompleteCheck';
1461 SELECT SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyDelivered*hex_to_int(C_OrderLine_ID)),
1462 SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyInvoiced*hex_to_int(C_OrderLine_ID))
1463 INTO ToDeliver,
1464 ToInvoice
1465 FROM C_ORDERLINE
1466 WHERE C_Order_ID=v_Record_ID;
1467 -- If no lines, ToDeliver is NULL
1468 IF((ToDeliver=0 AND ToInvoice=0) OR(ToDeliver IS NULL AND ToInvoice IS NULL)) THEN
1469 RAISE NOTICE '%','OrderComplete' ;
1470 IF(v_DocAction='CL') THEN
1471 END_PROCESSING:=TRUE;
1472 ELSIF(v_DocAction='VO') THEN
1473
1474 /*Q3 Added*/
1475 IF(v_isSoTrx='N') THEN
1476 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID;
1477
1478 IF(v_c_debt_payment IS NOT NULL) THEN
1479 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID and c_cashline_id is null and c_bankstatementline_id is null and c_settlement_cancel_id is null;
1480 IF(v_c_debt_payment IS NULL) THEN
1481
1482 RAISE EXCEPTION '%', '@Payment Is Processed@' ; --OBTG:-20000--
1483 ELSE
1484 Delete from c_debt_payment where c_debt_payment_id=v_c_debt_payment;
1485 END IF;
1486 END IF;
1487
1488 END IF;
1489 /*Q3 End*/
1490
1491
1492
1493
1494
1495 UPDATE C_ORDER
1496 SET DocStatus='VO',
1497 DocAction='--',
1498 Processed='Y',
1499 Updated=TO_DATE(NOW())
1500 WHERE C_Order_ID=v_Record_ID;
1501 END_PROCESSING:=TRUE;
1502 ELSE
1503 UPDATE C_ORDER
1504 SET DocStatus='CO',
1505 DocAction='--',
1506 Processed='Y',
1507 Updated=TO_DATE(NOW())
1508 WHERE C_Order_ID=v_Record_ID;
1509 END_PROCESSING:=TRUE;
1510 END IF;
1511 IF(NOT END_PROCESSING) THEN
1512 RAISE EXCEPTION '%', '@AlreadyPosted@'; --OBTG:-20000--
1513 END IF;--END_PROCESSING
1514 END IF;
1515 END;
1516END IF;--FINISH_PROCESS
1517IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1518 /**
1519 * In Progress -----------------------------------------------------------
1520 */
1521 UPDATE C_ORDER
1522 SET DocStatus='IP',
1523 DateAcct=DateOrdered,
1524 Updated=TO_DATE(NOW())
1525 WHERE C_Order_ID=v_Record_ID;
1526 IF(p_PInstance_ID IS NOT NULL) THEN
1527 -- COMMIT;
1528 END IF;
1529END IF;--FINISH_PROCESS
1530IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1531 /**
1532 * Finished with processing
1533 */
1534 IF(v_DocAction='PR') THEN
1535 v_ResultStr:='FinishProcessing';
1536 UPDATE C_ORDER
1537 SET DocStatus='IP',
1538 DocAction='CO',
1539 Processed='N',
1540 Updated=TO_DATE(NOW())
1541 WHERE C_Order_ID=v_Record_ID;
1542 -- C_Order_PickList(NULL, v_Record_ID); -- Print PickList
1543 END_PROCESSING:=TRUE;
1544 END IF;
1545END IF;--FINISH_PROCESS
1546IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1547 /**************************************************************************
1548 * Prepayment Order Create Invoice
1549 *************************************************************************/
1550 IF(v_DocSubTypeSO='PR' AND v_DocStatus<>'WP') THEN
1551 RAISE NOTICE '%','Create PreInvoice - ' || v_Record_ID ;
1552 v_ResultStr:='CreatePreInvoice';
1553 SELECT * INTO Invoice_ID FROM C_Invoice_Create(NULL, v_Record_ID) ;
1554 RAISE NOTICE '%',' PreInvoice - ' || Invoice_ID ;
1555 IF(Invoice_ID='0') THEN
1556 RAISE EXCEPTION '%', '@PreInvoiceCreateFailed@'; --OBTG:-20000--
1557 END IF;
1558 IF(NOT END_PROCESSING) THEN
1559 PERFORM C_INVOICE_POST(NULL, Invoice_ID) ;
1560 --
1561 UPDATE C_ORDER
1562 SET DocStatus='WP',
1563 DocAction='--',
1564 Processed='Y',
1565 Updated=TO_DATE(NOW())
1566 WHERE C_Order_ID=v_Record_ID;
1567 --
1568 END IF;--END_PROCESSING
1569 END_PROCESSING:=TRUE;
1570 END IF;
1571 IF(NOT END_PROCESSING) THEN
1572 /**
1573 * Deliver Direct Shipments
1574 */
1575 v_ResultStr:='NonInventoryDelivery';
1576 UPDATE C_ORDERLINE
1577 SET QtyDelivered=QtyOrdered
1578 WHERE DirectShip='Y'
1579 AND C_Order_ID=v_Record_ID;
1580 END IF;--END_PROCESSING
1581END IF;--FINISH_PROCESS
1582IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1583 /**************************************************************************
1584 * Will-Call + Walk In Processing
1585 * --
1586 * (W)illCall(I)nvoice - (W)illCall(P)ickup - (W)alkIn(R)eceipt
1587 * --
1588 *************************************************************************/
1589 IF(v_DocSubTypeSO IN('WI', 'WP', 'WR')) THEN
1590 /************
1591 * Shipment
1592 */
1593 RAISE NOTICE '%','Create Shipment - ' || v_Record_ID ;
1594 v_ResultStr:='CreateShipment';
1595
1596 SELECT * INTO InOut_ID FROM M_Inout_Create(NULL, v_Record_ID, NULL, 'Y') ; -- Force Delivery
1597
1598 RAISE NOTICE '%',' Shipment - ' || InOut_ID ;
1599 IF(InOut_ID='0') THEN
1600 RAISE EXCEPTION '%', '@InOutCreateFailed@'; --OBTG:-20000--
1601 ELSE
1602 SELECT documentno
1603 INTO v_DocumentNo
1604 FROM M_INOUT
1605 WHERE M_INOUT_ID = InOut_ID;
1606 v_Message:='@InoutDocumentno@ ' || v_DocumentNo || ' @beenCreated@';
1607 END IF;
1608 IF(NOT END_PROCESSING) THEN
1609 IF(v_DocSubTypeSO IN('WI', 'WR')) THEN
1610 /************
1611 * Invoice
1612 */
1613 RAISE NOTICE '%','Create Invoice - ' || v_Record_ID ;
1614 v_ResultStr:='CreateInvoice';
1615 SELECT * INTO Invoice_ID FROM C_Invoice_Create(NULL, v_Record_ID) ;
1616 RAISE NOTICE '%',' Invoice - ' || Invoice_ID ;
1617 IF(Invoice_ID IS NULL OR Invoice_ID='0') THEN
1618 RAISE EXCEPTION '%', '@InvoiceCreateFailed@'; --OBTG:-20000--
1619 ELSE
1620 SELECT documentno
1621 INTO v_DocumentNo
1622 FROM C_INVOICE
1623 WHERE C_INVOICE_ID = Invoice_ID;
1624 v_Message:=v_Message||' , '||'@InvoiceDocumentno@ ' || v_DocumentNo || ' @invbeenCreated@';
1625 END IF;
1626 END IF;
1627 END IF;--END_PROCESSING
1628 END IF;
1629END IF;--FINISH_PROCESS
1630IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1631 /**
1632 * Final Completeness check
1633 */
1634 SELECT SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyDelivered*hex_to_int(C_OrderLine_ID)),
1635 SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyInvoiced*hex_to_int(C_OrderLine_ID))
1636 INTO ToDeliver,
1637 ToInvoice
1638 FROM C_ORDERLINE
1639 WHERE C_Order_ID=v_Record_ID;
1640 RAISE NOTICE '%','To deliver - ' || ToDeliver ;
1641 RAISE NOTICE '%','ToInvoice - ' || ToInvoice ;
1642 RAISE NOTICE '%','v_DocSubTypeSO - ' || v_DocSubTypeSO ;
1643 -- Nothing to Deliver + Invoice for (W)illCall(I)nvoice and (W)alkIn(R)eceipt
1644 IF(v_DocSubTypeSO IN('WI', 'WR') AND ToDeliver=0 AND ToInvoice=0) THEN
1645 UPDATE C_ORDER
1646 SET DocStatus='CO',
1647 DocAction='--',
1648 IsDelivered='Y',
1649 IsInvoiced='Y',
1650 Processed='Y',
1651 Updated=TO_DATE(NOW())
1652 WHERE C_Order_ID=v_Record_ID;
1653 RAISE NOTICE '%','DocAction - ' || v_DocAction ;
1654 IF(v_DocAction='VO') THEN
1655 /*Q3 Added*/
1656 IF(v_isSoTrx='N') THEN
1657 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID;
1658
1659 IF(v_c_debt_payment IS NOT NULL) THEN
1660 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID and c_cashline_id is null and c_bankstatementline_id is null and c_settlement_cancel_id is null;
1661 IF(v_c_debt_payment IS NULL) THEN
1662
1663 RAISE EXCEPTION '%', '@Payment Is Processed@' ; --OBTG:-20000--
1664 ELSE
1665 Delete from c_debt_payment where c_debt_payment_id=v_c_debt_payment;
1666 END IF;
1667 END IF;
1668
1669 END IF;
1670 /*Q3 End*/
1671
1672
1673
1674
1675
1676
1677
1678
1679 UPDATE C_ORDER SET DocStatus='VO' WHERE C_Order_ID=v_Record_ID;
1680 END IF;
1681 END IF;
1682 -- Nothing to Deliver for (W)illCall(P)ickup (Invoice generated independently)
1683 IF(v_DocSubTypeSO='WP' AND ToDeliver=0) THEN
1684 UPDATE C_ORDER
1685 SET DocStatus='CO',
1686 DocAction='--',
1687 IsDelivered='Y',
1688 Processed='Y',
1689 Updated=TO_DATE(NOW())
1690 WHERE C_Order_ID=v_Record_ID;
1691 IF(v_DocAction='VO') THEN
1692 /*Q3 Added*/
1693 IF(v_isSoTrx='N') THEN
1694 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID;
1695
1696 IF(v_c_debt_payment IS NOT NULL) THEN
1697 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID and c_cashline_id is null and c_bankstatementline_id is null and c_settlement_cancel_id is null;
1698 IF(v_c_debt_payment IS NULL) THEN
1699
1700 RAISE EXCEPTION '%', '@Payment Is Processed@' ; --OBTG:-20000--
1701 ELSE
1702 Delete from c_debt_payment where c_debt_payment_id=v_c_debt_payment;
1703 END IF;
1704 END IF;
1705
1706 END IF;
1707 /*Q3 End*/
1708
1709
1710
1711
1712
1713
1714
1715 UPDATE C_ORDER SET DocStatus='VO' WHERE C_Order_ID=v_Record_ID;
1716 END IF;
1717 END IF;
1718 -- We are done with standard sales orders
1719 IF(v_DocAction IN('CO', 'CL', 'VO') AND v_DocSubTypeSO='SO') THEN
1720 UPDATE C_ORDER
1721 SET DocStatus='CO',
1722 DocAction='--',
1723 Processed='Y',
1724 Updated=TO_DATE(NOW())
1725 WHERE C_Order_ID=v_Record_ID;
1726 END IF;
1727 -- Purchase Orders
1728 IF(v_DocAction IN('CO', 'CL', 'VO') AND v_DocSubTypeSO IS NULL) THEN
1729 UPDATE C_ORDER
1730 SET DocStatus='CO',
1731 DocAction='--',
1732 Processed='Y',
1733 Updated=TO_DATE(NOW())
1734 WHERE C_Order_ID=v_Record_ID;
1735
1736 RAISE NOTICE '%', 'v_DocType'||v_DocType_ID; --OBTG:-20000--
1737
1738
1739
1740/*Q3 code Start*/
1741
1742
1743 SELECT em_hs_creforder
1744 INTO v_COrderId
1745 FROM C_ORDER
1746 WHERE C_ORDER_ID = v_Record_ID;
1747
1748 RAISE NOTICE '%', 'v_COrderId'||v_COrderId; --OBTG:-20000--
1749
1750 IF(v_COrderId IS NOT NULL) THEN
1751
1752
1753 SELECT sum(QtyOrdered)
1754 INTO v_QtyPOOrdered
1755 FROM C_ORDERLINE
1756 WHERE C_ORDER_ID = v_Record_ID;
1757 select StdPrecision into v_stdprecision from C_CURRENCY where C_CURRENCY_ID=v_c_currency_id;
1758
1759
1760 RAISE NOTICE '%', 'QtyPOOrdered'||v_QtyPOOrdered;
1761
1762 Select priceactual into v_priceactual from c_orderline where c_order_id=v_COrderId;
1763 Select c_bpartner_id into v_bpartner from c_order where c_order_id=v_COrderId;
1764 select name into v_name from c_doctype where c_doctype_id in(select c_doctype_id from c_order where c_order_id=v_COrderId);
1765 select em_hs_expirydays into v_expiry from c_order where c_order_id=v_COrderId;
1766
1767 RAISE NOTICE '%', 'v_expiry'||v_expiry;
1768 RAISE NOTICE '%', '(v_Date+v_expiry)'||(v_Date+v_expiry);
1769 IF(v_name ILIKE 'Grey%') THEN
1770 insert into c_debt_payment
1771 (c_debt_payment_id,ad_client_id,ad_org_id,isactive,created,createdby,updated,
1772 updatedby,c_bpartner_id,c_currency_id,amount,dateplanned,c_order_id,
1773 paymentrule,ispaid,ismanual,isvalid,status,status_initial,em_hs_c_order_ref,em_hs_expirydate)
1774 values(get_uuid(),v_Client_ID,v_Org_ID,'Y',TO_DATE(NOW()), v_UpdatedBy,TO_DATE(NOW()),
1775 v_UpdatedBy,v_bpartner, v_c_currency_id,
1776 ROUND((v_QtyPOOrdered*v_priceactual),v_stdprecision),
1777 v_Date,v_COrderId,'5','N','N','Y','DE','DE',v_Record_ID,(v_Date+v_expiry));
1778 END IF;
1779
1780
1781 END IF;
1782
1783
1784
1785/*Q3 code End*/
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802 END IF;
1803 -- Only create cash entry if docAction is Complete
1804 IF(v_DocAction NOT IN('CO')) THEN
1805 END_PROCESSING:=TRUE;
1806 END IF;
1807END IF;--FINISH_PROCESS
1808IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN
1809 /**************************************************************************
1810 * Create default Cash entry
1811 *************************************************************************/
1812 DECLARE
1813 v_PaymentRule VARCHAR(60) ;
1814 CUR_CB RECORD;
1815 v_debtPaymentID VARCHAR(32); --OBTG:varchar2--
1816 v_totalCash NUMERIC;
1817 v_CB_Curr VARCHAR(32); --OBTG:varchar2--
1818 BEGIN
1819 /* ALO
1820 */
1821 UPDATE C_DEBT_PAYMENT SET IsValid='Y' WHERE C_Order_ID=v_Record_ID;
1822 SELECT C_ORDER.PAYMENTRULE,
1823 C_ORDER.DOCUMENTNO||' - '||C_BPARTNER.NAME||' - '||C_ORDER.GRANDTOTAL,
1824 C_ORDER.GRANDTOTAL,
1825 C_ORDER.M_WAREHOUSE_ID
1826 INTO v_PaymentRule,
1827 v_DocumentNo,
1828 v_GrandTotal,
1829 v_M_Warehouse_ID
1830 FROM C_ORDER,
1831 C_BPARTNER
1832 WHERE C_ORDER.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
1833 AND C_ORDER_ID=v_Record_ID;
1834 SELECT MAX(NAME)
1835 INTO v_WarehouseName
1836 FROM M_WAREHOUSE
1837 WHERE M_WAREHOUSE_ID=v_M_Warehouse_ID;
1838
1839 --PaymentRule C, WI and WR have already created DP, this won't be inserted in cashline
1840 --because it will processed with the invoice.
1841 IF(v_PaymentRule='C') AND (v_DocSubTypeSO NOT IN ('WI', 'WR') OR v_DocSubTypeSO IS NULL) THEN
1842 IF(v_CashLine_ID IS NULL OR v_CashLine_ID='0') THEN
1843 -- Create CashLine
1844 -- Find Defaylt CashBook
1845 v_ResultStr:='Find C_CashBook Org_ID=' || v_Org_ID;
1846 BEGIN
1847 -- First active default Book of Org
1848 FOR CUR_CB IN
1849 (SELECT cb.C_CashBook_ID,
1850 c.ISO_Code,
1851 cb.NAME,
1852 cb.c_currency_id
1853 FROM C_CASHBOOK cb,
1854 C_CURRENCY c
1855 WHERE cb.AD_Org_ID=v_Org_ID
1856 AND cb.C_Currency_ID=c.C_Currency_ID
1857 AND cb.IsActive='Y'
1858 ORDER BY cb.IsDefault DESC
1859 )
1860 LOOP
1861 IF(v_CashBook_ID IS NULL) THEN
1862 v_CashBook_ID:=CUR_CB.C_CashBook_ID;
1863 v_ISO_Code:=CUR_CB.ISO_Code;
1864 v_CB_Curr:=CUR_CB.C_Currency_ID;
1865 ELSIF(CUR_CB.NAME=v_WarehouseName) THEN
1866 v_CashBook_ID:=CUR_CB.C_CashBook_ID;
1867 v_ISO_Code:=CUR_CB.ISO_Code;
1868 v_CB_Curr:=CUR_CB.C_Currency_ID;
1869 END IF;
1870 END LOOP;
1871 END;
1872 IF(v_CashBook_ID IS NULL) THEN
1873 RAISE EXCEPTION '%', '@CashBookPRSCnotfoundOrg@' || v_Org_Name ; --OBTG:-20000--
1874 END IF;
1875 RAISE NOTICE '%','CashBook_ID=' || v_CashBook_ID ;
1876 -- Find/Create Cash Journal
1877 v_ResultStr:='Find C_Cash for ' || v_Date;
1878 DECLARE
1879 Cur_CashId RECORD;
1880 BEGIN
1881 FOR Cur_CashId IN
1882 (SELECT C_Cash_ID AS Cash_ID
1883 FROM C_CASH
1884 WHERE C_CashBook_ID=v_CashBook_ID
1885 AND TRUNC(StatementDate)=v_Date
1886 AND Processed='N'
1887 )
1888 LOOP
1889 v_Cash_ID:=Cur_CashId.Cash_ID;
1890 EXIT;
1891 END LOOP;
1892 EXCEPTION
1893 WHEN DATA_EXCEPTION THEN
1894 NULL;
1895 END;
1896 /**************************************************************************
1897 * Credit Multiplier
1898 *************************************************************************/
1899 DECLARE
1900 v_DocBaseType C_DOCTYPE.DocBaseType%TYPE;
1901 BEGIN
1902 -- Is it a Credit Memo?
1903 SELECT DocBaseType
1904 INTO v_DocBaseType
1905 FROM C_DOCTYPE
1906 WHERE C_DocType_ID=v_DocType_ID;
1907 IF(v_DocBaseType IN('ARC', 'API')) THEN
1908 v_Multiplier:=-1;
1909 END IF;
1910 END;
1911
1912
1913
1914 IF(v_Cash_ID IS NULL) THEN
1915 v_ResultStr:='Create C_Cash';
1916 SELECT * INTO v_Cash_ID FROM Ad_Sequence_Next('C_Cash', v_Org_ID) ;
1917 INSERT
1918 INTO C_CASH
1919 (
1920 C_Cash_ID, AD_Client_ID, AD_Org_ID, IsActive,
1921 Created, CreatedBy, Updated, UpdatedBy,
1922 C_CashBook_ID, NAME, StatementDate, DateAcct,
1923 BeginningBalance, EndingBalance, StatementDifference, Processing,
1924 Processed, Posted
1925 )
1926 VALUES
1927 (
1928 v_Cash_ID, v_Client_ID, v_Org_ID, 'Y',
1929 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
1930 v_CashBook_ID, TO_CHAR(v_Date, 'YYYY-MM-DD') || ' ' || v_ISO_Code, v_Date, v_Date,
1931 0, 0, 0, 'N',
1932 'N', 'N'
1933 )
1934 ;
1935 END IF;
1936 /*
1937 Create a debt payment for paymentrule=C
1938
1939 Note: for WI and WR we have already created an invoice and its DP, we only have to link it
1940 */
1941 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)
1942 INTO v_totalCash
1943 FROM C_DEBT_PAYMENT_V dp
1944 WHERE C_Order_ID=v_Record_ID;
1945
1946/*
1947 SELECT COUNT(*)
1948 INTO v_Aux
1949 FROM C_DEBT_PAYMENT DP,
1950 C_INVOICE I
1951 WHERE I.C_ORDER_ID = v_Record_ID
1952 AND DP.C_INVOICE_ID = I.C_INVOICE_ID;
1953
1954 IF v_Aux != 0 THEN -- DP already created
1955 SELECT dp.C_DEBT_PAYMENT_ID
1956 INTO v_debtPaymentID
1957 FROM C_DEBT_PAYMENT DP,
1958 C_INVOICE I
1959 WHERE I.C_ORDER_ID = v_Record_ID
1960 AND DP.C_INVOICE_ID = I.C_INVOICE_ID;
1961
1962 UPDATE C_DEBT_PAYMENT
1963 SET C_ORDER_ID = v_Record_ID
1964 WHERE C_DEBT_PAYMENT_ID = v_debtPaymentID;
1965 ELSE */
1966 SELECT * INTO v_debtPaymentID FROM Ad_Sequence_Next('C_Debt_Payment', v_Record_ID) ;
1967 INSERT INTO C_DEBT_PAYMENT
1968 (C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
1969 CREATED, CREATEDBY, UPDATED, UPDATEDBY,
1970 ISRECEIPT, C_SETTLEMENT_CANCEL_ID, C_SETTLEMENT_GENERATE_ID, DESCRIPTION,
1971 C_ORDER_ID, C_BPARTNER_ID, C_CURRENCY_ID, C_CASHLINE_ID,
1972 C_BANKACCOUNT_ID, C_CASHBOOK_ID, PAYMENTRULE, ISPAID,
1973 AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL,
1974 ISVALID, C_BANKSTATEMENTLINE_ID, CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED,
1975 GENERATE_PROCESSED, c_project_id,IsAutomaticGenerated, STATUS_INITIAL)
1976 VALUES
1977 (v_debtPaymentID, v_Client_ID, v_Org_ID, 'Y',
1978 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
1979 v_isSoTrx, NULL, NULL, '',
1980 v_Record_ID, v_c_Bpartner_Id, v_c_currency_id, null,
1981 NULL, v_CashBook_ID, 'C', 'N',
1982 (v_GrandTotal-v_totalCash), 0, v_Date, 'N',
1983 'Y', NULL, 'N', 'N',
1984 'N', v_C_PROJECT_ID,'Y', 'DE');
1985 -- END IF;
1986
1987 RAISE NOTICE '%',' Cash_ID=' || v_Cash_ID ;
1988 -- Create CashJournal Line in invoice currency
1989 v_ResultStr:='Create C_CashLine';
1990 SELECT * INTO v_CashLine_ID FROM Ad_Sequence_Next('C_CashLine', v_Org_ID) ;
1991
1992 SELECT COALESCE(MAX(Line), 0) +10
1993 INTO v_Line
1994 FROM C_CASHLINE
1995 WHERE C_Cash_ID=v_Cash_ID;
1996 --
1997 INSERT
1998 INTO C_CASHLINE
1999 (
2000 C_CashLine_ID, AD_Client_ID, AD_Org_ID, IsActive,
2001 Created, CreatedBy, Updated, UpdatedBy,
2002 C_Cash_ID, C_Debt_Payment_ID, Line, Description,
2003 Amount, CashType, DiscountAmt, WriteOffAmt,
2004 IsGenerated
2005 )
2006 VALUES
2007 (
2008 v_CashLine_ID, v_Client_ID, v_Org_ID, 'Y',
2009 TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy,
2010 v_Cash_ID, v_debtPaymentID, v_Line, v_DocumentNo,
2011 (v_GrandTotal-v_totalCash) * (CASE WHEN v_isSoTrx='N' THEN -1 ELSE 1 END), 'P', 0, 0,
2012 'Y'
2013 )
2014 ;
2015 RAISE NOTICE '%',' CashLine_ID=' || v_CashLine_ID ;
2016
2017
2018 END IF; -- CashLine_ID IS NULL OR CashLine_ID = '0'
2019 END IF; -- v_PaymentRule = 'C'
2020END;
2021END IF;--FINISH_PROCESS
2022IF(NOT FINISH_PROCESS) THEN
2023 -- End Processing --------------------------------------------------------
2024 ---- <<END_PROCESSING>>
2025 -- Cloase Order
2026 IF(v_DocAction='CL') THEN
2027
2028 /*Q3 Added*/
2029 IF(v_isSoTrx='N') THEN
2030 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID;
2031
2032 IF(v_c_debt_payment IS NOT NULL) THEN
2033 Select c_debt_payment_id into v_c_debt_payment from c_debt_payment where em_hs_c_order_ref=v_Record_ID and c_cashline_id is null and c_bankstatementline_id is null and c_settlement_cancel_id is null;
2034 IF(v_c_debt_payment IS NULL) THEN
2035
2036 RAISE EXCEPTION '%', '@Payment Is Processed@' ; --OBTG:-20000--
2037 ELSE
2038 Delete from c_debt_payment where c_debt_payment_id=v_c_debt_payment;
2039 END IF;
2040 END IF;
2041
2042 END IF;
2043 /*Q3 End*/
2044
2045
2046
2047
2048 UPDATE C_ORDER
2049 SET DocStatus='CL',
2050 DocAction='--',
2051 Processed='Y'
2052 WHERE C_Order_ID=v_Record_ID;
2053
2054
2055
2056 END IF;
2057END IF;--FINISH_PROCESS
2058
2059--C_Order_Post - Finish_Process Extension Point
2060SELECT count(*) INTO v_count
2061FROM DUAL
2062where exists (select 1 from ad_ep_procedures where ad_extension_points_id = 'CB68FC0E8A4547D9943C785761977E77');
2063IF (v_count=1) THEN
2064 DECLARE
2065 v_ep_instance VARCHAR(32); --OBTG:VARCHAR2--
2066 v_extension_point_id VARCHAR(32) := 'CB68FC0E8A4547D9943C785761977E77'; --OBTG:VARCHAR2--
2067 BEGIN
2068 v_ep_instance := get_uuid();
2069 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Record_ID',
2070 v_record_id, NULL, NULL, NULL, NULL, NULL, NULL);
2071 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'DocAction',
2072 v_DocAction, NULL, NULL, NULL, NULL, NULL, NULL);
2073 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'User',
2074 v_User, NULL, NULL, NULL, NULL, NULL, NULL);
2075 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Message',
2076 NULL, NULL, NULL, NULL, NULL, NULL, v_Message);
2077 PERFORM AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Result',
2078 NULL, NULL, v_result, NULL, NULL, NULL, NULL);
2079 PERFORM AD_EXTENSION_POINT_HANDLER(v_ep_instance, v_extension_point_id);
2080 SELECT p_number INTO v_Result
2081 FROM ad_ep_instance_para
2082 WHERE ad_ep_instance_id = v_ep_instance
2083 AND parametername LIKE 'Result';
2084 SELECT p_text INTO v_Message
2085 FROM ad_ep_instance_para
2086 WHERE ad_ep_instance_id = v_ep_instance
2087 AND parametername LIKE 'Message';
2088
2089 DELETE FROM ad_ep_instance_para
2090 WHERE ad_ep_instance_id = v_ep_instance;
2091 END;
2092END IF;
2093
2094IF(NOT FINISH_PROCESS) THEN
2095 IF(p_PInstance_ID IS NOT NULL) THEN
2096 v_ResultStr:='UnLockingOrder';
2097 UPDATE C_ORDER
2098 SET Processing='N',
2099 Updated=TO_DATE(NOW()),
2100 UpdatedBy=v_User
2101 WHERE C_Order_ID=v_Record_ID;
2102 -- COMMIT;
2103 END IF;
2104END IF;--FINISH_PROCESS
2105
2106---- <<FINISH_PROCESS>>
2107IF(p_PInstance_ID IS NOT NULL) THEN
2108 -- Update AD_PInstance
2109 RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message ;
2110 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
2111END IF;
2112RETURN;
2113END; --BODY
2114EXCEPTION
2115WHEN OTHERS THEN
2116 RAISE NOTICE '%',v_ResultStr ;
2117 v_ResultStr:= '@ERROR=' || SQLERRM;
2118 IF(p_PInstance_ID IS NOT NULL) THEN
2119 -- ROLLBACK;
2120 --Inserted by Carlos Romero 062706
2121 UPDATE C_ORDER SET Processing='N' WHERE C_Order_ID=v_Record_ID;
2122 RAISE NOTICE '%',v_ResultStr ;
2123 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
2124 ELSE
2125 RAISE EXCEPTION '%', SQLERRM;
2126 END IF;
2127 RETURN;
2128END ; $function$