[Home] [Help]
PACKAGE BODY: APPS.POS_SCO_TOLERANCE_PVT
Source
1 PACKAGE BODY POS_SCO_TOLERANCE_PVT AS
2 /* $Header: POSPTOLB.pls 120.51 2006/10/03 07:31:48 svadlama noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'POS_SCO_TOLERANCE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POSPTOLB.pls';
6 g_module_prefix CONSTANT VARCHAR2(100) := 'pos.plsql.' || 'POS_SCO_TOLERANCE_PVT' || '.';
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
10
11 PROCEDURE LOG_MESSAGE( p_proc_name IN VARCHAR2,
12 p_text IN VARCHAR2,
13 p_log_data IN VARCHAR2)
14
15 IS
16 BEGIN
17
18 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
19 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
20 'POS_SCO_TOLERANCE_PVT',
21 p_proc_name || ': '
22 || p_text || ': '
23 || p_log_data);
24 END IF;
25 END LOG_MESSAGE;
26
27 /* This procedure call the ip API PO_CO_TOLERANCES_GRP.GET_TOLERANCES and sets the Tolerance
28 Attributes and Routing Attributes.
29 */
30 PROCEDURE INITIALIZE_TOL_VALUES( itemtype IN VARCHAR2,
31 itemkey IN VARCHAR2,
32 actid IN NUMBER,
33 funcmode IN VARCHAR2,
34 resultout OUT NOCOPY VARCHAR2)
35 IS
36
37 CURSOR getDocType(p_change_request_grp_id_csr IN NUMBER) IS
38 SELECT DISTINCT document_type
39 FROM po_change_requests
40 WHERE change_request_group_id = p_change_request_grp_id_csr;
41
42 CURSOR getDocSubType(p_po_header_id_csr IN NUMBER) IS
43 SELECT type_lookup_code
44 FROM po_headers_all poha
45 WHERE poha.po_header_id = p_po_header_id_csr;
46
47 CURSOR getDocSubTypeRel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
48 SELECT distinct(release_type)
49 FROM po_releases_all pora
50 WHERE pora.po_header_id = p_po_header_id_csr
51 AND pora.po_release_id = p_po_release_id_csr;
52
53 CURSOR getOrgId(p_po_header_id_csr IN NUMBER) IS
54 SELECT org_id
55 FROM po_headers_all
56 WHERE po_header_id = p_po_header_id_csr;
57
58 l_chg_req_grp_id po_change_requests.change_request_group_id%TYPE;
59 l_po_header_id po_headers_all.po_header_id%TYPE;
60 l_po_release_id po_releases_all.po_release_id%TYPE;
61 l_doc_type po_change_requests.document_type%TYPE;
62 l_po_style varchar2(10);
63 l_doc_subtype varchar2(10);
64 l_promise_date_incr NUMBER;
65 l_promise_date_decr NUMBER;
66 l_unit_price_incr NUMBER;
67 l_unit_price_decr NUMBER;
68 l_shipment_qty_incr NUMBER;
69 l_shipment_qty_decr NUMBER;
70 l_pay_item_qty_incr NUMBER;
71 l_pay_item_qty_decr NUMBER;
72 l_doc_amount_incr_val NUMBER;
73 l_doc_amount_decr_val NUMBER;
74 l_doc_amount_incr_per NUMBER;
75 l_doc_amount_decr_per NUMBER;
76 l_line_amount_incr_per NUMBER;
77 l_line_amount_decr_per NUMBER;
78 l_line_amount_incr_val NUMBER;
79 l_line_amount_decr_val NUMBER;
80 l_ship_amount_incr_val NUMBER;
81 l_ship_amount_decr_val NUMBER;
82 l_ship_amount_incr_per NUMBER;
83 l_ship_amount_decr_per NUMBER;
84 l_pay_item_amount_incr_per NUMBER;
85 l_pay_item_amount_decr_per NUMBER;
86 l_pay_item_amount_incr_val NUMBER;
87 l_pay_item_amount_decr_val NUMBER;
88 l_prm_date_approval_flag VARCHAR2(10);
89 l_ship_qty_approval_flag VARCHAR2(10);
90 l_price_approval_flag VARCHAR2(10);
91 l_complex_po_style VARCHAR2(10);
92 l_org_id NUMBER;
93 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
94 x_return_status varchar2(1);
95 x_msg_count NUMBER;
96 x_msg_data VARCHAR2(2000);
97 x_progress VARCHAR2(1000);
98 BEGIN
99
100 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
101 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
102 g_module_prefix,
103 'Enter Initialize Tol Proc'
104 );
105 END IF;
106
107 x_progress := 'INIT_TOL_VALUES:000';
108
109 l_chg_req_grp_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
110 itemkey => itemkey,
111 aname => 'CHANGE_REQUEST_GROUP_ID');
112
113 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'PO_HEADER_ID');
116
117 l_po_release_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
118 itemkey => itemkey,
119 aname => 'PO_RELEASE_ID');
120
121 x_progress := 'INIT_TOL_VALUES:001';
122
123 -- Getting the Doc types and subtypes coz the flow is valid for Standard PO's and Blanket Releases
124
125 OPEN getDocType(l_chg_req_grp_id);
126 LOOP
127 FETCH getDocType
128 INTO l_doc_type;
129 EXIT WHEN getDocType%NOTFOUND;
130 END LOOP;
131
132 IF getDocType%ISOPEN THEN
133 CLOSE getDocType;
134 END IF;
135
136 IF(l_doc_type = 'PO') THEN
137 OPEN getDocSubType(l_po_header_id);
138 LOOP
139 FETCH getDocSubType
140 INTO l_doc_subtype;
141 EXIT WHEN getDocSubType%NOTFOUND;
142 END LOOP;
143
144 IF getDocSubType%ISOPEN THEN
145 CLOSE getDocSubType;
146 END IF;
147
148 ELSIF(l_doc_type = 'RELEASE') THEN
149 OPEN getDocSubTypeRel(l_po_header_id,l_po_release_id);
150 LOOP
151 FETCH getDocSubTypeRel
152 INTO l_doc_subtype;
153 EXIT WHEN getDocSubTypeRel%NOTFOUND;
154 END LOOP;
155
156 IF getDocSubTypeRel%ISOPEN THEN
157 CLOSE getDocSubTypeRel;
158 END IF;
159 END IF;
160
161
162
163 -- get the org id and set the item attribute value
164 OPEN getOrgId(l_po_header_id);
165 LOOP
166 FETCH getOrgId
167 INTO l_org_id;
168 EXIT WHEN getOrgId%NOTFOUND;
169 END LOOP;
170
171 IF getOrgId%ISOPEN THEN
172 CLOSE getOrgId;
173 END IF;
174
175
176 x_progress := 'INIT_TOL_VALUES:002';
177
178
179
180 wf_engine.SetItemAttrText (itemtype => itemtype,
181 itemkey => itemkey,
182 aname => 'DOCUMENT_TYPE',
183 avalue => l_doc_type);
184
185 wf_engine.SetItemAttrText (itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'DOC_SUB_TYPE',
188 avalue => l_doc_subtype);
189
190 log_message('INITIALIZE_TOL_VALUES','Operating Unit',l_org_id);
191
192 x_progress := 'INIT_TOL_VALUES:003: Call get_tolerances';
193
194
195 PO_CO_TOLERANCES_GRP.GET_TOLERANCES (1.0,
196 FND_API.G_TRUE,
197 l_org_id,
198 PO_CO_TOLERANCES_GRP.G_SUPP_CHG_APP,
199 x_tol_tab,
200 x_return_status,
201 x_msg_count,
202 x_msg_data);
203
204 IF x_return_status IS NOT NULL AND x_return_status = FND_API.g_ret_sts_success THEN
205 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
207 g_module_prefix,
208 x_progress
209 || 'x_return_status=' || x_return_status);
210 END IF;
211
212 ELSE
213 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
214 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
215 g_module_prefix,
216 x_progress
217 ||'x_return_status = ' || x_return_status
218 ||'x_msg_count = ' || x_msg_count
219 ||'x_msg_data = ' || x_msg_data);
220 END IF;
221 END IF;
222
223
224 x_progress := 'INIT_TOL_VALUES:004';
225
226 -- loop through all the tolerances retrieved
227 FOR i in 1..x_tol_tab.count
228 LOOP
229 IF (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE) THEN
230 l_promise_date_incr := x_tol_tab(i).max_increment;
231 l_promise_date_decr := x_tol_tab(i).max_decrement;
232
233 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_UNIT_PRICE) THEN
234 l_unit_price_incr := x_tol_tab(i).max_increment;
235 l_unit_price_decr := x_tol_tab(i).max_decrement;
236
237 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY) THEN
238 l_shipment_qty_incr := x_tol_tab(i).max_increment;
239 l_shipment_qty_decr := x_tol_tab(i).max_decrement;
240
241 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_QTY) THEN
242 l_pay_item_qty_incr := x_tol_tab(i).max_increment;
243 l_pay_item_qty_decr := x_tol_tab(i).max_decrement;
244
245 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DOCUMENT_AMOUNT_VALUE) THEN
246 l_doc_amount_incr_val := x_tol_tab(i).max_increment;
247 l_doc_amount_decr_val := x_tol_tab(i).max_decrement;
248
249 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DOCUMENT_AMOUNT_PERCENT) THEN
250 l_doc_amount_incr_per := x_tol_tab(i).max_increment;
251 l_doc_amount_decr_per := x_tol_tab(i).max_decrement;
252
253 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_PERCENT) THEN
254 l_line_amount_incr_per := x_tol_tab(i).max_increment;
255 l_line_amount_decr_per := x_tol_tab(i).max_decrement;
256
257 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_VALUE) THEN
258 l_line_amount_incr_val := x_tol_tab(i).max_increment;
259 l_line_amount_decr_val := x_tol_tab(i).max_decrement;
260
261 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_VALUE) THEN
262 l_ship_amount_incr_val := x_tol_tab(i).max_increment;
263 l_ship_amount_decr_val := x_tol_tab(i).max_decrement;
264
265 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_AMOUNT_VALUE) THEN
266 l_pay_item_amount_incr_val := x_tol_tab(i).max_increment;
267 l_pay_item_amount_decr_val := x_tol_tab(i).max_decrement;
268
269 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_PERCENT) THEN
270 l_ship_amount_incr_per := x_tol_tab(i).max_increment;
271 l_ship_amount_decr_per := x_tol_tab(i).max_decrement;
272
273 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_AMOUNT_PERCENT) THEN
274 l_pay_item_amount_incr_per := x_tol_tab(i).max_increment;
275 l_pay_item_amount_decr_per := x_tol_tab(i).max_decrement;
276
277
278 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE_APPROVAL_FLAG) THEN
279 l_prm_date_approval_flag := x_tol_tab(i).enabled_flag;
280
281 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY_APPROVAL_FLAG) THEN
282 l_ship_qty_approval_flag := x_tol_tab(i).enabled_flag;
283
284 ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PRICE_APPROVAL_FLAG) THEN
285 l_price_approval_flag := x_tol_tab(i).enabled_flag;
286
287 END IF;
288 END LOOP;
289
290
291 x_progress := 'INIT_TOL_VALUES:005';
292
293
294 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
295 itemkey => itemkey,
296 aname => 'PROMISE_DATE_INCR',
297 avalue => l_promise_date_incr);
298
299 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
300 itemkey => itemkey,
301 aname => 'PROMISE_DATE_DEC',
302 avalue => l_promise_date_decr);
303
304 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
305 itemkey => itemkey,
306 aname => 'UNIT_PRICE_INCR',
307 avalue => l_unit_price_incr);
308
309 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
310 itemkey => itemkey,
311 aname => 'UNIT_PRICE_DEC',
312 avalue => l_unit_price_decr);
313
314 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'DOC_AMOUNT_INCR_PER',
317 avalue => l_doc_amount_incr_per);
318
319 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
320 itemkey => itemkey,
321 aname => 'DOC_AMOUNT_DEC_PER',
322 avalue => l_doc_amount_decr_per);
323
324 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
325 itemkey => itemkey,
326 aname => 'DOC_AMOUNT_INCR_VAL',
327 avalue => l_doc_amount_incr_val);
328
329 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
330 itemkey => itemkey,
331 aname => 'DOC_AMOUNT_DEC_VAL',
332 avalue => l_doc_amount_decr_val);
333
334 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
335 itemkey => itemkey,
336 aname => 'LINE_AMOUNT_INCR_PER',
337 avalue => l_line_amount_incr_per);
338
339 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
340 itemkey => itemkey,
341 aname => 'LINE_AMOUNT_DEC_PER',
342 avalue => l_line_amount_decr_per);
343
344 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
345 itemkey => itemkey,
346 aname => 'LINE_AMOUNT_INCR_VAL',
347 avalue => l_line_amount_incr_val);
348
349 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'LINE_AMOUNT_DEC_VAL',
352 avalue => l_line_amount_decr_val);
353
354 wf_engine.SetItemAttrText (itemtype => itemtype,
355 itemkey => itemkey,
356 aname => 'PROMISE_DATE_APP_FLAG',
357 avalue => l_prm_date_approval_flag);
358
359 wf_engine.SetItemAttrText (itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'SHIP_QTY_APPROVAL_FLAG',
362 avalue => l_ship_qty_approval_flag);
363
364 wf_engine.SetItemAttrText (itemtype => itemtype,
365 itemkey => itemkey,
366 aname => 'PRICE_APPROVAL_FLAG',
367 avalue => l_price_approval_flag);
368
369
370 -- Get the PO Style ( COMPLEX or NORMAL and accordingly populating the Tolerance Attributes
371 l_po_style := wf_engine.GetItemAttrText ( itemtype => itemtype,
372 itemkey => itemkey,
373 aname => 'PO_STYLE_TYPE');
374
375 log_message('INITIALIZE_TOL_VALUES','PO Style Type',l_po_style);
376
377
378 IF (l_po_style ='COMPLEX') THEN
379
380 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
381 itemkey => itemkey,
382 aname => 'PAY_QUANTITY_INCR',
383 avalue => l_pay_item_qty_incr);
384
385 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
386 itemkey => itemkey,
387 aname => 'PAY_QUANTITY_DEC',
388 avalue => l_pay_item_qty_decr);
389
390 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'PAY_AMOUNT_INCR_PER',
393 avalue => l_pay_item_amount_incr_per);
394
395 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'PAY_AMOUNT_DEC_PER',
398 avalue => l_pay_item_amount_decr_per);
399
400 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
401 itemkey => itemkey,
402 aname => 'PAY_AMOUNT_INCR_VAL',
403 avalue => l_pay_item_amount_incr_val);
404
405 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
406 itemkey => itemkey,
407 aname => 'PAY_AMOUNT_DEC_VAL',
408 avalue => l_pay_item_amount_decr_val);
409
410 IF (PO_COMPLEX_WORK_PVT.is_financing_po(l_po_header_id)) THEN
411 l_complex_po_style := 'FINANCING';
412
413 wf_engine.SetItemAttrText (itemtype => itemtype,
414 itemkey => itemkey,
415 aname => 'COMPLEX_PO_STYLE',
416 avalue => l_complex_po_style);
417 ELSE
418 l_complex_po_style := 'ACTUALS';
419
420 wf_engine.SetItemAttrText (itemtype => itemtype,
421 itemkey => itemkey,
422 aname => 'COMPLEX_PO_STYLE',
423 avalue => l_complex_po_style);
424 END IF;
425
426
427
428 ELSIF (l_po_style ='NORMAL') THEN
429
430 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
431 itemkey => itemkey,
432 aname => 'SHIP_QUANTITY_INCR',
433 avalue => l_shipment_qty_incr);
434
435 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
436 itemkey => itemkey,
437 aname => 'SHIP_QUANTITY_DEC',
438 avalue => l_shipment_qty_decr);
439
440 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
441 itemkey => itemkey,
442 aname => 'SHIP_AMOUNT_INCR_PER',
443 avalue => l_ship_amount_incr_per);
444
445 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'SHIP_AMOUNT_DEC_PER',
448 avalue => l_ship_amount_decr_per);
449
450 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
451 itemkey => itemkey,
452 aname => 'SHIP_AMOUNT_INCR_VAL',
453 avalue => l_ship_amount_incr_val);
454
455 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
456 itemkey => itemkey,
457 aname => 'SHIP_AMOUNT_DEC_VAL',
458 avalue => l_ship_amount_decr_val);
459
460
461
462
463 END IF;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
468 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
469 g_module_prefix,
470 x_progress || ':unexpected error' || Sqlerrm);
471 END IF;
472 wf_core.context('POSCHORD', 'INITIALIZE_TOL_VALUES', itemtype, itemkey, to_char(actid),funcmode);
473 raise;
474
475 END INITIALIZE_TOL_VALUES;
476
477
478 /* This procedure checks whether Promise_Date_Change is within the tolerance or not
479 Returns 'Y' if within the tolerance
480 Returns 'N' if out of tolerance
481 */
482
483 PROCEDURE PROMISE_DATE_WITHIN_TOL( itemtype IN VARCHAR2,
484 itemkey IN VARCHAR2,
485 actid IN NUMBER,
486 funcmode IN VARCHAR2,
487 resultout OUT NOCOPY VARCHAR2)
488
489 IS
490
491 -- Cursor to pick up old_promised_date and new_promised_date, handles the case when either of them is null
492 CURSOR c_promise_date_changes(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) IS
493 SELECT nvl(pcr.old_promised_date,pcr.old_need_by_date) old_promise_date,
494 pcr.new_promised_date, pll.promised_date,pll.need_by_date
495 FROM po_change_requests pcr,
496 po_line_locations_all pll
497 WHERE pcr.document_header_id=p_po_header_id_csr
498 AND pcr.document_line_location_id = pll.line_location_id
499 AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
500 AND pcr.request_level = 'SHIPMENT'
501 AND pcr.action_type = 'MODIFICATION'
502 AND pcr.request_status = 'PENDING'
503 AND pcr.initiator='SUPPLIER'
504 AND ( (pcr.new_promised_date <> old_promised_date) OR
505 (nvl(pcr.old_promised_date,nvl(pcr.old_need_by_date,pcr.new_promised_date - 1))<>pcr.new_promised_date)
506 );
507
508 l_po_header_id po_headers_all.po_header_id%type;
509 l_change_group_id po_change_requests.change_request_group_id%type;
510 l_prom_date_dec NUMBER;
511 l_prom_date_incr NUMBER;
512 l_old_promise_date po_change_requests.old_promised_date%type;
513 l_new_promise_date po_change_requests.new_promised_date%type;
514 l_promised_date po_line_locations_all.promised_date%type;
515 l_need_by_date po_line_locations_all.need_by_date%type;
516 x_progress VARCHAR2(1000);
517 l_return_val VARCHAR2(1):='Y';
518 l_po_style_type VARCHAR2(10);
519 l_doc_type VARCHAR2(10);
520
521 BEGIN
522
523 IF ( funcmode = 'RUN' ) THEN
524
525 x_progress := 'PROMISE_DATE_WITHIN_TOL:000';
526
527 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
528 itemkey => itemkey,
529 aname => 'CHANGE_REQUEST_GROUP_ID');
530
531 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'PO_HEADER_ID');
534
535 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
536 itemkey => itemkey,
537 aname => 'PO_STYLE_TYPE');
538
539 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
540 itemkey => itemkey,
541 aname => 'DOCUMENT_TYPE');
542
543 x_progress := 'PROMISE_DATE_WITHIN_TOL:001';
544
545 IF (l_change_group_id IS NOT NULL) THEN
546
547 -- check only for doc type SPO AND BPA Release (get the value from item attribute DOCUMENT_TYPE)
548 -- DOC_SUB_TYPE is already checked in business rules check
549
550 IF( (l_doc_type = 'PO') OR (l_doc_type = 'RELEASE')) THEN
551 -- get the promise date tolerances in days
552 l_prom_date_dec := wf_engine.GetItemAttrNumber (itemtype => itemtype,
553 itemkey => itemkey,
554 aname => 'PROMISE_DATE_DEC');
555 l_prom_date_incr := wf_engine.GetItemAttrNumber (itemtype => itemtype,
556 itemkey => itemkey,
557 aname => 'PROMISE_DATE_INCR');
558
559 log_message('PROMISE_DATE_WITHIN_TOL','Promise Date Incr and decr Values',l_prom_date_incr || ', '|| l_prom_date_dec);
560
561 x_progress := 'PROMISE_DATE_WITHIN_TOL:002';
562
563 OPEN c_promise_date_changes(l_po_header_id,l_change_group_id) ;
564 LOOP
565 FETCH c_promise_date_changes
566 INTO l_old_promise_date,
567 l_new_promise_date,
568 l_promised_date,
569 l_need_by_date;
570 EXIT WHEN c_promise_date_changes%NOTFOUND;
571
572 x_progress := 'PROMISE_DATE_WITHIN_TOL:003';
573 log_message('PROMISE_DATE_WITHIN_TOL','Old & New Promise Date',l_old_promise_date || ', '|| l_new_promise_date);
574
575 IF (l_promised_date is null AND l_need_by_date is null) THEN
576 CLOSE c_promise_date_changes;
577 l_return_val :='N' ;
578 END IF;
579
580
581 EXIT WHEN (l_return_val = 'N');
582
583 IF (NOT change_within_tol_date(l_old_promise_date, l_new_promise_date, l_prom_date_incr, l_prom_date_dec)) THEN
584 l_return_val := 'N';
585 END IF;
586
587 x_progress := 'PROMISE_DATE_WITHIN_TOL:004';
588 END LOOP;
589
590 IF c_promise_date_changes%ISOPEN THEN
591 CLOSE c_promise_date_changes;
592 END IF;
593
594 END IF; -- DOC_TYPE check
595 END IF; --l_change_group_id IS NOT NULL
596
597 -- set result value
598 resultout := wf_engine.eng_completed || ':' || l_return_val ;
599
600 x_progress := 'PROMISE_DATE_WITHIN_TOL:005';
601
602 log_message('PROMISE_DATE_WITHIN_TOL','Result',resultout);
603
604 END IF; -- IF ( funcmode = 'RUN' )
605
606
607 EXCEPTION
608 WHEN OTHERS THEN
609 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
611 g_module_prefix,
612 x_progress || ':unexpected error' || Sqlerrm);
613 END IF;
614 wf_core.context('POSCHORD', 'PROMISE_DATE_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
615 raise;
616 END PROMISE_DATE_WITHIN_TOL;
617
618
619 /* This procedure checks whether Unit_Price_Change is within the tolerance or not
620 Returns 'Y' if within the tolerance
621 Returns 'N' if out of tolerance
622 */
623 PROCEDURE UNIT_PRICE_WITHIN_TOL( itemtype IN VARCHAR2,
624 itemkey IN VARCHAR2,
625 actid IN NUMBER,
626 funcmode IN VARCHAR2,
627 resultout OUT NOCOPY VARCHAR2)
628
629 IS
630 -- This cursor picks up Unit Price chnages for SPO at Line Level
631 CURSOR c_unit_price_changes ( p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) IS
632 SELECT old_price,new_price
633 FROM po_change_requests
634 WHERE document_header_id=p_po_header_id_csr
635 AND CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
636 AND request_level = 'LINE'
637 AND new_price IS NOT NULL
638 AND action_type = 'MODIFICATION'
639 AND request_status = 'PENDING'
640 AND initiator='SUPPLIER';
641
642 -- This cursor picks up unit price changes for BPA release at the shipment level
643 -- pcr.old_price is added to consider price breaks for release
644 CURSOR c_ship_unit_price_rel (p_po_release_id_csr IN NUMBER,p_po_header_id IN NUMBER,p_change_group_id_csr IN NUMBER) IS
645 SELECT plla.price_override,nvl(pcr.new_price,pcr.old_price)
646 FROM po_change_requests pcr,
647 po_line_locations_all plla
648 WHERE pcr.po_release_id= p_po_release_id_csr
649 AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
650 AND pcr.request_level = 'SHIPMENT'
651 AND pcr.new_price IS NOT NULL
652 AND pcr.action_type = 'MODIFICATION'
653 AND pcr.request_status = 'PENDING'
654 AND pcr.initiator='SUPPLIER'
655 AND pcr.document_line_location_id = plla.line_location_id;
656
657 -- cursor to check for the COMPLEX WORK (Financing Case)
658 CURSOR c_line_unit_price_cw (p_po_release_id_csr IN NUMBER,p_po_header_id IN NUMBER,p_change_group_id_csr IN NUMBER) IS
659 SELECT pl.unit_price,pcr.new_price
660 FROM po_change_requests pcr,
661 po_lines_all pl
662 WHERE pcr.document_header_id= p_po_header_id
663 AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
664 AND pcr.request_level = 'LINE'
665 AND pcr.new_price IS NOT NULL
666 AND pcr.action_type = 'MODIFICATION'
667 AND pcr.request_status = 'PENDING'
668 AND pcr.initiator='SUPPLIER'
669 AND pcr.document_line_id = pl.po_line_id;
670
671
672 -- cursor to check for the Complex Work( Actuals Case)
673
674
675 l_po_header_id po_headers_all.po_header_id%TYPE;
676 l_po_release_id po_releases_all.po_release_id%TYPE;
677 l_change_group_id po_change_requests.change_request_group_id%type;
678 l_unitprice_lower_tol number;
679 l_unitprice_upper_tol number;
680 l_old_price po_change_requests.old_price%type;
681 l_new_price po_change_requests.new_price%type;
682 x_progress VARCHAR2(1000);
683 l_return_val VARCHAR2(1):='Y';
684 l_po_style_type VARCHAR2(10);
685 l_doc_type VARCHAR2(10);
686 l_complex_po_style VARCHAR2(10);
687
688 BEGIN
689
690 IF ( funcmode = 'RUN' ) THEN
691
692 x_progress := 'UNIT_PRICE_WITHIN_TOL:000';
693
694 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'CHANGE_REQUEST_GROUP_ID');
697
698 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
699 itemkey => itemkey,
700 aname => 'PO_HEADER_ID');
701
702 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'PO_RELEASE_ID');
705
706 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
707 itemkey => itemkey,
708 aname => 'PO_STYLE_TYPE');
709
710 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'DOCUMENT_TYPE');
713
714 l_complex_po_style := wf_engine.GetItemAttrText (itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'COMPLEX_PO_STYLE');
717
718 x_progress := 'UNIT_PRICE_WITHIN_TOL:001';
719 IF (l_change_group_id IS NOT NULL) THEN
720 -- check for the DOC types (applicable for PO unit price( Line level) and BPA release unit price(Shipment Level)
721 -- if other doc types return true and exit
722 IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
723 -- get the unit price percentage tolerances
724 l_unitprice_lower_tol := wf_engine.GetItemAttrNumber (itemtype => itemtype,
725 itemkey => itemkey,
726 aname => 'UNIT_PRICE_DEC');
727
728 l_unitprice_upper_tol := wf_engine.GetItemAttrNumber (itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'UNIT_PRICE_INCR');
731 x_progress := 'UNIT_PRICE_WITHIN_TOL:002';
732 log_message('UNIT_PRICE_WITHIN_TOL','Unit Price Incr & Decr Values',l_unitprice_upper_tol || ', '|| l_unitprice_lower_tol);
733 IF(l_doc_type = 'PO' and l_po_style_type='NORMAL' ) THEN
734 OPEN c_unit_price_changes(l_po_header_id,l_change_group_id);
735 LOOP
736 FETCH c_unit_price_changes
737 INTO l_old_price,
738 l_new_price;
739 x_progress := 'UNIT_PRICE_WITHIN_TOL:003';
740 log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
741 EXIT WHEN c_unit_price_changes%NOTFOUND;
742 EXIT WHEN (l_return_val = 'N');
743
744 IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
745 l_return_val := 'N';
746 END IF;
747 x_progress := 'UNIT_PRICE_WITHIN_TOL:004';
748 END LOOP;
749 CLOSE c_unit_price_changes;
750 ELSIF(l_doc_type = 'RELEASE' and l_po_style_type='NORMAL') THEN
751 OPEN c_ship_unit_price_rel(l_po_release_id,l_po_header_id,l_change_group_id);
752 LOOP
753 FETCH c_ship_unit_price_rel
754 INTO l_old_price,
755 l_new_price;
756 x_progress := 'UNIT_PRICE_WITHIN_TOL:005';
757 log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
758 EXIT WHEN c_ship_unit_price_rel%NOTFOUND;
759 EXIT WHEN (l_return_val = 'N');
760
761 IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
762 l_return_val := 'N';
763 END IF;
764 x_progress := 'UNIT_PRICE_WITHIN_TOL:006';
765 END LOOP;
766 CLOSE c_ship_unit_price_rel;
767 END IF; -- PO Or RELEASE
768
769
770 IF(l_po_style_type='COMPLEX') THEN
771 IF(l_complex_po_style = 'FINANCING') THEN
772 OPEN c_line_unit_price_cw(l_po_release_id,l_po_header_id,l_change_group_id);
773 LOOP
774 FETCH c_line_unit_price_cw
775 INTO l_old_price,
776 l_new_price;
777 x_progress := 'UNIT_PRICE_WITHIN_TOL:007';
778 log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
779 EXIT WHEN c_line_unit_price_cw%NOTFOUND;
780 EXIT WHEN (l_return_val = 'N');
781 IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
782 l_return_val := 'N';
783 END IF;
784 x_progress := 'UNIT_PRICE_WITHIN_TOL:008';
785 END LOOP;
786 CLOSE c_line_unit_price_cw;
787 END IF; -- financing
788 END IF; -- po_style_type='COMPLEX'
789
790 END IF; -- doc_type PO or RELEASE
791
792 END IF; -- change_group_id is not null
793
794 -- set result value
795
796 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
797 x_progress := 'UNIT_PRICE_WITHIN_TOL:009';
798 log_message('UNIT_PRICE_WITHIN_TOL','Result',resultout);
799
800 END IF; -- IF ( funcmode = 'RUN' )
801
802 EXCEPTION
803 WHEN OTHERS THEN
804
805 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
807 g_module_prefix,
808 x_progress || ':unexpected error' || Sqlerrm);
809 END IF;
810
811
812 wf_core.context('POSCHORD', 'UNIT_PRICE_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
813
814 raise;
815
816 END UNIT_PRICE_WITHIN_TOL;
817
818
819 /* This procedure checks whether Shipment_Quantity_Change is within the tolerance or not
820 Returns 'Y' if within the tolerance
821 Returns 'N' if out of tolerance
822 */
823
824 PROCEDURE SHIP_QUANTITY_WITHIN_TOL( itemtype IN VARCHAR2,
825 itemkey IN VARCHAR2,
826 actid IN NUMBER,
827 funcmode IN VARCHAR2,
828 resultout OUT NOCOPY VARCHAR2)
829 IS
830 -- This cursor picks up shipment quantity changes for SPO and BPA releases
831 CURSOR c_ship_qty_changes (p_change_group_id_csr IN NUMBER) IS
832 SELECT pcr.old_quantity,
833 pcr.new_quantity
834 FROM po_change_requests pcr
835 WHERE pcr.change_request_group_id=p_change_group_id_csr
836 AND pcr.new_quantity IS NOT NULL
837 AND pcr.action_type='MODIFICATION'
838 AND pcr.request_status= 'PENDING'
839 AND pcr.request_level= 'SHIPMENT'
840 AND pcr.initiator= 'SUPPLIER';
841
842 l_old_ship_qty po_change_requests.old_quantity%TYPE;
843 l_new_ship_qty po_change_requests.new_quantity%TYPE;
844 l_return_val VARCHAR2(1) :='Y';
845 l_ship_qty_max_incr_per NUMBER;
846 l_shipq_ty_max_dec_per NUMBER;
847 l_ship_qty_max_incr_val NUMBER;
848 l_ship_qty_max_dec_val NUMBER;
849 x_progress VARCHAR2(1000);
850 l_po_header_id po_headers_all.po_header_id%TYPE;
851 l_change_group_id po_change_requests.change_request_group_id%type;
852 l_po_style_type VARCHAR2(10);
853 l_doc_type VARCHAR2(10);
854
855 BEGIN
856
857 IF ( funcmode = 'RUN' ) THEN
858
859 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:000';
860 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'PO_HEADER_ID');
863
864 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'CHANGE_REQUEST_GROUP_ID');
867
868 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
869 itemkey => itemkey,
870 aname => 'PO_STYLE_TYPE');
871
872 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
873 itemkey => itemkey,
874 aname => 'DOCUMENT_TYPE');
875 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:001';
876 IF (l_change_group_id IS NOT NULL) THEN
877 -- get shipment quantity tolerances
878 -- check for the DOC types (applicable for PO and BPA release)
879 -- if other doc types return true and exit
880 IF (l_po_style_type='NORMAL') THEN
881
882 l_ship_qty_max_incr_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
883 itemkey => itemkey,
884 aname => 'SHIP_QUANTITY_INCR');
885
886 l_shipq_ty_max_dec_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
887 itemkey => itemkey,
888 aname => 'SHIP_QUANTITY_DEC');
889 ELSIF (l_po_style_type='COMPLEX') THEN
890
891 l_ship_qty_max_incr_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
892 itemkey => itemkey,
893 aname => 'PAY_QUANTITY_INCR');
894
895 l_shipq_ty_max_dec_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
896 itemkey => itemkey,
897 aname => 'PAY_QUANTITY_DEC');
898 END IF;
899 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:002';
900 log_message('SHIP_QUANTITY_WITHIN_TOL','Ship Quantity Max Incr & dec Values', l_ship_qty_max_incr_per || ', ' || l_shipq_ty_max_dec_per);
901 OPEN c_ship_qty_changes (l_change_group_id);
902
903 LOOP
904 FETCH c_ship_qty_changes
905 INTO l_old_ship_qty,
906 l_new_ship_qty;
907 EXIT WHEN c_ship_qty_changes%NOTFOUND;
908 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:003';
909 log_message('SHIP_QUANTITY_WITHIN_TOL','Old & New Ship Quantity values',l_old_ship_qty || ', ' ||l_new_ship_qty);
910 EXIT WHEN (l_return_val = 'N');
911
912 IF (NOT change_within_tol(l_old_ship_qty, l_new_ship_qty, l_ship_qty_max_incr_per, l_shipq_ty_max_dec_per,0,0)) THEN
913 l_return_val := 'N';
914 END IF;
915 x_progress:= 'SHIP_QUANTITY_WITHIN_TOL:004';
916 END LOOP;
917 CLOSE c_ship_qty_changes;
918 END IF; -- change group Id is not null
919 -- set result value
920 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
921 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:005';
922 log_message('SHIP_QUANTITY_WITHIN_TOL','Result',resultout);
923
924 END IF; -- IF ( funcmode = 'RUN' )
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
930 g_module_prefix,
931 x_progress || ':unexpected error' || Sqlerrm);
932 END IF;
933 wf_core.context('POSCHORD', 'SHIP_QUANTITY_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
934 raise;
935 END SHIP_QUANTITY_WITHIN_TOL;
936
937
938 /* This procedure checks whether Document Amount Change is within the tolerance or not
939 Any Line Price change or shipment quantity chnage that affects the amount is also
940 taken in to consideration
941 Returns 'Y' if within the tolerance
942 Returns 'N' if out of tolerance
943 */
944
945
946 PROCEDURE DOC_AMOUNT_WITHIN_TOL( itemtype IN VARCHAR2,
947 itemkey IN VARCHAR2,
948 actid IN NUMBER,
949 funcmode IN VARCHAR2,
950 resultout OUT NOCOPY VARCHAR2)
951 IS
952
953 -- Picks up Old Amount for SPO
954 CURSOR c_old_doc_amt_changes(p_po_header_id_csr IN NUMBER) IS
955 SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pl.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
956 FROM po_lines_all pl,
957 po_line_locations_all pll
958 WHERE pl.po_header_id = p_po_header_id_csr
959 AND pll.po_line_id = pl.po_line_id;
960 -- Picks up Old Amount For Releases
961 CURSOR c_old_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
962 SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
963 FROM po_lines_all pl,
964 po_line_locations_all pll
965 WHERE pll.po_release_id = p_po_release_id_csr
966 AND pll.po_header_id = p_po_header_id_csr
967 AND pll.po_line_id = pl.po_line_id;
968
969 -- Picks up Old Amount for Complex POs ( Actuals case )
970 CURSOR c_old_doc_amt_cw_actuals(p_po_header_id_csr IN NUMBER) IS
971 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
972 (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
973 'AMOUNT',
974 DECODE(pll.payment_type, 'LUMPSUM',
975 (pll.amount - NVL(pll.amount_cancelled,0)),
976 'MILESTONE',
977 (pll.amount - NVL(pll.amount_cancelled,0)),
978 'RATE',
979 (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override))))
980 FROM po_lines_all pl,
981 po_line_locations_all pll
982 WHERE pl.po_header_id = p_po_header_id_csr
983 AND pll.po_line_id = pl.po_line_id;
984
985
986 -- Picks up Old Doc Amount for Complex Pos( Financing case)
987 CURSOR c_old_doc_amt_cw_financing(p_po_header_id_csr IN NUMBER) IS
988 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
989 (pl.quantity*pl.unit_price),
990 'AMOUNT',
991 (pl.amount)))
992 FROM po_lines_all pl
993 WHERE pl.po_header_id = p_po_header_id_csr;
994
995 l_po_header_id po_change_requests.document_header_id%TYPE;
996 l_po_release_id po_change_requests.po_release_id%TYPE;
997 l_change_group_id po_change_requests.change_request_group_id%TYPE;
998 l_old_doc_amt NUMBER;
999 l_total_new_doc_amt NUMBER;
1000 l_new_doc_amt_rel NUMBER;
1001 l_return_val VARCHAR2(1) :='Y';
1002 l_doc_amt_max_incr_per NUMBER;
1003 l_doc_amt_max_dec_per NUMBER;
1004 l_doc_amt_max_incr_val NUMBER;
1005 l_doc_amt_max_dec_val NUMBER;
1006 x_progress VARCHAR2(1000);
1007 l_po_style_type VARCHAR2(10);
1008 l_doc_type VARCHAR2(10);
1009 l_complex_po_style VARCHAR2(10);
1010
1011 BEGIN
1012
1013 IF ( funcmode = 'RUN' ) THEN
1014
1015 x_progress := 'DOC_AMOUNT_WITHIN_TOL:000';
1016 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'PO_HEADER_ID');
1019
1020 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => 'CHANGE_REQUEST_GROUP_ID');
1023
1024 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1025 itemkey => itemkey,
1026 aname => 'PO_RELEASE_ID');
1027
1028 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1029 itemkey => itemkey,
1030 aname => 'PO_STYLE_TYPE');
1031
1032 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1033 itemkey => itemkey,
1034 aname => 'DOCUMENT_TYPE');
1035
1036 l_complex_po_style := wf_engine.GetItemAttrText (itemtype => itemtype,
1037 itemkey => itemkey,
1038 aname => 'COMPLEX_PO_STYLE');
1039
1040 x_progress := 'DOC_AMOUNT_WITHIN_TOL:001';
1041
1042 IF (l_change_group_id IS NOT NULL) THEN
1043
1044 -- get po document total tolerances
1045 -- check for the DOC types (applicable for PO Amount and BPA Release Amount)
1046 -- if other doc types return true and exit
1047 IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1048
1049 l_doc_amt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1050 itemkey => itemkey,
1051 aname => 'DOC_AMOUNT_INCR_PER');
1052 l_doc_amt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1053 itemkey => itemkey,
1054 aname => 'DOC_AMOUNT_DEC_PER');
1055
1056 x_progress := 'DOC_AMOUNT_WITHIN_TOL:002';
1057 log_message('DOC_AMOUNT_WITHIN_TOL','Doc Amount Inc & Dec percentage',l_doc_amt_max_incr_per || ', '|| l_doc_amt_max_dec_per);
1058
1059
1060 l_doc_amt_max_incr_val :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
1061 itemkey => itemkey,
1062 aname => 'DOC_AMOUNT_INCR_VAL');
1063 l_doc_amt_max_dec_val :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
1064 itemkey => itemkey,
1065 aname => 'DOC_AMOUNT_DEC_VAL');
1066
1067 x_progress := 'DOC_AMOUNT_WITHIN_TOL:003';
1068 log_message('DOC_AMOUNT_WITHIN_TOL','Doc Amount Inc & Dec Values',l_doc_amt_max_incr_val|| ', '||l_doc_amt_max_dec_val);
1069
1070
1071 IF(l_doc_type = 'PO') THEN
1072
1073 IF(l_po_style_type = 'NORMAL') THEN
1074 OPEN c_old_doc_amt_changes(l_po_header_id);
1075 FETCH c_old_doc_amt_changes INTO l_old_doc_amt;
1076 x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1077 log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1078 l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1079 x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1080 log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1081 CLOSE c_old_doc_amt_changes;
1082
1083 ELSIF(l_po_style_type = 'COMPLEX') THEN
1084
1085 IF(l_complex_po_style = 'ACTUALS') THEN
1086
1087 OPEN c_old_doc_amt_cw_actuals(l_po_header_id);
1088 FETCH c_old_doc_amt_cw_actuals INTO l_old_doc_amt;
1089 x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1090 log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1091 l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1092 x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1093 log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1094 CLOSE c_old_doc_amt_cw_actuals;
1095
1096 ELSIF(l_complex_po_style = 'FINANCING') THEN
1097
1098 OPEN c_old_doc_amt_cw_financing(l_po_header_id);
1099 FETCH c_old_doc_amt_cw_financing INTO l_old_doc_amt;
1100 x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1101 log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1102 l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1103 x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1104 log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1105 CLOSE c_old_doc_amt_cw_financing;
1106
1107 END IF;
1108
1109 END IF;
1110 IF (NOT change_within_tol(l_old_doc_amt, l_total_new_doc_amt, l_doc_amt_max_incr_per, l_doc_amt_max_dec_per, l_doc_amt_max_incr_val, l_doc_amt_max_dec_val)) THEN
1111 l_return_val := 'N';
1112 END IF;
1113 x_progress := 'DOC_AMOUNT_WITHIN_TOL:009';
1114
1115 ELSIF(l_doc_type = 'RELEASE') THEN
1116
1117 OPEN c_old_doc_amt_changes_rel(l_po_header_id,l_po_release_id);
1118 FETCH c_old_doc_amt_changes_rel INTO l_old_doc_amt;
1119 x_progress := 'DOC_AMOUNT_WITHIN_TOL:010';
1120 log_message('DOC_AMOUNT_WITHIN_TOL','Old Amount',l_old_doc_amt);
1121 CLOSE c_old_doc_amt_changes_rel;
1122
1123 l_new_doc_amt_rel := CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1124 x_progress := 'DOC_AMOUNT_WITHIN_TOL:011';
1125 log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_new_doc_amt_rel);
1126
1127 IF (NOT change_within_tol(l_old_doc_amt, l_new_doc_amt_rel, l_doc_amt_max_incr_per, l_doc_amt_max_dec_per, l_doc_amt_max_incr_val, l_doc_amt_max_dec_val)) THEN
1128 l_return_val := 'N';
1129 END IF;
1130 END IF; -- l_doc_type = PO , l_doc_type = RELEASE
1131
1132 x_progress := 'DOC_AMOUNT_WITHIN_TOL:012';
1133 END IF; -- PO Or RELEASE
1134
1135 END IF; -- change group id is not null
1136
1137 -- set result value
1138 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
1139
1140 x_progress := 'DOC_AMOUNT_WITHIN_TOL:013';
1141 log_message('DOC_AMOUNT_WITHIN_TOL','Result',resultout);
1142
1143 END IF; -- IF ( funcmode = 'RUN' )
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1149 g_module_prefix,
1150 x_progress || ':unexpected error' || Sqlerrm);
1151 END IF;
1152 wf_core.context('POSCHORD', 'DOC_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
1153 raise;
1154 END DOC_AMOUNT_WITHIN_TOL;
1155
1156
1157 FUNCTION CALCULATE_NEW_DOC_AMOUNT( p_po_header_id IN NUMBER , p_po_release_id IN NUMBER, p_complex_po_style IN VARCHAR2)
1158 RETURN NUMBER
1159 IS
1160 -- Picks up new amount for SPO
1161 CURSOR c_new_doc_amt_changes(p_po_header_id_csr IN NUMBER) IS
1162 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT',(nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
1163 (nvl(pcr.new_price,pl.unit_price) *
1164 (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1165 FROM po_change_requests pcr, --line amount/price change
1166 po_change_requests pcr1, --shipment quantity change
1167 po_lines_all pl,
1168 po_line_locations_all pll
1169 WHERE pl.po_header_id = p_po_header_id_csr
1170 AND pll.po_line_id = pl.po_line_id
1171 AND pcr1.document_header_id (+) = p_po_header_id_csr
1172 AND pcr1.document_line_location_id(+) = pll.line_location_id
1173 AND pcr1.action_type(+) = 'MODIFICATION'
1174 AND pcr1.request_status(+) = 'PENDING'
1175 AND pcr1.request_level (+) = 'SHIPMENT'
1176 AND pcr1.initiator(+) = 'SUPPLIER'
1177 AND pcr.document_line_id(+) = pl.po_line_id
1178 AND pcr.action_type(+) = 'MODIFICATION'
1179 AND pcr.request_status(+) = 'PENDING'
1180 AND pcr.request_level (+) = 'LINE'
1181 AND pcr.initiator(+) = 'SUPPLIER'
1182 UNION ALL
1183 -- for splitted shipments
1184 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1185 (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1186 FROM po_change_requests pcr, --line amount/price change
1187 po_change_requests pcr2, --for split shipments
1188 po_lines_all pl,
1189 po_line_locations_all pll
1190 WHERE pl.po_header_id = p_po_header_id_csr
1191 AND pll.po_line_id = pl.po_line_id
1192 AND pcr2.document_header_id = p_po_header_id_csr
1193 AND pcr2.parent_line_location_id = pll.line_location_id
1194 AND pcr2.action_type = 'MODIFICATION'
1195 AND pcr2.request_status = 'PENDING'
1196 AND pcr2.request_level = 'SHIPMENT'
1197 AND pcr2.initiator = 'SUPPLIER'
1198 AND pcr.document_line_id(+) = pl.po_line_id
1199 AND pcr.action_type(+) = 'MODIFICATION'
1200 AND pcr.request_status(+) = 'PENDING'
1201 AND pcr.request_level (+) = 'LINE'
1202 AND pcr.initiator(+) = 'SUPPLIER';
1203
1204 -- Picks up new amount for releases
1205 -- old_price included for price breaks
1206 CURSOR c_new_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER ) IS
1207 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)), (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) * (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1208 FROM po_change_requests pcr,
1209 po_lines_all pl,
1210 po_line_locations_all pll
1211 WHERE pll.po_header_id = p_po_header_id_csr
1212 AND pll.po_release_id = p_po_release_id_csr
1213 AND pll.po_line_id = pl.po_line_id
1214 AND pcr.po_release_id(+) = p_po_release_id_csr
1215 AND pcr.document_header_id(+) = p_po_header_id_csr
1216 --AND pcr.document_line_id = pl.po_line_id
1217 AND pcr.action_type(+) = 'MODIFICATION'
1218 AND pcr.request_status(+) = 'PENDING'
1219 AND pcr.request_level (+) = 'SHIPMENT'
1220 AND pcr.initiator(+) = 'SUPPLIER'
1221 AND pcr.document_line_location_id(+) = pll.line_location_id
1222 UNION ALL
1223 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(pcr2.new_price,pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1224 FROM po_change_requests pcr2, -- for splitted shipments
1225 po_lines_all pl,
1226 po_line_locations_all pll
1227 WHERE pll.po_header_id = p_po_header_id_csr
1228 AND pll.po_line_id = pl.po_line_id
1229 AND pcr2.po_release_id(+) = p_po_release_id_csr
1230 AND pcr2.document_header_id(+) = p_po_header_id_csr
1231 AND pcr2.document_line_id(+) = pl.po_line_id
1232 AND pcr2.action_type(+) = 'MODIFICATION'
1233 AND pcr2.request_status(+) = 'PENDING'
1234 AND pcr2.request_level (+) = 'SHIPMENT'
1235 AND pcr2.initiator(+) = 'SUPPLIER'
1236 AND pcr2.parent_line_location_id = pll.line_location_id;
1237
1238
1239 -- Picks Up New Amount for Complex Po's ( Actuals Case)
1240 CURSOR c_new_doc_amt_chg_cw_actuals(p_po_header_id_csr IN NUMBER) IS
1241 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1242 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1243 'AMOUNT',
1244 DECODE(pll.payment_type, 'LUMPSUM',
1245 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1246 'MILESTONE',
1247 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1248 'RATE',
1249 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1250 FROM
1251 po_change_requests pcr, --shipment changes
1252 po_lines_all pl,
1253 po_line_locations_all pll
1254 WHERE
1255 pl.po_header_id = p_po_header_id_csr
1256 AND pll.po_line_id = pl.po_line_id
1257 AND pcr.document_header_id (+) = p_po_header_id_csr
1258 AND pcr.document_line_location_id(+) = pll.line_location_id
1259 AND pcr.action_type(+) = 'MODIFICATION'
1260 AND pcr.request_status(+) = 'PENDING'
1261 AND pcr.request_level (+) = 'SHIPMENT'
1262 AND pcr.initiator(+) = 'SUPPLIER'
1263 UNION ALL
1264 -- for split shipment changes
1265 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1266 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1267 'AMOUNT',
1268 DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
1269 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1270 'MILESTONE',
1271 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1272 'RATE',
1273 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1274 FROM
1275 po_change_requests pcr, --shipment changes
1276 po_lines_all pl,
1277 po_line_locations_all pll
1278 WHERE
1279 pl.po_header_id = p_po_header_id_csr
1280 AND pll.po_line_id = pl.po_line_id
1281 AND pcr.document_header_id = p_po_header_id_csr
1282 AND pcr.parent_line_location_id = pll.line_location_id
1283 AND pcr.action_type = 'MODIFICATION'
1284 AND pcr.request_status = 'PENDING'
1285 AND pcr.request_level = 'SHIPMENT'
1286 AND pcr.initiator = 'SUPPLIER';
1287
1288
1289 -- Picks Up New Amount for Complex Po's ( Financing Case)
1290 CURSOR c_new_doc_amt_chg_cw_financing(p_po_header_id_csr IN NUMBER) IS
1291 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1292 'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1293 FROM
1294 po_lines_all pl,
1295 po_change_requests pcr
1296 WHERE
1297 pl.po_header_id = p_po_header_id_csr
1298 AND pcr.document_header_id (+) = p_po_header_id_csr
1299 AND pcr.document_line_id(+) = pl.po_line_id
1300 AND pcr.action_type(+) = 'MODIFICATION'
1301 AND pcr.request_status(+) = 'PENDING'
1302 AND pcr.request_level (+) = 'LINE'
1303 AND pcr.initiator(+) = 'SUPPLIER';
1304
1305
1306
1307 l_new_doc_amt NUMBER;
1308 l_total_new_doc_amt NUMBER := 0;
1309 x_progress VARCHAR2(1000);
1310
1311 BEGIN
1312
1313 x_progress := 'CALCULATE_NEW_DOC_AMOUNT:000';
1314 IF(p_po_release_id is null) THEN
1315
1316 x_progress := 'CALCULATE_NEW_DOC_AMOUNT:001';
1317 IF(p_complex_po_style = 'ACTUALS') THEN
1318 OPEN c_new_doc_amt_chg_cw_actuals(p_po_header_id);
1319 LOOP
1320 FETCH c_new_doc_amt_chg_cw_actuals INTO l_new_doc_amt;
1321 EXIT WHEN c_new_doc_amt_chg_cw_actuals%NOTFOUND;
1322 l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1323 END LOOP;
1324 x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1325 log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1326 CLOSE c_new_doc_amt_chg_cw_actuals;
1327 ELSIF(p_complex_po_style = 'FINANCING') THEN
1328 OPEN c_new_doc_amt_chg_cw_financing(p_po_header_id);
1329 LOOP
1330 FETCH c_new_doc_amt_chg_cw_financing INTO l_new_doc_amt;
1331 EXIT WHEN c_new_doc_amt_chg_cw_financing%NOTFOUND;
1332 l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1333 END LOOP;
1334 x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1335 log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1336 CLOSE c_new_doc_amt_chg_cw_financing;
1337 ELSE
1338 OPEN c_new_doc_amt_changes(p_po_header_id);
1339 LOOP
1340 FETCH c_new_doc_amt_changes INTO l_new_doc_amt;
1341 EXIT WHEN c_new_doc_amt_changes%NOTFOUND;
1342 l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1343 END LOOP;
1344 x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1345 log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1346 CLOSE c_new_doc_amt_changes;
1347 END IF;
1348
1349 ELSIF( p_po_release_id is not null) THEN
1350 x_progress := 'CALCULATE_NEW_DOC_AMOUNT:003';
1351 OPEN c_new_doc_amt_changes_rel(p_po_header_id,p_po_release_id);
1352 LOOP
1353 FETCH c_new_doc_amt_changes_rel INTO l_new_doc_amt;
1354 EXIT WHEN c_new_doc_amt_changes_rel%NOTFOUND;
1355 l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1356 END LOOP;
1357 x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1358 log_message('DOC_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_doc_amt);
1359 CLOSE c_new_doc_amt_changes_rel;
1360 x_progress := 'CALCULATE_NEW_DOC_AMOUNT:005';
1361 END IF;
1362
1363 return l_total_new_doc_amt;
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1368 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1369 g_module_prefix,
1370 x_progress || ':unexpected error' || Sqlerrm);
1371 END IF;
1372 raise;
1373
1374 END CALCULATE_NEW_DOC_AMOUNT;
1375
1376 /* This procedure checks whether Line Amount Change is within the tolerance or not
1377 Any Line Price change or shipment quantity chnage that affects the amount is also
1378 taken in to consideration
1379 Returns 'Y' if within the tolerance
1380 Returns 'N' if out of tolerance
1381 */
1382
1383
1384 PROCEDURE LINE_AMOUNT_WITHIN_TOL( itemtype IN VARCHAR2,
1385 itemkey IN VARCHAR2,
1386 actid IN NUMBER,
1387 funcmode IN VARCHAR2,
1388 resultout OUT NOCOPY VARCHAR2)
1389 IS
1390 -- Picks up Old Line Amount for SPO
1391 CURSOR c_line_amt_old(p_po_header_id_csr IN NUMBER) IS
1392 select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1393 from po_lines_all pol,
1394 po_line_locations_all pll
1395 where pll.po_header_id = p_po_header_id_csr
1396 AND pll.po_line_id = pol.po_line_id
1397 GROUP BY pll.po_line_id;
1398
1399 CURSOR c_line_amt_old_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
1400 select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1401 from po_lines_all pol,
1402 po_line_locations_all pll
1403 where pll.po_release_id =p_po_release_id_csr
1404 AND pll.po_header_id = p_po_header_id_csr
1405 AND pll.po_line_id = pol.po_line_id
1406 GROUP BY pll.po_line_id;
1407
1408
1409 -- Picks up Old Line Amount ( Financing Case)
1410 CURSOR c_line_amt_old_cw_financing(p_po_header_id_csr IN NUMBER) IS
1411 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',pl.unit_price*pl.quantity,'AMOUNT',pl.amount)),pl.po_line_id
1412 FROM po_lines_all pl
1413 WHERE pl.po_header_id = p_po_header_id_csr
1414 GROUP BY pl.po_line_id;
1415
1416 -- Picks up Old Line Amount ( Actuals case)
1417 CURSOR c_line_amt_old_cw_actuals(p_po_header_id_csr IN NUMBER) IS
1418 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
1419 (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
1420 'AMOUNT',
1421 DECODE(pll.payment_type, 'LUMPSUM',
1422 (pll.amount - NVL(pll.amount_cancelled,0)),
1423 'MILESTONE',
1424 (pll.amount - NVL(pll.amount_cancelled,0)),
1425 'RATE',
1426 (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.po_line_id
1427 FROM po_lines_all pl,
1428 po_line_locations_all pll
1429 WHERE pl.po_header_id = p_po_header_id_csr
1430 AND pll.po_line_id = pl.po_line_id
1431 GROUP BY pll.po_line_id;
1432
1433
1434 l_old_lineamt po_change_requests.old_amount%TYPE;
1435 l_temp_po_line_id NUMBER;
1436 l_return_val VARCHAR2(1) :='Y';
1437 l_line_amt_max_incr_per NUMBER;
1438 l_line_amt_max_dec_per NUMBER;
1439 l_line_amt_max_incr_val NUMBER;
1440 l_line_amt_max_dec_val NUMBER;
1441 l_temp_total_line_amt_new NUMBER;
1442 x_progress VARCHAR2(1000);
1443 l_po_header_id po_change_requests.document_header_id%TYPE;
1444 l_po_release_id NUMBER;
1445 l_po_style_type VARCHAR2(10);
1446 l_doc_type VARCHAR2(10);
1447 l_complex_po_style VARCHAR2(10);
1448
1449 BEGIN
1450
1451 IF ( funcmode = 'RUN' ) THEN
1452
1453 x_progress := 'LINE_AMOUNT_WITHIN_TOL:000';
1454
1455 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1456 itemkey => itemkey,
1457 aname => 'PO_HEADER_ID');
1458
1459 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1460 itemkey => itemkey,
1461 aname => 'PO_RELEASE_ID');
1462
1463 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1464 itemkey => itemkey,
1465 aname => 'PO_STYLE_TYPE');
1466
1467 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1468 itemkey => itemkey,
1469 aname => 'DOCUMENT_TYPE');
1470
1471 l_complex_po_style := wf_engine.GetItemAttrText (itemtype => itemtype,
1472 itemkey => itemkey,
1473 aname => 'COMPLEX_PO_STYLE');
1474
1475
1476 x_progress := 'LINE_AMOUNT_WITHIN_TOL:001';
1477
1478 IF (l_po_header_id IS NOT NULL) THEN
1479 -- check for the DOC types (applicable for PO LINE AMOUNT( Line level)
1480 -- if other doc types return true and exit
1481 IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1482 -- get shipment quantity tolerances
1483 l_line_amt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1484 itemkey => itemkey,
1485 aname => 'LINE_AMOUNT_INCR_PER');
1486
1487 l_line_amt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1488 itemkey => itemkey,
1489 aname => 'LINE_AMOUNT_DEC_PER');
1490
1491 x_progress := 'LINE_AMOUNT_WITHIN_TOL:002';
1492 log_message('LINE_AMOUNT_WITHIN_TOL','Line Amount Incr & Decr Tol percentage',l_line_amt_max_incr_per || ', '|| l_line_amt_max_dec_per);
1493
1494
1495 l_line_amt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1496 itemkey => itemkey,
1497 aname => 'LINE_AMOUNT_INCR_VAL');
1498
1499 l_line_amt_max_dec_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1500 itemkey => itemkey,
1501 aname => 'LINE_AMOUNT_DEC_VAL');
1502
1503 x_progress := 'LINE_AMOUNT_WITHIN_TOL:003';
1504 log_message('LINE_AMOUNT_WITHIN_TOL','Line Amount Incr & Decr Tol values ',l_line_amt_max_incr_val || ', '|| l_line_amt_max_dec_val);
1505
1506 IF(l_doc_type = 'PO') THEN
1507
1508 IF(l_po_style_type = 'NORMAL') THEN
1509 OPEN c_line_amt_old(l_po_header_id);
1510
1511 LOOP
1512 FETCH c_line_amt_old
1513 INTO l_old_lineamt,l_temp_po_line_id;
1514 EXIT WHEN c_line_amt_old%NOTFOUND;
1515
1516 x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1517 log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1518 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1519
1520 EXIT WHEN (l_return_val = 'N');
1521
1522 l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1523
1524 x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1525 log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1526
1527 IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1528 l_return_val := 'N';
1529 END IF;
1530 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1531 log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1532 END LOOP;
1533 CLOSE c_line_amt_old;
1534 ELSIF(l_po_style_type = 'COMPLEX') THEN
1535 IF(l_complex_po_style = 'ACTUALS') THEN
1536 OPEN c_line_amt_old_cw_actuals(l_po_header_id);
1537
1538 LOOP
1539 FETCH c_line_amt_old_cw_actuals
1540 INTO l_old_lineamt,l_temp_po_line_id;
1541 EXIT WHEN c_line_amt_old_cw_actuals%NOTFOUND;
1542
1543 x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1544 log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1545 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1546
1547 EXIT WHEN (l_return_val = 'N');
1548
1549 l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1550
1551 x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1552 log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1553
1554 IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1555 l_return_val := 'N';
1556 END IF;
1557 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1558 log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1559 END LOOP;
1560 CLOSE c_line_amt_old_cw_actuals;
1561 ELSIF(l_complex_po_style = 'FINANCING') THEN
1562 OPEN c_line_amt_old_cw_financing(l_po_header_id);
1563
1564 LOOP
1565 FETCH c_line_amt_old_cw_financing
1566 INTO l_old_lineamt,l_temp_po_line_id;
1567 EXIT WHEN c_line_amt_old_cw_financing%NOTFOUND;
1568
1569 x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1570 log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1571 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1572
1573 EXIT WHEN (l_return_val = 'N');
1574
1575 l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1576
1577 x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1578 log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1579 END LOOP;
1580 CLOSE c_line_amt_old_cw_financing;
1581
1582 END IF; -- ACTUALS , FINANCING
1583 END IF; -- po_style_type = NORMAL
1584
1585 IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1586 l_return_val := 'N';
1587 END IF;
1588 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1589 log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1590
1591
1592 ELSIF(l_doc_type = 'RELEASE') THEN
1593
1594 OPEN c_line_amt_old_rel(l_po_header_id,l_po_release_id);
1595 LOOP
1596 FETCH c_line_amt_old_rel INTO l_old_lineamt,l_temp_po_line_id;
1597 EXIT WHEN c_line_amt_old_rel%NOTFOUND;
1598 x_progress := 'LINE_AMOUNT_WITHIN_TOL:010';
1599 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount',l_old_lineamt);
1600
1601 l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1602 x_progress := 'LINE_AMOUNT_WITHIN_TOL:011';
1603 log_message('LINE_AMOUNT_WITHIN_TOL','New Line Amount',l_temp_total_line_amt_new);
1604
1605
1606 IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per,l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1607 l_return_val := 'N';
1608 END IF;
1609 END LOOP;
1610 CLOSE c_line_amt_old_rel;
1611
1612 ELSE
1613 l_return_val := 'Y';
1614
1615 END IF;
1616
1617 END IF; -- l_doc_type PO Or RELEASE
1618
1619 END IF; -- l_po_header_id is not null
1620 -- set result value
1621 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
1622 x_progress := 'LINE_AMOUNT_WITHIN_TOL:007';
1623 log_message('LINE_AMOUNT_WITHIN_TOL','Final result',resultout);
1624 END IF; -- IF ( funcmode = 'RUN' )
1625
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1630 g_module_prefix,
1631 x_progress || ':unexpected error' || Sqlerrm);
1632 END IF;
1633 wf_core.context('POSCHORD', 'LINE_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
1634 raise;
1635 END LINE_AMOUNT_WITHIN_TOL;
1636
1637
1638 FUNCTION CALCULATE_NEW_LINE_AMOUNT( p_po_header_id IN NUMBER, p_po_release_id IN NUMBER, p_po_line_id IN NUMBER,p_complex_po_style IN VARCHAR2)
1639 RETURN NUMBER
1640 IS
1641 -- Picks up new line amount for SPO
1642 CURSOR c_line_amt_new(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1643 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
1644 (nvl(pcr.new_price,pl.unit_price) *
1645 (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1646 FROM po_change_requests pcr, --line amount/price change
1647 po_change_requests pcr1, --shipment quantity change
1648 po_lines_all pl,
1649 po_line_locations_all pll
1650 WHERE pl.po_header_id = p_po_header_id_csr
1651 AND pl.po_line_id = p_temp_po_line_id_csr
1652 AND pll.po_line_id = pl.po_line_id
1653 AND pcr1.document_header_id (+) = p_po_header_id_csr
1654 AND pcr1.document_line_location_id(+) = pll.line_location_id
1655 AND pcr1.action_type(+) = 'MODIFICATION'
1656 AND pcr1.request_status(+) = req_status
1657 AND pcr1.request_level (+) = 'SHIPMENT'
1658 AND pcr1.initiator(+) = req_initiator
1659 AND pcr.document_line_id(+) = pl.po_line_id
1660 AND pcr.action_type(+) = 'MODIFICATION'
1661 AND pcr.request_status(+) = req_status
1662 AND pcr.request_level (+) = 'LINE'
1663 AND pcr.initiator(+) = req_initiator
1664 UNION ALL
1665 -- for splitted shipments
1666 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1667 (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1668 FROM po_change_requests pcr, --line amount/price change
1669 po_change_requests pcr2, --for split shipments
1670 po_lines_all pl,
1671 po_line_locations_all pll
1672 WHERE pl.po_header_id = p_po_header_id_csr
1673 AND pl.po_line_id = p_temp_po_line_id_csr
1674 AND pll.po_line_id = pl.po_line_id
1675 AND pcr2.document_header_id = p_po_header_id_csr
1676 AND pcr2.parent_line_location_id = pll.line_location_id
1677 AND pcr2.action_type = 'MODIFICATION'
1678 AND pcr2.request_status in req_status
1679 AND pcr2.request_level = 'SHIPMENT'
1680 AND pcr2.initiator = req_initiator
1681 AND pcr.document_line_id(+) = pl.po_line_id
1682 AND pcr.action_type(+) = 'MODIFICATION'
1683 AND pcr.request_status in req_status
1684 AND pcr.request_level (+) = 'LINE'
1685 AND pcr.initiator(+) = req_initiator;
1686
1687
1688 -- Picks up new line amount for releses
1689 -- old_price is included for price breaks
1690 CURSOR c_line_amt_new_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1691 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
1692 (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
1693 (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1694 FROM po_change_requests pcr,
1695 po_lines_all pl,
1696 po_line_locations_all pll
1697 WHERE pll.po_header_id = p_po_header_id_csr
1698 AND pll.po_line_id = p_temp_po_line_id_csr
1699 AND pll.po_line_id = pl.po_line_id
1700 AND pll.po_release_id = p_po_release_id_csr
1701 AND pcr.po_release_id (+) = p_po_release_id_csr
1702 AND pcr.document_header_id(+) = p_po_header_id_csr
1703 --AND pcr.document_line_id = pl.po_line_id
1704 AND pcr.action_type(+) = 'MODIFICATION'
1705 AND pcr.request_status (+) = req_status
1706 AND pcr.request_level (+) = 'SHIPMENT'
1707 AND pcr.initiator(+) = req_initiator
1708 AND pcr.document_line_location_id(+) = pll.line_location_id
1709 UNION ALL
1710 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1711 FROM po_change_requests pcr2, -- for splitted shipments
1712 po_lines_all pl,
1713 po_line_locations_all pll
1714 WHERE pll.po_header_id = p_po_header_id_csr
1715 AND pll.po_line_id = p_temp_po_line_id_csr
1716 AND pll.po_line_id = pl.po_line_id
1717 AND pcr2.po_release_id = p_po_release_id_csr
1718 AND pcr2.document_header_id(+) = p_po_header_id_csr
1719 AND pcr2.document_line_id(+) = pl.po_line_id
1720 AND pcr2.action_type(+) = 'MODIFICATION'
1721 AND pcr2.request_status in req_status
1722 AND pcr2.request_level (+) = 'SHIPMENT'
1723 AND pcr2.initiator(+) = req_initiator
1724 AND pcr2.parent_line_location_id = pll.line_location_id;
1725
1726
1727 -- Picks up New Line Amount for complex Po( Actuals Case)
1728 CURSOR c_line_amt_new_cw_actuals(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1729 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1730 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1731 'AMOUNT',
1732 DECODE(pll.payment_type, 'LUMPSUM',
1733 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1734 'MILESTONE',
1735 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1736 'RATE',
1737 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1738 FROM
1739 po_change_requests pcr, --shipment quantity changes
1740 po_lines_all pl,
1741 po_line_locations_all pll
1742 WHERE
1743 pl.po_header_id = p_po_header_id_csr
1744 AND pl.po_line_id = p_temp_po_line_id_csr
1745 AND pll.po_line_id = pl.po_line_id
1746 AND pcr.document_header_id (+) = p_po_header_id_csr
1747 AND pcr.document_line_location_id(+) = pll.line_location_id
1748 AND pcr.action_type(+) = 'MODIFICATION'
1749 AND pcr.request_status(+)=req_status
1750 AND pcr.request_level (+) = 'SHIPMENT'
1751 AND pcr.initiator(+) = req_initiator
1752 UNION ALL
1753 -- for split shipment changes
1754 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1755 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1756 'AMOUNT',
1757 DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
1758 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1759 'MILESTONE',
1760 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
1761 'RATE',
1762 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1763 FROM
1764 po_change_requests pcr, --shipment quantity changes
1765 po_lines_all pl,
1766 po_line_locations_all pll
1767 WHERE
1768 pl.po_header_id = p_po_header_id_csr
1769 AND pl.po_line_id = p_temp_po_line_id_csr
1770 AND pll.po_line_id = pl.po_line_id
1771 AND pcr.document_header_id = p_po_header_id_csr
1772 AND pcr.parent_line_location_id = pll.line_location_id
1773 AND pcr.action_type = 'MODIFICATION'
1774 AND pcr.request_status(+)=req_status
1775 AND pcr.request_level = 'SHIPMENT'
1776 AND pcr.initiator = req_initiator;
1777
1778
1779 -- Picks up New Line Amount for Complex POs( Financing Case)
1780 CURSOR c_line_amt_new_cw_financing(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1781 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1782 'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1783 FROM
1784 po_lines_all pl,
1785 po_change_requests pcr
1786 WHERE
1787 pl.po_header_id = p_po_header_id_csr
1788 AND pl.po_line_id = p_temp_po_line_id_csr
1789 AND pcr.document_header_id (+) = p_po_header_id_csr
1790 AND pcr.document_line_id(+) = pl.po_line_id
1791 AND pcr.action_type(+) = 'MODIFICATION'
1792 AND pcr.request_status(+)=req_status
1793 AND pcr.request_level (+) = 'LINE'
1794 AND pcr.initiator(+) = req_initiator;
1795
1796 l_new_line_amt NUMBER;
1797 l_total_new_line_amt NUMBER := 0;
1798 x_progress VARCHAR2(1000);
1799 req_status po_change_requests.request_status%TYPE;
1800 req_initiator po_change_requests.initiator%TYPE;
1801
1802 BEGIN
1803
1804 IF(p_po_release_id is null) THEN
1805 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1806 where document_header_id=p_po_header_id
1807 and change_active_flag='Y'
1808 and rownum=1;
1809 ELSE
1810 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1811 where document_header_id=p_po_header_id
1812 and po_release_id = p_po_release_id
1813 and change_active_flag='Y'
1814 and rownum=1;
1815 END IF;
1816
1817 x_progress := 'CALCULATE_NEW_LINE_AMOUNT:000';
1818 IF(p_po_release_id is null) THEN
1819 IF(p_complex_po_style = 'ACTUALS') THEN
1820 OPEN c_line_amt_new_cw_actuals(p_po_header_id,p_po_line_id,req_status,req_initiator);
1821 LOOP
1822 FETCH c_line_amt_new_cw_actuals INTO l_new_line_amt;
1823 EXIT WHEN c_line_amt_new_cw_actuals%NOTFOUND;
1824 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1825 END LOOP;
1826 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1827 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1828 CLOSE c_line_amt_new_cw_actuals;
1829 ELSIF(p_complex_po_style = 'FINANCING') THEN
1830 OPEN c_line_amt_new_cw_financing(p_po_header_id,p_po_line_id,req_status,req_initiator);
1831 LOOP
1832 FETCH c_line_amt_new_cw_financing INTO l_new_line_amt;
1833 EXIT WHEN c_line_amt_new_cw_financing%NOTFOUND;
1834 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1835 END LOOP;
1836 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1837 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1838 CLOSE c_line_amt_new_cw_financing;
1839
1840 ELSE
1841
1842 OPEN c_line_amt_new(p_po_header_id,p_po_line_id,req_status,req_initiator);
1843 LOOP
1844 FETCH c_line_amt_new INTO l_new_line_amt;
1845 EXIT WHEN c_line_amt_new%NOTFOUND;
1846 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1847 END LOOP;
1848 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1849 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1850 CLOSE c_line_amt_new;
1851 END IF;
1852 ELSIF( p_po_release_id is not null) THEN
1853
1854 OPEN c_line_amt_new_rel(p_po_header_id,p_po_release_id,p_po_line_id,req_status,req_initiator);
1855 LOOP
1856 FETCH c_line_amt_new_rel INTO l_new_line_amt;
1857 EXIT WHEN c_line_amt_new_rel%NOTFOUND;
1858 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1859 END LOOP;
1860 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1861 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1862 CLOSE c_line_amt_new_rel;
1863 END IF;
1864
1865 return l_total_new_line_amt;
1866
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1871 g_module_prefix,
1872 x_progress || ':unexpected error' || Sqlerrm);
1873 END IF;
1874 raise;
1875 END CALCULATE_NEW_LINE_AMOUNT;
1876
1877
1878 PROCEDURE SHIP_AMOUNT_WITHIN_TOL( itemtype IN VARCHAR2,
1879 itemkey IN VARCHAR2,
1880 actid IN NUMBER,
1881 funcmode IN VARCHAR2,
1882 resultout OUT NOCOPY VARCHAR2)
1883 IS
1884
1885 -- Calculates the old shipment amount
1886 CURSOR c_old_ship_amt(p_po_header_id_csr IN NUMBER) IS
1887 select sum(decode(pol.matching_basis,'AMOUNT',(nvl(pll.amount,0) - nvl(pll.amount_cancelled,0)),(pol.unit_price *(pll.quantity-nvl(pll.quantity_cancelled,0))))),pll.line_location_id
1888 from po_line_locations_all pll,
1889 po_lines_all pol
1890 where pll.po_header_id = p_po_header_id_csr
1891 AND pll.po_line_id = pol.po_line_id
1892 GROUP BY pll.line_location_id;
1893
1894 -- Calculate Old Shipment amount for the BPA Release
1895 CURSOR c_old_ship_amt_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
1896 SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0))))),
1897 pll.line_location_id
1898 FROM po_lines_all pl,
1899 po_line_locations_all pll
1900 WHERE pll.po_release_id = p_po_release_id_csr
1901 AND pll.po_header_id = p_po_header_id_csr
1902 AND pll.po_line_id = pl.po_line_id
1903 GROUP BY pll.line_location_id;
1904
1905 -- Calculate Old Shipment amount for the Complex work POs ( Actuals And Financing Case)
1906 CURSOR c_old_ship_amt_cw(p_po_header_id_csr IN NUMBER) IS
1907 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
1908 (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
1909 'AMOUNT',
1910 DECODE(pll.payment_type, 'LUMPSUM',
1911 (pll.amount - NVL(pll.amount_cancelled,0)),
1912 'MILESTONE',
1913 (pll.amount - NVL(pll.amount_cancelled,0)),
1914 'RATE',
1915 (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.line_location_id
1916 FROM po_lines_all pl,
1917 po_line_locations_all pll
1918 WHERE pl.po_header_id = p_po_header_id_csr
1919 AND pll.po_line_id = pl.po_line_id
1920 GROUP BY pll.line_location_id;
1921
1922
1923
1924
1925
1926
1927 l_po_header_id po_change_requests.document_header_id%TYPE;
1928 l_po_release_id po_change_requests.po_release_id%TYPE;
1929 l_po_line_id po_change_requests.document_line_id%TYPE;
1930 l_change_group_id po_change_requests.change_request_group_id%type;
1931 l_matching_basis po_lines_all.matching_basis%TYPE;
1932 l_old_shipamt po_change_requests.old_amount%TYPE;
1933 l_new_shipamt po_change_requests.new_amount%TYPE;
1934 l_old_ship_amt_rel NUMBER;
1935 l_new_ship_amt_rel NUMBER;
1936 l_old_ship_amt_cw NUMBER;
1937 l_new_ship_amt_cw NUMBER;
1938 l_return_val VARCHAR2(1) :='Y';
1939 l_shipamt_max_incr_per NUMBER;
1940 l_shipamt_max_dec_per NUMBER;
1941 l_shipamt_max_incr_val NUMBER;
1942 l_shipamt_max_dec_val NUMBER;
1943 l_old_ship_amt NUMBER;
1944 l_new_ship_amt NUMBER := 0;
1945 l_new_ship_amt_no_change NUMBER := 0;
1946 l_line_location_id NUMBER;
1947 l_po_style_type VARCHAR2(10);
1948 l_doc_type VARCHAR2(10);
1949 x_progress VARCHAR2(1000);
1950
1951
1952 BEGIN
1953
1954 IF ( funcmode = 'RUN' ) THEN
1955 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:000';
1956
1957 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1958 itemkey => itemkey,
1959 aname => 'PO_HEADER_ID');
1960
1961 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1962 itemkey => itemkey,
1963 aname => 'PO_RELEASE_ID');
1964
1965 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1966 itemkey => itemkey,
1967 aname => 'CHANGE_REQUEST_GROUP_ID');
1968
1969 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1970 itemkey => itemkey,
1971 aname => 'PO_STYLE_TYPE');
1972
1973 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1974 itemkey => itemkey,
1975 aname => 'DOCUMENT_TYPE');
1976 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:001';
1977
1978 IF (l_change_group_id IS NOT NULL) THEN
1979 -- check for the DOC types (applicable for PO LINE AMOUNT( Line level)
1980 -- if other doc types return true and exit
1981 IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1982
1983 IF (l_po_style_type='NORMAL') THEN
1984
1985 -- get shipment quantity tolerances
1986 l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1987 itemkey => itemkey,
1988 aname => 'SHIP_AMOUNT_INCR_PER');
1989
1990 l_shipamt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1991 itemkey => itemkey,
1992 aname => 'SHIP_AMOUNT_DEC_PER');
1993
1994 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:002';
1995 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
1996 -- get shipment quantity tolerances
1997 l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1998 itemkey => itemkey,
1999 aname => 'SHIP_AMOUNT_INCR_VAL');
2000
2001 l_shipamt_max_dec_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2002 itemkey => itemkey,
2003 aname => 'SHIP_AMOUNT_DEC_VAL');
2004
2005 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:003';
2006 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2007
2008 ELSIF (l_po_style_type='COMPLEX') THEN
2009 -- Complex Work PO Chack -- COMPLEX WORK
2010 -- get shipment quantity tolerances
2011 l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2012 itemkey => itemkey,
2013 aname => 'PAY_AMOUNT_INCR_PER');
2014
2015 l_shipamt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2016 itemkey => itemkey,
2017 aname => 'PAY_AMOUNT_DEC_PER');
2018
2019 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:004';
2020 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
2021 -- get shipment quantity tolerances
2022 l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2023 itemkey => itemkey,
2024 aname => 'PAY_AMOUNT_INCR_VAL');
2025
2026 l_shipamt_max_dec_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2027 itemkey => itemkey,
2028 aname => 'PAY_AMOUNT_DEC_VAL');
2029 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:005';
2030 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2031 END IF; --l_po_style_type='NORMAL'/'COMPLEX'
2032
2033
2034
2035 IF(l_doc_type = 'PO' and l_po_style_type='NORMAL') THEN
2036 OPEN c_old_ship_amt(l_po_header_id);
2037 LOOP
2038 FETCH c_old_ship_amt
2039 INTO l_old_ship_amt,l_line_location_id;
2040 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2041 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2042 EXIT WHEN (l_return_val='N');
2043 EXIT WHEN c_old_ship_amt%NOTFOUND;
2044
2045 l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2046
2047 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2048 IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val , l_shipamt_max_dec_val)) THEN
2049 l_return_val := 'N';
2050 END IF;
2051 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2052 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2053 END LOOP;
2054 CLOSE c_old_ship_amt;
2055
2056 IF (l_return_val = 'Y') THEN
2057 wf_engine.SetItemAttrText( itemtype => itemtype,
2058 itemkey => itemkey,
2059 aname => 'NOTIF_USAGE',
2060 avalue => 'BUYER_AUTO_FYI');
2061 END IF;
2062 ELSIF ((l_doc_type = 'RELEASE') and (l_po_style_type='NORMAL')) THEN
2063 OPEN c_old_ship_amt_rel(l_po_header_id,l_po_release_id);
2064 LOOP
2065 FETCH c_old_ship_amt_rel
2066 INTO l_old_ship_amt,l_line_location_id;
2067 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2068 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2069 EXIT WHEN (l_return_val='N');
2070 EXIT WHEN c_old_ship_amt_rel%NOTFOUND;
2071
2072 l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2073
2074 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2075 IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val , l_shipamt_max_dec_val)) THEN
2076 l_return_val := 'N';
2077 END IF;
2078 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2079 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2080 END LOOP;
2081 CLOSE c_old_ship_amt_rel;
2082
2083 IF (l_return_val = 'Y') THEN
2084 wf_engine.SetItemAttrText( itemtype => itemtype,
2085 itemkey => itemkey,
2086 aname => 'NOTIF_USAGE',
2087 avalue => 'BUYER_AUTO_FYI');
2088 END IF;
2089 ELSIF (l_po_style_type='COMPLEX') THEN
2090
2091 OPEN c_old_ship_amt_cw(l_po_header_id);
2092 LOOP
2093 FETCH c_old_ship_amt_cw
2094 INTO l_old_ship_amt,l_line_location_id;
2095 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2096 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2097 EXIT WHEN (l_return_val='N');
2098 EXIT WHEN c_old_ship_amt_cw%NOTFOUND;
2099
2100 l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2101
2102 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2103 IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val , l_shipamt_max_dec_val)) THEN
2104 l_return_val := 'N';
2105 END IF;
2106 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2107 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2108 END LOOP;
2109 CLOSE c_old_ship_amt_cw;
2110
2111 IF (l_return_val = 'Y') THEN
2112 wf_engine.SetItemAttrText( itemtype => itemtype,
2113 itemkey => itemkey,
2114 aname => 'NOTIF_USAGE',
2115 avalue => 'BUYER_AUTO_FYI');
2116 END IF;
2117
2118 END IF; -- PO Or RELEASE or complex work POs
2119 END IF; -- l_doc_type PO or RELEASE
2120 -- set result value
2121 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2122 END IF; -- change_group_id is not null
2123 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:016';
2124 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',resultout);
2125 END IF; -- IF ( funcmode = 'RUN' )
2126 EXCEPTION
2127 WHEN OTHERS THEN
2128 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2129 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2130 g_module_prefix,
2131 x_progress || ':unexpected error' || Sqlerrm);
2132 END IF;
2133 wf_core.context('POSCHORD', 'SHIP_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
2134 raise;
2135 END SHIP_AMOUNT_WITHIN_TOL;
2136
2137
2138 FUNCTION CALCULATE_NEW_SHIP_AMOUNT( p_po_header_id IN NUMBER,
2139 p_po_release_id IN NUMBER,
2140 p_line_location_id IN NUMBER,
2141 p_split_flag IN VARCHAR2,
2142 p_po_style_type IN VARCHAR2,
2143 p_po_shipment_num IN NUMBER)
2144 RETURN NUMBER
2145 IS
2146
2147 --- picks up new shipment amount for SPO
2148 CURSOR c_new_shipment_amount (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
2149 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
2150 (nvl(pcr.new_price,pl.unit_price) *
2151 (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2152 FROM po_change_requests pcr, --line amount/price change
2153 po_change_requests pcr1, --shipment quantity change
2154 po_lines_all pl,
2155 po_line_locations_all pll
2156 WHERE pl.po_header_id = p_po_header_id_csr
2157 AND pll.line_location_id = p_line_location_id_csr
2158 AND pll.po_line_id = pl.po_line_id
2159 AND pcr1.document_header_id (+) = p_po_header_id_csr
2160 AND pcr1.document_line_location_id(+) = pll.line_location_id
2161 AND pcr1.action_type(+) = 'MODIFICATION'
2162 AND pcr1.request_status(+) = req_status
2163 AND pcr1.request_level (+) = 'SHIPMENT'
2164 AND pcr1.initiator(+) = req_initiator
2165 AND pcr.document_line_id(+) = pl.po_line_id
2166 AND pcr.action_type(+) = 'MODIFICATION'
2167 AND pcr.request_status(+) = req_status
2168 AND pcr.request_level (+) = 'LINE'
2169 AND pcr.initiator(+) = req_initiator;
2170
2171 CURSOR c_new_shipment_amount_split (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR,p_po_shipment_num IN NUMBER) IS
2172 -- for splitted shipments
2173 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
2174 (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
2175 FROM po_change_requests pcr, --line amount/price change
2176 po_change_requests pcr2, --for split shipments
2177 po_lines_all pl,
2178 po_line_locations_all pll
2179 WHERE pl.po_header_id = p_po_header_id_csr
2180 AND pll.line_location_id = p_line_location_id_csr
2181 AND pll.po_line_id = pl.po_line_id
2182 AND pcr2.document_header_id = p_po_header_id_csr
2183 AND pcr2.parent_line_location_id = pll.line_location_id
2184 AND pcr2.action_type = 'MODIFICATION'
2185 AND pcr2.request_status = req_status
2186 AND pcr2.request_level = 'SHIPMENT'
2187 AND pcr2.initiator = req_initiator
2188 AND pcr2.document_shipment_number = p_po_shipment_num
2189 AND pcr.document_line_id(+) = pl.po_line_id
2190 AND pcr.action_type(+) = 'MODIFICATION'
2191 AND pcr.request_status(+) = req_status
2192 AND pcr.request_level (+) = 'LINE'
2193 AND pcr.initiator(+) = req_initiator;
2194
2195 --- picks up new shipment amount for releses
2196 -- old_price is included for price breaks
2197 CURSOR c_new_shipment_amount_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER, p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
2198 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
2199 (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
2200 (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2201 FROM po_change_requests pcr,
2202 po_lines_all pl,
2203 po_line_locations_all pll
2204 WHERE pll.po_header_id = p_po_header_id_csr
2205 AND pll.line_location_id = p_line_location_id_csr
2206 AND pll.po_release_id = p_po_release_id_csr
2207 AND pll.po_line_id = pl.po_line_id
2208 -- AND pcr.po_release_id = p_po_release_id_csr
2209 AND pcr.document_header_id(+) = p_po_header_id_csr
2210 AND pcr.document_line_id = pl.po_line_id
2211 AND pcr.action_type(+) = 'MODIFICATION'
2212 AND pcr.request_status(+) = req_status
2213 AND pcr.request_level (+) = 'SHIPMENT'
2214 AND pcr.initiator(+) = req_initiator
2215 AND pcr.document_line_location_id(+) = pll.line_location_id;
2216
2217 CURSOR c_new_ship_amt_rel_split(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER, p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR,p_po_shipment_num IN NUMBER) IS
2218 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
2219 FROM po_change_requests pcr2, -- for splitted shipments
2220 po_lines_all pl,
2221 po_line_locations_all pll
2222 WHERE pll.po_header_id = p_po_header_id_csr
2223 AND pll.line_location_id = p_line_location_id_csr
2224 AND pll.po_line_id = pl.po_line_id
2225 AND pcr2.po_release_id = p_po_release_id_csr
2226 AND pcr2.document_header_id(+) = p_po_header_id_csr
2227 AND pcr2.document_line_id(+) = pl.po_line_id
2228 AND pcr2.action_type(+) = 'MODIFICATION'
2229 AND pcr2.request_status(+)= req_status
2230 AND pcr2.request_level (+) = 'SHIPMENT'
2231 AND pcr2.initiator(+) = req_initiator
2232 AND pcr2.parent_line_location_id = pll.line_location_id
2233 AND pcr2.document_shipment_number = p_po_shipment_num;
2234
2235 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case)
2236 CURSOR c_new_shipment_amount_cw (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER) IS
2237 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2238 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2239 'AMOUNT',
2240 DECODE(pll.payment_type, 'LUMPSUM',
2241 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2242 'MILESTONE',
2243 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2244 'RATE',
2245 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2246 FROM
2247 po_change_requests pcr, --shipment quantity changes
2248 po_lines_all pl,
2249 po_line_locations_all pll
2250 WHERE
2251 pl.po_header_id = p_po_header_id_csr
2252 AND pll.line_location_id = p_line_location_id_csr
2253 AND pll.po_line_id = pl.po_line_id
2254 AND pcr.document_header_id (+) = p_po_header_id_csr
2255 AND pcr.document_line_location_id(+) = pll.line_location_id
2256 AND pcr.action_type(+) = 'MODIFICATION'
2257 AND pcr.request_status(+) = 'PENDING'
2258 AND pcr.request_level (+) = 'SHIPMENT'
2259 AND pcr.initiator(+) = 'SUPPLIER';
2260
2261 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case) for split cases
2262 CURSOR c_new_shipment_amount_split_cw (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,p_po_shipment_num IN NUMBER) IS
2263 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2264 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2265 'AMOUNT',
2266 DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
2267 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2268 'MILESTONE',
2269 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2270 'RATE',
2271 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2272 FROM
2273 po_change_requests pcr, --shipment quantity changes
2274 po_lines_all pl,
2275 po_line_locations_all pll
2276 WHERE
2277 pl.po_header_id = p_po_header_id_csr
2278 AND pll.line_location_id = p_line_location_id_csr
2279 AND pll.po_line_id = pl.po_line_id
2280 AND pcr.document_header_id = p_po_header_id_csr
2281 AND pcr.parent_line_location_id = pll.line_location_id
2282 AND pcr.action_type = 'MODIFICATION'
2283 AND pcr.request_status = 'PENDING'
2284 AND pcr.request_level = 'SHIPMENT'
2285 AND pcr.initiator = 'SUPPLIER'
2286 AND pcr.document_shipment_number = p_po_shipment_num;
2287
2288 l_new_ship_amt NUMBER;
2289 x_progress VARCHAR2(1000);
2290 req_status po_change_requests.request_status%TYPE;
2291 req_initiator po_change_requests.initiator%TYPE;
2292
2293 BEGIN
2294
2295 IF(p_po_release_id is null) THEN
2296 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2297 where document_header_id=p_po_header_id
2298 and change_active_flag='Y'
2299 and rownum=1;
2300 ELSE
2301 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2302 where document_header_id=p_po_header_id
2303 and po_release_id = p_po_release_id
2304 and change_active_flag='Y'
2305 and rownum=1;
2306 END IF;
2307
2308
2309 IF(p_po_release_id is null) THEN
2310 IF(p_split_flag = 'N') THEN
2311 IF(p_po_style_type = 'NORMAL') THEN
2312 OPEN c_new_shipment_amount(p_po_header_id,p_line_location_id,req_status,req_initiator);
2313 LOOP
2314 FETCH c_new_shipment_amount INTO l_new_ship_amt;
2315 EXIT WHEN c_new_shipment_amount%NOTFOUND;
2316 END LOOP;
2317 CLOSE c_new_shipment_amount;
2318 ELSIF(p_po_style_type = 'COMPLEX') THEN
2319 OPEN c_new_shipment_amount_cw(p_po_header_id,p_line_location_id);
2320 LOOP
2321 FETCH c_new_shipment_amount_cw INTO l_new_ship_amt;
2322 EXIT WHEN c_new_shipment_amount_cw%NOTFOUND;
2323 END LOOP;
2324 CLOSE c_new_shipment_amount_cw;
2325 END IF;
2326 ELSIF(p_split_flag = 'Y') THEN
2327 IF(p_po_style_type = 'NORMAL') THEN
2328 OPEN c_new_shipment_amount_split(p_po_header_id,p_line_location_id,req_status,req_initiator,p_po_shipment_num);
2329 LOOP
2330 FETCH c_new_shipment_amount_split INTO l_new_ship_amt;
2331 EXIT WHEN c_new_shipment_amount_split%NOTFOUND;
2332 END LOOP;
2333 CLOSE c_new_shipment_amount_split;
2334 ELSIF(p_po_style_type = 'COMPLEX') THEN
2335 OPEN c_new_shipment_amount_split_cw(p_po_header_id,p_line_location_id,p_po_shipment_num);
2336 LOOP
2337 FETCH c_new_shipment_amount_split_cw INTO l_new_ship_amt;
2338 EXIT WHEN c_new_shipment_amount_split_cw%NOTFOUND;
2339 END LOOP;
2340 CLOSE c_new_shipment_amount_split_cw;
2341 END IF;
2342 END IF;
2343 ELSIF( p_po_release_id is not null) THEN
2344 IF(p_split_flag = 'N') THEN
2345 OPEN c_new_shipment_amount_rel(p_po_header_id,p_po_release_id,p_line_location_id,req_status,req_initiator);
2346 LOOP
2347 FETCH c_new_shipment_amount_rel INTO l_new_ship_amt;
2348 EXIT WHEN c_new_shipment_amount_rel%NOTFOUND;
2349 END LOOP;
2350 CLOSE c_new_shipment_amount_rel;
2351 ELSIF (p_split_flag = 'Y') THEN
2352 OPEN c_new_ship_amt_rel_split(p_po_header_id,p_po_release_id,p_line_location_id,req_status,req_initiator,p_po_shipment_num);
2353 LOOP
2354 FETCH c_new_ship_amt_rel_split INTO l_new_ship_amt;
2355 EXIT WHEN c_new_ship_amt_rel_split%NOTFOUND;
2356 END LOOP;
2357 CLOSE c_new_ship_amt_rel_split;
2358 END IF;
2359 END IF;
2360
2361 return l_new_ship_amt;
2362
2363 EXCEPTION
2364 WHEN OTHERS THEN
2365 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2366 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2367 g_module_prefix,
2368 x_progress || ':unexpected error' || Sqlerrm);
2369 END IF;
2370 raise;
2371 END CALCULATE_NEW_SHIP_AMOUNT;
2372
2373 FUNCTION CHANGE_WITHIN_TOL( p_oldValue IN NUMBER,
2374 p_newValue IN NUMBER,
2375 p_maxIncrement_per IN NUMBER,
2376 p_maxDecrement_per IN NUMBER,
2377 p_maxIncrement_val IN NUMBER,
2378 p_maxDecrement_val IN NUMBER)
2379 RETURN boolean
2380
2381 IS
2382
2383 l_changePercent NUMBER;
2384 l_changeValue NUMBER;
2385 x_progress varchar2(1000);
2386
2387 BEGIN
2388
2389 x_progress := 'CHANGE_WITHIN_TOL:000';
2390 -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2391 IF((p_oldValue > 0) AND (p_newValue > 0)) THEN
2392 IF((p_newValue > p_oldValue) AND (nvl(p_maxIncrement_per,0) = 0 AND nvl(p_maxIncrement_val,0) = 0)) THEN
2393 return FALSE;
2394 ELSIF((p_newValue < p_oldValue) AND (nvl(p_maxDecrement_per,0) = 0 AND nvl(p_maxDecrement_val,0) = 0)) THEN
2395 return FALSE;
2396 END IF;
2397 END IF;
2398 x_progress := 'CHANGE_WITHIN_TOL:001';
2399 IF (p_oldValue <> p_newValue) THEN
2400 IF (p_oldValue > 0 AND p_newValue > 0) THEN
2401 --- Checking for the Value change
2402 l_changeValue := abs(p_oldValue - p_newValue);
2403 x_progress := 'CHANGE_WITHIN_TOL:002';
2404 -- value has increased
2405 IF (p_maxIncrement_val <> 0 AND p_oldValue < p_newValue) THEN
2406 IF(l_changeValue > p_maxIncrement_val) THEN
2407 return FALSE;
2408 END IF;
2409 END IF;
2410 -- value has decreased
2411 IF (p_maxDecrement_val <> 0 AND p_oldValue > p_newValue) THEN
2412 IF(l_changeValue > p_maxDecrement_val) THEN
2413 return FALSE;
2414 END IF;
2415 END IF;
2416 --- Checking for the percentage change
2417 l_changePercent := ((p_oldValue - p_newValue)/p_oldValue)*100;
2418 x_progress := 'CHANGE_WITHIN_TOL:003';
2419 -- value has increased
2420 IF (p_maxIncrement_per <> 0 AND p_oldValue < p_newValue) THEN
2421 IF((abs(l_changePercent)) > p_maxIncrement_per) THEN
2422 return FALSE;
2423 END IF;
2424 END IF;
2425 -- value has decreased
2426 IF (p_maxDecrement_per <> 0 AND p_oldValue > p_newValue) THEN
2427 IF(l_changePercent > p_maxDecrement_per) THEN
2428 return FALSE;
2429 END IF;
2430 END IF;
2431 END IF;
2432 END IF;
2433
2434 return TRUE;
2435
2436 EXCEPTION
2437 WHEN OTHERS THEN
2438 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2439 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2440 g_module_prefix,
2441 x_progress || ':unexpected error' || Sqlerrm);
2442 END IF;
2443 raise;
2444
2445 END CHANGE_WITHIN_TOL;
2446 ------------------------------------------------------------------------------
2447 FUNCTION CHANGE_WITHIN_TOL_DATE(p_oldValue IN DATE,
2448 p_newValue IN DATE,
2449 p_maxIncrement IN NUMBER,
2450 p_maxDecrement IN NUMBER)
2451 RETURN boolean
2452
2453 IS
2454 x_progress varchar2(1000);
2455 BEGIN
2456
2457 x_progress := 'CHANGE_WITHIN_TOL_DATE:000';
2458 -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2459 IF( (p_newValue > p_oldValue) AND (nvl(p_maxIncrement,0) = 0)) THEN
2460 return FALSE;
2461 ELSIF( (p_newValue < p_oldValue) AND(nvl(p_maxDecrement,0) = 0)) THEN
2462 return FALSE;
2463 END IF;
2464
2465 x_progress := 'CHANGE_WITHIN_TOL_DATE:001';
2466
2467 IF(p_oldValue <> p_newValue) THEN
2468
2469 -- check for upper tol
2470 IF (p_maxIncrement <> 0 AND p_oldValue < p_newValue)THEN
2471 IF(p_newValue - p_maxIncrement > p_oldValue) THEN
2472 return FALSE;
2473 END IF;
2474 END IF;
2475
2476 -- check for lower tol
2477 IF(p_maxDecrement <> 0 AND p_oldValue > p_newValue) THEN
2478 IF(p_newValue + p_maxDecrement < p_oldValue) THEN
2479 return FALSE;
2480 END IF;
2481 END IF;
2482 END IF;
2483
2484 x_progress := 'CHANGE_WITHIN_TOL_DATE:002';
2485
2486 return TRUE;
2487 EXCEPTION
2488 WHEN OTHERS THEN
2489 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2490 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2491 g_module_prefix,
2492 x_progress || ':unexpected error' || Sqlerrm);
2493 END IF;
2494 raise;
2495
2496
2497 END CHANGE_WITHIN_TOL_DATE;
2498
2499 PROCEDURE ROUTE_TO_REQUESTER( itemtype IN VARCHAR2,
2500 itemkey IN VARCHAR2,
2501 actid IN NUMBER,
2502 funcmode IN VARCHAR2,
2503 resultout OUT NOCOPY VARCHAR2)
2504
2505 IS
2506 l_return_val VARCHAR2(1) ;
2507 l_po_header_id po_headers_all.po_header_id%TYPE;
2508 l_change_group_id po_change_requests.change_request_group_id%type;
2509 x_progress VARCHAR2(1000);
2510 l_po_style_type VARCHAR2(10);
2511 l_doc_type VARCHAR2(10);
2512 l_doc_subtype VARCHAR2(10);
2513 l_prmdate_app_flag VARCHAR2(10);
2514 l_shi_qty_app_flag VARCHAR2(10);
2515 l_unit_price_app_flag VARCHAR2(10);
2516
2517 BEGIN
2518 x_progress := 'ROUTE_TO_REQUESTER:000';
2519 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2520 itemkey => itemkey,
2521 aname => 'PO_HEADER_ID');
2522
2523 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2524 itemkey => itemkey,
2525 aname => 'CHANGE_REQUEST_GROUP_ID');
2526
2527 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2528 itemkey => itemkey,
2529 aname => 'PO_STYLE_TYPE');
2530
2531 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2532 itemkey => itemkey,
2533 aname => 'DOCUMENT_TYPE');
2534
2535 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2536 itemkey => itemkey,
2537 aname => 'DOC_SUB_TYPE');
2538
2539 l_prmdate_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2540 itemkey => itemkey,
2541 aname => 'PROMISE_DATE_APP_FLAG');
2542
2543 l_shi_qty_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2544 itemkey => itemkey,
2545 aname => 'SHIP_QTY_APPROVAL_FLAG');
2546
2547 l_unit_price_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2548 itemkey => itemkey,
2549 aname => 'PRICE_APPROVAL_FLAG');
2550
2551 x_progress := 'ROUTE_TO_REQUESTER:001';
2552
2553 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2554
2555
2556 IF ( ROUTETOREQUESTER ( l_po_header_id,l_change_group_id,l_doc_type,l_prmdate_app_flag,l_shi_qty_app_flag,l_unit_price_app_flag)=TRUE) THEN
2557 l_return_val:= 'Y' ;
2558 x_progress := 'ROUTE_TO_REQUESTER:002';
2559 ELSE
2560 -- send the notification to buyer
2561 wf_engine.SetItemAttrText(itemtype => itemtype,
2562 itemkey => itemkey,
2563 aname => 'NOTIF_USAGE',
2564 avalue => 'BUYER');
2565 l_return_val:= 'N' ;
2566 x_progress := 'ROUTE_TO_REQUESTER:003';
2567 END IF;
2568
2569 ELSE
2570 wf_engine.SetItemAttrText(itemtype => itemtype,
2571 itemkey => itemkey,
2572 aname => 'NOTIF_USAGE',
2573 avalue => 'BUYER');
2574 l_return_val:= 'N' ;
2575 x_progress := 'ROUTE_TO_REQUESTER:004';
2576 END IF;
2577 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2578 x_progress := 'ROUTE_TO_REQUESTER:005';
2579 log_message('ROUTE_TO_REQUESTER','Result',resultout);
2580 EXCEPTION
2581 WHEN OTHERS THEN
2582 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2583 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2584 g_module_prefix,
2585 x_progress || ':unexpected error' || Sqlerrm);
2586 END IF;
2587 wf_core.context('POSCHORD', 'ROUTE_TO_REQUESTER', itemtype, itemkey, to_char(actid),funcmode);
2588 raise;
2589 END ROUTE_TO_REQUESTER;
2590
2591
2592 FUNCTION ROUTETOREQUESTER (p_po_header_id IN NUMBER ,p_change_group_id IN NUMBER, p_doc_type IN VARCHAR2, p_prm_date_app_flag IN VARCHAR2, p_ship_qty_app_flag IN VARCHAR2, p_unit_price_app_flag IN VARCHAR2)
2593 return boolean is
2594
2595 cursor c_promise_date_changed (p_po_header_id_csr_pd IN NUMBER,p_change_group_id_csr_pd IN NUMBER) is
2596 select count(1) from po_change_requests pcr
2597 where pcr.document_header_id=p_po_header_id_csr_pd
2598 AND pcr.change_request_group_id=p_change_group_id_csr_pd
2599 AND pcr.new_promised_date IS NOT NULL
2600 AND pcr.action_type='MODIFICATION'
2601 AND pcr.request_status= 'PENDING'
2602 AND pcr.request_level= 'SHIPMENT'
2603 AND pcr.initiator= 'SUPPLIER';
2604
2605
2606
2607 cursor c_ship_qty_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2608 select count(1) from po_change_requests pcr
2609 where pcr.document_header_id=p_po_header_id_csr
2610 AND pcr.change_request_group_id=p_change_group_id_csr
2611 AND pcr.new_quantity IS NOT NULL
2612 AND pcr.action_type='MODIFICATION'
2613 AND pcr.request_status= 'PENDING'
2614 AND pcr.request_level= 'SHIPMENT'
2615 AND pcr.initiator= 'SUPPLIER';
2616
2617
2618 cursor c_price_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2619 select count(1) from po_change_requests pcr
2620 where pcr.document_header_id=p_po_header_id_csr
2621 AND pcr.change_request_group_id=p_change_group_id_csr
2622 AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2623 AND pcr.action_type='MODIFICATION'
2624 AND pcr.request_status= 'PENDING'
2625 AND pcr.request_level IN ('LINE','SHIPMENT')
2626 AND pcr.initiator= 'SUPPLIER';
2627
2628 cursor c_price_changed_rel(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2629 select count(1) from po_change_requests pcr
2630 where pcr.document_header_id = p_po_header_id_csr
2631 AND pcr.change_request_group_id=p_change_group_id_csr
2632 AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2633 AND pcr.action_type='MODIFICATION'
2634 AND pcr.request_status= 'PENDING'
2635 AND pcr.request_level= 'SHIPMENT'
2636 AND pcr.initiator= 'SUPPLIER';
2637
2638 l_progress varchar2(50) := '000';
2639 l_promise_date_changed number;
2640 l_ship_qty_changed number;
2641 l_price_changed number;
2642 l_ret_val varchar2(10) := 'N';
2643 l_api_name varchar2(50) := 'ROUTE_TO_REQUESTER';
2644 x_progress varchar2(1000);
2645
2646
2647 BEGIN
2648 x_progress := 'ROUTETOREQUESTER:000';
2649 -- Checking whether there is a promise data change
2650 OPEN c_promise_date_changed(p_po_header_id ,p_change_group_id ) ;
2651 FETCH c_promise_date_changed INTO l_promise_date_changed;
2652 IF ( l_promise_date_changed >= 1) THEN
2653 CLOSE c_promise_date_changed;
2654 x_progress := 'ROUTETOREQUESTER:001';
2655 log_message('ROUTETOREQUESTER','Promise Date Changed','Yes');
2656 IF( p_prm_date_app_flag = 'Y') THEN
2657 l_ret_val := 'Y';
2658 ELSE
2659 l_ret_val := 'N';
2660 return FALSE;
2661 END IF;
2662 END IF;
2663 IF c_promise_date_changed%ISOPEN THEN
2664 CLOSE c_promise_date_changed;
2665 END IF;
2666
2667 -- Checking whether there is a Shipment quantity change
2668
2669 OPEN c_ship_qty_changed(p_po_header_id ,p_change_group_id );
2670 FETCH c_ship_qty_changed INTO l_ship_qty_changed;
2671 IF (l_ship_qty_changed >= 1) THEN
2672 CLOSE c_ship_qty_changed;
2673 x_progress := 'ROUTETOREQUESTER:002';
2674 log_message('ROUTETOREQUESTER','Ship Qty changed','Yes');
2675 IF( p_ship_qty_app_flag = 'Y') THEN
2676 l_ret_val := 'Y';
2677 ELSE
2678 l_ret_val := 'N';
2679 return FALSE;
2680 END IF;
2681 END IF;
2682 IF c_ship_qty_changed%ISOPEN THEN
2683 CLOSE c_ship_qty_changed;
2684 END IF;
2685
2686 -- Checking whether there is a Line price change
2687
2688 IF ( p_doc_type = 'PO') THEN
2689 OPEN c_price_changed(p_po_header_id ,p_change_group_id );
2690 FETCH c_price_changed INTO l_price_changed;
2691 IF(l_price_changed >= 1) THEN
2692 CLOSE c_price_changed;
2693 x_progress := 'ROUTETOREQUESTER:003';
2694 log_message('ROUTETOREQUESTER','Line Price Changed','Yes');
2695 IF( p_unit_price_app_flag = 'Y') THEN
2696 l_ret_val := 'Y';
2697 ELSE
2698 l_ret_val := 'N';
2699 return FALSE;
2700 END IF;
2701 END IF;
2702 IF c_price_changed%ISOPEN THEN
2703 CLOSE c_price_changed;
2704 END IF;
2705
2706 ELSIF( p_doc_type = 'RELEASE') THEN
2707 OPEN c_price_changed_rel(p_po_header_id ,p_change_group_id );
2708 FETCH c_price_changed_rel INTO l_price_changed;
2709 IF(l_price_changed >= 1) THEN
2710 CLOSE c_price_changed_rel;
2711 x_progress := 'ROUTETOREQUESTER:004';
2712 log_message('ROUTETOREQUESTER','Shipment Price Changed','Yes');
2713 IF(p_unit_price_app_flag = 'Y') THEN
2714 l_ret_val := 'Y';
2715 ELSE
2716 l_ret_val := 'N';
2717 return FALSE;
2718 END IF;
2719 END IF;
2720 IF c_price_changed_rel%ISOPEN THEN
2721 CLOSE c_price_changed_rel;
2722 END IF;
2723 END IF;
2724
2725 x_progress := 'ROUTETOREQUESTER:005';
2726 IF( l_ret_val = 'Y') THEN
2727 return TRUE;
2728 ELSIF(l_ret_val = 'N') THEN
2729 return FALSE;
2730 END IF;
2731
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2735 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2736 g_module_prefix,
2737 x_progress || ':unexpected error' || Sqlerrm);
2738 END IF;
2739 raise;
2740 END ROUTETOREQUESTER;
2741
2742
2743
2744
2745 PROCEDURE ROUTE_SCO_BIZ_RULES( itemtype IN VARCHAR2,
2746 itemkey IN VARCHAR2,
2747 actid IN NUMBER,
2748 funcmode IN VARCHAR2,
2749 resultout OUT NOCOPY VARCHAR2)
2750
2751 IS
2752
2753 -- curosr to get the ReqHeaderId
2754 cursor c_getReqHdrId(p_po_header_id_csr IN NUMBER) is
2755 select distinct porh.requisition_header_id
2756 from po_requisition_headers_all porh,
2757 po_requisition_lines_all porl,
2758 po_headers_all poh,
2759 po_line_locations_all poll
2760 where porh.requisition_header_id = porl.requisition_header_id AND
2761 porl.line_location_id = poll.line_location_id AND
2762 poh.po_header_id = poll.po_header_id AND
2763 poh.po_header_id = p_po_header_id_csr;
2764
2765 cursor c_getReqHdrId_r(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
2766 select distinct porh.requisition_header_id
2767 from po_requisition_headers_all porh,
2768 po_requisition_lines_all porl,
2769 po_headers_all poh,
2770 po_line_locations_all poll
2771 where porh.requisition_header_id = porl.requisition_header_id AND
2772 porl.line_location_id = poll.line_location_id AND
2773 poh.po_header_id = poll.po_header_id AND
2774 poh.po_header_id = p_po_header_id_csr AND
2775 poll.po_release_id = p_po_release_id_csr;
2776
2777 l_po_header_id po_headers_all.po_header_id%TYPE;
2778 l_po_release_id po_releases_all.po_release_id%TYPE;
2779 l_doc_type VARCHAR2(10);
2780 l_return_val VARCHAR2(1);
2781 l_change_group_id po_change_requests.change_request_group_id%TYPE;
2782 l_req_hdr_id po_requisition_headers_all.requisition_header_id%TYPE;
2783 x_progress VARCHAR2(1000);
2784 l_auto_app_flag VARCHAR2(1);
2785
2786 BEGIN
2787
2788 x_progress := 'ROUTE_SCO_BIZ_RULES:000';
2789
2790 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2791 itemkey => itemkey,
2792 aname => 'PO_HEADER_ID');
2793
2794 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2795 itemkey => itemkey,
2796 aname => 'PO_RELEASE_ID');
2797
2798 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2799 itemkey => itemkey,
2800 aname => 'DOCUMENT_TYPE');
2801
2802 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2803 itemkey => itemkey,
2804 aname => 'CHANGE_REQUEST_GROUP_ID');
2805
2806 l_auto_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2807 itemkey => itemkey,
2808 aname => 'AUTO_APP_BIZ_RULES_FLAG');
2809
2810 x_progress := 'ROUTE_SCO_BIZ_RULES:001';
2811
2812 IF(l_auto_app_flag = 'Y') THEN
2813
2814 IF (ROUTE_SCO_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type,l_change_group_id)=FALSE) THEN
2815 l_return_val:= 'N';
2816 x_progress := 'ROUTE_SCO_BIZ_RULES:002';
2817 -- Set the Notif Usage for the notification to be sent to Buyer
2818 wf_engine.SetItemAttrText (itemtype => itemtype,
2819 itemkey => itemkey,
2820 aname => 'NOTIF_USAGE',
2821 avalue =>'BUYER');
2822 ELSE
2823 l_return_val:= 'Y';
2824 x_progress := 'ROUTE_SCO_BIZ_RULES:003';
2825
2826 IF( PROMISEDATECHANGE(l_po_header_id,l_change_group_id) = FALSE) THEN -- lock the corresponding req if there are price or quantity changes
2827
2828 if l_po_release_id is not null then
2829 OPEN c_getReqHdrId_r(l_po_header_id,l_po_release_id);
2830 LOOP
2831 FETCH c_getReqHdrId_r INTO l_req_hdr_id;
2832 EXIT WHEN c_getReqHdrId_r%NOTFOUND;
2833 x_progress:= 'ROUTE_SCO_BIZ_RULES:004';
2834 log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2835
2836 update po_requisition_headers_all
2837 set change_pending_flag = 'Y'
2838 where requisition_header_id = l_req_hdr_id;
2839
2840 END LOOP;
2841 CLOSE c_getReqHdrId_r;
2842
2843 else
2844 OPEN c_getReqHdrId(l_po_header_id);
2845 LOOP
2846 FETCH c_getReqHdrId INTO l_req_hdr_id;
2847 EXIT WHEN c_getReqHdrId%NOTFOUND;
2848 x_progress:= 'ROUTE_SCO_BIZ_RULES:004a';
2849 log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2850
2851 update po_requisition_headers_all
2852 set change_pending_flag = 'Y'
2853 where requisition_header_id = l_req_hdr_id;
2854
2855 END LOOP;
2856 CLOSE c_getReqHdrId;
2857 end if;
2858
2859 END IF;
2860
2861 END IF;
2862
2863 ELSIF( l_auto_app_flag = 'N') THEN
2864 l_return_val:= 'N';
2865 -- Set the Notif Usage for the notification to be sent to Buyer
2866 wf_engine.SetItemAttrText( itemtype => itemtype,
2867 itemkey => itemkey,
2868 aname => 'NOTIF_USAGE',
2869 avalue =>'BUYER');
2870 END IF;
2871
2872
2873 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2874
2875 x_progress := 'ROUTE_SCO_BIZ_RULES:005';
2876 log_message('ROUTE_SCO_BIZ_RULES','Result',resultout);
2877
2878
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2882 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2883 g_module_prefix,
2884 x_progress || ':unexpected error' || Sqlerrm);
2885 END IF;
2886 wf_core.context('POSCHORD', 'ROUTE_SCO_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
2887 raise;
2888 END ROUTE_SCO_BIZ_RULES;
2889
2890
2891 PROCEDURE AUTO_APP_BIZ_RULES(itemtype IN VARCHAR2,
2892 itemkey IN VARCHAR2,
2893 actid IN NUMBER,
2894 funcmode IN VARCHAR2,
2895 resultout OUT NOCOPY VARCHAR2)
2896
2897 IS
2898
2899 l_po_header_id po_headers_all.po_header_id%TYPE;
2900 l_po_release_id po_releases_all.po_release_id%TYPE;
2901 l_return_val VARCHAR2(1);
2902 x_progress VARCHAR2(1000);
2903 l_po_style_type VARCHAR2(10);
2904 l_doc_type VARCHAR2(10);
2905 l_doc_subtype VARCHAR2(10);
2906
2907 BEGIN
2908 x_progress := 'AUTO_APP_BIZ_RULES:000';
2909
2910 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2911 itemkey => itemkey,
2912 aname => 'PO_HEADER_ID');
2913
2914 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2915 itemkey => itemkey,
2916 aname => 'PO_RELEASE_ID');
2917
2918 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2919 itemkey => itemkey,
2920 aname => 'PO_STYLE_TYPE');
2921
2922 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2923 itemkey => itemkey,
2924 aname => 'DOCUMENT_TYPE');
2925
2926 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2927 itemkey => itemkey,
2928 aname => 'DOC_SUB_TYPE');
2929
2930 x_progress := 'AUTO_APP_BIZ_RULES:001';
2931
2932 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2933 IF ( AUTO_APP_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type)=FALSE) THEN
2934
2935 wf_engine.SetItemAttrText( itemtype => itemtype,
2936 itemkey => itemkey,
2937 aname => 'NOTIF_USAGE',
2938 avalue => 'BUYER');
2939
2940 wf_engine.SetItemAttrText( itemtype => itemtype,
2941 itemkey => itemkey,
2942 aname => 'AUTO_APP_BIZ_RULES_FLAG',
2943 avalue => 'N');
2944
2945 x_progress := 'AUTO_APP_BIZ_RULES:002';
2946
2947 l_return_val:= 'N';
2948 ELSE
2949 wf_engine.SetItemAttrText( itemtype => itemtype,
2950 itemkey => itemkey,
2951 aname => 'AUTO_APP_BIZ_RULES_FLAG',
2952 avalue => 'Y');
2953
2954 l_return_val:= 'Y';
2955
2956 END IF;
2957 ELSE
2958 l_return_val := 'N';
2959 END IF;
2960
2961 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2962 x_progress := 'AUTO_APP_BIZ_RULES:003';
2963
2964 log_message('AUTO_APP_BIZ_RULES','Result',resultout);
2965
2966 EXCEPTION
2967 WHEN OTHERS THEN
2968 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2969 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2970 g_module_prefix,
2971 x_progress || ':unexpected error' || Sqlerrm);
2972 END IF;
2973 wf_core.context('POSCHORD', 'AUTO_APP_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
2974 raise;
2975 END AUTO_APP_BIZ_RULES;
2976
2977
2978 FUNCTION ROUTE_SCO_BIZ_RULES_CHECK (p_po_header_id IN NUMBER,p_po_release_id IN NUMBER,p_doc_type IN VARCHAR2, p_change_group_id IN NUMBER)
2979 RETURN BOOLEAN
2980 IS
2981
2982 -- Cursor for Checking for the multipe backing reqs is mapped to the PO
2983
2984 CURSOR c_reqs_count(p_po_header_id_csr IN NUMBER) is
2985 SELECT count(distinct porl.requisition_header_id)
2986 FROM po_requisition_lines_all porl, -- <Shared Proc FPJ>
2987 po_line_locations_all poll
2988 WHERE poll.line_location_id = porl.line_location_id AND
2989 NVL(poll.cancel_flag, 'N') = 'N' AND
2990 NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
2991 poll.shipment_type IN('STANDARD', 'BLANKET')
2992 AND poll.po_header_id=p_po_header_id_csr;
2993
2994 -- Cursor for Checking for the multipe backing reqs is mapped to the releases
2995
2996 CURSOR c_reqs_count_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
2997 SELECT count(distinct porl.requisition_header_id)
2998 FROM po_requisition_lines_all porl, -- <Shared Proc FPJ>
2999 po_line_locations_all poll
3000 WHERE poll.line_location_id = porl.line_location_id AND
3001 NVL(poll.cancel_flag, 'N') = 'N' AND
3002 NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
3003 poll.shipment_type IN('STANDARD', 'BLANKET')
3004 AND poll.po_header_id=p_po_header_id_csr
3005 AND poll.po_release_id = p_po_release_id_csr;
3006
3007
3008 -- Cursor for checking all shipments should be mappped to one req.
3009
3010 CURSOR c_req_map_ship(p_po_header_id_csr IN NUMBER) is
3011 SELECT count(1)
3012 FROM po_line_locations_all plla
3013 WHERE plla.po_header_id = p_po_header_id_csr
3014 AND plla.line_location_id NOT IN (SELECT plla2.line_location_id
3015 FROM po_requisition_lines_all porla,
3016 po_line_locations_all plla2
3017 WHERE plla2.po_header_id = p_po_header_id_csr
3018 AND porla.line_location_id = plla2.line_location_id);
3019
3020
3021 -- Cursor for checking all shipments should be mappped to one req for the releases
3022 CURSOR c_req_map_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
3023 SELECT count(1)
3024 FROM po_line_locations_all plla
3025 WHERE plla.po_header_id = p_po_header_id_csr
3026 AND plla.po_release_id = p_po_release_id_csr
3027 AND plla.line_location_id NOT IN ( SELECT plla2.line_location_id
3028 FROM po_requisition_lines_all porla,
3029 po_line_locations_all plla2
3030 WHERE plla2.po_header_id = p_po_header_id_csr
3031 AND plla2.po_release_id = p_po_release_id_csr
3032 AND porla.line_location_id = plla2.line_location_id);
3033
3034
3035 -- Cursor for checking whether there is a price change for Catalog item.
3036
3037 cursor c_cat_price_change(p_po_header_id_csr IN NUMBER) is
3038 select count(1)
3039 from po_change_requests pcr,
3040 po_requisition_lines_all porl1,
3041 po_line_locations_all pll
3042 where pcr.document_header_id = p_po_header_id_csr
3043 AND pcr.REQUEST_LEVEL = 'LINE'
3044 AND pcr.new_price is not NULL
3045 AND pcr.request_status = 'PENDING'
3046 AND porl1.line_location_id = pll.line_location_id
3047 AND pcr.document_line_id = pll.po_line_id
3048 AND porl1.item_id is not null;
3049
3050
3051 -- Curosr for getting the Releases Price changes for Catlog request should go to the buyer
3052 cursor c_ship_price_change(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3053 select count(1)
3054 from po_change_requests pcr,
3055 po_requisition_lines_all porl1,
3056 po_line_locations_all pll
3057 where pcr.document_header_id = p_po_header_id_csr
3058 AND pcr.po_release_id = p_po_release_id_csr
3059 AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3060 AND nvl(pcr.new_price,pcr.new_amount) is not NULL -- NEW_AMOUNT in case of FPS
3061 AND pcr.request_status = 'PENDING'
3062 AND pcr.document_line_location_id = porl1.line_location_id
3063 AND porl1.line_location_id = pll.line_location_id
3064 AND porl1.item_id is NOT NULL;
3065
3066 -- Cursor for checking the FPS price changes for catalog requests
3067 cursor c_fps_price_change(p_po_header_id_csr IN NUMBER) IS
3068 select count(1)
3069 from po_change_requests pcr,
3070 po_requisition_lines_all porl1,
3071 po_line_locations_all pll
3072 where pcr.document_header_id = p_po_header_id_csr
3073 AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3074 AND pcr.new_amount is not NULL
3075 AND pcr.request_status = 'PENDING'
3076 AND pcr.document_line_location_id = porl1.line_location_id
3077 AND porl1.line_location_id = pll.line_location_id
3078 AND porl1.item_id IS NOT NULL;
3079
3080
3081 -- Check whether that Requisition is Locked or not
3082
3083 cursor c_req_locks(p_po_header_id_csr IN NUMBER) is
3084 select count(1)
3085 from po_requisition_headers_all porh,
3086 po_requisition_lines_all porl,
3087 po_headers_all poh,
3088 po_line_locations_all poll
3089 where porh.requisition_header_id = porl.requisition_header_id AND
3090 porl.line_location_id = poll.line_location_id AND
3091 poh.po_header_id = poll.po_header_id AND
3092 poh.po_header_id = p_po_header_id_csr AND
3093 porh.change_pENDing_flag = 'Y' ;
3094
3095
3096 -- Check whether that Requisition is Locked or not for the releases
3097
3098 cursor c_req_locks_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) is
3099 select count(1)
3100 from po_requisition_headers_all porh,
3101 po_requisition_lines_all porl,
3102 po_headers_all poh,
3103 po_line_locations_all poll
3104 where porh.requisition_header_id = porl.requisition_header_id AND
3105 porl.line_location_id = poll.line_location_id AND
3106 poh.po_header_id = poll.po_header_id AND
3107 poh.po_header_id = p_po_header_id_csr AND
3108 poll.po_release_id = p_po_release_id_csr AND
3109 porh.change_pENDing_flag = 'Y' ;
3110
3111
3112 -- Check whether there is a quantity change from a shipment which is made up of two dIFferent req lines
3113
3114 -- will be taken care of INIsProrateNeeded function
3115
3116 l_backing_req_count number;
3117 l_catp_change_count number;
3118 l_fps_change_count number;
3119 l_ship_change_count number;
3120 l_req_lock_cnt number;
3121 l_temp_line_loc_id number;
3122 l_ship_map number;
3123 l_api_name varchar2(50) := 'ROUTE_SCO_BIZ_RULES_FUNC';
3124 x_progress varchar2(1000);
3125
3126 BEGIN
3127
3128 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:000';
3129 --- Po should have one backing req and all shipments should be mapped to the req line
3130 IF(p_doc_type = 'PO') THEN
3131 OPEN c_reqs_count(p_po_header_id);
3132 FETCH c_reqs_count INTO l_backing_req_count;
3133
3134 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:001';
3135 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3136
3137 IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3138 CLOSE c_reqs_count;
3139 return FALSE;
3140 ELSIF (l_backing_req_count = 1) THEN
3141 OPEN c_req_map_ship(p_po_header_id);
3142 FETCH c_req_map_ship INTO l_ship_map;
3143 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:002';
3144 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3145 IF (l_ship_map >= 1) THEN
3146 CLOSE c_req_map_ship;
3147 return FALSE;
3148 END IF;
3149 CLOSE c_req_map_ship;
3150 END IF;
3151
3152 IF c_req_map_ship%ISOPEN THEN
3153 CLOSE c_req_map_ship;
3154 END IF;
3155 IF c_reqs_count%ISOPEN THEN
3156 CLOSE c_reqs_count;
3157 END IF;
3158
3159 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:003';
3160 ---- Price changes for NCR should go to the buyer
3161
3162 OPEN c_cat_price_change(p_po_header_id);
3163 FETCH c_cat_price_change INTO l_catp_change_count;
3164 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_catp_change_count);
3165 IF (l_catp_change_count >= 1) THEN
3166 CLOSE c_cat_price_change;
3167 return FALSE;
3168 END IF;
3169
3170 IF c_cat_price_change%ISOPEN THEN
3171 CLOSE c_cat_price_change;
3172 END IF;
3173
3174 ---- FPS Price Changes for NCR should go to the buyer
3175 OPEN c_fps_price_change(p_po_header_id);
3176 FETCH c_fps_price_change INTO l_fps_change_count;
3177 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_fps_change_count);
3178 IF (l_fps_change_count >= 1) THEN
3179 CLOSE c_fps_price_change;
3180 return FALSE;
3181 END IF;
3182
3183 IF c_fps_price_change%ISOPEN THEN
3184 CLOSE c_fps_price_change;
3185 END IF;
3186
3187 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:004';
3188 --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3189
3190 OPEN c_req_locks(p_po_header_id);
3191 FETCH c_req_locks INTO l_req_lock_cnt;
3192 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3193
3194 IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3195 IF(l_req_lock_cnt >= 1) THEN
3196 CLOSE c_req_locks;
3197 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3198 return FALSE;
3199 END IF;
3200 END IF;
3201 IF c_req_locks%ISOPEN THEN
3202 CLOSE c_req_locks;
3203 END IF;
3204
3205 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:005';
3206
3207 return TRUE;
3208
3209
3210 ELSIF(p_doc_type = 'RELEASE') THEN
3211 --- Po should have one backing req and all shipments should be mapped to the req line
3212 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:006';
3213 OPEN c_reqs_count_rel(p_po_header_id,p_po_release_id);
3214 FETCH c_reqs_count_rel INTO l_backing_req_count;
3215 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:007';
3216 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3217
3218 IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3219 CLOSE c_reqs_count_rel;
3220 return FALSE;
3221
3222 ELSIF l_backing_req_count = 1 THEN
3223 OPEN c_req_map_ship_rel(p_po_header_id,p_po_release_id);
3224 FETCH c_req_map_ship_rel INTO l_ship_map;
3225 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:008';
3226 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3227 IF l_ship_map >= 1 THEN
3228 CLOSE c_req_map_ship_rel;
3229 return FALSE;
3230 END IF;
3231 CLOSE c_req_map_ship_rel;
3232 END IF;
3233
3234 IF c_req_map_ship_rel%ISOPEN THEN
3235 CLOSE c_req_map_ship_rel;
3236 END IF;
3237 IF c_reqs_count_rel%ISOPEN THEN
3238 CLOSE c_reqs_count_rel;
3239 END IF;
3240
3241 --- Ship Price Changes for the NCR releases should go to the buyer
3242 OPEN c_ship_price_change(p_po_header_id,p_po_release_id);
3243 FETCH c_ship_price_change INTO l_ship_change_count;
3244 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_ship_change_count);
3245 IF (l_ship_change_count >= 1) THEN
3246 CLOSE c_ship_price_change;
3247 return FALSE;
3248 END IF;
3249
3250 IF c_ship_price_change%ISOPEN THEN
3251 CLOSE c_ship_price_change;
3252 END IF;
3253
3254
3255
3256 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:009';
3257 --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3258
3259 OPEN c_req_locks_rel(p_po_header_id,p_po_release_id);
3260 FETCH c_req_locks_rel INTO l_req_lock_cnt;
3261 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3262
3263 IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3264 IF(l_req_lock_cnt >= 1) THEN
3265 CLOSE c_req_locks_rel;
3266 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3267 return FALSE;
3268 END IF;
3269 END IF;
3270 IF c_req_locks_rel%ISOPEN THEN
3271 CLOSE c_req_locks_rel;
3272 END IF;
3273
3274 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:010';
3275
3276 return TRUE;
3277 END IF; -- IF p_doc_type = PO or RELEASE
3278
3279 EXCEPTION
3280 WHEN OTHERS THEN
3281 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3282 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3283 g_module_prefix,
3284 x_progress || ':unexpected error' || Sqlerrm);
3285 END IF;
3286 raise;
3287 END ROUTE_SCO_BIZ_RULES_CHECK;
3288
3289 ------------------------------------------------------------------------------
3290
3291
3292 FUNCTION AUTO_APP_BIZ_RULES_CHECK (p_po_header_id IN NUMBER,p_po_release_id IN NUMBER,p_doc_type IN VARCHAR2)
3293 return boolean
3294
3295 IS
3296
3297 -- Split shipment check
3298
3299 cursor c_split_ships(p_po_header_id_csr IN NUMBER) is
3300 select count(1)
3301 from po_change_requests
3302 where parent_line_location_id is not null
3303 AND action_type = 'MODIFICATION'
3304 AND document_header_id = p_po_header_id_csr
3305 AND request_level = 'SHIPMENT'
3306 AND request_status = 'PENDING';
3307
3308
3309 -- Split shipment check for releses
3310 cursor c_split_ships_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3311 select count(1)
3312 from po_change_requests
3313 where parent_line_location_id is not null
3314 AND action_type = 'MODIFICATION'
3315 AND document_header_id = p_po_header_id_csr
3316 AND po_release_id = p_po_release_id_csr
3317 AND request_level = 'SHIPMENT'
3318 AND request_status = 'PENDING';
3319
3320 -- Cancellation request should go to buyer
3321
3322 cursor c_cancel_requests(p_po_header_id_csr IN NUMBER) is
3323 SELECT count(1)
3324 from po_change_requests
3325 where action_type = 'CANCELLATION'
3326 AND request_status = 'PENDING'
3327 AND document_header_id = p_po_header_id_csr;
3328
3329
3330 -- Cancellation request should go to buyer for the releases
3331 cursor c_cancel_requests_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3332 SELECT count(1)
3333 from po_change_requests
3334 where action_type = 'CANCELLATION'
3335 AND request_status = 'PENDING'
3336 AND document_header_id = p_po_header_id_csr
3337 AND po_release_id = p_po_release_id_csr;
3338
3339
3340 --Additional Change Request the unstructured change request
3341
3342 cursor c_add_changes(p_po_header_id_csr IN NUMBER) is
3343 select count(1)
3344 from po_change_requests
3345 where action_type = 'MODIFICATION'
3346 AND ADDITIONAL_CHANGES is not NULL
3347 AND request_status = 'PENDING'
3348 AND document_header_id = p_po_header_id_csr;
3349
3350 --Additional Change Request the unstructured change request for the releases
3351 cursor c_add_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3352 select count(1)
3353 from po_change_requests
3354 where action_type = 'MODIFICATION'
3355 AND ADDITIONAL_CHANGES is not NULL
3356 AND request_level = 'HEADER'
3357 AND request_status = 'PENDING'
3358 AND document_header_id = p_po_header_id_csr
3359 AND po_release_id = p_po_release_id_csr;
3360
3361
3362
3363
3364 -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment
3365 -- Cursor for checking whether sco is created during Acknowledgement or not.
3366 cursor c_sco_ack_ship(p_po_header_id_csr IN NUMBER) is
3367 SELECT acceptance_required_flag,revision_num
3368 FROM po_headers_all
3369 WHERE po_header_id = p_po_header_id_csr;
3370
3371 -- Cursor for checking the acknowledgement status of the shipment
3372 cursor c_sco_ack_rej_ship(p_po_header_id_csr IN NUMBER, c_revision_num IN NUMBER) is
3373 SELECT count(1)
3374 FROM po_acceptances pa,
3375 po_lines_archive_all pla,
3376 po_line_locations_archive_all plla
3377 WHERE plla.po_header_id = p_po_header_id_csr
3378 AND pa.accepted_flag = 'N'
3379 AND plla.po_line_id = pla.po_line_id
3380 AND pa.po_line_location_id = plla.line_location_id
3381 AND pa.revision_num = c_revision_num
3382 AND plla.revision_num = (SELECT max(plla2.revision_num)
3383 FROM po_line_locations_archive_all plla2
3384 WHERE plla2.line_location_id = plla.line_location_id
3385 AND plla.revision_num <= c_revision_num);
3386
3387
3388 -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment for the releases
3389 -- Cursor for checking whether sco is created during Acknowledgement or not.
3390 cursor c_sco_ack_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3391 SELECT acceptance_required_flag,revision_num
3392 FROM po_releases_all
3393 WHERE po_header_id = p_po_header_id_csr
3394 AND po_release_id = p_po_release_id_csr;
3395
3396 -- Cursor for checking the acknowledgement status of the shipment
3397 cursor c_sco_ack_rej_ship_rel(p_po_header_id_csr IN NUMBER, c_revision_num IN NUMBER, p_po_release_id_csr IN NUMBER ) is
3398 SELECT count(1)
3399 FROM po_acceptances pa,
3400 po_lines_archive_all pla,
3401 po_line_locations_archive_all plla
3402 WHERE plla.po_header_id = p_po_header_id_csr
3403 AND pa.po_release_id = p_po_release_id_csr
3404 AND pa.accepted_flag = 'N'
3405 AND plla.po_release_id = pa.po_release_id
3406 AND plla.po_line_id = pla.po_line_id
3407 AND pa.po_line_location_id = plla.line_location_id
3408 AND pa.revision_num = c_revision_num
3409 AND plla.revision_num = (SELECT max(plla2.revision_num)
3410 FROM po_line_locations_archive_all plla2
3411 WHERE plla2.line_location_id = plla.line_location_id
3412 AND plla.revision_num <= c_revision_num);
3413
3414 -- Cursor for checking whether the signature is required or not
3415 /* 5550515
3416 cursor c_sgn_req_flag(p_po_header_id_csr IN NUMBER) IS
3417 SELECT acceptance_required_flag
3418 FROM po_headers_all
3419 WHERE po_header_id = p_po_header_id_csr;
3420 */
3421 cursor c_sgn_req_flag_po(p_po_header_id_csr IN NUMBER,c_revision_num IN NUMBER ) IS
3422 SELECT count(1)
3423 FROM po_acceptances
3424 WHERE po_header_id = p_po_header_id_csr
3425 and revision_num=c_revision_num
3426 and signature_flag='Y';
3427 /*
3428 cursor c_sgn_req_flag_rel(p_po_release_id_csr IN NUMBER, c_revision_num IN NUMBER) IS
3429 SELECT count(1)
3430 FROM po_acceptances
3431 WHERE po_release_id =p_po_release_id_csr
3432 and revision_num=c_revision_num
3433 and signature_flag='Y';
3434 */
3435 -- Cursor for checking for a supplier Item Change
3436 CURSOR c_supp_item_chg(p_po_header_id_csr IN NUMBER) IS
3437 SELECT count(1)
3438 FROM po_change_requests
3439 WHERE action_type = 'MODIFICATION'
3440 AND request_status = 'PENDING'
3441 AND request_level = 'LINE'
3442 AND new_supplier_part_number is not null
3443 AND document_header_id = p_po_header_id_csr;
3444
3445 l_split_shipment_request number;
3446 l_cancel_requests number;
3447 l_additional_change_requests number;
3448 l_ack_reject_cnt number;
3449 l_supp_item_chg_cnt number;
3450 l_temp number;
3451 l_accpt_req_flag po_headers_all.acceptance_required_flag%type;
3452 l_revision_num number;
3453 l_signature_required number :=0;
3454 l_ack_status_flag_count number;
3455 l_api_name varchar2(50) := 'AUTO_APP_BIZ_RULES';
3456 x_progress varchar2(1000);
3457 BEGIN
3458
3459 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:000';
3460
3461 IF(p_doc_type = 'PO') THEN
3462 -- Split shipment check
3463 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:001';
3464 OPEN c_split_ships(p_po_header_id);
3465 FETCH c_split_ships INTO l_split_shipment_request;
3466 log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3467 IF(l_split_shipment_request > 0) THEN
3468 CLOSE c_split_ships;
3469 return FALSE;
3470 END IF;
3471 IF c_split_ships%ISOPEN THEN
3472 CLOSE c_split_ships;
3473 END IF;
3474
3475 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:002';
3476 -- Cancellation request should go to buyer
3477 OPEN c_cancel_requests(p_po_header_id);
3478 FETCH c_cancel_requests INTO l_cancel_requests;
3479 log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3480 IF(l_cancel_requests>0) THEN
3481 CLOSE c_cancel_requests;
3482 return FALSE;
3483 END IF;
3484 IF c_cancel_requests%ISOPEN THEN
3485 CLOSE c_cancel_requests;
3486 END IF;
3487
3488 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:003';
3489 -- additional changes should go to the buyer
3490 OPEN c_add_changes(p_po_header_id);
3491 FETCH c_add_changes INTO l_additional_change_requests;
3492 log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3493 IF(l_additional_change_requests > 0 ) THEN
3494 CLOSE c_add_changes;
3495 return FALSE;
3496 END IF;
3497 IF c_add_changes%ISOPEN THEN
3498 CLOSE c_add_changes;
3499 END IF;
3500
3501 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:004';
3502 --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3503 -- at least one shipment, the SCO should be rOUTed to the buyer.
3504 OPEN c_sco_ack_ship(p_po_header_id);
3505 FETCH c_sco_ack_ship INTO l_accpt_req_flag,l_revision_num;
3506 IF(l_accpt_req_flag = 'Y') THEN
3507 OPEN c_sco_ack_rej_ship(p_po_header_id,l_revision_num);
3508 FETCH c_sco_ack_rej_ship INTO l_ack_status_flag_count;
3509 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3510 IF(l_ack_status_flag_count >= 1) THEN
3511 CLOSE c_sco_ack_rej_ship;
3512 CLOSE c_sco_ack_ship;
3513 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3514 return FALSE;
3515 END IF;
3516 END IF;
3517 IF c_sco_ack_ship%ISOPEN THEN
3518 CLOSE c_sco_ack_ship;
3519 END IF;
3520 IF c_sco_ack_rej_ship%ISOPEN THEN
3521 CLOSE c_sco_ack_rej_ship;
3522 END IF;
3523
3524 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:005';
3525 -- check whether the document requires signature or not
3526 OPEN c_sgn_req_flag_po(p_po_header_id,l_revision_num);
3527 FETCH c_sgn_req_flag_po INTO l_signature_required;
3528 log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3529
3530 IF(l_signature_required >=1) THEN
3531 CLOSE c_sgn_req_flag_po;
3532 return FALSE;
3533 END IF;
3534
3535 IF c_sgn_req_flag_po%ISOPEN THEN
3536 CLOSE c_sgn_req_flag_po;
3537 END IF;
3538
3539 -- check whether the supplier item change is requested or not
3540 OPEN c_supp_item_chg(p_po_header_id);
3541 FETCH c_supp_item_chg INTO l_supp_item_chg_cnt;
3542 log_message('AUTO_APP_BIZ_RULES_CHECK','Supplie Item Chnage ',l_supp_item_chg_cnt);
3543
3544 IF(l_supp_item_chg_cnt > 0) THEN
3545 CLOSE c_supp_item_chg;
3546 return FALSE;
3547 END IF;
3548 IF c_supp_item_chg%ISOPEN THEN
3549 CLOSE c_supp_item_chg;
3550 END IF;
3551
3552 return TRUE;
3553
3554 ELSIF(p_doc_type = 'RELEASE') THEN
3555
3556 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:006';
3557 OPEN c_split_ships_rel(p_po_header_id,p_po_release_id);
3558 FETCH c_split_ships_rel INTO l_split_shipment_request;
3559 log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3560 IF(l_split_shipment_request > 0) THEN
3561 CLOSE c_split_ships_rel;
3562 return FALSE;
3563 END IF;
3564 IF c_split_ships_rel%ISOPEN THEN
3565 CLOSE c_split_ships_rel;
3566 END IF;
3567
3568 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:007';
3569 -- Cancellation request should go to buyer
3570 OPEN c_cancel_requests_rel(p_po_header_id,p_po_release_id);
3571 FETCH c_cancel_requests_rel INTO l_cancel_requests;
3572 log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3573 IF(l_cancel_requests>0) THEN
3574 CLOSE c_cancel_requests_rel;
3575 return FALSE;
3576 END IF;
3577 IF c_cancel_requests_rel%ISOPEN THEN
3578 CLOSE c_cancel_requests_rel;
3579 END IF;
3580
3581 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:008';
3582 -- Additional chnages requested should go to the buyer
3583 OPEN c_add_changes_rel(p_po_header_id,p_po_release_id);
3584 FETCH c_add_changes_rel INTO l_additional_change_requests;
3585 log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3586 IF(l_additional_change_requests > 0 ) THEN
3587 CLOSE c_add_changes_rel;
3588 return FALSE;
3589 END IF;
3590 IF c_add_changes_rel%ISOPEN THEN
3591 CLOSE c_add_changes_rel;
3592 END IF;
3593
3594 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:009';
3595 --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3596 -- at least one shipment, the SCO should be rOUTed to the buyer.
3597 OPEN c_sco_ack_ship_rel(p_po_header_id,p_po_release_id);
3598 FETCH c_sco_ack_ship_rel INTO l_accpt_req_flag,l_revision_num;
3599 IF(l_accpt_req_flag = 'Y') THEN
3600 OPEN c_sco_ack_rej_ship_rel(p_po_header_id,l_revision_num,p_po_release_id);
3601 FETCH c_sco_ack_rej_ship_rel INTO l_ack_status_flag_count;
3602 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3603 IF(l_ack_status_flag_count >= 1) THEN
3604 CLOSE c_sco_ack_rej_ship_rel;
3605 CLOSE c_sco_ack_ship_rel;
3606 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3607 return FALSE;
3608 END IF;
3609 END IF;
3610
3611 IF c_sco_ack_ship_rel%ISOPEN THEN
3612 CLOSE c_sco_ack_ship_rel;
3613 END IF;
3614 IF c_sco_ack_rej_ship_rel%ISOPEN THEN
3615 CLOSE c_sco_ack_rej_ship_rel;
3616 END IF;
3617 /*
3618 -- check whether the document requires signature or not
3619 OPEN c_sgn_req_flag_rel(p_po_release_id,l_revision_num);
3620 FETCH c_sgn_req_flag_rel INTO l_signature_required;
3621
3622 log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3623
3624 IF(l_signature_required <1) THEN
3625 CLOSE c_sgn_req_flag_rel;
3626 return FALSE;
3627 END IF;
3628
3629 IF c_sgn_req_flag_rel%ISOPEN THEN
3630 CLOSE c_sgn_req_flag_rel;
3631 END IF;
3632 */
3633
3634 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:010';
3635 return TRUE;
3636
3637 END IF; -- If po_doc_type = PO or RELEASE
3638
3639 EXCEPTION
3640 WHEN OTHERS THEN
3641 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3642 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3643 g_module_prefix,
3644 x_progress || ':unexpected error' || Sqlerrm);
3645 END IF;
3646 raise;
3647
3648 END AUTO_APP_BIZ_RULES_CHECK;
3649
3650 ------------------------------------------------------------------------------
3651 PROCEDURE PROMISE_DATE_CHANGE(itemtype IN VARCHAR2,
3652 itemkey IN VARCHAR2,
3653 actid IN NUMBER,
3654 funcmode IN VARCHAR2,
3655 resultout OUT NOCOPY VARCHAR2)
3656 IS
3657
3658 l_po_header_id po_headers_all.po_header_id%TYPE;
3659 x_progress VARCHAR2(1000);
3660 l_change_group_id po_change_requests.change_request_group_id%TYPE;
3661
3662 BEGIN
3663
3664
3665 IF (funcmode = 'RUN') THEN
3666
3667 x_progress := 'PROMISE_DATE_CHANGE:000';
3668
3669 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3670 itemkey => itemkey,
3671 aname => 'PO_HEADER_ID');
3672
3673 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3674 itemkey => itemkey,
3675 aname => 'CHANGE_REQUEST_GROUP_ID');
3676 -- IF only a promise date change return yes otherwise no
3677
3678 IF PROMISEDATECHANGE(l_po_header_id,l_change_group_id)=TRUE THEN
3679 resultout := wf_engine.eng_completed || ':' || 'Y';
3680 ELSE
3681 resultout := wf_engine.eng_completed || ':' || 'N';
3682 END IF;
3683 x_progress:= 'PROMISE_DATE_CHANGE:001';
3684 log_message('PROMISE_DATE_CHANGE','Only promised date changed',resultout);
3685 return;
3686 END IF;
3687
3688 EXCEPTION
3689 WHEN OTHERS THEN
3690 -- The line below records this function call INthe error
3691 -- system INthe case of an exception.
3692
3693 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3694 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3695 g_module_prefix,
3696 x_progress || ':unexpected error' || Sqlerrm);
3697 END IF;
3698
3699 wf_core.context('POSCHORD', 'PROMISE_DATE_CHANGE',itemtype, itemkey, to_char(actid),funcmode);
3700
3701 raise;
3702
3703 END PROMISE_DATE_CHANGE;
3704
3705
3706 FUNCTION PROMISEDATECHANGE(p_po_header_id IN NUMBER, p_change_group_id IN NUMBER)
3707 return boolean
3708 is
3709
3710 CURSOR c_shipment_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3711 select OLD_QUANTITY,
3712 NEW_QUANTITY,
3713 OLD_PROMISED_DATE,
3714 NEW_PROMISED_DATE,
3715 OLD_PRICE,
3716 NEW_PRICE,
3717 OLD_AMOUNT,
3718 NEW_AMOUNT
3719 from po_change_requests
3720 where initiator='SUPPLIER'
3721 AND action_type='MODIFICATION'
3722 AND request_level='SHIPMENT'
3723 AND change_request_group_id = l_change_group_id_csr
3724 AND request_status IN('PENDING','BUYER_APP')
3725 AND document_header_id=l_po_header_id
3726 AND ( (nvl(new_promised_date,sysdate) <> nvl(old_promised_date,sysdate-1)) OR
3727 (nvl(new_promised_date,old_promised_date-1)<>old_promised_date) OR
3728 (nvl(old_promised_date,new_promised_date-1)<>new_promised_date)
3729 );
3730
3731 CURSOR c_line_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3732 select count(1)
3733 from po_change_requests
3734 where initiator='SUPPLIER'
3735 AND action_type='MODIFICATION'
3736 AND change_request_group_id = l_change_group_id_csr
3737 AND request_level='LINE'
3738 AND request_status IN('PENDING','BUYER_APP')
3739 AND document_header_id=l_po_header_id;
3740
3741
3742 l_only_promised_date_change boolean :=TRUE;
3743 l_old_quantity po_change_requests.old_quantity%type;
3744 l_new_quantity po_change_requests.new_quantity%type;
3745 l_old_promised_date po_change_requests.old_promised_date%type;
3746 l_new_promised_date po_change_requests.new_promised_date%type;
3747 l_old_price po_change_requests.old_price%type;
3748 l_new_price po_change_requests.new_price%type;
3749 l_old_amount po_change_requests.old_amount%type;
3750 l_new_amount po_change_requests.new_amount%type;
3751 x_progress VARCHAR2(1000);
3752 l_line_changes_counter number:=0;
3753 l_api_name varchar2(50) := 'PROMISEDATECHANGE';
3754
3755
3756 BEGIN
3757
3758 x_progress:='PROMISEDATECHANGE:000';
3759
3760 IF (c_line_change%ISOPEN) THEN
3761 CLOSE c_line_change;
3762 ELSE
3763 OPEN c_line_change(p_po_header_id,p_change_group_id);
3764 END IF;
3765 FETCH c_line_change INTO l_line_changes_counter ;
3766 x_progress:='PROMISEDATECHANGE:001';
3767 log_message('PROMISEDATECHANGE','Price Changes Line Level',l_line_changes_counter);
3768
3769 IF l_line_changes_counter >0 THEN
3770 CLOSE c_line_change;
3771 return FALSE;
3772 END IF;
3773
3774 IF (c_line_change%ISOPEN) THEN
3775 CLOSE c_line_change;
3776 END IF;
3777
3778 x_progress:='PROMISEDATECHANGE:002';
3779
3780 IF (c_shipment_change%ISOPEN) THEN
3781 CLOSE c_shipment_change;
3782 else
3783 OPEN c_shipment_change(p_po_header_id,p_change_group_id);
3784 END IF;
3785 LOOP
3786 FETCH c_shipment_change INTO
3787 l_old_quantity,
3788 l_new_quantity,
3789 l_old_promised_date,
3790 l_new_promised_date,
3791 l_old_price,
3792 l_new_price,
3793 l_old_amount,
3794 l_new_amount;
3795 x_progress:='PROMISEDATECHANGE:003';
3796 log_message('PROMISEDATECHANGE','Quantity Changes',l_old_quantity || ', '||l_new_quantity);
3797 log_message('PROMISEDATECHANGE','Promise date Changes',l_old_promised_date || ', '||l_new_promised_date);
3798 log_message('PROMISEDATECHANGE','Shipment Price Changes',l_old_promised_date || ', '||l_new_promised_date);
3799 -- IF only a promise date change return TRUE otherwise return FALSE
3800 EXIT WHEN c_shipment_change%NOTFOUND;
3801 EXIT WHEN (l_only_promised_date_change=FALSE);
3802
3803 IF nvl(l_old_quantity,0)<>nvl(l_new_quantity,0) THEN
3804 l_only_promised_date_change:= FALSE;
3805 ELSIF nvl(l_old_price,0)<>nvl(l_new_price,nvl(l_old_price,0)) THEN
3806 l_only_promised_date_change:= FALSE;
3807 ELSIF nvl(l_old_amount,0)<>nvl(l_new_amount,0) THEN
3808 l_only_promised_date_change:= FALSE;
3809 ELSIF (l_new_promised_date is null AND l_old_promised_date is null) THEN
3810 l_only_promised_date_change:= FALSE;
3811 END IF;
3812 END LOOP;
3813 CLOSE c_shipment_change;
3814 x_progress:='PROMISEDATECHANGE:004';
3815 return l_only_promised_date_change;
3816 exception
3817 when others THEN
3818 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3819 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3820 g_module_prefix,
3821 x_progress || ':unexpected error' || Sqlerrm);
3822 END IF;
3823 raise;
3824 END PROMISEDATECHANGE;
3825
3826 ------------------------------------------------------------------------------
3827
3828 PROCEDURE INITIATE_RCO_FLOW (itemtype IN VARCHAR2,
3829 itemkey IN VARCHAR2,
3830 actid IN NUMBER,
3831 funcmode IN VARCHAR2,
3832 resultout OUT NOCOPY VARCHAR2)
3833 IS
3834 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
3835 l_po_header_id po_headers_all.po_header_id%type;
3836 l_po_release_id po_releases_all.po_release_id%type;
3837 x_progress VARCHAR2(1000);
3838
3839 BEGIN
3840 x_progress := 'INITIATE_RCO_FLOW:000';
3841 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3842 itemkey => itemkey,
3843 aname => 'PO_HEADER_ID');
3844 l_po_release_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3845 itemkey => itemkey,
3846 aname => 'PO_RELEASE_ID');
3847
3848
3849 x_progress := 'INITIATE_RCO_FLOW:001';
3850 log_message('INITIATE_RCO_FLOW','Po Header Id',l_po_header_id);
3851 INITIATERCOFLOW (l_po_header_id,l_po_release_id,l_change_group_id);
3852 -- set the x_change_request_group_id number
3853 x_progress:= 'INITIATE_RCO_FLOW:002';
3854 log_message('INITIATE_RCO_FLOW','Change Req Group Id',l_change_group_id);
3855
3856 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3857 itemkey => itemkey,
3858 aname => 'REQ_CHANGE_REQUEST_GROUP_ID',
3859 avalue => l_change_group_id);
3860
3861 resultout:=wf_engine.eng_completed;
3862 x_progress := 'INITIATE_RCO_FLOW:003';
3863
3864 EXCEPTION
3865
3866 when others THEN
3867
3868 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3869 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3870 g_module_prefix,
3871 x_progress || ':unexpected error' || Sqlerrm);
3872 END IF;
3873 wf_core.context('POSCHORD', 'INITIATE_RCO_FLOW', itemtype, itemkey, to_char(actid), funcmode);
3874 raise;
3875 END INITIATE_RCO_FLOW;
3876
3877
3878 PROCEDURE INITIATERCOFLOW(p_po_header_id IN NUMBER, p_po_release_id IN NUMBER, x_change_group_id OUT NOCOPY NUMBER)
3879 IS
3880
3881 --- this also should hANDle non sync values
3882 CURSOR c_change_request(p_po_header_id_csr IN NUMBER) IS
3883 SELECT pcr.change_request_group_id,
3884 pcr.new_price new_price,
3885 pcr.new_quantity,
3886 pcr.new_start_date,
3887 pcr.new_expiration_date,
3888 pcr.new_amount,
3889 pcr.request_level,
3890 --pcr.new_promised_date,
3891 nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
3892 pcr.request_reason,
3893 prla.requisition_line_id,
3894 prla.requisition_header_id,
3895 prla.line_location_id,
3896 prda.distribution_id
3897 FROM po_change_requests pcr,
3898 po_requisition_lines_all prla,
3899 po_req_distributions_all prda,
3900 po_line_locations_all plla
3901 WHERE document_header_id= p_po_header_id_csr
3902 AND request_status='BUYER_APP'
3903 AND change_active_flag='Y'
3904 AND initiator='SUPPLIER'
3905 --AND pcr.document_line_location_id = prla.line_location_id(+)
3906 AND prda.requisition_line_id=prla.requisition_line_id
3907 AND pcr.request_level='SHIPMENT'
3908 AND plla.line_location_id = prla.line_location_id
3909 AND plla.po_line_id=pcr.document_line_id
3910 AND plla.po_header_id=pcr.document_header_id
3911 AND ( pcr.new_price is not null --New change JAI
3912 OR pcr.new_quantity is not null
3913 OR pcr.new_start_date is not null
3914 OR pcr.new_expiration_date is not null
3915 OR pcr.new_amount is not null
3916 )
3917 UNION
3918 SELECT pcr.change_request_group_id,
3919 pcr.new_price,
3920 null new_quantity,
3921 null new_start_date ,
3922 null new_expiration_date,
3923 null new_amount,
3924 pcr.request_level,
3925 --null new_promised_date,
3926 null new_need_by_date,
3927 pcr.request_reason,
3928 prla.requisition_line_id,
3929 prla.requisition_header_id,
3930 prla.line_location_id,
3931 null distribution_id
3932 FROM po_change_requests pcr,
3933 po_line_locations_all plla,
3934 po_requisition_lines_all prla
3935 WHERE document_header_id=p_po_header_id_csr
3936 AND request_status='BUYER_APP'
3937 AND change_active_flag='Y'
3938 AND initiator='SUPPLIER'
3939 AND pcr.request_level='LINE'
3940 AND plla.line_location_id = prla.line_location_id
3941 AND plla.po_line_id=pcr.document_line_id
3942 AND plla.po_header_id=pcr.document_header_id;
3943
3944 --for releases
3945
3946 CURSOR c_change_request_rel(p_po_release_id_csr IN NUMBER) IS
3947 SELECT pcr.change_request_group_id,
3948 pcr.new_price new_price,
3949 pcr.new_quantity,
3950 pcr.new_start_date,
3951 pcr.new_expiration_date,
3952 pcr.new_amount,
3953 pcr.request_level,
3954 --pcr.new_promised_date,
3955 nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
3956 pcr.request_reason,
3957 prla.requisition_line_id,
3958 prla.requisition_header_id,
3959 prla.line_location_id,
3960 prda.distribution_id
3961 FROM po_change_requests pcr,
3962 po_requisition_lines_all prla,
3963 po_req_distributions_all prda,
3964 po_line_locations_all plla
3965 WHERE pcr.po_release_id= p_po_release_id_csr
3966 AND request_status='BUYER_APP'
3967 AND change_active_flag='Y'
3968 AND initiator='SUPPLIER'
3969 --AND pcr.document_line_location_id = prla.line_location_id(+)
3970 AND prda.requisition_line_id=prla.requisition_line_id
3971 AND pcr.request_level='SHIPMENT'
3972 AND plla.line_location_id = prla.line_location_id
3973 AND plla.po_line_id=pcr.document_line_id
3974 AND plla.po_header_id=pcr.document_header_id
3975 AND plla.po_release_id =pcr.po_release_id
3976 AND ( pcr.new_price is not null --New change JAI
3977 OR pcr.new_quantity is not null
3978 OR pcr.new_start_date is not null
3979 OR pcr.new_expiration_date is not null
3980 OR pcr.new_amount is not null
3981 )
3982 UNION
3983 SELECT pcr.change_request_group_id,
3984 pcr.new_price,
3985 null new_quantity,
3986 null new_start_date ,
3987 null new_expiration_date,
3988 null new_amount,
3989 pcr.request_level,
3990 --null new_promised_date,
3991 null new_need_by_date,
3992 pcr.request_reason,
3993 prla.requisition_line_id,
3994 prla.requisition_header_id,
3995 prla.line_location_id,
3996 null distribution_id
3997 FROM po_change_requests pcr,
3998 po_line_locations_all plla,
3999 po_requisition_lines_all prla
4000 WHERE pcr.po_release_id=p_po_release_id_csr
4001 AND request_status='BUYER_APP'
4002 AND change_active_flag='Y'
4003 AND initiator='SUPPLIER'
4004 AND pcr.request_level='LINE'
4005 AND plla.line_location_id = prla.line_location_id
4006 AND plla.po_line_id=pcr.document_line_id
4007 AND plla.po_header_id=pcr.document_header_id;
4008
4009
4010
4011 --l_change_group_id po_change_requests.change_request_group_id%type ;
4012 l_change_group_id po_change_requests.change_request_group_id%type ;
4013 l_new_price po_change_requests.new_price%type ;
4014 l_new_quantity po_change_requests.new_quantity%type ;
4015 l_new_start_date po_change_requests.new_start_date%type ;
4016 l_new_expiration_date po_change_requests.new_expiration_date%type ;
4017 l_new_amount po_change_requests.new_amount%type ;
4018 l_request_level po_change_requests.request_level%type ;
4019 l_new_promised_date po_change_requests.new_promised_date%type ;
4020 l_new_need_by_date po_change_requests.new_need_by_date%type ;
4021 l_request_reason po_change_requests.request_reason%type ;
4022 l_requisition_line_id po_requisition_lines_all.requisition_line_id%type ;
4023 l_requisition_header_id po_requisition_lines_all.requisition_header_id%type ;
4024 l_line_location_id po_requisition_lines_all.line_location_id%type ;
4025 l_req_distribution_id po_req_distributions_all.distribution_id%type ;
4026 l_change_table PO_REQ_CHANGE_TABLE;
4027 l_cancel_table PO_REQ_CANCEL_TABLE:=null;
4028 l_rec_count number :=0;
4029 l_req_hdr_id number;
4030 l_api_version number := 1.0;
4031 l_api_name varchar2(100) := 'INITIATERCOFLOW';
4032 x_progress varchar2(1000);
4033 l_po_line_id number;
4034 x_return_status VARCHAR2(10);
4035 x_retMsg VARCHAR2(2000):='';
4036 x_errTable PO_REQ_CHANGE_ERR_TABLE;
4037 x_errCode VARCHAR2(10);
4038 l_dummy_table_number po_tbl_number := po_tbl_number();
4039
4040 BEGIN
4041
4042 x_retMsg :='';
4043 x_progress := 'INITIATERCOFLOW:000';
4044
4045 l_change_table:=PO_REQ_CHANGE_TABLE(
4046 req_line_id => po_tbl_number(),
4047 req_dist_id => po_tbl_number(),
4048 price => po_tbl_number(),
4049 quantity => po_tbl_number(),
4050 need_by => po_tbl_date(),
4051 start_date => po_tbl_date(),
4052 END_date => po_tbl_date(),
4053 amount => po_tbl_number(),
4054 type => po_tbl_varchar60(),
4055 change_reason => po_tbl_VARCHAR2000());
4056
4057 if p_po_release_id is not null then
4058
4059 OPEN c_change_request_rel (p_po_release_id);
4060 l_rec_count :=1;
4061 loop
4062
4063
4064 FETCH c_change_request_rel INTO
4065 l_change_group_id,
4066 l_new_price ,
4067 l_new_quantity,
4068 l_new_start_date,
4069 l_new_expiration_date,
4070 l_new_amount,
4071 l_request_level,
4072 --l_new_promised_date,
4073 l_new_need_by_date,
4074 l_request_reason,
4075 l_requisition_line_id,
4076 l_requisition_header_id,
4077 l_line_location_id,
4078 l_req_distribution_id;
4079
4080
4081 EXIT WHEN c_change_request_rel%NOTFOUND;
4082 x_progress := 'INITIATERCOFLOW:001';
4083
4084 --Filling the table with data
4085
4086 l_change_table.req_line_id.extend(1);
4087 l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4088 l_change_table.req_dist_id.extend(1);
4089 l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4090 l_change_table.price.extend(1);
4091 l_change_table.price(l_rec_count):=l_new_price;
4092 l_change_table.quantity.extend(1);
4093 l_change_table.quantity(l_rec_count):=l_new_quantity;
4094 l_change_table.need_by.extend(1);
4095 l_change_table.start_date.extend(1);
4096 l_change_table.END_date.extend(1);
4097 l_change_table.amount.extend(1);
4098 l_change_table.amount(l_rec_count):=l_new_amount;
4099 l_change_table.type.extend(1);
4100 l_change_table.change_reason.extend(1);
4101 l_change_table.change_reason(l_rec_count):=l_request_reason;
4102
4103 l_rec_count:=l_rec_count+1;
4104
4105 END loop;
4106
4107 CLOSE c_change_request_rel;
4108
4109 else
4110
4111 OPEN c_change_request(p_po_header_id);
4112 l_rec_count :=1;
4113 loop
4114
4115
4116 FETCH c_change_request INTO
4117 l_change_group_id,
4118 l_new_price ,
4119 l_new_quantity,
4120 l_new_start_date,
4121 l_new_expiration_date,
4122 l_new_amount,
4123 l_request_level,
4124 --l_new_promised_date,
4125 l_new_need_by_date,
4126 l_request_reason,
4127 l_requisition_line_id,
4128 l_requisition_header_id,
4129 l_line_location_id,
4130 l_req_distribution_id;
4131
4132
4133 EXIT WHEN c_change_request%NOTFOUND;
4134 x_progress := 'INITIATERCOFLOW:001';
4135
4136 --Filling the table with data
4137
4138 l_change_table.req_line_id.extend(1);
4139 l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4140 l_change_table.req_dist_id.extend(1);
4141 l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4142 l_change_table.price.extend(1);
4143 l_change_table.price(l_rec_count):=l_new_price;
4144 l_change_table.quantity.extend(1);
4145 l_change_table.quantity(l_rec_count):=l_new_quantity;
4146 l_change_table.need_by.extend(1);
4147 l_change_table.start_date.extend(1);
4148 l_change_table.END_date.extend(1);
4149 l_change_table.amount.extend(1);
4150 l_change_table.amount(l_rec_count):=l_new_amount;
4151 l_change_table.type.extend(1);
4152 l_change_table.change_reason.extend(1);
4153 l_change_table.change_reason(l_rec_count):=l_request_reason;
4154
4155 l_rec_count:=l_rec_count+1;
4156
4157 END loop;
4158
4159 CLOSE c_change_request;
4160
4161 end if;
4162
4163 x_progress := 'INITIATERCOFLOW:002 Call Save Req Change';
4164
4165 PO_RCO_VALIDATION_PVT.Save_ReqChange( l_api_version,
4166 x_return_status,
4167 l_requisition_header_id,
4168 l_change_table ,
4169 l_cancel_table ,
4170 x_change_group_id,
4171 x_retMsg ,
4172 x_errTable);
4173
4174 IF x_return_status IS NOT NULL AND x_return_status = FND_API.g_ret_sts_success THEN
4175 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4177 g_module_prefix,
4178 x_progress
4179 || 'x_return_status=' || x_return_status
4180 || 'x_change_group_id = '|| x_change_group_id);
4181 END IF;
4182
4183 ELSE
4184 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4185 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4186 g_module_prefix,
4187 x_progress
4188 ||'x_return_status = ' || x_return_status
4189 ||'x_retMsg = ' || x_retMsg);
4190 END IF;
4191 END IF;
4192
4193 x_progress := 'INITIATERCOFLOW:003';
4194
4195
4196 --- Ip requirement
4197 update PO_CHANGE_REQUESTS
4198 set Parent_change_request_id = x_change_group_id
4199 where change_request_group_id= l_change_group_id;
4200
4201 Exception
4202
4203 when others THEN
4204 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4205 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4206 g_module_prefix,
4207 x_progress || ':unexpected error' || Sqlerrm);
4208 END IF;
4209
4210 raise;
4211
4212
4213 END INITIATERCOFLOW;
4214 ------------------------------------------------------------------------------
4215
4216
4217 PROCEDURE START_RCO_WORKFLOW (itemtype IN VARCHAR2,
4218 itemkey IN VARCHAR2,
4219 actid IN NUMBER,
4220 funcmode IN VARCHAR2,
4221 resultout OUT NOCOPY VARCHAR2) is
4222
4223 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4224 x_progress VARCHAR2(1000);
4225 x_apprv_status VARCHAR2(1);
4226
4227 BEGIN
4228
4229 x_progress := 'START_RCO_WORKFLOW:000';
4230
4231 l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4232 itemkey => itemkey,
4233 aname => 'REQ_CHANGE_REQUEST_GROUP_ID');
4234
4235 x_progress := 'START_RCO_WORKFLOW:001';
4236
4237 /* Call the ip API with change_request_group_id to set the approval_required_flag */
4238
4239 PO_RCOTOLERANCE_GRP.SET_APPROVAL_REQUIRED_FLAG(l_change_group_id,x_apprv_status);
4240
4241
4242 STARTRCOWORKFLOW (l_change_group_id);
4243
4244 resultout:=wf_engine.eng_completed;
4245
4246 x_progress := 'START_RCO_WORKFLOW:002';
4247
4248
4249 exception
4250 when others THEN
4251
4252 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4253 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4254 g_module_prefix,
4255 x_progress || ':unexpected error' || Sqlerrm);
4256 END IF;
4257
4258 -- The line below records this function call INthe error
4259 -- system INthe case of an exception.
4260 wf_core.context('POSCHORD', 'START_RCO_WORKFLOW', itemtype, itemkey, to_char(actid), funcmode);
4261
4262 raise;
4263
4264
4265 END START_RCO_WORKFLOW;
4266 ------------------------------------------------------------------------------
4267
4268 PROCEDURE STARTRCOWORKFLOW(p_change_request_group_id IN NUMBER) is
4269
4270
4271
4272 l_api_version number := 1.0;
4273 l_api_name varchar2(100) := 'STARTRCOWF';
4274 x_return_status VARCHAR2(10);
4275 x_change_request_group_id number;
4276 x_retMsg VARCHAR2(2000):='';
4277 x_errTable PO_REQ_CHANGE_ERR_TABLE;
4278 x_errCode VARCHAR2(10);
4279 x_progress varchar2(1000);
4280 l_dummy_table_number po_tbl_number := po_tbl_number();
4281
4282
4283 BEGIN
4284
4285 x_retMsg :='';
4286 x_progress := 'STARTRCOWORKFLOW:000 Call Submit Req Change';
4287
4288 -- get the x_change_request_group_id
4289
4290 PO_RCO_VALIDATION_PVT.Submit_ReqChange (l_api_version ,
4291 x_return_status,
4292 p_change_request_group_id,--x_change_request_group_id,
4293 'N',-- p_fundscheck_flag IN VARCHAR2,
4294 'Please',
4295 'SUPPLIER',
4296 x_retMsg ,
4297 x_errCode ,
4298 x_errTable );
4299
4300 IF x_return_status IS NOT NULL AND x_return_status = FND_API.g_ret_sts_success THEN
4301 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4302 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4303 g_module_prefix,
4304 x_progress
4305 || ' x_return_status=' || x_return_status);
4306 END IF;
4307
4308 ELSE
4309 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4310 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4311 g_module_prefix,
4312 x_progress
4313 ||' x_return_status = ' || x_return_status
4314 ||' x_retMsg = ' || x_retMsg
4315 ||' x_errCode = ' || x_errCode);
4316 END IF;
4317 END IF;
4318
4319 x_progress := 'STARTRCOWORKFLOW:001';
4320
4321
4322
4323 Exception
4324
4325 when others THEN
4326 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4327 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4328 g_module_prefix,
4329 x_progress || ':unexpected error' || Sqlerrm);
4330 END IF;
4331
4332
4333 raise;
4334
4335
4336 END STARTRCOWORKFLOW;
4337 ------------------------------------------------------------------------------
4338
4339 PROCEDURE MARK_SCO_FOR_REQ(itemtype IN VARCHAR2,
4340 itemkey IN VARCHAR2,
4341 actid IN NUMBER,
4342 funcmode IN VARCHAR2,
4343 resultout OUT NOCOPY VARCHAR2)
4344 IS
4345
4346 CURSOR l_planners_csr(c_po_header_id IN NUMBER)
4347 IS
4348 select porh.PREPARER_ID
4349 from po_requisition_headers_all porh,
4350 po_requisition_lines_all porl,
4351 po_headers_all poh,
4352 po_line_locations_all poll
4353 where porh.requisition_header_id = porl.requisition_header_id AND
4354 porl.line_location_id = poll.line_location_id AND
4355 poh.po_header_id = poll.po_header_id AND
4356 poh.po_header_id = c_po_header_id;
4357 --Bug 5053593.
4358 CURSOR l_requestors_csr(c_grp_id_csr IN NUMBER)
4359 IS
4360 select pda.deliver_to_person_id
4361 from
4362 po_change_requests pcr,
4363 po_distributions_all pda
4364 where pcr.change_request_group_id = c_grp_id_csr
4365 AND pcr.request_level = 'LINE'
4366 AND pcr.document_line_id = pda.po_line_id
4367 union
4368 select pda.deliver_to_person_id
4369 from
4370 po_change_requests pcr,
4371 po_distributions_all pda
4372 where pcr.change_request_group_id = c_grp_id_csr
4373 AND pcr.request_level = 'SHIPMENT'
4374 AND pcr.document_line_location_id = pda.line_location_id;
4375
4376 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4377 x_progress VARCHAR2(1000);
4378 l_planner_username fnd_user.user_name%type;
4379 l_planner_disp_name VARCHAR2(2000);
4380 l_requester_username fnd_user.user_name%type;
4381 l_requester_disp_name VARCHAR2(2000);
4382 l_requester_id number;
4383 l_planner_id number;
4384 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
4385 BEGIN
4386
4387 x_progress :='MARK_SCO_FOR_REQ:000';
4388
4389 l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4390 itemkey => itemkey,
4391 aname => 'CHANGE_REQUEST_GROUP_ID');
4392
4393 /*
4394 OPEN l_planners_csr(l_change_group_id );
4395 */
4396 l_po_header_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype,
4397 itemkey => itemkey,
4398 aname => 'PO_HEADER_ID');
4399
4400 OPEN l_planners_csr(l_po_header_id);
4401 FETCH l_planners_csr INTO l_planner_id;
4402 close l_planners_csr;
4403
4404 IF(l_planner_id is not null) THEN
4405
4406 x_progress:= 'MARK_SCO_FOR_REQ:001';
4407
4408
4409 -- Set the notIFication to be sent to the Requester
4410 wf_directory.GetUserName( p_orig_system => 'PER',
4411 p_orig_system_id => l_planner_id,
4412 p_name => l_planner_username,
4413 p_display_name => l_planner_disp_name);
4414
4415 x_progress:= 'MARK_SCO_FOR_REQ:002';
4416 log_message('MARK_SCO_FOR_REQ','Planner User Name ',l_planner_username);
4417
4418 wf_engine.SetItemAttrText(itemtype => itemtype,
4419 itemkey => itemkey,
4420 aname => 'PREPARER_USERNAME',
4421 avalue => l_planner_username);
4422 ELSE
4423 OPEN l_requestors_csr(l_change_group_id);
4424 FETCH l_requestors_csr INTO l_requester_id;
4425 close l_requestors_csr;
4426 x_progress:= 'MARK_SCO_FOR_REQ:003';
4427
4428
4429 wf_directory.GetUserName( p_orig_system => 'PER',
4430 p_orig_system_id => l_requester_id,
4431 p_name => l_requester_username,
4432 p_display_name => l_requester_disp_name);
4433
4434 x_progress:= 'MARK_SCO_FOR_REQ:004';
4435 log_message('MARK_SCO_FOR_REQ','Requester User Name ',l_requester_username);
4436
4437 wf_engine.SetItemAttrText(itemtype => itemtype,
4438 itemkey => itemkey,
4439 aname => 'PREPARER_USERNAME',
4440 avalue => l_requester_username);
4441 END IF;
4442 wf_engine.SetItemAttrText(itemtype => itemtype,
4443 itemkey => itemkey,
4444 aname => 'NOTIF_USAGE',
4445 avalue =>'REQ');
4446 update po_change_requests
4447 set request_status ='REQ_APP',
4448 responded_by = fnd_global.user_id,
4449 response_date = sysdate
4450 where change_request_group_id = l_change_group_id
4451 AND request_status = 'PENDING';
4452
4453
4454 resultout:=wf_engine.eng_completed;
4455
4456 x_progress := 'MARK_SCO_FOR_REQ:005';
4457
4458
4459
4460 exception
4461
4462 when others THEN
4463
4464 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4465 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4466 g_module_prefix,
4467 x_progress || ':unexpected error' || Sqlerrm);
4468 END IF;
4469 wf_core.context('POSCHORD', 'MARK_SCO_FOR_REQ', itemtype, itemkey, to_char(actid), funcmode);
4470
4471 raise;
4472
4473 END MARK_SCO_FOR_REQ;
4474
4475 ------------------------------------------------------------------------------
4476
4477
4478 END POS_SCO_TOLERANCE_PVT;