[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_TEST_WF_PKG
Source
1 PACKAGE BODY GMD_SS_TEST_WF_PKG AS
2 /* $Header: GMDQSTSB.pls 120.0 2005/05/26 00:54:01 appldev noship $ */
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
17 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
18 itemtype=>p_itemtype,
19 itemkey=>P_itemkey,
20 aname=>'EVENT_NAME');
21 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
22 itemtype=>p_itemtype,
23 itemkey=>P_itemkey,
24 aname=>'EVENT_KEY');
25
26 l_current_approver varchar2(240);
27
28 l_application_id number;
29 l_transaction_type varchar2(100):='GMDQM_STABILITY_PARENTTEST';
30 l_user varchar2(32);
31 Approver ame_util.approverRecord;
32
33
34 l_form varchar2(240);
35 l_itemtype varchar2(240);
36 l_itemkey varchar2(240);
37 l_workflow_process varchar2(240);
38 l_log varchar2(4000);
39 I NUMBER;
40 l_notify_lead number ;
41 l_grace_lead number ;
42 l_notify_lead_unit varchar2(10) ;
43 l_grace_lead_unit varchar2(10) ;
44 l_notify_ahead number;
45 l_grace_ahead number;
46
47 l_time_id number;
48 l_time date ;
49 l_wf_sent varchar2(10);
50 l_se_id number ;
51 l_disp varchar2(10);
52
53 cursor C1 is
54 select NOTIFICATION_LEAD_TIME , NOTIFICATION_LEAD_TIME_UNIT,
55 TESTING_GRACE_PERIOD , TESTING_GRACE_PERIOD_UNIT
56 from gmd_stability_studies_b
57 where ss_id = l_event_key ;
58
59 cursor C2 is
60 select distinct time.time_point_id , time.scheduled_date, time.wf_sent, time.sampling_event_id
61 from gmd_ss_time_points time ,
62 gmd_ss_variants variant ,
63 gmd_ss_material_sources sources,
64 gmd_stability_studies ss
65 where time.variant_id = variant.variant_id
66 and sources.source_id = variant.material_source_id
67 and variant.ss_id = l_event_key
68 and ss.ss_id = variant.ss_id
69 and nvl(variant.actual_end_date, sysdate+1) >= sysdate
70 and nvl(ss.ACTUAL_END_DATE , sysdate+1) >= sysdate
71 and variant.delete_mark = 0 ;
72
73 Cursor C3 (se_id_in number) is
74 select disposition
75 from gmd_sampling_events
76 where sampling_event_id = se_id_in ;
77
78
79 BEGIN
80
81
82 IF (l_debug = 'Y') THEN
83 gmd_debug.log_initialize('StabStudyParentTest');
84 gmd_debug.put_line('Event Name ' || l_event_name );
85 gmd_debug.put_line('Event Key ' || l_event_key );
86 END IF;
87
88
89 IF P_FUNCMODE='RUN' THEN
90
91 /* Get application_id from FND_APPLICATION */
92 select application_id into l_application_id
93 from fnd_application where application_short_name='GMD';
94
95 /* Check which event has been raised */
96 wf_log_pkg.string(6, 'Dummy','Entered Stability Study Testing '||l_event_key);
97
98 /* Get the notification and grace time information */
99 OPEN C1;
100 Fetch C1 into l_notify_lead , l_notify_lead_unit , l_grace_lead , l_grace_lead_unit ;
101 CLOSE C1;
102
103 /* In case notify and grace leads are not defined, default to 0 */
104 if (l_notify_lead is null) then
105 l_notify_lead := 0;
106 end if ;
107 if (l_grace_lead is null) then
108 l_grace_lead := 0;
109 end if ;
110
111
112 /* Get the time diff to compare to sysdate - scheduled_date */
113 GET_TIME(l_notify_lead, l_notify_lead_unit, l_notify_ahead);
114 GET_TIME(l_grace_lead, l_grace_lead_unit, l_grace_ahead);
115
116
117 IF (l_debug = 'Y') THEN
118 gmd_debug.put_line('Notify Lead ' || l_notify_lead);
119 gmd_debug.put_line('Grace Lead ' || l_grace_lead);
120 END IF;
121
122
123 /* Go through each timepoint and see if need to send a timepoint notification
124 or late timepoint one */
125 OPEN C2;
126 LOOP
127 Fetch C2 into l_time_id, l_time, l_wf_sent, l_se_id;
128 exit when C2%notfound;
129
130 if (nvl(l_wf_sent,'##') <> 'Y')
131 and ((l_time - l_notify_ahead) <= sysdate) then
132 /* We should send a timepoint testing workflow */
133
134 IF (l_debug = 'Y') THEN
135 gmd_debug.put_line('Timepoint test ' || l_time_id);
136 END IF;
137 gmd_api_pub.raise('oracle.apps.gmd.qm.ss.tp',to_char(l_time_id) );
138 elsif (nvl(l_wf_sent,'##') = 'Y')
139 and ((l_time + l_grace_ahead) <= sysdate) then
140
141 open C3 (l_se_id);
142 fetch C3 into l_disp ;
143 close C3 ;
144
145 if ((l_disp = '0RT') or (l_disp = '1P')) then
146 /* We should send a late timepoint testing workflow */
147 IF (l_debug = 'Y') THEN
148 gmd_debug.put_line('Late timepoint test ' || l_time_id);
149 END IF;
150
151 gmd_api_pub.raise('oracle.apps.gmd.qm.ss.tplt',to_char(l_time_id) );
152 end if;
153 end if;
154 END LOOP;
155 CLOSE C2;
156
157 END IF;
158
159 /* Right now, let the workflow keep on going indefinitely */
160 p_resultout:='COMPLETE:Y';
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 WF_CORE.CONTEXT ('GMD_SS_TEST_WF_PKG','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
165 raise;
166
167 END VERIFY_EVENT;
168
169
170 /* procedure to get time as a fraction, unit of measure is days */
171 PROCEDURE GET_TIME(
172 p_value IN NUMBER,
173 p_unit IN VARCHAR2,
174 p_time OUT NOCOPY NUMBER
175 ) IS
176 l_number number := 0;
177
178 BEGIN
179
180
181 if (p_unit = 'TY') then
182 p_time := p_value*365;
183 end if;
184
185 if (p_unit = 'TM') then
186 p_time := p_value*30;
187 end if;
188
189 if (p_unit = 'TW') then
190 p_time := p_value*7;
191 end if;
192
193 if (p_unit = 'TD') then
194 p_time := p_value;
195 end if;
196
197 if (p_unit = 'TH') then
198 p_time := p_value / 24;
199 end if;
200
201
202
203 END GET_TIME;
204
205
206 PROCEDURE CHECK_NEXT_APPROVER(
207 p_itemtype IN VARCHAR2,
208 p_itemkey IN VARCHAR2,
209 p_actid IN NUMBER,
210 p_funcmode IN VARCHAR2,
211 p_resultout OUT NOCOPY VARCHAR2)
212
213 IS
214 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
215 itemtype=>p_itemtype,
216 itemkey=>P_itemkey,
217 aname=>'EVENT_NAME');
218 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
219 itemtype=>p_itemtype,
220 itemkey=>P_itemkey,
221 aname=>'EVENT_KEY');
222
223 l_current_approver varchar2(240);
224
225 l_application_id number;
226 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
227 itemtype=>p_itemtype,
228 itemkey=>P_itemkey,
229 aname=>'AME_TRANS');
230 l_user varchar2(32);
231 Approver ame_util.approverRecord;
232 l_form varchar2(240);
233 BEGIN
234
235 /* Get Next Approver */
236 /* Get application_id from FND_APPLICATION */
237 select application_id into l_application_id
238 from fnd_application where application_short_name='GMD';
239
240 ame_api.getNextApprover(applicationIdIn => l_application_id,
241 transactionIdIn => l_event_key,
242 transactionTypeIn => l_transaction_type,
243 nextApproverOut => Approver);
244
245
246 if(Approver.user_id is null and Approver.person_id is null) then
247 /* No Approval Required */
248 P_resultout:='COMPLETE:N';
249 else
250 if(Approver.person_id is null) then
251 select user_name into l_user from fnd_user
252 where user_id=Approver.user_id;
253 else
254 select user_name into l_user from fnd_user
255 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
256 end if;
257
258 /* Set the User Attribute */
259
260 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
261 aname => 'CURRENT_APPROVER',
262 avalue => l_user);
263
264 P_resultout:='COMPLETE:Y';
265 Approver.approval_status := ame_util.approvedStatus;
266 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
267 transactionIdIn => l_event_key,
268 approverIn => Approver,
269 transactionTypeIn => l_transaction_type,
270 forwardeeIn => ame_util.emptyApproverRecord);
271 end if;
272 EXCEPTION
273 WHEN OTHERS THEN
274 WF_CORE.CONTEXT ('GMD_SS_TEST_WF_PKG','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
275 raise;
276
277 END CHECK_NEXT_APPROVER;
278
279
280 END GMD_SS_TEST_WF_PKG ;