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