[Home] [Help]
PACKAGE BODY: APPS.GMD_QMDSC
Source
1 PACKAGE BODY GMD_QMDSC AS
2 /* $Header: GMDQMSCB.pls 120.2 2006/12/05 16:06:22 rlnagara noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7 PROCEDURE VERIFY_EVENT(
8 p_itemtype IN VARCHAR2,
9 p_itemkey IN VARCHAR2,
10 p_actid IN NUMBER,
11 p_funcmode IN VARCHAR2,
12 p_resultout OUT NOCOPY VARCHAR2)
13
14 IS
15 l_event_name varchar2(240) := WF_ENGINE.GETITEMATTRTEXT(
16 itemtype=>p_itemtype,
17 itemkey=>P_itemkey,
18 aname=>'EVENT_NAME');
19 l_event_key varchar2(240) := WF_ENGINE.GETITEMATTRTEXT(
20 itemtype=>p_itemtype,
21 itemkey=>P_itemkey,
22 aname=>'EVENT_KEY');
23
24 l_status_change varchar2(40);
25 l_change_to varchar2(40);
26 l_log varchar2(200);
27
28 cursor get_from_role is
29 select nvl( text, '')
30 from wf_Resources where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
31 and language = userenv('LANG') ;
32
33 BEGIN
34
35 IF (l_debug = 'Y') THEN
36 gmd_debug.log_initialize('Sampledisp');
37 END IF;
38
39 IF (l_debug = 'Y') THEN
40 gmd_debug.put_line('Event ' || l_event_name);
41 gmd_debug.put_line('Event key ' || l_event_key);
42 END IF;
43
44 if l_event_name = 'oracle.apps.gmd.qm.samplingevent.disposition' then
45 l_log := 'Composite and lot status change' ;
46 GMD_RESULTS_GRP.composite_and_change_lot(l_event_key,
47 'NO',
48 l_status_change);
49 elsif l_event_name = 'oracle.apps.gmd.qm.sample.disposition' then
50 l_log := 'Sample status change' ;
51 GMD_RESULTS_GRP.change_disp_for_auto_lot(l_event_key,
52 l_change_to,
53 l_status_change);
54 end if;
55
56
57 IF (l_debug = 'Y') THEN
58 gmd_debug.put_line('Completed Result GRP APIs');
59 END IF;
60
61 /* No Approval Required */
62 P_resultout:='COMPLETE:NO_WORKFLOW';
63 return;
64
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 WF_CORE.CONTEXT ('GMD_QMDSC','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
69 raise;
70
71 END VERIFY_EVENT;
72
73
74
75 /* AME Code which is not used at the moment */
76 PROCEDURE CHECK_NEXT_APPROVER(
77 /* procedure to verify event if the event is sample disposition or sample event disposition */
78 p_itemtype IN VARCHAR2,
79 p_itemkey IN VARCHAR2,
80 p_actid IN NUMBER,
81 p_funcmode IN VARCHAR2,
82 p_resultout OUT NOCOPY VARCHAR2)
83
84 IS
85 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
86 itemtype=>p_itemtype,
87 itemkey=>P_itemkey,
88 aname=>'EVENT_NAME');
89 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
90 itemtype=>p_itemtype,
91 itemkey=>P_itemkey,
92 aname=>'EVENT_KEY');
93
94 l_current_approver varchar2(240);
95
96 l_application_id number;
97 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
98 itemtype=>p_itemtype,
99 itemkey=>P_itemkey,
100 aname=>'AME_TRANS');
101 l_user varchar2(32);
102 Approver ame_util.approverRecord;
103 l_item_no varchar2(240);
104 l_item_desc varchar2(240);
105 l_lot_no varchar2(240);
106 l_sublot_no varchar2(240);
107 l_sample_no varchar2(240);
108 l_sample_plan varchar2(240);
109 l_sample_disposition varchar2(240);
110 l_sample_source varchar2(240);
111 l_specification varchar2(240);
112 l_validity_rule varchar2(240);
113 l_validity_rule_version varchar2(240);
114 l_sample_event_text varchar2(4000);
115 l_sampling_event_id number;
116 l_form varchar2(240);
117
118 BEGIN
119
120 /* Get Next Approver */
121 /* Get application_id from FND_APPLICATION */
122 select application_id into l_application_id
123 from fnd_application where application_short_name='GMD';
124
125 ame_api.getNextApprover(applicationIdIn => l_application_id,
126 transactionIdIn => l_event_key,
127 transactionTypeIn => l_transaction_type,
128 nextApproverOut => Approver);
129
130 if(Approver.user_id is null and Approver.person_id is null) then
131 /* No Approval Required */
132 P_resultout:='COMPLETE:N';
133 else
134 if(Approver.person_id is null) then
135 select user_name into l_user from fnd_user
136 where user_id=Approver.user_id;
137 else
138 /*select user_name into l_user from fnd_user a,per_all_people b
139 where
140 b.person_id=Approver.person_id and
141 a.employee_id is not null and
142 a.employee_id = b.person_id; */
143
144 -- Bug# 5226352
145 -- Commented the above select statement and added new select to fix performance issues
146 select user_name into l_user from fnd_user a
147 where a.employee_id = Approver.person_id
148 and a.employee_id is not null
149 and exists (select 1 from per_all_people where person_id = Approver.person_id);
150
151 end if;
152
153 /* Set the User Attribute */
154
155 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
156 aname => 'CURRENT_APPROVER',
157 avalue => l_user);
158 P_resultout:='COMPLETE:Y';
159 Approver.approval_status := ame_util.approvedStatus;
160 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
161 transactionIdIn => l_event_key,
162 approverIn => Approver,
163 transactionTypeIn => l_transaction_type,
164 forwardeeIn => ame_util.emptyApproverRecord);
165 end if;
166 EXCEPTION
167 WHEN OTHERS THEN
168 WF_CORE.CONTEXT ('GMD_QMDSC','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
169 raise;
170
171 END CHECK_NEXT_APPROVER;
172
173
174
175 END GMD_QMDSC;