1 PACKAGE BODY PA_CROSS_BUSINESS_GRP
2 -- $Header: PAXCBGAB.pls 120.2 2005/08/19 17:11:01 mwasowic noship $
3 AS
4
5 G_package_name VARCHAR2(30) := 'Pa_Cross_Business_Grp';
6
7 /* The following global variables are used to track the size of the
8 * plsql table and the name and type of plsql tables being used.
9 */
10 TABLE_SIZE BINARY_INTEGER := 0;
11 type VAL_TAB_TYPE is table of NUMBER index by binary_integer;
12 type NAME_TAB_TYPE is table of varchar2(80) index by binary_integer;
13
14 G_FromJobToGrp NAME_TAB_TYPE;
15 G_ToJob VAL_TAB_TYPE;
16
17
18
19 FUNCTION IsMappedToJob (P_From_Job_Id IN NUMBER, P_To_Job_Group_Id IN NUMBER ) RETURN NUMBER
20
21 IS
22
23 /* This function should be used by views and conditional type of needs to return the
24 * ToJobId based on the FromJobId and ToJobGroupId passed in. Note that we use view
25 * pa_job_relationships_view to accomplish this if the FromJobId ToJobGroupId has not
26 * been stored in the plsql table already. If view has to be checked to get the
27 * ToJobId then the value retrieved will be placed in the plsql table for performance
28 * improvements.
29 */
30
31 l_To_Job_Id NUMBER ;
32 l_From_Job_Grp_Id NUMBER;
33 l_Status_Code VARCHAR2(30) ;
34 l_Error_Stage VARCHAR2(250) ;
35 l_Error_Code NUMBER ;
36 l_FromJobToGrp VARCHAR2(30);
37
38 TABLE_INDEX binary_integer;
39
40 BEGIN
41
42 l_FromJobToGrp := to_char(P_From_Job_Id) || '-' || to_char(P_To_Job_Group_Id);
43
44 /* Try are retrieve from the plsql table the index that corrolates to the
45 * concatenated combination of FromJobId ToJobGroupId.
46 */
47 TABLE_INDEX := FindJobIndex(l_FromJobToGrp);
48
49 If TABLE_INDEX < TABLE_SIZE Then
50 /* If an index is returned that is less than the table size then we how found a
51 * match and can return the value from the other plsql table that stores the
52 * ToJobId. Note that the first time thru table_index and table_size are the
53 * same value 0. So it knows to do the else the first time thru.
54 */
55 RETURN( G_ToJob(TABLE_INDEX) ) ;
56 Else
57 /* If the table_index value is not less than table_size then no match was
58 * found so get the value from the view and store it in plsql table using the
59 * current table size as the next available index to use. Then increment
60 * table_size by one so that it has a count of 1 more that the size
61 * of the plsql table.
62 */
63
64 SELECT job_group_id
65 INTO l_From_Job_Grp_Id
66 FROM per_jobs
67 WHERE job_id = P_From_Job_Id ;
68
69 If l_From_Job_Grp_Id IS NULL Then
70 /* Job Group Id is not a NOT NULL column so must check for it being null */
71 RETURN ( NULL ) ;
72
73 ElsIf l_From_Job_Grp_Id = P_To_Job_Group_Id Then
74 /* If the From/To Job Groups are same then return the From Job Id
75 * This can be the case if the customer chooses not the define relationships
76 * at all or chooses the HR job group for either cost job group or bill job group.
77 */
78 G_FromJobToGrp(TABLE_SIZE) := l_FromJobToGrp;
79 G_ToJob(TABLE_SIZE) := P_From_Job_Id ;
80
81 TABLE_SIZE := TABLE_SIZE + 1;
82 RETURN ( P_From_Job_Id ) ;
83
84 Else
85 /* If the From/To Job Groups are different then get the To Job Id from the
86 * job relationships view.
87 */
88 select to_job_id
89 into l_To_Job_Id
90 from pa_job_relationships_view
91 where from_job_id = P_From_Job_Id
92 and to_job_group_id = P_To_Job_Group_Id ;
93
94 G_FromJobToGrp(TABLE_SIZE) := l_FromJobToGrp;
95 G_ToJob(TABLE_SIZE) := l_To_Job_Id;
96
97 TABLE_SIZE := TABLE_SIZE + 1;
98
99 RETURN ( l_To_Job_Id ) ;
100 End If;
101 End If;
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 RETURN ( NULL );
106 WHEN OTHERS THEN
107 RAISE ;
108
109 END IsMappedToJob;
110
111 PROCEDURE GetMappedToJob (
112 P_From_Job_Id IN NUMBER,
113 P_To_Job_Group_Id IN NUMBER,
114 X_To_Job_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
115 X_Status_Code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
116 X_Error_Stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
117 X_Error_Code OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
118
119 IS
120 /* This procedure is used when you only looking for a single
121 * value being returned. It populates locally defined plsql tables
122 * and then passes the values to the the GetMappedToJobs which handles
123 * arrays of data for process and requires that arrays of data be passed
124 * to it. Once returning from GetMappedToJobs the single value is extracted
125 * and returned in the OUT variable.
126 */
127 l_From_Job_Id_Tab PA_PLSQL_DATATYPES.IdTabTyp;
128 l_To_Job_Group_Id_Tab PA_PLSQL_DATATYPES.IdTabTyp;
129 l_To_Job_Id_Tab PA_PLSQL_DATATYPES.IdTabTyp;
130 l_Status_Code_Tab PA_PLSQL_DATATYPES.Char30TabTyp;
131 l_Error_Stage VARCHAR2(150) := NULL ;
132 l_Error_Code NUMBER := NULL ;
133
134 BEGIN
135
136 pa_cc_utils.set_curr_function(G_package_name || '.GetMappedToJob().');
137 pa_cross_business_grp.ErrorStage(
138 P_Message => '20.10: Assign input variables to local table variables.',
139 X_Stage => X_Error_Stage);
140
141 l_From_Job_Id_Tab(1) := P_From_Job_Id ;
142 l_To_Job_Group_Id_Tab(1) := P_To_Job_Group_Id ;
143
144 /* The following two variables are set to NULL intentionally. */
145 l_To_Job_Id_Tab(1) := NULL ;
146 l_Status_Code_Tab(1) := NULL ;
147
148 pa_cross_business_grp.ErrorStage(
149 P_Message => '20.20: Calling procedure ' || G_package_name || '.GetMappedToJobs().',
150 X_Stage => X_Error_Stage );
151
152 pa_cross_business_grp.GetMappedToJobs (
153 P_From_Job_Id_Tab => l_From_Job_Id_Tab ,
154 P_To_Job_Group_Id_Tab => l_To_Job_Group_Id_Tab ,
155 X_To_Job_Id_Tab => l_To_Job_Id_Tab ,
156 X_StatusTab => l_Status_Code_Tab ,
157 X_Error_Stage => l_Error_Stage ,
158 X_Error_Code => l_Error_Code ) ;
159
160 pa_cross_business_grp.ErrorStage(
161 P_Message => '20.30: Assigning returned value of To_Job_id.',
162 X_Stage => X_Error_Stage );
163
164 X_To_Job_Id := l_To_Job_Id_Tab(1) ;
165
166 pa_cross_business_grp.ErrorStage(
167 P_Message => '20.40: Exiting the procedure ' || G_package_name || '.GetMappedToJob().',
168 X_Stage => X_Error_Stage );
169
170 pa_cc_utils.reset_curr_function;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 RAISE ;
175
176 END GetMappedToJob;
177
178 PROCEDURE GetMappedToJobs (
179 P_From_Job_Id_Tab IN PA_PLSQL_DATATYPES.IdTabTyp,
180 P_To_Job_Group_Id_Tab IN PA_PLSQL_DATATYPES.IdTabTyp,
181 X_To_Job_Id_Tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
182 X_StatusTab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
183 X_Error_Stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
184 X_Error_Code OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
185
186 IS
187 /* This procedure is designed to process table arrays so that a single
188 * run will process 1 or more rows of data before returning the calling
189 * procedure/pro*c program.
190
191 /* This cursor is used to retrieve the to_job_id based on the FromJobId and
192 * ToGrpId passed in if the combination was not already found and stored in
193 * plsql table array within this current sql session.
194 */
195 cursor getJob( from_job_id IN NUMBER,
196 to_grp_id IN NUMBER) IS
197 select to_job_id
198 from pa_job_relationships_view
199 where from_job_id = from_job_id
200 and to_job_group_id = to_grp_id ;
201
202 l_MinRecs NUMBER ;
203 l_MaxRecs NUMBER ;
204 l_From_Job_Grp_Id NUMBER ;
205
206 l_FromJobToGrp VARCHAR2(30);
207
208 TABLE_INDEX binary_integer;
209
210 BEGIN
211
212 pa_cc_utils.set_curr_function(G_package_name || '.GetMappedToJobs().');
213
214 pa_cross_business_grp.ErrorStage(
215 P_Message => '30.10: Assign Min and Max recs based on number of records in table.',
216 X_Stage => X_Error_Stage );
217
218 l_MinRecs := P_From_Job_Id_Tab.FIRST ;
219 l_MaxRecs := P_From_Job_Id_Tab.LAST ;
220
221 pa_cross_business_grp.ErrorStage(
222 P_Message => '30.15: Begin looping thru all the records in the table and process the data.',
223 X_Stage => X_Error_Stage );
224
225 /* From the passed in arrays a determination is made on the number of records that
226 * have been passed in and need to be processed in the LOOP.
227 */
228 FOR j IN l_MinRecs..l_MaxRecs
229 LOOP
230
231 pa_cross_business_grp.ErrorStage(
232 P_Message => '30.20: Concatenate the From_Job_Id with To_Job_Group_Id.',
233 X_Stage => X_Error_Stage );
234
235 l_FromJobToGrp := to_char(P_From_Job_Id_Tab(j)) || '-' ||
236 to_char(P_To_Job_Group_Id_Tab(j));
237
238 pa_cross_business_grp.ErrorStage(
239 P_Message => '30.25: Check if the job is already stored in the array table.',
240 X_Stage => X_Error_Stage );
241
242 /* Try are retrieve from the plsqsql table the index that corrolates to the
243 * concatenated combination of FromJobId ToJobGroupId.
244 */
245 TABLE_INDEX := FindJobIndex(l_FromJobToGrp);
246
247 IF TABLE_INDEX < TABLE_SIZE THEN
248 /* If table_index is that is less than the table size then we how found a
249 * match and can return the value from the other plsql table that stores
250 * the ToJobId. Note that the first time thru table_index and table_size
251 * are the same value 0. So it knows to do the else the first time thru.
252 */
253 X_To_Job_Id_Tab(j) := G_ToJob(TABLE_INDEX);
254
255 ELSE
256 /* If the table_index value is not less than table_size then no match was
257 * found, get the value from the cursor and store in plsql table using the
258 * current table size as the next available index to use. Then increment
259 * table_size by one so that it has a count of 1 more that the size
260 * of the plsql table.
261 */
262
263 pa_cross_business_grp.ErrorStage(
264 P_Message => '30.26: Get GroupId for From Job Id.',
265 X_Stage => X_Error_Stage );
266
267 SELECT job_group_id
268 INTO l_From_Job_Grp_Id
269 FROM per_jobs
270 WHERE job_id = P_From_Job_Id_Tab(j) ;
271
272 If l_From_Job_Grp_Id = P_To_Job_Group_Id_Tab(j) Then
273
274 pa_cross_business_grp.ErrorStage(
275 P_Message => '30.28: If From/To Job Groups are same then return From Job Id.',
276 X_Stage => X_Error_Stage );
277
278 G_FromJobToGrp(TABLE_SIZE) := l_FromJobToGrp;
279 G_ToJob(TABLE_SIZE) := P_From_Job_Id_Tab(j);
280
281 TABLE_SIZE := TABLE_SIZE + 1;
282
283 Else
284 pa_cross_business_grp.ErrorStage(
285 P_Message => '30.30: Open the cursor getJob.',
286 X_Stage => X_Error_Stage );
287
288 OPEN getJob(P_From_Job_Id_Tab(j), P_To_Job_Group_Id_Tab(j));
289
290 pa_cross_business_grp.ErrorStage(
291 P_Message => '30.40: Fetch record from cursor getJob.',
292 X_Stage => X_Error_Stage );
293
294 FETCH getJob
295 INTO X_To_Job_Id_Tab(j);
296
297 If getJob%NOTFOUND Then
298
299 pa_cross_business_grp.ErrorStage(
300 P_Message => '30.50: If NO_DATA_FOUND then set variables appropriately.',
301 X_Stage => X_Error_Stage );
302
303 X_To_Job_Id_Tab(j) := NULL;
304 X_StatusTab(j) := 'PA_CBGA_NO_MAPPING_EXISTS' ;
305
306 Else
307
308 pa_cross_business_grp.ErrorStage(
309 P_Message => '30.55: If FOUND then store in table arrays.',
310 X_Stage => X_Error_Stage );
311
312 G_FromJobToGrp(TABLE_SIZE) := l_FromJobToGrp;
313 G_ToJob(TABLE_SIZE) := X_To_Job_Id_Tab(j);
314
315 TABLE_SIZE := TABLE_SIZE + 1;
316 End If;
317
318 pa_cross_business_grp.ErrorStage(
319 P_Message => '30.60: Close cursor getJob.',
320 X_Stage => X_Error_Stage );
321
322 Close getJob;
323
324 End If;
325 End If;
326
327 pa_cross_business_grp.ErrorStage(
328 P_Message => '30.70: End Loop.',
329 X_Stage => X_Error_Stage );
330
331 END LOOP ;
332
333 pa_cross_business_grp.ErrorStage(
334 P_Message => '30.80: Exiting procedure ' || G_package_name || '.GetMappedToJobs().',
335 X_Stage => X_Error_Stage );
336
337 pa_cc_utils.reset_curr_function;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 RAISE ;
342
343 END GetMappedToJobs ;
344
345 FUNCTION IsCrossBGProfile RETURN VARCHAR2
346
347 IS
348
349 l_Status_Code VARCHAR2(30) ;
350 l_Error_Stage VARCHAR2(150) ;
351 l_Error_Code NUMBER;
352
353 BEGIN
354 IF pa_cross_business_grp.G_CrossBGProfile IS NULL THEN
355 pa_cross_business_grp.G_CrossBGProfile := FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP');
356 END IF;
357
358
359 RETURN (pa_cross_business_grp.G_CrossBGProfile ) ;
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 RAISE ;
364
365 END IsCrossBGProfile ;
366
367
368 PROCEDURE GetMasterGrpId (
369 P_Business_Group_Id IN NUMBER DEFAULT NULL,
370 X_Master_Grp_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
371 X_Status_Code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
372 X_Error_Stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
373 X_Error_Code OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
374
375 IS
376 BUSINESS_GROUP_NEEDED EXCEPTION;
377
378 BEGIN
379 pa_cc_utils.set_curr_function(G_package_name || '.GetMasterGrpId().');
380
381 pa_cross_business_grp.ErrorStage(
382 P_Message => '40.10: Check if the Global Master Group Id is already populated.',
383 X_Stage => X_Error_Stage );
384
385 /* If the Global variable G_MasterGroupId is null then we need to get it
386 * and store in the the global variable otherwise we will return the value from the
387 * global variable and return it the the calling procedure. At any given point there
388 * should be only 1 master group id for a given business group/enterprise so a global
389 * variable can be used.
390 */
391 If pa_cross_business_grp.G_MasterGroupId IS NULL Then
392
393 pa_cross_business_grp.ErrorStage(
394 P_Message => '40.20: Check if the Business Group is NULL.',
395 X_Stage => X_Error_Stage );
396
397 /* IF the profile HR_CROSS_BUSINESS_GROUP is 'N' Then it is expected that the
398 * calling procedure will pass in the business group since it is then required.
399 */
400 If pa_cross_business_grp.IsCrossBGProfile = 'N' AND P_Business_Group_Id IS NULL Then
401 RAISE BUSINESS_GROUP_NEEDED;
402 End If;
403
404 pa_cross_business_grp.ErrorStage(
405 P_Message => '40.30: Get the Master Group Id.',
406 X_Stage => X_Error_Stage );
407
408 /* Based on the profile HR_CROSS_BUSINESS_GROUP this select statement will
409 * return the master group id. Note that the way it is written if the customer
410 * has more than a single master group or no master group for either the
411 * enterprise or the business group defined then either TOO MANY ROWS or
412 * NO DATA FOUND will occur and will be handled appropriately in the
413 * exception handler. This can occur since HR is not restricting the form
414 * they are creating in this way, so we have to check and handle this in PA.
415 */
416 select job_group_id
417 into x_master_grp_id
418 from per_job_groups
419 where pa_cross_business_grp.IsCrossBGProfile = 'N'
420 and business_group_id = P_Business_group_Id
421 and master_flag = 'Y'
422 UNION ALL
423 select job_group_id
424 from per_job_groups
425 where pa_cross_business_grp.IsCrossBGProfile = 'Y'
426 and master_flag = 'Y' ;
427
428 pa_cross_business_grp.ErrorStage(
429 P_Message => '40.40: Assign master group id to Global variable.',
430 X_Stage => X_Error_Stage );
431
432 pa_cross_business_grp.G_MasterGroupId := x_master_grp_id;
433
434 Else
435
436 pa_cross_business_grp.ErrorStage(
437 P_Message => '40.50: Get master group id from Global variable.',
438 X_Stage => X_Error_Stage );
439
440 x_master_grp_id := pa_cross_business_grp.G_MasterGroupId;
441
442 End If;
443
444 pa_cross_business_grp.ErrorStage(
445 P_Message => '40.60:Exiting procedure ' || G_package_name || '.GetMasterGrpId().',
446 X_Stage => X_Error_Stage );
447
448 pa_cc_utils.reset_curr_function;
449
450
451 EXCEPTION
452 WHEN BUSINESS_GROUP_NEEDED THEN
453 X_Status_Code := 'PA_CBGA_BG_NEEDED' ;
454
455 WHEN TOO_MANY_ROWS THEN
456 /* This can occur if the customer defines more than 1 master group for the
457 * enterprise or business group which is being used.
458 */
459 If pa_cross_business_grp.IsCrossBGProfile = 'Y' Then
460 X_Status_Code := 'PA_CBGA_MULTI_GRP_G' ;
461 Else
462 X_Status_Code := 'PA_CBGA_MULTI_GRP_S' ;
463 End If;
464
465 WHEN NO_DATA_FOUND THEN
466 /* This can occur if the customer has not flagged any of the job group for either
467 * the enterprise or business group, as appropriate.
468 */
469 If pa_cross_business_grp.IsCrossBGProfile = 'Y' Then
470 X_Status_Code := 'PA_CBGA_NO_MASTER_G' ;
471 Else
472 X_Status_Code := 'PA_CBGA_NO_MASTER_S' ;
473 End If;
474
475 WHEN OTHERS THEN
476 x_error_code := SQLCODE;
477 RAISE;
478
479
480 END GetMasterGrpId ;
481
482 PROCEDURE GetGlobalHierarchy (
483 P_Org_Structure_Id IN NUMBER,
484 X_Global_Hierarchy OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
485 X_Status_Code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
486 X_Error_Stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
487 X_Error_Code OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
488
489 IS
490
491 BEGIN
492
493 pa_cc_utils.set_curr_function(G_package_name || '.GetGlobalHierarchy().');
494
495 pa_cross_business_grp.ErrorStage(
496 P_Message => '50.10: Get the Global Hierarchy Flag using the Org Structure Id.',
497 X_Stage => X_Error_Stage );
498
499 Select decode(business_group_id,NULL,'Y','N')
500 into X_Global_Hierarchy
501 from per_organization_structures
502 where organization_structure_id = P_Org_Structure_id ;
503
504
505 pa_cross_business_grp.ErrorStage(
506 P_Message => '50.20:Exiting procedure ' || G_package_name || '.GetGlobalHierarchy().',
507 X_Stage => X_Error_Stage );
508
509 pa_cc_utils.reset_curr_function;
510
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 X_Status_Code := 'PA_CBGA_BAD_ORG_STRC';
514 WHEN OTHERS THEN
515 RAISE ;
516
517 END GetGlobalHierarchy ;
518
519
520 PROCEDURE GetJobIds ( P_HR_Job_Id IN NUMBER,
521 P_Project_Id IN NUMBER,
522 X_Bill_Job_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
523 X_Bill_Job_Grp_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
524 X_Cost_Job_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
525 X_Cost_Job_Grp_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
526 X_PP_Bill_Job_Id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
527 X_Status_Code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
528 X_Error_Stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
529 X_Error_Code OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
530
531 IS
532
533 /* This procedure is assumed to be called from TRX IMPORT.
534 * TRX Import has to derive the Job since it is not provide in table
535 * pa_transaction_interface so it passed in the HR Job Id. Since TRX
536 * IMPORT has to validate the Project that is in each record in the
537 * pa_transaction_interface table TRX IMPORT passes in the project Id
538 * as well.
539 * The default Bill Job Group Id and Cost Job Group Id are retrieved and
540 * then the cost job id and the bill job id all based on the HR Job Id
541 * that is defined in HR for the employee.
542 */
543 MISSING_DATA EXCEPTION;
544 l_cost_job_group_id NUMBER ;
545 l_bill_job_group_id NUMBER ;
546 l_group_type VARCHAR2(1);
547 l_HR_Job_Group_Id NUMBER ;
548
549 BEGIN
550
551 pa_cc_utils.set_curr_function(G_package_name || '.GetJobIds().');
552
553 pa_cross_business_grp.ErrorStage(
554 P_Message => '60.05: Get the default cost job group for the project.',
555 X_Stage => X_Error_Stage );
556
557 l_group_type := 'C';
558
559 l_cost_job_group_id :=
560 pa_cross_business_grp.GetDefProjJobGrpId( P_Project_Id => P_Project_id,
561 P_Group_Type => l_group_type);
562
563 pa_cross_business_grp.ErrorStage(
564 P_Message => '60.10: Check if the default cost job group is populated.',
565 X_Stage => X_Error_Stage );
566
567 If l_cost_job_group_id IS NULL Then
568 /* There does not have to be a default cost job group defined for
569 * a project. This is optional. In the case that it is not
570 * defined then return the cost_job_id, cost_grp_id as NULL.
571 */
572 X_Cost_Job_Grp_Id := NULL;
573 X_Cost_Job_Id := NULL;
574 Else
575
576 pa_cross_business_grp.ErrorStage(
577 P_Message => '60.20: Assign the cost job group id to the out variable.',
578 X_Stage => X_Error_Stage );
579
580 X_Cost_Job_Grp_Id := l_cost_job_group_id;
581
582 pa_cross_business_grp.ErrorStage(
583 P_Message => '60.80: Get the cost job id.',
584 X_Stage => X_Error_Stage );
585
586 X_Cost_Job_Id := IsMappedToJob( P_From_Job_id => P_HR_Job_Id,
587 P_To_Job_Group_id => l_cost_job_group_id ) ;
588
589 pa_cross_business_grp.ErrorStage(
590 P_Message => '60.90: Check if the cost job id is NULL.',
591 X_Stage => X_Error_Stage );
592
593 If X_Cost_Job_Id IS NULL Then
594 X_Status_Code := 'PA_CBGA_NO_COST_JOB';
595 RAISE MISSING_DATA;
596 End If;
597 End If;
598
599 pa_cross_business_grp.ErrorStage(
600 P_Message => '60.25: Get the default bill job group for the project.',
601 X_Stage => X_Error_Stage );
602
603 l_group_type := 'B';
604
605 l_bill_job_group_id :=
606 pa_cross_business_grp.GetDefProjJobGrpId( P_Project_Id => P_Project_id,
607 P_Group_Type => l_group_type) ;
608
609 pa_cross_business_grp.ErrorStage(
610 P_Message => '60.30: Check if the default bill job group is populated.',
611 X_Stage => X_Error_Stage );
612
613 If l_bill_job_group_id IS NOT NULL Then
614
615 pa_cross_business_grp.ErrorStage(
616 P_Message => '60.40: Assign bill job group to the out variable.',
617 X_Stage => X_Error_Stage );
618
619 X_Bill_Job_Grp_Id := l_bill_job_group_id;
620
621 pa_cross_business_grp.ErrorStage(
622 P_Message => '60.50: Get the bill job id.',
623 X_Stage => X_Error_Stage );
624
625 X_Bill_Job_Id := IsMappedToJob( P_From_Job_id => P_HR_Job_Id,
626 P_To_Job_Group_id => l_bill_job_group_id) ;
627
628 pa_cross_business_grp.ErrorStage(
629 P_Message => '60.60: Check if the bill job id is NULL.',
630 X_Stage => X_Error_Stage );
631
632 If X_Bill_Job_Id IS NULL Then
633 X_Status_Code := 'PA_CBGA_NO_BILL_JOB';
634 RAISE MISSING_DATA;
635 End If;
636
637 pa_cross_business_grp.ErrorStage(
638 P_Message => '60.70: Assign bill job id to PP bill job id.',
639 X_Stage => X_Error_Stage );
640
641 X_PP_Bill_Job_Id := X_Bill_Job_Id;
642
643 Else
644 /* There does not have to be a default bill job group defined for
645 * a project. This is optional. In the case that it is not
646 * defined then return the bill_job_id, pp_bill_job_id, bill_grp_id
647 * as NULL.
648 */
649 X_Bill_Job_Grp_Id := NULL;
650 X_Bill_Job_Id := NULL;
651 X_PP_Bill_Job_Id := NULL;
652 End If;
653
654 pa_cc_utils.reset_curr_function;
655
656 EXCEPTION
657 WHEN MISSING_DATA THEN
658 If pa_cross_business_grp.G_CrossBGProfile = 'Y' THEN
659 X_Status_Code := X_Status_Code || 'G' ;
660 Else
661 X_Status_Code := X_Status_Code || 'S' ;
662
663 End If;
664 WHEN OTHERS THEN
665 RAISE ;
666 END GetJobIds ;
667
668 FUNCTION GetDefProjJobGrpId (P_Project_Id IN NUMBER,
669 P_Group_Type IN VARCHAR2 ) RETURN NUMBER
670
671 IS
672 l_JobGroupId NUMBER := NULL ;
673 BEGIN
674
675 /* If P_Group_Type := 'C' then the cost job group id needs to be returned
676 * If P_Group_Type := 'B' then the bill job group id needs to be returned
677 * If the project for some reason does not exist then NULL is returned.
678 */
679
680 select decode(P_Group_Type,'C',cost_job_group_id,'B',bill_job_group_id)
681 into l_JobGroupId
682 from pa_projects_all
683 where project_id = P_Project_Id ;
684
685 RETURN ( l_JobGroupId ) ;
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 RETURN ( to_number( NULL ) ) ;
690 WHEN OTHERS THEN
691 RAISE ;
692
693 END GetDefProjJobGrpId ;
694
695 FUNCTION FindJobIndex ( P_From_Job_To_Grp IN VARCHAR2 ) RETURN BINARY_INTEGER
696
697 IS
698
699 /* TABLE_SIZE is always one larger that the number of records stored in
700 * the plsql table except the first time that this function is called at
701 * which time both the tab_index and table_size will be 0 and the function
702 * immediately returns 0 to the calling procedure which is fine.
703 */
704
705 TAB_INDEX binary_integer;
706 FOUND boolean;
707
708 BEGIN
709 TAB_INDEX := 0;
710 FOUND := false;
711
712 /* Passing in the concatenated value the check for in the plsql table
713 * G_FromJobToGrp we are looking for match so that we can return the index
714 * back to the calling procedure/function. If not FOUND is to break out the
715 * the loop if a match is found before getting to the end of the plsql table.
716 * Once a match is found the FOUND := TRUE and the the loop is stopped.
717 * Until a match is found or reaching the end of the plsqpl table keep
718 * on incrementing tab_index by 1. If the index is found the return it to the
719 * calling procedure it will be the same number as the table_size which is what
720 * we want.
721 */
722 while (TAB_INDEX < TABLE_SIZE) and (not FOUND) loop
723 if G_FromJobToGrp(TAB_INDEX) = P_From_Job_To_Grp then
724 FOUND := true;
725 else
726 TAB_INDEX := TAB_INDEX + 1;
727 end if;
728 end loop;
729
730 return TAB_INDEX;
731
732 END FindJobIndex;
733
734 FUNCTION HRJobGroupIs ( P_Business_Group_Id IN NUMBER ) RETURN NUMBER
735
736 IS
737
738 JobGroup NUMBER;
739
740 BEGIN
741
742 SELECT JOB_GROUP_ID
743 INTO JobGroup
744 FROM PER_JOB_GROUPS
745 WHERE BUSINESS_GROUP_ID = P_Business_Group_Id
746 AND INTERNAL_NAME LIKE 'HR_%';
747
748 RETURN ( JobGroup );
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 RETURN ( NULL );
753
754 END HRJobGroupIs;
755
756 PROCEDURE ErrorStage( P_Message IN VARCHAR2,
757 X_Stage OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
758
759 IS
760 /* This is a centralized location to indicate the location in the package is in
761 * at each step of the way so as to be able to pinpoint if an error occurs where
762 * it happened.
763 */
764
765 BEGIN
766
767 X_Stage := P_Message;
768 pa_cc_utils.log_message(P_Message);
769
770 END ErrorStage;
771
772 /* End of API */
773 END ;
[Home] [Help]