4 PROCEDURE customer_exists
1 PACKAGE BODY PA_PROJECT_VERIFY_PKG AS
2 /* $Header: PAXPRVRB.pls 120.4 2011/04/01 06:59:34 kkorrapo ship $ */
3
5 (x_project_id IN NUMBER,
9 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
6 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
7 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
8 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10 CURSOR get_top_task_cust_enbled IS
11 SELECT enable_top_task_customer_flag
12 FROM pa_projects_all
13 WHERE project_id = x_project_id;
14 l_en_top_task_cust_flag VARCHAR2(1);
15
16 --added the below code for Federal changes by sunkalya
17 --sunkalya:federal Bug#5511353
18 CURSOR get_date_eff_funds_flag
19 IS
20 SELECT nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
21 FROM
22 pa_projects_all
23 WHERE project_id = x_project_id;
24 l_date_eff_funds_flag VARCHAR2(1);
25
26 --end of code added for federal changes by sunkalya
27 --sunkalya:federal Bug#5511353
28
29 x_old_stack varchar2(630);
30 dummy number;
31 begin
32 x_err_code := 0;
33 x_old_stack := x_err_stack;
34 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.client_exists';
35 x_err_msgname := NULL;
36 x_err_stage := 'Checking client exists...';
37
38 OPEN get_top_task_cust_enbled;
39 FETCH get_top_task_cust_enbled INTO l_en_top_task_cust_flag;
40 CLOSE get_top_task_cust_enbled;
41
42 OPEN get_date_eff_funds_flag;
43 FETCH get_date_eff_funds_flag INTO l_date_eff_funds_flag;
44 CLOSE get_date_eff_funds_flag;
45
46 --modified the below if for federal changes by sunkalya
47 --sunkalya:federal Bug#5511353
48 IF l_en_top_task_cust_flag <> 'Y' AND l_date_eff_funds_flag <> 'Y' THEN
49 SELECT NULL
50 INTO dummy
51 FROM sys.dual
52 WHERE exists (
53 SELECT NULL
54 FROM PA_PROJECT_CUSTOMERS
55 WHERE PROJECT_ID = x_project_id
56 GROUP BY PROJECT_ID
57 HAVING SUM(CUSTOMER_BILL_SPLIT) = 100);
58 --bug#10633080 - addition start
59 ELSIF l_en_top_task_cust_flag <> 'Y' AND l_date_eff_funds_flag = 'Y' THEN
60 SELECT NULL
61 INTO dummy
62 FROM PA_PROJECT_CUSTOMERS
63 WHERE PROJECT_ID = x_project_id
64 AND rownum < 2;
65 --bug#10633080 - addition end
66 END IF;
67
68 x_err_stack := x_old_stack;
69 exception
70 when NO_DATA_FOUND then
71 x_err_code := 10;
72 x_err_stage := 'PA_NO_CLIENT_EXISTS';
73 x_err_msgname := 'PA_PR_INSUF_BILL_SPLIT';
74 when others then
75 x_err_code := SQLCODE;
76 end customer_exists;
77
78 PROCEDURE contact_exists
79 (x_project_id IN NUMBER,
80 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
81 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
82 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
83 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
84 x_old_stack varchar2(630);
85 dummy number;
86 begin
87 x_err_code := 0;
88 x_old_stack := x_err_stack;
89 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.contact_exists';
90 x_err_msgname := NULL;
91 x_err_stage := 'Checking contact exists...';
92 SELECT NULL
93 INTO dummy
94 FROM sys.dual
95 WHERE exists (
96 SELECT NULL
97 FROM PA_PROJECT_CUSTOMERS CUST
98 WHERE CUST.PROJECT_ID = x_project_id
99 AND CUST.CUSTOMER_BILL_SPLIT > 0
100 AND NOT EXISTS (SELECT NULL
101 FROM PA_PROJECT_CONTACTS CONT
102 WHERE CONT.PROJECT_ID = x_project_id
103 AND CONT.CUSTOMER_ID= CUST.CUSTOMER_ID
104 AND CONT.PROJECT_CONTACT_TYPE_CODE = 'BILLING'));
105 x_err_code := 10;
106 x_err_stage := 'PA_NO_CONTACT_EXISTS';
107 x_err_msgname := 'PA_PR_INSUF_BILL_CONTACT';
108 exception
109 when NO_DATA_FOUND then
110 x_err_stack := x_old_stack;
111 when others then
112 x_err_code := SQLCODE;
113 end contact_exists;
114
115 PROCEDURE category_required
116 (x_project_id IN NUMBER,
117 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
118 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
119 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
120 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
121 x_old_stack varchar2(630);
122 dummy number;
123
124 /*
125 The following cursor has been commented for Performance Bug # 3691123
126 The cursor is split into C11 and C12 for performance reasons so that
127 queries can be based on base tables directly.
128
129 CURSOR C1
130 IS
131 SELECT NULL
132 FROM PA_VALID_CATEGORIES_V VC,
133 PA_PROJECTS_ALL PPA,
134 PA_PROJECT_TYPES_ALL PPTA
135 WHERE VC.MANDATORY_FLAG = 'Y'
136 AND PPA.PROJECT_ID = x_project_id
137 AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
138 AND nvl(PPA.ORG_ID, -99) = nvl(PPTA.ORG_ID, -99)
139 AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
140 AND NOT EXISTS (SELECT NULL
141 FROM PA_PROJECT_CLASSES PC
142 WHERE PC.PROJECT_ID = x_project_id
143 AND PC.CLASS_CATEGORY = VC.CLASS_CATEGORY);
144 */
145
146 CURSOR C11
147 IS
148 SELECT NULL
149 FROM DUAL
150 WHERE EXISTS
151 (
152 SELECT 1
153 FROM PA_CLASS_CATEGORIES cc,
154 PA_VALID_CATEGORIES vc,
155 PA_PROJECT_TYPES_ALL PPTA,
156 PA_PROJECTS_ALL PPA
157 WHERE VC.CLASS_CATEGORY = CC.CLASS_CATEGORY
158 AND TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
159 AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
160 AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
161 AND TRUNC(SYSDATE) BETWEEN TRUNC(PPTA.START_DATE_ACTIVE)
162 AND TRUNC(NVL(PPTA.END_DATE_ACTIVE, SYSDATE))
163 AND VC.MANDATORY_FLAG = 'Y'
164 AND PPA.PROJECT_ID = x_project_id
165 AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
166 AND PPA.ORG_ID = PPTA.ORG_ID --MOAC Changes: Bug 4363092: removed nvl usage with org_id
167 AND NOT EXISTS (SELECT NULL
168 FROM PA_PROJECT_CLASSES PC
169 WHERE PC.PROJECT_ID = x_project_id
170 AND PC.CLASS_CATEGORY = VC.CLASS_CATEGORY)
171 );
172
173 CURSOR C12
174 IS
175 SELECT NULL
176 FROM DUAL
177 WHERE EXISTS (SELECT 1
178 from PA_CLASS_CATEGORIES CC
179 WHERE CC.MANDATORY_FLAG = 'Y'
180 AND CC.OBJECT_TYPE = 'PA_PROJECTS'
181 AND CC.ALL_TYPES_VALID_FLAG = 'Y'
182 AND TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
183 AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
184 AND NOT EXISTS(SELECT NULL
185 FROM PA_PROJECT_CLASSES PC
186 WHERE PC.PROJECT_ID = x_project_id
187 AND PC.CLASS_CATEGORY = CC.CLASS_CATEGORY)
188 );
189
190 /*
191 The following cursor has been commented for Performance Bug # 3691123
192 The cursor looks only for sort_order = 'A'
193 This View PA_PROJECT_CLASS_TOTALS_V has two select statements joined by UNION
194 The 1st select statement is for sort_order A and C / the 2nd select statement for B
195
196 So,the query can be based directly on the base table as in 1st select statement of the view
197 CURSOR C2
198 IS
199 SELECT NULL
200 FROM PA_PROJECT_CLASS_TOTALS_V
201 WHERE project_id = x_project_id
202 AND sort_order = 'A';
203 */
204
205 /* Start of new code for Performance Bug # 3691123 */
206 CURSOR C2
207 IS
208 SELECT NULL
209 FROM PA_PROJECT_CLASSES
210 WHERE project_id = x_project_id
211 AND OBJECT_TYPE = 'PA_PROJECTS'
212 AND decode(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(object_id,object_type, class_category, 'N'), NULL, 'C', 'A') = 'A'
213 ;
214
215 /*End code for Performance Bug # 3691123 */
216 begin
217 -- This procedure has been modified for Classification enhancements
218 -- It checks whether there are any mandatory categories that have not
219 -- been specified
220 -- It also checks if there are any categories defined for this project
221 -- that have the total 100 percent flag enabled, but whose defined
222 -- defined class codes do not actually total 100
223 x_err_code := 0;
224 x_old_stack := x_err_stack;
225 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.category_required';
226 x_err_msgname := NULL;
227 x_err_stage := 'Checking required category exists...';
228
229 OPEN C11;
230 FETCH C11 INTO dummy;
231 if C11%FOUND then
232 CLOSE C11;
233 x_err_code := 10;
234 x_err_stage := 'PA_NO_REQ_CATEGORY_EXISTS';
235 x_err_msgname := 'PA_PR_INSUF_CLASS_CODES';
236 return;
237 else
238 OPEN C12;
239 FETCH C12 INTO dummy;
240 if C12%FOUND then
241 CLOSE C12;
242 x_err_code := 10;
243 x_err_stage := 'PA_NO_REQ_CATEGORY_EXISTS';
244 x_err_msgname := 'PA_PR_INSUF_CLASS_CODES';
245 return;
246 end if;
247 CLOSE C12 ;
248 end if;
249
250 CLOSE C11 ;
251
252 x_err_stage := 'Checking total class code percentages...';
253
254 OPEN C2;
255 FETCH C2 INTO dummy;
256 if C2%FOUND then
257 CLOSE C2;
258 x_err_code := 20;
259 x_err_stage := 'PA_CLASS_TOTALS_INVALID';
260 x_err_msgname := 'PA_PR_CLASS_TOTAL_INVLD';
261 return;
262 end if;
263 CLOSE C2;
264
265 x_err_stack := x_old_stack;
266 exception
267 when others then
268 x_err_code := SQLCODE;
269 end category_required;
270
271 PROCEDURE manager_exists
272 (x_project_id IN NUMBER,
273 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
274 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
275 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
276 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
277 x_old_stack varchar2(630);
278 proj_start_date DATE;
279 km_start_date DATE;
280 km_end_date DATE;
281 l_dummy NUMBER := 0;
282 CURSOR c1 IS
283 SELECT NVL(Start_Date,trunc(Sysdate)) FROM Pa_Projects_all -- Bug#3807805 : Modified Pa_Projects to Pa_Projects_all
284 WHERE PROJECT_ID = x_project_id;
285
286 /* Added the following cursor instead of select statement to
287 handle the "too many rows selected" condition.
288 Bug fix for # 824266 */
289
290 CURSOR c2 IS
291 SELECT START_DATE_ACTIVE,END_DATE_ACTIVE
292 FROM PA_PROJECT_PLAYERS
293 WHERE PROJECT_ID = x_project_id
294 AND PROJECT_ROLE_TYPE = 'PROJECT MANAGER';
295 BEGIN
296 x_err_code := 0;
297 x_old_stack := x_err_stack;
298 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.manager_exists';
299 x_err_msgname := NULL;
300 x_err_stage := 'Checking manager exists...';
301
302 OPEN c1 ;
303 FETCH c1 INTO proj_start_date ;
304 IF c1%notfound then
305 SELECT TRUNC(Sysdate) INTO proj_start_date FROM Dual;
306 END IF;
307 CLOSE c1 ;
308
309 /* Changed the following logic to use cursor and loop */
310 /* For bug # 824266 fix */
311
312 OPEN c2;
313 LOOP
314 FETCH c2 INTO km_start_date,km_end_date ;
315 EXIT WHEN c2%NOTFOUND ;
316
317 IF TRUNC(SYSDATE) BETWEEN
318 km_start_date AND nvl(km_end_date,GREATEST(km_start_date,TRUNC(SYSDATE)))
319 OR
320 proj_start_date BETWEEN
321 km_start_date AND nvl(km_end_date,GREATEST(km_start_date,TRUNC(SYSDATE)))
322 OR
323 (proj_start_date > TRUNC(SYSDATE) AND
324 km_start_date BETWEEN TRUNC(SYSDATE) and proj_start_date
325 AND km_end_date IS NULL )
326 THEN
327 l_dummy := 0;
328 EXIT ;
329 ELSE
330 l_dummy := -1;
331 END IF;
332 END LOOP;
333
334 IF c2%ROWCOUNT = 0 THEN
335 close c2;
336 raise no_data_found;
337 END IF;
338
339 CLOSE c2;
340 /* End of changes made for bug # 824266 fix */
341
342 IF l_dummy = -1 THEN
343 x_err_code := 10;
344 x_err_stage := 'PA_NO_MANAGER_EXISTS';
345 x_err_msgname := 'PA_PR_INSUF_PROJ_MGR';
346 END IF;
347 x_err_stack := x_old_stack;
348 exception
349 when NO_DATA_FOUND then
353 when others then
350 x_err_code := 10;
351 x_err_stage := 'PA_NO_MANAGER_EXISTS';
352 x_err_msgname := 'PA_PR_INSUF_PROJ_MGR';
354 x_err_code := SQLCODE;
355 END manager_exists;
356
357 PROCEDURE revenue_budget
358 (x_project_id IN NUMBER,
359 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
360 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
361 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
362 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
363 x_old_stack varchar2(630);
364 dummy number;
365 begin
366 x_err_code := 0;
367 x_old_stack := x_err_stack;
368 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.revenue_budget';
369 x_err_msgname := NULL;
370 x_err_stage := 'Checking revenue budget exists...';
371 SELECT 'x' INTO dummy
372 FROM PA_BUDGET_VERSIONS bv,
373 PA_BUDGET_TYPES bt
374 WHERE
375 bv.budget_type_code = bt.budget_type_code
376 AND bt.budget_amount_code = 'R';
377 x_err_stack := x_old_stack;
378 exception
379 when NO_DATA_FOUND then
380 x_err_code := 10;
381 x_err_stage := 'PA_NO_REV_BUDGET_EXISTS';
382 x_err_msgname := 'PA_PR_NO_REV_BUDGET';
383 when others then
384 x_err_code := SQLCODE;
385 end revenue_budget;
386
387 PROCEDURE cost_budget
388 (x_project_id IN NUMBER,
389 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
390 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
391 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
392 x_err_msgname IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
393 x_old_stack varchar2(630);
394 dummy number;
395 begin
396 x_err_code := 0;
397 x_old_stack := x_err_stack;
398 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.cost_budget';
399 x_err_msgname := NULL;
400 x_err_stage := 'Checking cost budget exists...';
401 SELECT 'x' INTO dummy
402 FROM PA_BUDGET_VERSIONS bv,
403 PA_BUDGET_TYPES bt
404 WHERE
405 bv.budget_type_code = bt.budget_type_code
406 AND bt.budget_amount_code = 'C';
407 x_err_stack := x_old_stack;
408 exception
409 when NO_DATA_FOUND then
410 x_err_code := 10;
411 x_err_stage := 'PA_NO_COST_BUDGET_EXISTS';
412 x_err_msgname := 'PA_PR_NO_COST_BUDGET';
413 when others then
414 x_err_code := SQLCODE;
415 end cost_budget;
416
417 PROCEDURE billing_event
418 (x_project_id IN NUMBER,
419 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
420 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
421 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
422 x_err_msgname IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
423 x_eamt_token_name IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
424 x_eamt_token_value IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
425 x_old_stack varchar2(630);
426 dummy number;
427 begin
428 x_err_code := 0;
429 x_old_stack := x_err_stack;
430 x_err_stack := x_err_stack ||'->PA_PROJECT_VERIFY_PKG.billing_event';
431 x_err_msgname := NULL;
432 x_eamt_token_name := 'EAMT';
433 x_eamt_token_value := 0;
434 /* Commented out for now,since the code is incorrectly referencing
435 pa_subbudgets. Need to fix this since pa_subbudgets is obsolete
436 in Rel 11.0 - Ramesh - 01/13/1998
437 x_err_stage := 'Checking billing event exists...';
438 SELECT NULL
439 INTO dummy
440 FROM sys.dual
441 WHERE exists (
442 select NULL
443 from pa_events e
444 , pa_event_Types et
445 , pa_tasks t
446 where nvl(e.task_id,t.task_id) = t.task_id
447 and e.project_id = t.project_id
448 and e.event_type = et.event_Type
449 and t.project_id = x_project_id
450 and e.completion_date is not null
451 having sum(nvl(decode(et.event_type_classification,
452 'INVOICE REDUCTION',-e.bill_amount,
453 e.bill_amount),0)) =
454 (select sum(nvl(revenue,0))
455 from pa_subbudgets s
456 , pa_tasks t
457 where s.project_id = x_project_id
458 and s.budget_Type_code= 'DRAFT'
459 and s.task_id = t.task_id(+)
460 and t.task_id = t.top_task_id
461 ));
462 */
463 x_err_stack := x_old_stack;
464 exception
465 when NO_DATA_FOUND then
466 NULL;
467 /* Commented out since the original sql has been commented out
468 begin
469 select to_char(sum(nvl(decode(et.event_type_classification,
470 'INVOICE REDUCTION', -e.bill_amount,
471 e.bill_amount),0)))
472 into x_eamt_token_value
473 from pa_events e
474 , pa_event_Types et
475 , pa_tasks t
476 where nvl(e.task_id, t.task_id) = t.task_id
477 and e.project_id = t.project_id
478 and e.event_Type = et.event_Type
479 and t.project_id = x_project_id
480 and e.completion_date is not null;
481 exception
482 when others then
483 null;
484 end;
488 */
485 x_err_code := 10;
486 x_err_stage := 'PA_NO_BILL_EVENT_EXISTS';
487 x_err_msgname := 'PA_PR_NEED_BILLING_EVENTS';
489 when others then
490 x_err_code := SQLCODE;
491 end billing_event;
492
493 end PA_PROJECT_VERIFY_PKG;
[Home] [Help]