[Home] [Help]
PACKAGE BODY: APPS.PO_CHORD_WF6
Source
1 PACKAGE BODY PO_CHORD_WF6 AS
2 /* $Header: POXWCO6B.pls 120.22 2008/02/21 22:06:17 jburugul ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 /**************************************************************************
8 * The following procedure is used to retrieve
9 * the tolerances from workflow attributes
10 **************************************************************************/
11
12 PROCEDURE get_tolerances_from_wf(
13 itemtype IN VARCHAR2,
14 itemkey IN VARCHAR2,
15 doc_type IN VARCHAR2,
16 x_tolerance_control IN OUT NOCOPY t_tolerance_control_type);
17
18
19 /**************************************************************************
20 * The following procedures determine what reapproval rules will be applied
21 * by the workflow system. Please be careful when modifying this
22 * package.
23 * These rules determine whether the document is routed through the full
24 * approval process.
25 *
26 * There are 6 procedures in this package, each for a different
27 * document type.
28 *
29 * 1. standard_po_reapproval : Standard Purchase Order
30 * 2. planned_po_reapproval : Planned Purchase Order
31 * 3. blanket_po_reapproval : Blanket Purchase Agreement
32 * 4. contract_po_reapproval : Contract Purchase Agreement
33 * 5. blanket_release_reapproval : Blanket Release
34 * 6. scheduled_release_reapproval : Scheduled Release
35 *
36 * How to customerize this package:
37 * (1) Backup this file
38 * (2) Do not change procedure definition.
39 * (3) Only modify the IF statement logic.
40 *
41 * The workflow system compares the current and previous version of
42 * the document and reports all modifications.
43 * For example, Standard Purchase Order has 4 sections, namely
44 * header, lines, shipments, and distributions.
45 * The modifications are thus stored in the following data structure:
46 * x_header_control, x_lines_control, x_shipments_control and
47 * x_dist_control.
48 * ( See POXWCO1S.pls, POXWCO2S.pls, POXWCO3S.pls POXWCO4S.pls
49 * for definition )
50 *
51 * The data structure x_tolerance_control holds the default tolerance
52 * percentage in the workflow definition. They are stored as
53 * item attributes in the workflow.
54 *
55 * Two types of value are stored in the data structure:
56 * (1) 'Y' or 'N' type (name same as table column name)
57 * (2) Numbers in Percentage (name with '_change')
58 *
59 **************************************************************************
60 */
61
62 /**************************************************************************
63 * *
64 * Reapproval Rules for Standard Purchase Order *
65 * *
66 **************************************************************************/
67
68 PROCEDURE standard_po_reapproval(itemtype IN VARCHAR2,
69 itemkey IN VARCHAR2,
70 actid IN NUMBER,
71 FUNCMODE IN VARCHAR2,
72 RESULT OUT NOCOPY VARCHAR2)
73 IS
74 x_header_control t_header_control_type;
75 x_lines_control t_lines_control_type;
76 x_shipments_control t_shipments_control_type;
77 x_dist_control t_dist_control_type;
78 x_tolerance_control t_tolerance_control_type;
79 x_result VARCHAR2(1);
80 l_retroactive_change VARCHAR2(1) := 'N'; -- RETROACTIVE FPI
81 l_autoapprove_retro VARCHAR2(1) := 'N'; -- RETROACTIVE FPI
82 l_actionoriginated_from varchar2(30); --Bug5697556
83 -- <Complex Work R12 Start>
84 l_is_complex_work_po BOOLEAN := FALSE;
85 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
86 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
87 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
88 -- <Complex Work R12 End>
89
90 BEGIN
91
92 IF (g_po_wf_debug = 'Y') THEN
93 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
94 '*** In Procedure: standard_po_reapproval ***');
95 END IF;
96
97 IF funcmode <> 'RUN' THEN
98 result := 'COMPLETE';
99 return;
100 END IF;
101
102 -- <Complex Work R12 Start>
103
104 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText(
105 itemtype => itemtype
106 , itemkey => itemkey
107 , aname => 'DOCUMENT_TYPE'
108 );
109
110 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(
111 itemtype => itemtype
112 , itemkey => itemkey
113 , aname => 'DOCUMENT_SUBTYPE'
114 );
115
116 IF ((l_document_type = 'PO') AND (l_document_subtype = 'STANDARD')) THEN
117
118 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrText(
119 itemtype => itemtype
120 , itemkey => itemkey
121 , aname => 'DOCUMENT_ID'
122 );
123
124 l_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_po(p_po_header_id => l_po_header_id);
125
126 END IF;
127
128 IF (g_po_wf_debug = 'Y') THEN
129 if l_is_complex_work_po then
130 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
131 'l_is_complex_work_po - Yes');
132 else
133 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
134 'l_is_complex_work_po - No');
135 end if;
136 END IF;
137
138 -- <Complex Work R12 End>
139
140
141 /* RETROACTIVE FPI START.
142 * Get the value of the attribute CO_H_RETROACTIVE_APPROVAL if
143 * this approval is initiated due to the retroactive change in
144 * the release. If this value is N, then we send the document
145 * through Change Order Workflow.
146 */
147 l_retroactive_change := PO_WF_UTIL_PKG.GetItemAttrText
148 (itemtype => itemtype,
149 itemkey => itemkey,
150 aname => 'CO_R_RETRO_CHANGE');
151
152 If (l_retroactive_change = 'Y') then
153
154 l_autoapprove_retro := PO_WF_UTIL_PKG.GetItemAttrText
155 (itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'CO_H_RETROACTIVE_AUTOAPPROVAL');
158
159
160 /*Bug5697556 Get the wf attribute value 'INTERFACE_SOURCE_CODE'.This would be
161 the value which indicates where the approval workflow is called from.If
162 approval is called from "Retroactive" concurrent program only then check
163 for the "automatic approval" wf attribute */
164
165 l_actionoriginated_from := PO_WF_UTIL_PKG.GetItemAttrText
166 (itemtype => itemtype,
167 itemkey => itemkey,
168 aname => 'INTERFACE_SOURCE_CODE');
169
170 if (l_autoapprove_retro = 'Y') AND (l_actionoriginated_from = 'RETRO') THEN
171
172
173 IF (g_po_wf_debug = 'Y') THEN
174 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
175 '$$$$$$$ Document requires full approval = N $$$$$$');
176 END IF;
177
178 RESULT := 'N';
179
180 return;
181 end if; /*l_autoapprove_retro = 'Y' */
182
183 end if; /* l_retroactive_change = 'Y' */
184
185 /* RETROACTIVE FPI END */
186 po_chord_wf1.get_wf_header_control(itemtype, itemkey, x_header_control);
187 po_chord_wf2.get_wf_lines_control(itemtype, itemkey, x_lines_control);
188 po_chord_wf3.get_wf_shipments_control(itemtype, itemkey, x_shipments_control);
189 po_chord_wf4.get_wf_dist_control(itemtype, itemkey, x_dist_control);
190 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'STANDARD_PO'); --<R12 Requester Driven Procurement>
191
192 /***************************************************************
193 * Check if the modifications to the document header requires
194 * full reapproval.
195 *
196 * Legends:
197 *
198 * 'Y' means modified
199 * 'N' means not modified
200 * Numbers are in Percentage
201 *
202 ***************************************************************/
203
204 -- bug 4624025: vendor site id should be considered for the reapproval,
205 -- rules, and if the same has changed the document should be routed
206 -- through the approval hierarchy
207
208 IF x_header_control.agent_id ='Y'
209 OR x_header_control.vendor_site_id ='Y'
210 OR x_header_control.vendor_contact_id ='Y'
211 OR x_header_control.confirming_order_flag ='Y'
212 OR x_header_control.ship_to_location_id ='Y'
213 OR x_header_control.bill_to_location_id ='Y'
214 OR x_header_control.terms_id ='Y'
215 OR x_header_control.ship_via_lookup_code ='Y'
216 OR x_header_control.fob_lookup_code ='Y'
217 OR x_header_control.freight_terms_lookup_code ='Y'
218 OR x_header_control.note_to_vendor ='Y'
219 OR x_header_control.acceptance_required_flag ='Y'
220 OR x_header_control.acceptance_due_date ='Y'
221 OR (x_header_control.po_total_change >
222 nvl(x_tolerance_control.h_po_total_t,0))
223 THEN
224 IF (g_po_wf_debug = 'Y') THEN
225 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
226 '########## header_require_reapproval result: Y');
227 END IF;
228 x_result:='Y';
229 ELSE
230 IF (g_po_wf_debug = 'Y') THEN
231 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
232 '########## header_require_reapproval result: N');
233 END IF;
234 x_result:='N';
235 END IF;
236
237 IF x_result <> 'Y' THEN
238 IF x_lines_control.line_num ='Y'
239 OR x_lines_control.item_id ='Y'
240 OR x_lines_control.item_revision ='Y'
241 OR x_lines_control.item_description ='Y'
242 OR x_lines_control.category_id ='Y'
243 OR x_lines_control.unit_meas_lookup_code ='Y'
244 OR x_lines_control.from_header_id ='Y'
245 OR x_lines_control.from_line_id ='Y'
246 OR x_lines_control.hazard_class_id ='Y'
247 OR x_lines_control.contract_num ='Y'
248 OR x_lines_control.vendor_product_num ='Y'
249 OR x_lines_control.un_number_id ='Y'
250 OR x_lines_control.price_type_lookup_code ='Y'
251 OR x_lines_control.note_to_vendor ='Y'
252 OR (x_lines_control.start_date_change >
253 nvl(x_tolerance_control.l_start_date_t,0)) --<R12 Requester Driven Procurement>
254 OR (x_lines_control.end_date ='Y'
255 AND (x_lines_control.end_date_change is null
256 OR (x_lines_control.end_date_change >
257 nvl(x_tolerance_control.l_end_date_t,0))) ) --<R12 Requester Driven Procurement>
258 -- <Complex Work R12 Start>
259 OR x_lines_control.retainage_rate = 'Y'
260 OR x_lines_control.max_retainage_amount = 'Y'
261 OR x_lines_control.progress_payment_rate = 'Y'
262 OR x_lines_control.recoupment_rate = 'Y'
263 OR x_lines_control.advance_amount = 'Y'
264 -- <Complex Work R12 End>
265 OR (x_lines_control.quantity_change >
266 nvl(x_tolerance_control.l_quantity_t,0))
267 OR (x_lines_control.unit_price_change >
268 nvl(x_tolerance_control.l_unit_price_t,0))
269 OR (x_lines_control.amount_change >
270 nvl(x_tolerance_control.l_amount_t,0)) --<R12 Requester Driven Procurement>
271 THEN
272 IF (g_po_wf_debug = 'Y') THEN
273 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
274 '########## lines_require_reapproval result: Y');
275 END IF;
276 x_result:='Y';
277 ELSE
278 IF (g_po_wf_debug = 'Y') THEN
279 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
280 '########## lines_require_reapproval result: N');
281 END IF;
282 x_result:='N';
283 END IF;
284 END IF;
285
286 IF x_result <> 'Y' THEN
287 IF x_shipments_control.shipment_num ='Y'
288 OR x_shipments_control.ship_to_location_id ='Y'
289 OR (x_shipments_control.last_accept_date ='Y'
290 AND x_shipments_control.days_late_rcpt_allowed ='Y') -- ECO 5080252
291 OR (x_shipments_control.promised_date ='Y'
292 AND (x_shipments_control.promised_date_change is null -- Bug 5123672
293 OR (x_shipments_control.promised_date_change >
294 nvl(x_tolerance_control.s_promised_date_t,0))) ) --<R12 Requester Driven Procurement>
295 OR (x_shipments_control.need_by_date ='Y'
296 AND (x_shipments_control.need_by_date_change is null -- Bug 5123672
297 OR (x_shipments_control.need_by_date_change >
298 nvl(x_tolerance_control.s_need_by_date_t,0))) ) --<R12 Requester Driven Procurement>
299
300 -- <Complex Work R12 Start>: Use different tolerances for complex work POs
301 OR x_shipments_control.payment_type = 'Y'
302 OR x_shipments_control.description = 'Y'
303 OR x_shipments_control.work_approver_id = 'Y'
304 OR ((NOT l_is_complex_work_po) AND
305 (x_shipments_control.quantity_change > nvl(x_tolerance_control.s_quantity_t,0)))
306 OR ((l_is_complex_work_po) AND
307 (x_shipments_control.quantity_change > nvl(x_tolerance_control.p_quantity_t,0)))
308 --<R12 Requester Driven Procurement>
309 OR ((NOT l_is_complex_work_po) AND
310 (x_shipments_control.amount_change > nvl(x_tolerance_control.s_amount_t,0)))
311 OR ((l_is_complex_work_po) AND
312 (x_shipments_control.amount_change > nvl(x_tolerance_control.p_amount_t,0)))
313 OR ((l_is_complex_work_po) AND
314 (x_shipments_control.price_override_change > nvl(x_tolerance_control.p_price_override_t,0)))
315 -- <Complex Work R12 End>
316 THEN
317 IF (g_po_wf_debug = 'Y') THEN
318 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
319 '########## shipments_require_reapproval result: Y');
320 END IF;
321 x_result:='Y';
322 ELSE
323 IF (g_po_wf_debug = 'Y') THEN
324 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
325 '########## shipments_require_reapproval result: N');
326 END IF;
327 x_result:='N';
328 END IF;
329 END IF;
330
331 /* Bug 1081717: kagarwal
332 ** Added the Check for change in Charge Account for Distributions
333 ** x_dist_control.code_combination_id = 'Y'
334 */
335 /* Bug 2747157: kagarwal
336 ** Added the Check for change in Gl Date for Distributions
337 ** x_dist_control.gl_encumbered_date = 'Y' .
338 */
339
340 IF x_result <> 'Y' THEN
341 IF x_dist_control.distribution_num ='Y'
342 OR x_dist_control.deliver_to_person_id ='Y'
343 OR x_dist_control.code_combination_id = 'Y'
344 OR x_dist_control.gl_encumbered_date = 'Y'
345 OR (x_dist_control.quantity_ordered_change >
346 nvl(x_tolerance_control.d_quantity_ordered_t,0))
347 OR (x_dist_control.amount_ordered_change >
348 nvl(x_tolerance_control.d_amount_ordered_t,0)) --<R12 Requester Driven Procurement>
349 THEN
350 IF (g_po_wf_debug = 'Y') THEN
351 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
352 '########## dist_require_reapproval result: Y');
353 END IF;
354 x_result:='Y';
355 ELSE
356 IF (g_po_wf_debug = 'Y') THEN
357 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
358 '########## dist_require_reapproval result: N');
359 END IF;
360 x_result:='N';
361 END IF;
362 END IF;
363
364 --<CONTERMS FPJ START>
365 IF x_result <> 'Y' THEN
366 -- Check if contract terms were changed
367 x_result := PO_CONTERMS_WF_PVT.contract_terms_changed(
368 itemtype => itemtype,
369 itemkey => itemkey);
370 END IF;
371 --<CONTERMS FPJ END>
372 IF x_result = 'Y' THEN
373 IF (g_po_wf_debug = 'Y') THEN
374 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
375 '$$$$$$$ Document requires full approval = Y $$$$$$');
376 END IF;
377 RESULT := 'Y';
378 ELSE
379 IF (g_po_wf_debug = 'Y') THEN
380 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
381 '$$$$$$$ Document requires full approval = N $$$$$$');
382 END IF;
383 RESULT := 'N';
384 END IF;
385
386 IF (g_po_wf_debug = 'Y') THEN
387 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
388 '*** Finish: standard_po_reapproval ***');
389 END IF;
390
391 return;
392
393 EXCEPTION
394
395 WHEN OTHERS THEN
396 wf_core.context('POAPPRV', 'po_chord_wf6.stanard_po_reapproval', 'others');
397 RAISE;
398
399 END;
400
401 /**************************************************************************
402 * *
403 * Reapproval Rules for Planned Purchase Order *
404 * *
405 **************************************************************************/
406
407 PROCEDURE planned_po_reapproval(itemtype IN VARCHAR2,
408 itemkey IN VARCHAR2,
409 actid IN NUMBER,
410 FUNCMODE IN VARCHAR2,
411 RESULT OUT NOCOPY VARCHAR2)
412 IS
413 x_header_control t_header_control_type;
414 x_lines_control t_lines_control_type;
415 x_shipments_control t_shipments_control_type;
416 x_dist_control t_dist_control_type;
417 x_tolerance_control t_tolerance_control_type;
418 x_result VARCHAR2(1);
419 BEGIN
420
421 IF (g_po_wf_debug = 'Y') THEN
422 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
423 '*** In Procedure: planned_po_reapproval ***');
424 END IF;
425
426 IF funcmode <> 'RUN' THEN
427 result := 'COMPLETE';
428 return;
429 END IF;
430
431 po_chord_wf1.get_wf_header_control(itemtype, itemkey, x_header_control);
432 po_chord_wf2.get_wf_lines_control(itemtype, itemkey, x_lines_control);
433 po_chord_wf3.get_wf_shipments_control(itemtype, itemkey, x_shipments_control);
434 po_chord_wf4.get_wf_dist_control(itemtype, itemkey, x_dist_control);
435 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'PLANNED_PO'); --<R12 Requester Driven Procurement>
436
437 -- 'Y' means modified
438 -- Numbers are in Percentage
439
440 -- bug 4624025: vendor site id should be considered for the reapproval,
441 -- rules, and if the same has changed the document should be routed
442 -- through the approval hierarchy
443 IF x_header_control.agent_id ='Y'
444 OR x_header_control.vendor_site_id ='Y'
445 OR x_header_control.vendor_contact_id ='Y'
446 OR x_header_control.confirming_order_flag ='Y'
447 OR x_header_control.ship_to_location_id ='Y'
448 OR x_header_control.bill_to_location_id ='Y'
449 OR x_header_control.terms_id ='Y'
450 OR x_header_control.ship_via_lookup_code ='Y'
451 OR x_header_control.fob_lookup_code ='Y'
452 OR x_header_control.freight_terms_lookup_code ='Y'
453 OR x_header_control.note_to_vendor ='Y'
454 OR x_header_control.acceptance_required_flag ='Y'
455 OR x_header_control.acceptance_due_date ='Y'
456 OR x_header_control.start_date ='Y'
457 OR x_header_control.end_date ='Y'
458 OR (x_header_control.amount_limit_change >
459 nvl(x_tolerance_control.h_amount_limit_t,0))
460 THEN
461 IF (g_po_wf_debug = 'Y') THEN
462 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
463 '########## header_require_reapproval result: Y');
464 END IF;
465 x_result:='Y';
466 ELSE
467 IF (g_po_wf_debug = 'Y') THEN
468 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
469 '########## header_require_reapproval result: N');
470 END IF;
471 x_result:='N';
472 END IF;
473
474 IF x_result <> 'Y' THEN
475 IF x_lines_control.line_num ='Y'
476 OR x_lines_control.item_id ='Y'
477 OR x_lines_control.item_revision ='Y'
478 OR x_lines_control.item_description ='Y'
479 OR x_lines_control.category_id ='Y'
480 OR x_lines_control.unit_meas_lookup_code ='Y'
481 OR x_lines_control.from_header_id ='Y'
482 OR x_lines_control.from_line_id ='Y'
483 OR x_lines_control.hazard_class_id ='Y'
484 OR x_lines_control.contract_num ='Y'
485 OR x_lines_control.vendor_product_num ='Y'
486 OR x_lines_control.un_number_id ='Y'
487 OR x_lines_control.price_type_lookup_code ='Y'
488 OR x_lines_control.note_to_vendor ='Y'
489 OR (x_lines_control.quantity_change >
490 nvl(x_tolerance_control.l_quantity_t,0))
491 OR (x_lines_control.unit_price_change >
492 nvl(x_tolerance_control.l_unit_price_t,0))
493 THEN
494 IF (g_po_wf_debug = 'Y') THEN
495 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
496 '########## lines_require_reapproval result: Y');
497 END IF;
498 x_result:='Y';
499 ELSE
500 IF (g_po_wf_debug = 'Y') THEN
501 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
502 '########## lines_require_reapproval result: N');
503 END IF;
504 x_result:='N';
505 END IF;
506 END IF;
507
508 IF x_result <> 'Y' THEN
509 IF x_shipments_control.shipment_num ='Y'
510 OR x_shipments_control.ship_to_location_id ='Y'
511 OR (x_shipments_control.last_accept_date ='Y'
512 AND x_shipments_control.days_late_rcpt_allowed ='Y') -- ECO 5080252
513 OR (x_shipments_control.promised_date ='Y'
514 AND (x_shipments_control.promised_date_change is null
515 OR (x_shipments_control.promised_date_change >
516 nvl(x_tolerance_control.s_promised_date_t,0))) ) --<R12 Requester Driven Procurement>
517 OR (x_shipments_control.need_by_date ='Y'
518 AND (x_shipments_control.need_by_date_change is null
519 OR (x_shipments_control.need_by_date_change >
520 nvl(x_tolerance_control.s_need_by_date_t,0))) ) --<R12 Requester Driven Procurement>
521 OR (x_shipments_control.quantity_change >
522 nvl(x_tolerance_control.s_quantity_t,0))
523 THEN
524 IF (g_po_wf_debug = 'Y') THEN
525 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
526 '########## shipments_require_reapproval result: Y');
527 END IF;
528 x_result:='Y';
529 ELSE
530 IF (g_po_wf_debug = 'Y') THEN
531 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
532 '########## shipments_require_reapproval result: N');
533 END IF;
534 x_result:='N';
535 END IF;
536 END IF;
537
538 /* Bug 1081717: kagarwal
539 ** Added the Check for change in Charge Account for Distributions
540 ** x_dist_control.code_combination_id = 'Y'
541 */
542 /* Bug 2747157: kagarwal
543 ** Added the Check for change in Gl Date for Distributions
544 ** x_dist_control.gl_encumbered_date = 'Y' .
545 */
546
547 IF x_result <> 'Y' THEN
548 IF x_dist_control.distribution_num ='Y'
549 OR x_dist_control.deliver_to_person_id ='Y'
550 OR x_dist_control.code_combination_id = 'Y'
551 OR x_dist_control.gl_encumbered_date = 'Y'
552 OR (x_dist_control.quantity_ordered_change >
553 nvl(x_tolerance_control.d_quantity_ordered_t,0))
554 THEN
555 IF (g_po_wf_debug = 'Y') THEN
556 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
557 '########## dist_require_reapproval result: Y');
558 END IF;
559 x_result:='Y';
560 ELSE
561 IF (g_po_wf_debug = 'Y') THEN
562 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
563 '########## dist_require_reapproval result: N');
564 END IF;
565 x_result:='N';
566 END IF;
567 END IF;
568
569 IF x_result = 'Y' THEN
570 IF (g_po_wf_debug = 'Y') THEN
571 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
572 '$$$$$$$ Document requires full approval = Y $$$$$$');
573 END IF;
574 RESULT := 'Y';
575 ELSE
576 IF (g_po_wf_debug = 'Y') THEN
577 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
578 '$$$$$$$ Document requires full approval = N $$$$$$');
579 END IF;
580 RESULT := 'N';
581 END IF;
582
583
584 IF (g_po_wf_debug = 'Y') THEN
585 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
586 '*** Finish: planned_po_reapproval ***');
587 END IF;
588 return;
589
590 EXCEPTION
591
592 WHEN OTHERS THEN
593 wf_core.context('POAPPRV', 'po_chord_wf6.planned_po_reapproval', 'others');
594 RAISE;
595
596 END;
597
598 /**************************************************************************
599 * *
600 * Reapproval Rules for Blanket Purchase Agreement *
601 * *
602 **************************************************************************/
603
604 PROCEDURE blanket_po_reapproval(itemtype IN VARCHAR2,
605 itemkey IN VARCHAR2,
606 actid IN NUMBER,
607 FUNCMODE IN VARCHAR2,
608 RESULT OUT NOCOPY VARCHAR2)
609 IS
610 x_header_control t_header_control_type;
611 x_lines_control t_lines_control_type;
612 x_shipments_control t_shipments_control_type; /* <TIMEPHASED FPI> */
613 x_tolerance_control t_tolerance_control_type;
614 x_result VARCHAR2(1);
615 l_ga_org_assign_change VARCHAR2(1); -- Bug 2911017
616
617 BEGIN
618
619 IF (g_po_wf_debug = 'Y') THEN
620 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
621 '*** In Procedure: blanket_po_reapproval ***');
622 END IF;
623
624 IF funcmode <> 'RUN' THEN
625 result := 'COMPLETE';
626 return;
627 END IF;
628
629 po_chord_wf1.get_wf_header_control(itemtype, itemkey, x_header_control);
630
631 po_chord_wf2.get_wf_lines_control(itemtype, itemkey, x_lines_control);
632
633 /* Get all relevant shipments attribute values */
634 po_chord_wf3.get_wf_shipments_control(itemtype, itemkey, x_shipments_control); /* <TIMEPHASED FPI> */
635
636 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'BLANKET_PO'); --<R12 Requester Driven Procurement>
637
638 -- 'Y' means modified
639 -- Numbers are in Percentage
640
641 -- bug 4624025: vendor site id should be considered for the reapproval,
642 -- rules, and if the same has changed the document should be routed
643 -- through the approval hierarchy
644
645 IF x_header_control.agent_id ='Y'
646 OR x_header_control.vendor_site_id ='Y'
647 OR x_header_control.vendor_contact_id ='Y'
648 OR x_header_control.confirming_order_flag ='Y'
649 OR x_header_control.ship_to_location_id ='Y'
650 OR x_header_control.bill_to_location_id ='Y'
651 OR x_header_control.terms_id ='Y'
652 OR x_header_control.ship_via_lookup_code ='Y'
653 OR x_header_control.fob_lookup_code ='Y'
654 OR x_header_control.freight_terms_lookup_code ='Y'
655 OR x_header_control.note_to_vendor ='Y'
656 OR x_header_control.acceptance_required_flag ='Y'
657 OR x_header_control.acceptance_due_date ='Y'
658 OR x_header_control.start_date ='Y'
659 OR x_header_control.end_date ='Y'
660 OR x_header_control.amount_limit ='Y' --6616522
661 OR (x_header_control.amount_limit_change >
662 nvl(x_tolerance_control.h_amount_limit_t,0))
663 OR (x_header_control.blanket_total_change >
664 nvl(x_tolerance_control.h_blanket_total_t,0)) -- Bug 5166228
665 THEN
666 IF (g_po_wf_debug = 'Y') THEN
667 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
668 '########## header_require_reapproval result: Y');
669 END IF;
670 x_result:='Y';
671 ELSE
672 IF (g_po_wf_debug = 'Y') THEN
673 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
674 '########## header_require_reapproval result: N');
675 END IF;
676 x_result:='N';
677 END IF;
678
679 IF x_result <> 'Y' THEN
680 IF x_lines_control.line_num ='Y'
681 OR x_lines_control.item_id ='Y'
682 OR x_lines_control.item_revision ='Y'
683 OR x_lines_control.item_description ='Y'
684 OR x_lines_control.category_id ='Y'
685 OR x_lines_control.unit_meas_lookup_code ='Y'
686 OR x_lines_control.from_header_id ='Y'
687 OR x_lines_control.from_line_id ='Y'
688 OR x_lines_control.hazard_class_id ='Y'
689 OR x_lines_control.contract_num ='Y'
690 OR x_lines_control.vendor_product_num ='Y'
691 OR x_lines_control.un_number_id ='Y'
692 OR x_lines_control.price_type_lookup_code ='Y'
693 OR x_lines_control.note_to_vendor ='Y'
694 OR (x_lines_control.quantity_change >
695 nvl(x_tolerance_control.l_quantity_t,0))
696 OR (x_lines_control.unit_price_change >
697 nvl(x_tolerance_control.l_unit_price_t,0))
698 OR (x_lines_control.not_to_exceed_price_change >
699 nvl(x_tolerance_control.l_price_limit_t,0))
700 OR (x_lines_control.quantity_committed_change >
701 nvl(x_tolerance_control.l_quantity_committed_t,0))
702 OR (x_lines_control.committed_amount_change >
703 nvl(x_tolerance_control.l_committed_amount_t,0))
704 THEN
705 IF (g_po_wf_debug = 'Y') THEN
706 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
707 '########## lines_require_reapproval result: Y');
708 END IF;
709 x_result:='Y';
710 ELSE
711 IF (g_po_wf_debug = 'Y') THEN
712 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
713 '########## lines_require_reapproval result: N');
714 END IF;
715 x_result:='N';
716 END IF;
717 END IF;
718
719 /* <TIMEPHASED FPI START> */
720 /* Bug 2808011. Added price_override to the reapproval rules */
721 IF x_result <> 'Y' THEN
722 IF x_shipments_control.shipment_num ='Y'
723 OR x_shipments_control.ship_to_location_id ='Y'
724 OR x_shipments_control.ship_to_organization_id ='Y'
725 OR x_shipments_control.promised_date ='Y'
726 OR x_shipments_control.need_by_date ='Y'
727 OR x_shipments_control.last_accept_date ='Y'
728 OR x_shipments_control.start_date ='Y'
729 OR x_shipments_control.end_date ='Y'
730 OR ( x_shipments_control.price_override ='Y'
731 AND (x_shipments_control.price_override_change >
732 nvl(x_tolerance_control.s_price_override_t,0)))
733 OR (x_shipments_control.quantity_change >
734 nvl(x_tolerance_control.s_quantity_t,0))
735 THEN
736 IF (g_po_wf_debug = 'Y') THEN
737 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
738 '########## shipments_require_reapproval result: Y');
739 END IF;
740 x_result:='Y';
741 ELSE
742 IF (g_po_wf_debug = 'Y') THEN
743 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
744 '########## shipments_require_reapproval result: N');
745 END IF;
746 x_result:='N';
747 END IF;
748 END IF;
749 /* <TIMEPHASED FPI END> */
750
751 -- Bug 2911017 START
752 -- Require reapproval if the GA org assignments have been changed.
753 IF x_result <> 'Y' THEN
754 l_ga_org_assign_change :=
755 PO_WF_UTIL_PKG.GetItemAttrText (itemtype , itemkey,'GA_ORG_ASSIGN_CHANGE');
756 IF l_ga_org_assign_change = 'Y' THEN
757 IF (g_po_wf_debug = 'Y') THEN
758 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
759 '########## GA org assignments require reapproval result: Y');
760 END IF;
761 x_result:='Y';
762 ELSE
763 IF (g_po_wf_debug = 'Y') THEN
764 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
765 '########## GA org assignments require reapproval result: N');
766 END IF;
767 x_result:='N';
768 END IF;
769 END IF;
770 -- Bug 2911017 END
771 --<CONTERMS FPJ START>
772 IF x_result <> 'Y' THEN
773 -- Check if contract terms were changed
774 x_result := PO_CONTERMS_WF_PVT.contract_terms_changed(
775 itemtype => itemtype,
776 itemkey => itemkey);
777 END IF;
778 --<CONTERMS FPJ END>
779 IF x_result = 'Y' THEN
780 IF (g_po_wf_debug = 'Y') THEN
781 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
782 '$$$$$$$ Document requires full approval = Y $$$$$$');
783 END IF;
784 RESULT := 'Y';
785 ELSE
786 IF (g_po_wf_debug = 'Y') THEN
787 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
788 '$$$$$$$ Document requires full approval = N $$$$$$');
789 END IF;
790 RESULT := 'N';
791 END IF;
792
793
794 IF (g_po_wf_debug = 'Y') THEN
795 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
796 '*** Finish: blanket_po_reapproval ***');
797 END IF;
798
799 END;
800
801 /**************************************************************************
802 * *
803 * Reapproval Rules for Contract Purchase Agreement *
804 * *
805 **************************************************************************/
806
807 PROCEDURE contract_po_reapproval(itemtype IN VARCHAR2,
808 itemkey IN VARCHAR2,
809 actid IN NUMBER,
810 FUNCMODE IN VARCHAR2,
811 RESULT OUT NOCOPY VARCHAR2)
812 IS
813 x_header_control t_header_control_type;
814 x_tolerance_control t_tolerance_control_type;
815 BEGIN
816
817 IF (g_po_wf_debug = 'Y') THEN
818 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
819 '*** In Procedure: contract_po_reapproval ***');
820 END IF;
821
822 IF funcmode <> 'RUN' THEN
823 result := 'COMPLETE';
824 return;
825 END IF;
826
827 po_chord_wf1.get_wf_header_control(itemtype, itemkey, x_header_control);
828 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'CONTRACT_PO'); --<R12 Requester Driven Procurement>
829
830 -- 'Y' means modified
831 -- Numbers are in Percentage
832
833
834 -- bug 4624025: vendor site id should be considered for the reapproval,
835 -- rules, and if the same has changed the document should be routed
836 -- through the approval hierarchy
837
838 IF x_header_control.agent_id ='Y'
839 OR x_header_control.vendor_site_id ='Y'
840 OR x_header_control.vendor_contact_id ='Y'
841 OR x_header_control.confirming_order_flag ='Y'
842 OR x_header_control.ship_to_location_id ='Y'
843 OR x_header_control.bill_to_location_id ='Y'
844 OR x_header_control.terms_id ='Y'
845 OR x_header_control.ship_via_lookup_code ='Y'
846 OR x_header_control.fob_lookup_code ='Y'
847 OR x_header_control.freight_terms_lookup_code ='Y'
848 OR x_header_control.note_to_vendor ='Y'
849 OR x_header_control.acceptance_required_flag ='Y'
850 OR x_header_control.acceptance_due_date ='Y'
851 OR x_header_control.start_date ='Y'
852 OR x_header_control.end_date ='Y'
853 OR (x_header_control.amount_limit_change >
854 nvl(x_tolerance_control.h_amount_limit_t,0))
855 OR (x_header_control.blanket_total_change >
856 nvl(x_tolerance_control.h_blanket_total_t,0))
857 THEN
858 IF (g_po_wf_debug = 'Y') THEN
859 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
860 '########## header_require_reapproval result: Y');
861 END IF;
862 result:='Y';
863 ELSE
864 IF (g_po_wf_debug = 'Y') THEN
865 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
866 '########## header_require_reapproval result: N');
867 END IF;
868 result:='N';
869 END IF;
870 --<CONTERMS FPJ START>
871 IF result <> 'Y' THEN
872 -- Check if contract terms were changed
873 result := PO_CONTERMS_WF_PVT.contract_terms_changed(
874 itemtype => itemtype,
875 itemkey => itemkey);
876 END IF;
877 -- Adding the following debug stmt to indicate the final result
878 -- now that the contract terms are also there
879 IF (g_po_wf_debug = 'Y') THEN
880 IF result = 'Y' THEN
881 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
882 '$$$$$$$ Document requires full approval = Y $$$$$$');
883 ELSE
884 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
885 '$$$$$$$ Document requires full approval = N $$$$$$');
886 END IF; -- if result <>'Y'
887 END IF; -- if debug 'Y'
888
889 --<CONTERMS FPJ END>
890
891 IF (g_po_wf_debug = 'Y') THEN
892 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
893 '*** Finish: contract_po_reapproval ***');
894 END IF;
895 return;
896
897 EXCEPTION
898
899 WHEN OTHERS THEN
900 wf_core.context('POAPPRV', 'po_chord_wf6.contract_po_reapproval', 'others');
901 RAISE;
902
903 END;
904
905 /**************************************************************************
906 * *
907 * Reapproval Rules for Blanket Release *
908 * *
909 **************************************************************************/
910
911 PROCEDURE blanket_release_reapproval(itemtype IN VARCHAR2,
912 itemkey IN VARCHAR2,
913 actid IN NUMBER,
914 FUNCMODE IN VARCHAR2,
915 RESULT OUT NOCOPY VARCHAR2)
916 IS
917 x_release_control t_release_control_type;
918 x_shipments_control t_shipments_control_type;
919 x_dist_control t_dist_control_type;
920 x_tolerance_control t_tolerance_control_type;
921 x_result VARCHAR2(1);
922 l_retroactive_change VARCHAR2(1) := 'N'; -- RETROACTIVE FPI
923 l_autoapprove_retro VARCHAR2(1) := 'N'; -- RETROACTIVE FPI
924 l_actionoriginated_from varchar2(30); --Bug5697556
925 BEGIN
926
927 IF (g_po_wf_debug = 'Y') THEN
928 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
929 '*** In Procedure: blanket_release_reapproval ***');
930 END IF;
931
932 IF funcmode <> 'RUN' THEN
933 result := 'COMPLETE';
934 return;
935 END IF;
936
937 /* RETROACTIVE FPI CHANGE START.
938 * Get the value of the attribute CO_H_RETROACTIVE_APPROVAL if
939 * this approval is initiated due to the retroactive change in
940 * the release. If this value is Y, then we send the document
941 * through Change Order Workflow.
942 */
943 l_retroactive_change := PO_WF_UTIL_PKG.GetItemAttrText
944 (itemtype => itemtype,
945 itemkey => itemkey,
946 aname => 'CO_R_RETRO_CHANGE');
947
948 If (l_retroactive_change = 'Y') then
949
950 l_autoapprove_retro := PO_WF_UTIL_PKG.GetItemAttrText
951 (itemtype => itemtype,
952 itemkey => itemkey,
953 aname => 'CO_H_RETROACTIVE_AUTOAPPROVAL');
954 /*Bug5697556 Get the wf attribute value 'INTERFACE_SOURCE_CODE'.This would be
955 the value which indicates where the approval workflow is called from.If
956 approval is called from "Retroactive" concurrent program only then check
957 for the "automatic approval" wf attribute */
958
959 l_actionoriginated_from := PO_WF_UTIL_PKG.GetItemAttrText
960 (itemtype => itemtype,
961 itemkey => itemkey,
962 aname => 'INTERFACE_SOURCE_CODE');
963
964 if (l_autoapprove_retro = 'Y') AND (l_actionoriginated_from = 'RETRO') THEN
965
966
967
968 IF (g_po_wf_debug = 'Y') THEN
969 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
970 '$$$$$$$ Document requires full approval = N $$$$$$');
971 END IF;
972 RESULT := 'N';
973
974 return;
975 end if; /* l__autoapprove_retro = 'Y' */
976
977 end if; /* l_retroactive_change = 'Y' */
978 /* RETROACTIVE FPI CHANGE END */
979
980 po_chord_wf5.get_wf_release_control(itemtype, itemkey, x_release_control);
981 po_chord_wf3.get_wf_shipments_control(itemtype, itemkey, x_shipments_control);
982 po_chord_wf4.get_wf_dist_control(itemtype, itemkey, x_dist_control);
983 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'BLANKET_RELEASE'); --<R12 Requester Driven Procurement>
984
985 -- 'Y' means modified
986 -- Numbers are in Percentage
987 IF x_release_control.agent_id ='Y'
988 OR x_release_control.acceptance_required_flag ='Y'
989 OR x_release_control.acceptance_due_date ='Y'
990 OR x_release_control.release_num ='Y'
991 OR x_release_control.release_date ='Y'
992 THEN
993 IF (g_po_wf_debug = 'Y') THEN
994 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
995 '########## blanket_release_require_reapproval result: Y');
996 END IF;
997 x_result:='Y';
998 ELSE
999 IF (g_po_wf_debug = 'Y') THEN
1000 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1001 '########## blanket_release_require_reapproval result: N');
1002 END IF;
1003 x_result:='N';
1004 END IF;
1005
1006
1007 IF x_result <> 'Y' THEN
1008 IF x_shipments_control.shipment_num ='Y'
1009 OR x_shipments_control.ship_to_location_id ='Y'
1010 OR (x_shipments_control.last_accept_date ='Y'
1011 AND x_shipments_control.days_late_rcpt_allowed ='Y') -- ECO 5080252
1012 OR (x_shipments_control.promised_date ='Y'
1013 AND (x_shipments_control.promised_date_change is null
1014 OR (x_shipments_control.promised_date_change >
1015 nvl(x_tolerance_control.s_promised_date_t,0))) ) --<R12 Requester Driven Procurement>
1016 OR (x_shipments_control.need_by_date ='Y'
1017 AND (x_shipments_control.need_by_date_change is null
1018 OR (x_shipments_control.need_by_date_change >
1019 nvl(x_tolerance_control.s_need_by_date_t,0))) ) --<R12 Requester Driven Procurement>
1020 OR (x_shipments_control.quantity_change >
1021 nvl(x_tolerance_control.s_quantity_t,0))
1022 OR (x_shipments_control.price_override_change >
1023 nvl(x_tolerance_control.s_price_override_t,0))
1024 OR (x_shipments_control.amount_change >
1025 nvl(x_tolerance_control.s_amount_t,0)) --<R12 Requester Driven Procurement>
1026 THEN
1027 IF (g_po_wf_debug = 'Y') THEN
1028 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1029 '########## shipments_require_reapproval result: Y');
1030 END IF;
1031 x_result:='Y';
1032 ELSE
1033 IF (g_po_wf_debug = 'Y') THEN
1034 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1035 '########## shipments_require_reapproval result: N');
1036 END IF;
1037 x_result:='N';
1038 END IF;
1039 END IF;
1040
1041 /* Bug 1081717: kagarwal
1042 ** Added the Check for change in Charge Account for Distributions
1043 ** x_dist_control.code_combination_id = 'Y'
1044 */
1045 /* Bug 2747157: kagarwal
1046 ** Added the Check for change in Gl Date for Distributions
1047 ** x_dist_control.gl_encumbered_date = 'Y' .
1048 */
1049
1050 IF x_result <> 'Y' THEN
1051 IF x_dist_control.distribution_num ='Y'
1052 OR x_dist_control.deliver_to_person_id ='Y'
1053 OR x_dist_control.code_combination_id = 'Y'
1054 OR x_dist_control.gl_encumbered_date = 'Y'
1055 OR (x_dist_control.quantity_ordered_change >
1056 nvl(x_tolerance_control.d_quantity_ordered_t,0))
1057 OR (x_dist_control.amount_ordered_change >
1058 nvl(x_tolerance_control.d_amount_ordered_t,0)) --<R12 Requester Driven Procurement>
1059 THEN
1060 IF (g_po_wf_debug = 'Y') THEN
1061 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1062 '########## dist_require_reapproval result: Y');
1063 END IF;
1064 x_result:='Y';
1065 ELSE
1066 IF (g_po_wf_debug = 'Y') THEN
1067 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1068 '########## dist_require_reapproval result: N');
1069 END IF;
1070 x_result:='N';
1071 END IF;
1072 END IF;
1073
1074 IF x_result = 'Y' THEN
1075 IF (g_po_wf_debug = 'Y') THEN
1076 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1077 '$$$$$$$ Document requires full approval = Y $$$$$$');
1078 END IF;
1079 RESULT := 'Y';
1080 ELSE
1081 IF (g_po_wf_debug = 'Y') THEN
1082 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1083 '$$$$$$$ Document requires full approval = N $$$$$$');
1084 END IF;
1085 RESULT := 'N';
1086 END IF;
1087
1088
1089 IF (g_po_wf_debug = 'Y') THEN
1090 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1091 '*** Finish: blanket_release_reapproval ***');
1092 END IF;
1093 return;
1094
1095 EXCEPTION
1096
1097 WHEN OTHERS THEN
1098 wf_core.context('POAPPRV', 'po_chord_wf6.release_reapproval', 'others');
1099 RAISE;
1100
1101 END;
1102
1103 /**************************************************************************
1104 * *
1105 * Reapproval Rules for Scheduled Release *
1106 * *
1107 **************************************************************************/
1108
1109 PROCEDURE scheduled_release_reapproval(itemtype IN VARCHAR2,
1110 itemkey IN VARCHAR2,
1111 actid IN NUMBER,
1112 FUNCMODE IN VARCHAR2,
1113 RESULT OUT NOCOPY VARCHAR2)
1114 IS
1115 x_release_control t_release_control_type;
1116 x_shipments_control t_shipments_control_type;
1117 x_dist_control t_dist_control_type;
1118 x_tolerance_control t_tolerance_control_type;
1119 x_result VARCHAR2(1);
1120 BEGIN
1121
1122 IF (g_po_wf_debug = 'Y') THEN
1123 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1124 '*** In Procedure: scheduled_release_reapproval ***');
1125 END IF;
1126
1127 IF funcmode <> 'RUN' THEN
1128 result := 'COMPLETE';
1129 return;
1130 END IF;
1131
1132 po_chord_wf5.get_wf_release_control(itemtype, itemkey, x_release_control);
1133 po_chord_wf3.get_wf_shipments_control(itemtype, itemkey, x_shipments_control);
1134 po_chord_wf4.get_wf_dist_control(itemtype, itemkey, x_dist_control);
1135 po_chord_wf6.get_default_tolerance(itemtype, itemkey, x_tolerance_control, 'SCHEDULED_RELEASE'); --<R12 Requester Driven Procurement>
1136
1137 -- 'Y' means modified
1138 -- Numbers are in Percentage
1139 IF x_release_control.agent_id ='Y'
1140 OR x_release_control.acceptance_required_flag ='Y'
1141 OR x_release_control.acceptance_due_date ='Y'
1142 OR x_release_control.release_num ='Y'
1143 OR x_release_control.release_date ='Y'
1144 THEN
1145 IF (g_po_wf_debug = 'Y') THEN
1146 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1147 '########## scheduled_release_require_reapproval result: Y');
1148 END IF;
1149 x_result:='Y';
1150 ELSE
1151 IF (g_po_wf_debug = 'Y') THEN
1152 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1153 '########## scheduled_release_require_reapproval result: N');
1154 END IF;
1155 x_result:='N';
1156 END IF;
1157
1158
1159 IF x_result <> 'Y' THEN
1160 IF x_shipments_control.shipment_num ='Y'
1161 OR x_shipments_control.ship_to_location_id ='Y'
1162 OR x_shipments_control.cancel_flag ='Y'
1163 OR x_shipments_control.closed_code ='Y'
1164 OR (x_shipments_control.quantity_change >
1165 nvl(x_tolerance_control.s_quantity_t,0))
1166 OR (x_shipments_control.price_override_change >
1167 nvl(x_tolerance_control.s_price_override_t,0))
1168 OR (x_shipments_control.amount_change >
1169 nvl(x_tolerance_control.s_amount_t,0))
1170 OR x_shipments_control.promised_date_change > 0
1171 OR x_shipments_control.need_by_date_change > 0
1172 THEN
1173 IF (g_po_wf_debug = 'Y') THEN
1174 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1175 '########## shipments_require_reapproval result: Y');
1176 END IF;
1177 x_result:='Y';
1178 ELSE
1179 IF (g_po_wf_debug = 'Y') THEN
1180 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1181 '########## shipments_require_reapproval result: N');
1182 END IF;
1183 x_result:='N';
1184 END IF;
1185 END IF;
1186
1187 /* Bug 1081717: kagarwal
1188 ** Added the Check for change in Charge Account for Distributions
1189 ** x_dist_control.code_combination_id = 'Y'
1190 */
1191 /* Bug 2747157: kagarwal
1192 ** Added the Check for change in Gl Date for Distributions
1193 ** x_dist_control.gl_encumbered_date = 'Y' .
1194 */
1195
1196 IF x_result <> 'Y' THEN
1197 IF x_dist_control.deliver_to_person_id ='Y'
1198 OR x_dist_control.code_combination_id = 'Y'
1199 OR x_dist_control.gl_encumbered_date = 'Y'
1200 OR (x_dist_control.quantity_ordered_change >
1201 nvl(x_tolerance_control.d_quantity_ordered_t,0))
1202 OR (x_dist_control.amount_ordered_change >
1203 nvl(x_tolerance_control.d_amount_ordered_t,0))
1204 THEN
1205 IF (g_po_wf_debug = 'Y') THEN
1206 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1207 '########## dist_require_reapproval result: Y');
1208 END IF;
1209 x_result:='Y';
1210 ELSE
1211 IF (g_po_wf_debug = 'Y') THEN
1212 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1213 '########## dist_require_reapproval result: N');
1214 END IF;
1215 x_result:='N';
1216 END IF;
1217 END IF;
1218
1219 IF x_result = 'Y' THEN
1220 IF (g_po_wf_debug = 'Y') THEN
1221 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1222 '$$$$$$$ Document requires full approval = Y $$$$$$');
1223 END IF;
1224 RESULT := 'Y';
1225 ELSE
1226 IF (g_po_wf_debug = 'Y') THEN
1227 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1228 '$$$$$$$ Document requires full approval = N $$$$$$');
1229 END IF;
1230 RESULT := 'N';
1231 END IF;
1232
1233
1234 IF (g_po_wf_debug = 'Y') THEN
1235 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1236 '*** Finish: scheduled_release_reapproval ***');
1237 END IF;
1238 return;
1239
1240 EXCEPTION
1241
1242 WHEN OTHERS THEN
1243 wf_core.context('POAPPRV', 'po_chord_wf6.scheduled_release_reapproval', 'others');
1244 RAISE;
1245
1246 END;
1247
1248
1249 /**************************************************************************
1250 * *
1251 * Get user-defined tolerance percentages from workflow definition *
1252 * *
1253 **************************************************************************/
1254
1255 PROCEDURE get_default_tolerance(
1256 itemtype IN VARCHAR2,
1257 itemkey IN VARCHAR2,
1258 x_tolerance_control IN OUT NOCOPY t_tolerance_control_type,
1259 chord_doc_type IN VARCHAR2 default NULL)
1260 IS
1261 x_org_id number;
1262 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
1263 p_api_name varchar2(50):= 'get_default_tolerance';
1264 x_return_status varchar2(1);
1265 x_msg_count NUMBER;
1266 x_msg_data VARCHAR2(2000);
1267 BEGIN
1268
1269 IF (g_po_wf_debug = 'Y') THEN
1270 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1271 '*** In Procedure: get_default_tolerance ***');
1272 END IF;
1273
1274 --<R12 Requester Driven Procurement Start>
1275 -- set the change order type
1276 -- prepare tolerances_table with the required list of tolerance names
1277 if (chord_doc_type = 'STANDARD_PO' OR
1278 chord_doc_type = 'PLANNED_PO') then
1279
1280 get_tolerances_from_wf (itemtype, itemkey, 'PO',x_tolerance_control );
1281
1282 elsif (chord_doc_type = 'BLANKET_PO' OR
1283 chord_doc_type = 'CONTRACT_PO') then
1284
1285 get_tolerances_from_wf (itemtype, itemkey, 'PA',x_tolerance_control );
1286
1287 elsif (chord_doc_type = 'BLANKET_RELEASE' OR
1288 chord_doc_type = 'SCHEDULED_RELEASE') then
1289
1290 get_tolerances_from_wf (itemtype, itemkey, 'RELEASE',x_tolerance_control );
1291 end if;
1292
1293 --<R12 Requester Driven Procurement End>
1294
1295 debug_default_tolerance(itemtype, itemkey,x_tolerance_control);
1296
1297 IF (g_po_wf_debug = 'Y') THEN
1298 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1299 '*** FINISH: get_default_tolerance ***');
1300 END IF;
1301
1302 EXCEPTION
1303 WHEN FND_API.g_exc_unexpected_error THEN
1304 wf_core.context('POAPPRV', 'po_chord_wf6.get_default_tolerance', 'Error in get_tolerances');
1305 RAISE;
1306
1307 WHEN OTHERS THEN
1308 wf_core.context('POAPPRV', 'po_chord_wf6.get_default_tolerance', 'others');
1309 RAISE;
1310
1311 END;
1312
1313 /**************************************************************************
1314 * ECO : 4716963 *
1315 * Get user-defined tolerance percentages from workflow definition *
1316 * *
1317 **************************************************************************/
1318
1319 PROCEDURE get_tolerances_from_wf(
1320 itemtype IN VARCHAR2,
1321 itemkey IN VARCHAR2,
1322 doc_type IN VARCHAR2,
1323 x_tolerance_control IN OUT NOCOPY t_tolerance_control_type)
1324 IS
1325 BEGIN
1326
1327 IF (g_po_wf_debug = 'Y') THEN
1328 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1329 '*** In Procedure: get_tolerances_from_wf ***');
1330 END IF;
1331
1332 -- Common attributes
1333 x_tolerance_control.s_quantity_t :=
1334 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1335 itemkey,
1336 'CO_S_QUANTITY_T');
1337
1338 x_tolerance_control.h_amount_limit_t :=
1339 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1340 itemkey,
1341 'CO_H_AMOUNT_LIMIT_T');
1342
1343 x_tolerance_control.l_unit_price_t :=
1344 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1345 itemkey,
1346 'CO_L_UNIT_PRICE_T');
1347
1348 x_tolerance_control.s_price_override_t :=
1349 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1350 itemkey,
1351 'CO_S_PRICE_OVERRIDE_T');
1352
1353 -- Common attributes between orders and releases
1354 IF doc_type in ('PO','RELEASE') THEN
1355
1356 x_tolerance_control.s_need_by_date_t :=
1357 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1358 itemkey,
1359 'CO_S_NEED_BY_DATE_T');
1360
1361 x_tolerance_control.s_promised_date_t :=
1362 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1363 itemkey,
1364 'CO_S_PROMISED_DATE_T');
1365
1366 x_tolerance_control.d_quantity_ordered_t:=
1367 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1368 itemkey,
1369 'CO_D_QUANTITY_ORDERED_T');
1370
1371 x_tolerance_control.d_amount_ordered_t:=
1372 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1373 itemkey,
1374 'CO_D_AMOUNT_ORDERED_T');
1375
1376 x_tolerance_control.s_amount_t :=
1377 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1378 itemkey,
1379 'CO_S_AMOUNT_T');
1380 END IF;
1381
1382 -- Attributes for orders
1383 IF doc_type = 'PO' THEN
1384
1385 x_tolerance_control.h_po_total_t :=
1386 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1387 itemkey,
1388 'CO_H_PO_TOTAL_T');
1389
1390 x_tolerance_control.l_start_date_t :=
1391 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1392 itemkey,
1393 'CO_L_START_DATE_T');
1394
1395 x_tolerance_control.l_end_date_t :=
1396 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1397 itemkey,
1398 'CO_L_END_DATE_T');
1399
1400 x_tolerance_control.l_quantity_t :=
1401 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1402 itemkey,
1403 'CO_L_QUANTITY_T');
1404
1405 x_tolerance_control.l_amount_t :=
1406 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1407 itemkey,
1408 'CO_L_AMOUNT_T');
1409
1410 -- Complex work attributes
1411 x_tolerance_control.p_quantity_t :=
1412 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1413 itemkey,
1414 'CO_P_QUANTITY_T');
1415
1416 x_tolerance_control.p_price_override_t :=
1417 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1418 itemkey,
1419 'CO_P_PRICE_OVERRIDE_T');
1420
1421 x_tolerance_control.p_amount_t :=
1422 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1423 itemkey,
1424 'CO_P_AMOUNT_T');
1425 END IF;
1426
1427 -- attributes for agreements
1428 IF doc_type = 'PA' THEN
1429 x_tolerance_control.h_blanket_total_t :=
1430 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1431 itemkey,
1432 'CO_H_BLANKET_TOTAL_T');
1433
1434 x_tolerance_control.l_quantity_committed_t :=
1435 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1436 itemkey,
1437 'CO_L_QTY_COMMITTED_T');
1438
1439 x_tolerance_control.l_committed_amount_t :=
1440 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1441 itemkey,
1442 'CO_L_COMMITTED_AMT_T');
1443
1444 x_tolerance_control.l_price_limit_t :=
1445 PO_WF_UTIL_PKG.GetItemAttrText(itemtype,
1446 itemkey,
1447 'CO_L_NOT_TO_EXCEED_PRICE_T');
1448 END IF;
1449
1450 debug_default_tolerance(itemtype, itemkey,x_tolerance_control);
1451
1452 IF (g_po_wf_debug = 'Y') THEN
1453 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1454 '*** FINISH: get_tolerances_from_wf ***');
1455 END IF;
1456
1457 EXCEPTION
1458
1459 WHEN OTHERS THEN
1460 wf_core.context('POAPPRV', 'po_chord_wf6.get_tolerances_from_wf', 'others');
1461 RAISE;
1462
1463 END;
1464
1465 ------------------------------------------------------------------------------
1466 --Start of Comments
1467 --Name: set_Wf_Order_Tol
1468 --Pre-reqs:
1469 -- None
1470 --Modifies:
1471 -- None
1472 --Locks:
1473 -- None
1474 --Function:
1475 -- 1. Get the auto-approval tolerances for orders
1476 --Parameters:
1477 --IN:
1478 -- itemtype Workflow item type (Standard WF function parameters)
1479 -- itemkey Workflow item key (Standard WF function parameters)
1480 -- order_type Type of order
1481 --End of Comment
1482 -------------------------------------------------------------------------------
1483 PROCEDURE Set_Wf_Order_Tol(
1484 itemtype IN VARCHAR2,
1485 itemkey IN VARCHAR2,
1486 order_type IN VARCHAR2)
1487 IS
1488 x_org_id number;
1489 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
1490 x_return_status varchar2(1);
1491 x_msg_count NUMBER;
1492 x_msg_data VARCHAR2(2000);
1493 BEGIN
1494 --<R12 Requester Driven Procurement Start>
1495 IF (g_po_wf_debug = 'Y') THEN
1496 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1497 '*** In Procedure: Set_Wf_Order_Tol ***');
1498 END IF;
1499
1500 -- Retrieve organization id
1501 x_org_id:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemType,
1502 itemkey => itemkey,
1503 aname => 'ORG_ID');
1504 -- Retrieve the tolerances
1505 PO_CO_TOLERANCES_GRP.GET_TOLERANCES (1.0,
1506 FND_API.G_TRUE,
1507 x_org_id,
1508 PO_CO_TOLERANCES_GRP.G_CHG_ORDERS,
1509 x_tol_tab,
1510 x_return_status,
1511 x_msg_count,
1512 x_msg_data);
1513
1514 IF x_return_status <> FND_API.g_ret_sts_success THEN
1515 RAISE FND_API.g_exc_unexpected_error;
1516 END IF;
1517
1518 -- loop through all the tolerances retrieved from the table and set
1519 -- the wf attributes with table values if values are not null
1520 for i in 1..x_tol_tab.count
1521 loop
1522 -- Assign the values
1523 -- ECO 4716963: Added a condition for all tolerences to only assign not
1524 -- null values from the table
1525 -- common tolerances
1526 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_UNIT_PRICE) then
1527
1528 if x_tol_tab(i).max_increment is not null then
1529 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1530 itemkey,
1531 'CO_L_UNIT_PRICE_T',
1532 x_tol_tab(i).max_increment);
1533 end if;
1534
1535 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_QTY) then
1536
1537 if x_tol_tab(i).max_increment is not null then
1538 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1539 itemkey,
1540 'CO_L_QUANTITY_T',
1541 x_tol_tab(i).max_increment);
1542 end if;
1543
1544 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY) then
1545
1546 if x_tol_tab(i).max_increment is not null then
1547 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1548 itemkey,
1549 'CO_S_QUANTITY_T',
1550 x_tol_tab(i).max_increment);
1551 end if;
1552
1553 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DISTRIBUTION_QTY) then
1554
1555 if x_tol_tab(i).max_increment is not null then
1556 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1557 itemkey,
1558 'CO_D_QUANTITY_ORDERED_T',
1559 x_tol_tab(i).max_increment);
1560 end if;
1561
1562 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_NEED_BY_DATE) then
1563
1564 if x_tol_tab(i).max_increment is not null then
1565 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1566 itemkey,
1567 'CO_S_NEED_BY_DATE_T',
1568 x_tol_tab(i).max_increment);
1569 end if;
1570
1571 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE) then
1572
1573 if x_tol_tab(i).max_increment is not null then
1574 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1575 itemkey,
1576 'CO_S_PROMISED_DATE_T',
1577 x_tol_tab(i).max_increment);
1578 end if;
1579
1580 end if;
1581
1582 -- Standard PO tolerances
1583 if (order_type = 'STANDARD') then
1584 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PO_AMOUNT) then
1585
1586 if x_tol_tab(i).max_increment is not null then
1587 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1588 itemkey,
1589 'CO_H_PO_TOTAL_T',
1590 x_tol_tab(i).max_increment);
1591 end if;
1592
1593 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_PERCENT) then
1594
1595 if x_tol_tab(i).max_increment is not null then
1596 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1597 itemkey,
1598 'CO_L_AMOUNT_T',
1599 x_tol_tab(i).max_increment);
1600 end if;
1601
1602 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_PERCENT) then
1603
1604 if x_tol_tab(i).max_increment is not null then
1605 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1606 itemkey,
1607 'CO_S_AMOUNT_T',
1608 x_tol_tab(i).max_increment);
1609 end if;
1610
1611 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DISTRIBUTION_AMOUNT_PERCENT) then
1612
1613 if x_tol_tab(i).max_increment is not null then
1614 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1615 itemkey,
1616 'CO_D_AMOUNT_ORDERED_T',
1617 x_tol_tab(i).max_increment);
1618 end if;
1619 -- <Complex Work R12 Start>
1620 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_AMOUNT_PERCENT) then
1621
1622 if x_tol_tab(i).max_increment is not null then
1623 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1624 itemkey,
1625 'CO_P_AMOUNT_T',
1626 x_tol_tab(i).max_increment);
1627 end if;
1628
1629 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_QTY) then
1630
1631 if x_tol_tab(i).max_increment is not null then
1632 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1633 itemkey,
1634 'CO_P_QUANTITY_T',
1635 x_tol_tab(i).max_increment);
1636 end if;
1637
1638 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_PRICE) then
1639
1640 if x_tol_tab(i).max_increment is not null then
1641 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1642 itemkey,
1643 'CO_P_PRICE_OVERRIDE_T',
1644 x_tol_tab(i).max_increment);
1645 end if;
1646
1647 -- <Complex Work R12 End>
1648 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_START_DATE) then
1649
1650 if x_tol_tab(i).max_increment is not null then
1651 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1652 itemkey,
1653 'CO_L_START_DATE_T',
1654 x_tol_tab(i).max_increment);
1655 end if;
1656
1657 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_END_DATE) then
1658
1659 if x_tol_tab(i).max_increment is not null then
1660 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1661 itemkey,
1662 'CO_L_END_DATE_T',
1663 x_tol_tab(i).max_increment);
1664 end if;
1665
1666 end if;
1667
1668 else -- Planned PO tolerances
1669 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_HEADER_AMOUNT_LIMIT) then
1670
1671 if x_tol_tab(i).max_increment is not null then
1672 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1673 itemkey,
1674 'CO_H_AMOUNT_LIMIT_T',
1675 x_tol_tab(i).max_increment);
1676 end if;
1677
1678 end if;
1679
1680 end if;
1681
1682 end loop;
1683
1684 IF (g_po_wf_debug = 'Y') THEN
1685 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1686 '*** FINISH: get_Default_Order_Tol ***');
1687 END IF;
1688 --<R12 Requester Driven Procurement End>
1689 EXCEPTION
1690 WHEN FND_API.g_exc_unexpected_error THEN
1691 wf_core.context('POAPPRV', 'po_chord_wf6.set_wf_Order_Tol', 'Error in set_wf_Order_Tol');
1692 RAISE;
1693
1694 WHEN OTHERS THEN
1695 wf_core.context('POAPPRV', 'po_chord_wf6.Set_Wf_Order_Tol', 'others');
1696 RAISE;
1697 END Set_Wf_Order_Tol;
1698
1699 ------------------------------------------------------------------------------
1700 --Start of Comments
1701 --Name: set_Wf_Agreement_Tol
1702 --Pre-reqs:
1703 -- None
1704 --Modifies:
1705 -- None
1706 --Locks:
1707 -- None
1708 --Function:
1709 -- 1. Get the auto-approval tolerances for agreements
1710 --Parameters:
1711 --IN:
1712 -- itemtype Workflow item type (Standard WF function parameters)
1713 -- itemkey Workflow item key (Standard WF function parameters)
1714 -- agreement_type Type of agreement
1715 --End of Comment
1716 -------------------------------------------------------------------------------
1717 PROCEDURE Set_Wf_Agreement_Tol(
1718 itemtype IN VARCHAR2,
1719 itemkey IN VARCHAR2,
1720 agreement_type IN VARCHAR2)
1721 IS
1722 x_org_id number;
1723 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
1724 x_return_status varchar2(1);
1725 x_msg_count NUMBER;
1726 x_msg_data VARCHAR2(2000);
1727 BEGIN
1728 --<R12 Requester Driven Procurement Start>
1729 IF (g_po_wf_debug = 'Y') THEN
1730 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1731 '*** In Procedure: Set_Wf_Agreement_Tol ***');
1732 END IF;
1733
1734 -- Retrieve organization id
1735 x_org_id:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemType,
1736 itemkey => itemkey,
1737 aname => 'ORG_ID');
1738 -- Retrieve the tolerances
1739 PO_CO_TOLERANCES_GRP.GET_TOLERANCES (1.0,
1740 FND_API.G_TRUE,
1741 x_org_id,
1742 PO_CO_TOLERANCES_GRP.G_CHG_AGREEMENTS,
1743 x_tol_tab,
1744 x_return_status,
1745 x_msg_count,
1746 x_msg_data);
1747
1748 IF x_return_status <> FND_API.g_ret_sts_success THEN
1749 RAISE FND_API.g_exc_unexpected_error;
1750 END IF;
1751
1752 -- loop through all the tolerances retrieved from the table and
1753 -- set the attributes from the wf with table values if not null
1754 for i in 1..x_tol_tab.count
1755 loop
1756 -- Assign the values
1757 -- ECO 4716963: Added a condition for all tolerences to only assign not
1758 -- null values from the table
1759 if (agreement_type = 'CONTRACT') then
1760
1761 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_HEADER_AMOUNT_LIMIT) then
1762 if x_tol_tab(i).max_increment is not null then
1763 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1764 itemkey,
1765 'CO_H_AMOUNT_LIMIT_T',
1766 x_tol_tab(i).max_increment);
1767 end if;
1768
1769 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_HEADER_AMOUNT_AGREED) then
1770
1771 if x_tol_tab(i).max_increment is not null then
1772 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1773 itemkey,
1774 'CO_H_BLANKET_TOTAL_T',
1775 x_tol_tab(i).max_increment);
1776 end if;
1777 end if;
1778
1779 else -- Blanket PO
1780 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_HEADER_AMOUNT_AGREED) then
1781
1782 if x_tol_tab(i).max_increment is not null then
1783 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1784 itemkey,
1785 'CO_H_BLANKET_TOTAL_T',
1786 x_tol_tab(i).max_increment);
1787 end if;
1788
1789 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_HEADER_AMOUNT_LIMIT) then
1790
1791 if x_tol_tab(i).max_increment is not null then
1792 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1793 itemkey,
1794 'CO_H_AMOUNT_LIMIT_T',
1795 x_tol_tab(i).max_increment);
1796 end if;
1797
1798 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_UNIT_PRICE) then
1799
1800 if x_tol_tab(i).max_increment is not null then
1801 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1802 itemkey,
1803 'CO_L_UNIT_PRICE_T',
1804 x_tol_tab(i).max_increment);
1805 end if;
1806
1807 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PRICE_LIMIT) then
1808
1809 if x_tol_tab(i).max_increment is not null then
1810 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1811 itemkey,
1812 'CO_L_NOT_TO_EXCEED_PRICE_T',
1813 x_tol_tab(i).max_increment);
1814 end if;
1815
1816 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_QTY_AGREED) then
1817 if x_tol_tab(i).max_increment is not null then
1818 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1819 itemkey,
1820 'CO_L_QTY_COMMITTED_T',
1821 x_tol_tab(i).max_increment);
1822 end if;
1823
1824 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_AGREED) then
1825
1826 if x_tol_tab(i).max_increment is not null then
1827 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1828 itemkey,
1829 'CO_L_COMMITTED_AMT_T',
1830 x_tol_tab(i).max_increment);
1831 end if;
1832
1833 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PRC_BRK_PRICE) then
1834
1835 if x_tol_tab(i).max_increment is not null then
1836 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1837 itemkey,
1838 'CO_S_PRICE_OVERRIDE_T',
1839 x_tol_tab(i).max_increment);
1840 end if;
1841
1842 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PRC_BRK_QTY) then
1843
1844 if x_tol_tab(i).max_increment is not null then
1845 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1846 itemkey,
1847 'CO_S_QUANTITY_T',
1848 x_tol_tab(i).max_increment);
1849 end if;
1850
1851 end if;
1852 end if;
1853 end loop;
1854
1855 IF (g_po_wf_debug = 'Y') THEN
1856 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1857 '*** FINISH: get_Default_Agreement_Tol ***');
1858 END IF;
1859 --<R12 Requester Driven Procurement End>
1860 EXCEPTION
1861 WHEN FND_API.g_exc_unexpected_error THEN
1862 wf_core.context('POAPPRV', 'po_chord_wf6.get_Default_Agreement_Tol', 'Error in get_Default_Agreement_Tol');
1863 RAISE;
1864
1865 WHEN OTHERS THEN
1866 wf_core.context('POAPPRV', 'po_chord_wf6.Set_Wf_Agreement_Tol', 'others');
1867 RAISE;
1868 END Set_Wf_Agreement_Tol;
1869
1870 ------------------------------------------------------------------------------
1871 --Start of Comments
1872 --Name: set_Wf_Release_Tol
1873 --Pre-reqs:
1874 -- None
1875 --Modifies:
1876 -- None
1877 --Locks:
1878 -- None
1879 --Function:
1880 -- 1. Get the auto-approval tolerances for releases
1881 --Parameters:
1882 --IN:
1883 -- itemtype Workflow item type (Standard WF function parameters)
1884 -- itemkey Workflow item key (Standard WF function parameters)
1885 -- release_type Type of release
1886 --End of Comment
1887 -------------------------------------------------------------------------------
1888 PROCEDURE Set_Wf_Release_Tol(
1889 itemtype IN VARCHAR2,
1890 itemkey IN VARCHAR2,
1891 release_type IN VARCHAR2)
1892 IS
1893 x_org_id number;
1894 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
1895 x_return_status varchar2(1);
1896 x_msg_count NUMBER;
1897 x_msg_data VARCHAR2(2000);
1898 BEGIN
1899 --<R12 Requester Driven Procurement Start>
1900 IF (g_po_wf_debug = 'Y') THEN
1901 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1902 '*** In Procedure: Set_Wf_Release_Tol ***');
1903 END IF;
1904
1905 -- Retrieve organization id
1906 x_org_id:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemType,
1907 itemkey => itemkey,
1908 aname => 'ORG_ID');
1909 -- Retrieve the tolerances
1910 PO_CO_TOLERANCES_GRP.GET_TOLERANCES (1.0,
1911 FND_API.G_TRUE,
1912 x_org_id,
1913 PO_CO_TOLERANCES_GRP.G_CHG_RELEASES,
1914 x_tol_tab,
1915 x_return_status,
1916 x_msg_count,
1917 x_msg_data);
1918
1919 IF x_return_status <> FND_API.g_ret_sts_success THEN
1920 RAISE FND_API.g_exc_unexpected_error;
1921 END IF;
1922
1923 -- loop through all the tolerances retrieved from the table and
1924 -- set wf attributes with values from the table if not null
1925 for i in 1..x_tol_tab.count
1926 loop
1927 -- Assign the values
1928 -- ECO 4716963: Added a condition for all tolerences to only assign not
1929 -- null values from the table
1930 -- Common Tolerances
1931 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_PRICE) then
1932
1933 if x_tol_tab(i).max_increment is not null then
1934 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1935 itemkey,
1936 'CO_S_PRICE_OVERRIDE_T',
1937 x_tol_tab(i).max_increment);
1938 end if;
1939
1940 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY) then
1941
1942 if x_tol_tab(i).max_increment is not null then
1943 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1944 itemkey,
1945 'CO_S_QUANTITY_T',
1946 x_tol_tab(i).max_increment);
1947 end if;
1948
1949 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DISTRIBUTION_QTY) then
1950
1951 if x_tol_tab(i).max_increment is not null then
1952 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1953 itemkey,
1954 'CO_D_QUANTITY_ORDERED_T',
1955 x_tol_tab(i).max_increment);
1956
1957 end if;
1958
1959 elsif (x_tol_tab(i).tolerance_name =
1960 PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_PERCENT) then
1961
1962 if x_tol_tab(i).max_increment is not null then
1963 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1964 itemkey,
1965 'CO_S_AMOUNT_T',
1966 x_tol_tab(i).max_increment);
1967 end if;
1968
1969 elsif (x_tol_tab(i).tolerance_name =
1970 PO_CO_TOLERANCES_GRP.G_DISTRIBUTION_AMOUNT_PERCENT) then
1971
1972 if x_tol_tab(i).max_increment is not null then
1973 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1974 itemkey,
1975 'CO_D_AMOUNT_ORDERED_T',
1976 x_tol_tab(i).max_increment);
1977 end if;
1978 end if;
1979
1980 if (release_type = 'BLANKET') then
1981
1982 if (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_NEED_BY_DATE) then
1983
1984 if x_tol_tab(i).max_increment is not null then
1985 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1986 itemkey,
1987 'CO_S_NEED_BY_DATE_T',
1988 x_tol_tab(i).max_increment);
1989 end if;
1990
1991 elsif (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE) then
1992
1993 if x_tol_tab(i).max_increment is not null then
1994 PO_WF_UTIL_PKG.SetItemAttrText(itemtype,
1995 itemkey,
1996 'CO_S_PROMISED_DATE_T',
1997 x_tol_tab(i).max_increment);
1998 end if;
1999
2000 end if;
2001 end if;
2002 end loop;
2003
2004 IF (g_po_wf_debug = 'Y') THEN
2005 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2006 '*** FINISH: Set_Wf_Release_Tol ***');
2007 END IF;
2008 --<R12 Requester Driven Procurement End>
2009 EXCEPTION
2010 WHEN FND_API.g_exc_unexpected_error THEN
2011 wf_core.context('POAPPRV', 'po_chord_wf6.get_Default_Release_Tol', 'Error in get_Default_Release_Tol');
2012 RAISE;
2013
2014 WHEN OTHERS THEN
2015 wf_core.context('POAPPRV', 'po_chord_wf6.set_wf_release_tol', 'others');
2016 RAISE;
2017 END Set_Wf_Release_Tol;
2018
2019 PROCEDURE debug_default_tolerance(
2020 itemtype IN VARCHAR2,
2021 itemkey IN VARCHAR2,
2022 x_tolerance_control t_tolerance_control_type)
2023 IS
2024 BEGIN
2025
2026 IF (g_po_wf_debug = 'Y') THEN
2027 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2028 '*** In Procedure: debug_default_tolerance ***');
2029 END IF;
2030
2031 /* Header Percentage Attibutes */
2032 IF (g_po_wf_debug = 'Y') THEN
2033 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2034 'Tolerance - h_blanket_total_t : '||
2035 x_tolerance_control.h_blanket_total_t );
2036 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2037 'Tolerance - h_amount_limit_t : '||
2038 x_tolerance_control.h_amount_limit_t );
2039 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2040 'Tolerance - h_po_total_t : '||
2041 x_tolerance_control.h_po_total_t );
2042
2043 --<R12 Requester Driven Procurement Start>
2044 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2045 'Tolerance - l_start_date_t : '||
2046 x_tolerance_control.l_start_date_t );
2047 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2048 'Tolerance - l_end_date_t : '||
2049 x_tolerance_control.l_end_date_t );
2050 --<R12 Requester Driven Procurement End>
2051 END IF;
2052
2053 /* Line Percentage Attibutes */
2054 IF (g_po_wf_debug = 'Y') THEN
2055 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2056 'Tolerance - l_quantity_t : '||
2057 x_tolerance_control.l_quantity_t );
2058 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2059 'Tolerance - l_unit_price_t : '||
2060 x_tolerance_control.l_unit_price_t );
2061 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2062 'Tolerance - l_quantity_committed_t : '||
2063 x_tolerance_control.l_quantity_committed_t );
2064 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2065 'Tolerance - l_committed_amount_t : '||
2066 x_tolerance_control.l_committed_amount_t );
2067 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2068 'Tolerance - l_price_limit_t : '||
2069 x_tolerance_control.l_price_limit_t );
2070 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2071 'Tolerance - l_price_limit_t : '||
2072 x_tolerance_control.l_price_limit_t );
2073 --<R12 Requester Driven Procurement Start>
2074 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2075 'Tolerance - l_amount_t : '||
2076 x_tolerance_control.l_amount_t );
2077 --<R12 Requester Driven Procurement End>
2078 END IF;
2079
2080 /* Shipment Percentage Attributes */
2081 IF (g_po_wf_debug = 'Y') THEN
2082 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2083 'Tolerance - s_quantity_t : '||
2084 x_tolerance_control.s_quantity_t );
2085 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2086 'Tolerance - s_price_override_t : '||
2087 x_tolerance_control.s_price_override_t );
2088
2089 --<R12 Requester Driven Procurement Start>
2090 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2091 'Tolerance - s_amount_t : '||
2092 x_tolerance_control.s_amount_t );
2093 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2094 'Tolerance - s_need_by_date_t : '||
2095 x_tolerance_control.s_need_by_date_t );
2096 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2097 'Tolerance - s_promised_date_t : '||
2098 x_tolerance_control.s_promised_date_t );
2099 --<R12 Requester Driven Procurement End>
2100
2101 END IF;
2102
2103 /* Distributions Attributes */
2104 IF (g_po_wf_debug = 'Y') THEN
2105 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2106 'Tolerance - d_quantity_ordered_t : '||
2107 x_tolerance_control.d_quantity_ordered_t );
2108 --<R12 Requester Driven Procurement Start>
2109 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2110 'Tolerance - d_amount_ordered_t : '||
2111 x_tolerance_control.d_amount_ordered_t );
2112 --<R12 Requester Driven Procurement End>
2113 END IF;
2114
2115 IF (g_po_wf_debug = 'Y') THEN
2116 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
2117 '*** FINISH: debug_default_tolerance ***');
2118 END IF;
2119
2120 EXCEPTION
2121
2122 WHEN OTHERS THEN
2123 wf_core.context('POAPPRV', 'po_chord_wf6.debug_default_tolerance', 'others');
2124 RAISE;
2125
2126 END;
2127
2128 END PO_CHORD_WF6;