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