[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_WORKFLOW
Source
1 PACKAGE BODY inv_reservation_workflow AS
2 /* $Header: INVRSVWB.pls 120.2 2005/10/11 10:23:11 methomas noship $*/
3
4 -- Procedure
5 -- get_item_number
6 -- Description
7 -- find the item number using the input p_organization_id and
8 -- p_inventory_item_id
9 -- Output Parameters
10 -- x_item_number item number or null if error occurred
11 PROCEDURE get_item_number
12 ( p_organization_id IN NUMBER
13 , p_inventory_item_id IN NUMBER
14 , x_item_number OUT NOCOPY VARCHAR2
15 ) IS
16 l_val BOOLEAN;
17 l_nseg NUMBER;
18 l_seglist fnd_flex_key_api.segment_list;
19 l_segs1 fnd_flex_ext.segmentarray;
20 l_segs2 fnd_flex_ext.segmentarray;
21 l_fftype fnd_flex_key_api.flexfield_type;
22 l_ffstru fnd_flex_key_api.structure_type;
23 l_segment_type fnd_flex_key_api.segment_type;
24 l_item_number VARCHAR2(32000);
25 l_delim VARCHAR2(1);
26 l_index NUMBER;
27 BEGIN
28 -- find flex field type
29 l_fftype := fnd_flex_key_api.find_flexfield('INV', 'MSTK');
30 -- find flex structure type
31 l_ffstru := fnd_flex_key_api.find_structure(l_fftype, 101);
32 -- find segment list for the key flex field
33 fnd_flex_key_api.get_segments(l_fftype, l_ffstru, TRUE, l_nseg, l_seglist);
34 -- get the corresponding clolumn for all segments
35 FOR l_loop IN 1..l_nseg LOOP
36 l_segment_type := fnd_flex_key_api.find_segment(l_fftype, l_ffstru, l_seglist(l_loop));
37 l_segs2(l_loop) := l_segment_type.column_name;
38 END LOOP;
39
40 -- get all segments from the item table
41 SELECT
42 segment1, segment2, segment3, segment4, segment5,
43 segment6, segment7, segment8, segment9, segment10,
44 segment11, segment12, segment13, segment14, segment15,
45 segment16, segment17, segment18, segment19, segment20
46 INTO
47 l_segs1(1), l_segs1(2), l_segs1(3), l_segs1(4), l_segs1(5),
48 l_segs1(6), l_segs1(7), l_segs1(8), l_segs1(9), l_segs1(10),
49 l_segs1(11), l_segs1(12), l_segs1(13), l_segs1(14), l_segs1(15),
50 l_segs1(16), l_segs1(17), l_segs1(18), l_segs1(19), l_segs1(20)
51 FROM mtl_system_items
52 WHERE organization_id = p_organization_id
53 AND inventory_item_id = p_inventory_item_id;
54
55 -- get delimiter for segment concatenation
56 l_delim := fnd_flex_ext.get_delimiter('INV', 'MSTK', 101);
57
58 -- concatenate segments based on the order defined by the flex
59 -- field structure
60 FOR l_loop IN 1..l_nseg LOOP
61 l_index := To_number(Substr(l_segs2 (l_loop), 8,1));
62 IF l_loop = 1 THEN
63 l_item_number := l_segs1(l_index);
64 ELSE
65 l_item_number := l_item_number || l_delim || l_segs1(l_index);
66 END IF;
67 END LOOP;
68
69 x_item_number := l_item_number;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 x_item_number := NULL;
74
75 END get_item_number;
76
77 -- Procedure
78 -- get_locator
79 -- Description
80 -- find the locator using the input p_organization_id and
81 -- p_locator_id
82 -- Output Parameters
83 -- x_locator locator or null if error occurred
84 PROCEDURE get_locator
85 ( p_organization_id IN NUMBER
86 , p_locator_id IN NUMBER
87 , x_locator OUT NOCOPY VARCHAR2
88 ) IS
89 l_nseg NUMBER;
90 l_seglist fnd_flex_key_api.segment_list;
91 l_segs1 fnd_flex_ext.segmentarray;
92 l_segs2 fnd_flex_ext.segmentarray;
93 l_fftype fnd_flex_key_api.flexfield_type;
94 l_ffstru fnd_flex_key_api.structure_type;
95 l_segment_type fnd_flex_key_api.segment_type;
96 l_locator VARCHAR2(32000);
97 l_structure_list fnd_flex_key_api.structure_list;
98 l_nstru NUMBER;
99 l_index NUMBER;
100 l_delim VARCHAR2(1);
101 BEGIN
102 -- find flex field type
103 l_fftype := fnd_flex_key_api.find_flexfield('INV', 'MTLL');
104
105 -- find flex structure type
106 l_ffstru := fnd_flex_key_api.find_structure(l_fftype, 101);
107
108 -- find segment list for the key flex field
109 fnd_flex_key_api.get_segments(l_fftype, l_ffstru, TRUE, l_nseg, l_seglist);
110
111 -- get the corresponding clolumn for all segments
112 FOR l_loop IN 1..l_nseg LOOP
113 l_segment_type := fnd_flex_key_api.find_segment(l_fftype, l_ffstru, l_seglist(l_loop));
114 l_segs2(l_loop) := l_segment_type.column_name;
115 END LOOP;
116
117 -- get all segments from the item table
118 SELECT
119 segment1, segment2, segment3, segment4, segment5,
120 segment6, segment7, segment8, segment9, segment10,
121 segment11, segment12, segment13, segment14, segment15,
122 segment16, segment17, segment18, segment19, segment20
123 INTO
124 l_segs1(1), l_segs1(2), l_segs1(3), l_segs1(4), l_segs1(5),
125 l_segs1(6), l_segs1(7), l_segs1(8), l_segs1(9), l_segs1(10),
126 l_segs1(11), l_segs1(12), l_segs1(13), l_segs1(14), l_segs1(15),
127 l_segs1(16), l_segs1(17), l_segs1(18), l_segs1(19), l_segs1(20)
128 FROM mtl_item_locations
129 WHERE organization_id = p_organization_id
130 AND inventory_location_id = p_locator_id;
131
132 -- get delimiter for segment concatenation
133 l_delim := fnd_flex_ext.get_delimiter('INV', 'MTLL', 101);
134
135 -- concatenate segments based on the order defined by the flex
136 -- field structure
137 FOR l_loop IN 1..l_nseg LOOP
138 l_index := To_number(Substr(l_segs2 (l_loop), 8,1));
139 IF l_loop = 1 THEN
140 l_locator := l_segs1(l_index);
141 ELSE
142 l_locator := l_locator || l_delim || l_segs1(l_index);
143 END IF;
144 END LOOP;
145
146 x_locator := l_locator;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 x_locator := NULL;
151
152 END get_locator;
153
154 -- Procedure
155 -- handle_broken_reservation
156 -- Description
157 -- Start the work flow process to handle broken reservation
158 -- Output Parameters
159 -- x_return_status 'T' if succeeded, 'F' if failed
160 PROCEDURE handle_broken_reservation
161 (
162 p_item_type IN VARCHAR2 DEFAULT 'INVRSVWF'
163 , p_item_key IN VARCHAR2
164 , p_reservation_id IN NUMBER
165 , p_organization_id IN NUMBER
166 , p_organization_code IN VARCHAR2
167 , p_inventory_item_id IN NUMBER
168 , p_inventory_item_number IN VARCHAR2
169 , p_revision IN VARCHAR2
170 , p_lot_number IN VARCHAR2
171 , p_subinventory_code IN VARCHAR2
172 , p_locator_id IN NUMBER
173 , p_locator IN VARCHAR2
174 , p_demand_source_type_id IN NUMBER
175 , p_demand_source_type IN VARCHAR2
176 , p_demand_source_header_id IN NUMBER
177 , p_demand_source_line_id IN NUMBER
178 , p_demand_source_name IN VARCHAR2
179 , p_supply_source_type_id IN NUMBER
180 , p_supply_source_type IN VARCHAR2
181 , p_supply_source_header_id IN NUMBER
182 , p_supply_source_line_id IN NUMBER
183 , p_supply_source_name IN VARCHAR2
184 , p_supply_source_line_detail IN NUMBER
185 , p_primary_uom_code IN VARCHAR2
186 , p_primary_reservation_quantity IN NUMBER
187 , p_from_user_name IN VARCHAR2
188 , p_to_notify_role IN VARCHAR2
189 ) IS
190 BEGIN
191
192 wf_engine.createprocess
193 (
194 itemtype => p_item_type
195 , itemkey => p_item_key
196 );
197
198 wf_engine.setitemowner
199 (
200 itemtype => p_item_type
201 , itemkey => p_item_key
202 , owner => 'OPERATIONS'
203 );
204
205 wf_engine.setitemattrtext
206 (
207 itemtype => p_item_type
208 , itemkey => p_item_key
209 , aname => 'FROM_USER_NAME'
210 , avalue => p_from_user_name
211 );
212
213 wf_engine.setitemattrnumber
214 (
215 itemtype => p_item_type
216 , itemkey => p_item_key
217 , aname => 'RESERVATION_ID'
218 , avalue => p_reservation_id
219 );
220
221 wf_engine.setitemattrnumber
222 (
223 itemtype => p_item_type
224 , itemkey => p_item_key
225 , aname => 'ORGANIZATION_ID'
226 , avalue => p_organization_id
227 );
228
229 wf_engine.setitemattrtext
230 (
231 itemtype => p_item_type
232 , itemkey => p_item_key
233 , aname => 'ORGANIZATION_CODE'
234 , avalue => p_organization_code
235 );
236
237 wf_engine.setitemattrnumber
238 (
239 itemtype => p_item_type
240 , itemkey => p_item_key
241 , aname => 'INVENTORY_ITEM_ID'
242 , avalue => p_inventory_item_id
243 );
244
245 wf_engine.setitemattrtext
246 (
247 itemtype => p_item_type
248 , itemkey => p_item_key
249 , aname => 'ITEM_NUMBER'
250 , avalue => p_inventory_item_number
251 );
252
253 wf_engine.setitemattrtext
254 (
255 itemtype => p_item_type
256 , itemkey => p_item_key
257 , aname => 'REVISION'
258 , avalue => p_revision
259 );
260
261 wf_engine.setitemattrtext
262 (
263 itemtype => p_item_type
264 , itemkey => p_item_key
265 , aname => 'LOT_NUMBER'
266 , avalue => p_lot_number
267 );
268
269 wf_engine.setitemattrtext
270 (
271 itemtype => p_item_type
272 , itemkey => p_item_key
273 , aname => 'SUBINVENTORY_CODE'
274 , avalue => p_subinventory_code
275 );
276
277 wf_engine.setitemattrnumber
278 (
279 itemtype => p_item_type
280 , itemkey => p_item_key
281 , aname => 'LOCATOR_ID'
282 , avalue => p_locator_id
283 );
284
285 wf_engine.setitemattrtext
286 (
287 itemtype => p_item_type
288 , itemkey => p_item_key
289 , aname => 'LOCATOR'
290 , avalue => p_locator
291 );
292
293 wf_engine.setitemattrtext
294 (
295 itemtype => p_item_type
296 , itemkey => p_item_key
297 , aname => 'DEMAND_SOURCE_TYPE'
298 , avalue => To_char(p_demand_source_type_id)
299 );
300
301 wf_engine.setitemattrnumber
302 (
303 itemtype => p_item_type
304 , itemkey => p_item_key
305 , aname => 'DEMAND_SOURCE_HEADER_ID'
306 , avalue => p_demand_source_header_id
307 );
308
309 wf_engine.setitemattrnumber
310 (
311 itemtype => p_item_type
312 , itemkey => p_item_key
313 , aname => 'DEMAND_SOURCE_LINE_ID'
314 , avalue => p_demand_source_line_id
315 );
316
317 wf_engine.setitemattrtext
318 (
319 itemtype => p_item_type
320 , itemkey => p_item_key
321 , aname => 'DEMAND_SOURCE_NAME'
322 , avalue => p_demand_source_name
323 );
324
325 wf_engine.setitemattrtext
326 (
327 itemtype => p_item_type
328 , itemkey => p_item_key
329 , aname => 'SUPPLY_SOURCE_TYPE'
330 , avalue => To_char(p_supply_source_type_id)
331 );
332
333 wf_engine.setitemattrnumber
334 (
335 itemtype => p_item_type
336 , itemkey => p_item_key
337 , aname => 'SUPPLY_SOURCE_HEADER_ID'
338 , avalue => p_supply_source_header_id
339 );
340
341 wf_engine.setitemattrnumber
342 (
343 itemtype => p_item_type
344 , itemkey => p_item_key
345 , aname => 'SUPPLY_SOURCE_LINE_ID'
346 , avalue => p_supply_source_line_id
347 );
348
349 wf_engine.setitemattrnumber
350 (
351 itemtype => p_item_type
352 , itemkey => p_item_key
353 , aname => 'SUPPLY_SOURCE_LINE_DETAIL'
354 , avalue => p_supply_source_line_detail
355 );
356
357 wf_engine.setitemattrtext
358 (
359 itemtype => p_item_type
360 , itemkey => p_item_key
361 , aname => 'SUPPLY_SOURCE_NAME'
362 , avalue => p_supply_source_name
363 );
364
365 wf_engine.setitemattrtext
366 (
367 itemtype => p_item_type
368 , itemkey => p_item_key
369 , aname => 'PRIMARY_UOM_CODE'
370 , avalue => p_primary_uom_code
371 );
372
373 wf_engine.setitemattrnumber
374 (
375 itemtype => p_item_type
376 , itemkey => p_item_key
377 , aname => 'PRIMARY_RESERVATION_QUANTITY'
378 , avalue => p_primary_reservation_quantity
379 );
380
381 wf_engine.setitemattrtext
382 (
383 itemtype => p_item_type
384 , itemkey => p_item_key
385 , aname => 'FROM_USER_NAME'
386 , avalue => p_from_user_name
387 );
388
389 wf_engine.setitemattrtext
390 (
391 itemtype => p_item_type
392 , itemkey => p_item_key
393 , aname => 'TO_NOTIFY_ROLE'
394 , avalue => p_to_notify_role
395 );
396
397 wf_engine.startprocess
398 (
399 itemtype => p_item_type
400 , itemkey => p_item_key
401 );
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 wf_core.context(
406 'INV_RESERVATION_WORKFLOW'
407 , 'HANDLE_BROKEN_RESERVATION'
408 , p_item_type
409 , p_item_key
410 );
411 RAISE;
412
413 END handle_broken_reservation;
414
415 -- Procedure
416 -- handle_broken_reservation
417 -- Description
418 -- Start the work flow process to handle broken reservation
419 -- Output Parameters
420 -- x_return_status 'T' if succeeded, 'F' if failed
421 PROCEDURE handle_broken_reservation
422 (
423 p_item_type IN VARCHAR2 DEFAULT 'INVRSVWF'
424 , p_item_key IN VARCHAR2
425 , p_reservation_id IN NUMBER
426 , p_from_user_name IN VARCHAR2
427 , p_to_notify_role IN VARCHAR2
428 , x_return_status OUT NOCOPY VARCHAR2
429 ) IS
430 l_organization_code VARCHAR2(3);
431 l_organization_id NUMBER;
432 l_inventory_item_id NUMBER;
433 l_inventory_item_number VARCHAR2(240);
434 l_revision VARCHAR2(3);
435 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
436 l_lot_number VARCHAR2(80);
437 l_subinventory_code VARCHAR2(10);
438 l_locator_id NUMBER;
439 l_locator VARCHAR2(240);
440 l_demand_source_type_id NUMBER;
441 l_demand_source_type VARCHAR2(30);
442 l_demand_source_header_id NUMBER;
443 l_demand_source_line_id NUMBER;
444 l_demand_source_name VARCHAR2(30);
445 l_supply_source_type_id NUMBER;
446 l_supply_source_type VARCHAR2(30);
447 l_supply_source_header_id NUMBER;
448 l_supply_source_line_id NUMBER;
449 l_supply_source_name VARCHAR2(30);
450 l_supply_source_line_detail NUMBER;
451 l_primary_uom_code VARCHAR2(3);
452 l_primary_reservation_quantity NUMBER;
453 l_requestor_user_name VARCHAR2(100);
454 l_user_to_notify VARCHAR2(100);
455 BEGIN
456 IF p_reservation_id IS NULL THEN
457 RAISE fnd_api.g_exc_error;
461 organization_id
458 END IF;
459
460 SELECT
462 , inventory_item_id
463 , revision
464 , lot_number
465 , subinventory_code
466 , locator_id
467 , demand_source_type_id
468 , demand_source_header_id
469 , demand_source_line_id
470 , demand_source_name
471 , supply_source_type_id
472 , supply_source_header_id
473 , supply_source_line_id
474 , supply_source_name
475 , supply_source_line_detail
476 , primary_uom_code
477 , primary_reservation_quantity
478 INTO
479 l_organization_id
480 , l_inventory_item_id
481 , l_revision
482 , l_lot_number
483 , l_subinventory_code
484 , l_locator_id
485 , l_demand_source_type_id
486 , l_demand_source_header_id
487 , l_demand_source_line_id
488 , l_demand_source_name
489 , l_supply_source_type_id
490 , l_supply_source_header_id
491 , l_supply_source_line_id
492 , l_supply_source_name
493 , l_supply_source_line_detail
494 , l_primary_uom_code
495 , l_primary_reservation_quantity
496 FROM mtl_reservations
497 WHERE reservation_id = p_reservation_id;
498
499 SELECT organization_code
500 INTO l_organization_code
501 FROM mtl_parameters
502 WHERE organization_id = l_organization_id;
503
504 -- find out item number here
505 get_item_number(l_organization_id
506 , l_inventory_item_id
507 , l_inventory_item_number);
508 IF l_inventory_item_number IS NULL THEN
509 RAISE fnd_api.g_exc_error;
510 END IF;
511
512 -- find out locator
513 get_locator(l_organization_id
514 , l_locator_id
515 , l_locator);
516 IF l_locator IS NULL THEN
517 RAISE fnd_api.g_exc_error;
518 END IF;
519
520 SELECT transaction_source_type_name INTO l_demand_source_type
521 FROM mtl_txn_source_types
522 WHERE transaction_source_type_id = l_demand_source_type_id;
523
524 SELECT transaction_source_type_name INTO l_supply_source_type
525 FROM mtl_txn_source_types
526 WHERE transaction_source_type_id = l_supply_source_type_id;
527
528 handle_broken_reservation
529 (
530 p_item_type => 'INVRSVWF'
531 , p_item_key => p_item_key
532 , p_reservation_id => p_reservation_id
533 , p_organization_id => l_organization_id
534 , p_organization_code => l_organization_code
535 , p_inventory_item_id => l_inventory_item_id
536 , p_inventory_item_number => l_inventory_item_number
537 , p_revision => l_revision
538 , p_lot_number => l_lot_number
539 , p_subinventory_code => l_subinventory_code
540 , p_locator_id => l_locator_id
541 , p_locator => l_locator
542 , p_demand_source_type_id => l_demand_source_type_id
543 , p_demand_source_type => l_demand_source_type
544 , p_demand_source_header_id => l_demand_source_header_id
545 , p_demand_source_line_id => l_demand_source_line_id
546 , p_demand_source_name => l_demand_source_name
547 , p_supply_source_type_id => l_supply_source_type_id
548 , p_supply_source_type => l_supply_source_type
549 , p_supply_source_header_id => l_supply_source_header_id
550 , p_supply_source_line_id => l_supply_source_line_id
551 , p_supply_source_name => l_supply_source_name
552 , p_supply_source_line_detail => l_supply_source_line_detail
553 , p_primary_uom_code => l_primary_uom_code
554 , p_primary_reservation_quantity => l_primary_reservation_quantity
555 , p_from_user_name => p_from_user_name
556 , p_to_notify_role => p_to_notify_role
557 );
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 wf_core.context(
562 'INV_RESERVATION_WORKFLOW'
563 , 'HANDLE_BROKEN_RESERVATION'
564 , p_item_type
565 , p_item_key
566 );
567 RAISE;
568 END handle_broken_reservation;
569
570 PROCEDURE selector
571 ( itemtype IN VARCHAR2,
572 itemkey IN VARCHAR2,
573 actid IN NUMBER,
574 command IN VARCHAR2,
575 result OUT NOCOPY VARCHAR2
576 ) IS
577 BEGIN
578 If ( command = 'RUN' ) then
579 result := 'HANDLE_BROKEN_RESERVATION';
580 return;
581 end if;
582
583 EXCEPTION
584 WHEN OTHERS THEN
585 wf_core.context(
586 'INV_RESERVATION_WORKFLOW'
587 , 'SELECTOR'
588 , itemtype
589 , itemkey
590 , To_char(actid)
591 , command
592 );
593 RAISE fnd_api.g_exc_error;
594 END selector;
595
596 END inv_reservation_workflow;