[Home] [Help]
PACKAGE BODY: APPS.PO_RCOTOLERANCE_PVT
Source
1 PACKAGE BODY po_rcotolerance_pvt AS
2 /* $Header: POXVRTWB.pls 120.8.12020000.3 2013/02/11 00:55:40 vegajula ship $*/
3 -- Read the profile option that enables/disables the debug log
4
5 g_debug CONSTANT VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RCOTOLERANCE_PVT';
8
9 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.'
10 || g_pkg_name
11 || '.';
12
13 TOL_INT_NEEDBY_IND CONSTANT NUMBER := 3;
14 /**
15 * Public FUNCTION get_new_line_quantity
16 * Requires:
17 * Requisition Header Id
18 * Requisition Line Id
19 * Change Request Group Id
20 * Modifies: None.
21 * Effects: Get revised line quantity from po_req_distributions and po_change_requests.
22 * Returns:
23 * Revised line quantity
24 * If there is an exception returns 0
25 */
26
27 FUNCTION get_new_line_quantity
28 (p_req_id IN NUMBER,
29 p_req_line_id IN NUMBER,
30 p_grp_id IN NUMBER)
31 RETURN NUMBER
32 IS
33 l_new_line_qty NUMBER := 0;
34 BEGIN
35 SELECT nvl(SUM(get_new_distribution_qty(prl.requisition_header_id, p_grp_id, prl.requisition_line_id,
36 prd.distribution_id)),
37 0)
38 INTO l_new_line_qty
39 FROM po_req_distributions prd,
40 po_requisition_lines prl
41 WHERE prl.requisition_header_id = p_req_id
42 AND prl.requisition_line_id = p_req_line_id
43 AND prl.requisition_line_id = prd.requisition_line_id
44 AND nvl(prl.cancel_flag, 'N') = 'N'
45 AND nvl(prl.modified_by_agent_flag, 'N') = 'N';
46
47 RETURN l_new_line_qty;
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN 0;
51 END get_new_line_quantity;
52 /**
53 * Public FUNCTION get_new_distribution_qty
54 * Requires: Requisition Header Id
55 * Change Request Group Id
56 * Requisition Line Id
57 * Modifies: None.
58 * Effects: Return updated distribution qty from po_change_requests if any
59 * Else Return the req_line_quantity from po_req_distributions
60 * If line was cancelled return 0
61 * Returns:
62 * Revised distribution quantity
63 * If something fails returns 0
64 */
65
66 FUNCTION get_new_distribution_qty
67 (p_req_id IN NUMBER,
68 p_grp_id IN NUMBER,
69 p_req_line_id IN NUMBER,
70 p_req_dist_id IN NUMBER)
71 RETURN NUMBER
72 IS
73 l_new_qty NUMBER := 0;
74 l_qty_changed NUMBER := 0;
75 BEGIN
76 SELECT COUNT(* )
77 INTO l_qty_changed
78 FROM po_change_requests
79 WHERE document_distribution_id = p_req_dist_id
80 AND document_line_id = p_req_line_id
81 AND document_header_id = p_req_id
82 AND request_status = 'SYSTEMSAVE'
83 AND new_quantity IS NOT NULL ;
84
85 IF (l_qty_changed > 0) THEN
86 SELECT new_quantity
87 INTO l_new_qty
88 FROM po_change_requests
89 WHERE document_distribution_id = p_req_dist_id
90 AND document_line_id = p_req_line_id
91 AND document_header_id = p_req_id
92 AND request_status = 'SYSTEMSAVE';
93 ELSE
94 SELECT req_line_quantity
95 INTO l_new_qty
96 FROM po_req_distributions
97 WHERE distribution_id = p_req_dist_id;
98 END IF;
99
100 RETURN l_new_qty;
101 EXCEPTION
102 WHEN OTHERS THEN
103 RETURN 0;
104 END get_new_distribution_qty;
105 /**
106 * Public FUNCTION change_within_tol_date
107 * Requires: oldValue
108 * newValue
109 * max increment tolerance
110 * max decrement tolerance
111 * Modifies: None.
112 * Effects: Checks whether given old and new values are within given
113 * max increment and max decrement values or not.
114 * Returns:
115 * 'Y' or 'N'
116 */
117
118 FUNCTION change_within_tol_date
119 (p_oldvalue IN DATE,
120 p_newvalue IN DATE,
121 p_maxincrement IN NUMBER,
122 p_maxdecrement IN NUMBER)
123 RETURN VARCHAR2
124 IS
125 l_returnvalue VARCHAR2(1) := 'Y';
126 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Date';
127 BEGIN
128 IF (g_debug = 'Y'
129 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
130 fnd_log.string(fnd_log.level_statement, g_module_prefix
131 || l_api_name, 'Parameters:'
132 ||' OldValue:'
133 || to_char(p_oldvalue, 'DD-MON-YYYY')
134 ||' NewValue:'
135 || to_char(p_newvalue, 'DD-MON-YYYY')
136 ||' MaxIncrement:'
137 || to_char(p_maxincrement)
138 ||' MaxDecrement:'
139 || to_char(p_maxdecrement));
140 END IF;
141
142 IF (p_oldvalue IS NOT NULL
143 AND p_newvalue IS NOT NULL
144 AND p_oldvalue <> p_newvalue) THEN
145 -- check for upper tol
146
147 IF (p_oldvalue < p_newvalue) THEN
148 IF (p_newvalue - p_maxincrement > p_oldvalue) THEN
149 l_returnvalue := 'N';
150 END IF;
151 END IF;
152 -- check for lower tol
153
154 IF (p_oldvalue > p_newvalue) THEN
155 IF (p_newvalue + p_maxdecrement < p_oldvalue) THEN
156 l_returnvalue := 'N';
157 END IF;
158 END IF;
159 END IF;
160
161 IF (g_debug = 'Y'
162 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
163 fnd_log.string(fnd_log.level_statement, g_module_prefix
164 || l_api_name,'Return Value:'
165 || l_returnvalue);
166 END IF;
167
168 RETURN l_returnvalue;
169 END change_within_tol_date;
170 /**
171 * Public FUNCTION change_within_tol_percent
172 * Requires: oldValue
173 * newValue
174 * max increment tolerance (%)
175 * max decrement tolerance (%)
176 * Modifies: None.
177 * Effects: Checks whether given old and new values are within given
178 * max increment and max decrement values or not.
179 * Returns:
180 * 'Y' or 'N'
181 */
182
183 FUNCTION change_within_tol_percent
184 (p_oldvalue IN NUMBER,
185 p_newvalue IN NUMBER,
186 p_maxincrement IN NUMBER,
187 p_maxdecrement IN NUMBER)
188 RETURN VARCHAR2
189 IS
190 l_changepercent NUMBER;
191 l_returnvalue VARCHAR2(1) := 'Y';
192 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Percent';
193 BEGIN
194 IF (g_debug = 'Y'
195 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
196 fnd_log.string(fnd_log.level_statement, g_module_prefix
197 || l_api_name, 'Parameters:'
198 ||' OldValue:'
199 || to_char(p_oldvalue)
200 ||' NewValue:'
201 || to_char(p_newvalue)
202 ||' MaxIncrement:'
203 || to_char(p_maxincrement)
204 ||' MaxDecrement:'
205 || to_char(p_maxdecrement));
206 END IF;
207
208 IF (p_oldvalue IS NOT NULL
209 AND p_newvalue IS NOT NULL
210 AND p_oldvalue <> p_newvalue
211 AND p_oldvalue <> 0) THEN
212 l_changepercent := abs((p_oldvalue - p_newvalue) / p_oldvalue) * 100;
213
214 IF (g_debug = 'Y'
215 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
216 fnd_log.string(fnd_log.level_statement, g_module_prefix
217 || l_api_name, 'ChangePercent:'
218 || to_char(l_changepercent));
219 END IF;
220 -- value has increased
221
222 IF (p_oldvalue < p_newvalue) THEN
223 IF (l_changepercent > p_maxincrement) THEN
224 l_returnvalue := 'N';
225 END IF;
226 END IF;
227 -- value has decreased
228
229 IF (p_oldvalue > p_newvalue) THEN
230 IF (l_changepercent > p_maxdecrement) THEN
231 l_returnvalue := 'N';
232 END IF;
233 END IF;
234 END IF;
235
236 IF (g_debug = 'Y'
237 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
238 fnd_log.string(fnd_log.level_statement, g_module_prefix
239 || l_api_name,'Return Value:'
240 || l_returnvalue);
241 END IF;
242
243 RETURN l_returnvalue;
244 END change_within_tol_percent;
245 /**
246 * Public FUNCTION change_within_tol_amount
247 * Requires: oldValue
248 * newValue
249 * max increment tolerance (functional currency)
250 * max decrement tolerance (functional currency)
251 * Modifies: None.
252 * Effects: Checks whether given old and new values are within given
253 * max increment and max decrement values in amount or not.
254 * Returns:
255 * 'Y' or 'N'
256 */
257
258 FUNCTION change_within_tol_amount
259 (p_oldvalue IN NUMBER,
260 p_newvalue IN NUMBER,
261 p_maxincrement IN NUMBER,
262 p_maxdecrement IN NUMBER)
263 RETURN VARCHAR2
264 IS
265 l_change NUMBER;
266 l_returnvalue VARCHAR2(1) := 'Y';
267 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Amount';
268 BEGIN
269 IF (g_debug = 'Y'
270 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
271 fnd_log.string(fnd_log.level_statement, g_module_prefix
272 || l_api_name, 'Parameters:'
273 ||' OldValue:'
274 || to_char(p_oldvalue)
275 ||' NewValue:'
276 || to_char(p_newvalue)
277 ||' MaxIncrement:'
278 || to_char(p_maxincrement)
279 ||' MaxDecrement:'
280 || to_char(p_maxdecrement));
281 END IF;
282
283 IF (p_oldvalue IS NOT NULL
284 AND p_newvalue IS NOT NULL
285 AND p_oldvalue <> p_newvalue) THEN
286 l_change := p_oldvalue - p_newvalue;
287 -- value has increased
288
289 IF (p_oldvalue < p_newvalue) THEN
290 IF (abs(l_change) > p_maxincrement) THEN
291 l_returnvalue := 'N';
292 END IF;
293 END IF;
294 -- value has decreased
295
296 IF (p_oldvalue > p_newvalue) THEN
297 IF (l_change > p_maxdecrement) THEN
298 l_returnvalue := 'N';
299 END IF;
300 END IF;
301 END IF;
302
303 IF (g_debug = 'Y'
304 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
305 fnd_log.string(fnd_log.level_statement, g_module_prefix
306 || l_api_name,'Return Value:'
307 || l_returnvalue);
308 END IF;
309
310 RETURN l_returnvalue;
311 END change_within_tol_amount;
312 /**
313 * Public FUNCTION changes_within_tol
314 * Requires: oldValue
315 * newValue
316 * max increment tolerance (%)
317 * max decrement tolerance (%)
318 * max increment tolerance (functional currency)
319 * max decrement tolerance (functional currency)
320 * Modifies: None.
321 * Effects: Checks whether given old and new values are within given
322 * max increment and max decrement values in percent and amount or not.
323 * Returns:
324 * 'Y' or 'N'
325 *
326 * The logic to call tolerance check API's (for % and USD) :
327 * - if both tolerances(% and USD) are zero, this should go to approval
328 * - if one has value and the other is zero, validate against the one
329 * with value, and ignore the zero one
330 * - if both values are set up, we should validate against both values,
331 * the change will not be auto-approved if it can't pass both values.
332 */
333
334 FUNCTION changes_within_tol
335 (p_oldvalue IN NUMBER,
336 p_newvalue IN NUMBER,
337 p_maxincrement IN NUMBER,
338 p_maxdecrement IN NUMBER,
339 p_maxincrementamount IN NUMBER,
340 p_maxdecrementamount IN NUMBER)
341 RETURN VARCHAR2
342 IS
343 l_returnvalue VARCHAR2(1) := 'Y';
344 l_api_name VARCHAR2(30) := 'Changes_Within_Tol';
345 l_call_percent_api BOOLEAN := TRUE;
346 l_call_amount_api BOOLEAN := TRUE;
347 BEGIN
348 IF (p_maxincrement = 0 AND p_maxdecrement = 0 AND
349 ( p_maxincrementamount <> 0 OR p_maxdecrementamount <> 0)) THEN
350 l_call_percent_api := FALSE;
351 END IF;
352
353 IF ((p_maxincrement <> 0 OR p_maxdecrement <> 0)
354 AND p_maxincrementamount = 0
355 AND p_maxdecrementamount = 0) THEN
356 l_call_amount_api := FALSE;
357 END IF;
358
359 IF (g_debug = 'Y'
360 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
361 IF (l_call_percent_api) THEN
362 fnd_log.string(fnd_log.level_statement, g_module_prefix
363 || l_api_name,'l_call_percent_api: Y');
364 ELSE
365 fnd_log.string(fnd_log.level_statement, g_module_prefix
366 || l_api_name,'l_call_percent_api: N');
367 END IF;
368
369 IF (l_call_amount_api) THEN
370 fnd_log.string(fnd_log.level_statement, g_module_prefix
371 || l_api_name,'l_call_amount_api: Y');
372 ELSE
373 fnd_log.string(fnd_log.level_statement, g_module_prefix
374 || l_api_name,'l_call_amount_api: N');
375 END IF;
376 END IF;
377
378 IF (l_call_percent_api) THEN
379 l_returnvalue := change_within_tol_percent(p_oldvalue, p_newvalue, p_maxincrement, p_maxdecrement);
380
381 IF (g_debug = 'Y'
382 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
383 fnd_log.string(fnd_log.level_statement, g_module_prefix
384 || l_api_name,'Change_Within_Tol_Percent Return Value:'
385 || l_returnvalue);
386 END IF;
387 END IF;
388
389 IF (l_returnvalue <> 'N'
390 AND l_call_amount_api) THEN
391 l_returnvalue := change_within_tol_amount(p_oldvalue, p_newvalue, p_maxincrementamount,
392 p_maxdecrementamount);
393 END IF;
394
395 IF (g_debug = 'Y'
396 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
397 fnd_log.string(fnd_log.level_statement, g_module_prefix
398 || l_api_name,'Return Value:'
399 || l_returnvalue);
400 END IF;
401
402 RETURN l_returnvalue;
403 END changes_within_tol;
404 /**
405 * Requires: organization id
406 * Modifies: None.
407 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
408 * tolerance values for RCO REQ Aproval tolerances.
409 * Returns:
410 * a table of records that contains tolerance values
411 */
412
413 FUNCTION populate_internal_tolerances
414 (p_organization_id IN NUMBER)
415 RETURN po_co_tolerances_grp.tolerances_tbl_type
416 IS
417 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
418 l_return_status VARCHAR2(1);
419 l_msg_count NUMBER;
420 l_msg_data VARCHAR2(100);
421 l_api_name VARCHAR2(30) := 'Populate_INTERNAL_Tolerances';
422 BEGIN
423 IF (g_debug = 'Y'
424 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
425 fnd_log.string(fnd_log.level_statement, g_module_prefix
426 || l_api_name,'p_organization_id :'
427 || p_organization_id);
428 END IF;
429
430
431 po_co_tolerances_grp.get_tolerances(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true,
432 p_organization_id => p_organization_id, p_change_order_type => po_co_tolerances_grp.g_rco_int_req_app,
433 x_tolerances_tbl => l_tolerances_tbl, x_return_status => l_return_status,
434 x_msg_count => l_msg_count, x_msg_data => l_msg_data);
435
436 IF (g_debug = 'Y'
437 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
438 fnd_log.string(fnd_log.level_statement, g_module_prefix
439 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API result:'
440 || l_return_status);
441 END IF;
442
443 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
444 IF (g_debug = 'Y'
445 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
446 fnd_log.string(fnd_log.level_statement, g_module_prefix
447 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API failed:'
448 || l_msg_count
449 ||' '
450 || l_msg_data);
451
452 END IF;
453 END IF;
454
455 RETURN l_tolerances_tbl;
456 END populate_internal_tolerances;
457 /**
458 * Requires: organization id
459 * Modifies: None.
460 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
461 * tolerance values for RCO REQ Aproval tolerances.
462 * Returns:
463 * a table of records that contains tolerance values
464 */
465
466 FUNCTION populate_tolerances
467 (p_organization_id IN NUMBER)
468 RETURN po_co_tolerances_grp.tolerances_tbl_type
469 IS
470 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
471 l_return_status VARCHAR2(1);
472 l_msg_count NUMBER;
473 l_msg_data VARCHAR2(100);
474 l_api_name VARCHAR2(30) := 'Populate_Tolerances';
475 BEGIN
476 IF (g_debug = 'Y'
477 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
478 fnd_log.string(fnd_log.level_statement, g_module_prefix
479 || l_api_name,'p_organization_id :'
480 || p_organization_id);
481 END IF;
482
483 po_co_tolerances_grp.get_tolerances(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true,
484 p_organization_id => p_organization_id, p_change_order_type => po_co_tolerances_grp.g_rco_req_app,
485 x_tolerances_tbl => l_tolerances_tbl, x_return_status => l_return_status,
486 x_msg_count => l_msg_count, x_msg_data => l_msg_data);
487
488 IF (g_debug = 'Y'
489 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
490 fnd_log.string(fnd_log.level_statement, g_module_prefix
491 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API result:'
492 || l_return_status);
493 END IF;
494
495 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
496 IF (g_debug = 'Y'
497 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
498 fnd_log.string(fnd_log.level_statement, g_module_prefix
499 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API failed:'
500 || l_msg_count
501 ||' '
502 || l_msg_data);
503 END IF;
504 END IF;
505
506 RETURN l_tolerances_tbl;
507 END populate_tolerances;
508 /**
509 * Requires: requisition change request group id
510 * organization id
511 * Modifies: None.
512 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
513 * tolerance values for RCO REQ Aproval tolerances.
514 * Returns:
515 * a table of records that contains tolerance values
516 */
517
518 FUNCTION changes_within_reqappr_tol_val
519 (p_reqgrp_id IN NUMBER,
520 p_reqheader_id IN NUMBER,
521 p_org_id NUMBER,
522 p_source_type_code IN VARCHAR2)
523 RETURN VARCHAR2
524 IS
525 l_return_val VARCHAR2(1) := 'Y';
526 -- PLSQL table of tolerance values
527 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
528 l_api_name VARCHAR2(30) := 'Changes_Within_ReqAppr_Tol_Val';
529 l_progress VARCHAR2(100) := '000';
530 BEGIN
531 l_progress := '001';
532
533 IF (g_debug = 'Y'
534 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
535 fnd_log.string(fnd_log.level_statement, g_module_prefix
536 || l_api_name, 'p_reqgrp_id:'
537 || p_reqgrp_id
538 ||' p_reqheader_id:'
539 || p_reqheader_id
540 ||' p_org_id:'
541 || p_org_id);
542 END IF;
543
544 IF (p_reqgrp_id IS NOT NULL ) THEN
545 -- get tolerance values
546
547 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
548 l_tolerances_tbl := populate_internal_tolerances(p_org_id);
549 ELSE
550 l_tolerances_tbl := populate_tolerances(p_org_id);
551 END IF;
552
553
554 l_progress := '002';
555
556 IF (g_debug = 'Y'
557 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
558 fnd_log.string(fnd_log.level_statement, g_module_prefix
559 || l_api_name,'Before needbydate, startdate, enddate checks');
560 END IF;
561 -- handle need by date, start date, end date, price checks
562
563 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
564 -- handle need by date only
565 IF (g_debug = 'Y'
566 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
567 fnd_log.string(fnd_log.level_statement, g_module_prefix
568 || l_api_name,'for internal req Before need by date checks');
569 END IF;
570 l_progress := '021';
571
572 BEGIN
573 SELECT 'N'
574 INTO l_return_val
575 FROM dual
576 WHERE EXISTS (SELECT 'N'
577 FROM po_change_requests
578 WHERE change_request_group_id = p_reqgrp_id
579 AND action_type = 'MODIFICATION'
580 AND request_status = 'SYSTEMSAVE'
581 AND request_level = 'LINE'
582 AND (change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_increment,
583 l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_decrement) = 'N')
584 );
585 EXCEPTION
586 WHEN no_data_found THEN
587 l_return_val := 'Y';
588 END;
589
590 IF (g_debug = 'Y'
591 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
592 fnd_log.string(fnd_log.level_statement, g_module_prefix
593 || l_api_name,'return value(needbydate):'
594 || l_return_val);
595 END IF;
596 ELSE
597 BEGIN
598 SELECT 'N'
599 INTO l_return_val
600 FROM dual
601 WHERE EXISTS (SELECT 'N'
602 FROM po_change_requests
603 WHERE change_request_group_id = p_reqgrp_id
604 AND action_type = 'MODIFICATION'
605 AND request_status = 'SYSTEMSAVE'
606 AND request_level = 'LINE'
607 AND ((change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(tol_needby_ind).max_increment,
608 l_tolerances_tbl(tol_needby_ind).max_decrement) = 'N')
609 OR (change_within_tol_date(old_start_date, new_start_date, l_tolerances_tbl(tol_startdate_ind).max_increment,
610 l_tolerances_tbl(tol_startdate_ind).max_decrement) = 'N')
611 OR (change_within_tol_date(old_expiration_date, new_expiration_date, l_tolerances_tbl(tol_enddate_ind).max_increment,
612 l_tolerances_tbl(tol_enddate_ind).max_decrement) = 'N')
613 OR (change_within_tol_percent(old_price, new_price, l_tolerances_tbl(tol_unitprice_ind).max_increment,
614 l_tolerances_tbl(tol_unitprice_ind).max_decrement) = 'N')));
615 EXCEPTION
616 WHEN no_data_found THEN
617 l_return_val := 'Y';
618 END;
619
620 IF (g_debug = 'Y'
621 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
622 fnd_log.string(fnd_log.level_statement, g_module_prefix
623 || l_api_name,'return value(needbydate):'
624 || l_return_val);
625 END IF;
626 END IF;
627 l_progress := '003';
628
629 IF (g_debug = 'Y'
630 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
631 fnd_log.string(fnd_log.level_statement, g_module_prefix
632 || l_api_name,'Before line quantity, line amount checks');
633 END IF;
634 IF (l_return_val <> 'N') THEN
635 -- check for line quantity, line amount tolerances
636 BEGIN
637
638 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
639 IF (g_debug = 'Y'
640 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
641 fnd_log.string(fnd_log.level_statement, g_module_prefix
642 || l_api_name,'for internal req Before line quantity, line amount checks');
643 END IF;
644
645 SELECT 'N'
646 INTO l_return_val
647 FROM dual
648 WHERE EXISTS (SELECT 'N' FROM (
649 SELECT PRL.UNIT_PRICE AS UNIT_PRICE, PRL.QUANTITY AS QUANTITY,PCR.NEW_QUANTITY AS NEW_QUANTITY
650 FROM PO_CHANGE_REQUESTS PCR,
651 PO_REQUISITION_LINES_ALL PRL
652 WHERE PCR.CHANGE_REQUEST_GROUP_ID = p_reqgrp_id
653 AND PCR.ACTION_TYPE = 'MODIFICATION'
654 AND PCR.REQUEST_STATUS NOT IN ('ACCEPTED',
655 'REJECTED')
656 AND PCR.REQUEST_LEVEL ='LINE'
657 AND PCR.INITIATOR = 'REQUESTER'
658 AND PCR.DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID
659 AND PCR.NEW_quantity IS NOT NULL) b
660
661 WHERE (((CHANGES_WITHIN_TOL( b.UNIT_PRICE * b.QUANTITY,
662 NVL(b.NEW_QUANTITY,b.QUANTITY) * b.UNIT_PRICE,
663 l_tolerances_tbl(tol_lineamt_ind).max_increment,
664 l_tolerances_tbl(tol_lineamt_ind).max_decrement,
665 l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
666 l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement))= 'N')
667 OR (CHANGE_WITHIN_TOL_PERCENT( b.QUANTITY, b.NEW_QUANTITY,
668 l_tolerances_tbl(tol_lineqty_ind).max_increment,
669 l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
670
671
672 ELSE
673
674 SELECT 'N'
675 INTO l_return_val
676 FROM dual
677 WHERE EXISTS (SELECT 'N'
678 FROM po_change_requests pcr,
679 po_change_requests pcr1,
680 po_requisition_lines_all prl,
681 po_req_distributions_all prd
682 WHERE prd.requisition_line_id = prl.requisition_line_id
683 AND pcr.change_request_group_id = p_reqgrp_id
684 AND pcr.action_type (+ ) = 'MODIFICATION'
685 AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
686 'REJECTED')
687 AND pcr.request_level (+ ) = 'DISTRIBUTION'
688 AND pcr.initiator (+ ) = 'REQUESTER'
689 AND pcr.document_distribution_id (+ ) = prd.distribution_id
690 -- AND pcr.document_line_id = pcr1.document_line_id
691 AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
692 AND pcr1.document_line_id (+ ) = prl.requisition_line_id
693 AND pcr1.action_type (+ ) = 'MODIFICATION'
694 AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
695 'REJECTED')
696 AND pcr1.request_level (+ ) = 'LINE'
697 AND pcr1.initiator (+ ) = 'REQUESTER'
698 AND pcr1.new_price (+ ) IS NOT NULL
699
700 GROUP BY pcr.document_line_id
701 HAVING ((changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
702 prl.unit_price * prd.req_line_quantity)),
703 SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
704 nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
705 l_tolerances_tbl(tol_lineamt_ind).max_increment,
706 l_tolerances_tbl(tol_lineamt_ind).max_decrement,
707 l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
708 l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement) = 'N')
709 OR (change_within_tol_percent(SUM(prd.req_line_quantity), SUM(nvl(pcr.new_quantity, prd.req_line_quantity)),
710 l_tolerances_tbl(tol_lineqty_ind).max_increment,
711 l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
712 END IF;
713 EXCEPTION
714 WHEN no_data_found THEN
715 l_return_val := 'Y';
716 END;
717 END IF;
718
719 IF (g_debug = 'Y'
720 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
721 fnd_log.string(fnd_log.level_statement, g_module_prefix
722 || l_api_name,'return value(line amount):'
723 || l_return_val);
724 END IF;
725
726 l_progress := '004';
727
728 IF (g_debug = 'Y'
729 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
730 fnd_log.string(fnd_log.level_statement, g_module_prefix
731 || l_api_name,'Before document total checks');
732 END IF;
733
734 IF (l_return_val <> 'N') THEN
735 BEGIN
736 -- check for document total tolerances (funccur and percent)
737 SELECT changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
738 prl.unit_price * (prd.req_line_quantity))),
739 SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
740 nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
741 l_tolerances_tbl(tol_reqtotal_ind).max_increment,
742 l_tolerances_tbl(tol_reqtotal_ind).max_decrement,
743 l_tolerances_tbl(tol_reqtotal_amt_ind).max_increment,
744 l_tolerances_tbl(tol_reqtotal_amt_ind).max_decrement)
745 INTO l_return_val
746 FROM po_change_requests pcr,
747 po_change_requests pcr1,
748 po_requisition_lines_all prl,
749 po_req_distributions_all prd
750 WHERE prl.requisition_line_id = prd.requisition_line_id
751 AND pcr.change_request_group_id (+ ) = p_reqgrp_id
752 AND pcr.action_type (+ ) = 'MODIFICATION'
753 AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
754 'REJECTED')
755 AND pcr.request_level (+ ) = 'DISTRIBUTION'
756 AND pcr.initiator (+ ) = 'REQUESTER'
757 AND pcr.document_distribution_id (+ ) = prd.distribution_id
758 -- AND pcr.document_line_id = pcr1.document_line_id
759 AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
760 AND prl.requisition_header_id = p_reqheader_id
761 AND pcr1.document_line_id (+ ) = prl.requisition_line_id
762 AND pcr1.action_type (+ ) = 'MODIFICATION'
763 AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
764 'REJECTED')
765 AND pcr1.request_level (+ ) = 'LINE'
766 AND pcr1.initiator (+ ) = 'REQUESTER'
767 AND pcr1.new_price (+ ) IS NOT NULL ;
768 EXCEPTION
769 WHEN no_data_found THEN
770 l_return_val := 'Y';
771 END;
772 END IF;
773 END IF;
774
775 IF (g_debug = 'Y'
776 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
777 fnd_log.string(fnd_log.level_statement, g_module_prefix
778 || l_api_name,'Return Value:'
779 || l_return_val);
780 END IF;
781
782 RETURN l_return_val;
783 EXCEPTION
784 WHEN OTHERS THEN
785 IF (g_debug = 'Y'
786 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
787 fnd_log.string(fnd_log.level_statement, g_module_prefix
788 || l_api_name, 'Exception:'
789 || SQLERRM
790 ||' PROGRESS:'
791 || l_progress);
792 END IF;
793
794 RETURN 'N';
795 END changes_within_reqappr_tol_val;
796 /**
797 * Public PROCEDURE set_approval_required_flag
798 * Requires: Change Request Group Id
799 * Modifies: Updates po_change_request with the result of the
800 * changes_within_reqapproval_tol_values() API
801 * Returns:
802 * approval_required_flag:Y if user cannot auto approve
803 * :N if he/she can auto approve
804 */
805
806 PROCEDURE set_approval_required_flag
807 (p_chreqgrp_id IN NUMBER,
808 x_appr_status OUT NOCOPY VARCHAR2,
809 p_source_type_code IN VARCHAR2 DEFAULT NULL)
810 IS
811 l_skip_std_logic VARCHAR2(1) := 'N';
812 l_org_id NUMBER;
813 l_reqheader_id NUMBER;
814 l_api_name VARCHAR2(30) := 'Set_Approval_Required_Flag';
815 BEGIN
816 x_appr_status := 'Y';
817
818 IF (g_debug = 'Y'
819 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
820 fnd_log.string(fnd_log.level_statement, g_module_prefix
821 || l_api_name, 'p_chreqgrp_id:'
822 || p_chreqgrp_id);
823 END IF;
824
825 -- call custom procedure first
826
827 por_custom_pkg.custom_rco_reqappr_tol_check(p_chreqgrp_id, x_appr_status, l_skip_std_logic);
828
829 IF (g_debug = 'Y'
830 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
831 fnd_log.string(fnd_log.level_statement, g_module_prefix
832 || l_api_name,'After Calling POR_CUSTOM_PKG.CUSTOM_RCO_REQAPPR_TOL_CHECK '
833 ||' x_appr_status:'
834 || x_appr_status
835 ||' l_skip_std_logic:'
836 || l_skip_std_logic);
837 END IF;
838
839 IF (l_skip_std_logic = 'N'
840 AND x_appr_status <> 'N') THEN
841
842 SELECT org_id,
843 requisition_header_id
844 INTO l_org_id,
845 l_reqheader_id
846 FROM po_requisition_headers prh,
847 po_change_requests pcr
848 WHERE pcr.change_request_group_id = p_chreqgrp_id
849 AND pcr.document_header_id = prh.requisition_header_id
850 AND ROWNUM = 1;
851
852 IF (changes_within_reqappr_tol_val(p_chreqgrp_id, l_reqheader_id, l_org_id, p_source_type_code) = 'Y') THEN
853
854 x_appr_status := 'N';
855 END IF;
856 END IF;
857
858 IF (g_debug = 'Y'
859 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
860 fnd_log.string(fnd_log.level_statement, g_module_prefix
861 || l_api_name, 'x_appr_status:'
862 || x_appr_status);
863 END IF;
864
865
866 UPDATE po_change_requests
867 SET approval_required_flag = x_appr_status
868 WHERE change_request_group_id = p_chreqgrp_id;
869 END set_approval_required_flag;
870 END po_rcotolerance_pvt;