[Home] [Help]
PACKAGE BODY: APPS.GMD_QMSMC
Source
1 PACKAGE BODY GMD_QMSMC AS
2 /* $Header: GMDQMSMB.pls 120.22.12010000.2 2008/11/07 18:37:11 rnalla ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE VERIFY_EVENT(
7 /* procedure to verify event if the event is sample disposition or sample event disposition */
8 p_itemtype IN VARCHAR2,
9 p_itemkey IN VARCHAR2,
10 p_actid IN NUMBER,
11 p_funcmode IN VARCHAR2,
12 p_resultout OUT NOCOPY VARCHAR2)
13 IS
14
15 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
16 itemtype=>p_itemtype,
17 itemkey=>P_itemkey,
18 aname=>'EVENT_NAME');
19 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
20 itemtype=>p_itemtype,
21 itemkey=>P_itemkey,
22 aname=>'EVENT_KEY');
23
24 l_current_approver varchar2(240);
25
26 l_application_id number;
27 l_transaction_type varchar2(100);
28 l_user varchar2(32);
29 Approver AME_UTIL.APPROVERRECORD;
30 l_inventory_item_id number;
31 l_item_number varchar2(240);
32 l_item_desc varchar2(240);
33 l_lot_number varchar2(240);
34 l_parent_lot_number varchar2(240);
35 l_sample_plan varchar2(4000) := '';
36 l_sample_plan_out varchar2(4000) ;
37 l_sample_plan_id number;
38 l_sample_count varchar2(240);
39 l_sample_qty varchar2(240) ;
40 l_sample_qty_uom varchar2(240) ;
41 l_frequency_count varchar2(240);
42 l_frequency_per varchar2(240);
43 l_frequency_type varchar2(240);
44 l_sampling_event_id number;
45 l_batch_no varchar2(100);
46 l_formula_no varchar2(240);
47 l_recipe_no varchar2(240);
48 l_organization_code varchar2(240):=NULL;
49 l_subinventory varchar2(240):=NULL;
50 l_doc_type varchar2(240);
51 l_transaction_type_id number(10);
52 l_transaction_type_name varchar2(240);
53 l_trans_qty number;
54 l_trans_qty_uom varchar2(32);
55 l_trans_qty2 number;
56 l_trans_qty_uom2 varchar2(32);
57 l_receipt_no varchar2(240);
58 l_purchase_no varchar2(240);
59 l_vendor_no varchar2(240);
60 l_supplier_id varchar2(240);
61 l_vendor_lot_num VARCHAR2(32); --Bug#6145310
62 --RLNAGARA B4905645 start
63 l_retest_Date_tmp date;
64 l_expiry_date_tmp date;
65 l_retest_Date varchar2(30);
66 l_expiry_date varchar2(30);
67 l_date_format_mask varchar2(30);
68 --RLNAGARA B4905645 end
69 l_batch_step_no varchar2(240);
70 l_form varchar2(240);
71 l_log varchar2(2000);
72 l_valid_transaction boolean:=false;
73 l_vendor_spec_found boolean:=false;
74 l_inv_spec GMD_SPEC_MATCH_GRP.INVENTORY_SPEC_REC_TYPE;
75 l_cust_spec GMD_SPEC_MATCH_GRP.CUSTOMER_SPEC_REC_TYPE;
76 l_supp_spec GMD_SPEC_MATCH_GRP.SUPPLIER_SPEC_REC_TYPE;
77
78 L_SPEC_ID NUMBER ;
79 L_SPEC_VR_ID NUMBER ;
80 L_SPEC_TYPE VARCHAR2(100) ;
81 L_RETURN_STATUS VARCHAR2(100) ;
82 L_Msg_DATA VARCHAR2(2000) ;
83
84 l_sampling_events GMD_SAMPLING_EVENTS%ROWTYPE;
85 l_sampling_events_out GMD_SAMPLING_EVENTS%ROWTYPE;
86 l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
87 l_event_spec_disp_out GMD_EVENT_SPEC_DISP%ROWTYPE;
88 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
89
90 l_shipment_header_id number;
91 l_po_header_id number;
92 l_po_line_id number;
93 l_vendor_id number;
94 l_org_id number;
95 l_vendor_site_id number;
96 l_organization_id number;
97 l_operating_unit VARCHAR2(240); --RLNAGARA B5018797 Changed from Number to VARCHAR2
98 l_retest_indicator varchar2(1):=NULL;
99 l_locator varchar2(240):=NULL;
100 l_locator_id NUMBER;
101
102 l_receipt_id number;
103 l_receipt_line_id number;
104 l_qty_conv number ;
105
106 l_reserve_cnt_req varchar2(240):= '';
107 l_reserve_qty varchar2(240):= '';
108 l_archive_cnt_req varchar2(240):= '';
109 l_archive_qty varchar2(240) := '';
110 l_doc_number number;
111 l_auto_sample varchar2(1) := 'N';
112 l_sample_plan_exists number := 0;
113 create_status varchar2(240);
114
115 -- added for bug 4165704
116 l_revision VARCHAR2(3);
117 l_orgn_found BOOLEAN;
118
119 sample_name_temp varchar2(100) := '';
120 sample_name varchar2(100) := '';
121 rsample_name varchar2(500) := '';
122 asample_name varchar2(500) := '';
123 fsample_name varchar2(500) := '';
124 frsample_name varchar2(500) := '';
125 fasample_name varchar2(500) := '';
126 lsample_name varchar2(500) := '';
127 lrsample_name varchar2(500) := '';
128 lasample_name varchar2(500) := '';
129 r NUMBER; --RLNAGARA Bug 5910300
130 l_from_role varchar2(2000);
131
132 no_vendor_found EXCEPTION; /* Bug # 4576699 */
133 no_application_id_found EXCEPTION; /* Bug # 4576699 */
134 no_user_name_found EXCEPTION; /* Bug # 4576699 */
135
136 --RLNAGARA Bug5334308
137 l_trans_id NUMBER;
138 l_gen_obj_id NUMBER;
139 l_lot_qty NUMBER;
140 l_lot_qty2 NUMBER;
141
142 --RLNAGARA B5499961 Added Organization_Code to the select clause.
143 CURSOR inv IS
144 SELECT B.INVENTORY_ITEM_ID,
145 M.revision ,
146 B.concatenated_segments ,
147 B.DESCRIPTION,
148 M.TRANSACTION_TYPE_ID,
149 T.TRANSACTION_TYPE_NAME,
150 M.TRANSACTION_QUANTITY,
151 M.SECONDARY_TRANSACTION_QUANTITY,
152 M.TRANSACTION_UOM,
153 M.SECONDARY_UOM_CODE ,
154 M.ORGANIZATION_ID,
155 mp.organization_code,
156 M.SUBINVENTORY_code,
157 M.LOCATOR_ID
158 FROM MTL_SYSTEM_ITEMS_B_KFV B,
159 MTL_MATERIAL_TRANSACTIONS M,
160 MTL_TRANSACTION_TYPES T,
161 mtl_parameters mp
162 WHERE M.transaction_id = l_event_key
163 AND M.organization_id = B.organization_id
164 AND mp.organization_id = B.organization_id
165 AND M.inventory_item_id = B.inventory_item_id
166 AND M.transaction_type_id = T.transaction_type_id;
167
168 -- Bug 4440045: added the inv_lot cursor
169 CURSOR inv_lot IS
170 SELECT c.PARENT_LOT_NUMBER,
171 c.LOT_NUMBER
172 FROM MTL_LOT_NUMBERS C,
173 MTL_MATERIAL_TRANSACTIONS M,
174 MTL_TRANSACTION_LOT_NUMBERS L
175 WHERE M.transaction_id = l_event_key
176 AND M.inventory_item_id = C.inventory_item_id
177 AND M.organization_id = C.organization_id
178 AND C.LOT_NUMBER = L.LOT_NUMBER
179 AND M.transaction_id = L.transaction_id;
180 -- Bug #3473230 (JKB) Added doc_id above.
181
182 -- Bug 4165704: added cursor below to get locator
183 CURSOR Cur_locator(loc_id NUMBER) IS
184 SELECT concatenated_segments
185 FROM mtl_item_locations_kfv
186 WHERE inventory_location_id = loc_id;
187
188 CURSOR lot is
189 SELECT B.inventory_item_id,
190 mp.organization_code, --RLNAGARA B4905645
191 B.organization_id,
192 B.concatenated_segments ITEM_Number,
193 B.description,
194 A.parent_LOT_Number,
195 A.LOT_Number,
196 B.primary_uom_code,
197 B.primary_uom_code,
198 A.expiration_date,
199 A.Retest_Date
200 FROM mtl_lot_numbers A,
201 mtl_system_items_b_kfv B,
202 mtl_parameters mp --RLNAGARA B4905645
203 WHERE
204 A.inventory_item_id = B.inventory_item_id AND
205 A.organization_id = B.organization_id AND
206 mp.organization_id = A.organization_id AND --RLNAGARA B4905645
207 A.organization_id||'-'||A.inventory_item_id||'-'||A.LOT_number = l_event_key;
208
209
210 CURSOR recv IS
211 SELECT so.organization_code,
212 im.organization_id,
213 rt.subinventory,
214 im.inventory_Item_id,
215 sl.item_revision,
216 im.concatenated_segments item_number,
217 im.description,
218 rt.Quantity,
219 rt.secondary_quantity ,
220 rt.unit_of_measure ,
221 rt.secondary_unit_of_measure,
222 rt.shipment_header_id,
223 rt.po_header_id ,
224 rt.po_line_id ,
225 rt.vendor_id,
226 rt.vendor_site_id,
227 sl.shipment_line_id ,
228 po.segment1 purchase_no,
229 sh.receipt_num,
230 rt.locator_id,
231 po.org_id, --RLNAGARA B5018797
232 hou.name, --RLNAGARA B5018797
233 rt.vendor_lot_num --Bug#6145310
234 FROM rcv_transactions rt,
235 mtl_system_items_kfv im,
236 mtl_parameters so,
237 rcv_shipment_lines sl,
238 po_headers_all po ,
239 rcv_shipment_headers sh,
240 hr_operating_units hou --RLNAGARA B5018797
241 WHERE rt.TRANSACTION_ID = l_event_key
242 AND rt.shipment_header_id = sl.shipment_header_id
243 AND rt.shipment_line_id = sl.shipment_line_id
244 AND sl.item_id = im.inventory_item_id
245 AND sl.to_organization_id = im.organization_id
246 AND so.organization_id = im.organization_id
247 AND rt.organization_id = im.ORGANIZATION_ID
248 AND po.po_header_id = rt.po_header_id
249 AND sh.shipment_header_id = rt.shipment_header_id
250 AND po.org_id = hou.organization_id ; --RLNAGARA B5018797
251
252 /* if subinventory from above is not null */
253 CURSOR recv_subinventory IS
254 SELECT description
255 FROM mtl_secondary_inventories
256 WHERE organization_id = l_organization_id
257 AND secondary_inventory_name = l_subinventory ;
258
259 --RLNAGARA Bug5334308 Added cursor get_lot and modified cursor recv_inv
260 CURSOR get_lot(p_gen_obj_id NUMBER) IS
261 SELECT LOT_NUMBER
262 FROM mtl_lot_numbers
263 WHERE GEN_OBJECT_ID = p_gen_obj_id;
264
265 CURSOR recv_inv(p_trans_id NUMBER,p_lot_number VARCHAR2) IS
266 SELECT so.organization_code,
267 im.organization_id,
268 rt.subinventory,
269 im.inventory_Item_id,
270 sl.item_revision,
271 im.concatenated_segments item_number,
272 im.description,
273 rt.Quantity total_primary,
274 rt.secondary_quantity total_secondary,
275 rt.unit_of_measure ,
276 rt.secondary_unit_of_measure,
277 rt.shipment_header_id,
278 rt.po_header_id ,
279 rt.po_line_id ,
280 rt.vendor_id,
281 rt.vendor_site_id,
282 sl.shipment_line_id ,
283 po.segment1 ,
284 sh.receipt_num,
285 rt.locator_id,
286 lot.lot_number,
287 lot.parent_lot_number,
288 po.org_id,
289 hou.name,
290 sum(tran.quantity) lot_primary,
291 sum(tran.secondary_quantity) lot_secondary,
292 rt.vendor_lot_num --Bug#6145310
293 FROM rcv_transactions rt,
294 mtl_system_items_b_kfv im,
295 mtl_parameters so,
296 rcv_shipment_lines sl,
297 po_headers_all po ,
298 rcv_shipment_headers sh ,
299 rcv_lot_transactions tran ,
300 mtl_lot_numbers lot,
301 hr_operating_units hou
302 WHERE rt.TRANSACTION_ID = p_trans_id
303 AND rt.shipment_header_id = sl.shipment_header_id
304 AND rt.shipment_line_id = sl.shipment_line_id
305 AND sl.item_id = im.inventory_item_id
306 AND sl.to_organization_id = im.organization_id
307 AND so.organization_id = im.organization_id
308 AND rt.organization_id = im.ORGANIZATION_ID
309 AND po.po_header_id = rt.po_header_id
310 AND sh.shipment_header_id = rt.shipment_header_id
311 AND rt.transaction_id = tran.transaction_id
312 AND tran.lot_num = p_lot_number
313 AND lot.inventory_item_id = im.inventory_item_id
314 AND tran.lot_num = lot.lot_number
315 AND lot.organization_id = im.organization_id
316 AND po.org_id = hou.organization_id
317 GROUP BY so.organization_code,
318 im.organization_id,
319 rt.subinventory,
320 im.inventory_Item_id,
321 sl.item_revision,
322 im.concatenated_segments,
323 im.description,
324 rt.Quantity,
325 rt.secondary_quantity,
326 rt.unit_of_measure ,
327 rt.secondary_unit_of_measure,
328 rt.shipment_header_id,
329 rt.po_header_id ,
330 rt.po_line_id ,
331 rt.vendor_id,
332 rt.vendor_site_id,
333 sl.shipment_line_id ,
334 po.segment1 ,
335 sh.receipt_num,
336 rt.locator_id,
337 lot.lot_number,
338 lot.parent_lot_number,
339 po.org_id,
340 hou.name,
341 rt.vendor_lot_num;
342
343
344
345
346 CURSOR get_sampling_plan_id (x_spec_vr_id_in number) is
347 select nvl(sample_cnt_req, 0) sample_cnt_req,
348 nvl(sample_qty, 0) sample_qty,
349 sample_qty_uom,
350 frequency_cnt,
351 frequency_per,
352 sm.sampling_plan_id ,
353 frequency_type,
354 nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
355 nvl(RESERVE_QTY, 0) reserve_qty,
356 nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
357 nvl(ARCHIVE_QTY,0) archive_qty
358 from gmd_com_spec_vrs_vl sv, --gmd_all_spec_vrs sv, performance bug# 4916912
359 gmd_sampling_plans_b sm
360 where sv.sampling_plan_id = sm.sampling_plan_id
361 and sv.spec_vr_id = x_spec_vr_id_in ;
362
363 CURSOR inv_sample_plan (X_SPEC_VR_ID NUMBER) is
364 select nvl(sample_cnt_req, 0) sample_cnt_req,
365 nvl(sample_qty, 0) sample_qty,
366 sample_qty_uom,
367 frequency_cnt,
368 frequency_per,
369 sm.sampling_plan_id ,
370 frequency_type,
371 nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
372 nvl(RESERVE_QTY, 0) reserve_qty,
373 nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
374 nvl(ARCHIVE_QTY,0) archive_qty
375 from gmd_sampling_plans_b sm,
376 gmd_inventory_spec_vrs sv
377 where sv.sampling_plan_id = sm.sampling_plan_id
378 and sv.spec_vr_id = X_SPEC_VR_ID;
379
380 CURSOR sample_plan_freq_per (x_frequency_per varchar2 ) is
381 SELECT meaning
382 FROM gem_lookups
383 WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
384 and lookup_code = x_frequency_per ;
385
386
387 /* Cursors to check if Spec VR has auto enable flag enabled */
388
389 CURSOR inventory_auto_sample ( X_SPEC_VR_ID number) is
390 select nvl(auto_sample_ind,'N')
391 from GMD_INVENTORY_SPEC_VRS
392 where spec_vr_id = X_SPEC_VR_ID ;
393
394 CURSOR supplier_auto_sample ( X_SPEC_VR_ID number) is
395 Select nvl(auto_sample_ind,'N')
396 from GMD_SUPPLIER_SPEC_VRS
397 where spec_vr_id = X_SPEC_VR_ID ;
398
399 CURSOR specvr_auto_sample ( X_SPEC_VR_ID number) is
400 Select nvl(auto_sample_ind,'N')
401 from GMD_COM_SPEC_VRS_VL --GMD_ALL_SPEC_VRS performance bug# 4916912
402 where spec_vr_id = X_SPEC_VR_ID ;
403
404
405 /* Given a sampling event and a retain as, gets the sample numbers */
406 CURSOR get_sample_num (x_sampling_event_in number , x_retain_as_in varchar2) is
407 select sample_no from
408 gmd_Samples where
409 sampling_event_id = x_Sampling_event_in
410 and retain_as = x_retain_as_in ;
411 CURSOR get_reg_sample_num (x_sampling_event_in number , x_retain_as_in varchar2) is
412 select sample_no from
413 gmd_Samples where
414 sampling_event_id = x_Sampling_event_in
415 and retain_as is NULL ;
416
417 CURSOR get_from_role is
418 select nvl( text, '')
419 from wf_Resources
420 where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
421 and language = userenv('LANG') ;
422
423
424 -- Bug 4165704 - org_id should be in rcv_transactions table, but since it isn't
425 -- I need to get the value here
426 --RLNAGARA B5018797 org_id is there in the po_headers_all table and hence getting from there.
427 -- Here we are getting vendor name instead of vendor_site_code.
428 CURSOR get_vendor_no
429 IS /* 4576699*/
430 SELECT a.segment1
431 FROM po_vendors a,
432 mtl_parameters m
433 WHERE a.vendor_id = l_vendor_id
434 AND m.organization_id = l_organization_id
435 AND m.process_enabled_flag = 'Y' ;
436
437 CURSOR get_application_id
438 IS /* 4576699 */
439 SELECT application_id
440 FROM fnd_application
441 WHERE application_short_name = 'GMD';
442
443 CURSOR get_user_name (x_user_id NUMBER)
444 IS /* 4576699 */
445 SELECT user_name
446 FROM fnd_user
447 WHERE user_id = x_user_id;
448
449 BEGIN
450 gmd_debug.put_line ('SampleCreation WF. VERIFY_EVENT '); /* 4576699 */
451
452
453 IF (l_debug = 'Y') THEN
454 gmd_debug.log_initialize('SampleCreation');
455 END IF;
456
457 IF (l_debug = 'Y') THEN
458 gmd_debug.put_line('Event Name ' || l_event_name);
459 gmd_debug.put_line('Event Key ' || l_event_key);
460
461
462 END IF;
463
464 /*************************************/
465 /* CHECK WHICH EVENT HAS BEEN RAISED */
466 /*************************************/
467 -- Bug 4165704: event name changed
468 -- IF l_event_name = 'oracle.apps.gmi.inventory.created' THEN
469
470 IF ((l_event_name = 'oracle.apps.gmd.inventory.created')
471 OR ( l_event_name = 'oracle.apps.gmi.inventory.created')) THEN
472 l_transaction_type:='INVENTORY_TRANSACTION';
473 gmd_debug.put_line ('Inventory '); /* 4576699 */
474
475 OPEN inv;
476 FETCH inv INTO l_inventory_item_id,
477 l_revision,
478 l_item_number,
479 l_item_desc,
480 l_transaction_type_id,
481 l_transaction_type_name,
482 l_trans_qty,
483 l_trans_qty2,
484 l_trans_qty_uom,
485 l_trans_qty_uom2,
486 l_organization_id,
487 l_organization_code, --RLNAGARA B5499961
488 l_subinventory,
489 l_locator_id;
490 IF inv%found THEN
491
492 l_valid_transaction:=true;
493
494 -- Bug 4440045: added the cursor below to fetch lot and parent lot.
495 OPEN inv_lot;
496 FETCH inv_lot INTO l_parent_lot_number,
497 l_lot_number;
498 IF inv_lot%NOTFOUND THEN
499
500 l_parent_lot_number := NULL;
501 l_lot_number := NULL;
502 END IF;
503 END IF;
504
505 -- Bug 4165704: this routine is using the 'ic' files and I don't see why it is needed
506 -- so I took it out. I don't see l_transaction_type_id being used!!!
507 -- GET_DOC_NO(l_transaction_type_id, l_doc_type, l_transaction_type_id);
508 -- Bug #3473230 (JKB) Added get_doc_no above.
509
510 CLOSE inv;
511
512 -- Bug 4165704: added cursor to retrieve locator
513 IF l_locator_id IS NOT NULL THEN
514 OPEN Cur_locator(l_locator_id);
515 FETCH Cur_locator INTO l_locator;
516 CLOSE Cur_locator;
517 END IF; -- l_locator_id is not null
518
519 -- Bug 4165704:event name changed
520 -- ELSIF l_event_name = 'oracle.apps.gmd.lotexpiry')
521
522 ELSIF ( l_event_name = 'oracle.apps.gmi.lotexpirydate.update') THEN
523 gmd_debug.put_line ('Lot Expiry Date ') ;/* 4576699 */
524
525 l_transaction_type:='LOTEXPIRY_TRANSACTION';
526 OPEN lot;
527 Fetch lot INTO l_inventory_item_id,
528 l_organization_code, --RLNAGARA B4905645
529 l_organization_id,
530 l_item_number,
531 l_item_desc,
532 l_parent_lot_number,
533 l_lot_number,
534 l_trans_qty_uom,
535 l_trans_qty_uom2,
536 l_expiry_date_tmp, --RLNAGARA B4905645
537 l_retest_date_tmp; --RLNAGARA B4905645
538 IF lot%FOUND THEN
539 l_valid_transaction:=true;
540 l_retest_indicator :='Y';
541 --RLNAGARA B4905645
542 l_date_format_mask := fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS';
543 l_expiry_date := TO_CHAR(l_expiry_date_tmp,l_date_format_mask);
544
545 END IF;
546
547 CLOSE lot;
548
549 l_revision := NULL;
550 l_subinventory := NULL;
551 l_locator_id := NULL;
552
553 -- Bug 4165704:event name changed
554 -- ELSIF l_event_name = 'oracle.apps.gmd.lotretest' THEN
555 ELSIF ( l_event_name = 'oracle.apps.gmi.lotretestdate.update') THEN
556 gmd_debug.put_line ('Lot Retest Date ') ;/* 4576699 */
557
558 l_transaction_type:='LOTRETEST_TRANSACTION';
559 OPEN lot;
560 FETCH lot INTO l_inventory_item_id,
561 l_organization_code, --RLNAGARA B4905645
562 l_organization_id,
563 l_item_number,
564 l_item_desc,
565 l_parent_lot_number,
566 l_lot_number,
567 l_trans_qty_uom,
568 l_trans_qty_uom2,
569 l_expiry_date_tmp, --RLNAGARA B4905645
570 l_retest_date_tmp; --RLNAGARA B4905645
571 IF lot%FOUND THEN
572 l_valid_transaction:=true;
573 l_retest_indicator :='Y';
574 --RLNAGARA B4905645
575 l_date_format_mask := fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS';
576 l_retest_date := TO_CHAR(l_retest_date_tmp,l_date_format_mask);
577
578 END IF;
579 CLOSE lot;
580
581 l_revision := NULL;
582 l_subinventory := NULL;
583 l_locator_id := NULL;
584
585 ELSIF l_event_name = 'oracle.apps.gml.po.receipt.created' THEN
586 gmd_debug.put_line ('PO Receipts'); -- Bug # 4576699
587
588 l_transaction_type:='RECEIVING_TRANSACTION';
589 --wf_log_pkg.string(6, 'Dummy','PO Receipts');
590 /* Set Org Context as we are using multi org view RCV_TRANSACTIONS_V */
591
592 OPEN recv;
593 FETCH recv INTO l_organization_code,
594 l_organization_id,
595 l_subinventory,
596 l_inventory_item_id,
597 l_revision,
598 l_item_number,
599 l_item_desc,
600 l_trans_qty,
601 l_trans_qty2,
602 l_trans_qty_uom,
603 l_trans_qty_uom2,
604 l_shipment_header_id,
605 l_po_header_id,
606 l_po_line_id,
607 l_vendor_id,
608 l_vendor_site_id ,
609 l_receipt_line_id,
610 l_purchase_no ,
611 l_receipt_no,
612 l_locator_id,
613 -- took out org_id for P1 bug
614 l_org_id, --RLNAGARA B5018797 Uncommented this
615 l_operating_unit, --RLNAGARA B5018797
616 l_vendor_lot_num; --Bug#6145310
617 IF recv%found THEN
618 --wf_log_pkg.string(6, 'Dummy','Found PO Receipts');
619
620 l_valid_transaction :=true;
621 END IF;
622
623 CLOSE recv;
624
625 -- Bug 4165704: added cursor to retrieve locator
626 IF l_locator_id IS NOT NULL THEN
627 OPEN Cur_locator(l_locator_id);
628 FETCH Cur_locator INTO l_locator;
629 CLOSE Cur_locator;
630 END IF; -- l_locator_id is not null
631
632 -- Bug 4165704:event name changed
633 --ELSIF l_event_name = 'oracle.apps.gmi.inv.po.receipt' THEN
634 ELSIF(( l_event_name = 'oracle.apps.gmd.po.receipt.inventory')
635 OR (l_event_name = 'oracle.apps.gmi.inv.po.receipt')) THEN
636 gmd_debug.put_line ('Inventory PO Receipts'); -- Bug # 4576699
637
638 l_transaction_type:='INV_RCV_TRANSACTION';
639
640 --RLNAGARA Bug5334308
641 l_trans_id := SUBSTR(l_event_key,1,INSTR(l_event_key,'-')-1);
642 l_gen_obj_id := SUBSTR(l_event_key,INSTR(l_event_key,'-')+1);
643
644 OPEN get_lot(l_gen_obj_id);
645 FETCH get_lot INTO l_lot_number;
646 CLOSE get_lot;
647
648 OPEN recv_inv(l_trans_id,l_lot_number);
649 FETCH recv_inv into l_organization_code,
650 l_organization_id,
651 l_subinventory,
652 l_inventory_item_id,
653 l_revision,
654 l_item_number,
655 l_item_desc,
656 l_trans_qty,
657 l_trans_qty2,
658 l_trans_qty_uom,
659 l_trans_qty_uom2,
660 l_shipment_header_id,
661 l_po_header_id,
662 l_po_line_id,
663 l_vendor_id,
664 l_vendor_site_id ,
665 l_receipt_line_id,
666 l_purchase_no ,
667 l_receipt_no,
668 l_locator_id,
669 l_lot_number,
670 l_parent_lot_number,
671 l_org_id,
672 l_operating_unit,
673 l_lot_qty,
674 l_lot_qty2,
675 l_vendor_lot_num; --Bug#6145310
676 IF recv_inv%found THEN
677 gmd_debug.put_line ('Found Inv PO Receipts'); -- Bug # 4576699
678 l_valid_transaction :=true;
679 END IF;
680 CLOSE recv_inv;
681
682 -- Bug 4165704: added cursor to retrieve locator
683 IF l_locator_id IS NOT NULL THEN
684 OPEN Cur_locator(l_locator_id);
685 FETCH Cur_locator INTO l_locator;
686 CLOSE Cur_locator;
687 END IF; -- l_locator_id is not null
688
689 ELSIF l_event_name = 'oracle.apps.gme.batch.created' THEN
690 gmd_debug.put_line ('Event is Batch Creation'); -- Bug # 4576699
691 l_transaction_type:='PRODUCTION_TRANSACTION';
692 l_valid_transaction:=true;
693 ELSIF l_event_name = 'oracle.apps.gme.batchstep.created' THEN
694 gmd_debug.put_line ('Event is Batchstep Creation'); -- Bug # 4576699
695 l_transaction_type:='PRODUCTION_TRANSACTION';
696 l_valid_transaction:=true;
697 ELSIF l_event_name = 'oracle.apps.gme.bstep.rel.wf' THEN
698 gmd_debug.put_line ('Event is Batch Step Release'); -- Bug # 4576699
699 l_transaction_type:='PRODUCTION_TRANSACTION';
700 l_valid_transaction:=true;
701 END IF;
702
703
704 /************************************************/
705 /* END OF CHECK FOR WHICH EVENT HAS BEEN RAISED */
706 /************************************************/
707
708
709 IF (l_debug = 'Y') THEN
710 gmd_debug.put_line('Transaction Type ' || l_transaction_type);
711 END IF;
712
713 OPEN get_from_role ;
714 FETCH get_from_role into l_from_role ;
715 CLOSE get_from_role ;
716
717
718 IF l_Event_name not in ('oracle.apps.gme.batchstep.created',
719 'oracle.apps.gme.batch.created',
720 'oracle.apps.gme.bstep.rel.wf') THEN
721 gmd_debug.put_line ('Checking for Vendor Specifications '); -- Bug # 4576699
722
723 /**********************************/
724 /* ONLY CONTINUE IF NOT WIP */
725 /**********************************/
726
727 -- Bug 4165704: event 'oracle.apps.gmi.inv.po.receipt' name changed to 'oracle.apps.gmd.po.receipt.inventory'
728 IF l_valid_transaction and l_event_name in ('oracle.apps.gml.po.receipt.created',
729 'oracle.apps.gmd.po.receipt.inventory',
730 'oracle.apps.gmi.inv.po.receipt') THEN
731 gmd_debug.put_line ('Processing PO Transactions'); -- Bug # 4576699
732
733
734 /**********************************/
735 /* Supplier Samples ONLY */
736 /* Check for specification */
737 /**********************************/
738
739
740 IF (l_debug = 'Y') THEN
741 gmd_debug.put_line('PO Receiving Code');
742 END IF;
743
744 -- Bug 4165704: replaced the following sql with code below
745 IF l_vendor_id IS NOT NULL THEN
746 OPEN get_vendor_no; /* 4576699 */
747 FETCH get_vendor_no INTO l_vendor_no;
748
749 IF (get_vendor_no%NOTFOUND)
750 THEN
751 CLOSE get_vendor_no;
752
753 RAISE no_vendor_found;
754 END IF;
755 CLOSE get_vendor_no;
756 END IF;
757
758
759
760 /*Figure out if proper Specifications exist */
761 l_supp_spec.inventory_item_id := l_inventory_item_id;
762 l_supp_spec.revision := l_revision ;
763 l_supp_spec.organization_id := l_organization_id;
764 l_supp_spec.subinventory := l_subinventory;
765 l_supp_spec.locator_id := l_locator_id;
766 l_supp_spec.supplier_id := l_vendor_id;
767 l_supp_spec.supplier_site_id := l_vendor_site_id;
768 l_supp_spec.po_header_id := l_po_header_id;
769 l_supp_spec.po_line_id := l_po_line_id;
770 l_supp_spec.date_effective := SYSDATE;
771 l_supp_spec.exact_match := 'N';
772 l_supp_spec.lot_number := L_lot_number;
773 l_supp_spec.parent_lot_number := L_parent_lot_number;
774 l_supp_spec.org_id := l_org_id; --RLNAGARA B5018797 to find correct supplier spec using org_id
775
776 gmd_debug.put_line ('PO Specification attributes Set'); -- Bug # 4576699
777 --wf_log_pkg.string(6, 'Dummy','PO Specification attributes Set');
778
779 IF (l_debug = 'Y') THEN
780 gmd_debug.put_line('Checking for Spec');
781 END IF;
782
783 IF GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_OR_INV_SPEC(
784 p_supplier_spec_rec => l_supp_spec,
785 x_spec_id => l_spec_id,
786 x_spec_vr_id => l_spec_vr_id,
787 x_spec_type => l_spec_type ,
788 x_return_status => l_return_status,
789 x_message_data => l_msg_data) THEN
790
791
792 /* Specification Found */
793 /* Check to see if there is a sampling plan */
794 IF (l_debug = 'Y') THEN
795 gmd_debug.put_line('Spec found');
796 END IF;
797
798 OPEN get_sampling_plan_id (l_spec_vr_id);
799 FETCH get_sampling_plan_id into l_sample_count,
800 l_sample_qty,
801 l_sample_qty_uom,
802 l_frequency_count, l_frequency_per ,
803 l_sample_plan_id ,
804 l_frequency_type,
805 l_reserve_cnt_req,
806 l_reserve_qty,
807 l_archive_cnt_req,
808 l_archive_qty ;
809
810 IF get_sampling_plan_id%found THEN
811 IF (l_frequency_type <> 'Q') THEN
812 OPEN sample_plan_freq_per (l_frequency_per) ;
813 FETCH sample_plan_freq_per into l_frequency_per ;
814 CLOSE sample_plan_freq_per ;
815 END IF;
816
817 l_sample_plan_exists := 1;
818 ELSE
819 l_sample_plan_exists := 0;
820 l_sample_count := 1;
821 END IF; -- IF get_sampling_plan_id%found
822 CLOSE get_sampling_plan_id ;
823
824 /*
825 OPEN recv_sample_plan(l_spec_vr_id);
826 FETCH recv_sample_plan into l_sample_count,
827 l_sample_qty,
828 l_sample_qty_uom,
829 l_frequency_count, l_frequency_per ,
830 l_sample_plan_id ,
831 l_frequency_type,
832 l_reserve_cnt_req,
833 l_reserve_qty,
834 l_archive_cnt_req,
835 l_archive_qty;
836 CLOSE recv_sample_plan;
837 */
838
839
840 /* Create Sampling Event for Supplier sample */
841 l_sampling_events.original_spec_vr_id := l_spec_vr_id;
842 l_sampling_events.sampling_plan_id := l_sample_plan_id;
843 l_sampling_events.disposition := '1P';
844 l_sampling_events.source := 'S';
845 l_sampling_events.inventory_item_id := L_inventory_item_id;
846 l_sampling_events.revision := L_revision ;
847 l_sampling_events.sample_req_cnt := l_sample_count;
848 l_sampling_events.sample_taken_cnt := 0;
849 l_sampling_events.supplier_id := L_vendor_id;
850 l_sampling_events.supplier_site_id := L_vendor_site_id;
851 l_sampling_events.po_header_id := L_po_header_id;
852 l_sampling_events.po_line_id := L_po_line_id;
853 l_sampling_events.sample_type := 'I';
854 l_sampling_events.subinventory := l_subinventory;
855 l_sampling_events.locator_id := l_locator_id;
856 l_sampling_events.lot_number := L_lot_number;
857 l_sampling_events.parent_lot_number := L_parent_lot_number;
858 l_sampling_events.organization_id := L_organization_id;
859 l_sampling_events.CREATION_DATE := SYSDATE;
860 l_sampling_events.CREATED_BY := FND_GLOBAL.USER_ID;
861 l_sampling_events.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
862 l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
863 l_sampling_events.org_id := l_org_id; --RLNAGARA B5018797 Added this parameter to insert
864 l_sampling_events.supplier_lot_no := l_vendor_lot_num;
865
866 /* Added missing PO Receiving information */
867 l_sampling_events.receipt_id := L_shipment_header_id;
868 l_sampling_events.receipt_line_id := L_receipt_line_id;
869
870 IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
871 p_sampling_events => l_sampling_events,
872 x_sampling_events => l_sampling_events_out) THEN
873 gmd_debug.put_line ('Sampling Event Creation Failed'); -- Bug # 4576699
874
875 --wf_log_pkg.string(6, 'Dummy','Sampling Event Creation Failed');
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878
879 l_sampling_events := l_sampling_events_out ;
880
881
882 /* Check to see if auto Sample Creation is enabled for this supplier or inventory Spec VR */
883 /* Invconv bug 4165704: if inventory spec vr found, inventory_auto_sample cursor used */
884 IF l_spec_type = 'I' THEN
885 OPEN inventory_auto_sample ( l_spec_vr_id);
886 FETCH inventory_auto_sample into l_auto_sample;
887 CLOSE inventory_auto_sample;
888 ELSE
889 OPEN supplier_auto_sample (l_spec_vr_id);
890 FETCH supplier_auto_sample into l_auto_Sample;
891 CLOSE supplier_auto_sample;
892 END IF;
893
894 -- bug 4165704: doc_number replaced by call to GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters
895 -- Check to see if Auto Doc Numbering exists for the Org
896 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
897 p_organization_id => l_organization_id
898 , x_quality_parameters => quality_config
899 , x_return_status => l_return_status
900 , x_orgn_found => l_orgn_found );
901
902 IF (l_return_status <> 'S') THEN
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906 l_doc_number := quality_config.sample_assignment_type;
907
908 IF l_sample_plan_exists = 1 THEN
909 -- taken out because redundant
910 --IF (l_frequency_type <> 'Q') THEN
911 -- OPEN sample_plan_freq_per (l_frequency_per) ;
912 -- FETCH sample_plan_freq_per into l_frequency_per ;
913 -- CLOSE sample_plan_freq_per ;
914 --END IF;
915
916 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_INFO');
917 FND_MESSAGE.SET_TOKEN('SAMPLE_NO', l_sample_count);
918 FND_MESSAGE.SET_TOKEN('FREQ_CNT',l_frequency_count);
919 FND_MESSAGE.SET_TOKEN('FREQ_PER',l_frequency_per);
920 FND_MESSAGE.SET_TOKEN('SAMPLE_QTY', l_sample_qty);
921 FND_MESSAGE.SET_TOKEN('SAMPLE_UOM', l_sample_qty_uom); --RLNAGARA B4905670 It is SAMPLE_UOM and not SAMPLE_QTY_UOM
922 FND_MESSAGE.SET_TOKEN('ASAMPLE_NO', l_archive_cnt_req);
923 FND_MESSAGE.SET_TOKEN('ASAMPLE_QTY', l_archive_qty);
924 FND_MESSAGE.SET_TOKEN('RSAMPLE_NO', l_reserve_cnt_req);
925 FND_MESSAGE.SET_TOKEN('RSAMPLE_QTY', l_reserve_qty);
926
927 /* Check to see if auto sample creation is enabled and auto docu numbering for the Org*/
928 IF ((l_auto_sample = 'Y') and (l_doc_number = 2))THEN
929
930 /* Calculate the required standard samples */
931 IF (l_frequency_type = 'Q') THEN -- Bug 3617267
932 -- Bug 4165704: Inventory Convergence
933 -- new conversion routine used.
934 -- GMICUOM.icuomcv( pitem_id => l_inventory_item_id,
935 -- plot_id => NULL,
936 -- pcur_qty => l_trans_qty ,
937 -- pcur_uom => l_trans_qty_uom,
938 -- pnew_uom => l_frequency_per ,
939 -- onew_qty => l_qty_conv);
940
941 --Begin Bug 6807847. For PO Transactions l_trans_qty_uom is in unit_of_measure.
942 --Hence get corresponding uom_code from unit_of_measure.
943 IF (l_debug = 'Y') THEN
944 gmd_debug.put_line('PO Transactions, l_trans_qty_uom = '||l_trans_qty_uom);
945 END IF;
946
947 BEGIN
948 SELECT uom_code
949 INTO l_trans_qty_uom
950 FROM mtl_units_of_measure
951 WHERE unit_of_measure = l_trans_qty_uom;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 IF (l_debug = 'Y') THEN
956 gmd_debug.put_line('Unable to fetch uom_code from mtl_units_of_measure');
957 END IF;
958 RAISE FND_API.G_EXC_ERROR;
959 END;
960 --End Bug 6807847.
961
962 --RLNAGARA 5334308 Added the IF condition..coz we need to create samples based on Lot qty
963 IF (l_event_name = 'oracle.apps.gmi.inv.po.receipt') THEN
964 l_qty_conv := INV_CONVERT. inv_um_convert (
965 item_id => l_inventory_item_id,
966 lot_number => NULL,
967 organization_id => l_organization_id,
968 precision => 5, -- decimal point precision
969 from_quantity => l_lot_qty,
970 from_unit => l_trans_qty_uom,
971 to_unit => l_frequency_per,
972 from_name => NULL ,
973 to_name => NULL) ;
974 ELSE
975 l_qty_conv := INV_CONVERT. inv_um_convert (
976 item_id => l_inventory_item_id,
977 lot_number => NULL,
978 organization_id => l_organization_id,
979 precision => 5, -- decimal point precision
980 from_quantity => l_trans_qty,
981 from_unit => l_trans_qty_uom,
982 to_unit => l_frequency_per,
983 from_name => NULL ,
984 to_name => NULL) ;
985 END IF;
986
987 --Bug 6807847
988 IF l_qty_conv = -99999 THEN
989 IF (l_debug = 'Y') THEN
990 gmd_debug.put_line('ERROR1 in function INV_CONVERT.inv_um_convert');
991 END IF;
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 -- Bug 4896237. svankada. Sample Count = (Trans_qty in sampling plan freq UOM / Per * Sample_cnt)
996 l_sampling_events.sample_req_cnt := floor(l_qty_conv / l_frequency_count) * l_sample_count;
997
998 ELSIF (l_frequency_type = 'F') THEN -- Bug 3617267
999 --RLNAGARA Bug 5910300 start
1000 UPDATE GMD_SUPPLIER_SPEC_VRS
1001 SET RECEIPT_FREQUENCY = NVL(RECEIPT_FREQUENCY,0) + 1 --RLNAGARA Bug 5910300 Rework Added NVL
1002 WHERE SPEC_VR_ID = l_spec_vr_id;
1003 select RECEIPT_FREQUENCY into r from GMD_SUPPLIER_SPEC_VRS
1004 where spec_vr_id = l_spec_vr_id;
1005 IF r <> l_frequency_count THEN
1006 p_resultout := 'COMPLETE:';
1007 RETURN;
1008 ELSE
1009 UPDATE GMD_SUPPLIER_SPEC_VRS
1010 SET RECEIPT_FREQUENCY = 0
1011 WHERE SPEC_VR_ID = l_spec_vr_id;
1012 l_sampling_events.sample_req_cnt := l_sample_count;
1013 END IF;
1014 --RLNAGARA Bug 5910300 end
1015 ELSIF (l_frequency_type = 'T') THEN -- Bug 3617267
1016 l_sampling_events.sample_req_cnt := l_sample_count;
1017 ELSIF (l_frequency_type = 'P') THEN -- Bug 3617267
1018 --RLNAGARA 5334308 Added the IF condition..coz we need to create samples based on Lot qty
1019 IF (l_event_name = 'oracle.apps.gmi.inv.po.receipt') THEN
1020 l_sampling_events.sample_req_cnt :=
1021 l_sample_count * floor(l_lot_qty / l_frequency_per);
1022 ELSE
1023 l_sampling_events.sample_req_cnt :=
1024 l_sample_count * floor(l_trans_qty / l_frequency_per);
1025 END IF;
1026 END IF; -- IF (l_frequency_type = 'Q')
1027
1028 -- Create Supplier samples
1029 GMD_AUTO_SAMPLE_PKG.create_samples(l_sampling_events,
1030 l_spec_id,
1031 l_spec_vr_id,
1032 create_status );
1033
1034 /* Sampling Event Successfully Created. Set Form Attribute to the sampling event */
1035 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="' || l_sampling_events.sampling_event_id||'"';
1036
1037 /* If we created samples, show them */
1038 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'R');
1039 FETCH get_sample_num into frsample_name ; /* Get the first */
1040 IF frsample_name is not NULL THEN
1041 LOOP
1042 lrsample_name := sample_name_temp ;
1043 sample_name_temp := '';
1044 FETCH get_sample_num into sample_name_temp ;
1045 EXIT WHEN get_sample_num%NOTFOUND;
1046 END LOOP ;
1047 ELSE
1048 lrsample_name := frsample_name ;
1049 END IF;
1050 CLOSE get_sample_num ;
1051
1052 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'A');
1053 FETCH get_sample_num into fasample_name ; /* Get the first */
1054 IF fasample_name is not NULL THEN
1055 LOOP
1056 lasample_name := sample_name_temp ;
1057 sample_name_temp := '';
1058 FETCH get_sample_num into sample_name_temp ;
1059 EXIT WHEN get_sample_num%NOTFOUND;
1060 END LOOP ;
1061 ELSE
1062 lasample_name := fasample_name ;
1063 END IF;
1064 CLOSE get_sample_num ;
1065
1066 OPEN get_reg_sample_num (l_sampling_events.sampling_event_id, null);
1067 FETCH get_reg_sample_num into fsample_name ; /* Get the first */
1068 IF fsample_name is not NULL THEN
1069 LOOP
1070 lsample_name := sample_name_temp ;
1071 sample_name_temp := '';
1072 FETCH get_reg_sample_num into sample_name_temp ;
1073 EXIT WHEN get_reg_sample_num%NOTFOUND;
1074 END LOOP ;
1075 ELSE
1076 lsample_name := fsample_name ;
1077 END IF;
1078 CLOSE get_reg_sample_num;
1079
1080 IF (lrsample_name IS not NULL) THEN
1081 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES',
1082 frsample_name || '-' ||lrsample_name);
1083 ELSE
1084 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES', frsample_name);
1085 END IF;
1086
1087 IF (lasample_name IS not NULL) THEN
1088 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES',
1089 fasample_name || '-' ||lasample_name);
1090 ELSE
1091 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES', fasample_name);
1092 END IF;
1093
1094 IF (lsample_name IS not NULL) THEN
1095 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES',
1096 fsample_name || '-' ||lsample_name);
1097 ELSE
1098 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES', fsample_name);
1099 END IF;
1100
1101 --l_sample_plan := FND_MESSAGE.GET(); RLNAGARA B4905670 Commented this line
1102
1103 ELSE -- IF ((l_auto_sample <> 'Y') or (l_doc_number <> 2))
1104
1105 /* Sampling Event Successfully Created. Set Form Attribute to the sampling event */
1106 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
1107 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
1108 --is getting created from the workflow notification. Based on this only we take the sample
1109 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
1110 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
1111 --then we take the default disposition ie Pending to create the samples.
1112
1113 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'"';
1114 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
1115
1116 END IF; -- IF ((l_auto_sample = 'Y') and (l_doc_number = 2))
1117
1118 -- Bug 3625651 : populate the workflow attribute with
1119 -- the sampling plan information
1120 l_sample_plan := FND_MESSAGE.GET();
1121
1122 ELSE -- l_sample_plan_exists <> 1
1123 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_NONE');
1124 l_sample_plan := FND_MESSAGE.GET();
1125 l_sample_count := 1; -- just in case
1126
1127 /* Sampling Event Successfully Created. Set Form Attribute
1128 to the sampling event */
1129 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
1130 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
1131 --is getting created from the workflow notification. Based on this only we take the sample
1132 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
1133 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
1134 --then we take the default disposition ie Pending to create the samples.
1135
1136 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'"';
1137 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
1138 END IF; -- IF l_sample_plan_exists = 1
1139
1140
1141 IF ((l_auto_sample <> 'Y') or (l_doc_number <> 2))THEN
1142 /* In the case where auto sample IS not enabled, create event spec disp */
1143 /* Create Spec Event Disposition Row */
1144 l_event_spec_disp.SAMPLING_EVENT_ID := l_sampling_events.sampling_event_id;
1145 l_event_spec_disp.SPEC_ID :=l_spec_id;
1146 l_event_spec_disp.SPEC_VR_ID := l_spec_vr_id;
1147 l_event_spec_disp.DISPOSITION :='1P';
1148 l_event_spec_disp.SPEC_USED_FOR_LOT_ATTRIB_IND:=NULL;
1149 l_event_spec_disp.DELETE_MARK:=0;
1150 l_event_spec_disp.CREATION_DATE:=sysdate;
1151 l_event_spec_disp.CREATED_BY:=FND_GLOBAL.USER_ID;
1152 l_event_spec_disp.LAST_UPDATE_DATE:=sysdate;
1153 l_event_spec_disp.LAST_UPDATED_BY:=FND_GLOBAL.USER_ID;
1154
1155 IF (l_debug = 'Y') THEN
1156 gmd_debug.put_line('Going to insert event spec disp');
1157 END IF;
1158
1159 IF NOT GMD_EVENT_SPEC_DISP_PVT.INSERT_ROW(
1160 p_event_spec_disp =>l_event_spec_disp,
1161 x_event_spec_disp =>l_event_spec_disp_out) THEN
1162 gmd_debug.put_line ('Sampling Event disposition Creation Failed'); -- Bug # 4576699
1163
1164 --wf_log_pkg.string(6, 'Dummy','Sampling Event disposition Creation Failed');
1165 RAISE FND_API.G_EXC_ERROR;
1166 END IF;
1167
1168 l_event_spec_disp := l_event_spec_disp_out ;
1169
1170 IF (l_debug = 'Y') THEN
1171 gmd_debug.put_line('Created event spec disp');
1172 END IF;
1173
1174 END IF ; /* End of check to create event spec for non-auto sample */
1175
1176 l_vendor_spec_found:=true;
1177 gmd_debug.put_line ( 'Vendor Specification Found....Event Created with id'
1178 || l_sampling_events.sampling_event_id); -- Bug # 4576699
1179
1180 ELSE -- supplier spec not found
1181 l_vendor_spec_found:=false;
1182 END IF; -- IF GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_OR_INV_SPEC(
1183
1184 END IF; -- IF l_valid_transaction and l_event_name in ('oracle.apps.gml.po.receipt.created',
1185
1186
1187 /***************************************************************/
1188 /***************************************************************/
1189 /***************************************************************/
1190 /* Come here if inventory transaction (lot retest/expiry, etc. */
1191 /***************************************************************/
1192 /* CHECK FOR INVENTORY SPEC IF OTHER SPEC NOT FOUND ************/
1193 /***************************************************************/
1194 /***************************************************************/
1195 /***************************************************************/
1196
1197 --gml_sf_log('Checking for inventory spec. ');
1198
1199 IF l_valid_transaction and not l_vendor_spec_found THEN
1200 IF (l_debug = 'Y') THEN
1201 gmd_debug.put_line('Inventory Code');
1202 END IF;
1203
1204 /* Figure out if proper Specifications exist */
1205 l_inv_spec.inventory_item_id := L_inventory_item_id;
1206 l_inv_spec.revision := L_revision ;
1207 l_inv_spec.organization_id := l_organization_id;
1208 l_inv_spec.lot_number := l_lot_number;
1209 l_inv_spec.parent_lot_number := l_parent_lot_number;
1210 l_inv_spec.subinventory := l_subinventory;
1211 l_inv_spec.locator_id := l_locator_id;
1212 l_inv_spec.date_effective := SYSDATE;
1213 l_inv_spec.exact_match := 'N';
1214
1215 IF (l_debug = 'Y') THEN
1216 gmd_debug.put_line('Checking for Spec');
1217 END IF;
1218
1219
1220 IF GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC(
1221 p_inventory_spec_rec => l_inv_spec,
1222 x_spec_id => l_spec_id,
1223 x_spec_vr_id => l_spec_vr_id,
1224 x_return_status => l_return_status,
1225 x_message_data => l_msg_data) THEN
1226
1227 /* Specification Found */
1228 /* Create Sampling Event */
1229
1230 IF (l_debug = 'Y') THEN
1231 gmd_debug.put_line('Spec Found: '||l_spec_id||' '||l_spec_vr_id);
1232 END IF;
1233
1234 OPEN get_sampling_plan_id (l_spec_vr_id);
1235 FETCH get_sampling_plan_id INTO l_sample_count,
1236 l_sample_qty,
1237 l_sample_qty_uom,
1238 l_frequency_count,
1239 l_frequency_per ,
1240 l_sample_plan_id ,
1241 l_frequency_type,
1242 l_reserve_cnt_req,
1243 l_reserve_qty,
1244 l_archive_cnt_req,
1245 l_archive_qty ;
1246
1247 /* replaced by get_sampling_plan_id above
1248 OPEN inv_sample_plan(l_spec_vr_id);
1249 FETCH inv_sample_plan into l_sample_count, l_sample_qty,
1250 l_sample_qty_uom, l_frequency_count, l_frequency_per ,
1251 l_sample_plan_id , l_frequency_type,
1252 l_reserve_cnt_req, l_reserve_qty,
1253 l_archive_cnt_req, l_archive_qty;
1254 CLOSE inv_sample_plan;
1255 */
1256
1257 IF get_sampling_plan_id%found THEN
1258 NULL;
1259 ELSE
1260 l_sample_count := 1;
1261 END IF;
1262
1263 CLOSE get_sampling_plan_id ;
1264
1265 l_sampling_events.original_spec_vr_id := l_spec_vr_id;
1266 l_sampling_events.sampling_plan_id := l_sample_plan_id;
1267 l_sampling_events.locator_id := l_locator_id;
1268 l_sampling_events.disposition := '1P';
1269 l_sampling_events.source := 'I';
1270 l_sampling_events.inventory_item_id := L_inventory_item_id;
1271 l_sampling_events.revision := L_revision ;
1272 l_sampling_events.lot_number := L_lot_number;
1273 l_sampling_events.parent_lot_number := L_parent_lot_number;
1274 l_sampling_events.subinventory := L_subinventory;
1275 l_sampling_events.lot_retest_ind := L_retest_indicator;
1276 l_sampling_events.sample_req_cnt := l_sample_count;
1277 l_sampling_events.sample_taken_cnt := 0;
1278 l_sampling_events.sample_type := 'I';
1279 -- Bug 2825696: added orgn_code to gmd_sampling_events table
1280 l_sampling_events.organization_id := L_organization_id;
1281 l_sampling_events.CREATION_DATE := SYSDATE;
1282 l_sampling_events.CREATED_BY := FND_GLOBAL.USER_ID;
1283 l_sampling_events.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1284 l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
1285
1286 IF (l_debug = 'Y') THEN
1287 gmd_debug.put_line('Going to insert sample event');
1288 END IF;
1289
1290 IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
1291 p_sampling_events => l_sampling_events,
1292 x_sampling_events => l_sampling_events_out) THEN
1293 gmd_debug.put_line ('Sampling Event Creation Failed'); -- Bug # 4576699
1294 RAISE FND_API.G_EXC_ERROR;
1295 END IF;
1296
1297 l_sampling_events := l_sampling_events_out ;
1298
1299 IF (l_debug = 'Y') THEN
1300 gmd_debug.put_line('Created Sampling Event');
1301 END IF;
1302
1303 /* Check to see if auto Sample Creation is enabled for this Spec VR */
1304 OPEN inventory_auto_sample ( l_spec_vr_id);
1305 FETCH inventory_auto_sample into l_auto_sample;
1306 CLOSE inventory_auto_sample;
1307
1308 /* Check to see if Auto Doc Numbering exists for the Org */
1309 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
1310 p_organization_id => l_organization_id
1311 , x_quality_parameters => quality_config
1312 , x_return_status => l_return_status
1313 , x_orgn_found => l_orgn_found );
1314
1315 IF (l_return_status <> 'S') THEN
1316 RAISE FND_API.G_EXC_ERROR;
1317 END IF;
1318
1319 l_doc_number := quality_config.sample_assignment_type;
1320
1321 IF (l_debug = 'Y') THEN
1322 gmd_debug.put_line('Auto Sample Flag: ' || l_auto_sample);
1323 END IF;
1324
1325 /* Check to see if there is a sampling plan */
1326 OPEN inv_sample_plan(l_spec_vr_id);
1327 FETCH inv_sample_plan into l_sample_count, l_sample_qty,
1328 l_sample_qty_uom, l_frequency_count, l_frequency_per ,
1329 l_sample_plan_id , l_frequency_type,
1330 l_reserve_cnt_req, l_reserve_qty,
1331 l_archive_cnt_req, l_archive_qty ;
1332
1333 IF inv_sample_plan%found THEN
1334 IF (l_frequency_type <> 'Q') THEN
1335 OPEN sample_plan_freq_per (l_frequency_per) ;
1336 FETCH sample_plan_freq_per into l_frequency_per ;
1337 CLOSE sample_plan_freq_per ;
1338 END IF;
1339
1340 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_INFO');
1341 FND_MESSAGE.SET_TOKEN('SAMPLE_NO', l_sample_count);
1342 FND_MESSAGE.SET_TOKEN('FREQ_CNT',l_frequency_count);
1343 FND_MESSAGE.SET_TOKEN('FREQ_PER',l_frequency_per);
1344 FND_MESSAGE.SET_TOKEN('SAMPLE_QTY', l_sample_qty);
1345 FND_MESSAGE.SET_TOKEN('SAMPLE_UOM', l_sample_qty_uom); --RLNAGARA B4905670
1346 FND_MESSAGE.SET_TOKEN('ASAMPLE_NO', l_archive_cnt_req);
1347 FND_MESSAGE.SET_TOKEN('ASAMPLE_QTY', l_archive_qty);
1348 FND_MESSAGE.SET_TOKEN('RSAMPLE_NO', l_reserve_cnt_req);
1349 FND_MESSAGE.SET_TOKEN('RSAMPLE_QTY', l_reserve_qty);
1350
1351 l_sample_plan_exists := 1;
1352
1353 /* Check to see if auto sample creation is enabled */
1354 IF ((l_auto_sample = 'Y') and (l_doc_number = 2)) THEN
1355 IF (l_debug = 'Y') THEN
1356 gmd_debug.put_line('going to Auto sample pkg');
1357 END IF;
1358
1359 /* Calculate the required standard samples */
1360 -- BUG 4165704: event name changed
1361 -- IF l_event_name = 'oracle.apps.gmi.inventory.created' THEN
1362 IF ((l_event_name = 'oracle.apps.gmd.inventory.created')
1363 OR (l_event_name = 'oracle.apps.gmi.inventory.created')) THEN
1364 IF (l_frequency_type = 'Q') THEN -- Bug 3617267
1365 -- Bug 4165704: Inventory Convergence
1366 -- new conversion routine used.
1367 -- GMICUOM.icuomcv( pitem_id => l_inventory_item_id,
1368 -- plot_id => NULL,
1369 -- pcur_qty => l_trans_qty ,
1370 -- pcur_uom => l_trans_qty_uom,
1371 -- pnew_uom => l_frequency_per ,
1372 -- onew_qty => l_qty_conv);
1373
1374 l_qty_conv := INV_CONVERT. inv_um_convert (
1375 item_id => l_inventory_item_id,
1376 lot_number => NULL,
1377 organization_id => l_organization_id,
1378 precision => 5, -- decimal point precision
1379 from_quantity => l_trans_qty,
1380 from_unit => l_trans_qty_uom,
1381 to_unit => l_frequency_per,
1382 from_name => NULL ,
1383 to_name => NULL) ;
1384
1385 --Bug 6807847
1386 IF l_qty_conv = -99999 THEN
1387 IF (l_debug = 'Y') THEN
1388 gmd_debug.put_line('ERROR2 in function INV_CONVERT.inv_um_convert');
1389 END IF;
1390 RAISE FND_API.G_EXC_ERROR;
1391 END IF;
1392
1393 -- Bug 4896237. svankada. Sample Count = (Trans_qty in sampling plan freq UOM / Per * Sample_cnt)
1394 l_sampling_events.sample_req_cnt := FLOOR (l_qty_conv / l_frequency_count) * l_sample_count;
1395 ELSIF (l_frequency_type = 'F') THEN -- Bug 3617267
1396 l_sampling_events.sample_req_cnt := l_sample_count;
1397 ELSIF (l_frequency_type = 'T') THEN -- Bug 3617267
1398 l_sampling_events.sample_req_cnt := l_sample_count;
1399 ELSIF (l_frequency_type = 'P') THEN -- Bug 3617267
1400 l_sampling_events.sample_req_cnt :=
1401 l_sample_count * floor(l_trans_qty / l_frequency_per);
1402 END IF; -- IF (l_frequency_type = 'Q')
1403 END IF; -- IF l_event_name = 'oracle.apps.gmd.inventory.created'
1404
1405 GMD_AUTO_SAMPLE_PKG.create_samples(l_sampling_events,
1406 l_spec_id,l_spec_vr_id, create_status );
1407
1408 IF (l_debug = 'Y') THEN
1409 gmd_debug.put_line('Status from Auto pkg ' ||create_status);
1410 END IF;
1411
1412
1413 /* Sampling Event Successfully Created. Set Form Attribute
1414 to the sampling event */
1415 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="'
1416 || l_sampling_events.sampling_event_id||'"';
1417
1418 /* If we created samples, show them */
1419 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'R');
1420 FETCH get_sample_num into frsample_name ; /* Get the first */
1421
1422 IF frsample_name IS not NULL THEN
1423 LOOP
1424 lrsample_name := sample_name_temp ;
1425 sample_name_temp := '';
1426 FETCH get_sample_num into sample_name_temp ;
1427 EXIT WHEN get_sample_num%NOTFOUND;
1428 END LOOP ;
1429 ELSE
1430 lrsample_name := frsample_name ;
1431 END IF; -- IF frsample_name IS not NULL
1432 CLOSE get_sample_num ;
1433
1434 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'A');
1435 FETCH get_sample_num into fasample_name ; /* Get the first */
1436
1437 IF fasample_name IS not NULL THEN
1438 LOOP
1439 lasample_name := sample_name_temp ;
1440 sample_name_temp := '';
1441 FETCH get_sample_num into sample_name_temp ;
1442 EXIT WHEN get_sample_num%NOTFOUND;
1443 END LOOP ;
1444 ELSE
1445 lasample_name := fasample_name ;
1446 END IF;
1447 CLOSE get_sample_num ;
1448
1449 OPEN get_reg_sample_num (l_sampling_events.sampling_event_id, null);
1450 FETCH get_reg_sample_num into fsample_name ; /* Get the first */
1451 IF fsample_name is not NULL THEN
1452 LOOP
1453 lsample_name := sample_name_temp ;
1454 sample_name_temp := '';
1455 FETCH get_reg_sample_num into sample_name_temp ;
1456 exit when get_reg_sample_num%NOTFOUND;
1457 END LOOP ;
1458 ELSE
1459 lsample_name := fsample_name ;
1460 END IF;
1461 CLOSE get_reg_sample_num;
1462
1463 IF (lrsample_name is not NULL) THEN
1464 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES',
1465 frsample_name || '-' ||lrsample_name);
1466 ELSE
1467 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES', frsample_name);
1468 END IF;
1469
1470 IF (lasample_name is not NULL) THEN
1471 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES',
1472 fasample_name || '-' ||lasample_name);
1473 ELSE
1474 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES', fasample_name);
1475 END IF;
1476
1477 IF (lsample_name is not NULL) THEN
1478 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES',
1479 fsample_name || '-' ||lsample_name);
1480 ELSE
1481 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES', fsample_name);
1482 END IF;
1483
1484 ELSE
1485 /* Sampling Event Successfully Created. Set Form Attribute
1486 to the sampling event */
1487 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
1488 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
1489 --is getting created from the workflow notification. Based on this only we take the sample
1490 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
1491 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
1492 --then we take the default disposition ie Pending to create the samples.
1493
1494 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'"';
1495 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
1496 END IF;
1497 -- bug 4165704: not sure why this is here so I took it out?
1498 --RLNAGARA Uncommented the below line as this is necessary here because we have to show this message in the notification.
1499 l_sample_plan := FND_MESSAGE.GET();
1500 ELSE
1501 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_NONE');
1502 l_sample_plan := FND_MESSAGE.GET();
1503 l_sample_count := 1; -- incase there is no sample plan used
1504
1505 /* Sampling Event Successfully Created. Set Form Attribute
1506 to the sampling event */
1507 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
1508 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
1509 --is getting created from the workflow notification. Based on this only we take the sample
1510 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
1511 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
1512 --then we take the default disposition ie Pending to create the samples.
1513
1514 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'"';
1515 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
1516 END IF;
1517 CLOSE inv_sample_plan;
1518
1519
1520 IF ((l_auto_sample <> 'Y') or (l_doc_number <> 2))THEN
1521 /* In the case where auto sample is not enabled, create event spec disp */
1522 /* Create Spec Event Disposition Row */
1523 l_event_spec_disp.SAMPLING_EVENT_ID := l_sampling_events.sampling_event_id;
1524 l_event_spec_disp.SPEC_ID :=l_spec_id;
1525 l_event_spec_disp.SPEC_VR_ID := l_spec_vr_id;
1526 l_event_spec_disp.DISPOSITION :='1P';
1527 l_event_spec_disp.SPEC_USED_FOR_LOT_ATTRIB_IND:=NULL;
1528 l_event_spec_disp.DELETE_MARK:=0;
1529 l_event_spec_disp.CREATION_DATE:=sysdate;
1530 l_event_spec_disp.CREATED_BY:=FND_GLOBAL.USER_ID;
1531 l_event_spec_disp.LAST_UPDATE_DATE:=sysdate;
1532 l_event_spec_disp.LAST_UPDATED_BY:=FND_GLOBAL.USER_ID;
1533
1534 IF (l_debug = 'Y') THEN
1535 gmd_debug.put_line('Going to insert event spec disp');
1536 END IF;
1537
1538 IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row(
1539
1540 p_event_spec_disp =>l_event_spec_disp,
1541 x_event_spec_disp =>l_event_spec_disp_out) THEN
1542 gmd_debug.put_line ('Sampling Event disposition Creation Failed'); -- Bug # 4576699
1543 RAISE FND_API.G_EXC_ERROR;
1544 END IF; -- IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row
1545
1546 l_event_spec_disp := l_event_spec_disp_out ;
1547
1548 IF (l_debug = 'Y') THEN
1549 gmd_debug.put_line('Created event spec disp');
1550 END IF;
1551
1552 END IF ; /* End of check to create event spec for non-auto sample */
1553
1554 ELSE
1555 gmd_debug.put_line ('Could Not Find Specification'); -- Bug # 4576699
1556 --wf_log_pkg.string(6, 'Dummy','Could Not Find Specification');
1557 P_resultout:='COMPLETE:NO_WORKFLOW';
1558 return;
1559 END IF;
1560 END IF;
1561 IF not l_valid_transaction THEN
1562 gmd_debug.put_line ('No Valid Transctions') ; /* 4576699 */
1563 /* No Valid Transctions */
1564 P_resultout:='COMPLETE:NO_WORKFLOW';
1565 return;
1566 END IF;
1567
1568
1569 /* Get First Approver */
1570 /* Get application_id from FND_APPLICATION */
1571 -- Bug 4576699: Added cursor instead of 'select' statement here
1572 OPEN get_application_id; /* xxx4576699 */
1573
1574 FETCH get_application_id INTO l_application_id;
1575
1576 IF (get_application_id%NOTFOUND)
1577 THEN
1578 CLOSE get_application_id;
1579
1580 RAISE no_application_id_found;
1581 END IF;
1582 CLOSE get_application_id;
1583
1584 AME_API.CLEARALLAPPROVALS(applicationIdIn => l_application_id,
1585 transactionIdIn => l_event_key,
1586 transactionTypeIn => l_transaction_type);
1587
1588 l_log:='Approvers Cleared';
1589 AME_API.GETNEXTAPPROVER(applicationIdIn => l_application_id,
1590 transactionIdIn => l_event_key,
1591 transactionTypeIn => l_transaction_type,
1592 nextApproverOut => Approver);
1593
1594 IF(Approver.user_id is null and Approver.person_id is null) THEN
1595 gmd_debug.put_line ('No Approval Required 1'); /* 4576699 */
1596
1597 /* No Approval Required */
1598 P_resultout:='COMPLETE:NO_WORKFLOW';
1599 return;
1600 END IF;
1601
1602 IF(Approver.person_id is null) THEN
1603 OPEN get_user_name (approver.user_id); /* 4576699 */
1604
1605 FETCH get_user_name INTO l_user;
1606
1607 IF (get_user_name%NOTFOUND)
1608 THEN
1609 CLOSE get_user_name;
1610 RAISE no_user_name_found;
1611 END IF;
1612
1613 CLOSE get_user_name;
1614
1615 ELSE
1616 OPEN get_user_name (ame_util.personidtouserid (approver.person_id)); /* 4576699 */
1617 FETCH get_user_name INTO l_user;
1618
1619 IF (get_user_name%NOTFOUND)
1620 THEN
1621 CLOSE get_user_name;
1622
1623 RAISE no_user_name_found;
1624 END IF;
1625
1626 CLOSE get_user_name;
1627
1628 END IF;
1629
1630
1631 /* Set the User Attribute */
1632 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1633 aname => 'CURRENT_APPROVER',
1634 avalue => l_user);
1635 /* Set All other Attributes */
1636
1637 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1638 aname => 'APPS_FORM',
1639 avalue =>l_form );
1640
1641 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1642 aname => 'ITEM_NUMBER',
1643 avalue =>l_ITEM_NUMBER );
1644
1645 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1646 aname => 'ITEM_REVISION',
1647 avalue =>l_REVISION );
1648
1649 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1650 aname => 'ITEM_DESC',
1651 avalue =>l_item_desc );
1652
1653 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1654 aname => 'PARENT_LOT',
1655 avalue =>l_parent_lot_number );
1656
1657 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1658 aname => 'LOT_NUMBER',
1659 avalue =>l_lot_number );
1660
1661 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1662 aname => 'ORGANIZATION_CODE',
1663 avalue =>l_ORGANIZATION_CODE );
1664
1665 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1666 aname => 'SUBINVENTORY',
1667 avalue =>l_subinventory );
1668
1669 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1670 aname => 'LOCATOR',
1671 avalue =>l_locator );
1672
1673 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1674 aname => 'DOC_TYPE',
1675 avalue =>l_doc_type );
1676
1677 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1678 aname => 'DOCUMENT_NO',
1679 avalue =>l_doc_number ); -- where does doc no come from???
1680 -- Bug #3473230 (JKB) Added doc_no above.
1681 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1682 aname => 'DOC_DESC',
1683 avalue =>l_transaction_type_name );
1684
1685 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
1686 aname => 'TRANS_QTY',
1687 avalue =>l_trans_qty );
1688
1689 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
1690 aname => 'TRANS_QTY2',
1691 avalue =>l_trans_qty2 );
1692
1693 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1694 aname => 'TRANS_UOM',
1695 avalue =>l_trans_qty_uom );
1696
1697 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1698 aname => 'TRANS_UOM2',
1699 avalue =>l_trans_qty_uom2 );
1700
1701 --RLNAGARA B4905645 Changed SETITEMATTRDATE with SETITEMATTRTEXT for retest and expiry dates.
1702 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1703 aname => 'RETEST_DATE',
1704 avalue =>l_retest_date );
1705
1706 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1707 aname => 'EXPIRY_DATE',
1708 avalue =>l_expiry_date );
1709
1710 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1711 aname => 'PURCHASE_NO',
1712 avalue =>l_purchase_no );
1713
1714 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1715 aname => 'SUPPLIER_NO',
1716 avalue =>l_vendor_no );
1717
1718 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1719 aname => 'RECEIPT_NO',
1720 avalue =>l_receipt_no );
1721
1722 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1723 aname => 'SAMPLING_PLAN',
1724 avalue =>l_sample_plan );
1725
1726 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1727 aname => '#FROM_ROLE',
1728 avalue =>l_from_role );
1729
1730 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1731 aname => 'AME_TRANS',
1732 avalue =>l_transaction_type );
1733
1734 --RLNAGARA B5018797 Added this as Operting Unit is added to the Notification also.
1735
1736 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1737 aname => 'OPERATING_UNIT',
1738 avalue =>l_operating_unit );
1739
1740 --RLNAGARA Bug5334308
1741 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
1742 aname => 'LOT_QTY',
1743 avalue =>l_lot_qty );
1744
1745 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
1746 aname => 'LOT_QTY2',
1747 avalue =>l_lot_qty2 );
1748
1749 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1750 aname => 'LOT_UOM',
1751 avalue =>l_trans_qty_uom );
1752
1753 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1754 aname => 'LOT_UOM2',
1755 avalue =>l_trans_qty_uom2 );
1756
1757
1758
1759 /* As this a pure FYI notification we will set the approver to approve status */
1760 Approver.approval_status := AME_UTIL.APPROVEDSTATUS;
1761 AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
1762 transactionIdIn => l_event_key,
1763 approverIn => Approver,
1764 transactionTypeIn => l_transaction_type,
1765 forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);
1766 END IF;
1767
1768 P_resultout:='COMPLETE:'||l_transaction_type;
1769
1770 -- Bug 4576699: added Exceptions besides OTHERS
1771 EXCEPTION
1772 WHEN no_vendor_found
1773 THEN
1774 gmd_debug.put_line ('Vendor Number not found');
1775 WHEN no_application_id_found
1776 THEN
1777 gmd_debug.put_line ('Application Id not found');
1778 WHEN no_user_name_found
1779 THEN
1780 gmd_debug.put_line ('User Name not found');
1781 WHEN OTHERS
1782 THEN
1783 wf_core.CONTEXT ('GMD_QMSMC',
1784 'VERIFY_EVENT',
1785 p_itemtype,
1786 p_itemkey,
1787 l_log
1788 );
1789 RAISE;
1790 END VERIFY_EVENT;
1791
1792
1793 /* procedure check next approver */
1794 PROCEDURE CHECK_NEXT_APPROVER(
1795 p_itemtype IN VARCHAR2,
1796 p_itemkey IN VARCHAR2,
1797 p_actid IN NUMBER,
1798 p_funcmode IN VARCHAR2,
1799 p_resultout OUT NOCOPY VARCHAR2)
1800
1801 IS
1802 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
1803 itemtype=>p_itemtype,
1804 itemkey=>P_itemkey,
1805 aname=>'EVENT_NAME');
1806
1807 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
1808 itemtype=>p_itemtype,
1809 itemkey=>P_itemkey,
1810 aname=>'EVENT_KEY');
1811
1812 l_current_approver varchar2(240);
1813
1814 l_application_id number;
1815 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
1816 itemtype=>p_itemtype,
1817 itemkey=>P_itemkey,
1818 aname=>'AME_TRANS');
1819 l_user varchar2(32);
1820 Approver AME_UTIL.APPROVERRECORD;
1821 l_ITEM_NUMBER varchar2(240);
1822 l_item_desc varchar2(240);
1823 l_lot_number varchar2(240);
1824 l_parent_lot_number varchar2(240);
1825 l_sample_no varchar2(240);
1826 l_sample_plan varchar2(240);
1827 l_sample_disposition varchar2(240);
1828 l_sample_source varchar2(240);
1829 l_specification varchar2(240);
1830 l_validity_rule varchar2(240);
1831 l_validity_rule_version varchar2(240);
1832 l_sample_event_text varchar2(4000);
1833 l_sampling_event_id number;
1834 l_form varchar2(240);
1835 l_log varchar2(4000);
1836 BEGIN
1837
1838 /* Get Next Approver */
1839 /* Get application_id from FND_APPLICATION */
1840 select application_id into l_application_id
1841 from fnd_application where application_short_name='GMD';
1842
1843 AME_API.GETNEXTAPPROVER(applicationIdIn => l_application_id,
1844 transactionIdIn => l_event_key,
1845 transactionTypeIn => l_transaction_type,
1846 nextApproverOut => Approver);
1847
1848 IF(Approver.user_id is null and Approver.person_id is null) THEN
1849 /* No Approval Required */
1850 P_resultout:='COMPLETE:N';
1851 ELSE
1852 IF(Approver.person_id is null) THEN
1853 select user_name into l_user from fnd_user
1854 where user_id=Approver.user_id;
1855 ELSE
1856 select user_name into l_user from fnd_user
1857 where
1858 user_id=AME_UTIL.PERSONIDTOUSERID(Approver.person_id);
1859 END IF;
1860
1861
1862 /* Set the User Attribute */
1863
1864 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
1865 aname => 'CURRENT_APPROVER',
1866 avalue => l_user);
1867
1868 P_resultout:='COMPLETE:Y';
1869 Approver.approval_status := AME_UTIL.APPROVEDSTATUS;
1870 AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
1871 transactionIdIn => l_event_key,
1872 approverIn => Approver,
1873 transactionTypeIn => l_transaction_type,
1874 forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);
1875 END IF;
1876
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 WF_CORE.CONTEXT ('GMD_QMSMC','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,l_log );
1880 raise;
1881
1882 END CHECK_NEXT_APPROVER;
1883
1884
1885 /* Production Procedure */
1886 PROCEDURE PRODUCTION(
1887 p_itemtype IN VARCHAR2,
1888 p_itemkey IN VARCHAR2,
1889 p_actid IN NUMBER,
1890 p_funcmode IN VARCHAR2,
1891 p_resultout OUT NOCOPY VARCHAR2)
1892
1893 IS
1894 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT( itemtype => p_itemtype,
1895 itemkey => P_itemkey,
1896 aname => 'EVENT_NAME');
1897
1898 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT( itemtype => p_itemtype,
1899 itemkey => P_itemkey,
1900 aname => 'EVENT_KEY');
1901
1902 Approver AME_UTIL.APPROVERRECORD;
1903 L_WIP_SPEC GMD_SPEC_MATCH_GRP.WIP_SPEC_REC_TYPE;
1904 L_SAMPLING_EVENTS GMD_SAMPLING_EVENTS%ROWTYPE;
1905 l_sampling_events_out GMD_SAMPLING_EVENTS%ROWTYPE;
1906 l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1907 l_event_spec_disp_out GMD_EVENT_SPEC_DISP%ROWTYPE;
1908 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
1909
1910
1911 l_current_approver varchar2(240);
1912 l_ITEM_NUMBER varchar2(240);
1913 l_item_desc varchar2(240);
1914 l_lot_number varchar2(240);
1915 l_parent_lot_number varchar2(240);
1916 L_RECIPE_NO varchar2(240);
1917 L_FORMULA_NO varchar2(240);
1918 L_ROUTING_NO varchar2(240);
1919 L_OPRN_NO varchar2(240);
1920 L_FORM varchar2(240);
1921 l_itemtype varchar2(240);
1922 l_itemkey varchar2(240);
1923 l_workflow_process varchar2(240);
1924 X_batch_id varchar2(240);
1925 X_batch_step_id varchar2(240);
1926 t_batch_id varchar2(240);
1927 t_batch_step_id varchar2(240);
1928 l_sample_count varchar2(240);
1929 l_sample_qty varchar2(240);
1930 l_sample_qty_uom varchar2(240);
1931 l_frequency_count varchar2(240);
1932 l_frequency_per varchar2(240);
1933 l_frequency_type varchar2(240);
1934 message varchar2(200);
1935 l_reserve_cnt_req varchar2(240);
1936 l_reserve_qty varchar2(240);
1937 l_archive_cnt_req varchar2(240);
1938 l_archive_qty varchar2(240);
1939 l_revision varchar2(240);
1940 l_spec_name varchar2(240); -- RLNAGARA Bug 5032406 (FP of 4604305 ME)
1941
1942 L_BATCH_NO varchar2(32);
1943 L_EXACT_MATCH varchar2(1);
1944 L_RETURN_STATUS varchar2(100);
1945
1946 l_log varchar2(4000);
1947 l_sample_plan varchar2(4000) ;
1948 l_sample_plan_out varchar2(4000) ;
1949 L_Msg_DATA varchar2(2000);
1950
1951
1952 l_auto_sample varchar2(1);
1953 create_status varchar2(20);
1954
1955
1956 sample_name_temp varchar2(100) := '';
1957 fsample_name varchar2(500) := '';
1958 frsample_name varchar2(500) := '';
1959 fasample_name varchar2(500) := '';
1960 lsample_name varchar2(500) := '';
1961 lrsample_name varchar2(500) := '';
1962 lasample_name varchar2(500) := '';
1963 l_from_role varchar2(500);
1964
1965 L_ORGANIZATION_CODE varchar2(240):=NULL;
1966 l_subinventory varchar2(240):=NULL;
1967 l_locator varchar2(240):=NULL;
1968
1969 L_GRADE varchar2(150);
1970 l_transaction_type varchar2(100);
1971 l_user varchar2(32);
1972
1973 l_sampling_event_id number;
1974 l_application_id number;
1975 l_locator_id number;
1976 L_inventory_ITEM_ID number;
1977 L_ORGANIZATION_ID number;
1978 L_BATCH_ID number;
1979 L_RECIPE_ID number;
1980 L_FORMULA_ID number;
1981 L_FORMULALINE_ID number;
1982 L_MATERIAL_DETAIL_ID number;
1983 L_STEP_ID number;
1984 L_STEP_NO number;
1985 L_OPRN_ID number;
1986 L_ROUTING_ID number;
1987
1988 L_FORMULA_VERS number(5);
1989 L_ROUTING_VERS number(5);
1990 L_RECIPE_VERSION number(5);
1991 L_OPRN_VERS number(5);
1992 L_CHARGE number;
1993 L_SPEC_ID number;
1994 L_SPEC_VR_ID number;
1995 l_sample_plan_id number;
1996 x_temp number;
1997 dummy number;
1998 l_last_update_by number ;
1999 l_doc_number number;
2000 l_spec_vers number; -- RLNAGARA Bug 5032406 (FP of 4604305 ME)
2001 l_sample_plan_exists number := 0;
2002
2003 L_DATE_EFFECTIVE DATE;
2004 l_orgn_found BOOLEAN;
2005 b NUMBER; --RLNAGARA Bug 5910300
2006
2007 CURSOR wip_sample_plan (X_SPEC_VR_ID NUMBER) IS
2008 SELECT nvl(sample_cnt_req, 0) sample_cnt_req,
2009 nvl(sample_qty, 0) sample_qty,
2010 sample_qty_uom,
2011 frequency_cnt,
2012 frequency_per,
2013 sm.sampling_plan_id ,
2014 frequency_type,
2015 nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
2016 nvl(RESERVE_QTY, 0) reserve_qty,
2017 nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
2018 nvl(ARCHIVE_QTY,0) archive_qty
2019 FROM gmd_sampling_plans_b sm,
2020 gmd_wip_spec_vrs sv
2021 WHERE sv.sampling_plan_id = sm.sampling_plan_id
2022 AND sv.spec_vr_id = X_SPEC_VR_ID;
2023
2024 CURSOR wip_sample_plan_freq_per (x_frequency_per varchar2 ) IS
2025 SELECT meaning
2026 FROM gem_lookups
2027 WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
2028 AND lookup_code = x_frequency_per ;
2029
2030 CURSOR get_sampling_plan_id (x_spec_vr_id_in number) IS
2031 SELECT sampling_plan_id
2032 FROM gmd_com_spec_vrs_vl --gmd_all_spec_vrs performance bug# 4916912
2033 WHERE spec_vr_id = x_spec_vr_id_in ;
2034
2035 -- Bug 5391632: added cursor below to get locator
2036 CURSOR Cur_locator(loc_id NUMBER) IS
2037 SELECT concatenated_segments
2038 FROM mtl_item_locations_kfv
2039 WHERE inventory_location_id = loc_id;
2040
2041
2042 /* RLNAGARA Bug 5032406 (FP of 4604305 ME) added cursor to get spec name and version */
2043 CURSOR get_spec_name(x_spec_id_in NUMBER) IS
2044 SELECT spec_name,spec_vers
2045 FROM gmd_specifications_b
2046 WHERE spec_id = x_spec_id_in ;
2047
2048
2049
2050 /* This cursor works for both batch created and batchstep created event */
2051 -- Bug 4165704: 1. a.wip_subinventory taken out since it no longer exists in gme_batch_header
2052 -- 2. a.revision added
2053 CURSOR C1(x_batch_id varchar2,x_batch_step_id varchar2) is
2054 SELECT A.BATCH_NO,
2055 A.BATCH_ID,
2056 A.ORGANIZATION_ID,
2057 P.ORGANIZATION_CODE,
2058 A.ROUTING_ID,
2059 C.RECIPE_ID,
2060 A.FORMULA_ID,
2061 A.ROUTING_ID,
2062 F.INVENTORY_ITEM_ID,
2063 F.REVISION,
2064 F.FORMULALINE_ID,
2065 F.MATERIAL_DETAIL_ID,
2066 F.LOCATOR_ID, --RLNAGARA B5389806
2067 F.SUBINVENTORY, --RLNAGARA B5389806
2068 to_number(NULL) BATCHSTEP_ID,
2069 to_number(NULL) BATCHSTEP_NO,
2070 to_number(NULL) OPRN_ID,
2071 C.RECIPE_NO||' / '||C.RECIPE_VERSION,
2072 H.FORMULA_NO||' / '||FORMULA_VERS,
2073 I.CONCATENATED_SEGMENTS ,
2074 I.DESCRIPTION ITEM_DESC1,
2075 A.LAST_UPDATED_BY
2076 FROM GME_BATCH_HEADER A,
2077 GMD_RECIPE_VALIDITY_RULES b,
2078 GMD_RECIPES_B C, --GMD_RECIPES C performance bug# 4916912
2079 GME_MATERIAL_DETAILS F,
2080 FM_FORM_MST_B H, --FM_FORM_MST H performance bug# 4916912
2081 MTL_PARAMETERS P,
2082 MTL_SYSTEM_ITEMS_B_KFV I
2083 WHERE
2084 A.BATCH_ID = x_batch_id AND
2085 A.BATCH_ID = F.BATCH_ID AND
2086 A.RECIPE_VALIDITY_RULE_ID = B.RECIPE_VALIDITY_RULE_ID AND
2087 B.RECIPE_ID = C.RECIPE_ID AND
2088 NVL(x_batch_step_id,1) = 1 AND
2089 F.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND
2090 H.FORMULA_ID = A.FORMULA_ID AND
2091 F.LINE_TYPE = 1 /*Bug#6638743*/ AND
2092 P.ORGANIZATION_ID = A.ORGANIZATION_ID AND
2093 (B.ORGANIZATION_ID = A.ORGANIZATION_ID OR B.ORGANIZATION_ID IS NULL) AND -- PK Bug 6595576
2094 I.ORGANIZATION_ID = A.ORGANIZATION_ID AND
2095 F.ORGANIZATION_ID = A.ORGANIZATION_ID
2096 UNION
2097 SELECT A.BATCH_NO,
2098 A.BATCH_ID,
2099 A.ORGANIZATION_ID,
2100 P.ORGANIZATION_CODE,
2101 A.ROUTING_ID,
2102 C.RECIPE_ID,
2103 A.FORMULA_ID,
2104 A.ROUTING_ID,
2105 F.INVENTORY_ITEM_ID,
2106 F.REVISION,
2107 F.FORMULALINE_ID,
2108 F.MATERIAL_DETAIL_ID,
2109 F.LOCATOR_ID, --RLNAGARA B5389806
2110 F.SUBINVENTORY, --RLNAGARA B5389806
2111 D.BATCHSTEP_ID,
2112 D.BATCHSTEP_NO BATCHSTEP_NO,
2113 D.OPRN_ID,
2114 C.RECIPE_NO||' / '||C.RECIPE_VERSION,
2115 H.FORMULA_NO||' / '||FORMULA_VERS,
2116 I.CONCATENATED_SEGMENTS ITEM_NUMBER ,
2117 I.DESCRIPTION,
2118 A.LAST_UPDATED_BY
2119 FROM GME_BATCH_HEADER A,
2120 GMD_RECIPE_VALIDITY_RULES b,
2121 GMD_RECIPES_B C, --GMD_RECIPES C performance bug# 4916912
2122 GME_BATCH_STEPS D,
2123 GME_MATERIAL_DETAILS F,
2124 FM_FORM_MST_B H, --FM_FORM_MST H performance bug# 4916912
2125 MTL_PARAMETERS P,
2126 MTL_SYSTEM_ITEMS_B_KFV I
2127 WHERE
2128 A.BATCH_ID =x_batch_id AND
2129 A.BATCH_ID =F.BATCH_ID AND
2130 A.BATCH_ID =D.BATCH_ID AND
2131 A.RECIPE_VALIDITY_RULE_ID=B.RECIPE_VALIDITY_RULE_ID AND
2132 B.RECIPE_ID =C.RECIPE_ID AND
2133 D.BATCHSTEP_ID =x_batch_step_id AND --RLNAGARA Bug 5032406 (FP of 4604305 ME) For batch creation, do not select steps
2134 F.INVENTORY_ITEM_ID =I.INVENTORY_ITEM_ID AND
2135 H.FORMULA_ID =A.FORMULA_ID AND
2136 P.ORGANIZATION_ID = A.ORGANIZATION_ID AND
2137 F.ORGANIZATION_ID = A.ORGANIZATION_ID AND
2138 (B.ORGANIZATION_ID = A.ORGANIZATION_ID OR B.ORGANIZATION_ID IS NULL) AND -- PK Bug 6595576
2139 I.ORGANIZATION_ID = A.ORGANIZATION_ID AND
2140 F.LINE_TYPE = 1 /*Bug#6638743*/
2141 ORDER by BATCHSTEP_NO DESC;
2142
2143 /* Cursors to check if Spec VR has auto enable flag enabled */
2144 CURSOR wip_auto_sample ( X_SPEC_VR_ID number) is
2145 select nvl(auto_sample_ind,'N')
2146 from GMD_WIP_SPEC_VRS
2147 where spec_vr_id = X_SPEC_VR_ID ;
2148
2149
2150 /* Given a sampling event and a retain as, gets the sample numbers */
2151 CURSOR get_sample_num (x_sampling_event_in number , x_retain_as varchar2) is
2152 select sample_no
2153 from gmd_Samples
2154 where sampling_event_id = x_Sampling_event_in
2155 and retain_as = x_retain_as ;
2156
2157 CURSOR get_reg_sample_num (x_sampling_event_in number , x_retain_as_in varchar2) is
2158 select sample_no
2159 from gmd_Samples
2160 where sampling_event_id = x_Sampling_event_in
2161 and retain_as is NULL ;
2162
2163 CURSOR get_from_role is
2164 select nvl( text, '')
2165 from wf_Resources
2166 where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
2167 and language = userenv('LANG') ;
2168
2169
2170 BEGIN
2171 --gml_sf_log('start proc PRODUCTION and event='||l_event_name);
2172
2173 IF (l_debug = 'Y') THEN
2174 gmd_debug.put_line('Event name ' || l_event_name);
2175 gmd_debug.put_line('Event key ' || l_Event_key);
2176 END IF;
2177
2178 OPEN get_from_role ;
2179 FETCH get_from_role into l_from_role ;
2180 CLOSE get_from_role ;
2181
2182 IF P_FUNCMODE='RUN' THEN
2183 /* Get application_id from FND_APPLICATION */
2184 SELECT application_id INTO l_application_id
2185 FROM fnd_application
2186 WHERE application_short_name='GMD';
2187
2188 /*************************************/
2189 /* Check which event has been raised */
2190 /*************************************/
2191 -- wf_log_pkg.string( 6,
2192 -- 'Dummy',
2193 -- 'Entered Production Transaction with event_key '||l_event_key
2194 -- );
2195
2196 IF l_event_name = 'oracle.apps.gme.batch.created' THEN
2197 l_transaction_type :='PRODUCTION_TRANSACTION';
2198 t_batch_id :=l_Event_key;
2199 t_batch_step_id :=NULL;
2200
2201 ELSIF l_event_name = 'oracle.apps.gme.batchstep.created' THEN
2202 SELECT BATCH_ID INTO t_batch_id
2203 FROM gme_batch_steps
2204 WHERE batchstep_id=l_event_key;
2205
2206 l_transaction_type :='PRODUCTION_TRANSACTION';
2207 t_batch_step_id :=l_event_key;
2208 l_Event_key :=t_batch_id;
2209
2210 ELSIF l_event_name = 'oracle.apps.gme.bstep.rel.wf' THEN
2211 SELECT BATCH_ID INTO t_batch_id
2212 FROM gme_batch_steps
2213 WHERE batchstep_id=l_event_key;
2214
2215 l_transaction_type :='BATCHRELEASE_TRANSACTION';
2216 t_batch_step_id :=l_event_key;
2217 l_Event_key :=t_batch_id;
2218 END IF;
2219
2220
2221 /*Figure out if all the batch_steps are covered for sample creation */
2222 OPEN C1(t_batch_id,t_batch_step_id);
2223 LOOP
2224 FETCH C1 INTO L_BATCH_NO,
2225 L_BATCH_ID,
2226 L_ORGANIZATION_ID,
2227 L_ORGANIZATION_CODE,
2228 L_ROUTING_ID,
2229 L_RECIPE_ID,
2230 L_FORMULA_ID,
2231 L_ROUTING_ID,
2232 L_INVENTORY_ITEM_ID,
2233 L_REVISION,
2234 L_FORMULALINE_ID,
2235 L_MATERIAL_DETAIL_ID,
2236 l_locator_id, --RLNAGARA B5389806
2237 l_subinventory, --RLNAGARA B5389806
2238 L_STEP_ID,
2239 L_STEP_NO,
2240 L_OPRN_ID,
2241 L_RECIPE_NO,
2242 L_FORMULA_NO,
2243 L_ITEM_NUMBER,
2244 L_ITEM_DESC,
2245 L_LAST_UPDATE_BY;
2246
2247 EXIT WHEN C1%NOTFOUND;
2248
2249 /* Material Details found Proceed for Finding the spec */
2250 l_wip_spec.organization_id := L_organization_id;
2251 l_wip_spec.inventory_item_id := L_inventory_item_id;
2252 l_wip_spec.revision := L_revision;
2253 l_wip_spec.batch_id := L_batch_id;
2254 l_wip_spec.recipe_id := L_recipe_id;
2255 l_wip_spec.formula_id := L_formula_id;
2256 l_wip_spec.routing_id := L_routing_id;
2257 l_wip_spec.step_id := L_step_id;
2258 l_wip_spec.step_no := L_step_no;
2259 l_wip_spec.oprn_id := L_oprn_id;
2260 l_wip_spec.charge := NULL;
2261 l_wip_spec.date_effective := SYSDATE;
2262 l_wip_spec.exact_match := 'N';
2263
2264 -- bug 4640143: if batch id exists then material detail id is used
2265 IF l_batch_id IS NOT NULL THEN
2266 l_wip_spec.material_detail_id := L_material_detail_id;
2267 ELSE
2268 l_wip_spec.formulaline_id := L_formulaline_id;
2269 END IF;
2270
2271 /* Bug No.7032231 - Commented the following code as it is not supporting to get spec details */
2272
2273 /* IF l_Event_name in ('oracle.apps.gme.batchstep.created',
2274 'oracle.apps.gme.bstep.rel.wf') THEN
2275 l_wip_spec.find_spec_with_step := 'Y';
2276 ELSE
2277 l_wip_spec.find_spec_with_step := 'N';
2278 END IF; */
2279
2280
2281 IF (l_debug = 'Y') THEN
2282 gmd_debug.put_line('Checking for WIP Spec ');
2283 END IF;
2284
2285 IF GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC(
2286 p_wip_spec_rec => l_wip_spec,
2287 x_spec_id => l_spec_id,
2288 x_spec_vr_id => l_spec_vr_id,
2289 x_return_status => l_return_status,
2290 x_message_data => l_msg_data) THEN
2291
2292 IF (l_debug = 'Y') THEN
2293 gmd_debug.put_line('WIP Spec Found: '||l_Spec_id||' '||l_spec_vr_id);
2294 END IF;
2295
2296 /* RLNAGARA Bug 5032406 (FP of 4604305 ME) Assigning values to l_spec_name and l_spec_vers */
2297 OPEN get_spec_name(l_spec_id);
2298 FETCH get_spec_name INTO l_spec_name, l_spec_vers;
2299 CLOSE get_spec_name;
2300
2301 -- 5391632
2302 IF l_locator_id IS NOT NULL THEN
2303 OPEN Cur_locator(l_locator_id);
2304 FETCH Cur_locator INTO l_locator;
2305 CLOSE Cur_locator;
2306 END IF; -- l_locator_id is not null
2307
2308
2309 OPEN get_sampling_plan_id (l_spec_vr_id);
2310 FETCH get_sampling_plan_id into l_sample_plan_id ;
2311 CLOSE get_sampling_plan_id ;
2312
2313 OPEN wip_sample_plan(l_spec_vr_id);
2314 FETCH wip_sample_plan into l_sample_count,
2315 l_sample_qty,
2316 l_sample_qty_uom,
2317 l_frequency_count,
2318 l_frequency_per ,
2319 l_sample_plan_id ,
2320 l_frequency_type,
2321 l_reserve_cnt_req,
2322 l_reserve_qty,
2323 l_archive_cnt_req,
2324 l_archive_qty;
2325
2326 IF wip_sample_plan%found THEN
2327 -- 4165704: added l_sample_plan_exists here so I could uncomment 'CLOSE wip_sample_plan' below
2328 l_sample_plan_exists := 1;
2329 ELSE
2330 l_sample_count := 1;
2331 END IF ;
2332
2333 CLOSE wip_sample_plan;
2334
2335 /*****************************/
2336 /* Specification Found */
2337 /* Create the Sampling Event */
2338 /*****************************/
2339 --RLNAGARA B5389806 Added the below IF-ELSE-ENDIF condition.
2340 IF l_Event_name in ('oracle.apps.gme.batchstep.created',
2341 'oracle.apps.gme.batch.created') THEN
2342 l_sampling_events.disposition := '0PL';
2343 ELSE
2344 l_sampling_events.disposition := '1P';
2345 END IF;
2346
2347 l_sampling_events.original_spec_vr_id := l_spec_vr_id;
2348 l_sampling_events.sampling_plan_id := l_sample_plan_id;
2349 l_sampling_events.source := 'W';
2350 l_sampling_events.inventory_item_id := l_inventory_item_id;
2351 l_sampling_events.revision := l_revision ;
2352 l_sampling_events.sample_req_cnt := l_sample_count;
2353 l_sampling_events.sample_taken_cnt := 0;
2354 l_sampling_events.batch_ID := l_batch_id;
2355 l_sampling_events.recipe_id := l_recipe_id; /*Bug 3378697*/
2356 l_sampling_events.formula_id := l_formula_id;
2357 l_sampling_events.formulaline_id := l_formulaline_id;
2358 l_sampling_events.material_detail_id := l_material_detail_id; -- Bug 4640143 added this
2359 l_sampling_events.routing_id := l_routing_id;
2360 l_sampling_events.subinventory := l_subinventory;
2361 l_sampling_events.locator_id := l_locator_id;
2362
2363 l_sampling_events.step_id := l_step_id;
2364 l_sampling_events.step_no := l_step_no;
2365 l_sampling_events.oprn_id := l_oprn_id;
2366 l_sampling_events.sample_type := 'I';
2367 l_sampling_events.organization_id := L_organization_id;
2368 l_sampling_events.CREATION_DATE := SYSDATE;
2369 l_sampling_events.CREATED_BY := FND_GLOBAL.USER_ID;
2370 l_sampling_events.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2371 l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
2372 --wf_log_pkg.string(6, 'Dummy','Before Creating the Sampling Event');
2373
2374 IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
2375 p_sampling_events => l_sampling_events,
2376 x_sampling_events => l_sampling_events_out) THEN
2377 RAISE FND_API.G_EXC_ERROR;
2378 END IF;
2379
2380 l_sampling_events := l_sampling_events_out ;
2381
2382 /* Check to see if auto Sample Creation is enabled for this Spec VR */
2383 OPEN wip_auto_sample (l_spec_vr_id);
2384 FETCH wip_auto_sample into l_auto_Sample;
2385 CLOSE wip_auto_sample;
2386
2387 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
2388 p_organization_id => l_organization_id
2389 , x_quality_parameters => quality_config
2390 , x_return_status => l_return_status
2391 , x_orgn_found => l_orgn_found );
2392
2393 IF (l_return_status <> 'S') THEN
2394 RAISE FND_API.G_EXC_ERROR;
2395 END IF;
2396
2397 l_doc_number := quality_config.sample_assignment_type;
2398 IF (l_debug = 'Y') THEN
2399 gmd_debug.put_line('Auto sample flag: ' ||l_auto_sample);
2400 END IF;
2401
2402 -- Bug 4165704: took out reference to wip_sample_plan cursor and put in l_sample_plan_exists
2403 IF l_sample_plan_exists = 1 THEN
2404 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_INFO');
2405 FND_MESSAGE.SET_TOKEN('SAMPLE_NO', l_sample_count);
2406 FND_MESSAGE.SET_TOKEN('SAMPLE_QTY', l_sample_qty);
2407 FND_MESSAGE.SET_TOKEN('SAMPLE_UOM', l_sample_qty_uom); --RLNAGARA B4905670
2408 FND_MESSAGE.SET_TOKEN('FREQ_CNT',l_frequency_count);
2409 FND_MESSAGE.SET_TOKEN('ASAMPLE_NO', l_archive_cnt_req);
2410 FND_MESSAGE.SET_TOKEN('ASAMPLE_QTY', l_archive_qty);
2411 FND_MESSAGE.SET_TOKEN('RSAMPLE_NO', l_reserve_cnt_req);
2412 FND_MESSAGE.SET_TOKEN('RSAMPLE_QTY', l_reserve_qty);
2413 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES', frsample_name);
2414 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES', fasample_name);
2415 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES', fsample_name);
2416
2417 --RLNAGARA Bug 5910300 start
2418 IF (l_frequency_type = 'F') THEN
2419 UPDATE GMD_WIP_SPEC_VRS
2420 SET BATCH_FREQUENCY = NVL(BATCH_FREQUENCY,0) + 1 --RLNAGARA Bug 5910300 Rework Added NVL
2421 WHERE SPEC_VR_ID = l_spec_vr_id;
2422 select batch_frequency into b from gmd_wip_spec_vrs
2423 where spec_vr_id = l_spec_vr_id;
2424
2425 IF b <> l_frequency_count THEN
2426 p_resultout := 'COMPLETE:';
2427 RETURN;
2428 ELSE
2429 UPDATE GMD_WIP_SPEC_VRS
2430 SET BATCH_FREQUENCY = 0
2431 WHERE SPEC_VR_ID = l_spec_vr_id;
2432 l_sampling_events.sample_req_cnt := l_sample_count;
2433 END IF;
2434 --RLNAGARA Bug 5910300 end
2435 ELSIF (l_frequency_type <> 'Q') THEN
2436 OPEN wip_sample_plan_freq_per (l_frequency_per) ;
2437 FETCH wip_sample_plan_freq_per into l_frequency_per ;
2438 CLOSE wip_sample_plan_freq_per ;
2439 END IF;
2440
2441 FND_MESSAGE.SET_TOKEN('FREQ_PER',l_frequency_per);
2442
2443 /* Check to see if auto sample creation is enabled */
2444 IF ((l_auto_sample = 'Y') and (l_doc_number = 2)) THEN
2445 GMD_AUTO_SAMPLE_PKG.create_samples(l_sampling_events,
2446 l_spec_id,
2447 l_spec_vr_id,
2448 create_status );
2449 /* Sampling Event Successfully Created. Set Form Attribute
2450 to the sampling event */
2451 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="'
2452 || l_sampling_events.sampling_event_id||'"';
2453
2454 /* If we created samples, show them */
2455 /* get the reserve sample names*/
2456 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'R');
2457 FETCH get_sample_num into frsample_name ; /* Get the first */
2458
2459 IF frsample_name is not NULL THEN
2460 LOOP
2461 lrsample_name := sample_name_temp ;
2462 sample_name_temp := '';
2463 FETCH get_sample_num into sample_name_temp ;
2464 EXIT WHEN get_sample_num%NOTFOUND;
2465 END LOOP ;
2466 ELSE
2467 lrsample_name := frsample_name ;
2468 END IF;
2469 CLOSE get_sample_num ;
2470
2471 /* get the archive sample names*/
2472 OPEN get_sample_num (l_sampling_events.sampling_event_id, 'A');
2473 FETCH get_sample_num into fasample_name ; /* Get the first */
2474
2475 IF fasample_name is not NULL THEN
2476 LOOP
2477 lasample_name := sample_name_temp ;
2478 sample_name_temp := '';
2479 FETCH get_sample_num into sample_name_temp ;
2480 EXIT WHEN get_sample_num%NOTFOUND;
2481 END LOOP ;
2482 ELSE
2483 lasample_name := fasample_name ;
2484 END IF;
2485 CLOSE get_sample_num ;
2486
2487 /* get the sample names*/
2488 OPEN get_reg_sample_num (l_sampling_events.sampling_event_id, null);
2489 FETCH get_reg_sample_num into fsample_name ; /* Get the first */
2490
2491 IF fsample_name is not NULL THEN
2492 LOOP
2493 lsample_name := sample_name_temp ;
2494 sample_name_temp := '';
2495 FETCH get_reg_sample_num into sample_name_temp ;
2496 exit when get_reg_sample_num%NOTFOUND;
2497 END LOOP ;
2498 ELSE
2499 lsample_name := fsample_name ;
2500 END IF;
2501 CLOSE get_reg_sample_num;
2502
2503 IF (lrsample_name is not NULL) THEN
2504 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES',
2505 frsample_name || '-' ||lrsample_name);
2506 ELSE
2507 FND_MESSAGE.SET_TOKEN('RESERVE_SAMPLES', frsample_name);
2508 END IF ;
2509
2510 IF (lasample_name is not NULL) THEN
2511 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES',
2512 fasample_name || '-' ||lasample_name);
2513 ELSE
2514 FND_MESSAGE.SET_TOKEN('ARCHIVE_SAMPLES', fasample_name);
2515 END IF ;
2516
2517 IF (lsample_name is not NULL) THEN
2518 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES',
2519 fsample_name || '-' ||lsample_name);
2520 ELSE
2521 FND_MESSAGE.SET_TOKEN('RGULAR_SAMPLES', fsample_name);
2522 END IF ;
2523
2524 ELSE
2525 /* Sampling Event Successfully Created. Set Form Attribute
2526 to the sampling event */
2527 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
2528 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
2529 --is getting created from the workflow notification. Based on this only we take the sample
2530 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
2531 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
2532 --then we take the default disposition ie Pending to create the samples.
2533
2534 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'"';
2535 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
2536 END IF ;
2537 l_sample_plan := FND_MESSAGE.GET();
2538
2539 ELSE
2540 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLE_PLAN_NONE');
2541 l_sample_plan := FND_MESSAGE.GET();
2542
2543 l_sample_count := 1; -- just in case
2544 --RLNAGARA B5389806 Only passing sampling_event_id is not enough to create samples.
2545 --Here we need to pass another parameter called WF_SAMPLE which informs that the sample
2546 --is getting created from the workflow notification. Based on this only we take the sample
2547 --disposition as the disposition in the sampling events table otherwise if the WF_SAMPLE
2548 --is "N"(or NULL) (this will happen when we call samples form from other forms to create samples)
2549 --then we take the default disposition ie Pending to create the samples.
2550
2551 --l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'||l_sampling_events.sampling_event_id||'"';
2552 l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'|| l_sampling_events.sampling_event_id||'" WF_SAMPLE="Y"';
2553 END IF;
2554
2555 IF ((l_auto_sample <> 'Y') or (l_doc_number <> 2))THEN
2556 l_event_spec_disp.SAMPLING_EVENT_ID :=
2557 l_sampling_events.sampling_event_id;
2558 l_event_spec_disp.SPEC_ID :=l_spec_id;
2559 l_event_spec_disp.SPEC_VR_ID := l_spec_vr_id;
2560 l_event_spec_disp.DISPOSITION :='1P';
2561 l_event_spec_disp.SPEC_USED_FOR_LOT_ATTRIB_IND:=NULL;
2562 l_event_spec_disp.DELETE_MARK :=0;
2563 l_event_spec_disp.CREATION_DATE :=sysdate;
2564 l_event_spec_disp.CREATED_BY :=FND_GLOBAL.USER_ID;
2565 l_event_spec_disp.LAST_UPDATE_DATE :=sysdate;
2566 l_event_spec_disp.LAST_UPDATED_BY :=FND_GLOBAL.USER_ID;
2567
2568 IF (l_debug = 'Y') THEN
2569 gmd_debug.put_line('Going to insert event spec disp');
2570 END IF;
2571
2572 IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row(
2573 p_event_spec_disp =>l_event_spec_disp,
2574 x_event_spec_disp =>l_event_spec_disp_out) THEN
2575
2576 RAISE FND_API.G_EXC_ERROR;
2577 END IF;
2578 l_event_spec_disp := l_event_spec_disp_out ;
2579
2580 IF (l_debug = 'Y') THEN
2581 gmd_debug.put_line('Created event spec disp');
2582 END IF;
2583 END IF ; /* End of check to create event spec for non-auto sample */
2584
2585
2586 IF l_step_id is NOT NULL THEN
2587 fnd_global.apps_initialize( USER_ID => l_last_update_by ,
2588 resp_id =>NULL,
2589 resp_appl_id =>NULL);
2590
2591 fnd_profile.initialize(l_last_update_by );
2592
2593 -- Bug 4165704: GME added p_org_id to parameter list
2594 gme_api_grp.update_step_quality_status(p_batchstep_id => l_step_id
2595 , p_org_id => l_organization_id
2596 , p_quality_status => 2
2597 , x_return_status => l_return_status);
2598
2599 IF l_return_status <> 'S' THEN
2600 NULL;
2601 END IF;
2602 END IF;
2603
2604 IF (l_debug = 'Y') THEN
2605 gmd_debug.put_line('Ckecking for approver ');
2606 END IF;
2607
2608 /* Start the Workflow for the Given Combination */
2609 AME_API.CLEARALLAPPROVALS(applicationIdIn => l_application_id,
2610 transactionIdIn => l_event_key,
2611 transactionTypeIn => l_transaction_type);
2612 --wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
2613 AME_API.GETNEXTAPPROVER(applicationIdIn => l_application_id,
2614 transactionIdIn => l_event_key,
2615 transactionTypeIn => l_transaction_type,
2616 nextApproverOut => Approver);
2617
2618 /* IF(Approver.user_id is null and Approver.person_id is null) THEN*/
2619 /* No Approval Required */
2620 /* P_resultout:='COMPLETE:NO_WORKFLOW';
2621 return;
2622 END IF;*/
2623
2624 IF(Approver.user_id is not null or Approver.person_id is not null) THEN /* Added in Bug No.7032334 */
2625
2626 IF(Approver.person_id is null) THEN
2627 select user_name
2628 into l_user
2629 from fnd_user
2630 where user_id=Approver.user_id;
2631 ELSE
2632 select user_name
2633 into l_user
2634 from fnd_user
2635 where user_id=AME_UTIL.PERSONIDTOUSERID(Approver.person_id);
2636 END IF;
2637
2638 IF (l_debug = 'Y') THEN
2639 gmd_debug.put_line('Approver Found ');
2640 END IF;
2641
2642 l_itemtype :='GMDQMSMC';
2643 l_itemkey :=l_event_key||'-'||l_step_id||'-'||l_INVENTORY_item_id
2644 ||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
2645 l_workflow_process:='GMDQMSMC_PROD_PROCESS';
2646
2647
2648 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
2649 itemkey => l_itemkey,
2650 process => l_Workflow_Process) ;
2651
2652 --wf_log_pkg.string(6, 'Dummy','Child Process Created');
2653
2654 /* Set the User Attribute */
2655
2656 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2657 aname => 'CURRENT_APPROVER',
2658 avalue => l_user);
2659
2660 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2661 aname => 'APPS_FORM',
2662 avalue =>l_form );
2663 /* Set All other Attributes */
2664
2665 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2666 aname => 'EVENT_NAME',
2667 avalue =>l_event_name );
2668
2669 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2670 aname => 'EVENT_KEY',
2671 avalue =>l_event_key );
2672
2673 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2674 aname => 'APPS_FORM',
2675 avalue =>l_form );
2676
2677 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2678 aname => 'ITEM_NUMBER',
2679 avalue =>l_ITEM_NUMBER );
2680
2681 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2682 aname => 'ITEM_REVISION',
2683 avalue =>l_REVISION );
2684
2685 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2686 aname => 'ITEM_DESC',
2687 avalue =>l_item_desc );
2688
2689 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2690 aname => 'ORGANIZATION_CODE',
2691 avalue =>l_organization_code );
2692
2693 WF_ENGINE.SETITEMATTRTEXT(itemtype =>l_itemtype,itemkey => l_itemkey,
2694 aname => 'SUBINVENTORY',
2695 avalue =>l_subinventory );
2696
2697 WF_ENGINE.SETITEMATTRTEXT(itemtype =>l_itemtype,itemkey => l_itemkey,
2698 aname => 'LOCATOR',
2699 avalue => l_locator);
2700
2701 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2702 aname => 'FORMULA_NO',
2703 avalue =>l_formula_no );
2704
2705 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2706 aname => 'RECIPE_NO',
2707 avalue =>l_recipe_no );
2708
2709 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey =>l_itemkey,
2710 aname => 'BATCH_NO',
2711 avalue =>l_batch_no );
2712
2713 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2714 aname => 'BATCH_STEP_NO',
2715 avalue =>l_step_no );
2716
2717 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2718 aname => 'SAMPLING_PLAN',
2719 avalue =>l_sample_plan );
2720
2721 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
2722 aname => '#FROM_ROLE',
2723 avalue =>l_from_role );
2724
2725 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2726 aname => 'AME_TRANS',
2727 avalue =>l_transaction_type );
2728
2729 -- RLNAGARA Bug 5032406 (FP of 4604305 ME) Setting spec name and version wf attributes
2730 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2731 aname => 'SPEC_NAME',
2732 avalue =>l_spec_name );
2733 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
2734 aname => 'SPEC_VERS',
2735 avalue =>l_spec_vers );
2736
2737 WF_LOG_PKG.STRING(6, 'Setting','Setting Parent');
2738
2739 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,
2740 itemkey =>l_itemkey,
2741 parent_itemtype => p_itemtype,
2742 parent_itemkey => p_itemkey,
2743 parent_context => NULL);
2744
2745
2746 /* As this a pure FYI notification we will set the approver to approve status */
2747 /* Bug #4576699 Moved this code before WF_ENGINE.STARTPROCESS so that
2748 CHECK_NEXT_APPROVER doesn't fetch records from the first approver to whom
2749 a notification is already sent */
2750
2751 Approver.approval_status := AME_UTIL.APPROVEDSTATUS;
2752 AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
2753 transactionIdIn => l_event_key,
2754 approverIn => Approver,
2755 transactionTypeIn => l_transaction_type,
2756 forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);
2757
2758 /* start the Workflow process */
2759 --WF_LOG_PKG.STRING(6, 'Dummy','Starting Process');
2760 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
2761 END IF; /* Added in Bug no. 7033224 */
2762 END IF; /* Spec Found */
2763 END LOOP;
2764 CLOSE C1;
2765 END IF;
2766 p_resultout:='COMPLETE:';
2767
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 WF_CORE.CONTEXT ('GMD_QMSMC','PRODUCTION',p_itemtype,p_itemkey,l_log );
2771 raise;
2772
2773 END PRODUCTION;
2774
2775
2776 /* RLNAGARA Bug 5032406 (FP of 4604305 ME) Added new procedure IS_STEP */
2777 /* procedure to check if the event is raised for a batch step level transaction */
2778 PROCEDURE IS_STEP(
2779 p_itemtype IN VARCHAR2,
2780 p_itemkey IN VARCHAR2,
2781 p_actid IN NUMBER,
2782 p_funcmode IN VARCHAR2,
2783 p_resultout OUT NOCOPY VARCHAR2)
2784 IS
2785 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
2786 itemtype=>p_itemtype,
2787 itemkey=>P_itemkey,
2788 aname=>'EVENT_NAME');
2789
2790 BEGIN
2791
2792 IF l_event_name IN ('oracle.apps.gme.batchstep.created','oracle.apps.gme.bstep.rel.wf') THEN
2793 P_resultout:='COMPLETE:Y';
2794 ELSE
2795 P_resultout:='COMPLETE:N';
2796 END IF;
2797
2798 EXCEPTION
2799 WHEN OTHERS THEN
2800 WF_CORE.CONTEXT ('GMD_QMSMC','IS_STEP',p_itemtype,p_itemkey );
2801 RAISE;
2802
2803 END IS_STEP;
2804
2805 END GMD_QMSMC;