[Home] [Help]
PACKAGE BODY: APPS.POS_SCO_TOLERANCE_PVT
Source
1 PACKAGE BODY POS_SCO_TOLERANCE_PVT AS
2 /* $Header: POSPTOLB.pls 120.59.12020000.4 2013/02/09 13:16:30 hvutukur ship $ */
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 /* Code Changes for Bug - 11794109 Start */
1805 BEGIN
1806
1807 IF(p_po_release_id is null) THEN
1808 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1809 where document_header_id=p_po_header_id AND document_type = 'PO'
1810 and change_active_flag='Y'
1811 and rownum=1;
1812 ELSE
1813 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1814 where document_header_id=p_po_header_id AND document_type = 'RELEASE'
1815 and po_release_id = p_po_release_id
1816 and change_active_flag='Y'
1817 and rownum=1;
1818 END IF;
1819
1820 EXCEPTION
1821 WHEN NO_DATA_FOUND THEN
1822 return l_total_new_line_amt;
1823 END;
1824 /* Code Changes for Bug - 11794109 End */
1825
1826 x_progress := 'CALCULATE_NEW_LINE_AMOUNT:000';
1827 IF(p_po_release_id is null) THEN
1828 IF(p_complex_po_style = 'ACTUALS') THEN
1829 OPEN c_line_amt_new_cw_actuals(p_po_header_id,p_po_line_id,req_status,req_initiator);
1830 LOOP
1831 FETCH c_line_amt_new_cw_actuals INTO l_new_line_amt;
1832 EXIT WHEN c_line_amt_new_cw_actuals%NOTFOUND;
1833 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1834 END LOOP;
1835 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1836 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1837 CLOSE c_line_amt_new_cw_actuals;
1838 ELSIF(p_complex_po_style = 'FINANCING') THEN
1839 OPEN c_line_amt_new_cw_financing(p_po_header_id,p_po_line_id,req_status,req_initiator);
1840 LOOP
1841 FETCH c_line_amt_new_cw_financing INTO l_new_line_amt;
1842 EXIT WHEN c_line_amt_new_cw_financing%NOTFOUND;
1843 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1844 END LOOP;
1845 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1846 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1847 CLOSE c_line_amt_new_cw_financing;
1848
1849 ELSE
1850
1851 OPEN c_line_amt_new(p_po_header_id,p_po_line_id,req_status,req_initiator);
1852 LOOP
1853 FETCH c_line_amt_new INTO l_new_line_amt;
1854 EXIT WHEN c_line_amt_new%NOTFOUND;
1855 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1856 END LOOP;
1857 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1858 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1859 CLOSE c_line_amt_new;
1860 END IF;
1861 ELSIF( p_po_release_id is not null) THEN
1862
1863 OPEN c_line_amt_new_rel(p_po_header_id,p_po_release_id,p_po_line_id,req_status,req_initiator);
1864 LOOP
1865 FETCH c_line_amt_new_rel INTO l_new_line_amt;
1866 EXIT WHEN c_line_amt_new_rel%NOTFOUND;
1867 l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1868 END LOOP;
1869 x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1870 log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1871 CLOSE c_line_amt_new_rel;
1872 END IF;
1873
1874 return l_total_new_line_amt;
1875
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1879 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1880 g_module_prefix,
1881 x_progress || ':unexpected error' || Sqlerrm);
1882 END IF;
1883 raise;
1884 END CALCULATE_NEW_LINE_AMOUNT;
1885
1886
1887 PROCEDURE SHIP_AMOUNT_WITHIN_TOL( itemtype IN VARCHAR2,
1888 itemkey IN VARCHAR2,
1889 actid IN NUMBER,
1890 funcmode IN VARCHAR2,
1891 resultout OUT NOCOPY VARCHAR2)
1892 IS
1893
1894 -- Calculates the old shipment amount
1895 CURSOR c_old_ship_amt(p_po_header_id_csr IN NUMBER) IS
1896 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
1897 from po_line_locations_all pll,
1898 po_lines_all pol
1899 where pll.po_header_id = p_po_header_id_csr
1900 AND pll.po_line_id = pol.po_line_id
1901 GROUP BY pll.line_location_id;
1902
1903 -- Calculate Old Shipment amount for the BPA Release
1904 CURSOR c_old_ship_amt_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
1905 SELECT sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0))))),
1906 pll.line_location_id
1907 FROM po_lines_all pl,
1908 po_line_locations_all pll
1909 WHERE pll.po_release_id = p_po_release_id_csr
1910 AND pll.po_header_id = p_po_header_id_csr
1911 AND pll.po_line_id = pl.po_line_id
1912 GROUP BY pll.line_location_id;
1913
1914 -- Calculate Old Shipment amount for the Complex work POs ( Actuals And Financing Case)
1915 CURSOR c_old_ship_amt_cw(p_po_header_id_csr IN NUMBER) IS
1916 SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
1917 (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
1918 'AMOUNT',
1919 DECODE(pll.payment_type, 'LUMPSUM',
1920 (pll.amount - NVL(pll.amount_cancelled,0)),
1921 'MILESTONE',
1922 (pll.amount - NVL(pll.amount_cancelled,0)),
1923 'RATE',
1924 (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.line_location_id
1925 FROM po_lines_all pl,
1926 po_line_locations_all pll
1927 WHERE pl.po_header_id = p_po_header_id_csr
1928 AND pll.po_line_id = pl.po_line_id
1929 GROUP BY pll.line_location_id;
1930
1931
1932
1933
1934
1935
1936 l_po_header_id po_change_requests.document_header_id%TYPE;
1937 l_po_release_id po_change_requests.po_release_id%TYPE;
1938 l_po_line_id po_change_requests.document_line_id%TYPE;
1939 l_change_group_id po_change_requests.change_request_group_id%type;
1940 l_matching_basis po_lines_all.matching_basis%TYPE;
1941 l_old_shipamt po_change_requests.old_amount%TYPE;
1942 l_new_shipamt po_change_requests.new_amount%TYPE;
1943 l_old_ship_amt_rel NUMBER;
1944 l_new_ship_amt_rel NUMBER;
1945 l_old_ship_amt_cw NUMBER;
1946 l_new_ship_amt_cw NUMBER;
1947 l_return_val VARCHAR2(1) :='Y';
1948 l_shipamt_max_incr_per NUMBER;
1949 l_shipamt_max_dec_per NUMBER;
1950 l_shipamt_max_incr_val NUMBER;
1951 l_shipamt_max_dec_val NUMBER;
1952 l_old_ship_amt NUMBER;
1953 l_new_ship_amt NUMBER := 0;
1954 l_new_ship_amt_no_change NUMBER := 0;
1955 l_line_location_id NUMBER;
1956 l_po_style_type VARCHAR2(10);
1957 l_doc_type VARCHAR2(10);
1958 x_progress VARCHAR2(1000);
1959
1960
1961 BEGIN
1962
1963 IF ( funcmode = 'RUN' ) THEN
1964 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:000';
1965
1966 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1967 itemkey => itemkey,
1968 aname => 'PO_HEADER_ID');
1969
1970 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1971 itemkey => itemkey,
1972 aname => 'PO_RELEASE_ID');
1973
1974 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1975 itemkey => itemkey,
1976 aname => 'CHANGE_REQUEST_GROUP_ID');
1977
1978 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1979 itemkey => itemkey,
1980 aname => 'PO_STYLE_TYPE');
1981
1982 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1983 itemkey => itemkey,
1984 aname => 'DOCUMENT_TYPE');
1985 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:001';
1986
1987 IF (l_change_group_id IS NOT NULL) THEN
1988 -- check for the DOC types (applicable for PO LINE AMOUNT( Line level)
1989 -- if other doc types return true and exit
1990 IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1991
1992 IF (l_po_style_type='NORMAL') THEN
1993
1994 -- get shipment quantity tolerances
1995 l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1996 itemkey => itemkey,
1997 aname => 'SHIP_AMOUNT_INCR_PER');
1998
1999 l_shipamt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2000 itemkey => itemkey,
2001 aname => 'SHIP_AMOUNT_DEC_PER');
2002
2003 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:002';
2004 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
2005 -- get shipment quantity tolerances
2006 l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2007 itemkey => itemkey,
2008 aname => 'SHIP_AMOUNT_INCR_VAL');
2009
2010 l_shipamt_max_dec_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2011 itemkey => itemkey,
2012 aname => 'SHIP_AMOUNT_DEC_VAL');
2013
2014 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:003';
2015 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2016
2017 ELSIF (l_po_style_type='COMPLEX') THEN
2018 -- Complex Work PO Chack -- COMPLEX WORK
2019 -- get shipment quantity tolerances
2020 l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2021 itemkey => itemkey,
2022 aname => 'PAY_AMOUNT_INCR_PER');
2023
2024 l_shipamt_max_dec_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2025 itemkey => itemkey,
2026 aname => 'PAY_AMOUNT_DEC_PER');
2027
2028 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:004';
2029 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
2030 -- get shipment quantity tolerances
2031 l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2032 itemkey => itemkey,
2033 aname => 'PAY_AMOUNT_INCR_VAL');
2034
2035 l_shipamt_max_dec_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2036 itemkey => itemkey,
2037 aname => 'PAY_AMOUNT_DEC_VAL');
2038 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:005';
2039 log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2040 END IF; --l_po_style_type='NORMAL'/'COMPLEX'
2041
2042
2043
2044 IF(l_doc_type = 'PO' and l_po_style_type='NORMAL') THEN
2045 OPEN c_old_ship_amt(l_po_header_id);
2046 LOOP
2047 FETCH c_old_ship_amt
2048 INTO l_old_ship_amt,l_line_location_id;
2049 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2050 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2051 EXIT WHEN (l_return_val='N');
2052 EXIT WHEN c_old_ship_amt%NOTFOUND;
2053
2054 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);
2055
2056 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2057 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
2058 l_return_val := 'N';
2059 END IF;
2060 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2061 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2062 END LOOP;
2063 CLOSE c_old_ship_amt;
2064
2065 IF (l_return_val = 'Y') THEN
2066 wf_engine.SetItemAttrText( itemtype => itemtype,
2067 itemkey => itemkey,
2068 aname => 'NOTIF_USAGE',
2069 avalue => 'BUYER_AUTO_FYI');
2070 END IF;
2071 ELSIF ((l_doc_type = 'RELEASE') and (l_po_style_type='NORMAL')) THEN
2072 OPEN c_old_ship_amt_rel(l_po_header_id,l_po_release_id);
2073 LOOP
2074 FETCH c_old_ship_amt_rel
2075 INTO l_old_ship_amt,l_line_location_id;
2076 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2077 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2078 EXIT WHEN (l_return_val='N');
2079 EXIT WHEN c_old_ship_amt_rel%NOTFOUND;
2080
2081 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);
2082
2083 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2084 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
2085 l_return_val := 'N';
2086 END IF;
2087 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2088 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2089 END LOOP;
2090 CLOSE c_old_ship_amt_rel;
2091
2092 IF (l_return_val = 'Y') THEN
2093 wf_engine.SetItemAttrText( itemtype => itemtype,
2094 itemkey => itemkey,
2095 aname => 'NOTIF_USAGE',
2096 avalue => 'BUYER_AUTO_FYI');
2097 END IF;
2098 ELSIF (l_po_style_type='COMPLEX') THEN
2099
2100 OPEN c_old_ship_amt_cw(l_po_header_id);
2101 LOOP
2102 FETCH c_old_ship_amt_cw
2103 INTO l_old_ship_amt,l_line_location_id;
2104 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2105 log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2106 EXIT WHEN (l_return_val='N');
2107 EXIT WHEN c_old_ship_amt_cw%NOTFOUND;
2108
2109 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);
2110
2111 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2112 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
2113 l_return_val := 'N';
2114 END IF;
2115 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2116 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2117 END LOOP;
2118 CLOSE c_old_ship_amt_cw;
2119
2120 IF (l_return_val = 'Y') THEN
2121 wf_engine.SetItemAttrText( itemtype => itemtype,
2122 itemkey => itemkey,
2123 aname => 'NOTIF_USAGE',
2124 avalue => 'BUYER_AUTO_FYI');
2125 END IF;
2126
2127 END IF; -- PO Or RELEASE or complex work POs
2128 END IF; -- l_doc_type PO or RELEASE
2129 -- set result value
2130 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2131 END IF; -- change_group_id is not null
2132 x_progress := 'SHIP_AMOUNT_WITHIN_TOL:016';
2133 log_message('SHIP_AMOUNT_WITHIN_TOL','Result',resultout);
2134 END IF; -- IF ( funcmode = 'RUN' )
2135 EXCEPTION
2136 WHEN OTHERS THEN
2137 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2138 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2139 g_module_prefix,
2140 x_progress || ':unexpected error' || Sqlerrm);
2141 END IF;
2142 wf_core.context('POSCHORD', 'SHIP_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
2143 raise;
2144 END SHIP_AMOUNT_WITHIN_TOL;
2145
2146
2147 FUNCTION CALCULATE_NEW_SHIP_AMOUNT( p_po_header_id IN NUMBER,
2148 p_po_release_id IN NUMBER,
2149 p_line_location_id IN NUMBER,
2150 p_split_flag IN VARCHAR2,
2151 p_po_style_type IN VARCHAR2,
2152 p_po_shipment_num IN NUMBER)
2153 RETURN NUMBER
2154 IS
2155
2156 --- picks up new shipment amount for SPO
2157 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
2158 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
2159 (nvl(pcr.new_price,pl.unit_price) *
2160 (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2161 FROM po_change_requests pcr, --line amount/price change
2162 po_change_requests pcr1, --shipment quantity change
2163 po_lines_all pl,
2164 po_line_locations_all pll
2165 WHERE pl.po_header_id = p_po_header_id_csr
2166 AND pll.line_location_id = p_line_location_id_csr
2167 AND pll.po_line_id = pl.po_line_id
2168 AND pcr1.document_header_id (+) = p_po_header_id_csr
2169 AND pcr1.document_line_location_id(+) = pll.line_location_id
2170 AND pcr1.action_type(+) = 'MODIFICATION'
2171 AND pcr1.request_status(+) = req_status
2172 AND pcr1.request_level (+) = 'SHIPMENT'
2173 AND pcr1.initiator(+) = req_initiator
2174 AND pcr.document_line_id(+) = pl.po_line_id
2175 AND pcr.action_type(+) = 'MODIFICATION'
2176 AND pcr.request_status(+) = req_status
2177 AND pcr.request_level (+) = 'LINE'
2178 AND pcr.initiator(+) = req_initiator;
2179
2180 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
2181 -- for splitted shipments
2182 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
2183 (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
2184 FROM po_change_requests pcr, --line amount/price change
2185 po_change_requests pcr2, --for split shipments
2186 po_lines_all pl,
2187 po_line_locations_all pll
2188 WHERE pl.po_header_id = p_po_header_id_csr
2189 AND pll.line_location_id = p_line_location_id_csr
2190 AND pll.po_line_id = pl.po_line_id
2191 AND pcr2.document_header_id = p_po_header_id_csr
2192 AND pcr2.parent_line_location_id = pll.line_location_id
2193 AND pcr2.action_type = 'MODIFICATION'
2194 AND pcr2.request_status = req_status
2195 AND pcr2.request_level = 'SHIPMENT'
2196 AND pcr2.initiator = req_initiator
2197 AND pcr2.document_shipment_number = p_po_shipment_num
2198 AND pcr.document_line_id(+) = pl.po_line_id
2199 AND pcr.action_type(+) = 'MODIFICATION'
2200 AND pcr.request_status(+) = req_status
2201 AND pcr.request_level (+) = 'LINE'
2202 AND pcr.initiator(+) = req_initiator;
2203
2204 --- picks up new shipment amount for releses
2205 -- old_price is included for price breaks
2206 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
2207 SELECT nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
2208 (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
2209 (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2210 FROM po_change_requests pcr,
2211 po_lines_all pl,
2212 po_line_locations_all pll
2213 WHERE pll.po_header_id = p_po_header_id_csr
2214 AND pll.line_location_id = p_line_location_id_csr
2215 AND pll.po_release_id = p_po_release_id_csr
2216 AND pll.po_line_id = pl.po_line_id
2217 -- AND pcr.po_release_id = p_po_release_id_csr
2218 AND pcr.document_header_id(+) = p_po_header_id_csr
2219 AND pcr.document_line_id = pl.po_line_id
2220 AND pcr.action_type(+) = 'MODIFICATION'
2221 AND pcr.request_status(+) = req_status
2222 AND pcr.request_level (+) = 'SHIPMENT'
2223 AND pcr.initiator(+) = req_initiator
2224 AND pcr.document_line_location_id(+) = pll.line_location_id;
2225
2226 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
2227 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)
2228 FROM po_change_requests pcr2, -- for splitted shipments
2229 po_lines_all pl,
2230 po_line_locations_all pll
2231 WHERE pll.po_header_id = p_po_header_id_csr
2232 AND pll.line_location_id = p_line_location_id_csr
2233 AND pll.po_line_id = pl.po_line_id
2234 AND pcr2.po_release_id = p_po_release_id_csr
2235 AND pcr2.document_header_id(+) = p_po_header_id_csr
2236 AND pcr2.document_line_id(+) = pl.po_line_id
2237 AND pcr2.action_type(+) = 'MODIFICATION'
2238 AND pcr2.request_status(+)= req_status
2239 AND pcr2.request_level (+) = 'SHIPMENT'
2240 AND pcr2.initiator(+) = req_initiator
2241 AND pcr2.parent_line_location_id = pll.line_location_id
2242 AND pcr2.document_shipment_number = p_po_shipment_num;
2243
2244 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case)
2245 CURSOR c_new_shipment_amount_cw (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER) IS
2246 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2247 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2248 'AMOUNT',
2249 DECODE(pll.payment_type, 'LUMPSUM',
2250 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2251 'MILESTONE',
2252 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2253 'RATE',
2254 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2255 FROM
2256 po_change_requests pcr, --shipment quantity changes
2257 po_lines_all pl,
2258 po_line_locations_all pll
2259 WHERE
2260 pl.po_header_id = p_po_header_id_csr
2261 AND pll.line_location_id = p_line_location_id_csr
2262 AND pll.po_line_id = pl.po_line_id
2263 AND pcr.document_header_id (+) = p_po_header_id_csr
2264 AND pcr.document_line_location_id(+) = pll.line_location_id
2265 AND pcr.action_type(+) = 'MODIFICATION'
2266 AND pcr.request_status(+) = 'PENDING'
2267 AND pcr.request_level (+) = 'SHIPMENT'
2268 AND pcr.initiator(+) = 'SUPPLIER';
2269
2270 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case) for split cases
2271 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
2272 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2273 (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2274 'AMOUNT',
2275 DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
2276 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2277 'MILESTONE',
2278 (nvl(pcr.new_amount,pll.amount) - NVL(pll.amount_cancelled,0)),
2279 'RATE',
2280 (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2281 FROM
2282 po_change_requests pcr, --shipment quantity changes
2283 po_lines_all pl,
2284 po_line_locations_all pll
2285 WHERE
2286 pl.po_header_id = p_po_header_id_csr
2287 AND pll.line_location_id = p_line_location_id_csr
2288 AND pll.po_line_id = pl.po_line_id
2289 AND pcr.document_header_id = p_po_header_id_csr
2290 AND pcr.parent_line_location_id = pll.line_location_id
2291 AND pcr.action_type = 'MODIFICATION'
2292 AND pcr.request_status = 'PENDING'
2293 AND pcr.request_level = 'SHIPMENT'
2294 AND pcr.initiator = 'SUPPLIER'
2295 AND pcr.document_shipment_number = p_po_shipment_num;
2296
2297 l_new_ship_amt NUMBER;
2298 x_progress VARCHAR2(1000);
2299 req_status po_change_requests.request_status%TYPE;
2300 req_initiator po_change_requests.initiator%TYPE;
2301
2302 BEGIN
2303
2304 /* Code Changes for Bug - 11794109 Start */
2305 BEGIN
2306
2307 IF(p_po_release_id is null) THEN
2308 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2309 where document_header_id=p_po_header_id AND document_type = 'PO'
2310 and change_active_flag='Y'
2311 and rownum=1;
2312 ELSE
2313 select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2314 where document_header_id=p_po_header_id AND document_type = 'RELEASE'
2315 and po_release_id = p_po_release_id
2316 and change_active_flag='Y'
2317 and rownum=1;
2318 END IF;
2319
2320 EXCEPTION
2321 WHEN NO_DATA_FOUND THEN
2322 l_new_ship_amt := 0;
2323 return l_new_ship_amt;
2324 END;
2325 /* Code Changes for Bug - 11794109 End */
2326
2327
2328 IF(p_po_release_id is null) THEN
2329 IF(p_split_flag = 'N') THEN
2330 IF(p_po_style_type = 'NORMAL') THEN
2331 OPEN c_new_shipment_amount(p_po_header_id,p_line_location_id,req_status,req_initiator);
2332 LOOP
2333 FETCH c_new_shipment_amount INTO l_new_ship_amt;
2334 EXIT WHEN c_new_shipment_amount%NOTFOUND;
2335 END LOOP;
2336 CLOSE c_new_shipment_amount;
2337 ELSIF(p_po_style_type = 'COMPLEX') THEN
2338 OPEN c_new_shipment_amount_cw(p_po_header_id,p_line_location_id);
2339 LOOP
2340 FETCH c_new_shipment_amount_cw INTO l_new_ship_amt;
2341 EXIT WHEN c_new_shipment_amount_cw%NOTFOUND;
2342 END LOOP;
2343 CLOSE c_new_shipment_amount_cw;
2344 END IF;
2345 ELSIF(p_split_flag = 'Y') THEN
2346 IF(p_po_style_type = 'NORMAL') THEN
2347 OPEN c_new_shipment_amount_split(p_po_header_id,p_line_location_id,req_status,req_initiator,p_po_shipment_num);
2348 LOOP
2349 FETCH c_new_shipment_amount_split INTO l_new_ship_amt;
2350 EXIT WHEN c_new_shipment_amount_split%NOTFOUND;
2351 END LOOP;
2352 CLOSE c_new_shipment_amount_split;
2353 ELSIF(p_po_style_type = 'COMPLEX') THEN
2354 OPEN c_new_shipment_amount_split_cw(p_po_header_id,p_line_location_id,p_po_shipment_num);
2355 LOOP
2356 FETCH c_new_shipment_amount_split_cw INTO l_new_ship_amt;
2357 EXIT WHEN c_new_shipment_amount_split_cw%NOTFOUND;
2358 END LOOP;
2359 CLOSE c_new_shipment_amount_split_cw;
2360 END IF;
2361 END IF;
2362 ELSIF( p_po_release_id is not null) THEN
2363 IF(p_split_flag = 'N') THEN
2364 OPEN c_new_shipment_amount_rel(p_po_header_id,p_po_release_id,p_line_location_id,req_status,req_initiator);
2365 LOOP
2366 FETCH c_new_shipment_amount_rel INTO l_new_ship_amt;
2367 EXIT WHEN c_new_shipment_amount_rel%NOTFOUND;
2368 END LOOP;
2369 CLOSE c_new_shipment_amount_rel;
2370 ELSIF (p_split_flag = 'Y') THEN
2371 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);
2372 LOOP
2373 FETCH c_new_ship_amt_rel_split INTO l_new_ship_amt;
2374 EXIT WHEN c_new_ship_amt_rel_split%NOTFOUND;
2375 END LOOP;
2376 CLOSE c_new_ship_amt_rel_split;
2377 END IF;
2378 END IF;
2379
2380 return l_new_ship_amt;
2381
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2385 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2386 g_module_prefix,
2387 x_progress || ':unexpected error' || Sqlerrm);
2388 END IF;
2389 raise;
2390 END CALCULATE_NEW_SHIP_AMOUNT;
2391
2392 FUNCTION CHANGE_WITHIN_TOL( p_oldValue IN NUMBER,
2393 p_newValue IN NUMBER,
2394 p_maxIncrement_per IN NUMBER,
2395 p_maxDecrement_per IN NUMBER,
2396 p_maxIncrement_val IN NUMBER,
2397 p_maxDecrement_val IN NUMBER)
2398 RETURN boolean
2399
2400 IS
2401
2402 l_changePercent NUMBER;
2403 l_changeValue NUMBER;
2404 x_progress varchar2(1000);
2405
2406 BEGIN
2407
2408 x_progress := 'CHANGE_WITHIN_TOL:000';
2409 -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2410 /* bug 9884700 , Consider p_oldValue=0 condition also */
2411 IF((p_oldValue >= 0) AND (p_newValue > 0)) THEN
2412 IF((p_newValue > p_oldValue) AND (nvl(p_maxIncrement_per,0) = 0 AND nvl(p_maxIncrement_val,0) = 0)) THEN
2413 return FALSE;
2414 ELSIF((p_newValue < p_oldValue) AND (nvl(p_maxDecrement_per,0) = 0 AND nvl(p_maxDecrement_val,0) = 0)) THEN
2415 return FALSE;
2416 END IF;
2417 END IF;
2418 x_progress := 'CHANGE_WITHIN_TOL:001';
2419 IF (p_oldValue <> p_newValue) THEN
2420 IF (p_oldValue >= 0 AND p_newValue > 0) THEN
2421 --- Checking for the Value change
2422 l_changeValue := abs(p_oldValue - p_newValue);
2423 x_progress := 'CHANGE_WITHIN_TOL:002';
2424 -- value has increased
2425 IF (p_maxIncrement_val <> 0 AND p_oldValue < p_newValue) THEN
2426 IF(l_changeValue > p_maxIncrement_val) THEN
2427 return FALSE;
2428 END IF;
2429 END IF;
2430 -- value has decreased
2431 IF (p_maxDecrement_val <> 0 AND p_oldValue > p_newValue) THEN
2432 IF(l_changeValue > p_maxDecrement_val) THEN
2433 return FALSE;
2434 END IF;
2435 END IF;
2436 --- Checking for the percentage change
2437 /* bug 9884700 */
2438 if p_oldValue=0 then
2439 l_changePercent:=p_newValue*100;
2440 else
2441 l_changePercent := ((p_oldValue - p_newValue)/p_oldValue)*100;
2442 end if;
2443 x_progress := 'CHANGE_WITHIN_TOL:003';
2444 -- value has increased
2445 IF (p_maxIncrement_per <> 0 AND p_oldValue < p_newValue) THEN
2446 IF((abs(l_changePercent)) > p_maxIncrement_per) THEN
2447 return FALSE;
2448 END IF;
2449 END IF;
2450 -- value has decreased
2451 IF (p_maxDecrement_per <> 0 AND p_oldValue > p_newValue) THEN
2452 IF(l_changePercent > p_maxDecrement_per) THEN
2453 return FALSE;
2454 END IF;
2455 END IF;
2456 END IF;
2457 END IF;
2458
2459 return TRUE;
2460
2461 EXCEPTION
2462 WHEN OTHERS THEN
2463 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2464 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2465 g_module_prefix,
2466 x_progress || ':unexpected error' || Sqlerrm);
2467 END IF;
2468 raise;
2469
2470 END CHANGE_WITHIN_TOL;
2471 ------------------------------------------------------------------------------
2472 FUNCTION CHANGE_WITHIN_TOL_DATE(p_oldValue IN DATE,
2473 p_newValue IN DATE,
2474 p_maxIncrement IN NUMBER,
2475 p_maxDecrement IN NUMBER)
2476 RETURN boolean
2477
2478 IS
2479 x_progress varchar2(1000);
2480 BEGIN
2481
2482 x_progress := 'CHANGE_WITHIN_TOL_DATE:000';
2483 -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2484 IF( (p_newValue > p_oldValue) AND (nvl(p_maxIncrement,0) = 0)) THEN
2485 return FALSE;
2486 ELSIF( (p_newValue < p_oldValue) AND(nvl(p_maxDecrement,0) = 0)) THEN
2487 return FALSE;
2488 END IF;
2489
2490 x_progress := 'CHANGE_WITHIN_TOL_DATE:001';
2491
2492 IF(p_oldValue <> p_newValue) THEN
2493
2494 -- check for upper tol
2495 IF (p_maxIncrement <> 0 AND p_oldValue < p_newValue)THEN
2496 IF(p_newValue - p_maxIncrement > p_oldValue) THEN
2497 return FALSE;
2498 END IF;
2499 END IF;
2500
2501 -- check for lower tol
2502 IF(p_maxDecrement <> 0 AND p_oldValue > p_newValue) THEN
2503 IF(p_newValue + p_maxDecrement < p_oldValue) THEN
2504 return FALSE;
2505 END IF;
2506 END IF;
2507 END IF;
2508
2509 x_progress := 'CHANGE_WITHIN_TOL_DATE:002';
2510
2511 return TRUE;
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2515 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2516 g_module_prefix,
2517 x_progress || ':unexpected error' || Sqlerrm);
2518 END IF;
2519 raise;
2520
2521
2522 END CHANGE_WITHIN_TOL_DATE;
2523
2524 PROCEDURE ROUTE_TO_REQUESTER( itemtype IN VARCHAR2,
2525 itemkey IN VARCHAR2,
2526 actid IN NUMBER,
2527 funcmode IN VARCHAR2,
2528 resultout OUT NOCOPY VARCHAR2)
2529
2530 IS
2531 l_return_val VARCHAR2(1) ;
2532 l_po_header_id po_headers_all.po_header_id%TYPE;
2533 l_change_group_id po_change_requests.change_request_group_id%type;
2534 x_progress VARCHAR2(1000);
2535 l_po_style_type VARCHAR2(10);
2536 l_doc_type VARCHAR2(10);
2537 l_doc_subtype VARCHAR2(10);
2538 l_prmdate_app_flag VARCHAR2(10);
2539 l_shi_qty_app_flag VARCHAR2(10);
2540 l_unit_price_app_flag VARCHAR2(10);
2541
2542 BEGIN
2543 x_progress := 'ROUTE_TO_REQUESTER:000';
2544 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2545 itemkey => itemkey,
2546 aname => 'PO_HEADER_ID');
2547
2548 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2549 itemkey => itemkey,
2550 aname => 'CHANGE_REQUEST_GROUP_ID');
2551
2552 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2553 itemkey => itemkey,
2554 aname => 'PO_STYLE_TYPE');
2555
2556 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2557 itemkey => itemkey,
2558 aname => 'DOCUMENT_TYPE');
2559
2560 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2561 itemkey => itemkey,
2562 aname => 'DOC_SUB_TYPE');
2563
2564 l_prmdate_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2565 itemkey => itemkey,
2566 aname => 'PROMISE_DATE_APP_FLAG');
2567
2568 l_shi_qty_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2569 itemkey => itemkey,
2570 aname => 'SHIP_QTY_APPROVAL_FLAG');
2571
2572 l_unit_price_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2573 itemkey => itemkey,
2574 aname => 'PRICE_APPROVAL_FLAG');
2575
2576 x_progress := 'ROUTE_TO_REQUESTER:001';
2577
2578 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2579
2580
2581 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
2582 l_return_val:= 'Y' ;
2583 x_progress := 'ROUTE_TO_REQUESTER:002';
2584 ELSE
2585 -- send the notification to buyer
2586 wf_engine.SetItemAttrText(itemtype => itemtype,
2587 itemkey => itemkey,
2588 aname => 'NOTIF_USAGE',
2589 avalue => 'BUYER');
2590 l_return_val:= 'N' ;
2591 x_progress := 'ROUTE_TO_REQUESTER:003';
2592 END IF;
2593
2594 ELSE
2595 wf_engine.SetItemAttrText(itemtype => itemtype,
2596 itemkey => itemkey,
2597 aname => 'NOTIF_USAGE',
2598 avalue => 'BUYER');
2599 l_return_val:= 'N' ;
2600 x_progress := 'ROUTE_TO_REQUESTER:004';
2601 END IF;
2602 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2603 x_progress := 'ROUTE_TO_REQUESTER:005';
2604 log_message('ROUTE_TO_REQUESTER','Result',resultout);
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2608 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2609 g_module_prefix,
2610 x_progress || ':unexpected error' || Sqlerrm);
2611 END IF;
2612 wf_core.context('POSCHORD', 'ROUTE_TO_REQUESTER', itemtype, itemkey, to_char(actid),funcmode);
2613 raise;
2614 END ROUTE_TO_REQUESTER;
2615
2616
2617 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)
2618 return boolean is
2619
2620 cursor c_promise_date_changed (p_po_header_id_csr_pd IN NUMBER,p_change_group_id_csr_pd IN NUMBER) is
2621 select count(1) from po_change_requests pcr
2622 where pcr.document_header_id=p_po_header_id_csr_pd
2623 AND pcr.change_request_group_id=p_change_group_id_csr_pd
2624 AND pcr.new_promised_date IS NOT NULL
2625 AND pcr.action_type='MODIFICATION'
2626 AND pcr.request_status= 'PENDING'
2627 AND pcr.request_level= 'SHIPMENT'
2628 AND pcr.initiator= 'SUPPLIER';
2629
2630
2631
2632 cursor c_ship_qty_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2633 select count(1) from po_change_requests pcr
2634 where pcr.document_header_id=p_po_header_id_csr
2635 AND pcr.change_request_group_id=p_change_group_id_csr
2636 AND pcr.new_quantity IS NOT NULL
2637 AND pcr.action_type='MODIFICATION'
2638 AND pcr.request_status= 'PENDING'
2639 AND pcr.request_level= 'SHIPMENT'
2640 AND pcr.initiator= 'SUPPLIER';
2641
2642
2643 cursor c_price_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2644 select count(1) from po_change_requests pcr
2645 where pcr.document_header_id=p_po_header_id_csr
2646 AND pcr.change_request_group_id=p_change_group_id_csr
2647 AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2648 AND pcr.action_type='MODIFICATION'
2649 AND pcr.request_status= 'PENDING'
2650 AND pcr.request_level IN ('LINE','SHIPMENT')
2651 AND pcr.initiator= 'SUPPLIER';
2652
2653 cursor c_price_changed_rel(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) is
2654 select count(1) from po_change_requests pcr
2655 where pcr.document_header_id = p_po_header_id_csr
2656 AND pcr.change_request_group_id=p_change_group_id_csr
2657 AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2658 AND pcr.action_type='MODIFICATION'
2659 AND pcr.request_status= 'PENDING'
2660 AND pcr.request_level= 'SHIPMENT'
2661 AND pcr.initiator= 'SUPPLIER';
2662
2663 l_progress varchar2(50) := '000';
2664 l_promise_date_changed number;
2665 l_ship_qty_changed number;
2666 l_price_changed number;
2667 l_ret_val varchar2(10) := 'N';
2668 l_ret_val_prom_date_ch varchar2(10) := 'N';
2669 l_ret_val_ship_qty_ch varchar2(10) := 'N';
2670 l_ret_val_line_price_ch varchar2(10) := 'N';
2671 l_ret_val_ship_price_ch varchar2(10) := 'N';
2672 l_api_name varchar2(50) := 'ROUTE_TO_REQUESTER';
2673 x_progress varchar2(1000);
2674
2675
2676 BEGIN
2677 x_progress := 'ROUTETOREQUESTER:000';
2678 -- Checking whether there is a promise data change
2679 log_message('ROUTETOREQUESTER','Checking for Promise Date Changes','.');
2680
2681 OPEN c_promise_date_changed(p_po_header_id ,p_change_group_id ) ;
2682 FETCH c_promise_date_changed INTO l_promise_date_changed;
2683 log_message('ROUTETOREQUESTER','l_promise_date_changed',l_promise_date_changed);
2684
2685 IF ( l_promise_date_changed >= 1) THEN
2686 CLOSE c_promise_date_changed;
2687 x_progress := 'ROUTETOREQUESTER:001';
2688 log_message('ROUTETOREQUESTER','Promise Date Changed','Yes');
2689 IF( p_prm_date_app_flag = 'Y') THEN
2690 log_message('ROUTETOREQUESTER','Promise Date Changed Retrun Value','Yes');
2691 l_ret_val_prom_date_ch := 'Y';
2692 ELSE
2693 log_message('ROUTETOREQUESTER','Promise Date Changed Retrun Value','No');
2694 l_ret_val_prom_date_ch := 'N';
2695 END IF;
2696 END IF;
2697
2698 IF c_promise_date_changed%ISOPEN THEN
2699 CLOSE c_promise_date_changed;
2700 END IF;
2701
2702 log_message('ROUTETOREQUESTER','Checking for Ship Qty Changes','.');
2703
2704 -- Checking whether there is a Shipment quantity change
2705
2706 OPEN c_ship_qty_changed(p_po_header_id ,p_change_group_id );
2707 FETCH c_ship_qty_changed INTO l_ship_qty_changed;
2708 log_message('ROUTETOREQUESTER','l_ship_qty_changed',l_ship_qty_changed);
2709 IF (l_ship_qty_changed >= 1) THEN
2710 CLOSE c_ship_qty_changed;
2711 x_progress := 'ROUTETOREQUESTER:002';
2712 log_message('ROUTETOREQUESTER','Ship Qty changed','Yes');
2713 IF( p_ship_qty_app_flag = 'Y') THEN
2714 log_message('ROUTETOREQUESTER','Shipment Qty Retrun Value','Yes');
2715 l_ret_val_ship_qty_ch := 'Y';
2716 ELSE
2717 log_message('ROUTETOREQUESTER','Shipment Qty Retrun Value','No');
2718 l_ret_val_ship_qty_ch := 'N';
2719 END IF;
2720 END IF;
2721
2722 IF c_ship_qty_changed%ISOPEN THEN
2723 CLOSE c_ship_qty_changed;
2724 END IF;
2725
2726 -- Checking whether there is a Line price change
2727 log_message('ROUTETOREQUESTER','Checking for Line Price Changes','.');
2728
2729
2730 IF ( p_doc_type = 'PO') THEN
2731 OPEN c_price_changed(p_po_header_id ,p_change_group_id );
2732 FETCH c_price_changed INTO l_price_changed;
2733 log_message('ROUTETOREQUESTER','l_price_changed',l_price_changed);
2734 IF(l_price_changed >= 1) THEN
2735 CLOSE c_price_changed;
2736 x_progress := 'ROUTETOREQUESTER:003';
2737 log_message('ROUTETOREQUESTER','Line Price Changed','Yes');
2738 IF( p_unit_price_app_flag = 'Y') THEN
2739 log_message('ROUTETOREQUESTER','Line Price Return Value','Yes');
2740 l_ret_val_line_price_ch := 'Y';
2741 ELSE
2742 log_message('ROUTETOREQUESTER','Line Price Return Value','No');
2743 l_ret_val_line_price_ch := 'N';
2744 END IF;
2745 END IF;
2746
2747 IF c_price_changed%ISOPEN THEN
2748 CLOSE c_price_changed;
2749 END IF;
2750 log_message('ROUTETOREQUESTER','Checking for Shipment Price Changes','.');
2751
2752
2753 ELSIF( p_doc_type = 'RELEASE') THEN
2754 OPEN c_price_changed_rel(p_po_header_id ,p_change_group_id );
2755 FETCH c_price_changed_rel INTO l_price_changed;
2756 log_message('ROUTETOREQUESTER','l_price_changed',l_price_changed);
2757 IF(l_price_changed >= 1) THEN
2758 CLOSE c_price_changed_rel;
2759 x_progress := 'ROUTETOREQUESTER:004';
2760 log_message('ROUTETOREQUESTER','Shipment Price Changed','Yes');
2761 IF(p_unit_price_app_flag = 'Y') THEN
2762 log_message('ROUTETOREQUESTER','Shipment Price Return Value','Yes');
2763 l_ret_val_ship_price_ch := 'Y';
2764 ELSE
2765 log_message('ROUTETOREQUESTER','Shipment Price Return Value','No');
2766 l_ret_val_ship_price_ch := 'N';
2767 END IF;
2768 END IF;
2769
2770 IF c_price_changed_rel%ISOPEN THEN
2771 CLOSE c_price_changed_rel;
2772 END IF;
2773
2774 END IF; -- END ELSIF
2775
2776 x_progress := 'ROUTETOREQUESTER:005';
2777 IF(l_ret_val_line_price_ch = 'Y' OR l_ret_val_ship_qty_ch = 'Y' OR l_ret_val_prom_date_ch = 'Y' OR l_ret_val_ship_price_ch = 'Y')
2778 THEN
2779 l_ret_val := 'Y';
2780 ELSE
2781 l_ret_val := 'N';
2782 END IF;
2783 log_message('ROUTETOREQUESTER','l_ret_val - ',l_ret_val);
2784 log_message('ROUTETOREQUESTER','l_ret_val_line_price_ch - ',l_ret_val_line_price_ch);
2785 log_message('ROUTETOREQUESTER','l_ret_val_ship_qty_ch - ',l_ret_val_ship_qty_ch);
2786 log_message('ROUTETOREQUESTER','l_ret_val_prom_date_ch - ',l_ret_val_prom_date_ch);
2787
2788 IF( l_ret_val = 'Y') THEN
2789 log_message('ROUTETOREQUESTER','Returning - ','True');
2790 return TRUE;
2791 ELSIF(l_ret_val = 'N') THEN
2792 log_message('ROUTETOREQUESTER','Returning - ','False');
2793 return FALSE;
2794 ELSE RETURN FALSE;
2795 END IF;
2796
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2800 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2801 g_module_prefix,
2802 x_progress || ':unexpected error' || Sqlerrm);
2803 END IF;
2804 raise;
2805 END ROUTETOREQUESTER;
2806
2807
2808
2809
2810 PROCEDURE ROUTE_SCO_BIZ_RULES( itemtype IN VARCHAR2,
2811 itemkey IN VARCHAR2,
2812 actid IN NUMBER,
2813 funcmode IN VARCHAR2,
2814 resultout OUT NOCOPY VARCHAR2)
2815
2816 IS
2817
2818 -- curosr to get the ReqHeaderId
2819 cursor c_getReqHdrId(p_po_header_id_csr IN NUMBER) is
2820 select distinct porh.requisition_header_id
2821 from po_requisition_headers_all porh,
2822 po_requisition_lines_all porl,
2823 po_headers_all poh,
2824 po_line_locations_all poll
2825 where porh.requisition_header_id = porl.requisition_header_id AND
2826 porl.line_location_id = poll.line_location_id AND
2827 poh.po_header_id = poll.po_header_id AND
2828 poh.po_header_id = p_po_header_id_csr;
2829
2830 cursor c_getReqHdrId_r(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
2831 select distinct porh.requisition_header_id
2832 from po_requisition_headers_all porh,
2833 po_requisition_lines_all porl,
2834 po_headers_all poh,
2835 po_line_locations_all poll
2836 where porh.requisition_header_id = porl.requisition_header_id AND
2837 porl.line_location_id = poll.line_location_id AND
2838 poh.po_header_id = poll.po_header_id AND
2839 poh.po_header_id = p_po_header_id_csr AND
2840 poll.po_release_id = p_po_release_id_csr;
2841
2842 l_po_header_id po_headers_all.po_header_id%TYPE;
2843 l_po_release_id po_releases_all.po_release_id%TYPE;
2844 l_doc_type VARCHAR2(10);
2845 l_return_val VARCHAR2(1);
2846 l_change_group_id po_change_requests.change_request_group_id%TYPE;
2847 l_req_hdr_id po_requisition_headers_all.requisition_header_id%TYPE;
2848 x_progress VARCHAR2(1000);
2849 l_auto_app_flag VARCHAR2(1);
2850
2851 BEGIN
2852
2853 x_progress := 'ROUTE_SCO_BIZ_RULES:000';
2854
2855 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2856 itemkey => itemkey,
2857 aname => 'PO_HEADER_ID');
2858
2859 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2860 itemkey => itemkey,
2861 aname => 'PO_RELEASE_ID');
2862
2863 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2864 itemkey => itemkey,
2865 aname => 'DOCUMENT_TYPE');
2866
2867 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2868 itemkey => itemkey,
2869 aname => 'CHANGE_REQUEST_GROUP_ID');
2870
2871 l_auto_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2872 itemkey => itemkey,
2873 aname => 'AUTO_APP_BIZ_RULES_FLAG');
2874
2875 x_progress := 'ROUTE_SCO_BIZ_RULES:001';
2876
2877 IF(l_auto_app_flag = 'Y') THEN
2878
2879 IF (ROUTE_SCO_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type,l_change_group_id)=FALSE) THEN
2880 l_return_val:= 'N';
2881 x_progress := 'ROUTE_SCO_BIZ_RULES:002';
2882 -- Set the Notif Usage for the notification to be sent to Buyer
2883 wf_engine.SetItemAttrText (itemtype => itemtype,
2884 itemkey => itemkey,
2885 aname => 'NOTIF_USAGE',
2886 avalue =>'BUYER');
2887 ELSE
2888 l_return_val:= 'Y';
2889 x_progress := 'ROUTE_SCO_BIZ_RULES:003';
2890
2891 IF( PROMISEDATECHANGE(l_po_header_id,l_change_group_id) = FALSE) THEN -- lock the corresponding req if there are price or quantity changes
2892
2893 if l_po_release_id is not null then
2894 OPEN c_getReqHdrId_r(l_po_header_id,l_po_release_id);
2895 LOOP
2896 FETCH c_getReqHdrId_r INTO l_req_hdr_id;
2897 EXIT WHEN c_getReqHdrId_r%NOTFOUND;
2898 x_progress:= 'ROUTE_SCO_BIZ_RULES:004';
2899 log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2900
2901 update po_requisition_headers_all
2902 set change_pending_flag = 'Y'
2903 where requisition_header_id = l_req_hdr_id;
2904
2905 END LOOP;
2906 CLOSE c_getReqHdrId_r;
2907
2908 else
2909 OPEN c_getReqHdrId(l_po_header_id);
2910 LOOP
2911 FETCH c_getReqHdrId INTO l_req_hdr_id;
2912 EXIT WHEN c_getReqHdrId%NOTFOUND;
2913 x_progress:= 'ROUTE_SCO_BIZ_RULES:004a';
2914 log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2915
2916 update po_requisition_headers_all
2917 set change_pending_flag = 'Y'
2918 where requisition_header_id = l_req_hdr_id;
2919
2920 END LOOP;
2921 CLOSE c_getReqHdrId;
2922 end if;
2923
2924 END IF;
2925
2926 END IF;
2927
2928 ELSIF( l_auto_app_flag = 'N') THEN
2929 l_return_val:= 'N';
2930 -- Set the Notif Usage for the notification to be sent to Buyer
2931 wf_engine.SetItemAttrText( itemtype => itemtype,
2932 itemkey => itemkey,
2933 aname => 'NOTIF_USAGE',
2934 avalue =>'BUYER');
2935 END IF;
2936
2937
2938 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2939
2940 x_progress := 'ROUTE_SCO_BIZ_RULES:005';
2941 log_message('ROUTE_SCO_BIZ_RULES','Result',resultout);
2942
2943
2944 EXCEPTION
2945 WHEN OTHERS THEN
2946 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2947 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2948 g_module_prefix,
2949 x_progress || ':unexpected error' || Sqlerrm);
2950 END IF;
2951 wf_core.context('POSCHORD', 'ROUTE_SCO_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
2952 raise;
2953 END ROUTE_SCO_BIZ_RULES;
2954
2955
2956 PROCEDURE AUTO_APP_BIZ_RULES(itemtype IN VARCHAR2,
2957 itemkey IN VARCHAR2,
2958 actid IN NUMBER,
2959 funcmode IN VARCHAR2,
2960 resultout OUT NOCOPY VARCHAR2)
2961
2962 IS
2963
2964 l_po_header_id po_headers_all.po_header_id%TYPE;
2965 l_po_release_id po_releases_all.po_release_id%TYPE;
2966 l_return_val VARCHAR2(1);
2967 x_progress VARCHAR2(1000);
2968 l_po_style_type VARCHAR2(10);
2969 l_doc_type VARCHAR2(10);
2970 l_doc_subtype VARCHAR2(10);
2971
2972 BEGIN
2973 x_progress := 'AUTO_APP_BIZ_RULES:000';
2974
2975 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2976 itemkey => itemkey,
2977 aname => 'PO_HEADER_ID');
2978
2979 l_po_release_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2980 itemkey => itemkey,
2981 aname => 'PO_RELEASE_ID');
2982
2983 l_po_style_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2984 itemkey => itemkey,
2985 aname => 'PO_STYLE_TYPE');
2986
2987 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2988 itemkey => itemkey,
2989 aname => 'DOCUMENT_TYPE');
2990
2991 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2992 itemkey => itemkey,
2993 aname => 'DOC_SUB_TYPE');
2994
2995 x_progress := 'AUTO_APP_BIZ_RULES:001';
2996
2997 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2998 IF ( AUTO_APP_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type)=FALSE) THEN
2999
3000 wf_engine.SetItemAttrText( itemtype => itemtype,
3001 itemkey => itemkey,
3002 aname => 'NOTIF_USAGE',
3003 avalue => 'BUYER');
3004
3005 wf_engine.SetItemAttrText( itemtype => itemtype,
3006 itemkey => itemkey,
3007 aname => 'AUTO_APP_BIZ_RULES_FLAG',
3008 avalue => 'N');
3009
3010 x_progress := 'AUTO_APP_BIZ_RULES:002';
3011
3012 l_return_val:= 'N';
3013 ELSE
3014 wf_engine.SetItemAttrText( itemtype => itemtype,
3015 itemkey => itemkey,
3016 aname => 'AUTO_APP_BIZ_RULES_FLAG',
3017 avalue => 'Y');
3018
3019 l_return_val:= 'Y';
3020
3021 END IF;
3022 ELSE
3023 l_return_val := 'N';
3024 END IF;
3025
3026 resultout := wf_engine.eng_completed|| ':' || l_return_val ;
3027 x_progress := 'AUTO_APP_BIZ_RULES:003';
3028
3029 log_message('AUTO_APP_BIZ_RULES','Result',resultout);
3030
3031 EXCEPTION
3032 WHEN OTHERS THEN
3033 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3034 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3035 g_module_prefix,
3036 x_progress || ':unexpected error' || Sqlerrm);
3037 END IF;
3038 wf_core.context('POSCHORD', 'AUTO_APP_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
3039 raise;
3040 END AUTO_APP_BIZ_RULES;
3041
3042
3043 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)
3044 RETURN BOOLEAN
3045 IS
3046
3047 -- Cursor for Checking for the multipe backing reqs is mapped to the PO
3048
3049 CURSOR c_reqs_count(p_po_header_id_csr IN NUMBER) is
3050 SELECT count(distinct porl.requisition_header_id)
3051 FROM po_requisition_lines_all porl, -- <Shared Proc FPJ>
3052 po_line_locations_all poll
3053 WHERE poll.line_location_id = porl.line_location_id AND
3054 NVL(poll.cancel_flag, 'N') = 'N' AND
3055 NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
3056 poll.shipment_type IN('STANDARD', 'BLANKET')
3057 AND poll.po_header_id=p_po_header_id_csr;
3058
3059 -- Cursor for Checking for the multipe backing reqs is mapped to the releases
3060
3061 CURSOR c_reqs_count_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3062 SELECT count(distinct porl.requisition_header_id)
3063 FROM po_requisition_lines_all porl, -- <Shared Proc FPJ>
3064 po_line_locations_all poll
3065 WHERE poll.line_location_id = porl.line_location_id AND
3066 NVL(poll.cancel_flag, 'N') = 'N' AND
3067 NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
3068 poll.shipment_type IN('STANDARD', 'BLANKET')
3069 AND poll.po_header_id=p_po_header_id_csr
3070 AND poll.po_release_id = p_po_release_id_csr;
3071
3072
3073 -- Cursor for checking all shipments should be mappped to one req.
3074
3075 CURSOR c_req_map_ship(p_po_header_id_csr IN NUMBER) is
3076 SELECT count(1)
3077 FROM po_line_locations_all plla
3078 WHERE plla.po_header_id = p_po_header_id_csr
3079 AND plla.line_location_id NOT IN (SELECT plla2.line_location_id
3080 FROM po_requisition_lines_all porla,
3081 po_line_locations_all plla2
3082 WHERE plla2.po_header_id = p_po_header_id_csr
3083 AND porla.line_location_id = plla2.line_location_id);
3084
3085
3086 -- Cursor for checking all shipments should be mappped to one req for the releases
3087 CURSOR c_req_map_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
3088 SELECT count(1)
3089 FROM po_line_locations_all plla
3090 WHERE plla.po_header_id = p_po_header_id_csr
3091 AND plla.po_release_id = p_po_release_id_csr
3092 AND plla.line_location_id NOT IN ( SELECT plla2.line_location_id
3093 FROM po_requisition_lines_all porla,
3094 po_line_locations_all plla2
3095 WHERE plla2.po_header_id = p_po_header_id_csr
3096 AND plla2.po_release_id = p_po_release_id_csr
3097 AND porla.line_location_id = plla2.line_location_id);
3098
3099
3100 -- Cursor for checking whether there is a price change for Catalog item.
3101
3102 cursor c_cat_price_change(p_po_header_id_csr IN NUMBER) is
3103 select count(1)
3104 from po_change_requests pcr,
3105 po_requisition_lines_all porl1,
3106 po_line_locations_all pll
3107 where pcr.document_header_id = p_po_header_id_csr
3108 AND pcr.REQUEST_LEVEL = 'LINE'
3109 AND pcr.new_price is not NULL
3110 AND pcr.request_status = 'PENDING'
3111 AND porl1.line_location_id = pll.line_location_id
3112 AND pcr.document_line_id = pll.po_line_id
3113 AND porl1.item_id is not null;
3114
3115
3116 -- Curosr for getting the Releases Price changes for Catlog request should go to the buyer
3117 cursor c_ship_price_change(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3118 select count(1)
3119 from po_change_requests pcr,
3120 po_requisition_lines_all porl1,
3121 po_line_locations_all pll
3122 where pcr.document_header_id = p_po_header_id_csr
3123 AND pcr.po_release_id = p_po_release_id_csr
3124 AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3125 AND nvl(pcr.new_price,pcr.new_amount) is not NULL -- NEW_AMOUNT in case of FPS
3126 AND pcr.request_status = 'PENDING'
3127 AND pcr.document_line_location_id = porl1.line_location_id
3128 AND porl1.line_location_id = pll.line_location_id
3129 AND porl1.item_id is NOT NULL;
3130
3131 -- Cursor for checking the FPS price changes for catalog requests
3132 cursor c_fps_price_change(p_po_header_id_csr IN NUMBER) IS
3133 select count(1)
3134 from po_change_requests pcr,
3135 po_requisition_lines_all porl1,
3136 po_line_locations_all pll
3137 where pcr.document_header_id = p_po_header_id_csr
3138 AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3139 AND pcr.new_amount is not NULL
3140 AND pcr.request_status = 'PENDING'
3141 AND pcr.document_line_location_id = porl1.line_location_id
3142 AND porl1.line_location_id = pll.line_location_id
3143 AND porl1.item_id IS NOT NULL;
3144
3145
3146 -- Check whether that Requisition is Locked or not
3147
3148 cursor c_req_locks(p_po_header_id_csr IN NUMBER) is
3149 select count(1)
3150 from po_requisition_headers_all porh,
3151 po_requisition_lines_all porl,
3152 po_headers_all poh,
3153 po_line_locations_all poll
3154 where porh.requisition_header_id = porl.requisition_header_id AND
3155 porl.line_location_id = poll.line_location_id AND
3156 poh.po_header_id = poll.po_header_id AND
3157 poh.po_header_id = p_po_header_id_csr AND
3158 porh.change_pENDing_flag = 'Y' ;
3159
3160
3161 -- Check whether that Requisition is Locked or not for the releases
3162
3163 cursor c_req_locks_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) is
3164 select count(1)
3165 from po_requisition_headers_all porh,
3166 po_requisition_lines_all porl,
3167 po_headers_all poh,
3168 po_line_locations_all poll
3169 where porh.requisition_header_id = porl.requisition_header_id AND
3170 porl.line_location_id = poll.line_location_id AND
3171 poh.po_header_id = poll.po_header_id AND
3172 poh.po_header_id = p_po_header_id_csr AND
3173 poll.po_release_id = p_po_release_id_csr AND
3174 porh.change_pENDing_flag = 'Y' ;
3175
3176 --Cursor to test whether changes are done on lines from multiple requesters
3177 CURSOR l_requestors_csr(c_grp_id_csr IN NUMBER)
3178 IS
3179 select UNIQUE(pda.deliver_to_person_id)
3180 from
3181 po_change_requests pcr,
3182 po_distributions_all pda
3183 where pcr.change_request_group_id = c_grp_id_csr
3184 AND pcr.request_level = 'LINE'
3185 AND pcr.document_line_id = pda.po_line_id
3186 and pda.deliver_to_person_id is not null
3187 union
3188 select UNIQUE(pda.deliver_to_person_id)
3189 from
3190 po_change_requests pcr,
3191 po_distributions_all pda
3192 where pcr.change_request_group_id = c_grp_id_csr
3193 AND pcr.request_level = 'SHIPMENT'
3194 AND pcr.document_line_location_id = pda.line_location_id
3195 and pda.deliver_to_person_id is not null;
3196
3197
3198
3199 -- Check whether there is a quantity change from a shipment which is made up of two dIFferent req lines
3200
3201 -- will be taken care of INIsProrateNeeded function
3202
3203 l_backing_req_count number;
3204 l_catp_change_count number;
3205 l_fps_change_count number;
3206 l_ship_change_count number;
3207 l_req_lock_cnt number;
3208 l_temp_line_loc_id number;
3209 l_ship_map number;
3210 l_api_name varchar2(50) := 'ROUTE_SCO_BIZ_RULES_FUNC';
3211 x_progress varchar2(1000);
3212
3213 l_change_group_id NUMBER;
3214 l_requester_id NUMBER;
3215 l_count_req NUMBER;
3216
3217 BEGIN
3218
3219 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:000';
3220
3221 --Bug 11732340
3222 --Check for unique requestor
3223 l_count_req := 0;
3224 OPEN l_requestors_csr(p_change_group_id);
3225 LOOP
3226 FETCH l_requestors_csr INTO l_requester_id;
3227 EXIT WHEN l_requestors_csr%NOTFOUND;
3228 l_count_req := l_count_req + 1;
3229 END LOOP;
3230
3231 close l_requestors_csr;
3232
3233 IF(l_count_req <> 1)
3234 THEN
3235 RETURN FALSE;
3236 END IF;
3237
3238
3239
3240
3241 --- Po should have one backing req and all shipments should be mapped to the req line
3242 IF(p_doc_type = 'PO') THEN
3243 /* OPEN c_reqs_count(p_po_header_id);
3244 FETCH c_reqs_count INTO l_backing_req_count;
3245
3246 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:001';
3247 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3248
3249 IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3250 CLOSE c_reqs_count;
3251 return FALSE;
3252 ELSIF (l_backing_req_count = 1) THEN
3253 OPEN c_req_map_ship(p_po_header_id);
3254 FETCH c_req_map_ship INTO l_ship_map;
3255 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:002';
3256 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3257 IF (l_ship_map >= 1) THEN
3258 CLOSE c_req_map_ship;
3259 return FALSE;
3260 END IF;
3261 CLOSE c_req_map_ship;
3262 END IF;
3263
3264 IF c_req_map_ship%ISOPEN THEN
3265 CLOSE c_req_map_ship;
3266 END IF;
3267 IF c_reqs_count%ISOPEN THEN
3268 CLOSE c_reqs_count;
3269 END IF; */
3270
3271 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:003';
3272 ---- Price changes for NCR should go to the buyer
3273
3274 OPEN c_cat_price_change(p_po_header_id);
3275 FETCH c_cat_price_change INTO l_catp_change_count;
3276 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_catp_change_count);
3277 IF (l_catp_change_count >= 1) THEN
3278 CLOSE c_cat_price_change;
3279 return FALSE;
3280 END IF;
3281
3282 IF c_cat_price_change%ISOPEN THEN
3283 CLOSE c_cat_price_change;
3284 END IF;
3285
3286 ---- FPS Price Changes for NCR should go to the buyer
3287 OPEN c_fps_price_change(p_po_header_id);
3288 FETCH c_fps_price_change INTO l_fps_change_count;
3289 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_fps_change_count);
3290 IF (l_fps_change_count >= 1) THEN
3291 CLOSE c_fps_price_change;
3292 return FALSE;
3293 END IF;
3294
3295 IF c_fps_price_change%ISOPEN THEN
3296 CLOSE c_fps_price_change;
3297 END IF;
3298
3299 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:004';
3300 --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3301
3302 OPEN c_req_locks(p_po_header_id);
3303 FETCH c_req_locks INTO l_req_lock_cnt;
3304 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3305
3306 IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3307 IF(l_req_lock_cnt >= 1) THEN
3308 CLOSE c_req_locks;
3309 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3310 return FALSE;
3311 END IF;
3312 END IF;
3313 IF c_req_locks%ISOPEN THEN
3314 CLOSE c_req_locks;
3315 END IF;
3316
3317 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:005';
3318
3319 return TRUE;
3320
3321
3322 ELSIF(p_doc_type = 'RELEASE') THEN
3323 --- Po should have one backing req and all shipments should be mapped to the req line
3324 /* x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:006';
3325 OPEN c_reqs_count_rel(p_po_header_id,p_po_release_id);
3326 FETCH c_reqs_count_rel INTO l_backing_req_count;
3327 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:007';
3328 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3329
3330 IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3331 CLOSE c_reqs_count_rel;
3332 return FALSE;
3333
3334 ELSIF l_backing_req_count = 1 THEN
3335 OPEN c_req_map_ship_rel(p_po_header_id,p_po_release_id);
3336 FETCH c_req_map_ship_rel INTO l_ship_map;
3337 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:008';
3338 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3339 IF l_ship_map >= 1 THEN
3340 CLOSE c_req_map_ship_rel;
3341 return FALSE;
3342 END IF;
3343 CLOSE c_req_map_ship_rel;
3344 END IF;
3345
3346 IF c_req_map_ship_rel%ISOPEN THEN
3347 CLOSE c_req_map_ship_rel;
3348 END IF;
3349 IF c_reqs_count_rel%ISOPEN THEN
3350 CLOSE c_reqs_count_rel;
3351 END IF; */
3352
3353 --- Ship Price Changes for the NCR releases should go to the buyer
3354 OPEN c_ship_price_change(p_po_header_id,p_po_release_id);
3355 FETCH c_ship_price_change INTO l_ship_change_count;
3356 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_ship_change_count);
3357 IF (l_ship_change_count >= 1) THEN
3358 CLOSE c_ship_price_change;
3359 return FALSE;
3360 END IF;
3361
3362 IF c_ship_price_change%ISOPEN THEN
3363 CLOSE c_ship_price_change;
3364 END IF;
3365
3366
3367
3368 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:009';
3369 --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3370
3371 OPEN c_req_locks_rel(p_po_header_id,p_po_release_id);
3372 FETCH c_req_locks_rel INTO l_req_lock_cnt;
3373 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3374
3375 IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3376 IF(l_req_lock_cnt >= 1) THEN
3377 CLOSE c_req_locks_rel;
3378 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3379 return FALSE;
3380 END IF;
3381 END IF;
3382 IF c_req_locks_rel%ISOPEN THEN
3383 CLOSE c_req_locks_rel;
3384 END IF;
3385
3386 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:010';
3387
3388 return TRUE;
3389 END IF; -- IF p_doc_type = PO or RELEASE
3390 RETURN TRUE;
3391 EXCEPTION
3392 WHEN OTHERS THEN
3393 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3394 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3395 g_module_prefix,
3396 x_progress || ':unexpected error' || Sqlerrm);
3397 END IF;
3398 raise;
3399 END ROUTE_SCO_BIZ_RULES_CHECK;
3400
3401 ------------------------------------------------------------------------------
3402
3403
3404 FUNCTION AUTO_APP_BIZ_RULES_CHECK (p_po_header_id IN NUMBER,p_po_release_id IN NUMBER,p_doc_type IN VARCHAR2)
3405 return boolean
3406
3407 IS
3408
3409 -- Split shipment check
3410
3411 cursor c_split_ships(p_po_header_id_csr IN NUMBER) is
3412 select count(1)
3413 from po_change_requests
3414 where parent_line_location_id is not null
3415 AND action_type = 'MODIFICATION'
3416 AND document_header_id = p_po_header_id_csr
3417 AND request_level = 'SHIPMENT'
3418 AND request_status = 'PENDING';
3419
3420
3421 -- Split shipment check for releses
3422 cursor c_split_ships_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3423 select count(1)
3424 from po_change_requests
3425 where parent_line_location_id is not null
3426 AND action_type = 'MODIFICATION'
3427 AND document_header_id = p_po_header_id_csr
3428 AND po_release_id = p_po_release_id_csr
3429 AND request_level = 'SHIPMENT'
3430 AND request_status = 'PENDING';
3431
3432 -- Cancellation request should go to buyer
3433
3434 cursor c_cancel_requests(p_po_header_id_csr IN NUMBER) is
3435 SELECT count(1)
3436 from po_change_requests
3437 where action_type = 'CANCELLATION'
3438 AND request_status = 'PENDING'
3439 AND document_header_id = p_po_header_id_csr;
3440
3441
3442 -- Cancellation request should go to buyer for the releases
3443 cursor c_cancel_requests_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3444 SELECT count(1)
3445 from po_change_requests
3446 where action_type = 'CANCELLATION'
3447 AND request_status = 'PENDING'
3448 AND document_header_id = p_po_header_id_csr
3449 AND po_release_id = p_po_release_id_csr;
3450
3451
3452 --Additional Change Request the unstructured change request
3453
3454 cursor c_add_changes(p_po_header_id_csr IN NUMBER) is
3455 select count(1)
3456 from po_change_requests
3457 where action_type = 'MODIFICATION'
3458 AND ADDITIONAL_CHANGES is not NULL
3459 AND request_status = 'PENDING'
3460 AND document_header_id = p_po_header_id_csr;
3461
3462 --Additional Change Request the unstructured change request for the releases
3463 cursor c_add_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3464 select count(1)
3465 from po_change_requests
3466 where action_type = 'MODIFICATION'
3467 AND ADDITIONAL_CHANGES is not NULL
3468 AND request_level = 'HEADER'
3469 AND request_status = 'PENDING'
3470 AND document_header_id = p_po_header_id_csr
3471 AND po_release_id = p_po_release_id_csr;
3472
3473
3474
3475
3476 -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment
3477 -- Cursor for checking whether sco is created during Acknowledgement or not.
3478 cursor c_sco_ack_ship(p_po_header_id_csr IN NUMBER) is
3479 SELECT acceptance_required_flag,revision_num
3480 FROM po_headers_all
3481 WHERE po_header_id = p_po_header_id_csr;
3482
3483 -- Cursor for checking the acknowledgement status of the shipment
3484 cursor c_sco_ack_rej_ship(p_po_header_id_csr IN NUMBER, c_revision_num IN NUMBER) is
3485 SELECT count(1)
3486 FROM po_acceptances pa,
3487 po_lines_archive_all pla,
3488 po_line_locations_archive_all plla
3489 WHERE plla.po_header_id = p_po_header_id_csr
3490 AND pa.accepted_flag = 'N'
3491 AND plla.po_line_id = pla.po_line_id
3492 AND pa.po_line_location_id = plla.line_location_id
3493 AND pa.revision_num = c_revision_num
3494 AND plla.revision_num = (SELECT max(plla2.revision_num)
3495 FROM po_line_locations_archive_all plla2
3496 WHERE plla2.line_location_id = plla.line_location_id
3497 AND plla.revision_num <= c_revision_num);
3498
3499
3500 -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment for the releases
3501 -- Cursor for checking whether sco is created during Acknowledgement or not.
3502 cursor c_sco_ack_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3503 SELECT acceptance_required_flag,revision_num
3504 FROM po_releases_all
3505 WHERE po_header_id = p_po_header_id_csr
3506 AND po_release_id = p_po_release_id_csr;
3507
3508 -- Cursor for checking the acknowledgement status of the shipment
3509 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
3510 SELECT count(1)
3511 FROM po_acceptances pa,
3512 po_lines_archive_all pla,
3513 po_line_locations_archive_all plla
3514 WHERE plla.po_header_id = p_po_header_id_csr
3515 AND pa.po_release_id = p_po_release_id_csr
3516 AND pa.accepted_flag = 'N'
3517 AND plla.po_release_id = pa.po_release_id
3518 AND plla.po_line_id = pla.po_line_id
3519 AND pa.po_line_location_id = plla.line_location_id
3520 AND pa.revision_num = c_revision_num
3521 AND plla.revision_num = (SELECT max(plla2.revision_num)
3522 FROM po_line_locations_archive_all plla2
3523 WHERE plla2.line_location_id = plla.line_location_id
3524 AND plla.revision_num <= c_revision_num);
3525
3526 -- Cursor for checking whether the signature is required or not
3527 /* 5550515
3528 cursor c_sgn_req_flag(p_po_header_id_csr IN NUMBER) IS
3529 SELECT acceptance_required_flag
3530 FROM po_headers_all
3531 WHERE po_header_id = p_po_header_id_csr;
3532 */
3533 cursor c_sgn_req_flag_po(p_po_header_id_csr IN NUMBER,c_revision_num IN NUMBER ) IS
3534 SELECT count(1)
3535 FROM po_acceptances
3536 WHERE po_header_id = p_po_header_id_csr
3537 and revision_num=c_revision_num
3538 and signature_flag='Y';
3539 /*
3540 cursor c_sgn_req_flag_rel(p_po_release_id_csr IN NUMBER, c_revision_num IN NUMBER) IS
3541 SELECT count(1)
3542 FROM po_acceptances
3543 WHERE po_release_id =p_po_release_id_csr
3544 and revision_num=c_revision_num
3545 and signature_flag='Y';
3546 */
3547 -- Cursor for checking for a supplier Item Change
3548 CURSOR c_supp_item_chg(p_po_header_id_csr IN NUMBER) IS
3549 SELECT count(1)
3550 FROM po_change_requests
3551 WHERE action_type = 'MODIFICATION'
3552 AND request_status = 'PENDING'
3553 AND request_level = 'LINE'
3554 AND new_supplier_part_number is not null
3555 AND document_header_id = p_po_header_id_csr;
3556
3557 l_split_shipment_request number;
3558 l_cancel_requests number;
3559 l_additional_change_requests number;
3560 l_ack_reject_cnt number;
3561 l_supp_item_chg_cnt number;
3562 l_temp number;
3563 l_accpt_req_flag po_headers_all.acceptance_required_flag%type;
3564 l_revision_num number;
3565 l_signature_required number :=0;
3566 l_ack_status_flag_count number;
3567 l_api_name varchar2(50) := 'AUTO_APP_BIZ_RULES';
3568 x_progress varchar2(1000);
3569 BEGIN
3570
3571 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:000';
3572
3573 IF(p_doc_type = 'PO') THEN
3574 -- Split shipment check
3575 /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3576 /*
3577 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:001';
3578 OPEN c_split_ships(p_po_header_id);
3579 FETCH c_split_ships INTO l_split_shipment_request;
3580 log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3581 IF(l_split_shipment_request > 0) THEN
3582 CLOSE c_split_ships;
3583 return FALSE;
3584 END IF;
3585 IF c_split_ships%ISOPEN THEN
3586 CLOSE c_split_ships;
3587 END IF;
3588 */
3589 /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3590 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:002';
3591 -- Cancellation request should go to buyer
3592 OPEN c_cancel_requests(p_po_header_id);
3593 FETCH c_cancel_requests INTO l_cancel_requests;
3594 log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3595 IF(l_cancel_requests>0) THEN
3596 CLOSE c_cancel_requests;
3597 return FALSE;
3598 END IF;
3599 IF c_cancel_requests%ISOPEN THEN
3600 CLOSE c_cancel_requests;
3601 END IF;
3602
3603 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:003';
3604 -- additional changes should go to the buyer
3605 OPEN c_add_changes(p_po_header_id);
3606 FETCH c_add_changes INTO l_additional_change_requests;
3607 log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3608 IF(l_additional_change_requests > 0 ) THEN
3609 CLOSE c_add_changes;
3610 return FALSE;
3611 END IF;
3612 IF c_add_changes%ISOPEN THEN
3613 CLOSE c_add_changes;
3614 END IF;
3615
3616 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:004';
3617 --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3618 -- at least one shipment, the SCO should be rOUTed to the buyer.
3619 OPEN c_sco_ack_ship(p_po_header_id);
3620 FETCH c_sco_ack_ship INTO l_accpt_req_flag,l_revision_num;
3621 IF(l_accpt_req_flag = 'Y') THEN
3622 OPEN c_sco_ack_rej_ship(p_po_header_id,l_revision_num);
3623 FETCH c_sco_ack_rej_ship INTO l_ack_status_flag_count;
3624 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3625 IF(l_ack_status_flag_count >= 1) THEN
3626 CLOSE c_sco_ack_rej_ship;
3627 CLOSE c_sco_ack_ship;
3628 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3629 return FALSE;
3630 END IF;
3631 END IF;
3632 IF c_sco_ack_ship%ISOPEN THEN
3633 CLOSE c_sco_ack_ship;
3634 END IF;
3635 IF c_sco_ack_rej_ship%ISOPEN THEN
3636 CLOSE c_sco_ack_rej_ship;
3637 END IF;
3638
3639 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:005';
3640 -- check whether the document requires signature or not
3641 OPEN c_sgn_req_flag_po(p_po_header_id,l_revision_num);
3642 FETCH c_sgn_req_flag_po INTO l_signature_required;
3643 log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3644
3645 IF(l_signature_required >=1) THEN
3646 CLOSE c_sgn_req_flag_po;
3647 return FALSE;
3648 END IF;
3649
3650 IF c_sgn_req_flag_po%ISOPEN THEN
3651 CLOSE c_sgn_req_flag_po;
3652 END IF;
3653
3654 -- check whether the supplier item change is requested or not
3655 OPEN c_supp_item_chg(p_po_header_id);
3656 FETCH c_supp_item_chg INTO l_supp_item_chg_cnt;
3657 log_message('AUTO_APP_BIZ_RULES_CHECK','Supplie Item Chnage ',l_supp_item_chg_cnt);
3658
3659 IF(l_supp_item_chg_cnt > 0) THEN
3660 CLOSE c_supp_item_chg;
3661 return FALSE;
3662 END IF;
3663 IF c_supp_item_chg%ISOPEN THEN
3664 CLOSE c_supp_item_chg;
3665 END IF;
3666
3667 return TRUE;
3668
3669 ELSIF(p_doc_type = 'RELEASE') THEN
3670 /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3671 /*
3672 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:006';
3673 OPEN c_split_ships_rel(p_po_header_id,p_po_release_id);
3674 FETCH c_split_ships_rel INTO l_split_shipment_request;
3675 log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3676 IF(l_split_shipment_request > 0) THEN
3677 CLOSE c_split_ships_rel;
3678 return FALSE;
3679 END IF;
3680 IF c_split_ships_rel%ISOPEN THEN
3681 CLOSE c_split_ships_rel;
3682 END IF;
3683 */
3684 /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3685 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:007';
3686 -- Cancellation request should go to buyer
3687 OPEN c_cancel_requests_rel(p_po_header_id,p_po_release_id);
3688 FETCH c_cancel_requests_rel INTO l_cancel_requests;
3689 log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3690 IF(l_cancel_requests>0) THEN
3691 CLOSE c_cancel_requests_rel;
3692 return FALSE;
3693 END IF;
3694 IF c_cancel_requests_rel%ISOPEN THEN
3695 CLOSE c_cancel_requests_rel;
3696 END IF;
3697
3698 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:008';
3699 -- Additional chnages requested should go to the buyer
3700 OPEN c_add_changes_rel(p_po_header_id,p_po_release_id);
3701 FETCH c_add_changes_rel INTO l_additional_change_requests;
3702 log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3703 IF(l_additional_change_requests > 0 ) THEN
3704 CLOSE c_add_changes_rel;
3705 return FALSE;
3706 END IF;
3707 IF c_add_changes_rel%ISOPEN THEN
3708 CLOSE c_add_changes_rel;
3709 END IF;
3710
3711 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:009';
3712 --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3713 -- at least one shipment, the SCO should be rOUTed to the buyer.
3714 OPEN c_sco_ack_ship_rel(p_po_header_id,p_po_release_id);
3715 FETCH c_sco_ack_ship_rel INTO l_accpt_req_flag,l_revision_num;
3716 IF(l_accpt_req_flag = 'Y') THEN
3717 OPEN c_sco_ack_rej_ship_rel(p_po_header_id,l_revision_num,p_po_release_id);
3718 FETCH c_sco_ack_rej_ship_rel INTO l_ack_status_flag_count;
3719 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3720 IF(l_ack_status_flag_count >= 1) THEN
3721 CLOSE c_sco_ack_rej_ship_rel;
3722 CLOSE c_sco_ack_ship_rel;
3723 log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3724 return FALSE;
3725 END IF;
3726 END IF;
3727
3728 IF c_sco_ack_ship_rel%ISOPEN THEN
3729 CLOSE c_sco_ack_ship_rel;
3730 END IF;
3731 IF c_sco_ack_rej_ship_rel%ISOPEN THEN
3732 CLOSE c_sco_ack_rej_ship_rel;
3733 END IF;
3734 /*
3735 -- check whether the document requires signature or not
3736 OPEN c_sgn_req_flag_rel(p_po_release_id,l_revision_num);
3737 FETCH c_sgn_req_flag_rel INTO l_signature_required;
3738
3739 log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3740
3741 IF(l_signature_required <1) THEN
3742 CLOSE c_sgn_req_flag_rel;
3743 return FALSE;
3744 END IF;
3745
3746 IF c_sgn_req_flag_rel%ISOPEN THEN
3747 CLOSE c_sgn_req_flag_rel;
3748 END IF;
3749 */
3750
3751 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:010';
3752 return TRUE;
3753
3754 END IF; -- If po_doc_type = PO or RELEASE
3755
3756 EXCEPTION
3757 WHEN OTHERS THEN
3758 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3759 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3760 g_module_prefix,
3761 x_progress || ':unexpected error' || Sqlerrm);
3762 END IF;
3763 raise;
3764
3765 END AUTO_APP_BIZ_RULES_CHECK;
3766
3767 ------------------------------------------------------------------------------
3768 PROCEDURE PROMISE_DATE_CHANGE(itemtype IN VARCHAR2,
3769 itemkey IN VARCHAR2,
3770 actid IN NUMBER,
3771 funcmode IN VARCHAR2,
3772 resultout OUT NOCOPY VARCHAR2)
3773 IS
3774
3775 l_po_header_id po_headers_all.po_header_id%TYPE;
3776 x_progress VARCHAR2(1000);
3777 l_change_group_id po_change_requests.change_request_group_id%TYPE;
3778
3779 BEGIN
3780
3781
3782 IF (funcmode = 'RUN') THEN
3783
3784 x_progress := 'PROMISE_DATE_CHANGE:000';
3785
3786 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3787 itemkey => itemkey,
3788 aname => 'PO_HEADER_ID');
3789
3790 l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3791 itemkey => itemkey,
3792 aname => 'CHANGE_REQUEST_GROUP_ID');
3793 -- IF only a promise date change return yes otherwise no
3794
3795 IF PROMISEDATECHANGE(l_po_header_id,l_change_group_id)=TRUE THEN
3796 resultout := wf_engine.eng_completed || ':' || 'Y';
3797 ELSE
3798 resultout := wf_engine.eng_completed || ':' || 'N';
3799 END IF;
3800 x_progress:= 'PROMISE_DATE_CHANGE:001';
3801 log_message('PROMISE_DATE_CHANGE','Only promised date changed',resultout);
3802 return;
3803 END IF;
3804
3805 EXCEPTION
3806 WHEN OTHERS THEN
3807 -- The line below records this function call INthe error
3808 -- system INthe case of an exception.
3809
3810 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3811 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3812 g_module_prefix,
3813 x_progress || ':unexpected error' || Sqlerrm);
3814 END IF;
3815
3816 wf_core.context('POSCHORD', 'PROMISE_DATE_CHANGE',itemtype, itemkey, to_char(actid),funcmode);
3817
3818 raise;
3819
3820 END PROMISE_DATE_CHANGE;
3821
3822
3823 FUNCTION PROMISEDATECHANGE(p_po_header_id IN NUMBER, p_change_group_id IN NUMBER)
3824 return boolean
3825 is
3826
3827 CURSOR c_shipment_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3828 select OLD_QUANTITY,
3829 NEW_QUANTITY,
3830 OLD_PROMISED_DATE,
3831 NEW_PROMISED_DATE,
3832 OLD_PRICE,
3833 NEW_PRICE,
3834 OLD_AMOUNT,
3835 NEW_AMOUNT
3836 from po_change_requests
3837 where initiator='SUPPLIER'
3838 AND action_type='MODIFICATION'
3839 AND request_level='SHIPMENT'
3840 AND change_request_group_id = l_change_group_id_csr
3841 AND request_status IN('PENDING','BUYER_APP')
3842 AND document_header_id=l_po_header_id
3843 AND ( (nvl(new_promised_date,sysdate) <> nvl(old_promised_date,sysdate-1)) OR
3844 (nvl(new_promised_date,old_promised_date-1)<>old_promised_date) OR
3845 (nvl(old_promised_date,new_promised_date-1)<>new_promised_date)
3846 );
3847
3848 CURSOR c_line_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3849 select count(1)
3850 from po_change_requests
3851 where initiator='SUPPLIER'
3852 AND action_type='MODIFICATION'
3853 AND change_request_group_id = l_change_group_id_csr
3854 AND request_level='LINE'
3855 AND request_status IN('PENDING','BUYER_APP')
3856 AND document_header_id=l_po_header_id;
3857
3858
3859 l_only_promised_date_change boolean :=TRUE;
3860 l_old_quantity po_change_requests.old_quantity%type;
3861 l_new_quantity po_change_requests.new_quantity%type;
3862 l_old_promised_date po_change_requests.old_promised_date%type;
3863 l_new_promised_date po_change_requests.new_promised_date%type;
3864 l_old_price po_change_requests.old_price%type;
3865 l_new_price po_change_requests.new_price%type;
3866 l_old_amount po_change_requests.old_amount%type;
3867 l_new_amount po_change_requests.new_amount%type;
3868 x_progress VARCHAR2(1000);
3869 l_line_changes_counter number:=0;
3870 l_api_name varchar2(50) := 'PROMISEDATECHANGE';
3871
3872
3873 BEGIN
3874
3875 x_progress:='PROMISEDATECHANGE:000';
3876
3877 IF (c_line_change%ISOPEN) THEN
3878 CLOSE c_line_change;
3879 ELSE
3880 OPEN c_line_change(p_po_header_id,p_change_group_id);
3881 END IF;
3882 FETCH c_line_change INTO l_line_changes_counter ;
3883 x_progress:='PROMISEDATECHANGE:001';
3884 log_message('PROMISEDATECHANGE','Price Changes Line Level',l_line_changes_counter);
3885
3886 IF l_line_changes_counter >0 THEN
3887 CLOSE c_line_change;
3888 return FALSE;
3889 END IF;
3890
3891 IF (c_line_change%ISOPEN) THEN
3892 CLOSE c_line_change;
3893 END IF;
3894
3895 x_progress:='PROMISEDATECHANGE:002';
3896
3897 IF (c_shipment_change%ISOPEN) THEN
3898 CLOSE c_shipment_change;
3899 else
3900 OPEN c_shipment_change(p_po_header_id,p_change_group_id);
3901 END IF;
3902 LOOP
3903 FETCH c_shipment_change INTO
3904 l_old_quantity,
3905 l_new_quantity,
3906 l_old_promised_date,
3907 l_new_promised_date,
3908 l_old_price,
3909 l_new_price,
3910 l_old_amount,
3911 l_new_amount;
3912 x_progress:='PROMISEDATECHANGE:003';
3913 log_message('PROMISEDATECHANGE','Quantity Changes',l_old_quantity || ', '||l_new_quantity);
3914 log_message('PROMISEDATECHANGE','Promise date Changes',l_old_promised_date || ', '||l_new_promised_date);
3915 log_message('PROMISEDATECHANGE','Shipment Price Changes',l_old_promised_date || ', '||l_new_promised_date);
3916 -- IF only a promise date change return TRUE otherwise return FALSE
3917 EXIT WHEN c_shipment_change%NOTFOUND;
3918 EXIT WHEN (l_only_promised_date_change=FALSE);
3919
3920 IF nvl(l_old_quantity,0)<>nvl(l_new_quantity,0) THEN
3921 l_only_promised_date_change:= FALSE;
3922 ELSIF nvl(l_old_price,0)<>nvl(l_new_price,nvl(l_old_price,0)) THEN
3923 l_only_promised_date_change:= FALSE;
3924 ELSIF nvl(l_old_amount,0)<>nvl(l_new_amount,0) THEN
3925 l_only_promised_date_change:= FALSE;
3926 ELSIF (l_new_promised_date is null AND l_old_promised_date is null) THEN
3927 l_only_promised_date_change:= FALSE;
3928 END IF;
3929 END LOOP;
3930 CLOSE c_shipment_change;
3931 x_progress:='PROMISEDATECHANGE:004';
3932 return l_only_promised_date_change;
3933 exception
3934 when others THEN
3935 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3936 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3937 g_module_prefix,
3938 x_progress || ':unexpected error' || Sqlerrm);
3939 END IF;
3940 raise;
3941 END PROMISEDATECHANGE;
3942
3943 ------------------------------------------------------------------------------
3944
3945 PROCEDURE INITIATE_RCO_FLOW (itemtype IN VARCHAR2,
3946 itemkey IN VARCHAR2,
3947 actid IN NUMBER,
3948 funcmode IN VARCHAR2,
3949 resultout OUT NOCOPY VARCHAR2)
3950 IS
3951 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
3952 l_po_header_id po_headers_all.po_header_id%type;
3953 l_po_release_id po_releases_all.po_release_id%type;
3954 x_progress VARCHAR2(1000);
3955
3956 BEGIN
3957 x_progress := 'INITIATE_RCO_FLOW:000';
3958 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3959 itemkey => itemkey,
3960 aname => 'PO_HEADER_ID');
3961 l_po_release_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3962 itemkey => itemkey,
3963 aname => 'PO_RELEASE_ID');
3964
3965
3966 x_progress := 'INITIATE_RCO_FLOW:001';
3967 log_message('INITIATE_RCO_FLOW','Po Header Id',l_po_header_id);
3968 INITIATERCOFLOW (l_po_header_id,l_po_release_id,l_change_group_id);
3969 -- set the x_change_request_group_id number
3970 x_progress:= 'INITIATE_RCO_FLOW:002';
3971 log_message('INITIATE_RCO_FLOW','Change Req Group Id',l_change_group_id);
3972
3973 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3974 itemkey => itemkey,
3975 aname => 'REQ_CHANGE_REQUEST_GROUP_ID',
3976 avalue => l_change_group_id);
3977
3978 resultout:=wf_engine.eng_completed;
3979 x_progress := 'INITIATE_RCO_FLOW:003';
3980
3981 EXCEPTION
3982
3983 when others THEN
3984
3985 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3986 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3987 g_module_prefix,
3988 x_progress || ':unexpected error' || Sqlerrm);
3989 END IF;
3990 wf_core.context('POSCHORD', 'INITIATE_RCO_FLOW', itemtype, itemkey, to_char(actid), funcmode);
3991 raise;
3992 END INITIATE_RCO_FLOW;
3993
3994
3995 PROCEDURE INITIATERCOFLOW(p_po_header_id IN NUMBER, p_po_release_id IN NUMBER, x_change_group_id OUT NOCOPY NUMBER)
3996 IS
3997
3998 --- this also should hANDle non sync values
3999 CURSOR c_change_request(p_po_header_id_csr IN NUMBER) IS
4000 SELECT pcr.change_request_group_id,
4001 pcr.new_price new_price,
4002 pcr.new_quantity,
4003 pcr.new_start_date,
4004 pcr.new_expiration_date,
4005 pcr.new_amount,
4006 pcr.request_level,
4007 --pcr.new_promised_date,
4008 nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
4009 pcr.request_reason,
4010 prla.requisition_line_id,
4011 prla.requisition_header_id,
4012 prla.line_location_id,
4013 prda.distribution_id
4014 FROM po_change_requests pcr,
4015 po_requisition_lines_all prla,
4016 po_req_distributions_all prda,
4017 po_line_locations_all plla
4018 WHERE document_header_id= p_po_header_id_csr
4019 AND request_status='BUYER_APP'
4020 AND change_active_flag='Y'
4021 AND initiator='SUPPLIER'
4022 --AND pcr.document_line_location_id = prla.line_location_id(+)
4023 AND prda.requisition_line_id=prla.requisition_line_id
4024 AND pcr.request_level='SHIPMENT'
4025 AND plla.line_location_id = prla.line_location_id
4026 AND plla.po_line_id=pcr.document_line_id
4027 AND plla.po_header_id=pcr.document_header_id
4028 AND ( pcr.new_price is not null --New change JAI
4029 OR pcr.new_quantity is not null
4030 OR pcr.new_start_date is not null
4031 OR pcr.new_expiration_date is not null
4032 OR pcr.new_amount is not null
4033 )
4034 UNION
4035 SELECT pcr.change_request_group_id,
4036 pcr.new_price,
4037 null new_quantity,
4038 null new_start_date ,
4039 null new_expiration_date,
4040 null new_amount,
4041 pcr.request_level,
4042 --null new_promised_date,
4043 null new_need_by_date,
4044 pcr.request_reason,
4045 prla.requisition_line_id,
4046 prla.requisition_header_id,
4047 prla.line_location_id,
4048 null distribution_id
4049 FROM po_change_requests pcr,
4050 po_line_locations_all plla,
4051 po_requisition_lines_all prla
4052 WHERE document_header_id=p_po_header_id_csr
4053 AND request_status='BUYER_APP'
4054 AND change_active_flag='Y'
4055 AND initiator='SUPPLIER'
4056 AND pcr.request_level='LINE'
4057 AND plla.line_location_id = prla.line_location_id
4058 AND plla.po_line_id=pcr.document_line_id
4059 AND plla.po_header_id=pcr.document_header_id;
4060
4061 --for releases
4062
4063 CURSOR c_change_request_rel(p_po_release_id_csr IN NUMBER) IS
4064 SELECT pcr.change_request_group_id,
4065 pcr.new_price new_price,
4066 pcr.new_quantity,
4067 pcr.new_start_date,
4068 pcr.new_expiration_date,
4069 pcr.new_amount,
4070 pcr.request_level,
4071 --pcr.new_promised_date,
4072 nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
4073 pcr.request_reason,
4074 prla.requisition_line_id,
4075 prla.requisition_header_id,
4076 prla.line_location_id,
4077 prda.distribution_id
4078 FROM po_change_requests pcr,
4079 po_requisition_lines_all prla,
4080 po_req_distributions_all prda,
4081 po_line_locations_all plla
4082 WHERE pcr.po_release_id= p_po_release_id_csr
4083 AND request_status='BUYER_APP'
4084 AND change_active_flag='Y'
4085 AND initiator='SUPPLIER'
4086 --AND pcr.document_line_location_id = prla.line_location_id(+)
4087 AND prda.requisition_line_id=prla.requisition_line_id
4088 AND pcr.request_level='SHIPMENT'
4089 AND plla.line_location_id = prla.line_location_id
4090 AND plla.po_line_id=pcr.document_line_id
4091 AND plla.po_header_id=pcr.document_header_id
4092 AND plla.po_release_id =pcr.po_release_id
4093 AND ( pcr.new_price is not null --New change JAI
4094 OR pcr.new_quantity is not null
4095 OR pcr.new_start_date is not null
4096 OR pcr.new_expiration_date is not null
4097 OR pcr.new_amount is not null
4098 )
4099 UNION
4100 SELECT pcr.change_request_group_id,
4101 pcr.new_price,
4102 null new_quantity,
4103 null new_start_date ,
4104 null new_expiration_date,
4105 null new_amount,
4106 pcr.request_level,
4107 --null new_promised_date,
4108 null new_need_by_date,
4109 pcr.request_reason,
4110 prla.requisition_line_id,
4111 prla.requisition_header_id,
4112 prla.line_location_id,
4113 null distribution_id
4114 FROM po_change_requests pcr,
4115 po_line_locations_all plla,
4116 po_requisition_lines_all prla
4117 WHERE pcr.po_release_id=p_po_release_id_csr
4118 AND request_status='BUYER_APP'
4119 AND change_active_flag='Y'
4120 AND initiator='SUPPLIER'
4121 AND pcr.request_level='LINE'
4122 AND plla.line_location_id = prla.line_location_id
4123 AND plla.po_line_id=pcr.document_line_id
4124 AND plla.po_header_id=pcr.document_header_id;
4125
4126
4127
4128 --l_change_group_id po_change_requests.change_request_group_id%type ;
4129 l_change_group_id po_change_requests.change_request_group_id%type ;
4130 l_new_price po_change_requests.new_price%type ;
4131 l_new_quantity po_change_requests.new_quantity%type ;
4132 l_new_start_date po_change_requests.new_start_date%type ;
4133 l_new_expiration_date po_change_requests.new_expiration_date%type ;
4134 l_new_amount po_change_requests.new_amount%type ;
4135 l_request_level po_change_requests.request_level%type ;
4136 l_new_promised_date po_change_requests.new_promised_date%type ;
4137 l_new_need_by_date po_change_requests.new_need_by_date%type ;
4138 l_request_reason po_change_requests.request_reason%type ;
4139 l_requisition_line_id po_requisition_lines_all.requisition_line_id%type ;
4140 l_requisition_header_id po_requisition_lines_all.requisition_header_id%type ;
4141 l_line_location_id po_requisition_lines_all.line_location_id%type ;
4142 l_req_distribution_id po_req_distributions_all.distribution_id%type ;
4143 l_change_table PO_REQ_CHANGE_TABLE;
4144 l_cancel_table PO_REQ_CANCEL_TABLE:=null;
4145 l_rec_count number :=0;
4146 l_req_hdr_id number;
4147 l_api_version number := 1.0;
4148 l_api_name varchar2(100) := 'INITIATERCOFLOW';
4149 x_progress varchar2(1000);
4150 l_po_line_id number;
4151 x_return_status VARCHAR2(10);
4152 x_retMsg VARCHAR2(2000):='';
4153 x_errTable PO_REQ_CHANGE_ERR_TABLE;
4154 x_errCode VARCHAR2(10);
4155 l_dummy_table_number po_tbl_number := po_tbl_number();
4156
4157 /* Bug 7422622 - Added the following variables to check whether AME
4158 the setup is done. If yes then clear the approval list. - Start*/
4159
4160 l_application_id number :=201;
4161 l_ame_transaction_type po_document_types.ame_transaction_type%TYPE;
4162
4163 /* Bug 7422622 - End */
4164
4165 BEGIN
4166
4167 x_retMsg :='';
4168 x_progress := 'INITIATERCOFLOW:000';
4169
4170 l_change_table:=PO_REQ_CHANGE_TABLE(
4171 req_line_id => po_tbl_number(),
4172 req_dist_id => po_tbl_number(),
4173 price => po_tbl_number(),
4174 quantity => po_tbl_number(),
4175 need_by => po_tbl_date(),
4176 start_date => po_tbl_date(),
4177 END_date => po_tbl_date(),
4178 amount => po_tbl_number(),
4179 type => po_tbl_varchar60(),
4180 change_reason => po_tbl_VARCHAR2000());
4181
4182 if p_po_release_id is not null then
4183
4184 OPEN c_change_request_rel (p_po_release_id);
4185 l_rec_count :=1;
4186 loop
4187
4188
4189 FETCH c_change_request_rel INTO
4190 l_change_group_id,
4191 l_new_price ,
4192 l_new_quantity,
4193 l_new_start_date,
4194 l_new_expiration_date,
4195 l_new_amount,
4196 l_request_level,
4197 --l_new_promised_date,
4198 l_new_need_by_date,
4199 l_request_reason,
4200 l_requisition_line_id,
4201 l_requisition_header_id,
4202 l_line_location_id,
4203 l_req_distribution_id;
4204
4205
4206 EXIT WHEN c_change_request_rel%NOTFOUND;
4207 x_progress := 'INITIATERCOFLOW:001';
4208
4209 --Filling the table with data
4210
4211 l_change_table.req_line_id.extend(1);
4212 l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4213 l_change_table.req_dist_id.extend(1);
4214 l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4215 l_change_table.price.extend(1);
4216 l_change_table.price(l_rec_count):=l_new_price;
4217 l_change_table.quantity.extend(1);
4218 l_change_table.quantity(l_rec_count):=l_new_quantity;
4219 l_change_table.need_by.extend(1);
4220 l_change_table.start_date.extend(1);
4221 l_change_table.END_date.extend(1);
4222 l_change_table.amount.extend(1);
4223 l_change_table.amount(l_rec_count):=l_new_amount;
4224 l_change_table.type.extend(1);
4225 l_change_table.change_reason.extend(1);
4226 l_change_table.change_reason(l_rec_count):=l_request_reason;
4227
4228 l_rec_count:=l_rec_count+1;
4229
4230 END loop;
4231
4232 CLOSE c_change_request_rel;
4233
4234 else
4235
4236 OPEN c_change_request(p_po_header_id);
4237 l_rec_count :=1;
4238 loop
4239
4240
4241 FETCH c_change_request INTO
4242 l_change_group_id,
4243 l_new_price ,
4244 l_new_quantity,
4245 l_new_start_date,
4246 l_new_expiration_date,
4247 l_new_amount,
4248 l_request_level,
4249 --l_new_promised_date,
4250 l_new_need_by_date,
4251 l_request_reason,
4252 l_requisition_line_id,
4253 l_requisition_header_id,
4254 l_line_location_id,
4255 l_req_distribution_id;
4256
4257
4258 EXIT WHEN c_change_request%NOTFOUND;
4259 x_progress := 'INITIATERCOFLOW:001';
4260
4261 --Filling the table with data
4262
4263 l_change_table.req_line_id.extend(1);
4264 l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4265 l_change_table.req_dist_id.extend(1);
4266 l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4267 l_change_table.price.extend(1);
4268 l_change_table.price(l_rec_count):=l_new_price;
4269 l_change_table.quantity.extend(1);
4270 l_change_table.quantity(l_rec_count):=l_new_quantity;
4271 l_change_table.need_by.extend(1);
4272 l_change_table.start_date.extend(1);
4273 l_change_table.END_date.extend(1);
4274 l_change_table.amount.extend(1);
4275 l_change_table.amount(l_rec_count):=l_new_amount;
4276 l_change_table.type.extend(1);
4277 l_change_table.change_reason.extend(1);
4278 l_change_table.change_reason(l_rec_count):=l_request_reason;
4279
4280 l_rec_count:=l_rec_count+1;
4281
4282 END loop;
4283
4284 CLOSE c_change_request;
4285
4286 end if;
4287
4288 x_progress := 'INITIATERCOFLOW:002 Call Save Req Change';
4289
4290 PO_RCO_VALIDATION_PVT.Save_ReqChange( l_api_version,
4291 x_return_status,
4292 l_requisition_header_id,
4293 l_change_table ,
4294 l_cancel_table ,
4295 x_change_group_id,
4296 x_retMsg ,
4297 x_errTable);
4298
4299 IF x_return_status IS NOT NULL AND x_return_status = FND_API.g_ret_sts_success THEN
4300 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4301 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4302 g_module_prefix,
4303 x_progress
4304 || 'x_return_status=' || x_return_status
4305 || 'x_change_group_id = '|| x_change_group_id);
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 END IF;
4316 END IF;
4317
4318 x_progress := 'INITIATERCOFLOW:003';
4319
4320 /* Bug 7422622 -- Check whether AME is setup for Requistion flow. If
4321 the setup is done then clear the approval list.*/
4322
4323 SELECT ame_transaction_type
4324 INTO l_ame_transaction_type
4325 FROM po_document_types
4326 WHERE document_type_code = 'CHANGE_REQUEST' and
4327 document_subtype = 'REQUISITION';
4328
4329 if(l_ame_transaction_type is not null) then
4330
4331 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4332 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','l_ame_transaction_type = ' ||l_ame_transaction_type);
4333 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','l_requisition_header_id = ' ||l_requisition_header_id);
4334 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','applicationId = ' ||l_application_id);
4335 END IF;
4336
4337 ame_api2.clearAllApprovals( applicationIdIn => l_application_id,
4338 transactionIdIn => l_requisition_header_id,
4339 transactionTypeIn => l_ame_transaction_type
4340 );
4341 end if;
4342 /* Bug 7422622 - End */
4343 --- Ip requirement
4344 update PO_CHANGE_REQUESTS
4345 set Parent_change_request_id = x_change_group_id
4346 where change_request_group_id= l_change_group_id;
4347
4348 Exception
4349
4350 when others THEN
4351 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4352 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4353 g_module_prefix,
4354 x_progress || ':unexpected error' || Sqlerrm);
4355 END IF;
4356
4357 raise;
4358
4359
4360 END INITIATERCOFLOW;
4361 ------------------------------------------------------------------------------
4362
4363
4364 PROCEDURE START_RCO_WORKFLOW (itemtype IN VARCHAR2,
4365 itemkey IN VARCHAR2,
4366 actid IN NUMBER,
4367 funcmode IN VARCHAR2,
4368 resultout OUT NOCOPY VARCHAR2) is
4369
4370 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4371 x_progress VARCHAR2(1000);
4372 x_apprv_status VARCHAR2(1);
4373
4374 BEGIN
4375
4376 x_progress := 'START_RCO_WORKFLOW:000';
4377
4378 l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4379 itemkey => itemkey,
4380 aname => 'REQ_CHANGE_REQUEST_GROUP_ID');
4381
4382 x_progress := 'START_RCO_WORKFLOW:001';
4383
4384 /* Call the ip API with change_request_group_id to set the approval_required_flag */
4385
4386 PO_RCOTOLERANCE_GRP.SET_APPROVAL_REQUIRED_FLAG(l_change_group_id,x_apprv_status);
4387
4388
4389 STARTRCOWORKFLOW (l_change_group_id);
4390
4391 resultout:=wf_engine.eng_completed;
4392
4393 x_progress := 'START_RCO_WORKFLOW:002';
4394
4395
4396 exception
4397 when others THEN
4398
4399 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4400 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4401 g_module_prefix,
4402 x_progress || ':unexpected error' || Sqlerrm);
4403 END IF;
4404
4405 -- The line below records this function call INthe error
4406 -- system INthe case of an exception.
4407 wf_core.context('POSCHORD', 'START_RCO_WORKFLOW', itemtype, itemkey, to_char(actid), funcmode);
4408
4409 raise;
4410
4411
4412 END START_RCO_WORKFLOW;
4413 ------------------------------------------------------------------------------
4414
4415 PROCEDURE STARTRCOWORKFLOW(p_change_request_group_id IN NUMBER) is
4416
4417
4418
4419 l_api_version number := 1.0;
4420 l_api_name varchar2(100) := 'STARTRCOWF';
4421 x_return_status VARCHAR2(10);
4422 x_change_request_group_id number;
4423 x_retMsg VARCHAR2(2000):='';
4424 x_errTable PO_REQ_CHANGE_ERR_TABLE;
4425 x_errCode VARCHAR2(10);
4426 x_progress varchar2(1000);
4427 l_dummy_table_number po_tbl_number := po_tbl_number();
4428
4429
4430 BEGIN
4431
4432 x_retMsg :='';
4433 x_progress := 'STARTRCOWORKFLOW:000 Call Submit Req Change';
4434
4435 -- get the x_change_request_group_id
4436
4437 PO_RCO_VALIDATION_PVT.Submit_ReqChange (l_api_version ,
4438 x_return_status,
4439 p_change_request_group_id,--x_change_request_group_id,
4440 'N',-- p_fundscheck_flag IN VARCHAR2,
4441 'Please',
4442 'SUPPLIER',
4443 x_retMsg ,
4444 x_errCode ,
4445 x_errTable );
4446
4447 IF x_return_status IS NOT NULL AND x_return_status = FND_API.g_ret_sts_success THEN
4448 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4449 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4450 g_module_prefix,
4451 x_progress
4452 || ' x_return_status=' || x_return_status);
4453 END IF;
4454
4455 ELSE
4456 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4457 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4458 g_module_prefix,
4459 x_progress
4460 ||' x_return_status = ' || x_return_status
4461 ||' x_retMsg = ' || x_retMsg
4462 ||' x_errCode = ' || x_errCode);
4463 END IF;
4464 END IF;
4465
4466 x_progress := 'STARTRCOWORKFLOW:001';
4467
4468
4469
4470 Exception
4471
4472 when others THEN
4473 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4474 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4475 g_module_prefix,
4476 x_progress || ':unexpected error' || Sqlerrm);
4477 END IF;
4478
4479
4480 raise;
4481
4482
4483 END STARTRCOWORKFLOW;
4484 ------------------------------------------------------------------------------
4485
4486 PROCEDURE MARK_SCO_FOR_REQ(itemtype IN VARCHAR2,
4487 itemkey IN VARCHAR2,
4488 actid IN NUMBER,
4489 funcmode IN VARCHAR2,
4490 resultout OUT NOCOPY VARCHAR2)
4491 IS
4492
4493 CURSOR l_planners_csr(c_po_header_id IN NUMBER)
4494 IS
4495 select UNIQUE(porh.PREPARER_ID)
4496 from po_requisition_headers_all porh,
4497 po_requisition_lines_all porl,
4498 po_headers_all poh,
4499 po_line_locations_all poll
4500 where porh.requisition_header_id = porl.requisition_header_id AND
4501 porl.line_location_id = poll.line_location_id AND
4502 poh.po_header_id = poll.po_header_id AND
4503 poh.po_header_id = c_po_header_id;
4504 --Bug 5053593.
4505 CURSOR l_requestors_csr(c_grp_id_csr IN NUMBER)
4506 IS
4507 select pda.deliver_to_person_id
4508 from
4509 po_change_requests pcr,
4510 po_distributions_all pda
4511 where pcr.change_request_group_id = c_grp_id_csr
4512 AND pcr.request_level = 'LINE'
4513 AND pcr.document_line_id = pda.po_line_id
4514 union
4515 select pda.deliver_to_person_id
4516 from
4517 po_change_requests pcr,
4518 po_distributions_all pda
4519 where pcr.change_request_group_id = c_grp_id_csr
4520 AND pcr.request_level = 'SHIPMENT'
4521 AND pcr.document_line_location_id = pda.line_location_id;
4522
4523 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4524 x_progress VARCHAR2(1000);
4525 l_planner_username fnd_user.user_name%type;
4526 l_planner_disp_name VARCHAR2(2000);
4527 l_requester_username fnd_user.user_name%type;
4528 l_requester_disp_name VARCHAR2(2000);
4529 l_requester_id number;
4530 l_planner_id number;
4531 l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
4532 count_rec NUMBER;
4533 BEGIN
4534
4535 x_progress :='MARK_SCO_FOR_REQ:000';
4536
4537 --Bug 11732340
4538 --Change group id to get the requester
4539 l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4540 itemkey => itemkey,
4541 aname => 'CHANGE_REQUEST_GROUP_ID');
4542
4543 /*
4544 OPEN l_planners_csr(l_change_group_id );
4545 */
4546 l_po_header_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype,
4547 itemkey => itemkey,
4548 aname => 'PO_HEADER_ID');
4549
4550 count_rec := 0;
4551 OPEN l_planners_csr(l_po_header_id);
4552 LOOP
4553 FETCH l_planners_csr INTO l_planner_id;
4554 EXIT WHEN l_planners_csr%NOTFOUND;
4555 count_rec := count_rec + 1;
4556
4557 END LOOP;
4558
4559 IF (count_rec <> 1 )--Means there are more than 1 planners for the req
4560 THEN
4561 l_planner_id := NULL;
4562 END IF;
4563
4564 close l_planners_csr;
4565
4566 IF(l_planner_id is not null) THEN
4567
4568 x_progress:= 'MARK_SCO_FOR_REQ:001';
4569
4570
4571 -- Set the notIFication to be sent to the Requester
4572 wf_directory.GetUserName( p_orig_system => 'PER',
4573 p_orig_system_id => l_planner_id,
4574 p_name => l_planner_username,
4575 p_display_name => l_planner_disp_name);
4576
4577 x_progress:= 'MARK_SCO_FOR_REQ:002';
4578 log_message('MARK_SCO_FOR_REQ','Planner User Name ',l_planner_username);
4579
4580 wf_engine.SetItemAttrText(itemtype => itemtype,
4581 itemkey => itemkey,
4582 aname => 'PREPARER_USERNAME',
4583 avalue => l_planner_username);
4584 ELSE
4585 OPEN l_requestors_csr(l_change_group_id);
4586 FETCH l_requestors_csr INTO l_requester_id;
4587 close l_requestors_csr;
4588 x_progress:= 'MARK_SCO_FOR_REQ:003';
4589
4590
4591 wf_directory.GetUserName( p_orig_system => 'PER',
4592 p_orig_system_id => l_requester_id,
4593 p_name => l_requester_username,
4594 p_display_name => l_requester_disp_name);
4595
4596 x_progress:= 'MARK_SCO_FOR_REQ:004';
4597 log_message('MARK_SCO_FOR_REQ','Requester User Name ',l_requester_username);
4598
4599 wf_engine.SetItemAttrText(itemtype => itemtype,
4600 itemkey => itemkey,
4601 aname => 'PREPARER_USERNAME',
4602 avalue => l_requester_username);
4603 END IF;
4604 wf_engine.SetItemAttrText(itemtype => itemtype,
4605 itemkey => itemkey,
4606 aname => 'NOTIF_USAGE',
4607 avalue =>'REQ');
4608 update po_change_requests
4609 set request_status ='REQ_APP',
4610 responded_by = fnd_global.user_id,
4611 response_date = sysdate
4612 where change_request_group_id = l_change_group_id
4613 AND request_status = 'PENDING';
4614
4615
4616 resultout:=wf_engine.eng_completed;
4617
4618 x_progress := 'MARK_SCO_FOR_REQ:005';
4619
4620
4621
4622 exception
4623
4624 when others THEN
4625
4626 IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4627 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4628 g_module_prefix,
4629 x_progress || ':unexpected error' || Sqlerrm);
4630 END IF;
4631 wf_core.context('POSCHORD', 'MARK_SCO_FOR_REQ', itemtype, itemkey, to_char(actid), funcmode);
4632
4633 raise;
4634
4635 END MARK_SCO_FOR_REQ;
4636
4637 ------------------------------------------------------------------------------
4638
4639
4640 END POS_SCO_TOLERANCE_PVT;