[Home] [Help]
PACKAGE BODY: APPS.PA_FI_AMT_CALC_PKG
Source
1 PACKAGE BODY Pa_Fi_Amt_Calc_Pkg as
2 /* $Header: PAFICALB.pls 120.5.12010000.3 2009/02/16 10:40:13 amehrotr ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE Calculate_Fcst_Amounts_Wrap
7 (
8 errbuff OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10 p_run_mode IN VARCHAR2 default 'I',
11 p_select_criteria IN VARCHAR2 default '00',
12 p_project_flag IN VARCHAR2 default NULL,
13 p_project_id IN NUMBER default NULL,
14 p_assignment_id IN NUMBER default NULL,
15 P_ORGANIZATION_FLAG IN VARCHAR2 default NULL,
16 p_organization_id IN NUMBER default NULL,
17 P_Start_Organization_Flag IN VARCHAR2 default NULL,
18 p_start_organization_id IN NUMBER default NULL,
19 p_debug_mode IN VARCHAR2 default 'N',
20 p_gen_report_flag IN VARCHAR2 default 'N'
21 ) IS
22
23 CURSOR Org_Hierarchy(c_organization_id NUMBER) IS
24 SELECT
25 Org.CHILD_ORGANIZATION_ID organization_id
26 FROM
27 pa_org_hierarchy_denorm org,
28 pa_implementations imp
29 WHERE Org.PA_ORG_USE_TYPE='REPORTING' and
30 Org.PARENT_ORGANIZATION_ID=c_organization_Id and
31 /* Bug fix: 4367847 NVL(org.ORG_ID,-99)=NVL(imp.ORG_ID,-99) and */
32 org.ORG_ID = imp.ORG_ID and
33 Org.ORG_HIERARCHY_VERSION_ID=imp.ORG_STRUCTURE_VERSION_ID
34 ORDER BY
35 Org.CHILD_ORGANIZATION_ID;
36
37 l_errbuff VARCHAR2(500);
38 l_retcode VARCHAR2(100);
39 l_rpt_request_id NUMBER;
40 l_excep_org_id NUMBER;
41 l_current_org_id NUMBER;
42 BEGIN
43
44 /* Bug fix:4329035 */
45 IF P_PA_DEBUG_MODE = 'Y' THEN
46 PA_DEBUG.Set_Curr_Function( p_function => 'FI Amt Wrap Pkg:',
47 p_debug_mode => p_debug_mode );
48 PA_DEBUG.g_err_stage := 'Parameters :';
49 END IF;
50 IF P_PA_DEBUG_MODE = 'Y' THEN
51 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
52 PA_DEBUG.g_err_stage := 'Run Mode :'||p_run_mode;
53 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
54 PA_DEBUG.g_err_stage := 'Select Criteria :'||p_select_criteria;
55 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
56 PA_DEBUG.g_err_stage := 'Project Id :'||NVL(p_project_id,-99);
57 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58 PA_DEBUG.g_err_stage := 'Assignment Id :'||NVL(p_assignment_id,-99);
59 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60 PA_DEBUG.g_err_stage := 'Organization Id :'||NVL(p_organization_id,-99);
61 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62 PA_DEBUG.g_err_stage := 'Start Organization Id :'||NVL(p_start_organization_id,-99);
63 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
64 PA_DEBUG.g_err_stage := 'Generate Report Flag :'||NVL(p_gen_Report_flag,'N');
65 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
66 END IF;
67
68 /* Bug fix: 4367847 */
69 SELECT NVL(org_id,-99) INTO
70 l_excep_org_id FROM PA_IMPLEMENTATIONS;
71
72 If P_PA_DEBUG_MODE = 'Y' THEN
73 PA_DEBUG.Log_Message(p_message => 'l_excep_org_id => :'||l_excep_org_id);
74 PA_DEBUG.Log_Message(p_message => 'Calling MO_GLOBAL.INITapi');
75 End If;
76 MO_GLOBAL.INIT('PA');
77 If NVL(l_excep_org_id,-99) = -99 Then
78 l_excep_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID;
79 End If;
80 If P_PA_DEBUG_MODE = 'Y' THEN
81 PA_DEBUG.Log_Message(p_message => 'Calling MO_GLOBAL.SET_POLICY_CONTEXT');
82 End If;
83 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_excep_org_id);
84 /* end of bug fix:4367847 */
85
86 IF p_run_mode IN ( 'F' , 'I' ) OR
87 ( p_run_mode = 'P' AND p_select_criteria IN ( '02' , '01' ) ) THEN
88
89 IF P_PA_DEBUG_MODE = 'Y' THEN
90 PA_DEBUG.g_err_stage := 'Inside Full or Incremental call or specific Org call';
91 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
92 END IF;
93
94 Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts
95 (
96 errbuff => l_errbuff,
97 retcode => l_retcode,
98 p_run_mode => p_run_mode,
99 p_select_criteria => p_select_criteria,
100 p_project_id => p_project_id,
101 p_assignment_id => p_assignment_id,
102 p_organization_id => p_organization_id,
103 p_debug_mode => p_debug_mode );
104 ELSIF p_run_mode = 'P' AND p_select_criteria = '03' THEN
105 IF P_PA_DEBUG_MODE = 'Y' THEN
106 PA_DEBUG.g_err_stage := 'Inside Org hierarchy call';
107 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
108 END IF;
109
110 FOR org_rec IN org_hierarchy(p_start_organization_id) LOOP
111
112 IF P_PA_DEBUG_MODE = 'Y' THEN
113 PA_DEBUG.g_err_stage := 'Processing Organizaion Id : '||
114 org_rec.organization_id;
115 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
116 END IF;
117
118
119 Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts
120 (
121 errbuff => l_errbuff,
122 retcode => l_retcode,
123 p_run_mode => p_run_mode,
124 p_select_criteria => p_select_criteria,
125 p_project_id => p_project_id,
126 p_assignment_id => p_assignment_id,
127 p_organization_id => Org_Rec.organization_id,
128 p_debug_mode => p_debug_mode );
129 END LOOP;
130 END IF;
131 retcode := l_retcode;
132 IF NVL(p_gen_report_flag,'N' ) = 'Y' AND p_run_mode <> 'I' THEN
133 IF P_PA_DEBUG_MODE = 'Y' THEN
134 PA_DEBUG.g_err_stage := 'Before calling the Exception Report';
135 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
136 END IF;
137
138
139 SELECT NVL(org_id,-99) INTO
140 l_excep_org_id FROM PA_IMPLEMENTATIONS;
141
142 /* Bug fix: 4367847 */
143 If P_PA_DEBUG_MODE = 'Y' THEN
144 PA_DEBUG.Log_Message(p_message => 'Calling FND_REQUEST.set_org_id{'||l_excep_org_id||'}');
145 End If;
146 FND_REQUEST.set_org_id(l_excep_org_id);
147 /* end of Bug fix: 4367847 */
148
149 l_rpt_request_id := FND_REQUEST.submit_request
150 (application => 'PA',
151 program => 'PAFPEXRP',
152 description => 'PRC: List Organization Forecast Exceptions',
153 start_time => NULL,
154 sub_request => false,
155 argument1 => l_excep_org_id,
156 argument2 => p_select_criteria,
157 argument3 => p_project_flag,
158 argument4 => p_project_id,
159 argument5 => p_assignment_id,
160 argument6 => P_ORGANIZATION_FLAG,
161 argument7 => p_organization_id,
162 argument8 => P_Start_Organization_Flag,
163 argument9 => p_start_organization_id);
164
165 IF l_rpt_request_id = 0 then
166 IF P_PA_DEBUG_MODE = 'Y' THEN
167 PA_DEBUG.g_err_stage := 'Error while submitting Report [PAFPEXRP]';
168 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
169 END IF;
170 retcode := FND_API.G_RET_STS_ERROR;
171 ELSE
172 IF P_PA_DEBUG_MODE = 'Y' THEN
173 PA_DEBUG.g_err_stage := 'Exception Report Request Id : ' ||
174 LTRIM(TO_CHAR(l_rpt_request_id )) ;
175 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
176 p_write_file => 'OUT',
177 p_write_mode => 1);
178 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
179 END IF;
180 END IF;
181 END IF;
182 /* Bug fix:4329035 */
183 IF P_PA_DEBUG_MODE = 'Y' THEN
184 PA_DEBUG.Reset_Curr_Function;
185 END IF;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 /* Bug fix:4329035 */
190 IF P_PA_DEBUG_MODE = 'Y' THEN
191 PA_DEBUG.Reset_Curr_Function;
192 END IF;
193 RAISE;
194 END Calculate_Fcst_Amounts_Wrap;
195
196
197
198
199
200 PROCEDURE Calculate_Fcst_Amounts
201 (
202 errbuff OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
203 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
204 p_run_mode IN VARCHAR2 default 'I',
205 p_select_criteria IN VARCHAR2 default '00',
206 p_project_id IN NUMBER default NULL,
207 p_assignment_id IN NUMBER default NULL,
208 p_organization_id IN NUMBER default NULL,
209 p_debug_mode IN VARCHAR2 default 'N'
210 ) IS
211 l_count NUMBER := 5;
212 CURSOR fcst_item_All(c_start_date DATE) IS SELECT
213 forecast_item_id,
214 forecast_item_type,
215 EXPENDITURE_ORG_ID,
216 EXPENDITURE_ORGANIZATION_ID,
217 PROJECT_ORG_ID,
218 PROJECT_ORGANIZATION_ID,
219 PROJECT_ID,
220 PROJECT_TYPE_CLASS,
221 PERSON_ID,
222 RESOURCE_ID,
223 NVL(ASSIGNMENT_ID,-9999),
224 ITEM_DATE,
225 ITEM_UOM,
226 ITEM_QUANTITY,
227 PVDR_PA_PERIOD_NAME,
228 RCVR_PA_PERIOD_NAME,
229 EXPENDITURE_TYPE,
230 EXPENDITURE_TYPE_CLASS,
231 Tp_Amount_Type,
232 Delete_Flag
233 FROM
234 Pa_Forecast_Items
235 WHERE Error_Flag = 'N' AND Item_Date >= c_start_date
236 ORDER BY PROJECT_ID,ASSIGNMENT_ID;
237
238 CURSOR fcst_item_Inc IS SELECT
239 forecast_item_id,
240 forecast_item_type,
241 EXPENDITURE_ORG_ID,
242 EXPENDITURE_ORGANIZATION_ID,
243 PROJECT_ORG_ID,
244 PROJECT_ORGANIZATION_ID,
245 PROJECT_ID,
246 PROJECT_TYPE_CLASS,
247 PERSON_ID,
248 RESOURCE_ID,
249 NVL(ASSIGNMENT_ID,-9999),
250 ITEM_DATE,
251 ITEM_UOM,
252 ITEM_QUANTITY,
253 PVDR_PA_PERIOD_NAME,
254 RCVR_PA_PERIOD_NAME,
255 EXPENDITURE_TYPE,
256 EXPENDITURE_TYPE_CLASS,
257 Tp_Amount_Type,
258 Delete_Flag
259 FROM
260 Pa_Forecast_Items WHERE
261 forecast_amt_calc_flag = 'N' AND
262 Error_Flag = 'N'
263 ORDER BY PROJECT_ID,ASSIGNMENT_ID;
264
265 CURSOR fcst_item_Prj(c_project_id NUMBER) IS SELECT
266 forecast_item_id,
267 forecast_item_type,
268 EXPENDITURE_ORG_ID,
269 EXPENDITURE_ORGANIZATION_ID,
270 PROJECT_ORG_ID,
271 PROJECT_ORGANIZATION_ID,
272 PROJECT_ID,
273 PROJECT_TYPE_CLASS,
274 PERSON_ID,
275 RESOURCE_ID,
276 NVL(ASSIGNMENT_ID,-9999),
277 ITEM_DATE,
278 ITEM_UOM,
279 ITEM_QUANTITY,
280 PVDR_PA_PERIOD_NAME,
281 RCVR_PA_PERIOD_NAME,
282 EXPENDITURE_TYPE,
283 EXPENDITURE_TYPE_CLASS,
284 Tp_Amount_Type,
285 Delete_Flag
286 FROM
287 Pa_Forecast_Items WHERE
288 Project_Id = c_project_id AND
289 Error_Flag = 'N'
290 ORDER BY PROJECT_ID,ASSIGNMENT_ID;
291
292 CURSOR fcst_item_Prj_Asg(c_project_id NUMBER,
293 c_assignment_id NUMBER) IS SELECT
294 forecast_item_id,
295 forecast_item_type,
296 EXPENDITURE_ORG_ID,
297 EXPENDITURE_ORGANIZATION_ID,
298 PROJECT_ORG_ID,
299 PROJECT_ORGANIZATION_ID,
300 PROJECT_ID,
301 PROJECT_TYPE_CLASS,
302 PERSON_ID,
303 RESOURCE_ID,
304 NVL(ASSIGNMENT_ID,-9999),
305 ITEM_DATE,
306 ITEM_UOM,
307 ITEM_QUANTITY,
308 PVDR_PA_PERIOD_NAME,
309 RCVR_PA_PERIOD_NAME,
310 EXPENDITURE_TYPE,
311 EXPENDITURE_TYPE_CLASS,
312 Tp_Amount_Type,
313 Delete_Flag
314 FROM
315 Pa_Forecast_Items WHERE
316 Project_Id = c_project_id AND
317 Assignment_Id = c_assignment_id AND
318 Error_Flag = 'N';
319
320 CURSOR fcst_item_Organization(c_organization_id NUMBER) IS
321 SELECT
322 forecast_item_id,
323 forecast_item_type,
324 EXPENDITURE_ORG_ID,
325 EXPENDITURE_ORGANIZATION_ID,
326 PROJECT_ORG_ID,
327 PROJECT_ORGANIZATION_ID,
328 PROJECT_ID,
329 PROJECT_TYPE_CLASS,
330 PERSON_ID,
331 RESOURCE_ID,
332 Assignment_Id,
333 ITEM_DATE,
334 ITEM_UOM,
335 ITEM_QUANTITY,
336 PVDR_PA_PERIOD_NAME,
337 RCVR_PA_PERIOD_NAME,
338 EXPENDITURE_TYPE,
339 EXPENDITURE_TYPE_CLASS,
340 Tp_Amount_Type,
341 Delete_Flag
342 FROM
343 (
344 SELECT
345 forecast_item_id,
346 forecast_item_type,
347 EXPENDITURE_ORG_ID,
348 EXPENDITURE_ORGANIZATION_ID,
349 PROJECT_ORG_ID,
350 PROJECT_ORGANIZATION_ID,
351 PROJECT_ID,
352 PROJECT_TYPE_CLASS,
353 PERSON_ID,
354 RESOURCE_ID,
355 NVL(ASSIGNMENT_ID,-9999) Assignment_Id,
356 ITEM_DATE,
357 ITEM_UOM,
358 ITEM_QUANTITY,
359 PVDR_PA_PERIOD_NAME,
360 RCVR_PA_PERIOD_NAME,
361 EXPENDITURE_TYPE,
362 EXPENDITURE_TYPE_CLASS,
363 Tp_Amount_Type,
364 Delete_Flag
365 FROM
366 Pa_Forecast_Items WHERE
367 EXPENDITURE_ORGANIZATION_ID = c_organization_id AND
368 Error_Flag = 'N'
369 UNION
370 SELECT
371 forecast_item_id,
372 forecast_item_type,
373 EXPENDITURE_ORG_ID,
374 EXPENDITURE_ORGANIZATION_ID,
375 PROJECT_ORG_ID,
376 PROJECT_ORGANIZATION_ID,
377 PROJECT_ID,
378 PROJECT_TYPE_CLASS,
379 PERSON_ID,
380 RESOURCE_ID,
381 NVL(ASSIGNMENT_ID,-9999) ASSIGNMENT_ID,
382 ITEM_DATE,
383 ITEM_UOM,
384 ITEM_QUANTITY,
385 PVDR_PA_PERIOD_NAME,
386 RCVR_PA_PERIOD_NAME,
387 EXPENDITURE_TYPE,
388 EXPENDITURE_TYPE_CLASS,
389 Tp_Amount_Type,
390 Delete_Flag
391 FROM
392 Pa_Forecast_Items WHERE
393 PROJECT_ORGANIZATION_ID = c_organization_id AND
394 Error_Flag = 'N' ) DUAL
395 ORDER BY Project_Id,Assignment_Id;
396
397 CURSOR Fcst_Options IS
398 SELECT
399 NVL(imp.Org_Id,-99) Org_Id,
400 NVL(fcst.ORG_FCST_PERIOD_TYPE,'AaBb') ORG_FCST_PERIOD_TYPE,
401 fcst.START_PERIOD_NAME START_PERIOD_NAME,
402 imp.Pa_Period_Type Pa_Period_Type,
403 sob.PERIOD_SET_NAME PERIOD_SET_NAME,
404 sob.ACCOUNTED_PERIOD_TYPE ACCOUNTED_PERIOD_TYPE
405 FROM Pa_Forecasting_Options_All fcst,
406 Pa_Implementations_All imp,
407 Gl_Sets_Of_Books sob
408 WHERE /* Bug fix:4367847 NVL(imp.Org_Id,-99) = NVL(fcst.Org_Id,-99) AND */
409 imp.Org_Id = NVL(fcst.Org_Id,-99) AND
410 sob.SET_OF_BOOKS_ID = imp.SET_OF_BOOKS_ID;
411
412 /* Added this cursor for bug 3051110 */
413
414 CURSOR Cur_Assignments(c_request_id NUMBER) IS
415 Select distinct assignment_id from PA_FORECAST_ITEMS
416 Where request_id = c_request_id and Forecast_amt_calc_flag = 'Y'
417 and assignment_id is NOT NULL;
418
419 l_fi_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
420 l_fi_item_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
421 l_fi_exp_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
422 l_fi_exp_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
423 l_fi_proj_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
424 l_fi_proj_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
425 l_fi_projid_tab PA_PLSQL_DATATYPES.IdTabTyp;
426 l_fi_proj_type_class_tab PA_PLSQL_DATATYPES.Char30TabTyp;
427 l_fi_personid_tab PA_PLSQL_DATATYPES.IdTabTyp;
428 l_fi_resid_tab PA_PLSQL_DATATYPES.IdTabTyp;
429 l_fi_asgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
430 l_fi_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
431 l_fi_uom_tab PA_PLSQL_DATATYPES.Char30TabTyp;
432 l_fi_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
433 l_fi_pvdr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
434 l_fi_rcvr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
435 l_fi_exptype_tab PA_PLSQL_DATATYPES.Char30TabTyp;
436 l_fi_exptypeclass_tab PA_PLSQL_DATATYPES.Char30TabTyp;
437 l_fi_amount_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
438 l_fi_rev_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
439 l_fi_cst_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
440 l_fi_bd_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
441 l_fi_others_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
442 l_fi_tp_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
443 l_fi_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
444
445 l_fi_process_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
446 l_line_num Pa_Forecast_Item_Details.Line_Num%TYPE;
447 l_temp_line_num Pa_Forecast_Item_Details.Line_Num%TYPE;
448
449 l_prev_fi_pvdr_papd Pa_Forecast_Items.PVDR_PA_PERIOD_NAME%TYPE;
450
451 l_prev_project_id NUMBER;
452 l_prev_asg_id NUMBER;
453 l_prev_proj_orgid NUMBER;
454 l_prev_exp_type Pa_Expenditure_Types.EXPENDITURE_TYPE%TYPE;
455 /* Project Info */
456 l_prj_type Pa_Projects_All.Project_Type%TYPE;
457 l_distribution_rule Pa_Projects_All.Distribution_Rule%TYPE;
458 l_bill_job_group_id Pa_Projects_All.Bill_Job_Group_Id%TYPE;
459 l_cost_job_group_id Pa_Projects_All.Cost_Job_Group_Id%TYPE;
460 l_job_bill_rate_sch_id Pa_Projects_All.JOB_BILL_RATE_SCHEDULE_ID%TYPE;
461 l_emp_bill_rate_sch_id Pa_Projects_All.EMP_BILL_RATE_SCHEDULE_ID%TYPE;
462 l_prj_curr_code Pa_Projects_All.PROJECT_CURRENCY_CODE%TYPE;
463 l_prj_rate_date Pa_Projects_All.PROJECT_RATE_DATE%TYPE;
464 l_prj_rate_type Pa_Projects_All.PROJECT_RATE_TYPE%TYPE;
465 l_prj_bil_rate_date_code Pa_Projects_All.PROJECT_BIL_RATE_DATE_CODE%TYPE;
466 l_prj_bil_rate_type Pa_Projects_All.PROJECT_BIL_RATE_TYPE%TYPE;
467 l_prj_bil_rate_date Pa_Projects_All.PROJECT_BIL_RATE_DATE%TYPE;
468 l_prj_bil_ex_rate Pa_Projects_All.PROJECT_BIL_EXCHANGE_RATE%TYPE;
469 l_prjfunc_curr_code Pa_Projects_All.PROJFUNC_CURRENCY_CODE%TYPE;
470 l_prjfunc_cost_rate_type Pa_Projects_All.PROJFUNC_COST_RATE_TYPE%TYPE;
471 l_prjfunc_cost_rate_date Pa_Projects_All.PROJFUNC_COST_RATE_DATE%TYPE;
472 l_prjfunc_bil_rate_date_code Pa_Projects_All.PROJFUNC_BIL_RATE_DATE_CODE%TYPE;
473 l_prjfunc_bil_rate_type Pa_Projects_All.PROJFUNC_BIL_RATE_TYPE%TYPE;
474 l_prjfunc_bil_rate_date Pa_Projects_All.PROJFUNC_BIL_RATE_DATE%TYPE;
475 l_prjfunc_bil_ex_rate Pa_Projects_All.PROJFUNC_BIL_EXCHANGE_RATE%TYPE;
476 l_labor_tp_schedule_id Pa_Projects_All.LABOR_TP_SCHEDULE_ID%TYPE;
477 l_labor_tp_fixed_date Pa_Projects_All.LABOR_TP_FIXED_DATE%TYPE;
478 l_fcst_cost_rate_schid pa_forecasting_options_All.JOB_COST_RATE_SCHEDULE_ID%TYPE;
479
480 l_labor_sch_discount Pa_Projects_All.LABOR_SCHEDULE_DISCOUNT%TYPE;
481 l_asg_precedes_task Pa_Projects_All.ASSIGN_PRECEDES_TASK%TYPE;
482 l_labor_bill_rate_orgid Pa_Projects_All.LABOR_BILL_RATE_ORG_ID%TYPE;
483 l_labor_std_bill_rate_sch Pa_Projects_All.LABOR_STD_BILL_RATE_SCHDL%TYPE;
484 l_labor_sch_fixed_dt Pa_Projects_All.LABOR_SCHEDULE_FIXED_DATE%TYPE;
485 l_labor_sch_type Pa_Projects_All.LABOR_SCH_TYPE%TYPE;
486
487 l_chk number := 0;
488
489 /* Project PL SQL table for Rate API */
490
491 l_prj_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
492 l_distribution_rule_tab PA_PLSQL_DATATYPES.Char30TabTyp;
493 l_bill_job_group_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
494 l_cost_job_group_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
495 l_job_bill_rate_sch_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
496 l_emp_bill_rate_sch_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
497 l_prj_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
498 l_prj_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
499 l_prj_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
500 l_prj_bil_rate_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
501 l_prj_bil_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
502 l_prj_bil_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
503 l_prj_bil_ex_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
504 l_prjfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
505 l_prjfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
506 l_prjfunc_cost_rt_dt_tab PA_PLSQL_DATATYPES.DateTabTyp;
507 l_prjfunc_bil_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
508 l_prjfunc_bil_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
509 l_prjfunc_bil_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
510 l_prjfunc_bil_ex_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
511 l_prj_cost_rate_schid_tab PA_PLSQL_DATATYPES.IdTabTyp;
512
513 l_labor_sch_discount_tab PA_PLSQL_DATATYPES.NumTabTyp;
514 l_asg_precedes_task_tab PA_PLSQL_DATATYPES.Char1TabTyp;
515 l_labor_bill_rate_orgid_tab PA_PLSQL_DATATYPES.NumTabTyp;
516 l_labor_std_bill_rate_sch_tab PA_PLSQL_DATATYPES.Char20TabTyp;
517 l_labor_sch_fixed_dt_tab PA_PLSQL_DATATYPES.DateTabTyp;
518 l_labor_sch_type_tab PA_PLSQL_DATATYPES.Char1TabTyp;
519
520
521 /* Project Assignment info */
522
523 l_asg_fcst_job_id Pa_Project_Assignments.Fcst_Job_Id%TYPE;
524 l_asg_fcst_job_group_id Pa_Project_Assignments.Fcst_Job_Group_Id%TYPE;
525 l_asg_project_role_id Pa_Project_Assignments.Project_Role_Id%TYPE;
526 l_asg_markup_percent Pa_Project_Assignments.Markup_Percent%TYPE;
527 l_asg_bill_rate_override Pa_Project_Assignments.Bill_Rate_Override%TYPE;
528 l_asg_bill_rate_curr_override Pa_Project_Assignments.BILL_RATE_CURR_OVERRIDE%TYPE;
529 l_asg_markup_percent_override Pa_Project_Assignments.MARKUP_PERCENT_OVERRIDE%TYPE;
530 l_asg_tp_rate_override Pa_Project_Assignments.TP_RATE_OVERRIDE%TYPE;
531 l_asg_tp_curr_override Pa_Project_Assignments.TP_CURRENCY_OVERRIDE%TYPE;
532 l_asg_tp_calc_base_code_ovr Pa_Project_Assignments.TP_CALC_BASE_CODE_OVERRIDE%TYPE;
533 l_asg_tp_percent_applied_ovr Pa_Project_Assignments.TP_PERCENT_APPLIED_OVERRIDE%TYPE;
534 l_prj_assignment_type PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE;
535 l_prj_status_code PA_PROJECT_ASSIGNMENTS.STATUS_CODE%TYPE;
536
537 /* Project Assignments PL SQL Tables */
538
539 l_asg_fcst_jobid_tab PA_PLSQL_DATATYPES.IdTabTyp;
540 l_asg_fcst_jobgroupid_tab PA_PLSQL_DATATYPES.IdTabTyp;
541
542 l_cc_sys_link_tab PA_PLSQL_DATATYPES.Char30TabTyp;
543 l_cc_taskid_tab PA_PLSQL_DATATYPES.IdTabTyp;
544 l_cc_expitemid_tab PA_PLSQL_DATATYPES.IdTabTyp;
545 l_cc_transsource_tab PA_PLSQL_DATATYPES.Char30TabTyp;
546 l_cc_NLOrgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
547 l_cc_prvdreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
548 l_cc_recvreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
549 lx_cc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
550 lx_cc_type_tab PA_PLSQL_DATATYPES.Char3TabTyp;
551 lx_cc_code_tab PA_PLSQL_DATATYPES.Char1TabTyp;
552 lx_cc_prvdr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
553 lx_cc_recvr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
554 lx_cc_recvr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
555 lx_cc_prvdr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
556 lx_cc_error_stage VARCHAR2(500);
557 lx_cc_error_code NUMBER;
558
559 l_cc_sys_link Pa_Expenditure_Types.System_Linkage_Function%TYPE;
560 l_cc_exp_category Pa_Expenditure_Types.EXPENDITURE_CATEGORY%TYPE;
561
562 l_tp_asgid PA_PLSQL_DATATYPES.IdTabTyp;
563 l_tp_exp_category PA_PLSQL_DATATYPES.Char30TabTyp;
564 l_tp_exp_itemid PA_PLSQL_DATATYPES.IdTabTyp;
565 l_tp_labor_nl_flag PA_PLSQL_DATATYPES.Char1TabTyp;
566 l_tp_taskid PA_PLSQL_DATATYPES.IdTabTyp;
567 l_tp_scheduleid PA_PLSQL_DATATYPES.IdTabTyp;
568 l_tp_denom_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
569 l_tp_rev_distributed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
570 l_tp_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
571 l_tp_fixed_date PA_PLSQL_DATATYPES.DateTabTyp;
572
573 l_tp_asg_precedes_task_tab PA_PLSQL_DATATYPES.Char1TabTyp; -- Added for bug 3260017
574
575 l_tp_denom_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
576 l_tp_denom_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
577 l_tp_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
578 l_tp_nl_resource PA_PLSQL_DATATYPES.Char20TabTyp;
579 l_tp_nl_resource_orgzid PA_PLSQL_DATATYPES.IdTabTyp;
580 l_tp_pa_date PA_PLSQL_DATATYPES.DateTabTyp;
581
582 lx_proj_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
583 lx_proj_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
584 lx_proj_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
585 lx_proj_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
586
587 lx_projfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
588 lx_projfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
589 lx_projfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
590 lx_projfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
591
592 lx_denom_tp_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
593 lx_denom_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
594
595 lx_expfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
596 lx_expfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
597 lx_expfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
598 lx_expfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
599
600 lx_cc_markup_basecode PA_PLSQL_DATATYPES.Char1TabTyp;
601 lx_tp_ind_compiled_setid PA_PLSQL_DATATYPES.IdTabTyp;
602 lx_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
603 lx_tp_base_amount PA_PLSQL_DATATYPES.NumTabTyp;
604 lx_tp_bill_markup_percent PA_PLSQL_DATATYPES.NumTabTyp;
605 lx_tp_sch_line_percent PA_PLSQL_DATATYPES.NumTabTyp;
606 lx_tp_rule_percent PA_PLSQL_DATATYPES.NumTabTyp;
607 lx_tp_job_id PA_PLSQL_DATATYPES.IdTabTyp;
608 lx_tp_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
609
610 l_tp_array_size NUMBER;
611 l_tp_debug_mode VARCHAR2(30);
612 lx_tp_return_status NUMBER;
613
614
615 /* Rate API */
616
617 l_rt_fi_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
618 l_rt_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
619 l_rt_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
620 l_rt_exp_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
621 l_rt_exp_organization_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
622 l_rt_system_linkage_tab PA_PLSQL_DATATYPES.Char30TabTyp;
623
624 lx_rt_expfunc_raw_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
625 lx_rt_expfunc_raw_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
626 lx_rt_expfunc_bd_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
627 lx_rt_expfunc_bd_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
628
629 lx_rt_proj_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
630 lx_rt_projfunc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
631 lx_rt_proj_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
632 lx_rt_proj_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
633 lx_rt_proj_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
634 lx_rt_proj_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
635 lx_rt_proj_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
636
637 lx_rt_rev_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
638 lx_rt_cst_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
639 lx_rt_bd_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
640 lx_rt_others_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
641
642 lx_rt_error_msg VARCHAR2(1000);
643 lx_rt_return_status VARCHAR2(30);
644 lx_rt_msg_count NUMBER;
645 lx_rt_msg_data VARCHAR2(1000); --Bug 7423839
646
647 /* new parameters for Org Fcst */
648
649 lx_rt_pfunc_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
650
651 l_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
652 l_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
653 l_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
654 l_rt_pfunc_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
655
656
657 lx_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
658 lx_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
659 lx_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
660
661 lx_rt_pfunc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
662 lx_rt_pfunc_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
663 lx_rt_pfunc_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
664 lx_rt_pfunc_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
665
666 l_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
667 l_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
668
669 lx_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
670 lx_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
671 lx_rt_pfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
672
673 l_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
674 l_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
675 l_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
676 l_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
677 l_rt_proj_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
678 l_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
679
680 lx_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
681 lx_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
682 lx_rt_proj_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
683 lx_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
684 lx_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
685 lx_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
686
687 lx_rt_expfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
688 lx_rt_expfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
689 lx_rt_expfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
690 lx_rt_expfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
691
692 lx_rt_cost_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
693 lx_rt_txn_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp ;
694 lx_rt_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
695 lx_rt_txn_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
696 lx_rt_txn_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
697 lx_rt_rev_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
698 lx_rt_txn_rev_bill_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
699 lx_rt_txn_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
700
701
702 /* Forecast Item PL SQL Tables */
703
704 l_fia_cost_txn_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
705 l_fia_rev_txn_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
706 l_fia_txn_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
707 l_fia_txn_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
708 l_fia_txn_revenue PA_PLSQL_DATATYPES.NumTabTyp;
709
710 l_fia_expfunc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
711 l_fia_expfunc_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
712 l_fia_expfunc_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
713
714 l_fia_projfunc_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
715 l_fia_projfunc_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
716 l_fia_projfunc_revenue PA_PLSQL_DATATYPES.NumTabTyp;
717
718 l_fia_proj_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
719 l_fia_proj_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
720 l_fia_proj_revenue PA_PLSQL_DATATYPES.NumTabTyp;
721
722 l_fia_proj_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
723 l_fia_proj_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
724 l_fia_proj_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
725
726 l_fia_proj_rev_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
727 l_fia_proj_rev_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
728 l_fia_proj_rev_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
729
730 l_fia_expfunc_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
731 l_fia_expfunc_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
732 l_fia_expfunc_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
733
734 l_fia_projfunc_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
735 l_fia_projfunc_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
736 l_fia_projfunc_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
737
738 l_fia_projfunc_rev_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
739 l_fia_projfunc_rev_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
740 l_fia_projfunc_rev_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
741
742 /* fid - forecast item amount details PL SQL tables */
743
744 l_fid_cost_txn_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
745 l_fid_rev_txn_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
746 l_fid_txn_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
747 l_fid_txn_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
748 l_fid_txn_revenue PA_PLSQL_DATATYPES.NumTabTyp;
749
750 l_fid_expfunc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
751 l_fid_expfunc_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
752 l_fid_expfunc_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
753
754 l_fid_projfunc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
755 l_fid_projfunc_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
756 l_fid_projfunc_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
757 l_fid_projfunc_revenue PA_PLSQL_DATATYPES.NumTabTyp;
758
759 l_fid_proj_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
760 l_fid_proj_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
761 l_fid_proj_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
762 l_fid_proj_revenue PA_PLSQL_DATATYPES.NumTabTyp;
763
764 l_fid_proj_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
765 l_fid_proj_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
766 l_fid_proj_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
767
768 l_fid_proj_rev_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
769 l_fid_proj_rev_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
770 l_fid_proj_rev_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
771
772 l_fid_expfunc_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
773 l_fid_expfunc_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
774 l_fid_expfunc_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
775
776 l_fid_projfunc_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
777 l_fid_projfunc_cost_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
778 l_fid_projfunc_cost_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
779
780 l_fid_projfunc_rev_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
781 l_fid_projfunc_rev_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
782 l_fid_projfunc_rev_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
783
784 l_fid_fcst_itemid PA_PLSQL_DATATYPES.IdTabTyp;
785 l_fid_line_num PA_PLSQL_DATATYPES.NumTabTyp;
786 l_fid_item_date PA_PLSQL_DATATYPES.DateTabTyp;
787 l_fid_item_uom PA_PLSQL_DATATYPES.Char30TabTyp;
788 l_fid_item_qty PA_PLSQL_DATATYPES.NumTabTyp;
789 l_fid_reversed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
790 l_fid_net_zero_flag PA_PLSQL_DATATYPES.Char1TabTyp;
791 l_fid_line_num_reversed PA_PLSQL_DATATYPES.NumTabTyp;
792
793 l_fid_proj_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
794 l_fid_proj_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
795 l_fid_proj_tp_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
796 l_fid_proj_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
797
798 l_fid_projfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
799 l_fid_projfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
800 l_fid_projfunc_tp_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
801 l_fid_projfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
802
803 l_fid_denom_tp_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
804 l_fid_denom_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
805
806 l_fid_expfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
807 l_fid_expfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
808 l_fid_expfunc_tp_ex_rate PA_PLSQL_DATATYPES.NumTabTyp;
809 l_fid_expfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
810
811 l_fid_upd_fcst_itemid PA_PLSQL_DATATYPES.IdTabTyp;
812 l_fid_upd_line_num PA_PLSQL_DATATYPES.NumTabTyp;
813 l_fid_upd_reversed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
814 l_fid_upd_net_zero_flag PA_PLSQL_DATATYPES.Char1TabTyp;
815
816 l_prev_rt_prj_id Pa_Forecast_Items.Project_Id%TYPE;
817 l_prev_rt_asg_id Pa_Forecast_Items.Assignment_Id%TYPE;
818 l_prev_rt_personid Pa_Forecast_Items.Person_Id%TYPE;
819 l_prev_rt_fi_itemtype Pa_Forecast_Items.Forecast_Item_Type%TYPE;
820 l_prev_rt_fi_proc_flag VARCHAR2(1);
821 l_prev_rt_calling_mode VARCHAR2(30);
822 l_prev_rt_fcst_jobid Pa_Project_Assignments.Fcst_Job_Id%TYPE;
823 l_prev_rt_fcst_jobgroupid Pa_Project_Assignments.Fcst_Job_Group_Id%TYPE;
824 l_prev_rt_exp_type Pa_Forecast_Items.Expenditure_Type%TYPE;
825 l_prev_rt_org_id Pa_Forecast_Items.Expenditure_Org_Id%TYPE;
826 l_prev_rt_prj_type Pa_Projects_All.Project_Type%TYPE;
827 l_prev_rt_projfunc_currcode Pa_Projects_All.Project_Currency_Code%TYPE;
828 l_prev_rt_proj_currcode Pa_Projects_All.Project_Currency_Code%TYPE;
829 l_prev_rt_bill_jobgroup_id Pa_Projects_All.Bill_Job_Group_Id%TYPE;
830 l_prev_rt_emp_bilrate_schid Pa_Projects_All.Emp_Bill_Rate_Schedule_Id%TYPE;
831 l_prev_rt_job_bilrate_schid Pa_Projects_All.Job_Bill_Rate_Schedule_Id%TYPE;
832 l_prev_rt_dist_rule Pa_Projects_All.Distribution_Rule%TYPE;
833 l_prev_rt_cost_rate_schid Pa_Projects_All.Emp_Bill_Rate_Schedule_Id%TYPE;
834
835 l_prev_rt_labor_sch_discount NUMBER;
836 l_prev_rt_asg_precedes_task Pa_Projects_All.ASSIGN_PRECEDES_TASK%TYPE;
837 l_prev_rt_labor_bill_rt_orgid Pa_Projects_All.LABOR_BILL_RATE_ORG_ID%TYPE;
838 l_prev_rt_labor_std_bl_rt_sch Pa_Projects_All.LABOR_STD_BILL_RATE_SCHDL%TYPE;
839 l_prev_rt_labor_sch_fixed_dt Pa_Projects_All.LABOR_SCHEDULE_FIXED_DATE%TYPE;
840 l_prev_rt_labor_sch_type Pa_Projects_All.LABOR_SCH_TYPE%TYPE;
841
842 /* local variables */
843 l_fi_process_flag varchar2(1);
844 l_call_rate_api_flag varchar2(1) := 'N';
845 l_amount_calc_mode VARCHAR2(30);
846 l_rt_index NUMBER;
847 l_temp_last NUMBER;
848 l_fia_index NUMBER;
849 l_fia_upd_index NUMBER;
850 l_fetch_size NUMBER(5) := 200;
851 l_t_LINE_NUM Pa_Fi_Amount_Details.LINE_NUM%TYPE;
852 l_t_ITEM_QUANTITY Pa_Fi_Amount_Details.ITEM_QUANTITY%TYPE;
853 l_t_COST_TXN_CURR_CODE Pa_Fi_Amount_Details.COST_TXN_CURRENCY_CODE%TYPE;
854 l_t_REV_TXN_CURR_CODE Pa_Fi_Amount_Details.REVENUE_TXN_CURRENCY_CODE%TYPE;
855 l_t_TXN_RAW_COST Pa_Fi_Amount_Details.TXN_RAW_COST%TYPE;
856 l_t_TXN_BD_COST Pa_Fi_Amount_Details.TXN_BURDENED_COST%TYPE;
857 l_t_TXN_REVENUE Pa_Fi_Amount_Details.TXN_REVENUE %TYPE;
858 l_t_TXN_TRANSFER_PRICE Pa_Fi_Amount_Details.TXN_TRANSFER_PRICE%TYPE;
859 l_t_TP_TXN_CURR_CODE Pa_Fi_Amount_Details.TP_TXN_CURRENCY_CODE%TYPE;
860 l_t_PROJ_CURR_CODE Pa_Fi_Amount_Details.PROJECT_CURRENCY_CODE%TYPE;
861 l_t_PROJ_RAW_COST Pa_Fi_Amount_Details.PROJECT_RAW_COST%TYPE;
862 l_t_PROJ_BD_COST Pa_Fi_Amount_Details.PROJECT_BURDENED_COST%TYPE;
863 l_t_PROJ_COST_RATE_DATE Pa_Fi_Amount_Details.PROJECT_COST_RATE_DATE%TYPE;
864 l_t_PROJ_COST_RATE_TYPE Pa_Fi_Amount_Details.PROJECT_COST_RATE_TYPE%TYPE;
865 l_t_PROJ_COST_EX_RATE Pa_Fi_Amount_Details.PROJECT_COST_EXCHANGE_RATE%TYPE;
866 l_t_PROJ_REV_RATE_DATE Pa_Fi_Amount_Details.PROJECT_REVENUE_RATE_DATE %TYPE;
867 l_t_PROJ_REV_RATE_TYPE Pa_Fi_Amount_Details.PROJECT_REVENUE_RATE_TYPE%TYPE;
868 l_t_PROJ_REV_EX_RATE Pa_Fi_Amount_Details.PROJECT_REVENUE_EXCHANGE_RATE%TYPE;
869 l_t_PROJ_REVENUE Pa_Fi_Amount_Details.PROJECT_REVENUE%TYPE;
870 l_t_PROJ_TP_RATE_DATE Pa_Fi_Amount_Details.PROJECT_TP_RATE_DATE%TYPE;
871 l_t_PROJ_TP_RATE_TYPE Pa_Fi_Amount_Details.PROJECT_TP_RATE_TYPE%TYPE;
872 l_t_PROJ_TP_EX_RATE Pa_Fi_Amount_Details.PROJECT_TP_EXCHANGE_RATE%TYPE;
873 l_t_PROJ_TRANSFER_PRICE Pa_Fi_Amount_Details.PROJECT_TRANSFER_PRICE%TYPE;
874 l_t_PFUNC_CURR_CODE Pa_Fi_Amount_Details.PROJFUNC_CURRENCY_CODE%TYPE;
875 l_t_PFUNC_COST_RATE_DATE Pa_Fi_Amount_Details.PROJFUNC_COST_RATE_DATE%TYPE;
876 l_t_PFUNC_COST_RATE_TYPE Pa_Fi_Amount_Details.PROJFUNC_COST_RATE_TYPE %TYPE;
877 l_t_PFUNC_COST_EX_RATE Pa_Fi_Amount_Details.PROJFUNC_COST_EXCHANGE_RATE%TYPE;
878 l_t_PFUNC_RAW_COST Pa_Fi_Amount_Details.PROJFUNC_RAW_COST%TYPE;
879 l_t_PFUNC_BD_COST Pa_Fi_Amount_Details.PROJFUNC_RAW_COST%TYPE;
880 l_t_PFUNC_REV_RATE_DATE Pa_Fi_Amount_Details.PROJFUNC_REVENUE_RATE_DATE%TYPE;
881 l_t_PFUNC_REV_RATE_TYPE Pa_Fi_Amount_Details.PROJFUNC_REVENUE_RATE_TYPE%TYPE;
882 l_t_PFUNC_REV_EX_RATE Pa_Fi_Amount_Details.PROJFUNC_REVENUE_EXCHANGE_RATE%TYPE;
883 l_t_PFUNC_REVENUE Pa_Fi_Amount_Details.PROJFUNC_REVENUE%TYPE;
884 l_t_PFUNC_TP_RATE_DATE Pa_Fi_Amount_Details.PROJFUNC_TP_RATE_DATE%TYPE;
885 l_t_PFUNC_TP_RATE_TYPE Pa_Fi_Amount_Details.PROJFUNC_TP_RATE_TYPE%TYPE;
886 l_t_PFUNC_TP_EX_RATE Pa_Fi_Amount_Details.PROJFUNC_TP_EXCHANGE_RATE%TYPE;
887 l_t_PFUNC_TRANSFER_PRICE Pa_Fi_Amount_Details.PROJFUNC_TRANSFER_PRICE%TYPE;
888 l_t_EFUNC_CURR_CODE Pa_Fi_Amount_Details.EXPFUNC_CURRENCY_CODE%TYPE;
889 l_t_EFUNC_COST_RATE_DATE Pa_Fi_Amount_Details.EXPFUNC_COST_RATE_DATE %TYPE;
890 l_t_EFUNC_COST_RATE_TYPE Pa_Fi_Amount_Details.EXPFUNC_COST_RATE_TYPE%TYPE;
891 l_t_EFUNC_COST_EX_RATE Pa_Fi_Amount_Details.EXPFUNC_COST_EXCHANGE_RATE%TYPE;
892 l_t_EFUNC_RAW_COST Pa_Fi_Amount_Details.EXPFUNC_RAW_COST%TYPE;
893 l_t_EFUNC_BD_COST Pa_Fi_Amount_Details.EXPFUNC_BURDENED_COST%TYPE;
894 l_t_EFUNC_TP_RATE_DATE Pa_Fi_Amount_Details.EXPFUNC_TP_RATE_DATE%TYPE;
895 l_t_EFUNC_TP_RATE_TYPE Pa_Fi_Amount_Details.EXPFUNC_TP_RATE_TYPE%TYPE;
896 l_t_EFUNC_TP_EX_RATE Pa_Fi_Amount_Details.EXPFUNC_TP_EXCHANGE_RATE%TYPE;
897 l_t_EFUNC_TRANSFER_PRICE Pa_Fi_Amount_Details.EXPFUNC_TRANSFER_PRICE%TYPE;
898
899 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
900 l_created_by NUMBER := FND_GLOBAL.USER_ID;
901 l_creation_date DATE := SYSDATE;
902 l_last_update_date DATE := l_creation_date;
903 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
904 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
905 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
906 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
907 l_call_tp_api_flag VARCHAR2(1);
908 l_fi_process_start_date DATE;
909 l_curr_process_start_date DATE;
910 l_process_fis_flag VARCHAR2(1);
911 l_asgmt_status_flag VARCHAR2(1);
912
913 /* Variables added for bug 3051110 */
914 l_assignment_id pa_project_assignments.assignment_id%TYPE;
915 l_sum_transfer_price PA_FORECAST_ITEMS.PROJFUNC_TRANSFER_PRICE%TYPE;
916 l_sum_item_quantity PA_FORECAST_ITEMS.ITEM_QUANTITY%TYPE;
917 l_average_transfer_price_rate PA_FORECAST_ITEMS.PROJFUNC_TRANSFER_PRICE%TYPE;
918 l_return_status VARCHAR2(1);
919
920 BEGIN
921 retcode := '0';
922 /* Bug fix:4329035 */
923 IF P_PA_DEBUG_MODE = 'Y' THEN
924 PA_DEBUG.Set_Curr_Function( p_function => 'F I Amt:',
925 p_debug_mode => p_debug_mode );
926 END IF;
927 l_count := l_count + 5;
928 l_chk := 1;
929 l_prev_project_id := -9999991;
930 l_prev_asg_id := -9999991;
931 l_prev_proj_orgid := -9999991;
932 l_prev_exp_type := 'DummY ExP TyPe';
933 l_cc_exp_category := NULL;
934 l_prev_fi_pvdr_papd := NULL;
935 l_tp_debug_mode := p_debug_mode;
936
937 l_asg_fcst_job_id := NULL;
938 l_asg_fcst_job_group_id := NULL;
939 l_asg_project_role_id := NULL;
940 l_prj_assignment_type := NULL;
941 l_prj_status_code := NULL;
942 l_process_fis_flag := 'Y';
943
944 IF p_run_mode = 'F' THEN
945 l_fi_process_start_date := NULL;
946 FOR opt_rec IN Fcst_Options LOOP
947 IF opt_rec.ORG_FCST_PERIOD_TYPE = 'PA' AND
948 opt_rec.START_PERIOD_NAME IS NOT NULL THEN
949 BEGIN
950 SELECT Start_Date INTO l_curr_process_start_date FROM Gl_Periods
951 WHERE Period_Set_Name = opt_rec.Period_Set_Name AND
952 Period_Type = opt_rec.Pa_Period_Type AND
953 Period_Name = opt_rec.START_PERIOD_NAME AND
954 ADJUSTMENT_PERIOD_FLAG = 'N';
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 retcode := '2';
958 /* dbms_output.put_line('inside PA period no data ');
959 dbms_output.put_line('PA Pd Type : Start Pd Name :'||
960 opt_rec.Pa_Period_Type ||' : ' ||opt_rec.START_PERIOD_NAME); */
961 IF P_PA_DEBUG_MODE = 'Y' THEN
962 PA_DEBUG.g_err_stage := 'Org Id :'||opt_rec.Org_Id;
963 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
964 PA_DEBUG.g_err_stage := 'No Data Found In PA Pd:'||opt_rec.Pa_Period_Type;
965 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
966 END IF;
967 RAISE;
968 END;
969 ELSIF opt_rec.ORG_FCST_PERIOD_TYPE = 'GL' AND
970 opt_rec.START_PERIOD_NAME IS NOT NULL THEN
971 BEGIN
972 SELECT Start_Date INTO l_curr_process_start_date FROM Gl_Periods
973 WHERE Period_Set_Name = opt_rec.Period_Set_Name AND
974 Period_Type = opt_rec.Accounted_Period_Type AND
975 Period_Name = opt_rec.START_PERIOD_NAME AND
976 ADJUSTMENT_PERIOD_FLAG = 'N';
977 EXCEPTION
978 WHEN NO_DATA_FOUND THEN
979 /* dbms_output.put_line('inside GL period no data ');
980 dbms_output.put_line('GL Pd Type : Start Pd Name : '||
981 opt_rec.Accounted_Period_Type ||' : ' ||opt_rec.START_PERIOD_NAME); */
982 retcode := '2';
983 IF P_PA_DEBUG_MODE = 'Y' THEN
984 PA_DEBUG.g_err_stage := 'Org Id :'||opt_rec.Org_Id;
985 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
986 PA_DEBUG.g_err_stage := 'No Data Found In GL Pd:'||opt_rec.Accounted_Period_Type;
987 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
988 END IF;
989 RAISE;
990 END;
991 END IF;
992 IF l_fi_process_start_date IS NULL OR
993 ( l_fi_process_start_date IS NOT NULL AND
994 l_curr_process_start_date IS NOT NULL AND
995 l_curr_process_start_date < l_fi_process_start_date ) THEN
996 l_fi_process_start_date := l_curr_process_start_date;
997 END IF;
998 END LOOP;
999 IF l_fi_process_start_date IS NULL THEN
1000 IF P_PA_DEBUG_MODE = 'Y' THEN
1001 PA_DEBUG.g_err_stage := 'No Forecasting Options Setup, returning';
1002 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1003 PA_DEBUG.Reset_Curr_Function;
1004 END IF;
1005 RETURN;
1006 END IF;
1007 IF P_PA_DEBUG_MODE = 'Y' THEN
1008 PA_DEBUG.g_err_stage := 'FI Process Start Date :'||
1009 TO_CHAR(l_fi_process_start_date,'yyyy/mm/dd');
1010 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1011 END IF;
1012 /* dbms_output.put_line('FI Process Start Dt :'||
1013 TO_CHAR(l_fi_process_start_date,'yyyy/mm/dd')); */
1014 OPEN fcst_item_All(l_fi_process_start_date);
1015 IF P_PA_DEBUG_MODE = 'Y' THEN
1016 PA_DEBUG.g_err_stage := 'Opening Fcst_Item_All cursor';
1017 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1018 END IF;
1019 ELSIF p_run_mode = 'I' THEN
1020 IF P_PA_DEBUG_MODE = 'Y' THEN
1021 PA_DEBUG.g_err_stage := 'Opening Fcst_Item_Inc cursor';
1022 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1023 END IF;
1024 OPEN fcst_item_Inc;
1025 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
1026 p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
1027 IF P_PA_DEBUG_MODE = 'Y' THEN
1028 PA_DEBUG.g_err_stage := 'Opening Fcst_Item_Prj cursor';
1029 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1030 END IF;
1031 OPEN fcst_item_Prj(p_project_id);
1032 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
1033 p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
1034 IF P_PA_DEBUG_MODE = 'Y' THEN
1035 PA_DEBUG.g_err_stage := 'Opening Fcst_Item_Prj_Asg cursor';
1036 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1037 END IF;
1038 OPEN fcst_item_Prj_Asg(p_project_id,p_assignment_id);
1039 ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
1040 IF P_PA_DEBUG_MODE = 'Y' THEN
1041 PA_DEBUG.g_err_stage := 'Opening Fcst_Ftem_Organization cursor';
1042 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1043 END IF;
1044 OPEN fcst_item_Organization(p_organization_id);
1045 END IF;
1046 LOOP
1047 l_fi_id_tab.delete;
1048 l_fi_item_type_tab.delete;
1049 l_fi_exp_orgid_tab.delete;
1050 l_fi_exp_organizationid_tab.delete;
1051 l_fi_proj_orgid_tab.delete;
1052 l_fi_proj_organizationid_tab.delete;
1053 l_fi_projid_tab.delete;
1054 l_fi_proj_type_class_tab.delete;
1055 l_fi_personid_tab.delete;
1056 l_fi_resid_tab.delete;
1057 l_fi_asgid_tab.delete;
1058 l_fi_date_tab.delete;
1059 l_fi_uom_tab.delete;
1060 l_fi_qty_tab.delete;
1061 l_fi_pvdr_papd_tab.delete;
1062 l_fi_rcvr_papd_tab.delete;
1063 l_fi_exptype_tab.delete;
1064 l_fi_exptypeclass_tab.delete;
1065 l_fi_amount_type_tab.delete;
1066 l_fi_process_flag_tab.delete;
1067
1068 l_fi_rev_rejct_reason_tab.delete;
1069 l_fi_cst_rejct_reason_tab.delete;
1070 l_fi_bd_rejct_reason_tab.delete;
1071 l_fi_others_rejct_reason_tab.delete;
1072 l_fi_tp_rejct_reason_tab.delete;
1073 l_fi_delete_flag_tab.delete;
1074
1075 l_prj_type_tab.delete;
1076 l_distribution_rule_tab.delete;
1077 l_bill_job_group_id_tab.delete;
1078 l_cost_job_group_id_tab.delete;
1079 l_job_bill_rate_sch_id_tab.delete;
1080 l_emp_bill_rate_sch_id_tab.delete;
1081 l_prj_curr_code_tab.delete;
1082 l_prj_rate_date_tab.delete;
1083 l_prj_rate_type_tab.delete;
1084 l_prj_bil_rate_dt_code_tab.delete;
1085 l_prj_bil_rate_type_tab.delete;
1086 l_prj_bil_rate_date_tab.delete;
1087 l_prj_bil_ex_rate_tab.delete;
1088 l_prjfunc_curr_code_tab.delete;
1089 l_prjfunc_cost_rt_type_tab.delete;
1090 l_prjfunc_cost_rt_dt_tab.delete;
1091 l_prjfunc_bil_rt_dt_code_tab.delete;
1092 l_prjfunc_bil_rate_type_tab.delete;
1093 l_prjfunc_bil_rate_date_tab.delete;
1094 l_prjfunc_bil_ex_rate_tab.delete;
1095
1096 l_labor_sch_discount_tab.delete;
1097 l_asg_precedes_task_tab.delete;
1098 l_labor_bill_rate_orgid_tab.delete;
1099 l_labor_std_bill_rate_sch_tab.delete;
1100 l_labor_sch_fixed_dt_tab.delete;
1101 l_labor_sch_type_tab.delete;
1102
1103 l_prj_cost_rate_schid_tab.delete;
1104
1105 l_asg_fcst_jobid_tab.delete;
1106 l_asg_fcst_jobgroupid_tab.delete;
1107
1108 l_cc_sys_link_tab.delete;
1109 l_cc_taskid_tab.delete;
1110 l_cc_expitemid_tab.delete;
1111 l_cc_transsource_tab.delete;
1112 l_cc_NLOrgzid_tab.delete;
1113 l_cc_prvdreid_tab.delete;
1114 l_cc_recvreid_tab.delete;
1115 lx_cc_status_tab.delete;
1116 lx_cc_type_tab.delete;
1117 lx_cc_code_tab.delete;
1118 lx_cc_prvdr_orgzid_tab.delete;
1119 lx_cc_recvr_orgzid_tab.delete;
1120 lx_cc_recvr_orgid_tab.delete;
1121 lx_cc_prvdr_orgid_tab.delete;
1122
1123 l_tp_exp_category.delete;
1124 l_tp_exp_itemid.delete;
1125 l_tp_labor_nl_flag.delete;
1126 l_tp_taskid.delete;
1127 l_tp_scheduleid.delete;
1128 l_tp_denom_currcode.delete;
1129 l_tp_rev_distributed_flag.delete;
1130 l_tp_compute_flag.delete;
1131 l_tp_fixed_date.delete;
1132 l_tp_denom_raw_cost.delete;
1133 l_tp_denom_bd_cost.delete;
1134 l_tp_raw_revenue.delete;
1135 l_tp_nl_resource.delete;
1136 l_tp_nl_resource_orgzid.delete;
1137 l_tp_pa_date.delete;
1138 l_tp_asg_precedes_task_tab.delete; -- Added for bug 3260017
1139
1140 lx_proj_tp_rate_type.delete;
1141 lx_proj_tp_rate_date.delete;
1142 lx_proj_tp_exchange_rate.delete;
1143 lx_proj_tp_amt.delete;
1144
1145 lx_projfunc_tp_rate_type.delete;
1146 lx_projfunc_tp_rate_date.delete;
1147 lx_projfunc_tp_exchange_rate.delete;
1148 lx_projfunc_tp_amt.delete;
1149
1150 lx_denom_tp_currcode.delete;
1151 lx_denom_tp_amt.delete;
1152
1153 lx_expfunc_tp_rate_type.delete;
1154 lx_expfunc_tp_rate_date.delete;
1155 lx_expfunc_tp_exchange_rate.delete;
1156 lx_expfunc_tp_amt.delete;
1157
1158 lx_cc_markup_basecode.delete;
1159 lx_tp_ind_compiled_setid.delete;
1160 lx_tp_bill_rate.delete;
1161 lx_tp_base_amount.delete;
1162 lx_tp_bill_markup_percent.delete;
1163 lx_tp_sch_line_percent.delete;
1164 lx_tp_rule_percent.delete;
1165 lx_tp_job_id.delete;
1166 lx_tp_error_code.delete;
1167
1168 /* fcst item start */
1169
1170 l_fia_cost_txn_curr_code.delete;
1171 l_fia_rev_txn_curr_code.delete;
1172 l_fia_txn_raw_cost.delete;
1173 l_fia_txn_bd_cost.delete;
1174 l_fia_txn_revenue.delete;
1175
1176 l_fia_expfunc_curr_code.delete;
1177 l_fia_expfunc_raw_cost.delete;
1178 l_fia_expfunc_bd_cost.delete;
1179
1180 l_fia_projfunc_raw_cost.delete;
1181 l_fia_projfunc_bd_cost.delete;
1182 l_fia_projfunc_revenue.delete;
1183
1184 l_fia_proj_raw_cost.delete;
1185 l_fia_proj_bd_cost.delete;
1186 l_fia_proj_revenue.delete;
1187
1188
1189 l_fia_proj_cost_rate_type.delete;
1190 l_fia_proj_cost_rate_date.delete;
1191 l_fia_proj_cost_ex_rate.delete;
1192
1193 l_fia_proj_rev_rate_type.delete;
1194 l_fia_proj_rev_rate_date.delete;
1195 l_fia_proj_rev_ex_rate.delete;
1196
1197 l_fia_expfunc_cost_rate_type.delete;
1198 l_fia_expfunc_cost_rate_date.delete;
1199 l_fia_expfunc_cost_ex_rate.delete;
1200
1201 l_fia_projfunc_cost_rate_type.delete;
1202 l_fia_projfunc_cost_rate_date.delete;
1203 l_fia_projfunc_cost_ex_rate.delete;
1204
1205 l_fia_projfunc_rev_rate_type.delete;
1206 l_fia_projfunc_rev_rate_date.delete;
1207 l_fia_projfunc_rev_ex_rate.delete;
1208
1209 /* fcst item end */
1210
1211 /* Fcst Amount Details start */
1212
1213 l_fid_fcst_itemid.delete;
1214 l_fid_line_num.delete;
1215 l_fid_item_date.delete;
1216 l_fid_item_uom.delete;
1217 l_fid_item_qty.delete;
1218 l_fid_reversed_flag.delete;
1219 l_fid_net_zero_flag.delete;
1220 l_fid_line_num_reversed.delete;
1221
1222 l_fid_cost_txn_curr_code.delete;
1223 l_fid_rev_txn_curr_code.delete;
1224 l_fid_txn_raw_cost.delete;
1225 l_fid_txn_bd_cost.delete;
1226 l_fid_txn_revenue.delete;
1227
1228 l_fid_expfunc_curr_code.delete;
1229 l_fid_expfunc_raw_cost.delete;
1230 l_fid_expfunc_bd_cost.delete;
1231
1232 l_fid_projfunc_curr_code.delete;
1233 l_fid_projfunc_raw_cost.delete;
1234 l_fid_projfunc_bd_cost.delete;
1235 l_fid_projfunc_revenue.delete;
1236
1237 l_fid_proj_curr_code.delete;
1238 l_fid_proj_raw_cost.delete;
1239 l_fid_proj_bd_cost.delete;
1240 l_fid_proj_revenue.delete;
1241
1242
1243 l_fid_proj_cost_rate_type.delete;
1244 l_fid_proj_cost_rate_date.delete;
1245 l_fid_proj_cost_ex_rate.delete;
1246
1247 l_fid_proj_rev_rate_type.delete;
1248 l_fid_proj_rev_rate_date.delete;
1249 l_fid_proj_rev_ex_rate.delete;
1250
1251 l_fid_expfunc_cost_rate_type.delete;
1252 l_fid_expfunc_cost_rate_date.delete;
1253 l_fid_expfunc_cost_ex_rate.delete;
1254
1255 l_fid_projfunc_cost_rate_type.delete;
1256 l_fid_projfunc_cost_rate_date.delete;
1257 l_fid_projfunc_cost_ex_rate.delete;
1258
1259 l_fid_projfunc_rev_rate_type.delete;
1260 l_fid_projfunc_rev_rate_date.delete;
1261 l_fid_projfunc_rev_ex_rate.delete;
1262
1263 l_fid_proj_tp_rate_type.delete;
1264 l_fid_proj_tp_rate_date.delete;
1265 l_fid_proj_tp_ex_rate.delete;
1266 l_fid_proj_tp_amt.delete;
1267
1268 l_fid_projfunc_tp_rate_type.delete;
1269 l_fid_projfunc_tp_rate_date.delete;
1270 l_fid_projfunc_tp_ex_rate.delete;
1271 l_fid_projfunc_tp_amt.delete;
1272
1273 l_fid_denom_tp_currcode.delete;
1274 l_fid_denom_tp_amt.delete;
1275
1276 l_fid_expfunc_tp_rate_type.delete;
1277 l_fid_expfunc_tp_rate_date.delete;
1278 l_fid_expfunc_tp_ex_rate.delete;
1279 l_fid_expfunc_tp_amt.delete;
1280
1281 /* Fcst Amount Details start */
1282
1283 l_fia_index := 1;
1284 l_fia_upd_index := 1;
1285 IF p_run_mode = 'F' THEN
1286 IF P_PA_DEBUG_MODE = 'Y' THEN
1287 PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_All';
1288 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1289 END IF;
1290 FETCH fcst_item_All BULK COLLECT INTO
1291 l_fi_id_tab,
1292 l_fi_item_type_tab,
1293 l_fi_exp_orgid_tab,
1294 l_fi_exp_organizationid_tab,
1295 l_fi_proj_orgid_tab,
1296 l_fi_proj_organizationid_tab,
1297 l_fi_projid_tab,
1298 l_fi_proj_type_class_tab,
1299 l_fi_personid_tab,
1300 l_fi_resid_tab,
1301 l_fi_asgid_tab,
1302 l_fi_date_tab,
1303 l_fi_uom_tab,
1304 l_fi_qty_tab,
1305 l_fi_pvdr_papd_tab,
1306 l_fi_rcvr_papd_tab,
1307 l_fi_exptype_tab,
1308 l_fi_exptypeclass_tab,
1309 l_fi_amount_type_tab,
1310 l_fi_delete_flag_tab LIMIT l_fetch_size;
1311 ELSIF p_run_mode = 'I' THEN
1312 IF P_PA_DEBUG_MODE = 'Y' THEN
1313 PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_Inc';
1314 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1315 END IF;
1316 FETCH fcst_item_Inc BULK COLLECT INTO
1317 l_fi_id_tab,
1318 l_fi_item_type_tab,
1319 l_fi_exp_orgid_tab,
1320 l_fi_exp_organizationid_tab,
1321 l_fi_proj_orgid_tab,
1322 l_fi_proj_organizationid_tab,
1323 l_fi_projid_tab,
1324 l_fi_proj_type_class_tab,
1325 l_fi_personid_tab,
1326 l_fi_resid_tab,
1327 l_fi_asgid_tab,
1328 l_fi_date_tab,
1329 l_fi_uom_tab,
1330 l_fi_qty_tab,
1331 l_fi_pvdr_papd_tab,
1332 l_fi_rcvr_papd_tab,
1333 l_fi_exptype_tab,
1334 l_fi_exptypeclass_tab,
1335 l_fi_amount_type_tab,
1336 l_fi_delete_flag_tab LIMIT l_fetch_size;
1337 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
1338 p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
1339 IF P_PA_DEBUG_MODE = 'Y' THEN
1340 PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_Prj';
1341 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1342 END IF;
1343 FETCH fcst_item_Prj BULK COLLECT INTO
1344 l_fi_id_tab,
1345 l_fi_item_type_tab,
1346 l_fi_exp_orgid_tab,
1347 l_fi_exp_organizationid_tab,
1348 l_fi_proj_orgid_tab,
1349 l_fi_proj_organizationid_tab,
1350 l_fi_projid_tab,
1351 l_fi_proj_type_class_tab,
1352 l_fi_personid_tab,
1353 l_fi_resid_tab,
1354 l_fi_asgid_tab,
1355 l_fi_date_tab,
1356 l_fi_uom_tab,
1357 l_fi_qty_tab,
1358 l_fi_pvdr_papd_tab,
1359 l_fi_rcvr_papd_tab,
1360 l_fi_exptype_tab,
1361 l_fi_exptypeclass_tab,
1362 l_fi_amount_type_tab,
1363 l_fi_delete_flag_tab LIMIT l_fetch_size;
1364 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
1365 p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
1366 IF P_PA_DEBUG_MODE = 'Y' THEN
1367 PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_Prj_Asg';
1368 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1369 END IF;
1370 FETCH fcst_item_Prj_Asg BULK COLLECT INTO
1371 l_fi_id_tab,
1372 l_fi_item_type_tab,
1373 l_fi_exp_orgid_tab,
1374 l_fi_exp_organizationid_tab,
1375 l_fi_proj_orgid_tab,
1376 l_fi_proj_organizationid_tab,
1377 l_fi_projid_tab,
1378 l_fi_proj_type_class_tab,
1379 l_fi_personid_tab,
1380 l_fi_resid_tab,
1381 l_fi_asgid_tab,
1382 l_fi_date_tab,
1383 l_fi_uom_tab,
1384 l_fi_qty_tab,
1385 l_fi_pvdr_papd_tab,
1386 l_fi_rcvr_papd_tab,
1387 l_fi_exptype_tab,
1388 l_fi_exptypeclass_tab,
1389 l_fi_amount_type_tab,
1390 l_fi_delete_flag_tab LIMIT l_fetch_size;
1391 ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
1392 IF P_PA_DEBUG_MODE = 'Y' THEN
1393 PA_DEBUG.g_err_stage := 'Fetching fcst_item_Organization';
1394 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1395 END IF;
1396 FETCH fcst_item_Organization BULK COLLECT INTO
1397 l_fi_id_tab,
1398 l_fi_item_type_tab,
1399 l_fi_exp_orgid_tab,
1400 l_fi_exp_organizationid_tab,
1401 l_fi_proj_orgid_tab,
1402 l_fi_proj_organizationid_tab,
1403 l_fi_projid_tab,
1404 l_fi_proj_type_class_tab,
1405 l_fi_personid_tab,
1406 l_fi_resid_tab,
1407 l_fi_asgid_tab,
1408 l_fi_date_tab,
1409 l_fi_uom_tab,
1410 l_fi_qty_tab,
1411 l_fi_pvdr_papd_tab,
1412 l_fi_rcvr_papd_tab,
1413 l_fi_exptype_tab,
1414 l_fi_exptypeclass_tab,
1415 l_fi_amount_type_tab,
1416 l_fi_delete_flag_tab LIMIT l_fetch_size;
1417 END IF;
1418 IF l_fi_id_tab.count = 0 THEN
1419 EXIT;
1420 END IF;
1421 /* dbms_output.put_line('fetch count:'||l_fi_id_tab.count); */
1422 IF P_PA_DEBUG_MODE = 'Y' THEN
1423 PA_DEBUG.g_err_stage := 'Fetch Count:'||l_fi_id_tab.COUNT;
1424 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1425 END IF;
1426
1427
1428 DELETE FROM Pa_Fi_Amount_Dtls_Tmp;
1429 COMMIT;
1430
1431
1432 /* Forecast Item records should be locked to prevent Update from
1433 Forecast Item generation Process */
1434
1435 FORALL l_fi_lck_idx IN 1 .. l_fi_id_tab.COUNT
1436 UPDATE Pa_Forecast_Items SET Forecast_Amt_Calc_Flag = 'P'
1437 WHERE
1438 Forecast_Item_Id = l_fi_id_tab(l_fi_lck_idx);
1439
1440 FOR I IN 1 .. l_fi_id_tab.count LOOP
1441 IF l_fi_item_type_tab(i) = 'R' THEN
1442 l_fi_item_type_tab(i) := 'ROLE';
1443 ELSIF l_fi_item_type_tab(i) = 'A' THEN
1444 l_fi_item_type_tab(i) := 'ASSIGNMENT';
1445 ELSIF l_fi_item_type_tab(i) = 'U' THEN
1446 l_fi_item_type_tab(i) := 'UNASSIGNED';
1447 l_process_fis_flag := 'Y';
1448 END IF;
1449 l_fi_process_flag_tab(i) := 'Y';
1450 l_fi_others_rejct_reason_tab(i) := NULL;
1451 l_tp_exp_itemid(i) := l_fi_id_tab(i);
1452
1453 /* dbms_output.put_line('fi id :'||l_fi_id_tab(i) );
1454 dbms_output.put_line('del fg:'||l_fi_delete_flag_tab(i) ); */
1455
1456 IF l_fi_delete_flag_tab(i) = 'Y' OR l_fi_qty_tab(i) <= 0 THEN
1457 l_fi_process_flag_tab(i) := 'N';
1458 l_fi_others_rejct_reason_tab(i) := 'E';
1459 END IF;
1460 /* The following logic is included, because the FI generation process
1461 populates negative values if the Resource is transferred from Exp Orgz
1462 to Non-Exp Orgz */
1463 IF ( l_fi_exp_orgid_tab(i) < 0 AND l_fi_exp_orgid_tab(i) <> -99 ) OR
1464 ( l_fi_exp_organizationid_tab(i) < 0 AND l_fi_exp_organizationid_tab(i) <> -99 ) OR
1465 ( l_fi_proj_orgid_tab(i) < 0 AND l_fi_proj_orgid_tab(i) <> -99 ) OR
1466 ( l_fi_proj_organizationid_tab(i) < 0 AND l_fi_proj_organizationid_tab(i) <> -99 ) THEN
1467 l_fi_process_flag_tab(i) := 'X';
1468 l_fi_others_rejct_reason_tab(i) := 'E';
1469 END IF;
1470
1471
1472 IF l_prev_project_id <> l_fi_projId_tab(i) AND
1473 l_fi_process_flag_tab(i) = 'Y' THEN
1474 /* dbms_output.put_line('prev prj id :'||l_prev_project_id);
1475 dbms_output.put_line('curr prj id :'||l_fi_projid_tab(i)); */
1476
1477 IF P_PA_DEBUG_MODE = 'Y' THEN -- Bug 7423839
1478 PA_DEBUG.g_err_stage := 'F I Amt: prev prj id :'||l_prev_project_id||' curr prj id :'||l_fi_projid_tab(i);
1479 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1480 END IF;
1481
1482
1483 l_prj_type := NULL;
1484 l_distribution_rule := NULL;
1485 l_bill_job_group_id := NULL;
1486 l_cost_job_group_id := NULL;
1487 l_job_bill_rate_sch_id := NULL;
1488 l_emp_bill_rate_sch_id := NULL;
1489 l_prj_curr_code := NULL;
1490 l_prj_rate_date := NULL;
1491 l_prj_rate_type := NULL;
1492 l_prj_bil_rate_date_code := NULL;
1493 l_prj_bil_rate_type := NULL;
1494 l_prj_bil_rate_date := NULL;
1495 l_prj_bil_ex_rate := NULL;
1496 l_prjfunc_curr_code := NULL;
1497 l_prjfunc_cost_rate_type := NULL;
1498 l_prjfunc_cost_rate_date := NULL;
1499 l_prjfunc_bil_rate_date_code := NULL;
1500 l_prjfunc_bil_rate_type := NULL;
1501 l_prjfunc_bil_rate_date := NULL;
1502 l_prjfunc_bil_ex_rate := NULL;
1503 l_labor_tp_schedule_id := NULL;
1504 l_labor_tp_fixed_date := NULL;
1505 l_labor_sch_discount := NULL;
1506 l_asg_precedes_task := NULL;
1507 l_labor_bill_rate_orgid := NULL;
1508 l_labor_std_bill_rate_sch := NULL;
1509 l_labor_sch_fixed_dt := NULL;
1510 l_labor_sch_type := NULL;
1511 l_prev_project_id := l_fi_projid_tab(i);
1512 BEGIN
1513 SELECT Project_Type,
1514 DISTRIBUTION_RULE,
1515 BILL_JOB_GROUP_ID,
1516 COST_JOB_GROUP_ID,
1517 JOB_BILL_RATE_SCHEDULE_ID,
1518 EMP_BILL_RATE_SCHEDULE_ID,
1519 PROJECT_CURRENCY_CODE,
1520 PROJECT_RATE_DATE,
1521 PROJECT_RATE_TYPE,
1522 PROJECT_BIL_RATE_DATE_CODE,
1523 PROJECT_BIL_RATE_TYPE,
1524 PROJECT_BIL_RATE_DATE,
1525 PROJECT_BIL_EXCHANGE_RATE,
1526 PROJFUNC_CURRENCY_CODE,
1527 PROJFUNC_COST_RATE_TYPE,
1528 PROJFUNC_COST_RATE_DATE,
1529 PROJFUNC_BIL_RATE_DATE_CODE,
1530 PROJFUNC_BIL_RATE_TYPE,
1531 PROJFUNC_BIL_RATE_DATE,
1532 PROJFUNC_BIL_EXCHANGE_RATE,
1533 LABOR_TP_SCHEDULE_ID,
1534 LABOR_TP_FIXED_DATE,
1535 LABOR_SCHEDULE_DISCOUNT,
1536 ASSIGN_PRECEDES_TASK,
1537 NVL(LABOR_BILL_RATE_ORG_ID,-99),
1538 LABOR_STD_BILL_RATE_SCHDL,
1539 LABOR_SCHEDULE_FIXED_DATE,
1540 LABOR_SCH_TYPE
1541 INTO
1542 l_prj_type,
1543 l_distribution_rule,
1544 l_bill_job_group_id,
1545 l_cost_job_group_id,
1546 l_job_bill_rate_sch_id,
1547 l_emp_bill_rate_sch_id,
1548 l_prj_curr_code,
1549 l_prj_rate_date,
1550 l_prj_rate_type,
1551 l_prj_bil_rate_date_code,
1552 l_prj_bil_rate_type,
1553 l_prj_bil_rate_date,
1554 l_prj_bil_ex_rate,
1555 l_prjfunc_curr_code,
1556 l_prjfunc_cost_rate_type,
1557 l_prjfunc_cost_rate_date,
1558 l_prjfunc_bil_rate_date_code,
1559 l_prjfunc_bil_rate_type,
1560 l_prjfunc_bil_rate_date,
1561 l_prjfunc_bil_ex_rate,
1562 l_labor_tp_schedule_id,
1563 l_labor_tp_fixed_date,
1564 l_labor_sch_discount,
1565 l_asg_precedes_task,
1566 l_labor_bill_rate_orgid,
1567 l_labor_std_bill_rate_sch,
1568 l_labor_sch_fixed_dt,
1569 l_labor_sch_type
1570 FROM Pa_Projects_All P
1571 WHERE P.Project_Id = l_prev_project_id;
1572 EXCEPTION
1573 WHEN NO_DATA_FOUND THEN
1574 l_fi_others_rejct_reason_tab(i) := 'PA_INVALID_PROJECT_ID';
1575 l_fi_process_flag_tab(i) := 'N';
1576 IF P_PA_DEBUG_MODE = 'Y' THEN
1577 PA_DEBUG.g_err_stage := 'Invalid Project Id :'||TO_CHAR(l_prev_project_id);
1578 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1579 END IF;
1580 END;
1581 END IF;
1582
1583 /* dbms_output.put_line('after prj'); */
1584
1585 l_fcst_cost_rate_schid := NULL;
1586
1587 IF l_prev_proj_orgid <> l_fi_proj_orgid_tab(i) AND
1588 l_fi_process_flag_tab(i) = 'Y' THEN
1589 l_prev_proj_orgid := l_fi_proj_orgid_tab(i);
1590 BEGIN
1591 SELECT JOB_COST_RATE_SCHEDULE_ID INTO
1592 l_fcst_cost_rate_schid
1593 FROM PA_FORECASTING_OPTIONS_ALL
1594 WHERE NVL(ORG_ID,-99) = l_prev_proj_orgid AND
1595 JOB_COST_RATE_SCHEDULE_ID IS NOT NULL;
1596 EXCEPTION
1597 WHEN NO_DATA_FOUND THEN
1598 IF P_PA_DEBUG_MODE = 'Y' THEN
1599 PA_DEBUG.g_err_stage := 'No Fcst Job Cost Rt Sch for Proj Orgid:' ||
1600 TO_CHAR(l_prev_proj_orgid);
1601 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1602 END IF;
1603 l_fi_others_rejct_reason_tab(i) := 'PA_FCST_NO_JOB_SCH_ID';
1604 l_fi_process_flag_tab(i) := 'N';
1605 END;
1606 END IF;
1607 /* dbms_output.put_line('after options'); */
1608
1609 l_prj_type_tab(i) := l_prj_type;
1610 l_distribution_rule_tab(i) := l_distribution_rule;
1611 l_bill_job_group_id_tab(i) := l_bill_job_group_id;
1612 l_cost_job_group_id_tab(i) := l_cost_job_group_id;
1613 l_job_bill_rate_sch_id_tab(i) := l_job_bill_rate_sch_id;
1614 l_emp_bill_rate_sch_id_tab(i) := l_emp_bill_rate_sch_id;
1615 l_prj_curr_code_tab(i) := l_prj_curr_code;
1616 l_prj_rate_date_tab(i) := l_prj_rate_date;
1617 l_prj_rate_type_tab(i) := l_prj_rate_type;
1618 l_prj_bil_rate_dt_code_tab(i) := l_prj_bil_rate_date_code;
1619 l_prj_bil_rate_type_tab(i) := l_prj_bil_rate_type;
1620 l_prj_bil_rate_date_tab(i) := l_prj_bil_rate_date;
1621 l_prj_bil_ex_rate_tab(i) := l_prj_bil_ex_rate;
1622 l_prjfunc_curr_code_tab(i) := l_prjfunc_curr_code;
1623 l_prjfunc_cost_rt_type_tab(i) := l_prjfunc_cost_rate_type;
1624 l_prjfunc_cost_rt_dt_tab(i) := l_prjfunc_cost_rate_date;
1625 l_prjfunc_bil_rt_dt_code_tab(i) := l_prjfunc_bil_rate_date_code;
1626 l_prjfunc_bil_rate_type_tab(i) := l_prjfunc_bil_rate_type;
1627 l_prjfunc_bil_rate_date_tab(i) := l_prjfunc_bil_rate_date;
1628 l_prjfunc_bil_ex_rate_tab(i) := l_prjfunc_bil_ex_rate;
1629 l_tp_scheduleid(i) := l_labor_tp_schedule_id;
1630 l_tp_fixed_date(i) := l_labor_tp_fixed_date;
1631 l_tp_denom_currcode(i) := NULL;
1632 l_tp_denom_raw_cost(i) := NULL;
1633 l_tp_denom_bd_cost(i) := NULL;
1634 l_tp_raw_revenue(i) := NULL;
1635 l_prj_cost_rate_schid_tab(i) := l_fcst_cost_rate_schid;
1636
1637 l_labor_sch_discount_tab(i) := l_labor_sch_discount;
1638 l_asg_precedes_task_tab(i) := l_asg_precedes_task;
1639 l_labor_bill_rate_orgid_tab(i) := l_labor_bill_rate_orgid;
1640 l_labor_std_bill_rate_sch_tab(i) := l_labor_std_bill_rate_sch;
1641 l_labor_sch_fixed_dt_tab(i) := l_labor_sch_fixed_dt;
1642 l_labor_sch_type_tab(i) := l_labor_sch_type;
1643
1644
1645 l_asg_markup_percent := NULL;
1646 l_asg_bill_rate_override := NULL;
1647 l_asg_bill_rate_curr_override := NULL;
1648 l_asg_markup_percent_override := NULL;
1649 l_asg_tp_rate_override := NULL;
1650 l_asg_tp_curr_override := NULL;
1651 l_asg_tp_calc_base_code_ovr := NULL;
1652 l_asg_tp_percent_applied_ovr := NULL;
1653
1654 l_asg_fcst_jobid_tab(i) := NULL;
1655 l_asg_fcst_jobgroupid_tab(i) := NULL;
1656
1657 lx_cc_status_tab(i) := NULL;
1658 lx_tp_error_code(i) := NULL;
1659
1660 IF ( l_prev_asg_id <> l_fi_asgid_tab(i) AND
1661 l_fi_asgid_tab(i) <> -9999 AND
1662 l_fi_process_flag_tab(i) = 'Y' ) THEN
1663 /* dbms_output.put_line('asg id changing prev id :'||l_prev_asg_id );
1664 dbms_output.put_line('asg id current prev id :'||l_fi_asgid_tab(i) ); */
1665 l_prev_asg_id := l_fi_asgid_tab(i);
1666 /* dbms_output.put_line('inside asg ');
1667 dbms_output.put_line('asg id '||l_prev_asg_id); */
1668 l_process_fis_flag := 'Y';
1669 BEGIN
1670 l_asg_fcst_job_id := NULL;
1671 l_asg_fcst_job_group_id := NULL;
1672 l_asg_project_role_id := NULL;
1673 l_prj_assignment_type := NULL;
1674 l_prj_status_code := NULL;
1675
1676 SELECT Fcst_Job_Id,
1677 Fcst_Job_Group_Id,
1678 Project_Role_Id,
1679 MARKUP_PERCENT,
1680 BILL_RATE_OVERRIDE,
1681 BILL_RATE_CURR_OVERRIDE,
1682 MARKUP_PERCENT_OVERRIDE,
1683 TP_RATE_OVERRIDE,
1684 TP_CURRENCY_OVERRIDE,
1685 TP_CALC_BASE_CODE_OVERRIDE,
1686 TP_PERCENT_APPLIED_OVERRIDE,
1687 ASSIGNMENT_TYPE,
1688 STATUS_CODE
1689 INTO
1690 l_asg_fcst_job_id,
1691 l_asg_fcst_job_group_id,
1692 l_asg_project_role_id,
1693 l_asg_markup_percent,
1694 l_asg_bill_rate_override,
1695 l_asg_bill_rate_curr_override,
1696 l_asg_markup_percent_override,
1697 l_asg_tp_rate_override,
1698 l_asg_tp_curr_override,
1699 l_asg_tp_calc_base_code_ovr,
1700 l_asg_tp_percent_applied_ovr,
1701 l_prj_assignment_type,
1702 l_prj_status_code
1703 FROM PA_PROJECT_ASSIGNMENTS P
1704 WHERE P.Assignment_Id = l_prev_asg_id;
1705 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1706 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1707 EXCEPTION
1708 WHEN NO_DATA_FOUND THEN
1709 IF P_PA_DEBUG_MODE = 'Y' THEN
1710 PA_DEBUG.g_err_stage := 'Invalid Assignment Id:' ||
1711 TO_CHAR(l_prev_asg_id);
1712 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1713 END IF;
1714 l_fi_others_rejct_reason_tab(i) := 'PA_FP_INVALID_ASG_ID';
1715 l_fi_process_flag_tab(i) := 'N';
1716 RAISE;
1717 END;
1718 /* dbms_output.put_line('after asg'); */
1719
1720 /* The Forecast Items needs to be checked in the Assignment Level whether
1721 it needs to be processed or not. The flag l_process_fis_flag is used to set
1722 the l_fi_process_flag_tab PL SQL table value */
1723
1724 IF l_fi_process_flag_tab(i) = 'Y' AND l_prj_status_code IS NOT NULL THEN
1725 IF l_prj_assignment_type = 'OPEN_ASSIGNMENT' THEN
1726 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Asgmt_In_Open_Status(
1727 l_prj_status_code,
1728 'OPEN_ASGMT');
1729 IF l_asgmt_status_flag = 'N' THEN
1730 l_process_fis_flag := 'N';
1731 END IF;
1732 ELSIF ( l_prj_assignment_type = 'STAFFED_ASSIGNMENT' OR
1733 l_prj_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' ) THEN
1734 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(
1735 l_prj_status_code,
1736 'STAFFED_ASGMT');
1737 IF l_asgmt_status_flag = 'Y' THEN
1738 l_process_fis_flag := 'N';
1739 END IF;
1740 END IF;
1741 END IF;
1742 /* dbms_output.put_line('asg id : fis process flag'||l_fi_asgid_tab(i) ||
1743 ' : ' ||l_process_fis_flag); */
1744 IF l_fi_item_type_tab(i) = 'R' AND
1745 ( l_asg_fcst_job_id IS NULL OR
1746 l_asg_fcst_job_group_id IS NULL ) AND
1747 l_process_fis_flag = 'Y' THEN
1748 BEGIN
1749 SELECT PR.DEFAULT_JOB_ID,
1750 PJ.JOB_GROUP_ID
1751 INTO
1752 l_asg_fcst_job_id,
1753 l_asg_fcst_job_group_id
1754 FROM PA_PROJECT_ROLE_TYPES PR,
1755 PER_JOBS PJ
1756 WHERE
1757 PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
1758 PJ.JOB_ID = PR.DEFAULT_JOB_ID;
1759 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1760 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1761 EXCEPTION
1762 WHEN NO_DATA_FOUND THEN
1763 l_fi_others_rejct_reason_tab(i) := 'PA_FCST_NOJOB_FOR_ROLE';
1764 l_fi_process_flag_tab(i) := 'N';
1765 l_asg_fcst_job_id := NULL;
1766 l_asg_fcst_job_group_id := NULL;
1767 WHEN OTHERS THEN
1768 IF P_PA_DEBUG_MODE = 'Y' THEN
1769 PA_DEBUG.g_err_stage := 'Inside Prj Role others Excep';
1770 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1771 END IF;
1772 RAISE;
1773 END;
1774 END IF; /* fi item type = R chk */
1775 END IF; /* if asg id changes chk */
1776 /* dbms_output.put_line('after prj role '); */
1777
1778 IF l_fi_asgid_tab(i) = -9999 THEN
1779 l_asg_fcst_jobid_tab(i) := NULL;
1780 l_asg_fcst_jobgroupid_tab(i) := NULL;
1781 ELSE
1782 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1783 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1784 IF l_process_fis_flag = 'N' THEN
1785 l_fi_others_rejct_reason_tab(i) := 'E';
1786 l_fi_process_flag_tab(i) := 'X';
1787
1788 /* Forecast Amt Calc Flag should be set to X, so that it would not be
1789 picked up by Org Forecast Generation Process */
1790 END IF;
1791 END IF;
1792
1793 l_cc_sys_link_tab(i) := NULL;
1794 l_cc_taskid_tab(i) := NULL;
1795 l_cc_expitemid_tab(i) := NULL;
1796 l_cc_transsource_tab(i) := NULL;
1797 l_cc_NLOrgzid_tab(i) := NULL;
1798 l_cc_prvdreid_tab(i) := NULL;
1799 l_cc_recvreid_tab(i) := NULL;
1800 lx_cc_type_tab(i) := NULL;
1801 lx_cc_code_tab(i) := NULL;
1802 lx_cc_prvdr_orgzid_tab(i) := NULL;
1803 lx_cc_recvr_orgzid_tab(i) := NULL;
1804 lx_cc_recvr_orgid_tab(i) := NULL;
1805 lx_cc_prvdr_orgid_tab(i) := NULL;
1806
1807 IF l_prev_exp_type <> l_fi_exptype_tab(i) AND
1808 l_fi_process_flag_tab(i) = 'Y' THEN
1809 l_prev_exp_type := l_fi_exptype_tab(i);
1810 BEGIN
1811 SELECT EXPENDITURE_CATEGORY
1812 INTO l_cc_exp_category
1813 FROM pa_expenditure_types WHERE
1814 EXPENDITURE_TYPE = l_prev_exp_type;
1815 EXCEPTION
1816 WHEN NO_DATA_FOUND THEN
1817 l_fi_process_flag_tab(i) := 'N';
1818 l_cc_exp_category := NULL;
1819 WHEN OTHERS THEN
1820 IF P_PA_DEBUG_MODE = 'Y' THEN
1821 PA_DEBUG.g_err_stage := 'Inside Exp Type others Excep';
1822 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1823 END IF;
1824 RAISE;
1825 END;
1826 END IF;
1827 /* dbms_output.put_line('after sys link '); */
1828 l_tp_exp_category(i) := l_cc_exp_category;
1829
1830 IF l_fi_process_flag_tab(i) = 'Y' THEN
1831 /* AND l_fi_amount_type_tab(i) IS NULL THEN */
1832 BEGIN
1833 SELECT
1834 TP_AMOUNT_TYPE
1835 INTO
1836 l_fi_amount_type_tab(i)
1837 FROM Pa_Forecast_Item_Details
1838 WHERE
1839 FORECAST_ITEM_ID = l_fi_id_tab(i) AND
1840 Line_Num = ( SELECT MAX(Line_Num) FROM
1841 Pa_Forecast_Item_Details WHERE
1842 Forecast_Item_Id = l_fi_id_tab(i) AND
1843 Net_Zero_Flag = 'N' );
1844 EXCEPTION
1845 WHEN NO_DATA_FOUND THEN
1846 NULL;
1847 /* dbms_output.put_line('inside no data found for tp amt type'||
1848 l_fi_delete_flag_tab(i) );
1849 dbms_output.put_line('fi id :'||l_fi_id_tab(i) ); */
1850 WHEN OTHERS THEN
1851 retcode := '2';
1852 IF P_PA_DEBUG_MODE = 'Y' THEN
1853 PA_DEBUG.g_err_stage := 'Inside FI Dtls others Excep';
1854 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1855 END IF;
1856 RAISE;
1857 END;
1858 END IF;
1859
1860 IF l_fi_process_flag_tab(i) <> 'Y' THEN
1861 lx_cc_status_tab(i) := 'E';
1862 lx_tp_error_code(i) := 'E';
1863 END IF;
1864
1865 /* The Cross Charge API should process the Forecast Item only,
1866 if there is no error in the Forecast Item level and no error
1867 returned from the Rate API.
1868
1869 The CC API checks for the X_StatusTab value and proceeds with processing
1870 only if the value is NULL */
1871
1872
1873 /* dbms_output.put_line('fi prj id :<'||i ||'>' ||l_fi_projid_tab(i) ||
1874 ' : '|| l_fi_id_tab(i));
1875 dbms_output.put_line('fi asg id :<'||i ||'>' ||l_fi_asgid_tab(i));
1876 dbms_output.put_line('proc flag and qty <'||i ||'>' ||l_fi_process_flag_tab(i)
1877 || ' Q ' || l_fi_qty_tab(i) );
1878 dbms_output.put_line('others :<'||i ||'>' ||l_fi_others_rejct_reason_tab(i)); */
1879
1880 l_fi_rev_rejct_reason_tab(i) := NULL;
1881 l_fi_cst_rejct_reason_tab(i) := NULL;
1882 l_fi_bd_rejct_reason_tab(i) := NULL;
1883 l_fi_tp_rejct_reason_tab(i) := NULL;
1884
1885 l_fia_cost_txn_curr_code(i) := NULL;
1886 l_fia_rev_txn_curr_code(i) := NULL;
1887 l_fia_txn_raw_cost(i) := NULL;
1888 l_fia_txn_bd_cost(i) := NULL;
1889 l_fia_txn_revenue(i) := NULL;
1890
1891 l_fia_expfunc_curr_code(i) := NULL;
1892 l_fia_expfunc_raw_cost(i) := NULL;
1893 l_fia_expfunc_bd_cost(i) := NULL;
1894
1895 l_fia_projfunc_raw_cost(i) := NULL;
1896 l_fia_projfunc_bd_cost(i) := NULL;
1897 l_fia_projfunc_revenue(i) := NULL;
1898
1899 l_fia_proj_raw_cost(i) := NULL;
1900 l_fia_proj_bd_cost(i) := NULL;
1901 l_fia_proj_revenue(i) := NULL;
1902
1903
1904 l_fia_proj_cost_rate_type(i) := NULL;
1905 l_fia_proj_cost_rate_date(i) := NULL;
1906 l_fia_proj_cost_ex_rate(i) := NULL;
1907
1908 l_fia_proj_rev_rate_type(i) := NULL;
1909 l_fia_proj_rev_rate_date(i) := NULL;
1910 l_fia_proj_rev_ex_rate(i) := NULL;
1911
1912 l_fia_expfunc_cost_rate_type(i) := NULL;
1913 l_fia_expfunc_cost_rate_date(i) := NULL;
1914 l_fia_expfunc_cost_ex_rate(i) := NULL;
1915
1916 l_fia_projfunc_cost_rate_type(i) := NULL;
1917 l_fia_projfunc_cost_rate_date(i) := NULL;
1918 l_fia_projfunc_cost_ex_rate(i) := NULL;
1919
1920 l_fia_projfunc_rev_rate_type(i) := NULL;
1921 l_fia_projfunc_rev_rate_date(i) := NULL;
1922 l_fia_projfunc_rev_ex_rate(i) := NULL;
1923
1924
1925 END LOOP;
1926 /* end loop for setting other plsql table values
1927 for rt_tmp in 1 .. l_fi_id_tab.count loop
1928 dbms_output.put_line('index: '||rt_tmp ||' asg id '||
1929 l_fi_asgid_tab(rt_tmp) || ' job id :job gr id: '||
1930 l_asg_fcst_jobid_tab(rt_tmp)|| ' '||
1931 l_asg_fcst_jobgroupid_tab(rt_tmp)||' fl '||
1932 l_fi_process_flag_tab(rt_tmp) || ' '||
1933 l_fi_id_tab(rt_tmp));
1934 end loop; */
1935 /* calling Rate API */
1936 /* dbms_output.put_line('before assigning variables for Rate api '); */
1937 l_prev_rt_prj_id := l_fi_projid_tab(1);
1938 l_prev_rt_asg_id := l_fi_asgid_tab(1);
1939 l_prev_rt_personid := l_fi_personid_tab(1);
1940 l_prev_rt_fi_itemtype := l_fi_item_type_tab(1);
1941 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(1);
1942 l_prev_rt_calling_mode := l_fi_item_type_tab(1);
1943 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(1);
1944 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(1);
1945 l_prev_rt_exp_type := l_fi_exptype_tab(1);
1946 l_prev_rt_org_id := l_fi_proj_orgid_tab(1);
1947 l_prev_rt_prj_type := l_prj_type_tab(1);
1948 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(1);
1949 l_prev_rt_proj_currcode := l_prj_curr_code_tab(1);
1950 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(1);
1951 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(1);
1952 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(1);
1953 l_prev_rt_dist_rule := l_distribution_rule_tab(1);
1954 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(1);
1955
1956 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(1);
1957 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(1);
1958 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(1);
1959 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(1);
1960 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(1);
1961 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(1);
1962
1963 /* dbms_output.put_line('after assigning variables for Rate api '); */
1964
1965 l_rt_fi_id_tab.delete;
1966 l_rt_start_date_tab.delete;
1967 l_rt_qty_tab.delete;
1968 l_rt_exp_org_id_tab.delete;
1969 l_rt_exp_organization_id_tab.delete;
1970 l_rt_system_linkage_tab.delete;
1971 lx_rt_others_rejct_reason_tab.delete;
1972
1973 /* new parameters added for Org Forecast */
1974
1975
1976 l_rt_pfunc_rev_rt_date_tab.delete;
1977 l_rt_pfunc_rev_rt_type_tab.delete;
1978 l_rt_pfunc_rev_ex_rt_tab.delete;
1979
1980 l_rt_pfunc_cost_rt_date_tab.delete;
1981 l_rt_pfunc_cost_rt_type_tab.delete;
1982
1983 l_rt_proj_cost_rt_date_tab.delete;
1984 l_rt_proj_cost_rt_type_tab.delete;
1985 l_rt_proj_rev_rt_date_tab.delete;
1986 l_rt_proj_rev_rt_type_tab.delete;
1987 l_rt_proj_rev_ex_rt_tab.delete;
1988 l_rt_proj_rev_rt_dt_code_tab.delete;
1989 l_rt_pfunc_rev_rt_dt_code_tab.delete;
1990
1991 l_temp_last := l_fi_id_tab.LAST;
1992
1993 FOR m IN 1 .. l_fi_id_tab.count LOOP
1994 IF ( ( l_prev_rt_prj_id <> l_fi_projid_tab(m) ) OR
1995
1996 ( l_prev_rt_asg_id <> l_fi_asgid_tab(m) AND
1997 l_prev_rt_asg_id > 0 AND l_fi_asgid_tab(m) > 0 ) OR
1998 ( l_prev_rt_asg_id = -9999 AND l_fi_asgid_tab(m) > 0 ) OR
1999 ( l_prev_rt_asg_id > 0 AND l_fi_asgid_tab(m) = -9999 ) OR
2000 ( l_prev_rt_asg_id IS NOT NULL and l_fi_asgid_tab(m) IS NULL ) OR
2001 ( l_prev_rt_asg_id = -9999 and l_fi_asgid_tab(m) = -9999 AND
2002 l_prev_rt_personid <> l_fi_personid_tab(m) ) )
2003 OR ( m = l_temp_last ) THEN
2004 /* dbms_output.put_line('prev asg id :'||l_prev_rt_asg_id);
2005 dbms_output.put_line('curr asg id :'||l_fi_asgid_tab(m));
2006 dbms_output.put_line('prev per id :'||l_prev_rt_personid);
2007 dbms_output.put_line('curr per id :'||l_fi_personid_tab(m));
2008 dbms_output.put_line('prev prj id :'||l_prev_rt_prj_id);
2009 dbms_output.put_line('curr prj id :'|| l_fi_projid_tab(m)); */
2010 IF m = l_temp_last AND l_fi_process_flag_tab(m) = 'Y' THEN
2011 l_prev_rt_prj_id := l_fi_projid_tab(m);
2012 l_prev_rt_asg_id := l_fi_asgid_tab(m);
2013 l_prev_rt_personid := l_fi_personid_tab(m);
2014 l_prev_rt_fi_itemtype := l_fi_item_type_tab(m);
2015 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2016 l_prev_rt_calling_mode := l_fi_item_type_tab(m);
2017 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(m);
2018 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(m);
2019 l_prev_rt_exp_type := l_fi_exptype_tab(m);
2020 l_prev_rt_org_id := l_fi_proj_orgid_tab(m);
2021 l_prev_rt_prj_type := l_prj_type_tab(m);
2022 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(m);
2023 l_prev_rt_proj_currcode := l_prj_curr_code_tab(m);
2024 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(m);
2025 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(m);
2026 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(m);
2027 l_prev_rt_dist_rule := l_distribution_rule_tab(m);
2028 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(m);
2029
2030 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(m);
2031 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(m);
2032 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(m);
2033 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(m);
2034 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(m);
2035 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(m);
2036
2037 l_rt_fi_id_tab(m) := l_fi_id_tab(m);
2038 l_rt_start_date_tab(m) := l_fi_date_tab(m);
2039 l_rt_qty_tab(m) := l_fi_qty_tab(m);
2040 l_rt_system_linkage_tab(m) := l_fi_exptypeclass_tab(m);
2041 l_rt_exp_org_id_tab(m) := l_fi_exp_orgid_tab(m);
2042 l_rt_exp_organization_id_tab(m) := l_fi_exp_organizationid_tab(m);
2043 lx_rt_others_rejct_reason_tab(m) := l_fi_others_rejct_reason_tab(m);
2044
2045 l_rt_pfunc_rev_rt_date_tab(m) := l_prjfunc_bil_rate_date_tab(m);
2046 l_rt_pfunc_rev_rt_type_tab(m) := l_prjfunc_bil_rate_type_tab(m);
2047 l_rt_pfunc_rev_ex_rt_tab(m) := l_prjfunc_bil_ex_rate_tab(m);
2048 l_rt_pfunc_cost_rt_date_tab(m) := l_prjfunc_cost_rt_dt_tab(m);
2049 l_rt_pfunc_cost_rt_type_tab(m) := l_prjfunc_cost_rt_type_tab(m);
2050 l_rt_proj_cost_rt_date_tab(m) := l_prj_rate_date_tab(m);
2051 l_rt_proj_cost_rt_type_tab(m) := l_prj_rate_type_tab(m);
2052 l_rt_proj_rev_rt_date_tab(m) := l_prj_bil_rate_date_tab(m);
2053 l_rt_proj_rev_rt_type_tab(m) := l_prj_bil_rate_type_tab(m);
2054 l_rt_proj_rev_ex_rt_tab(m) := l_prj_bil_ex_rate_tab(m);
2055 l_rt_proj_rev_rt_dt_code_tab(m) := l_prjfunc_bil_rt_dt_code_tab(m);
2056 l_rt_pfunc_rev_rt_dt_code_tab(m) := l_prj_bil_rate_dt_code_tab(m);
2057 END IF;
2058
2059 IF l_rt_start_date_tab.COUNT > 0 THEN
2060
2061 /* dbms_output.put_line('st dt :'|| l_rt_start_date_tab.count);
2062 dbms_output.put_line('id :'|| l_rt_fi_id_tab.count);
2063 dbms_output.put_line('qty :'|| l_rt_qty_tab.count);
2064 dbms_output.put_line('sys lk:'|| l_rt_system_linkage_tab.count);
2065 dbms_output.put_line('exp org :'|| l_rt_exp_org_id_tab.count);
2066 dbms_output.put_line('exp orgz :'||l_rt_exp_organization_id_tab.count);
2067 dbms_output.put_line('pf bl rt dt:'|| l_rt_pfunc_rev_rt_date_tab.count);
2068 dbms_output.put_line('pf bl rt ty :'||l_rt_pfunc_rev_rt_type_tab.count);
2069 dbms_output.put_line('pf bl ex rt :'||l_rt_pfunc_rev_ex_rt_tab.count);
2070 dbms_output.put_line('pf ct rt dt :'||l_rt_pfunc_cost_rt_date_tab.count);
2071 dbms_output.put_line('pf ct rt ty :'||l_rt_pfunc_cost_rt_type_tab.count);
2072 dbms_output.put_line('pf bl rt dt cd :'||l_rt_pfunc_rev_rt_dt_code_tab.count);
2073 dbms_output.put_line('p bl rt dt:'||l_rt_proj_rev_rt_date_tab.count);
2074 dbms_output.put_line('p_bl_rt ty:'|| l_rt_proj_rev_rt_type_tab.count);
2075 dbms_output.put_line('p_bl_ex rt:'||l_rt_proj_rev_ex_rt_tab.count);
2076 dbms_output.put_line('p bl rt dt cd:'||l_rt_proj_rev_rt_dt_code_tab.count);
2077 dbms_output.put_line('p ct rt dt:'|| l_rt_proj_cost_rt_date_tab.count);
2078 dbms_output.put_line('p ct rt ty:'|| l_rt_proj_cost_rt_type_tab.count);
2079 dbms_output.put_line('other :'||lx_rt_others_rejct_reason_tab.count);
2080 dbms_output.put_line('asg id :'||l_prev_rt_asg_id);
2081 dbms_output.put_line('asg id :'||l_prev_rt_asg_id); */
2082
2083 IF l_prev_rt_fi_itemtype = 'UNASSIGNED' THEN
2084 l_amount_calc_mode := 'COST';
2085 ELSE
2086 l_amount_calc_mode := 'ALL';
2087 END IF;
2088
2089 IF P_PA_DEBUG_MODE = 'Y' THEN
2090 PA_DEBUG.g_err_stage := 'Bef calling Rate API for Asg Id:Person Id' ||
2091 TO_CHAR(l_prev_rt_asg_id)||' : '||
2092 TO_CHAR(l_prev_rt_personid);
2093 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2094 END IF;
2095 /* dbms_output.put_line('before calling Rate api :count'||
2096 l_rt_start_date_tab.count); */
2097
2098 IF P_PA_DEBUG_MODE = 'Y' THEN -- Bug 7423839
2099 PA_DEBUG.g_err_stage := 'F I Amt: prj id :'||l_prev_rt_prj_id||' proj type:'||l_prev_rt_prj_type;
2100 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2101 END IF;
2102
2103 Begin
2104 PA_RATE_PVT_PKG.Calc_Rate_Amount(
2105 P_CALLING_MODE => l_prev_rt_fi_itemtype,
2106 p_forecasting_type => 'ORG_FORECASTING',
2107 p_amount_calc_mode => l_amount_calc_mode,
2108 P_RATE_CALC_DATE_TAB => l_rt_start_date_tab,
2109 P_ITEM_ID => l_prev_rt_asg_id,
2110 P_ASSIGN_PRECEDES_TASK => l_prev_rt_asg_precedes_task,
2111 P_LABOR_SCHDL_DISCNT => l_prev_rt_labor_sch_discount,
2112 P_LABOR_BILL_RATE_ORG_ID => l_prev_rt_labor_bill_rt_orgid,
2113 P_LABOR_STD_BILL_RATE_SCHDL => l_prev_rt_labor_std_bl_rt_sch,
2114 P_LABOR_SCHEDULE_FIXED_DATE => l_prev_rt_labor_sch_fixed_dt,
2115 P_LABOR_SCH_TYPE => l_prev_rt_labor_sch_type,
2116 p_forecast_item_id_tab => l_rt_fi_id_tab,
2117 P_ASGN_START_DATE => NULL,
2118 P_PROJECT_ID => l_prev_rt_prj_id,
2119 P_QUANTITY_TAB => l_rt_qty_tab,
2120 P_SYSTEM_LINKAGE => l_rt_system_linkage_tab,
2121 P_FORECAST_JOB_ID => l_prev_rt_fcst_jobid,
2122 P_FORECAST_JOB_GROUP_ID => l_prev_rt_fcst_jobgroupid,
2123 P_PERSON_ID => l_prev_rt_personid,
2124 P_EXPENDITURE_ORG_ID_TAB => l_rt_exp_org_id_tab,
2125 P_EXPENDITURE_TYPE => l_prev_rt_exp_type,
2126 P_EXPENDITURE_ORGZ_ID_TAB => l_rt_exp_organization_id_tab,
2127 P_PROJECT_ORG_ID => l_prev_rt_org_id,
2128 P_LABOR_COST_MULTI_NAME => NULL,
2129 P_PROJ_COST_JOB_GROUP_ID => NULL,
2130 P_JOB_COST_RATE_SCHEDULE_ID => l_prev_rt_cost_rate_schid,
2131 P_PROJECT_TYPE => l_prev_rt_prj_type,
2132 P_TASK_ID => NULL,
2133 P_PROJFUNC_CURRENCY_CODE => l_prev_rt_projfunc_currcode,
2134 P_BILL_RATE_MULTIPLIER => NULL,
2135 P_PROJECT_BILL_JOB_GROUP_ID => l_prev_rt_bill_jobgroup_id,
2136 P_EMP_BILL_RATE_SCHEDULE_ID => l_prev_rt_emp_bilrate_schid,
2137 P_JOB_BILL_RATE_SCHEDULE_ID => l_prev_rt_job_bilrate_schid,
2138 P_DISTRIBUTION_RULE => l_prev_rt_dist_rule,
2139 X_PROJFUNC_BILL_RT_TAB => lx_rt_projfunc_bill_rate_tab,
2140 X_PROJFUNC_RAW_REVENUE_TAB => lx_rt_pfunc_raw_revenue_tab,
2141 X_PROJFUNC_RAW_CST_TAB => lx_rt_pfunc_raw_cost_tab,
2142 X_PROJFUNC_RAW_CST_RT_TAB => lx_rt_pfunc_raw_cost_rt_tab,
2143 X_PROJFUNC_BURDNED_CST_TAB => lx_rt_pfunc_bd_cost_tab,
2144 X_PROJFUNC_BURDNED_CST_RT_TAB => lx_rt_pfunc_bd_cost_rt_tab,
2145 p_projfunc_rev_rt_date_tab => l_rt_pfunc_rev_rt_date_tab,
2146 p_projfunc_rev_rt_type_tab => l_rt_pfunc_rev_rt_type_tab,
2147 p_projfunc_rev_exch_rt_tab => l_rt_pfunc_rev_ex_rt_tab,
2148 p_projfunc_cst_rt_date_tab => l_rt_pfunc_cost_rt_date_tab,
2149 p_projfunc_cst_rt_type_tab => l_rt_pfunc_cost_rt_type_tab,
2150 x_projfunc_rev_rt_date_tab => lx_rt_pfunc_rev_rt_date_tab,
2151 x_projfunc_rev_rt_type_tab => lx_rt_pfunc_rev_rt_type_tab,
2152 x_projfunc_rev_exch_rt_tab => lx_rt_pfunc_rev_ex_rt_tab,
2153 p_projfunc_rev_rt_dt_code_tab => l_rt_pfunc_rev_rt_dt_code_tab,
2154 x_projfunc_cst_rt_date_tab => lx_rt_pfunc_cost_rt_date_tab,
2155 x_projfunc_cst_rt_type_tab => lx_rt_pfunc_cost_rt_type_tab,
2156 x_projfunc_cst_exch_rt_tab => lx_rt_pfunc_cost_ex_rt_tab,
2157 p_project_currency_code => l_prev_rt_proj_currcode,
2158 x_project_bill_rt_tab => lx_rt_proj_bill_rate_tab,
2159 x_project_raw_revenue_tab => lx_rt_proj_raw_revenue_tab,
2160 p_project_rev_rt_date_tab => l_rt_proj_rev_rt_date_tab,
2161 p_project_rev_rt_type_tab => l_rt_proj_rev_rt_type_tab,
2162 p_project_rev_exch_rt_tab => l_rt_proj_rev_ex_rt_tab,
2163 p_project_rev_rt_dt_code_tab => l_rt_proj_rev_rt_dt_code_tab,
2164 x_project_rev_rt_date_tab => lx_rt_proj_rev_rt_date_tab,
2165 x_project_rev_rt_type_tab => lx_rt_proj_rev_rt_type_tab,
2166 x_project_rev_exch_rt_tab => lx_rt_proj_rev_ex_rt_tab,
2167 x_project_raw_cst_tab => lx_rt_proj_raw_cost_tab,
2168 x_project_raw_cst_rt_tab => lx_rt_proj_raw_cost_rt_tab,
2169 x_project_burdned_cst_tab => lx_rt_proj_bd_cost_tab,
2170 x_project_burdned_cst_rt_tab => lx_rt_proj_bd_cost_rt_tab,
2171 p_project_cst_rt_date_tab => l_rt_proj_cost_rt_date_tab,
2172 p_project_cst_rt_type_tab => l_rt_proj_cost_rt_type_tab,
2173 x_project_cst_rt_date_tab => lx_rt_proj_cost_rt_date_tab,
2174 x_project_cst_rt_type_tab => lx_rt_proj_cost_rt_type_tab,
2175 x_project_cst_exch_rt_tab => lx_rt_proj_cost_ex_rt_tab,
2176 x_exp_func_curr_code_tab => lx_rt_expfunc_curr_code_tab,
2177 x_exp_func_raw_cst_rt_tab => lx_rt_expfunc_raw_cst_rt_tab,
2178 x_exp_func_raw_cst_tab => lx_rt_expfunc_raw_cst_tab,
2179 x_exp_func_burdned_cst_rt_tab => lx_rt_expfunc_bd_cst_rt_tab,
2180 x_exp_func_burdned_cst_tab => lx_rt_expfunc_bd_cst_tab,
2181 x_exp_func_cst_rt_date_tab => lx_rt_expfunc_cost_rt_date_tab,
2182 x_exp_func_cst_rt_type_tab => lx_rt_expfunc_cost_rt_type_tab,
2183 x_exp_func_cst_exch_rt_tab => lx_rt_expfunc_cost_ex_rt_tab,
2184 x_cst_txn_curr_code_tab => lx_rt_cost_txn_curr_code_tab,
2185 x_txn_raw_cst_rt_tab => lx_rt_txn_raw_cost_rt_tab,
2186 x_txn_raw_cst_tab => lx_rt_txn_raw_cost_tab,
2187 x_txn_burdned_cst_rt_tab => lx_rt_txn_bd_cost_rt_tab,
2188 x_txn_burdned_cst_tab => lx_rt_txn_bd_cost_tab,
2189 x_rev_txn_curr_code_tab => lx_rt_rev_txn_curr_code_tab,
2190 x_txn_rev_bill_rt_tab => lx_rt_txn_rev_bill_rt_tab,
2191 x_txn_rev_raw_revenue_tab => lx_rt_txn_raw_revenue_tab,
2192 X_ERROR_MSG => lx_rt_error_msg,
2193 X_REV_REJCT_REASON_TAB => lx_rt_rev_rejct_reason_tab,
2194 X_CST_REJCT_REASON_TAB => lx_rt_cst_rejct_reason_tab,
2195 X_BURDNED_REJCT_REASON_TAB => lx_rt_bd_rejct_reason_tab,
2196 X_OTHERS_REJCT_REASON_TAB => lx_rt_others_rejct_reason_tab,
2197 X_RETURN_STATUS => lx_rt_return_status,
2198 X_MSG_COUNT => lx_rt_msg_count,
2199 X_MSG_DATA => lx_rt_msg_data );
2200
2201 exception -- Bug 7423839
2202 when others then
2203 IF P_PA_DEBUG_MODE = 'Y' THEN
2204 PA_DEBUG.g_err_stage := 'Error in PA_RATE_PVT_PKG.Calc_Rate_Amount';
2205 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2206 PA_DEBUG.Log_Message(p_message => 'x_msg_count '||lx_rt_msg_count || 'l_msg_data '||substr(lx_rt_msg_data,1,300));
2207 PA_DEBUG.Log_Message(p_message => 'X_ERROR_MSG '||substr(lx_rt_error_msg,1,300) || 'X_RETURN_STATUS '||lx_rt_return_status);
2208 END IF;
2209 Raise;
2210 end;
2211
2212 IF P_PA_DEBUG_MODE = 'Y' THEN
2213 PA_DEBUG.g_err_stage := 'Aft calling Rate API';
2214 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2215 END IF;
2216 /* dbms_output.put_line('after calling Rate api'); */
2217
2218 FOR k IN l_fi_process_flag_tab.FIRST .. l_fi_process_flag_tab.LAST LOOP
2219 IF l_fi_process_flag_tab(k) = 'Y' THEN
2220 /* dbms_output.put_line('fi id in rt:'||k ||':'||
2221 l_fi_id_tab(k)); */
2222
2223 IF lx_rt_cost_txn_curr_code_tab.exists(k) THEN
2224 l_tp_denom_currcode(k) := lx_rt_cost_txn_curr_code_tab(k);
2225 END IF;
2226 IF lx_rt_txn_raw_cost_tab.exists(k) THEN
2227 l_tp_denom_raw_cost(k) := lx_rt_txn_raw_cost_tab(k);
2228 END IF;
2229 IF lx_rt_txn_bd_cost_tab.exists(k) THEN
2230 l_tp_denom_bd_cost(k) := lx_rt_txn_bd_cost_tab(k);
2231 END IF;
2232 /* Transfer price API supports only Txn currency for Cost and for
2233 Revenue always Project Functional Currency Amount should be passed
2234 bug 2444090 */
2235 IF lx_rt_pfunc_raw_revenue_tab.exists(k) THEN
2236 l_tp_raw_revenue(k) := lx_rt_pfunc_raw_revenue_tab(k);
2237 END IF;
2238
2239
2240 IF lx_rt_pfunc_raw_cost_tab.exists(k) THEN
2241 l_fia_projfunc_raw_cost(k) := lx_rt_pfunc_raw_cost_tab(k);
2242 END IF;
2243 IF lx_rt_pfunc_bd_cost_tab.exists(k) THEN
2244 l_fia_projfunc_bd_cost(k) := lx_rt_pfunc_bd_cost_tab(k);
2245 END IF;
2246 IF lx_rt_pfunc_raw_revenue_tab.exists(k) THEN
2247 l_fia_projfunc_revenue(k) := lx_rt_pfunc_raw_revenue_tab(k);
2248 END IF;
2249
2250 /* dbms_output.put_line('prjfunc raw cost :'||l_fia_projfunc_raw_cost(k) );
2251 dbms_output.put_line('prjfunc bd cost :'||l_fia_projfunc_bd_cost(k) );
2252 dbms_output.put_line('prjfunc rev :'||l_fia_projfunc_revenue(k) ); */
2253
2254 IF lx_rt_pfunc_cost_rt_type_tab.exists(k) THEN
2255 l_fia_projfunc_cost_rate_type(k) := lx_rt_pfunc_cost_rt_type_tab(k);
2256 END IF;
2257 IF lx_rt_pfunc_cost_rt_date_tab.exists(k) THEN
2258 l_fia_projfunc_cost_rate_date(k) := lx_rt_pfunc_cost_rt_date_tab(k);
2259 END IF;
2260 IF lx_rt_pfunc_cost_ex_rt_tab.exists(k) THEN
2261 l_fia_projfunc_cost_ex_rate(k) := lx_rt_pfunc_cost_ex_rt_tab(k);
2262 END IF;
2263
2264 IF lx_rt_pfunc_rev_rt_type_tab.exists(k) THEN
2265 l_fia_projfunc_rev_rate_type(k) := lx_rt_pfunc_rev_rt_type_tab(k);
2266 END IF;
2267
2268 IF lx_rt_pfunc_rev_rt_date_tab.exists(k) THEN
2269 l_fia_projfunc_rev_rate_date(k) := lx_rt_pfunc_rev_rt_date_tab(k);
2270 END IF;
2271 IF lx_rt_pfunc_rev_ex_rt_tab.exists(k) THEN
2272 l_fia_projfunc_rev_ex_rate(k) := lx_rt_pfunc_rev_ex_rt_tab(k);
2273 END IF;
2274
2275 IF lx_rt_proj_raw_cost_tab.exists(k) THEN
2276 l_fia_proj_raw_cost(k) := lx_rt_proj_raw_cost_tab(k);
2277 END IF;
2278 IF lx_rt_proj_bd_cost_tab.exists(k) THEN
2279 l_fia_proj_bd_cost(k) := lx_rt_proj_bd_cost_tab(k);
2280 END IF;
2281 IF lx_rt_proj_raw_revenue_tab.exists(k) THEN
2282 l_fia_proj_revenue(k) := lx_rt_proj_raw_revenue_tab(k);
2283 END IF;
2284 /* dbms_output.put_line('prj raw cost :'||l_fia_proj_raw_cost(k) );
2285 dbms_output.put_line('prj bd cost :'||l_fia_proj_bd_cost(k) );
2286 dbms_output.put_line('prj rev :'||l_fia_proj_revenue(k) ); */
2287 IF lx_rt_proj_cost_rt_type_tab.exists(k) THEN
2288 l_fia_proj_cost_rate_type(k) := lx_rt_proj_cost_rt_type_tab(k);
2289 END IF;
2290 IF lx_rt_proj_cost_rt_date_tab.exists(k) THEN
2291 l_fia_proj_cost_rate_date(k) := lx_rt_proj_cost_rt_date_tab(k);
2292 END IF;
2293 IF lx_rt_proj_cost_ex_rt_tab.exists(k) THEN
2294 l_fia_proj_cost_ex_rate(k) := lx_rt_proj_cost_ex_rt_tab(k);
2295 /* dbms_output.put_line('prj ex rt:'|| lx_rt_proj_cost_ex_rt_tab(k)); */
2296 END IF;
2297 IF lx_rt_proj_rev_rt_type_tab.exists(k) THEN
2298 l_fia_proj_rev_rate_type(k) := lx_rt_proj_rev_rt_type_tab(k);
2299 END IF;
2300 IF lx_rt_proj_rev_rt_date_tab.exists(k) THEN
2301 l_fia_proj_rev_rate_date(k) := lx_rt_proj_rev_rt_date_tab(k);
2302 END IF;
2303 IF lx_rt_proj_rev_ex_rt_tab.exists(k) THEN
2304 l_fia_proj_rev_ex_rate(k) := lx_rt_proj_rev_ex_rt_tab(k);
2305 END IF;
2306
2307 IF lx_rt_expfunc_curr_code_tab.exists(k) THEN
2308 l_fia_expfunc_curr_code(k) := lx_rt_expfunc_curr_code_tab(k);
2309 END IF;
2310 IF lx_rt_expfunc_cost_rt_type_tab.exists(k) THEN
2311 l_fia_expfunc_cost_rate_type(k) := lx_rt_expfunc_cost_rt_type_tab(k);
2312 END IF;
2313 IF lx_rt_expfunc_cost_rt_date_tab.exists(k) THEN
2314 l_fia_expfunc_cost_rate_date(k) := lx_rt_expfunc_cost_rt_date_tab(k);
2315 END IF;
2316 IF lx_rt_expfunc_cost_ex_rt_tab.exists(k) THEN
2317 l_fia_expfunc_cost_ex_rate(k) := lx_rt_expfunc_cost_ex_rt_tab(k);
2318 END IF;
2319 IF lx_rt_expfunc_raw_cst_tab.exists(k) THEN
2320 l_fia_expfunc_raw_cost(k) := lx_rt_expfunc_raw_cst_tab(k);
2321 END IF;
2322 IF lx_rt_expfunc_bd_cst_tab.exists(k) THEN
2323 l_fia_expfunc_bd_cost(k) := lx_rt_expfunc_bd_cst_tab(k);
2324 END IF;
2325
2326 IF lx_rt_cost_txn_curr_code_tab.exists(k) THEN
2327 l_fia_cost_txn_curr_code(k) := lx_rt_cost_txn_curr_code_tab(k);
2328 END IF;
2329 IF lx_rt_rev_txn_curr_code_tab.exists(k) THEN
2330 l_fia_rev_txn_curr_code(k) := lx_rt_rev_txn_curr_code_tab(k);
2331 END IF;
2332 IF lx_rt_txn_raw_cost_tab.exists(k) THEN
2333 l_fia_txn_raw_cost(k) := lx_rt_txn_raw_cost_tab(k);
2334 END IF;
2335 IF lx_rt_txn_bd_cost_tab.exists(k) THEN
2336 l_fia_txn_bd_cost(k) := lx_rt_txn_bd_cost_tab(k);
2337 END IF;
2338 IF lx_rt_txn_raw_revenue_tab.exists(k) THEN
2339 l_fia_txn_revenue(k) := lx_rt_txn_raw_revenue_tab(k);
2340 END IF;
2341
2342
2343 IF lx_rt_cst_rejct_reason_tab.exists(k) THEN
2344 l_fi_cst_rejct_reason_tab(k) := lx_rt_cst_rejct_reason_tab(k);
2345 END IF;
2346 IF lx_rt_rev_rejct_reason_tab.exists(k) THEN
2347 l_fi_rev_rejct_reason_tab(k) := lx_rt_rev_rejct_reason_tab(k);
2348 END IF;
2349 IF lx_rt_bd_rejct_reason_tab.exists(k) THEN
2350 l_fi_bd_rejct_reason_tab(k) := lx_rt_bd_rejct_reason_tab(k);
2351 END IF;
2352 IF lx_rt_others_rejct_reason_tab.exists(k) THEN
2353 l_fi_others_rejct_reason_tab(k) := lx_rt_others_rejct_reason_tab(k);
2354 END IF;
2355 IF l_fi_process_flag_tab(k) = 'Y' AND
2356 ( l_fi_rev_rejct_reason_tab(k) IS NOT NULL OR
2357 l_fi_cst_rejct_reason_tab(k) IS NOT NULL OR
2358 l_fi_bd_rejct_reason_tab(k) IS NOT NULL OR
2359 l_fi_others_rejct_reason_tab(k) IS NOT NULL ) THEN
2360 /* dbms_output.put_line('inside rate api error');
2361 dbms_output.put_line('fi id:'||l_fi_id_tab(k));
2362 dbms_output.put_line('rev rej code :'||l_fi_rev_rejct_reason_tab(k));
2363 dbms_output.put_line('cst rej code :'||l_fi_cst_rejct_reason_tab(k));
2364 dbms_output.put_line('bd rej code :'||l_fi_bd_rejct_reason_tab(k));
2365 dbms_output.put_line('ot rej code :'||l_fi_others_rejct_reason_tab(k));
2366 dbms_output.put_line('p curr:'||l_prj_curr_code_tab(k)); */
2367 lx_cc_status_tab(k) := 'E';
2368 lx_tp_error_code(k) := 'E';
2369 l_fi_process_flag_tab(k) := 'N';
2370 END IF;
2371 END IF; /* for process flag tab = Y to avoid the index problem */
2372 END LOOP;
2373
2374 l_rt_fi_id_tab.delete;
2375 l_rt_start_date_tab.delete;
2376 l_rt_qty_tab.delete;
2377 l_rt_system_linkage_tab.delete;
2378 l_rt_exp_org_id_tab.delete;
2379 l_rt_exp_organization_id_tab.delete;
2380 lx_rt_others_rejct_reason_tab.delete;
2381
2382 l_rt_pfunc_rev_rt_date_tab.delete;
2383 l_rt_pfunc_rev_rt_type_tab.delete;
2384 l_rt_pfunc_rev_ex_rt_tab.delete;
2385
2386 l_rt_pfunc_cost_rt_date_tab.delete;
2387 l_rt_pfunc_cost_rt_type_tab.delete;
2388
2389 l_rt_proj_cost_rt_date_tab.delete;
2390 l_rt_proj_cost_rt_type_tab.delete;
2391 l_rt_proj_rev_rt_date_tab.delete;
2392 l_rt_proj_rev_rt_type_tab.delete;
2393 l_rt_proj_rev_ex_rt_tab.delete;
2394 l_rt_proj_rev_rt_dt_code_tab.delete;
2395 l_rt_pfunc_rev_rt_dt_code_tab.delete;
2396 END IF; /* for rate table count > 0 */
2397
2398 l_prev_rt_prj_id := l_fi_projid_tab(m);
2399 l_prev_rt_asg_id := l_fi_asgid_tab(m);
2400 l_prev_rt_personid := l_fi_personid_tab(m);
2401 l_prev_rt_fi_itemtype := l_fi_item_type_tab(m);
2402 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2403 l_prev_rt_calling_mode := l_fi_item_type_tab(m);
2404 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(m);
2405 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(m);
2406 l_prev_rt_exp_type := l_fi_exptype_tab(m);
2407 l_prev_rt_org_id := l_fi_proj_orgid_tab(m);
2408 l_prev_rt_prj_type := l_prj_type_tab(m);
2409 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(m);
2410 l_prev_rt_proj_currcode := l_prj_curr_code_tab(m);
2411 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(m);
2412 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(m);
2413 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(m);
2414 l_prev_rt_dist_rule := l_distribution_rule_tab(m);
2415 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(m);
2416
2417 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(m);
2418 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(m);
2419 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(m);
2420 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(m);
2421 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(m);
2422 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(m);
2423
2424 /* dbms_output.put_line('inside any changes asg id :'||l_fi_asgid_tab(m)); */
2425
2426 END IF; /* if prj or asg id or person id changes */
2427 IF l_fi_process_flag_tab(m) = 'Y' THEN
2428 l_rt_fi_id_tab(m) := l_fi_id_tab(m);
2429 l_rt_start_date_tab(m) := l_fi_date_tab(m);
2430 l_rt_qty_tab(m) := l_fi_qty_tab(m);
2431 l_rt_system_linkage_tab(m) := l_fi_exptypeclass_tab(m);
2432 l_rt_exp_org_id_tab(m) := l_fi_exp_orgid_tab(m);
2433 l_rt_exp_organization_id_tab(m) := l_fi_exp_organizationid_tab(m);
2434 lx_rt_others_rejct_reason_tab(m) := l_fi_others_rejct_reason_tab(m);
2435
2436 l_rt_pfunc_rev_rt_date_tab(m) := l_prjfunc_bil_rate_date_tab(m);
2437 l_rt_pfunc_rev_rt_type_tab(m) := l_prjfunc_bil_rate_type_tab(m);
2438 l_rt_pfunc_rev_ex_rt_tab(m) := l_prjfunc_bil_ex_rate_tab(m);
2439 l_rt_pfunc_cost_rt_date_tab(m) := l_prjfunc_cost_rt_dt_tab(m);
2440 l_rt_pfunc_cost_rt_type_tab(m) := l_prjfunc_cost_rt_type_tab(m);
2441 l_rt_proj_cost_rt_date_tab(m) := l_prj_rate_date_tab(m);
2442 l_rt_proj_cost_rt_type_tab(m) := l_prj_rate_type_tab(m);
2443 l_rt_proj_rev_rt_date_tab(m) := l_prj_bil_rate_date_tab(m);
2444 l_rt_proj_rev_rt_type_tab(m) := l_prj_bil_rate_type_tab(m);
2445 l_rt_proj_rev_ex_rt_tab(m) := l_prj_bil_ex_rate_tab(m);
2446 l_rt_proj_rev_rt_dt_code_tab(m) := l_prjfunc_bil_rt_dt_code_tab(m);
2447 l_rt_pfunc_rev_rt_dt_code_tab(m) := l_prj_bil_rate_dt_code_tab(m);
2448
2449
2450 END IF;
2451 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2452 END LOOP; /* for I1 rate api call */
2453
2454 lx_cc_error_stage := NULL;
2455 lx_cc_error_code := NULL;
2456
2457 /* dbms_output.put_line('bef calling cc ident ');
2458 dbms_output.put_line('count exp orgzid :'||l_fi_exp_organizationid_tab.count);
2459 dbms_output.put_line('count exp org id :'||l_fi_exp_orgid_tab.count);
2460 dbms_output.put_line('count prj id :'||l_fi_projid_tab.count);
2461 dbms_output.put_line('count tsk id :'||l_cc_taskid_tab.count);
2462 dbms_output.put_line('count fi dt id :'||l_fi_date_tab.count);
2463 dbms_output.put_line('count ex item id :'||l_cc_expitemid_tab.count);
2464 dbms_output.put_line('count person id :'||l_fi_personid_tab.count);
2465 dbms_output.put_line('count exp type :'||l_fi_exptype_tab.count);
2466 dbms_output.put_line('count sys link :'||l_fi_exptypeclass_tab.count);
2467 dbms_output.put_line('count prj orgz id:'||l_fi_proj_organizationid_tab.count);
2468 dbms_output.put_line('count prj org id:'||l_fi_proj_orgid_tab.count);
2469 dbms_output.put_line('count tran source:'||l_cc_transsource_tab.count);
2470 dbms_output.put_line('count nl orgz id :'||l_cc_NLOrgzid_tab.count);
2471 dbms_output.put_line('count prvdr eiid :'||l_cc_prvdreid_tab.count);
2472 dbms_output.put_line('count recvr eiid :'||l_cc_recvreid_tab.count);
2473 dbms_output.put_line('count status :'||lx_cc_status_tab.count);
2474 dbms_output.put_line('count cc type :'||lx_cc_type_tab.count);
2475 dbms_output.put_line('count cc code :'||lx_cc_code_tab.count);
2476 dbms_output.put_line('count prvdr orgz :'||lx_cc_prvdr_orgzid_tab.count);
2477 dbms_output.put_line('count recvr orgz :'||lx_cc_recvr_orgzid_tab.count);
2478 dbms_output.put_line('count recvr org :'||lx_cc_recvr_orgid_tab.count);
2479 dbms_output.put_line('count prvdr org :'||lx_cc_prvdr_orgid_tab.count); */
2480
2481 IF P_PA_DEBUG_MODE = 'Y' THEN
2482 PA_DEBUG.g_err_stage := 'Bef calling CC API';
2483 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2484 END IF;
2485
2486 Pa_Cc_Ident.PA_CC_IDENTIFY_TXN_FI(
2487 P_ExpOrganizationIdTab => l_fi_exp_organizationid_tab,
2488 P_ExpOrgidTab => l_fi_exp_orgid_tab,
2489 P_ProjectIdTab => l_fi_projid_tab,
2490 P_TaskIdTab => l_cc_taskid_tab,
2491 P_ExpItemDateTab => l_fi_date_tab,
2492 P_ExpItemIdTab => l_cc_expitemid_tab,
2493 P_PersonIdTab => l_fi_personid_tab,
2494 P_ExpTypeTab => l_fi_exptype_tab,
2495 P_SysLinkTab => l_fi_exptypeclass_tab,
2496 P_PrjOrganizationIdTab => l_fi_proj_organizationid_tab,
2497 P_PrjOrgIdTab => l_fi_proj_orgid_tab,
2498 P_TransSourceTab => l_cc_transsource_tab,
2499 P_NLROrganizationIdTab => l_cc_NLOrgzid_tab,
2500 P_PrvdrLEIdTab => l_cc_prvdreid_tab,
2501 P_RecvrLEIdTab => l_cc_recvreid_tab,
2502 X_StatusTab => lx_cc_status_tab,
2503 X_CrossChargeTypeTab => lx_cc_type_tab,
2504 X_CrossChargeCodeTab => lx_cc_code_tab,
2505 X_PrvdrOrganizationIdTab => lx_cc_prvdr_orgzid_tab,
2506 X_RecvrOrganizationIdTab => lx_cc_recvr_orgzid_tab,
2507 X_RecvrOrgIdTab => lx_cc_recvr_orgid_tab,
2508 X_PrvdrOrgIdTab => lx_cc_prvdr_orgid_tab,
2509 X_Error_Stage => lx_cc_error_stage,
2510 X_Error_Code => lx_cc_error_code );
2511
2512 IF P_PA_DEBUG_MODE = 'Y' THEN
2513 PA_DEBUG.g_err_stage := 'Aft calling CC API';
2514 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2515 END IF;
2516
2517 /* dbms_output.put_line('aft calling cc ident ');
2518 dbms_output.put_line('count exp orgzid :'||l_fi_exp_organizationid_tab.count);
2519 dbms_output.put_line('count exp org id :'||l_fi_exp_orgid_tab.count);
2520 dbms_output.put_line('count prj id :'||l_fi_projid_tab.count);
2521 dbms_output.put_line('count tsk id :'||l_cc_taskid_tab.count);
2522 dbms_output.put_line('count fi dt id :'||l_fi_date_tab.count);
2523 dbms_output.put_line('count ex item id :'||l_cc_expitemid_tab.count);
2524 dbms_output.put_line('count person id :'||l_fi_personid_tab.count);
2525 dbms_output.put_line('count exp type :'||l_fi_exptype_tab.count);
2526 dbms_output.put_line('count sys link :'||l_fi_exptypeclass_tab.count);
2527 dbms_output.put_line('count prj orgz id:'||l_fi_proj_organizationid_tab.count);
2528 dbms_output.put_line('count prj org id:'||l_fi_proj_orgid_tab.count);
2529 dbms_output.put_line('count tran source:'||l_cc_transsource_tab.count);
2530 dbms_output.put_line('count nl orgz id :'||l_cc_NLOrgzid_tab.count);
2531 dbms_output.put_line('count prvdr eiid :'||l_cc_prvdreid_tab.count);
2532 dbms_output.put_line('count recvr eiid :'||l_cc_recvreid_tab.count);
2533 dbms_output.put_line('count status :'||lx_cc_status_tab.count);
2534 dbms_output.put_line('count cc type :'||lx_cc_type_tab.count);
2535 dbms_output.put_line('count cc code :'||lx_cc_code_tab.count);
2536 dbms_output.put_line('count prvdr orgz :'||lx_cc_prvdr_orgzid_tab.count);
2537 dbms_output.put_line('count recvr orgz :'||lx_cc_recvr_orgzid_tab.count);
2538 dbms_output.put_line('count recvr org :'||lx_cc_recvr_orgid_tab.count);
2539 dbms_output.put_line('count prvdr org :'||lx_cc_prvdr_orgid_tab.count); */
2540
2541 FOR l_temp IN 1 .. l_fi_date_tab.count LOOP
2542 IF lx_cc_status_tab(l_temp) IS NOT NULL AND
2543 lx_cc_status_tab(l_temp) <> 'E' THEN
2544 l_fi_tp_rejct_reason_tab(l_temp) := lx_cc_status_tab(l_temp);
2545 lx_tp_error_code(l_temp) := lx_cc_status_tab(l_temp);
2546 l_fi_process_flag_tab(l_temp) := 'N';
2547 ELSIF lx_cc_status_tab(l_temp) IS NULL AND
2548 lx_cc_code_tab(l_temp) NOT IN ( 'B','I') THEN
2549 lx_tp_error_code(l_temp) := 'E';
2550 END IF;
2551 /* dbms_output.put_line('error code :'||lx_tp_error_code(l_temp));
2552 dbms_output.put_line('exp orgid :'||l_fi_exp_orgid_tab(l_temp));
2553 dbms_output.put_line('papd :'||l_fi_pvdr_papd_tab(l_temp)); */
2554
2555 l_tp_pa_date(l_temp) := l_fi_date_tab(l_temp);
2556
2557 IF lx_tp_error_code(l_temp) IS NULL THEN
2558 BEGIN
2559 SELECT End_Date INTO l_tp_pa_date(l_temp)
2560 FROM Pa_Periods_All WHERE
2561 PERIOD_NAME = l_fi_pvdr_papd_tab(l_temp) AND
2562 -- begin:bug:5938943: NVL function has been removed to achieve the performance gain
2563 Org_Id = l_fi_exp_orgid_tab(l_temp);
2564 -- end:bug:5938943
2565 EXCEPTION
2566 WHEN NO_DATA_FOUND THEN
2567 l_fi_process_flag_tab(l_temp) := 'N';
2568 lx_tp_error_code(l_temp) := 'E';
2569 l_fi_others_rejct_reason_tab(l_temp) := 'PA_FP_PVDR_PA_PD_NOT_FOUND';
2570 END;
2571 END IF;
2572
2573 /* assign TP values */
2574
2575 l_tp_labor_nl_flag(l_temp) := 'Y';
2576 l_tp_taskid(l_temp) := NULL;
2577 l_tp_rev_distributed_flag(l_temp) := 'Y';
2578 l_tp_compute_flag(l_temp) := 'Y';
2579 l_tp_nl_resource(l_temp) := NULL;
2580 l_tp_nl_resource_orgzid(l_temp) := NULL;
2581
2582 l_tp_asg_precedes_task_tab(l_temp) := l_asg_precedes_task; -- Added for bug 3260017
2583
2584 /* set NULL to all tp out variables */
2585
2586 lx_proj_tp_rate_type(l_temp) := NULL;
2587 lx_proj_tp_rate_date(l_temp) := NULL;
2588 lx_proj_tp_exchange_rate(l_temp) := NULL;
2589 lx_proj_tp_amt(l_temp) := NULL;
2590
2591 lx_projfunc_tp_rate_type(l_temp) := NULL;
2592 lx_projfunc_tp_rate_date(l_temp) := NULL;
2593 lx_projfunc_tp_exchange_rate(l_temp) := NULL;
2594 lx_projfunc_tp_amt(l_temp) := NULL;
2595
2596 lx_denom_tp_currcode(l_temp) := NULL;
2597 lx_denom_tp_amt(l_temp) := NULL;
2598
2599 lx_expfunc_tp_rate_type(l_temp) := NULL;
2600 lx_expfunc_tp_rate_date(l_temp) := NULL;
2601 lx_expfunc_tp_exchange_rate(l_temp) := NULL;
2602 lx_expfunc_tp_amt(l_temp) := NULL;
2603
2604 lx_cc_markup_basecode(l_temp) := NULL;
2605 lx_tp_ind_compiled_setid(l_temp) := NULL;
2606 lx_tp_bill_rate(l_temp) := NULL;
2607 lx_tp_base_amount(l_temp) := NULL;
2608 lx_tp_bill_markup_percent(l_temp) := NULL;
2609 lx_tp_sch_line_percent(l_temp) := NULL;
2610 lx_tp_rule_percent(l_temp) := NULL;
2611 lx_tp_job_id(l_temp) := NULL;
2612
2613
2614
2615 END LOOP;
2616
2617 /* FOR i_dummy in 1 .. lx_tp_error_code.count loop
2618 dbms_output.put_line('tp error code <'||i_dummy||'>'||
2619 lx_tp_error_code(i_dummy));
2620 dbms_output.put_line('sys link:exp cate:'|| l_fi_exptypeclass_tab(i_dummy)
2621 ||':'||l_tp_exp_category(i_dummy) );
2622
2623 END LOOP; */
2624
2625
2626 /* dbms_output.put_line ( lx_cc_prvdr_orgid_tab.count );
2627 dbms_output.put_line ( lx_cc_prvdr_orgzid_tab.count );
2628 dbms_output.put_line ( lx_cc_recvr_orgid_tab.count );
2629 dbms_output.put_line ( lx_cc_recvr_orgzid_tab.count );
2630 dbms_output.put_line ( l_fi_exp_organizationid_tab.count );
2631 dbms_output.put_line ( l_tp_exp_itemid.count );
2632 dbms_output.put_line ( l_fi_exptype_tab.count );
2633 dbms_output.put_line ( l_tp_exp_category.count );
2634 dbms_output.put_line ( l_fi_date_tab.count );
2635 dbms_output.put_line ( l_tp_labor_nl_flag.count );
2636 dbms_output.put_line ( l_fi_exptypeclass_tab.count );
2637 dbms_output.put_line ( l_tp_taskid.count );
2638 dbms_output.put_line ( l_tp_scheduleid.count );
2639 dbms_output.put_line ( l_tp_denom_currcode.count );
2640 dbms_output.put_line ( l_prj_curr_code_tab.count );
2641 dbms_output.put_line ( l_prjfunc_curr_code_tab.count );
2642 dbms_output.put_line( l_tp_rev_distributed_flag.count );
2643 dbms_output.put_line ( l_tp_compute_flag.count );
2644 dbms_output.put_line ( l_tp_fixed_date.count );
2645 dbms_output.put_line ( l_tp_denom_raw_cost.count );
2646 dbms_output.put_line ( l_tp_denom_bd_cost.count );
2647 dbms_output.put_line ( l_tp_raw_revenue.count );
2648 dbms_output.put_line ( l_tp_asg_precedes_task_tab.count ); -- Added for bug 3260017
2649 dbms_output.put_line ( l_fi_projid_tab.count );
2650 dbms_output.put_line ( l_fi_qty_tab.count );
2651 dbms_output.put_line ( l_fi_personid_tab.count );
2652 dbms_output.put_line ( l_asg_fcst_jobid_tab.count );
2653 dbms_output.put_line ( l_tp_nl_resource.count );
2654 dbms_output.put_line( l_tp_nl_resource_orgzid.count );
2655 dbms_output.put_line ( l_tp_pa_date.count );
2656 dbms_output.put_line ( l_tp_array_size );
2657 dbms_output.put_line ( l_tp_debug_mode );
2658 dbms_output.put_line ( l_fi_amount_type_tab.count );
2659 dbms_output.put_line ( l_fi_asgid_tab.count );
2660 dbms_output.put_line ( lx_proj_tp_rate_type.count );
2661 dbms_output.put_line ( lx_proj_tp_rate_date.count );
2662 dbms_output.put_line ( lx_proj_tp_exchange_rate.count );
2663 dbms_output.put_line ( lx_proj_tp_amt.count );
2664 dbms_output.put_line ( lx_projfunc_tp_rate_type.count );
2665 dbms_output.put_line ( lx_proj_tp_rate_date.count );
2666 dbms_output.put_line ( lx_proj_tp_exchange_rate.count );
2667 dbms_output.put_line ( lx_proj_tp_amt.count );
2668 dbms_output.put_line ( lx_denom_tp_currcode.count );
2669 dbms_output.put_line ( lx_denom_tp_amt.count );
2670 dbms_output.put_line ( lx_expfunc_tp_rate_type.count );
2671 dbms_output.put_line ( lx_expfunc_tp_rate_date.count );
2672 dbms_output.put_line ( lx_expfunc_tp_exchange_rate.count );
2673 dbms_output.put_line ( lx_expfunc_tp_amt.count );
2674 dbms_output.put_line ( lx_cc_markup_basecode.count );
2675 dbms_output.put_line ( lx_tp_ind_compiled_setid.count );
2676 dbms_output.put_line ( lx_tp_bill_rate.count );
2677 dbms_output.put_line ( lx_tp_base_amount.count );
2678 dbms_output.put_line ( lx_tp_bill_markup_percent.count );
2679 dbms_output.put_line( lx_tp_sch_line_percent.count );
2680 dbms_output.put_line ( lx_tp_rule_percent.count );
2681 dbms_output.put_line ( lx_tp_job_id.count );
2682 dbms_output.put_line ( lx_tp_error_code.count ); */
2683
2684 /* call Trf Price API to get Cross Charge Amounts */
2685
2686 l_tp_asgid.delete;
2687 l_call_tp_api_flag := 'N';
2688
2689 FOR l_tp_asg_idx IN 1 .. l_fi_asgid_tab.COUNT LOOP
2690 IF l_fi_asgid_tab(l_tp_asg_idx) <= 0 THEN
2691 l_tp_asgid(l_tp_asg_idx) := NULL;
2692 ELSE
2693 l_tp_asgid(l_tp_asg_idx) := l_fi_asgid_tab(l_tp_asg_idx);
2694 END IF;
2695 IF lx_tp_error_code(l_tp_asg_idx) IS NULL THEN
2696 l_call_tp_api_flag := 'Y';
2697 END IF;
2698 END LOOP;
2699
2700 IF l_call_tp_api_flag = 'Y' THEN
2701 FORALL b_tmp IN 1 .. l_fi_id_tab.COUNT
2702 INSERT INTO Pa_Fi_Amount_Dtls_Tmp(
2703 FORECAST_ITEM_ID ,
2704 ITEM_DATE ,
2705 ITEM_UOM ,
2706 ITEM_QUANTITY ,
2707 COST_TXN_CURRENCY_CODE ,
2708 REVENUE_TXN_CURRENCY_CODE ,
2709 TXN_RAW_COST ,
2710 TXN_BURDENED_COST ,
2711 TXN_REVENUE ,
2712 TP_TXN_CURRENCY_CODE_IN ,
2713 TP_TXN_CURRENCY_CODE_OUT ,
2714 TXN_TRANSFER_PRICE ,
2715 PROJECT_CURRENCY_CODE ,
2716 PROJECT_COST_RATE_DATE ,
2717 PROJECT_COST_RATE_TYPE ,
2718 PROJECT_COST_EXCHANGE_RATE ,
2719 PROJECT_RAW_COST ,
2720 PROJECT_BURDENED_COST ,
2721 PROJECT_REVENUE_RATE_DATE ,
2722 PROJECT_REVENUE_RATE_TYPE ,
2723 PROJECT_REVENUE_EXCHANGE_RATE,
2724 PROJECT_REVENUE ,
2725 PROJECT_TP_RATE_DATE ,
2726 PROJECT_TP_RATE_TYPE ,
2727 PROJECT_TP_EXCHANGE_RATE ,
2728 PROJECT_TRANSFER_PRICE ,
2729 PROJFUNC_CURRENCY_CODE ,
2730 PROJFUNC_COST_RATE_DATE ,
2731 PROJFUNC_COST_RATE_TYPE ,
2732 PROJFUNC_COST_EXCHANGE_RATE ,
2733 PROJFUNC_RAW_COST ,
2734 PROJFUNC_BURDENED_COST ,
2735 PROJFUNC_REVENUE_RATE_DATE ,
2736 PROJFUNC_REVENUE_RATE_TYPE ,
2737 PROJFUNC_REVENUE_EXCHANGE_RATE,
2738 PROJFUNC_REVENUE ,
2739 PROJFUNC_TP_RATE_DATE ,
2740 PROJFUNC_TP_RATE_TYPE ,
2741 PROJFUNC_TP_EXCHANGE_RATE ,
2742 PROJFUNC_TRANSFER_PRICE ,
2743 EXPFUNC_CURRENCY_CODE ,
2744 EXPFUNC_COST_RATE_DATE ,
2745 EXPFUNC_COST_RATE_TYPE ,
2746 EXPFUNC_COST_EXCHANGE_RATE ,
2747 EXPFUNC_RAW_COST ,
2748 EXPFUNC_BURDENED_COST ,
2749 EXPFUNC_TP_RATE_DATE ,
2750 EXPFUNC_TP_RATE_TYPE ,
2751 EXPFUNC_TP_EXCHANGE_RATE ,
2752 EXPFUNC_TRANSFER_PRICE ,
2753 CC_PRVDR_ORG_ID ,
2754 CC_PRVDR_ORGANIZITION_ID ,
2755 CC_RECVR_ORG_ID ,
2756 CC_RECVR_ORGANIZITION_ID ,
2757 EXPENDITURE_ORGANIZATION_ID ,
2758 EXPENDITURE_TYPE ,
2759 EXPENDITURE_TYPE_CLASS ,
2760 EXPENDITURE_CATEGORY ,
2761 TP_LABOR_NL_FLAG ,
2762 TP_TASK_ID ,
2763 TP_SCHEDULE_ID ,
2764 TP_REV_DISTRIBUTED_FLAG ,
2765 TP_COMPUTE_FLAG ,
2766 TP_FIXED_DATE ,
2767 PROJECT_ID ,
2768 PERSON_ID ,
2769 FORECAST_JOB_ID ,
2770 TP_NL_RESOURCE ,
2771 TP_NL_RESOURCE_ORGZ_ID ,
2772 TP_PA_DATE ,
2773 TP_AMOUNT_TYPE ,
2774 assignment_id ,
2775 fi_process_flag ,
2776 delete_flag ,
2777 tp_error_code ,
2778 COST_REJECTION_CODE ,
2779 REV_REJECTION_CODE ,
2780 BURDEN_REJECTION_CODE ,
2781 OTHER_REJECTION_CODE ,
2782 TP_DENOM_RAW_COST ,
2783 TP_DENOM_BURDENED_COST ,
2784 TP_RAW_REVENUE ,
2785 tp_ind_compiled_setid ,
2786 tp_bill_rate ,
2787 tp_base_amount ,
2788 tp_bill_markup_percent ,
2789 tp_sch_line_percent ,
2790 tp_rule_percent ,
2791 tp_job_id ,
2792 cc_markup_basecode )
2793 VALUES(
2794 l_fi_id_tab(b_tmp),
2795 l_fi_date_tab(b_tmp),
2796 l_fi_uom_tab(b_tmp),
2797 l_fi_qty_tab(b_tmp),
2798 l_fia_cost_txn_curr_code(b_tmp),
2799 l_fia_rev_txn_curr_code(b_tmp),
2800 l_fia_txn_raw_cost(b_tmp),
2801 l_fia_txn_bd_cost(b_tmp),
2802 l_fia_txn_revenue(b_tmp),
2803 l_tp_denom_currcode(b_tmp), -- tp txn curr code
2804 lx_denom_tp_currcode(b_tmp),
2805 null, -- TXN_TRANSFER_PRICE
2806 l_prj_curr_code_tab(b_tmp),
2807 l_fia_proj_cost_rate_date(b_tmp),
2808 l_fia_proj_cost_rate_type(b_tmp),
2809 l_fia_proj_cost_ex_rate(b_tmp),
2810 l_fia_proj_raw_cost(b_tmp),
2811 l_fia_proj_bd_cost(b_tmp),
2812 l_fia_proj_rev_rate_date(b_tmp),
2813 l_fia_proj_rev_rate_type(b_tmp),
2814 l_fia_proj_rev_ex_rate(b_tmp),
2815 l_fia_proj_revenue(b_tmp),
2816 null, -- PROJECT_TP_RATE_DATE
2817 null, -- PROJECT_TP_RATE_TYPE
2818 null, -- PROJECT_TP_EXCHANGE_RATE
2819 null, -- PROJECT_TRANSFER_PRICE
2820 l_prjfunc_curr_code_tab(b_tmp),
2821 l_fia_projfunc_cost_rate_date(b_tmp),
2822 l_fia_projfunc_cost_rate_type(b_tmp),
2823 l_fia_projfunc_cost_ex_rate(b_tmp),
2824 l_fia_projfunc_raw_cost(b_tmp),
2825 l_fia_projfunc_bd_cost(b_tmp),
2826 l_fia_projfunc_rev_rate_date (b_tmp),
2827 l_fia_projfunc_rev_rate_type(b_tmp),
2828 l_fia_projfunc_rev_ex_rate (b_tmp),
2829 l_fia_projfunc_revenue(b_tmp),
2830 null, -- PROJFUNC_TP_RATE_DATE
2831 null, -- PROJFUNC_TP_RATE_TYPE
2832 null, -- PROJFUNC_TP_EXCHANGE_RATE
2833 null, -- PROJFUNC_TRANSFER_PRICE
2834 l_fia_expfunc_curr_code(b_tmp),
2835 l_fia_expfunc_cost_rate_date(b_tmp),
2836 l_fia_expfunc_cost_rate_type(b_tmp),
2837 l_fia_expfunc_cost_ex_rate(b_tmp),
2838 l_fia_expfunc_raw_cost(b_tmp),
2839 l_fia_expfunc_bd_cost(b_tmp),
2840 null, -- EXPFUNC_TP_RATE_DATE
2841 null, -- EXPFUNC_TP_RATE_TYPE
2842 null, -- EXPFUNC_TP_EXCHANGE_RATE
2843 null, -- EXPFUNC_TRANSFER_PRICE
2844 lx_cc_prvdr_orgid_tab(b_tmp),
2845 lx_cc_prvdr_orgzid_tab(b_tmp),
2846 lx_cc_recvr_orgid_tab(b_tmp),
2847 lx_cc_recvr_orgzid_tab(b_tmp),
2848 l_fi_exp_organizationid_tab(b_tmp),
2849 l_fi_exptype_tab(b_tmp),
2850 l_fi_exptypeclass_tab(b_tmp),
2851 l_tp_exp_category(b_tmp),
2852 l_tp_labor_nl_flag(b_tmp),
2853 l_tp_taskid(b_tmp),
2854 l_tp_scheduleid(b_tmp),
2855 l_tp_rev_distributed_flag(b_tmp),
2856 l_tp_compute_flag(b_tmp),
2857 l_tp_fixed_date(b_tmp),
2858 l_fi_projid_tab(b_tmp),
2859 l_fi_personid_tab(b_tmp),
2860 l_asg_fcst_jobid_tab(b_tmp),
2861 l_tp_nl_resource(b_tmp),
2862 l_tp_nl_resource_orgzid(b_tmp),
2863 l_tp_pa_date(b_tmp),
2864 l_fi_amount_type_tab(b_tmp),
2865 l_tp_asgid(b_tmp),
2866 l_fi_process_flag_tab(b_tmp),
2867 l_fi_delete_flag_tab(b_tmp),
2868 lx_tp_error_code(b_tmp),
2869 l_fi_cst_rejct_reason_tab(b_tmp),
2870 l_fi_rev_rejct_reason_tab(b_tmp),
2871 l_fi_bd_rejct_reason_tab(b_tmp),
2872 l_fi_others_rejct_reason_tab(b_tmp),
2873 l_tp_denom_raw_cost(b_tmp),
2874 l_tp_denom_bd_cost(b_tmp),
2875 l_tp_raw_revenue(b_tmp) ,
2876 lx_tp_ind_compiled_setid(b_tmp),
2877 lx_tp_bill_rate(b_tmp) ,
2878 lx_tp_base_amount(b_tmp) ,
2879 lx_tp_bill_markup_percent(b_tmp) ,
2880 lx_tp_sch_line_percent(b_tmp) ,
2881 lx_tp_rule_percent(b_tmp) ,
2882 lx_tp_job_id(b_tmp) ,
2883 lx_cc_markup_basecode(b_tmp) );
2884
2885 l_fi_id_tab.delete;
2886 l_fi_date_tab.delete;
2887 l_fi_uom_tab.delete;
2888 l_fi_qty_tab.delete;
2889 l_fia_cost_txn_curr_code.delete;
2890 l_fia_rev_txn_curr_code.delete;
2891 l_fia_txn_raw_cost.delete;
2892 l_fia_txn_bd_cost.delete;
2893 l_fia_txn_revenue.delete;
2894 l_tp_denom_currcode.delete; -- tp txn curr code
2895 lx_denom_tp_currcode.delete; -- tp txn curr code
2896 lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
2897 l_prj_curr_code_tab.delete;
2898 l_fia_proj_cost_rate_date.delete;
2899 l_fia_proj_cost_rate_type.delete;
2900 l_fia_proj_cost_ex_rate.delete;
2901 l_fia_proj_raw_cost.delete;
2902 l_fia_proj_bd_cost.delete;
2903 l_fia_proj_rev_rate_date.delete;
2904 l_fia_proj_rev_rate_type.delete;
2905 l_fia_proj_rev_ex_rate.delete;
2906 l_fia_proj_revenue.delete;
2907 lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
2908 lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
2909 lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
2910 lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
2911 l_prjfunc_curr_code_tab.delete;
2912 l_fia_projfunc_cost_rate_date.delete;
2913 l_fia_projfunc_cost_rate_type.delete;
2914 l_fia_projfunc_cost_ex_rate.delete;
2915 l_fia_projfunc_raw_cost.delete;
2916 l_fia_projfunc_bd_cost.delete;
2917 l_fia_projfunc_rev_rate_date .delete;
2918 l_fia_projfunc_rev_rate_type.delete;
2919 l_fia_projfunc_rev_ex_rate .delete;
2920 l_fia_projfunc_revenue.delete;
2921 lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
2922 lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
2923 lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
2924 lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
2925 l_fia_expfunc_curr_code.delete;
2926 l_fia_expfunc_cost_rate_date.delete;
2927 l_fia_expfunc_cost_rate_type.delete;
2928 l_fia_expfunc_cost_ex_rate.delete;
2929 l_fia_expfunc_raw_cost.delete;
2930 l_fia_expfunc_bd_cost.delete;
2931 lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
2932 lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
2933 lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
2934 lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
2935 lx_cc_prvdr_orgid_tab.delete;
2936 lx_cc_prvdr_orgzid_tab.delete;
2937 lx_cc_recvr_orgid_tab.delete;
2938 lx_cc_recvr_orgzid_tab.delete;
2939 l_fi_exp_organizationid_tab.delete;
2940 l_fi_exptype_tab.delete;
2941 l_fi_exptypeclass_tab.delete;
2942 l_tp_exp_category.delete;
2943 l_tp_labor_nl_flag.delete;
2944 l_tp_taskid.delete;
2945 l_tp_scheduleid.delete;
2946 l_tp_rev_distributed_flag.delete;
2947 l_tp_compute_flag.delete;
2948 l_tp_fixed_date.delete;
2949 l_tp_asg_precedes_task_tab.delete; -- Added for bug 3260017
2950 l_fi_projid_tab.delete;
2951 l_fi_personid_tab.delete;
2952 l_asg_fcst_jobid_tab.delete;
2953 l_tp_nl_resource.delete;
2954 l_tp_nl_resource_orgzid.delete;
2955 l_tp_pa_date.delete;
2956 l_fi_amount_type_tab.delete;
2957 l_tp_asgid.delete;
2958 l_fi_process_flag_tab.delete;
2959 l_fi_delete_flag_tab.delete;
2960 lx_tp_error_code.delete;
2961
2962 lx_tp_ind_compiled_setid.delete;
2963 lx_tp_bill_rate.delete;
2964 lx_tp_base_amount.delete;
2965 lx_tp_bill_markup_percent.delete;
2966 lx_tp_sch_line_percent.delete;
2967 lx_tp_rule_percent.delete;
2968 lx_tp_job_id.delete;
2969 lx_cc_markup_basecode.delete;
2970
2971 l_tp_denom_raw_cost.delete;
2972 l_tp_denom_bd_cost.delete;
2973 l_tp_raw_revenue.delete;
2974
2975 l_fi_cst_rejct_reason_tab.delete;
2976 l_fi_rev_rejct_reason_tab.delete;
2977 l_fi_bd_rejct_reason_tab.delete;
2978 l_fi_others_rejct_reason_tab.delete;
2979
2980
2981 /* dbms_output.put_line('tp api call flag:'||l_call_tp_api_flag); */
2982
2983 SELECT
2984 FORECAST_ITEM_ID ,
2985 ITEM_DATE ,
2986 ITEM_UOM ,
2987 ITEM_QUANTITY ,
2988 COST_TXN_CURRENCY_CODE ,
2989 REVENUE_TXN_CURRENCY_CODE ,
2990 TXN_RAW_COST ,
2991 TXN_BURDENED_COST ,
2992 TXN_REVENUE ,
2993 TP_TXN_CURRENCY_CODE_IN ,
2994 TP_TXN_CURRENCY_CODE_OUT ,
2995 TXN_TRANSFER_PRICE ,
2996 PROJECT_CURRENCY_CODE ,
2997 PROJECT_COST_RATE_DATE ,
2998 PROJECT_COST_RATE_TYPE ,
2999 PROJECT_COST_EXCHANGE_RATE ,
3000 PROJECT_RAW_COST ,
3001 PROJECT_BURDENED_COST ,
3002 PROJECT_REVENUE_RATE_DATE ,
3003 PROJECT_REVENUE_RATE_TYPE ,
3004 PROJECT_REVENUE_EXCHANGE_RATE,
3005 PROJECT_REVENUE ,
3006 PROJECT_TP_RATE_DATE ,
3007 PROJECT_TP_RATE_TYPE ,
3008 PROJECT_TP_EXCHANGE_RATE ,
3009 PROJECT_TRANSFER_PRICE ,
3010 PROJFUNC_CURRENCY_CODE ,
3011 PROJFUNC_COST_RATE_DATE ,
3012 PROJFUNC_COST_RATE_TYPE ,
3013 PROJFUNC_COST_EXCHANGE_RATE ,
3014 PROJFUNC_RAW_COST ,
3015 PROJFUNC_BURDENED_COST ,
3016 PROJFUNC_REVENUE_RATE_DATE ,
3017 PROJFUNC_REVENUE_RATE_TYPE ,
3018 PROJFUNC_REVENUE_EXCHANGE_RATE,
3019 PROJFUNC_REVENUE ,
3020 PROJFUNC_TP_RATE_DATE ,
3021 PROJFUNC_TP_RATE_TYPE ,
3022 PROJFUNC_TP_EXCHANGE_RATE ,
3023 PROJFUNC_TRANSFER_PRICE ,
3024 EXPFUNC_CURRENCY_CODE ,
3025 EXPFUNC_COST_RATE_DATE ,
3026 EXPFUNC_COST_RATE_TYPE ,
3027 EXPFUNC_COST_EXCHANGE_RATE ,
3028 EXPFUNC_RAW_COST ,
3029 EXPFUNC_BURDENED_COST ,
3030 EXPFUNC_TP_RATE_DATE ,
3031 EXPFUNC_TP_RATE_TYPE ,
3032 EXPFUNC_TP_EXCHANGE_RATE ,
3033 EXPFUNC_TRANSFER_PRICE ,
3034 CC_PRVDR_ORG_ID ,
3035 CC_PRVDR_ORGANIZITION_ID ,
3036 CC_RECVR_ORG_ID ,
3037 CC_RECVR_ORGANIZITION_ID ,
3038 EXPENDITURE_ORGANIZATION_ID ,
3039 EXPENDITURE_TYPE ,
3040 EXPENDITURE_TYPE_CLASS ,
3041 EXPENDITURE_CATEGORY ,
3042 TP_LABOR_NL_FLAG ,
3043 TP_TASK_ID ,
3044 TP_SCHEDULE_ID ,
3045 TP_REV_DISTRIBUTED_FLAG ,
3046 TP_COMPUTE_FLAG ,
3047 TP_FIXED_DATE ,
3048 PROJECT_ID ,
3049 PERSON_ID ,
3050 FORECAST_JOB_ID ,
3051 TP_NL_RESOURCE ,
3052 TP_NL_RESOURCE_ORGZ_ID ,
3053 TP_PA_DATE ,
3054 TP_AMOUNT_TYPE ,
3055 assignment_id ,
3056 fi_process_flag ,
3057 delete_flag ,
3058 tp_error_code ,
3059 COST_REJECTION_CODE ,
3060 REV_REJECTION_CODE ,
3061 BURDEN_REJECTION_CODE ,
3062 OTHER_REJECTION_CODE ,
3063 TP_DENOM_RAW_COST ,
3064 TP_DENOM_BURDENED_COST ,
3065 TP_RAW_REVENUE ,
3066 tp_ind_compiled_setid ,
3067 tp_bill_rate ,
3068 tp_base_amount ,
3069 tp_bill_markup_percent ,
3070 tp_sch_line_percent ,
3071 tp_rule_percent ,
3072 tp_job_id ,
3073 cc_markup_basecode BULK COLLECT INTO
3074 l_fi_id_tab,
3075 l_fi_date_tab,
3076 l_fi_uom_tab,
3077 l_fi_qty_tab,
3078 l_fia_cost_txn_curr_code,
3079 l_fia_rev_txn_curr_code,
3080 l_fia_txn_raw_cost,
3081 l_fia_txn_bd_cost,
3082 l_fia_txn_revenue,
3083 l_tp_denom_currcode, -- tp txn curr code
3084 lx_denom_tp_currcode,
3085 lx_denom_tp_amt,
3086 l_prj_curr_code_tab,
3087 l_fia_proj_cost_rate_date,
3088 l_fia_proj_cost_rate_type,
3089 l_fia_proj_cost_ex_rate,
3090 l_fia_proj_raw_cost,
3091 l_fia_proj_bd_cost,
3092 l_fia_proj_rev_rate_date,
3093 l_fia_proj_rev_rate_type,
3094 l_fia_proj_rev_ex_rate,
3095 l_fia_proj_revenue,
3096 lx_proj_tp_rate_date,
3097 lx_proj_tp_rate_type,
3098 lx_proj_tp_exchange_rate,
3099 lx_proj_tp_amt,
3100 l_prjfunc_curr_code_tab,
3101 l_fia_projfunc_cost_rate_date,
3102 l_fia_projfunc_cost_rate_type,
3103 l_fia_projfunc_cost_ex_rate,
3104 l_fia_projfunc_raw_cost,
3105 l_fia_projfunc_bd_cost,
3106 l_fia_projfunc_rev_rate_date ,
3107 l_fia_projfunc_rev_rate_type,
3108 l_fia_projfunc_rev_ex_rate ,
3109 l_fia_projfunc_revenue,
3110 lx_projfunc_tp_rate_date,
3111 lx_projfunc_tp_rate_type,
3112 lx_projfunc_tp_exchange_rate,
3113 lx_projfunc_tp_amt,
3114 l_fia_expfunc_curr_code,
3115 l_fia_expfunc_cost_rate_date,
3116 l_fia_expfunc_cost_rate_type,
3117 l_fia_expfunc_cost_ex_rate,
3118 l_fia_expfunc_raw_cost,
3119 l_fia_expfunc_bd_cost,
3120 lx_expfunc_tp_rate_date,
3121 lx_expfunc_tp_rate_type,
3122 lx_expfunc_tp_exchange_rate,
3123 lx_expfunc_tp_amt,
3124 lx_cc_prvdr_orgid_tab,
3125 lx_cc_prvdr_orgzid_tab,
3126 lx_cc_recvr_orgid_tab,
3127 lx_cc_recvr_orgzid_tab,
3128 l_fi_exp_organizationid_tab,
3129 l_fi_exptype_tab,
3130 l_fi_exptypeclass_tab,
3131 l_tp_exp_category,
3132 l_tp_labor_nl_flag,
3133 l_tp_taskid,
3134 l_tp_scheduleid,
3135 l_tp_rev_distributed_flag,
3136 l_tp_compute_flag,
3137 l_tp_fixed_date,
3138 l_fi_projid_tab,
3139 l_fi_personid_tab,
3140 l_asg_fcst_jobid_tab,
3141 l_tp_nl_resource,
3142 l_tp_nl_resource_orgzid,
3143 l_tp_pa_date,
3144 l_fi_amount_type_tab,
3145 l_tp_asgid,
3146 l_fi_process_flag_tab,
3147 l_fi_delete_flag_tab,
3148 lx_tp_error_code,
3149 l_fi_cst_rejct_reason_tab,
3150 l_fi_rev_rejct_reason_tab,
3151 l_fi_bd_rejct_reason_tab,
3152 l_fi_others_rejct_reason_tab,
3153 l_tp_denom_raw_cost,
3154 l_tp_denom_bd_cost,
3155 l_tp_raw_revenue ,
3156 lx_tp_ind_compiled_setid,
3157 lx_tp_bill_rate,
3158 lx_tp_base_amount,
3159 lx_tp_bill_markup_percent,
3160 lx_tp_sch_line_percent,
3161 lx_tp_rule_percent,
3162 lx_tp_job_id,
3163 lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp WHERE
3164 Tp_Error_Code IS NULL;
3165
3166 l_tp_array_size := l_fi_date_tab.count;
3167
3168
3169 -- Added this for loop for bug 3260017
3170 IF (l_tp_array_size > 0 ) THEN
3171 l_tp_asg_precedes_task_tab.delete;
3172
3173 FOR i IN l_fi_date_tab.first..l_fi_date_tab.last LOOP
3174 l_tp_asg_precedes_task_tab(i) := l_asg_precedes_task;
3175 END LOOP;
3176 END IF;
3177
3178 /* FOR trf_temp IN l_fi_id_tab.first .. l_fi_id_tab.last loop
3179 dbms_output.put_line('before index : proc flag : tp err:'||trf_temp||
3180 ' '||l_fi_process_flag_tab(trf_temp) ||
3181 ' '||lx_tp_error_code(trf_temp));
3182 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab(trf_temp));
3183 dbms_output.put_line('after index : proc flag : tp err:'||trf_temp||
3184 ' '||l_fi_process_flag_tab(trf_temp) ||
3185 ' '||lx_tp_error_code(trf_temp));
3186 dbms_output.put_line('cc pvdr orgid :'||lx_cc_prvdr_orgid_tab(trf_temp));
3187 dbms_output.put_line('cc pvdr orgzd :'||lx_cc_prvdr_orgzid_tab(trf_temp));
3188 dbms_output.put_line('cc rcvr orgid :'||lx_cc_recvr_orgid_tab(trf_temp));
3189 dbms_output.put_line('cc rcvr orgzd :'||lx_cc_recvr_orgzid_tab(trf_temp));
3190 dbms_output.put_line('fi exp orgz d :'||l_fi_exp_organizationid_tab(trf_temp));
3191 dbms_output.put_line('exp item id :'||l_fi_id_tab(trf_temp));
3192 dbms_output.put_line('exp type tab :'||l_fi_exptype_tab(trf_temp));
3193 dbms_output.put_line('index : tp error :'||trf_temp
3194 || ' :'||lx_tp_error_code(trf_temp));
3195 dbms_output.put_line('exp category :'||l_tp_exp_category(trf_temp));
3196 dbms_output.put_line('fi date tab :'||l_fi_date_tab(trf_temp));
3197 dbms_output.put_line('tp labor fg :'||l_tp_labor_nl_flag(trf_temp));
3198 dbms_output.put_line('exp type cls :'||l_fi_exptypeclass_tab(trf_temp));
3199 dbms_output.put_line('task id :'||l_tp_taskid(trf_temp));
3200 dbms_output.put_line('tp sch id :'||l_tp_scheduleid(trf_temp));
3201 dbms_output.put_line('tp denom curr :'||l_tp_denom_currcode(trf_temp));
3202 dbms_output.put_line('l prj curr :'||l_prj_curr_code_tab(trf_temp));
3203 dbms_output.put_line('l pf curr :'||l_prjfunc_curr_code_tab(trf_temp));
3204 dbms_output.put_line('rev disti fg :'||l_tp_rev_distributed_flag(trf_temp));
3205 dbms_output.put_line('tp comp fg :'||l_tp_compute_flag(trf_temp));
3206 dbms_output.put_line('tp fixed dt :'||l_tp_fixed_date(trf_temp));
3207 dbms_output.put_line('tp raw cost :'||l_tp_denom_raw_cost(trf_temp));
3208 dbms_output.put_line('bd cost :'||l_tp_denom_bd_cost(trf_temp));
3209 dbms_output.put_line('raw revenue :'||l_tp_raw_revenue(trf_temp));
3210 dbms_output.put_line('asgn precedes task :'||l_tp_asg_precedes_task_tab(trf_temp)); -- Added for bug 3260017
3211 dbms_output.put_line('projid :'||l_fi_projid_tab(trf_temp));
3212 dbms_output.put_line('fi qty :'||l_fi_qty_tab(trf_temp));
3213 dbms_output.put_line('fi person id :'||l_fi_personid_tab(trf_temp));
3214 dbms_output.put_line('fcst job id :'||l_asg_fcst_jobid_tab(trf_temp));
3215 dbms_output.put_line('nl res :'||l_tp_nl_resource(trf_temp));
3216 dbms_output.put_line('nl orgz id :'||l_tp_nl_resource_orgzid(trf_temp));
3217 dbms_output.put_line('tp pa date :'||l_tp_pa_date(trf_temp));
3218 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab(trf_temp));
3219 dbms_output.put_line('fi asgid :'||l_tp_asgid(trf_temp));
3220 END LOOP;
3221 dbms_output.put_line('tp asgid :'||l_tp_asgid.count);
3222 dbms_output.put_line('tp array siz :'||l_tp_array_size);
3223 dbms_output.put_line('tp debug mode :'||l_tp_debug_mode);
3224 dbms_output.put_line('cc pvdr orgid :'||lx_cc_prvdr_orgid_tab.count);
3225 dbms_output.put_line('cc pvdr orgzd :'||lx_cc_prvdr_orgzid_tab.count);
3226 dbms_output.put_line('cc rcvr orgid :'||lx_cc_recvr_orgid_tab.count);
3227 dbms_output.put_line('cc rcvr orgzd :'||lx_cc_recvr_orgzid_tab.count);
3228 dbms_output.put_line('fi exp orgz d :'||l_fi_exp_organizationid_tab.count);
3229 dbms_output.put_line('exp item id :'||l_fi_id_tab.count);
3230 dbms_output.put_line('exp type tab :'||l_fi_exptype_tab.count);
3231 dbms_output.put_line('exp category :'||l_tp_exp_category.count);
3232 dbms_output.put_line('fi date tab :'||l_fi_date_tab.count);
3233 dbms_output.put_line('tp labor fg :'||l_tp_labor_nl_flag.count);
3234 dbms_output.put_line('exp type cls :'||l_fi_exptypeclass_tab.count);
3235 dbms_output.put_line('task id :'||l_tp_taskid.count);
3236 dbms_output.put_line('tp sch id :'||l_tp_scheduleid.count);
3237 dbms_output.put_line('tp denom curr :'||l_tp_denom_currcode.count);
3238 dbms_output.put_line('l prj curr :'||l_prj_curr_code_tab.count);
3239 dbms_output.put_line('l pf curr :'||l_prjfunc_curr_code_tab.count);
3240 dbms_output.put_line('rev disti fg :'||l_tp_rev_distributed_flag.count);
3241 dbms_output.put_line('tp comp fg :'||l_tp_compute_flag.count);
3242 dbms_output.put_line('tp fixed dt :'||l_tp_fixed_date.count);
3243 dbms_output.put_line('tp raw cost :'||l_tp_denom_raw_cost.count);
3244 dbms_output.put_line('bd cost :'||l_tp_denom_bd_cost.count);
3245 dbms_output.put_line('raw revenue :'||l_tp_raw_revenue.count);
3246 dbms_output.put_line('asgn precedes task :'||l_tp_asg_precedes_task_tab.count); -- Added for bug 3260017
3247 dbms_output.put_line('projid :'||l_fi_projid_tab.count);
3248 dbms_output.put_line('fi qty :'||l_fi_qty_tab.count);
3249 dbms_output.put_line('fi person id :'||l_fi_personid_tab.count);
3250 dbms_output.put_line('fcst job id :'||l_asg_fcst_jobid_tab.count);
3251 dbms_output.put_line('nl res :'||l_tp_nl_resource.count);
3252 dbms_output.put_line('nl orgz id :'||l_tp_nl_resource_orgzid.count);
3253 dbms_output.put_line('tp pa date :'||l_tp_pa_date.count);
3254 dbms_output.put_line('tp array siz :'||l_tp_array_size);
3255 dbms_output.put_line('tp debug mode :'||l_tp_debug_mode);
3256 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab.count);
3257 dbms_output.put_line('tp asgid :'||l_tp_asgid.count);
3258 dbms_output.put_line('prj tp rt ty :'||lx_proj_tp_rate_type.count);
3259 dbms_output.put_line('prj tp rt dt :'||lx_proj_tp_rate_date.count);
3260 dbms_output.put_line('prj tp ex :'||lx_proj_tp_exchange_rate.count);
3261 dbms_output.put_line('prj tp amt :'||lx_proj_tp_amt.count);
3262 dbms_output.put_line('pf tp rt ty :'||lx_projfunc_tp_rate_type.count);
3263 dbms_output.put_line('pf tp rt dt :'||lx_projfunc_tp_rate_date.count);
3264 dbms_output.put_line('pf tp ex :'||lx_projfunc_tp_exchange_rate.count);
3265 dbms_output.put_line('pf tp amt :'||lx_projfunc_tp_amt.count);
3266 dbms_output.put_line('denom tp curr:'||lx_denom_tp_currcode.count);
3267 dbms_output.put_line('denom tp amt :'||lx_denom_tp_amt.count);
3268 dbms_output.put_line('ef tp rt ty :'||lx_expfunc_tp_rate_type.count);
3269 dbms_output.put_line('ef tp rt dt :'||lx_expfunc_tp_rate_date.count);
3270 dbms_output.put_line('ef tp ex :'||lx_expfunc_tp_exchange_rate.count);
3271 dbms_output.put_line('ef tp amt :'||lx_expfunc_tp_amt.count);
3272 dbms_output.put_line('cc mark :'||lx_cc_markup_basecode.count);
3273 dbms_output.put_line('ind compiled :'||lx_tp_ind_compiled_setid.count);
3274 dbms_output.put_line('tp bill rate :'||lx_tp_bill_rate.count);
3275 dbms_output.put_line('tp base amt :'||lx_tp_base_amount.count);
3276 dbms_output.put_line('tp bill mark :'||lx_tp_bill_markup_percent.count);
3277 dbms_output.put_line('tp sch perc :'||lx_tp_sch_line_percent.count);
3278 dbms_output.put_line('tp rule perc :'||lx_tp_rule_percent.count);
3279 dbms_output.put_line('tp job id :'||lx_tp_job_id.count);
3280
3281 dbms_output.put_line('bef calling trf price'); */
3282
3283 IF P_PA_DEBUG_MODE = 'Y' THEN
3284 PA_DEBUG.g_err_stage := 'Bef calling Trf Price API';
3285 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3286 END IF;
3287
3288 Pa_Cc_Transfer_Price.Get_Transfer_Price(
3289 p_module_name => 'FORECAST',
3290 p_prvdr_operating_unit => lx_cc_prvdr_orgid_tab,
3291 p_prvdr_organization_id => lx_cc_prvdr_orgzid_tab,
3292 p_recvr_org_id => lx_cc_recvr_orgid_tab,
3293 p_recvr_organization_id => lx_cc_recvr_orgzid_tab,
3294 p_expnd_organization_id => l_fi_exp_organizationid_tab,
3295 p_expenditure_item_id => l_fi_id_tab,
3296 p_expenditure_type => l_fi_exptype_tab,
3297 p_expenditure_category => l_tp_exp_category,
3298 p_expenditure_item_date => l_fi_date_tab,
3299 p_labor_non_labor_flag => l_tp_labor_nl_flag,
3300 p_system_linkage_function => l_fi_exptypeclass_tab,
3301 p_task_id => l_tp_taskid,
3302 p_tp_schedule_id => l_tp_scheduleid,
3303 p_denom_currency_code => l_tp_denom_currcode,
3304 p_project_currency_code => l_prj_curr_code_tab,
3305 p_projfunc_currency_code => l_prjfunc_curr_code_tab,
3306 p_revenue_distributed_flag=> l_tp_rev_distributed_flag,
3307 p_processed_thru_date => sysdate,
3308 p_compute_flag => l_tp_compute_flag,
3309 p_tp_fixed_date => l_tp_fixed_date,
3310 p_denom_raw_cost_amount => l_tp_denom_raw_cost,
3311 p_denom_burdened_cost_amount => l_tp_denom_bd_cost,
3312 p_raw_revenue_amount => l_tp_raw_revenue,
3313 p_assignment_precedes_task => l_tp_asg_precedes_task_tab, -- Added for bug 3260017
3314 p_project_id => l_fi_projid_tab,
3315 p_quantity => l_fi_qty_tab,
3316 p_incurred_by_person_id => l_fi_personid_tab,
3317 p_job_id => l_asg_fcst_jobid_tab,
3318 p_non_labor_resource => l_tp_nl_resource,
3319 p_nl_resource_organization_id=> l_tp_nl_resource_orgzid,
3320 p_pa_date => l_tp_pa_date,
3321 p_array_size => l_tp_array_size,
3322 p_debug_mode => l_tp_debug_mode,
3323 p_tp_amt_type_code => l_fi_amount_type_tab,
3324 p_assignment_id => l_tp_asgid,
3325 x_proj_tp_rate_type => lx_proj_tp_rate_type,
3326 x_proj_tp_rate_date => lx_proj_tp_rate_date,
3327 x_proj_tp_exchange_rate => lx_proj_tp_exchange_rate,
3328 x_proj_transfer_price => lx_proj_tp_amt,
3329 x_projfunc_tp_rate_type => lx_projfunc_tp_rate_type,
3330 x_projfunc_tp_rate_date => lx_projfunc_tp_rate_date,
3331 x_projfunc_tp_exchange_rate => lx_projfunc_tp_exchange_rate,
3332 x_projfunc_transfer_price => lx_projfunc_tp_amt,
3333 x_denom_tp_currency_code => lx_denom_tp_currcode,
3334 x_denom_transfer_price => lx_denom_tp_amt,
3335 x_acct_tp_rate_type => lx_expfunc_tp_rate_type,
3336 x_acct_tp_rate_date => lx_expfunc_tp_rate_date,
3337 x_acct_tp_exchange_rate => lx_expfunc_tp_exchange_rate,
3338 x_acct_transfer_price => lx_expfunc_tp_amt,
3339 x_cc_markup_base_code => lx_cc_markup_basecode,
3340 x_tp_ind_compiled_set_id => lx_tp_ind_compiled_setid,
3341 x_tp_bill_rate => lx_tp_bill_rate,
3342 x_tp_base_amount => lx_tp_base_amount,
3343 x_tp_bill_markup_percentage => lx_tp_bill_markup_percent,
3344 x_tp_schedule_line_percentage=> lx_tp_sch_line_percent,
3345 x_tp_rule_percentage => lx_tp_rule_percent,
3346 x_tp_job_id => lx_tp_job_id,
3347 x_error_code => lx_tp_error_code,
3348 x_return_status => lx_tp_return_status );
3349
3350 IF P_PA_DEBUG_MODE = 'Y' THEN
3351 PA_DEBUG.g_err_stage := 'Aft calling Trf Price API';
3352 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3353 END IF;
3354 /* dbms_output.put_line('aft calling trf price'); */
3355
3356 FORALL l_trf_upd IN 1 .. l_fi_date_tab.COUNT
3357 UPDATE Pa_Fi_Amount_Dtls_Tmp SET
3358 TP_TXN_CURRENCY_CODE_out = lx_denom_tp_currcode(l_trf_upd),
3359 TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_trf_upd),
3360 PROJECT_TP_RATE_DATE = lx_proj_tp_rate_date(l_trf_upd),
3361 PROJECT_TP_RATE_TYPE = lx_proj_tp_rate_type(l_trf_upd),
3362 PROJECT_TP_EXCHANGE_RATE = lx_proj_tp_exchange_rate(l_trf_upd),
3363 PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_trf_upd),
3364 PROJFUNC_TP_RATE_DATE = lx_projfunc_tp_rate_date(l_trf_upd),
3365 PROJFUNC_TP_RATE_TYPE = lx_projfunc_tp_rate_type(l_trf_upd),
3366 PROJFUNC_TP_EXCHANGE_RATE = lx_projfunc_tp_exchange_rate(l_trf_upd),
3367 PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_trf_upd),
3368 EXPFUNC_TP_RATE_DATE = lx_expfunc_tp_rate_date(l_trf_upd),
3369 EXPFUNC_TP_RATE_TYPE = lx_expfunc_tp_rate_type(l_trf_upd),
3370 EXPFUNC_TP_EXCHANGE_RATE = lx_expfunc_tp_exchange_rate(l_trf_upd),
3371 EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_trf_upd),
3372 TP_ERROR_CODE = lx_tp_error_code(l_trf_upd)
3373 WHERE Forecast_Item_Id = l_fi_id_tab(l_trf_upd);
3374
3375 /* dbms_output.put_line('no of rows upd '||sql%rowcount); */
3376
3377 l_fi_id_tab.delete;
3378 l_fi_date_tab.delete;
3379 l_fi_uom_tab.delete;
3380 l_fi_qty_tab.delete;
3381 l_fia_cost_txn_curr_code.delete;
3382 l_fia_rev_txn_curr_code.delete;
3383 l_fia_txn_raw_cost.delete;
3384 l_fia_txn_bd_cost.delete;
3385 l_fia_txn_revenue.delete;
3386 l_tp_denom_currcode.delete; -- tp txn curr code
3387 lx_denom_tp_currcode.delete; -- tp txn curr code
3388 lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
3389 l_prj_curr_code_tab.delete;
3390 l_fia_proj_cost_rate_date.delete;
3391 l_fia_proj_cost_rate_type.delete;
3392 l_fia_proj_cost_ex_rate.delete;
3393 l_fia_proj_raw_cost.delete;
3394 l_fia_proj_bd_cost.delete;
3395 l_fia_proj_rev_rate_date.delete;
3396 l_fia_proj_rev_rate_type.delete;
3397 l_fia_proj_rev_ex_rate.delete;
3398 l_fia_proj_revenue.delete;
3399 lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
3400 lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
3401 lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
3402 lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
3403 l_prjfunc_curr_code_tab.delete;
3404 l_fia_projfunc_cost_rate_date.delete;
3405 l_fia_projfunc_cost_rate_type.delete;
3406 l_fia_projfunc_cost_ex_rate.delete;
3407 l_fia_projfunc_raw_cost.delete;
3408 l_fia_projfunc_bd_cost.delete;
3409 l_fia_projfunc_rev_rate_date .delete;
3410 l_fia_projfunc_rev_rate_type.delete;
3411 l_fia_projfunc_rev_ex_rate .delete;
3412 l_fia_projfunc_revenue.delete;
3413 lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
3414 lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
3415 lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
3416 lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
3417 l_fia_expfunc_curr_code.delete;
3418 l_fia_expfunc_cost_rate_date.delete;
3419 l_fia_expfunc_cost_rate_type.delete;
3420 l_fia_expfunc_cost_ex_rate.delete;
3421 l_fia_expfunc_raw_cost.delete;
3422 l_fia_expfunc_bd_cost.delete;
3423 lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
3424 lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
3425 lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
3426 lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
3427 lx_cc_prvdr_orgid_tab.delete;
3428 lx_cc_prvdr_orgzid_tab.delete;
3429 lx_cc_recvr_orgid_tab.delete;
3430 lx_cc_recvr_orgzid_tab.delete;
3431 l_fi_exp_organizationid_tab.delete;
3432 l_fi_exptype_tab.delete;
3433 l_fi_exptypeclass_tab.delete;
3434 l_tp_exp_category.delete;
3435 l_tp_labor_nl_flag.delete;
3436 l_tp_taskid.delete;
3437 l_tp_scheduleid.delete;
3438 l_tp_rev_distributed_flag.delete;
3439 l_tp_compute_flag.delete;
3440 l_tp_fixed_date.delete;
3441 l_fi_projid_tab.delete;
3442 l_fi_personid_tab.delete;
3443 l_asg_fcst_jobid_tab.delete;
3444 l_tp_nl_resource.delete;
3445 l_tp_nl_resource_orgzid.delete;
3446 l_tp_pa_date.delete;
3447 l_fi_amount_type_tab.delete;
3448 l_tp_asgid.delete;
3449 l_fi_process_flag_tab.delete;
3450 l_fi_delete_flag_tab.delete;
3451 lx_tp_error_code.delete;
3452
3453 lx_tp_ind_compiled_setid.delete;
3454 lx_tp_bill_rate.delete;
3455 lx_tp_base_amount.delete;
3456 lx_tp_bill_markup_percent.delete;
3457 lx_tp_sch_line_percent.delete;
3458 lx_tp_rule_percent.delete;
3459 lx_tp_job_id.delete;
3460 lx_cc_markup_basecode.delete;
3461
3462 l_tp_denom_raw_cost.delete;
3463 l_tp_denom_bd_cost.delete;
3464 l_tp_raw_revenue.delete;
3465
3466 l_fi_cst_rejct_reason_tab.delete;
3467 l_fi_rev_rejct_reason_tab.delete;
3468 l_fi_bd_rejct_reason_tab.delete;
3469 l_fi_others_rejct_reason_tab.delete;
3470
3471
3472
3473 SELECT
3474 FORECAST_ITEM_ID ,
3475 ITEM_DATE ,
3476 ITEM_UOM ,
3477 ITEM_QUANTITY ,
3478 COST_TXN_CURRENCY_CODE ,
3479 REVENUE_TXN_CURRENCY_CODE ,
3480 TXN_RAW_COST ,
3481 TXN_BURDENED_COST ,
3482 TXN_REVENUE ,
3483 TP_TXN_CURRENCY_CODE_IN ,
3484 TP_TXN_CURRENCY_CODE_OUT ,
3485 TXN_TRANSFER_PRICE ,
3486 PROJECT_CURRENCY_CODE ,
3487 PROJECT_COST_RATE_DATE ,
3488 PROJECT_COST_RATE_TYPE ,
3489 PROJECT_COST_EXCHANGE_RATE ,
3490 PROJECT_RAW_COST ,
3491 PROJECT_BURDENED_COST ,
3492 PROJECT_REVENUE_RATE_DATE ,
3493 PROJECT_REVENUE_RATE_TYPE ,
3494 PROJECT_REVENUE_EXCHANGE_RATE,
3495 PROJECT_REVENUE ,
3496 PROJECT_TP_RATE_DATE ,
3497 PROJECT_TP_RATE_TYPE ,
3498 PROJECT_TP_EXCHANGE_RATE ,
3499 PROJECT_TRANSFER_PRICE ,
3500 PROJFUNC_CURRENCY_CODE ,
3501 PROJFUNC_COST_RATE_DATE ,
3502 PROJFUNC_COST_RATE_TYPE ,
3503 PROJFUNC_COST_EXCHANGE_RATE ,
3504 PROJFUNC_RAW_COST ,
3505 PROJFUNC_BURDENED_COST ,
3506 PROJFUNC_REVENUE_RATE_DATE ,
3507 PROJFUNC_REVENUE_RATE_TYPE ,
3508 PROJFUNC_REVENUE_EXCHANGE_RATE,
3509 PROJFUNC_REVENUE ,
3510 PROJFUNC_TP_RATE_DATE ,
3511 PROJFUNC_TP_RATE_TYPE ,
3512 PROJFUNC_TP_EXCHANGE_RATE ,
3513 PROJFUNC_TRANSFER_PRICE ,
3514 EXPFUNC_CURRENCY_CODE ,
3515 EXPFUNC_COST_RATE_DATE ,
3516 EXPFUNC_COST_RATE_TYPE ,
3517 EXPFUNC_COST_EXCHANGE_RATE ,
3518 EXPFUNC_RAW_COST ,
3519 EXPFUNC_BURDENED_COST ,
3520 EXPFUNC_TP_RATE_DATE ,
3521 EXPFUNC_TP_RATE_TYPE ,
3522 EXPFUNC_TP_EXCHANGE_RATE ,
3523 EXPFUNC_TRANSFER_PRICE ,
3524 CC_PRVDR_ORG_ID ,
3525 CC_PRVDR_ORGANIZITION_ID ,
3526 CC_RECVR_ORG_ID ,
3527 CC_RECVR_ORGANIZITION_ID ,
3528 EXPENDITURE_ORGANIZATION_ID ,
3529 EXPENDITURE_TYPE ,
3530 EXPENDITURE_TYPE_CLASS ,
3531 EXPENDITURE_CATEGORY ,
3532 TP_LABOR_NL_FLAG ,
3533 TP_TASK_ID ,
3534 TP_SCHEDULE_ID ,
3535 TP_REV_DISTRIBUTED_FLAG ,
3536 TP_COMPUTE_FLAG ,
3537 TP_FIXED_DATE ,
3538 PROJECT_ID ,
3539 PERSON_ID ,
3540 FORECAST_JOB_ID ,
3541 TP_NL_RESOURCE ,
3542 TP_NL_RESOURCE_ORGZ_ID ,
3543 TP_PA_DATE ,
3544 TP_AMOUNT_TYPE ,
3545 assignment_id ,
3546 fi_process_flag ,
3547 delete_flag ,
3548 tp_error_code ,
3549 COST_REJECTION_CODE ,
3550 REV_REJECTION_CODE ,
3551 BURDEN_REJECTION_CODE ,
3552 OTHER_REJECTION_CODE ,
3553 TP_DENOM_RAW_COST ,
3554 TP_DENOM_BURDENED_COST ,
3555 TP_RAW_REVENUE ,
3556 tp_ind_compiled_setid ,
3557 tp_bill_rate ,
3558 tp_base_amount ,
3559 tp_bill_markup_percent ,
3560 tp_sch_line_percent ,
3561 tp_rule_percent ,
3562 tp_job_id ,
3563 cc_markup_basecode BULK COLLECT INTO
3564 l_fi_id_tab,
3565 l_fi_date_tab,
3566 l_fi_uom_tab,
3567 l_fi_qty_tab,
3568 l_fia_cost_txn_curr_code,
3569 l_fia_rev_txn_curr_code,
3570 l_fia_txn_raw_cost,
3571 l_fia_txn_bd_cost,
3572 l_fia_txn_revenue,
3573 l_tp_denom_currcode, -- tp txn curr code
3574 lx_denom_tp_currcode,
3575 lx_denom_tp_amt,
3576 l_prj_curr_code_tab,
3577 l_fia_proj_cost_rate_date,
3578 l_fia_proj_cost_rate_type,
3579 l_fia_proj_cost_ex_rate,
3580 l_fia_proj_raw_cost,
3581 l_fia_proj_bd_cost,
3582 l_fia_proj_rev_rate_date,
3583 l_fia_proj_rev_rate_type,
3584 l_fia_proj_rev_ex_rate,
3585 l_fia_proj_revenue,
3586 lx_proj_tp_rate_date,
3587 lx_proj_tp_rate_type,
3588 lx_proj_tp_exchange_rate,
3589 lx_proj_tp_amt,
3590 l_prjfunc_curr_code_tab,
3591 l_fia_projfunc_cost_rate_date,
3592 l_fia_projfunc_cost_rate_type,
3593 l_fia_projfunc_cost_ex_rate,
3594 l_fia_projfunc_raw_cost,
3595 l_fia_projfunc_bd_cost,
3596 l_fia_projfunc_rev_rate_date ,
3597 l_fia_projfunc_rev_rate_type,
3598 l_fia_projfunc_rev_ex_rate ,
3599 l_fia_projfunc_revenue,
3600 lx_projfunc_tp_rate_date,
3601 lx_projfunc_tp_rate_type,
3602 lx_projfunc_tp_exchange_rate,
3603 lx_projfunc_tp_amt,
3604 l_fia_expfunc_curr_code,
3605 l_fia_expfunc_cost_rate_date,
3606 l_fia_expfunc_cost_rate_type,
3607 l_fia_expfunc_cost_ex_rate,
3608 l_fia_expfunc_raw_cost,
3609 l_fia_expfunc_bd_cost,
3610 lx_expfunc_tp_rate_date,
3611 lx_expfunc_tp_rate_type,
3612 lx_expfunc_tp_exchange_rate,
3613 lx_expfunc_tp_amt,
3614 lx_cc_prvdr_orgid_tab,
3615 lx_cc_prvdr_orgzid_tab,
3616 lx_cc_recvr_orgid_tab,
3617 lx_cc_recvr_orgzid_tab,
3618 l_fi_exp_organizationid_tab,
3619 l_fi_exptype_tab,
3620 l_fi_exptypeclass_tab,
3621 l_tp_exp_category,
3622 l_tp_labor_nl_flag,
3623 l_tp_taskid,
3624 l_tp_scheduleid,
3625 l_tp_rev_distributed_flag,
3626 l_tp_compute_flag,
3627 l_tp_fixed_date,
3628 l_fi_projid_tab,
3629 l_fi_personid_tab,
3630 l_asg_fcst_jobid_tab,
3631 l_tp_nl_resource,
3632 l_tp_nl_resource_orgzid,
3633 l_tp_pa_date,
3634 l_fi_amount_type_tab,
3635 l_tp_asgid,
3636 l_fi_process_flag_tab,
3637 l_fi_delete_flag_tab,
3638 lx_tp_error_code,
3639 l_fi_cst_rejct_reason_tab,
3640 l_fi_rev_rejct_reason_tab,
3641 l_fi_bd_rejct_reason_tab,
3642 l_fi_others_rejct_reason_tab,
3643 l_tp_denom_raw_cost,
3644 l_tp_denom_bd_cost,
3645 l_tp_raw_revenue ,
3646 lx_tp_ind_compiled_setid,
3647 lx_tp_bill_rate,
3648 lx_tp_base_amount,
3649 lx_tp_bill_markup_percent,
3650 lx_tp_sch_line_percent,
3651 lx_tp_rule_percent,
3652 lx_tp_job_id,
3653 lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp;
3654
3655 FOR l_trf_err_idx IN 1 .. l_fi_id_tab.COUNT LOOP
3656 IF lx_tp_error_code(l_trf_err_idx) IS NOT NULL AND
3657 lx_tp_error_code(l_trf_err_idx) <> 'E' THEN
3658 l_fi_process_flag_tab(l_trf_err_idx) := 'N';
3659 END IF;
3660 END LOOP;
3661 /* dbms_output.put_line('after calling tp api :'||l_fi_id_tab.count); */
3662 END IF;
3663
3664 FOR d IN 1 .. l_fi_id_tab.count LOOP
3665
3666 l_t_line_num := 0;
3667
3668 IF l_fi_process_flag_tab(d) IN ( 'Y' , 'X' ) OR
3669 l_fi_delete_flag_tab(d) = 'Y' OR
3670 l_fi_qty_tab(d) <= 0 THEN
3671 BEGIN
3672 SELECT
3673 LINE_NUM ,
3674 ITEM_QUANTITY ,
3675 COST_TXN_CURRENCY_CODE ,
3676 REVENUE_TXN_CURRENCY_CODE ,
3677 TXN_RAW_COST ,
3678 TXN_BURDENED_COST ,
3679 TXN_REVENUE ,
3680 TXN_TRANSFER_PRICE ,
3681 TP_TXN_CURRENCY_CODE ,
3682 PROJECT_CURRENCY_CODE ,
3683 PROJECT_RAW_COST ,
3684 PROJECT_BURDENED_COST ,
3685 PROJECT_COST_RATE_DATE ,
3686 PROJECT_COST_RATE_TYPE ,
3687 PROJECT_COST_EXCHANGE_RATE ,
3688 PROJECT_REVENUE_RATE_DATE ,
3689 PROJECT_REVENUE_RATE_TYPE ,
3690 PROJECT_REVENUE_EXCHANGE_RATE,
3691 PROJECT_REVENUE ,
3692 PROJECT_TP_RATE_DATE ,
3693 PROJECT_TP_RATE_TYPE ,
3694 PROJECT_TP_EXCHANGE_RATE ,
3695 PROJECT_TRANSFER_PRICE ,
3696 PROJFUNC_CURRENCY_CODE ,
3697 PROJFUNC_COST_RATE_DATE ,
3698 PROJFUNC_COST_RATE_TYPE ,
3699 PROJFUNC_COST_EXCHANGE_RATE ,
3700 PROJFUNC_RAW_COST ,
3701 PROJFUNC_BURDENED_COST ,
3702 PROJFUNC_REVENUE_RATE_DATE ,
3703 PROJFUNC_REVENUE_RATE_TYPE ,
3704 PROJFUNC_REVENUE_EXCHANGE_RATE,
3705 PROJFUNC_REVENUE ,
3706 PROJFUNC_TP_RATE_DATE ,
3707 PROJFUNC_TP_RATE_TYPE ,
3708 PROJFUNC_TP_EXCHANGE_RATE ,
3709 PROJFUNC_TRANSFER_PRICE ,
3710 EXPFUNC_CURRENCY_CODE ,
3711 EXPFUNC_COST_RATE_DATE ,
3712 EXPFUNC_COST_RATE_TYPE ,
3713 EXPFUNC_COST_EXCHANGE_RATE ,
3714 EXPFUNC_RAW_COST ,
3715 EXPFUNC_BURDENED_COST ,
3716 EXPFUNC_TP_RATE_DATE ,
3717 EXPFUNC_TP_RATE_TYPE ,
3718 EXPFUNC_TP_EXCHANGE_RATE ,
3719 EXPFUNC_TRANSFER_PRICE
3720 INTO
3721 l_t_LINE_NUM ,
3722 l_t_ITEM_QUANTITY ,
3723 l_t_COST_TXN_CURR_CODE ,
3724 l_t_REV_TXN_CURR_CODE ,
3725 l_t_TXN_RAW_COST ,
3726 l_t_TXN_BD_COST ,
3727 l_t_TXN_REVENUE ,
3728 l_t_TXN_TRANSFER_PRICE ,
3729 l_t_TP_TXN_CURR_CODE ,
3730 l_t_PROJ_CURR_CODE ,
3731 l_t_PROJ_RAW_COST ,
3732 l_t_PROJ_BD_COST ,
3733 l_t_PROJ_COST_RATE_DATE ,
3734 l_t_PROJ_COST_RATE_TYPE ,
3735 l_t_PROJ_COST_EX_RATE ,
3736 l_t_PROJ_REV_RATE_DATE ,
3737 l_t_PROJ_REV_RATE_TYPE ,
3738 l_t_PROJ_REV_EX_RATE ,
3739 l_t_PROJ_REVENUE ,
3740 l_t_PROJ_TP_RATE_DATE ,
3741 l_t_PROJ_TP_RATE_TYPE ,
3742 l_t_PROJ_TP_EX_RATE ,
3743 l_t_PROJ_TRANSFER_PRICE ,
3744 l_t_PFUNC_CURR_CODE ,
3745 l_t_PFUNC_COST_RATE_DATE ,
3746 l_t_PFUNC_COST_RATE_TYPE ,
3747 l_t_PFUNC_COST_EX_RATE ,
3748 l_t_PFUNC_RAW_COST ,
3749 l_t_PFUNC_BD_COST ,
3750 l_t_PFUNC_REV_RATE_DATE ,
3751 l_t_PFUNC_REV_RATE_TYPE ,
3752 l_t_PFUNC_REV_EX_RATE ,
3753 l_t_PFUNC_REVENUE ,
3754 l_t_PFUNC_TP_RATE_DATE ,
3755 l_t_PFUNC_TP_RATE_TYPE ,
3756 l_t_PFUNC_TP_EX_RATE ,
3757 l_t_PFUNC_TRANSFER_PRICE ,
3758 l_t_EFUNC_CURR_CODE ,
3759 l_t_EFUNC_COST_RATE_DATE ,
3760 l_t_EFUNC_COST_RATE_TYPE ,
3761 l_t_EFUNC_COST_EX_RATE ,
3762 l_t_EFUNC_RAW_COST ,
3763 l_t_EFUNC_BD_COST ,
3764 l_t_EFUNC_TP_RATE_DATE ,
3765 l_t_EFUNC_TP_RATE_TYPE ,
3766 l_t_EFUNC_TP_EX_RATE ,
3767 l_t_EFUNC_TRANSFER_PRICE
3768 FROM Pa_Fi_Amount_Details WHERE
3769 Forecast_Item_Id = l_fi_id_tab(d) AND
3770 Line_Num = ( SELECT MAX(LINE_NUM) FROM
3771 Pa_Fi_Amount_Details WHERE
3772 Forecast_Item_Id = l_fi_id_tab(d) );
3773
3774
3775 /* setting the variables for updating the FI amount records */
3776 IF l_t_ITEM_QUANTITY > 0 THEN
3777 l_fid_upd_fcst_itemid(l_fia_upd_index) := l_fi_id_tab(d);
3778 l_fid_upd_line_num(l_fia_upd_index) := l_t_line_num;
3779 l_fid_upd_reversed_flag(l_fia_upd_index) := 'Y';
3780 l_fid_upd_net_zero_flag(l_fia_upd_index) := 'Y';
3781 l_fia_upd_index := l_fia_upd_index + 1;
3782
3783 /* Setting the variables for the reversal record */
3784
3785 l_fid_cost_txn_curr_code(l_fia_index) := l_t_COST_TXN_CURR_CODE;
3786 l_fid_rev_txn_curr_code(l_fia_index) := l_t_REV_TXN_CURR_CODE;
3787 l_fid_txn_raw_cost(l_fia_index) := -l_t_TXN_RAW_COST;
3788 l_fid_txn_bd_cost(l_fia_index) := -l_t_TXN_BD_COST;
3789 l_fid_txn_revenue(l_fia_index) := -l_t_TXN_REVENUE;
3790
3791 l_fid_expfunc_curr_code(l_fia_index) := l_t_EFUNC_CURR_CODE;
3792 l_fid_expfunc_raw_cost(l_fia_index) := -l_t_EFUNC_RAW_COST;
3793 l_fid_expfunc_bd_cost(l_fia_index) := -l_t_EFUNC_BD_COST;
3794
3795 l_fid_projfunc_curr_code(l_fia_index) := l_t_pfunc_curr_code;
3796 l_fid_projfunc_raw_cost(l_fia_index) := -l_t_PFUNC_RAW_COST;
3797 l_fid_projfunc_bd_cost(l_fia_index) := -l_t_PFUNC_BD_COST;
3798 l_fid_projfunc_revenue(l_fia_index) := -l_t_PFUNC_REVENUE;
3799
3800 l_fid_proj_curr_code(l_fia_index) := l_t_proj_curr_code;
3801 l_fid_proj_raw_cost(l_fia_index) := -l_t_PROJ_RAW_COST;
3802 l_fid_proj_bd_cost(l_fia_index) := -l_t_PROJ_BD_COST;
3803 l_fid_proj_revenue(l_fia_index) := -l_t_PROJ_REVENUE;
3804
3805 l_fid_proj_cost_rate_type(l_fia_index) := l_t_PROJ_COST_RATE_TYPE;
3806 l_fid_proj_cost_rate_date(l_fia_index) := l_t_PROJ_COST_RATE_DATE;
3807 l_fid_proj_cost_ex_rate(l_fia_index) := l_t_PROJ_COST_EX_RATE;
3808
3809 l_fid_proj_rev_rate_type(l_fia_index) := l_t_PROJ_REV_RATE_TYPE;
3810 l_fid_proj_rev_rate_date(l_fia_index) := l_t_PROJ_REV_RATE_DATE;
3811 l_fid_proj_rev_ex_rate(l_fia_index) := l_t_PROJ_REV_EX_RATE;
3812
3813 l_fid_expfunc_cost_rate_type(l_fia_index) := l_t_EFUNC_COST_RATE_TYPE;
3814 l_fid_expfunc_cost_rate_date(l_fia_index) := l_t_EFUNC_COST_RATE_DATE;
3815 l_fid_expfunc_cost_ex_rate(l_fia_index) := l_t_EFUNC_COST_EX_RATE;
3816
3817 l_fid_projfunc_cost_rate_type(l_fia_index) := l_t_PFUNC_COST_RATE_TYPE;
3818 l_fid_projfunc_cost_rate_date(l_fia_index) := l_t_PFUNC_COST_RATE_DATE;
3819 l_fid_projfunc_cost_ex_rate(l_fia_index) := l_t_PFUNC_COST_EX_RATE;
3820
3821 l_fid_projfunc_rev_rate_type(l_fia_index) := l_t_PFUNC_REV_RATE_TYPE;
3822 l_fid_projfunc_rev_rate_date(l_fia_index) := l_t_PFUNC_REV_RATE_DATE;
3823 l_fid_projfunc_rev_ex_rate(l_fia_index) := l_t_PFUNC_REV_EX_RATE;
3824
3825 l_fid_proj_tp_rate_type(l_fia_index) := l_t_PROJ_TP_RATE_TYPE;
3826 l_fid_proj_tp_rate_date(l_fia_index) := l_t_PROJ_TP_RATE_DATE;
3827 l_fid_proj_tp_ex_rate(l_fia_index) := l_t_PROJ_TP_EX_RATE;
3828 l_fid_proj_tp_amt(l_fia_index) := -l_t_PROJ_TRANSFER_PRICE;
3829
3830 l_fid_projfunc_tp_rate_type(l_fia_index) := l_t_PFUNC_TP_RATE_TYPE;
3831 l_fid_projfunc_tp_rate_date(l_fia_index) := l_t_PFUNC_TP_RATE_DATE;
3832 l_fid_projfunc_tp_ex_rate(l_fia_index) := l_t_PFUNC_TP_EX_RATE;
3833 l_fid_projfunc_tp_amt(l_fia_index) := -l_t_PFUNC_TRANSFER_PRICE;
3834
3835 l_fid_denom_tp_currcode(l_fia_index) := l_t_TP_TXN_CURR_CODE;
3836 l_fid_denom_tp_amt(l_fia_index) := -l_t_TXN_TRANSFER_PRICE;
3837
3838 l_fid_expfunc_tp_rate_type(l_fia_index) := l_t_EFUNC_TP_RATE_TYPE;
3839 l_fid_expfunc_tp_rate_date(l_fia_index) := l_t_EFUNC_TP_RATE_DATE;
3840 l_fid_expfunc_tp_ex_rate(l_fia_index) := l_t_EFUNC_TP_EX_RATE;
3841 l_fid_expfunc_tp_amt(l_fia_index) := -l_t_EFUNC_TRANSFER_PRICE;
3842
3843 l_fid_fcst_itemid(l_fia_index) := l_fi_id_tab(d);
3844 l_fid_line_num(l_fia_index) := l_t_line_num + 1;
3845 l_fid_item_date(l_fia_index) := l_fi_date_tab(d);
3846 l_fid_item_uom(l_fia_index) := l_fi_uom_tab(d);
3847 l_fid_item_qty(l_fia_index) := -l_t_ITEM_QUANTITY;
3848 l_fid_reversed_flag(l_fia_index) := 'N';
3849 l_fid_net_zero_flag(l_fia_index) := 'Y';
3850 l_fid_line_num_reversed(l_fia_index) := l_t_line_num;
3851
3852 l_t_line_num := l_t_line_num + 1;
3853 l_fia_index := l_fia_index + 1;
3854 END IF;
3855 /* end if for l_t_ITEM_QUANTITY gt zero */
3856 EXCEPTION
3857 WHEN NO_DATA_FOUND THEN
3858 NULL;
3859 WHEN OTHERS THEN
3860 IF P_PA_DEBUG_MODE = 'Y' THEN
3861 PA_DEBUG.g_err_stage := 'Inside FI Amt Dtls others Excep';
3862 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3863 END IF;
3864 RAISE;
3865 END;
3866
3867 /* dbms_output.put_line('assigning values for the new line');
3868 dbms_output.put_line('before txn curr code and txn amts'); */
3869 IF l_fi_process_flag_tab(d) = 'Y' THEN
3870 l_fid_cost_txn_curr_code(l_fia_index) := l_fia_cost_txn_curr_code(d);
3871 l_fid_rev_txn_curr_code(l_fia_index) := l_fia_rev_txn_curr_code(d);
3872 l_fid_txn_raw_cost(l_fia_index) := l_fia_txn_raw_cost(d);
3873 l_fid_txn_bd_cost(l_fia_index) := l_fia_txn_bd_cost(d);
3874 l_fid_txn_revenue(l_fia_index) := l_fia_txn_revenue(d);
3875
3876 /* dbms_output.put_line('before exp func code and amts'); */
3877
3878 l_fid_expfunc_curr_code(l_fia_index) := l_fia_expfunc_curr_code(d);
3879 l_fid_expfunc_raw_cost(l_fia_index) := l_fia_expfunc_raw_cost(d);
3880 l_fid_expfunc_bd_cost(l_fia_index) := l_fia_expfunc_bd_cost(d);
3881
3882 l_fid_expfunc_cost_rate_type(l_fia_index) := l_fia_expfunc_cost_rate_type(d);
3883 l_fid_expfunc_cost_rate_date(l_fia_index) := l_fia_expfunc_cost_rate_date(d);
3884 l_fid_expfunc_cost_ex_rate(l_fia_index) := l_fia_expfunc_cost_ex_rate(d);
3885
3886 l_fid_expfunc_tp_rate_type(l_fia_index) := lx_expfunc_tp_rate_type(d);
3887 l_fid_expfunc_tp_rate_date(l_fia_index) := lx_expfunc_tp_rate_date(d);
3888 l_fid_expfunc_tp_ex_rate(l_fia_index) := lx_expfunc_tp_exchange_rate(d);
3889 l_fid_expfunc_tp_amt(l_fia_index) := lx_expfunc_tp_amt(d);
3890
3891 /* dbms_output.put_line('before proj func code and amts'); */
3892
3893 l_fid_projfunc_curr_code(l_fia_index) := l_prjfunc_curr_code_tab(d);
3894 l_fid_projfunc_raw_cost(l_fia_index) := l_fia_projfunc_raw_cost(d);
3895 l_fid_projfunc_bd_cost(l_fia_index) := l_fia_projfunc_bd_cost(d);
3896 l_fid_projfunc_revenue(l_fia_index) := l_fia_projfunc_revenue(d);
3897
3898 l_fid_projfunc_cost_rate_type(l_fia_index) := l_fia_projfunc_cost_rate_type(d);
3899 l_fid_projfunc_cost_rate_date(l_fia_index) := l_fia_projfunc_cost_rate_date(d);
3900 l_fid_projfunc_cost_ex_rate(l_fia_index) := l_fia_projfunc_cost_ex_rate(d);
3901
3902 l_fid_projfunc_rev_rate_type(l_fia_index) := l_fia_projfunc_rev_rate_type(d);
3903 l_fid_projfunc_rev_rate_date(l_fia_index) := l_fia_projfunc_rev_rate_date(d);
3904 l_fid_projfunc_rev_ex_rate(l_fia_index) := l_fia_projfunc_rev_ex_rate(d);
3905
3906 l_fid_projfunc_tp_rate_type(l_fia_index) := lx_projfunc_tp_rate_type(d);
3907 l_fid_projfunc_tp_rate_date(l_fia_index) := lx_projfunc_tp_rate_date(d);
3908 l_fid_projfunc_tp_ex_rate(l_fia_index) := lx_projfunc_tp_exchange_rate(d);
3909 l_fid_projfunc_tp_amt(l_fia_index) := lx_projfunc_tp_amt(d);
3910
3911 /* dbms_output.put_line('pf tp rt type :'||lx_projfunc_tp_rate_type(d));
3912 dbms_output.put_line('pf tp rt date :'||lx_projfunc_tp_rate_date(d));
3913 dbms_output.put_line('pf tp ex rate :'||lx_projfunc_tp_exchange_rate(d));
3914 dbms_output.put_line('pf tp amount :'||lx_projfunc_tp_amt(d));
3915 dbms_output.put_line('before proj code and amts'); */
3916
3917 l_fid_proj_curr_code(l_fia_index) := l_prj_curr_code_tab(d);
3918 l_fid_proj_raw_cost(l_fia_index) := l_fia_proj_raw_cost(d);
3919 l_fid_proj_bd_cost(l_fia_index) := l_fia_proj_bd_cost(d);
3920 l_fid_proj_revenue(l_fia_index) := l_fia_proj_revenue(d);
3921
3922 /* dbms_output.put_line('before assigning proj rev');
3923 dbms_output.put_line('fia proj rev :'||l_fia_proj_revenue(d));
3924 dbms_output.put_line('fid proj rev :'||l_fid_proj_revenue(l_fia_index)); */
3925
3926 l_fid_proj_cost_rate_type(l_fia_index) := l_fia_proj_cost_rate_type(d);
3927 l_fid_proj_cost_rate_date(l_fia_index) := l_fia_proj_cost_rate_date(d);
3928 l_fid_proj_cost_ex_rate(l_fia_index) := l_fia_proj_cost_ex_rate(d);
3929
3930 l_fid_proj_rev_rate_type(l_fia_index) := l_fia_proj_rev_rate_type(d);
3931 l_fid_proj_rev_rate_date(l_fia_index) := l_fia_proj_rev_rate_date(d);
3932 l_fid_proj_rev_ex_rate(l_fia_index) := l_fia_proj_rev_ex_rate(d);
3933
3934 l_fid_proj_tp_rate_type(l_fia_index) := lx_proj_tp_rate_type(d);
3935 l_fid_proj_tp_rate_date(l_fia_index) := lx_proj_tp_rate_date(d);
3936 l_fid_proj_tp_ex_rate(l_fia_index) := lx_proj_tp_exchange_rate(d);
3937 l_fid_proj_tp_amt(l_fia_index) := lx_proj_tp_amt(d);
3938
3939 /* dbms_output.put_line('before denom and amts'); */
3940
3941 l_fid_denom_tp_currcode(l_fia_index) := lx_denom_tp_currcode(d);
3942 l_fid_denom_tp_amt(l_fia_index) := lx_denom_tp_amt(d);
3943
3944
3945 l_fid_fcst_itemid(l_fia_index) := l_fi_id_tab(d);
3946 l_fid_line_num(l_fia_index) := l_t_line_num + 1;
3947 l_fid_item_date(l_fia_index) := l_fi_date_tab(d);
3948 l_fid_item_uom(l_fia_index) := l_fi_uom_tab(d);
3949 l_fid_item_qty(l_fia_index) := l_fi_qty_tab(d);
3950 l_fid_reversed_flag(l_fia_index) := 'N';
3951 l_fid_net_zero_flag(l_fia_index) := 'N';
3952 l_fid_line_num_reversed(l_fia_index) := NULL;
3953
3954 l_fia_index := l_fia_index + 1;
3955 END IF;
3956 END IF;
3957 /* forecast_process_flag = Y */
3958 END LOOP;
3959
3960 /* dbms_output.put_line('fi amt tab count :'||l_fid_fcst_itemid.count);
3961 dbms_output.put_line('bef inserting FI amount dtls :'); */
3962
3963 IF P_PA_DEBUG_MODE = 'Y' THEN
3964 PA_DEBUG.g_err_stage := 'Bef inserting FI Amt Dtls';
3965 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3966 END IF;
3967
3968 FORALL b IN 1 .. l_fid_fcst_itemid.COUNT
3969 INSERT INTO Pa_Fi_Amount_Details(
3970 FORECAST_ITEM_ID,
3971 LINE_NUM,
3972 ITEM_DATE,
3973 ITEM_UOM,
3974 ITEM_QUANTITY,
3975 NET_ZERO_FLAG,
3976 REVERSED_FLAG,
3977 LINE_NUM_REVERSED,
3978 CREATION_DATE,
3979 CREATED_BY,
3980 LAST_UPDATE_DATE,
3981 LAST_UPDATED_BY,
3982 LAST_UPDATE_LOGIN,
3983 REQUEST_ID,
3984 PROGRAM_APPLICATION_ID,
3985 PROGRAM_ID,
3986 PROGRAM_UPDATE_DATE,
3987 COST_TXN_CURRENCY_CODE,
3988 REVENUE_TXN_CURRENCY_CODE,
3989 TXN_RAW_COST,
3990 TXN_BURDENED_COST,
3991 TXN_REVENUE,
3992 TXN_TRANSFER_PRICE,
3993 TP_TXN_CURRENCY_CODE,
3994 PROJECT_CURRENCY_CODE,
3995 PROJECT_RAW_COST,
3996 PROJECT_BURDENED_COST,
3997 PROJECT_COST_RATE_DATE,
3998 PROJECT_COST_RATE_TYPE,
3999 PROJECT_COST_EXCHANGE_RATE,
4000 PROJECT_REVENUE_RATE_DATE,
4001 PROJECT_REVENUE_RATE_TYPE,
4002 PROJECT_REVENUE_EXCHANGE_RATE,
4003 PROJECT_REVENUE,
4004 PROJECT_TP_RATE_DATE,
4005 PROJECT_TP_RATE_TYPE,
4006 PROJECT_TP_EXCHANGE_RATE,
4007 PROJECT_TRANSFER_PRICE,
4008 PROJFUNC_CURRENCY_CODE,
4009 PROJFUNC_COST_RATE_DATE,
4010 PROJFUNC_COST_RATE_TYPE,
4011 PROJFUNC_COST_EXCHANGE_RATE,
4012 PROJFUNC_RAW_COST,
4013 PROJFUNC_BURDENED_COST,
4014 PROJFUNC_REVENUE_RATE_DATE,
4015 PROJFUNC_REVENUE_RATE_TYPE,
4016 PROJFUNC_REVENUE_EXCHANGE_RATE,
4017 PROJFUNC_REVENUE,
4018 PROJFUNC_TP_RATE_DATE,
4019 PROJFUNC_TP_RATE_TYPE,
4020 PROJFUNC_TP_EXCHANGE_RATE,
4021 PROJFUNC_TRANSFER_PRICE,
4022 EXPFUNC_CURRENCY_CODE,
4023 EXPFUNC_COST_RATE_DATE,
4024 EXPFUNC_COST_RATE_TYPE,
4025 EXPFUNC_COST_EXCHANGE_RATE,
4026 EXPFUNC_RAW_COST,
4027 EXPFUNC_BURDENED_COST,
4028 EXPFUNC_TP_RATE_DATE,
4029 EXPFUNC_TP_RATE_TYPE,
4030 EXPFUNC_TP_EXCHANGE_RATE,
4031 EXPFUNC_TRANSFER_PRICE )
4032 VALUES(
4033 l_fid_fcst_itemid(b),
4034 l_fid_line_num(b),
4035 l_fid_item_date(b),
4036 l_fid_item_uom(b),
4037 l_fid_item_qty(b),
4038 l_fid_net_zero_flag(b),
4039 l_fid_reversed_flag(b),
4040 l_fid_line_num_reversed(b),
4041 l_creation_date,
4042 l_created_by,
4043 l_last_update_date,
4044 l_last_updated_by,
4045 l_last_update_login,
4046 l_request_id,
4047 l_program_application_id,
4048 l_program_id,
4049 l_last_update_date,
4050 l_fid_cost_txn_curr_code(b),
4051 l_fid_rev_txn_curr_code(b),
4052 l_fid_txn_raw_cost(b),
4053 l_fid_txn_bd_cost(b),
4054 l_fid_txn_revenue(b),
4055 l_fid_denom_tp_amt(b),
4056 l_fid_denom_tp_currcode(b),
4057 l_fid_proj_curr_code(b),
4058 l_fid_proj_raw_cost(b),
4059 l_fid_proj_bd_cost(b),
4060 l_fid_proj_cost_rate_date(b),
4061 l_fid_proj_cost_rate_type(b),
4062 l_fid_proj_cost_ex_rate(b),
4063 l_fid_proj_rev_rate_date(b),
4064 l_fid_proj_rev_rate_type(b),
4065 l_fid_proj_rev_ex_rate(b),
4066 l_fid_proj_revenue(b),
4067 l_fid_proj_tp_rate_date(b),
4068 l_fid_proj_tp_rate_type(b),
4069 l_fid_proj_tp_ex_rate(b),
4070 l_fid_proj_tp_amt(b),
4071 l_fid_projfunc_curr_code(b),
4072 l_fid_projfunc_cost_rate_date(b),
4073 l_fid_projfunc_cost_rate_type(b),
4074 l_fid_projfunc_cost_ex_rate(b),
4075 l_fid_projfunc_raw_cost(b),
4076 l_fid_projfunc_bd_cost(b),
4077 l_fid_projfunc_rev_rate_date(b),
4078 l_fid_projfunc_rev_rate_type(b),
4079 l_fid_projfunc_rev_ex_rate(b),
4080 l_fid_projfunc_revenue(b),
4081 l_fid_projfunc_tp_rate_date(b),
4082 l_fid_projfunc_tp_rate_type(b),
4083 l_fid_projfunc_tp_ex_rate(b),
4084 l_fid_projfunc_tp_amt(b),
4085 l_fid_expfunc_curr_code(b),
4086 l_fid_expfunc_cost_rate_date(b),
4087 l_fid_expfunc_cost_rate_type(b),
4088 l_fid_expfunc_cost_ex_rate(b),
4089 l_fid_expfunc_raw_cost(b),
4090 l_fid_expfunc_bd_cost(b),
4091 l_fid_expfunc_tp_rate_date(b),
4092 l_fid_expfunc_tp_rate_type(b),
4093 l_fid_expfunc_tp_ex_rate(b),
4094 l_fid_expfunc_tp_amt(b) );
4095
4096 IF P_PA_DEBUG_MODE = 'Y' THEN
4097 PA_DEBUG.g_err_stage := 'Bef updating FI Amt Dtls';
4098 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4099 END IF;
4100 /* dbms_output.put_line('bef updating FI amount dtls :'); */
4101
4102 FORALL l_upd_idx IN 1 .. l_fid_upd_fcst_itemid.COUNT
4103 UPDATE Pa_FI_Amount_Details SET
4104 Reversed_flag = 'Y',
4105 Net_Zero_Flag = 'Y',
4106 LAST_UPDATE_DATE = l_last_update_date,
4107 LAST_UPDATED_BY = l_last_updated_by,
4108 LAST_UPDATE_LOGIN = l_last_update_login,
4109 REQUEST_ID = l_request_id,
4110 PROGRAM_APPLICATION_ID = l_program_application_id,
4111 PROGRAM_ID = l_program_id,
4112 PROGRAM_UPDATE_DATE = l_last_update_date
4113 WHERE
4114 Forecast_Item_Id = l_fid_upd_fcst_itemid(l_upd_idx) AND
4115 Line_Num = l_fid_upd_line_num(l_upd_idx);
4116
4117 IF P_PA_DEBUG_MODE = 'Y' THEN
4118 PA_DEBUG.g_err_stage := 'Bef updating Fcst Items';
4119 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4120 END IF;
4121 /* dbms_output.put_line('bef updating FIs :'); */
4122
4123 FOR l_tupd IN 1 .. l_fi_id_tab.COUNT LOOP
4124 IF l_fi_delete_flag_tab(l_tupd) = 'Y' OR l_fi_qty_tab(l_tupd) <= 0 THEN
4125 l_fi_process_flag_tab(l_tupd) := 'X';
4126 END IF;
4127 IF NVL(l_fi_others_rejct_reason_tab(l_tupd),'abcx') = 'E' THEN
4128 l_fi_others_rejct_reason_tab(l_tupd) := NULL;
4129 END IF;
4130 IF NVL(lx_tp_error_code(l_tupd),'abcx') = 'E' THEN
4131 lx_tp_error_code(l_tupd) := NULL;
4132 END IF;
4133 IF l_fi_process_flag_tab(l_tupd) <> 'Y' OR
4134 l_fi_delete_flag_tab(l_tupd) = 'Y' OR
4135 l_fi_qty_tab(l_tupd) <= 0 THEN
4136 l_fia_cost_txn_curr_code(l_tupd) := NULL;
4137 l_fia_rev_txn_curr_code(l_tupd) := NULL;
4138 l_fia_txn_raw_cost(l_tupd) := NULL;
4139 l_fia_txn_bd_cost(l_tupd) := NULL;
4140 l_fia_txn_revenue(l_tupd) := NULL;
4141 lx_denom_tp_currcode(l_tupd) := NULL;
4142 lx_denom_tp_amt(l_tupd) := NULL;
4143 l_fia_expfunc_curr_code(l_tupd) := NULL;
4144 l_fia_expfunc_raw_cost(l_tupd) := NULL;
4145 l_fia_expfunc_bd_cost(l_tupd) := NULL;
4146 lx_expfunc_tp_amt(l_tupd) := NULL;
4147 l_prjfunc_curr_code_tab(l_tupd) := NULL;
4148 l_fia_projfunc_raw_cost(l_tupd) := NULL;
4149 l_fia_projfunc_bd_cost(l_tupd) := NULL;
4150 l_fia_projfunc_revenue(l_tupd) := NULL;
4151 lx_projfunc_tp_amt(l_tupd) := NULL;
4152 l_prj_curr_code_tab(l_tupd) := NULL;
4153 l_fia_proj_raw_cost(l_tupd) := NULL;
4154 l_fia_proj_bd_cost(l_tupd) := NULL;
4155 l_fia_proj_revenue(l_tupd) := NULL;
4156 lx_proj_tp_amt(l_tupd) := NULL;
4157 END IF;
4158 END LOOP;
4159
4160 /* dbms_output.put_line(' pro flag '||l_fi_process_flag_tab.count);
4161 dbms_output.put_line(' amt type '||l_fi_amount_type_tab.count);
4162 dbms_output.put_line(' cst txn curr '||l_fia_cost_txn_curr_code.count);
4163 dbms_output.put_line(' rev txn curr '||l_fia_rev_txn_curr_code.count);
4164 dbms_output.put_line(' txn raw cost '||l_fia_txn_raw_cost.count);
4165 dbms_output.put_line(' txn bd cost '||l_fia_txn_bd_cost.count);
4166 dbms_output.put_line(' txn rev '||l_fia_txn_revenue.count);
4167 dbms_output.put_line(' denom tp curr '||lx_denom_tp_currcode.count);
4168 dbms_output.put_line(' denom tp amt '||lx_denom_tp_amt.count);
4169 dbms_output.put_line(' ef curr '||l_fia_expfunc_curr_code.count);
4170 dbms_output.put_line(' ef raw cst '||l_fia_expfunc_raw_cost.count);
4171 dbms_output.put_line(' ef bd cst '||l_fia_expfunc_bd_cost.count);
4172 dbms_output.put_line(' ef tp amt '||lx_expfunc_tp_amt.count);
4173 dbms_output.put_line(' pf curr '||l_prjfunc_curr_code_tab.count);
4174 dbms_output.put_line(' pf raw cst '||l_fia_projfunc_raw_cost.count);
4175 dbms_output.put_line(' pf bd cst '||l_fia_projfunc_bd_cost.count);
4176 dbms_output.put_line(' pf rev '||l_fia_projfunc_revenue.count);
4177 dbms_output.put_line(' pf tp amt '||lx_projfunc_tp_amt.count);
4178 dbms_output.put_line(' prj curr '||l_prj_curr_code_tab.count);
4179 dbms_output.put_line(' prj raw cst '||l_fia_proj_raw_cost.count);
4180 dbms_output.put_line(' prj bd cst '||l_fia_proj_bd_cost.count);
4181 dbms_output.put_line(' prj rev '||l_fia_proj_revenue.count);
4182 dbms_output.put_line(' prj tp amt '||lx_proj_tp_amt.count);
4183 dbms_output.put_line(' cst rej '||l_fi_cst_rejct_reason_tab.count);
4184 dbms_output.put_line(' rev rej '||l_fi_rev_rejct_reason_tab.count);
4185 dbms_output.put_line(' tp rej '||lx_tp_error_code.count);
4186 dbms_output.put_line(' bd rej '||l_fi_bd_rejct_reason_tab.count);
4187 dbms_output.put_line(' other rej '||l_fi_others_rejct_reason_tab.count);
4188 dbms_output.put_line(' fi id '||l_fi_id_tab.count); */
4189
4190
4191 FORALL l_fi_upd_index IN 1 .. l_fi_id_tab.COUNT
4192 UPDATE Pa_Forecast_Items SET
4193 FORECAST_AMT_CALC_FLAG = l_fi_process_flag_tab(l_fi_upd_index),
4194 TP_AMOUNT_TYPE = l_fi_amount_type_tab(l_fi_upd_index),
4195 COST_TXN_CURRENCY_CODE = l_fia_cost_txn_curr_code(l_fi_upd_index),
4196 REVENUE_TXN_CURRENCY_CODE = l_fia_rev_txn_curr_code(l_fi_upd_index),
4197 TXN_RAW_COST = l_fia_txn_raw_cost(l_fi_upd_index),
4198 TXN_BURDENED_COST = l_fia_txn_bd_cost(l_fi_upd_index),
4199 TXN_REVENUE = l_fia_txn_revenue(l_fi_upd_index),
4200 TP_TXN_CURRENCY_CODE = lx_denom_tp_currcode(l_fi_upd_index),
4201 TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_fi_upd_index),
4202 EXPFUNC_CURRENCY_CODE = l_fia_expfunc_curr_code(l_fi_upd_index),
4203 EXPFUNC_RAW_COST = l_fia_expfunc_raw_cost(l_fi_upd_index),
4204 EXPFUNC_BURDENED_COST = l_fia_expfunc_bd_cost(l_fi_upd_index),
4205 EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_fi_upd_index),
4206 PROJFUNC_CURRENCY_CODE = l_prjfunc_curr_code_tab(l_fi_upd_index),
4207 PROJFUNC_RAW_COST = l_fia_projfunc_raw_cost(l_fi_upd_index),
4208 PROJFUNC_BURDENED_COST = l_fia_projfunc_bd_cost(l_fi_upd_index),
4209 PROJFUNC_REVENUE = l_fia_projfunc_revenue(l_fi_upd_index),
4210 PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_fi_upd_index),
4211 PROJECT_CURRENCY_CODE = l_prj_curr_code_tab(l_fi_upd_index),
4212 PROJECT_RAW_COST = l_fia_proj_raw_cost(l_fi_upd_index),
4213 PROJECT_BURDENED_COST = l_fia_proj_bd_cost(l_fi_upd_index),
4214 PROJECT_REVENUE = l_fia_proj_revenue(l_fi_upd_index),
4215 PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_fi_upd_index),
4216 COST_REJECTION_CODE = l_fi_cst_rejct_reason_tab(l_fi_upd_index),
4217 REV_REJECTION_CODE = l_fi_rev_rejct_reason_tab(l_fi_upd_index),
4218 TP_REJECTION_CODE = lx_tp_error_code(l_fi_upd_index),
4219 BURDEN_REJECTION_CODE = l_fi_bd_rejct_reason_tab(l_fi_upd_index),
4220 OTHER_REJECTION_CODE = l_fi_others_rejct_reason_tab(l_fi_upd_index),
4221 LAST_UPDATE_DATE = l_last_update_date,
4222 LAST_UPDATED_BY = l_last_updated_by,
4223 LAST_UPDATE_LOGIN = l_last_update_login,
4224 REQUEST_ID = l_request_id,
4225 PROGRAM_APPLICATION_ID = l_program_application_id,
4226 PROGRAM_ID = l_program_id,
4227 PROGRAM_UPDATE_DATE = l_last_update_date
4228 WHERE Forecast_Item_Id = l_fi_id_tab(l_fi_upd_index);
4229
4230 /* dbms_output.put_line('records updated in FI :'||sql%rowcount ); */
4231
4232
4233 /* for i in 1.. l_fi_id_tab.count loop
4234 dbms_output.put_line(i ||' pid ' ||l_fi_projid_tab(i) || ' aid '||l_fi_asgid_tab(i)
4235 ||' ity ' ||l_fi_item_type_tab(I)
4236 ||' etyp ' ||l_fi_exptype_Tab(i)
4237 ||' sys ' ||l_fi_exptypeclass_tab(i)
4238 ||' cty ' ||lx_cc_type_tab(i)
4239 ||' cc ' ||lx_cc_code_tab(i)
4240 ||' csta ' ||lx_cc_status_tab(i) );
4241 dbms_output.put_line('pfunc curr:'||l_prjfunc_curr_code_tab(i));
4242 dbms_output.put_line('p curr:'||l_prj_curr_code_tab(i));
4243 end loop; */
4244
4245 COMMIT;
4246 END LOOP;
4247 /* main loop for bulk fetch from FIs */
4248
4249 /* Bug 3051110 - Code added for populating two columns in pa_project_assignments table
4250 TP Enhancement */
4251
4252 IF p_debug_mode = 'Y' THEN
4253 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Start of populating TP Rate columns', 3);
4254 END IF;
4255
4256 /* The cursor Cur_Assignments will get all the assignment_ids processed in this particular request */
4257
4258 Open Cur_Assignments(l_request_id);
4259 LOOP
4260 FETCH Cur_Assignments INTO l_assignment_id;
4261 EXIT WHEN Cur_Assignments%NOTFOUND;
4262
4263 IF p_debug_mode = 'Y' THEN
4264 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Currently Populating for assignment_id :'||l_assignment_id, 3);
4265 END IF;
4266
4267 /*
4268 Getting the sum of projfunc_transfer_price and item_quantity from pa_forecast_items table for the assignment_id,
4269 if any of the sum is NULL we populate the transfer_price_rate as NULL and transfer_pr_rate_curr as NULL and if
4270 any of the sum is 0, we populate the transfer_price_rate as 0 and transfer_pr_rate_curr as the project
4271 functional currency. If both the sums have non zero value and they are not null, we calculate the average
4272 transfer_price_rate and call the api to populate the transfer_price_rate as average Transfer_price_Rate and
4273 transfer_pr_rate_curr as the project functional currency
4274 */
4275
4276 BEGIN
4277 Select sum(PROJFUNC_TRANSFER_PRICE), Sum(ITEM_QUANTITY)
4278 INTO l_sum_transfer_price, l_sum_item_quantity
4279 From PA_FORECAST_ITEMS
4280 Where assignment_id = l_assignment_ID and delete_flag = 'N'
4281 and error_flag = 'N' And forecast_amt_Calc_flag ='Y';
4282 EXCEPTION WHEN NO_DATA_FOUND THEN
4283 l_sum_transfer_price := Null;
4284 l_sum_item_quantity := Null;
4285 END;
4286
4287 IF p_debug_mode = 'Y' THEN
4288 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'TP rate for assignment:'||l_sum_transfer_price, 3);
4289 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Item Quantity for assignment:'||l_sum_item_quantity, 3);
4290 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Proj Func Curency:'||l_prjfunc_curr_code, 3);
4291 END IF;
4292
4293 IF l_sum_transfer_price is NULL OR l_sum_item_quantity is NULL OR l_sum_item_quantity = 0 THEN
4294 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
4295 (
4296 p_assignment_id => l_assignment_id
4297 ,p_debug_mode => p_debug_mode
4298 ,p_transfer_price_rate => NULL
4299 ,p_transfer_pr_rate_curr=> NULL
4300 ,x_return_status => l_return_status
4301 );
4302 ELSE
4303 l_average_transfer_price_rate := l_sum_transfer_price / l_sum_item_quantity;
4304 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
4305 (
4306 p_assignment_id => l_assignment_id
4307 ,p_debug_mode => p_debug_mode
4308 ,p_transfer_price_rate => l_average_transfer_price_rate
4309 ,p_transfer_pr_rate_curr=> l_prjfunc_curr_code
4310 ,x_return_status => l_return_status
4311 );
4312 END IF;
4313
4314 END LOOP;
4315 CLOSE Cur_Assignments;
4316
4317 IF p_run_mode = 'F' THEN
4318 IF P_PA_DEBUG_MODE = 'Y' THEN
4319 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_All and returning';
4320 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4321 END IF;
4322 CLOSE fcst_item_All;
4323 ELSIF p_run_mode = 'I' THEN
4324 IF P_PA_DEBUG_MODE = 'Y' THEN
4325 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Inc and returning';
4326 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4327 END IF;
4328 CLOSE fcst_item_Inc;
4329 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
4330 p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
4331 IF P_PA_DEBUG_MODE = 'Y' THEN
4332 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj and returning';
4333 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4334 END IF;
4335 CLOSE fcst_item_Prj;
4336 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
4337 p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
4338 IF P_PA_DEBUG_MODE = 'Y' THEN
4339 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj_Asg and returning';
4340 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4341 END IF;
4342 CLOSE fcst_item_Prj_Asg;
4343 ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
4344 IF P_PA_DEBUG_MODE = 'Y' THEN
4345 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Organization and returning';
4346 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4347 END IF;
4348 CLOSE fcst_item_Organization;
4349 END IF;
4350 /* Bug fix:4329035 */
4351 IF P_PA_DEBUG_MODE = 'Y' THEN
4352 PA_DEBUG.Reset_Curr_Function;
4353 END IF;
4354 RETURN;
4355 EXCEPTION
4356 WHEN OTHERS THEN
4357 retcode := '2';
4358 IF P_PA_DEBUG_MODE = 'Y' THEN
4359 PA_DEBUG.g_err_stage := 'Inside Main Others Excep';
4360 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4361 PA_DEBUG.Log_Message(p_message => SQLERRM); -- Bug 7423839
4362 PA_DEBUG.Reset_Curr_Function;
4363 END IF;
4364 RAISE;
4365 END;
4366 END Pa_Fi_Amt_Calc_Pkg;