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