1 PACKAGE BODY PO_CHORD_WF2 AS
2 /* $Header: POXWCO2B.pls 120.4 2006/03/30 15:44:43 dreddy noship $ */
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
7 PROCEDURE chord_lines(itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 result OUT NOCOPY VARCHAR2)
12 IS
13 x_lines_control t_lines_control_type;
14 x_lines_parameters t_lines_parameters_type;
15 BEGIN
16
17 IF (g_po_wf_debug = 'Y') THEN
18 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
19 '*** In Procedure: chord_lines ***' );
20 END IF;
21
22 IF funcmode <> 'RUN' THEN
23 result := 'COMPLETE';
24 return;
25 END IF;
26
27 get_wf_lines_parameters(itemtype, itemkey, x_lines_parameters);
28
29 check_lines_change(itemtype, itemkey, x_lines_parameters, x_lines_control);
30
31 set_wf_lines_control(itemtype, itemkey, x_lines_control);
32
33 IF (g_po_wf_debug = 'Y') THEN
34 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
35 '*** Finish: chord_lines ***' );
36 END IF;
37
38 result := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
39 return;
40
41 EXCEPTION
42
43 WHEN OTHERS THEN
44 wf_core.context('POAPPRV', 'po_chord_wf2.chord_lines', 'others');
45 RAISE;
46
47 END;
48
49 PROCEDURE check_lines_change(itemtype IN VARCHAR2,
50 itemkey IN VARCHAR2,
51 x_lines_parameters IN t_lines_parameters_type,
52 x_lines_control IN OUT NOCOPY t_lines_control_type)
53 IS
54 x_po_header_id NUMBER;
55 l_currency_code VARCHAR2(15);
56 l_min_acct_unit VARCHAR2(15);
57 l_precision VARCHAR2(15);
58
59 BEGIN
60 IF (g_po_wf_debug = 'Y') THEN
61 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
62 '*** In Procedure: check_lines_change ***' );
63 END IF;
64
65 /* To use change order,
66 * System should have Archive on Approval set
67 */
68
69 /* initialize */
70 x_lines_control.line_num :='N';
71 x_lines_control.item_id :='N';
72 x_lines_control.item_revision :='N';
73 x_lines_control.category_id :='N';
74 x_lines_control.item_description :='N';
75 x_lines_control.unit_meas_lookup_code :='N';
76 x_lines_control.un_number_id :='N';
77 x_lines_control.hazard_class_id :='N';
78 x_lines_control.note_to_vendor :='N';
79 x_lines_control.from_header_id :='N';
80 x_lines_control.from_line_id :='N';
81 x_lines_control.closed_code :='N';
82 x_lines_control.vendor_product_num :='N';
83 x_lines_control.contract_num :='N';
84 x_lines_control.price_type_lookup_code :='N';
85 x_lines_control.cancel_flag :='N';
86 x_lines_control.end_date :='N';
87
88
89 -- <Complex Work R12 Start>
90 x_lines_control.retainage_rate := 'N';
91 x_lines_control.max_retainage_amount := 'N';
92 x_lines_control.progress_payment_rate := 'N';
93 x_lines_control.recoupment_rate := 'N';
94 x_lines_control.advance_amount := 'N';
95 -- <Complex Work R12 End>
96
97 x_lines_control.quantity_change :=0;
98 x_lines_control.unit_price_change :=0;
99 x_lines_control.quantity_committed_change :=0;
100 x_lines_control.committed_amount_change :=0;
101 x_lines_control.not_to_exceed_price_change:=0;
102 x_lines_control.amount_change :=0; --<R12 Requester Driven Procurement>
103 x_lines_control.start_date_change :=0; --<R12 Requester Driven Procurement>
104 x_lines_control.end_date_change :=0; --<R12 Requester Driven Procurement>
105
106 x_po_header_id := x_lines_parameters.po_header_id;
107
108 BEGIN
109 -- SQL What: Select 'Y' if line number is changed
110 -- SQL Why: Need the value for routing to reapproval
111 -- if there is a change
112 -- SQL Join: po_line_id, line_num
113 SELECT DISTINCT 'Y'
114 INTO x_lines_control.line_num
115 FROM PO_LINES POL,
116 PO_LINES_ARCHIVE_ALL POLA
117 WHERE POL.po_header_id = x_po_header_id
118 AND POL.po_line_id = POLA.po_line_id (+)
119 AND POLA.latest_external_flag (+) = 'Y'
120 AND (
121 (POLA.po_line_id is NULL)
122 OR (POL.line_num <> POLA.line_num)
123 OR (POL.line_num IS NULL AND POLA.line_num IS NOT NULL)
124 OR (POL.line_num IS NOT NULL AND POLA.line_num IS NULL)
125 );
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 x_lines_control.line_num :='N';
129 END;
130
131
132 BEGIN
133 -- SQL What: Select 'Y' if item id is changed
134 -- SQL Why: Need the value for routing to reapproval
135 -- if there is a change
136 -- SQL Join: po_line_id, item_id
137 SELECT DISTINCT 'Y'
138 INTO x_lines_control.item_id
139 FROM PO_LINES_ALL POL,
140 PO_LINES_ARCHIVE_ALL POLA
141 WHERE POL.po_header_id = x_po_header_id
142 AND POL.po_line_id = POLA.po_line_id (+)
143 AND POLA.latest_external_flag (+) = 'Y'
144 AND (
145 (POLA.po_line_id is NULL)
146 OR (POL.item_id <> POLA.item_id)
147 OR (POL.item_id IS NULL AND POLA.item_id IS NOT NULL)
148 OR (POL.item_id IS NOT NULL AND POLA.item_id IS NULL)
149 );
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 x_lines_control.item_id :='N';
153 END;
154
155 BEGIN
156 -- SQL What: Select 'Y' if item revision is changed
157 -- SQL Why: Need the value for routing to reapproval
158 -- if there is a change
159 -- SQL Join: po_line_id, item_revision
160 SELECT DISTINCT 'Y'
161 INTO x_lines_control.item_revision
162 FROM PO_LINES_ALL POL,
163 PO_LINES_ARCHIVE_ALL POLA
164 WHERE POL.po_header_id = x_po_header_id
165 AND POL.po_line_id = POLA.po_line_id (+)
166 AND POLA.latest_external_flag (+) = 'Y'
167 AND (
168 (POLA.po_line_id is NULL)
169 OR (POL.item_revision <> POLA.item_revision)
170 OR (POL.item_revision IS NULL AND POLA.item_revision IS NOT NULL)
171 OR (POL.item_revision IS NOT NULL AND POLA.item_revision IS NULL)
172 );
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 x_lines_control.item_revision :='N';
176 END;
177
178 BEGIN
179 -- SQL What: Select 'Y' if category is changed
180 -- SQL Why: Need the value for routing to reapproval
181 -- if there is a change
182 -- SQL Join: po_line_id, category_id
183 SELECT DISTINCT 'Y'
184 INTO x_lines_control.category_id
185 FROM PO_LINES_ALL POL,
186 PO_LINES_ARCHIVE_ALL POLA
187 WHERE POL.po_header_id = x_po_header_id
188 AND POL.po_line_id = POLA.po_line_id (+)
189 AND POLA.latest_external_flag (+) = 'Y'
190 AND (
191 (POLA.po_line_id is NULL)
192 OR (POL.category_id <> POLA.category_id)
193 OR (POL.category_id IS NULL AND POLA.category_id IS NOT NULL)
194 OR (POL.category_id IS NOT NULL AND POLA.category_id IS NULL)
195 );
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 x_lines_control.category_id :='N';
199 END;
200
201 BEGIN
202 -- SQL What: Select 'Y' if item description is changed
203 -- SQL Why: Need the value for routing to reapproval
204 -- if there is a change
205 -- SQL Join: po_line_id, item_description
206 SELECT DISTINCT 'Y'
207 INTO x_lines_control.item_description
208 FROM PO_LINES_ALL POL,
209 PO_LINES_ARCHIVE_ALL POLA
210 WHERE POL.po_header_id = x_po_header_id
211 AND POL.po_line_id = POLA.po_line_id (+)
212 AND POLA.latest_external_flag (+) = 'Y'
213 AND (
214 (POLA.po_line_id is NULL)
215 OR (POL.item_description <> POLA.item_description)
216 OR (POL.item_description IS NULL AND POLA.item_description IS NOT NULL)
217 OR (POL.item_description IS NOT NULL AND POLA.item_description IS NULL)
218 );
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 x_lines_control.item_description :='N';
222 END;
223
224 BEGIN
225 -- SQL What: Select 'Y' if UOM is changed
226 -- SQL Why: Need the value for routing to reapproval
227 -- if there is a change
228 -- SQL Join: po_line_id, unit_meas_lookup_code
229 SELECT DISTINCT 'Y'
230 INTO x_lines_control.unit_meas_lookup_code
231 FROM PO_LINES_ALL POL,
232 PO_LINES_ARCHIVE_ALL POLA
233 WHERE POL.po_header_id = x_po_header_id
234 AND POL.po_line_id = POLA.po_line_id (+)
235 AND POLA.latest_external_flag (+) = 'Y'
236 AND (
237 (POLA.po_line_id is NULL)
238 OR (POL.unit_meas_lookup_code <> POLA.unit_meas_lookup_code)
239 OR (POL.unit_meas_lookup_code IS NULL AND POLA.unit_meas_lookup_code IS NOT NULL)
240 OR (POL.unit_meas_lookup_code IS NOT NULL AND POLA.unit_meas_lookup_code IS NULL)
241 );
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 x_lines_control.unit_meas_lookup_code :='N';
245 END;
246
247 BEGIN
248 -- SQL What: Select 'Y' if UN Number is changed
249 -- SQL Why: Need the value for routing to reapproval
250 -- if there is a change
251 -- SQL Join: po_line_id, un_number_id
252 SELECT DISTINCT 'Y'
253 INTO x_lines_control.un_number_id
254 FROM PO_LINES_ALL POL,
255 PO_LINES_ARCHIVE_ALL POLA
256 WHERE POL.po_header_id = x_po_header_id
257 AND POL.po_line_id = POLA.po_line_id (+)
258 AND POLA.latest_external_flag (+) = 'Y'
259 AND (
260 (POLA.po_line_id is NULL)
261 OR (POL.un_number_id <> POLA.un_number_id)
262 OR (POL.un_number_id IS NULL AND POLA.un_number_id IS NOT NULL)
263 OR (POL.un_number_id IS NOT NULL AND POLA.un_number_id IS NULL)
264 );
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 x_lines_control.un_number_id :='N';
268 END;
269
270 BEGIN
271 -- SQL What: Select 'Y' if hazard class is changed
272 -- SQL Why: Need the value for routing to reapproval
273 -- if there is a change
274 -- SQL Join: po_line_id, hazard_class_id
275 SELECT DISTINCT 'Y'
276 INTO x_lines_control.hazard_class_id
277 FROM PO_LINES_ALL POL,
278 PO_LINES_ARCHIVE_ALL POLA
279 WHERE POL.po_header_id = x_po_header_id
280 AND POL.po_line_id = POLA.po_line_id (+)
281 AND POLA.latest_external_flag (+) = 'Y'
282 AND (
283 (POLA.po_line_id is NULL)
284 OR (POL.hazard_class_id <> POLA.hazard_class_id)
285 OR (POL.hazard_class_id IS NULL AND POLA.hazard_class_id IS NOT NULL)
286 OR (POL.hazard_class_id IS NOT NULL AND POLA.hazard_class_id IS NULL)
287 );
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 x_lines_control.hazard_class_id :='N';
291 END;
292
293 BEGIN
294 -- SQL What: Select 'Y' if note to vendor is changed
295 -- SQL Why: Need the value for routing to reapproval
296 -- if there is a change
297 -- SQL Join: po_line_id, note_to_vendor
298 SELECT DISTINCT 'Y'
299 INTO x_lines_control.note_to_vendor
300 FROM PO_LINES_ALL POL,
301 PO_LINES_ARCHIVE_ALL POLA
302 WHERE POL.po_header_id = x_po_header_id
303 AND POL.po_line_id = POLA.po_line_id (+)
304 AND POLA.latest_external_flag (+) = 'Y'
305 AND (
306 (POLA.po_line_id is NULL)
307 OR (POL.note_to_vendor <> POLA.note_to_vendor)
308 OR (POL.note_to_vendor IS NULL AND POLA.note_to_vendor IS NOT NULL)
309 OR (POL.note_to_vendor IS NOT NULL AND POLA.note_to_vendor IS NULL)
310 );
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN
313 x_lines_control.note_to_vendor :='N';
314 END;
315
316 BEGIN
317 -- SQL What: Select 'Y' if source document id is changed
318 -- SQL Why: Need the value for routing to reapproval
319 -- if there is a change
320 -- SQL Join: po_line_id, from_header_id
321 SELECT DISTINCT 'Y'
322 INTO x_lines_control.from_header_id
323 FROM PO_LINES_ALL POL,
324 PO_LINES_ARCHIVE_ALL POLA
325 WHERE POL.po_header_id = x_po_header_id
326 AND POL.po_line_id = POLA.po_line_id (+)
327 AND POLA.latest_external_flag (+) = 'Y'
328 AND (
329 (POLA.po_line_id is NULL)
330 OR (POL.from_header_id <> POLA.from_header_id)
331 OR (POL.from_header_id IS NULL AND POLA.from_header_id IS NOT NULL)
332 OR (POL.from_header_id IS NOT NULL AND POLA.from_header_id IS NULL)
333 );
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 x_lines_control.from_header_id :='N';
337 END;
338
339 BEGIN
340 -- SQL What: Select 'Y' if from line id is changed
341 -- SQL Why: Need the value for routing to reapproval
342 -- if there is a change
343 -- SQL Join: po_line_id, from_line_id
344 SELECT DISTINCT 'Y'
345 INTO x_lines_control.from_line_id
346 FROM PO_LINES_ALL POL,
347 PO_LINES_ARCHIVE_ALL POLA
348 WHERE POL.po_header_id = x_po_header_id
349 AND POL.po_line_id = POLA.po_line_id (+)
350 AND POLA.latest_external_flag (+) = 'Y'
351 AND (
352 (POLA.po_line_id is NULL)
353 OR (POL.from_line_id <> POLA.from_line_id)
354 OR (POL.from_line_id IS NULL AND POLA.from_line_id IS NOT NULL)
355 OR (POL.from_line_id IS NOT NULL AND POLA.from_line_id IS NULL)
356 );
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 x_lines_control.from_line_id :='N';
360 END;
361
362 BEGIN
366 -- SQL Join: po_line_id, closed_code
363 -- SQL What: Select 'Y' if closed code is changed
364 -- SQL Why: Need the value for routing to reapproval
365 -- if there is a change
367 SELECT DISTINCT 'Y'
368 INTO x_lines_control.closed_code
369 FROM PO_LINES_ALL POL,
370 PO_LINES_ARCHIVE_ALL POLA
371 WHERE POL.po_header_id = x_po_header_id
372 AND POL.po_line_id = POLA.po_line_id (+)
373 AND POLA.latest_external_flag (+) = 'Y'
374 AND (
375 (POLA.po_line_id is NULL)
376 OR (POL.closed_code <> POLA.closed_code)
377 OR (POL.closed_code IS NULL AND POLA.closed_code IS NOT NULL)
378 OR (POL.closed_code IS NOT NULL AND POLA.closed_code IS NULL)
379 );
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 x_lines_control.closed_code :='N';
383 END;
384
385 BEGIN
386 -- SQL What: Select 'Y' if vendor product number is changed
387 -- SQL Why: Need the value for routing to reapproval
388 -- if there is a change
389 -- SQL Join: po_line_id, vendor_product_num
390 SELECT DISTINCT 'Y'
391 INTO x_lines_control.vendor_product_num
392 FROM PO_LINES_ALL POL,
393 PO_LINES_ARCHIVE_ALL POLA
394 WHERE POL.po_header_id = x_po_header_id
395 AND POL.po_line_id = POLA.po_line_id (+)
396 AND POLA.latest_external_flag (+) = 'Y'
397 AND (
398 (POLA.po_line_id is NULL)
399 OR (POL.vendor_product_num <> POLA.vendor_product_num)
400 OR (POL.vendor_product_num IS NULL AND POLA.vendor_product_num IS NOT NULL)
401 OR (POL.vendor_product_num IS NOT NULL AND POLA.vendor_product_num IS NULL)
402 );
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 x_lines_control.vendor_product_num :='N';
406 END;
407
408 BEGIN
409 -- <GC FPJ>
410 -- SQL What: Select 'Y' if contract number is changed
411 -- SQL Why: Need the value for routing to reapproval
412 -- if there is a change
413 -- SQL Join: po_line_id, contract_id
414 SELECT DISTINCT 'Y'
415 INTO x_lines_control.contract_num
416 FROM PO_LINES_ALL POL,
417 PO_LINES_ARCHIVE_ALL POLA
418 WHERE POL.po_header_id = x_po_header_id
419 AND POL.po_line_id = POLA.po_line_id (+)
420 AND POLA.latest_external_flag (+) = 'Y'
421 AND (
422 (POLA.po_line_id is NULL)
423 OR (POL.contract_id <> POLA.contract_id)
424 OR (POL.contract_id IS NULL
425 AND POLA.contract_id IS NOT NULL)
426 OR (POL.contract_id IS NOT NULL
427 AND POLA.contract_id IS NULL)
428 );
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 x_lines_control.contract_num :='N';
432 END;
433
434
435 BEGIN
436 -- SQL What: Select 'Y' if Price lookup code is changed
437 -- SQL Why: Need the value for routing to reapproval
438 -- if there is a change
439 -- SQL Join: po_line_id, price_type_lookup_code
440 SELECT DISTINCT 'Y'
441 INTO x_lines_control.price_type_lookup_code
442 FROM PO_LINES_ALL POL,
443 PO_LINES_ARCHIVE_ALL POLA
444 WHERE POL.po_header_id = x_po_header_id
445 AND POL.po_line_id = POLA.po_line_id (+)
446 AND POLA.latest_external_flag (+) = 'Y'
447 AND (
448 (POLA.po_line_id is NULL)
449 OR (POL.price_type_lookup_code <> POLA.price_type_lookup_code)
450 OR (POL.price_type_lookup_code IS NULL AND POLA.price_type_lookup_code IS NOT NULL)
451 OR (POL.price_type_lookup_code IS NOT NULL AND POLA.price_type_lookup_code IS NULL)
452 );
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 x_lines_control.price_type_lookup_code :='N';
456 END;
457
458 BEGIN
459 -- SQL What: Select 'Y' if cancel flag is changed
460 -- SQL Why: Need the value for routing to reapproval
461 -- if there is a change
462 -- SQL Join: po_line_id, cancel_flag
463 SELECT DISTINCT 'Y'
464 INTO x_lines_control.cancel_flag
465 FROM PO_LINES_ALL POL,
466 PO_LINES_ARCHIVE_ALL POLA
467 WHERE POL.po_header_id = x_po_header_id
468 AND POL.po_line_id = POLA.po_line_id (+)
469 AND POLA.latest_external_flag (+) = 'Y'
470 AND (
471 (POLA.po_line_id is NULL)
472 OR (POL.cancel_flag <> POLA.cancel_flag)
473 OR (POL.cancel_flag IS NULL AND POLA.cancel_flag IS NOT NULL)
477 WHEN NO_DATA_FOUND THEN
474 OR (POL.cancel_flag IS NOT NULL AND POLA.cancel_flag IS NULL)
475 );
476 EXCEPTION
478 x_lines_control.cancel_flag :='N';
479 END;
480
481 -- <Complex Work R12 Start>
482
483 BEGIN
484
485 -- SQL What: Select 'Y' if line's retainage rate has changed
486 -- SQL Why: Need the value for routing to reapproval if there is a change
487 -- SQL Join: po_line_id
488
489 SELECT DISTINCT 'Y'
490 INTO x_lines_control.retainage_rate
491 FROM po_lines_all pol,
492 po_lines_archive_all pola
493 WHERE pol.po_header_id = x_po_header_id
494 AND pol.po_line_id = pola.po_line_id (+)
495 AND pola.latest_external_flag (+) = 'Y'
496 AND (
497 (pola.po_line_id IS NULL)
498 OR (pol.retainage_rate <> pola.retainage_rate)
499 OR (pol.retainage_rate IS NULL AND pola.retainage_rate IS NOT NULL)
500 OR (pol.retainage_rate IS NOT NULL AND pola.retainage_rate IS NULL)
501 );
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 x_lines_control.retainage_rate :='N';
505 END;
506
507 BEGIN
508
509 -- SQL What: Select 'Y' if line's max retainage amount has changed
510 -- SQL Why: Need the value for routing to reapproval if there is a change
511 -- SQL Join: po_line_id
512
513 SELECT DISTINCT 'Y'
514 INTO x_lines_control.max_retainage_amount
515 FROM po_lines_all pol,
516 po_lines_archive_all pola
517 WHERE pol.po_header_id = x_po_header_id
518 AND pol.po_line_id = pola.po_line_id (+)
519 AND pola.latest_external_flag (+) = 'Y'
520 AND (
521 (pola.po_line_id IS NULL)
522 OR (pol.max_retainage_amount <> pola.max_retainage_amount)
523 OR (pol.max_retainage_amount IS NULL AND pola.max_retainage_amount IS NOT NULL)
524 OR (pol.max_retainage_amount IS NOT NULL AND pola.max_retainage_amount IS NULL)
525 );
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 x_lines_control.max_retainage_amount :='N';
529 END;
530
531 BEGIN
532
533 -- SQL What: Select 'Y' if line's progress payment rate has changed
534 -- SQL Why: Need the value for routing to reapproval if there is a change
535 -- SQL Join: po_line_id
536
537 SELECT DISTINCT 'Y'
538 INTO x_lines_control.progress_payment_rate
539 FROM po_lines_all pol,
540 po_lines_archive_all pola
541 WHERE pol.po_header_id = x_po_header_id
542 AND pol.po_line_id = pola.po_line_id (+)
543 AND pola.latest_external_flag (+) = 'Y'
544 AND (
545 (pola.po_line_id IS NULL)
546 OR (pol.progress_payment_rate <> pola.progress_payment_rate)
547 OR (pol.progress_payment_rate IS NULL AND
548 pola.progress_payment_rate IS NOT NULL)
549 OR (pol.progress_payment_rate IS NOT NULL AND
550 pola.progress_payment_rate IS NULL)
551 );
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 x_lines_control.progress_payment_rate :='N';
555 END;
556
557 BEGIN
558
559 -- SQL What: Select 'Y' if line's recoupment rate has changed
560 -- SQL Why: Need the value for routing to reapproval if there is a change
561 -- SQL Join: po_line_id
562
563 SELECT DISTINCT 'Y'
564 INTO x_lines_control.recoupment_rate
565 FROM po_lines_all pol,
566 po_lines_archive_all pola
567 WHERE pol.po_header_id = x_po_header_id
568 AND pol.po_line_id = pola.po_line_id (+)
569 AND pola.latest_external_flag (+) = 'Y'
570 AND (
571 (pola.po_line_id IS NULL)
572 OR (pol.recoupment_rate <> pola.recoupment_rate)
573 OR (pol.recoupment_rate IS NULL AND pola.recoupment_rate IS NOT NULL)
574 OR (pol.recoupment_rate IS NOT NULL AND pola.recoupment_rate IS NULL)
575 );
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 x_lines_control.recoupment_rate :='N';
579 END;
580
581 BEGIN
582
583 -- SQL What: Select 'Y' if a line's advance amount has changed.
584 -- Since advance is stored at line location level, hit that table.
585 -- SQL Why: Need the value for routing to reapproval if there is a change
586 -- SQL Join: line_location_id
587
588 SELECT DISTINCT 'Y'
589 INTO x_lines_control.advance_amount
590 FROM po_line_locations_all poll,
591 po_line_locations_archive_all polla
592 WHERE poll.po_header_id = x_po_header_id
593 AND poll.payment_type = 'ADVANCE'
594 AND poll.line_location_id = polla.line_location_id (+)
595 AND polla.latest_external_flag (+) = 'Y'
596 AND (
597 (polla.line_location_id IS NULL)
598 OR (poll.amount <> polla.amount)
599 OR (poll.amount IS NULL AND polla.amount IS NOT NULL)
600 OR (poll.amount IS NOT NULL AND polla.amount IS NULL)
601 );
602 EXCEPTION
603 WHEN NO_DATA_FOUND THEN
604 x_lines_control.advance_amount :='N';
605 END;
606
610 BEGIN
607 -- <Complex Work R12 End>
608
609
611 -- SQL What: Retrieving the percentage change in
612 -- line quantity
613 -- SQL Why: Need the value in tolerance check (i.e reapproval
614 -- rule validations)
615 -- SQL Join: po_line_id
616 SELECT max(po_chord_wf0.percentage_change(
617 POLA.quantity, POL.quantity))
618 INTO x_lines_control.quantity_change
619 FROM PO_LINES_ALL POL,
620 PO_LINES_ARCHIVE_ALL POLA
621 WHERE POL.po_header_id = x_po_header_id
622 AND POL.po_line_id = POLA.po_line_id (+)
623 AND POLA.latest_external_flag (+) = 'Y';
624
625 EXCEPTION
626 WHEN NO_DATA_FOUND THEN
627 x_lines_control.quantity_change :=0;
628 END;
629
630 BEGIN
631 -- SQL What: Retrieving the percentage change in
632 -- unit price
633 -- SQL Why: Need the value in tolerance check (i.e reapproval
634 -- rule validations)
635 -- SQL Join: po_line_id
636 SELECT max(po_chord_wf0.percentage_change(
637 POLA.unit_price, POL.unit_price))
638 INTO x_lines_control.unit_price_change
639 FROM PO_LINES_ALL POL,
640 PO_LINES_ARCHIVE_ALL POLA
641 WHERE POL.po_header_id = x_po_header_id
642 AND POL.po_line_id = POLA.po_line_id (+)
643 AND POLA.latest_external_flag (+) = 'Y';
644
645 EXCEPTION
646 WHEN NO_DATA_FOUND THEN
647 x_lines_control.unit_price_change :=0;
648 END;
649
650 BEGIN
651 -- SQL What: Retrieving the percentage change in
652 -- exceed price tolerance
653 -- SQL Why: Need the value in tolerance check (i.e reapproval
654 -- rule validations)
655 -- SQL Join: po_line_id
656 SELECT max(po_chord_wf0.percentage_change(
657 POLA.not_to_exceed_price, POL.not_to_exceed_price))
658 INTO x_lines_control.not_to_exceed_price_change
659 FROM PO_LINES_ALL POL,
660 PO_LINES_ARCHIVE_ALL POLA
661 WHERE POL.po_header_id = x_po_header_id
662 AND POL.po_line_id = POLA.po_line_id (+)
663 AND POLA.latest_external_flag (+) = 'Y';
664
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 x_lines_control.not_to_exceed_price_change :=0;
668 END;
669
670 BEGIN
671 -- SQL What: Retrieving the percentage change in
672 -- commited quantity
673 -- SQL Why: Need the value in tolerance check (i.e reapproval
674 -- rule validations)
675 -- SQL Join: po_line_id
676 SELECT max(po_chord_wf0.percentage_change(
677 POLA.quantity_committed, POL.quantity_committed))
678 INTO x_lines_control.quantity_committed_change
679 FROM PO_LINES_ALL POL,
680 PO_LINES_ARCHIVE_ALL POLA
681 WHERE POL.po_header_id = x_po_header_id
682 AND POL.po_line_id = POLA.po_line_id (+)
683 AND POLA.latest_external_flag (+) = 'Y';
684
685 EXCEPTION
686 WHEN NO_DATA_FOUND THEN
687 x_lines_control.quantity_committed_change :=0;
688 END;
689
690 BEGIN
691 -- SQL What: Retrieving the percentage change in
692 -- commited amount
693 -- SQL Why: Need the value in tolerance check (i.e reapproval
694 -- rule validations)
695 -- SQL Join: po_line_id
696 SELECT max(po_chord_wf0.percentage_change(
697 POLA.committed_amount, POL.committed_amount))
698 INTO x_lines_control.committed_amount_change
699 FROM PO_LINES_ALL POL,
700 PO_LINES_ARCHIVE_ALL POLA
701 WHERE POL.po_header_id = x_po_header_id
702 AND POL.po_line_id = POLA.po_line_id (+)
703 AND POLA.latest_external_flag (+) = 'Y';
704
705 EXCEPTION
706 WHEN NO_DATA_FOUND THEN
707 x_lines_control.committed_amount_change :=0;
708 END;
709
710
711 -- Get the currency code and precision
712 SELECT poh.currency_code
713 INTO l_currency_code
714 FROM po_headers_all poh
715 WHERE poh.po_header_id = x_po_header_id;
716
717 PO_CORE_S2.get_currency_info(
718 x_currency_code => l_currency_code
719 , x_min_unit => l_min_acct_unit
720 , x_precision => l_precision);
721
722 --<R12 Requester Driven Procurement Start>
723 -- SQL What: Retrieving the percentage change in line amount
724 -- SQL Why: Need the value in tolerance check (i.e reapproval
725 -- rule validations)
726 -- SQL Join: po_line_id
727 -- Bug 5071741: Amount change is calculated using price and qty for
728 -- qty based lines and amount for services lines and rounded accordingly
729 BEGIN
730
731 IF l_min_acct_unit is not null AND
732 l_min_acct_unit <> 0 THEN
733
734 SELECT max(po_chord_wf0.percentage_change(
735 round(
736 decode(POLA.order_type_lookup_code, 'RATE',POLA.amount,'FIXED PRICE', POLA.amount,
737 (POLA.quantity*POLA.unit_price)) / l_min_acct_unit )* l_min_acct_unit ,
738 round(
739 decode(POL.order_type_lookup_code, 'RATE',POL.amount,'FIXED PRICE', POL.amount,
740 (POL.quantity*POL.unit_price)) / l_min_acct_unit )* l_min_acct_unit
741 ) )
742 INTO x_lines_control.amount_change
743 FROM PO_LINES_ALL POL,
744 PO_LINES_ARCHIVE_ALL POLA
745 WHERE POL.po_header_id = x_po_header_id
746 AND POL.po_line_id = POLA.po_line_id (+)
747 AND POLA.latest_external_flag (+) = 'Y';
748
749 ELSE
750 SELECT max(po_chord_wf0.percentage_change(
751 round(
752 decode(POLA.order_type_lookup_code, 'RATE',POLA.amount,'FIXED PRICE', POLA.amount,
753 (POLA.quantity*POLA.unit_price )) , l_precision) ,
754 round(
755 decode(POL.order_type_lookup_code, 'RATE',POL.amount,'FIXED PRICE', POL.amount,
756 (POL.quantity*POL.unit_price )) , l_precision)
757 ) )
758 INTO x_lines_control.amount_change
759 FROM PO_LINES_ALL POL,
760 PO_LINES_ARCHIVE_ALL POLA
761 WHERE POL.po_header_id = x_po_header_id
762 AND POL.po_line_id = POLA.po_line_id (+)
763 AND POLA.latest_external_flag (+) = 'Y';
764 END IF;
765
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 x_lines_control.amount_change :=0;
769 END;
770
771 -- Bug 5083205: Added start and end date change checks
772 -- SQL What: Retrieving the percentage change in start date
773 -- SQL Why: Need the value in tolerance check (i.e reapproval
774 -- rule validations)
775 -- SQL Join: line_id
776 BEGIN
777 SELECT max(trunc(POL.start_date-POLA.start_date))
778 INTO x_lines_control.start_date_change
779 FROM PO_LINES_ALL POL,
780 PO_LINES_ARCHIVE_ALL POLA
781 WHERE POL.po_header_id = x_po_header_id
782 AND POL.po_line_id = POLA.po_line_id (+)
783 AND POLA.latest_external_flag (+) = 'Y';
784
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787 x_lines_control.start_date_change := 0;
788 END;
789
790 BEGIN
791 -- Bug 5123672 Added query to check if date changed
792 -- SQL What: Select 'Y' if end date is changed
793 -- SQL Why: Need the value for routing to reapproval
794 -- if there is a change
795 -- SQL Join: po_line_id, line_num
799 PO_LINES_ARCHIVE_ALL POLA
796 SELECT DISTINCT 'Y'
797 INTO x_lines_control.end_date
798 FROM PO_LINES POL,
800 WHERE POL.po_header_id = x_po_header_id
801 AND POL.po_line_id = POLA.po_line_id (+)
802 AND POLA.latest_external_flag (+) = 'Y'
803 AND (
804 (POLA.po_line_id is NULL)
805 OR (POL.expiration_date <> POLA.expiration_date)
806 OR (POL.expiration_date IS NULL AND POLA.expiration_date IS NOT NULL)
807 OR (POL.expiration_date IS NOT NULL AND POLA.expiration_date IS NULL)
808 );
809
810 -- SQL What: Retrieving the change in end date
811 -- SQL Why: Need the value in tolerance check (i.e reapproval
812 -- rule validations)
813 -- SQL Join: line_id
814
815 SELECT max(trunc(POL.expiration_date-POLA.expiration_date))
816 INTO x_lines_control.end_date_change
817 FROM PO_LINES_ALL POL,
818 PO_LINES_ARCHIVE_ALL POLA
819 WHERE POL.po_header_id = x_po_header_id
820 AND POL.po_line_id = POLA.po_line_id (+)
821 AND POLA.latest_external_flag (+) = 'Y'
822 AND (POL.expiration_date IS NOT NULL OR POLA.expiration_date IS NOT NULL);
823 EXCEPTION
824 WHEN NO_DATA_FOUND THEN
825 x_lines_control.end_date_change := 0;
826 END;
827
828 --<R12 Requester Driven Procurement End>
829
830 IF (g_po_wf_debug = 'Y') THEN
831 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
832 '*** Finish: check_lines_change ***' );
833 END IF;
834
835 EXCEPTION
836
837 WHEN OTHERS THEN
838 wf_core.context('POAPPRV', 'check_lines_change', 'others');
839 RAISE;
840
841 END check_lines_change;
842
843 PROCEDURE set_wf_lines_control(itemtype IN VARCHAR2,
844 itemkey IN VARCHAR2,
845 x_lines_control IN t_lines_control_type)
846 IS
847 BEGIN
848 IF (g_po_wf_debug = 'Y') THEN
849 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
850 '*** In procedure: set_wf_lines_control ***');
851 END IF;
852
853 wf_engine.SetItemAttrText(itemtype,
854 itemkey,
855 'CO_L_LINE_NUM',
856 x_lines_control.line_num);
857
858 wf_engine.SetItemAttrText(itemtype,
859 itemkey,
860 'CO_L_ITEM',
861 x_lines_control.item_id);
862
863 wf_engine.SetItemAttrText(itemtype,
864 itemkey,
865 'CO_L_ITEM_REVISION',
866 x_lines_control.item_revision);
867
868
869 wf_engine.SetItemAttrText(itemtype,
870 itemkey,
871 'CO_L_CATEGORY',
872 x_lines_control.category_id);
873
874
875 wf_engine.SetItemAttrText(itemtype,
876 itemkey,
877 'CO_L_ITEM_DESCRIPTION',
878 x_lines_control.item_description);
879
880
881 wf_engine.SetItemAttrText(itemtype,
882 itemkey,
883 'CO_L_UOM',
884 x_lines_control.unit_meas_lookup_code);
885
886
887 wf_engine.SetItemAttrText(itemtype,
888 itemkey,
889 'CO_L_UN_NUMBER',
890 x_lines_control.un_number_id);
891
892
893 wf_engine.SetItemAttrText(itemtype,
894 itemkey,
895 'CO_L_HAZARD_CLASS',
896 x_lines_control.hazard_class_id);
897
898
899 wf_engine.SetItemAttrText(itemtype,
900 itemkey,
901 'CO_L_NOTE_TO_VENDOR',
902 x_lines_control.note_to_vendor);
903
904
905 wf_engine.SetItemAttrText(itemtype,
906 itemkey,
907 'CO_L_FROM_HEADER_ID',
908 x_lines_control.from_header_id);
909
910
911 wf_engine.SetItemAttrText(itemtype,
912 itemkey,
913 'CO_L_FROM_LINE_ID',
914 x_lines_control.from_line_id);
915
916
917 wf_engine.SetItemAttrText(itemtype,
918 itemkey,
919 'CO_L_CLOSED_CODE',
920 x_lines_control.closed_code);
921
922
923 wf_engine.SetItemAttrText(itemtype,
924 itemkey,
925 'CO_L_VENDOR_PRODUCT_NUM',
926 x_lines_control.vendor_product_num);
927
928
929 wf_engine.SetItemAttrText(itemtype,
930 itemkey,
931 'CO_L_CONTRACT_NUM',
932 x_lines_control.contract_num);
933
934
935 wf_engine.SetItemAttrText(itemtype,
936 itemkey,
937 'CO_L_PRICE_TYPE',
938 x_lines_control.price_type_lookup_code);
939
940 wf_engine.SetItemAttrText(itemtype,
941 itemkey,
942 'CO_L_CANCEL_FLAG',
943 x_lines_control.cancel_flag);
944
945 -- <Complex Work R12 Start>
946
947 wf_engine.SetItemAttrText(itemtype,
948 itemkey,
949 'CO_L_RETAINAGE_RATE',
950 x_lines_control.retainage_rate);
951
952 wf_engine.SetItemAttrText(itemtype,
953 itemkey,
954 'CO_L_MAX_RETAINAGE_AMOUNT',
955 x_lines_control.max_retainage_amount);
956
957 wf_engine.SetItemAttrText(itemtype,
958 itemkey,
959 'CO_L_PROGRESS_PAYMENT_RATE',
960 x_lines_control.progress_payment_rate);
961
962 wf_engine.SetItemAttrText(itemtype,
963 itemkey,
964 'CO_L_RECOUPMENT_RATE',
965 x_lines_control.recoupment_rate);
966
967 wf_engine.SetItemAttrText(itemtype,
968 itemkey,
969 'CO_L_ADVANCE_AMOUNT',
970 x_lines_control.advance_amount);
971
972 -- <Complex Work R12 End>
973
974
975 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
979
976 itemkey,
977 'CO_L_END_DATE',
978 x_lines_control.end_date);
980 wf_engine.SetItemAttrNumber(itemtype,
981 itemkey,
982 'CO_L_QUANTITY_CHANGE',
983 x_lines_control.quantity_change);
984
985 wf_engine.SetItemAttrNumber(itemtype,
986 itemkey,
987 'CO_L_UNIT_PRICE_CHANGE',
988 x_lines_control.unit_price_change);
989
990 wf_engine.SetItemAttrNumber(itemtype,
991 itemkey,
992 'CO_L_NOT_TO_EXCEED_PRICE',
993 x_lines_control.not_to_exceed_price_change);
994
995 wf_engine.SetItemAttrNumber(itemtype,
996 itemkey,
997 'CO_L_QTY_COMMITTED_CHANGE',
998 x_lines_control.quantity_committed_change);
999
1000 wf_engine.SetItemAttrNumber(itemtype,
1001 itemkey,
1002 'CO_L_COMMITTED_AMT_CHANGE',
1003 x_lines_control.committed_amount_change);
1004
1005 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype, itemkey,
1006 'CO_L_AMOUNT_CHANGE', x_lines_control.amount_change); --<R12 Requester Driven Procurement>
1007
1008 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1009 itemkey,
1010 'CO_L_START_DATE_CHANGE',
1011 x_lines_control.start_date_change);
1012
1013 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1014 itemkey,
1015 'CO_L_END_DATE_CHANGE',
1016 x_lines_control.end_date_change);
1017
1018 -- debug_lines_control(itemtype, itemkey, x_lines_control);
1019
1020 IF (g_po_wf_debug = 'Y') THEN
1021 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1022 '*** Finished: set_wf_lines_control ***');
1023 END IF;
1024
1025 END;
1026
1027
1028 PROCEDURE get_wf_lines_control(itemtype IN VARCHAR2,
1029 itemkey IN VARCHAR2,
1030 x_lines_control IN OUT NOCOPY t_lines_control_type)
1031 IS
1032 BEGIN
1033 IF (g_po_wf_debug = 'Y') THEN
1034 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1035 '*** In procedure: get_wf_lines_control ***');
1036 END IF;
1037
1038 x_lines_control.line_num :=
1039 wf_engine.GetItemAttrText(itemtype,
1040 itemkey,
1041 'CO_L_LINE_NUM');
1042
1043 x_lines_control.item_id :=
1044 wf_engine.GetItemAttrText(itemtype,
1045 itemkey,
1046 'CO_L_ITEM');
1047
1048 x_lines_control.item_revision :=
1049 wf_engine.GetItemAttrText(itemtype,
1050 itemkey,
1051 'CO_L_ITEM_REVISION');
1052
1053
1054 x_lines_control.category_id :=
1055 wf_engine.GetItemAttrText(itemtype,
1056 itemkey,
1057 'CO_L_CATEGORY');
1058
1059 x_lines_control.item_description :=
1060 wf_engine.GetItemAttrText(itemtype,
1061 itemkey,
1062 'CO_L_ITEM_DESCRIPTION');
1063
1064
1065 x_lines_control.unit_meas_lookup_code :=
1066 wf_engine.GetItemAttrText(itemtype,
1067 itemkey,
1068 'CO_L_UOM');
1069
1070 x_lines_control.un_number_id :=
1071 wf_engine.GetItemAttrText(itemtype,
1072 itemkey,
1073 'CO_L_UN_NUMBER');
1074
1075 x_lines_control.hazard_class_id :=
1076 wf_engine.GetItemAttrText(itemtype,
1077 itemkey,
1078 'CO_L_HAZARD_CLASS');
1079
1080 x_lines_control.note_to_vendor :=
1081 wf_engine.GetItemAttrText(itemtype,
1082 itemkey,
1083 'CO_L_NOTE_TO_VENDOR');
1084
1085
1086 x_lines_control.from_header_id :=
1087 wf_engine.GetItemAttrText(itemtype,
1088 itemkey,
1089 'CO_L_FROM_HEADER_ID');
1090
1091
1092 x_lines_control.from_line_id :=
1093 wf_engine.GetItemAttrText(itemtype,
1094 itemkey,
1095 'CO_L_FROM_LINE_ID');
1096
1097 x_lines_control.closed_code :=
1098 wf_engine.GetItemAttrText(itemtype,
1099 itemkey,
1100 'CO_L_CLOSED_CODE');
1101
1102 x_lines_control.vendor_product_num :=
1103 wf_engine.GetItemAttrText(itemtype,
1104 itemkey,
1105 'CO_L_VENDOR_PRODUCT_NUM');
1106
1107 x_lines_control.contract_num :=
1108 wf_engine.GetItemAttrText(itemtype,
1109 itemkey,
1110 'CO_L_CONTRACT_NUM');
1111
1112 x_lines_control.price_type_lookup_code :=
1113 wf_engine.GetItemAttrText(itemtype,
1114 itemkey,
1115 'CO_L_PRICE_TYPE');
1116
1117 x_lines_control.cancel_flag :=
1118 wf_engine.GetItemAttrText(itemtype,
1119 itemkey,
1120 'CO_L_CANCEL_FLAG');
1121
1122 -- <Complex Work R12 Start>
1123
1124 x_lines_control.retainage_rate:=
1125 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_L_RETAINAGE_RATE');
1126
1127 x_lines_control.max_retainage_amount:=
1128 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_L_MAX_RETAINAGE_AMOUNT');
1129
1130 x_lines_control.progress_payment_rate:=
1131 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_L_PROGRESS_PAYMENT_RATE');
1132
1133 x_lines_control.recoupment_rate:=
1134 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_L_RECOUPMENT_RATE');
1135
1136 x_lines_control.advance_amount:=
1137 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_L_ADVANCE_AMOUNT');
1138
1139 -- <Complex Work R12 End>
1140
1141 x_lines_control.end_date :=
1142 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1143 itemkey,
1144 'CO_L_END_DATE');
1145
1146 x_lines_control.quantity_change:=
1147 wf_engine.GetItemAttrNumber(itemtype,
1148 itemkey,
1152 wf_engine.GetItemAttrNumber(itemtype,
1149 'CO_L_QUANTITY_CHANGE');
1150
1151 x_lines_control.unit_price_change:=
1153 itemkey,
1154 'CO_L_UNIT_PRICE_CHANGE');
1155
1156 x_lines_control.not_to_exceed_price_change:=
1157 wf_engine.GetItemAttrNumber(itemtype,
1158 itemkey,
1159 'CO_L_NOT_TO_EXCEED_PRICE');
1160
1161 x_lines_control.quantity_committed_change:=
1162 wf_engine.GetItemAttrNumber(itemtype,
1163 itemkey,
1164 'CO_L_QTY_COMMITTED_CHANGE');
1165
1166 x_lines_control.committed_amount_change:=
1167 wf_engine.GetItemAttrNumber(itemtype,
1168 itemkey,
1169 'CO_L_COMMITTED_AMT_CHANGE');
1170
1171 x_lines_control.amount_change:=
1172 PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype,
1173 itemkey,
1174 'CO_L_AMOUNT_CHANGE'); --<R12 Requester Driven Procurement>
1175
1176 x_lines_control.start_date_change :=
1177 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1178 itemkey,
1179 'CO_L_START_DATE_CHANGE');
1180
1181 x_lines_control.end_date_change :=
1182 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1183 itemkey,
1184 'CO_L_END_DATE_CHANGE');
1185
1186 debug_lines_control(itemtype, itemkey, x_lines_control);
1187
1188 IF (g_po_wf_debug = 'Y') THEN
1189 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1190 '*** Finished: get_wf_lines_control ***');
1191 END IF;
1192
1193 END;
1194
1195 PROCEDURE get_wf_lines_parameters(itemtype IN VARCHAR2,
1196 itemkey IN VARCHAR2,
1197 x_lines_parameters IN OUT NOCOPY t_lines_parameters_type)
1198 IS
1199 BEGIN
1200
1201 IF (g_po_wf_debug = 'Y') THEN
1202 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1203 '*** In procedure get_wf_lines_parameters ***');
1204 END IF;
1205
1206 x_lines_parameters.po_header_id :=
1207 wf_engine.GetItemAttrNumber(itemtype,
1208 itemkey,
1209 'DOCUMENT_ID');
1210
1211 IF (g_po_wf_debug = 'Y') THEN
1212 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1213 'po_header_id = '|| to_char(x_lines_parameters.po_header_id));
1214 END IF;
1215
1216
1217 IF (g_po_wf_debug = 'Y') THEN
1218 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1219 '*** FINISH: get_wf_lines_parameters ***');
1220 END IF;
1221 END;
1222
1223
1224 PROCEDURE debug_lines_control(itemtype IN VARCHAR2,
1225 itemkey IN VARCHAR2,
1226 x_lines_control IN t_lines_control_type)
1227 IS
1228 BEGIN
1229 IF (g_po_wf_debug = 'Y') THEN
1230 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1231 '*** In procedure: debug_lines_control ***');
1232 END IF;
1233
1234 IF (g_po_wf_debug = 'Y') THEN
1235 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1236 'line_num : ' || x_lines_control.line_num);
1237 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1238 'item_id : ' || x_lines_control.item_id);
1239 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1240 'item_revision : ' || x_lines_control.item_revision);
1241 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1242 'category_id : ' || x_lines_control.category_id);
1243 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1244 'item_description : ' || x_lines_control.item_description);
1245 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1246 'unit_meas_lookup_code : ' || x_lines_control.unit_meas_lookup_code);
1247 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1248 'un_number_id : ' || x_lines_control.un_number_id);
1249 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1250 'hazard_class_id : ' || x_lines_control.hazard_class_id);
1251 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1252 'note_to_vendor : ' || x_lines_control.note_to_vendor);
1253 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1254 'from_header_id : ' || x_lines_control.from_header_id);
1255 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1256 'closed_code : ' || x_lines_control.closed_code);
1257 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1258 'vendor_product_num : ' || x_lines_control.vendor_product_num);
1259 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1260 'contract_num : ' || x_lines_control.contract_num);
1261 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1262 'price_type_lookup_code : ' || x_lines_control.price_type_lookup_code);
1263 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1264 'cancel_flag : ' || x_lines_control.cancel_flag);
1265 -- <Complex Work R12 Start>
1266 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1267 'retainage_rate : ' || x_lines_control.retainage_rate);
1268 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1269 'max_retainage_amount : ' || x_lines_control.max_retainage_amount);
1270 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1271 'progress_payment_rate : ' || x_lines_control.progress_payment_rate);
1272 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1273 'recoupment_rate : ' || x_lines_control.recoupment_rate);
1274 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1275 'advance_amount : ' || x_lines_control.advance_amount);
1276 -- <Complex Work R12 End>
1277 END IF;
1278
1279 IF (g_po_wf_debug = 'Y') THEN
1280 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1281 'quantity_change : ' || to_char(x_lines_control.quantity_change));
1282 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1283 'unit_price_change : ' || to_char(x_lines_control.unit_price_change));
1284 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1288 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1285 'not_to_exceed_price_change : ' || to_char(x_lines_control.not_to_exceed_price_change));
1286 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1287 'quantity_committed_change : ' || to_char(x_lines_control.quantity_committed_change));
1289 'committed_amount_change : ' || to_char(x_lines_control.committed_amount_change));
1290 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1291 'amount_change : ' || to_char(x_lines_control.amount_change)); --<R12 Requester Driven Procurement>
1292 END IF;
1293
1294 IF (g_po_wf_debug = 'Y') THEN
1295 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1296 '*** Finished: debug_lines_control ***');
1297 END IF;
1298 END;
1299
1300 END PO_CHORD_WF2;
[Home] [Help]