[Home] [Help]
PACKAGE BODY: APPS.GMD_QM_UOM
Source
1 PACKAGE BODY GMD_QM_UOM AS
2 /* $Header: GMDQMUMB.pls 120.2 2005/10/07 11:57:48 jdiiorio noship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7
8 PROCEDURE VERIFY_EVENT(
9 /* procedure to verify event and send out notifications*/
10 p_itemtype IN VARCHAR2,
11 p_itemkey IN VARCHAR2,
12 p_actid IN NUMBER,
13 p_funcmode IN VARCHAR2,
14 p_resultout OUT NOCOPY VARCHAR2)
15
16 IS
17 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
18 itemtype=>p_itemtype,
19 itemkey=>P_itemkey,
20 aname=>'EVENT_NAME');
21 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
22 itemtype=>p_itemtype,
23 itemkey=>P_itemkey,
24 aname=>'EVENT_KEY');
25
26 l_current_approver varchar2(240);
27
28 l_application_id number;
29 l_transaction_type varchar2(100):='GMDQMUM';
30 l_user varchar2(32);
31 Approver ame_util.approverRecord;
32
33
34 l_form varchar2(240);
35 l_itemtype varchar2(240);
36 l_itemkey varchar2(240);
37 l_workflow_process varchar2(240);
38 l_log varchar2(4000);
39 I NUMBER;
40 l_item_no varchar2(240);
41 l_item_desc varchar2(240);
42 l_lot_no varchar2(240);
43 l_item_id number ;
44 l_orgn_code varchar2(240);
45 l_disposition varchar2(240);
46 l_source varchar2(240);
47 l_samples_taken number ;
48 l_receipt_id number;
49 l_receipt_line_id number;
50 l_po_id number;
51 l_po_line_id number;
52 l_supplier_id number;
53 l_supplier_lot varchar2(240);
54 l_test_id number ;
55 l_spec_id number ;
56 l_from_uom varchar2(240);
57 l_to_uom varchar2(240);
58 l_from_uom_base varchar2(240);
59 l_to_uom_base varchar2(240);
60 l_current_conv number ;
61 l_propose_conv number ;
62 l_prop_conv_base number ;
63 l_prop_conv_base_recip number ;
64 l_test_name varchar2(240);
65 l_spec_name varchar2(240);
66 l_spec_vers varchar2(240);
67 l_test_desc varchar2(240);
68 l_supplier varchar2(240);
69 l_po_num varchar2(240);
70 l_po_line_num varchar2(240);
71 l_receipt_num varchar2(240);
72 l_receipt_line_num varchar2(240);
73 l_created_by varchar2(240) := -1;
74 l_mode number;
75 l_lot_ctl number;
76 l_owner number;
77 l_uom_type varchar2(240);
78 l_item_revision varchar2(240);
79 l_subinventory varchar2(240);
80 l_locator varchar2(240);
81 l_parent_lot_no varchar2(240);
82 l_orgn_id number;
83
84
85 /* These cursors Will Pick up all the info for a UOM conversion */
86
87 Cursor C1 is
88
89 SELECT H.INVENTORY_ITEM_ID,
90 K.concatenated_segments, -- Item Number
91 H.parent_lot_number,
92 K.description,
93 H.lot_number,
94 I.organization_code,
95 H.organization_id,
96 H.revision,
97 H.subinventory,
98 MIL.concatenated_segments , -- Locator
99 H.disposition,
100 h.source,
101 h.sample_taken_cnt,
102 h.receipt_id,
103 H.RECEIPT_LINE_ID,
104 H.PO_HEADER_ID ,
105 H.PO_LINE_ID ,
106 H.SUPPLIER_ID ,
107 H.SUPPLIER_LOT_NO ,
108 E.test_id ,
109 F.spec_id ,
110 E.from_qty_uom,
111 E.to_qty_uom,
112 E.CURRENT_CONVERSION ,
113 E.PROPOSED_CONVERSION ,
114 E.from_qty_uom_base ,
115 E.to_qty_uom_base ,
116 E.PROPOSED_CONVERSION_BASE,
117 E.result_id
118 from GMD_UOM_CONVERSIONS E,
119 gmd_event_spec_disp F,
120 gmd_sampling_events H,
121 mtl_parameters I,
122 mtl_item_locations_kfv MIL,
123 mtl_system_items_kfv K
124 where e.EVENT_SPEC_DISP_ID = l_event_key and
125 e.EVENT_SPEC_DISP_ID = f.event_spec_disp_id and
126 f.sampling_event_id = h.SAMPLING_EVENT_ID and
127 I.organization_id = H.organization_id and
128 MIL.organization_id(+) = H.organization_id and
129 MIL.inventory_location_id(+) = H.locator_id and
130 K.organization_id = H.organization_id and
131 K.inventory_item_id = H.inventory_item_id and
132 e.recommended_ind = 'Y';
133
134 l_result_id NUMBER;
135
136 Cursor C2 (spec_id_in number) is
137 select spec_name , spec_vers, owner_id
138 from gmd_specifications
139 where spec_id = spec_id_in ;
140
141 Cursor C3 (test_id_in number) is
142 select test_desc
143 from gmd_qc_tests
144 where test_id = test_id_in;
145
146 Cursor C4 (vendor_id_in number) is
147 select vendor_name
148 from po_vendors
149 where vendor_id = vendor_id_in ;
150
151 Cursor C5 (po_header_in number) is
152 select segment1
153 from po_headers_all
154 where po_header_id = po_header_in ;
155
156 Cursor C6 (po_line_in number) is
157 select line_num
158 from po_lines_all
159 where po_line_id = po_line_in ;
160
161 Cursor C7 (receipt_in number) is
162 select receipt_num
163 from rcv_shipment_headers
164 where shipment_header_id = receipt_in ;
165
166 Cursor C8 (receipt_line_in number) is
167 select line_num
168 from rcv_shipment_lines
169 where shipment_line_id = receipt_line_in ;
170 -- SCHANDRU INVCONV START
171
172
173 Cursor C9 (lot_number_in varchar2, org_id_in number, item_id_in number,
174 from_uom_in varchar2, to_uom_in varchar2) is
175 select conversion_id
176 from MTL_LOT_UOM_CLASS_CONVERSIONS
177 where inventory_item_id = item_id_in
178 and organization_id = org_id_in
179 and lot_number = lot_number_in
180 and from_uom_code = from_uom_in
181 and to_uom_code = to_uom_in;
182
183 l_conversion_id mtl_lot_uom_class_conversions.conversion_id%TYPE;
184 -- JD changed query above to get exact conversion.
185
186 -- SCHANDRU INVCONV END
187
188 Cursor C10 (disp_in varchar2) is
189 SELECT meaning
190 FROM fnd_lookup_values
191 WHERE lookup_TYPE = 'GMD_QC_SAMPLE_DISP'
192 AND language = USERENV('LANG')
193 AND lookup_code = disp_in ;
194
195
196 Cursor C11 (item_source_in varchar2) is
197 SELECT description
198 FROM fnd_lookup_values
199 WHERE lookup_TYPE like 'GMD_QC_SOURCE'
200 AND language = USERENV('LANG')
201 AND lookup_code = item_source_in ;
202
203 Cursor C12 (mon_source_in varchar2) is
204 SELECT description
205 FROM fnd_lookup_values
206 WHERE lookup_TYPE like 'GMD_QC_MONITOR_RULE_TYPE'
207 AND language = USERENV('LANG')
208 AND lookup_code = mon_source_in ;
209 --SCHANDRU INVCONV START
210 Cursor control (item_no_in number) is
211 select LOT_CONTROL_CODE
212 from mtl_system_items_kfv
213 where inventory_item_id = item_no_in;
214
215 --SCHANDRU INVCONV END
216 cursor get_child_lots (l_item_id_in number, l_organization_id_in number,l_parent_lot_number_in varchar2) is
217 select lot_number
218 from mtl_lot_numbers
219 where parent_lot_number = l_parent_lot_number_in and
220 organization_id = l_organization_id_in and
221 inventory_item_id = l_item_id_in ;
222
223
224 cursor get_owner_name (owner_id_in number) is
225 select user_name
226 from fnd_user
227 where user_id = owner_id_in;
228
229 cursor get_uom_type (uom_code_in varchar2) is
230 select UOM_CLASS from mtl_units_of_measure -- SCHANDRU INVCONV
231 where uom_code = uom_code_in ;
232
233
234 cursor get_from_role is
235 select nvl( text, '')
236 from wf_Resources where name = 'WF_ADMIN_ROLE'
237 and language = userenv('LANG') ;
238
239 l_from_role varchar2(240);
240 l_conversion varchar2(2000);
241
242
243 BEGIN
244
245
246 IF (l_debug = 'Y') THEN
247 gmd_debug.log_initialize('UomConv');
248 END IF;
249
250 IF (l_debug = 'Y') THEN
251 gmd_debug.put_line('Event Name ' || l_event_name);
252 gmd_debug.put_line('Event Key ' || l_event_key);
253 END IF;
254
255 open get_from_role ;
256 fetch get_from_role into l_from_role ;
257 close get_from_role ;
258
259
260 IF P_FUNCMODE='RUN' THEN
261 /* Get application_id from FND_APPLICATION */
262 select application_id into l_application_id
263 from fnd_application where application_short_name='GMD';
264
265 /* Check which event has been raised */
266 wf_log_pkg.string(6, 'Dummy','Entered UOM Conversion with event_key '||l_event_key);
267
268 /* Check each UOM conversion which is recommended to send a notification */
269 OPEN C1;
270 LOOP
271 wf_log_pkg.string(6, 'Dummy','Before Fetching the values. Inside the Loop');
272
273
274 IF (l_debug = 'Y') THEN
275 gmd_debug.put_line('Getting data from cursors ');
276 END IF;
277
278
279 Fetch C1 into l_item_id,
280 --l_lot_id,
281 l_item_no,
282 l_parent_lot_no,
283 l_item_desc,
284 l_lot_no,
285 --l_sublot_no,
286 l_orgn_code,
287 l_orgn_id,
288 l_item_revision,
289 l_subinventory,--l_whse_code,
290 l_locator, --l_location,
291 l_disposition,
292 l_source,
293 l_samples_taken,
294 l_receipt_id,
295 l_receipt_line_id,
296 l_po_id ,
297 l_po_line_id ,
298 l_supplier_id,
299 l_supplier_lot,
300 l_test_id,
301 l_spec_id,
302 l_from_uom,
303 l_to_uom,
304 l_current_conv,
305 l_propose_conv,
306 l_from_uom_base,
307 l_to_uom_base,
308 l_prop_conv_base,
309 l_result_id;
310 EXIT when C1%notfound;
311
312
313 open C2 (l_spec_id);
314 fetch C2 into l_spec_name , l_spec_vers, l_owner;
315 close C2;
316
317 open C3(l_test_id);
318 fetch C3 into l_test_name;
319 close C3;
320
321 open C4(l_supplier_id);
322 fetch C4 into l_supplier;
323 close C4;
324
325 open C5(l_po_id);
326 fetch C5 into l_po_num;
327 close C5;
328
329 open C6(l_po_line_id);
330 fetch C6 into l_po_line_num;
331 close C6;
332
333 open C7(l_receipt_id);
334 fetch C7 into l_receipt_num;
335 close C7;
336
337 open C8(l_receipt_line_id);
338 fetch C8 into l_receipt_line_num;
339 close C8;
340
341 open C10(l_disposition);
342 fetch C10 into l_disposition;
343 close C10;
344
345 open control (l_item_id);
346 fetch control into l_lot_ctl;
347 close control;
348
349 open get_owner_name (l_owner);
350 fetch get_owner_name into l_user;
351 close get_owner_name;
352
353
354 IF (l_debug = 'Y') THEN
355 gmd_debug.put_line('l_source ' || l_source);
356 END IF;
357
358 if (l_source = 'R') or (l_source = 'L') then
359 /* Resource source */
360 open C12(l_source);
361 fetch C12 into l_source;
362 close C12;
363 else
364 /* Item source */
365 open C11 (l_source);
366 fetch C11 into l_source;
367 close C11 ;
368 end if ;
369
370
371
372 open get_uom_type (l_to_uom);
373 fetch get_uom_type into l_uom_type ;
374 close get_uom_type;
375
376 --BUG#3676227
377 l_prop_conv_base_recip :=to_number(substr(to_char(1.0 / l_prop_conv_base),1,30));
378
379 IF (l_debug = 'Y') THEN
380 gmd_debug.put_line('checking approvers ');
381 END IF;
382
383 /* Start the Workflow for the Given Combination */
384 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
385 transactionIdIn => l_event_key,
386 transactionTypeIn => l_transaction_type);
387
388 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
389 ame_api.getNextApprover(applicationIdIn => l_application_id,
390 transactionIdIn => l_event_key,
391 transactionTypeIn => l_transaction_type,
392 nextApproverOut => Approver);
393
394 if(Approver.user_id is null and Approver.person_id is null) then
395 /* No Approval Required so default to owner*/
396 null ;
397 elsif(Approver.person_id is null) then
398 select user_name into l_user from fnd_user
399 where user_id=Approver.user_id;
400 else
401 select user_name into l_user from fnd_user
402 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
403 end if;
404
405 IF (l_debug = 'Y') THEN
406 gmd_debug.put_line('User Name ' || l_user);
407 END IF;
408
409 /* In the case there is a lot_id, we should send a notification. in the
410 case there is a lot_no with not sublot_no but item is not sublot
411 controlled then also send a notification. In the case there is a lot_no
412 specified but no sublot_no but the item is sublot controlled then need
413 to loop through each of the sublots and send a different notification */
414 if (l_lot_no is not null ) then
415
416 IF (l_debug = 'Y') THEN
417 gmd_debug.put_line('Case where lot defined and not sublot ctrl ');
418 END IF;
419
420 /* Need to check if UOM conversion already exists */
421 l_conversion_id := NULL;
422 open C9 (l_lot_no, l_orgn_id, l_item_id, l_from_uom, l_to_uom);
423 fetch C9 into l_conversion_id;
424 if (l_conversion_id IS NOT NULL) THEN
425 l_mode := 1;
426 else
427 l_mode := 0;
428 end if;
429 close C9 ;
430
431 /* Set Form Attribute to the sampling event */
432 -- BUG#3315141 Sastry
433 -- Passed l_prop_conv_base_recip for type_factor
434 -- JD changed to converged form function
435
436
437
438
439
440 -- JD changed parm list.
441
442 l_form := 'INVSDLUC_F: CONV_MODE="'||l_mode||'"'||' CONV_ID="'||l_conversion_id||'"'||' RID="'||l_result_id||'"'||' C_RATE="'||l_prop_conv_base_recip||'"'||' EVT_DISPID="'||l_event_key||'"';
443
444
445 l_itemtype:='GMDQMUOM';
446 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
447
448 l_workflow_process:='GMDQMUOM_SUB_PROCESS';
449
450 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
451 itemkey => l_itemkey,
452 process => l_workflow_process );
453
454 /* Set the User Attribute */
455
456 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
457 aname => '#FROM_ROLE',
458 avalue => l_user );
459
460 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
461 aname => 'CURRENT_APPROVER',
462 avalue => l_user);
463 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
464 aname => 'APPS_FORM',
465 avalue =>l_form );
466 /* Set All other Attributes */
467 -- SCHANDRU INVCONV START
468
469 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
470 aname => 'ITEM_REVISION',
471 avalue =>l_item_revision );
472 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => l_itemkey,
473 aname => 'ITEM_REVISION',
474 avalue =>l_item_revision );
475 -- SCHANDRU INVCONV END
476
477 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
478 aname => 'EVENT_KEY',
479 avalue =>l_event_key );
480 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
481 aname => 'EVENT_NAME',
482 avalue =>l_event_name );
483
484 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
485 aname => 'SOURCE',
486 avalue =>l_source );
487 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
488 aname => 'SAMPLES_TAKEN',
489 avalue =>l_samples_taken );
490 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
491 aname => 'SAMPLE_GRP_DISP',
492 avalue =>l_disposition );
493 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
494 aname => 'ITEM_NO',
495 avalue =>l_item_no );
496 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
497 aname => 'LOT_NO',
498 avalue =>l_lot_no );
499
500 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
501 aname => 'ORGANIZATION',
502 avalue =>l_orgn_code );
503 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
504 aname => 'SUBINVENTORY',
505 avalue =>l_subinventory );
506 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
507 aname => 'LOCATOR',
508 avalue =>l_locator );
509 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
510 aname => 'SUPPLIER',
511 avalue =>l_supplier );
512 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
513 aname => 'SUPPLIER_LOT',
514 avalue =>l_supplier_lot );
515 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
516 aname => 'PO_NUMBER',
517 avalue =>l_po_num );
518 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
519 aname => 'PO_LINE_NO',
520 avalue =>l_po_line_num );
521 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
522 aname => 'RECEIPT_NO',
523 avalue =>l_receipt_num );
524 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
525 aname => 'RECEIPT_LINE_NO',
526 avalue =>l_receipt_line_num );
527 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
528 aname => 'SPEC',
529 avalue =>l_spec_name );
530 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
531 aname => 'SPEC_VERSION',
532 avalue =>l_spec_vers );
533 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
534 aname => 'TEST_NAME',
535 avalue =>l_test_name );
536 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
537 aname => 'FROM_UOM',
538 avalue =>l_from_uom );
539 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
540 aname => 'PROPOSED_CONVERSION',
541 avalue =>l_propose_conv );
542 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
543 aname => 'TO_UOM',
544 avalue =>l_TO_UOM );
545
546 l_conversion := '1 '|| l_from_uom || ' = ' || l_propose_conv || ' ' || l_TO_UOM ;
547 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
548 itemkey => l_itemkey,
549 aname => 'CONVERSION',
550 avalue => l_conversion );
551
552
553 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
554 aname => 'AME_TRANS',
555 avalue =>l_transaction_type);
556
557
558 wf_log_pkg.string(6, 'Dummy','Setting Parent');
559
560
561 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
562 parent_itemtype => p_itemtype,
563 parent_itemkey=> p_itemkey,
564 parent_context=> NULL);
565
566 /* start the Workflow process */
567 wf_log_pkg.string(6, 'Dummy','Starting Process');
568
569 /* As this a pure FYI notification we will set the approver to approve status */
570 Approver.approval_status := ame_util.approvedStatus;
571 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
572 transactionIdIn => l_event_key,
573 approverIn => Approver,
574 transactionTypeIn => l_transaction_type,
575 forwardeeIn => ame_util.emptyApproverRecord);
576
577
578 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
579
580
581 elsif(l_parent_lot_no is not null) then
582
583 IF (l_debug = 'Y') THEN
584 gmd_debug.put_line('Case where lot not defined ');
585 END IF;
586 open get_child_lots (l_item_id, l_orgn_id,l_parent_lot_no);
587
588 loop
589 fetch get_child_lots into l_lot_no;
590 EXIT when get_child_lots%notfound;
591
592 /* Need to check if UOM conversion already exists */
593 l_conversion_id := NULL;
594 open C9 (l_lot_no, l_orgn_id, l_item_id, l_from_uom, l_to_uom);
595 fetch C9 into l_conversion_id;
596 if (l_conversion_id IS NOT NULL) then
597 l_mode := 1;
598 else
599 l_mode := 0;
600 end if;
601 close C9 ;
602
603 /* Set Form Attribute to the sampling event */
604 -- BUG#3315141 Sastry
605 -- Passed l_prop_conv_base_recip for type_factor
606
607
608
609 l_form := 'INVSDLUC_F: CONV_MODE="'||l_mode||'"'||' CONV_ID="'||l_conversion_id||'"'||' RID="'||l_result_id||'"'||' C_RATE="'||l_prop_conv_base_recip||'"'||' EVT_DISPID="'||l_event_key||'"';
610
611
612
613 l_itemtype:='GMDQMUOM';
614 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
615
616 l_workflow_process:='GMDQMUOM_SUB_PROCESS';
617
618 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
619 itemkey => l_itemkey,
620 process => l_workflow_process );
621
622 /* Set the User Attribute */
623
624 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
625 aname => 'CURRENT_APPROVER',
626 avalue => l_user);
627 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
628 aname => 'APPS_FORM',
629 avalue =>l_form );
630 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
631 aname => '#FROM_ROLE',
632 avalue => l_user );
633
634
635 /* Set All other Attributes */
636 -- SCHANDRU INVCONV START
637 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
638 aname => 'ITEM_REVISION',
639 avalue =>l_item_revision );
640 -- SCHANDRU INVCONV END
641 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
642 aname => 'EVENT_KEY',
643 avalue =>l_event_key );
644 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
645 aname => 'EVENT_NAME',
646 avalue =>l_event_name );
647
648 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
649 aname => 'SOURCE',
650 avalue =>l_source );
651 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
652 aname => 'SAMPLES_TAKEN',
653 avalue =>l_samples_taken );
654 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
655 aname => 'SAMPLE_GRP_DISP',
656 avalue =>l_disposition );
657 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
658 aname => 'ITEM_NO',
659 avalue =>l_item_no );
660 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
661 aname => 'LOT_NO',
662 avalue =>l_lot_no );
663
664 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
665 aname => 'ORGANIZATION',
666 avalue =>l_orgn_code );
667 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
668 aname => 'SUBINVENTORY',
669 avalue =>l_subinventory );
670 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
671 aname => 'LOCATOR',
672 avalue =>l_locator );
673 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
674 aname => 'SUPPLIER',
675 avalue =>l_supplier );
676 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
677 aname => 'SUPPLIER_LOT',
678 avalue =>l_supplier_lot );
679 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
680 aname => 'PO_NUMBER',
681 avalue =>l_po_num );
682 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
683 aname => 'PO_LINE_NO',
684 avalue =>l_po_line_num );
685 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
686 aname => 'RECEIPT_NO',
687 avalue =>l_receipt_num );
688 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
689 aname => 'RECEIPT_LINE_NO',
690 avalue =>l_receipt_line_num );
691 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
692 aname => 'SPEC',
693 avalue =>l_spec_name );
694 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
695 aname => 'SPEC_VERSION',
696 avalue =>l_spec_vers );
697 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
698 aname => 'TEST_NAME',
699 avalue =>l_test_name );
700 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
701 aname => 'FROM_UOM',
702 avalue =>l_from_uom );
703 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
704 aname => 'PROPOSED_CONVERSION',
705 avalue =>l_propose_conv );
706 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
707 aname => 'TO_UOM',
708 avalue =>l_TO_UOM );
709 l_conversion := '1 '|| l_from_uom || ' = ' || l_propose_conv
710 || ' ' || l_TO_UOM ;
711 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
712 itemkey => l_itemkey,
713 aname => 'CONVERSION',
714 avalue => l_conversion );
715
716 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
717 aname => 'AME_TRANS',
718 avalue =>l_transaction_type);
719
720
721 wf_log_pkg.string(6, 'Dummy','Setting Parent');
722
723
724 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
725 parent_itemtype => p_itemtype,
726 parent_itemkey=> p_itemkey,
727 parent_context=> NULL);
728
729 /* start the Workflow process */
730 wf_log_pkg.string(6, 'Dummy','Starting Process');
731
732 /* As this a pure FYI notification we will set the approver to approve status */
733 Approver.approval_status := ame_util.approvedStatus;
734 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
735 transactionIdIn => l_event_key,
736 approverIn => Approver,
737 transactionTypeIn => l_transaction_type,
738 forwardeeIn => ame_util.emptyApproverRecord);
739
740
741 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
742
743
744 end loop ;
745 close get_child_lots;
746 end if;
747
748
749 END LOOP;
750 CLOSE C1;
751
752 END IF;
753 commit ;
754
755 p_resultout:='COMPLETE:';
756
757 EXCEPTION
758
759 WHEN OTHERS THEN
760 WF_CORE.CONTEXT ('GMD_QMU_OM','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
761 raise;
762
763 END VERIFY_EVENT;
764
765
766
767 PROCEDURE CHECK_NEXT_APPROVER(
768 p_itemtype IN VARCHAR2,
769 p_itemkey IN VARCHAR2,
770 p_actid IN NUMBER,
771 p_funcmode IN VARCHAR2,
772 p_resultout OUT NOCOPY VARCHAR2)
773
774 IS
775 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
776 itemtype=>p_itemtype,
777 itemkey=>P_itemkey,
778 aname=>'EVENT_NAME');
779 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
780 itemtype=>p_itemtype,
781 itemkey=>P_itemkey,
782 aname=>'EVENT_KEY');
783
784 l_current_approver varchar2(240);
785
786 l_application_id number;
787 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
788 itemtype=>p_itemtype,
789 itemkey=>P_itemkey,
790 aname=>'AME_TRANS');
791 l_user varchar2(32);
792 Approver ame_util.approverRecord;
793 l_form varchar2(240);
794 BEGIN
795
796 /* Get Next Approver */
797 /* Get application_id from FND_APPLICATION */
798 select application_id into l_application_id
799 from fnd_application where application_short_name='GMD';
800
801 ame_api.getNextApprover(applicationIdIn => l_application_id,
802 transactionIdIn => l_event_key,
803 transactionTypeIn => l_transaction_type,
804 nextApproverOut => Approver);
805
806
807 if(Approver.user_id is null and Approver.person_id is null) then
808 /* No Approval Required */
809 P_resultout:='COMPLETE:N';
810 else
811 if(Approver.person_id is null) then
812 select user_name into l_user from fnd_user
813 where user_id=Approver.user_id;
814 else
815 select user_name into l_user from fnd_user
816 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
817 end if;
818
819 /* Set the User Attribute */
820
821 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
822 aname => 'CURRENT_APPROVER',
823 avalue => l_user);
824
825 P_resultout:='COMPLETE:Y';
826 Approver.approval_status := ame_util.approvedStatus;
827 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
828 transactionIdIn => l_event_key,
829 approverIn => Approver,
830 transactionTypeIn => l_transaction_type,
831 forwardeeIn => ame_util.emptyApproverRecord);
832 end if;
833 EXCEPTION
834 WHEN OTHERS THEN
835 WF_CORE.CONTEXT ('GMD_QMUOM','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
836 raise;
837
838 END CHECK_NEXT_APPROVER;
839
840
841 END;