[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_SV11
Source
1 PACKAGE BODY PO_LINES_SV11 as
2 /* $Header: POXPOL6B.pls 120.12.12020000.3 2013/02/10 11:47:30 vegajula ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: update_line()
7
8 Preetam B.(OPM-GML) 21-feb-2000 Bug# 1056597 added 5 columns to update line.
9 Preetam B.(OPM-GML) 21-nov-2003 Bug# 3274039 derive sec qty for shipment.
10
11 ===========================================================================*/
12
13
14 PROCEDURE update_line(X_Rowid VARCHAR2,
15 X_Po_Line_Id NUMBER,
16 X_Last_Update_Date DATE,
17 X_Last_Updated_By NUMBER,
18 X_Po_Header_Id NUMBER,
19 X_Line_Type_Id NUMBER,
20 X_Line_Num NUMBER,
21 X_Last_Update_Login NUMBER,
22 X_Item_Id NUMBER,
23 X_Item_Revision VARCHAR2,
24 X_Category_Id NUMBER,
25 X_Item_Description VARCHAR2,
26 X_Unit_Meas_Lookup_Code VARCHAR2,
27 X_Quantity_Committed NUMBER,
28 X_Committed_Amount NUMBER,
29 X_Allow_Price_Override_Flag VARCHAR2,
30 X_Not_To_Exceed_Price NUMBER,
31 X_List_Price_Per_Unit NUMBER,
32 X_Unit_Price NUMBER,
33 X_Quantity NUMBER,
34 X_Un_Number_Id NUMBER,
35 X_Hazard_Class_Id NUMBER,
36 X_Note_To_Vendor VARCHAR2,
37 X_From_Header_Id NUMBER,
38 X_From_Line_Id NUMBER,
39 X_From_Line_Location_Id NUMBER, -- <SERVICES FPJ>
40 X_Min_Order_Quantity NUMBER,
41 X_Max_Order_Quantity NUMBER,
42 X_Qty_Rcv_Tolerance NUMBER,
43 X_Over_Tolerance_Error_Flag VARCHAR2,
44 X_Market_Price NUMBER,
45 X_Unordered_Flag VARCHAR2,
46 X_Closed_Flag VARCHAR2,
47 X_User_Hold_Flag VARCHAR2,
48 X_Cancel_Flag VARCHAR2,
49 X_Cancelled_By NUMBER,
50 X_Cancel_Date DATE,
51 X_Cancel_Reason VARCHAR2,
52 X_Firm_Status_Lookup_Code VARCHAR2,
53 X_Firm_Date DATE,
54 X_Vendor_Product_Num VARCHAR2,
55 X_Contract_Num VARCHAR2,
56 X_Taxable_Flag VARCHAR2,
57 X_Tax_Code_Id NUMBER,
58 X_Type_1099 VARCHAR2,
59 X_Capital_Expense_Flag VARCHAR2,
60 X_Negotiated_By_Preparer_Flag VARCHAR2,
61 X_Attribute_Category VARCHAR2,
62 X_Attribute1 VARCHAR2,
63 X_Attribute2 VARCHAR2,
64 X_Attribute3 VARCHAR2,
65 X_Attribute4 VARCHAR2,
66 X_Attribute5 VARCHAR2,
67 X_Attribute6 VARCHAR2,
68 X_Attribute7 VARCHAR2,
69 X_Attribute8 VARCHAR2,
70 X_Attribute9 VARCHAR2,
71 X_Attribute10 VARCHAR2,
72 X_Reference_Num VARCHAR2,
73 X_Attribute11 VARCHAR2,
74 X_Attribute12 VARCHAR2,
75 X_Attribute13 VARCHAR2,
76 X_Attribute14 VARCHAR2,
77 X_Attribute15 VARCHAR2,
78 X_Min_Release_Amount NUMBER,
79 X_Price_Type_Lookup_Code VARCHAR2,
80 X_Closed_Code VARCHAR2,
81 X_Price_Break_Lookup_Code VARCHAR2,
82 X_Ussgl_Transaction_Code VARCHAR2,
83 X_Government_Context VARCHAR2,
84 X_Closed_Date DATE,
85 X_Closed_Reason VARCHAR2,
86 X_Closed_By NUMBER,
87 X_Transaction_Reason_Code VARCHAR2,
88 X_unapprove_doc IN OUT NOCOPY BOOLEAN,
89 X_authorization_status IN OUT NOCOPY VARCHAR2,
90 X_approved_flag IN OUT NOCOPY VARCHAR2,
91 --< NBD TZ/Timestamp FPJ Start >
92 --X_combined_param IN VARCHAR2,
93 -- The following 5 parameters were being combined
94 -- into one due to the historic reasons. That is not
95 -- required now.
96 p_ship_window_open IN VARCHAR2,
97 p_type_lookup_code IN VARCHAR2,
98 p_change_date IN VARCHAR2,
99 p_promised_date IN DATE,
100 p_need_by_date IN DATE,
101 --< NBD TZ/Timestamp FPJ End >
102 p_shipment_block_status IN VARCHAR2, -- bug 4042434
103 X_orig_unit_price IN NUMBER,
104 X_orig_quantity IN NUMBER,
105 X_Global_Attribute_Category VARCHAR2,
106 X_Global_Attribute1 VARCHAR2,
107 X_Global_Attribute2 VARCHAR2,
108 X_Global_Attribute3 VARCHAR2,
109 X_Global_Attribute4 VARCHAR2,
110 X_Global_Attribute5 VARCHAR2,
111 X_Global_Attribute6 VARCHAR2,
112 X_Global_Attribute7 VARCHAR2,
113 X_Global_Attribute8 VARCHAR2,
114 X_Global_Attribute9 VARCHAR2,
115 X_Global_Attribute10 VARCHAR2,
116 X_Global_Attribute11 VARCHAR2,
117 X_Global_Attribute12 VARCHAR2,
118 X_Global_Attribute13 VARCHAR2,
119 X_Global_Attribute14 VARCHAR2,
120 X_Global_Attribute15 VARCHAR2,
121 X_Global_Attribute16 VARCHAR2,
122 X_Global_Attribute17 VARCHAR2,
123 X_Global_Attribute18 VARCHAR2,
124 X_Global_Attribute19 VARCHAR2,
125 X_Global_Attribute20 VARCHAR2,
126 X_Expiration_Date DATE,
127 --Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
128 --Bug# 1056597
129 X_Base_Uom VARCHAR2,
130 X_Base_Qty NUMBER,
131 X_Secondary_Uom VARCHAR2,
132 X_Secondary_Qty NUMBER,
133 X_Qc_Grade VARCHAR2,
134 --togeorge 10/03/2000
135 --added oke columns
136 X_oke_contract_header_id NUMBER default null,
137 X_oke_contract_version_id NUMBER default null,
138 -- 1548597.. added 3 fields for process item..
139 X_Secondary_Unit_of_measure VARCHAR2 default null,
140 X_Secondary_Quantity NUMBER default null,
141 X_preferred_Grade VARCHAR2 default null,
142 p_contract_id IN NUMBER DEFAULT NULL, -- <GC FPJ>
143 X_job_id NUMBER default null, -- <SERVICES FPJ>
144 X_contractor_first_name VARCHAR2 default null, -- <SERVICES FPJ>
145 X_contractor_last_name VARCHAR2 default null, -- <SERVICES FPJ>
146 X_assignment_start_date DATE default null, -- <SERVICES FPJ>
147 X_amount_db NUMBER default null, -- <SERVICES FPJ>
148 -- <FPJ Advanced Price START>
149 X_Base_Unit_Price NUMBER DEFAULT NULL,
150 -- <FPJ Advanced Price END>
151 p_manual_price_change_flag VARCHAR2 DEFAULT NULL, --<Manual Price Override FPJ>
152 p_planned_item_flag VARCHAR2 DEFAULT NULL --bug 5533267
153 ) IS
154
155 X_progress VARCHAR2(3) := NULL;
156
157 X_num_of_shipments number;
158 X_num_of_distributions number;
159
160 --< NBD TZ/Timestamp FPJ Start >
161 --X_type_lookup_code varchar2(25);
162 --X_ship_window_open varchar2(1);
163 --X_change_date varchar2(1);
164 --X_promised_date date;
165 --X_need_by_date date;
166 --X_promised_date_char varchar2(30);
167 --X_need_by_date_char varchar2(30);
168 --< NBD TZ/Timestamp FPJ End >
169
170 X_days_late_receipt_allowed number;
171
172
173 -- PB Bug# 3274039
174 l_opm_item_id number := NULL;
175 l_item_um2 varchar2(4) := NULL;
176 l_dualum_ind number := NULL;
177 l_opm_order_um varchar2(4) := NULL;
178 X_ship_org_id NUMBER := NULL;
179 X_secondary_quantity_ship NUMBER := NULL;
180 X_secondary_quantity_ship_new NUMBER := NULL;
181
182 l_orig_amount po_lines_all.amount%TYPE; -- Bug 3262883
183 l_line_type po_lines_all.order_type_lookup_code%TYPE; -- Bug 3262883
184 l_purchase_basis po_lines_all.purchase_basis%TYPE; -- Bug 3262883
185 l_ip_category_id PO_LINES_ALL.ip_category_id%TYPE; -- Bug 7577670
186
187
188 --OPM bug3455686
189 CURSOR opm_fetch_quantity IS
190 SELECT line_location_id,
191 quantity,
192 ship_to_organization_id
193 FROM po_line_locations
194 WHERE po_line_id = X_po_line_id
195 AND nvl(cancel_flag,'N') = 'N'
196 AND unit_meas_lookup_code <> X_unit_meas_lookup_code
197 AND shipment_type in ('STANDARD','PLANNED')
198 AND secondary_unit_of_measure is NOT NULL;
199
200 l_shipment_quantity number;
201 l_shipment_sec_quantity number;
202 l_line_location_id number;
203 -- End bug3455686
204
205 l_orig_category_id number; --Bug 13067295
206
207
208 BEGIN
209
210 X_progress := '010';
211
212 --Bug 13067295, added one more variable to retrieve the category_id from the db.
213 --Bug 13536036, retrieving the ip_category_id from the po_lines_all tbale for that line_id.
214 --In case if the po_category_id gets updated then we will over ride l_ip_Category_id value later.
215
216
217
218 select order_type_lookup_code,purchase_basis,amount,category_id, ip_category_id
219 into l_line_type,l_purchase_basis ,l_orig_amount,l_orig_category_id,l_ip_category_id
220 from po_lines_all
221 where po_line_id = X_po_line_id;
222
223
224 if ( (p_type_lookup_code = 'STANDARD') OR
225 (p_type_lookup_code = 'PLANNED' ) OR
226 (p_type_lookup_code = 'BLANKET' )) then
227
228 /* Check if the document has to be unapproved */
229
230 if (X_Approved_Flag = 'Y') THEN
231
232 /* ER- 1260356 - Added expiration_date so that any change to expiration_date
233 at the Line Level of the blanket can also be archived */
234
235 -- <GC FPJ>
236 -- Pass in contract_id and remove contract_num
237
238 X_unapprove_doc := po_lines_sv.val_approval_status(
239 X_po_line_id ,
240 p_type_lookup_code ,
241 X_unit_price ,
242 X_line_num ,
243 X_item_id ,
244 X_item_description ,
245 X_quantity ,
246 X_unit_meas_lookup_code ,
247 X_from_header_id ,
248 X_from_line_id ,
249 X_hazard_class_id ,
250 X_vendor_product_num ,
251 X_un_number_id ,
252 X_note_to_vendor ,
253 X_item_revision ,
254 X_category_id ,
255 X_price_type_lookup_code ,
256 X_not_to_exceed_price ,
257 X_quantity_committed ,
258 X_committed_amount ,
259 X_Expiration_Date ,
260 p_contract_id, -- <GC FPJ>
261 X_contractor_first_name, -- <SERVICES FPJ>
262 X_contractor_last_name, -- <SERVICES FPJ>
263 X_assignment_start_date, -- <SERVICES FPJ>
264 X_amount_db -- <SERVICES FPJ>
265 );
266
267 /* If the document has to be unapproved, set the approved_flag to be 'R' */
268 if X_unapprove_doc then
269 -- No changes and no need to change the header's approval status
270
271 X_Approved_Flag := 'X';
272 else
273 -- Found changes and need to change the header's approval status
274
275 X_Approved_Flag := 'Z';
276 end if;
277
278 else
279 X_Approved_Flag := 'U';
280
281 end if; /* end of testing x_approved_flag */
282
283
284 if ( (p_type_lookup_code = 'STANDARD') OR
285 (p_type_lookup_code = 'PLANNED' ) ) then
286
287 /* If the Unit Price on the line has changed, update every shipment of SHIPMENT/PLANNED
288 ** shipment type and that is not cancelled, with this price.
289 ** DEBUG : Move this to POXPOSHB.pls
290 */
291
292 if X_orig_unit_price <> X_unit_price then
293
294 X_progress := '030';
295 /* Bug 1916593, Commenting the Cancel Flag condition, so that the
296 canceled shipments can also be updated. Pl. refer the bug for further
297 info */
298
299 UPDATE po_line_locations
300 SET price_override = X_unit_price,
301 calculate_tax_flag = 'Y',
302 approved_flag = decode(approved_flag, NULL, 'N', 'N','N','R'),
303 last_update_date = sysdate,
304 last_updated_by = X_last_updated_by,
305 last_update_login = X_last_update_login
306 WHERE po_line_id = X_po_line_id
307 AND shipment_type in ('STANDARD','PLANNED') ;
308
309
310 -- set document status to be requires reapproval
311 if X_approved_flag IN ( 'Y', 'X') then
312 X_approved_flag := 'Z';
313 end if;
314
315 end if;
316
317 --OPM bug3455686
318 -- For OPM, If the primary unit of measure has changed on the line/shipment, recompute the
319 -- secondary quantity for the shipments.
320 OPEN opm_fetch_quantity;
321 LOOP
322 Begin
323 FETCH opm_fetch_quantity into l_line_location_id, l_shipment_quantity, x_ship_org_id;
324 EXIT WHEN opm_fetch_quantity%NOTFOUND;
325 --Get opm attributes to derive secondary quantity
326 --We derive the OPM attributes only once since they will
327 --be the same for the item across orgs.
328 IF (l_opm_item_id is NULL) THEN
329 BEGIN
330 SELECT oi.item_id , oi.item_um2, oi.dualum_ind
331 INTO l_opm_item_id, l_item_um2, l_dualum_ind
332 FROM ic_item_mst oi,
333 mtl_system_items ai
334 WHERE ai.organization_id = x_ship_org_id
335 AND ai.inventory_item_id = x_item_id
336 AND ai.segment1 = oi.item_no;
337
338 EXCEPTION WHEN OTHERS THEN
339 l_dualum_ind := 0;
340 l_shipment_sec_quantity := NULL;
341 END;
342 END IF;
343
344 IF nvl(l_dualum_ind,0) <> 0 THEN
345 --Get corresponding opm uom code
346 l_opm_order_um := PO_GML_DB_COMMON.get_opm_uom_code(X_Unit_Meas_Lookup_Code);
347
348 PO_GML_DB_COMMON.validate_quantity( l_opm_item_id,
349 l_dualum_ind,
350 l_shipment_quantity,
351 l_opm_order_um,
352 l_item_um2,
353 l_shipment_sec_quantity);
354
355 /*Bug4906693 who columns like last_updated_by and last_update_login
356 also needs to be updated.*/
357
358 UPDATE po_line_locations
359 SET secondary_quantity = l_shipment_sec_quantity,
360 last_update_date = X_last_update_date,
361 last_updated_by = X_last_updated_by,
362 last_update_login = X_last_update_login
363 WHERE po_line_id = X_po_line_id
364 AND line_location_id = l_line_location_id;
365
366 END IF;
367 EXCEPTION WHEN OTHERS THEN
368 IF (opm_fetch_quantity%FOUND) THEN
369 CLOSE opm_fetch_quantity;
370 END IF;
371 END;
372 END LOOP;
373 CLOSE opm_fetch_quantity;
374 -- End bug3455686
375
376
377
378 -- Bug 731564
379 -- Update all shipments if Unit has changed too.
380
381 /*Bug4906693 who columns like last_updated_by and last_update_login
382 also needs to be updated.*/
383
384 UPDATE po_line_locations
385 SET unit_meas_lookup_code = X_unit_meas_lookup_code,
386 last_update_date = sysdate,
387 last_updated_by = X_last_updated_by,
388 last_update_login = X_last_update_login
389 WHERE po_line_id = X_po_line_id
390 AND nvl(cancel_flag,'N') = 'N'
391 AND unit_meas_lookup_code <> X_unit_meas_lookup_code
392 AND shipment_type in ('STANDARD','PLANNED') ;
393
394
395 /* If there is only one shipment for this location, and the quantity is changed,
396 ** update the shipment. SImilarly, if there is only one distribution, we need to
397 ** update that too.
398 ** DEBUG Move this to the appropriate packages ( POXPOSHB for shipments ..)
399 */
400 -- Bug 3262883
401 ---Bug 13067295, Moved the below query to the begining of this procedure.
402
403 /* select order_type_lookup_code,purchase_basis,amount
404 into l_line_type,l_purchase_basis ,l_orig_amount
405 from po_lines_all
406 where po_line_id = X_po_line_id;*/
407
408 if ((X_orig_quantity <> X_quantity ) or (l_orig_amount <> X_amount_db))
409 then
410
411 -- bug 5856760 For a single shipment and distribution, if the status of shipment is
412 -- finally closed we shld not update the shipment and distribution. For this the
413 -- status is checked in the below sql, so that updation not occur for finally closed shipment.
414 X_progress := '040';
415 SELECT count(pll.po_line_id)
416 INTO X_num_of_shipments
417 FROM po_line_locations pll
418 WHERE pll.po_line_id = X_po_line_id
419 AND NOT EXISTS (SELECT 'there are encumbered or cancelled or drop shipments'
420 FROM po_line_locations pll2
421 WHERE pll2.po_line_id = X_po_line_id
422 AND pll2.shipment_type IN ('STANDARD','PLANNED')
423 AND ( nvl(pll2.encumbered_flag, 'N') <> 'N'
424 OR nvl(pll2.cancel_flag,'N') <> 'N'
425 OR nvl(pll2.closed_code,'OPEN') = 'FINALLY CLOSED' --bug 5856760
426 OR nvl(pll2.drop_ship_flag,'N') <> 'N') --bug 3359011
427 );
428
429 if X_num_of_shipments = 1 then
430
431 X_progress := '050';
432
433 -- PB Bug# 3274039
434 --In case where the financial option purchasing org is discrete
435 --then the line secondary quantity will be null. But if the shipment has a
436 --process org the sec qty will be present. Updating the shipment with the
437 --sec qty(which is null) from the line will nullify it. Hence recomupte it before updating.
438 IF X_item_id is NOT NULL AND X_Secondary_Quantity IS NULL THEN
439
440 SELECT secondary_quantity,
441 ship_to_organization_id
442 INTO X_secondary_quantity_ship,
443 X_ship_org_id
444 FROM po_line_locations pll
445 WHERE pll.po_line_id = X_po_line_id
446 AND pll.shipment_type IN ('STANDARD','PLANNED')
447 AND nvl(pll.cancel_flag,'N') <> 'Y';
448
449 IF X_secondary_quantity_ship IS NOT NULL THEN
450
451 BEGIN
452 --Get opm attributes to derive secondary quantity.
453 SELECT oi.item_id , oi.item_um2, oi.dualum_ind
454 INTO l_opm_item_id, l_item_um2, l_dualum_ind
455 FROM ic_item_mst oi,
456 mtl_system_items ai
457 WHERE ai.organization_id = X_ship_org_id
458 AND ai.inventory_item_id = X_item_id
459 AND ai.segment1 = oi.item_no;
460
461 EXCEPTION WHEN OTHERS THEN
462 l_dualum_ind := 0;
463 X_secondary_quantity_ship_new := NULL;
464 END;
465
466 IF nvl(l_dualum_ind,0) <> 0 THEN
467 --Get corresponding opm uom code
468 l_opm_order_um := PO_GML_DB_COMMON.get_opm_uom_code(X_Unit_Meas_Lookup_Code);
469
470
471 PO_GML_DB_COMMON.validate_quantity( l_opm_item_id,
472 l_dualum_ind,
473 X_Quantity,
474 l_opm_order_um,
475 l_item_um2,
476 X_secondary_quantity_ship_new);
477 END IF;
478 END IF;
479 ELSIF X_item_id is NOT NULL AND X_Secondary_Quantity IS NOT NULL THEN
480 X_secondary_quantity_ship_new := X_Secondary_Quantity;
481 END IF; -- IF X_item_id is NOT null
482 -- End PB Bug# 3274039
483
484 /* Bug - 1101939 - Need to update the calculate_tax_flag to 'Y' so
485 that the tax is recalculated when the shipment quantity is changed
486 automatically */
487
488 IF l_line_type in ('RATE','FIXED PRICE') THEN -- Bug 3262883
489
490 -- bug 4042434: Add check for p_shipment_block_status. In
491 -- prior versions, this update would erroneously occur if
492 -- the line amount was changed before the contents of
493 -- the shipment block were analyzed when saving a PO line.
494
495 IF ((p_ship_window_open = 'N') and (p_shipment_block_status <> 'C')) or
496 ((p_ship_window_open = 'Y') and (l_purchase_basis = 'TEMP LABOR')) THEN
497
498 /*Bug4906693 who columns like last_updated_by and last_update_login
499 also needs to be updated.*/
500
501 UPDATE po_line_locations
502 SET amount = X_amount_db,
503 calculate_tax_flag = 'Y',
504 last_update_date = sysdate,
505 last_updated_by = X_last_updated_by,
506 last_update_login = X_last_update_login,
507 approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
508 -- Bug 5227695. Recalculate tax if tax attributes on
509 -- shipment are being updated
510 tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
511 WHERE po_line_id = X_po_line_id
512 AND nvl(cancel_flag,'N') <> 'Y'
513 AND shipment_type = 'STANDARD';
514
515 END IF;
516
517 ELSE
518
519 -- bug 4042434: Add check for p_shipment_block_status. In
520 -- prior versions, this update would erroneously occur if
521 -- the line quantity was changed before the contents of
522 -- the shipment block were analyzed when saving a PO line.
523
524 IF ((p_ship_window_open = 'N') and (p_shipment_block_status <> 'C')) THEN
525
526 /*Bug4906693 who columns like last_updated_by and last_update_login
527 also needs to be updated.*/
528
529 UPDATE po_line_locations
530 SET quantity = X_quantity,
531 -- start of 1548597 --PB Bug# 3274039 changed the variable to X_secondary_quantity_ship_new
532 secondary_quantity = decode(secondary_quantity,null,null,X_secondary_quantity_ship_new),
533 -- end of 1548597
534 calculate_tax_flag = 'Y',
535 last_update_date = sysdate,
536 last_updated_by = X_last_updated_by,
537 last_update_login = X_last_update_login,
538 approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
539 -- Bug 5227695. Recalculate tax if tax attributes on
540 -- shipment are being updated
541 tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
542 WHERE po_line_id = X_po_line_id
543 AND nvl(cancel_flag,'N') <> 'Y'
544 AND shipment_type IN ('STANDARD','PLANNED');
545
546 END IF;
547
548 END IF;
549
550 -- set document status to be requires reapproval
551 if X_approved_flag IN ('Y','X') then
552 X_approved_flag := 'Z';
553 end if;
554
555 X_progress := '060';
556
557 SELECT count(po_distribution_id)
558 INTO X_num_of_distributions
559 FROM po_distributions pd
560 WHERE pd.po_line_id = X_po_line_id
561 AND NOT EXISTS (SELECT 'there are encumbered distributions'
562 FROM po_distributions pd2
563 WHERE pd2.po_line_id = X_po_line_id
564 AND nvl(pd2.encumbered_flag, 'N') <> 'N');
565
566
567 if X_num_of_distributions = 1 AND (p_ship_window_open = 'N') then
568 -- Bug 6321268. Added the condition p_ship_window_open = 'N'.
569 X_progress := '070';
570 IF l_line_type in ('RATE','FIXED PRICE') THEN -- Bug 3262883
571
572 /*Bug4906693 who columns like last_updated_by and last_update_login
573 also needs to be updated.*/
574
575 UPDATE po_distributions
576 SET amount_ordered = X_amount_db,
577 last_update_date = sysdate,
578 last_updated_by = X_last_updated_by,
579 last_update_login = X_last_update_login
580 WHERE po_line_id = X_po_line_id;
581
582 ELSE
583
584 /*Bug4906693 who columns like last_updated_by and last_update_login
585 also needs to be updated.*/
586
587 UPDATE po_distributions
588 SET quantity_ordered = X_quantity,
589 last_update_date = sysdate,
590 last_updated_by = X_last_updated_by,
591 last_update_login = X_last_update_login
592 WHERE po_line_id = X_po_line_id;
593
594 END IF;
595 end if;
596
597 end if;
598
599 end if; /* Quantity/amount Changed */
600
601 if (p_change_date = 'Y') and (p_ship_window_open = 'N') then
602
603 X_progress := '080';
604
605 SELECT count(pll.po_line_id), max(days_late_receipt_allowed)
606 INTO X_num_of_shipments,
607 X_days_late_receipt_allowed
608 FROM po_line_locations pll
609 WHERE pll.po_line_id = X_po_line_id
610 AND nvl(pll.cancel_flag,'N') <> 'Y'
611 AND pll.shipment_type IN ('STANDARD','PLANNED');
612
613
614 if X_num_of_shipments = 1 then
615
616 X_progress := '090';
617 --START Bug 5533266
618 /*This is to enforce the user to enter either a promise by date or need by date before updating the record.
619 The previous related bugs had the fix at the W-V-R for the PO_LINES block and the same are reverted as a part of this fix */
620
621 IF p_type_lookup_code IN ('STANDARD',
622 'PLANNED') THEN
623 IF p_planned_item_flag = 'Y'
624 AND p_promised_date IS NULL
625 AND p_need_by_date IS NULL THEN
626 po_message_s.app_error('PO_PO_PLANNED_ITEM_DATE_REQ');
627 END IF;
628 END IF;
629 --END Bug 5533266
630
631 /*Bug4906693 who columns like last_updated_by and last_update_login
632 also needs to be updated.*/
633
634 UPDATE po_line_locations
635 SET promised_date = p_promised_date,
636 need_by_date = p_need_by_date,
637 last_accept_date = decode(p_promised_date,NULL,NULL,
638 p_promised_date+nvl(X_days_late_receipt_allowed,0)),
639 last_update_date = sysdate,
640 last_updated_by = X_last_updated_by,
641 last_update_login = X_last_update_login,
642 approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
643 -- Bug 5227695. Recalculate tax if tax attributes on
644 -- shipment are being updated
645 tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
646 WHERE po_line_id = X_po_line_id
647 AND nvl(cancel_flag,'N') <> 'Y'
648 AND shipment_type IN ('STANDARD','PLANNED');
649
650 -- set document status to be requires reapproval
651 if X_approved_flag IN ('Y','X') then
652 X_approved_flag := 'Z';
653 end if;
654
655 end if;
656
657 end if; /* Change Promised and need by dates at shipment level */
658
659
660 end if; /* End of If Standard/Planned */
661 end if; /* End of If Standard/Planned/Blanket */
662
663 ---13067295, Added the if condition to check whether the category_id has changed or not.
664 ---We will retrive the ip_category_id only when the po_category_id is changed.
665
666 if l_orig_category_id <> x_category_id then
667 -- Bug 7577670: Derive ip_category_id from po_category_id
668 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id(p_po_category_id => x_category_id,
669 x_ip_category_id => l_ip_category_id);
670 end if;
671
672
673 /* Update the PO LINE itself */
674
675 po_lines_pkg_sud.update_row(
676 X_Rowid ,
677 X_Po_Line_Id ,
678 X_Last_Update_Date ,
679 X_Last_Updated_By ,
680 X_Po_Header_Id ,
681 X_Line_Type_Id ,
682 X_Line_Num ,
683 X_Last_Update_Login ,
684 X_Item_Id ,
685 X_Item_Revision ,
686 X_Category_Id ,
687 X_Item_Description ,
688 X_Unit_Meas_Lookup_Code ,
689 X_Quantity_Committed ,
690 X_Committed_Amount ,
691 X_Allow_Price_Override_Flag ,
692 X_Not_To_Exceed_Price ,
693 X_List_Price_Per_Unit ,
694 -- <FPJ Advanced Price START>
695 -- Bug 3417479
696 X_Base_Unit_Price,
697 -- <FPJ Advanced Price END>
698 X_Unit_Price ,
699 X_Quantity ,
700 X_Un_Number_Id ,
701 X_Hazard_Class_Id ,
702 X_Note_To_Vendor ,
703 X_From_Header_Id ,
704 X_From_Line_Id ,
705 X_From_Line_Location_Id , -- <SERVICES FPJ>
706 X_Min_Order_Quantity ,
707 X_Max_Order_Quantity ,
708 X_Qty_Rcv_Tolerance ,
709 X_Over_Tolerance_Error_Flag ,
710 X_Market_Price ,
711 X_Unordered_Flag ,
712 X_Closed_Flag ,
713 X_User_Hold_Flag ,
714 X_Cancel_Flag ,
715 X_Cancelled_By ,
716 X_Cancel_Date ,
717 X_Cancel_Reason ,
718 X_Firm_Status_Lookup_Code ,
719 X_Firm_Date ,
720 X_Vendor_Product_Num ,
721 X_Contract_Num ,
722 X_Taxable_Flag ,
723 X_Tax_Code_Id ,
724 X_Type_1099 ,
725 X_Capital_Expense_Flag ,
726 X_Negotiated_By_Preparer_Flag ,
727 X_Attribute_Category ,
728 X_Attribute1 ,
729 X_Attribute2 ,
730 X_Attribute3 ,
731 X_Attribute4 ,
732 X_Attribute5 ,
733 X_Attribute6 ,
734 X_Attribute7 ,
735 X_Attribute8 ,
736 X_Attribute9 ,
737 X_Attribute10 ,
738 X_Reference_Num ,
739 X_Attribute11 ,
740 X_Attribute12 ,
741 X_Attribute13 ,
742 X_Attribute14 ,
743 X_Attribute15 ,
744 X_Min_Release_Amount ,
745 X_Price_Type_Lookup_Code ,
746 X_Closed_Code ,
747 X_Price_Break_Lookup_Code ,
748 NULL , --<R12 SLA>
749 X_Government_Context ,
750 X_Closed_Date ,
751 X_Closed_Reason ,
752 X_Closed_By ,
753 X_Transaction_Reason_Code ,
754 X_Global_Attribute_Category ,
755 X_Global_Attribute1 ,
756 X_Global_Attribute2 ,
757 X_Global_Attribute3 ,
758 X_Global_Attribute4 ,
759 X_Global_Attribute5 ,
760 X_Global_Attribute6 ,
761 X_Global_Attribute7 ,
762 X_Global_Attribute8 ,
763 X_Global_Attribute9 ,
764 X_Global_Attribute10 ,
765 X_Global_Attribute11 ,
766 X_Global_Attribute12 ,
767 X_Global_Attribute13 ,
768 X_Global_Attribute14 ,
769 X_Global_Attribute15 ,
770 X_Global_Attribute16 ,
771 X_Global_Attribute17 ,
772 X_Global_Attribute18 ,
773 X_Global_Attribute19 ,
774 X_Global_Attribute20 ,
775 X_Expiration_Date,
776 --Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
777 --Bug# 1056597
778 X_Base_Uom ,
779 X_Base_Qty ,
780 X_Secondary_Uom ,
781 X_Secondary_Qty ,
782 X_Qc_Grade ,
783 --togeorge 10/03/2000
784 --added oke columns
785 X_oke_contract_header_id ,
786 X_oke_contract_version_id,
787 -- start of 1548597.add 3 process fields..
788 X_secondary_unit_of_measure,
789 X_secondary_quantity,
790 X_preferred_grade,
791 -- end of 1548597
792 p_contract_id, -- <GC FPJ>
793 X_job_id, -- <SERVICES FPJ>
794 X_contractor_first_name, -- <SERVICES FPJ>
795 X_contractor_last_name, -- <SERVICES FPJ>
796 X_assignment_start_date, -- <SERVICES FPJ>
797 X_amount_db, -- <SERVICES FPJ>
798 p_manual_price_change_flag, -- <Manual Price Override FPJ>
799 l_ip_category_id -- Bug 7577670
800 );
801 --Bug 10039388.Added IF. l_ip_category_id = -2 meaning it can not find corresponding record in iP. So no attr to update
802 IF l_ip_category_id IS NOT NULL AND l_ip_category_id <> -2 THEN
803 -- <Bug 7655719>
804 -- update po_attribute_values and po_attribute_values_tlp tables also.
805 PO_ATTRIBUTE_VALUES_PVT.update_attributes(
806 p_doc_type => p_type_lookup_code,
807 p_po_line_id => x_po_line_id,
808 p_req_template_name => NULL,
809 p_req_template_line_num => NULL,
810 p_org_id => PO_MOAC_UTILS_PVT.get_current_org_id,
811 p_ip_category_id => l_ip_category_id,
812 p_item_description => x_item_description,
813 p_language => userenv('LANG')
814 );
815 END IF;
816
817 EXCEPTION
818
819 when others then
820 po_message_s.sql_error('update_line', x_progress, sqlcode);
821 raise;
822 END update_line;
823
824 END PO_LINES_SV11;