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