1 PACKAGE BODY PO_CHORD_WF3 AS
2 /* $Header: POXWCO3B.pls 120.9 2006/04/14 11:36:34 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_shipments(itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 result OUT NOCOPY VARCHAR2)
12 IS
13 x_shipments_control t_shipments_control_type;
14 x_shipments_parameters t_shipments_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_shipments ***' );
20 END IF;
21
22 IF funcmode <> 'RUN' THEN
23 result := 'COMPLETE';
24 return;
25 END IF;
26
27 get_wf_shipments_parameters(itemtype, itemkey, x_shipments_parameters);
28
29 check_shipments_change(itemtype, itemkey, x_shipments_parameters, x_shipments_control);
30
31 set_wf_shipments_control(itemtype, itemkey, x_shipments_control);
32
33 IF (g_po_wf_debug = 'Y') THEN
34 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
35 '*** Finish: chord_shipments ***' );
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_wf3.chord_shipments', 'others');
45 RAISE;
46
47 END;
48
49 PROCEDURE check_shipments_change(
50 itemtype IN VARCHAR2,
51 itemkey IN VARCHAR2,
52 x_shipments_parameters IN t_shipments_parameters_type,
53 x_shipments_control OUT NOCOPY t_shipments_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
62 BEGIN
63 IF (g_po_wf_debug = 'Y') THEN
64 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
65 '*** In procedure: check_shipments_change ***' );
66 END IF;
67
68 /* To use change order,
69 * System should have Archive on Approval set
70 */
71
72 x_shipments_control.shipment_num :='N';
73 x_shipments_control.ship_to_organization_id :='N';
74 x_shipments_control.ship_to_location_id :='N';
75 x_shipments_control.promised_date :='N';
76 x_shipments_control.need_by_date :='N';
77 x_shipments_control.last_accept_date :='N';
78 x_shipments_control.taxable_flag :='N';
79 x_shipments_control.price_discount :='N';
80 x_shipments_control.cancel_flag :='N';
81 x_shipments_control.closed_code :='N';
82 x_shipments_control.start_date :='N'; /* <TIMEPHASED FPI> */
83 x_shipments_control.end_date :='N'; /* <TIMEPHASED FPI> */
84 x_shipments_control.price_override :='N'; /* Bug 2808011 */
85 x_shipments_control.days_late_rcpt_allowed :='N'; -- ECO 5080252
86
87 -- <Complex Work R12 Start>
88 x_shipments_control.payment_type := 'N';
89 x_shipments_control.work_approver_id := 'N';
90 x_shipments_control.description := 'N';
91 -- <Complex Work R12 End>
92
93 x_shipments_control.quantity_change :=0;
94 x_shipments_control.price_override_change :=0;
95
96 --<R12 Requester Driven Procurement Start>
97
98 x_shipments_control.amount_change :=0;
99 x_shipments_control.start_date_change :=0;
100 x_shipments_control.end_date_change :=0;
101 x_shipments_control.need_by_date_change :=0;
102 x_shipments_control.promised_date_change :=0;
103
104 --<R12 Requester Driven Procurement End>
105
106 /* This package is shared by PO and Release
107 * Pre-condition: Either po_header_id or po_release_id is NULL
108 */
109
110 x_po_header_id := x_shipments_parameters.po_header_id;
111 x_po_release_id := x_shipments_parameters.po_release_id;
112
113 IF ((x_po_header_id IS NOT NULL AND x_po_release_id IS NOT NULL) OR
114 (x_po_header_id IS NULL AND x_po_release_id IS NULL)) THEN
115 raise e_invalid_setup;
116 END IF;
117
118 /* bug# 880416: brought forward the following changes from 110.5.
119 csheu bug #875995: split the old SQLs based on x_po_header_id */
120
121 -- SQL What: Select 'Y' if Shipment number is changed
122 -- SQL Why: Need the value for routing to reapproval
123 -- if there is a change
124 -- SQL Join: line_location_id, shipment_num
125 IF (x_po_header_id IS NOT NULL) THEN
126 BEGIN
127 SELECT DISTINCT 'Y'
128 INTO x_shipments_control.shipment_num
129 FROM PO_LINE_LOCATIONS_ALL POLL,
130 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
131 WHERE
132 POLL.po_header_id = x_po_header_id
133 AND POLL.line_location_id = POLLA.line_location_id (+)
134 AND POLLA.latest_external_flag (+) = 'Y'
135 AND (
136 (POLLA.line_location_id is NULL)
137 OR (POLL.shipment_num <> POLLA.shipment_num)
138 OR (POLL.shipment_num IS NULL AND POLLA.shipment_num IS NOT NULL)
139 OR (POLL.shipment_num IS NOT NULL AND POLLA.shipment_num IS NULL)
140 )
141 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 x_shipments_control.shipment_num := 'N';
146 END;
147 ELSE
148 BEGIN
149 SELECT DISTINCT 'Y'
150 INTO x_shipments_control.shipment_num
151 FROM PO_LINE_LOCATIONS_ALL POLL,
152 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
153 WHERE
154 POLL.po_release_id = x_po_release_id
155 AND POLL.line_location_id = POLLA.line_location_id (+)
156 AND POLLA.latest_external_flag (+) = 'Y'
157 AND (
158 (POLLA.line_location_id is NULL)
159 OR (POLL.shipment_num <> POLLA.shipment_num)
160 OR (POLL.shipment_num IS NULL AND POLLA.shipment_num IS NOT NULL)
161 OR (POLL.shipment_num IS NOT NULL AND POLLA.shipment_num IS NULL)
162 );
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 x_shipments_control.shipment_num := 'N';
166 END;
167 END IF;
168
169 -- SQL What: Select 'Y' if Ship to is changed
170 -- SQL Why: Need the value for routing to reapproval
171 -- if there is a change
172 -- SQL Join: line_location_id, ship_to_organization_id
173 IF (x_po_header_id IS NOT NULL) THEN
174 BEGIN
175 SELECT DISTINCT 'Y'
176 INTO x_shipments_control.ship_to_organization_id
177 FROM PO_LINE_LOCATIONS_ALL POLL,
178 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
179 WHERE
180 POLL.po_header_id = x_po_header_id
181 AND POLL.line_location_id = POLLA.line_location_id (+)
182 AND POLLA.latest_external_flag (+) = 'Y'
183 AND (
184 (POLLA.line_location_id is NULL)
185 OR (POLL.ship_to_organization_id <> POLLA.ship_to_organization_id)
186 OR (POLL.ship_to_organization_id IS NULL
187 AND POLLA.ship_to_organization_id IS NOT NULL)
188 OR (POLL.ship_to_organization_id IS NOT NULL
189 AND POLLA.ship_to_organization_id IS NULL)
190 )
191 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
192
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 x_shipments_control.ship_to_organization_id := 'N';
196 END;
197 ELSE
198 BEGIN
199 SELECT DISTINCT 'Y'
200 INTO x_shipments_control.ship_to_organization_id
201 FROM PO_LINE_LOCATIONS_ALL POLL,
202 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
203 WHERE
204 POLL.po_release_id = x_po_release_id
205 AND POLL.line_location_id = POLLA.line_location_id (+)
206 AND POLLA.latest_external_flag (+) = 'Y'
207 AND (
208 (POLLA.line_location_id is NULL)
209 OR (POLL.ship_to_organization_id <> POLLA.ship_to_organization_id)
210 OR (POLL.ship_to_organization_id IS NULL
211 AND POLLA.ship_to_organization_id IS NOT NULL)
212 OR (POLL.ship_to_organization_id IS NOT NULL
213 AND POLLA.ship_to_organization_id IS NULL)
214 );
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 x_shipments_control.ship_to_organization_id := 'N';
218 END;
219 END IF;
220
221 -- SQL What: Select 'Y' if Ship to is changed
222 -- SQL Why: Need the value for routing to reapproval
223 -- if there is a change
224 -- SQL Join: line_location_id, ship_to_location_id
225 IF (x_po_header_id IS NOT NULL) THEN
226 BEGIN
227 SELECT DISTINCT 'Y'
228 INTO x_shipments_control.ship_to_location_id
229 FROM PO_LINE_LOCATIONS_ALL POLL,
230 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
231 WHERE
232 POLL.po_header_id = x_po_header_id
233 AND POLL.line_location_id = POLLA.line_location_id (+)
234 AND POLLA.latest_external_flag (+) = 'Y'
235 AND (
236 (POLLA.line_location_id is NULL)
237 OR (POLL.ship_to_location_id <> POLLA.ship_to_location_id)
238 OR (POLL.ship_to_location_id IS NULL
239 AND POLLA.ship_to_location_id IS NOT NULL)
240 OR (POLL.ship_to_location_id IS NOT NULL
241 AND POLLA.ship_to_location_id IS NULL)
242 )
243 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 x_shipments_control.ship_to_location_id := 'N';
247 END;
248 ELSE
249 BEGIN
250 SELECT DISTINCT 'Y'
251 INTO x_shipments_control.ship_to_location_id
252 FROM PO_LINE_LOCATIONS_ALL POLL,
253 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
254 WHERE
255 POLL.po_release_id = x_po_release_id
256 AND POLL.line_location_id = POLLA.line_location_id (+)
257 AND POLLA.latest_external_flag (+) = 'Y'
258 AND (
259 (POLLA.line_location_id is NULL)
260 OR (POLL.ship_to_location_id <> POLLA.ship_to_location_id)
261 OR (POLL.ship_to_location_id IS NULL
262 AND POLLA.ship_to_location_id IS NOT NULL)
263 OR (POLL.ship_to_location_id IS NOT NULL
264 AND POLLA.ship_to_location_id IS NULL)
265 );
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 x_shipments_control.ship_to_location_id := 'N';
269 END;
270 END IF;
271
272 --<R12 Requester Driven Procurement Start>
273
274 -- query to retrieve the change in promised date
275 IF (x_po_header_id IS NOT NULL) THEN
276 BEGIN
277 -- Bug 5123672 Added query to check if date changed
278 -- Sql What : determine if promised date changed
279 SELECT DISTINCT 'Y'
280 INTO x_shipments_control.promised_date
281 FROM PO_LINE_LOCATIONS_ALL POLL,
282 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
283 WHERE
284 POLL.po_header_id = x_po_header_id
285 AND POLL.line_location_id = POLLA.line_location_id (+)
286 AND POLLA.latest_external_flag (+) = 'Y'
287 AND (
288 (POLLA.line_location_id is NULL)
289 OR (POLL.promised_date <> POLLA.promised_date)
290 OR (POLL.promised_date IS NULL
291 AND POLLA.promised_date IS NOT NULL)
292 OR (POLL.promised_date IS NOT NULL
293 AND POLLA.promised_date IS NULL)
294 )
295 AND poll.po_release_id is NULL;
296
297 -- Sql What : retrieve the change in promised date
298 SELECT max(trunc(POLL.promised_date-POLLA.promised_date))
299 INTO x_shipments_control.promised_date_change
300 FROM PO_LINE_LOCATIONS_ALL POLL,
301 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
302 WHERE POLL.po_header_id = x_po_header_id
303 AND POLL.line_location_id = POLLA.line_location_id (+)
304 AND POLLA.latest_external_flag (+) = 'Y'
305 AND poll.po_release_id is NULL
306 AND (POLL.promised_date IS NOT NULL OR POLLA.promised_date IS NOT NULL);
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 x_shipments_control.promised_date_change := 0;
310 END;
311 ELSE
312 BEGIN
313 -- Bug 5123672 Added query to check if date changed
314 -- Sql What : determine if promised date changed
315 SELECT DISTINCT 'Y'
316 INTO x_shipments_control.promised_date
317 FROM PO_LINE_LOCATIONS_ALL POLL,
318 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
319 WHERE
320 POLL.po_release_id = x_po_release_id
321 AND POLL.line_location_id = POLLA.line_location_id (+)
322 AND POLLA.latest_external_flag (+) = 'Y'
323 AND (
324 (POLLA.line_location_id is NULL)
325 OR (POLL.promised_date <> POLLA.promised_date)
326 OR (POLL.promised_date IS NULL
327 AND POLLA.promised_date IS NOT NULL)
328 OR (POLL.promised_date IS NOT NULL
329 AND POLLA.promised_date IS NULL)
330 );
331
332 -- Sql What : retrieve the change in promised date
333 SELECT max(trunc(POLL.promised_date-POLLA.promised_date))
334 INTO x_shipments_control.promised_date_change
335 FROM PO_LINE_LOCATIONS_ALL POLL,
336 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
337 WHERE POLL.po_release_id = x_po_release_id
338 AND POLL.line_location_id = POLLA.line_location_id (+)
339 AND POLLA.latest_external_flag (+) = 'Y'
340 AND (POLL.promised_date IS NOT NULL OR POLLA.promised_date IS NOT NULL);
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 x_shipments_control.promised_date_change := 0;
344 END;
345 END IF;
346
347 -- query to retrieve the change in need by date
348 IF (x_po_header_id IS NOT NULL) THEN
349 BEGIN
350 -- Bug 5123672 Added query to check if date changed
351 -- Sql What : determine if need by date changed
352 SELECT DISTINCT 'Y'
353 INTO x_shipments_control.need_by_date
354 FROM PO_LINE_LOCATIONS_ALL POLL,
355 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
356 WHERE
357 POLL.po_header_id = x_po_header_id
358 AND POLL.line_location_id = POLLA.line_location_id (+)
359 AND POLLA.latest_external_flag (+) = 'Y'
360 AND (
361 (POLLA.line_location_id is NULL)
362 OR (POLL.need_by_date <> POLLA.need_by_date)
363 OR (POLL.need_by_date IS NULL
364 AND POLLA.need_by_date IS NOT NULL)
365 OR (POLL.need_by_date IS NOT NULL
366 AND POLLA.need_by_date IS NULL)
367 )
368 AND poll.po_release_id is NULL;
369
370 -- Sql What : retrieve the change in need by date
374 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
371 SELECT max(trunc(POLL.need_by_date-POLLA.need_by_date))
372 INTO x_shipments_control.need_by_date_change
373 FROM PO_LINE_LOCATIONS_ALL POLL,
375 WHERE POLL.po_header_id = x_po_header_id
376 AND POLL.line_location_id = POLLA.line_location_id (+)
377 AND POLLA.latest_external_flag (+) = 'Y'
378 AND poll.po_release_id is NULL -- Bug 4016493 : Ignore release shipments
379 AND (POLL.need_by_date IS NOT NULL OR POLLA.need_by_date IS NOT NULL);
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 x_shipments_control.need_by_date_change := 0;
383 END;
384 ELSE
385 BEGIN
386 -- Bug 5123672 Added query to check if date changed
387 -- Sql What : determine if need by date changed
388 SELECT DISTINCT 'Y'
389 INTO x_shipments_control.need_by_date
390 FROM PO_LINE_LOCATIONS_ALL POLL,
391 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
392 WHERE
393 POLL.po_release_id = x_po_release_id
394 AND POLL.line_location_id = POLLA.line_location_id (+)
395 AND POLLA.latest_external_flag (+) = 'Y'
396 AND (
397 (POLLA.line_location_id is NULL)
398 OR (POLL.need_by_date <> POLLA.need_by_date)
399 OR (POLL.need_by_date IS NULL
400 AND POLLA.need_by_date IS NOT NULL)
401 OR (POLL.need_by_date IS NOT NULL
402 AND POLLA.need_by_date IS NULL)
403 );
404
405 -- Sql What : retrieve the change in need by date
406 SELECT max(trunc(POLL.need_by_date-POLLA.need_by_date))
407 INTO x_shipments_control.need_by_date_change
408 FROM PO_LINE_LOCATIONS_ALL POLL,
409 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
410 WHERE POLL.po_release_id = x_po_release_id
411 AND POLL.line_location_id = POLLA.line_location_id (+)
412 AND POLLA.latest_external_flag (+) = 'Y'
413 AND (POLL.need_by_date IS NOT NULL OR POLLA.need_by_date IS NOT NULL);
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 x_shipments_control.need_by_date_change := 0;
417 END;
418 END IF;
419
420 --<R12 Requester Driven Procurement End>
421
422 -- SQL What: Select 'Y' if last accepted date is changed
423 -- SQL Why: Need the value for routing to reapproval
424 -- if there is a change
425 -- SQL Join: line_location_id, last_accept_date
426 IF (x_po_header_id IS NOT NULL) THEN
427 BEGIN
428 SELECT DISTINCT 'Y'
429 INTO x_shipments_control.last_accept_date
430 FROM PO_LINE_LOCATIONS_ALL POLL,
431 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
432 WHERE
433 POLL.po_header_id = x_po_header_id
434 AND POLL.line_location_id = POLLA.line_location_id (+)
435 AND POLLA.latest_external_flag (+) = 'Y'
436 AND (
437 (POLLA.line_location_id is NULL)
438 OR (POLL.last_accept_date <> POLLA.last_accept_date)
439 OR (POLL.last_accept_date IS NULL AND POLLA.last_accept_date IS NOT NULL)
440 OR (POLL.last_accept_date IS NOT NULL AND POLLA.last_accept_date IS NULL)
441 )
442 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
443
444 EXCEPTION
445 WHEN NO_DATA_FOUND THEN
446 x_shipments_control.last_accept_date := 'N';
447 END;
448 ELSE
449 BEGIN
450 SELECT DISTINCT 'Y'
451 INTO x_shipments_control.last_accept_date
452 FROM PO_LINE_LOCATIONS_ALL POLL,
453 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
454 WHERE
455 POLL.po_release_id = x_po_release_id
456 AND POLL.line_location_id = POLLA.line_location_id (+)
457 AND POLLA.latest_external_flag (+) = 'Y'
458 AND (
459 (POLLA.line_location_id is NULL)
460 OR (POLL.last_accept_date <> POLLA.last_accept_date)
461 OR (POLL.last_accept_date IS NULL AND POLLA.last_accept_date IS NOT NULL)
462 OR (POLL.last_accept_date IS NOT NULL AND POLLA.last_accept_date IS NULL)
463 );
464
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 x_shipments_control.last_accept_date := 'N';
468 END;
469 END IF;
470
471 -- SQL What: Select 'Y' if taxable flag is changed
472 -- SQL Why: Need the value for routing to reapproval
473 -- if there is a change
474 -- SQL Join: line_location_id, taxable_flag
475 IF (x_po_header_id IS NOT NULL) THEN
476 BEGIN
477 SELECT DISTINCT 'Y'
478 INTO x_shipments_control.taxable_flag
479 FROM PO_LINE_LOCATIONS_ALL POLL,
480 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
481 WHERE
482 POLL.po_header_id = x_po_header_id
483 AND POLL.line_location_id = POLLA.line_location_id (+)
484 AND POLLA.latest_external_flag (+) = 'Y'
485 AND (
489 OR (POLL.taxable_flag IS NOT NULL AND POLLA.taxable_flag IS NULL)
486 (POLLA.line_location_id is NULL)
487 OR (POLL.taxable_flag <> POLLA.taxable_flag)
488 OR (POLL.taxable_flag IS NULL AND POLLA.taxable_flag IS NOT NULL)
490 )
491 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 x_shipments_control.taxable_flag := 'N';
495 END;
496 ELSE
497 BEGIN
498 SELECT DISTINCT 'Y'
499 INTO x_shipments_control.taxable_flag
500 FROM PO_LINE_LOCATIONS_ALL POLL,
501 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
502 WHERE
503 POLL.po_release_id = x_po_release_id
504 AND POLL.line_location_id = POLLA.line_location_id (+)
505 AND POLLA.latest_external_flag (+) = 'Y'
506 AND (
507 (POLLA.line_location_id is NULL)
508 OR (POLL.taxable_flag <> POLLA.taxable_flag)
509 OR (POLL.taxable_flag IS NULL AND POLLA.taxable_flag IS NOT NULL)
510 OR (POLL.taxable_flag IS NOT NULL AND POLLA.taxable_flag IS NULL)
511 );
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514 x_shipments_control.taxable_flag := 'N';
515 END;
516 END IF;
517
518 -- SQL What: Select 'Y' if cancel flag is changed
519 -- SQL Why: Need the value for routing to reapproval
520 -- if there is a change
521 -- SQL Join: line_location_id, cancel_flag
522 IF (x_po_header_id IS NOT NULL) THEN
523 BEGIN
524 SELECT DISTINCT 'Y'
525 INTO x_shipments_control.cancel_flag
526 FROM PO_LINE_LOCATIONS_ALL POLL,
527 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
528 WHERE
529 POLL.po_header_id = x_po_header_id
530 AND POLL.line_location_id = POLLA.line_location_id (+)
531 AND POLLA.latest_external_flag (+) = 'Y'
532 AND (
533 (POLLA.line_location_id is NULL)
534 OR (POLL.cancel_flag <> POLLA.cancel_flag)
535 OR (POLL.cancel_flag IS NULL AND POLLA.cancel_flag IS NOT NULL)
536 OR (POLL.cancel_flag IS NOT NULL AND POLLA.cancel_flag IS NULL)
537 )
538 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 x_shipments_control.cancel_flag := 'N';
542 END;
543 ELSE
544 BEGIN
545 SELECT DISTINCT 'Y'
546 INTO x_shipments_control.cancel_flag
547 FROM PO_LINE_LOCATIONS_ALL POLL,
548 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
549 WHERE
550 POLL.po_release_id = x_po_release_id
551 AND POLL.line_location_id = POLLA.line_location_id (+)
552 AND POLLA.latest_external_flag (+) = 'Y'
553 AND (
554 (POLLA.line_location_id is NULL)
555 OR (POLL.cancel_flag <> POLLA.cancel_flag)
556 OR (POLL.cancel_flag IS NULL AND POLLA.cancel_flag IS NOT NULL)
557 OR (POLL.cancel_flag IS NOT NULL AND POLLA.cancel_flag IS NULL)
558 );
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 x_shipments_control.cancel_flag := 'N';
562 END;
563 END IF;
564
565 -- SQL What: Select 'Y' if closed code is changed
566 -- SQL Why: Need the value for routing to reapproval
567 -- if there is a change
568 -- SQL Join: line_location_id, closed_code
569 IF (x_po_header_id IS NOT NULL) THEN
570 BEGIN
571 SELECT DISTINCT 'Y'
572 INTO x_shipments_control.closed_code
573 FROM PO_LINE_LOCATIONS_ALL POLL,
574 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
575 WHERE
576 POLL.po_header_id = x_po_header_id
577 AND POLL.line_location_id = POLLA.line_location_id (+)
578 AND POLLA.latest_external_flag (+) = 'Y'
579 AND (
580 (POLLA.line_location_id is NULL)
581 OR (POLL.closed_code <> POLLA.closed_code)
582 OR (POLL.closed_code IS NULL AND POLLA.closed_code IS NOT NULL)
583 OR (POLL.closed_code IS NOT NULL AND POLLA.closed_code IS NULL)
584 )
585 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
586
587 EXCEPTION
588 WHEN NO_DATA_FOUND THEN
589 x_shipments_control.closed_code := 'N';
590 END;
591 ELSE
592 BEGIN
593 SELECT DISTINCT 'Y'
594 INTO x_shipments_control.closed_code
595 FROM PO_LINE_LOCATIONS_ALL POLL,
596 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
597 WHERE
598 POLL.po_release_id = x_po_release_id
599 AND POLL.line_location_id = POLLA.line_location_id (+)
600 AND POLLA.latest_external_flag (+) = 'Y'
601 AND (
602 (POLLA.line_location_id is NULL)
603 OR (POLL.closed_code <> POLLA.closed_code)
604 OR (POLL.closed_code IS NULL AND POLLA.closed_code IS NOT NULL)
608 WHEN NO_DATA_FOUND THEN
605 OR (POLL.closed_code IS NOT NULL AND POLLA.closed_code IS NULL)
606 );
607 EXCEPTION
609 x_shipments_control.closed_code := 'N';
610 END;
611 END IF;
612
613 /* <TIMEPHASED FPI START> */
614 --<R12 Requester Driven Procurement Start>
615 -- SQL What: Retrieving the percentage change in start date
616 -- SQL Why: Need the value in tolerance check (i.e reapproval
617 -- rule validations)
618 -- SQL Join: line_location_id
619 IF (x_po_header_id IS NOT NULL) THEN
620 BEGIN
621 SELECT max(trunc(POLL.start_date-POLLA.start_date))
622 INTO x_shipments_control.start_date_change
623 FROM PO_LINE_LOCATIONS_ALL POLL,
624 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
625 WHERE POLL.po_header_id = x_po_header_id
626 AND POLL.line_location_id = POLLA.line_location_id (+)
627 AND POLLA.latest_external_flag (+) = 'Y'
628 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 x_shipments_control.start_date_change := 0;
632 END;
633 ELSE
634 BEGIN
635 SELECT max(trunc(POLL.start_date-POLLA.start_date))
636 INTO x_shipments_control.start_date_change
637 FROM PO_LINE_LOCATIONS_ALL POLL,
638 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
639 WHERE POLL.po_release_id = x_po_release_id
640 AND POLL.line_location_id = POLLA.line_location_id (+)
641 AND POLLA.latest_external_flag (+) = 'Y';
642 EXCEPTION
643 WHEN NO_DATA_FOUND THEN
644 x_shipments_control.start_date_change := 0;
645 END;
646 END IF;
647
648 IF (x_shipments_control.start_date_change > 0) THEN
649 x_shipments_control.start_date := 'Y';
650 ELSE
651 x_shipments_control.start_date := 'N';
652 END IF;
653
654 -- SQL What: Retrieving the percentage change in end date
655 -- SQL Why: Need the value in tolerance check (i.e reapproval
656 -- rule validations)
657 -- SQL Join: line_location_id
658 IF (x_po_header_id IS NOT NULL) THEN
659 BEGIN
660 SELECT max(trunc(POLL.end_date-POLLA.end_date))
661 INTO x_shipments_control.end_date_change
662 FROM PO_LINE_LOCATIONS_ALL POLL,
663 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
664 WHERE POLL.po_header_id = x_po_header_id
665 AND POLL.line_location_id = POLLA.line_location_id (+)
666 AND POLLA.latest_external_flag (+) = 'Y'
667 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 x_shipments_control.end_date_change := 0;
671 END;
672 ELSE
673 BEGIN
674 SELECT max(trunc(POLL.end_date-POLLA.end_date))
675 INTO x_shipments_control.end_date_change
676 FROM PO_LINE_LOCATIONS_ALL POLL,
677 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
678 WHERE POLL.po_release_id = x_po_release_id
679 AND POLL.line_location_id = POLLA.line_location_id (+)
680 AND POLLA.latest_external_flag (+) = 'Y';
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 x_shipments_control.end_date_change := 0;
684 END;
685 END IF;
686
687 if (x_shipments_control.end_date_change > 0) then
688 x_shipments_control.end_date := 'Y';
689 else
690 x_shipments_control.end_date := 'N';
691 end if;
692
693
694 -- <Complex Work R12 Start>
695
696 IF (x_po_header_id IS NOT NULL) THEN
697
698 BEGIN
699
700 SELECT DISTINCT 'Y'
701 INTO x_shipments_control.payment_type
702 FROM PO_LINE_LOCATIONS_ALL POLL,
703 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
704 WHERE POLL.po_header_id = x_po_header_id
705 AND NVL(POLL.payment_type, 'NONE') NOT IN ('DELIVERY', 'ADVANCE')
706 AND POLL.line_location_id = POLLA.line_location_id (+)
707 AND POLLA.latest_external_flag (+) = 'Y'
708 AND (
709 (POLLA.line_location_id is NULL)
710 OR (POLL.payment_type <> POLLA.payment_type)
711 OR (POLL.payment_type IS NULL AND POLLA.payment_type IS NOT NULL)
712 OR (POLL.payment_type IS NOT NULL AND POLLA.payment_type IS NULL)
713 )
714 AND poll.po_release_id is NULL;
715
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 x_shipments_control.payment_type := 'N';
719 END;
720
721 BEGIN
722
723 SELECT DISTINCT 'Y'
724 INTO x_shipments_control.description
725 FROM PO_LINE_LOCATIONS_ALL POLL,
726 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
727 WHERE POLL.po_header_id = x_po_header_id
728 AND NVL(POLL.payment_type, 'NONE') NOT IN ('DELIVERY', 'ADVANCE')
729 AND POLL.line_location_id = POLLA.line_location_id (+)
730 AND POLLA.latest_external_flag (+) = 'Y'
731 AND (
735 OR (POLL.description IS NOT NULL AND POLLA.description IS NULL)
732 (POLLA.line_location_id is NULL)
733 OR (POLL.description <> POLLA.description)
734 OR (POLL.description IS NULL AND POLLA.description IS NOT NULL)
736 )
737 AND poll.po_release_id is NULL;
738
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 x_shipments_control.description := 'N';
742 END;
743
744 BEGIN
745
746 SELECT DISTINCT 'Y'
747 INTO x_shipments_control.work_approver_id
748 FROM PO_LINE_LOCATIONS_ALL POLL,
749 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
750 WHERE POLL.po_header_id = x_po_header_id
751 AND NVL(POLL.payment_type, 'NONE') NOT IN ('DELIVERY', 'ADVANCE')
752 AND POLL.line_location_id = POLLA.line_location_id (+)
753 AND POLLA.latest_external_flag (+) = 'Y'
754 AND (
755 (POLLA.line_location_id is NULL)
756 OR (POLL.work_approver_id <> POLLA.work_approver_id)
757 OR (POLL.work_approver_id IS NULL AND POLLA.work_approver_id IS NOT NULL)
758 OR (POLL.work_approver_id IS NOT NULL AND POLLA.work_approver_id IS NULL)
759 )
760 AND poll.po_release_id is NULL;
761
762 EXCEPTION
763 WHEN NO_DATA_FOUND THEN
764 x_shipments_control.work_approver_id := 'N';
765 END;
766
767 END IF; -- x_po_header_id IS NOT NULL (complex work fields)
768
769 -- <Complex Work R12 End>
770
771
772 --<R12 Requester Driven Procurement End>
773
774 /* <TIMEPHASED FPI END> */
775
776 /* Bug 2808011 START */
777 -- SQL What: Select 'Y' if price override is changed
778 -- SQL Why: Need the value for routing to reapproval
779 -- if there is a change
780 -- SQL Join: line_location_id, price_override
781 IF (x_po_header_id IS NOT NULL) THEN
782 BEGIN
783 SELECT DISTINCT 'Y'
784 INTO x_shipments_control.price_override
785 FROM PO_LINE_LOCATIONS_ALL POLL,
786 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
787 WHERE
788 POLL.po_header_id = x_po_header_id
789 AND NVL(POLL.payment_type, 'NONE') <> 'DELIVERY' -- <Complex Work R12>
790 AND POLL.line_location_id = POLLA.line_location_id (+)
791 AND POLLA.latest_external_flag (+) = 'Y'
792 AND (
793 (POLLA.line_location_id is NULL)
794 OR (POLL.price_override <> POLLA.price_override)
795 OR (POLL.price_override IS NULL AND POLLA.price_override IS NOT NULL)
796 OR (POLL.price_override IS NOT NULL AND POLLA.price_override IS NULL)
797 )
798 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
799
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 x_shipments_control.price_override := 'N';
803 END;
804 ELSE
805 BEGIN
806 SELECT DISTINCT 'Y'
807 INTO x_shipments_control.price_override
808 FROM PO_LINE_LOCATIONS_ALL POLL,
809 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
810 WHERE
811 POLL.po_release_id = x_po_release_id
812 AND POLL.line_location_id = POLLA.line_location_id (+)
813 AND POLLA.latest_external_flag (+) = 'Y'
814 AND (
815 (POLLA.line_location_id is NULL)
816 OR (POLL.price_override <> POLLA.price_override)
817 OR (POLL.price_override IS NULL AND POLLA.price_override IS NOT NULL)
818 OR (POLL.price_override IS NOT NULL AND POLLA.price_override IS NULL)
819 );
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 x_shipments_control.price_override := 'N';
823 END;
824 END IF;
825 /* Bug 2808011 END */
826
827 -- SQL What: Retrieving the percentage change in quantity
828 -- SQL Why: Need the value in tolerance check (i.e reapproval
829 -- rule validations)
830 -- SQL Join: line_location_id
831 IF (x_po_header_id IS NOT NULL) THEN
832 BEGIN
833 SELECT max(po_chord_wf0.percentage_change(
834 POLLA.quantity, POLL.quantity))
835 INTO x_shipments_control.quantity_change
836 FROM PO_LINE_LOCATIONS_ALL POLL,
837 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
838 WHERE POLL.po_header_id = x_po_header_id
839 AND NVL(POLL.payment_type, 'NONE') <> 'DELIVERY' -- <Complex Work R12>
840 AND POLL.line_location_id = POLLA.line_location_id (+)
841 AND POLLA.latest_external_flag (+) = 'Y'
842 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 x_shipments_control.quantity_change := 0;
846 END;
847 ELSE
848 BEGIN
849 SELECT max(po_chord_wf0.percentage_change(
850 POLLA.quantity, POLL.quantity))
851 INTO x_shipments_control.quantity_change
852 FROM PO_LINE_LOCATIONS_ALL POLL,
856 AND POLL.line_location_id = POLLA.line_location_id (+)
853 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
854 WHERE
855 POLL.po_release_id = x_po_release_id
857 AND POLLA.latest_external_flag (+) = 'Y';
858 EXCEPTION
859 WHEN NO_DATA_FOUND THEN
860 x_shipments_control.quantity_change := 0;
861 END;
862 END IF;
863
864 -- SQL What: Retrieving the percentage change in price override
865 -- SQL Why: Need the value in tolerance check (i.e reapproval
866 -- rule validations)
867 -- SQL Join: line_location_id
868 IF (x_po_header_id IS NOT NULL) THEN
869 BEGIN
870 SELECT max(po_chord_wf0.percentage_change(
871 POLLA.price_override, POLL.price_override))
872 INTO x_shipments_control.price_override_change
873 FROM PO_LINE_LOCATIONS_ALL POLL,
874 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
875 WHERE POLL.po_header_id = x_po_header_id
876 AND NVL(POLL.payment_type, 'NONE') <> 'DELIVERY' -- <Complex Work R12>
877 AND POLL.line_location_id = POLLA.line_location_id (+)
878 AND POLLA.latest_external_flag (+) = 'Y'
879 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
880 EXCEPTION
881 WHEN NO_DATA_FOUND THEN
882 x_shipments_control.price_override_change := 0;
883 END;
884 ELSE
885 BEGIN
886 SELECT max(po_chord_wf0.percentage_change(
887 POLLA.price_override, POLL.price_override))
888 INTO x_shipments_control.price_override_change
889 FROM PO_LINE_LOCATIONS_ALL POLL,
890 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
891 WHERE
892 POLL.po_release_id = x_po_release_id
893 AND POLL.line_location_id = POLLA.line_location_id (+)
894 AND POLLA.latest_external_flag (+) = 'Y';
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 x_shipments_control.price_override_change := 0;
898 END;
899 END IF;
900
901 --<R12 Requester Driven Procurement Start>
902 -- SQL What: Retrieving the percentage change in amount
903 -- SQL Why: Need the value in tolerance check (i.e reapproval
904 -- rule validations)
905 -- SQL Join: line_location_id
906 -- Bug 5071741: Amount change is calculated using price and qty for
907 -- qty based lines and amount for services lines and rounded accordingly
908 IF (x_po_header_id IS NOT NULL) THEN
909
910 -- Get the currency code and precision
911 SELECT poh.currency_code
912 INTO l_currency_code
913 FROM po_headers_all poh
914 WHERE poh.po_header_id = x_po_header_id;
915
916 PO_CORE_S2.get_currency_info(
917 x_currency_code => l_currency_code
918 , x_min_unit => l_min_acct_unit
919 , x_precision => l_precision);
920
921 BEGIN
922 IF l_min_acct_unit is not null AND
923 l_min_acct_unit <> 0 THEN
924
925 SELECT max(po_chord_wf0.percentage_change(
926 round(
927 decode(POLLA.value_basis, 'RATE',POLLA.amount,'FIXED PRICE', POLLA.amount,
928 (POLLA.quantity*POLLA.price_override)) / l_min_acct_unit )* l_min_acct_unit ,
929 round(
930 decode(POLL.value_basis, 'RATE',POLL.amount,'FIXED PRICE', POLL.amount,
931 (POLL.quantity*POLL.price_override)) / l_min_acct_unit )* l_min_acct_unit
932 ))
933 INTO x_shipments_control.amount_change
934 FROM PO_LINE_LOCATIONS_ALL POLL,
935 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
936 WHERE POLL.po_header_id = x_po_header_id
937 -- <Complex Work R12>: "line level" info should be ignored.
938 AND NVL(POLL.payment_type, 'NONE') NOT IN ('DELIVERY', 'ADVANCE')
939 AND POLL.line_location_id = POLLA.line_location_id (+)
940 AND POLLA.latest_external_flag (+) = 'Y'
941 AND poll.po_release_id is NULL; -- Bug 4016493 : Ignore release shipments
942
943 ELSE
944
945 SELECT max(po_chord_wf0.percentage_change(
946 round(
947 decode(POLLA.value_basis, 'RATE',POLLA.amount,'FIXED PRICE', POLLA.amount,
948 (POLLA.quantity*POLLA.price_override)) ,l_precision ) ,
949 round(
950 decode(POLL.value_basis, 'RATE',POLL.amount,'FIXED PRICE', POLL.amount,
951 (POLL.quantity*POLL.price_override) ),l_precision )
952 ))
953 INTO x_shipments_control.amount_change
954 FROM PO_LINE_LOCATIONS_ALL POLL,
955 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
956 WHERE POLL.po_header_id = x_po_header_id
957 AND NVL(POLL.payment_type, 'NONE') NOT IN ('DELIVERY', 'ADVANCE')
958 AND POLL.line_location_id = POLLA.line_location_id (+)
959 AND POLLA.latest_external_flag (+) = 'Y'
960 AND poll.po_release_id is NULL;
961
962 END IF;
963
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
969 ELSE -- po_header_id null : release
966 x_shipments_control.amount_change := 0;
967 END;
968
970
971 -- Get the currency code and precision
972 SELECT poh.currency_code
973 INTO l_currency_code
974 FROM po_releases_all por,
975 po_headers_all poh
976 WHERE por.po_release_id = x_po_release_id
977 AND poh.po_header_id = por.po_header_id;
978
979 PO_CORE_S2.get_currency_info(
980 x_currency_code => l_currency_code
981 , x_min_unit => l_min_acct_unit
982 , x_precision => l_precision);
983
984 BEGIN
985 IF l_min_acct_unit is not null AND
986 l_min_acct_unit <> 0 THEN
987
988 SELECT max(po_chord_wf0.percentage_change(
989 round(
990 decode(POLLA.value_basis,'FIXED PRICE', POLLA.amount,
991 (POLLA.quantity*POLLA.price_override)) / l_min_acct_unit )* l_min_acct_unit ,
992 round(
993 decode(POLL.value_basis, 'FIXED PRICE', POLL.amount,
994 (POLL.quantity*POLL.price_override) ) / l_min_acct_unit )* l_min_acct_unit
995 ))
996 INTO x_shipments_control.amount_change
997 FROM PO_LINE_LOCATIONS_ALL POLL,
998 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
999 WHERE POLL.po_release_id = x_po_release_id
1000 AND POLL.line_location_id = POLLA.line_location_id (+)
1001 AND POLLA.latest_external_flag (+) = 'Y';
1002
1003 ELSE
1004
1005 SELECT max(po_chord_wf0.percentage_change(
1006 round(
1007 decode(POLLA.value_basis,'FIXED PRICE', POLLA.amount,
1008 (POLLA.quantity*POLLA.price_override)) ,l_precision ) ,
1009 round(
1010 decode(POLL.value_basis, 'FIXED PRICE', POLL.amount,
1011 (POLL.quantity*POLL.price_override) ),l_precision )
1012 ))
1013 INTO x_shipments_control.amount_change
1014 FROM PO_LINE_LOCATIONS_ALL POLL,
1015 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
1016 WHERE POLL.po_release_id = x_po_release_id
1017 AND POLL.line_location_id = POLLA.line_location_id (+)
1018 AND POLLA.latest_external_flag (+) = 'Y';
1019 END IF;
1020
1021 EXCEPTION
1022 WHEN NO_DATA_FOUND THEN
1023 x_shipments_control.amount_change := 0;
1024 END;
1025
1026 END IF; -- po_header_id not null
1027 --<R12 Requester Driven Procurement End>
1028
1029 -- ECO 5080252 Start
1030 -- SQL What: Select 'Y' if days late receipt allowed flag is changed
1031 -- SQL Why: Need the value for routing to reapproval if there is a change
1032 -- SQL Join: line_location_id, days late receipt allowed
1033 IF (x_po_header_id IS NOT NULL) THEN
1034 BEGIN
1035 SELECT DISTINCT 'Y'
1036 INTO x_shipments_control.days_late_rcpt_allowed
1037 FROM PO_LINE_LOCATIONS_ALL POLL,
1038 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
1039 WHERE
1040 POLL.po_header_id = x_po_header_id
1041 AND POLL.line_location_id = POLLA.line_location_id (+)
1042 AND POLLA.latest_external_flag (+) = 'Y'
1043 AND (
1044 (POLLA.line_location_id is NULL)
1045 OR (POLL.days_late_receipt_allowed <> POLLA.days_late_receipt_allowed)
1046 OR (POLL.days_late_receipt_allowed IS NULL
1047 AND POLLA.days_late_receipt_allowed IS NOT NULL)
1048 OR (POLL.days_late_receipt_allowed IS NOT NULL
1049 AND POLLA.days_late_receipt_allowed IS NULL)
1050 )
1051 AND poll.po_release_id is NULL;
1052 EXCEPTION
1053 WHEN NO_DATA_FOUND THEN
1054 x_shipments_control.days_late_rcpt_allowed := 'N';
1055 END;
1056 ELSE
1057 BEGIN
1058 SELECT DISTINCT 'Y'
1059 INTO x_shipments_control.days_late_rcpt_allowed
1060 FROM PO_LINE_LOCATIONS_ALL POLL,
1061 PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
1062 WHERE
1063 POLL.po_release_id = x_po_release_id
1064 AND POLL.line_location_id = POLLA.line_location_id (+)
1065 AND POLLA.latest_external_flag (+) = 'Y'
1066 AND (
1067 (POLLA.line_location_id is NULL)
1068 OR (POLL.days_late_receipt_allowed <> POLLA.days_late_receipt_allowed)
1069 OR (POLL.days_late_receipt_allowed IS NULL
1070 AND POLLA.days_late_receipt_allowed IS NOT NULL)
1071 OR (POLL.days_late_receipt_allowed IS NOT NULL
1072 AND POLLA.days_late_receipt_allowed IS NULL)
1073 );
1074 EXCEPTION
1075 WHEN NO_DATA_FOUND THEN
1076 x_shipments_control.days_late_rcpt_allowed := 'N';
1077 END;
1078 END IF;
1079 -- ECO 5080252 End
1080
1081 IF (g_po_wf_debug = 'Y') THEN
1082 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1083 '*** Finish: check_shipments_change ***' );
1084 END IF;
1085
1086 EXCEPTION
1090 '*** In procedure: set_wf_shipments_control ***');
1087 WHEN e_invalid_setup THEN
1088 IF (g_po_wf_debug = 'Y') THEN
1089 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1091 END IF;
1092 wf_core.context('POAPPRV', 'set_wf_shipments_control', 'e_invalid_setup');
1093 raise;
1094
1095 WHEN others THEN
1096 IF (g_po_wf_debug = 'Y') THEN
1097 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1098 '*** In procedure: set_wf_shipments_control ***');
1099 END IF;
1100 wf_core.context('POAPPRV', 'set_wf_shipments_control', 'others');
1101 raise;
1102
1103 END;
1104
1105 PROCEDURE set_wf_shipments_control( itemtype IN VARCHAR2,
1106 itemkey IN VARCHAR2,
1107 x_shipments_control IN t_shipments_control_type)
1108 IS
1109 BEGIN
1110 IF (g_po_wf_debug = 'Y') THEN
1111 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1112 '*** In procedure: set_wf_shipments_control ***');
1113 END IF;
1114
1115 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1116 itemkey,
1117 'CO_S_SHIPMENT_NUMBER',
1118 x_shipments_control.shipment_num);
1119
1120
1121 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1122 itemkey,
1123 'CO_S_SHIP_TO_ORGANIZATION',
1124 x_shipments_control.ship_to_organization_id);
1125
1126
1127 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1128 itemkey,
1129 'CO_S_SHIP_TO_LOCATION',
1130 x_shipments_control.ship_to_location_id);
1131
1132 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1133 itemkey,
1134 'CO_S_PROMISED_DATE',
1135 x_shipments_control.promised_date);
1136
1137
1138 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1139 itemkey,
1140 'CO_S_NEED_BY_DATE',
1141 x_shipments_control.need_by_date);
1142
1143
1144 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1145 itemkey,
1146 'CO_S_LAST_ACCEPT_DATE',
1147 x_shipments_control.last_accept_date);
1148
1149 -- ECO 5080252
1150 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1151 itemkey,
1152 'CO_S_DAYS_LATE_RCPT_ALLOWED',
1153 x_shipments_control.days_late_rcpt_allowed);
1154
1155 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1156 itemkey,
1157 'CO_S_TAXABLE_FLAG',
1158 x_shipments_control.taxable_flag);
1159
1160
1161 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1162 itemkey,
1163 'CO_S_PRICE_DISCOUNT',
1164 x_shipments_control.price_discount);
1165
1166
1167 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1168 itemkey,
1169 'CO_S_CANCEL_FLAG',
1170 x_shipments_control.cancel_flag);
1171
1172
1173 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1174 itemkey,
1175 'CO_S_CLOSED_CODE',
1176 x_shipments_control.closed_code);
1177
1178
1179 -- <Complex Work R12 Start>
1180
1181 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1182 itemkey,
1183 'CO_S_PAYMENT_TYPE',
1184 x_shipments_control.payment_type);
1185
1186 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1187 itemkey,
1188 'CO_S_WORK_APPROVER_ID',
1189 x_shipments_control.work_approver_id);
1190
1191 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1192 itemkey,
1193 'CO_S_DESCRIPTION',
1194 x_shipments_control.description);
1195
1196 -- <Complex Work R12 End>
1197
1201 x_shipments_control.quantity_change);
1198 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype,
1199 itemkey,
1200 'CO_S_QUANTITY_CHANGE',
1202
1203 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype,
1204 itemkey,
1205 'CO_S_PRICE_OVERRIDE_CHANGE',
1206 x_shipments_control.price_override_change);
1207
1208 /* <TIMEPHASED FPI START> */
1209 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1210 itemkey,
1211 'CO_S_START_DATE',
1212 x_shipments_control.start_date);
1213
1214 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1215 itemkey,
1216 'CO_S_END_DATE',
1217 x_shipments_control.end_date);
1218 /* <TIMEPHASED FPI END> */
1219
1220 /* Bug 2808011 START */
1221 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1222 itemkey,
1223 'CO_S_PRICE_OVERRIDE',
1224 x_shipments_control.price_override);
1225 /* Bug 2808011 END */
1226
1227 --<R12 Requester Driven Procurement Start>
1228
1229 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1230 itemkey,
1231 'CO_S_AMOUNT_CHANGE',
1232 x_shipments_control.amount_change);
1233
1234 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1235 itemkey,
1236 'CO_S_START_DATE_CHANGE',
1237 x_shipments_control.start_date_change);
1238
1239 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1240 itemkey,
1241 'CO_S_END_DATE_CHANGE',
1242 x_shipments_control.end_date_change);
1243
1244 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1245 itemkey,
1246 'CO_S_NEED_BY_DATE_DATE_CHANGE',
1247 x_shipments_control.need_by_date_change);
1248
1249 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1250 itemkey,
1251 'CO_S_PROMISED_DATE_DATE_CHANGE',
1252 x_shipments_control.promised_date_change);
1253
1254 --<R12 Requester Driven Procurement End>
1255
1256 IF (g_po_wf_debug = 'Y') THEN
1257 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1258 '*** Finish: set_wf_shipments_control ***');
1259 END IF;
1260
1261 END;
1262
1263
1264 PROCEDURE get_wf_shipments_control( itemtype IN VARCHAR2,
1265 itemkey IN VARCHAR2,
1266 x_shipments_control IN OUT NOCOPY t_shipments_control_type)
1267 IS
1268 BEGIN
1269 IF (g_po_wf_debug = 'Y') THEN
1270 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1271 '*** In procedure: get_wf_shipments_control ***');
1272 END IF;
1273
1274 x_shipments_control.shipment_num :=
1275 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1276 itemkey,
1277 'CO_S_SHIPMENT_NUMBER');
1278
1279 x_shipments_control.ship_to_organization_id :=
1280 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1281 itemkey,
1282 'CO_S_SHIP_TO_ORGANIZATION');
1283
1284 x_shipments_control.ship_to_location_id :=
1285 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1286 itemkey,
1287 'CO_S_SHIP_TO_LOCATION');
1288
1289 x_shipments_control.promised_date :=
1290 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1291 itemkey,
1292 'CO_S_PROMISED_DATE');
1293
1294 x_shipments_control.need_by_date :=
1295 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1296 itemkey,
1297 'CO_S_NEED_BY_DATE');
1298
1299 x_shipments_control.last_accept_date:=
1300 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1301 itemkey,
1302 'CO_S_LAST_ACCEPT_DATE');
1303
1304 -- ECO 5080252
1305 x_shipments_control.days_late_rcpt_allowed :=
1306 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1307 itemkey,
1308 'CO_S_DAYS_LATE_RCPT_ALLOWED');
1309
1310 x_shipments_control.taxable_flag :=
1311 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1312 itemkey,
1313 'CO_S_TAXABLE_FLAG');
1314
1315 x_shipments_control.price_discount :=
1316 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1317 itemkey,
1318 'CO_S_PRICE_DISCOUNT');
1319
1320 x_shipments_control.cancel_flag :=
1321 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1322 itemkey,
1323 'CO_S_CANCEL_FLAG');
1324
1325 x_shipments_control.closed_code :=
1326 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1327 itemkey,
1328 'CO_S_CLOSED_CODE');
1329
1330 x_shipments_control.quantity_change :=
1331 PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype,
1332 itemkey,
1333 'CO_S_QUANTITY_CHANGE');
1334
1335 x_shipments_control.price_override_change :=
1336 PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype,
1337 itemkey,
1338 'CO_S_PRICE_OVERRIDE_CHANGE');
1339
1340 /* <TIMEPHASED FPI START> */
1341 x_shipments_control.start_date :=
1342 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1343 itemkey,
1344 'CO_S_START_DATE');
1345
1346 x_shipments_control.end_date :=
1347 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1348 itemkey,
1349 'CO_S_END_DATE');
1350 /* <TIMEPHASED FPI END> */
1351
1352 /* Bug 2808011 START */
1353 x_shipments_control.price_override :=
1354 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1355 itemkey,
1356 'CO_S_PRICE_OVERRIDE');
1357 /* Bug 2808011 END */
1358
1359
1363 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_S_PAYMENT_TYPE');
1360 -- <Complex Work R12 Start>
1361
1362 x_shipments_control.payment_type :=
1364
1365 x_shipments_control.work_approver_id :=
1366 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_S_WORK_APPROVER_ID');
1367
1368 x_shipments_control.description :=
1369 PO_WF_UTIL_PKG.GetItemAttrText(itemtype, itemkey, 'CO_S_DESCRIPTION');
1370
1371 -- <Complex Work R12 End>
1372
1373
1374 --<R12 Requester Driven Procurement Start>
1375
1376 x_shipments_control.amount_change :=
1377 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1378 itemkey,
1379 'CO_S_AMOUNT_CHANGE');
1380
1381 x_shipments_control.start_date_change :=
1382 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1383 itemkey,
1384 'CO_S_START_DATE_CHANGE');
1385
1386 x_shipments_control.end_date_change :=
1387 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1388 itemkey,
1389 'CO_S_END_DATE_CHANGE');
1390
1391 x_shipments_control.need_by_date_change :=
1392 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1393 itemkey,
1394 'CO_S_NEED_BY_DATE_DATE_CHANGE');
1395
1396 x_shipments_control.promised_date_change :=
1397 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1398 itemkey,
1399 'CO_S_PROMISED_DATE_DATE_CHANGE');
1400
1401 --<R12 Requester Driven Procurement End>
1402
1403 debug_shipments_control(itemtype, itemkey, x_shipments_control);
1404
1405 IF (g_po_wf_debug = 'Y') THEN
1406 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1407 '*** Finish: get_wf_shipments_control ***');
1408 END IF;
1409
1410 END;
1411
1412 PROCEDURE get_wf_shipments_parameters(itemtype IN VARCHAR2,
1413 itemkey IN VARCHAR2,
1414 x_shipments_parameters IN OUT NOCOPY t_shipments_parameters_type)
1415 IS
1416 e_invalid_doc_type EXCEPTION;
1417 x_doc_type VARCHAR2(25);
1418 BEGIN
1419 IF (g_po_wf_debug = 'Y') THEN
1420 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1421 '*** In procedure get_wf_shipments_parameters ***');
1422 END IF;
1423
1424 x_doc_type := PO_WF_UTIL_PKG.GetItemAttrText (itemtype,
1425 itemkey,
1426 'DOCUMENT_TYPE');
1427
1428 IF x_doc_type IN ('PO', 'PA') THEN
1429
1430 x_shipments_parameters.po_header_id :=
1431 PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype,
1432 itemkey,
1433 'DOCUMENT_ID');
1434
1435 x_shipments_parameters.po_release_id:=NULL;
1436
1437 ELSIF x_doc_type = 'RELEASE' THEN
1438
1439 x_shipments_parameters.po_release_id :=
1440 PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype,
1441 itemkey,
1442 'DOCUMENT_ID');
1443
1444 x_shipments_parameters.po_header_id:=NULL;
1445
1446 ELSE
1447 raise e_invalid_doc_type;
1448
1449 END IF;
1450
1451 IF (g_po_wf_debug = 'Y') THEN
1452 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1453 'po_header_id = ' || to_char(x_shipments_parameters.po_header_id));
1454 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1455 'po_release_id = '|| to_char(x_shipments_parameters.po_release_id));
1456 END IF;
1457
1458 IF (g_po_wf_debug = 'Y') THEN
1459 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1460 '*** FINISH: get_wf_shipments_parameters ***');
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN e_invalid_doc_type THEN
1465 IF (g_po_wf_debug = 'Y') THEN
1466 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1467 '***set_wf_shipments_control exception e_invalid_setup *** ');
1468 END IF;
1469 wf_core.context('POAPPRV', 'set_wf_shipments_control', 'e_invalid_setup');
1470 raise;
1471
1472 WHEN OTHERS THEN
1473 IF (g_po_wf_debug = 'Y') THEN
1474 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1475 '*** set_wf_shipments_control exception others ***');
1476 END IF;
1477 wf_core.context('POAPPRV', 'set_wf_shipments_control', 'others');
1478 raise;
1479
1480 END;
1481
1482
1483 PROCEDURE debug_shipments_control(
1484 itemtype IN VARCHAR2,
1485 itemkey IN VARCHAR2,
1486 x_shipments_control IN t_shipments_control_type)
1487 IS
1488 BEGIN
1489 IF (g_po_wf_debug = 'Y') THEN
1490 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1491 '*** In procedure: debug_shipments_control ***');
1492 END IF;
1493
1494 IF (g_po_wf_debug = 'Y') THEN
1495 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1496 'shipment_num : '||x_shipments_control.shipment_num);
1497 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1498 'ship_to_organization_id : '||x_shipments_control.ship_to_organization_id);
1499 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1500 'ship_to_location_id : '||x_shipments_control.ship_to_location_id);
1501 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1502 'promised_date : '||x_shipments_control.promised_date);
1503 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1504 'need_by_date : '||x_shipments_control.need_by_date);
1505 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1506 'last_accept_date : '||x_shipments_control.last_accept_date);
1507 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1508 'taxable_flag : '||x_shipments_control.taxable_flag);
1509 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1510 'price_discount : '||x_shipments_control.price_discount);
1511 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1512 'cancel_flag : '||x_shipments_control.cancel_flag );
1513 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1514 'closed_code : '||x_shipments_control.closed_code );
1515 -- <Complex Work R12 Start>
1516 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1517 'payment_type : '||x_shipments_control.payment_type );
1518 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1519 'work_approver_id : '||x_shipments_control.work_approver_id );
1520 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1521 'description : '||x_shipments_control.description );
1522 -- <Complex Work R12 End>
1523 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1524 'quantity_change : '||to_char(x_shipments_control.quantity_change));
1525 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1526 'price_override_change : '||to_char(x_shipments_control.price_override_change));
1527 END IF;
1528
1529 /* <TIMEPHASED FPI START> */
1530 IF (g_po_wf_debug = 'Y') THEN
1531 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1532 'start_date : '||x_shipments_control.start_date );
1533 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1534 'end_date : '||x_shipments_control.end_date );
1535 /* Bug 2808011 */
1536 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1537 'price_override : '||x_shipments_control.price_override);
1538 END IF;
1539 /* <TIMEPHASED FPI END> */
1540
1541
1542 --<R12 Requester Driven Procurement Start>
1543 IF (g_po_wf_debug = 'Y') THEN
1544 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1545 'amount_change : '||x_shipments_control.amount_change );
1546 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1547 'start_date_change : '||x_shipments_control.start_date_change );
1548 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1549 'end_date_change : '||x_shipments_control.end_date_change );
1550 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1551 'need_by_date_change : '||x_shipments_control.need_by_date_change );
1552 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1553 'promised_date_change : '||x_shipments_control.promised_date_change );
1554
1555 END IF;
1556 --<R12 Requester Driven Procurement End>
1557
1558 IF (g_po_wf_debug = 'Y') THEN
1559 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1560 '*** Finished: debug_shipments_control ***');
1561 END IF;
1562 END;
1563
1564 END PO_CHORD_WF3;
[Home] [Help]