1 PACKAGE BODY PJM_INTEGRATION_PROJ_MFG as
2 /* $Header: PJMVLDTB.pls 115.8 2002/10/29 20:15:51 alaw ship $ */
3
4 FUNCTION PJM_VALIDATE_DATE (P_SCHEDULE_DATE IN DATE
5 ,P_TIME_POINT IN VARCHAR2
6 ,P_PROJECT_ID IN NUMBER
7 ,P_TASK_ID IN NUMBER
8 ,P_TOLERANCE_DAYS IN NUMBER
9 ,P_ERROR_MSG OUT NOCOPY VARCHAR2
10 ) return number
11 IS
12 d_proj_start_date date;
13 d_proj_end_date date;
14 d_task_start_date date;
15 d_task_end_date date;
16 c_x varchar2(1);
17 n_valid number :=1;
18 n_invalid number :=0;
19
20 BEGIN
21
22 IF p_schedule_date IS NULL OR p_project_id IS NULL THEN
23 RETURN n_valid;
24 END IF;
25
26 BEGIN
27 IF p_task_id IS NULL THEN
28 select ppa.start_date
29 ,ppa.completion_date
30 into d_proj_start_date
31 ,d_proj_end_date
32 from pa_projects_all ppa
33 where ppa.project_id =p_project_id
34 ;
35 d_task_start_date :=Null;
36 d_task_end_date :=Null;
37 ELSE
38 select ppa.start_date
39 ,ppa.completion_date
40 ,pt.start_date
41 ,pt.completion_date
42 into d_proj_start_date
43 ,d_proj_end_date
44 ,d_task_start_date
45 ,d_task_end_date
46 from pa_projects_all ppa
47 ,pa_tasks pt
48 where ppa.project_id =p_project_id
49 and pt.project_id =ppa.project_id
50 and pt.task_id =p_task_id
51 ;
52 END IF;
53 EXCEPTION
54 when no_data_found then
55 p_error_msg := sqlerrm;
56 RETURN n_invalid;
57 when too_many_rows then
58 p_error_msg := sqlerrm;
59 RETURN n_invalid;
60 END;
61
62 IF p_time_point='BEFORE_START' then
63 IF p_schedule_date-p_tolerance_days > nvl(d_task_start_date,nvl(d_proj_start_date,p_schedule_date-p_tolerance_days+1)) THEN
64 FND_MESSAGE.SET_NAME('PJM','SCHED-DATE BEFORE');
65 FND_MESSAGE.SET_TOKEN('DATE', FND_DATE.DATE_TO_DISPLAYDATE(
66 nvl(d_task_start_date,d_proj_start_date)));
67 p_error_msg := FND_MESSAGE.GET;
68 RETURN n_invalid;
69 END IF;
70 END IF;
71
72 IF upper(p_time_point)='BEFORE_END' then
73 IF p_schedule_date-p_tolerance_days > nvl(d_task_end_date,nvl(d_proj_end_date,p_schedule_date-p_tolerance_days+1)) THEN
74 FND_MESSAGE.SET_NAME('PJM','SCHED-DATE BEFORE');
75 FND_MESSAGE.SET_TOKEN('DATE', FND_DATE.DATE_TO_DISPLAYDATE(
76 nvl(d_task_end_date, d_proj_end_date)));
77 p_error_msg := FND_MESSAGE.GET;
78 RETURN n_invalid;
79 END IF;
80 END IF;
81
82 IF upper(p_time_point)='AFTER_START' then
83 IF p_schedule_date+p_tolerance_days < nvl(d_task_start_date,nvl(d_proj_start_date,p_schedule_date+p_tolerance_days-1)) THEN
84 FND_MESSAGE.SET_NAME('PJM','SCHED-DATE AFTER');
85 FND_MESSAGE.SET_TOKEN('DATE', FND_DATE.DATE_TO_DISPLAYDATE(
86 nvl(d_task_start_date,d_proj_start_date)));
87 p_error_msg := FND_MESSAGE.GET;
88 RETURN n_invalid;
89 END IF;
90 END IF;
91
92 IF upper(p_time_point)='AFTER_END' then
93 IF p_schedule_date+p_tolerance_days < nvl(d_task_end_date,nvl(d_proj_end_date,p_schedule_date+p_tolerance_days-1)) THEN
94 FND_MESSAGE.SET_NAME('PJM','SCHED-DATE AFTER');
95 FND_MESSAGE.SET_TOKEN('DATE', FND_DATE.DATE_TO_DISPLAYDATE(
96 nvl(d_task_end_date, d_proj_end_date)));
97 p_error_msg := FND_MESSAGE.GET;
98 RETURN n_invalid;
99 END IF;
100 END IF;
101
102 IF upper(p_time_point)='BETWEEN' then
103 IF (p_schedule_date+p_tolerance_days < nvl(d_task_start_date,nvl(d_proj_start_date,p_schedule_date+p_tolerance_days-1)) OR
104 p_schedule_date-p_tolerance_days > nvl(d_task_end_date,nvl(d_proj_end_date,p_schedule_date-p_tolerance_days+1))) THEN
105 FND_MESSAGE.SET_NAME('PJM','SCHED-DATE BETWEEN');
106 FND_MESSAGE.SET_TOKEN('START_DATE', FND_DATE.DATE_TO_DISPLAYDATE(
107 nvl(d_task_start_date,d_proj_start_date)));
108 FND_MESSAGE.SET_TOKEN('END_DATE', FND_DATE.DATE_TO_DISPLAYDATE(
109 nvl(d_task_end_date, d_proj_end_date)));
110 p_error_msg := FND_MESSAGE.GET;
111 RETURN n_invalid;
112 END IF;
113 END IF;
114
115 RETURN n_valid;
116
117 END PJM_VALIDATE_DATE;
118
119
120 FUNCTION PJM_EXCEPTION_DAYS(pd_schedule_date IN date
121 ,pc_time_point IN varchar2
122 ,pn_tolerance_days IN number
123 ,pd_project_start_date IN date
124 ,pd_project_end_date IN date
125 ,pd_task_start_date IN date
126 ,pd_task_end_date IN date
127 ) return number
128 IS
129 n_exception_days number:=0;
130 BEGIN
131 IF pd_schedule_date IS NULL THEN
132 RETURN n_exception_days;
133 END IF;
134
135 IF pc_time_point='BEFORE_START' then
136 IF trunc(pd_schedule_date)-pn_tolerance_days > trunc(nvl(pd_task_start_date,nvl(pd_project_start_date,pd_schedule_date-pn_tolerance_days+1))) THEN
137 n_exception_days:=(trunc(pd_schedule_date)-pn_tolerance_days) - trunc(nvl(pd_task_start_date,pd_project_start_date));
138 END IF;
139 END IF;
140
141 IF upper(pc_time_point)='BEFORE_END' then
142 IF trunc(pd_schedule_date)-pn_tolerance_days > trunc(nvl(pd_task_end_date,nvl(pd_project_end_date,pd_schedule_date-pn_tolerance_days+1))) THEN
143 n_exception_days:=(trunc(pd_schedule_date)-pn_tolerance_days) - trunc(nvl(pd_task_end_date,pd_project_end_date));
144 END IF;
145 END IF;
146
147 IF upper(pc_time_point)='AFTER_START' then
148 IF trunc(pd_schedule_date)+pn_tolerance_days < trunc(nvl(pd_task_start_date,nvl(pd_project_start_date,pd_schedule_date+pn_tolerance_days-1))) THEN
149 n_exception_days:=trunc(nvl(pd_task_start_date,pd_project_start_date))-(trunc(pd_schedule_date)+pn_tolerance_days);
150 END IF;
151 END IF;
152
153 IF upper(pc_time_point)='AFTER_END' then
154 IF trunc(pd_schedule_date)+pn_tolerance_days < trunc(nvl(pd_task_end_date,nvl(pd_project_end_date,pd_schedule_date+pn_tolerance_days-1))) THEN
155 n_exception_days:=trunc(nvl(pd_task_end_date,pd_project_end_date))-(trunc(pd_schedule_date)+pn_tolerance_days);
156 END IF;
157 END IF;
158
159 IF upper(pc_time_point)='BETWEEN' then
160 -- positive --> too early
161 -- negative --> too late
162 IF trunc(pd_schedule_date)+pn_tolerance_days < trunc(nvl(pd_task_start_date,nvl(pd_project_start_date,pd_schedule_date+pn_tolerance_days-1))) THEN
163 n_exception_days:=trunc(nvl(pd_task_start_date,pd_project_start_date))-(trunc(pd_schedule_date)+pn_tolerance_days);
164 END IF;
165 IF trunc(pd_schedule_date)-pn_tolerance_days > trunc(nvl(pd_task_end_date,nvl(pd_project_end_date,pd_schedule_date-pn_tolerance_days+1))) THEN
166 n_exception_days:=trunc(nvl(pd_task_end_date,pd_project_end_date)) - (trunc(pd_schedule_date)-pn_tolerance_days);
167 END IF;
168 END IF;
169
170 RETURN n_exception_days;
174
171
172 END PJM_EXCEPTION_DAYS;
173
175 FUNCTION PJM_SELECT_PROJECT_MANAGER(pn_project_id IN number
176 ) return varchar2
177 IS
178 c_project_manager varchar2(240):='';
179
180 cursor cu_project_manager is
181 select fu.user_name project_manager
182 from fnd_user fu
183 ,pa_project_players ppp
184 ,pa_projects_all ppa
185 where fu.employee_id =ppp.person_id
186 and nvl(fu.end_date,sysdate) >=sysdate
187 and ppp.project_role_type = 'PROJECT MANAGER'
188 and ppp.project_id = ppa.project_id
189 and nvl(ppp.end_date_active,sysdate) >= sysdate
190 and ppa.project_id = pn_project_id
191 order by fu.user_name
192 ;
193 BEGIN
194 open cu_project_manager;
195 FETCH cu_project_manager INTO c_project_manager;
196 close cu_project_manager;
197
198 return c_project_manager;
199
200 END PJM_SELECT_PROJECT_MANAGER;
201
202 FUNCTION PJM_SELECT_TASK_MANAGER(pn_task_id IN number
203 ) return varchar2
204 IS
205 c_task_manager varchar2(240):='';
206
207 cursor cu_task_manager is
208 select fu.user_name task_manager
209 from fnd_user fu
210 ,pa_tasks pt
211 where fu.employee_id =pt.task_manager_person_id
212 and nvl(fu.end_date,sysdate) >=sysdate
213 and pt.task_id = pn_task_id
214 order by fu.user_name
215 ;
216 BEGIN
217 open cu_task_manager;
218 FETCH cu_task_manager INTO c_task_manager;
219 close cu_task_manager;
220
221 return c_task_manager;
222 END PJM_SELECT_TASK_MANAGER;
223
224 PROCEDURE SELECT_DOCUMENT_TYPE(ITEMTYPE IN VARCHAR2
225 ,ITEMKEY IN VARCHAR2
226 ,ACTID IN NUMBER
227 ,FUNCMODE IN VARCHAR2
228 ,RESULTOUT OUT NOCOPY VARCHAR2
229 ) IS
230 BEGIN
231 if (funcmode='RUN') then
232 resultout:=wf_engine.getitemattrtext(itemtype => itemtype
233 ,itemkey => itemkey
234 ,aname => 'DOCUMENT_TYPE'
235 );
236 return;
237 end if;
238 if (funcmode='CANCEL') then
239 resultout:='COMPLETE:';
240 return;
241 end if;
242 if (funcmode='TIMEOUT') then
243 resultout:='COMPLETE:';
244 return;
245 end if;
246 END SELECT_DOCUMENT_TYPE;
247
248 PROCEDURE PJM_WF_SEEK_PROJECT_MGR( itemtype in varchar2,
249 itemkey in varchar2,
250 actid in number,
251 funcmode in varchar2,
252 resultout out nocopy varchar2)
253 IS
254 xc_project_manager varchar2(80) :=
255 wf_engine.GetItemAttrText( itemtype => itemtype,
256 itemkey => itemkey,
257 aname => 'PROJECT_MANAGER');
258
259 BEGIN
260 if (funcmode = 'RUN') then
261 if (xc_project_manager is not null) then
262 resultout := 'COMPLETE:FOUND';
263 else
264 resultout := 'COMPLETE:NOT_FOUND';
265 end if;
266 return;
267 end if;
268 if (funcmode = 'CANCEL') then
269 resultout := 'COMPLETE:';
270 return;
271 end if;
272 if (funcmode = 'TIMEOUT') then
273 resultout := 'COMPLETE:';
274 return;
275 end if;
276 EXCEPTION
277 when others then
278 wf_core.context('PJM_INTEGRATION_PROJ_MFG', 'PJM_WF_SEEK_PROJECT_MGR',itemtype, itemkey, actid,funcmode,resultout);
279 raise;
280
281 END PJM_WF_SEEK_PROJECT_MGR;
282
283 PROCEDURE PJM_WF_SEEK_TASK_MGR( itemtype in varchar2,
284 itemkey in varchar2,
285 actid in number,
286 funcmode in varchar2,
287 resultout out nocopy varchar2)
288 IS
289 xc_task_manager varchar2(80) :=
290 wf_engine.GetItemAttrText( itemtype => itemtype,
291 itemkey => itemkey,
292 aname => 'TASK_MANAGER');
293
294 BEGIN
295 if (funcmode = 'RUN') then
296 if (xc_task_manager is not null) then
297 resultout := 'COMPLETE:FOUND';
298 else
299 resultout := 'COMPLETE:NOT_FOUND';
300 end if;
301 return;
302 end if;
303 if (funcmode = 'CANCEL') then
304 resultout := 'COMPLETE:';
305 return;
306 end if;
307 if (funcmode = 'TIMEOUT') then
308 resultout := 'COMPLETE:';
309 return;
310 end if;
311 EXCEPTION
312 when others then
313 wf_core.context('PJM_INTEGRATION_PROJ_MFG', 'PJM_WF_SEEK_TASK_MGR',itemtype, itemkey, actid,funcmode,resultout);
314 raise;
315 END PJM_WF_SEEK_TASK_MGR;
316
317
318 END PJM_INTEGRATION_PROJ_MFG;
[Home] [Help]