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