[Home] [Help]
PACKAGE BODY: APPS.PA_BURDEN_COSTING
Source
1 PACKAGE BODY PA_BURDEN_COSTING as
2 -- /* $Header: PAXCBCAB.pls 120.32.12020000.4 2013/04/10 09:21:36 rboyalap ship $
3
4 --Start Of Mods(SOM) Bug # 5743708
5 l_tbl_eiid typ_tbl_eiid;
6 l_tbl_cdlln typ_tbl_cdlln;
7 --End Of Mods(EOM) Bug # 5743708
8
9 current_project_id pa_projects_all.project_id%type;
10 -- Project id of the project being processed.
11 current_run_id pa_cost_distribution_lines_all.burden_sum_source_run_id%TYPE; /*Bug# 2255068*/
12 -- Run id of the batch being processed.
13 -- 3699045
14 current_sponsored_flag gms_project_types.sponsored_flag%TYPE := 'N' ;
15
16 P_DEBUG_MODE BOOLEAN := pa_cc_utils.g_debug_mode ;
17
18 P_BTC_SRC_RESRC varchar2(1) := NVL(FND_PROFILE.value('PA_RPT_BTC_SRC_RESRC'), 'N'); -- 4057874
19 G_MOAC_ORG_ID NUMBER ;
20
21 -- ======
22 -- Bug : 3699045 - PJ.M:B4:P13:OTH:PERF:XPL PERFORMANCE ISSUES IN PAVW341.SQL
23 -- set_current_sponsored_flag, get_current_sponsored_flag were added.
24 -- ======
25 PROCEDURE set_current_sponsored_flag(x_project_id in number) IS
26 BEGIN
27 if pa_gms_api.is_sponsored_project(x_project_id) THEN
28 current_sponsored_flag := 'Y' ;
29 else
30 current_sponsored_flag := 'N' ;
31 end if ;
32 END set_current_sponsored_flag;
33
34 FUNCTION get_current_sponsored_flag RETURN VARCHAR2 IS
35 BEGIN
36 return current_sponsored_flag;
37 END get_current_sponsored_flag;
38 -- ======
39 PROCEDURE set_current_project_id(x_project_id in number) IS
40 BEGIN
41 current_project_id := x_project_id;
42 END set_current_project_id;
43
44 FUNCTION get_current_project_id RETURN NUMBER IS
45 BEGIN
46 return current_project_id;
47 END get_current_project_id;
48 /*Bug# 2255068*/
49 PROCEDURE set_current_run_id(x_run_id in number)
50 IS
51 BEGIN
52 current_run_id := x_run_id;
53 END set_current_run_id;
54
55 FUNCTION get_current_run_id RETURN pa_cost_distribution_lines_all.burden_sum_source_run_id%TYPE
56 IS
57 BEGIN
58 return current_run_id;
59 END get_current_run_id;
60 /*End of changes for bug# 2255068*/
61
62 PROCEDURE create_burden_expenditure_item (p_start_project_number in pa_projects_all.segment1%TYPE,/*2255068*/
63 p_end_project_number in pa_projects_all.segment1%TYPE,/*2255068*/
64 x_request_id in number, /*2255068*/
65 x_end_date in varchar2,
66 status in out NOCOPY number,
67 stage in out NOCOPY number,
68 x_run_id in out NOCOPY number)
69 IS
70
71 ------------ Declararion of Variables ---------------
72
73 current_run_id number;
74 -- run id of burden accounting program for an invokation.
75 create_exp_grp_flag boolean := TRUE;
76 -- flag to check if new expenditure_group record to be created
77 create_exp_flag boolean := FALSE;
78 -- flag to check if new expenditure record to be created
79 init_cdl_run_id number := -9999;
80 -- Initial value of run id in CDL when they are created - Static
81 message_string varchar2(200);
82 -- For debugging messages
83
84 exp_group pa_expenditure_groups_all.expenditure_group%type;
85 -- expenditure group id for each run of this program
86 exp_id pa_expenditures_all.expenditure_id%type;
87 -- expenditure id of expenditure items
88 x_exp_id pa_expenditures_all.expenditure_id%type;
89 exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
90 exp_org_id pa_expenditure_items_all.organization_id%type;
91 -- expenditure organization/override organization
92 over_project_id pa_projects_all.project_id%type;
93 -- project id of the source cdl or the project type level override
94 over_task_id pa_tasks.task_id%type;
95 -- task id of the source cdl or the project type level override
96 prev_bcc_rec pa_cdl_burden_summary_v%rowtype;
97 -- Record to hold previous record from bcc_cur Cursor
98 l_attribute1 pa_projects_all.attribute1%type;
99 -- local variable to hold attribute1 from project for locking
100 l_burden_cost pa_expenditure_items_all.burden_cost%type;
101 -- Running total of burden cost (project functional currency)for an summarization group
102 /*
103 Multi-Currency Related changes:
104 Added local variables to store the sum of burdened_cost in denom and acct currencies
105 (also initialised to 0)
106 */
107 l_denom_burdened_cost pa_expenditure_items_all.denom_burdened_cost%type := 0;
108 -- Running total of burden cost (denom currency)for an summarization group
109 l_acct_burdened_cost pa_expenditure_items_all.acct_burdened_cost%type := 0;
110 -- Running total of burden cost (acct currency)for an summarization group
111 l_project_burdened_cost pa_expenditure_items_all.project_burdened_cost%type := 0;
112 -- Running total of burden cost (project currency)for a summarization group
113
114 i number := 0; -- running sequence to load EI
115
116 c_task_id number;
117 -- The current task id. To maintain the current task id so
118 -- call to patc.get_status needs to be called only if task_id
119 -- or project_id or expenditure_type
120 c_project_id number;
121 -- The current project id
122 c_expenditure_type varchar2(30);
123 -- The current expenditure type
124 c_billable_flag varchar2(1);
125 l_work_type_id pa_expenditure_items_all.work_type_id%TYPE := NULL;
126
127 -- To maintain the current task id, project_id and expenditure_type
128 -- call to patc.get_status needs to be called only if task_id
129 -- or project_id or expenditure_type
130 c_status varchar2(30);
131 c_msg_application VARCHAR2(30) :='PA';
132 c_msg_type VARCHAR2(1) := 'E';
133 c_msg_token1 Varchar2(240) := '';
134 c_msg_token2 Varchar2(240) :='';
135 c_msg_token3 Varchar2(240) :='';
136 c_msg_count Number ;
137 l_debug_mode VARCHAR2(1);
138
139 /*x_request_id Number; commented for bug#2255068*/
140 l_profile_set_size NUMBER := 0 ; /*2255068*/
141 l_default_set_size NUMBER := 500 ; /*2255068*/
142 /*l_last_batch_for_project VARCHAR2(1) := 'N' ; commented for Bug 4747865 */
143
144 l_user_id NUMBER ; /*2933915*/
145 lstatus NUMBER ; /*3040724*/
146 l_status NUMBER ; /*2933915*/
147 l_compiled_multiplier NUMBER ; /*2933915*/
148 l_compiled_set_id NUMBER ; /*2933915*/
149 l_stage NUMBER ; /*2933915*/
150 l_burden_profile VARCHAR2(2); /*2933915*/
151 ei_update_count NUMBER ; /*2933915*/
152 cdl_update_count NUMBER ; /*2933915*/
153 /*reason PA_Expenditure_Items.Ind_Cost_Dist_Rejection_Code%TYPE; /*2933915*/
154 l_proj_bc_enabled VARCHAR2(1);
155
156
157
158 l_prev_expenditure_id NUMBER ; -- Bug 3551106
159 l_curr_expenditure_id NUMBER ; -- Bug 3551106
160
161 /* Local variables added for 4057874 */
162 l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type DEFAULT NULL;
163 l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164 l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165 l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166 l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
167 l_inventory_item_id PA_EXPENDITURE_ITEMS_ALL.inventory_item_id%type DEFAULT NULL;
168 l_vendor_id PA_COMMITMENT_TXNS.vendor_id%type default null;
169 l_bom_labor_resource_id PA_COMMITMENT_TXNS.bom_equipment_resource_id%type default null;
170 l_bom_equipment_resource_id PA_COMMITMENT_TXNS.bom_labor_resource_id%type default null;
171
172 /* Local variables added for 5980459 */
173 l_eiid_tbl PA_PLSQL_DATATYPES.NUMTabTyp;
174 l_task_id_tbl PA_PLSQL_DATATYPES.NUMTabTyp;
175 l_org_id_tbl PA_PLSQL_DATATYPES.NUMTabTyp;
176 l_exp_item_date_tbl PA_PLSQL_DATATYPES.dateTabTyp;
177 l_exp_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
178 l_status_tbl PA_PLSQL_DATATYPES.Char30tabTyp ;
179 l_compiled_multiplier_tbl PA_PLSQL_DATATYPES.NUMtabTyp;
180 l_compiled_set_id_tbl PA_PLSQL_DATATYPES.NUMtabTyp;
181 l_stage_tbl PA_PLSQL_DATATYPES.Char30tabTyp;
182 reason PA_PLSQL_DATATYPES.Char30TabTyp;
183
184 /* SOM Bug# 5743708 */
185 l_person_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
186 l_incur_per_id_tbl PA_PLSQL_DATATYPES.NUMTabTyp;
187
188 l_cp_structure_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
189 l_cost_base_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
190 l_cp_structure VARCHAR2(30);
191 l_cost_base VARCHAR2(30);
192 /* EOM Bug# 5743708 */
193
194 ------------ Decalrarion of Cursors ---------------
195
196 -- Cursor to select all the Projects for which there are non-summarized CDLs
197
198 -- Modifying to select org_id to be passed to LoadEi program
199 /*
200 * Bug# 924438
201 * The Org_id join is created between project and project_type
202 */
203
204 /* S.N. Bug 3618193 Added Record Type to fetch different cursors */
205
206 TYPE proj_rec_type IS RECORD (
207 project_id pa_projects_all.project_id%TYPE,
208 segment1 pa_projects_all.segment1%TYPE,
209 org_id pa_projects_all.org_id%TYPE,
210 burden_account_flag pa_project_types.burden_account_flag%TYPE,
211 dest_project_id pa_projects_all.project_id%TYPE,
212 dest_task_id pa_tasks.task_id%TYPE,
213 burden_amt_display_method pa_project_types.burden_amt_display_method%TYPE
214 );
215
216 proj_rec proj_rec_type;
217
218
219 /* SOM Bug# 5743708 */
220
221 TYPE Typ_project_id IS TABLE OF pa_projects_all.project_id%TYPE INDEX BY BINARY_INTEGER;
222 TYPE Typ_segment1 IS TABLE OF pa_projects_all.segment1%TYPE INDEX BY BINARY_INTEGER;
223 TYPE Typ_org_id IS TABLE OF pa_projects_all.org_id%TYPE INDEX BY BINARY_INTEGER;
224 TYPE Typ_burden_account_flag IS TABLE OF pa_project_types.burden_account_flag%TYPE INDEX BY BINARY_INTEGER;
225 TYPE Typ_dest_project_id IS TABLE OF pa_projects_all.project_id%TYPE INDEX BY BINARY_INTEGER;
226 TYPE Typ_dest_task_id IS TABLE OF pa_tasks.task_id%TYPE INDEX BY BINARY_INTEGER;
227 TYPE Typ_burden_amt_display_method IS TABLE OF pa_project_types.burden_amt_display_method%TYPE INDEX BY BINARY_INTEGER;
228
229 l_tbl_project_id Typ_project_id;
230 l_tbl_segment1 Typ_segment1;
231 l_tbl_org_id Typ_org_id;
232 l_tbl_burden_account_flag Typ_burden_account_flag;
233 l_tbl_dest_project_id Typ_dest_project_id;
234 l_tbl_dest_task_id Typ_dest_task_id;
235 l_tbl_burden_amt_disp_method Typ_burden_amt_display_method;
236
237 l_end_date DATE := to_date(x_end_date,'DD-MM-RR');
238
239 l_gms_installed boolean;
240
241 /* EOM Bug# 5743708 */
242
243
244 /* E.N. Bug 3618193 Added Record Type to fetch different cursors */
245
246 /* Changed this cursor for Bug# 5743708 */
247
248 Cursor projects_with_eb IS /* S.N. Bug 3618193 Updated Cursor Name */
249 select p.project_id
250 , p.segment1
251 , p.org_id
252 , upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
253 , pt.burden_sum_dest_project_id dest_project_id
254 , pt.burden_sum_dest_task_id dest_task_id
255 , upper(pt.burden_amt_display_method) burden_amt_display_method
256 from pa_projects_all p, -- pa_projects_all changed to pa_projects for Bug# 5743708 /*pa_projects is changed to pa_projects_all for the bug 6610145*/
257 pa_project_types_all pt -- pa_project_types_all changed to pa_project_types for Bug# 5743708 /*pa_project_types is changed to pa_project_types_all for the bug 6610145*/
258 where pt.project_type = p.project_type
259 and p.segment1 between p_start_project_number and p_end_project_number /*2255068*/
260 and ( pt.burden_amt_display_method in ('D','d') or
261 pt.burden_amt_display_method in ('S','s') and
262 pt.burden_account_flag in ('Y','y'))
263 and pt.org_id = p.org_id /*5368274*/
264 /* Bug#3033030 Added the following to check if the project status allows creation of
265 burden trasanction */
266 and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
267 and (exists (select --1 /*Added for bug#14367928 */
268 /*+ no_unnest index(CDL PA_COST_DISTRIBUTION_LINES_N10 ) */ 1
269 /*Added for bug#14531616*/
270 /* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
271 from pa_cost_distribution_lines cdl,
272 /*2255068*/ pa_expenditure_items ei
273 where cdl.line_type = 'R'
274 and cdl.amount <> 0 --Bug 10174581
275 and cdl.burden_sum_source_run_id = init_cdl_run_id
276 and cdl.project_id = p.project_id
277 and ei.project_id = p.project_id /*Added for bug#14367928 */
278 and ei.expenditure_item_id = cdl.expenditure_item_id
279 and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
280 )
281 or exists (select 1
282 from pa_cost_distribution_lines cdl,
283 pa_expenditure_items ei
284 where cdl.line_type = 'R'
285 and cdl.amount <> 0 --Bug 10174581
286 and cdl.burden_sum_source_run_id >0
287 and nvl(cdl.reversed_flag,'N') = 'N'
288 and cdl.line_num_reversed IS NULL
289 and ei.adjustment_type ='BURDEN_RESUMMARIZE'
290 and cdl.project_id = p.project_id
291 and ei.project_id = p.project_id /* Bug# 5743708 */
292 and ei.expenditure_item_id = cdl.expenditure_item_id
293 and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
294 );
295
296
297
298 /* S.N. Bug 3618193 Added one more cursor */
299
300 /* Cursor modified for performance issue 9373031, 10052255 */
301 Cursor projects_without_eb is
302 select /*+ LEADING(p) INDEX (p, PA_PROJECTS_U2) */ p.project_id --Hint Added for BUG13828269
303 , p.segment1
304 , p.org_id
305 , upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
306 , pt.burden_sum_dest_project_id dest_project_id
307 , pt.burden_sum_dest_task_id dest_task_id
308 , upper(pt.burden_amt_display_method) burden_amt_display_method
309 from pa_projects_all p, /*changed pa_projects to pa_projects_all for the bug 13109368 */
310 pa_project_types_all pt /*changed pa_project_types to pa_project_types_all for the bug 13109368 */
311 where pt.project_type = p.project_type
312 and p.segment1 between p_start_project_number and p_end_project_number
313 and ( pt.burden_amt_display_method in ('D','d') or
314 pt.burden_amt_display_method in ('S','s') and
315 pt.burden_account_flag in ('Y','y'))
316 and pt.org_id = p.org_id
317 and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
318 and exists (
319 select /*+ NO_UNNEST INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ 1
320 from pa_cost_distribution_lines cdl
321 where cdl.line_type||'' = 'R'
322 and cdl.amount <> 0 --Bug 10174581
323 and cdl.burden_sum_source_run_id = init_cdl_run_id
324 and cdl.project_id = p.project_id
325 and ( x_end_date is null OR exists
326 (select 1 from pa_expenditure_items ei
327 where ei.expenditure_item_id = cdl.expenditure_item_id
328 and ei.expenditure_item_date <= l_end_date )
329 )
330 );
331
332 /* E.N. Bug 3618193 Added one more cursor */
333
334 /* Condition of PA_DATE <= x_end_date added for bug#1171986 */
335
336 -- Cursor to select Burden cost components of all the non-summarized CDLs of a project
337
338 /*
339 Multi-Currency Related changes:
340 Picked up additional columns source_denom_burdened_cost,source_acct_burdened_cost,
341 source_denom_currency_code, source_acct_currency_code,source_project_currency_code
342 */
343
344 /*
345 * CRL Related Changes
346 * Inclued attribute2 - attibute10 and attribute_category in select clause of the bcc_cur
347 */
348
349 /*Bug# 2368916: Added the hint ordered in the cursor below to ensure that the
350 tables (mainly:PA_COST_BASE_EXP_TYPES) are accessed in the order
351 in which it appears in the base view -pa_cdl_burden_detail_v*/
352
353 /*========================================================================================+
354 | 03-Feb-2004 - M - The grouping used for budgetory control projects and non-budgetory |
355 | control projects are different. This is, to enabled funds-check for change in burden |
356 | cost following burden schedule recompilation for projects with budgetory control |
357 | enabled. The additional grouping is by the adjustment type (=BURDEN_RESUMMARIZE). |
358 +========================================================================================*/
359 cursor bcc_cur (p_proj_bc_enabled IN VARCHAR2) is
360 select source_project_id
361 ,source_task_id
362 ,source_org_id
363 ,source_pa_date
364 ,source_attribute1
365 ,source_attribute2
366 ,source_attribute3
367 ,source_attribute4
368 ,source_attribute5
369 ,source_attribute6
370 ,source_attribute7
371 ,source_attribute8
372 ,source_attribute9
373 ,source_attribute10
374 ,source_attribute_category
375 ,source_person_type
376 ,source_po_line_id
377 ,source_adjustment_type
378 ,source_ind_cost_code
379 ,source_expenditure_type
380 ,source_ind_expenditure_type
381 ,source_cost_base
382 ,source_compiled_multiplier
383 -- ,source_ind_rate_sch_id
384 -- ,source_ind_rate_sch_rev_id
385 ,source_exp_item_id
386 ,source_line_num
387 ,source_exp_item_date
388 ,source_burden_cost
389 ,source_denom_burdened_cost
390 ,source_acct_burdened_cost
391 ,source_project_burdened_cost
392 ,source_projfunc_currency_code
393 ,source_denom_currency_code
394 ,source_acct_currency_code
395 ,source_project_currency_code
396 ,source_id
397 ,source_burden_reject_code
398 /*,dest_project_id
399 ,dest_task_id :Commented for :3069632*/
400 ,dest_org_id
401 ,dest_pa_date
402 ,dest_attribute1
403 ,dest_ind_expenditure_type
404 ,billable_flag /* Added for bug 2091559*/
405 ,dest_summary_group_resum dest_summary_group_Y /* Added for bug 5743708*/
406 ,dest_summary_group dest_summary_group_N /* Added for bug 5743708*/
407 ,source_request_id /*Bug# 2161261*/
408 ,source_system_linkage_function /* 4057874 */
409 ,source_job_id /* 4057874 */
410 ,source_nl_resource /* 4057874 */
411 ,source_nl_resource_orgn_id /* 4057874 */
412 ,source_wip_resource_id /* 4057874 */
413 ,source_incurred_by_person_id /* 4057874 */
414 ,source_inventory_item_id /* 4057874 */
415 ,source_vendor_id /* 4057874 */
416 ,src_acct_rate_date
417 ,src_acct_rate_type
418 ,src_acct_exchange_rate
419 ,src_project_rate_date
420 ,src_project_rate_type
421 ,src_project_exchange_rate
422 ,src_projfunc_cost_rate_date
423 ,src_projfunc_cost_rate_type
424 ,src_projfunc_cost_xchng_rate
425 ,cbs_element_id -- Added for CBS enahncement bug 16579913
426 from pa_cdl_burden_summary_v
427 order by DECODE(p_proj_bc_enabled, 'Y', dest_summary_group_resum, dest_summary_group), source_exp_item_date;
428
429 /* Bug S.N. 5406802 */
430
431 TYPE ltbl_bcc_cur IS TABLE OF bcc_cur%ROWTYPE INDEX BY BINARY_INTEGER;
432
433 bcc_rec pa_cdl_burden_summary_v%ROWTYPE;
434 bcc_rec1 pa_cdl_burden_summary_v%ROWTYPE;
435
436 l_bcc_rec ltbl_bcc_cur;
437
438 l_loop_ctr NUMBER := 0;
439
440 l_next_weekend_date DATE;
441 l_prev_next_weekend_date DATE;
442
443 /* END Bug 5406802 */
444
445
446 /******2933915:Cursor to select attributes for deriving new compiled set ids for the 'Special eis/cdls':
447 By SPECIAL eis we mean the one having corresponding summarized cdls and which are marked with adjsutment type
448 :BURDEN_RESUMMARIZE by the burden compilation process when the profile option PA_ENHANCED_BURDENING is 'Y'****/
449
450 /*Bug# 3040724 :We need to derive new compile set id even when burden_sum_source_run_id =-9999 and
451 adjustment_type =:BURDEN_RESUMMARIZE*/
452
453 CURSOR get_compile_cursor(l_project_id NUMBER )
454 IS
455 select ei.expenditure_item_id , ei.task_id,nvl(ei.override_to_organization_id,e.incurred_by_organization_id) organization_id ,
456 ei.expenditure_item_date, ei.expenditure_type ,
457 e.person_type person_type,
458 e.incurred_by_person_id
459 from pa_cost_distribution_lines cdl,
460 pa_expenditure_items ei,
461 pa_expenditures e /*3040724*/
462 where cdl.line_type = 'R'
463 and cdl.amount <> 0 --Bug 10174581
464 and ((cdl.burden_sum_source_run_id >0
465 and cdl.prev_ind_compiled_set_id is NOT NULL) /*2933915*/
466 OR cdl.burden_sum_source_run_id = init_cdl_run_id) /*3040724*/
467 and cdl.request_id = x_request_id
468 and ei.request_id = x_request_id /*2933915*/
469 and nvl(cdl.reversed_flag,'N') = 'N'
470 and cdl.line_num_reversed IS NULL
471 and ei.adjustment_type ='BURDEN_RESUMMARIZE'
472 and ei.project_id = l_project_id /* Bug# 5406802 */
473 and cdl.burden_sum_rejection_code ='IN_PROCESS' /*2933915*/
474 and cdl.project_id = l_project_id
475 and ei.expenditure_item_id = cdl.expenditure_item_id
476 and ei.expenditure_id = e.expenditure_id
477 and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date);/*5743708*/
478 --GROUP BY ei.expenditure_item_id , ei.task_id, ei.expenditure_item_date, ei.expenditure_type,nvl(ei.override_to_organization_id,e.incurred_by_organization_id) ;
479 /* added expenditure_item_id in group by for bug 4311703*/
480 /****2933915****/
481
482 /*Code Changes for Bug No.2984871 start */
483 l_rowcount number :=0;
484 /*Code Changes for Bug No.2984871 end */
485
486 begin
487
488 l_gms_installed := pa_gms_api.vert_install; /* Bug# 5406802 */
489
490 /* x_request_id:=FND_GLOBAL.CONC_REQUEST_ID(); --Commented for Bug# 2255068*/
491
492 /*2933915 :Caching the value of user_id and burden profile */
493
494 l_user_id:=FND_GLOBAL.USER_ID();
495
496 --l_burden_profile := nvl(fnd_profile.value('PA_ENHANCED_BURDENING'),'N'); /*2933915*/
497 l_burden_profile := pa_utils2.IsEnhancedBurdeningEnabled;
498
499 -- Step 1 . Select All projects
500
501 if pa_cc_utils.g_debug_mode then
502 l_debug_mode := 'Y';
503 else
504 l_debug_mode := 'N';
505 end if;
506 pa_debug.set_process(
507 x_process => 'PLSQL',
508 x_debug_mode => l_debug_mode);
509 pa_debug.G_Err_Stage := 'Starting Create_burden_expenditure_item' ;
510
511 pa_cc_utils.set_curr_function('create_burden_expenditure_item');
512
513 IF P_DEBUG_MODE THEN
514 pa_cc_utils.log_message('50:Entered create_burden_expenditure_item');
515 END IF;
516
517 begin
518 -- Expenditure group is set to current run id of the program
519 -- Pre-fixed with 6 bytes of the MEANING of 'BURDEN_ACCOUNTING'
520 -- lookup code 'BS'
521
522 stage := 100; -- At start
523
524 /*
525 IF P_DEBUG_MODE THEN
526 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '100:Select Org_id from Implementations');
527 END IF;
528 select org_id
529 into x_org_id
530 from pa_implementations;
531 */
532 IF P_DEBUG_MODE THEN
533 pa_cc_utils.log_message('Create_Burden_Expenditure_Item: ' || '100:Select Current Org_id.');
534 END IF;
535 IF ( G_MOAC_ORG_ID IS NULL )
536 THEN
537 G_MOAC_ORG_ID := pa_moac_utils.get_current_org_id ;
538 END IF ;
539
540 /*
541 * Bug#2255068
542 * Select the profile set size for CDLs per batch.
543 */
544 FND_PROFILE.GET('PA_NUM_CDL_PER_SET', l_profile_set_size );
545 IF ( NVL(l_profile_set_size, 0) = 0 )
546 THEN
547 l_profile_set_size := l_default_set_size ;
548 END IF;
549 /* Bug# 2255068
550 * pa_cc_utils.log_message('150:Get Exp Group and Run Id');
551 * select SUBSTRB(meaning,1,6), pa_burden_sum_run_s.nextval
552 * into exp_group, current_run_id
553 * from pa_lookups
554 * where lookup_type = 'BURDEN_ACCOUNTING'
555 * and lookup_code = 'BS'
556 * and sysdate between start_date_active and nvl(end_date_active,sysdate);
557 * exp_group := exp_group||current_run_id;
558 */
559 exception
560 when no_data_found then
561 IF P_DEBUG_MODE THEN
562 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '200:Get Exp Group and Run Id:No_Data_Found');
563 END IF;
564 goto END_OF_PROCESS;
565 when others then
566 IF P_DEBUG_MODE THEN
567 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '200:Get Exp Group and Run Id:Others');
568 END IF;
569 goto END_OF_PROCESS;
570 end;
571
572 /*SOM Bug# 5743708 */
573
574 IF (l_burden_profile= 'Y') THEN
575 OPEN projects_with_eb;
576 ELSE
577 OPEN projects_without_eb;
578 END IF;
579
580 <<PROJECT_LOOP>> -- for projects from projects cursor
581 LOOP
582
583 /* Introduced bulk collect logic for the bug# 5406802 */
584
585 l_tbl_project_id.delete;
586 l_tbl_segment1.delete;
587 l_tbl_org_id.delete;
588 l_tbl_burden_account_flag.delete;
589 l_tbl_dest_project_id.delete;
590 l_tbl_dest_task_id.delete;
591 l_tbl_burden_amt_disp_method.delete;
592
593 IF (l_burden_profile= 'Y') THEN
594 FETCH projects_with_eb BULK COLLECT INTO
595 l_tbl_project_id,
596 l_tbl_segment1,
597 l_tbl_org_id,
598 l_tbl_burden_account_flag,
599 l_tbl_dest_project_id,
600 l_tbl_dest_task_id,
601 l_tbl_burden_amt_disp_method
602 LIMIT 1000;
603 ELSE
604 FETCH projects_without_eb BULK COLLECT INTO
605 l_tbl_project_id,
606 l_tbl_segment1,
607 l_tbl_org_id,
608 l_tbl_burden_account_flag,
609 l_tbl_dest_project_id,
610 l_tbl_dest_task_id,
611 l_tbl_burden_amt_disp_method
612 LIMIT 1000;
613 END IF;
614
615 EXIT WHEN l_tbl_project_id.count = 0;
616
617 /*EOM Bug# 5743708 */
618
619
620 /* for proj_rec in projects loop -- Bug 3618193 */
621 /* Bug 3618193 : Added following check
622 based on the l_burden_profile we will open different cursosrs */
623
624
625 FOR K in l_tbl_project_id.first..l_tbl_project_id.last
626
627
628 LOOP
629
630 proj_rec.project_id := l_tbl_project_id(K);
631 proj_rec.segment1 := l_tbl_segment1(K);
632 proj_rec.org_id := l_tbl_org_id(K);
633 proj_rec.burden_account_flag :=l_tbl_burden_account_flag(K);
634 proj_rec.dest_project_id := l_tbl_dest_project_id(K);
635 proj_rec.dest_task_id := l_tbl_dest_task_id(K);
636 proj_rec.burden_amt_display_method := l_tbl_burden_amt_disp_method(K);
637
638 IF P_DEBUG_MODE THEN
639 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '250:Processing for Project:'||to_char(proj_rec.project_id));
640 END IF;
641 stage := 110; -- in project loop
642 -- Set current project id in the package pa_burden_costing for
643 -- view definitions and for local variable
644
645 IF P_DEBUG_MODE THEN
646 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '300:before set current project id');
647 END IF;
648
649 /*PA_BURDEN_COSTING.set_current_project_id(proj_rec.project_id); 5406802*/
650
651
652 /*Bug# 5406802*/
653 IF l_gms_installed THEN
654 -- ======
655 -- Bug : 3699045 - PJ.M:B4:P13:OTH:PERF:XPL PERFORMANCE ISSUES IN PAVW341.SQL
656 -- ======
657 PA_BURDEN_COSTING.set_current_sponsored_flag(proj_rec.project_id);
658 END IF;
659
660 current_project_id := proj_rec.project_id;
661 IF P_DEBUG_MODE THEN
662 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '350:after set current project id');
663 END IF;
664
665 l_proj_bc_enabled := Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(current_project_id, 'STD'); /* 5406802 moved here */
666
667 -- Step 2a. Project level validations
668 -- Set the project and task id of the burden expenditure item
669 -- depending on burden_accounting flag
670
671 if (proj_rec.burden_amt_display_method = 'S' ) then
672 if ( proj_rec.burden_account_flag = 'Y') then
673 over_project_id := proj_rec.dest_project_id;
674 over_task_id := proj_rec.dest_task_id;
675
676 IF P_DEBUG_MODE THEN
677 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '400:within if for burden_account and same ei case');
678 END IF;
679 if over_project_id is null or over_task_id is null then
680 begin
681
682 --- Error: PROJECT_TASK_NULL Destination project and
683 -- task not identified when account to seperated
684 -- project/task is opted
685 -- Mark all the CDLs of the project with error
686 stage := 120; -- in CDL update for project/task null
687 IF P_DEBUG_MODE THEN
688 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '450:before update CDL for error:project_task_null');
689 END IF;
690
691 /* Removed ei table and pa_tasks table and changed cdl_all table to cdl view for
692 bug# 1668634 */
693
694 /*Bug#2255068:Added loop and committed by batch.*/
695
696 loop
697 /*
698 * If the Through Date parameter is NOT provided, join to Expenditure Items
699 * is NOT required.
700 */
701 IF ( X_end_date IS NOT NULL )
702 THEN
703 update pa_cost_distribution_lines cdl
704 set burden_sum_rejection_code = 'PROJECT_TASK_NULL'
705 where cdl.line_type = 'R'
706 and cdl.amount <> 0 --Bug 10174581
707 and (cdl.burden_sum_source_run_id = init_cdl_run_id
708 OR
709 (cdl.burden_sum_source_run_id >0 /*2933915*/
710 and nvl(cdl.reversed_flag,'N') = 'N' /*2933915*/
711 and cdl.line_num_reversed IS NULL )) /*2933915*/
712 and cdl.project_id = current_project_id
713 and nvl(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL' /*2255068*/
714 and exists ( select NULL
715 from pa_expenditure_items_all ei
716 where ei.expenditure_item_id = cdl.expenditure_item_id
717 and ei.expenditure_item_date <= l_end_date
718 )
719 and rownum <= l_profile_set_size ;
720 ELSE
721 update pa_cost_distribution_lines cdl
722 set cdl.burden_sum_rejection_code = 'PROJECT_TASK_NULL'
723 where cdl.line_type = 'R'
724 and cdl.amount <> 0 --Bug 10174581
725 and (cdl.burden_sum_source_run_id = init_cdl_run_id
726 OR
727 (cdl.burden_sum_source_run_id >0 /*2933915*/
728 and nvl(cdl.reversed_flag,'N') = 'N' /*2933915*/
729 and cdl.line_num_reversed IS NULL )) /*2933915*/
730 and cdl.project_id = current_project_id
731 and NVL(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL'
732 and rownum <= l_profile_set_size ;
733 END IF;
734
735 /*Code Changes for Bug No.2984871 start */
736 l_rowcount:=sql%rowcount;
737 /*Code Changes for Bug No.2984871 end */
738
739 COMMIT ;
740 IF ( l_rowcount < l_profile_set_size )
741 THEN
742 COMMIT ;
743 EXIT ;
744 END IF ;
745 end loop ;
746
747 IF P_DEBUG_MODE THEN
748 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '500:after update CDL for error:project_task_null');
749 END IF;
750 goto NEXT_PROJECT;
751 exception
752 when others then
753 IF P_DEBUG_MODE THEN
754 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '550:CDL update:Others');
755 END IF;
756 goto NEXT_PROJECT;
757 end;
758 end if;
759 end if;
760 end if;
761
762 -- Step 3. Lock current project; skip project if locked by other process.
763 -- initialize CDLs run_id so that the view PA_CDL_BURDEN_DETAIL_V can
764 -- pick up all CDLs
765
766 begin
767 stage := 130; -- Locking current project
768 IF P_DEBUG_MODE THEN
769 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '600:Lock the current Project');
770 END IF;
771 select attribute1
772 into l_attribute1
773 from pa_projects_all
774 where project_id = proj_rec.project_id
775 for update of attribute1 nowait;
776 exception /*2255068*/
777 when resource_busy then
778 goto NEXT_PROJECT;
779 when others then
780 goto NEXT_PROJECT;
781 end;
782
783 /*
784 * Loop for processing CDLs in batches for this project.
785 */
786 loop
787
788 /***2933915***/
789
790 /**2933915 :To process special eis : Copying previous compiled_set_id with ind_compiled_set_id****/
791 /*This is to insert audit records for affected cdls before starting with summarisation.
792 Since the update immediately after this loop is updating burden_sum_source_run_id so we have to insert audit record before
793 at this point to identify original burden sum source run id to insert in audit table */
794
795 /************ MOVED THE CODE HERE WHICH WAS AFTER THE IF-ENDIF LOGIC (5406802)***********/
796
797 /*Bug#2255068: Run ids are generated once for each batch*/
798
799 select SUBSTRB(meaning,1,6), pa_burden_sum_run_s.nextval
800 into exp_group, current_run_id
801 from pa_lookups
802 where lookup_type = 'BURDEN_ACCOUNTING'
803 and lookup_code = 'BS'
804 and sysdate between start_date_active and nvl(end_date_active,sysdate);
805
806 exp_group := exp_group||current_run_id;
807 /*
808 * Bug#2255068
809 * Setting the current_run_id in the global variable so that the cdl_burden_detail_v
810 * will pick-up only these records.
811 */
812 PA_BURDEN_COSTING.set_current_run_id(current_run_id);
813
814 /*
815 * Bug#2255068
816 * Added - so, new expenditure group is created per batch - since the run_id
817 * changes for each batch.
818 */
819 create_exp_grp_flag := TRUE;
820
821 /************ END MOVED THE CODE HERE WHICH WAS AFTER THE IF-ENDIF LOGIC (5406802)***********/
822
823
824 If l_burden_profile ='Y' Then /*3040724 :process special eis only when profile is 'Y'*/
825
826 IF P_DEBUG_MODE THEN
827 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '603:Update CDL with prev_ind_compiled_set_id');
828 END IF;
829 /*SOM Bug# 5743708*/
830 l_tbl_eiid.delete;
831 l_tbl_cdlln.delete;
832 /*EOM Bug# 5743708*/
833 if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
834 UPDATE pa_cost_distribution_lines cdl
835 SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
836 ,cdl.ind_compiled_set_id), /*3071338*/
837 request_id = x_request_id,
838 burden_sum_rejection_code = 'IN_PROCESS' /*2933915:Stamping it for intermediate processing*/
839 where cdl.line_num_reversed is null
840 and nvl(cdl.reversed_flag,'N') = 'N'
841 and cdl.line_type = 'R'
842 and cdl.amount <> 0 --Bug 10174581
843 /* and cdl.burden_sum_source_run_id > 0 :3040724*/
844 /* and cdl.burden_sum_rejection_code IS NULL 3040274 -Commented to process rejected cdls of previous runs*/
845 and cdl.project_id = current_project_id
846 and cdl.request_id <>x_request_id /*2933915*/
847 and exists (select null
848 from pa_expenditure_items ei
849 where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
850 and ei.project_id = current_project_id /*5406802*/
851 and ei.expenditure_item_id = cdl.expenditure_item_id
852 and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
853 and rownum <= l_profile_set_size /*2933915*/
854 returning expenditure_item_id, line_num bulk collect into l_tbl_eiid, l_tbl_cdlln; /* Bug# 5406802 */
855
856 else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
857
858 UPDATE pa_cost_distribution_lines cdl
859 SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
860 ,cdl.ind_compiled_set_id),
861 request_id = x_request_id,
862 burden_sum_rejection_code = 'IN_PROCESS'
863 where cdl.line_num_reversed is null
864 and nvl(cdl.reversed_flag,'N') = 'N'
865 and cdl.line_type = 'R'
866 and cdl.amount <> 0 --Bug 10174581
867 and cdl.project_id = current_project_id
868 and cdl.request_id <>x_request_id
869 and exists (select null
870 from pa_expenditure_items ei
871 where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
872 and ei.project_id = current_project_id
873 and ei.expenditure_item_id = cdl.expenditure_item_id
874 and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
875 and exists (select null
876 from gms_award_distributions adl
877 where adl.expenditure_item_id = cdl.expenditure_item_id
878 and adl.cdl_line_num = cdl.line_num
879 and adl.adl_status = 'A'
880 and adl.document_type = 'EXP')
881 and rownum <= l_profile_set_size
882 returning expenditure_item_id, line_num bulk collect into l_tbl_eiid, l_tbl_cdlln; /* Bug# 5406802 */
883
884 end if;
885 /* bug 9165385 ends */
886
887 IF l_tbl_eiid.count > 0 THEN /* Bug# 5406802 */
888
889 /* Changed this update for Bug# 5406802 */
890 FORALL I in 1..l_tbl_eiid.count
891 UPDATE pa_expenditure_items ei
892 set ei.request_id = x_request_id
893 where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
894 and ei.project_id = current_project_id
895 and ei.request_id <> x_request_id /*2933915*/
896 and ei.expenditure_Item_id = l_tbl_eiid(i);
897
898
899 IF P_DEBUG_MODE THEN
900 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '604:Creating Audit in pa_aud_cost_dist_lines');
901 END IF;
902
903 PA_BURDEN_COSTING.InsBurdenAudit(current_project_id,x_request_id,l_user_id,lstatus);
904
905 /*To get compiled set for special cdls and update on ei and cdl*/
906
907 IF P_DEBUG_MODE THEN
908 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '605:Deriving compiled set id for special eis');
909 END IF;
910
911
912 OPEN get_compile_cursor(current_project_id) ;
913
914 LOOP
915 l_eiid_tbl.Delete;
916 l_task_id_tbl.Delete;
917 l_org_id_tbl.Delete;
918 l_exp_item_date_tbl.Delete;
919 l_exp_type_tbl.Delete;
920 l_stage_tbl.Delete;
921 l_status_tbl.Delete;
922 l_compiled_multiplier_tbl.Delete;
923 l_compiled_set_id_tbl.Delete;
924 l_cp_structure_tbl.Delete;/*5743708*/
925 l_cost_base_tbl.Delete;/*5743708*/
926
927 FETCH get_compile_cursor BULK COLLECT INTO
928 l_eiid_tbl ,
929 l_task_id_tbl ,
930 l_org_id_tbl ,
931 l_exp_item_date_tbl,
932 l_exp_type_tbl,
933 l_person_type_tbl,
934 l_incur_per_id_tbl
935 LIMIT l_profile_set_size ;
936
937 --InsertIntoDummy (' After fetching from get compile cursor ');
938
939 IF nvl(l_eiid_tbl.count,0) =0 THEN /*3134445*/
940 EXIT;
941 END IF;
942
943 FOR i in 1..l_eiid_tbl.count loop
944
945 pa_cost_plus1.get_compile_set_info(p_txn_interface_id =>l_eiid_tbl(i),
946 task_id =>l_task_id_tbl(i),
947 effective_date =>l_exp_item_date_tbl(i),
948 expenditure_type =>l_exp_type_tbl(i),
949 organization_id =>l_org_id_tbl(i),
950 schedule_type =>'C',
951 compiled_multiplier =>l_compiled_multiplier,
952 compiled_set_id =>l_compiled_set_id ,
953 status =>l_status,
954 stage =>l_stage,
955 x_cp_structure =>l_cp_structure, -- Bug# 5406802
956 x_cost_base =>l_cost_base -- Bug# 5406802
957 );
958
959 --InsertIntoDummy (' After get compile set info ');
960
961 l_compiled_multiplier_tbl(i) := l_compiled_multiplier;
962 l_compiled_set_id_tbl(i) := l_compiled_set_id ;
963
964 /* S.N. Bug# 5406802 */
965 l_cp_structure_tbl(i) := l_cp_structure;
966 l_cost_base_tbl(i) := l_cost_base;
967 /* E.N Bug# 5406802 */
968
969 l_status_tbl(i) := l_status;
970 l_stage_tbl(i) := l_stage ;
971
972 /*2933915 :Added Error handling for pa_cost_plus1.get_compile_set_info*/
973 IF ((l_status_tbl(i) = 100) and (l_stage_tbl(i) = 400)) THEN /*Cannot find cost base -burdened cost equals raw cost :2933915*/
974 l_compiled_set_id_tbl(i) :=NULL;
975 ELSE
976 IF (l_status_tbl(i) <>0 ) THEN
977 IF (l_status_tbl(i) = 100) THEN
978 IF (l_stage_tbl(i) = 200) THEN
979 reason(i) := 'NO_IND_RATE_SCH_REVISION';
980 ELSIF (l_stage_tbl(i) = 300) THEN
981 reason(i) := 'NO_COST_PLUS_STRUCTURE';
982 ELSIF (l_stage_tbl(i) = 500) THEN
983 reason(i) := 'NO_ORGANIZATION';
984 ELSIF (l_stage_tbl(i) = 600) THEN
985 reason(i) := 'NO_COMPILED_MULTIPLIER'; --Added for the bug#6033835
986 ELSIF (l_stage_tbl(i) = 700) THEN
987 reason(i) := 'NO_ACTIVE_COMPILED_SET';
988 ELSE
989 reason(i) := 'GET_INDIRECT_COST_FAIL';
990 END IF;
991 ELSE
992 reason(i) := 'GET_INDIRECT_COST_FAIL';
993 END IF;
994 ELSE
995 reason(i) := NULL ;
996 END IF ; /*End if for l_status <>0*/
997 END IF ; /*If ((l_status = 100) and (l_stage = 400))*/
998 END loop ;
999
1000 IF P_DEBUG_MODE THEN
1001 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607 STATUS :Update eis/cdls ');/*5980459*/
1002 END IF;
1003
1004 FORALL i in 1..l_eiid_tbl.count
1005 update pa_cost_distribution_lines cdl
1006 set cdl.burden_sum_rejection_code = reason(i),
1007 cdl.prev_ind_compiled_set_id =NULL
1008 where cdl.request_id = x_request_id
1009 AND cdl.project_id = current_project_id
1010 AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1011 AND cdl.expenditure_item_id = l_eiid_tbl(i)
1012 AND (l_status_tbl(i) <> 0) AND (l_stage_tbl(i) <> 400 or l_status_tbl(i) <> 100);
1013
1014 /*2933915 : Update affected ei with the newly derived compiled set_id */
1015
1016 FORALL i in 1..l_eiid_tbl.count
1017 UPDATE pa_expenditure_items ei
1018 SET cost_ind_compiled_set_id = l_compiled_set_id_tbl(i)
1019 where ei.task_id =l_task_id_tbl(i)
1020 AND ei.expenditure_type =l_exp_type_tbl(i)
1021 AND trunc(ei.expenditure_item_date) =trunc(l_exp_item_date_tbl(i))
1022 AND ei.request_id = x_request_id /*2933915*/
1023 AND ei.expenditure_item_id = l_eiid_tbl(i)
1024 AND ((l_status_tbl(i) = 0) OR (l_status_tbl(i) =100 AND l_stage_tbl(i) =400)) ;
1025
1026
1027
1028 /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999*/
1029
1030 FORALL i in 1..l_eiid_tbl.count
1031 UPDATE pa_cost_distribution_lines cdl
1032 set ind_compiled_set_id = l_compiled_set_id_tbl(i) ,
1033 burden_sum_source_run_id = -9999 ,
1034 burden_sum_rejection_code = NULL
1035 where cdl.request_id = x_request_id
1036 AND cdl.project_id = current_project_id
1037 /* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724*/
1038 AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915*/
1039 AND cdl.expenditure_item_id = l_eiid_tbl(i)
1040 AND ((l_status_tbl(i) = 0) OR (l_status_tbl(i) =100 AND l_stage_tbl(i) =400));
1041
1042
1043
1044 --InsertIntoDummy (' Before inserting into global temp ');
1045 /* Bug 5896943: Inserting prvdr_accrual_date in place of expenditure_item_date
1046 for period accrual transactions so that the reversal BTC's will be in the future period.
1047 */
1048 /* Insert into global temp table with the Ei's selected by get_compile_cursor Bug# 5406802 */
1049 /* Modified expenditure item date for 5907315*/
1050 FORALL i in 1..l_eiid_tbl.count
1051 insert into PA_EI_CDL_CM_GTEMP(
1052 PROJECT_ID ,TASK_ID ,ORGANIZATION_ID
1053 ,PA_DATE ,PA_PERIOD_NAME ,ATTRIBUTE1
1054 ,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4
1055 ,ATTRIBUTE5 ,ATTRIBUTE6 ,ATTRIBUTE7
1056 ,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
1057 ,ATTRIBUTE_CATEGORY ,PERSON_TYPE ,PO_LINE_ID
1058 ,SYSTEM_LINKAGE_FUNCTION ,EI_EXPENDITURE_TYPE ,IND_COMPILED_SET_ID
1059 ,PREV_IND_COMPILED_SET_ID ,EXPENDITURE_ITEM_ID ,LINE_NUM
1060 ,EXPENDITURE_ITEM_DATE ,CDL_AMOUNT ,CDL_PROJFUNC_CURRENCY_CODE
1061 ,CDL_DENOM_RAW_COST ,CDL_DENOM_CURRENCY_CODE ,CDL_ACCT_RAW_COST
1062 ,CDL_ACCT_CURRENCY_CODE ,CDL_PROJECT_RAW_COST ,CDL_PROJECT_CURRENCY_CODE
1063 ,BURDEN_SUM_SOURCE_RUN_ID ,BURDEN_SUM_REJECTION_CODE ,SYSTEM_REFERENCE1
1064 ,DENOM_CURRENCY_CODE ,ACCT_CURRENCY_CODE ,PROJECT_CURRENCY_CODE
1065 ,PROJFUNC_CURRENCY_CODE ,BILLABLE_FLAG ,REQUEST_ID
1066 ,ADJUSTMENT_TYPE ,JOB_ID ,NON_LABOR_RESOURCE
1067 ,NON_LABOR_RESOURCE_ORGN_ID ,WIP_RESOURCE_ID ,INCURRED_BY_PERSON_ID
1068 ,INVENTORY_ITEM_ID ,COST_PLUS_STRUCTURE ,COST_BASE
1069 ,ORG_ID ,ACCT_RATE_DATE ,ACCT_RATE_TYPE
1070 ,ACCT_EXCHANGE_RATE ,PROJECT_RATE_DATE ,PROJECT_RATE_TYPE
1071 ,PROJECT_EXCHANGE_RATE ,PROJFUNC_COST_RATE_DATE ,PROJFUNC_COST_RATE_TYPE
1072 ,PROJFUNC_COST_EXCHANGE_RATE
1073 ,CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
1074 )
1075 select
1076 cdl.project_id ,cdl.task_id ,l_org_id_tbl(i)
1077 ,cdl.pa_date ,decode(cdl.prev_ind_compiled_set_id, null, cdl.pa_period_name
1078 ,nvl(pa_utils2.get_pa_period_name(ei.expenditure_item_date, ei.org_id), cdl.pa_period_name))
1079 , ei.attribute1
1080 , ei.attribute2 , ei.attribute3 , ei.attribute4
1081 , ei.attribute5 , ei.attribute6 , ei.attribute7
1082 , ei.attribute8 , ei.attribute9 , ei.attribute10
1083 , ei.attribute_category , l_person_type_tbl(i) , ei.po_line_id
1084 , ei.system_linkage_function , ei.expenditure_type , cdl.ind_compiled_set_id
1085 , cdl.prev_ind_compiled_set_id , ei.expenditure_item_id , cdl.line_num
1086 , decode(NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N'),'N',nvl(ei.prvdr_accrual_date,ei.expenditure_item_date),ei.expenditure_item_date),
1087 cdl.amount , cdl.projfunc_currency_code
1088 , cdl.denom_raw_cost , cdl.denom_currency_code , cdl.acct_raw_cost
1089 , cdl.acct_currency_code , cdl.project_raw_cost , cdl.project_currency_code
1090 , current_run_id , cdl.burden_sum_rejection_code , cdl.system_reference1
1091 , ei.denom_currency_code , ei.acct_currency_code , ei.project_currency_code
1092 , ei.projfunc_currency_code , cdl.billable_flag , cdl.request_id
1093 , DECODE(ei.system_linkage_function, 'VI', ei.adjustment_type
1094 , DECODE(ei.po_line_id, NULL, NULL, ei.adjustment_type) ) adjustment_type
1095 , ei.job_id , ei.non_labor_resource
1096 , ei.organization_id NON_LABOR_RESOURCE_ORGN_ID , ei.wip_resource_id , l_incur_per_id_tbl(i)
1097 , ei.inventory_item_id ,l_cp_structure_tbl(i) ,l_cost_base_tbl(i)
1098 , ei.org_id ,CDL.ACCT_RATE_DATE ,CDL.ACCT_RATE_TYPE
1099 ,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
1100 ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
1101 ,CDL.PROJFUNC_COST_EXCHANGE_RATE
1102 ,CDL.CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
1103 FROM
1104 PA_COST_DISTRIBUTION_LINES_ALL CDL,
1105 PA_EXPENDITURE_ITEMS EI
1106 WHERE ei.expenditure_item_id = l_eiid_tbl(i)
1107 AND cdl.expenditure_item_id = ei.expenditure_item_id
1108 AND cdl.request_id = x_request_id
1109 AND cdl.project_id = current_project_id
1110 AND cdl.burden_sum_rejection_code is NULL
1111 AND cdl.line_type = 'R'
1112 AND ( ei.transaction_source IS NULL or
1113 --pa_utils2.get_ts_allow_burden_flag(ei.transaction_source)<>'Y' ); modified as part of 12331962
1114 'Y' <> ( SELECT allow_burden_flag
1115 FROM pa_transaction_sources pts
1116 WHERE pts.transaction_source = ei.transaction_source
1117 )
1118 );
1119
1120 --InsertIntoDummy (' After inserting into global temp ');
1121
1122
1123 END LOOP;
1124 close get_compile_cursor ;
1125 End If; /* End If for l_tbl_eiid.count > 0 */
1126
1127 /*Commented for bug 5980459
1128 FOR rec IN get_compile_cursor(current_project_id)
1129 LOOP
1130
1131 /*Deriving new compiled set id for the 'special' eis
1132 pa_cost_plus1.get_compile_set_info(p_txn_interface_id =>rec.expenditure_item_id, /*bug 4311703
1133 task_id =>rec.task_id,
1134 effective_date =>rec.expenditure_item_date,
1135 expenditure_type =>rec.expenditure_type,
1136 organization_id => rec.organization_id,
1137 schedule_type =>'C',
1138 compiled_multiplier =>l_compiled_multiplier,
1139 compiled_set_id => l_compiled_set_id ,
1140 status => l_status,
1141 stage => l_stage);
1142
1143 /*2933915 :Added Error handling for pa_cost_plus1.get_compile_set_info
1144 IF ((l_status = 100) and (l_stage = 400)) THEN /*Cannot find cost base -burdened cost equals raw
1145 cost :2933915
1146 l_compiled_set_id :=NULL;
1147 ELSE
1148 IF (l_status <>0 ) THEN
1149 IF (l_status = 100) THEN
1150 IF (l_stage = 200) THEN
1151 reason := 'NO_IND_RATE_SCH_REVISION';
1152 ELSIF (l_stage = 300) THEN
1153 reason := 'NO_COST_PLUS_STRUCTURE';
1154 ELSIF (l_stage = 500) THEN
1155 reason := 'NO_ORGANIZATION';
1156 ELSIF (l_stage = 600) THEN
1157 reason := 'NO_COMPILED_MULTIPLIER';
1158 ELSIF (l_stage = 700) THEN
1159 reason := 'NO_ACTIVE_COMPILED_SET';
1160 ELSE
1161 reason := 'GET_INDIRECT_COST_FAIL';
1162 END IF;
1163 ELSE
1164 reason := 'GET_INDIRECT_COST_FAIL';
1165 END IF;
1166
1167 IF P_DEBUG_MODE THEN
1168 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '606:Update CDL with rejection reason ');
1169 END IF;
1170
1171 update pa_cost_distribution_lines cdl
1172 set cdl.burden_sum_rejection_code = reason,
1173 cdl.prev_ind_compiled_set_id =NULL
1174 where cdl.request_id = x_request_id
1175 AND cdl.project_id = current_project_id
1176 /* AND cdl.prev_ind_compiled_set_id is NOT NULL :3040724
1177 AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1178 AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1179 from pa_expenditure_items_all ei,
1180 pa_expenditures_all e
1181 where e.expenditure_id =ei.expenditure_id
1182 AND ei.task_id =rec.task_id
1183 AND ei.expenditure_type =rec.expenditure_type
1184 AND ei.expenditure_item_date =rec.expenditure_item_date
1185 AND rec.organization_id = nvl(ei.override_to_organization_id,
1186 e.incurred_by_organization_id)
1187 AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'
1188 );
1189 END IF ; /*End if for l_status <>0
1190 END IF ; /*If ((l_status = 100) and (l_stage = 400))
1191
1192 /*2933915 : Update affected ei with the newly derived compiled set_id
1193
1194 IF P_DEBUG_MODE THEN
1195 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607:Update eis/cdls with newly derived compiled set id ');
1196 END IF;
1197
1198 IF ((l_status = 0) OR (l_status =100 AND l_stage =400)) THEN
1199
1200 UPDATE pa_expenditure_items ei
1201 SET cost_ind_compiled_set_id = l_compiled_set_id
1202 where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
1203 AND ei.task_id =rec.task_id
1204 AND ei.expenditure_type =rec.expenditure_type
1205 AND ei.project_id =current_project_id
1206 AND trunc(ei.expenditure_item_date) =trunc(rec.expenditure_item_date)
1207 AND ei.request_id = x_request_id /*2933915
1208 AND exists (select 1
1209 from pa_expenditures e,
1210 pa_cost_distribution_lines cdl
1211 where e.expenditure_id =ei.expenditure_id
1212 AND cdl.expenditure_item_id =ei.expenditure_item_id
1213 AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1214 AND nvl(ei.override_to_organization_id,e.incurred_by_organization_id)=rec.organization_id);
1215
1216
1217 /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999
1218
1219 UPDATE pa_cost_distribution_lines cdl
1220 set ind_compiled_set_id = l_compiled_set_id ,
1221 burden_sum_source_run_id = -9999 ,
1222 burden_sum_rejection_code = NULL
1223 where cdl.request_id = x_request_id
1224 AND cdl.project_id = current_project_id
1225 /* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724
1226 AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915
1227 AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1228 from pa_expenditure_items_all ei,
1229 pa_expenditures_all e
1230 where e.expenditure_id =ei.expenditure_id
1231 AND ei.task_id =rec.task_id
1232 AND ei.expenditure_type =rec.expenditure_type
1233 AND ei.expenditure_item_date =rec.expenditure_item_date
1234 AND rec.organization_id = nvl(ei.override_to_organization_id,e.incurred_by_organization_id)
1235 AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'
1236 );
1237
1238 /* COMMIT; commented for bug4747865
1239 END IF ; /*2933915 :End if -((status = 0) OR (status =100 AND stage =400))
1240 END LOOP; /* End loop for get_compile_cursor
1241 */
1242 IF P_DEBUG_MODE THEN
1243 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '608:Special cdls are also ready for resummarized now ');
1244 END IF;
1245 End if ; /*End of profile option check :3040724*/
1246
1247 /*End of changes for 2933915*/
1248
1249 begin
1250 stage := 140; -- Locking CDLs, initializing rejection code
1251
1252 IF P_DEBUG_MODE THEN
1253 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '650:Lock the CDLs');
1254 END IF;
1255
1256 /*Bug#2255068 :Resetting this flag */
1257
1258 /*l_last_batch_for_project := 'N' ; * Bug 4747865 */
1259
1260 /*
1261 * Bug#2255068
1262 * The following update is modified to update a set of CDLs to the current_run_id.
1263 * The cdl_burden_detail_v picks up CDLs of this run_id.
1264 * Also this update is modified to update cdls on the basis of ei date(and not on
1265 * pa_date.
1266 */
1267 IF ( X_end_date IS NOT NULL )
1268 THEN
1269 /* Removed ei table and pa_tasks table and changed cdl_all table to cdl view for
1270 bug# 1668634
1271 */
1272 if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1273
1274 /* Bug# 9373031: Added hint for performane issue */
1275
1276 update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1277 set burden_sum_rejection_code = NULL,
1278 request_id = x_request_id, /*2161261*/
1279 burden_sum_source_run_id = current_run_id
1280 where cdl.line_type = 'R'
1281 and cdl.amount <> 0 --Bug 10174581
1282 and cdl.burden_sum_source_run_id = init_cdl_run_id
1283 and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
1284 request_id <> x_request_id or
1285 burden_sum_rejection_code is null)
1286 and cdl.project_id = current_project_id
1287 and exists
1288 (select null
1289 from pa_expenditure_items_all ei
1290 where ei.expenditure_item_id = cdl.expenditure_item_id
1291 and ei.expenditure_item_date <= l_end_date
1292 )
1293 and rownum <= l_profile_set_size;
1294 /*and cdl.PA_DATE <= nvl(l_end_date,cdl.PA_DATE); Bug# 2255068*/
1295
1296 else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1297
1298 update pa_cost_distribution_lines cdl
1299 set burden_sum_rejection_code = NULL,
1300 request_id = x_request_id, /*2161261*/
1301 burden_sum_source_run_id = current_run_id
1302 where cdl.line_type = 'R'
1303 and cdl.amount <> 0 --Bug 10174581
1304 and cdl.burden_sum_source_run_id = init_cdl_run_id
1305 and (burden_sum_rejection_code = 'IN_PROCESS' or
1306 request_id <> x_request_id or
1307 burden_sum_rejection_code is null)
1308 and cdl.project_id = current_project_id
1309 and exists
1310 (select null
1311 from pa_expenditure_items_all ei
1312 where ei.expenditure_item_id = cdl.expenditure_item_id
1313 and ei.expenditure_item_date <= l_end_date
1314 )
1315 and exists
1316 (select null
1317 from gms_award_distributions adl
1318 where adl.expenditure_item_id = cdl.expenditure_item_id
1319 and adl.cdl_line_num = cdl.line_num
1320 and adl.adl_status = 'A'
1321 and adl.document_type = 'EXP')
1322 and rownum <= l_profile_set_size;
1323
1324 end if;
1325 /* bug 9165385 ends */
1326
1327 ELSE
1328
1329 if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1330
1331 /* Bug# 9373031: Added hint for performane issue */
1332 update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1333 set burden_sum_rejection_code = NULL,
1334 request_id = x_request_id, /*2161261*/
1335 burden_sum_source_run_id = current_run_id
1336 where cdl.line_type = 'R'
1337 and cdl.amount <> 0 --Bug 10174581
1338 and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
1339 request_id <> x_request_id or
1340 burden_sum_rejection_code is null)
1341 and cdl.burden_sum_source_run_id = init_cdl_run_id
1342 and cdl.project_id = current_project_id
1343 and rownum <= l_profile_set_size;
1344
1345 else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1346
1347 update pa_cost_distribution_lines cdl
1348 set burden_sum_rejection_code = NULL,
1349 request_id = x_request_id, /*2161261*/
1350 burden_sum_source_run_id = current_run_id
1351 where cdl.line_type = 'R'
1352 and cdl.amount <> 0 --Bug 10174581
1353 and (burden_sum_rejection_code = 'IN_PROCESS' or
1354 request_id <> x_request_id or
1355 burden_sum_rejection_code is null)
1356 and cdl.burden_sum_source_run_id = init_cdl_run_id
1357 and cdl.project_id = current_project_id
1358 and exists
1359 (select null
1360 from gms_award_distributions adl
1361 where adl.expenditure_item_id = cdl.expenditure_item_id
1362 and adl.cdl_line_num = cdl.line_num
1363 and adl.adl_status = 'A'
1364 and adl.document_type = 'EXP')
1365 and rownum <= l_profile_set_size;
1366
1367 end if;
1368 /* bug 9165385 ends */
1369
1370 END IF;
1371
1372 IF ( SQL%ROWCOUNT = 0 )
1373 THEN
1374 /*
1375 * Completed processing all CDLs for this project.
1376 */
1377 EXIT ;
1378 /*ELSIF ( SQL%ROWCOUNT < l_profile_set_size )
1379 THEN
1380 l_last_batch_for_project := 'Y' ; * Bug 4747865 */
1381 END IF;
1382
1383 /*
1384 * Bug#2255068
1385 * The following update ensures that all CDLs of an EI gets processed
1386 * in the same batch.
1387 */
1388
1389 if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1390
1391 /* Bug# 9373031: Added hint for performane issue */
1392 update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1393 set burden_sum_rejection_code = NULL
1394 ,request_id = x_request_id
1395 ,burden_sum_source_run_id = current_run_id
1396 where cdl.line_type = 'R'
1397 and cdl.amount <> 0 --Bug 10174581
1398 and cdl.burden_sum_source_run_id = init_cdl_run_id
1399 and cdl.project_id = current_project_id
1400 and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
1401 request_id <> x_request_id or
1402 burden_sum_rejection_code is null)
1403 and exists
1404 (select NULL
1405 from pa_cost_distribution_lines cdl1
1406 where cdl1.burden_sum_source_run_id+0 = current_run_id
1407 and cdl1.burden_sum_rejection_code is NULL /*3071338*/
1408 and cdl1.expenditure_item_id= cdl.expenditure_item_id) ;
1409
1410 else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1411
1412 update pa_cost_distribution_lines cdl
1413 set burden_sum_rejection_code = NULL
1414 ,request_id = x_request_id
1415 ,burden_sum_source_run_id = current_run_id
1416 where cdl.line_type = 'R'
1417 and cdl.amount <> 0 --Bug 10174581
1418 and cdl.burden_sum_source_run_id = init_cdl_run_id
1419 and cdl.project_id = current_project_id
1420 and (burden_sum_rejection_code = 'IN_PROCESS' or
1421 request_id <> x_request_id or
1422 burden_sum_rejection_code is null) /*Added for the bug#5683194*/
1423 and exists
1424 (select NULL
1425 from pa_cost_distribution_lines cdl1
1426 where cdl1.burden_sum_source_run_id+0 = current_run_id
1427 and cdl1.burden_sum_rejection_code is NULL /*3071338*/
1428 and cdl1.expenditure_item_id= cdl.expenditure_item_id)
1429 and exists
1430 (select null
1431 from gms_award_distributions adl
1432 where adl.expenditure_item_id = cdl.expenditure_item_id
1433 and adl.cdl_line_num = cdl.line_num
1434 and adl.adl_status = 'A'
1435 and adl.document_type = 'EXP');
1436
1437 end if;
1438 /* bug 9165385 ends */
1439
1440 /*
1441 * Bug#2255068
1442 * The rollback statements in the exception clause is added to avoid
1443 * any unsummarized CDL getting left-out with a valid source_run_id -
1444 * due to the above update.
1445 */
1446 exception
1447 when resource_busy then
1448 IF P_DEBUG_MODE THEN
1449 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '700:Lock the CDLs:Resource_busy');
1450 END IF;
1451 ROLLBACK;
1452 goto NEXT_PROJECT;
1453 when others then
1454 ROLLBACK;
1455 IF P_DEBUG_MODE THEN
1456 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '700:Lock the CDLs:Others');
1457 END IF;
1458 goto NEXT_PROJECT;
1459 end;
1460
1461
1462 l_denom_burdened_cost := 0; /* Bug 1535280 Initialised for every project run */
1463 prev_bcc_rec.dest_summary_group := null;
1464 l_burden_cost := 0;
1465 /*
1466 * l_project_burdened_cost is newly added and i'm initializing it.
1467 * but l_acct_burdened_cost was already there and i'm initializing it now - because
1468 * i thought it should be. It should have been done with bug 1535280 itself. -rahariha
1469 */
1470 l_acct_burdened_cost := 0;
1471 l_project_burdened_cost := 0;
1472
1473 /* Bug# 685104 - Intializing for the next project.*/
1474 prev_bcc_rec.source_org_id := null;
1475 prev_bcc_rec.billable_flag := null; /*bug# 2091559*/
1476 -- Step 4. select Burden cost components of CDLs.
1477
1478 /* SOM Bug# 5406802 */
1479
1480 populate_gtemp(current_run_id, current_project_id, x_end_date);
1481 update_gtemp(x_request_id); /*Added for the bug#5949107*/
1482
1483 /* EOM Bug# 5406802 */
1484 begin
1485
1486 IF P_DEBUG_MODE THEN
1487 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '850:call Flusheitabs');
1488 END IF;
1489 pa_transactions.FlushEiTabs();
1490 i := 0;
1491 /*=========================================================+
1492 | CWK - See if the project has Budgetory control enabled. |
1493 +=========================================================*/
1494 /* Introduced bulk collect logic for bcc cursor for Bug# 5406802 */
1495
1496 l_bcc_rec.delete;
1497 bcc_rec := bcc_rec1;
1498 l_loop_ctr := 0;
1499
1500 open bcc_cur(l_proj_bc_enabled);
1501 fetch bcc_cur BULK COLLECT INTO l_bcc_rec;
1502 close bcc_cur;
1503
1504 FOR bcc_rec_loop in 1..l_bcc_rec.count /* l_bcc_rec.FIRST..l_bcc_rec.LAST in bcc_cur(l_proj_bc_enabled) loop */
1505 LOOP
1506 l_loop_ctr := l_loop_ctr + 1;
1507
1508 bcc_rec.source_project_id := l_bcc_rec(l_loop_ctr).source_project_id ;
1509 bcc_rec.source_org_id := l_bcc_rec(l_loop_ctr).source_org_id ;
1510 bcc_rec.source_attribute1 := l_bcc_rec(l_loop_ctr).source_attribute1 ;
1511 bcc_rec.source_attribute3 := l_bcc_rec(l_loop_ctr).source_attribute3 ;
1512 bcc_rec.source_attribute5 := l_bcc_rec(l_loop_ctr).source_attribute5 ;
1513 bcc_rec.source_attribute7 := l_bcc_rec(l_loop_ctr).source_attribute7 ;
1514 bcc_rec.source_attribute9 := l_bcc_rec(l_loop_ctr).source_attribute9 ;
1515 bcc_rec.source_task_id := l_bcc_rec(l_loop_ctr).source_task_id ;
1516 bcc_rec.source_pa_date := l_bcc_rec(l_loop_ctr).source_pa_date ;
1517 bcc_rec.source_attribute2 := l_bcc_rec(l_loop_ctr).source_attribute2 ;
1518 bcc_rec.source_attribute4 := l_bcc_rec(l_loop_ctr).source_attribute4 ;
1519 bcc_rec.source_attribute6 := l_bcc_rec(l_loop_ctr).source_attribute6 ;
1520 bcc_rec.source_attribute8 := l_bcc_rec(l_loop_ctr).source_attribute8 ;
1521 bcc_rec.source_attribute10 := l_bcc_rec(l_loop_ctr).source_attribute10 ;
1522 bcc_rec.source_attribute_category := l_bcc_rec(l_loop_ctr).source_attribute_category ;
1523 bcc_rec.source_ind_cost_code := l_bcc_rec(l_loop_ctr).source_ind_cost_code ;
1524 bcc_rec.source_expenditure_type := l_bcc_rec(l_loop_ctr).source_expenditure_type ;
1525 bcc_rec.source_ind_expenditure_type := l_bcc_rec(l_loop_ctr).source_ind_expenditure_type ;
1526 bcc_rec.source_cost_base := l_bcc_rec(l_loop_ctr).source_cost_base ;
1527 bcc_rec.source_compiled_multiplier := l_bcc_rec(l_loop_ctr).source_compiled_multiplier ;
1528
1529 bcc_rec.source_exp_item_id := l_bcc_rec(l_loop_ctr).source_exp_item_id ;
1530 bcc_rec.source_line_num := l_bcc_rec(l_loop_ctr).source_line_num ;
1531 bcc_rec.source_exp_item_date := l_bcc_rec(l_loop_ctr).source_exp_item_date ;
1532 bcc_rec.source_burden_cost := l_bcc_rec(l_loop_ctr).source_burden_cost ;
1533 bcc_rec.source_denom_burdened_cost := l_bcc_rec(l_loop_ctr).source_denom_burdened_cost ;
1534 bcc_rec.source_acct_burdened_cost := l_bcc_rec(l_loop_ctr).source_acct_burdened_cost ;
1535 bcc_rec.source_project_burdened_cost := l_bcc_rec(l_loop_ctr).source_project_burdened_cost ;
1536 bcc_rec.source_denom_currency_code := l_bcc_rec(l_loop_ctr).source_denom_currency_code ;
1537 bcc_rec.source_project_currency_code := l_bcc_rec(l_loop_ctr).source_project_currency_code ;
1538 bcc_rec.source_burden_reject_code := l_bcc_rec(l_loop_ctr).source_burden_reject_code ;
1539 bcc_rec.dest_pa_date := l_bcc_rec(l_loop_ctr).dest_pa_date ;
1540 bcc_rec.dest_ind_expenditure_type := l_bcc_rec(l_loop_ctr).dest_ind_expenditure_type ;
1541
1542 bcc_rec.source_incurred_by_person_Id := l_bcc_rec(l_loop_ctr).source_incurred_by_person_id ;
1543 bcc_rec.source_vendor_id := l_bcc_rec(l_loop_ctr).source_vendor_id;
1544
1545
1546 IF l_proj_bc_enabled = 'Y' THEN
1547 bcc_rec.dest_summary_group := l_bcc_rec(l_loop_ctr).dest_summary_group_Y;
1548 ELSE
1549 bcc_rec.dest_summary_group := l_bcc_rec(l_loop_ctr).dest_summary_group_N;
1550 END IF;
1551
1552 bcc_rec.source_projfunc_currency_code:= l_bcc_rec(l_loop_ctr).source_projfunc_currency_code;
1553 bcc_rec.source_acct_currency_code := l_bcc_rec(l_loop_ctr).source_acct_currency_code ;
1554 bcc_rec.source_id := l_bcc_rec(l_loop_ctr).source_id ;
1555 bcc_rec.dest_org_id := l_bcc_rec(l_loop_ctr).dest_org_id ;
1556 bcc_rec.dest_attribute1 := l_bcc_rec(l_loop_ctr).dest_attribute1 ;
1557 bcc_rec.billable_flag := l_bcc_rec(l_loop_ctr).billable_flag ;
1558 bcc_rec.source_request_id := l_bcc_rec(l_loop_ctr).source_request_id ;
1559 bcc_rec.cbs_element_id := l_bcc_rec(l_loop_ctr).cbs_element_id ; -- Added for CBS enahncement bug 16579913
1560
1561
1562 stage := 160; -- In bcc_cur cursor
1563
1564
1565 -- 5a. For each invokation create only one expenditure group.
1566
1567 IF P_DEBUG_MODE THEN
1568 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '900:Processing for:'||bcc_rec.dest_summary_group);
1569 END IF;
1570 if (create_exp_grp_flag ) then
1571 IF P_DEBUG_MODE THEN
1572 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '950:call to insertexpgroup');
1573 END IF;
1574 pa_transactions.InsertExpGroup(exp_group,'APPROVED',sysdate,'BTC',0,NULL,NULL,G_MOAC_ORG_ID);
1575 IF P_DEBUG_MODE THEN
1576 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1000:back from insertexpgroup');
1577 END IF;
1578 -- Handle error
1579 create_exp_grp_flag := FALSE;
1580 end if;
1581
1582 -- 5b. Create expenditures for every organization change if expenditure is not
1583 -- created for the organization during the run.
1584
1585 If P_BTC_SRC_RESRC = 'Y' then -- added for 4057874
1586
1587 pa_client_extn_burden_resource.client_column_values (
1588 p_job_id => l_job_id,
1589 p_non_labor_resource => l_nl_resource,
1590 p_non_labor_resource_orgn_id => l_nl_resource_orgn_id,
1591 p_wip_resource_id => l_wip_resource_id,
1592 p_incurred_by_person_id => bcc_rec.source_incurred_by_person_id,
1593 p_inventory_item_id => l_inventory_item_id,
1594 p_vendor_id => l_vendor_id,
1595 p_bom_labor_resource_id => l_bom_labor_resource_id,
1596 p_bom_equipment_resource_id => l_bom_equipment_resource_id);
1597 else
1598 bcc_rec.source_incurred_by_person_id := null;
1599
1600 end if; -- profile option
1601
1602 /* Introduced Caching for deriving weekend date for Bug# 5406802 */
1603 IF prev_bcc_rec.source_exp_item_date IS NULL THEN
1604 l_next_weekend_date := pa_utils.NewGetWeekEnding(bcc_rec.source_exp_item_date);
1605 ELSE
1606 l_prev_next_weekend_date := l_next_weekend_date;
1607 l_next_weekend_date := pa_utils.NewGetWeekEnding(bcc_rec.source_exp_item_date);
1608 END IF;
1609
1610 /* Changed the if condition for Bug# 5406802 */
1611 if ( bcc_rec.source_org_id = prev_bcc_rec.source_org_id
1612 -- Bug 3551106 : Added following And condition, new expenditure should be
1613 -- created whenever there is a chane in week ending date.
1614 and l_next_weekend_date
1615 = l_prev_next_weekend_date
1616 and nvl(bcc_rec.source_incurred_by_person_id,-99) = nvl(prev_bcc_rec.source_incurred_by_person_id,-99) -- 4057874
1617 and nvl(bcc_rec.source_vendor_id,-99) = nvl(prev_bcc_rec.source_vendor_id,-99) )then -- 4057874
1618
1619
1620 create_exp_flag := FALSE;
1621 else
1622 -- begining of a block to process items
1623 begin
1624 IF P_DEBUG_MODE THEN
1625 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1050:Get Expenditure Information');
1626 END IF;
1627 /*===================================================+
1628 | Bug 4115096 : Added person_id check to create new |
1629 | Expenditure for change in person. |
1630 +===================================================*/
1631
1632 /* Changed this query for Bug# 5406802 */
1633
1634 select expenditure_id,incurred_by_organization_id
1635 into exp_id,exp_org_id
1636 from pa_expenditures_all
1637 where expenditure_group = exp_group
1638 and incurred_by_organization_id = bcc_rec.source_org_id
1639 and NVL(incurred_by_person_id,-99) = nvl(bcc_rec.source_incurred_by_person_id,-99) -- changes done for 4324340 . Bug 4115096 and added NVL By 4282553
1640 and expenditure_ending_date = l_next_weekend_date -- Bug 3551106
1641 and nvl(vendor_id,-99) = nvl(bcc_rec.source_vendor_id,-99); -- Bug 6993002
1642
1643 IF P_DEBUG_MODE THEN
1644 pa_cc_utils.log_message('create_burden_expenditure_item: ' || 'See ->exp_id'||exp_id);
1645 END IF;
1646 exception
1647 when no_data_found then
1648 IF P_DEBUG_MODE THEN
1649 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1100:Get Expenditure Information:No_data_found');
1650 END IF;
1651 create_exp_flag := TRUE;
1652 exp_org_id := bcc_rec.source_org_id;
1653 end;
1654
1655 end if;
1656
1657 if (create_exp_flag ) then
1658 -- Get new expenditure id
1659 IF P_DEBUG_MODE THEN
1660 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1150:Get Expenditure Id from sequence');
1661 END IF;
1662 select pa_expenditures_s.nextval
1663 into exp_id from dual;
1664 stage := 170; -- Creating burden expenditure
1665
1666 l_curr_expenditure_id := exp_id ; -- Bug 3551106
1667
1668 -- Bug# 685104 -Creating Burden Cost Component by the period End date
1669
1670 /*
1671 Multi-Currency Related changes: Added additional parameters (denom currency code and acct currency code;
1672 all other currency attributes are set to null)
1673 */
1674 IF P_DEBUG_MODE THEN
1675 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1200:Call InsertExp');
1676 END IF;
1677 pa_transactions.InsertExp(
1678 x_expenditure_id =>exp_id,
1679 x_expend_status =>'APPROVED',
1680 x_expend_ending => pa_utils.NewGetWeekEnding((bcc_rec.source_exp_item_date)), --Bug 2236707,3551106
1681 -- x_expend_ending =>pa_utils.NewGetWeekEnding(pa_utils2.get_pa_period_end_date_OU(bcc_rec.source_pa_date)-6), -- Bug 2933915,3551106
1682 x_expend_class => 'BT',
1683 x_inc_by_person => bcc_rec.source_incurred_by_person_id, -- 4057874
1684 x_inc_by_org => bcc_rec.source_org_id,
1685 x_expend_group => exp_group,
1686 x_entered_by_id =>exp_org_id,
1687 x_created_by_id =>0,
1688 x_attribute_category => null,
1689 x_attribute1 => null,
1690 x_attribute2 => null,
1691 x_attribute3 => null,
1692 x_attribute4 => null,
1693 x_attribute5 => null,
1694 x_attribute6 => null,
1695 x_attribute7 => null,
1696 x_attribute8 => null,
1697 x_attribute9 => null,
1698 x_attribute10=> null,
1699 x_description=> null,
1700 x_control_total=> null,
1701 x_denom_currency_code =>bcc_rec.source_denom_currency_code,
1702 x_acct_currency_code => bcc_rec.source_acct_currency_code,
1703 x_acct_rate_type => null,
1704 x_acct_rate_date => null,
1705 x_acct_exchange_rate=> null
1706 ,X_person_type => bcc_rec.source_person_type
1707 ,X_vendor_id => bcc_rec.source_vendor_id -- 4057874
1708 ,P_Org_Id => G_MOAC_ORG_ID
1709 );
1710
1711 IF P_DEBUG_MODE THEN
1712 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1250:Back from InsertExp');
1713 END IF;
1714 -- Setting the flag to false to avoid unnecessary insertion of the records
1715 -- 09/11/98
1716 create_exp_flag := FALSE;
1717 -- Added else part as part of the Bug 13093735
1718 else
1719 l_curr_expenditure_id := exp_id ;
1720 end if;
1721
1722 -- 5c. Create expenditure item for every dest_summary_group change
1723
1724 if bcc_rec.dest_summary_group = prev_bcc_rec.dest_summary_group and NVL(l_prev_expenditure_id,-1) = NVL(l_curr_expenditure_id,-1) then -- Bug 3551106
1725 IF P_DEBUG_MODE THEN
1726 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1300:No change in Summary Group ..');
1727 END IF;
1728 l_burden_cost := l_burden_cost + bcc_rec.source_burden_cost;
1729
1730 /*
1731 Multi-Currency Related changes: Sum up burdened_cost in denom and Acct currencies also
1732 */
1733 l_denom_burdened_cost := l_denom_burdened_cost + bcc_rec.source_denom_burdened_cost;
1734 l_acct_burdened_cost := l_acct_burdened_cost + bcc_rec.source_acct_burdened_cost;
1735 l_project_burdened_cost := l_project_burdened_cost + bcc_rec.source_project_burdened_cost;
1736
1737 /****Bug# 3611675 :Commenting this as it is a redundant code now ***
1738
1739 -- Check for other attributes of summarized burden expenditure item
1740 if nvl(bcc_rec.source_attribute1,'X') <> nvl(prev_bcc_rec.source_attribute1,'X') then
1741 l_attribute1 := ''; -- nullify the attribute1 column
1742 else
1743 l_attribute1 := prev_bcc_rec.source_attribute1;
1744 end if;
1745 ******/
1746 else
1747 IF P_DEBUG_MODE THEN
1748 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1350:Change in Summary Group ..');
1749 END IF;
1750
1751 -- Create new summarization expenditure item
1752 -- Get new expenditure item id
1753 -- Checking for l_burden_cost <> 0 rather than l_burden_cost > 0
1754 -- to handle split/transfer cases (Shree)
1755 /* Bug # 697690 -- Initializing, for insterting first Exp.item */
1756
1757 -- l_burden_cost := bcc_rec.source_burden_cost;
1758 -- prev_bcc_rec.source_exp_item_date := bcc_rec.source_exp_item_date ;
1759 -- prev_bcc_rec.source_ind_expenditure_type := bcc_rec.source_ind_expenditure_type ;
1760
1761 /*
1762 Multi-Currency Related changes:
1763 check is based on denom_burdened_cost and not burden_Cost
1764 */
1765
1766 /*
1767 * Bug 2359625
1768 * The BTC EI is to be created, even if one of the amount buckets has a non-zero value.
1769 * if l_denom_burdened_cost <> 0 then
1770 */
1771 --Bug 4444387: Added l_project_burdened_cost <> 0
1772 /* Bug#54065802 */
1773 l_denom_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_denom_burdened_cost, bcc_rec.source_denom_currency_code);
1774 l_acct_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_acct_burdened_cost, bcc_rec.source_acct_currency_code);
1775 l_burden_cost :=PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_burden_cost, bcc_rec.source_projfunc_currency_code);
1776 l_project_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_project_burdened_cost, bcc_rec.source_project_currency_code);
1777 /* Bug#54065802 */
1778
1779 if ( l_denom_burdened_cost <> 0 OR l_acct_burdened_cost <> 0 OR l_burden_cost <> 0 OR l_project_burdened_cost <> 0 )
1780 then
1781 begin
1782 IF P_DEBUG_MODE THEN
1783 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1400:Get Expenditure Item Id from Sequence');
1784 END IF;
1785 select pa_expenditure_items_s.nextval
1786 into exp_item_id from dual;
1787 i := i +1;
1788
1789
1790
1791 -- Make sure that burden amount will be displayed as a separate item on same
1792 -- project and task. Check to see if over_task_id is null and
1793 -- dest_task_id is null, if it is then assign source_task_id to over_task_id
1794 --
1795 if (over_task_id is NULL AND proj_rec.burden_amt_display_method='D')
1796 then
1797 -- if bcc_rec.dest_task_id is NULL then -- commented for bug 3069632
1798 if proj_rec.dest_task_id is NULL then
1799 IF P_DEBUG_MODE THEN
1800 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1450:No Dest Override');
1801 END IF;
1802 over_task_id := bcc_rec.source_task_id;
1803 over_project_id := bcc_rec.source_project_id;
1804 else -- destination override was provided by user
1805 IF P_DEBUG_MODE THEN
1806 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1500:Dest Override');
1807 END IF;
1808 /*3069632 :Commented ************************
1809 over_task_id := bcc_rec.dest_task_id;
1810 over_project_id := bcc_rec.dest_project_id;
1811 *********************************************/
1812 /*3069632 :Added this */
1813 over_task_id := proj_rec.dest_task_id;
1814 over_project_id := proj_rec.dest_project_id;
1815 end if;
1816 end if;
1817
1818 /* Moved the derivation of work type id to here for bug 2607781 as it needs to be passed to pa_transactions_pub.validate_transaction */
1819 IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
1820 THEN
1821 IF P_DEBUG_MODE THEN
1822 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1510:Calling Get_work_type_id with Project_id [' ||
1823 to_char(over_project_id) ||
1824 '] task_id [' || to_char(over_task_id) || ']');
1825 END IF;
1826
1827 l_work_type_id := pa_utils4.Get_work_type_id
1828 ( p_project_id => over_project_id
1829 ,p_task_id => over_task_id
1830 ,p_assignment_id => NULL
1831 );
1832 IF P_DEBUG_MODE THEN
1833 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1520:Obtained work_type_id [' || to_char(l_work_type_id) || ']');
1834 END IF;
1835 END IF; -- 2607781
1836
1837 -- The following section of the code was added by Sandeep. It gets
1838 -- the billable_flag value from patc.get_status API. The API is called
1839 -- only if either task_id, project_id or expenditure_type changes.
1840 -- patc.get_status will return the billable_flag which will determine the billability of
1841 -- newly created burden summarized transaction.
1842 -- Ref Bug # : 609978
1843 --
1844 if ((nvl(c_task_id, -999999) <> over_task_id ) or
1845 (nvl(c_project_id, -999999) <> over_project_id) or
1846 (nvl(c_expenditure_type, '') <> prev_bcc_rec.source_ind_expenditure_type)) then
1847 /*
1848 Multi-Curr Changes. Chnaged patc.get_status to
1849 pa_transactions_pub.validate_transaction. Also passing null's for rate
1850 attributes, the raw cost for BTC transactions is 0. The burdened costs are
1851 calculated using existing CDL's which are already converted.
1852 */
1853
1854 IF P_DEBUG_MODE THEN
1855 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1550:Call PATC for project,task or exp type change');
1856 END IF;
1857 /* modified for the Bug#1825827 */
1858 pa_transactions_pub.validate_transaction(
1859 x_project_id =>over_project_id,
1860 x_task_id =>over_task_id,
1861 x_ei_date =>prev_bcc_rec.source_exp_item_date,
1862 x_expenditure_type =>prev_bcc_rec.source_ind_expenditure_type,
1863 x_non_labor_resource =>NULL,
1864 x_person_id => NULL,
1865 x_quantity =>NULL,
1866 x_denom_currency_code =>prev_bcc_rec.source_denom_currency_code,
1867 x_acct_currency_code =>prev_bcc_rec.source_acct_currency_code,
1868 x_denom_raw_cost => 0,
1869 x_acct_raw_cost => 0,
1870 x_acct_rate_type => NULL,
1871 x_acct_rate_date => NULL,
1872 x_acct_exchange_rate => NULL,
1873 x_transfer_ei =>NULL,
1874 x_incurred_by_org_id =>prev_bcc_rec.source_org_id,
1875 x_nl_resource_org_id =>NULL,
1876 x_transaction_source =>NULL,
1877 x_calling_module =>NULL,
1878 x_vendor_id =>NULL,
1879 x_entered_by_user_id =>NULL,
1880 x_attribute_category =>NULL,
1881 x_attribute1 =>NULL,
1882 x_attribute2 =>NULL,
1883 x_attribute3 =>NULL,
1884 x_attribute4 =>NULL,
1885 x_attribute5 =>NULL,
1886 x_attribute6 =>NULL,
1887 x_attribute7 =>NULL,
1888 x_attribute8 =>NULL,
1889 x_attribute9 =>NULL,
1890 x_attribute10 =>NULL,
1891 x_attribute11 =>NULL,
1892 x_attribute12 =>NULL,
1893 x_attribute13 =>NULL,
1894 x_attribute14 =>NULL,
1895 x_attribute15 =>NULL,
1896 x_msg_application =>c_msg_application,
1897 x_msg_type =>c_msg_type,
1898 x_msg_token1 =>c_msg_token1,
1899 x_msg_token2 =>c_msg_token2,
1900 x_msg_token3 =>c_msg_token3,
1901 x_msg_count =>c_msg_count,
1902 x_msg_data =>c_status,
1903 x_billable_flag =>c_billable_flag,
1904 p_sys_link_function => 'BTC',
1905 p_work_type_id => l_work_type_id -- 2607781
1906 );
1907 IF P_DEBUG_MODE THEN
1908 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1600:Back from PATC call');
1909 END IF;
1910 if c_status is not null then
1911 null ;
1912 end if;
1913
1914 c_task_id := over_task_id;
1915 c_project_id := over_project_id;
1916 c_expenditure_type := prev_bcc_rec.source_ind_expenditure_type ;
1917 /*Bug# 2091559 Added followinf If Clause*/
1918 if nvl(prev_bcc_rec.billable_flag,bcc_rec.billable_flag) = 'N' then
1919 c_billable_flag :='N' ;
1920 end if;
1921 end if;
1922 -- **********************************
1923 --dbms_output.put_line('Items will be created on task: '|| to_char(over_task_id));
1924 stage := 180; -- Loading EI details
1925 /*
1926 Multi-Currency Related changes:
1927 Added additional parameters
1928 (denom currency code ,acct currency code, project currency code;
1929 all other currency attributes are set to null)
1930 */
1931
1932 -- Passed the value for parameter x_labor_cost_multiplier_name
1933 -- which is a new parameter created for bug 791759
1934
1935 -- IC Changes
1936 -- BTC txns should not be cross charged, so setting cross charge code to X.
1937
1938 /*
1939 * AddEi Attributes - related change.
1940 * Deriving work_type_id.
1941 */
1942 /* Commenting this for bug 2607781 as work type id needs to be derived before the call to pa_transactions_pub.validate_transaction
1943 IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
1944 THEN
1945 IF P_DEBUG_MODE THEN
1946 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1645:Calling Get_work_type_id with Project_id [' ||
1947 to_char(over_project_id) ||
1948 '] task_id [' || to_char(over_task_id) || ']');
1949 END IF;
1950
1951 l_work_type_id := pa_utils4.Get_work_type_id
1952 ( p_project_id => over_project_id
1953 ,p_task_id => over_task_id
1954 ,p_assignment_id => NULL
1955 );
1956 IF P_DEBUG_MODE THEN
1957 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1645:Obtained work_type_id [' || to_char(l_work_type_id) || ']');
1958 END IF;
1959 END IF; */
1960
1961 /*
1962 * CRL Related Changes
1963 * Passing the values for attribute2 - attribute10 and
1964 * attribute_category retrived from previous function call
1965 */
1966
1967 /*
1968 * Bug#2255068
1969 * Changed the variable passed for incurred_by_organization_id. Passed
1970 * prev_bcc_rec.source_org_id - because exp_org_id actually holds the
1971 * organization_id of the next grouping.
1972 */
1973 IF P_DEBUG_MODE THEN
1974 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1650:Call Loadei');
1975 END IF;
1976
1977 pa_transactions.LoadEi(
1978 x_expenditure_item_id =>exp_item_id,
1979 x_expenditure_id =>x_exp_id,
1980 x_expenditure_item_date
1981 =>prev_bcc_rec.source_exp_item_date ,
1982 -- for 1664962 pa_utils.GetWeekEnding((prev_bcc_rec.source_pa_date)-6),
1983 x_project_id => over_project_id, -- NULL, bugfix: 2201207
1984 x_task_id =>over_task_id ,
1985 x_expenditure_type =>prev_bcc_rec.source_ind_expenditure_type,
1986 x_non_labor_resource =>prev_bcc_rec.source_nl_resource, -- 4057874
1987 x_nl_resource_org_id =>prev_bcc_rec.source_nl_resource_orgn_id , -- 4057874
1988 x_quantity =>0,
1989 x_raw_cost =>0 ,
1990 x_raw_cost_rate =>0,
1991 /* x_override_to_org_id =>exp_org_id -Bug# 2255068*/
1992 x_override_to_org_id =>prev_bcc_rec.source_org_id,
1993 x_billable_flag =>c_billable_flag, /*Bug4643188:Reverted Bug#2840048 */
1994 x_bill_hold_flag =>'N',
1995 x_orig_transaction_ref =>NULL ,
1996 x_transferred_from_ei =>NULL ,
1997 x_adj_expend_item_id =>NULL,
1998 x_attribute_category =>prev_bcc_rec.source_attribute_category ,
1999 x_attribute1 =>prev_bcc_rec.source_attribute1 , /*Bug# 3611675 Replaced l_attribute1*/
2000 x_attribute2 =>prev_bcc_rec.source_attribute2 ,
2001 x_attribute3 =>prev_bcc_rec.source_attribute3 ,
2002 x_attribute4 =>prev_bcc_rec.source_attribute4 ,
2003 x_attribute5 =>prev_bcc_rec.source_attribute5 ,
2004 x_attribute6 =>prev_bcc_rec.source_attribute6 ,
2005 x_attribute7 =>prev_bcc_rec.source_attribute7 ,
2006 x_attribute8 =>prev_bcc_rec.source_attribute8 ,
2007 x_attribute9 =>prev_bcc_rec.source_attribute9 ,
2008 x_attribute10 =>prev_bcc_rec.source_attribute10 ,
2009 x_ei_comment =>NULL ,
2010 x_transaction_source =>NULL ,
2011 x_source_exp_item_id =>NULL ,
2012 i => i ,
2013 x_job_id =>prev_bcc_rec.source_job_id , -- 4057874
2014 x_org_id =>G_MOAC_ORG_ID ,
2015 x_labor_cost_multiplier_name => NULL,
2016 x_drccid =>NULL ,
2017 x_crccid =>NULL ,
2018 x_cdlsr1 =>NULL ,
2019 x_cdlsr2 =>NULL ,
2020 x_cdlsr3 =>NULL ,
2021 x_gldate =>NULL ,
2022 x_bcost =>l_burden_cost ,
2023 x_bcostrate =>NULL ,
2024 x_etypeclass => 'BTC',
2025 x_burden_sum_dest_run_id =>current_run_id,
2026 x_burden_compile_set_id =>null,
2027 x_receipt_currency_amount =>null,
2028 x_receipt_currency_code =>null,
2029 x_receipt_exchange_rate =>null,
2030 x_denom_currency_code =>prev_bcc_rec.source_denom_currency_code,
2031 x_denom_raw_cost =>null,
2032 x_denom_burdened_cost =>l_denom_burdened_cost,
2033 x_acct_currency_code =>prev_bcc_rec.source_acct_currency_code,
2034 x_acct_rate_date =>prev_bcc_rec.src_acct_rate_date,
2035 x_acct_rate_type =>prev_bcc_rec.src_acct_rate_type,
2036 x_acct_exchange_rate =>prev_bcc_rec.src_acct_exchange_rate,
2037 x_acct_raw_cost =>null,
2038 x_acct_burdened_cost =>l_acct_burdened_cost,
2039 x_acct_exchange_rounding_limit =>null,
2040 x_project_currency_code =>prev_bcc_rec.source_project_currency_code,
2041 x_project_rate_date =>prev_bcc_rec.src_project_rate_date,
2042 x_project_rate_type =>prev_bcc_rec.src_project_rate_type,
2043 x_project_exchange_rate =>prev_bcc_rec.src_project_exchange_rate,
2044 p_project_raw_cost =>null,
2045 p_project_burdened_cost =>l_project_burdened_cost,
2046 p_projfunc_currency_code => prev_bcc_rec.source_projfunc_currency_code,
2047 p_projfunc_cost_rate_date => prev_bcc_rec.src_projfunc_cost_rate_date,
2048 p_projfunc_cost_rate_type => prev_bcc_rec.src_projfunc_cost_rate_type,
2049 p_projfunc_cost_exchange_rate => prev_bcc_rec.src_projfunc_cost_xchng_rate,
2050 p_work_type_id => l_work_type_id,
2051 X_Cross_Charge_Code => 'X',
2052 x_recv_operating_unit => proj_rec.org_id
2053 ,p_Po_Line_Id => prev_bcc_rec.source_po_line_id
2054 ,p_adjustment_type => prev_bcc_rec.source_adjustment_type
2055 ,p_Wip_Resource_Id => prev_bcc_rec.source_wip_resource_id -- 4057874
2056 ,p_Inventory_Item_Id => prev_bcc_rec.source_inventory_item_id -- 4057874
2057 ,p_src_system_linkage_function => prev_bcc_rec.source_system_linkage_function -- 4057874
2058 ,p_vendor_id => prev_bcc_rec.source_vendor_id -- Bug 6993002
2059 ,p_cbs_element_id => prev_bcc_rec.cbs_element_id -- Added for CBS enahncement bug 16579913
2060 );
2061 IF P_DEBUG_MODE THEN
2062 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1700:Back from Loadei');
2063 END IF;
2064
2065
2066 -- Frequently create EI and flush EiTabs to reduce load on LoadEi
2067 if ( i >= 500) then
2068 IF P_DEBUG_MODE THEN
2069 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1750:Call Insitems for i>=500');
2070 END IF;
2071 pa_transactions.InsItems(1,0,'BTC','BTC',i, status,'N');
2072
2073 -- -----------------------------------------------------------------------
2074 -- OGM_0.0 - Interface for creating new ADLS for each expenditure Item
2075 -- created. This will create award distribution lines only when OGM is
2076 -- installed for the ORG in process.
2077 -- The folowing procedure returns doing nothing if status returned from
2078 -- pa_transactions.InsItemsis is in error.
2079 -- ------------------------------------------------------------------------
2080 IF P_DEBUG_MODE THEN
2081 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1760:Call Vertical APPS interface for i>=500');
2082 END IF;
2083 PA_GMS_API.vert_trx_interface(0,0,'PAXCBCAB', 'PA_BURDEN_COSTING', i, status, 'N') ;
2084 IF P_DEBUG_MODE THEN
2085 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1760:Call Vertical APPS interface for i>=500 END.');
2086 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1800:Call FlushEiTabs');
2087 END IF;
2088 pa_transactions.FlushEiTabs();
2089 IF P_DEBUG_MODE THEN
2090 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1850:Back from FlushEiTabs');
2091 END IF;
2092 i := 0;
2093 end if;
2094 -- end of a block to process items
2095 end;
2096 end if;
2097 l_burden_cost := bcc_rec.source_burden_cost;
2098
2099 /***Bug#3611675 :Commented this as it ia redundant code
2100 --
2101 -- Bug 907767: Attribute1 was not being set. Added following statement
2102 --
2103 l_attribute1 := bcc_rec.source_attribute1;
2104
2105 ******/
2106
2107 /*
2108 Multi-Currency Related changes:
2109 Store denom_burdened_cost and acct_burdened_cost also.
2110 */
2111 l_denom_burdened_cost := bcc_rec.source_denom_burdened_cost;
2112 l_acct_burdened_cost := bcc_rec.source_acct_burdened_cost;
2113 l_project_burdened_cost := bcc_rec.source_project_burdened_cost;
2114 end if;
2115
2116 -- Set the project and task id of the burden expenditure item depending
2117 -- on burden_accounting flag
2118
2119 -- Make sure that burden amount will be displayed as a separate item on same
2120 -- project and task. Check to see if over_task_id is null and
2121 -- dest_task_id is null, if it is then assign source_task_id to over_task_id
2122 --
2123 if (proj_rec.burden_amt_display_method='D')
2124 then
2125 ----- if bcc_rec.dest_task_id is NULL then
2126 if proj_rec.dest_task_id is NULL then
2127 IF P_DEBUG_MODE THEN
2128 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1900:Display method D and dest task id null');
2129 END IF;
2130 over_task_id := bcc_rec.source_task_id;
2131 over_project_id := bcc_rec.source_project_id;
2132 else -- destination override was provided by user
2133 IF P_DEBUG_MODE THEN
2134 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1950:Display method D and dest task id provided');
2135 END IF;
2136 /*3069632 :Commented this
2137 over_task_id := bcc_rec.dest_task_id;
2138 over_project_id := bcc_rec.dest_project_id;
2139 ********************************************/
2140 /*3069632 :Added this */
2141 over_task_id := proj_rec.dest_task_id;
2142 over_project_id := proj_rec.dest_project_id;
2143
2144 end if;
2145 end if;
2146 if (proj_rec.burden_amt_display_method='S') then
2147 IF P_DEBUG_MODE THEN
2148 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2000:Display method S');
2149 END IF;
2150
2151 /*3069632 :Commented this
2152 over_task_id := bcc_rec.dest_task_id;
2153 over_project_id := bcc_rec.dest_project_id;
2154 ********************************************/
2155 /*3069632 :Added this */
2156 over_task_id := proj_rec.dest_task_id;
2157 over_project_id := proj_rec.dest_project_id;
2158 end if;
2159
2160 -- Set the previous bcc record details
2161 prev_bcc_rec.source_project_id := bcc_rec.source_project_id ;
2162 prev_bcc_rec.source_task_id := bcc_rec.source_task_id ;
2163 prev_bcc_rec.source_org_id := bcc_rec.source_org_id ;
2164 prev_bcc_rec.source_pa_date := bcc_rec.source_pa_date ;
2165 prev_bcc_rec.source_attribute1 := bcc_rec.source_attribute1 ;
2166 prev_bcc_rec.source_attribute2 := bcc_rec.source_attribute2 ;
2167 prev_bcc_rec.source_attribute3 := bcc_rec.source_attribute3 ;
2168 prev_bcc_rec.source_attribute4 := bcc_rec.source_attribute4 ;
2169 prev_bcc_rec.source_attribute5 := bcc_rec.source_attribute5 ;
2170 prev_bcc_rec.source_attribute6 := bcc_rec.source_attribute6 ;
2171 prev_bcc_rec.source_attribute7 := bcc_rec.source_attribute7 ;
2172 prev_bcc_rec.source_attribute8 := bcc_rec.source_attribute8 ;
2173 prev_bcc_rec.source_attribute9 := bcc_rec.source_attribute9 ;
2174 prev_bcc_rec.source_attribute10 := bcc_rec.source_attribute10 ;
2175 prev_bcc_rec.source_ind_cost_code := bcc_rec.source_ind_cost_code ;
2176 prev_bcc_rec.source_expenditure_type := bcc_rec.source_expenditure_type ;
2177 prev_bcc_rec.source_ind_expenditure_type := bcc_rec.source_ind_expenditure_type ;
2178 prev_bcc_rec.source_cost_base := bcc_rec.source_cost_base ;
2179 prev_bcc_rec.source_compiled_multiplier := bcc_rec.source_compiled_multiplier ;
2180 -- prev_bcc_rec.source_ind_rate_sch_id := bcc_rec.source_ind_rate_sch_id ;
2181 -- prev_bcc_rec.source_ind_rate_sch_rev_id := bcc_rec.source_ind_rate_sch_rev_id ;
2182 prev_bcc_rec.source_exp_item_id := bcc_rec.source_exp_item_id ;
2183 prev_bcc_rec.source_line_num := bcc_rec.source_line_num ;
2184 prev_bcc_rec.source_exp_item_date := bcc_rec.source_exp_item_date ;
2185 prev_bcc_rec.source_burden_cost := bcc_rec.source_burden_cost ;
2186 prev_bcc_rec.source_id := bcc_rec.source_id ;
2187 prev_bcc_rec.source_burden_reject_code := bcc_rec.source_burden_reject_code ;
2188 /*3069632 Commented as it seems these variables are used no where.
2189 prev_bcc_rec.dest_project_id := bcc_rec.dest_project_id ;
2190 prev_bcc_rec.dest_task_id := bcc_rec.dest_task_id ;
2191 ************************/
2192 prev_bcc_rec.dest_org_id := bcc_rec.dest_org_id ;
2193 prev_bcc_rec.dest_pa_date := bcc_rec.dest_pa_date ;
2194 prev_bcc_rec.dest_attribute1 := bcc_rec.dest_attribute1 ;
2195 prev_bcc_rec.dest_ind_expenditure_type := bcc_rec.dest_ind_expenditure_type ;
2196 prev_bcc_rec.dest_summary_group := bcc_rec.dest_summary_group ;
2197 prev_bcc_rec.billable_flag := bcc_rec.billable_flag ;/*2091559*/
2198 l_prev_expenditure_id := l_curr_expenditure_id ; -- Bug 3551106
2199
2200 /*
2201 Multi-Currency Related changes:
2202 Copy the currency codes
2203 */
2204 prev_bcc_rec.source_denom_currency_code :=bcc_rec.source_denom_currency_code;
2205 prev_bcc_rec.source_acct_currency_code :=bcc_rec.source_acct_currency_code;
2206 prev_bcc_rec.source_project_currency_code :=bcc_rec.source_project_currency_code;
2207 prev_bcc_rec.source_projfunc_currency_code :=bcc_rec.source_projfunc_currency_code;
2208 prev_bcc_rec.source_po_line_id :=bcc_rec.source_po_line_id;
2209 prev_bcc_rec.source_adjustment_type :=bcc_rec.source_adjustment_type;
2210 x_exp_id := exp_id;
2211
2212 /* 4057874 */
2213 prev_bcc_rec.source_job_id := bcc_rec.source_job_id;
2214 prev_bcc_rec.source_nl_resource := bcc_rec.source_nl_resource;
2215 prev_bcc_rec.source_nl_resource_orgn_id := bcc_rec.source_nl_resource_orgn_id;
2216 prev_bcc_rec.source_wip_resource_id := bcc_rec.source_wip_resource_id;
2217 prev_bcc_rec.source_incurred_by_person_id := bcc_rec.source_incurred_by_person_id;
2218 prev_bcc_rec.source_inventory_item_id := bcc_rec.source_inventory_item_id;
2219 prev_bcc_rec.source_system_linkage_function := bcc_rec.source_system_linkage_function;
2220 prev_bcc_rec.source_vendor_id := bcc_rec.source_vendor_id;
2221
2222 prev_bcc_rec.src_acct_rate_date := bcc_rec.src_acct_rate_date;
2223 prev_bcc_rec.src_acct_rate_type := bcc_rec.src_acct_rate_type;
2224 prev_bcc_rec.src_acct_exchange_rate := bcc_rec.src_acct_exchange_rate;
2225 prev_bcc_rec.src_project_rate_date := bcc_rec.src_project_rate_date;
2226 prev_bcc_rec.src_project_rate_type := bcc_rec.src_project_rate_type;
2227 prev_bcc_rec.src_project_exchange_rate := bcc_rec.src_project_exchange_rate;
2228 prev_bcc_rec.src_projfunc_cost_rate_date := bcc_rec.src_projfunc_cost_rate_date;
2229 prev_bcc_rec.src_projfunc_cost_rate_type := bcc_rec.src_projfunc_cost_rate_type;
2230 prev_bcc_rec.src_projfunc_cost_xchng_rate := bcc_rec.src_projfunc_cost_xchng_rate;
2231 prev_bcc_rec.cbs_element_id := bcc_rec.cbs_element_id; -- Added for CBS enahncement bug 16579913
2232
2233 /* bug fix 4091690 starts */
2234
2235 IF P_DEBUG_MODE THEN
2236 pa_cc_utils.log_message('create_burden_expenditure_item: ' ||
2237 '1625:Call pa_client_extn_burden_summary grouping.CLIENT_column_values');
2238 END IF;
2239
2240 /*
2241 * CRL Related Changes
2242 * Included the following Function call get values for attribute2 - attribute10 and
2243 * attribute_category
2244 */
2245 pa_client_extn_burden_summary.CLIENT_COLUMN_VALUES
2246 (
2247 p_src_attribute2 => prev_bcc_rec.source_attribute2,
2248 p_src_attribute3 => prev_bcc_rec.source_attribute3,
2249 p_src_attribute4 => prev_bcc_rec.source_attribute4,
2250 p_src_attribute5 => prev_bcc_rec.source_attribute5,
2251 p_src_attribute6 => prev_bcc_rec.source_attribute6,
2252 p_src_attribute7 => prev_bcc_rec.source_attribute7,
2253 p_src_attribute8 => prev_bcc_rec.source_attribute8,
2254 p_src_attribute9 => prev_bcc_rec.source_attribute9,
2255 p_src_attribute10 => prev_bcc_rec.source_attribute10,
2256 p_src_attribute_category => prev_bcc_rec.source_attribute_category
2257 ,p_src_acct_rate_date => prev_bcc_rec.src_acct_rate_date
2258 ,p_src_acct_rate_type => prev_bcc_rec.src_acct_rate_type
2259 ,p_src_acct_exchange_rate => prev_bcc_rec.src_acct_exchange_rate
2260 ,p_src_project_rate_date => prev_bcc_rec.src_project_rate_date
2261 ,p_src_project_rate_type => prev_bcc_rec.src_project_rate_type
2262 ,p_src_project_exchange_rate => prev_bcc_rec.src_project_exchange_rate
2263 ,p_src_projfunc_cost_rate_date => prev_bcc_rec.src_projfunc_cost_rate_date
2264 ,p_src_projfunc_cost_rate_type => prev_bcc_rec.src_projfunc_cost_rate_type
2265 ,p_src_projfunc_cost_xchng_rate => prev_bcc_rec.src_projfunc_cost_xchng_rate
2266 );
2267
2268 If P_BTC_SRC_RESRC = 'Y' then -- 4057874
2269
2270 IF P_DEBUG_MODE THEN
2271 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1625:Call pa_client_extn_burden_resource.client_column_values');
2272 END IF;
2273
2274 pa_client_extn_burden_resource.client_column_values (
2275 p_job_id => prev_bcc_rec.source_job_id,
2276 p_non_labor_resource => prev_bcc_rec.source_nl_resource,
2277 p_non_labor_resource_orgn_id => prev_bcc_rec.source_nl_resource_orgn_id,
2278 p_wip_resource_id => prev_bcc_rec.source_wip_resource_id,
2279 p_incurred_by_person_id => l_incurred_by_person_id,
2280 p_inventory_item_id => prev_bcc_rec.source_inventory_item_id,
2281 p_vendor_id => l_vendor_id,
2282 p_bom_labor_resource_id => l_bom_labor_resource_id,
2283 p_bom_equipment_resource_id => l_bom_equipment_resource_id);
2284 else
2285 prev_bcc_rec.source_job_id := null;
2286 prev_bcc_rec.source_nl_resource := null;
2287 prev_bcc_rec.source_nl_resource_orgn_id := null;
2288 prev_bcc_rec.source_wip_resource_id := null;
2289 prev_bcc_rec.source_inventory_item_id := null;
2290
2291 -- Bug 4323236 : The source_system_linkage_function parameter will be populated always for BTC as this
2292 -- can be used to uniquely identify all the source exp's associated with the BTC in pa_res_map_btc_v.
2293 -- Note : Populating this column value always for a BTC will have no issues as system linkage function
2294 -- is part of grouping criteria ,hence each BTC line will have unique source system linkage function.
2295 --prev_bcc_rec.source_system_linkage_function := null;
2296
2297 end if; -- profile option
2298
2299 /* bug fix 4091690 */
2300
2301 IF P_DEBUG_MODE THEN
2302 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2050:End of bcc rec loop');
2303 END IF;
2304 end loop;
2305 end;
2306
2307
2308 /* create EI for the last set */
2309 -- Checking for l_burden_cost <> 0 rather than l_burden_cost > 0
2310 -- to handle split/transfer cases (Shree)
2311 /*
2312 Multi-Currency Related changes:
2313 check is based on denom_burdened_cost rather than burden_cost.
2314 */
2315
2316 /*
2317 * Bug 2359625
2318 * The BTC EI is to be created, even if one of the amount buckets has a non-zero value.
2319 * if l_denom_burdened_cost <> 0 then
2320 */
2321 --Bug 4444387: Added l_project_burdened_cost <> 0
2322
2323 /* Bug#54065802 */
2324 if ( l_bcc_rec.count > 0) then /* Added for bug#6035619 */
2325
2326 l_denom_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_denom_burdened_cost, bcc_rec.source_denom_currency_code);
2327 l_acct_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_acct_burdened_cost, bcc_rec.source_acct_currency_code);
2328 l_burden_cost :=PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_burden_cost, bcc_rec.source_projfunc_currency_code);
2329 l_project_burdened_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_project_burdened_cost, bcc_rec.source_project_currency_code);
2330
2331 /* Bug#54065802 */
2332 end if; /* Added for bug#6035619 */
2333 if ( l_denom_burdened_cost <> 0 OR l_acct_burdened_cost <> 0 OR l_burden_cost <> 0 OR l_project_burdened_cost <> 0 )
2334 then
2335 begin
2336
2337 IF P_DEBUG_MODE THEN
2338 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2100:Process Last set .. Get Exp item id from sequence');
2339 END IF;
2340 select pa_expenditure_items_s.nextval
2341 into exp_item_id from dual;
2342
2343 /* Moved the derivation of work type id to here for bug 2607781 as it needs to be passed to pa_transactions_pub.validate_transaction */
2344 IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
2345 THEN
2346 IF P_DEBUG_MODE THEN
2347 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2110:Calling Get_work_type_id with Project_id [' ||
2348 to_char(over_project_id) ||
2349 '] task_id [' || to_char(over_task_id) || ']');
2350 END IF;
2351 l_work_type_id := pa_utils4.Get_work_type_id
2352 ( p_project_id => over_project_id
2353 ,p_task_id => over_task_id
2354 ,p_assignment_id => NULL
2355 );
2356 IF P_DEBUG_MODE THEN
2357 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2120:Obtained work_type_id [' || to_char(l_work_type_id) || ']');
2358 END IF;
2359 END IF; -- 2607781
2360
2361
2362 -- The following section of the code was added by Sandeep. It gets
2363 -- the billable_flag value from patc.get_status API. The API is called
2364 -- only if either task_id, project_id or expenditure_type changes.
2365 -- patc.get_status will return the billable_flag which will determine the billability of
2366 -- newly created burden summarized transaction.
2367 -- Ref Bug # : 609978
2368 --
2369 /*
2370 Multi-Curr Changes. Changed patc.get_status to
2371 pa_transactions_pub.validate_transaction. Also passing null's for rate
2372 attributes, the raw cost for BTC transactions is 0. The burdened costs are
2373 calculated using existing CDL's which are already converted.
2374 */
2375
2376 IF P_DEBUG_MODE THEN
2377 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2150:Process Last Set .. call PATC');
2378 END IF;
2379 /* modified for the Bug#1825827 */
2380 pa_transactions_pub.validate_transaction(
2381 x_project_id =>over_project_id,
2382 x_task_id =>over_task_id,
2383 x_ei_date =>prev_bcc_rec.source_exp_item_date,
2384 x_expenditure_type =>prev_bcc_rec.source_ind_expenditure_type,
2385 x_non_labor_resource =>NULL,
2386 x_person_id => NULL,
2387 x_quantity =>NULL,
2388 x_denom_currency_code =>prev_bcc_rec.source_denom_currency_code,
2389 x_acct_currency_code =>prev_bcc_rec.source_acct_currency_code,
2390 x_denom_raw_cost => 0,
2391 x_acct_raw_cost => 0,
2392 x_acct_rate_type => NULL,
2393 x_acct_rate_date => NULL,
2394 x_acct_exchange_rate => NULL,
2395 x_transfer_ei =>NULL,
2396 x_incurred_by_org_id =>prev_bcc_rec.source_org_id,
2397 x_nl_resource_org_id =>NULL,
2398 x_transaction_source =>NULL,
2399 x_calling_module =>NULL,
2400 x_vendor_id =>NULL,
2401 x_entered_by_user_id =>NULL,
2402 x_attribute_category =>NULL,
2403 x_attribute1 =>NULL,
2404 x_attribute2 =>NULL,
2405 x_attribute3 =>NULL,
2406 x_attribute4 =>NULL,
2407 x_attribute5 =>NULL,
2408 x_attribute6 =>NULL,
2409 x_attribute7 =>NULL,
2410 x_attribute8 =>NULL,
2411 x_attribute9 =>NULL,
2412 x_attribute10 =>NULL,
2413 x_attribute11 =>NULL,
2414 x_attribute12 =>NULL,
2415 x_attribute13 =>NULL,
2416 x_attribute14 =>NULL,
2417 x_attribute15 =>NULL,
2418 x_msg_application =>c_msg_application,
2419 x_msg_type =>c_msg_type,
2420 x_msg_token1 =>c_msg_token1,
2421 x_msg_token2 =>c_msg_token2,
2422 x_msg_token3 =>c_msg_token3,
2423 x_msg_count =>c_msg_count,
2424 x_msg_data =>c_status,
2425 x_billable_flag =>c_billable_flag,
2426 p_sys_link_function => 'BTC',
2427 p_work_type_id => l_work_type_id -- 2607781
2428 );
2429 IF P_DEBUG_MODE THEN
2430 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2200:Process Last Set .. back from PATC');
2431 END IF;
2432 if c_status is not null then
2433 null ;
2434 end if;
2435 -- ***********************
2436 i := i +1;
2437 /*2091559 Added followinf If Clause*/
2438 if prev_bcc_rec.billable_flag = 'N' then
2439 c_billable_flag :='N' ;
2440 end if;
2441
2442 /*
2443 * AddEi Attributes - related change.
2444 * Deriving work_type_id.
2445 */
2446 /* Commenting this for bug 2607781 as work type id needs to be derived before the call to pa_transactions_pub.validate_transaction
2447 IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
2448 THEN
2449 IF P_DEBUG_MODE THEN
2450 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2245:Calling Get_work_type_id with Project_id [' ||
2451 to_char(over_project_id) ||
2452 '] task_id [' || to_char(over_task_id) || ']');
2453 END IF;
2454 l_work_type_id := pa_utils4.Get_work_type_id
2455 ( p_project_id => over_project_id
2456 ,p_task_id => over_task_id
2457 ,p_assignment_id => NULL
2458 );
2459 IF P_DEBUG_MODE THEN
2460 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2245:Obtained work_type_id [' || to_char(l_work_type_id) || ']');
2461 END IF;
2462 END IF; */
2463
2464 /*
2465 Multi-Currency Related changes:
2466 Added additional parameters
2467 (denom currency code ,acct currency code, project currency code;
2468 all other currency attributes are set to null)
2469 */
2470
2471 -- Passed the value for parameter x_labor_cost_multiplier_name
2472 -- which is a new parameter created for bug 791759
2473
2474 -- IC Changes
2475 -- BTC txns should not be cross charged, so setting cross charge code to X.
2476 IF P_DEBUG_MODE THEN
2477 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2250:Process Last Set ..Call Loadei');
2478 END IF;
2479 pa_transactions.LoadEi(
2480 x_expenditure_item_id =>exp_item_id,
2481 x_expenditure_id =>x_exp_id,
2482 x_expenditure_item_date
2483 =>prev_bcc_rec.source_exp_item_date ,
2484 -- for 1664962 pa_utils.GetWeekEnding((prev_bcc_rec.source_pa_date)-6),
2485 x_project_id => over_project_id , --bugfix: 2201207 NULL,
2486 x_task_id =>over_task_id ,
2487 x_expenditure_type =>prev_bcc_rec.source_ind_expenditure_type,
2488 x_non_labor_resource =>prev_bcc_rec.source_nl_resource, -- 4057874
2489 x_nl_resource_org_id =>prev_bcc_rec.source_nl_resource_orgn_id , -- 4057874
2490 x_quantity =>0,
2491 x_raw_cost =>0 ,
2492 x_raw_cost_rate =>0,
2493 x_override_to_org_id =>exp_org_id ,
2494 x_billable_flag =>c_billable_flag, /*Bug# 4643188:Reverted fix of Bug#2840048 */
2495 x_bill_hold_flag =>'N',
2496 x_orig_transaction_ref =>NULL ,
2497 x_transferred_from_ei =>NULL ,
2498 x_adj_expend_item_id =>NULL,
2499 x_attribute_category =>prev_bcc_rec.source_attribute_category ,
2500 x_attribute1 =>prev_bcc_rec.source_attribute1 , /*Bug#3611675:Replaced l_attribute1*/
2501 x_attribute2 =>prev_bcc_rec.source_attribute2 ,
2502 x_attribute3 =>prev_bcc_rec.source_attribute3 ,
2503 x_attribute4 =>prev_bcc_rec.source_attribute4 ,
2504 x_attribute5 =>prev_bcc_rec.source_attribute5 ,
2505 x_attribute6 =>prev_bcc_rec.source_attribute6 ,
2506 x_attribute7 =>prev_bcc_rec.source_attribute7 ,
2507 x_attribute8 =>prev_bcc_rec.source_attribute8 ,
2508 x_attribute9 =>prev_bcc_rec.source_attribute9 ,
2509 x_attribute10 =>prev_bcc_rec.source_attribute10 ,
2510 x_ei_comment =>NULL ,
2511 x_transaction_source =>NULL ,
2512 x_source_exp_item_id =>NULL ,
2513 i => i ,
2514 x_job_id =>prev_bcc_rec.source_job_id , -- 4057874
2515 x_org_id =>G_MOAC_ORG_ID ,
2516 x_labor_cost_multiplier_name => NULL,
2517 x_drccid =>NULL ,
2518 x_crccid =>NULL ,
2519 x_cdlsr1 =>NULL ,
2520 x_cdlsr2 =>NULL ,
2521 x_cdlsr3 =>NULL ,
2522 x_gldate =>NULL ,
2523 x_bcost =>l_burden_cost ,
2524 x_bcostrate =>NULL ,
2525 x_etypeclass => 'BTC',
2526 x_burden_sum_dest_run_id =>current_run_id,
2527 x_burden_compile_set_id =>null,
2528 x_receipt_currency_amount =>null,
2529 x_receipt_currency_code =>null,
2530 x_receipt_exchange_rate =>null,
2531 x_denom_currency_code =>prev_bcc_rec.source_denom_currency_code,
2532 x_denom_raw_cost =>null,
2533 x_denom_burdened_cost =>l_denom_burdened_cost,
2534 x_acct_currency_code =>prev_bcc_rec.source_acct_currency_code,
2535 x_acct_rate_date =>prev_bcc_rec.src_acct_rate_date,
2536 x_acct_rate_type =>prev_bcc_rec.src_acct_rate_type,
2537 x_acct_exchange_rate =>prev_bcc_rec.src_acct_exchange_rate,
2538 x_acct_raw_cost =>null,
2539 x_acct_burdened_cost =>l_acct_burdened_cost,
2540 x_acct_exchange_rounding_limit =>null,
2541 x_project_currency_code =>prev_bcc_rec.source_project_currency_code,
2542 x_project_rate_date =>prev_bcc_rec.src_project_rate_date,
2543 x_project_rate_type =>prev_bcc_rec.src_project_rate_type,
2544 x_project_exchange_rate =>prev_bcc_rec.src_project_exchange_rate,
2545 p_project_raw_cost =>null,
2546 p_project_burdened_cost =>l_project_burdened_cost,
2547 p_projfunc_currency_code => prev_bcc_rec.source_projfunc_currency_code,
2548 p_projfunc_cost_rate_date => prev_bcc_rec.src_projfunc_cost_rate_date,
2549 p_projfunc_cost_rate_type => prev_bcc_rec.src_projfunc_cost_rate_type,
2550 p_projfunc_cost_exchange_rate => prev_bcc_rec.src_projfunc_cost_xchng_rate,
2551 p_work_type_id => l_work_type_id,
2552 X_Cross_Charge_Code => 'X',
2553 x_recv_operating_unit => proj_rec.org_id
2554 ,p_Po_line_id => prev_bcc_rec.source_po_line_id
2555 ,p_adjustment_type => prev_bcc_rec.source_adjustment_type
2556 ,p_Wip_Resource_Id => prev_bcc_rec.source_wip_resource_id -- 4057874
2557 ,p_Inventory_Item_Id => prev_bcc_rec.source_inventory_item_id -- 4057874
2558 ,p_src_system_linkage_function => prev_bcc_rec.source_system_linkage_function -- 4057874
2559 ,p_vendor_id => prev_bcc_rec.source_vendor_id -- Bug 6993002
2560 ,p_cbs_element_id => prev_bcc_rec.cbs_element_id -- Added for CBS enahncement bug 16579913
2561 );
2562 end;
2563
2564 end if;
2565 if i > 0 then
2566 IF P_DEBUG_MODE THEN
2567 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2300:Process Last Set ..Call Insitems');
2568 END IF;
2569 pa_transactions.InsItems(1,0,'BTC','BTC',i, status,'N');
2570
2571 -- -----------------------------------------------------------------------
2572 -- OGM_0.0 - Interface for creating new ADLS for each expenditure Item
2573 -- created. This will create award distribution lines only when OGM is
2574 -- installed for the ORG in process.
2575 -- The folowing procedure returns doing nothing if status is in ERROR for
2576 -- pa_transactions.InsItems.
2577 -- ------------------------------------------------------------------------
2578 IF P_DEBUG_MODE THEN
2579 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1760:Call Vertical APPS interface for i>=500');
2580 END IF;
2581
2582 PA_GMS_API.vert_trx_interface(0,0,'PAXCBCAB', 'PA_BURDEN_COSTING', i, status, 'N') ;
2583
2584 IF P_DEBUG_MODE THEN
2585 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1760:Call Vertical APPS interface for i>=500 END.');
2586 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2350:Process Last Set ..Call Flusheitabs');
2587 END IF;
2588 pa_transactions.FlushEiTabs();
2589 i := 0;
2590 end if;
2591
2592 /*<<NEXT_PROJECT>>.....bug# 2255068*/
2593 /* Bug#2255068
2594 * Commented this update as this update is no more required.
2595 *
2596 *
2597 * ** Update those CDLs which are successfully processed with run id
2598 *
2599 * pa_cc_utils.log_message('2400:Update successful CDLs');
2600 *
2601 * ** Removed ei table and pa_tasks table and
2602 * changed cdl_all table to cdl view for
2603 * bug# 1668634
2604 *
2605 * update pa_cost_distribution_lines cdl
2606 * set burden_sum_source_run_id = current_run_id
2607 * where cdl.line_type = 'R'
2608 * and cdl.burden_sum_source_run_id = init_cdl_run_id
2609 * and cdl.project_id = current_project_id
2610 * cdl.PA_DATE <= nvl(to_date(x_end_date,'DD-MM-RR'),cdl.PA_DATE)
2611 * and request_id = x_request_id ;
2612 *
2613 * pa_cc_utils.log_message('2500:before commit');
2614 */
2615
2616
2617 /* IF ( l_last_batch_for_project = 'Y')
2618 THEN Bug 4747865 */
2619 /*
2620 * Completed processing all CDLs of this project.
2621 */
2622 IF l_tbl_eiid.count > 0 THEN /* Bug# 5406802 */
2623
2624 IF l_burden_profile ='Y' Then /*3040724*/
2625 /*2933915 :Create Audit records for the special cdls using prev_ind_compiled_set_id and burden_sum_source_run_id*/
2626
2627 PA_BURDEN_COSTING.InsBurdenAudit(current_project_id,x_request_id,l_user_id,lstatus);
2628
2629 /*2933915 :Resetting 'special eis' -Adjustment Type and commiting by batches */
2630
2631 /* LOOP commented for Bug 4747865 */
2632
2633 UPDATE pa_expenditure_items_all ei
2634 set adjustment_type =NULL, /*Start of bug 4754024*/
2635 cc_bl_distributed_code =decode(tp_ind_compiled_set_id,NULL,
2636 decode(cc_bl_distributed_code,'Y','N',cc_bl_distributed_code),cc_bl_distributed_code), /*4754024*/
2637 cc_ic_processed_code =decode(tp_ind_compiled_set_id,NULL,decode(cc_ic_processed_code,'Y','N',cc_ic_processed_code)
2638 ,cc_ic_processed_code),
2639 Denom_Tp_Currency_Code =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Denom_Tp_Currency_Code),
2640 Denom_Tp_Currency_Code),
2641 Denom_Transfer_Price =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Denom_Transfer_Price),
2642 Denom_Transfer_Price),
2643 Acct_Tp_Rate_Type =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Rate_Type),
2644 Acct_Tp_Rate_Type),
2645 Acct_Tp_Rate_Date =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Rate_Date),
2646 Acct_Tp_Rate_Date),
2647 Acct_Tp_Exchange_Rate =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Exchange_Rate),
2648 Acct_Tp_Exchange_Rate),
2649 Acct_Transfer_Price =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Transfer_Price),
2650 Acct_Transfer_Price),
2651 Projacct_Transfer_Price=decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Projacct_Transfer_Price),
2652 Projacct_Transfer_Price),
2653 Cc_Markup_Base_Code =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Cc_Markup_Base_Code),
2654 Cc_Markup_Base_Code),
2655 Tp_Base_Amount =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Base_Amount),
2656 Tp_Base_Amount),
2657 Tp_Bill_Rate =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Bill_Rate),TP_Bill_Rate),
2658 Tp_Bill_Markup_Percentage=decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,
2659 Tp_Bill_Markup_Percentage),Tp_Bill_Markup_Percentage),
2660 Tp_Schedule_line_Percentage =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,
2661 Tp_Schedule_line_Percentage),Tp_Schedule_line_Percentage),
2662 Tp_Rule_percentage = decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Rule_percentage),
2663 Tp_Rule_percentage) /*End of bug 4754024*/
2664 where adjustment_type ='BURDEN_RESUMMARIZE'
2665 and project_id = current_project_id
2666 and exists (select 1 from pa_cost_distribution_lines_all cdl
2667 where cdl.expenditure_item_id = ei.expenditure_item_id
2668 and cdl.request_id = x_request_id
2669 /* and cdl.prev_ind_compiled_set_id is NOT NULL :Commented for bug# 3040724*/
2670 and cdl.burden_sum_source_run_id =current_run_id )
2671 and rownum <=l_profile_set_size;
2672
2673 ei_update_count :=SQL%ROWCOUNT ;
2674
2675
2676 /*2933915 :Resetting prev_ind_compiled_set_id */
2677
2678 /* Modified this sql for bug 5406802*/
2679
2680 FORALL I IN 1..l_tbl_eiid.count
2681
2682 UPDATE pa_cost_distribution_lines_all
2683 set prev_ind_compiled_set_id = NULL
2684 where prev_ind_compiled_set_id IS NOT NULL
2685 and project_id = current_project_id
2686 and request_id = x_request_id
2687 and burden_sum_source_run_id =current_run_id
2688 and expenditure_item_id = l_tbl_eiid(i);
2689
2690 cdl_update_count :=SQL%ROWCOUNT ;
2691
2692 /* COMMIT; commented for Bug 4747865 */
2693
2694 IF (ei_update_count <l_profile_set_size) AND (cdl_update_count <l_profile_set_size) Then
2695 COMMIT;
2696 /* EXIT; Bug 4747865 */
2697 END IF ;
2698 /*END LOOP; Bug 4747865 */
2699 END If ; /*If profile is 'Y' 3040724*/
2700
2701 END IF; -- IF l_tbl_eiid.count > 0 THEN /* Bug# 5406802*/
2702
2703
2704 /***2933915****/
2705 --COMMIT ;
2706 --EXIT ;
2707 /* END IF; End if of last_batch_for_project commented for Bug 4747865 */
2708
2709 /*
2710 * Bug#2255068
2711 * Commit happens once per CDL batch.
2712 */
2713 COMMIT ;
2714 end loop; -- Loop for processing CDLs in batches for this project.
2715
2716 IF P_DEBUG_MODE THEN
2717 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2550:End of Project loop');
2718 END IF;
2719 <<NEXT_PROJECT>>
2720 null;
2721 END LOOP; -- FOR LOOP
2722 end loop project_loop;
2723
2724 /* S.N. Bug 3618193 : Added following code to close the cursor */
2725
2726 IF projects_with_eb%ISOPEN THEN
2727 CLOSE projects_with_eb;
2728 END IF;
2729
2730 IF projects_without_eb%ISOPEN THEN
2731 CLOSE projects_without_eb;
2732 END IF;
2733
2734 /* E.N. Bug 3618193 : Added following code to close the cursor */
2735
2736 <<END_OF_PROCESS>>
2737 IF P_DEBUG_MODE THEN
2738 pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2600:At the end of the Process');
2739 END IF;
2740 null;
2741 x_run_id := current_run_id;
2742 pa_cc_utils.reset_curr_function;
2743 end create_burden_expenditure_item;
2744
2745
2746
2747 PROCEDURE create_burden_cmt_transaction ( status in out NOCOPY number,
2748 stage in out NOCOPY number,
2749 x_run_id in out NOCOPY number,
2750 x_project_id in number) /* bug#2791563 added x_project_id */
2751 IS
2752
2753 ------------ Decalrarion of Variables ---------------
2754
2755 current_project_id number;
2756 current_run_id number := 1;
2757 init_cmt_run_id number := -9999;
2758 message_string varchar2(200);
2759
2760 prev_bcc_rec pa_cmt_burden_summary_v%rowtype;
2761 l_attribute1 pa_projects_all.attribute1%type;
2762 l_cmt_line_id pa_commitment_txns.cmt_line_id%type;
2763 l_burden_cost pa_expenditure_items_all.burden_cost%type;
2764 l_txn_ref1 pa_commitment_txns.original_txn_reference1%type;
2765 apps_id number(3) := 275; -- For PA
2766 sob_id number(15);
2767 l_gl_period varchar2(15);
2768 l_pa_end_date date;
2769
2770 /* start bug 2324127 */
2771
2772 l_acct_burdened_cost pa_commitment_txns.acct_burdened_cost%type;
2773 l_denom_burdened_cost pa_commitment_txns.denom_burdened_cost%type;
2774
2775 /* end bug 2324127 */
2776
2777 /* 4057874 */
2778 l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2779 l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2780 l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2781 l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2782 l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2783
2784 ------------ Decalrarion of Cursors ---------------
2785
2786 -- Cursor to select all the Projects for which there are non-summarized Commitment tran
2787
2788 -- Bug 2556167: Added hint for using hash join
2789 -- Bug 2838499: Used pa_projects Org view instead of table.
2790 -- added org_id join between pt and p.
2791 /*
2792 * Bug 4914022 : Changed projects cursor to join to pa_projects_all
2793 * pa_project_types_all instead of pa_projects, pa_project_types.
2794 */
2795 Cursor projects is
2796 select /*+ use_hash(pt p) */
2797 p.project_id , p.segment1
2798 , upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
2799 , upper(pt.burden_amt_display_method) burden_amt_display_method
2800 from pa_projects_all p,
2801 pa_project_types_all pt
2802 where pt.project_type = p.project_type
2803 and pt.org_id = p.org_id /*5368274*/
2804 and pt.burden_amt_display_method in ('D','d')
2805 and p.project_id = nvl(x_project_id,p.project_id) /* bug#2791563 */
2806 and exists ( select 1
2807 from pa_commitment_txns cmt
2808 where nvl(x_project_id,p.project_id) = cmt.project_id /* Bug 3613712 : Perf Issue SQL rep ID : 7938694 FTS on pa_commitment_txns */
2809 --Bug#960813
2810 -- and cmt.line_type = 'R'
2811 and cmt.burden_sum_source_run_id = init_cmt_run_id );
2812
2813 -- Cursor to select Burden cost components of all the non-summarized CDLs of a project
2814
2815 cursor bcc_cur is
2816 select source_project_id,
2817 source_task_id,
2818 source_org_id ,
2819 source_pa_period ,
2820 source_gl_period ,
2821 source_txn_source,
2822 source_line_type ,
2823 source_ind_cost_code ,
2824 source_txn_ref1,
2825 source_expenditure_type,
2826 source_ind_expenditure_type,
2827 source_exp_category ,
2828 source_revenue_category,
2829 source_cost_base ,
2830 source_compiled_multiplier,
2831 source_ind_rate_sch_id ,
2832 source_ind_rate_sch_rev_id,
2833 source_burden_cost ,
2834 source_run_id,
2835 source_burden_sum_rej_code,
2836 resource_class,
2837 source_system_linkage_function, -- 4057874
2838 dest_project_id,
2839 dest_task_id ,
2840 dest_org_id,
2841 dest_pa_period ,
2842 dest_txn_source,
2843 dest_gl_period ,
2844 dest_exp_category ,
2845 dest_revenue_category ,
2846 dest_ind_exp_type ,
2847 dest_line_type ,
2848 dest_txn_ref1,
2849 dest_ind_cost_code,
2850 acct_raw_cost , /* 2324127 */
2851 acct_burdened_cost , /* 2324127 */
2852 denom_currency_code , /* 2324127 */
2853 denom_raw_cost , /* 2324127 */
2854 denom_burdened_cost , /* 2324127 */
2855 acct_currency_code , /* 2324127 */
2856 acct_rate_date , /* 2324127 */
2857 acct_rate_type , /* 2324127 */
2858 acct_exchange_rate , /* 2324127 */
2859 -- receipt_currency_code , /* 2324127 */
2860 -- receipt_currency_amount, /* 2324127 */
2861 -- receipt_exchange_rate , /* 2324127 */
2862 project_currency_code , /* 2324127 */
2863 project_rate_date , /* 2324127 */
2864 project_rate_type , /* 2324127 */
2865 project_exchange_rate , /* 2324127 */
2866 vendor_id , -- 4057874
2867 inventory_item_id , -- 4057874
2868 bom_labor_resource_id , -- 4057874
2869 bom_equipment_resource_id , -- 4057874
2870 dest_summary_group
2871 from pa_cmt_burden_summary_v
2872 order by dest_summary_group;
2873
2874 begin
2875
2876 /* Bug 2989775: If the client extension has been modified to create same line burdening
2877 for commitment transactions irrespective of project type set up, then no BTC lines
2878 should be created for commitment transactions and so no processing will be done in this
2879 procedure. */
2880
2881 IF PA_CLIENT_EXTN_BURDEN_SUMMARY.Same_Line_Burden_Cmt
2882 then null;
2883 else
2884
2885 select pa_burden_sum_run_s.nextval
2886 into current_run_id
2887 from dual;
2888 /*
2889 * Bug 2838499: commented this stray sql. (nothing related to the bug.)
2890 * select set_of_books_id
2891 * into sob_id
2892 * from pa_implementations;
2893 */
2894
2895 -- Step 1 . Select All projects
2896
2897 begin
2898
2899 <<PROJECT_LOOP>> -- for projects from projects cursor
2900
2901 stage := 200; -- at start
2902 for proj_rec in projects loop
2903 --dbms_output.put_line('no of projects');
2904 -- Set current project id in the package pa_burden_costing for view definitions and for local variable
2905
2906 PA_BURDEN_COSTING.set_current_project_id(proj_rec.project_id);
2907 current_project_id := proj_rec.project_id;
2908
2909 -- ======
2910 -- Bug : 3699045 - PJ.M:B4:P13:OTH:PERF:XPL PERFORMANCE ISSUES IN PAVW341.SQL
2911 -- ======
2912 PA_BURDEN_COSTING.set_current_sponsored_flag(proj_rec.project_id);
2913
2914 -- Step 2a. Project level validations
2915 -- Set the project and task id of the burden expenditure item
2916 -- depending on burden_accounting flag
2917
2918 begin
2919 stage := 210; -- locking project record
2920 select attribute1
2921 into l_attribute1
2922 from pa_projects_all
2923 where project_id = proj_rec.project_id
2924 for update of attribute1 nowait;
2925
2926 update pa_commitment_txns
2927 set burden_sum_rejection_code = NULL
2928 where project_id = current_project_id
2929 and burden_sum_source_run_id = init_cmt_run_id;
2930
2931 exception
2932 when resource_busy then
2933 goto NEXT_PROJECT;
2934 end;
2935
2936 -- Step 2b. Do CMT level validations of the project
2937
2938 begin
2939 stage := 220; -- updating error transactions
2940 update pa_commitment_txns
2941 set burden_sum_rejection_code = 'BCC_EXP_TYPE_NULL',
2942 burden_sum_source_run_id = current_run_id
2943 where project_id = current_project_id
2944 and (cmt_line_id) in
2945 (select cmt.cmt_line_id
2946 from pa_commitment_txns cmt
2947 -- Bug#960813
2948 -- where cmt.line_type = 'R'
2949 where cmt.burden_sum_source_run_id = current_run_id
2950 and cmt.project_id = current_project_id
2951 and exists (select 1
2952 from pa_compiled_multipliers cm,
2953 pa_ind_cost_codes icc
2954 where cm.ind_compiled_set_id=cmt.cmt_ind_compiled_set_id
2955 and icc.ind_cost_code = cm.ind_cost_code
2956 and icc.expenditure_type is null));
2957 exception
2958 when others then
2959 null;
2960 end;
2961
2962
2963 prev_bcc_rec.dest_summary_group := null;
2964 l_burden_cost := 0;
2965 /* start fix bug#2324127 */
2966 l_acct_burdened_cost := 0;
2967 l_denom_burdened_cost := 0;
2968 /* end fix bug#2324127 */
2969 prev_bcc_rec.source_org_id := null;
2970 -- Step 4. select Burden cost components of CDLs.
2971
2972 begin
2973
2974 for bcc_rec in bcc_cur loop
2975 stage := 230; -- in burden loop
2976
2977 -- 5c. Create expenditure item for every dest_summary_group change
2978
2979 if bcc_rec.dest_summary_group = prev_bcc_rec.dest_summary_group then
2980 /* commented for bug 5984985
2981 l_burden_cost := l_burden_cost + bcc_rec.source_burden_cost;
2982 -- start fix bug#2324127
2983 l_acct_burdened_cost := l_acct_burdened_cost + bcc_rec.acct_burdened_cost ;
2984 -- end fix bug#2324127
2985 l_denom_burdened_cost := l_denom_burdened_cost + bcc_rec.denom_burdened_cost ;
2986 commented for bug 5984985 */
2987
2988 l_burden_cost := l_burden_cost + pa_currency.round_currency_amt1(bcc_rec.source_burden_cost); /* added currency rounding for bug 5984985 */
2989 l_acct_burdened_cost := l_acct_burdened_cost + pa_currency.round_currency_amt1(bcc_rec.acct_burdened_cost); /* added currency rounding for bug 5984985 */
2990 l_denom_burdened_cost := l_denom_burdened_cost + pa_currency.round_trans_currency_amt1(bcc_rec.denom_burdened_cost,bcc_rec.denom_currency_code); /* added currency rounding for bug 5984985 */
2991
2992 if nvl(bcc_rec.source_txn_ref1,'X') <> nvl(prev_bcc_rec.source_txn_ref1,'X') then
2993 l_txn_ref1 := ''; -- nullify the transaction reference column
2994 else
2995 l_txn_ref1 := prev_bcc_rec.source_txn_ref1;
2996 end if;
2997 -- Check for other attributes of summarized burden expenditure item
2998 else
2999 -- Create new summarization Commitment transaction
3000 -- Get new commitment transaction id
3001
3002 -- Checking for l_burden_cost <> 0 rather than l_burden_cost > 0
3003 -- l_burden_cost is replaced by l_denom_burdened_cost <> 0 for bug 2324127
3004 if l_denom_burdened_cost <> 0 then
3005
3006 begin
3007 select pa_commitment_txns_s.nextval
3008 into l_cmt_line_id
3009 from dual;
3010
3011 -- Getting expenditure_item_date as the end_date for that pa period
3012 stage := 235; -- Getting expenditure_item_date
3013 l_pa_end_date := pa_utils.get_pa_end_date(prev_bcc_rec.dest_pa_period);
3014 -- Getting GL period from the view itself
3015 stage := 240; -- creating transactions
3016 insert into pa_commitment_txns (
3017 cmt_line_id,
3018 project_id,
3019 task_id ,
3020 transaction_source ,
3021 line_type ,
3022 expenditure_item_date,
3023 pa_period ,
3024 gl_period,
3025 expenditure_type,
3026 expenditure_category ,
3027 revenue_category,
3028 system_linkage_function,
3029 tot_cmt_burdened_cost ,
3030 original_txn_reference1,
3031 last_updated_by ,
3032 last_update_date ,
3033 creation_date ,
3034 created_by ,
3035 last_update_login,
3036 acct_raw_cost , /* 2324127 */
3037 acct_burdened_cost , /* 2324127 */
3038 denom_currency_code , /* 2324127 */
3039 denom_raw_cost , /* 2324127 */
3040 denom_burdened_cost , /* 2324127 */
3041 acct_currency_code , /* 2324127 */
3042 acct_rate_date , /* 2324127 */
3043 acct_rate_type , /* 2324127 */
3044 acct_exchange_rate , /* 2324127 */
3045 -- receipt_currency_code , /* 2324127 */
3046 -- receipt_currency_amount , /* 2324127 */
3047 -- receipt_exchange_rate , /* 2324127 */
3048 project_currency_code , /* 2324127 */
3049 project_rate_date , /* 2324127 */
3050 project_rate_type , /* 2324127 */
3051 project_exchange_rate , /* 2324127 */
3052 burden_sum_dest_run_id,
3053 organization_id ,
3054 resource_class ,
3055 vendor_id , /* 4057874 */
3056 inventory_item_id , /* 4057874 */
3057 bom_labor_resource_id , /* 4057874 */
3058 bom_equipment_resource_id , /* 4057874 */
3059 src_system_linkage_function ) /* 4057874 */
3060 values (
3061 l_cmt_line_id,
3062 prev_bcc_rec.dest_project_id,
3063 prev_bcc_rec.dest_task_id,
3064 prev_bcc_rec.dest_txn_source,
3065 prev_bcc_rec.dest_line_type,
3066 nvl(l_pa_end_date, sysdate) ,
3067 prev_bcc_rec.dest_pa_period,
3068 prev_bcc_rec.dest_gl_period,
3069 prev_bcc_rec.dest_ind_exp_type,
3070 prev_bcc_rec.dest_exp_category,
3071 prev_bcc_rec.dest_revenue_category,
3072 'BTC',
3073 l_burden_cost,
3074 l_txn_ref1,
3075 1,
3076 sysdate,
3077 sysdate,
3078 0,
3079 0,
3080 0, /* acct_raw_cost 2324127 */
3081 l_acct_burdened_cost , /* 2324127 */
3082 prev_bcc_rec.denom_currency_code , /* 2324127 */
3083 0 , /* denom_raw_cost 2324127 */
3084 l_denom_burdened_cost , /* 2324127 */
3085 prev_bcc_rec.acct_currency_code , /* 2324127 */
3086 prev_bcc_rec.acct_rate_date , /* 2324127 */
3087 prev_bcc_rec.acct_rate_type , /* 2324127 */
3088 prev_bcc_rec.acct_exchange_rate , /* 2324127 */
3089 -- receipt_currency_code , /* 2324127 */
3090 -- receipt_currency_amount , /* 2324127 */
3091 -- receipt_exchange_rate , /* 2324127 */
3092 prev_bcc_rec.project_currency_code , /* 2324127 */
3093 prev_bcc_rec.project_rate_date , /* 2324127 */
3094 prev_bcc_rec.project_rate_type , /* 2324127 */
3095 prev_bcc_rec.project_exchange_rate , /* 2324127 */
3096 current_run_id,
3097 prev_bcc_rec.dest_org_id,
3098 prev_bcc_rec.resource_class,
3099 prev_bcc_rec.vendor_id, /* 4057874 */
3100 prev_bcc_rec.inventory_item_id, /* 4057874 */
3101 prev_bcc_rec.bom_labor_resource_id, /* 4057874 */
3102 prev_bcc_rec.bom_equipment_resource_id, /* 4057874 */
3103 prev_bcc_rec.source_system_linkage_function ); /* 4057874 */
3104 end;
3105 end if;
3106
3107 l_burden_cost := pa_currency.round_currency_amt1(bcc_rec.source_burden_cost); /* added currency rounding for bug 5984985 */
3108 l_acct_burdened_cost := pa_currency.round_currency_amt1(bcc_rec.acct_burdened_cost); /* added currency rounding for bug 5984985 */ /* 2324127 */
3109 l_denom_burdened_cost := pa_currency.round_trans_currency_amt(bcc_rec.denom_burdened_cost,bcc_rec.denom_currency_code); /* added currency rounding for bug 5984985 */ /* 2324127 */
3110
3111
3112 prev_bcc_rec.source_project_id :=bcc_rec.source_project_id;
3113 prev_bcc_rec.source_task_id :=bcc_rec.source_task_id;
3114 prev_bcc_rec.source_org_id :=bcc_rec.source_org_id;
3115 prev_bcc_rec.source_pa_period :=bcc_rec.source_pa_period;
3116 prev_bcc_rec.source_gl_period :=bcc_rec.source_gl_period;
3117 prev_bcc_rec.source_txn_source :=bcc_rec.source_txn_source;
3118 prev_bcc_rec.source_line_type :=bcc_rec.source_line_type;
3119 prev_bcc_rec.source_ind_cost_code :=bcc_rec.source_ind_cost_code;
3120 prev_bcc_rec.source_txn_ref1 :=bcc_rec.source_txn_ref1;
3121 prev_bcc_rec.source_expenditure_type :=bcc_rec.source_expenditure_type;
3122 prev_bcc_rec.source_ind_expenditure_type :=bcc_rec.source_ind_expenditure_type;
3123 prev_bcc_rec.source_exp_category :=bcc_rec.source_exp_category;
3124 prev_bcc_rec.source_revenue_category :=bcc_rec.source_revenue_category;
3125 prev_bcc_rec.source_cost_base :=bcc_rec.source_cost_base;
3126 prev_bcc_rec.source_compiled_multiplier :=bcc_rec.source_compiled_multiplier;
3127 prev_bcc_rec.source_ind_rate_sch_id :=bcc_rec.source_ind_rate_sch_id;
3128 prev_bcc_rec.source_ind_rate_sch_rev_id :=bcc_rec.source_ind_rate_sch_rev_id;
3129 prev_bcc_rec.source_burden_cost :=pa_currency.round_currency_amt1(bcc_rec.source_burden_cost); /* added currency rounding for bug 5984985 */
3130 -- prev_bcc_rec.source_burden_cost :=bcc_rec.source_burden_cost;
3131 prev_bcc_rec.source_run_id :=bcc_rec.source_run_id;
3132 prev_bcc_rec.source_burden_sum_rej_code :=bcc_rec.source_burden_sum_rej_code;
3133 prev_bcc_rec.dest_project_id :=bcc_rec.dest_project_id;
3134 prev_bcc_rec.dest_task_id :=bcc_rec.dest_task_id;
3135 prev_bcc_rec.dest_org_id :=bcc_rec.dest_org_id;
3136 prev_bcc_rec.dest_pa_period :=bcc_rec.dest_pa_period;
3137 prev_bcc_rec.dest_gl_period :=bcc_rec.dest_gl_period;
3138 prev_bcc_rec.dest_txn_source :=bcc_rec.dest_txn_source;
3139 prev_bcc_rec.dest_line_type :=bcc_rec.dest_line_type;
3140 prev_bcc_rec.dest_exp_category :=bcc_rec.dest_exp_category;
3141 prev_bcc_rec.dest_revenue_category :=bcc_rec.dest_revenue_category;
3142 prev_bcc_rec.dest_ind_exp_type :=bcc_rec.dest_ind_exp_type;
3143 prev_bcc_rec.dest_txn_ref1 :=bcc_rec.dest_txn_ref1;
3144 prev_bcc_rec.dest_ind_cost_code :=bcc_rec.dest_ind_cost_code;
3145 prev_bcc_rec.dest_summary_group :=bcc_rec.dest_summary_group;
3146 /* start bug 2324127 */
3147
3148 prev_bcc_rec.denom_currency_code :=bcc_rec.denom_currency_code ;
3149 prev_bcc_rec.acct_currency_code :=bcc_rec.acct_currency_code ;
3150 prev_bcc_rec.acct_rate_date :=bcc_rec.acct_rate_date ;
3151 prev_bcc_rec.acct_rate_type :=bcc_rec.acct_rate_type ;
3152 prev_bcc_rec.acct_exchange_rate :=bcc_rec.acct_exchange_rate ;
3153 prev_bcc_rec.project_currency_code :=bcc_rec.project_currency_code ;
3154 prev_bcc_rec.project_rate_date :=bcc_rec.project_rate_date ;
3155 prev_bcc_rec.project_rate_type :=bcc_rec.project_rate_type ;
3156 prev_bcc_rec.project_exchange_rate :=bcc_rec.project_exchange_rate ;
3157 prev_bcc_rec.resource_class :=bcc_rec.resource_class ;
3158
3159 /* 4057874 */
3160 prev_bcc_rec.vendor_id := bcc_rec.vendor_id;
3161 prev_bcc_rec.inventory_item_id := bcc_rec.inventory_item_id;
3162 prev_bcc_rec.bom_labor_resource_id := bcc_rec.bom_labor_resource_id;
3163 prev_bcc_rec.bom_equipment_resource_id := bcc_rec.bom_equipment_resource_id;
3164 prev_bcc_rec.source_system_linkage_function := bcc_rec.source_system_linkage_function;
3165
3166 /* bug fix 4091690 starts */
3167 if P_BTC_SRC_RESRC = 'Y' then
3168
3169 pa_client_extn_burden_resource.client_column_values (
3170 p_job_id => l_job_id,
3171 p_non_labor_resource => l_non_labor_resource,
3172 p_non_labor_resource_orgn_id => l_non_labor_resource_orgn_id,
3173 p_wip_resource_id => l_wip_resource_id,
3174 p_incurred_by_person_id => l_incurred_by_person_id,
3175 p_vendor_id => prev_bcc_rec.vendor_id,
3176 p_inventory_item_id => prev_bcc_rec.inventory_item_id,
3177 p_bom_labor_resource_id => prev_bcc_rec.bom_labor_resource_id,
3178 p_bom_equipment_resource_id => prev_bcc_rec.bom_equipment_resource_id);
3179 else
3180 prev_bcc_rec.vendor_id := null;
3181 prev_bcc_rec.inventory_item_id := null;
3182 prev_bcc_rec.bom_labor_resource_id := null;
3183 prev_bcc_rec.bom_equipment_resource_id := null;
3184
3185 prev_bcc_rec.source_system_linkage_function := null;
3186
3187 end if; -- profile option
3188
3189 /* bug fix 4091690 ends */
3190
3191
3192
3193 /* start bug 2324127 */
3194 end if;
3195 end loop; -- bcc_cur loop ends here
3196 end;
3197
3198 <<NEXT_PROJECT>>
3199 -- Checking for l_burden_cost <> 0 rather than l_burden_cost > 0
3200 -- Replaced l_burden_cost by l_denom_burdened_cost for bug#2324127
3201 if l_denom_burdened_cost <> 0 then
3202 begin
3203 select pa_commitment_txns_s.nextval
3204 into l_cmt_line_id
3205 from dual;
3206
3207 -- Getting expenditure_item_date as the end_date for that pa period
3208 stage := 245; -- Getting expenditure_item_date
3209 l_pa_end_date := pa_utils.get_pa_end_date(prev_bcc_rec.dest_pa_period);
3210 -- create summarized commitment transaction
3211 insert into pa_commitment_txns (
3212 cmt_line_id,
3213 project_id,
3214 task_id ,
3215 transaction_source ,
3216 line_type ,
3217 expenditure_item_date,
3218 pa_period ,
3219 gl_period,
3220 expenditure_type,
3221 expenditure_category ,
3222 revenue_category,
3223 system_linkage_function,
3224 tot_cmt_burdened_cost ,
3225 original_txn_reference1,
3226 last_updated_by ,
3227 last_update_date ,
3228 creation_date ,
3229 created_by ,
3230 last_update_login,
3231 acct_raw_cost , /* 2324127 */
3232 acct_burdened_cost , /* 2324127 */
3233 denom_currency_code , /* 2324127 */
3234 denom_raw_cost , /* 2324127 */
3235 denom_burdened_cost , /* 2324127 */
3236 acct_currency_code , /* 2324127 */
3237 acct_rate_date , /* 2324127 */
3238 acct_rate_type , /* 2324127 */
3239 acct_exchange_rate , /* 2324127 */
3240 -- receipt_currency_code , /* 2324127 */
3241 -- receipt_currency_amount , /* 2324127 */
3242 -- receipt_exchange_rate , /* 2324127 */
3243 project_currency_code , /* 2324127 */
3244 project_rate_date , /* 2324127 */
3245 project_rate_type , /* 2324127 */
3246 project_exchange_rate , /* 2324127 */
3247 burden_sum_dest_run_id,
3248 organization_id
3249 ,resource_class,
3250 vendor_id , /* 4057874 */
3251 inventory_item_id , /* 4057874 */
3252 bom_labor_resource_id , /* 4057874 */
3253 bom_equipment_resource_id , /* 4057874 */
3254 src_system_linkage_function ) /* 4057874 */
3255
3256 values (
3257 l_cmt_line_id,
3258 prev_bcc_rec.dest_project_id,
3259 prev_bcc_rec.dest_task_id,
3260 prev_bcc_rec.dest_txn_source,
3261 prev_bcc_rec.dest_line_type,
3262 nvl(l_pa_end_date, sysdate) ,
3263 prev_bcc_rec.dest_pa_period,
3264 prev_bcc_rec.dest_gl_period,
3265 prev_bcc_rec.dest_ind_exp_type,
3266 prev_bcc_rec.dest_exp_category,
3267 prev_bcc_rec.dest_revenue_category,
3268 'BTC',
3269 l_burden_cost,
3270 l_txn_ref1,
3271 1,
3272 sysdate,
3273 sysdate,
3274 0,
3275 0,
3276 0, /* acct_raw_cost 2324127 */
3277 l_acct_burdened_cost , /* 2324127 */
3278 prev_bcc_rec.denom_currency_code , /* 2324127 */
3279 0 , /* denom_raw_cost 2324127 */
3280 l_denom_burdened_cost , /* 2324127 */
3281 prev_bcc_rec.acct_currency_code , /* 2324127 */
3282 prev_bcc_rec.acct_rate_date , /* 2324127 */
3283 prev_bcc_rec.acct_rate_type , /* 2324127 */
3284 prev_bcc_rec.acct_exchange_rate , /* 2324127 */
3285 -- receipt_currency_code , /* 2324127 */
3286 -- receipt_currency_amount , /* 2324127 */
3287 -- receipt_exchange_rate , /* 2324127 */
3288 prev_bcc_rec.project_currency_code , /* 2324127 */
3289 prev_bcc_rec.project_rate_date , /* 2324127 */
3290 prev_bcc_rec.project_rate_type , /* 2324127 */
3291 prev_bcc_rec.project_exchange_rate , /* 2324127 */
3292 current_run_id,
3293 prev_bcc_rec.dest_org_id
3294 ,prev_bcc_rec.resource_class,
3295 prev_bcc_rec.vendor_id, /* 4057874 */
3296 prev_bcc_rec.inventory_item_id, /* 4057874 */
3297 prev_bcc_rec.bom_labor_resource_id, /* 4057874 */
3298 prev_bcc_rec.bom_equipment_resource_id, /* 4057874 */
3299 prev_bcc_rec.source_system_linkage_function ); /* 4057874 */
3300 end;
3301 end if;
3302
3303 -- Update those Commitment transactions which are successfully processed
3304 -- with run id
3305
3306 update pa_commitment_txns
3307 set burden_sum_source_run_id = current_run_id
3308 where (cmt_line_id) in
3309 ( select cmt_line_id
3310 from pa_commitment_txns cmt
3311 where cmt.burden_sum_rejection_code is NULL
3312 -- Bug#960813
3313 -- and cmt.line_type = 'R'
3314 and cmt.burden_sum_source_run_id = init_cmt_run_id
3315 and cmt.project_id = current_project_id);
3316 COMMIT;
3317
3318 end loop project_loop;
3319 end;
3320 <<END_OF_PROCESS>>
3321 null;
3322 x_run_id := current_run_id;
3323
3324 end if; -- Same_Line_Burden_Cmt
3325
3326 end CREATE_BURDEN_CMT_TRANSACTION ;
3327
3328 /******************************************************************************
3329 PROCEDURE InsBurdenAudit
3330 ******************************************************************************/
3331 /* Bug#5406802
3332 Changed the logic for populating the audit table.
3333 Used the cached expenditure item pl/sql table for populating the audit table
3334 instead opening the cursor from CDL table.
3335 */
3336
3337 PROCEDURE InsBurdenAudit( p_project_id IN pa_cost_distribution_lines_all.project_id%TYPE,
3338 p_request_id IN NUMBER ,
3339 p_user_id IN number,
3340 x_status IN OUT NOCOPY number )
3341 IS
3342 l_program_id NUMBER;
3343 l_program_application_id NUMBER;
3344 l_profile_set_size NUMBER ;
3345 l_eid_tbl PA_PLSQL_DATATYPES.IdTabTyp; /*2933915*/
3346 l_line_tbl PA_PLSQL_DATATYPES.IdTabTyp; /*2933915*/
3347 l_prev_id_tbl PA_PLSQL_DATATYPES.IdTabTyp; /*2933915*/
3348 l_run_id_tbl PA_PLSQL_DATATYPES.IdTabTyp; /*2933915*/
3349
3350
3351
3352 BEGIN
3353 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
3354 l_program_application_id := FND_GLOBAL.PROG_APPL_ID();
3355 FND_PROFILE.GET('PA_NUM_CDL_PER_SET', l_profile_set_size );
3356 x_status := 0;
3357
3358 FORALL i in 1..l_tbl_eiid.count
3359 INSERT INTO pa_aud_cost_dist_lines (
3360 expenditure_item_id
3361 , line_num
3362 , ind_compiled_set_id
3363 , burden_sum_source_run_id
3364 , creation_date
3365 , created_by
3366 , program_id
3367 , program_application_id
3368 , request_id
3369 )
3370 SELECT
3371 expenditure_Item_id,
3372 line_num,
3373 prev_ind_compiled_set_id,
3374 burden_sum_source_run_id,
3375 sysdate,
3376 p_user_id,
3377 l_program_id,
3378 l_program_application_id,
3379 p_request_id
3380 FROM
3381 pa_cost_distribution_lines_all
3382 WHERE expenditure_item_id = l_tbl_eiid(i)
3383 AND line_num= l_tbl_cdlln(i)
3384 AND prev_ind_compiled_set_id IS NOT NULL
3385 AND request_id = p_request_id
3386 AND project_id = p_project_id;
3387
3388 EXCEPTION
3389 WHEN OTHERS THEN
3390 X_status := SQLCODE;
3391 RAISE;
3392 END InsBurdenAudit;
3393
3394
3395 /* Bug# 5406802
3396 Introduced this procedure to populate global temporary table with valid EI's to be processed
3397 for BTC generation picked up for the first time.
3398 */
3399 procedure populate_gtemp(p_current_run_id NUMBER, p_project_id NUMBER, x_end_date varchar2) is
3400
3401 l_end_date DATE := to_date(x_end_date,'DD-MM-RR');
3402
3403 begin
3404 /* Bug 5896943: Inserting prvdr_accrual_date in place of expenditure_item_date
3405 for period accrual transactions so that the reversal BTC's will be in the future period.
3406 */
3407 /* Modified expenditure item date for bug 5907315*/
3408 /* Bug# 9373031: Added hint for performane issue */
3409
3410 insert into PA_EI_CDL_CM_GTEMP(
3411 PROJECT_ID ,TASK_ID ,ORGANIZATION_ID
3412 ,PA_DATE ,PA_PERIOD_NAME ,ATTRIBUTE1
3413 ,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4
3414 ,ATTRIBUTE5 ,ATTRIBUTE6 ,ATTRIBUTE7
3415 ,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
3416 ,ATTRIBUTE_CATEGORY ,PERSON_TYPE ,PO_LINE_ID
3417 ,SYSTEM_LINKAGE_FUNCTION ,EI_EXPENDITURE_TYPE ,IND_COMPILED_SET_ID
3418 ,PREV_IND_COMPILED_SET_ID ,EXPENDITURE_ITEM_ID ,LINE_NUM
3419 ,EXPENDITURE_ITEM_DATE ,CDL_AMOUNT ,CDL_PROJFUNC_CURRENCY_CODE
3420 ,CDL_DENOM_RAW_COST ,CDL_DENOM_CURRENCY_CODE ,CDL_ACCT_RAW_COST
3421 ,CDL_ACCT_CURRENCY_CODE ,CDL_PROJECT_RAW_COST ,CDL_PROJECT_CURRENCY_CODE
3422 ,BURDEN_SUM_SOURCE_RUN_ID ,BURDEN_SUM_REJECTION_CODE ,SYSTEM_REFERENCE1
3423 ,DENOM_CURRENCY_CODE ,ACCT_CURRENCY_CODE ,PROJECT_CURRENCY_CODE
3424 ,PROJFUNC_CURRENCY_CODE ,BILLABLE_FLAG ,REQUEST_ID
3425 ,ADJUSTMENT_TYPE ,JOB_ID ,NON_LABOR_RESOURCE
3426 ,NON_LABOR_RESOURCE_ORGN_ID ,WIP_RESOURCE_ID ,INCURRED_BY_PERSON_ID
3427 ,INVENTORY_ITEM_ID
3428 ,ORG_ID ,ACCT_RATE_DATE ,ACCT_RATE_TYPE
3429 ,ACCT_EXCHANGE_RATE ,PROJECT_RATE_DATE ,PROJECT_RATE_TYPE
3430 ,PROJECT_EXCHANGE_RATE ,PROJFUNC_COST_RATE_DATE ,PROJFUNC_COST_RATE_TYPE
3431 ,PROJFUNC_COST_EXCHANGE_RATE
3432 ,CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
3433 )
3434 (
3435 select /*+ ordered INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) INDEX(ei,PA_EXPENDITURE_ITEMS_U1) */
3436 cdl.project_id
3437 ,cdl.task_id
3438 ,nvl(ei.override_to_organization_id,e.incurred_by_organization_id)
3439 ,cdl.pa_date
3440 ,decode(cdl.prev_ind_compiled_set_id, null, cdl.pa_period_name
3441 ,nvl(pa_utils2.get_pa_period_name(ei.expenditure_item_date, ei.org_id), cdl.pa_period_name))
3442 , ei.attribute1 , ei.attribute2 , ei.attribute3 , ei.attribute4
3443 , ei.attribute5 , ei.attribute6 , ei.attribute7
3444 , ei.attribute8 , ei.attribute9 , ei.attribute10
3445 , ei.attribute_category , e.person_type , ei.po_line_id
3446 , ei.system_linkage_function , ei.expenditure_type , cdl.ind_compiled_set_id
3447 , cdl.prev_ind_compiled_set_id , ei.expenditure_item_id , cdl.line_num
3448 , decode(NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N'),'N',nvl(ei.prvdr_accrual_date,ei.expenditure_item_date),ei.expenditure_item_date)
3449 , cdl.amount , cdl.projfunc_currency_code
3450 , cdl.denom_raw_cost , cdl.denom_currency_code , cdl.acct_raw_cost
3451 , cdl.acct_currency_code , cdl.project_raw_cost , cdl.project_currency_code
3452 , cdl.burden_sum_source_run_id , cdl.burden_sum_rejection_code , cdl.system_reference1
3453 , ei.denom_currency_code , ei.acct_currency_code , ei.project_currency_code
3454 , ei.projfunc_currency_code , cdl.billable_flag , cdl.request_id
3455 , DECODE(ei.adjustment_type, 'BURDEN_RESUMMARIZE'
3456 , DECODE(ei.system_linkage_function, 'VI', ei.adjustment_type
3457 , DECODE(ei.po_line_id, NULL, NULL, ei.adjustment_type)), NULL) adjustment_type
3458 , ei.job_id , ei.non_labor_resource
3459 , ei.organization_id NON_LABOR_RESOURCE_ORGN_ID , ei.wip_resource_id , e.incurred_by_person_id
3460 , ei.inventory_item_id
3461 , ei.org_id ,CDL.ACCT_RATE_DATE ,CDL.ACCT_RATE_TYPE
3462 ,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
3463 ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
3464 ,CDL.PROJFUNC_COST_EXCHANGE_RATE
3465 ,CDL.CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
3466 FROM
3467 PA_COST_DISTRIBUTION_LINES_ALL CDL,
3468 PA_EXPENDITURE_ITEMS EI,
3469 PA_EXPENDITURES_ALL E
3470 WHERE cdl.burden_sum_source_run_id = p_current_run_id
3471 AND cdl.project_id = p_project_id
3472 AND cdl.expenditure_item_id = ei.expenditure_item_id
3473 AND cdl.line_type = 'R'
3474 AND ei.expenditure_id = e.expenditure_id
3475 AND nvl(ei.adjustment_type,'-999') <> 'BURDEN_RESUMMARIZE' /*Bug# 6449677*/
3476 AND cdl.burden_sum_rejection_code is NULL
3477 AND cdl.prev_ind_compiled_set_id IS NULL
3478 AND ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
3479 AND ( ei.transaction_source IS NULL or
3480 --pa_utils2.get_ts_allow_burden_flag(ei.transaction_source)<>'Y' ); modified as part of 12331962
3481 'Y' <> ( SELECT allow_burden_flag
3482 FROM pa_transaction_sources pts
3483 WHERE pts.transaction_source = ei.transaction_source
3484 )
3485 )
3486 );
3487
3488
3489
3490
3491 IF SQL%ROWCOUNT > 0 THEN
3492
3493 /* The sub query should return only one row, if not we have to use distinct clause*/
3494 UPDATE PA_EI_CDL_CM_GTEMP ei
3495 set (COST_PLUS_STRUCTURE ,COST_BASE) =
3496 (select /*+ ORDERED */ distinct
3497 cbcc.cost_plus_structure, cbcc.cost_base
3498 from
3499 PA_COST_BASE_EXP_TYPES CBET,
3500 PA_COMPILED_MULTIPLIERS CM,
3501 PA_COST_BASE_COST_CODES CBCC
3502 WHERE cbet.expenditure_type = ei.ei_expenditure_type
3503 AND cbet.cost_base_type = 'INDIRECT COST'
3504 AND cm.ind_Compiled_set_id = ei.ind_compiled_set_id
3505 AND cm.cost_base = cbet.cost_base
3506 AND cbcc.cost_base_cost_code_id = cm.cost_base_cost_code_id
3507 AND cbcc.ind_cost_code = cm.ind_cost_code
3508 AND cbcc.cost_base = cm.cost_base
3509 AND cbcc.cost_base_type = 'INDIRECT COST'
3510 AND cbcc.cost_plus_structure = cbet.cost_plus_structure)
3511 where ei.cost_base is null
3512 and ei.prev_ind_compiled_set_id is null
3513 AND ei.burden_sum_source_run_id = p_current_run_id
3514 AND ei.project_id = p_project_id;
3515
3516 END IF;
3517
3518 end populate_gtemp;
3519
3520 /* Introduced this Procedure for updating CDL records with rejection code 'BCC_EXP_TYPE_NULL'
3521 in case if there is no icc_expenditure_type defined for an indirect cost code used in structure
3522 that is attached to the burden schedule*/
3523
3524 PROCEDURE update_gtemp(l_request_id number) IS
3525
3526 l_eiid typ_tbl_eiid;
3527 l_linenum typ_tbl_cdlln; /*added for the bug#5949107*/
3528
3529 BEGIN
3530
3531 l_eiid.delete;
3532 l_linenum.delete; /*added for the bug#5949107*/
3533 UPDATE PA_EI_CDL_CM_GTEMP gtemp
3534 set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
3535 WHERE BURDEN_SUM_REJECTION_CODE IS NULL
3536 and exists ( select 1 from pa_ind_cost_codes icc, pa_cost_base_cost_codes cbcc
3537 where cbcc.cost_plus_structure = gtemp.cost_plus_structure
3538 and cbcc.cost_base = gtemp.cost_base
3539 and cbcc.cost_base_type = 'INDIRECT COST'
3540 and cbcc.ind_cost_code = icc.ind_cost_code
3541 and icc.expenditure_type is NULL )
3542 returning expenditure_Item_id,line_num bulk collect into l_eiid,l_linenum; /*l_linenum is added for the bug#5949107*/
3543
3544 If l_eiid.count > 0 THEN
3545
3546 FORALL I in 1..l_eiid.count
3547 UPDATE PA_COST_DISTRIBUTION_LINES
3548 set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
3549 where expenditure_item_id = l_eiid(i)
3550 and line_num = l_linenum(i); /*added for the bug#5949107*/
3551
3552 End If;
3553
3554
3555 END update_gtemp;
3556
3557 end PA_BURDEN_COSTING;