[Home] [Help]
PACKAGE BODY: APPS.PO_CHORD_WF5
Source
1 PACKAGE BODY PO_CHORD_WF5 AS
2 /* $Header: POXWCO5B.pls 115.4 2002/11/25 22:36:01 sbull 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_release(itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 FUNCMODE IN VARCHAR2,
11 RESULT OUT NOCOPY VARCHAR2)
12
13 IS
14 x_release_control t_release_control_type;
15 x_release_parameters t_release_parameters_type;
16 BEGIN
17 IF (g_po_wf_debug = 'Y') THEN
18 PO_WF_DEBUG_PKG.insert_debug(ITEMTYPE, ITEMKEY,
19 '*** In Procedure: chord_release ***' );
20 END IF;
21
22 If funcmode <> 'RUN' THEN
23 result := 'COMPLETE';
24 return;
25 END IF;
26
27 get_wf_release_parameters(itemtype, itemkey, x_release_parameters);
28
29 check_release_change(itemtype, itemkey, x_release_parameters, x_release_control);
30
31 set_wf_release_control(itemtype, itemkey, x_release_control);
32
33 IF (g_po_wf_debug = 'Y') THEN
34 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
35 '*** Finish: chord_release ***' );
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_wf5.chord_release', 'others');
45 RAISE;
46
47 END;
48
49 PROCEDURE check_release_change(
50 itemtype IN VARCHAR2,
51 itemkey IN VARCHAR2,
52 x_release_parameters IN t_release_parameters_type,
53 x_release_control IN OUT NOCOPY t_release_control_type)
54 IS
55 x_po_release_id NUMBER;
56 BEGIN
57 IF (g_po_wf_debug = 'Y') THEN
58 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
59 '*** In Procedure check_release_change ***');
60 END IF;
61
62 x_release_control.agent_id :='N';
63 x_release_control.acceptance_required_flag :='N';
64 x_release_control.acceptance_due_date :='N';
65 x_release_control.release_num :='N';
66 x_release_control.release_date :='N';
67
68 x_po_release_id := x_release_parameters.po_release_id;
69
70 BEGIN
71 SELECT DISTINCT 'Y'
72 INTO x_release_control.agent_id
73 FROM PO_RELEASES_all POR,
74 PO_RELEASES_ARCHIVE_all PORA
75 WHERE POR.po_release_id = x_po_release_id
76 AND POR.po_release_id = PORA.po_release_id
77 AND PORA.latest_external_flag (+) = 'Y'
78 AND (
79 (PORA.po_release_id IS NULL)
80 OR (POR.agent_id <> PORA.agent_id)
81 OR (POR.agent_id IS NULL
82 AND PORA.agent_id IS NOT NULL)
83 OR (POR.agent_id IS NOT NULL
84 AND PORA.agent_id IS NULL)
85 );
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 x_release_control.agent_id :='N';
89 END;
90
91
92 BEGIN
93 SELECT DISTINCT 'Y'
94 INTO x_release_control.acceptance_required_flag
95 FROM PO_RELEASES_all POR,
96 PO_RELEASES_ARCHIVE_all PORA
97 WHERE POR.po_release_id = x_po_release_id
98 AND POR.po_release_id = PORA.po_release_id
99 AND PORA.latest_external_flag (+) = 'Y'
100 AND (
101 (PORA.po_release_id IS NULL)
102 OR (POR.acceptance_required_flag <> PORA.acceptance_required_flag)
103 OR (POR.acceptance_required_flag IS NULL
104 AND PORA.acceptance_required_flag IS NOT NULL)
105 OR (POR.acceptance_required_flag IS NOT NULL
106 AND PORA.acceptance_required_flag IS NULL)
107 );
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 x_release_control.acceptance_required_flag :='N';
111 END;
112
113
114 BEGIN
115 SELECT DISTINCT 'Y'
116 INTO x_release_control.acceptance_due_date
117 FROM PO_RELEASES_all POR,
118 PO_RELEASES_ARCHIVE_all PORA
119 WHERE POR.po_release_id = x_po_release_id
120 AND POR.po_release_id = PORA.po_release_id
121 AND PORA.latest_external_flag (+) = 'Y'
122 AND (
123 (PORA.po_release_id IS NULL)
124 OR (POR.acceptance_due_date <> PORA.acceptance_due_date)
125 OR (POR.acceptance_due_date IS NULL
126 AND PORA.acceptance_due_date IS NOT NULL)
127 OR (POR.acceptance_due_date IS NOT NULL
128 AND PORA.acceptance_due_date IS NULL)
129 );
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 x_release_control.acceptance_due_date :='N';
133 END;
134
135
136 BEGIN
137 SELECT DISTINCT 'Y'
138 INTO x_release_control.release_num
139 FROM PO_RELEASES_all POR,
140 PO_RELEASES_ARCHIVE_all PORA
141 WHERE POR.po_release_id = x_po_release_id
142 AND POR.po_release_id = PORA.po_release_id
143 AND PORA.latest_external_flag (+) = 'Y'
144 AND (
145 (PORA.po_release_id IS NULL)
146 OR (POR.release_num <> PORA.release_num)
147 OR (POR.release_num IS NULL
148 AND PORA.release_num IS NOT NULL)
149 OR (POR.release_num IS NOT NULL
150 AND PORA.release_num IS NULL)
151 );
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 x_release_control.release_num :='N';
155 END;
156
157
158 BEGIN
159 SELECT DISTINCT 'Y'
160 INTO x_release_control.release_date
161 FROM PO_RELEASES_all POR,
162 PO_RELEASES_ARCHIVE_all PORA
163 WHERE POR.po_release_id = x_po_release_id
164 AND POR.po_release_id = PORA.po_release_id
165 AND PORA.latest_external_flag (+) = 'Y'
166 AND (
167 (PORA.po_release_id IS NULL)
168 OR (POR.release_date <> PORA.release_date)
169 OR (POR.release_date IS NULL
170 AND PORA.release_date IS NOT NULL)
171 OR (POR.release_date IS NOT NULL
172 AND PORA.release_date IS NULL)
173 );
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 x_release_control.release_date :='N';
177 END;
178
179
180 x_release_control.release_total_change:=release_total_change(x_po_release_id);
181
182
183 IF (g_po_wf_debug = 'Y') THEN
184 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
185 '*** Finish check_release_change ***');
186 END IF;
187 EXCEPTION
188
189 WHEN OTHERS THEN
190 wf_core.context('POAPPRV', 'po_chord_wf5.chord_release', 'others');
191 RAISE;
192
193 END;
194
195 FUNCTION release_total_change(x_po_release_id IN NUMBER) return NUMBER
196 IS
197 x_po_header_id NUMBER;
198 x_release_total NUMBER;
199 x_release_total_archive NUMBER;
200 x_base_currency VARCHAR2(16);
201 x_po_currency VARCHAR2(16);
202 x_min_unit NUMBER;
203 x_base_min_unit NUMBER;
204 x_precision INTEGER;
205 x_base_precision INTEGER;
206 x_total_change NUMBER;
207 BEGIN
208
209 SELECT po_header_id
210 into x_po_header_id
211 FROM po_releases_all PR
212 WHERE PR.po_release_id = x_po_release_id;
213
214 po_core_s2.get_po_currency (x_po_header_id,
215 x_base_currency,
216 x_po_currency );
217
218 IF x_base_currency <> x_po_currency THEN
219
220 po_core_s2.get_currency_info (x_po_currency,
221 x_precision,
222 x_min_unit );
223
224 po_core_s2.get_currency_info (x_base_currency,
225 x_base_precision,
226 x_base_min_unit );
227
228
229 Begin
230 SELECT nvl(round( round(sum((nvl(POD.quantity_ordered, 0) -
231 nvl(POD.quantity_cancelled, 0)) *
232 nvl(POLL.price_override, 0) * nvl(POD.rate,1) /
233 nvl(X_min_unit,1))) * nvl(X_min_unit,1) /
234 nvl(X_base_min_unit,1))
235 * nvl(X_base_min_unit,1) , 0)
236 INTO x_release_total
237 FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL POLL
238 WHERE POLL.po_release_id = x_po_release_id
239 AND POLL.shipment_type in ('SCHEDULED','BLANKET')
240 AND POLL.line_location_id = POD.line_location_id;
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 x_release_total := 0;
245
246 End;
247 /* Bug: 1851833
248 ** Desc: Modified the where claues of SQL below to
249 ** avoid Full Table Scan and use po_header_id index
250 */
251
252 Begin
253 SELECT nvl(round( round(sum((nvl(PODA.quantity_ordered, 0) -
254 nvl(PODA.quantity_cancelled, 0)) *
255 nvl(POLLA.price_override, 0) * nvl(PODA.rate,1) /
256 nvl(X_min_unit,1))) * nvl(X_min_unit,1) /
257 nvl(X_base_min_unit,1))
258 * nvl(X_base_min_unit,1) , 0)
259 INTO x_release_total_archive
260 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA,
261 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
262 WHERE PODA.po_header_id = (SELECT po_header_id
263 FROM po_releases
264 WHERE po_release_id = x_po_release_id)
265 AND POLLA.po_header_id = PODA.po_header_id
266 AND POLLA.po_release_id = x_po_release_id
267 AND POLLA.latest_external_flag (+) = 'Y'
268 AND PODA.latest_external_flag (+) = 'Y'
269 AND POLLA.shipment_type in ('SCHEDULED','BLANKET')
270 AND POLLA.line_location_id = PODA.line_location_id;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 x_release_total_archive :=0;
274
275 End;
276
277 ELSE /* base currency = po_currrency */
278
279 Begin
280 SELECT sum(
281 (nvl(POD.quantity_ordered, 0) -
282 nvl(POD.quantity_cancelled, 0)) *
283 nvl(POLL.price_override, 0))
284 INTO x_release_total
285 FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL POLL
286 WHERE POLL.po_release_id = x_po_release_id
287 AND POLL.shipment_type in ('SCHEDULED','BLANKET')
288 AND POLL.line_location_id = POD.line_location_id;
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 x_release_total :=0;
292
293 End;
294
295 Begin
296 SELECT sum(
297 (nvl(PODA.quantity_ordered, 0) -
298 nvl(PODA.quantity_cancelled, 0)) *
299 nvl(POLLA.price_override, 0))
300 INTO x_release_total_archive
301 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA,
302 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
303 WHERE PODA.po_header_id = (SELECT po_header_id
304 FROM po_releases
305 WHERE po_release_id = x_po_release_id)
306 AND POLLA.po_header_id = PODA.po_header_id
307 AND POLLA.po_release_id = x_po_release_id
308 AND POLLA.latest_external_flag (+) = 'Y'
309 AND PODA.latest_external_flag (+) = 'Y'
310 AND POLLA.shipment_type in ('SCHEDULED','BLANKET')
311 AND POLLA.line_location_id = PODA.line_location_id;
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 x_release_total_archive :=0;
315
316 End;
317
318 END IF;
319
320 x_total_change := PO_CHORD_WF0.percentage_change(x_release_total_archive,
321 x_release_total);
322
323 return(round(x_total_change,2));
324
325 EXCEPTION
326
327 WHEN OTHERS THEN
328 wf_core.context('POAPPRV', 'po_total_change', 'others');
329
330 END;
331
332
333
334
335 PROCEDURE set_wf_release_control(itemtype IN VARCHAR2,
336 itemkey IN VARCHAR2,
337 x_release_control IN t_release_control_type)
338 IS
339 BEGIN
340 IF (g_po_wf_debug = 'Y') THEN
341 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** In procedure set_wf_release_control ***');
342 END IF;
343
344 wf_engine.SetItemAttrText(itemtype,
345 itemkey,
346 'CO_R_AGENT',
347 x_release_control.agent_id);
348
349 wf_engine.SetItemAttrText(itemtype,
350 itemkey,
351 'CO_R_ACCEPTANCE_REQUIRED',
352 x_release_control.acceptance_required_flag);
353
354 wf_engine.SetItemAttrText(itemtype,
355 itemkey,
356 'CO_R_ACCEPTANCE_DUE_DATE',
357 x_release_control.acceptance_due_date);
358
359 wf_engine.SetItemAttrText(itemtype,
360 itemkey,
361 'CO_R_RELEASE_NUM',
362 x_release_control.release_num);
363
364 wf_engine.SetItemAttrText(itemtype,
365 itemkey,
366 'CO_R_RELEASE_DATE',
367 x_release_control.release_date);
368
369 wf_engine.SetItemAttrNumber(itemtype,
370 itemkey,
371 'CO_R_TOTAL_CHANGE',
372 x_release_control.release_total_change);
373
374 debug_release_control(itemtype, itemkey, x_release_control);
375
376 IF (g_po_wf_debug = 'Y') THEN
377 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
378 '*** Finish set_wf_release_control ***');
379 END IF;
380
381
382 EXCEPTION
383
384 WHEN OTHERS THEN
385 wf_core.context('POAPPRV', 'po_chord_wf5.set_wf_release_control', 'others');
386 RAISE;
387 END;
388
389 PROCEDURE get_wf_release_control(itemtype IN VARCHAR2,
390 itemkey IN VARCHAR2,
391 x_release_control IN OUT NOCOPY t_release_control_type)
392 IS
393 BEGIN
394 IF (g_po_wf_debug = 'Y') THEN
395 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
396 '*** In procedure get_wf_release_control ***');
397 END IF;
398
399 x_release_control.agent_id :=
400 wf_engine.GetItemAttrText(itemtype,
401 itemkey,
402 'CO_R_AGENT');
403
404 x_release_control.acceptance_required_flag :=
405 wf_engine.GetItemAttrText(itemtype,
406 itemkey,
407 'CO_R_ACCEPTANCE_REQUIRED');
408
409 x_release_control.acceptance_due_date :=
410 wf_engine.GetItemAttrText(itemtype,
411 itemkey,
412 'CO_R_ACCEPTANCE_DUE_DATE');
413
414 x_release_control.release_num :=
415 wf_engine.GetItemAttrText(itemtype,
416 itemkey,
417 'CO_R_RELEASE_NUM');
418
419 x_release_control.release_date :=
420 wf_engine.GetItemAttrText(itemtype,
421 itemkey,
422 'CO_R_RELEASE_DATE');
423
424 x_release_control.release_total_change :=
425 wf_engine.GetItemAttrNumber(itemtype,
426 itemkey,
427 'CO_R_TOTAL_CHANGE');
428
429
430
431 debug_release_control(itemtype, itemkey, x_release_control);
432
433 IF (g_po_wf_debug = 'Y') THEN
434 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
435 '*** FINISH: get_wf_release_control ***');
436 END IF;
437 EXCEPTION
438
439 WHEN OTHERS THEN
440 wf_core.context('POAPPRV', 'po_chord_wf5.get_wf_release_control', 'others');
441 RAISE;
442 END;
443
444
445 PROCEDURE debug_release_control(
446 itemtype IN VARCHAR2,
447 itemkey IN VARCHAR2,
448 x_release_control IN t_release_control_type)
449 IS
450 BEGIN
451 IF (g_po_wf_debug = 'Y') THEN
452 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
453 '*** In procedure: debug_release_control ***');
454 END IF;
455
456 IF (g_po_wf_debug = 'Y') THEN
457 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
458 'agent_id : ' ||x_release_control.agent_id);
459 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
460 'acceptance_required_flag : ' ||x_release_control.acceptance_required_flag);
461 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
462 'acceptance_due_date : ' ||x_release_control.acceptance_due_date);
463 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
464 'release_num : ' ||x_release_control.release_num);
465 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
466 'release_date : ' ||x_release_control.release_date);
467 END IF;
468
469 IF (g_po_wf_debug = 'Y') THEN
470 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
471 '*** Finished: debug_release_control ***');
472 END IF;
473 EXCEPTION
474
475 WHEN OTHERS THEN
476 wf_core.context('POAPPRV', 'po_chord_wf5.get_wf_release_control', 'others');
477 RAISE;
478 END;
479
480 PROCEDURE get_wf_release_parameters(itemtype IN VARCHAR2,
481 itemkey IN VARCHAR2,
482 x_release_parameters IN OUT NOCOPY t_release_parameters_type)
483 IS
484 BEGIN
485 IF (g_po_wf_debug = 'Y') THEN
486 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
487 '*** In procedure get_wf_release_parameters ***');
488 END IF;
489
490 x_release_parameters.po_release_id :=
491 wf_engine.GetItemAttrNumber(itemtype,
492 itemkey,
493 'DOCUMENT_ID');
494
495 IF (g_po_wf_debug = 'Y') THEN
496 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
497 'po_release_id = '|| to_char(x_release_parameters.po_release_id));
498 END IF;
499
500 IF (g_po_wf_debug = 'Y') THEN
501 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
502 '*** FINISH: get_wf_release_parameters ***');
503 END IF;
504
505 EXCEPTION
506
507 WHEN OTHERS THEN
508 wf_core.context('POAPPRV', 'po_chord_wf5.chord_release', 'others');
509 RAISE;
510 END;
511
512 END PO_CHORD_WF5;