[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_ACCOUNT_PKG
Source
1 PACKAGE BODY PA_BUDGET_ACCOUNT_PKG AS
2 -- $Header: PABDACGB.pls 120.6.12000000.3 2007/10/26 05:42:44 pvishnoi ship $
3
4 -- ## Forward Declaration
5 PROCEDURE Upd_Budget_Acct_Line (
6 p_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
7 p_Project_ID IN PA_Projects_All.Project_ID%TYPE,
8 p_Period_Name IN GL_PERIODS.period_name%TYPE,
9 p_Start_Date IN DATE,
10 p_End_Date IN DATE,
11 P_CCID IN gl_Code_Combinations.code_Combination_Id%TYPE,
12 P_Budget_Version_ID IN pa_Budget_versions.budget_version_Id%TYPE,
13 p_Prev_Budget_Version_ID IN pa_Budget_versions.budget_version_Id%TYPE,
14 p_Amount IN NUMBER,
15 X_Return_Status OUT NOCOPY VARCHAR2,
16 X_Msg_Count OUT NOCOPY NUMBER,
17 X_Msg_Data OUT NOCOPY VARCHAR2
18 );
19
20 ----------------------------------------------------------------------------------------+
21 -- Package : PA_BUDGET_ACCOUNT_PKG
22 --
23 -- Purpose : 1) Generate the Account Code CCID for every Budget Line
24 -- depending upon the calling mode parameter.
25 -- 2) Update the Budget Line Data with generated CCID
26 -- 3) Update the Budget Account Summary Details
27 -- 4) Insert new Budget Lines which are having missed GL Periods
28 -- 5) Derive the Resource and Task related Parameters
29 -- Parameters :
30 -- P_Calling_Mode--> SUBMIT/ BASELINE / GENERATE_ACCOUNT
31 ----------------------------------------------------------------------------------------+
32
33 PROCEDURE Gen_Account (
34 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
35 P_Calling_Mode IN VARCHAR2,
36 X_Return_Status OUT NOCOPY VARCHAR2,
37 X_Msg_Count OUT NOCOPY NUMBER,
38 X_Msg_Data OUT NOCOPY VARCHAR2
39 )
40 AS
41
42 -- Local Parameters:
43 l_Budget_Type_Code PA_Budget_Types.Budget_Type_Code%TYPE;
44 l_Project_ID PA_Projects_All.Project_ID%TYPE;
45 l_Budget_Entry_Level_Code PA_Budget_Entry_Methods.Entry_Level_Code%TYPE;
46
47 l_msg_Index_out NUMBER;
48 l_Return_Status VARCHAR2(50);
49 l_Msg_Count NUMBER;
50 l_Msg_Data VARCHAR2(500);
51
52 PRAGMA AUTONOMOUS_TRANSACTION;
53
54 BEGIN
55
56 PA_FCK_UTIL.debug_msg('Entering Gen_Account ..........');
57 PA_FCK_UTIL.debug_msg('p_calling_mode ..........'||p_calling_mode);
58 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
59
60 -----------------------------------------------------------------+
61 -- Fetch the values of Budget_type_Code and Project_ID and
62 -- Also check If Budgetry Control is implemented
63 -----------------------------------------------------------------+
64 PA_FCK_UTIL.debug_msg('Before executing bc controls select statement ..........');
65 BEGIN
66 SELECT
67 BVER.Budget_Type_Code BUDGET_TYPE_CODE,
68 BVER.Project_ID PROJECT_ID,
69 BEM.Entry_Level_Code BUDGET_ENTRY_LEVEL_CODE
70 INTO
71 l_Budget_Type_Code,
72 l_Project_ID,
73 l_Budget_Entry_Level_Code
74 FROM
75 PA_Budget_versions BVER,
76 PA_Budget_Entry_Methods BEM,
77 PA_Budgetary_Control_Options BCO
78 WHERE
79 BVER.Budget_Version_ID = p_Budget_version_Id
80 AND BEM.Budget_Entry_Method_Code = BVER.Budget_Entry_Method_Code
81 AND BCO.Budget_Type_Code = BVER.Budget_Type_Code
82 AND BCO.Project_ID = BVER.Project_ID
83 AND BCO.External_Budget_Code IS NOT NULL ;
84 END;
85 PA_FCK_UTIL.debug_msg(' '||to_char(SQL%ROWCOUNT)||' Rows selected/update/deleted/inserted ..........');
86
87 -----------------------------------------------------------------+
88 -- If found then call API Gen_Acct_All_Lines() to generate CCID
89 -- for all its Budget Lines
90 -----------------------------------------------------------------+
91 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
92 P_Budget_Version_ID, -- Current version of the Budget
93 P_Calling_Mode, -- Input from SQL*Form
94 l_Budget_Type_Code,
95 l_Budget_Entry_Level_Code,
96 l_Project_ID,
97 l_return_status,
98 l_Msg_Count,
99 l_Msg_Data
100 );
101
102 X_Return_Status := l_Return_Status;
103 X_Msg_Count := l_Msg_Count ;
104 X_Msg_Data := l_Msg_Data ;
105
106 COMMIT;
107
108 PA_FCK_UTIL.debug_msg('Exiting Gen_Account ..........');
109 RETURN;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 X_Msg_Count := 1;
114 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
115 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
116 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_BUDGET_ACCOUNT_PKG',
117 P_Procedure_Name => 'Gen_Account');
118 END Gen_Account; /* End of Gen_Account API */
119
120 PROCEDURE Gen_Acct_All_Lines (
121 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
122 P_Calling_Mode IN VARCHAR2,
123 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
124 P_Budget_Entry_Level_Code IN PA_Budget_Entry_Methods.Entry_Level_Code%TYPE,
125 P_Project_ID IN PA_projects_All.project_Id%TYPE,
126 X_Return_Status OUT NOCOPY VARCHAR2,
127 X_Msg_Count OUT NOCOPY NUMBER,
128 X_Msg_Data OUT NOCOPY VARCHAR2
129 )
130 IS
131
132 -- Local Parameters
133 l_acc_gen_error EXCEPTION;
134 l_no_budgetary_control EXCEPTION;
135
136 l_Prev_Budget_Version_ID PA_Budget_Versions.Budget_Version_ID%TYPE;
137 l_Concat_Segs VARCHAR2(500);
138 l_Concat_IDs VARCHAR2(500);
139 l_Concat_Descrs VARCHAR2(500);
140 l_Error_Message VARCHAR2(2000);
141 l_CCID NUMBER;
142 l_Error_Flag VARCHAR2(1);
143
144 l_ccid_gen_result_flag VARCHAR2(1); /* values s--> sucess, f --> failure */
145 l_Return_Status VARCHAR2(50);
146 l_Msg_Count NUMBER;
147 l_Msg_Data VARCHAR2(500);
148
149 l_Max_Version_Number NUMBER;
150 l_Project_Number PA_Projects_All.Segment1%TYPE;
151 l_Project_Org_Name HR_Organization_Units.Name%TYPE;
152 l_Project_Org_ID HR_Organization_Units.Organization_ID %TYPE;
153 l_Project_Type PA_Project_Types_All.Project_Type%TYPE;
154 l_Project_Class_Code PA_Project_Classes.Class_Code%TYPE; /* Added for bug 2914197 */
155 l_Project_Start_Date DATE;
156 l_Project_End_Date DATE;
157
158 l_Msg_Index_Out NUMBER;
159
160 l_balance_type PA_BUDGETARY_CONTROL_OPTIONS.Balance_type%TYPE ;
161 l_budget_amount_code Pa_Budget_Types.Budget_Amount_Code%TYPE ;
162
163 l_is_cc_budget varchar2(1); --Bug 6524116
164 -----------------------------------------------------------------------+
165 -- Need cursor to fetch all the budget line details from Budget Lines
166 -- for a given budget version ID
167 -----------------------------------------------------------------------+
168 CURSOR cur_All_Budget_Lines IS
169 SELECT
170 BL.Code_Combination_ID CCID_old,
171 BL.Resource_Assignment_ID Resource_Assignment_ID,
172 BL.Start_Date Start_Date,
173 BL.End_Date End_Date,
174 NVL(RM.Parent_Member_ID, RM.Resource_List_Member_ID) Resource_Group_ID,
175 RTYPE.Resource_Type_Code Resource_Type,
176 DECODE(RTYPE.Resource_Type_Code, NULL, 'N', 'Y') Resource_List_Flag,
177 RA.resource_list_member_id resource_list_member_id,
178 RM.Parent_Member_ID parent_resource_id,
179 RM.alias resource_name,
180 RA.Task_ID Task_ID,
181 nvl(PT.Top_task_id,-99) Top_task_id,
182 PT.task_number task_number,
183 RT.Person_ID Person_ID,
184 RT.Expenditure_Category Expenditure_Category,
185 RT.Expenditure_Type Expenditure_Type,
186 RT.Job_ID Job_ID,
187 RT.Organization_ID Organization_ID,
188 RT.Vendor_ID Supplier_ID,
189 BL.Period_Name Period_Name,
190 decode(nvl(l_balance_type,'X'),
191 'E', decode(NVL(BL.Burdened_Cost,0),
192 0,nvl(bl.raw_cost,0),
193 bl.burdened_cost ) ,
194 'B',decode(l_Budget_Amount_Code,
195 'R',nvl(bl.revenue,0) ,
196 'C', decode(NVL(BL.Burdened_Cost,0),
197 0,nvl(bl.raw_cost,0),
198 bl.burdened_cost ),
199 0 ),
200 0 ) Total_Amount,
201 BL.txn_currency_code
202 FROM
203 PA_Resource_Types RTYPE,
204 PA_Budget_Lines BL,
205 PA_Resources RS,
206 PA_Resource_List_Members RM,
207 PA_Resource_Assignments RA,
208 PA_Resource_Txn_Attributes RT,
209 PA_TASKS PT
210 WHERE
211 -- ra.Budget_Version_ID = P_Budget_Version_ID AND
212 ra.Resource_Assignment_ID = BL.Resource_Assignment_ID
213 AND RA.Resource_List_Member_ID = RM.Resource_List_Member_ID
214 AND RM.Resource_ID = RS.Resource_ID
215 AND RM.Resource_ID = RT.Resource_ID (+)
216 AND RS.Resource_Type_ID = RTYPE.Resource_Type_ID
217 AND ra.budget_version_id = BL.budget_version_id
218 AND BL.Budget_Version_ID = P_Budget_Version_ID
219 AND PT.task_id(+) = RA.Task_ID ;
220
221
222 -- Last 2 criteria added as part of bug 4009377: performance improvement
223
224 Cursor c_Budget_funds is
225 Select PBCO.Balance_type,
226 PBT.Budget_Amount_Code
227 From PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
228 PA_BUDGET_VERSIONS PBV ,
229 PA_BUDGET_TYPES PBT
230 WHERE PBCO.Project_Id = p_project_id
231 AND PBV.Budget_version_id = p_Budget_version_id
232 AND PBV.Budget_Type_Code = PBCO.Budget_Type_Code
233 AND PBV.Budget_Type_Code = PBT.Budget_Type_Code;
234
235 l_txn_exists_against_project varchar2(1);
236 l_prev_ccid pa_budget_lines.code_combination_id%type;
237 BEGIN
238
239 PA_FCK_UTIL.debug_msg('Entering Gen_Acct_All_Lines ..........');
240 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
241
242 PA_FCK_UTIL.debug_msg('p_calling_mode ..........'||p_calling_mode);
243 -----------------------------------------------+
244 -- Get the budgetary control options
245 -----------------------------------------------+
246 pa_fck_util.debug_msg('Opening budget_funds cursor') ;
247 pa_fck_util.debug_msg('proj_id '||to_char(p_project_id)||'bver_id '
248 ||to_char(p_budget_version_id ));
249
250 OPEN c_Budget_funds ;
251 FETCH c_Budget_funds
252 INTO l_balance_type,l_Budget_Amount_Code;
253
254 IF c_Budget_funds%NOTFOUND THEN
255 RAISE l_no_budgetary_control;
256 END IF;
257
258 CLOSE c_Budget_funds ;
259
260 ---------------------------------------------------------+
261 -- Get/Derive project details from Project ID parameter
262 ---------------------------------------------------------+
263 BEGIN
264 SELECT
265 PROJ.Segment1 PROJECT_NUMBER,
266 ORG.Name PROJECT_ORGANIZATION_NAME,
267 ORG.Organization_ID PROJECT_ORGANIZATION_ID,
268 PROJ.Project_Type PROJECT_TYPE,
269 PROJ.Start_Date PROJ_START_DATE,
270 PROJ.Completion_Date PROJ_END_DATE
271 INTO
272 l_Project_Number,
273 l_Project_Org_Name,
274 l_Project_Org_Id,
275 l_Project_Type,
276 l_Project_Start_Date,
277 l_Project_End_Date
278 FROM
279 HR_All_Organization_Units ORG,
280 PA_Projects PROJ
281 WHERE
282 PROJ.Project_ID = P_Project_ID
283 AND ORG.Organization_ID = PROJ.Carrying_Out_Organization_ID ;
284 END;
285
286 PA_FCK_UTIL.debug_msg('after selecting proj org details ..........');
287
288 /* Code addition for bug 2914197 starts */
289 ---------------------------------------------------------+
290 -- Get class code from Project ID parameter
291 ---------------------------------------------------------+
292 BEGIN
293 SELECT
294 CLASS.Class_Code PROJECT_CLASS_CODE
295 INTO l_Project_Class_Code
296 FROM
297 PA_Project_Classes CLASS,
298 Pa_Class_Categories CLCAT
299 WHERE
300 CLCAT.Autoaccounting_Flag = 'Y' AND
301 CLCAT.Class_Category = CLASS.Class_Category AND
302 CLASS.Project_ID = P_Project_ID;
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 l_Project_Class_Code := null;
306 END;
307 PA_FCK_UTIL.debug_msg('after selecting class code ..........');
308
309 /* Code addition for bug 2914197 ends */
310
311 ----------------------------------------------------+
312 -- Fetch the Latest Previous Budget Version Number
313 ----------------------------------------------------+
314 BEGIN
315 SELECT
316 MAX(Version_Number)
317 INTO
318 l_Max_Version_Number
319 FROM
320 PA_Budget_Versions
321 WHERE
322 Project_ID = p_Project_ID
323 AND Budget_Status_Code = 'B'
324 AND Budget_Type_Code = p_Budget_Type_Code ;
325 END;
326
327 IF l_Max_Version_Number <> 0
328 THEN
329 BEGIN
330 SELECT
331 Budget_Version_ID
332 INTO
333 l_Prev_Budget_Version_ID
334 FROM
335 PA_Budget_Versions
336 WHERE
337 Project_ID = p_Project_ID
338 AND Budget_Status_Code = 'B'
339 AND Budget_Type_Code = p_Budget_Type_Code
340 AND Version_Number = l_Max_Version_Number - 1; --Bug 6524116
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN NULL;
343 END;
344 ELSE
345 l_Prev_Budget_Version_ID := NULL;
346 END IF;
347
348 PA_FCK_UTIL.debug_msg('after selecting max version number ..........'||to_char(l_max_version_number));
349 PA_FCK_UTIL.debug_msg('after selecting prev_budget_version_id ..........'||to_char(l_prev_budget_version_id));
350 PA_FCK_UTIL.debug_msg('p_calling_mode ......'||p_calling_mode);
351 PA_FCK_UTIL.debug_msg('P_Budget_Version_ID ......'||P_Budget_Version_ID);
352 PA_FCK_UTIL.debug_msg('l_Project_Start_Date ......'||l_Project_Start_Date);
353 PA_FCK_UTIL.debug_msg('l_Project_End_Date ......'||l_Project_End_Date);
354
355 IF P_Calling_Mode = 'BASELINE'
356 THEN
357 --------------------------------------------------+
358 -- Create Budget Line records in PA_BUDGET_LINES
359 -- whose GL Periods are not existing ...
360 -- NOT REQUIRED TO GENERATE : 04/23/01
361 --------------------------------------------------+
362 PA_FCK_UTIL.debug_msg(' before calling .... Insert_Into_Budget_Lines ');
363 PA_BUDGET_ACCOUNT_PKG.Insert_Into_Budget_Lines (
364 P_Budget_Version_ID,
365 P_Project_ID,
366 l_Project_Start_Date,
367 l_Project_End_Date,
368 l_Return_Status,
369 l_Msg_Count,
370 l_Msg_Data );
371 END IF;
372
373 PA_FCK_UTIL.debug_msg('after calling insert into budget lines ..........');
374 ------------------------------------------------------------+
375 -- Delete all account lines from PA_BUDGET_ACCT_LINES
376 -- pertaining to a given Current Budget Version ID
377 ------------------------------------------------------------+
378 IF P_Calling_Mode in ('SUBMIT','GENERATE_ACCOUNT') then
379 DELETE FROM PA_BUDGET_ACCT_LINES
380 WHERE Budget_Version_ID = p_Budget_Version_ID;
381 END IF;
382
383 PA_FCK_UTIL.debug_msg('after deleting from budget acct lines ..........');
384 l_Error_Flag := 'N' ;
385 PA_FCK_UTIL.debug_msg('budget version id '||to_char(p_budget_version_id) );
386
387
388 -- Following code has been added to take care of the scenario where
389 -- 'Account generator' is called to change the accounts
390 -- At this point of time, we should check whether the account on the
391 -- budget line can be changed ..
392
393 l_txn_exists_against_project := 'N';
394
395 If (P_Calling_Mode in ('SUBMIT','GENERATE_ACCOUNT') and
396 nvl(l_balance_type,'B') = 'E' and
397 nvl(l_Budget_Amount_Code,'R') = 'C' and
398 l_Prev_Budget_Version_ID is not null
399 ) then -- I
400
401 PA_FCK_UTIL.debug_msg('Check if txns. exists ..');
402 -- Check if txn. exists against the project
403 -- This needs to be done only if its a re-baseline
404
405 Begin -- II
406
407 Select 'Y'
408 into l_txn_exists_against_project
409 from dual where exists (select 1
410 from pa_bc_balances
411 where budget_version_id = l_Prev_Budget_Version_ID
412 and balance_type <> 'BGT');
413 Exception
414 When no_data_found then
415 Begin -- III
416 Select 'Y'
417 into l_txn_exists_against_project
418 from dual where exists (select 1
419 from pa_bc_packets
420 where budget_version_id = l_Prev_Budget_Version_ID
421 and status_code in ('P','Z','A','I'));
422 Exception
423 When no_data_found then
424 null;
425 End; -- III
426 End; -- II
427 End If; -- I
428
429 PA_FCK_UTIL.debug_msg('l_txn_exists_against_project:['||l_txn_exists_against_project||']');
430 PA_FCK_UTIL.debug_msg('P_Budget_Entry_Level_Code:'||P_Budget_Entry_Level_Code);
431
432
433 FOR bl IN cur_All_Budget_Lines -- Loop thru every Budget Line
434 LOOP
435
436 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
437
438 ----------------------------------------------------------------------------+
439 -- Call API Gen_Acct_Line to generate CCID for each Budget Line
440 ----------------------------------------------------------------------------+
441 IF (p_Calling_Mode IN ('BASELINE','SUBMIT') AND bl.CCID_old IS NULL ) OR
442 (P_Calling_Mode = 'GENERATE_ACCOUNT')
443 THEN
444
445 PA_FCK_UTIL.debug_msg('before calling gen_acct_line ..........');
446 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_Line (
447 P_Budget_Entry_Level_Code,
448 P_Budget_Type_Code,
449 P_Budget_Version_ID,
450
451 P_Project_ID,
452 l_Project_Number,
453 l_Project_Org_Name,
454 l_Project_Org_ID,
455 l_Project_Type,
456 l_Project_Class_Code, /* Added for bug 2914197 */
457 bl.Task_ID,
458
459 bl.Resource_List_Flag,
460 bl.Resource_Type,
461 bl.Resource_Group_ID,
462 bl.Resource_Assignment_ID,
463 bl.Start_Date,
464
465 bl.Person_ID,
466 bl.Expenditure_Category,
467 bl.Expenditure_Type,
468 bl.Job_ID,
469 bl.Organization_ID,
470 bl.Supplier_ID,
471
472 l_CCID,
473 l_Return_status,
474 l_Msg_Count,
475 l_Msg_Data,
476
477 l_Concat_Segs,
478 l_Concat_IDs,
479 l_Concat_Descrs,
480 l_Error_Message
481 );
482
483 PA_FCK_UTIL.debug_msg(to_char(l_ccid)||' l_ccid ..........(after calling gen_acct_line )');
484 PA_FCK_UTIL.debug_msg((l_error_message)||' l_error_message ..........(after calling gen_acct_line )');
485 PA_FCK_UTIL.debug_msg((l_return_status)||' l_return_status ..........(after calling gen_acct_line )');
486
487
488 -- Following code added to validate the accounts that are being generated ..
489 -- If txn. exists against the budget line then account change is not allowed ..
490 IF (l_Return_Status = FND_API.G_RET_STS_SUCCESS and
491 l_txn_exists_against_project = 'Y' and
492 P_Calling_Mode in ('SUBMIT','GENERATE_ACCOUNT')
493 ) then -- I
494
495
496 --IF P_Calling_Mode = 'GENERATE_ACCOUNT' then ----------------------------------------------------+
497 Begin
498
499 PA_FCK_UTIL.debug_msg('Retrieve ccid from latest budget version');
500
501 select code_combination_id
502 into l_prev_ccid
503 from pa_budget_lines pbl,
504 pa_resource_assignments pra
505 where pra.budget_version_id = l_Prev_Budget_Version_ID
506 and pra.resource_list_member_id = bl.resource_list_member_id
507 and pra.project_id = p_project_id
508 and pra.task_id = bl.task_id
509 and pbl.budget_version_id = pra.budget_version_id
510 and pbl.resource_assignment_id = pra.resource_assignment_id
511 and pbl.start_date = bl.start_date
512 and pbl.txn_currency_code = bl.txn_currency_code;
513
514 PA_FCK_UTIL.debug_msg('Retrieved ccid from latest budget version:'||l_prev_ccid);
515
516 Exception
517 when no_data_found then
518 PA_FCK_UTIL.debug_msg('Retrieve ccid from latest budget version .. failed');
519
520 -- this can happen if the old budget version did not have data
521 l_prev_ccid := -99;
522
523 End;
524 --END IF; ---------------------------------------------------------------------------+
525
526 If nvl(l_prev_ccid,-99) <> l_ccid then -- II
527
528 PA_FCK_UTIL.debug_msg('Calling pa_funds_control_utils.is_Account_change_allowed2 ..');
529 PA_FCK_UTIL.debug_msg('Period_name ['||bl.period_name||'] Task_id ['||bl.task_id||'] RLMI ['
530 ||bl.resource_list_member_id||']');
531
532 IF pa_funds_control_utils.is_Account_change_allowed2
533 (p_budget_version_id => l_Prev_Budget_Version_ID,
534 p_project_id => p_project_id,
535 p_top_task_id => bl.top_task_id,
536 p_task_id => bl.task_id,
537 p_parent_resource_id => bl.parent_resource_id,
538 p_resource_list_member_id => bl.resource_list_member_id,
539 p_start_date => bl.start_date,
540 p_period_name => bl.period_name,
541 p_entry_level_code => P_Budget_Entry_Level_Code,
542 p_mode => 'FORM') = 'N'
543 THEN -- III
544
545 PA_FCK_UTIL.debug_msg('pa_funds_control_utils.is_Account_change_allowed2 failed');
546 If nvl(l_prev_ccid,-99) <> -99 then -- IV
547
548 -- Basically assigning the old value back ....
549 PA_FCK_UTIL.debug_msg('Assigning old value back');
550 l_ccid := l_prev_ccid;
551
552 Else
553 l_Return_Status := FND_API.G_RET_STS_ERROR;
554 l_error_flag := 'Y';
555
556 select description
557 into l_Error_message
558 from pa_lookups
559 where lookup_type = 'FC_RESULT_CODE'
560 and lookup_code = 'F169';
561
562 l_Error_message := l_Error_message;
563
564 PA_FCK_UTIL.debug_msg('F169: ccid['||l_ccid||'] for task['||bl.task_number||'] resource ['||bl.resource_name||'] period ['||bl.period_name||']');
565 PA_FCK_UTIL.debug_msg('Failed validation: ccid['||l_ccid||'] for task_id['||bl.task_id||'] top task ['||bl.top_task_id||
566 '] resource ['||bl.resource_list_member_id||'] parent rlmi ['|| bl.parent_resource_id||']');
567
568 End If; -- IV
569
570 End If; -- III
571
572 End If; -- II
573
574 End If; -- I
575
576 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS
577 THEN
578 UPDATE PA_BUDGET_LINES
579 SET Code_Combination_ID = l_CCID,
580 CCID_Gen_Status_Code = 'Y'
581 WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
582 Start_Date = bl.Start_Date ;
583 ELSE
584 l_Error_Flag := 'Y' ;
585
586 if ( l_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) then
587
588 UPDATE PA_BUDGET_LINES
589 SET CCID_Gen_Rej_Message = l_Msg_Data,
590 CCID_Gen_Status_Code = 'N'
591 WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
592 Start_Date = bl.Start_Date ;
593
594 else
595
596 UPDATE PA_BUDGET_LINES
597 SET CCID_Gen_Rej_Message = l_Error_message,
598 CCID_Gen_Status_Code = 'N'
599 WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
600 Start_Date = bl.Start_Date ;
601
602 end if;
603 END IF;
604 ELSE
605 -- Otherwise use the existing old CCID value available in the Bdgt Line
606 l_CCID := bl.CCID_Old ;
607 END IF;
608
609 begin
610 SELECT 'Y'
611 INTO l_is_cc_budget
612 FROM pa_budgetary_control_options
613 WHERE project_id = p_Project_ID
614 AND Budget_Type_Code = p_Budget_Type_Code
615 AND EXTERNAL_BUDGET_CODE = 'CC'
616 AND BDGT_CNTRL_FLAG = 'Y';
617 exception
618 WHEN NO_DATA_FOUND then
619 l_is_cc_budget := 'N';
620 end;
621
622 IF (l_Return_Status = FND_API.G_RET_STS_SUCCESS and
623 (P_Calling_Mode in ('SUBMIT','GENERATE_ACCOUNT')
624 or (P_Calling_Mode in ('BASELINE') and l_is_cc_budget = 'Y'))) then
625
626 -- Update the Budget and Available balance amounts
627 PA_BUDGET_ACCOUNT_PKG.Upd_Budget_Acct_Line (
628 p_Budget_Type_Code,
629 p_Project_ID,
630 bl.Period_Name,
631 bl.Start_Date,
632 bl.End_Date,
633 l_CCID,
634 p_Budget_Version_ID,
635 l_Prev_Budget_Version_ID,
636 bl.Total_Amount,
637 l_Return_Status,
638 l_Msg_Count,
639 l_Msg_Data
640 );
641 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
642 THEN
643 l_Error_Flag := 'Y' ;
644 END IF;
645 END IF;
646
647 END LOOP ;
648
649 X_Msg_Count := l_Msg_Count ;
650 X_Msg_Data := l_Msg_Data ;
651 -----------------------------------------------------------+
652 -- if atleast one row exists in pa_budget_lines with
653 -- CCID_Gen_Status_Code as 'N' then return error message
654 -----------------------------------------------------------+
655 IF l_Error_Flag = 'Y'
656 THEN
657 raise l_acc_gen_error;
658 END IF;
659
660 BEGIN
661 X_Return_Status := FND_API.G_RET_STS_SUCCESS ;
662 IF (l_Prev_Budget_Version_ID IS NOT NULL and P_Calling_Mode in ('SUBMIT','GENERATE_ACCOUNT')) then
663 INSERT INTO PA_BUDGET_ACCT_LINES (
664 Budget_Acct_Line_ID,
665 Budget_Version_ID,
666 GL_Period_Name,
667 Start_Date,
668 End_Date,
669 Code_Combination_ID,
670 Prev_Ver_Budget_Amount,
671 Prev_Ver_Available_Amount,
672 Curr_Ver_Budget_Amount,
673 Curr_Ver_Available_Amount,
674 accounted_amount,
675 LAST_UPDATE_DATE,
676 LAST_UPDATED_BY,
677 LAST_UPDATE_LOGIN,
678 CREATION_DATE,
679 CREATED_BY
680 )
681 SELECT
682 PA_BUDGET_ACCT_LINES_S.nextval,
683 P_Budget_Version_ID,
684 BL1.GL_Period_Name,
685 BL1.Start_Date,
686 BL1.End_Date,
687 BL1.Code_Combination_ID,
688 BL1.Curr_Ver_Budget_Amount,
689 BL1.Curr_Ver_Available_Amount,
690 0,
691 -- Prev_Ver_Budget_Amount - Prev_Ver_Available_Amount, -- Bug # 2008368
692 0, -- Curr_Ver_Available_Amount
693 /* -- Commented for bug 30399850 - BL1.Curr_Ver_Available_Amount, -- Accounted Amount */
694 0 - BL1.Curr_Ver_Budget_Amount, /* Accounted Amount Added for bug3039985 */
695 sysdate,
696 -1,
697 -1,
698 sysdate,
699 -1
700 FROM
701 PA_BUDGET_ACCT_LINES BL1
702 WHERE
703 BL1.Budget_Version_ID = l_Prev_Budget_Version_ID
704 AND NOT EXISTS
705 ( SELECT 'x'
706 FROM PA_BUDGET_ACCT_LINES BL2
707 WHERE BL2.Code_Combination_ID = BL1.Code_Combination_ID
708 AND BL2.Budget_Version_ID = P_Budget_Version_ID
709 AND BL2.Start_Date = BL1.Start_Date ) ;
710 END IF;
711 END;
712
713 PA_FCK_UTIL.debug_msg('Exiting Gen_Acct_All_Lines ..........');
714 RETURN;
715
716 EXCEPTION
717
718 WHEN l_no_budgetary_control THEN
719 PA_UTILS.Add_Message('PA', 'PA_BC_NO_BGT_CNTL');
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 x_msg_data := 'PA_BC_NO_BGT_CNTL';
722 x_msg_count := 1;
723
724 WHEN l_acc_gen_error THEN
725 PA_UTILS.Add_Message('PA', 'PA_BC_ACC_GEN_ERROR');
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 x_msg_data := 'PA_BC_ACC_GEN_ERROR';
728 x_msg_count := 1;
729
730 WHEN OTHERS THEN
731 X_Msg_Count := 1;
732 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
733 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
734 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_BUDGET_ACCOUNT_PKG',
735 P_Procedure_Name => 'Gen_Acct_All_Lines');
736
737 END Gen_Acct_All_Lines ; /* End of Gen_Acct_All_Lines API */
738
739 PROCEDURE Gen_Acct_Line (
740
741 P_Budget_Entry_Level_Code IN PA_Budget_Entry_Methods.Entry_Level_Code%TYPE,
742 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
743 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
744
745 P_Project_ID IN PA_Projects_All.Project_ID%TYPE,
746 P_Project_Number IN PA_Projects_All.Segment1%TYPE,
747 P_Project_Org_Name IN HR_Organization_Units.Name%TYPE,
748 P_project_Org_ID IN HR_Organization_Units.Organization_ID %TYPE,
749 P_Project_Type IN PA_Project_Types_All.Project_Type%TYPE,
750 P_Project_Class_Code IN PA_Project_Classes.Class_Code%TYPE, /* added for bug 2914197 */
751 P_Task_ID IN PA_Tasks.Task_ID%TYPE,
752
753 P_Resource_List_Flag IN VARCHAR2,
754 P_Resource_Type_ID IN PA_Resource_Types.Resource_Type_Code%TYPE,
755 P_Resource_Group_ID IN PA_Resource_Types.Resource_Type_ID%TYPE,
756 P_Resource_Assign_ID IN PA_Budget_Lines.Resource_Assignment_Id%TYPE,
757 P_Start_Date IN PA_Budget_Lines.Start_Date%TYPE,
758
759 P_Person_ID IN PER_All_People_F.Person_ID%TYPE,
760 P_Expenditure_Category IN PA_Expenditure_Categories.Expenditure_Category%TYPE,
761 P_Expenditure_Type IN PA_Expenditure_Types.Expenditure_Type%TYPE,
762 P_Job_ID IN PER_Jobs.Job_ID%TYPE,
763 P_Organization_ID IN HR_All_Organization_Units.Organization_ID%TYPE,
764 P_Supplier_ID IN PO_Vendors.Vendor_ID%TYPE,
765
766 X_Return_CCID OUT NOCOPY GL_Code_Combinations.Code_Combination_ID%TYPE,
767 X_Return_Status OUT NOCOPY VARCHAR2,
768 X_Msg_Count OUT NOCOPY NUMBER,
769 X_Msg_Data OUT NOCOPY VARCHAR2,
770
771 X_Concat_Segs OUT NOCOPY VARCHAR2,
772 X_Concat_IDs OUT NOCOPY VARCHAR2,
773 X_Concat_Descrs OUT NOCOPY VARCHAR2,
774 X_Error_Message OUT NOCOPY VARCHAR2
775 )
776
777 IS
778
779 -- Local Parameters
780 l_Itemtype CONSTANT VARCHAR2(30) := 'PABDACWF' ;
781 l_Itemkey VARCHAR2(30);
782 l_result BOOLEAN;
783 l_Concat_segs VARCHAR2(200);
784 l_Concat_Ids VARCHAR2(200);
785 l_Concat_Descrs VARCHAR2(500);
786 l_Error_message VARCHAR2(100);
787 l_return_Ccid GL_Code_Combinations.code_Combination_Id%TYPE;
788
789 l_top_task_Id pa_tasks.task_Id%TYPE;
790 l_top_task_number pa_tasks.task_number%TYPE;
791 l_task_org_name hr_organization_units.name%TYPE;
792 l_task_org_Id hr_organization_units.organization_Id %TYPE;
793 l_task_service_type pa_tasks.service_type_Code%TYPE;
794
795 l_low_task_Id pa_tasks.task_Id%TYPE;
796 l_low_task_number pa_tasks.task_number%TYPE;
797
798 l_Employee_number per_All_people_F.employee_number%TYPE;
799
800 -- Added in FP_M for CWK changes
801 l_Person_Type PA_Employees.Person_Type%TYPE;
802
803 l_Job_name per_Jobs.name%TYPE;
804 l_Job_Group_Id per_Jobs.job_Group_Id%TYPE;
805 l_Job_Group_name per_Job_Groups.internal_name%TYPE;
806
807 l_organization_name hr_organization_units.name%TYPE;
808 l_organization_type hr_organization_units.type%TYPE;
809
810 l_supplier_name po_vendors.vendor_name%TYPE;
811
812 l_Chart_of_Accounts_Id NUMBER; -- Eg. 50234;
813
814 l_msg_Index_out NUMBER;
815
816 l_Return_Status VARCHAR2(50);
817 l_Msg_Count NUMBER;
818 l_Msg_Data VARCHAR2(500);
819 l_code_combination BOOLEAN;
820
821 BEGIN
822
823 PA_FCK_UTIL.debug_msg('Entering Gen_Acct_Lines ..........');
824 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
825
826 ------------------------------------------------------------------+
827 -- Derive Task Parameters only when Top Task Details are present.
828 ------------------------------------------------------------------+
829 IF NVL(p_Task_ID, 0) <> 0
830 THEN
831 ----------------------------------------------------------------------------------+
832 -- Check the given task ID is whether Top task or Low Task ?
833 -- If given Task_Id = Top Task id, then it is a top task budgeting, so
834 -- don't need Low Task Details. In this case, Low Task ID should be NULL.
835 -- OTHERWIESE if it is a low task budgeting, then derive the Low Task Details also
836 ----------------------------------------------------------------------------------+
837 BEGIN
838 SELECT
839 top_task_Id,
840 DECODE(task_Id, top_task_Id, NULL, task_Id)
841 INTO
842 l_top_task_Id,
843 l_low_task_Id
844 FROM
845 PA_Tasks
846 WHERE
847 task_Id = p_task_Id
848 AND project_Id = p_project_Id ;
849 END;
850
851 PA_FCK_UTIL.debug_msg(l_return_status||' gen before calling Derive_Task_Params ..........');
852 ------------------------------------------------+
853 -- Now Derive Task Parameters and their Details
854 ------------------------------------------------+
855 PA_BUDGET_ACCOUNT_PKG.Derive_Task_Params (
856 p_project_Id => p_project_Id,
857 p_top_task_Id => l_top_task_Id,
858 p_low_task_Id => l_low_task_Id,
859 x_top_task_number => l_top_task_number,
860 x_task_organization_Id => l_task_org_Id,
861 x_task_organization_name => l_task_org_name,
862 x_task_service_type => l_task_service_type,
863 x_task_number => l_low_task_number,
864 X_return_status => l_Return_Status,
865 X_Msg_Count => l_Msg_Count,
866 X_Msg_Data => l_Msg_Data
867 );
868 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling Derive_Task_Params ..........');
869 END IF;
870
871 PA_FCK_UTIL.debug_msg(l_return_status||' gen before calling derive_res_para ..........');
872 ------------------------------------------------+
873 -- Derive Resource Parameters and their Details
874 ------------------------------------------------+
875 PA_BUDGET_ACCOUNT_PKG.Derive_Resource_Params (
876 p_person_Id => p_person_Id,
877 p_Job_Id => p_Job_Id,
878 p_organization_Id => p_organization_Id,
879 p_supplier_Id => p_supplier_Id,
880 x_Employee_number => l_Employee_number,
881 x_Person_Type => l_Person_Type, -- Added in FP_M for CWK changes
882 x_Job_name => l_Job_name,
883 x_Job_Group_Id => l_Job_Group_Id,
884 x_Job_Group_name => l_Job_Group_name,
885 x_organization_type => l_organization_type,
886 x_organization_name => l_organization_name,
887 x_supplier_name => l_supplier_name,
888 X_return_status => l_Return_Status,
889 X_Msg_Count => l_Msg_Count,
890 X_Msg_Data => l_Msg_Data
891 );
892
893 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling derive_res_para ..........');
894 select sob.chart_of_accounts_id
895 into l_chart_of_accounts_id
896 from pa_implementations imp, gl_sets_of_books sob
897 where imp.set_of_books_id = sob.set_of_books_id;
898
899 PA_FCK_UTIL.debug_msg(l_return_status||' gen after getting chart of account id ..........');
900 -------------------------------------+
901 -- Call API FND initialize function
902 -------------------------------------+
903 l_Itemkey := fnd_Flex_workflow.initialize
904 (appl_short_name => 'SQLGL',
905 code => 'GL#',
906 num => l_Chart_of_Accounts_Id,
907 itemtype => l_Itemtype);
908
909
910 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling initialize ..........');
911 ------------------------------------------------------------+
912 -- BEGIN: Initialize / Set ALL the workflow item attributes
913 ------------------------------------------------------------+
914
915 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
916 itemkey => l_Itemkey,
917 aname => 'CHART_OF_ACCOUNTS_ID',
918 avalue => l_Chart_of_Accounts_Id);
919
920 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set CHART_OF_ACCOUNTS_ID ..........');
921 --------------------------------------------------------+
922 -- Set Item Attributes for workflow items BUDGET DETAILS
923 --------------------------------------------------------+
924 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
925 itemkey => l_Itemkey,
926 aname => 'BUDGET_TYPE',
927 avalue => p_Budget_type_Code);
928
929 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set BUDGET_TYPE ..........');
930 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
931 itemkey => l_Itemkey,
932 aname => 'BUDGET_ENTRY_LEVEL',
933 avalue => p_Budget_Entry_Level_Code);
934 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set BUDGET_ENTRY_LEVEL ..........');
935
936 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
937 itemkey => l_Itemkey,
938 aname => 'BUDGET_VERSION_ID',
939 avalue => p_Budget_version_Id);
940
941 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set BUDGET_VERSION_ID ..........');
942 ---------------------------------------------------------+
943 -- Set Item Attributes for workflow items PROJECT DETAILS
944 ---------------------------------------------------------+
945 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
946 itemkey => l_Itemkey,
947 aname => 'PROJECT_ID',
948 avalue => p_project_Id);
949
950 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set PROJECT_ID ..........');
951 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
952 itemkey => l_Itemkey,
953 aname => 'PROJECT_NUMBER',
954 avalue => p_project_number);
955
956 PA_FCK_UTIL.debug_msg(l_return_status||' gen after calling set PROJECT_NUMBER ..........');
957 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
958 itemkey => l_Itemkey,
959 aname => 'PROJECT_ORG_NAME',
960 avalue => p_project_org_name);
961
962 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
963 itemkey => l_Itemkey,
964 aname => 'PROJECT_ORG_ID',
965 avalue => p_project_org_Id);
966
967 /* Code Addition for bug 2914197 starts */
968 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
969 itemkey => l_Itemkey,
970 aname => 'CLASS_CODE',
971 avalue => p_project_class_code);
972 /* Code Addition for bug 2914197 starts */
973
974 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
975 itemkey => l_Itemkey,
976 aname => 'PROJECT_TYPE',
977 avalue => p_project_type);
978
979 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set PROJECT DETAILS ..........');
980 ----------------------------------------------------------+
981 -- Set Item Attributes for workflow items TOP TASK DETAILS
982 ----------------------------------------------------------+
983 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
984 itemkey => l_Itemkey,
985 aname => 'TOP_TASK_ID',
986 avalue => l_top_task_Id);
987
988 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
989 itemkey => l_Itemkey,
990 aname => 'TOP_TASK_NUMBER',
991 avalue => l_top_task_number);
992
993 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
994 itemkey => l_Itemkey,
995 aname => 'TASK_SERVICE_TYPE',
996 avalue => l_task_service_type);
997
998 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
999 itemkey => l_Itemkey,
1000 aname => 'TASK_ORG_ID',
1001 avalue => l_task_org_Id);
1002
1003 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1004 itemkey => l_Itemkey,
1005 aname => 'TASK_ORG_NAME',
1006 avalue => l_task_org_name);
1007
1008 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set TOP TASK DETAILS ..........');
1009 ----------------------------------------------------------+
1010 -- Set Item Attributes for workflow items LOW TASK DETAILS
1011 ----------------------------------------------------------+
1012 PA_FCK_UTIL.debug_msg('low_task_id '||to_char(l_low_task_Id));
1013 PA_FCK_UTIL.debug_msg('l_low_task_number *'||l_low_task_number||'*');
1014 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1015 itemkey => l_Itemkey,
1016 aname => 'LOW_TASK_ID',
1017 avalue => l_low_task_Id);
1018
1019 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set LOW TASK ID ..........');
1020 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1021 itemkey => l_Itemkey,
1022 aname => 'LOW_TASK_NUMBER',
1023 avalue => l_low_task_number);
1024
1025 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set LOW TASK NUMBER ..........');
1026 --------------------------------------------------------------------------+
1027 -- Set ALL resource related Item Attributes if Resource List Flag is TRUE
1028 --------------------------------------------------------------------------+
1029 IF p_resource_list_Flag = 'Y' THEN
1030 wf_Engine.SetItemAttrText ( itemtype => l_Itemtype,
1031 itemkey => l_Itemkey,
1032 aname => 'RESOURCE_LIST_FLAG',
1033 avalue => 'Y');
1034
1035 wf_Engine.SetItemAttrText ( itemtype => l_Itemtype,
1036 itemkey => l_Itemkey,
1037 aname => 'RESOURCE_TYPE',
1038 avalue => p_resource_type_Id);
1039
1040 wf_Engine.SetItemAttrText ( itemtype => l_Itemtype,
1041 itemkey => l_Itemkey,
1042 aname => 'RESOURCE_GROUP_ID',
1043 avalue => p_resource_Group_Id);
1044
1045 wf_Engine.SetItemAttrText ( itemtype => l_Itemtype,
1046 itemkey => l_Itemkey,
1047 aname => 'RESOURCE_ASSIGNMENT_ID',
1048 avalue => p_resource_Assign_Id);
1049
1050 wf_Engine.SetItemAttrText ( itemtype => l_Itemtype,
1051 itemkey => l_Itemkey,
1052 aname => 'BUDGET_LINE_START_DATE',
1053 avalue => p_start_Date);
1054
1055 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set RESOURCE LIST DETAILS ..........');
1056 --------------------------------------------------------------------------+
1057 -- Set Item Attributes for workflow items PERSON/EMPLOYEE DETAILS (Resource Type)
1058 --------------------------------------------------------------------------+
1059 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1060 itemkey => l_Itemkey,
1061 aname => 'PERSON_ID',
1062 avalue => p_person_Id);
1063
1064 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1065 itemkey => l_Itemkey,
1066 aname => 'EMPLOYEE_NUMBER',
1067 avalue => l_Employee_number);
1068
1069 -- This attribute is added for FP_M Build2 for Contingent Labor changes
1070 -- to get the Person Type value from WorkFlow
1071 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1072 itemkey => l_Itemkey,
1073 aname => 'PERSON_TYPE',
1074 avalue => l_Person_Type);
1075
1076 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set PERSON/EMPLOYEE LIST DETAILS ..........');
1077 PA_FCK_UTIL.debug_msg(l_return_status||' p_expenditure_type ... '||p_Expenditure_type);
1078 -----------------------------------------------------------------------+
1079 -- Set Item Attributes for workflow items EXPENDITURE CATEGORY DETAILS
1080 -----------------------------------------------------------------------+
1081 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1082 itemkey => l_Itemkey,
1083 aname => 'EXPENDITURE_CATEGORY',
1084 avalue => p_Expenditure_Category);
1085
1086 -------------------------------------------------------------------+
1087 -- Set Item Attributes for workflow items EXPENDITURE TYPE DETAILS
1088 -------------------------------------------------------------------+
1089 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1090 itemkey => l_Itemkey,
1091 aname => 'EXPENDITURE_TYPE',
1092 avalue => p_Expenditure_type);
1093
1094 ----------------------------------------------------------------------+
1095 -- Set Item Attributes for workflow items JOB DETAILS (Resource Type)
1096 ----------------------------------------------------------------------+
1097 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1098 itemkey => l_Itemkey,
1099 aname => 'JOB_ID',
1100 avalue => p_Job_Id);
1101
1102 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1103 itemkey => l_Itemkey,
1104 aname => 'JOB_NAME',
1105 avalue => l_Job_name);
1106
1107 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1108 itemkey => l_Itemkey,
1109 aname => 'JOB_GROUP_ID',
1110 avalue => l_Job_Group_Id);
1111
1112 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1113 itemkey => l_Itemkey,
1114 aname => 'JOB_GROUP_NAME',
1115 avalue => l_Job_Group_name);
1116
1117 PA_FCK_UTIL.debug_msg(l_return_status||' gen after set EXP CAT/DETAIL JOB DETAILS ..........');
1118 ------------------------------------------------------------------------------+
1119 -- Set Item Attributes for workflow items ORGANIZATION DETAILS (Resource Type)
1120 ------------------------------------------------------------------------------+
1121 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1122 itemkey => l_Itemkey,
1123 aname => 'ORGANIZATION_ID',
1124 avalue => p_organization_Id);
1125
1126 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1127 itemkey => l_Itemkey,
1128 aname => 'ORGANIZATION_NAME',
1129 avalue => l_organization_name);
1130
1131 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1132 itemkey => l_Itemkey,
1133 aname => 'ORGANIZATION_TYPE',
1134 avalue => l_organization_type);
1135
1136 --------------------------------------------------------------------------+
1137 -- Set Item Attributes for workflow items SUPPLIER DETAILS (Resource Type)
1138 --------------------------------------------------------------------------+
1139 wf_Engine.SetItemAttrNumber( itemtype => l_Itemtype,
1140 itemkey => l_Itemkey,
1141 aname => 'SUPPLIER_ID',
1142 avalue => p_supplier_Id);
1143
1144 wf_Engine.SetItemAttrText( itemtype => l_Itemtype,
1145 itemkey => l_Itemkey,
1146 aname => 'SUPPLIER_NAME',
1147 avalue => l_supplier_name);
1148
1149 END IF; -- Setting of all resource list item attributes is completed.
1150 ----------------------------------------------------------------------------+
1151 -- END: Initialize / Set ALL the workflow item attributes and Resource Types
1152 ----------------------------------------------------------------------------+
1153
1154 PA_FCK_UTIL.debug_msg(l_return_status||' gen before calling generate ..........');
1155 --------------------------------------------------------------------------------+
1156 -- Call the workflow Generate function to trigger off the w/f account generation
1157 --------------------------------------------------------------------------------+
1158 /*Modified the call to generate function to resolve bug 2266072.Added
1159 parameter TRUE to insert_if_new argument */
1160
1161 l_result := fnd_Flex_workflow.generate( l_Itemtype,
1162 l_Itemkey,
1163 TRUE,
1164 l_return_Ccid,
1165 l_Concat_segs,
1166 l_Concat_Ids,
1167 l_Concat_Descrs,
1168 l_Error_message,
1169 l_code_combination);
1170
1171 if l_result then
1172 PA_FCK_UTIL.debug_msg(' gen success ..........');
1173 else
1174 PA_FCK_UTIL.debug_msg(' gen failure ..........');
1175 end if;
1176 PA_FCK_UTIL.debug_msg(to_char(l_return_ccid)||' gen l_ccid ..........');
1177 PA_FCK_UTIL.debug_msg((l_error_message)||' gen l_error_message ..........');
1178 ------------------------------------------------------------------+
1179 -- Copy the return values to the corresponding output parameters
1180 ------------------------------------------------------------------+
1181
1182 x_Concat_segs := l_Concat_segs;
1183 x_Concat_Ids := l_Concat_Ids;
1184 x_Concat_Descrs := l_Concat_Descrs;
1185 x_Error_message := l_Error_message;
1186 x_return_Ccid := l_return_ccid;
1187
1188 X_Msg_Count := l_Msg_Count ;
1189 X_Msg_Data := l_Msg_Data ;
1190 X_Return_Status := l_Return_Status;
1191
1192 -------------------------------------------------+
1193 -- Set the value for x_return_status accordingly
1194 -------------------------------------------------+
1195
1196 IF l_result
1197 THEN
1198 x_return_status := 'S';
1199 ELSE
1200 x_return_status := 'E';
1201 END IF;
1202
1203 PA_FCK_UTIL.debug_msg('Exiting Gen_Acct_Lines ..........');
1204 RETURN;
1205
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 PA_FCK_UTIL.debug_msg(' Unexpected Error ..........');
1209 PA_FCK_UTIL.debug_msg(SUBSTR(SQLERRM, 1, 240));
1210 X_Msg_Count := 1;
1211 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1212 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1213
1214 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_BUDGET_ACCOUNT_PKG',
1215 P_Procedure_Name => 'Gen_Acct_Line');
1216
1217 ------------------------------------------------------------------------------+
1218 -- W/F related Error:
1219 -- Record error using generic error message routine for debugging and raise it
1220 ------------------------------------------------------------------------------+
1221 wf_Core.context( pkg_name => 'PA_BUDGET_ACCOUNT_PKG',
1222 proc_name => 'Gen_Acct_Line',
1223 arg1 => 'Budget Version ID : '|| p_Budget_version_Id,
1224 arg2 => 'Project ID : '|| p_project_Id,
1225 arg3 => null,
1226 arg4 => null,
1227 arg5 => null );
1228
1229 END Gen_Acct_Line ; /* End API Gen_Acct_Line */
1230
1231 PROCEDURE Upd_Budget_Acct_Line (
1232 p_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
1233 p_Project_ID IN PA_Projects_All.Project_ID%TYPE,
1234 p_Period_Name IN GL_PERIODS.period_name%TYPE,
1235 p_Start_Date IN DATE,
1236 p_End_Date IN DATE,
1237 P_CCID IN gl_Code_Combinations.code_Combination_Id%TYPE,
1238 P_Budget_Version_ID IN pa_Budget_versions.budget_version_Id%TYPE,
1239 p_Prev_Budget_Version_ID IN pa_Budget_versions.budget_version_Id%TYPE,
1240 p_Amount IN NUMBER,
1241 X_Return_Status OUT NOCOPY VARCHAR2,
1242 X_Msg_Count OUT NOCOPY NUMBER,
1243 X_Msg_Data OUT NOCOPY VARCHAR2
1244 )
1245 AS
1246
1247 -- Local Parameters
1248 l_Prev_Ver_Budget_Amount NUMBER := 0;
1249 l_Prev_Ver_Available_Amount NUMBER := 0;
1250
1251 l_Budget_Acct_Line_ID NUMBER ;
1252 l_update_count NUMBER ;
1253
1254 l_msg_Index_out NUMBER;
1255 l_Return_Status VARCHAR2(50);
1256 l_Msg_Count NUMBER;
1257 l_Msg_Data VARCHAR2(500);
1258
1259 BEGIN
1260
1261 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1262
1263 PA_FCK_UTIL.debug_msg('Entering Upd_Budget_Acct_Line ...... ');
1264 -------------------------------------------------------------------------------+
1265 -- Update a record for a combination of ( Budget_Verison, GL_Period and CCID )
1266 -- If No_Data_Found then create a new record
1267 -------------------------------------------------------------------------------+
1268
1269 PA_FCK_UTIL.debug_msg(' bdg_ver, p_start_date, p_ccid .. '||to_char(p_Budget_Version_ID)||' , '||to_char(p_Start_Date,'DD-MON-YYYY')||' , '||to_char(p_CCID));
1270 BEGIN
1271 UPDATE
1272 PA_BUDGET_ACCT_LINES
1273 SET
1274 Curr_Ver_Budget_Amount = Curr_Ver_Budget_Amount + p_Amount,
1275 Curr_Ver_Available_Amount = Curr_Ver_Available_Amount + p_Amount,
1276 accounted_amount = accounted_amount + p_Amount
1277 WHERE
1278 Budget_Version_ID = p_Budget_Version_ID
1279 AND Start_Date = p_Start_Date
1280 AND Code_Combination_ID = p_CCID ;
1281
1282 l_update_count := SQL%ROWCOUNT;
1283
1284 PA_FCK_UTIL.debug_msg('updated pa_budget_acct_line ......row '||to_char(l_update_count));
1285 PA_FCK_UTIL.debug_msg('previous budget version id ......row '||to_char(p_prev_budget_version_id));
1286
1287 IF l_update_count = 0 THEN
1288 PA_FCK_UTIL.debug_msg(' record not found ');
1289 -- Check the prev. Baselined budget record in PA_BUDGET_ACCT_LINES table
1290 IF p_Prev_Budget_Version_ID IS NOT NULL
1291 THEN
1292 BEGIN
1293 SELECT
1294 Curr_Ver_Budget_Amount,
1295 Curr_Ver_Available_Amount
1296 INTO
1297 l_Prev_Ver_Budget_Amount,
1298 l_Prev_Ver_Available_Amount
1299 FROM
1300 PA_BUDGET_ACCT_LINES
1301 WHERE
1302 Budget_Version_ID = p_Prev_Budget_Version_ID
1303 AND Start_Date = p_Start_Date
1304 AND Code_Combination_ID = p_CCID;
1305 EXCEPTION WHEN NO_DATA_FOUND THEN
1306 l_Prev_Ver_Budget_Amount := 0;
1307 l_Prev_Ver_Available_Amount := 0;
1308 END;
1309 ELSE
1310 l_Prev_Ver_Budget_Amount := 0;
1311 l_Prev_Ver_Available_Amount := 0;
1312 END IF;
1313
1314 PA_FCK_UTIL.debug_msg(' p_b_amt, p_av_amt '||to_char(l_Prev_Ver_Budget_Amount)||','||to_char(l_Prev_Ver_Budget_Amount) );
1315
1316 INSERT INTO PA_BUDGET_ACCT_LINES (
1317 Budget_Acct_Line_ID,
1318 Budget_Version_ID,
1319 GL_Period_Name,
1320 Start_Date,
1321 End_Date,
1322 Code_Combination_ID,
1323 Prev_Ver_Budget_Amount,
1324 Prev_ver_Available_Amount,
1325 Curr_Ver_Budget_Amount,
1326 Curr_ver_Available_Amount,
1327 Accounted_Amount,
1328 LAST_UPDATE_DATE,
1329 LAST_UPDATED_BY,
1330 LAST_UPDATE_LOGIN,
1331 CREATION_DATE,
1332 CREATED_BY
1333 )
1334 VALUES (
1335 PA_BUDGET_ACCT_LINES_S.nextval,
1336 p_Budget_Version_ID,
1337 p_Period_Name,
1338 p_Start_Date,
1339 p_End_Date,
1340 p_CCID,
1341 l_Prev_Ver_Budget_Amount,
1342 l_Prev_Ver_Available_Amount,
1343 p_Amount,
1344 -- p_Amount - (l_Prev_Ver_Budget_Amount-l_Prev_Ver_Available_Amount),
1345 p_Amount, -- Bug # 2008368 Curr_ver_Available_Amount
1346 p_Amount - l_Prev_Ver_Available_Amount, -- Accounted Amount
1347 sysdate,
1348 -1,
1349 -1,
1350 sysdate,
1351 -1
1352 );
1353 END IF;
1354 END;
1355
1356 X_Msg_Count := l_Msg_Count ;
1357 X_Msg_Data := l_Msg_Data ;
1358 X_Return_Status := l_Return_Status;
1359
1360 RETURN;
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 PA_FCK_UTIL.debug_msg(' failed with the error '||SUBSTR(SQLERRM, 1, 240));
1365 X_Msg_Count := 1;
1366 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1367 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1368
1369 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_BUDGET_ACCOUNT_PKG',
1370 P_Procedure_Name => 'Upd_Budget_Acct_Line');
1371
1372 END Upd_Budget_Acct_Line; /* End API UPD_Budget_Acct_Line */
1373
1374 PROCEDURE Insert_Into_Budget_Lines (
1375 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
1376 P_Project_ID IN PA_projects_All.project_Id%TYPE,
1377 P_Project_Start_Date IN DATE,
1378 P_Project_End_Date IN DATE,
1379 X_Return_Status OUT NOCOPY VARCHAR2,
1380 X_Msg_Count OUT NOCOPY NUMBER,
1381 X_Msg_Data OUT NOCOPY VARCHAR2
1382 )
1383 IS
1384
1385 -- Local Parameters
1386
1387 l_Return_Status VARCHAR2(50);
1388 l_Msg_Count NUMBER;
1389 l_Msg_Data VARCHAR2(500);
1390
1391 l_Gl_period_name GL_PERIODS.period_name%TYPE;
1392 l_Gl_start_Date DATE;
1393 l_Gl_End_Date DATE;
1394
1395 t_Gl_start_Date DATE;
1396 t_Gl_End_Date DATE;
1397
1398 l_Task_ID NUMBER;
1399 l_Resource_Assignment_ID NUMBER;
1400 l_Count NUMBER := 0;
1401 l_quantity NUMBER := 0;
1402 l_raw_Cost NUMBER := 0;
1403 l_Burdened_Cost NUMBER := 0;
1404 l_revenue NUMBER := 0;
1405 l_rowid VARCHAR2(50);
1406
1407 l_msg_Index_out NUMBER;
1408
1409 l_BL_Max_Date DATE;
1410 l_Boundary_Code VARCHAR2(1);
1411 l_Set_Of_Books_Id pa_implementations_all.set_of_books_id%type;
1412
1413 ------------------------------------------------------------------------------+
1414 -- Cursor to fetch all missed GL Periods for a given Res. Assign ID and Start Date
1415 ------------------------------------------------------------------------------+
1416 CURSOR cur_GL_Periods IS
1417 SELECT
1418 RA1.Resource_Assignment_ID RESOURCE_ASSIGNMENT_ID,
1419 nvl(RA1.Task_ID, -1) TASK_ID,
1420 RA1.Resource_List_Member_ID RESOURCE_LIST_MEMBER_ID,
1421 GLPRD.Period_Name PERIOD_NAME,
1422 GLPRD.Start_Date START_DATE,
1423 GLPRD.End_Date END_DATE
1424 FROM
1425 GL_Period_Statuses GLPRD,
1426 PA_Implementations IMP,
1427 PA_Resource_Assignments RA1
1428 WHERE
1429 GLPRD.Application_ID = PA_Period_Process_Pkg.Application_ID -- 101
1430 AND GLPRD.Set_Of_Books_ID = IMP.Set_Of_Books_ID
1431 AND GLPRD.Closing_status IN ('O','F')
1432 AND ( l_GL_Start_Date BETWEEN GLPRD.Start_Date AND GLPRD.End_Date OR
1433 l_GL_End_Date BETWEEN GLPRD.Start_Date AND GLPRD.End_Date OR
1434 GLPRD.Start_Date BETWEEN l_GL_Start_Date AND l_GL_End_Date )
1435 AND GLPRD.Adjustment_Period_Flag <> 'Y' -- Bug #1891179
1436 AND RA1.Project_ID = P_Project_ID
1437 AND RA1.Budget_Version_ID = p_Budget_Version_ID
1438 MINUS
1439 SELECT
1440 BL2.Resource_Assignment_ID RESOURCE_ASSIGNMENT_ID,
1441 nvl(RA2.Task_ID, -1) TASK_ID,
1442 RA2.Resource_List_Member_ID RESOURCE_LIST_MEMBER_ID,
1443 BL2.Period_Name PERIOD_NAME,
1444 BL2.Start_Date START_DATE,
1445 BL2.End_Date END_DATE
1446 FROM
1447 PA_Budget_Lines BL2,
1448 PA_Resource_Assignments RA2,
1449 GL_Period_Statuses GLPRD2,
1450 PA_Implementations IMP2
1451 WHERE
1452 RA2.Resource_Assignment_ID = BL2.Resource_Assignment_ID
1453 AND RA2.Project_ID = P_Project_ID
1454 AND RA2.Budget_Version_ID = p_Budget_Version_ID
1455 AND GLPRD2.Period_Name = BL2.Period_Name
1456 AND GLPRD2.Application_ID = PA_Period_Process_Pkg.Application_ID -- 101
1457 AND GLPRD2.Closing_status IN ('O','F')
1458 AND GLPRD2.Adjustment_Period_Flag <> 'Y' -- Bug #1891179
1459 AND GLPRD2.Set_Of_Books_ID = IMP2.Set_Of_Books_ID ;
1460
1461 BEGIN
1462
1463 PA_FCK_UTIL.debug_msg(' Entering Insert_Into_Budget_Lines ');
1464 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1465
1466 PA_FCK_UTIL.debug_msg('P_Budget_Version_ID ......'||P_Budget_Version_ID);
1467 PA_FCK_UTIL.debug_msg('P_Project_Id ......'||P_Project_Id);
1468 PA_FCK_UTIL.debug_msg('p_Project_Start_Date ......'||P_Project_Start_Date);
1469 PA_FCK_UTIL.debug_msg('p_Project_End_Date ......'||p_Project_End_Date);
1470
1471 IF P_Project_Start_Date IS NULL OR
1472 P_Project_End_Date IS NULL
1473 THEN
1474
1475 SELECT PBCO.boundary_code
1476 INTO l_boundary_code
1477 FROM PA_BUDGETARY_CONTROL_OPTIONS PBCO,
1478 PA_BUDGET_VERSIONS PBV
1479 WHERE PBV.Project_Id = p_project_id
1480 AND PBV.Budget_version_id = p_Budget_version_id
1481 AND PBCO.Project_id = PBV.Project_id
1482 AND PBCO.Budget_Type_Code = PBV.Budget_Type_Code;
1483
1484
1485 IF nvl(l_boundary_code,'P') = 'Y' then
1486 PA_FCK_UTIL.debug_msg(' Boundary code is Year');
1487
1488 SELECT MAX(BL.End_Date)
1489 INTO l_BL_Max_Date
1490 FROM PA_Budget_Lines BL
1491 WHERE BL.Budget_Version_ID = p_Budget_Version_ID;
1492
1493 PA_FCK_UTIL.debug_msg(' Max. end date is:'||l_BL_Max_Date);
1494
1495 Select set_of_books_id into l_set_of_books_id from pa_implementations;
1496
1497 PA_FCK_UTIL.debug_msg(' SOB ID:' ||l_set_of_books_id);
1498
1499 SELECT gps.year_start_date,
1500 ADD_MONTHS (gps.year_start_date, 12 ) - 1
1501 INTO t_GL_Start_Date,
1502 t_GL_End_Date
1503 FROM gl_period_statuses gps
1504 WHERE gps.application_id = PA_Period_Process_Pkg.Application_ID
1505 AND gps.set_of_books_id = l_set_of_books_id
1506 AND l_BL_Max_Date BETWEEN gps.start_date AND gps.end_date
1507 AND gps.adjustment_period_flag = 'N';
1508
1509 ELSE
1510 PA_FCK_UTIL.debug_msg(' Boundary code is not Year');
1511
1512 SELECT
1513 MIN(BL.start_Date), -- decode(p_Start_Date, NULL, MIN(BL.start_Date), p_Start_Date)
1514 MAX(BL.End_Date) -- decode(p_End_Date, NULL, MAX(BL.End_Date), p_End_Date)
1515 INTO
1516 t_GL_Start_Date,
1517 t_GL_End_Date
1518 FROM
1519 PA_Budget_Lines BL,
1520 PA_Resource_Assignments RA
1521 WHERE
1522 RA.Budget_Version_ID = p_Budget_Version_ID
1523 AND RA.Resource_Assignment_ID = BL.Resource_Assignment_ID ;
1524
1525 END IF;
1526
1527 PA_FCK_UTIL.debug_msg(' selected max bl start and end date ');
1528
1529 IF p_Project_Start_Date IS NULL
1530 THEN
1531 l_GL_Start_Date := t_GL_Start_Date ;
1532 else
1533 l_GL_Start_Date := p_Project_Start_Date ;
1534 END IF;
1535 IF p_Project_End_Date IS NULL
1536 THEN
1537 l_Gl_End_Date := t_Gl_End_Date ;
1538 else
1539 l_Gl_End_Date := p_Project_End_Date ;
1540 End IF;
1541 ELSE
1542 l_GL_Start_Date := P_Project_Start_Date;
1543 l_GL_End_Date := P_Project_End_Date;
1544 END IF;
1545
1546 PA_FCK_UTIL.debug_msg(' start_date '||to_char(l_GL_Start_Date)||' end date '||to_char(l_Gl_End_Date));
1547 FOR GlPrds IN cur_GL_Periods -- For every missed GL Periods
1548 LOOP
1549
1550 l_Resource_Assignment_ID := GlPrds.Resource_Assignment_ID ;
1551 ----------------------------------------------------------------------+
1552 -- Call table handler for inserting into PA_BUDGET_LINES with amount=0
1553 ----------------------------------------------------------------------+
1554 BEGIN
1555
1556 PA_FCK_UTIL.debug_msg(' res_id '||to_char(l_Resource_Assignment_ID)||' start date '||to_char(GlPrds.Start_Date));
1557 PA_FCK_UTIL.debug_msg(' P_Budget_Version_ID '||P_Budget_Version_ID);
1558 PA_FCK_UTIL.debug_msg(' GlPrds.Period_Name '||GlPrds.Period_Name);
1559
1560 IF GlPrds.Task_ID = -1 then
1561 l_Task_ID := NULL;
1562 ELSE
1563 l_Task_ID := GlPrds.Task_ID ;
1564 END IF;
1565
1566 PA_BUDGET_LINES_V_PKG.Insert_Row(
1567 X_ROWID => l_rowid,
1568 X_Resource_Assignment_Id => l_Resource_Assignment_ID,
1569 X_Budget_Version_Id => P_Budget_Version_ID,
1570 X_Project_Id => P_Project_ID,
1571 X_Task_Id => l_Task_ID,
1572 X_Resource_List_Member_Id => GlPrds.Resource_List_Member_Id,
1573 X_Description => NULL,
1574 X_Start_Date => GlPrds.Start_Date,
1575 X_End_Date => GlPrds.End_Date,
1576 X_Period_Name => GlPrds.Period_Name,
1577 X_Quantity => l_quantity,
1578 X_Unit_Of_Measure => NULL,
1579 X_Track_As_Labor_Flag => NULL,
1580 X_Raw_Cost => l_raw_Cost,
1581 X_Burdened_Cost => l_Burdened_Cost,
1582 X_Revenue => l_revenue,
1583 X_Change_Reason_Code => NULL,
1584 X_Last_Update_Date => SYSDATE,
1585 X_Last_Updated_By => -1,
1586 X_Creation_Date => SYSDATE,
1587 X_Created_By => -1,
1588 X_Last_Update_Login => -1,
1589 X_Attribute_Category => NULL,
1590 X_Attribute1 => NULL,
1591 X_Attribute2 => NULL,
1592 X_Attribute3 => NULL,
1593 X_Attribute4 => NULL,
1594 X_Attribute5 => NULL,
1595 X_Attribute6 => NULL,
1596 X_Attribute7 => NULL,
1597 X_Attribute8 => NULL,
1598 X_Attribute9 => NULL,
1599 X_Attribute10 => NULL,
1600 X_Attribute11 => NULL,
1601 X_Attribute12 => NULL,
1602 X_Attribute13 => NULL,
1603 X_Attribute14 => NULL,
1604 X_Attribute15 => NULL,
1605 X_Calling_Process => 'PR',
1606 X_Pm_Product_Code => NULL,
1607 X_Pm_Budget_Line_Reference => NULL,
1608 X_raw_Cost_source => 'M',
1609 X_Burdened_Cost_source => 'M',
1610 X_quantity_source => 'M',
1611 X_revenue_source => 'M',
1612 x_standard_Bill_rate => NULL,
1613 x_Average_Bill_rate => NULL,
1614 x_Average_Cost_rate => NULL,
1615 x_project_Assignment_Id => -1,
1616 x_plan_Error_Code => NULL,
1617 x_total_plan_revenue => NULL,
1618 x_total_plan_raw_Cost => NULL,
1619 x_total_plan_Burdened_Cost => NULL,
1620 x_total_plan_quantity => NULL,
1621 x_Average_Discount_percentage => NULL,
1622 x_Cost_rejection_Code => NULL,
1623 x_Burden_rejection_Code => NULL,
1624 x_revenue_rejection_Code => NULL,
1625 x_other_rejection_Code => NULL);
1626 -- Bug Fix: 4569365. Removed MRC code.
1627 -- ,x_mrc_flag => 'Y' /* FPB2 MRC */
1628 -- );
1629
1630 PA_FCK_UTIL.debug_msg(' after insert res_id '||to_char(l_Resource_Assignment_ID)||' start date '||to_char(GlPrds.Start_Date));
1631 END;
1632
1633 END LOOP; -- End of cursor Missed GL Periods
1634
1635 X_Msg_Count := l_Msg_Count ;
1636 X_Msg_Data := l_Msg_Data ;
1637 X_Return_Status := l_Return_Status;
1638
1639 RETURN;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 PA_FCK_UTIL.debug_msg(' ERROR INSERT_INTO_BUDGET_LINES '||SQLERRM);
1644 X_Msg_Count := 1;
1645 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1646 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1647
1648 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_BUDGET_ACCOUNT_PKG',
1649 P_Procedure_Name => 'Insert_Into_Budget_Lines');
1650 END Insert_Into_Budget_Lines ; /* End API Insert_Into_Budget_Lines */
1651
1652 PROCEDURE Derive_Resource_Params (
1653 p_person_Id IN per_All_people_F.person_Id%TYPE,
1654 p_Job_Id IN per_Jobs.job_Id%TYPE,
1655 p_organization_Id IN hr_All_organization_units.organization_Id%TYPE,
1656 p_supplier_Id IN po_vendors.vendor_Id%TYPE,
1657 x_Employee_number OUT NOCOPY per_All_people_F.employee_number%TYPE,
1658 x_Person_Type OUT NOCOPY PA_Employees.Person_Type%TYPE,
1659 x_Job_name OUT NOCOPY per_Jobs.name%TYPE,
1660 x_Job_Group_Id OUT NOCOPY per_Jobs.job_Group_Id%TYPE,
1661 x_Job_Group_name OUT NOCOPY per_Job_Groups.internal_name%TYPE,
1662 x_organization_type OUT NOCOPY hr_All_organization_units.type%TYPE,
1663 x_organization_name OUT NOCOPY hr_All_organization_units.name%TYPE,
1664 x_supplier_name OUT NOCOPY po_vendors.vendor_name%TYPE,
1665 X_Return_Status OUT NOCOPY VARCHAR2,
1666 X_Msg_Count OUT NOCOPY NUMBER,
1667 X_Msg_Data OUT NOCOPY VARCHAR2
1668 )
1669 AS
1670
1671 --Local Parameters
1672 l_msg_Index_out NUMBER;
1673
1674 l_Return_Status VARCHAR2(50);
1675 l_Msg_Count NUMBER;
1676 l_Msg_Data VARCHAR2(500);
1677
1678 BEGIN
1679
1680 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1681
1682 -----------------------------------------------------------------------+
1683 -- Derive Employee/Person Parameters if Resource Type: EMPLOYEE
1684 -----------------------------------------------------------------------+
1685 -- Here logic has been modified for FP_M Build 2 changes to incorporate
1686 -- the Person Type value for validating the person as Employee or Contractor
1687 -- These changes are for Contingent Labor
1688 IF P_Person_ID IS NOT NULL THEN
1689 BEGIN
1690 SELECT
1691 Decode(Current_NPW_Flag, 'Y', NPW_Number, Employee_Number),
1692 Decode(Current_NPW_Flag, 'Y', 'CWK', 'EMP') Person_Type -- FP_M changes
1693 INTO
1694 X_Employee_Number,
1695 X_Person_Type -- FP_M changes
1696 FROM
1697 PER_All_People_F
1698 WHERE
1699 Person_ID = P_Person_ID
1700 AND (Current_Employee_Flag = 'Y' OR Current_NPW_Flag = 'Y'); -- FP_M changes
1701 END;
1702 END IF;
1703
1704 -----------------------------------------------------------------------+
1705 -- Derive Job Parameters if Resource Type: JOB
1706 -----------------------------------------------------------------------+
1707 IF p_Job_ID IS NOT NULL THEN
1708 BEGIN
1709 SELECT
1710 JOB.Name JOB_NAME,
1711 JOB.Job_Group_ID JOB_GROUP_ID,
1712 JBGRP.Internal_Name JOB_GROUP_NAME
1713 INTO
1714 X_Job_Name,
1715 X_Job_Group_ID,
1716 X_Job_Group_Name
1717 FROM
1718 PER_Jobs JOB,
1719 PER_Job_Groups JBGRP
1720 WHERE
1721 JOB.job_Id = p_Job_Id
1722 AND JOB.job_Group_Id = JBGRP.job_Group_Id ;
1723 END;
1724 END IF;
1725
1726 -----------------------------------------------------------------------+
1727 -- Derive organization name if Resource Type: ORGANIZATION
1728 -----------------------------------------------------------------------+
1729 IF p_organization_Id IS NOT NULL THEN
1730 BEGIN
1731 SELECT
1732 ORG.name ORG_NAME,
1733 ORG.Type ORG_TYPE
1734 INTO
1735 x_organization_name,
1736 x_organization_type
1737 FROM
1738 HR_All_organization_units ORG
1739 WHERE
1740 ORG.organization_Id = P_Organization_ID;
1741 END;
1742 END IF;
1743
1744 -----------------------------------------------------------------------+
1745 -- Derive Supplier/vendor information for Resource Type : SUPPLIER
1746 -----------------------------------------------------------------------+
1747 IF p_supplier_Id IS NOT NULL THEN
1748 BEGIN
1749 SELECT
1750 SUP.Vendor_Name SUPPLIER_NAME
1751 INTO
1752 X_Supplier_Name
1753 FROM
1754 PO_Vendors SUP
1755 WHERE
1756 SUP.Vendor_ID = P_Supplier_ID;
1757 END;
1758 END IF;
1759
1760 X_Msg_Count := l_Msg_Count ;
1761 X_Msg_Data := l_Msg_Data ;
1762 X_Return_Status := l_Return_Status;
1763
1764 RETURN;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 x_msg_Count := 1;
1769 x_msg_Data := substr(SQLERRM, 1, 240);
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771
1772 FND_MSG_PUB.add_Exc_msg( p_pkg_name => 'PA_BUDGET_ACCOUNT_PKG',
1773 p_procedure_name => 'Derive_Resource_Params');
1774
1775 END Derive_Resource_Params ; /* End API Derive_Resource_Params */
1776
1777 PROCEDURE Derive_Task_Params (
1778 p_project_Id IN pa_projects_All.project_Id%TYPE,
1779 p_top_task_Id IN pa_tasks.task_Id%TYPE,
1780 p_low_task_Id IN pa_tasks.task_Id%TYPE,
1781 x_top_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
1782 x_task_organization_Id OUT NOCOPY hr_organization_units.organization_Id%TYPE,
1783 x_task_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
1784 x_task_service_type OUT NOCOPY pa_tasks.service_type_Code%TYPE,
1785 x_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
1786 X_Return_Status OUT NOCOPY VARCHAR2,
1787 X_Msg_Count OUT NOCOPY NUMBER,
1788 X_Msg_Data OUT NOCOPY VARCHAR2
1789 )
1790 AS
1791
1792 --Local Parameters
1793 l_msg_Index_out NUMBER;
1794
1795 l_Return_Status VARCHAR2(50);
1796 l_Msg_Count NUMBER;
1797 l_Msg_Data VARCHAR2(500);
1798
1799 BEGIN
1800
1801 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1802
1803 --------------------------------------------------------------------+
1804 -- Derive Top Task parameters if Top Task ID is passed
1805 --------------------------------------------------------------------+
1806 IF P_Top_Task_ID IS NOT NULL THEN
1807 BEGIN
1808 SELECT
1809 TOP_TASK.task_number TASK_NUMBER,
1810 ORG.Organization_Id TASK_ORGANIZATION_ID,
1811 ORG.Name TASK_ORGANIZATION_NAME,
1812 TOP_TASK.Service_Type_Code TASK_SERVICE_TYPE
1813 INTO
1814 X_Top_Task_Number,
1815 X_Task_Organization_ID,
1816 X_Task_Organization_Name,
1817 X_Task_Service_Type
1818 FROM
1819 HR_All_Organization_Units ORG,
1820 PA_Tasks TOP_TASK
1821 WHERE
1822 TOP_TASK.task_Id = p_top_task_Id
1823 AND TOP_TASK.Top_Task_ID = TOP_TASK.Task_ID
1824 AND TOP_TASK.Project_ID = p_project_Id
1825 AND ORG.organization_Id = TOP_TASK.carrying_out_organization_Id;
1826 END;
1827 END IF;
1828
1829 --------------------------------------------------------------------+
1830 -- Derive Low Task parameters if Low task id is passed
1831 --------------------------------------------------------------------+
1832 IF p_low_task_Id IS NOT NULL THEN
1833 BEGIN
1834 SELECT
1835 TASK.task_number TASK_NUMBER
1836 INTO
1837 x_task_number
1838 FROM
1839 PA_Tasks TASK
1840 WHERE
1841 TASK.task_Id = p_low_task_Id;
1842 END;
1843 END IF;
1844
1845 X_Msg_Count := l_Msg_Count ;
1846 X_Msg_Data := l_Msg_Data ;
1847 X_Return_Status := l_Return_Status;
1848
1849 RETURN;
1850
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 x_msg_Count := 1;
1854 x_msg_Data := substr(SQLERRM, 1, 240);
1855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1856
1857 FND_MSG_PUB.add_Exc_msg( p_pkg_name => 'PA_BUDGET_ACCOUNT_PKG',
1858 p_procedure_name => 'Derive_Task_Params');
1859
1860 END Derive_Task_Params ; /* End API Derive_Task_Params */
1861
1862 END PA_BUDGET_ACCOUNT_PKG ; /* End Package Body PA_BUDGET_ACCOUNT_PKG */