[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_SV4_832_UPDATE
Source
1 PACKAGE BODY PO_LINES_SV4_832_UPDATE AS
2 /* $Header: POXPILUB.pls 120.1.12000000.2 2007/07/18 12:25:14 puppulur ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
7
8 /*===========================================================================*/
9 /*======================== SPECIFICATIONS (PRIVATE) =========================*/
10 /*===========================================================================*/
11
12 FUNCTION price_tolerance_check ( p_interface_header_id NUMBER,
13 p_interface_line_id NUMBER,
14 p_item_id NUMBER,
15 p_category_id NUMBER,
16 p_vendor_id NUMBER,
17 p_vendor_site_id NUMBER,
18 p_document_id NUMBER,
19 p_po_line_id NUMBER,
20 p_unit_price NUMBER,
21 p_def_master_org_id NUMBER)
22 return BOOLEAN;
23
24 PROCEDURE delete_price_breaks (x_po_header_id NUMBER, x_po_line_id NUMBER);
25
26 PROCEDURE update_price_discount -- <2703076>
27 ( p_po_line_id IN PO_LINE_LOCATIONS.po_line_id%TYPE
28 , p_unit_price IN PO_LINES.unit_price%TYPE
29 );
30
31 /*===========================================================================*/
32 /*============================ BODY (PUBLIC) ================================*/
33 /*===========================================================================*/
34
35 /*================================================================
36
37 PROCEDURE NAME: update_po_line()
38
39 ==================================================================*/
40
41 PROCEDURE update_po_line( X_interface_header_id IN NUMBER,
42 X_interface_line_id IN NUMBER,
43 X_line_num IN NUMBER,
44 X_po_line_id IN NUMBER,
45 X_shipment_num IN OUT NOCOPY NUMBER,
46 X_line_location_id IN OUT NOCOPY NUMBER,
47 X_shipment_type IN VARCHAR2,
48 X_requisition_line_id IN NUMBER,
49 X_document_num IN VARCHAR2,
50 X_po_header_id IN NUMBER,
51 X_release_num IN NUMBER,
52 X_po_release_id IN NUMBER,
53 X_source_shipment_id IN NUMBER,
54 X_contract_num IN VARCHAR2,
55 X_line_type IN VARCHAR2,
56 X_line_type_id IN NUMBER,
57 X_item IN VARCHAR2,
58 X_item_id IN OUT NOCOPY NUMBER,
59 X_item_revision IN VARCHAR2,
60 X_category IN VARCHAR2,
61 X_category_id IN NUMBER,
62 X_item_description IN VARCHAR2,
63 X_vendor_product_num IN VARCHAR2,
64 X_uom_code IN VARCHAR2,
65 X_unit_of_measure IN VARCHAR2,
66 X_quantity IN NUMBER,
67 X_committed_amount IN NUMBER,
68 X_min_order_quantity IN NUMBER,
69 X_max_order_quantity IN NUMBER,
70 X_base_unit_price IN NUMBER, -- <FPJ Advanced Price>
71 X_unit_price IN NUMBER,
72 X_list_price_per_unit IN NUMBER,
73 X_market_price IN NUMBER,
74 X_allow_price_override_flag IN VARCHAR2,
75 X_not_to_exceed_price IN NUMBER,
76 X_negotiated_by_preparer_flag IN VARCHAR2,
77 X_un_number IN VARCHAR2,
78 X_un_number_id IN NUMBER,
79 X_hazard_class IN VARCHAR2,
80 X_hazard_class_id IN NUMBER,
81 X_note_to_vendor IN VARCHAR2,
82 X_transaction_reason_code IN VARCHAR2,
83 X_taxable_flag IN VARCHAR2,
84 X_tax_name IN VARCHAR2,
85 X_type_1099 IN VARCHAR2,
86 X_capital_expense_flag IN VARCHAR2,
87 X_inspection_required_flag IN VARCHAR2,
88 X_receipt_required_flag IN VARCHAR2,
89 X_payment_terms IN VARCHAR2,
90 X_terms_id IN NUMBER,
91 X_price_type IN VARCHAR2,
92 X_min_release_amount IN NUMBER,
93 X_price_break_lookup_code IN VARCHAR2,
94 X_ussgl_transaction_code IN VARCHAR2,
95 X_closed_code IN VARCHAR2,
96 X_closed_reason IN VARCHAR2,
97 X_closed_date IN DATE,
98 X_closed_by IN NUMBER,
99 X_invoice_close_tolerance IN NUMBER,
100 X_receive_close_tolerance IN NUMBER,
101 X_firm_flag IN VARCHAR2,
102 X_days_early_receipt_allowed IN NUMBER,
103 X_days_late_receipt_allowed IN NUMBER,
104 X_enforce_ship_to_loc_code IN VARCHAR2,
105 X_allow_sub_receipts_flag IN VARCHAR2,
106 X_receiving_routing IN VARCHAR2,
107 X_receiving_routing_id IN NUMBER,
108 X_qty_rcv_tolerance IN NUMBER,
109 X_over_tolerance_error_flag IN VARCHAR2,
110 X_qty_rcv_exception_code IN VARCHAR2,
111 X_receipt_days_exception_code IN VARCHAR2,
112 X_ship_to_organization_code IN VARCHAR2,
113 X_ship_to_organization_id IN NUMBER,
114 X_ship_to_location IN VARCHAR2,
115 X_ship_to_location_id IN NUMBER,
116 X_need_by_date IN DATE,
117 X_promised_date IN DATE,
118 X_accrue_on_receipt_flag IN VARCHAR2,
119 X_lead_time IN NUMBER,
120 X_lead_time_unit IN VARCHAR2,
121 X_price_discount IN NUMBER,
122 X_freight_carrier IN VARCHAR2,
123 X_fob IN VARCHAR2,
124 X_freight_terms IN VARCHAR2,
125 X_effective_date IN DATE,
126 X_expiration_date IN DATE,
127 X_from_header_id IN NUMBER,
128 X_from_line_id IN NUMBER,
129 X_from_line_location_id IN NUMBER,
130 X_line_attribute_catg_lines IN VARCHAR2,
131 X_line_attribute1 IN VARCHAR2,
132 X_line_attribute2 IN VARCHAR2,
133 X_line_attribute3 IN VARCHAR2,
134 X_line_attribute4 IN VARCHAR2,
135 X_line_attribute5 IN VARCHAR2,
136 X_line_attribute6 IN VARCHAR2,
137 X_line_attribute7 IN VARCHAR2,
138 X_line_attribute8 IN VARCHAR2,
139 X_line_attribute9 IN VARCHAR2,
140 X_line_attribute10 IN VARCHAR2,
141 X_line_attribute11 IN VARCHAR2,
142 X_line_attribute12 IN VARCHAR2,
143 X_line_attribute13 IN VARCHAR2,
144 X_line_attribute14 IN VARCHAR2,
145 X_line_attribute15 IN VARCHAR2,
146 X_shipment_attribute_category IN VARCHAR2,
147 X_shipment_attribute1 IN VARCHAR2,
148 X_shipment_attribute2 IN VARCHAR2,
149 X_shipment_attribute3 IN VARCHAR2,
150 X_shipment_attribute4 IN VARCHAR2,
151 X_shipment_attribute5 IN VARCHAR2,
152 X_shipment_attribute6 IN VARCHAR2,
153 X_shipment_attribute7 IN VARCHAR2,
154 X_shipment_attribute8 IN VARCHAR2,
155 X_shipment_attribute9 IN VARCHAR2,
156 X_shipment_attribute10 IN VARCHAR2,
157 X_shipment_attribute11 IN VARCHAR2,
158 X_shipment_attribute12 IN VARCHAR2,
159 X_shipment_attribute13 IN VARCHAR2,
160 X_shipment_attribute14 IN VARCHAR2,
161 X_shipment_attribute15 IN VARCHAR2,
162 X_last_update_date IN DATE,
163 X_last_updated_by IN NUMBER,
164 X_last_update_login IN NUMBER,
165 X_creation_date IN DATE,
166 X_created_by IN NUMBER,
167 X_request_id IN NUMBER,
168 X_program_application_id IN NUMBER,
169 X_program_id IN NUMBER,
170 X_program_update_date IN DATE,
171 X_organization_id IN NUMBER,
172 X_item_attribute_category IN VARCHAR2,
173 X_item_attribute1 IN VARCHAR2,
174 X_item_attribute2 IN VARCHAR2,
175 X_item_attribute3 IN VARCHAR2,
176 X_item_attribute4 IN VARCHAR2,
177 X_item_attribute5 IN VARCHAR2,
178 X_item_attribute6 IN VARCHAR2,
179 X_item_attribute7 IN VARCHAR2,
180 X_item_attribute8 IN VARCHAR2,
181 X_item_attribute9 IN VARCHAR2,
182 X_item_attribute10 IN VARCHAR2,
183 X_item_attribute11 IN VARCHAR2,
184 X_item_attribute12 IN VARCHAR2,
185 X_item_attribute13 IN VARCHAR2,
186 X_item_attribute14 IN VARCHAR2,
187 X_item_attribute15 IN VARCHAR2,
188 X_unit_weight IN NUMBER,
189 X_weight_uom_code IN VARCHAR2,
190 X_volume_uom_code IN VARCHAR2,
191 X_unit_volume IN NUMBER,
192 X_template_id IN NUMBER,
193 X_template_name IN VARCHAR2,
194 X_line_reference_num IN VARCHAR2,
195 X_sourcing_rule_name IN VARCHAR2,
196 X_quantity_committed IN NUMBER,
197 X_government_context IN VARCHAR2,
198 X_hd_load_sourcing_flag IN VARCHAR2,
199 X_load_sourcing_rules_flag IN VARCHAR2,
200 X_update_po_line_flag IN VARCHAR2,
201 X_create_po_line_loc_flag IN VARCHAR2,
202 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
203 X_create_items IN VARCHAR2, -- create or update item
204 X_def_purch_org_id IN NUMBER,
205 X_def_inv_org_id IN NUMBER,
206 X_def_master_org_id IN NUMBER,
207 X_approved_flag IN VARCHAR2,
208 X_approved_date IN DATE,
209 X_vendor_id IN NUMBER,
210 X_document_type IN VARCHAR2,
211 X_current_po_header_id IN NUMBER,
212 X_line_quantity IN NUMBER,
213 X_approval_status IN VARCHAR2,
214 X_rel_gen_method IN VARCHAR2,
215 X_price_tolerance_flag IN OUT NOCOPY VARCHAR2,
216 X_price_breaks_deleted IN OUT NOCOPY VARCHAR2,
217 x_line_updated_flag IN OUT NOCOPY VARCHAR2,
218 --togeorge 09/28/2000
219 --added oke variables
220 X_note_to_receiver IN VARCHAR2,
221 X_oke_contract_header_id IN NUMBER,
222 X_oke_contract_version_id IN NUMBER,
223 --<SERVICES FPJ START>
224 p_job_id IN NUMBER,
225 p_amount IN NUMBER,
226 p_order_type_lookup_code IN VARCHAR2,
227 p_purchase_basis IN VARCHAR2
228 --<SERVICES FPJ END>
229 )
230 IS
231
232 X_progress VARCHAR2(3) := NULL;
233 X_cancel_flag VARCHAR2(1) := NULL;
234 X_unordered_flag VARCHAR2(1) := NULL;
235 X_result_flag BOOLEAN := FALSE;
236 X_update_item VARCHAR2(2);
237 X_allow_item_desc_update_flag mtl_system_items.allow_item_desc_update_flag%TYPE;
238 X_msi_item_description mtl_system_items.description%TYPE := NULL;
239 x_doc_line_unit_price NUMBER;
240 x_current_line_uom_code varchar2(25);
241 x_current_line_item_desc varchar2(240);
242 X_current_expiration_date date;
243 l_process_code VARCHAR2(25);
244 l_start_date date;
245 l_end_date date;
246 /* Bug 2722795 */
247 l_retroactive_date po_lines.retroactive_date%type := null;
248 l_price_break_lookup_code po_lines.price_break_lookup_code%type;
249 l_transaction_flow_header_id
250 PO_LINE_LOCATIONS_ALL.transaction_flow_header_id%TYPE; --< Shared Proc FPJ >
251 l_uom_valid boolean := TRUE; -- bug 3335027
252 l_uom_different boolean := FALSE; -- bug 3335027
253 l_update_exp_date boolean := FALSE; -- bug 3335027
254 l_conv_price number;
255 l_conv_rate number;
256 l_precision FND_CURRENCIES.precision%type;
257 l_header_processable_flag varchar2(1);
258 l_price_break_ct number;
259 l_current_line_price_limit number;
260
261 Begin
262
263 IF (g_po_pdoi_write_to_file = 'Y') THEN
264 PO_DEBUG.put_line ('Start update of line/creation of price breaks');
265 PO_DEBUG.put_line ('header id:'|| to_char(X_current_po_header_id));
266 PO_DEBUG.put_line ('line id:'|| to_char(X_po_line_id ));
267 PO_DEBUG.put_line ('X_update_po_line_flag:' || X_update_po_line_flag );
268 PO_DEBUG.put_line ('X_price_tolerance_flag:' || X_price_tolerance_flag);
269 END IF;
270
271 x_line_updated_flag := 'N';
272
273 -- If X_update_po_line_flag is 'Y' then update corresponding line in po_lines.
274 -- If X_create_po_line_loc_flag is 'Y' then create a price break in po_line_locations.
275
276 X_progress := '010';
277
278
279 /* begin bug 3335027
280 We need to validate UOM and expiration date before updating anything. */
281
282 select unit_meas_lookup_code ,
283 not_to_exceed_price
284 into x_current_line_uom_code,
285 l_current_line_price_limit
286 from po_lines
287 where po_header_id = x_current_po_header_id
288 and po_line_id = x_po_line_id;
289
290 IF ((x_current_line_uom_code is null and p_order_type_lookup_code <> 'FIXED PRICE') or
291 (x_unit_of_measure is not null and x_unit_of_measure <> nvl(x_current_line_uom_code,x_unit_of_measure))) then
292
293 l_uom_valid := po_unit_of_measures_sv1.val_unit_of_measure(
294 X_unit_of_measure,
295 NULL);
296 IF (l_uom_valid = FALSE) THEN
297 po_interface_errors_sv1.handle_interface_errors(
298 'PO_DOCS_OPEN_INTERFACE',
299 'FATAL',
300 null,
301 X_interface_header_id,
302 X_interface_line_id,
303 'PO_PDOI_INVALID_UOM_CODE',
304 'PO_LINES_INTERFACE',
305 'UNIT_OF_MEASURE',
306 'VALUE',
307 null, null, null, null, null,
308 X_unit_of_measure,
309 null, null, null, null, null,
310 X_header_processable_flag);
311 ELSE
312 l_uom_different := TRUE;
313 END IF;
314
315 END IF;
316 /* End bug 3335027 */
317
318 IF ((X_update_po_line_flag = 'Y') and (X_header_processable_flag = 'Y')) THEN
319
320 IF (g_po_pdoi_write_to_file = 'Y') THEN
321 PO_DEBUG.put_line('Skip line validation process for line update');
322 END IF;
323
324 X_progress := '020';
325
326 -- During validation of item we also check if the item
327 -- description or price for the item is different at item master
328 -- if it is different and item updates are allowed
329 -- allow_item_desc_update_flag in mtl_system_items is 'Y' then we
330 -- update the two in mtl_system_items table.
331
332 -- We allow update to the master items description through the update action
333 -- if allow_item_desc_update_flag is 'N' and description is different we log errors
334 -- except for one-time items.
335
336 -- we need not care about the other function performed by the call. i.e
337 -- insert_item_master()
338 -- handle_ioi_to_po_errors() - in case there are errors in updating/creating the item.
339 -- val_item_interface()
340 -- these are for inventory interface.
341
342 IF (g_po_pdoi_write_to_file = 'Y') THEN
343 PO_DEBUG.put_line('Item description is:' || x_item_description);
344 END IF;
345 /*
346 Bug 2696413
347 While updating we do not need to check the item description. If it
348 exists we can proceed with updating if required else skip the item
349 updation.
350 */
351
352 /*** also need to find out if item_description is different from
353 what is setup for the item. Would not allow item_description update
354 if item attribute allow_item_desc_update_flag is N
355 ****/
356 X_progress := '021';
357
358 if x_po_line_id is not NULL then
359 select item_description into x_current_line_item_desc
360 from po_lines
361 where po_header_id = x_current_po_header_id
362 and po_line_id = x_po_line_id;
363 end if;
364
365 if X_item_id is not null then -- item exists in item master.
366
367 /** Bug 5366732 If foreign language is used then item_desc comparision was always
368 Failing because derived value of X_item_description was coming from
369 mtl_system_items_tl to keep consistency changing below SQL to fetch item desc
370 from mtl_system_items_tl **/
371
372 X_msi_item_description:=null;
373
374 SELECT msi.allow_item_desc_update_flag,
375 mtl.description
376 INTO X_allow_item_desc_update_flag,
377 X_msi_item_description
378 FROM mtl_system_items msi, mtl_system_items_tl mtl
379 WHERE mtl.inventory_item_id = msi.inventory_item_id
380 and mtl.organization_id = msi.organization_id
381 and mtl.language = USERENV('LANG')
382 and mtl.inventory_item_id = X_item_id
383 and msi.organization_id = X_def_inv_org_id;
384 /* Bug 5366732 End */
385
386 else
387 X_allow_item_desc_update_flag := 'Y';
388 end if;
389
390 X_progress := '022';
391 /*Bug 1267907
392 On an update action if the item description in the edi file is different from
393 the description in the catalog and the allow_item_desc_flag_update is Yes, then
394 update po_lines.item_description ,need not check if create_items is Yes too
395 bcos that is only for checking if the item_master needs to be updated too.
396 */
397 IF (X_allow_item_desc_update_flag = 'N')
398 AND
399 (X_item_description <> nvl(X_msi_item_description, X_item_description)
400 OR
401 X_item_description <> x_current_line_item_desc
402 )
403 THEN
404 /*** error because descriptions do not match and item attribute
405 does not allow item description update and update item runtime
406 parameter is set to N.
407 both these flags must be 'Y' for desc to be updated.
408 ***/
409
410 X_progress := '110';
411 X_update_item := 'N';
412
413 po_interface_errors_sv1.handle_interface_errors(
414 'PO_DOCS_OPEN_INTERFACE',
415 'FATAL',
416 null,
417 X_interface_header_id,
418 X_interface_line_id,
419 'PO_PDOI_DIFF_ITEM_DESC',
420 'PO_LINES_INTERFACE',
421 'ITEM_DESCRIPTION',
422 null, null, null, null, null, null,
423 null, null, null, null, null, null,
424 X_header_processable_flag);
425
426 ELSE /* Bug 2696413 */
427 if (x_item_description is null ) then
428 X_update_item := 'N';
429 else
430 X_update_item := 'Y';
431 end if;
432 END IF;
433
434 /* Bug 1267907
435 Added the check to update the item master only if
436 the value of x_create_items is set to yes too.
437 */
438
439 if (X_update_item = 'Y' and
440 NVL(X_create_items, 'N') = 'Y' and
441 X_item_id is not NULL) then
442
443 -- Update item master ( mtl_system_items table )
444
445 UPDATE mtl_system_items
446 SET description = x_item_description,
447 last_update_date = sysdate,
448 last_updated_by = fnd_global.user_id,
449 last_update_login = fnd_global.login_id,
450 request_id = fnd_global.conc_request_id,
451 program_application_id = fnd_global.prog_appl_id,
452 program_id = fnd_global.conc_program_id,
453 program_update_date = sysdate
454 WHERE inventory_item_id = X_item_id
455 AND organization_id = x_def_master_org_id;
456
457 /* Bug 2064714 - GMudgal
458 ** Added the following update statement since we need to update
459 ** the description in the tl table as well */
460
461 UPDATE mtl_system_items_tl
462 SET description = x_item_description
463 WHERE inventory_item_id = X_item_id
464 AND organization_id = x_def_master_org_id
465 and language = USERENV('LANG');
466
467 end if;
468
469 if (X_update_item = 'Y') then
470
471 IF (g_po_pdoi_write_to_file = 'Y') THEN
472 PO_DEBUG.put_line ('Updating the desc to:' || x_item_description);
473 END IF;
474
475 UPDATE po_lines
476 SET item_description = x_item_description,
477 last_update_date = sysdate,
478 last_updated_by = fnd_global.user_id,
479 last_update_login = fnd_global.login_id,
480 request_id = fnd_global.conc_request_id,
481 program_application_id = fnd_global.prog_appl_id,
482 program_id = fnd_global.conc_program_id,
483 program_update_date = sysdate
484 WHERE po_line_id = x_po_line_id
485 AND po_header_id = x_current_po_header_id;
486
487 x_line_updated_flag := 'Y';
488
489 end if;
490
491 -- Need to populate the following fields . These columns do not appear in the interface table.
492
493 IF (X_document_type = 'BLANKET') THEN
494 X_unordered_flag := 'N';
495 X_cancel_flag := 'N';
496
497 select trunc(expiration_date) into x_current_expiration_date
498 from po_lines
499 where po_header_id = x_current_po_header_id
500 and po_line_id = x_po_line_id;
501
502 if (NVL(trunc(x_expiration_date), trunc(sysdate)) <> NVL(trunc(x_current_expiration_date), trunc(sysdate))
503 OR
504 x_expiration_date is not null and x_current_expiration_date is null ) then
505
506 l_update_exp_date := TRUE;
507 --bug 3335027 comment below update since expiration date will be updated when unit_price is
508 end if;
509
510 ELSE
511 X_unordered_flag := NULL;
512 X_cancel_flag := NULL;
513 END IF;
514
515 IF (g_po_pdoi_write_to_file = 'Y') THEN
516 PO_DEBUG.put_line ('Unit price update - unit_price:' || to_char(X_unit_price));
517 END IF;
518
519 select unit_price, retroactive_date,price_break_lookup_code -- 2722795
520 into x_doc_line_unit_price,l_retroactive_date,l_price_break_lookup_code --2722795
521 from po_lines
522 where po_header_id = x_current_po_header_id
523 and po_line_id = x_po_line_id;
524
525
526 IF (X_unit_price is NOT NULL and NVL(x_unit_price, x_doc_line_unit_price) <> x_doc_line_unit_price) then
527
528 -- Perform price tolerance check,
529 -- If successful then Archive the line and then update the price information.
530
531 -- Check price tolerance.
532
533
534 if(l_uom_different = FALSE) then -- bug 3335027: check price tolerance only if UOM is same
535 X_result_flag := price_tolerance_check (X_interface_header_id,
536 X_interface_line_id,
537 X_item_id,
538 x_category_id,
539 X_vendor_id,
540 NULL, -- X_vendor_site_id
541 x_po_header_id,
542 x_po_line_id,
543 X_unit_price,
544 x_def_master_org_id);
545 else
546 x_result_flag := TRUE;
547 end if; --bug 3335027
548
549 If X_result_flag then
550
551 -- Update the price information.
552
553 /* Bug 2722795. When we update an existing blanket with
554 * a new unit_price, retroactive_date in po_lines must
555 * be updated with the timestamp. This has to be done
556 * for non-cumulative blanket lines only.
557 */
558 IF (g_po_pdoi_write_to_file = 'Y') THEN
559 PO_DEBUG.put_line('X_price_break_lookup_code '||X_price_break_lookup_code);
560 PO_DEBUG.put_line('X_document_type '||X_document_type);
561 END IF;
562 IF ((X_document_type = 'BLANKET') and
563 (nvl(l_price_break_lookup_code,'NON CUMULATIVE') =
564 'NON CUMULATIVE')) THEN
565 l_retroactive_date := sysdate;
566 END IF;
567
568 IF (g_po_pdoi_write_to_file = 'Y') THEN
569 PO_DEBUG.put_line ('Updating the unit price');
570 END IF;
571
572
573 UPDATE po_lines
574 SET unit_price = X_unit_price,
575 base_unit_price = NVL(X_base_unit_price, X_unit_price), -- <FPJ Advanced Price>
576 retroactive_date = l_retroactive_date, -- 2722795
577 last_update_date = sysdate,
578 last_updated_by = fnd_global.user_id,
579 last_update_login = fnd_global.login_id,
580 request_id = fnd_global.conc_request_id,
581 program_application_id = fnd_global.prog_appl_id,
582 program_id = fnd_global.conc_program_id,
583 program_update_date = sysdate
584 WHERE po_line_id = x_po_line_id
585 AND po_header_id = x_current_po_header_id;
586
587 -- <2703076 START>: For Blankets, we do not delete Price Breaks when
588 -- updating the Line. Therefore, we need to specifically go and update
589 -- price_discount in Price Breaks to reflect the new Line price.
590 --
591 IF ( x_document_type = 'BLANKET' ) THEN
592
593 update_price_discount( x_po_line_id, x_unit_price );
594
595 ELSE -- Else, for non-Blankets, delete all Price Breaks for this Line.
596
597 delete_price_breaks ( x_current_po_header_id, x_po_line_id );
598 x_price_breaks_deleted := 'Y';
599
600 END IF;
601 --
602 -- <2703076>
603
604 x_line_updated_flag := 'Y';
605 X_price_tolerance_flag := 'N';
606 else
607 -- Price tolerance check failed.
608 -- Mark the line as Notified
609
610 update po_lines_interface
611 set process_code = 'NOTIFIED'
612 where interface_line_id = X_interface_line_id
613 and interface_header_id = X_interface_header_id;
614
615 X_price_tolerance_flag := 'Y';
616 l_update_exp_date := FALSE; --bug 3335027
617
618 end if;
619 END IF; -- x_unit_price not null
620
621 --begin bug 3335027: update expiration date
622 IF(l_update_exp_date) THEN
623 UPDATE po_lines
624 SET expiration_date = trunc(x_expiration_date),
625 last_update_date = trunc(sysdate),
626 last_updated_by = fnd_global.user_id,
627 last_update_login = fnd_global.login_id,
628 request_id = fnd_global.conc_request_id,
629 program_application_id = fnd_global.prog_appl_id,
630 program_id = fnd_global.conc_program_id,
631 program_update_date = trunc(sysdate)
632 WHERE po_line_id = x_po_line_id
633 AND po_header_id = x_current_po_header_id;
634 END IF;
635 --end bug 3335027
636 -- Update UOM
637
638
639 if (x_unit_of_measure is not null and
640 upper(x_unit_of_measure) <> nvl(upper(x_current_line_uom_code),upper(x_unit_of_measure))) then
641
642 IF (g_po_pdoi_write_to_file = 'Y') THEN
643 PO_DEBUG.put_line ('UOM update - current uom on line :' || x_current_line_uom_code);
644 PO_DEBUG.put_line ('UOM update - new uom :' ||x_unit_of_measure );
645 PO_DEBUG.put_line ('UOM update - new price :' ||X_unit_price );
646 END IF;
647
648 select fnd.precision
649 into l_precision
650 from fnd_currencies fnd,
651 po_headers poh
652 where poh.currency_code = fnd.currency_code
653 and poh.po_header_id = x_current_po_header_id;
654
655 -- Bug 3346174
656 -- If a blanket line is updated with a different UOM then the
657 -- unit price on the line is updated with the price converted
658 -- to the new uom. Also a warning message is inserted in the
659 -- interface table to let the users know that they need to update
660 -- the price limit and price break prices accordingly.
661 -- Bug 3489387
662 -- The existing price is converted only if a new price is not
663 -- specified.
664 IF (X_unit_price is NULL) THEN
665
666 Begin
667 po_uom_s.po_uom_conversion(x_current_line_uom_code,
668 x_unit_of_measure,
669 nvl(X_item_id,0),
670 l_conv_rate );
671
672 l_conv_price := round((x_doc_line_unit_price/l_conv_rate),l_precision);
673
674 Exception
675 When others then
676 -- Log an error message if uom conversion fails for some reason
677 po_interface_errors_sv1.handle_interface_errors(
678 'PO_DOCS_OPEN_INTERFACE',
679 'FATAL',
680 null,
681 X_interface_header_id,
682 X_interface_line_id,
683 'PO_PDOI_INVALID_UOM_CODE',
684 'PO_LINES_INTERFACE',
685 'UNIT_OF_MEASURE',
686 'VALUE',
687 null,null, null, null, null,
688 x_unit_of_measure,
689 null,null, null, null, null,
690 X_header_processable_flag );
691 end;
692
693 ELSE
694 l_conv_price := X_unit_price;
695 END IF;
696
697 -- update uom and converted price on the doc.
698
699 IF (g_po_pdoi_write_to_file = 'Y') THEN
700 PO_DEBUG.put_line ('Updating the UOM to:' || x_unit_of_measure);
701 PO_DEBUG.put_line ('Updating the price to:' || X_unit_price);
702 END IF;
703
704 IF (X_header_processable_flag = 'Y') THEN
705
706 UPDATE po_lines
707 SET unit_meas_lookup_code = x_unit_of_measure,
708 unit_price = l_conv_price,
709 base_unit_price = NVL(X_base_unit_price, l_conv_price),
710 retroactive_date = l_retroactive_date,
711 last_update_date = sysdate,
712 last_updated_by = fnd_global.user_id,
713 last_update_login = fnd_global.login_id,
714 request_id = fnd_global.conc_request_id,
715 program_application_id = fnd_global.prog_appl_id,
716 program_id = fnd_global.conc_program_id,
717 program_update_date = sysdate
718 WHERE po_line_id = x_po_line_id
719 AND po_header_id = x_current_po_header_id;
720
721 -- pass a local header processable flag so that the actual
722 -- flag is not updated which marks the record as failed as
723 -- this message is just supposed to be a warning
724 -- Insert the warning only if price breaks exist
725 Begin
726 select count(*)
727 into l_price_break_ct
728 from po_line_locations_all
729 where po_line_id = x_po_line_id
730 and shipment_type = 'PRICE BREAK';
731 Exception
732 When others then
733 l_price_break_ct := 0;
734 End;
735
736 IF l_price_break_ct <> 0 OR l_current_line_price_limit is not null THEN
737
738 IF X_unit_price is null THEN -- Bug 3489387
739 po_interface_errors_sv1.handle_interface_errors(
740 'PO_DOCS_OPEN_INTERFACE',
741 'WARNING',
742 null,
743 X_interface_header_id,
744 X_interface_line_id,
745 'PO_BLANKET_UPDATE_PRICE_BREAKS',
746 'PO_LINES_INTERFACE',
747 'UNIT_OF_MEASURE',
748 null,null, null, null, null, null,
749 null,null, null, null, null, null,
750 l_header_processable_flag );
751 ELSE
752 po_interface_errors_sv1.handle_interface_errors(
753 'PO_DOCS_OPEN_INTERFACE',
754 'WARNING',
755 null,
756 X_interface_header_id,
757 X_interface_line_id,
758 'PO_BLANKET_UPDATE_PB_NO_CONV',
759 'PO_LINES_INTERFACE',
760 'UNIT_OF_MEASURE',
761 null,null, null, null, null, null,
762 null,null, null, null, null, null,
763 l_header_processable_flag );
764
765 END IF; -- unit price populated in the interface
766
767 END IF; -- price limit or price breaks exist
768
769 x_line_updated_flag := 'Y';
770
771 END IF;
772 end if;
773
774 -- Update URL
775
776 If (X_line_attribute14 is not NULL) then
777
778 -- Note that URL changes do not require archiving hence x_line_updated_flag is not set to 'Y'
779 IF (g_po_pdoi_write_to_file = 'Y') THEN
780 PO_DEBUG.put_line ('Updating the URL to:' || X_line_attribute14);
781 END IF;
782
783 UPDATE po_lines
784 SET ATTRIBUTE14 = X_line_attribute14,
785 last_update_date = sysdate,
786 last_updated_by = fnd_global.user_id,
787 last_update_login = fnd_global.login_id,
788 request_id = fnd_global.conc_request_id,
789 program_application_id = fnd_global.prog_appl_id,
790 program_id = fnd_global.conc_program_id,
791 program_update_date = sysdate
792 WHERE po_line_id = x_po_line_id
793 AND po_header_id = x_current_po_header_id;
794
795 end if;
796
797 --<SERVICES FPJ START>
798 X_progress := '030';
799
800 IF (g_po_pdoi_write_to_file = 'Y') THEN
801 PO_DEBUG.put_line ('Start updating amount');
802 END IF;
803
804 IF (NVL(p_amount, 0) <> 0) THEN
805 UPDATE PO_LINES
806 SET amount = p_amount,
807 last_update_date = sysdate,
808 last_updated_by = FND_GLOBAL.user_id,
809 last_update_login = FND_GLOBAL.login_id,
810 request_id = FND_GLOBAL.conc_request_id,
811 program_application_id = FND_GLOBAL.prog_appl_id,
812 program_id = FND_GLOBAL.conc_program_id,
813 program_update_date = sysdate
814 WHERE po_line_id = x_po_line_id
815 AND po_header_id = x_current_po_header_id;
816 END IF;
817
818 --<SERVICES FPJ END>
819
820 END IF; -- condition for (X_update_po_line_flag = 'Y') and ...
821
822
823 --<SERVICES FPJ START>
824 --Add price differential records to an existing blanket line
825 X_progress := '040';
826
827 IF (g_po_pdoi_write_to_file = 'Y') THEN
828 PO_DEBUG.put_line ('Start create price differentials for the line');
829 PO_DEBUG.put_line('**update_po_line_flag: '||x_update_po_line_flag
830 || ' header_processable_flag: '||x_header_processable_flag
831 || 'order_type_lookup_code: '||p_order_type_lookup_code);
832
833 END IF;
834
835
836 IF ((X_update_po_line_flag = 'Y') AND (X_header_processable_flag = 'Y')) THEN
837 IF (p_order_type_lookup_code = 'RATE') THEN
838
839 PO_PRICE_DIFFERENTIALS_PVT.validate_price_differentials(
840 p_interface_header_id => X_interface_header_id,
841 p_interface_line_id => X_interface_line_id,
842 p_entity_type => 'BLANKET LINE',
843 p_entity_id => X_po_line_id,
844 p_header_processable_flag => X_header_processable_flag);
845
846 IF (g_po_pdoi_write_to_file = 'Y') THEN
847 PO_DEBUG.put_line ('interface_line_id: '||x_interface_line_id||
848 ' entity_id: ' ||x_po_line_id);
849 END IF;
850
851 --create price differential records
852 PO_PRICE_DIFFERENTIALS_PVT.create_from_interface(
853 p_interface_line_id => X_interface_line_id,
854 p_entity_id => X_po_line_id);
855
856 END IF; --IF (p_order_type_lookup_code = 'RATE')
857 END IF; --IF ((X_update_po_line_flag = 'Y')...
858 --<SERVICES FPJ END>
859
860 --
861 -- Line is a price break line if X_create_po_line_loc_flag = 'Y' - insert in po_line_locations.
862 --
863
864 IF (X_create_po_line_loc_flag = 'Y') AND
865 (X_header_processable_flag = 'Y') AND
866 (X_price_tolerance_flag = 'N') THEN
867
868 /* <TIMEPHASED FPI START> */
869 /* If the document is a Blanket Agreement, pricebreak deletion is prevented */
870 if (X_document_type <> 'BLANKET') then
871 IF NVL(X_price_breaks_deleted, 'N') = 'N' then
872
873 -- Delete all the price breaks for this line - if any
874 delete_price_breaks (x_current_po_header_id, x_po_line_id);
875 X_price_breaks_deleted := 'Y';
876 END IF;
877 end if;
878 /* <TIMEPHASED FPI END> */
879
880 IF (g_po_pdoi_write_to_file = 'Y') THEN
881 PO_DEBUG.put_line ('Start Creating Price Break/ Shipment Line - X_price_breaks_deleted:' || X_price_breaks_deleted);
882 END IF;
883
884 X_progress := '055';
885 x_line_updated_flag := 'Y';
886 IF (g_po_pdoi_write_to_file = 'Y') THEN
887 PO_DEBUG.put_line('Start validate po_line_coordination process');
888 END IF;
889
890 /* call this procedure to make sure that for each po_line_location
891 record that we are going to create, we will always be able to
892 find a coordinated match in po_lines
893 */
894
895 --
896 -- We derive the shipment number and line_location_id at this stage as the original price breaks
897 -- have already been deleted.
898 --
899
900 IF (X_shipment_num IS NULL ) THEN
901 SELECT NVL(MAX(shipment_num),0) +1
902 INTO X_shipment_num
903 FROM po_line_locations
904 WHERE po_header_id = X_po_header_id
905 AND po_line_id = X_po_line_id
906 --Bug# 1549896
907 --togeorge 01/29/2001
908 --The above condition only takes care of the case of BLANKETS
909 --but ignores the case of QUOTATIONS which have a shipment_type='QUOTATION'
910 --Hence using X_shipment_type instead of PRICE BREAK below.
911 AND shipment_type = X_shipment_type;
912 --AND shipment_type = 'PRICE BREAK';
913 END IF;
914 /* Bug 2794986 commented if condition which generates line_location_id
915 rather we will generate the new id everytime.
916 earlier it was retaining old line_location_id which was in turn
917 creating problems in PO change history comparisons.
918 */
919
920
921
922 SELECT po_line_locations_s.nextval
923 INTO X_line_location_id
924 FROM dual;
925
926
927 /* Bug 2845962. Added a new parameter, X_line_num. */
928 po_line_locations_sv7.validate_po_line_coordination(
929 X_interface_header_id,
930 X_interface_line_id,
931 X_item_id,
932 X_item_description,
933 X_item_revision,
934 X_po_line_id,
935 X_current_po_header_id,
936 X_unit_of_measure,
937 X_line_type_id,
938 X_category_id,
939 X_document_type,
940 X_header_processable_flag,
941 X_line_num,
942 p_job_id); --<SERVICES FPJ>
943
944 X_progress := '060';
945 /* if after line default, the create_po_line_location_flag
946 is 'Y', then we will go ahead and validate line location */
947 IF (g_po_pdoi_write_to_file = 'Y') THEN
948 PO_DEBUG.put_line('Start validate line locations');
949 END IF;
950
951 --
952 -- DO WE perform a complete validation. YES as we are creating a new line
953 --
954
955 po_line_locations_sv7.validate_po_line_locations(
956 X_interface_header_id,
957 X_interface_line_id,
958 X_line_location_id,
959 X_last_update_date,
960 X_last_updated_by,
961 X_current_po_header_id,
962 X_po_line_id,
963 X_last_update_login ,
964 X_creation_date,
965 X_created_by,
966 X_quantity,
967 NULL, /* quantity_received */
968 NULL, /* quantity_accepted */
969 NULL, /* quantity_rejected */
970 NULL, /* quantity_billed */
971 NULL, /* quantity_cancelled */
972 X_unit_of_measure,
973 X_po_release_id,
974 X_ship_to_location_id,
975 X_freight_carrier,
976 X_need_by_date,
977 X_promised_date,
978 NULL, /* last_accept_date */
979 X_unit_price, /* price_override */
980 NULL, /* encumbered_flag*/
981 NULL, /* encumbered_date */
982 X_fob,
983 X_freight_terms,
984 X_taxable_flag,
985 X_tax_name,
986 NULL, /* estimated_tax_amount */
987 X_from_header_id,
988 X_from_line_id,
989 X_from_line_location_id,
990 X_effective_date,
991 X_expiration_date,
992 X_lead_time,
993 X_lead_time_unit,
994 X_price_discount,
995 X_terms_id,
996 X_approved_flag,
997 X_approved_date,
998 NULL, /* closed_flag */
999 NULL, /* X_cancel_flag */
1000 NULL, /*cancelled_by */
1001 NULL, /*cancel_date*/
1002 NULL, /*cancel_reason*/
1003 NULL, /* firm_status_lookup_code */
1004 NULL, /* firm_date */
1005 X_shipment_attribute_category,
1006 X_shipment_attribute1,
1007 X_shipment_attribute2,
1008 X_shipment_attribute3,
1009 X_shipment_attribute4,
1010 X_shipment_attribute5,
1011 X_shipment_attribute6,
1012 X_shipment_attribute7,
1013 X_shipment_attribute8,
1014 X_shipment_attribute9,
1015 X_shipment_attribute10,
1016 NULL, /* unit_of_measure_class */
1017 X_shipment_attribute11,
1018 X_shipment_attribute12,
1019 X_shipment_attribute13,
1020 X_shipment_attribute14,
1021 X_shipment_attribute15,
1022 X_inspection_required_flag,
1023 X_receipt_required_flag,
1024 X_qty_rcv_tolerance,
1025 X_qty_rcv_exception_code,
1026 X_enforce_ship_to_loc_code,
1027 X_allow_sub_receipts_flag,
1028 X_days_early_receipt_allowed,
1029 X_days_late_receipt_allowed,
1030 X_receipt_days_exception_code,
1031 X_invoice_close_tolerance,
1032 X_receive_close_tolerance,
1033 X_ship_to_organization_id,
1034 X_shipment_num,
1035 X_source_shipment_id ,
1036 X_shipment_type,
1037 X_closed_code,
1038 X_request_id,
1039 X_program_application_id,
1040 X_program_id,
1041 X_program_update_date,
1042 NULL, -- <R12 SLA replaced by null>
1043 X_government_context,
1044 X_receiving_routing_id,
1045 NULL, /* accrue_on_receipt_flag */
1046 X_closed_reason,
1047 X_closed_date,
1048 X_closed_by,
1049 X_organization_id,
1050 X_def_inv_org_id,
1051 X_header_processable_flag,
1052 X_document_type,
1053 X_item_id,
1054 X_item_revision,
1055 x_category_id, --< Shared Proc FPJ >
1056 l_transaction_flow_header_id, --< Shared Proc FPJ >
1057 p_order_type_lookup_code, --<SERVICES FPJ>
1058 p_purchase_basis, --<SERVICES FPJ>
1059 p_job_id); --<SERVICES FPJ>
1060 END IF;
1061
1062 IF (X_header_processable_flag = 'Y') AND
1063 (X_create_po_line_loc_flag = 'Y') AND
1064 (X_price_tolerance_flag = 'N') then
1065
1066 /* if no error found after line location validation,
1067 then insert a new rec in line_locaiton table */
1068
1069 X_progress := '070';
1070 IF (g_po_pdoi_write_to_file = 'Y') THEN
1071 PO_DEBUG.put_line('Start insert new record into line location');
1072 END IF;
1073
1074 --
1075 -- UPDATE THE PRICE BREAK INFORMATION HERE - note that any old price breaks,if any,
1076 -- would have been deleted by now.
1077 -- ASSUMPTION - We always update a line before updating the price breaks for that line.
1078 -- this is a safe assumption as the price break creation will fail if the line
1079 -- for which we are creating the price break does not exist in po_lines table.
1080 -- Coordination check will fail.
1081 --
1082
1083 /* <TIMEPHASED FPI START> */
1084 --Bug#4040677 Start
1085 --This SQL neglects QUOTATIONS. Fixing to check shipment_type against argument provided
1086 -- and not to look for type_lookup_code. Hence, removed the condition for type_lookup_code
1087
1088 BEGIN
1089 SELECT NVL(MAX(pll.shipment_num),0) + 1
1090 INTO X_shipment_num
1091 FROM po_line_locations pll,
1092 po_headers_all poh
1093 WHERE pll.po_header_id = X_po_header_id
1094 AND pll.po_line_id = X_po_line_id
1095 AND poh.po_header_id = pll.po_header_id
1096 AND pll.shipment_type = x_shipment_type;
1097 EXCEPTION
1098 when others then
1099 null;
1100 END;
1101 --Bug#4040677 End
1102 /* <TIMEPHASED FPI END> */
1103
1104 /* Bug 2722795. Insert retroactive_Date in po_lines with the timestamp
1105 * if a price break row is inserted.
1106 */
1107
1108 IF ((X_document_type = 'BLANKET') and
1109 (nvl(l_price_break_lookup_code,'NON CUMULATIVE') =
1110 'NON CUMULATIVE')) THEN
1111 po_lines_sv2.retroactive_change(X_po_Line_id);
1112 END IF;
1113
1114 po_line_locations_sv6.insert_po_line_locations(
1115 X_line_location_id,
1116 X_last_update_date,
1117 X_last_updated_by,
1118 X_current_po_header_id,
1119 X_po_line_id,
1120 X_last_update_login,
1121 X_creation_date,
1122 X_created_by,
1123 X_quantity,
1124 NULL, /* quantity_received */
1125 NULL, /* quantity_accepted */
1126 NULL, /* quantity_rejected */
1127 NULL, /* quantity_billed */
1128 NULL, /* quantity_cancelled */
1129 X_unit_of_measure,
1130 X_po_release_id,
1131 X_ship_to_location_id,
1132 X_freight_carrier,
1133 X_need_by_date,
1134 X_promised_date,
1135 NULL, /* last_accept_date */
1136 X_unit_price, /* price_override */
1137 NULL, /* X_encumbered_flag*/
1138 NULL, /*encumbered_date*/
1139 X_fob,
1140 X_freight_terms,
1141 X_taxable_flag,
1142 to_number(null), -- tax_id
1143 X_from_header_id,
1144 X_from_line_id,
1145 X_from_line_location_id,
1146 X_effective_date,
1147 X_expiration_date,
1148 X_lead_time,
1149 X_lead_time_unit,
1150 X_price_discount,
1151 X_terms_id,
1152 X_approved_flag,
1153 NULL, /* closed_flag */
1154 NULL, /* cancel_flag*/
1155 NULL, /*cancelled_by*/
1156 NULL, /*cancel_date*/
1157 NULL, /* cancel_reason */
1158 NULL, /* firm_status_lookup_code*/
1159 X_shipment_attribute_category,
1160 X_shipment_attribute1,
1161 X_shipment_attribute2,
1162 X_shipment_attribute3,
1163 X_shipment_attribute4,
1164 X_shipment_attribute5,
1165 X_shipment_attribute6,
1166 X_shipment_attribute7,
1167 X_shipment_attribute8,
1168 X_shipment_attribute9,
1169 X_shipment_attribute10,
1170 X_shipment_attribute11,
1171 X_shipment_attribute12,
1172 X_shipment_attribute13,
1173 X_shipment_attribute14,
1174 X_shipment_attribute15,
1175 X_inspection_required_flag,
1176 X_receipt_required_flag,
1177 X_qty_rcv_tolerance,
1178 X_qty_rcv_exception_code,
1179 X_enforce_ship_to_loc_code,
1180 X_allow_sub_receipts_flag,
1181 X_days_early_receipt_allowed,
1182 X_days_late_receipt_allowed,
1183 X_receipt_days_exception_code,
1184 X_invoice_close_tolerance,
1185 X_receive_close_tolerance,
1186 X_ship_to_organization_id,
1187 X_shipment_num,
1188 X_source_shipment_id,
1189 X_shipment_type,
1190 X_closed_code,
1191 X_request_id,
1192 X_program_application_id,
1193 X_program_id,
1194 X_program_update_date,
1195 NULL, -- <R12 SLA replaced by null>
1196 X_government_context,
1197 X_receiving_routing_id,
1198 X_accrue_on_receipt_flag,
1199 X_closed_reason,
1200 X_closed_date,
1201 X_closed_by,
1202 X_organization_id,
1203 l_transaction_flow_header_id, --< Shared Proc FPJ >
1204 --<SERVICES FPJ START>
1205 p_amount,
1206 p_order_type_lookup_code,
1207 p_purchase_basis,
1208 --<SERVICES FPJ END>
1209 NULL, --< Shared Proc FPJ > explicit NULL was missing
1210 --togeorge 09/28/2000
1211 --added note to receiver
1212 X_note_to_receiver
1213 );
1214
1215 END IF;
1216
1217 --<SERVICES FPJ START>
1218 --Add new price differential records to an existing price break
1219
1220 IF ((X_create_po_line_loc_flag = 'Y')
1221 AND (X_header_processable_flag = 'Y')) THEN
1222
1223 X_progress := '080';
1224 IF (g_po_pdoi_write_to_file = 'Y') THEN
1225 PO_DEBUG.put_line('Start insert price differentials '||
1226 'for the price break');
1227 END IF;
1228
1229 If (p_order_type_lookup_code = 'RATE') then
1230
1231 PO_PRICE_DIFFERENTIALS_PVT.validate_price_differentials(
1232 p_interface_header_id => X_interface_header_id,
1233 p_interface_line_id => X_interface_line_id,
1234 p_entity_type => 'PRICE BREAK',
1235 p_entity_id => X_line_location_id,
1236 p_header_processable_flag => X_header_processable_flag);
1237
1238 --create price differential records
1239 PO_PRICE_DIFFERENTIALS_PVT.create_from_interface(
1240 p_interface_line_id => X_interface_line_id,
1241 p_entity_id => X_line_location_id);
1242
1243 END IF; --IF (p_order_type_lookup_code = 'RATE')
1244 END IF; --IF ((X_create_po_line_loc_flag = 'Y')...
1245 --<SERVICES FPJ END>
1246
1247
1248 select process_code into l_process_code
1249 from po_headers_interface
1250 where interface_header_id = X_interface_header_id;
1251
1252 if NVL(l_process_code, 'UPDATE') = 'NOTIFIED' then
1253
1254 -- This flag should not be set to 'Y' when processing notified rows.
1255 X_price_tolerance_flag := 'N';
1256 end if;
1257
1258 EXCEPTION
1259 WHEN others THEN
1260 po_message_s.sql_error('update_po_line', X_progress, sqlcode);
1261 raise;
1262
1263 END update_po_line;
1264
1265
1266 --
1267 -- PROCEDURE NAME: delete_po_line() - ** NOT USED ANY MORE **
1268 --
1269
1270 PROCEDURE delete_po_line(
1271 X_interface_header_id NUMBER,
1272 X_interface_line_id NUMBER,
1273 X_po_line_id NUMBER,
1274 X_line_location_id NUMBER,
1275 X_shipment_type VARCHAR2,
1276 X_document_num VARCHAR2,
1277 X_po_header_id NUMBER,
1278 X_item NUMBER,
1279 X_item_id NUMBER,
1280 X_item_revision VARCHAR2,
1281 X_category VARCHAR2,
1282 X_category_id NUMBER,
1283 X_item_description VARCHAR2,
1284 X_vendor_product_num VARCHAR2)
1285 IS
1286 x_progress VARCHAR2(100) := NULL;
1287 BEGIN
1288
1289 -- We can use a soft_cancel_line flag (po_lines table) that when enabled, the line
1290 -- will not show up in the blanket/quotation or appear when creating releases.
1291 -- Would also need to modify sourcing, auto-create, ASL etc. and add this column to
1292 -- the PO_LINES_ALL table and PO_LINES view.
1293
1294 -- Can use expiration date as well. That is the item would not show up in any of the
1295 -- above after that expiration date.
1296
1297 -- Cannot cancel item if open releases.
1298 /*
1299 update po_lines
1300 set deleted_item_flag = 'Y'
1301 -- set expiration_date = (select sysdate from sys.dual) or X_expiration_date
1302 last_update_date = sysdate,
1303 last_updated_by = fnd_global.user_id,
1304 last_update_login = fnd_global.login_id,
1305 request_id = fnd_global.conc_request_id,
1306 program_application_id = fnd_global.prog_appl_id,
1307 program_id = fnd_global.conc_program_id,
1308 program_update_date = sysdate
1309 where po_header_id = X_po_header_id
1310 and po_line_id = X_po_line_id;
1311 */
1312
1313 NULL;
1314
1315 EXCEPTION
1316 WHEN others THEN
1317 po_message_s.sql_error('delete_po_line', X_progress, sqlcode);
1318 raise;
1319
1320 END delete_po_line;
1321
1322 --
1323 -- PROCEDURE NAME : item_exists
1324 --
1325
1326 PROCEDURE item_exists ( X_ItemType IN VARCHAR2,
1327 X_ItemKey IN VARCHAR2,
1328 X_Item_exist OUT NOCOPY VARCHAR2,
1329 X_Item_end_date OUT NOCOPY DATE)
1330 IS
1331 l_progress VARCHAR2(300) := NULL;
1332
1333 BEGIN
1334
1335 l_progress := 'PO_LINES_SV4_832_UPDATE.Item_Exists: 01';
1336 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_itemtype,X_itemkey,l_progress);
1337
1338 -- initialize the return variables
1339 X_item_exist := NULL;
1340 X_item_end_date := NULL;
1341
1342 SELECT 'Y', WI.end_date
1343 INTO X_item_exist, X_item_end_date
1344 FROM WF_ITEMS_V WI
1345 WHERE WI.ITEM_TYPE = X_ItemType
1346 AND WI.ITEM_KEY = X_ItemKey;
1347
1348
1349 l_progress := 'PO_LINES_SV4_832_UPDATE.Item_Exists: 900 ';
1350 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_itemtype,X_itemkey,l_progress);
1351
1352 EXCEPTION
1353 WHEN NO_DATA_FOUND THEN
1354
1355 -- item key does not exist
1356 X_item_exist := 'N';
1357 X_item_end_date := NULL;
1358
1359 WHEN OTHERS THEN
1360
1361 wf_core.context ('PO_LINES_SV4_832_UPDATE','Item_exists','SQL error ' || sqlcode);
1362 l_progress := 'PO_LINES_SV4_832_UPDATE.Item_Exists: 990 - ' ||
1363 'EXCEPTION - sql error: ' || sqlcode;
1364 IF (g_po_wf_debug = 'Y') THEN
1365 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_itemtype,X_itemkey,l_progress);
1366 END IF;
1367
1368 RAISE;
1369
1370 END item_exists;
1371
1372
1373 --
1374 -- PROCEDURE NAME: Start_Pricat_WF()
1375 --
1376
1377 PROCEDURE Start_Pricat_WF ( X_ItemType IN VARCHAR2,
1378 X_ItemKey IN VARCHAR2,
1379 X_interface_header_id IN NUMBER,
1380 X_po_header_id IN NUMBER,
1381 X_batch_id IN NUMBER,
1382 X_document_type_code IN VARCHAR2,
1383 X_document_sub_type IN VARCHAR2,
1384 X_commit_interval IN NUMBER,
1385 X_any_item_udpated IN VARCHAR2,
1386 X_buyer_id IN NUMBER )
1387 IS
1388 l_orig_system VARCHAR2(5);
1389 l_agent_username VARCHAR2(240);
1390 l_agent_display_name VARCHAR2(240);
1391 l_progress VARCHAR2(240);
1392 x_agent_id NUMBER;
1393 X_supplier_id NUMBER;
1394 --
1395 -- For bug 2834902. Current field size change from 80 -> 240
1396 x_vendor_name PO_VENDORS.vendor_name%TYPE;
1397 --
1398 l_open_form VARCHAR2(240);
1399 l_number_of_items NUMBER;
1400 l_document_num VARCHAR2(20);
1401 Begin
1402
1403 IF (g_po_pdoi_write_to_file = 'Y') THEN
1404 PO_DEBUG.put_line ('Start Notifications Workflow');
1405 END IF;
1406
1407 begin
1408 select count(*) into l_number_of_items
1409 from po_lines_interface
1410 where interface_header_id = X_interface_header_id
1411 and process_code = 'NOTIFIED'
1412 and nvl(price_break_flag,'N') = 'N';
1413
1414 IF (g_po_pdoi_write_to_file = 'Y') THEN
1415 PO_DEBUG.put_line('number of line items failing tolerance :' || to_char(l_number_of_items));
1416 END IF;
1417 exception
1418 when others then
1419 l_number_of_items := NULL;
1420 end;
1421
1422
1423 begin
1424 select segment1 into l_document_num
1425 from po_headers
1426 where po_header_id = X_po_header_id;
1427
1428 exception
1429 when others then
1430 NULL;
1431 end;
1432
1433 wf_engine.createProcess ( ItemType => X_ItemType,
1434 ItemKey => X_ItemKey,
1435 Process => 'PROCESS_LINE_ITEMS');
1436
1437 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1438 itemkey => X_itemkey,
1439 aname => 'INTERFACE_HEADER_ID',
1440 avalue => X_interface_header_id );
1441
1442 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1443 itemkey => X_itemkey,
1444 aname => 'DOCUMENT_ID',
1445 avalue => X_po_header_id );
1446
1447 wf_engine.SetItemAttrText ( itemtype => X_ItemType,
1448 itemkey => X_itemkey,
1449 aname => 'DOCUMENT_NUM',
1450 avalue => l_document_num);
1451
1452 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1453 itemkey => X_itemkey,
1454 aname => 'BATCH_ID',
1455 avalue => X_batch_id );
1456
1457 wf_engine.SetItemAttrText ( itemtype => X_ItemType,
1458 itemkey => X_itemkey,
1459 aname => 'DOCUMENT_TYPE_CODE',
1460 avalue => X_document_type_code);
1461
1462 wf_engine.SetItemAttrText ( itemtype => X_ItemType,
1463 itemkey => X_itemkey,
1464 aname => 'DOCUMENT_SUBTYPE',
1465 avalue => X_document_sub_type);
1466
1467 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1468 itemkey => X_itemkey,
1469 aname => 'COMMIT_INTERVAL',
1470 avalue => X_commit_interval );
1471
1472 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1473 itemkey => X_itemkey,
1474 aname => 'NUMBER_OF_ITEMS',
1475 avalue => l_number_of_items );
1476
1477 wf_engine.SetItemAttrText ( itemtype => X_ItemType,
1478 itemkey => X_itemkey,
1479 aname => 'ANY_LINE_ITEM_UPDATED',
1480 avalue => X_any_item_udpated);
1481
1482 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1483 itemkey => X_itemkey,
1484 aname => 'BUYER_ID',
1485 avalue => X_buyer_id );
1486
1487 select agent_id, vendor_id into x_agent_id, X_supplier_id
1488 from po_headers
1489 where po_header_id = X_po_header_id;
1490
1491 l_orig_system:= 'PER';
1492
1493 WF_DIRECTORY.GetUserName( l_orig_system,
1494 x_agent_id,
1495 l_agent_username,
1496 l_agent_display_name );
1497
1498 wf_engine.SetItemAttrText ( itemtype => X_itemtype,
1499 itemkey => X_itemkey,
1500 aname => 'BUYER_USER_NAME',
1501 avalue => l_agent_username );
1502
1503 wf_engine.SetItemAttrText ( itemtype => X_itemtype,
1504 itemkey => X_itemkey,
1505 aname => 'BUYER_DISPLAY_NAME',
1506 avalue => l_agent_display_name );
1507
1508 l_open_form := 'PO_POXPCATN:INTERFACE_HEADER_ID="' || '&' || 'INTERFACE_HEADER_ID"' ||
1509 ' ACCESS_LEVEL_CODE="' || '&' || 'ACCESS_LEVEL_CODE"';
1510
1511 wf_engine.SetItemAttrText ( itemtype => X_itemType,
1512 itemkey => X_itemkey,
1513 aname => 'OPEN_FORM_COMMAND' ,
1514 avalue => l_open_form );
1515
1516 -- Need to set Supplier attributes as well
1517 -- using X_supplier_id
1518
1519 begin
1520 select vendor_name into x_vendor_name
1521 from po_vendors
1522 where vendor_id = x_supplier_id ;
1523 exception
1524 when others then
1525 null;
1526 end;
1527
1528 wf_engine.SetItemAttrNumber ( itemtype => X_ItemType,
1529 itemkey => X_itemkey,
1530 aname => 'SUPPLIER_ID',
1531 avalue => x_supplier_id );
1532
1533 wf_engine.SetItemAttrText ( itemtype => X_ItemType,
1534 itemkey => X_itemkey,
1535 aname => 'SUPPLIER',
1536 avalue => x_vendor_name );
1537
1538 -- setting workflow owner
1539
1540 wf_engine.SetItemOwner ( itemtype => X_ItemType,
1541 itemkey => X_itemkey,
1542 owner => l_agent_username );
1543
1544 IF (g_po_pdoi_write_to_file = 'Y') THEN
1545 PO_DEBUG.put_line ('Done setting workflow attributes');
1546 END IF;
1547
1548 -- Start Workflow
1549
1550 wf_engine.startprocess ( itemtype => X_ItemType,
1551 itemkey => X_itemkey );
1552
1553 IF (g_po_pdoi_write_to_file = 'Y') THEN
1554 PO_DEBUG.put_line ('Workflow started');
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559
1560 wf_core.context ('PO_LINES_SV4_832_UPDATE','Start_Pricat_WF','SQL error ' || sqlcode);
1561 l_progress := 'PO_LINES_SV4_832_UPDATE.Start_Pricat_WF: 990 - ' || 'EXCEPTION - sql error: ' || sqlcode;
1562 IF (g_po_wf_debug = 'Y') THEN
1563 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_itemtype,X_itemkey,l_progress);
1564 END IF;
1565
1566 RAISE;
1567
1568 end Start_Pricat_WF;
1569
1570
1571 /*===========================================================================*/
1572 /*============================ BODY (PRIVATE) ===============================*/
1573 /*===========================================================================*/
1574
1575 FUNCTION price_tolerance_check ( p_interface_header_id NUMBER,
1576 p_interface_line_id NUMBER,
1577 p_item_id NUMBER,
1578 p_category_id NUMBER,
1579 p_vendor_id NUMBER,
1580 p_vendor_site_id NUMBER,
1581 p_document_id NUMBER,
1582 p_po_line_id NUMBER,
1583 p_unit_price NUMBER,
1584 p_def_master_org_id NUMBER) -- X_org_id
1585 RETURN Boolean
1586 IS
1587 l_original_price NUMBER;
1588 l_price_update_tolerance NUMBER;
1589 l_progress VARCHAR2(240);
1590 l_asl_id NUMBER;
1591 l_vendor_product_num VARCHAR2(25);
1592 l_purchasing_uom VARCHAR2(25);
1593 l_process_code VARCHAR2(25);
1594 l_acceptance_flag VARCHAR2(1);
1595
1596 cursor C1 is
1597 SELECT price_update_tolerance
1598 FROM po_asl_attributes
1599 WHERE (item_id = p_item_id or
1600 category_id = p_category_id or
1601 category_id in (SELECT MIC.category_id
1602 FROM MTL_ITEM_CATEGORIES MIC
1603 WHERE MIC.inventory_item_id = p_item_id
1604 AND MIC.organization_id = p_def_master_org_id))
1605 AND
1606 vendor_id = p_vendor_id
1607 AND
1608 ( NVL(vendor_site_id, 1) = NVL(p_vendor_site_id, 1)
1609 OR
1610 vendor_site_id is NULL or p_vendor_site_id is NULL )
1611 AND
1612 using_organization_id IN (-1, p_def_master_org_id)
1613 ORDER BY item_id ASC, using_organization_id DESC;
1614
1615 x_asl_rows C1%ROWTYPE;
1616
1617 begin
1618 IF (g_po_pdoi_write_to_file = 'Y') THEN
1619 PO_DEBUG.put_line ('$ Checking price tolerance');
1620 END IF;
1621
1622 select process_code into l_process_code
1623 from po_headers_interface
1624 where interface_header_id = p_interface_header_id;
1625
1626 select price_chg_accept_flag into l_acceptance_flag
1627 from po_lines_interface
1628 where interface_header_id = p_interface_header_id
1629 and interface_line_id = p_interface_line_id;
1630
1631 IF (g_po_pdoi_write_to_file = 'Y') THEN
1632 PO_DEBUG.put_line ('p_vendor_id:' || to_char(p_vendor_id));
1633 PO_DEBUG.put_line ('p_category_id:' || to_char(p_category_id));
1634 PO_DEBUG.put_line ('p_def_master_org_id' || to_char(p_def_master_org_id));
1635 PO_DEBUG.put_line ('l_process_code:' || l_process_code);
1636 PO_DEBUG.put_line ('l_acceptance_flag:' || l_acceptance_flag);
1637 END IF;
1638
1639 if NVL(l_process_code, 'UPDATE') = 'NOTIFIED' and NVL(l_acceptance_flag, 'N') = 'Y' then
1640 return TRUE;
1641 end if;
1642
1643 --
1644 -- Try to get the price tolerance percentage in following sequence
1645 -- from the
1646 -- 1. item supplier (ASL)
1647 -- 2. commodity (category) supplier (ASL)
1648 -- 3. original document (Blanket Only)
1649 -- 4. system level profile
1650
1651 -- item supplier or commodity supplier
1652 -- get it from item-supplier, if not found then get it from category-supplier.
1653
1654 begin
1655
1656 Open C1;
1657 fetch C1 into x_asl_rows;
1658 l_price_update_tolerance := x_asl_rows.price_update_tolerance;
1659 Close C1;
1660
1661 IF (g_po_pdoi_write_to_file = 'Y') THEN
1662 PO_DEBUG.put_line ('ASL tolerance' || to_char(l_price_update_tolerance));
1663 END IF;
1664
1665 exception
1666 when others then
1667 null;
1668 IF (g_po_pdoi_write_to_file = 'Y') THEN
1669 PO_DEBUG.put_line ('ASL tolerance - exception');
1670 END IF;
1671 end;
1672
1673 if l_price_update_tolerance is null then
1674
1675 begin
1676 -- original document (blanket)
1677
1678 select price_update_tolerance into l_price_update_tolerance
1679 from po_headers
1680 where po_header_id = p_document_id
1681 and type_lookup_code = 'BLANKET';
1682
1683 exception
1684 when others then
1685 null;
1686 end;
1687 end if;
1688
1689 if l_price_update_tolerance is null then
1690
1691 -- system profile
1692
1693 fnd_profile.get('PO_PRICE_UPDATE_TOLERANCE',l_price_update_tolerance);
1694
1695 end if;
1696
1697 if l_price_update_tolerance is null then
1698
1699 -- no price tolerance specified.
1700
1701 return TRUE;
1702 else
1703
1704 IF (g_po_pdoi_write_to_file = 'Y') THEN
1705 PO_DEBUG.put_line('Price_update_tolerance_percent specified:' || to_char(l_price_update_tolerance));
1706 END IF;
1707
1708 -- need to save the tolerance value at the line level as we would have to display it in the PRICAT form
1709
1710 update po_lines_interface
1711 set price_update_tolerance = l_price_update_tolerance
1712 where interface_header_id = p_interface_header_id
1713 and interface_line_id = p_interface_line_id;
1714
1715 select unit_price into l_original_price
1716 from po_lines
1717 where po_header_id = p_document_id
1718 and po_line_id = p_po_line_id;
1719
1720 If (((1 + (l_price_update_tolerance/100)) * l_original_price) >= p_unit_price) then
1721 IF (g_po_pdoi_write_to_file = 'Y') THEN
1722 PO_DEBUG.put_line('Price_update_tolerance_percent exceeded');
1723 END IF;
1724 return TRUE;
1725 else
1726 IF (g_po_pdoi_write_to_file = 'Y') THEN
1727 PO_DEBUG.put_line('Price_update_tolerance_percent NOT exceeded');
1728 END IF;
1729 return FALSE;
1730 end if;
1731 end if;
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 po_message_s.sql_error('price_tolerance_check', l_progress, sqlcode);
1736 raise;
1737
1738 end price_tolerance_check;
1739
1740
1741 PROCEDURE delete_price_breaks (x_po_header_id NUMBER, x_po_line_id NUMBER)
1742 IS
1743
1744 Cursor C_line_locations Is
1745 SELECT line_location_id
1746 FROM po_line_locations
1747 WHERE po_line_id = x_po_line_id
1748 AND po_header_id = x_po_header_id;
1749
1750 x_line_location_id NUMBER;
1751 rows_processed_counter NUMBER := 0;
1752 new_revision_num NUMBER;
1753
1754 BEGIN
1755
1756 IF (g_po_pdoi_write_to_file = 'Y') THEN
1757 PO_DEBUG.put_line ('Deleting price breaks on original catalog/blanket');
1758 PO_DEBUG.put_line ('x_po_header_id:' || to_char(x_po_header_id));
1759 PO_DEBUG.put_line ('x_po_line_id:' || to_char(x_po_line_id));
1760 END IF;
1761
1762 -- delete all price breaks for this line.
1763 -- Bug: 1588445 While deleting price breaks take into consideration shipment
1764 -- type QUOTATION also. Otherwise for shipment type quotation it does not
1765 -- delete the old price breaks.
1766
1767 DELETE FROM PO_LINE_LOCATIONS
1768 WHERE PO_LINE_ID = X_po_line_id
1769 AND PO_HEADER_ID = X_po_header_id
1770 AND SHIPMENT_TYPE in ('PRICE BREAK','QUOTATION');
1771
1772 END;
1773
1774
1775 /*=============================================================================
1776
1777 PROCEDURE: update_price_discount <2703076>
1778
1779 DESCRIPTION: Updates the price_discount field for all Price Breaks
1780 of the given po_line_id with respect to the unit_price.
1781
1782 =============================================================================*/
1783 PROCEDURE update_price_discount
1784 (
1785 p_po_line_id IN PO_LINE_LOCATIONS.po_line_id%TYPE,
1786 p_unit_price IN PO_LINES.unit_price%TYPE
1787 )
1788 IS BEGIN
1789
1790 UPDATE PO_LINE_LOCATIONS
1791 SET price_discount = round(((p_unit_price - price_override)/p_unit_price * 100), 2)
1792 WHERE po_line_id = p_po_line_id;
1793
1794 EXCEPTION
1795
1796 WHEN OTHERS THEN
1797 PO_MESSAGE_S.sql_error('update_price_discount','000',sqlcode);
1798 RAISE;
1799
1800 END update_price_discount;
1801
1802
1803 END PO_LINES_SV4_832_UPDATE;