[Home] [Help]
PACKAGE BODY: APPS.GMD_QMTES
Source
1 PACKAGE BODY GMD_QMTES AS
2 /* $Header: GMDQMTEB.pls 120.7.12010000.3 2009/09/17 07:01:11 kannavar ship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 PROCEDURE VERIFY_EVENT(
8 /* procedure to verify event and send out notifications*/
9 p_itemtype IN VARCHAR2,
10 p_itemkey IN VARCHAR2,
11 p_actid IN NUMBER,
12 p_funcmode IN VARCHAR2,
13 p_resultout OUT NOCOPY VARCHAR2)
14
15 IS
16 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
17 itemtype=>p_itemtype,
18 itemkey=>P_itemkey,
19 aname=>'EVENT_NAME');
20 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
21 itemtype=>p_itemtype,
22 itemkey=>P_itemkey,
23 aname=>'EVENT_KEY');
24
25 l_current_approver varchar2(240);
26
27 l_application_id number;
28 l_transaction_type varchar2(100):='GMDQMPF';
29 l_user varchar2(32);
30 Approver ame_util.approverRecord;
31 l_test_id number:=WF_ENGINE.GETITEMATTRTEXT(
32 itemtype=>p_itemtype,
33 itemkey=>P_itemkey,
34 aname=>'TEST_ID');
35
36 l_form varchar2(240);
37 l_itemtype varchar2(240);
38 l_itemkey varchar2(240);
39 l_workflow_process varchar2(240);
40 l_log varchar2(4000);
41 I NUMBER;
42 l_item_no varchar2(240);
43 l_item_desc varchar2(240);
44 l_item_um varchar2(32);
45 l_parent_lot varchar2(240); --RLNAGARA B5714214 Added parent_lot
46 l_lot_no varchar2(240);
47 l_lpn VARCHAR2(240); --RLNAGARA LPN ME 7027149
48 l_result_id varchar2(240);
49 l_test_code varchar2(240);
50 l_test_desc varchar2(240);
51 l_test_class varchar2(240);
52 l_test_method_code varchar2(240);
53 l_test_method_desc varchar2(240);
54 l_resources varchar2(240);
55 l_test_replicate varchar2(240);
56 l_qc_lab_orgn_code varchar2(240);
57 l_qc_lab_org_id number;
58 l_sample_id varchar2(240);
59 l_sample_no varchar2(240);
60 l_sample_desc varchar2(240);
61 l_days number ;
62 l_hours number ;
63 l_minutes number ;
64 l_seconds number ;
65 l_testbydate varchar2(240);
66
67 L_ITEM_REVISION varchar2(240);
68 l_SUBINVENTORY varchar2(240);
69 l_LOCATOR varchar2(240);
70 l_planned_resource varchar2(240);
71 l_planned_Result_date varchar2(240);
72
73 --RLNAGARA B5738147 start
74 l_source VARCHAR2(1);
75 l_source_subinv varchar2(240);
76 l_source_loc varchar2(240);
77 --RLNAGARA B5738147 end
78
79 --RLNAGARA B5714214 Removed the reference to table MTL_LOT_NUMBERS and retrieved the lot info from gmd_samples table.
80 -- Also added parent_lot_number
81 /* This Cusror Will Pick up all Test Details for a Given Sample */
82 Cursor C1 is
83 SELECT D.CONCATENATED_SEGMENTS,D.description,D.primary_uom_code,C.PARENT_LOT_NUMBER,C.LOT_NUMBER, wlpn.license_plate_number lpn,
84 to_char(GR.RESULT_ID),A.TEST_CODE,A.TEST_DESC,A.TEST_CLASS,
85 B.TEST_METHOD_CODE, B.TEST_METHOD_DESC,B.RESOURCES,B.TEST_REPLICATE,
86 C.LAB_ORGANIZATION_ID, C.SAMPLE_ID,C.SAMPLE_NO,C.SAMPLE_DESC,
87 C.SUBINVENTORY, gr.test_by_date, gr.planned_Resource, gr.planned_result_date,
88 MIL.concatenated_segments, c.revision,
89 c.source_subinventory, MIL1.concatenated_segments,c.source --RLNAGARA B5738147
90 from GMD_RESULTS GR,
91 GMD_QC_TESTS_VL A,
92 GMD_TEST_METHODS B,
93 GMD_SAMPLES C,
94 mtl_system_items_kfv D,
95 -- mtl_lot_numbers E,
96 mtl_parameters MP,
97 mtl_item_locations_kfv MIL,
98 mtl_item_locations_kfv MIL1, --RLNAGARA B5738147
99 wms_license_plate_numbers wlpn --RLNAGARA LPN ME 7027149
100 WHERE GR.SAMPLE_ID = l_event_key AND
101 GR.SAMPLE_ID=C.SAMPLE_ID AND
102 NVL(L_TEST_ID,GR.TEST_ID)=A.TEST_ID AND
103 A.TEST_METHOD_ID=B.TEST_METHOD_ID AND
104 C.ORGANIZATION_ID=D.ORGANIZATION_ID AND
105 -- C.INVENTORY_ITEM_ID=E.INVENTORY_ITEM_ID(+) AND
106 C.INVENTORY_ITEM_ID= D.INVENTORY_ITEM_ID AND
107 -- C.ORGANIZATION_ID = E.ORGANIZATION_ID AND
108 -- C.LOT_NUMBER = E.LOT_NUMBER AND
109 C.organization_id = mp.organization_id AND
110 MIL.organization_id(+) = C.organization_id AND
111 MIL.inventory_location_id(+) = C.locator_id AND
112 MIL1.organization_id(+) = C.organization_id AND --RLNAGARA B5738147
113 MIL1.inventory_location_id(+) = C.source_locator_id AND --RLNAGARA B5738147
114 wlpn.lpn_id(+) = c.lpn_id; -- RLNAGARA LPN ME 7027149
115
116
117 cursor get_from_role is
118 select nvl( text, '')
119 from wf_Resources where name = 'WF_ADMIN_ROLE'
120 and language = userenv('LANG') ;
121
122
123 CURSOR GET_LAB_ORG IS
124 SELECT organization_code
125 FROM mtl_parameters
126 WHERE organization_id = l_qc_lab_org_id;
127
128 l_from_role varchar2(240);
129 l_sequence_id number;
130
131 BEGIN
132
133 IF (l_debug = 'Y') THEN
134 gmd_debug.log_initialize('PerformTest');
135 gmd_debug.put_line('Event Name ' || l_event_name);
136 gmd_debug.put_line('Event Key ' || l_event_key);
137 END IF;
138
139 open get_from_role ;
140 fetch get_from_role into l_from_role ;
141 close get_from_role ;
142
143
144 IF P_FUNCMODE='RUN' THEN
145 /* Get application_id from FND_APPLICATION */
146 select application_id into l_application_id
147 from fnd_application where application_short_name='GMD';
148
149 /* Check which event has been raised */
150 wf_log_pkg.string(6, 'Dummy','Entered Test Transactions with event_key '||l_event_key);
151
152 /*Figure out if all the batch_steps are covered for sampple creation */
153
154 OPEN C1;
155 LOOP
156 wf_log_pkg.string(6, 'Dummy','Before Fetching the values. Inside the Loop');
157 Fetch C1 into L_ITEM_NO,L_ITEM_DESC,L_ITEM_UM,L_PARENT_LOT,L_LOT_NO,l_lpn,L_RESULT_ID,L_TEST_CODE,L_TEST_DESC, --RLNAGARA B5714214 Added l_parent_lot
158 L_TEST_CLASS,L_TEST_METHOD_CODE,L_TEST_METHOD_DESC,L_RESOURCES,L_TEST_REPLICATE,
159 l_qc_lab_org_id, L_SAMPLE_ID,L_SAMPLE_NO,L_SAMPLE_DESC,
160 l_SUBINVENTORY, l_testbydate,l_planned_resource,l_planned_Result_date, l_LOCATOR,
161 L_ITEM_REVISION,l_source_subinv,l_source_loc,l_source; --RLNAGARA B5738147
162 EXIT when c1%notfound;
163 --RLNAGARA B5738147 start
164 IF l_source ='W' THEN
165 l_SUBINVENTORY := l_source_subinv;
166 l_LOCATOR := l_source_loc;
167 END IF;
168 --RLNAGARA B5738147 end
169
170 OPEN GET_LAB_ORG;
171 Fetch GET_LAB_ORG into l_qc_lab_orgn_code;
172 IF (GET_LAB_ORG%NOTFOUND) THEN
173 l_qc_lab_orgn_code := NULL;
174 END IF;
175 CLOSE GET_LAB_ORG;
176
177
178 /* Set Form Attribute to the sampling event */
179 l_form := 'GMDQRSLT_EDIT_F:SAMPLE_ID="'||l_sample_id||'"';
180
181
182 IF (l_debug = 'Y') THEN
183 gmd_debug.put_line('Checking for approvers ');
184 END IF;
185
186 /* Start the Workflow for the Given Combination */
187 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
188 transactionIdIn => l_result_id,
189 transactionTypeIn => l_transaction_type);
190 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
191 ame_api.getNextApprover(applicationIdIn => l_application_id,
192 transactionIdIn => l_result_id,
193 transactionTypeIn => l_transaction_type,
194 nextApproverOut => Approver);
195
196 if(Approver.user_id is null and Approver.person_id is null) then
197 /* No Approval Required */
198 NULL;
199 -- Bug #3801988 (JKB) Changed EXIT to NULL above and realigned code below so it is inside the ELSE.
200 else
201
202 IF (l_debug = 'Y') THEN
203 gmd_debug.put_line('Approvers Found');
204 END IF;
205
206 if(Approver.person_id is null) then
207 select user_name into l_user from fnd_user
208 where user_id=Approver.user_id;
209 else
210 select user_name into l_user from fnd_user
211 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
212 end if;
213
214
215 Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
216 Into l_sequence_id
217 From dual;
218
219
220 l_itemtype:='GMDQMTST';
221 l_itemkey:=l_event_key||'-'||l_result_id||'-'
222 ||to_char(sysdate,'dd/mm/yy hh:mi:ss')||'-'||l_sequence_id;
223 l_workflow_process:='GMDQMTST_SUB_PROCESS';
224 --insert into rg_debug(vdata) values(l_itemkey);
225
226 /* Start the subprocess now instead of earlier to fix the cancelled FYI
227 notifications */
228 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
229 itemkey => l_itemkey,
230 process => l_Workflow_Process) ;
231
232
233 /* Set the User Attribute */
234
235 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
236 aname => 'CURRENT_APPROVER',
237 avalue => l_user);
238 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
239 aname => 'APPS_FORM',
240 avalue =>l_form );
241 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
242 aname => 'EVENT_NAME',
243 avalue =>l_event_name );
244 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
245 aname => 'EVENT_KEY',
246 avalue =>l_result_id );
247 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
248 aname => 'ITEM_NO',
249 avalue =>l_item_no );
250 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
251 aname => 'ITEM_REVISION',
252 avalue =>l_item_revision );
253 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
254 aname => 'ITEM_DESC',
255 avalue =>l_item_desc );
256 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
257 aname => 'ITEM_UM',
258 avalue =>l_item_um );
259 --RLNAGARA B5714214 Added parent_lot to the notification also.
260 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
261 aname => 'PARENT_LOT',
262 avalue =>l_parent_lot );
263 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
264 aname => 'LOT_NO',
265 avalue =>l_lot_no );
266 --RLNAGARA LPN ME 7027149
267 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
268 aname => 'LPN',
269 avalue =>l_lpn );
270 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
271 aname => 'TEST_CODE',
272 avalue =>l_test_code );
273 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
274 aname => 'TEST_DESC',
275 avalue =>l_test_desc );
276 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
277 aname => 'TEST_CLASS',
278 avalue =>l_test_class );
279 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
280 aname => 'TEST_METHOD_CODE',
281 avalue =>l_test_method_Code );
282 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
283 aname => 'TEST_METHOD_DESC',
284 avalue =>l_test_method_desc );
285 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
286 aname => 'TEST_REPLICATE',
287 avalue =>l_test_replicate );
288 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
289 aname => 'RESOURCES',
290 avalue =>l_resources );
291 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
292 aname => 'QC_LAB_ORGN_CODE',
293 avalue =>l_qc_lab_orgn_code );
294 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
295 aname => 'SAMPLE_NO',
296 avalue =>l_sample_no );
297 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
298 aname => 'SAMPLE_DESC',
299 avalue =>l_sample_desc);
300 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
301 aname => 'AME_TRANS',
302 avalue =>l_transaction_type);
303 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
304 aname => 'DAYS',
305 avalue =>l_days);
306 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
307 aname => 'HOURS',
308 avalue =>l_hours);
309 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
310 aname => 'MINUTES',
311 avalue =>l_minutes);
312 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
313 aname => 'SECONDS',
314 avalue =>l_seconds);
315 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
316 aname => 'TEST_BY_DATE',
317 avalue =>l_testbydate);
318 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey, aname => '#FROM_ROLE',avalue => l_from_role );
319 -- SCHANDRU INVCONV START
320 wf_engine.setitemattrtext(itemtype => l_itemtype,itemkey => l_itemkey,
321 aname =>'SUBINVENTORY',
322 avalue =>l_SUBINVENTORY);
323 wf_engine.setitemattrtext(itemtype => l_itemtype,itemkey => l_itemkey,
324 aname => 'LOCATOR',
325 avalue => l_LOCATOR);
326 wf_engine.setitemattrtext(itemtype => l_itemtype, itemkey =>l_itemkey,
327 aname =>'PLANNED_RESOURCE',
328 avalue =>l_planned_resource);
329 wf_engine.setitemattrtext(itemtype => l_itemtype,itemkey => l_itemkey,
330 aname =>'PLANNED_RESULT_DATE',
331 avalue => l_planned_Result_date);
332 -- SCHANDRU INVCONV END
333 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
334 parent_itemtype => p_itemtype,
335 parent_itemkey=> p_itemkey,
336 parent_context=> NULL);
337
338 /* start the Workflow process */
339 wf_log_pkg.string(6, 'Dummy','Starting Process');
340
341
342 /* As this a pure FYI notification we will set the approer to approve status */
343 Approver.approval_status := ame_util.approvedStatus;
344 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
345 transactionIdIn => l_result_id,
346 approverIn => Approver,
347 transactionTypeIn => l_transaction_type,
348 forwardeeIn => ame_util.emptyApproverRecord);
349
350
351 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
352
353
354 wf_log_pkg.string(6, 'Dummy','Child Process Created and current approver is '||l_user);
355
356 end if;
357
358 END LOOP;
359 CLOSE C1;
360 END IF;
361 p_resultout:='COMPLETE:';
362
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 WF_CORE.CONTEXT ('GMD_QMTES','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
367 raise;
368
369
370
371 END VERIFY_EVENT;
372
373 PROCEDURE CHECK_NEXT_APPROVER(
374 /* procedure to verify event if the event is sample disposition or sample event disposition */
375 p_itemtype IN VARCHAR2,
376 p_itemkey IN VARCHAR2,
377 p_actid IN NUMBER,
378 p_funcmode IN VARCHAR2,
379 p_resultout OUT NOCOPY VARCHAR2)
380
381 IS
382 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
383 itemtype=>p_itemtype,
384 itemkey=>P_itemkey,
385 aname=>'EVENT_NAME');
386 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
387 itemtype=>p_itemtype,
388 itemkey=>P_itemkey,
389 aname=>'EVENT_KEY');
390
391 l_current_approver varchar2(240);
392
393 l_application_id number;
394 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
395 itemtype=>p_itemtype,
396 itemkey=>P_itemkey,
397 aname=>'AME_TRANS');
398 l_user varchar2(32);
399 Approver ame_util.approverRecord;
400 l_form varchar2(240);
401 BEGIN
402 /* Get Next Approver */
403 /* Get application_id from FND_APPLICATION */
404 select application_id into l_application_id
405 from fnd_application where application_short_name='GMD';
406
407 ame_api.getNextApprover(applicationIdIn => l_application_id,
408 transactionIdIn => l_event_key,
409 transactionTypeIn => l_transaction_type,
410 nextApproverOut => Approver);
411
412 if(Approver.user_id is null and Approver.person_id is null) then
413 /* No Approval Required */
414 P_resultout:='COMPLETE:N';
415 else
416 if(Approver.person_id is null) then
417 select user_name into l_user from fnd_user
418 where user_id=Approver.user_id;
419 else
420 select user_name into l_user from fnd_user
421 where
422 user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
423 end if;
424
425 /* Set the User Attribute */
426
427 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
428 aname => 'CURRENT_APPROVER',
429 avalue => l_user);
430 P_resultout:='COMPLETE:Y';
431 Approver.approval_status := ame_util.approvedStatus;
432 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
433 transactionIdIn => l_event_key,
434 approverIn => Approver,
435 transactionTypeIn => l_transaction_type,
436 forwardeeIn => ame_util.emptyApproverRecord);
437 end if;
438 EXCEPTION
439 WHEN OTHERS THEN
440 WF_CORE.CONTEXT ('GMD_QMTES','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
441 raise;
442
443 END CHECK_NEXT_APPROVER;
444 END GMD_QMTES;