[Home] [Help]
PACKAGE BODY: APPS.PO_CHORD_WF4
Source
1 PACKAGE BODY PO_CHORD_WF4 AS
2 /* $Header: POXWCO4B.pls 120.2 2006/03/08 16:34:56 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_dist(itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 result OUT NOCOPY VARCHAR2)
12 IS
13 x_dist_control t_dist_control_type;
14 x_dist_parameters t_dist_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_dist ***' );
20 END IF;
21
22 IF funcmode <> 'RUN' THEN
23 result := 'COMPLETE';
24 return;
25 END IF;
26
27 get_wf_dist_parameters(itemtype, itemkey, x_dist_parameters);
28
29 check_dist_change(itemtype, itemkey, x_dist_parameters, x_dist_control);
30
31 set_wf_dist_control(itemtype, itemkey, x_dist_control);
32
33 IF (g_po_wf_debug = 'Y') THEN
34 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
35 '*** Finish: chord_dist ***' );
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_wf4.chord_dist', 'others');
45 RAISE;
46
47 END;
48
49 PROCEDURE check_dist_change(
50 itemtype IN VARCHAR2,
51 itemkey IN VARCHAR2,
52 x_dist_parameters IN t_dist_parameters_type,
53 x_dist_control IN OUT NOCOPY t_dist_control_type)
54 IS
55 x_po_header_id NUMBER:=NULL;
56 x_po_release_id NUMBER:=NULL;
57 e_invalid_setup EXCEPTION;
58 l_currency_code VARCHAR2(15);
59 l_min_acct_unit VARCHAR2(15);
60 l_precision VARCHAR2(15);
61 BEGIN
62 IF (g_po_wf_debug = 'Y') THEN
63 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
64 '*** In procedure: check_dist_change ***' );
65 END IF;
66
67 x_dist_control.distribution_num := 'N';
68 x_dist_control.deliver_to_person_id := 'N';
69 x_dist_control.rate := 'N';
70 x_dist_control.rate_date := 'N';
71 x_dist_control.gl_encumbered_date := 'N';
72 x_dist_control.code_combination_id := 'N';
73 x_dist_control.destination_subinventory := 'N';
74 x_dist_control.quantity_ordered_change :=0;
75 x_dist_control.rate_change :=0;
76 x_dist_control.amount_ordered_change :=0; --<R12 Requester Driven Procurement>
77
78 /* This package is shared by PO and Release
79 * Pre-condition: Either po_header_id or po_release_id is NULL
80 */
81
82 x_po_header_id := x_dist_parameters.po_header_id;
83 x_po_release_id := x_dist_parameters.po_release_id;
84
85 IF ((x_po_header_id IS NOT NULL AND x_po_release_id IS NOT NULL) OR
86 (x_po_header_id IS NULL AND x_po_release_id IS NULL)) THEN
87 raise e_invalid_setup;
88 END IF;
89
90 /*bug# 880416: changes from 110.5 -
91 csheu bug #875995: split the old SQLs based on x_po_header_id */
92
93 -- SQL What: Select 'Y' if distribution number is changed
94 -- SQL Why: Need the value for routing to reapproval
95 -- if there is a change
96 -- SQL Join: po_distribution_id, distribution_num
97 IF (x_po_header_id IS NOT NULL) THEN
98 BEGIN
99 SELECT DISTINCT 'Y'
100 INTO x_dist_control.distribution_num
101 FROM PO_DISTRIBUTIONS_ALL POD,
102 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
103 WHERE POD.po_header_id = x_po_header_id
104 AND POD.po_distribution_id = PODA.po_distribution_id (+)
105 AND PODA.latest_external_flag (+) = 'Y'
106 AND (
107 (PODA.po_distribution_id is NULL)
108 OR (POD.distribution_num <> PODA.distribution_num)
109 OR (POD.distribution_num IS NULL
110 AND PODA.distribution_num IS NOT NULL)
111 OR (POD.distribution_num IS NOT NULL
112 AND PODA.distribution_num IS NULL)
113 )
114 -- <Encumbrance FPJ>
115 AND POD.distribution_type <> 'AGREEMENT';
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 x_dist_control.distribution_num := 'N';
119 END;
120 ELSE
121 BEGIN
122 SELECT DISTINCT 'Y'
123 INTO x_dist_control.distribution_num
124 FROM PO_DISTRIBUTIONS_ALL POD,
125 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
126 WHERE POD.po_release_id = x_po_release_id
127 AND POD.po_distribution_id = PODA.po_distribution_id (+)
128 AND PODA.latest_external_flag (+) = 'Y'
129 AND (
130 (PODA.po_distribution_id is NULL)
131 OR (POD.distribution_num <> PODA.distribution_num)
132 OR (POD.distribution_num IS NULL
133 AND PODA.distribution_num IS NOT NULL)
134 OR (POD.distribution_num IS NOT NULL
135 AND PODA.distribution_num IS NULL)
136 );
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 x_dist_control.distribution_num := 'N';
140 END;
141 END IF;
142
143 -- SQL What: Select 'Y' if deliver to person is changed
144 -- SQL Why: Need the value for routing to reapproval
145 -- if there is a change
146 -- SQL Join: po_distribution_id, deliver_to_person_id
147 IF (x_po_header_id IS NOT NULL) THEN
148 BEGIN
149 SELECT DISTINCT 'Y'
150 INTO x_dist_control.deliver_to_person_id
151 FROM PO_DISTRIBUTIONS_ALL POD,
152 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
153 WHERE
154 POD.po_header_id = x_po_header_id
155 AND POD.po_distribution_id = PODA.po_distribution_id (+)
156 AND PODA.latest_external_flag (+) = 'Y'
157 AND (
158 (PODA.po_distribution_id is NULL)
159 OR (POD.deliver_to_person_id <> PODA.deliver_to_person_id)
160 OR (POD.deliver_to_person_id IS NULL
161 AND PODA.deliver_to_person_id IS NOT NULL)
162 OR (POD.deliver_to_person_id IS NOT NULL
163 AND PODA.deliver_to_person_id IS NULL)
164 )
165 -- <Encumbrance FPJ>
166 AND POD.distribution_type <> 'AGREEMENT';
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 x_dist_control.deliver_to_person_id := 'N';
170 END;
171 ELSE
172 BEGIN
173 SELECT DISTINCT 'Y'
174 INTO x_dist_control.deliver_to_person_id
175 FROM PO_DISTRIBUTIONS_ALL POD,
176 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
177 WHERE
178 POD.po_release_id = x_po_release_id
179 AND POD.po_distribution_id = PODA.po_distribution_id (+)
180 AND PODA.latest_external_flag (+) = 'Y'
181 AND (
182 (PODA.po_distribution_id is NULL)
183 OR (POD.deliver_to_person_id <> PODA.deliver_to_person_id)
184 OR (POD.deliver_to_person_id IS NULL
185 AND PODA.deliver_to_person_id IS NOT NULL)
186 OR (POD.deliver_to_person_id IS NOT NULL
187 AND PODA.deliver_to_person_id IS NULL)
188 );
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 x_dist_control.deliver_to_person_id := 'N';
192 END;
193 END IF;
194
195 -- SQL What: Select 'Y' if rate date is changed
196 -- SQL Why: Need the value for routing to reapproval
197 -- if there is a change
198 -- SQL Join: po_distribution_id, rate_date
199 IF (x_po_header_id IS NOT NULL) THEN
200 BEGIN
201 SELECT DISTINCT 'Y'
202 INTO x_dist_control.rate_date
203 FROM PO_DISTRIBUTIONS_ALL POD,
204 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
205 WHERE
206 POD.po_header_id = x_po_header_id
207 AND POD.po_distribution_id = PODA.po_distribution_id (+)
208 AND PODA.latest_external_flag (+) = 'Y'
209 AND (
210 (PODA.po_distribution_id is NULL)
211 OR (POD.rate_date <> PODA.rate_date)
212 OR (POD.rate_date IS NULL
213 AND PODA.rate_date IS NOT NULL)
214 OR (POD.rate_date IS NOT NULL
215 AND PODA.rate_date IS NULL)
216 )
217 -- <Encumbrance FPJ>
218 AND POD.distribution_type <> 'AGREEMENT';
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 x_dist_control.rate_date := 'N';
222 END;
223 ELSE
224 BEGIN
225 SELECT DISTINCT 'Y'
226 INTO x_dist_control.rate_date
227 FROM PO_DISTRIBUTIONS_ALL POD,
228 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
229 WHERE
230 POD.po_release_id = x_po_release_id
231 AND POD.po_distribution_id = PODA.po_distribution_id (+)
232 AND PODA.latest_external_flag (+) = 'Y'
233 AND (
234 (PODA.po_distribution_id is NULL)
235 OR (POD.rate_date <> PODA.rate_date)
236 OR (POD.rate_date IS NULL
237 AND PODA.rate_date IS NOT NULL)
238 OR (POD.rate_date IS NOT NULL
239 AND PODA.rate_date IS NULL)
240 );
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 x_dist_control.rate_date := 'N';
244 END;
245 END IF;
246
247 -- SQL What: Select 'Y' if encumbered date is changed
248 -- SQL Why: Need the value for routing to reapproval
249 -- if there is a change
250 -- SQL Join: po_distribution_id, gl_encumbered_date
251 IF (x_po_header_id IS NOT NULL) THEN
252 BEGIN
253 SELECT DISTINCT 'Y'
254 INTO x_dist_control.gl_encumbered_date
255 FROM PO_DISTRIBUTIONS_ALL POD,
256 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
257 WHERE
258 POD.po_header_id = x_po_header_id
259 AND POD.po_distribution_id = PODA.po_distribution_id (+)
260 AND PODA.latest_external_flag (+) = 'Y'
261 AND (
262 (PODA.po_distribution_id is NULL)
263 OR (POD.gl_encumbered_date <> PODA.gl_encumbered_date)
264 OR (POD.gl_encumbered_date IS NULL
265 AND PODA.gl_encumbered_date IS NOT NULL)
266 OR (POD.gl_encumbered_date IS NOT NULL
267 AND PODA.gl_encumbered_date IS NULL)
268 )
269 -- <Encumbrance FPJ>
270 AND POD.distribution_type <> 'AGREEMENT';
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 x_dist_control.gl_encumbered_date := 'N';
274 END;
275 ELSE
276 BEGIN
277 SELECT DISTINCT 'Y'
278 INTO x_dist_control.gl_encumbered_date
279 FROM PO_DISTRIBUTIONS_ALL POD,
280 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
281 WHERE
282 POD.po_release_id = x_po_release_id
283 AND POD.po_distribution_id = PODA.po_distribution_id (+)
284 AND PODA.latest_external_flag (+) = 'Y'
285 AND (
286 (PODA.po_distribution_id is NULL)
287 OR (POD.gl_encumbered_date <> PODA.gl_encumbered_date)
288 OR (POD.gl_encumbered_date IS NULL
289 AND PODA.gl_encumbered_date IS NOT NULL)
290 OR (POD.gl_encumbered_date IS NOT NULL
291 AND PODA.gl_encumbered_date IS NULL)
292 );
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 x_dist_control.gl_encumbered_date := 'N';
296 END;
297
298 END IF;
299
300 -- SQL What: Select 'Y' if code_combination_id is changed
301 -- SQL Why: Need the value for routing to reapproval
302 -- if there is a change
303 -- SQL Join: po_distribution_id, code_combination_id
304 IF (x_po_header_id IS NOT NULL) THEN
305 BEGIN
306 SELECT DISTINCT 'Y'
307 INTO x_dist_control.code_combination_id
308 FROM PO_DISTRIBUTIONS_ALL POD,
309 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
310 WHERE POD.po_header_id = x_po_header_id
311 AND POD.po_distribution_id = PODA.po_distribution_id (+)
312 AND PODA.latest_external_flag (+) = 'Y'
313 AND (
314 (PODA.po_distribution_id is NULL)
315 OR (POD.code_combination_id <> PODA.code_combination_id)
316 OR (POD.code_combination_id IS NULL
317 AND PODA.code_combination_id IS NOT NULL)
318 OR (POD.code_combination_id IS NOT NULL
319 AND PODA.code_combination_id IS NULL)
320 )
321 -- <Encumbrance FPJ>
322 AND POD.distribution_type <> 'AGREEMENT';
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 x_dist_control.code_combination_id := 'N';
326 END;
327 ELSE
328 BEGIN
329 SELECT DISTINCT 'Y'
330 INTO x_dist_control.code_combination_id
331 FROM PO_DISTRIBUTIONS_ALL POD,
332 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
333 WHERE
334 POD.po_release_id = x_po_release_id
335 AND POD.po_distribution_id = PODA.po_distribution_id (+)
336 AND PODA.latest_external_flag (+) = 'Y'
337 AND (
338 (PODA.po_distribution_id is NULL)
339 OR (POD.code_combination_id <> PODA.code_combination_id)
340 OR (POD.code_combination_id IS NULL
341 AND PODA.code_combination_id IS NOT NULL)
342 OR (POD.code_combination_id IS NOT NULL
343 AND PODA.code_combination_id IS NULL)
344 );
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 x_dist_control.code_combination_id := 'N';
348 END;
349
350 END IF;
351
352 -- SQL What: Select 'Y' if destination subinventory is changed
353 -- SQL Why: Need the value for routing to reapproval
354 -- if there is a change
355 -- SQL Join: po_distribution_id, destination_subinventory
356 IF (x_po_header_id IS NOT NULL) THEN
357 BEGIN
358 SELECT DISTINCT 'Y'
359 INTO x_dist_control.destination_subinventory
360 FROM PO_DISTRIBUTIONS_ALL POD,
361 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
362 WHERE
363 POD.po_header_id = x_po_header_id
364 AND POD.po_distribution_id = PODA.po_distribution_id (+)
365 AND PODA.latest_external_flag (+) = 'Y'
366 AND (
367 (PODA.po_distribution_id is NULL)
368 OR (POD.destination_subinventory <> PODA.destination_subinventory)
369 OR (POD.destination_subinventory IS NULL
370 AND PODA.destination_subinventory IS NOT NULL)
371 OR (POD.destination_subinventory IS NOT NULL
372 AND PODA.destination_subinventory IS NULL)
373 )
374 -- <Encumbrance FPJ>
375 AND POD.distribution_type <> 'AGREEMENT';
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 x_dist_control.destination_subinventory := 'N';
379 END;
380 ELSE
381 BEGIN
382 SELECT DISTINCT 'Y'
383 INTO x_dist_control.destination_subinventory
384 FROM PO_DISTRIBUTIONS_ALL POD,
385 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
386 WHERE
387 POD.po_release_id = x_po_release_id
388 AND POD.po_distribution_id = PODA.po_distribution_id (+)
389 AND PODA.latest_external_flag (+) = 'Y'
390 AND (
391 (PODA.po_distribution_id is NULL)
392 OR (POD.destination_subinventory <> PODA.destination_subinventory)
393 OR (POD.destination_subinventory IS NULL
394 AND PODA.destination_subinventory IS NOT NULL)
395 OR (POD.destination_subinventory IS NOT NULL
396 AND PODA.destination_subinventory IS NULL)
397 );
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 x_dist_control.destination_subinventory := 'N';
401 END;
402 END IF;
403
404 -- SQL What: Retrieving the percentage change in quantity ordered
405 -- SQL Why: Need the value in tolerance check (i.e reapproval
406 -- rule validations)
407 -- SQL Join: po_distribution_id
408 IF (x_po_header_id IS NOT NULL) THEN
409 BEGIN
410
411 SELECT max(po_chord_wf0.percentage_change(
412 PODA.quantity_ordered, POD.quantity_ordered))
413 INTO x_dist_control.quantity_ordered_change
414 FROM PO_DISTRIBUTIONS_ALL POD,
415 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
416 WHERE
417 POD.po_header_id = x_po_header_id
418 AND POD.po_distribution_id = PODA.po_distribution_id (+)
419 AND PODA.latest_external_flag (+) = 'Y'
420 -- <Encumbrance FPJ>
421 AND POD.distribution_type <> 'AGREEMENT';
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 x_dist_control.quantity_ordered_change := 0;
425 END;
426 ELSE
427 BEGIN
428
429 SELECT max(po_chord_wf0.percentage_change(
430 PODA.quantity_ordered, POD.quantity_ordered))
431 INTO x_dist_control.quantity_ordered_change
432 FROM PO_DISTRIBUTIONS_ALL POD,
433 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
434 WHERE
435 POD.po_release_id = x_po_release_id
436 AND POD.po_distribution_id = PODA.po_distribution_id (+)
437 AND PODA.latest_external_flag (+) = 'Y';
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 x_dist_control.quantity_ordered_change := 0;
441 END;
442 END IF;
443
444 -- SQL What: Retrieving the percentage change in rate
445 -- SQL Why: Need the value in tolerance check (i.e reapproval
446 -- rule validations)
447 -- SQL Join: po_distribution_id
448 IF (x_po_header_id IS NOT NULL) THEN
449 BEGIN
450 SELECT max(po_chord_wf0.percentage_change(
451 PODA.rate, POD.rate))
452 INTO x_dist_control.rate_change
453 FROM PO_DISTRIBUTIONS_ALL POD,
454 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
455 WHERE
456 POD.po_header_id = x_po_header_id
457 AND POD.po_distribution_id = PODA.po_distribution_id (+)
458 AND PODA.latest_external_flag (+) = 'Y'
459 -- <Encumbrance FPJ>
460 AND POD.distribution_type <> 'AGREEMENT';
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 x_dist_control.rate_change := 0;
464 END;
465 ELSE
466 BEGIN
467
468 SELECT max(po_chord_wf0.percentage_change(
469 PODA.rate, POD.rate))
470 INTO x_dist_control.rate_change
471 FROM PO_DISTRIBUTIONS_ALL POD,
472 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
473 WHERE
474 POD.po_release_id = x_po_release_id
475 AND POD.po_distribution_id = PODA.po_distribution_id (+)
476 AND PODA.latest_external_flag (+) = 'Y';
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 x_dist_control.rate_change := 0;
480 END;
481
482 END IF;
483
484 --<R12 Requester Driven Procurement Start>
485
486 -- SQL What: Retrieving the percentage change in amount ordered
487 -- SQL Why: Need the value in tolerance check (i.e reapproval
488 -- rule validations)
489 -- SQL Join: po_distribution_id
490 -- Bug 5071741: Amount change is calculated using price and qty for
491 -- qty based lines and amount for services lines and rounded accordingly
492 IF (x_po_header_id IS NOT NULL) THEN
493
494 -- Get the currency code and precision
495 SELECT poh.currency_code
496 INTO l_currency_code
497 FROM po_headers_all poh
498 WHERE poh.po_header_id = x_po_header_id;
499
500 PO_CORE_S2.get_currency_info(
501 x_currency_code => l_currency_code
502 , x_min_unit => l_min_acct_unit
503 , x_precision => l_precision);
504
505 BEGIN
506
507 IF l_min_acct_unit is not null AND
508 l_min_acct_unit <> 0 THEN
509
510 SELECT max(po_chord_wf0.percentage_change(
511 round (
512 decode(POLLA.value_basis, 'RATE',PODA.amount_ordered,'FIXED PRICE', PODA.amount_ordered,
513 (PODA.quantity_ordered*POLLA.price_override))/ l_min_acct_unit )* l_min_acct_unit ,
514 round (
515 decode(POLL.value_basis, 'RATE',POD.amount_ordered,'FIXED PRICE', POD.amount_ordered,
516 (POD.quantity_ordered*POLL.price_override)) / l_min_acct_unit )* l_min_acct_unit
517 ))
518 INTO x_dist_control.amount_ordered_change
519 FROM PO_DISTRIBUTIONS_ALL POD,
520 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA,
521 PO_LINE_LOCATIONS_ALL POLL,
522 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
523 WHERE POD.po_header_id = x_po_header_id
524 AND POD.po_distribution_id = PODA.po_distribution_id (+)
525 AND PODA.latest_external_flag (+) = 'Y'
526 AND POD.distribution_type <> 'AGREEMENT'
527 AND POD.line_location_id = POLL.line_location_id -- Bug 5071741
528 AND PODA.line_location_id = POLLA.line_location_id -- Bug 5071741
529 AND POLL.line_location_id = POLLA.line_location_id (+) -- Bug 5071741
530 AND POLLA.latest_external_flag (+) = 'Y' -- Bug 5071741
531 AND POLL.po_release_id is NULL; -- Bug 5071741
532
533 ELSE
534 SELECT max(po_chord_wf0.percentage_change(
535 round (
536 decode(POLLA.value_basis, 'RATE',PODA.amount_ordered,'FIXED PRICE', PODA.amount_ordered,
537 (PODA.quantity_ordered*POLLA.price_override)), l_precision ) ,
538 round (
539 decode(POLL.value_basis, 'RATE',POD.amount_ordered,'FIXED PRICE', POD.amount_ordered,
540 (POD.quantity_ordered*POLL.price_override)) , l_precision )
541 ))
542 INTO x_dist_control.amount_ordered_change
543 FROM PO_DISTRIBUTIONS_ALL POD,
544 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA,
545 PO_LINE_LOCATIONS_ALL POLL,
546 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
547 WHERE POD.po_header_id = x_po_header_id
548 AND POD.po_distribution_id = PODA.po_distribution_id (+)
549 AND PODA.latest_external_flag (+) = 'Y'
550 AND POD.distribution_type <> 'AGREEMENT'
551 AND POD.line_location_id = POLL.line_location_id -- Bug 5071741
552 AND PODA.line_location_id = POLLA.line_location_id -- Bug 5071741
553 AND POLL.line_location_id = POLLA.line_location_id (+) -- Bug 5071741
554 AND POLLA.latest_external_flag (+) = 'Y' -- Bug 5071741
555 AND POLL.po_release_id is NULL; -- Bug 5071741
556 END IF;
557
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 x_dist_control.amount_ordered_change := 0;
561 END;
562
563 ELSE -- po_header_id null : release
564
565 -- Get the currency code and precision
566 SELECT poh.currency_code
567 INTO l_currency_code
568 FROM po_releases_all por,
569 po_headers_all poh
570 WHERE por.po_release_id = x_po_release_id
571 AND poh.po_header_id = por.po_header_id;
572
573 PO_CORE_S2.get_currency_info(
574 x_currency_code => l_currency_code
575 , x_min_unit => l_min_acct_unit
576 , x_precision => l_precision);
577
578 BEGIN
579
580 IF l_min_acct_unit is not null AND
581 l_min_acct_unit <> 0 THEN
582
583 SELECT max(po_chord_wf0.percentage_change(
584 round (
585 decode(POLLA.value_basis,'FIXED PRICE', PODA.amount_ordered,
586 (PODA.quantity_ordered*POLLA.price_override))/ l_min_acct_unit )* l_min_acct_unit ,
587 round (
588 decode(POLL.value_basis, 'FIXED PRICE', POD.amount_ordered,
589 (POD.quantity_ordered*POLL.price_override)) / l_min_acct_unit )* l_min_acct_unit
590 ))
591 INTO x_dist_control.amount_ordered_change
592 FROM PO_DISTRIBUTIONS_ALL POD,
593 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA,
594 PO_LINE_LOCATIONS_ALL POLL,
595 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
596 WHERE POD.po_release_id = x_po_release_id
597 AND POD.po_distribution_id = PODA.po_distribution_id (+)
598 AND PODA.latest_external_flag (+) = 'Y'
599 AND POD.line_location_id = POLL.line_location_id -- Bug 5071741
600 AND PODA.line_location_id = POLLA.line_location_id -- Bug 5071741
601 AND POLL.line_location_id = POLLA.line_location_id (+) -- Bug 5071741
602 AND POLLA.latest_external_flag (+) = 'Y'; -- Bug 5071741
603
604 ELSE
605
606 SELECT max(po_chord_wf0.percentage_change(
607 round (
608 decode(POLLA.value_basis,'FIXED PRICE', PODA.amount_ordered,
609 (PODA.quantity_ordered*POLLA.price_override)) , l_precision ) ,
610 round (
611 decode(POLL.value_basis, 'FIXED PRICE', POD.amount_ordered,
612 (POD.quantity_ordered*POLL.price_override)) , l_precision )
613 ))
614 INTO x_dist_control.amount_ordered_change
615 FROM PO_DISTRIBUTIONS_ALL POD,
616 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA,
617 PO_LINE_LOCATIONS_ALL POLL,
618 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
619 WHERE POD.po_release_id = x_po_release_id
620 AND POD.po_distribution_id = PODA.po_distribution_id (+)
621 AND PODA.latest_external_flag (+) = 'Y'
622 AND POD.line_location_id = POLL.line_location_id -- Bug 5071741
623 AND PODA.line_location_id = POLLA.line_location_id -- Bug 5071741
624 AND POLL.line_location_id = POLLA.line_location_id (+) -- Bug 5071741
625 AND POLLA.latest_external_flag (+) = 'Y'; -- Bug 5071741
626
627 END IF;
628
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 x_dist_control.amount_ordered_change := 0;
632 END;
633 END IF;
634
635 --<R12 Requester Driven Procurement End>
636
637 --debug_dist_control(itemtype, itemkey, x_dist_control);
638
639 IF (g_po_wf_debug = 'Y') THEN
640 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
641 '*** Finish: check_dist_change ***' );
642 END IF;
643
644 EXCEPTION
645 WHEN e_invalid_setup THEN
646 IF (g_po_wf_debug = 'Y') THEN
647 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
648 '*** exception check_dist_change ***');
649 END IF;
650 wf_core.context('POAPPRV', 'set_wf_dist_control', 'e_invalid_setup');
651 raise;
652
653 WHEN others THEN
654 IF (g_po_wf_debug = 'Y') THEN
655 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
656 '*** exeption: check_dist_change ***');
657 END IF;
658 wf_core.context('POAPPRV', 'set_wf_dist_control', 'others');
659 raise;
660
661 END;
662
663 PROCEDURE set_wf_dist_control( itemtype IN VARCHAR2,
664 itemkey IN VARCHAR2,
665 x_dist_control IN t_dist_control_type)
666 IS
667 BEGIN
668 IF (g_po_wf_debug = 'Y') THEN
669 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
670 '*** In Procedure:set_wf_dist_control ***' );
671 END IF;
672
673 wf_engine.SetItemAttrText(itemtype,
674 itemkey,
675 'CO_D_DIST_NUM',
676 x_dist_control.distribution_num);
677
678 wf_engine.SetItemAttrText(itemtype,
679 itemkey,
680 'CO_D_DELIVER_TO_PERSON',
681 x_dist_control.deliver_to_person_id);
682
683 wf_engine.SetItemAttrText(itemtype,
684 itemkey,
685 'CO_D_RATE_DATE',
686 x_dist_control.rate_date);
687
688
689 wf_engine.SetItemAttrText(itemtype,
690 itemkey,
691 'CO_D_GL_ENCUMBERED_DATE',
692 x_dist_control.gl_encumbered_date);
693
694
695 wf_engine.SetItemAttrText(itemtype,
696 itemkey,
697 'CO_D_CHARGE_ACCOUNT',
698 x_dist_control.code_combination_id);
699
700 wf_engine.SetItemAttrText(itemtype,
701 itemkey,
702 'CO_D_DEST_SUBINVENTORY',
703 x_dist_control.destination_subinventory);
704
705 wf_engine.SetItemAttrText(itemtype,
706 itemkey,
707 'CO_D_DEST_SUBINVENTORY',
708 x_dist_control.destination_subinventory);
709
710 wf_engine.SetItemAttrNumber(itemtype,
711 itemkey,
712 'CO_D_QUANTITY_ORDERED_CHANGE',
713 x_dist_control.quantity_ordered_change);
714
715 wf_engine.SetItemAttrNumber(itemtype,
716 itemkey,
717 'CO_D_RATE_CHANGE',
718 x_dist_control.rate_change);
719
720 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
721 itemkey,
722 'CO_D_AMOUNT_ORDERED_CHANGE',
723 x_dist_control.amount_ordered_change); --<R12 Requester Driven Procurement>
724
725 IF (g_po_wf_debug = 'Y') THEN
726 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
727 '*** Finish:set_wf_dist_control ***' );
728 END IF;
729 EXCEPTION
730 WHEN others THEN
731 IF (g_po_wf_debug = 'Y') THEN
732 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
733 '*** exeption: set_wf_dist_control ***');
734 END IF;
735 wf_core.context('POAPPRV', 'set_wf_dist_control', 'others');
736 raise;
737
738 END;
739
740
741 PROCEDURE get_wf_dist_control( itemtype IN VARCHAR2,
742 itemkey IN VARCHAR2,
743 x_dist_control IN OUT NOCOPY t_dist_control_type)
744 IS
745 BEGIN
746 IF (g_po_wf_debug = 'Y') THEN
747 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
748 '*** In Procedure :get_wf_dist_control ***' );
749 END IF;
750
751 x_dist_control.distribution_num:=
752 wf_engine.GetItemAttrText(itemtype,
753 itemkey,
754 'CO_D_DIST_NUM');
755
756 x_dist_control.deliver_to_person_id:=
757 wf_engine.GetItemAttrText(itemtype,
758 itemkey,
759 'CO_D_DELIVER_TO_PERSON');
760
761 x_dist_control.rate_date :=
762 wf_engine.GetItemAttrText(itemtype,
763 itemkey,
764 'CO_D_RATE_DATE');
765
766 x_dist_control.gl_encumbered_date :=
767 wf_engine.GetItemAttrText(itemtype,
768 itemkey,
769 'CO_D_GL_ENCUMBERED_DATE');
770
771 x_dist_control.code_combination_id :=
772 wf_engine.GetItemAttrText(itemtype,
773 itemkey,
774 'CO_D_CHARGE_ACCOUNT');
775
776 x_dist_control.destination_subinventory :=
777 wf_engine.GetItemAttrText(itemtype,
778 itemkey,
779 'CO_D_DEST_SUBINVENTORY');
780
781 x_dist_control.quantity_ordered_change :=
782 wf_engine.GetItemAttrNumber(itemtype,
783 itemkey,
784 'CO_D_QUANTITY_ORDERED_CHANGE');
785
786 x_dist_control.rate_change :=
787 wf_engine.GetItemAttrNumber(itemtype,
788 itemkey,
789 'CO_D_RATE_CHANGE');
790
791 x_dist_control.amount_ordered_change :=
792 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
793 itemkey,
794 'CO_D_AMOUNT_ORDERED_CHANGE'); --<R12 Requester Driven Procurement>
795
796 debug_dist_control(itemtype, itemkey, x_dist_control);
797
798 IF (g_po_wf_debug = 'Y') THEN
799 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
800 '*** Finished: get_wf_dist_control ***' );
801 END IF;
802
803 END;
804
805 PROCEDURE get_wf_dist_parameters(itemtype IN VARCHAR2,
806 itemkey IN VARCHAR2,
807 x_dist_parameters IN OUT NOCOPY t_dist_parameters_type)
808 IS
809 x_doc_type VARCHAR2(25);
810 e_invalid_doc_type EXCEPTION;
811 BEGIN
812 IF (g_po_wf_debug = 'Y') THEN
813 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
814 '*** In procedure get_wf_dist_parameters ***');
815 END IF;
816
817 x_doc_type := wf_engine.GetItemAttrText (itemtype,
818 itemkey,
819 'DOCUMENT_TYPE');
820
821 IF x_doc_type IN ('PO', 'PA') THEN
822
823 x_dist_parameters.po_header_id :=
824 wf_engine.GetItemAttrNumber(itemtype,
825 itemkey,
826 'DOCUMENT_ID');
827
828 x_dist_parameters.po_release_id:=NULL;
829
830 ELSIF x_doc_type = 'RELEASE' THEN
831
832 x_dist_parameters.po_release_id :=
833 wf_engine.GetItemAttrNumber(itemtype,
834 itemkey,
835 'DOCUMENT_ID');
836
837 x_dist_parameters.po_header_id:=NULL;
838
839 ELSE
840 raise e_invalid_doc_type;
841
842 END IF;
843
844 IF (g_po_wf_debug = 'Y') THEN
845 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
846 'po_header_id = ' || to_char(x_dist_parameters.po_header_id));
847 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
848 'po_release_id = '|| to_char(x_dist_parameters.po_release_id));
849 END IF;
850
851 IF (g_po_wf_debug = 'Y') THEN
852 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
853 '*** FINISH: get_wf_dist_parameters ***');
854 END IF;
855
856 EXCEPTION
857 WHEN e_invalid_doc_type THEN
858 IF (g_po_wf_debug = 'Y') THEN
859 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
860 '***set_wf_dist_parameters exception e_invalid_setup *** ');
861 END IF;
862 wf_core.context('POAPPRV', 'set_wf_dist_control', 'e_invalid_setup');
863 raise;
864
865 WHEN OTHERS THEN
866 IF (g_po_wf_debug = 'Y') THEN
867 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
868 '***set_wf_dist_parameters exception *** ');
869 END IF;
870 wf_core.context('POAPPRV', 'set_wf_dist_control', 'others');
871 raise;
872
873
874 END;
875
876
877
878 PROCEDURE debug_dist_control(
879 itemtype IN VARCHAR2,
880 itemkey IN VARCHAR2,
881 x_dist_control IN t_dist_control_type)
882 IS
883 BEGIN
884 IF (g_po_wf_debug = 'Y') THEN
885 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
886 '*** In Procedure: debug_dist_control ***' );
887 END IF;
888
889 IF (g_po_wf_debug = 'Y') THEN
890 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
891 'distribution_num : '|| x_dist_control.distribution_num);
892 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
893 'deliver_to_person_id : '|| x_dist_control.deliver_to_person_id);
894 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
895 'rate_date : '|| x_dist_control.rate_date);
896 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
897 'gl_encumbered_date : '|| x_dist_control.gl_encumbered_date);
898 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
899 'code_combination_id : '|| x_dist_control.code_combination_id);
900 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
901 'destination_subinventory : '|| x_dist_control.destination_subinventory);
902 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
903 'quantity_ordered_change : '|| to_char(x_dist_control.quantity_ordered_change));
904 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
905 'amount_ordered_change : '|| to_char(x_dist_control.amount_ordered_change)); --<R12 Requester Driven Procurement>
906 END IF;
907
908 IF (g_po_wf_debug = 'Y') THEN
909 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
910 '*** Finished: debug_dist_control **' );
911 END IF;
912
913 END;
914
915
916 END PO_CHORD_WF4;