[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_PRICE_TOLERANCE_PVT
Source
1 PACKAGE BODY PO_PDOI_PRICE_TOLERANCE_PVT AS
2 /* $Header: PO_PDOI_PRICE_TOLERANCE_PVT.plb 120.5 2011/03/10 17:36:35 ppadilam ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_PRICE_TOLERANCE_PVT');
6
7 --------------------------------------------------------------------------
8 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
9 --------------------------------------------------------------------------
10
11
12
13 --------------------------------------------------------------------------
14 ---------------------- PUBLIC PROCEDURES ---------------------------------
15 --------------------------------------------------------------------------
16
17 -----------------------------------------------------------------------
18 --Start of Comments
19 --Name: start_price_tolerance_wf
20 --Function:
21 -- start the workflow to send notification of price exceeding tolerance
22 -- to users
23 --Parameters:
24 --IN:
25 -- p_intf_header_id
26 -- identifier in interface table for the document
27 -- p_po_header_id
28 -- identifier in txn table for the document
29 -- p_document_num
30 -- document number
31 -- p_batch_id
32 -- batch_id of current request
33 -- p_document_type
34 -- document type, can be STANDARD/BLANKET/QUOTATION
35 -- p_document_subtype
36 -- subtype of the document type
37 -- p_commit_interval
38 -- value passed from request
39 -- p_any_line_updated
40 -- flag to indicate whether there is any other line created or updated
41 -- for the same document
42 -- p_buyer_id
43 -- value passed from request
44 -- p_agent_id
45 -- agent_id for the document
46 -- p_vendor_id
47 -- vendor_id for the document
48 -- p_vendor_name
49 -- corresponding vendor name derived from vendor_id
50 --IN OUT:
51 --OUT:
52 --End of Comments
53 ------------------------------------------------------------------------
54 PROCEDURE start_price_tolerance_wf
55 (
56 p_intf_header_id IN NUMBER,
57 p_po_header_id IN NUMBER,
58 p_document_num IN VARCHAR2,
59 p_batch_id IN NUMBER,
60 p_document_type IN VARCHAR2,
61 p_document_subtype IN VARCHAR2,
62 p_commit_interval IN NUMBER,
63 p_any_line_updated IN VARCHAR2,
64 p_buyer_id IN NUMBER,
65 p_agent_id IN NUMBER,
66 p_vendor_id IN NUMBER,
67 p_vendor_name IN VARCHAR2
68 ) IS
69
70 d_api_name CONSTANT VARCHAR2(30) := 'start_price_tolerance_wf';
71 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
72 d_position NUMBER;
73
74 l_wf_item_type VARCHAR2(240) := 'POPRICAT';
75 l_wf_item_key VARCHAR2(240);
76 l_wf_item_exists VARCHAR2(1) := 'N';
77 l_wf_item_end_date DATE;
78
79 l_num_of_items NUMBER;
80
81 l_orig_system VARCHAR2(5) := 'PER';
82 l_agent_username VARCHAR2(240);
83 l_agent_display_name VARCHAR2(240);
84
85 l_open_form VARCHAR2(240);
86 l_batch_id Number;
87 BEGIN
88 d_position := 0;
89
90 IF (PO_LOG.d_proc) THEN
91 PO_LOG.proc_begin(d_module, 'p_intf_header_id', p_intf_header_id);
92 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
93 PO_LOG.proc_begin(d_module, 'p_document_num', p_document_num);
94 PO_LOG.proc_begin(d_module, 'p_batch_id', p_batch_id);
95 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
96 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
97 PO_LOG.proc_begin(d_module, 'p_commit_interval', p_commit_interval);
98 PO_LOG.proc_begin(d_module, 'p_any_line_updated', p_any_line_updated);
99 PO_LOG.proc_begin(d_module, 'p_buyer_id', p_buyer_id);
100 PO_LOG.proc_begin(d_module, 'p_agent_id', p_agent_id);
101 PO_LOG.proc_begin(d_module, 'p_vendor_id', p_vendor_id);
102 PO_LOG.proc_begin(d_module, 'p_vendor_name', p_vendor_name);
103 END IF;
104
105 /*Bug 10023715:<Start> Retrived the batch_id value from Po_headers_interface table*/
106 SELECT batch_id
107 INTO l_batch_id
108 FROM po_headers_interface
109 WHERE INTERFACE_HEADER_ID = p_intf_header_id;
110
111 l_wf_item_key := 'POI-PRICAT-' || to_char(p_intf_header_id) || '-' || to_char(l_batch_id);
112 /* Bug 10023715 <end>*/
113 -- check whether there is same item exist and open
114 BEGIN
115 SELECT 'Y', WI.end_date
116 INTO l_wf_item_exists, l_wf_item_end_date
117 FROM WF_ITEMS_V WI
118 WHERE WI.ITEM_TYPE = l_wf_item_type
119 AND WI.ITEM_KEY = l_wf_item_key;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 NULL;
123 WHEN OTHERS THEN
124 PO_MESSAGE_S.add_exc_msg
125 (
126 p_pkg_name => d_pkg_name,
127 p_procedure_name => d_api_name || '.' || d_position
128 );
129 RAISE;
130 END;
131
132 IF (PO_LOG.d_stmt) THEN
133 PO_LOG.stmt(d_module, d_position, 'l_wf_item_key', l_wf_item_key);
134 PO_LOG.stmt(d_module, d_position, 'l_wf_item_exists', l_wf_item_exists);
135 PO_LOG.stmt(d_module, d_position, 'l_wf_item_end_date', l_wf_item_end_date);
136 END IF;
137
138 d_position := 10;
139
140 -- check whether workflow needs to be started
141 IF (l_wf_item_exists = 'Y' AND l_wf_item_end_date IS NULL) THEN
142 d_position := 20;
143
144 -- Workflow item exists and is still open - bypass creating workflow
145 IF (PO_LOG.d_proc) THEN
146 PO_LOG.proc_end (d_module, 'bypass workflow', l_wf_item_exists);
147 END IF;
148
149 RETURN;
150 ELSE
151 d_position := 30;
152
153 IF (l_wf_item_exists = 'Y' AND l_wf_item_end_date IS NOT NULL) THEN
154 -- Call purge workflow to remove the completed process
155 IF (PO_LOG.d_stmt) THEN
156 PO_LOG.stmt(d_module, d_position, 'Purging completed Workflow');
157 END IF;
158
159 WF_PURGE.TOTAL
160 (
161 l_wf_item_type,
162 l_wf_item_key
163 );
164 END IF;
165
166 d_position := 40;
167
168 -- start workflow
169 SELECT COUNT(1)
170 INTO l_num_of_items
171 FROM po_lines_interface
172 WHERE interface_header_id = p_intf_header_id
173 AND process_code = 'NOTIFIED'
174 AND nvl(price_break_flag,'N') = 'N';
175
176 IF (PO_LOG.d_stmt) THEN
177 PO_LOG.stmt(d_module, d_position, 'l_num_of_items', l_num_of_items);
178 END IF;
179
180 d_position := 50;
181
182 WF_DIRECTORY.GetUserName
183 (
184 l_orig_system,
185 p_agent_id,
186 l_agent_username,
187 l_agent_display_name
188 );
189
190 d_position := 60;
191
192 l_open_form := 'PO_POXPCATN:INTERFACE_HEADER_ID="' || '&' || 'INTERFACE_HEADER_ID"' ||
193 ' ACCESS_LEVEL_CODE="' || '&' || 'ACCESS_LEVEL_CODE"';
194
195 IF (PO_LOG.d_stmt) THEN
196 PO_LOG.stmt(d_module, d_position, 'l_agent_username', l_agent_username);
197 PO_LOG.stmt(d_module, d_position, 'l_agent_display_name', l_agent_display_name);
198 PO_LOG.stmt(d_module, d_position, 'l_open_form', l_open_form);
199 END IF;
200
201 WF_ENGINE.createProcess
202 (
203 ItemType => l_wf_item_type,
204 ItemKey => l_wf_item_key,
205 Process => 'PROCESS_LINE_ITEMS'
206 );
207
208 d_position := 70;
209
210 WF_ENGINE.SetItemAttrNumber
211 (
212 itemtype => l_wf_item_type,
213 itemkey => l_wf_item_key,
214 aname => 'INTERFACE_HEADER_ID',
215 avalue => p_intf_header_id
216 );
217
218 WF_ENGINE.SetItemAttrNumber
219 (
220 itemtype => l_wf_item_type,
221 itemkey => l_wf_item_key,
222 aname => 'DOCUMENT_ID',
223 avalue => p_po_header_id
224 );
225
226 WF_ENGINE.SetItemAttrText
227 (
228 itemtype => l_wf_item_type,
229 itemkey => l_wf_item_key,
230 aname => 'DOCUMENT_NUM',
231 avalue => p_document_num
232 );
233
234 WF_ENGINE.SetItemAttrNumber
235 (
236 itemtype => l_wf_item_type,
237 itemkey => l_wf_item_key,
238 aname => 'BATCH_ID',
239 avalue => p_batch_id
240 );
241
242 WF_ENGINE.SetItemAttrText
243 (
244 itemtype => l_wf_item_type,
245 itemkey => l_wf_item_key,
246 aname => 'DOCUMENT_TYPE_CODE',
247 avalue => p_document_type
248 );
249
250 d_position := 80;
251
252 WF_ENGINE.SetItemAttrText
253 (
254 itemtype => l_wf_item_type,
255 itemkey => l_wf_item_key,
256 aname => 'DOCUMENT_SUBTYPE',
257 avalue => p_document_subtype
258 );
259
260 WF_ENGINE.SetItemAttrNumber
261 (
262 itemtype => l_wf_item_type,
263 itemkey => l_wf_item_key,
264 aname => 'COMMIT_INTERVAL',
265 avalue => p_commit_interval
266 );
267
268 WF_ENGINE.SetItemAttrNumber
269 (
270 itemtype => l_wf_item_type,
271 itemkey => l_wf_item_key,
272 aname => 'NUMBER_OF_ITEMS',
273 avalue => l_num_of_items
274 );
275
276 WF_ENGINE.SetItemAttrText
277 (
278 itemtype => l_wf_item_type,
279 itemkey => l_wf_item_key,
280 aname => 'ANY_LINE_ITEM_UPDATED',
281 avalue => NVL(p_any_line_updated, 'N')
282 );
283
284 WF_ENGINE.SetItemAttrNumber
285 (
286 itemtype => l_wf_item_type,
287 itemkey => l_wf_item_key,
288 aname => 'BUYER_ID',
289 avalue => p_buyer_id
290 );
291
292 d_position := 90;
293
294 WF_ENGINE.SetItemAttrText
295 (
296 itemtype => l_wf_item_type,
297 itemkey => l_wf_item_key,
298 aname => 'BUYER_USER_NAME',
299 avalue => l_agent_username
300 );
301
302 WF_ENGINE.SetItemAttrText
303 (
304 itemtype => l_wf_item_type,
305 itemkey => l_wf_item_key,
306 aname => 'BUYER_DISPLAY_NAME',
307 avalue => l_agent_display_name
308 );
309
310 WF_ENGINE.SetItemAttrText
311 (
312 itemtype => l_wf_item_type,
313 itemkey => l_wf_item_key,
314 aname => 'OPEN_FORM_COMMAND',
315 avalue => l_open_form
316 );
317
318 WF_ENGINE.SetItemAttrNumber
319 (
320 itemtype => l_wf_item_type,
321 itemkey => l_wf_item_key,
322 aname => 'SUPPLIER_ID',
323 avalue => p_vendor_id
324 );
325
326 WF_ENGINE.SetItemAttrText
327 (
328 itemtype => l_wf_item_type,
329 itemkey => l_wf_item_key,
330 aname => 'SUPPLIER',
331 avalue => p_vendor_name
332 );
333
334 WF_ENGINE.SetItemOwner
335 (
336 itemtype => l_wf_item_type,
337 itemkey => l_wf_item_key,
338 owner => l_agent_username
339 );
340
341 d_position := 100;
342
343 WF_ENGINE.startprocess
344 (
345 itemtype => l_wf_item_type,
346 itemkey => l_wf_item_key
347 );
348 END IF;
349
350 IF (PO_LOG.d_stmt) THEN
351 PO_LOG.stmt(d_module, d_position, 'workflow started');
352 END IF;
353
354 IF (PO_LOG.d_proc) THEN
355 PO_LOG.proc_end (d_module);
356 END IF;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 PO_MESSAGE_S.add_exc_msg
361 (
362 p_pkg_name => d_pkg_name,
363 p_procedure_name => d_api_name || '.' || d_position
364 );
365 RAISE;
366 END start_price_tolerance_wf;
367
368 -----------------------------------------------------------------------
369 --Start of Comments
370 --Name: get_price_tolerance
371 --Function: get the price tolerance percentage for each line
372 --Parameters:
373 --IN:
374 -- p_index_tbl
375 -- table containing indexes of rows
376 -- p_po_header_id_tbl
377 -- list of po_header_id within the batch
378 -- p_item_id_tbl
379 -- list of item_ids within the batch
380 -- p_category_id_tbl
381 -- list of category_ids within the batch
382 -- p_vendor_id_tbl
383 -- list of vendor_ids within the batch
384 --IN OUT:
385 -- x_price_update_tolerance_tbl
386 -- list of price_update_tolerance values within the batch;
387 -- the extracted result in this procedure will also will
388 -- saved in this pl/sql table
389 --OUT:
390 --End of Comments
391 ------------------------------------------------------------------------
392 PROCEDURE get_price_tolerance
393 (
394 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
395 p_po_header_id_tbl IN PO_TBL_NUMBER,
396 p_item_id_tbl IN PO_TBL_NUMBER,
397 p_category_id_tbl IN PO_TBL_NUMBER,
398 p_vendor_id_tbl IN PO_TBL_NUMBER,
399 x_price_update_tolerance_tbl OUT NOCOPY PO_TBL_NUMBER
400 ) IS
401
402 d_api_name CONSTANT VARCHAR2(30) := 'get_price_tolerance';
403 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
404 d_position NUMBER;
405
406 l_key po_session_gt.key%TYPE;
407
408 l_index_tbl PO_TBL_NUMBER;
409 l_tolerance_tbl PO_TBL_NUMBER;
410
411 l_index NUMBER;
412 BEGIN
413 d_position := 0;
414
415 IF (PO_LOG.d_proc) THEN
416 PO_LOG.proc_begin(d_module, 'p_po_header_id_tbl', p_po_header_id_tbl);
417 PO_LOG.proc_begin(d_module, 'p_item_id_tbl', p_item_id_tbl);
418 PO_LOG.proc_begin(d_module, 'p_category_id_tbl', p_category_id_tbl);
419 PO_LOG.proc_begin(d_module, 'p_vendor_id_tbl', p_vendor_id_tbl);
420 END IF;
421
422 -- initialize out parameter
423 x_price_update_tolerance_tbl := PO_TBL_NUMBER();
424 x_price_update_tolerance_tbl.EXTEND(p_po_header_id_tbl.COUNT);
425
426 l_key := PO_CORE_S.get_session_gt_nextval;
427
428 -- first, the value is fetched from po_asl_attributes table
429 FORALL i IN INDICES OF p_index_tbl
430 INSERT INTO po_session_gt(key, num1, num2)
431 SELECT l_key,
432 p_index_tbl(i),
433 price_update_tolerance
434 FROM po_asl_attributes
435 WHERE (item_id = p_item_id_tbl(i) OR
436 category_id = p_category_id_tbl(i) OR
437 category_id IN
438 (SELECT MIC.category_id
439 FROM MTL_ITEM_CATEGORIES MIC
440 WHERE MIC.inventory_item_id = p_item_id_tbl(i)
441 AND MIC.organization_id =
442 PO_PDOI_PARAMS.g_sys.master_inv_org_id)
443 )
444 AND vendor_id = p_vendor_id_tbl(i)
445 AND using_organization_id IN (-1, PO_PDOI_PARAMS.g_sys.master_inv_org_id);
446
447 d_position := 20;
448
449 DELETE FROM po_session_gt
450 WHERE key = l_key
451 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_tolerance_tbl;
452
453 FOR i IN 1..l_index_tbl.COUNT
454 LOOP
455 IF (PO_LOG.d_stmt) THEN
456 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
457 PO_LOG.stmt(d_module, d_position, 'price tolerance from asl attributes',
458 l_tolerance_tbl(i));
459 END IF;
460
461 x_price_update_tolerance_tbl(l_index_tbl(i)) := l_tolerance_tbl(i);
462 END LOOP;
463
464 d_position := 30;
465
466 -- if tolerance is still null, get value from document header
467 FORALL i IN INDICES OF p_index_tbl
468 INSERT INTO po_session_gt(key, num1, num2)
469 SELECT l_key,
470 p_index_tbl(i),
471 price_update_tolerance
472 FROM po_headers_all
473 WHERE po_header_id = p_po_header_id_tbl(i)
474 AND type_lookup_code = PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET
475 AND x_price_update_tolerance_tbl(i) IS NULL;
476
477 d_position := 40;
478
479 DELETE FROM po_session_gt
480 WHERE key = l_key
481 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_tolerance_tbl;
482
483 FOR I IN 1..l_index_tbl.COUNT
484 LOOP
485 IF (PO_LOG.d_stmt) THEN
486 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
487 PO_LOG.stmt(d_module, d_position, 'price tolerance from headers',
488 l_tolerance_tbl(i));
489 END IF;
490
491 x_price_update_tolerance_tbl(l_index_tbl(i)) := l_tolerance_tbl(i);
492 END LOOP;
493
494 d_position := 50;
495
496 -- set up price_tolerance from profile for the incoming index
497 l_index := p_index_tbl.FIRST;
498 WHILE (l_index IS NOT NULL)
499 LOOP
500 d_position := 60;
501
502 IF (x_price_update_tolerance_tbl(l_index) IS NULL) THEN
503 IF (PO_LOG.d_stmt) THEN
504 PO_LOG.stmt(d_module, d_position, 'index', l_index);
505 PO_LOG.stmt(d_module, d_position, 'price tolerance from profile',
506 PO_PDOI_PARAMS.g_profile.po_price_update_tolerance);
507 END IF;
508
509 x_price_update_tolerance_tbl(l_index) :=
510 PO_PDOI_PARAMS.g_profile.po_price_update_tolerance;
511 END IF;
512
513 l_index := p_index_tbl.NEXT(l_index);
514 END LOOP;
515
516 IF (PO_LOG.d_proc) THEN
517 PO_LOG.proc_end(d_module);
518 END IF;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 PO_MESSAGE_S.add_exc_msg
523 (
524 p_pkg_name => d_pkg_name,
525 p_procedure_name => d_api_name || '.' || d_position
526 );
527 RAISE;
528 END get_price_tolerance;
529
530 -----------------------------------------------------------------------
531 --Start of Comments
532 --Name: exceed_tolerance_check
533 --Function: check whether new price exceeds the tolerance
534 --Parameters:
535 --IN:
536 -- p_price_tolerance
537 -- update tolerance value
538 -- p_old_price
539 -- price before update
540 -- p_new_price
541 -- new price after update
542 --IN OUT:
543 --OUT:
544 --RETURN: flag indicate whether new price exceeds the tolerance
545 --End of Comments
546 ------------------------------------------------------------------------
547 FUNCTION exceed_tolerance_check
548 (
549 p_price_tolerance IN NUMBER,
550 p_old_price IN NUMBER,
551 p_new_price IN NUMBER
552 ) RETURN VARCHAR2
553 IS
554
555 d_api_name CONSTANT VARCHAR2(30) := 'exceed_tolerance_check';
556 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
557 d_position NUMBER;
558
559 l_exceed VARCHAR2(1) := FND_API.g_FALSE;
560 BEGIN
561 d_position := 0;
562
563 IF (PO_LOG.d_proc) THEN
564 PO_LOG.proc_begin(d_module, 'p_price_tolerance', p_price_tolerance);
565 PO_LOG.proc_begin(d_module, 'p_old_price', p_old_price);
566 PO_LOG.proc_begin(d_module, 'p_new_price', p_new_price);
567 END IF;
568
569 d_position := 10;
570
571 IF (p_price_tolerance IS NOT NULL AND
572 ((1 + p_price_tolerance/100) * p_old_price < p_new_price)) THEN
573 l_exceed := FND_API.g_TRUE;
574 END IF;
575
576 IF (PO_LOG.d_proc) THEN
577 PO_LOG.proc_return(d_module, l_exceed);
578 END IF;
579
580 RETURN l_exceed;
581 EXCEPTION
582 WHEN OTHERS THEN
583 PO_MESSAGE_S.add_exc_msg
584 (
585 p_pkg_name => d_pkg_name,
586 p_procedure_name => d_api_name || '.' || d_position
587 );
588 RAISE;
589 END exceed_tolerance_check;
590 -------------------------------------------------------------------------
591 --------------------- PRIVATE PROCEDURES --------------------------------
592 -------------------------------------------------------------------------
593
594 END PO_PDOI_PRICE_TOLERANCE_PVT;