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