[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.4.12000000.3 2007/03/22 06:19:37 kvuyyuru 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(100);
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 l_prj_type := NULL;
1478 l_distribution_rule := NULL;
1479 l_bill_job_group_id := NULL;
1480 l_cost_job_group_id := NULL;
1481 l_job_bill_rate_sch_id := NULL;
1482 l_emp_bill_rate_sch_id := NULL;
1483 l_prj_curr_code := NULL;
1484 l_prj_rate_date := NULL;
1485 l_prj_rate_type := NULL;
1486 l_prj_bil_rate_date_code := NULL;
1487 l_prj_bil_rate_type := NULL;
1488 l_prj_bil_rate_date := NULL;
1489 l_prj_bil_ex_rate := NULL;
1490 l_prjfunc_curr_code := NULL;
1491 l_prjfunc_cost_rate_type := NULL;
1492 l_prjfunc_cost_rate_date := NULL;
1493 l_prjfunc_bil_rate_date_code := NULL;
1494 l_prjfunc_bil_rate_type := NULL;
1495 l_prjfunc_bil_rate_date := NULL;
1496 l_prjfunc_bil_ex_rate := NULL;
1497 l_labor_tp_schedule_id := NULL;
1498 l_labor_tp_fixed_date := NULL;
1499 l_labor_sch_discount := NULL;
1500 l_asg_precedes_task := NULL;
1501 l_labor_bill_rate_orgid := NULL;
1502 l_labor_std_bill_rate_sch := NULL;
1503 l_labor_sch_fixed_dt := NULL;
1504 l_labor_sch_type := NULL;
1505 l_prev_project_id := l_fi_projid_tab(i);
1506 BEGIN
1507 SELECT Project_Type,
1508 DISTRIBUTION_RULE,
1509 BILL_JOB_GROUP_ID,
1510 COST_JOB_GROUP_ID,
1511 JOB_BILL_RATE_SCHEDULE_ID,
1512 EMP_BILL_RATE_SCHEDULE_ID,
1513 PROJECT_CURRENCY_CODE,
1514 PROJECT_RATE_DATE,
1515 PROJECT_RATE_TYPE,
1516 PROJECT_BIL_RATE_DATE_CODE,
1517 PROJECT_BIL_RATE_TYPE,
1518 PROJECT_BIL_RATE_DATE,
1519 PROJECT_BIL_EXCHANGE_RATE,
1520 PROJFUNC_CURRENCY_CODE,
1521 PROJFUNC_COST_RATE_TYPE,
1522 PROJFUNC_COST_RATE_DATE,
1523 PROJFUNC_BIL_RATE_DATE_CODE,
1524 PROJFUNC_BIL_RATE_TYPE,
1525 PROJFUNC_BIL_RATE_DATE,
1526 PROJFUNC_BIL_EXCHANGE_RATE,
1527 LABOR_TP_SCHEDULE_ID,
1528 LABOR_TP_FIXED_DATE,
1529 LABOR_SCHEDULE_DISCOUNT,
1530 ASSIGN_PRECEDES_TASK,
1531 NVL(LABOR_BILL_RATE_ORG_ID,-99),
1532 LABOR_STD_BILL_RATE_SCHDL,
1533 LABOR_SCHEDULE_FIXED_DATE,
1534 LABOR_SCH_TYPE
1535 INTO
1536 l_prj_type,
1537 l_distribution_rule,
1538 l_bill_job_group_id,
1539 l_cost_job_group_id,
1540 l_job_bill_rate_sch_id,
1541 l_emp_bill_rate_sch_id,
1542 l_prj_curr_code,
1543 l_prj_rate_date,
1544 l_prj_rate_type,
1545 l_prj_bil_rate_date_code,
1546 l_prj_bil_rate_type,
1547 l_prj_bil_rate_date,
1548 l_prj_bil_ex_rate,
1549 l_prjfunc_curr_code,
1550 l_prjfunc_cost_rate_type,
1551 l_prjfunc_cost_rate_date,
1552 l_prjfunc_bil_rate_date_code,
1553 l_prjfunc_bil_rate_type,
1554 l_prjfunc_bil_rate_date,
1555 l_prjfunc_bil_ex_rate,
1556 l_labor_tp_schedule_id,
1557 l_labor_tp_fixed_date,
1558 l_labor_sch_discount,
1559 l_asg_precedes_task,
1560 l_labor_bill_rate_orgid,
1561 l_labor_std_bill_rate_sch,
1562 l_labor_sch_fixed_dt,
1563 l_labor_sch_type
1564 FROM Pa_Projects_All P
1565 WHERE P.Project_Id = l_prev_project_id;
1566 EXCEPTION
1567 WHEN NO_DATA_FOUND THEN
1568 l_fi_others_rejct_reason_tab(i) := 'PA_INVALID_PROJECT_ID';
1569 l_fi_process_flag_tab(i) := 'N';
1570 IF P_PA_DEBUG_MODE = 'Y' THEN
1571 PA_DEBUG.g_err_stage := 'Invalid Project Id :'||TO_CHAR(l_prev_project_id);
1572 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1573 END IF;
1574 END;
1575 END IF;
1576
1577 /* dbms_output.put_line('after prj'); */
1578
1579 l_fcst_cost_rate_schid := NULL;
1580
1581 IF l_prev_proj_orgid <> l_fi_proj_orgid_tab(i) AND
1582 l_fi_process_flag_tab(i) = 'Y' THEN
1583 l_prev_proj_orgid := l_fi_proj_orgid_tab(i);
1584 BEGIN
1585 SELECT JOB_COST_RATE_SCHEDULE_ID INTO
1586 l_fcst_cost_rate_schid
1587 FROM PA_FORECASTING_OPTIONS_ALL
1588 WHERE NVL(ORG_ID,-99) = l_prev_proj_orgid AND
1589 JOB_COST_RATE_SCHEDULE_ID IS NOT NULL;
1590 EXCEPTION
1591 WHEN NO_DATA_FOUND THEN
1592 IF P_PA_DEBUG_MODE = 'Y' THEN
1593 PA_DEBUG.g_err_stage := 'No Fcst Job Cost Rt Sch for Proj Orgid:' ||
1594 TO_CHAR(l_prev_proj_orgid);
1595 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1596 END IF;
1597 l_fi_others_rejct_reason_tab(i) := 'PA_FCST_NO_JOB_SCH_ID';
1598 l_fi_process_flag_tab(i) := 'N';
1599 END;
1600 END IF;
1601 /* dbms_output.put_line('after options'); */
1602
1603 l_prj_type_tab(i) := l_prj_type;
1604 l_distribution_rule_tab(i) := l_distribution_rule;
1605 l_bill_job_group_id_tab(i) := l_bill_job_group_id;
1606 l_cost_job_group_id_tab(i) := l_cost_job_group_id;
1607 l_job_bill_rate_sch_id_tab(i) := l_job_bill_rate_sch_id;
1608 l_emp_bill_rate_sch_id_tab(i) := l_emp_bill_rate_sch_id;
1609 l_prj_curr_code_tab(i) := l_prj_curr_code;
1610 l_prj_rate_date_tab(i) := l_prj_rate_date;
1611 l_prj_rate_type_tab(i) := l_prj_rate_type;
1612 l_prj_bil_rate_dt_code_tab(i) := l_prj_bil_rate_date_code;
1613 l_prj_bil_rate_type_tab(i) := l_prj_bil_rate_type;
1614 l_prj_bil_rate_date_tab(i) := l_prj_bil_rate_date;
1615 l_prj_bil_ex_rate_tab(i) := l_prj_bil_ex_rate;
1616 l_prjfunc_curr_code_tab(i) := l_prjfunc_curr_code;
1617 l_prjfunc_cost_rt_type_tab(i) := l_prjfunc_cost_rate_type;
1618 l_prjfunc_cost_rt_dt_tab(i) := l_prjfunc_cost_rate_date;
1619 l_prjfunc_bil_rt_dt_code_tab(i) := l_prjfunc_bil_rate_date_code;
1620 l_prjfunc_bil_rate_type_tab(i) := l_prjfunc_bil_rate_type;
1621 l_prjfunc_bil_rate_date_tab(i) := l_prjfunc_bil_rate_date;
1622 l_prjfunc_bil_ex_rate_tab(i) := l_prjfunc_bil_ex_rate;
1623 l_tp_scheduleid(i) := l_labor_tp_schedule_id;
1624 l_tp_fixed_date(i) := l_labor_tp_fixed_date;
1625 l_tp_denom_currcode(i) := NULL;
1626 l_tp_denom_raw_cost(i) := NULL;
1627 l_tp_denom_bd_cost(i) := NULL;
1628 l_tp_raw_revenue(i) := NULL;
1629 l_prj_cost_rate_schid_tab(i) := l_fcst_cost_rate_schid;
1630
1631 l_labor_sch_discount_tab(i) := l_labor_sch_discount;
1632 l_asg_precedes_task_tab(i) := l_asg_precedes_task;
1633 l_labor_bill_rate_orgid_tab(i) := l_labor_bill_rate_orgid;
1634 l_labor_std_bill_rate_sch_tab(i) := l_labor_std_bill_rate_sch;
1635 l_labor_sch_fixed_dt_tab(i) := l_labor_sch_fixed_dt;
1636 l_labor_sch_type_tab(i) := l_labor_sch_type;
1637
1638
1639 l_asg_markup_percent := NULL;
1640 l_asg_bill_rate_override := NULL;
1641 l_asg_bill_rate_curr_override := NULL;
1642 l_asg_markup_percent_override := NULL;
1643 l_asg_tp_rate_override := NULL;
1644 l_asg_tp_curr_override := NULL;
1645 l_asg_tp_calc_base_code_ovr := NULL;
1646 l_asg_tp_percent_applied_ovr := NULL;
1647
1648 l_asg_fcst_jobid_tab(i) := NULL;
1649 l_asg_fcst_jobgroupid_tab(i) := NULL;
1650
1651 lx_cc_status_tab(i) := NULL;
1652 lx_tp_error_code(i) := NULL;
1653
1654 IF ( l_prev_asg_id <> l_fi_asgid_tab(i) AND
1655 l_fi_asgid_tab(i) <> -9999 AND
1656 l_fi_process_flag_tab(i) = 'Y' ) THEN
1657 /* dbms_output.put_line('asg id changing prev id :'||l_prev_asg_id );
1658 dbms_output.put_line('asg id current prev id :'||l_fi_asgid_tab(i) ); */
1659 l_prev_asg_id := l_fi_asgid_tab(i);
1660 /* dbms_output.put_line('inside asg ');
1661 dbms_output.put_line('asg id '||l_prev_asg_id); */
1662 l_process_fis_flag := 'Y';
1663 BEGIN
1664 l_asg_fcst_job_id := NULL;
1665 l_asg_fcst_job_group_id := NULL;
1666 l_asg_project_role_id := NULL;
1667 l_prj_assignment_type := NULL;
1668 l_prj_status_code := NULL;
1669
1670 SELECT Fcst_Job_Id,
1671 Fcst_Job_Group_Id,
1672 Project_Role_Id,
1673 MARKUP_PERCENT,
1674 BILL_RATE_OVERRIDE,
1675 BILL_RATE_CURR_OVERRIDE,
1676 MARKUP_PERCENT_OVERRIDE,
1677 TP_RATE_OVERRIDE,
1678 TP_CURRENCY_OVERRIDE,
1679 TP_CALC_BASE_CODE_OVERRIDE,
1680 TP_PERCENT_APPLIED_OVERRIDE,
1681 ASSIGNMENT_TYPE,
1682 STATUS_CODE
1683 INTO
1684 l_asg_fcst_job_id,
1685 l_asg_fcst_job_group_id,
1686 l_asg_project_role_id,
1687 l_asg_markup_percent,
1688 l_asg_bill_rate_override,
1689 l_asg_bill_rate_curr_override,
1690 l_asg_markup_percent_override,
1691 l_asg_tp_rate_override,
1692 l_asg_tp_curr_override,
1693 l_asg_tp_calc_base_code_ovr,
1694 l_asg_tp_percent_applied_ovr,
1695 l_prj_assignment_type,
1696 l_prj_status_code
1697 FROM PA_PROJECT_ASSIGNMENTS P
1698 WHERE P.Assignment_Id = l_prev_asg_id;
1699 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1700 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1701 EXCEPTION
1702 WHEN NO_DATA_FOUND THEN
1703 IF P_PA_DEBUG_MODE = 'Y' THEN
1704 PA_DEBUG.g_err_stage := 'Invalid Assignment Id:' ||
1705 TO_CHAR(l_prev_asg_id);
1706 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1707 END IF;
1708 l_fi_others_rejct_reason_tab(i) := 'PA_FP_INVALID_ASG_ID';
1709 l_fi_process_flag_tab(i) := 'N';
1710 RAISE;
1711 END;
1712 /* dbms_output.put_line('after asg'); */
1713
1714 /* The Forecast Items needs to be checked in the Assignment Level whether
1715 it needs to be processed or not. The flag l_process_fis_flag is used to set
1716 the l_fi_process_flag_tab PL SQL table value */
1717
1718 IF l_fi_process_flag_tab(i) = 'Y' AND l_prj_status_code IS NOT NULL THEN
1719 IF l_prj_assignment_type = 'OPEN_ASSIGNMENT' THEN
1720 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Asgmt_In_Open_Status(
1721 l_prj_status_code,
1722 'OPEN_ASGMT');
1723 IF l_asgmt_status_flag = 'N' THEN
1724 l_process_fis_flag := 'N';
1725 END IF;
1726 ELSIF ( l_prj_assignment_type = 'STAFFED_ASSIGNMENT' OR
1727 l_prj_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' ) THEN
1728 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(
1729 l_prj_status_code,
1730 'STAFFED_ASGMT');
1731 IF l_asgmt_status_flag = 'Y' THEN
1732 l_process_fis_flag := 'N';
1733 END IF;
1734 END IF;
1735 END IF;
1736 /* dbms_output.put_line('asg id : fis process flag'||l_fi_asgid_tab(i) ||
1737 ' : ' ||l_process_fis_flag); */
1738 IF l_fi_item_type_tab(i) = 'R' AND
1739 ( l_asg_fcst_job_id IS NULL OR
1740 l_asg_fcst_job_group_id IS NULL ) AND
1741 l_process_fis_flag = 'Y' THEN
1742 BEGIN
1743 SELECT PR.DEFAULT_JOB_ID,
1744 PJ.JOB_GROUP_ID
1745 INTO
1746 l_asg_fcst_job_id,
1747 l_asg_fcst_job_group_id
1748 FROM PA_PROJECT_ROLE_TYPES PR,
1749 PER_JOBS PJ
1750 WHERE
1751 PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
1752 PJ.JOB_ID = PR.DEFAULT_JOB_ID;
1753 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1754 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1755 EXCEPTION
1756 WHEN NO_DATA_FOUND THEN
1757 l_fi_others_rejct_reason_tab(i) := 'PA_FCST_NOJOB_FOR_ROLE';
1758 l_fi_process_flag_tab(i) := 'N';
1759 l_asg_fcst_job_id := NULL;
1760 l_asg_fcst_job_group_id := NULL;
1761 WHEN OTHERS THEN
1762 IF P_PA_DEBUG_MODE = 'Y' THEN
1763 PA_DEBUG.g_err_stage := 'Inside Prj Role others Excep';
1764 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1765 END IF;
1766 RAISE;
1767 END;
1768 END IF; /* fi item type = R chk */
1769 END IF; /* if asg id changes chk */
1770 /* dbms_output.put_line('after prj role '); */
1771
1772 IF l_fi_asgid_tab(i) = -9999 THEN
1773 l_asg_fcst_jobid_tab(i) := NULL;
1774 l_asg_fcst_jobgroupid_tab(i) := NULL;
1775 ELSE
1776 l_asg_fcst_jobid_tab(i) := l_asg_fcst_job_id;
1777 l_asg_fcst_jobgroupid_tab(i) := l_asg_fcst_job_group_id;
1778 IF l_process_fis_flag = 'N' THEN
1779 l_fi_others_rejct_reason_tab(i) := 'E';
1780 l_fi_process_flag_tab(i) := 'X';
1781
1782 /* Forecast Amt Calc Flag should be set to X, so that it would not be
1783 picked up by Org Forecast Generation Process */
1784 END IF;
1785 END IF;
1786
1787 l_cc_sys_link_tab(i) := NULL;
1788 l_cc_taskid_tab(i) := NULL;
1789 l_cc_expitemid_tab(i) := NULL;
1790 l_cc_transsource_tab(i) := NULL;
1791 l_cc_NLOrgzid_tab(i) := NULL;
1792 l_cc_prvdreid_tab(i) := NULL;
1793 l_cc_recvreid_tab(i) := NULL;
1794 lx_cc_type_tab(i) := NULL;
1795 lx_cc_code_tab(i) := NULL;
1796 lx_cc_prvdr_orgzid_tab(i) := NULL;
1797 lx_cc_recvr_orgzid_tab(i) := NULL;
1798 lx_cc_recvr_orgid_tab(i) := NULL;
1799 lx_cc_prvdr_orgid_tab(i) := NULL;
1800
1801 IF l_prev_exp_type <> l_fi_exptype_tab(i) AND
1802 l_fi_process_flag_tab(i) = 'Y' THEN
1803 l_prev_exp_type := l_fi_exptype_tab(i);
1804 BEGIN
1805 SELECT EXPENDITURE_CATEGORY
1806 INTO l_cc_exp_category
1807 FROM pa_expenditure_types WHERE
1808 EXPENDITURE_TYPE = l_prev_exp_type;
1809 EXCEPTION
1810 WHEN NO_DATA_FOUND THEN
1811 l_fi_process_flag_tab(i) := 'N';
1812 l_cc_exp_category := NULL;
1813 WHEN OTHERS THEN
1814 IF P_PA_DEBUG_MODE = 'Y' THEN
1815 PA_DEBUG.g_err_stage := 'Inside Exp Type others Excep';
1816 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1817 END IF;
1818 RAISE;
1819 END;
1820 END IF;
1821 /* dbms_output.put_line('after sys link '); */
1822 l_tp_exp_category(i) := l_cc_exp_category;
1823
1824 IF l_fi_process_flag_tab(i) = 'Y' THEN
1825 /* AND l_fi_amount_type_tab(i) IS NULL THEN */
1826 BEGIN
1827 SELECT
1828 TP_AMOUNT_TYPE
1829 INTO
1830 l_fi_amount_type_tab(i)
1831 FROM Pa_Forecast_Item_Details
1832 WHERE
1833 FORECAST_ITEM_ID = l_fi_id_tab(i) AND
1834 Line_Num = ( SELECT MAX(Line_Num) FROM
1835 Pa_Forecast_Item_Details WHERE
1836 Forecast_Item_Id = l_fi_id_tab(i) AND
1837 Net_Zero_Flag = 'N' );
1838 EXCEPTION
1839 WHEN NO_DATA_FOUND THEN
1840 NULL;
1841 /* dbms_output.put_line('inside no data found for tp amt type'||
1842 l_fi_delete_flag_tab(i) );
1843 dbms_output.put_line('fi id :'||l_fi_id_tab(i) ); */
1844 WHEN OTHERS THEN
1845 retcode := '2';
1846 IF P_PA_DEBUG_MODE = 'Y' THEN
1847 PA_DEBUG.g_err_stage := 'Inside FI Dtls others Excep';
1848 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1849 END IF;
1850 RAISE;
1851 END;
1852 END IF;
1853
1854 IF l_fi_process_flag_tab(i) <> 'Y' THEN
1855 lx_cc_status_tab(i) := 'E';
1856 lx_tp_error_code(i) := 'E';
1857 END IF;
1858
1859 /* The Cross Charge API should process the Forecast Item only,
1860 if there is no error in the Forecast Item level and no error
1861 returned from the Rate API.
1862
1863 The CC API checks for the X_StatusTab value and proceeds with processing
1864 only if the value is NULL */
1865
1866
1867 /* dbms_output.put_line('fi prj id :<'||i ||'>' ||l_fi_projid_tab(i) ||
1868 ' : '|| l_fi_id_tab(i));
1869 dbms_output.put_line('fi asg id :<'||i ||'>' ||l_fi_asgid_tab(i));
1870 dbms_output.put_line('proc flag and qty <'||i ||'>' ||l_fi_process_flag_tab(i)
1871 || ' Q ' || l_fi_qty_tab(i) );
1872 dbms_output.put_line('others :<'||i ||'>' ||l_fi_others_rejct_reason_tab(i)); */
1873
1874 l_fi_rev_rejct_reason_tab(i) := NULL;
1875 l_fi_cst_rejct_reason_tab(i) := NULL;
1876 l_fi_bd_rejct_reason_tab(i) := NULL;
1877 l_fi_tp_rejct_reason_tab(i) := NULL;
1878
1879 l_fia_cost_txn_curr_code(i) := NULL;
1880 l_fia_rev_txn_curr_code(i) := NULL;
1881 l_fia_txn_raw_cost(i) := NULL;
1882 l_fia_txn_bd_cost(i) := NULL;
1883 l_fia_txn_revenue(i) := NULL;
1884
1885 l_fia_expfunc_curr_code(i) := NULL;
1886 l_fia_expfunc_raw_cost(i) := NULL;
1887 l_fia_expfunc_bd_cost(i) := NULL;
1888
1889 l_fia_projfunc_raw_cost(i) := NULL;
1890 l_fia_projfunc_bd_cost(i) := NULL;
1891 l_fia_projfunc_revenue(i) := NULL;
1892
1893 l_fia_proj_raw_cost(i) := NULL;
1894 l_fia_proj_bd_cost(i) := NULL;
1895 l_fia_proj_revenue(i) := NULL;
1896
1897
1898 l_fia_proj_cost_rate_type(i) := NULL;
1899 l_fia_proj_cost_rate_date(i) := NULL;
1900 l_fia_proj_cost_ex_rate(i) := NULL;
1901
1902 l_fia_proj_rev_rate_type(i) := NULL;
1903 l_fia_proj_rev_rate_date(i) := NULL;
1904 l_fia_proj_rev_ex_rate(i) := NULL;
1905
1906 l_fia_expfunc_cost_rate_type(i) := NULL;
1907 l_fia_expfunc_cost_rate_date(i) := NULL;
1908 l_fia_expfunc_cost_ex_rate(i) := NULL;
1909
1910 l_fia_projfunc_cost_rate_type(i) := NULL;
1911 l_fia_projfunc_cost_rate_date(i) := NULL;
1912 l_fia_projfunc_cost_ex_rate(i) := NULL;
1913
1914 l_fia_projfunc_rev_rate_type(i) := NULL;
1915 l_fia_projfunc_rev_rate_date(i) := NULL;
1916 l_fia_projfunc_rev_ex_rate(i) := NULL;
1917
1918
1919 END LOOP;
1920 /* end loop for setting other plsql table values
1921 for rt_tmp in 1 .. l_fi_id_tab.count loop
1922 dbms_output.put_line('index: '||rt_tmp ||' asg id '||
1923 l_fi_asgid_tab(rt_tmp) || ' job id :job gr id: '||
1924 l_asg_fcst_jobid_tab(rt_tmp)|| ' '||
1925 l_asg_fcst_jobgroupid_tab(rt_tmp)||' fl '||
1926 l_fi_process_flag_tab(rt_tmp) || ' '||
1927 l_fi_id_tab(rt_tmp));
1928 end loop; */
1929 /* calling Rate API */
1930 /* dbms_output.put_line('before assigning variables for Rate api '); */
1931 l_prev_rt_prj_id := l_fi_projid_tab(1);
1932 l_prev_rt_asg_id := l_fi_asgid_tab(1);
1933 l_prev_rt_personid := l_fi_personid_tab(1);
1934 l_prev_rt_fi_itemtype := l_fi_item_type_tab(1);
1935 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(1);
1936 l_prev_rt_calling_mode := l_fi_item_type_tab(1);
1937 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(1);
1938 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(1);
1939 l_prev_rt_exp_type := l_fi_exptype_tab(1);
1940 l_prev_rt_org_id := l_fi_proj_orgid_tab(1);
1941 l_prev_rt_prj_type := l_prj_type_tab(1);
1942 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(1);
1943 l_prev_rt_proj_currcode := l_prj_curr_code_tab(1);
1944 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(1);
1945 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(1);
1946 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(1);
1947 l_prev_rt_dist_rule := l_distribution_rule_tab(1);
1948 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(1);
1949
1950 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(1);
1951 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(1);
1952 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(1);
1953 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(1);
1954 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(1);
1955 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(1);
1956
1957 /* dbms_output.put_line('after assigning variables for Rate api '); */
1958
1959 l_rt_fi_id_tab.delete;
1960 l_rt_start_date_tab.delete;
1961 l_rt_qty_tab.delete;
1962 l_rt_exp_org_id_tab.delete;
1963 l_rt_exp_organization_id_tab.delete;
1964 l_rt_system_linkage_tab.delete;
1965 lx_rt_others_rejct_reason_tab.delete;
1966
1967 /* new parameters added for Org Forecast */
1968
1969
1970 l_rt_pfunc_rev_rt_date_tab.delete;
1971 l_rt_pfunc_rev_rt_type_tab.delete;
1972 l_rt_pfunc_rev_ex_rt_tab.delete;
1973
1974 l_rt_pfunc_cost_rt_date_tab.delete;
1975 l_rt_pfunc_cost_rt_type_tab.delete;
1976
1977 l_rt_proj_cost_rt_date_tab.delete;
1978 l_rt_proj_cost_rt_type_tab.delete;
1979 l_rt_proj_rev_rt_date_tab.delete;
1980 l_rt_proj_rev_rt_type_tab.delete;
1981 l_rt_proj_rev_ex_rt_tab.delete;
1982 l_rt_proj_rev_rt_dt_code_tab.delete;
1983 l_rt_pfunc_rev_rt_dt_code_tab.delete;
1984
1985 l_temp_last := l_fi_id_tab.LAST;
1986
1987 FOR m IN 1 .. l_fi_id_tab.count LOOP
1988 IF ( ( l_prev_rt_prj_id <> l_fi_projid_tab(m) ) OR
1989
1990 ( l_prev_rt_asg_id <> l_fi_asgid_tab(m) AND
1991 l_prev_rt_asg_id > 0 AND l_fi_asgid_tab(m) > 0 ) OR
1992 ( l_prev_rt_asg_id = -9999 AND l_fi_asgid_tab(m) > 0 ) OR
1993 ( l_prev_rt_asg_id > 0 AND l_fi_asgid_tab(m) = -9999 ) OR
1994 ( l_prev_rt_asg_id IS NOT NULL and l_fi_asgid_tab(m) IS NULL ) OR
1995 ( l_prev_rt_asg_id = -9999 and l_fi_asgid_tab(m) = -9999 AND
1996 l_prev_rt_personid <> l_fi_personid_tab(m) ) )
1997 OR ( m = l_temp_last ) THEN
1998 /* dbms_output.put_line('prev asg id :'||l_prev_rt_asg_id);
1999 dbms_output.put_line('curr asg id :'||l_fi_asgid_tab(m));
2000 dbms_output.put_line('prev per id :'||l_prev_rt_personid);
2001 dbms_output.put_line('curr per id :'||l_fi_personid_tab(m));
2002 dbms_output.put_line('prev prj id :'||l_prev_rt_prj_id);
2003 dbms_output.put_line('curr prj id :'|| l_fi_projid_tab(m)); */
2004 IF m = l_temp_last AND l_fi_process_flag_tab(m) = 'Y' THEN
2005 l_prev_rt_prj_id := l_fi_projid_tab(m);
2006 l_prev_rt_asg_id := l_fi_asgid_tab(m);
2007 l_prev_rt_personid := l_fi_personid_tab(m);
2008 l_prev_rt_fi_itemtype := l_fi_item_type_tab(m);
2009 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2010 l_prev_rt_calling_mode := l_fi_item_type_tab(m);
2011 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(m);
2012 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(m);
2013 l_prev_rt_exp_type := l_fi_exptype_tab(m);
2014 l_prev_rt_org_id := l_fi_proj_orgid_tab(m);
2015 l_prev_rt_prj_type := l_prj_type_tab(m);
2016 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(m);
2017 l_prev_rt_proj_currcode := l_prj_curr_code_tab(m);
2018 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(m);
2019 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(m);
2020 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(m);
2021 l_prev_rt_dist_rule := l_distribution_rule_tab(m);
2022 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(m);
2023
2024 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(m);
2025 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(m);
2026 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(m);
2027 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(m);
2028 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(m);
2029 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(m);
2030
2031 l_rt_fi_id_tab(m) := l_fi_id_tab(m);
2032 l_rt_start_date_tab(m) := l_fi_date_tab(m);
2033 l_rt_qty_tab(m) := l_fi_qty_tab(m);
2034 l_rt_system_linkage_tab(m) := l_fi_exptypeclass_tab(m);
2035 l_rt_exp_org_id_tab(m) := l_fi_exp_orgid_tab(m);
2036 l_rt_exp_organization_id_tab(m) := l_fi_exp_organizationid_tab(m);
2037 lx_rt_others_rejct_reason_tab(m) := l_fi_others_rejct_reason_tab(m);
2038
2039 l_rt_pfunc_rev_rt_date_tab(m) := l_prjfunc_bil_rate_date_tab(m);
2040 l_rt_pfunc_rev_rt_type_tab(m) := l_prjfunc_bil_rate_type_tab(m);
2041 l_rt_pfunc_rev_ex_rt_tab(m) := l_prjfunc_bil_ex_rate_tab(m);
2042 l_rt_pfunc_cost_rt_date_tab(m) := l_prjfunc_cost_rt_dt_tab(m);
2043 l_rt_pfunc_cost_rt_type_tab(m) := l_prjfunc_cost_rt_type_tab(m);
2044 l_rt_proj_cost_rt_date_tab(m) := l_prj_rate_date_tab(m);
2045 l_rt_proj_cost_rt_type_tab(m) := l_prj_rate_type_tab(m);
2046 l_rt_proj_rev_rt_date_tab(m) := l_prj_bil_rate_date_tab(m);
2047 l_rt_proj_rev_rt_type_tab(m) := l_prj_bil_rate_type_tab(m);
2048 l_rt_proj_rev_ex_rt_tab(m) := l_prj_bil_ex_rate_tab(m);
2049 l_rt_proj_rev_rt_dt_code_tab(m) := l_prjfunc_bil_rt_dt_code_tab(m);
2050 l_rt_pfunc_rev_rt_dt_code_tab(m) := l_prj_bil_rate_dt_code_tab(m);
2051 END IF;
2052
2053 IF l_rt_start_date_tab.COUNT > 0 THEN
2054
2055 /* dbms_output.put_line('st dt :'|| l_rt_start_date_tab.count);
2056 dbms_output.put_line('id :'|| l_rt_fi_id_tab.count);
2057 dbms_output.put_line('qty :'|| l_rt_qty_tab.count);
2058 dbms_output.put_line('sys lk:'|| l_rt_system_linkage_tab.count);
2059 dbms_output.put_line('exp org :'|| l_rt_exp_org_id_tab.count);
2060 dbms_output.put_line('exp orgz :'||l_rt_exp_organization_id_tab.count);
2061 dbms_output.put_line('pf bl rt dt:'|| l_rt_pfunc_rev_rt_date_tab.count);
2062 dbms_output.put_line('pf bl rt ty :'||l_rt_pfunc_rev_rt_type_tab.count);
2063 dbms_output.put_line('pf bl ex rt :'||l_rt_pfunc_rev_ex_rt_tab.count);
2064 dbms_output.put_line('pf ct rt dt :'||l_rt_pfunc_cost_rt_date_tab.count);
2065 dbms_output.put_line('pf ct rt ty :'||l_rt_pfunc_cost_rt_type_tab.count);
2066 dbms_output.put_line('pf bl rt dt cd :'||l_rt_pfunc_rev_rt_dt_code_tab.count);
2067 dbms_output.put_line('p bl rt dt:'||l_rt_proj_rev_rt_date_tab.count);
2068 dbms_output.put_line('p_bl_rt ty:'|| l_rt_proj_rev_rt_type_tab.count);
2069 dbms_output.put_line('p_bl_ex rt:'||l_rt_proj_rev_ex_rt_tab.count);
2070 dbms_output.put_line('p bl rt dt cd:'||l_rt_proj_rev_rt_dt_code_tab.count);
2071 dbms_output.put_line('p ct rt dt:'|| l_rt_proj_cost_rt_date_tab.count);
2072 dbms_output.put_line('p ct rt ty:'|| l_rt_proj_cost_rt_type_tab.count);
2073 dbms_output.put_line('other :'||lx_rt_others_rejct_reason_tab.count);
2074 dbms_output.put_line('asg id :'||l_prev_rt_asg_id);
2075 dbms_output.put_line('asg id :'||l_prev_rt_asg_id); */
2076
2077 IF l_prev_rt_fi_itemtype = 'UNASSIGNED' THEN
2078 l_amount_calc_mode := 'COST';
2079 ELSE
2080 l_amount_calc_mode := 'ALL';
2081 END IF;
2082
2083 IF P_PA_DEBUG_MODE = 'Y' THEN
2084 PA_DEBUG.g_err_stage := 'Bef calling Rate API for Asg Id:Person Id' ||
2085 TO_CHAR(l_prev_rt_asg_id)||' : '||
2086 TO_CHAR(l_prev_rt_personid);
2087 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2088 END IF;
2089 /* dbms_output.put_line('before calling Rate api :count'||
2090 l_rt_start_date_tab.count); */
2091 PA_RATE_PVT_PKG.Calc_Rate_Amount(
2092 P_CALLING_MODE => l_prev_rt_fi_itemtype,
2093 p_forecasting_type => 'ORG_FORECASTING',
2094 p_amount_calc_mode => l_amount_calc_mode,
2095 P_RATE_CALC_DATE_TAB => l_rt_start_date_tab,
2096 P_ITEM_ID => l_prev_rt_asg_id,
2097 P_ASSIGN_PRECEDES_TASK => l_prev_rt_asg_precedes_task,
2098 P_LABOR_SCHDL_DISCNT => l_prev_rt_labor_sch_discount,
2099 P_LABOR_BILL_RATE_ORG_ID => l_prev_rt_labor_bill_rt_orgid,
2100 P_LABOR_STD_BILL_RATE_SCHDL => l_prev_rt_labor_std_bl_rt_sch,
2101 P_LABOR_SCHEDULE_FIXED_DATE => l_prev_rt_labor_sch_fixed_dt,
2102 P_LABOR_SCH_TYPE => l_prev_rt_labor_sch_type,
2103 p_forecast_item_id_tab => l_rt_fi_id_tab,
2104 P_ASGN_START_DATE => NULL,
2105 P_PROJECT_ID => l_prev_rt_prj_id,
2106 P_QUANTITY_TAB => l_rt_qty_tab,
2107 P_SYSTEM_LINKAGE => l_rt_system_linkage_tab,
2108 P_FORECAST_JOB_ID => l_prev_rt_fcst_jobid,
2109 P_FORECAST_JOB_GROUP_ID => l_prev_rt_fcst_jobgroupid,
2110 P_PERSON_ID => l_prev_rt_personid,
2111 P_EXPENDITURE_ORG_ID_TAB => l_rt_exp_org_id_tab,
2112 P_EXPENDITURE_TYPE => l_prev_rt_exp_type,
2113 P_EXPENDITURE_ORGZ_ID_TAB => l_rt_exp_organization_id_tab,
2114 P_PROJECT_ORG_ID => l_prev_rt_org_id,
2115 P_LABOR_COST_MULTI_NAME => NULL,
2116 P_PROJ_COST_JOB_GROUP_ID => NULL,
2117 P_JOB_COST_RATE_SCHEDULE_ID => l_prev_rt_cost_rate_schid,
2118 P_PROJECT_TYPE => l_prev_rt_prj_type,
2119 P_TASK_ID => NULL,
2120 P_PROJFUNC_CURRENCY_CODE => l_prev_rt_projfunc_currcode,
2121 P_BILL_RATE_MULTIPLIER => NULL,
2122 P_PROJECT_BILL_JOB_GROUP_ID => l_prev_rt_bill_jobgroup_id,
2123 P_EMP_BILL_RATE_SCHEDULE_ID => l_prev_rt_emp_bilrate_schid,
2124 P_JOB_BILL_RATE_SCHEDULE_ID => l_prev_rt_job_bilrate_schid,
2125 P_DISTRIBUTION_RULE => l_prev_rt_dist_rule,
2126 X_PROJFUNC_BILL_RT_TAB => lx_rt_projfunc_bill_rate_tab,
2127 X_PROJFUNC_RAW_REVENUE_TAB => lx_rt_pfunc_raw_revenue_tab,
2128 X_PROJFUNC_RAW_CST_TAB => lx_rt_pfunc_raw_cost_tab,
2129 X_PROJFUNC_RAW_CST_RT_TAB => lx_rt_pfunc_raw_cost_rt_tab,
2130 X_PROJFUNC_BURDNED_CST_TAB => lx_rt_pfunc_bd_cost_tab,
2131 X_PROJFUNC_BURDNED_CST_RT_TAB => lx_rt_pfunc_bd_cost_rt_tab,
2132 p_projfunc_rev_rt_date_tab => l_rt_pfunc_rev_rt_date_tab,
2133 p_projfunc_rev_rt_type_tab => l_rt_pfunc_rev_rt_type_tab,
2134 p_projfunc_rev_exch_rt_tab => l_rt_pfunc_rev_ex_rt_tab,
2135 p_projfunc_cst_rt_date_tab => l_rt_pfunc_cost_rt_date_tab,
2136 p_projfunc_cst_rt_type_tab => l_rt_pfunc_cost_rt_type_tab,
2137 x_projfunc_rev_rt_date_tab => lx_rt_pfunc_rev_rt_date_tab,
2138 x_projfunc_rev_rt_type_tab => lx_rt_pfunc_rev_rt_type_tab,
2139 x_projfunc_rev_exch_rt_tab => lx_rt_pfunc_rev_ex_rt_tab,
2140 p_projfunc_rev_rt_dt_code_tab => l_rt_pfunc_rev_rt_dt_code_tab,
2141 x_projfunc_cst_rt_date_tab => lx_rt_pfunc_cost_rt_date_tab,
2142 x_projfunc_cst_rt_type_tab => lx_rt_pfunc_cost_rt_type_tab,
2143 x_projfunc_cst_exch_rt_tab => lx_rt_pfunc_cost_ex_rt_tab,
2144 p_project_currency_code => l_prev_rt_proj_currcode,
2145 x_project_bill_rt_tab => lx_rt_proj_bill_rate_tab,
2146 x_project_raw_revenue_tab => lx_rt_proj_raw_revenue_tab,
2147 p_project_rev_rt_date_tab => l_rt_proj_rev_rt_date_tab,
2148 p_project_rev_rt_type_tab => l_rt_proj_rev_rt_type_tab,
2149 p_project_rev_exch_rt_tab => l_rt_proj_rev_ex_rt_tab,
2150 p_project_rev_rt_dt_code_tab => l_rt_proj_rev_rt_dt_code_tab,
2151 x_project_rev_rt_date_tab => lx_rt_proj_rev_rt_date_tab,
2152 x_project_rev_rt_type_tab => lx_rt_proj_rev_rt_type_tab,
2153 x_project_rev_exch_rt_tab => lx_rt_proj_rev_ex_rt_tab,
2154 x_project_raw_cst_tab => lx_rt_proj_raw_cost_tab,
2155 x_project_raw_cst_rt_tab => lx_rt_proj_raw_cost_rt_tab,
2156 x_project_burdned_cst_tab => lx_rt_proj_bd_cost_tab,
2157 x_project_burdned_cst_rt_tab => lx_rt_proj_bd_cost_rt_tab,
2158 p_project_cst_rt_date_tab => l_rt_proj_cost_rt_date_tab,
2159 p_project_cst_rt_type_tab => l_rt_proj_cost_rt_type_tab,
2160 x_project_cst_rt_date_tab => lx_rt_proj_cost_rt_date_tab,
2161 x_project_cst_rt_type_tab => lx_rt_proj_cost_rt_type_tab,
2162 x_project_cst_exch_rt_tab => lx_rt_proj_cost_ex_rt_tab,
2163 x_exp_func_curr_code_tab => lx_rt_expfunc_curr_code_tab,
2164 x_exp_func_raw_cst_rt_tab => lx_rt_expfunc_raw_cst_rt_tab,
2165 x_exp_func_raw_cst_tab => lx_rt_expfunc_raw_cst_tab,
2166 x_exp_func_burdned_cst_rt_tab => lx_rt_expfunc_bd_cst_rt_tab,
2167 x_exp_func_burdned_cst_tab => lx_rt_expfunc_bd_cst_tab,
2168 x_exp_func_cst_rt_date_tab => lx_rt_expfunc_cost_rt_date_tab,
2169 x_exp_func_cst_rt_type_tab => lx_rt_expfunc_cost_rt_type_tab,
2170 x_exp_func_cst_exch_rt_tab => lx_rt_expfunc_cost_ex_rt_tab,
2171 x_cst_txn_curr_code_tab => lx_rt_cost_txn_curr_code_tab,
2172 x_txn_raw_cst_rt_tab => lx_rt_txn_raw_cost_rt_tab,
2173 x_txn_raw_cst_tab => lx_rt_txn_raw_cost_tab,
2174 x_txn_burdned_cst_rt_tab => lx_rt_txn_bd_cost_rt_tab,
2175 x_txn_burdned_cst_tab => lx_rt_txn_bd_cost_tab,
2176 x_rev_txn_curr_code_tab => lx_rt_rev_txn_curr_code_tab,
2177 x_txn_rev_bill_rt_tab => lx_rt_txn_rev_bill_rt_tab,
2178 x_txn_rev_raw_revenue_tab => lx_rt_txn_raw_revenue_tab,
2179 X_ERROR_MSG => lx_rt_error_msg,
2180 X_REV_REJCT_REASON_TAB => lx_rt_rev_rejct_reason_tab,
2181 X_CST_REJCT_REASON_TAB => lx_rt_cst_rejct_reason_tab,
2182 X_BURDNED_REJCT_REASON_TAB => lx_rt_bd_rejct_reason_tab,
2183 X_OTHERS_REJCT_REASON_TAB => lx_rt_others_rejct_reason_tab,
2184 X_RETURN_STATUS => lx_rt_return_status,
2185 X_MSG_COUNT => lx_rt_msg_count,
2186 X_MSG_DATA => lx_rt_msg_data );
2187
2188 IF P_PA_DEBUG_MODE = 'Y' THEN
2189 PA_DEBUG.g_err_stage := 'Aft calling Rate API';
2190 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2191 END IF;
2192 /* dbms_output.put_line('after calling Rate api'); */
2193
2194 FOR k IN l_fi_process_flag_tab.FIRST .. l_fi_process_flag_tab.LAST LOOP
2195 IF l_fi_process_flag_tab(k) = 'Y' THEN
2196 /* dbms_output.put_line('fi id in rt:'||k ||':'||
2197 l_fi_id_tab(k)); */
2198
2199 IF lx_rt_cost_txn_curr_code_tab.exists(k) THEN
2200 l_tp_denom_currcode(k) := lx_rt_cost_txn_curr_code_tab(k);
2201 END IF;
2202 IF lx_rt_txn_raw_cost_tab.exists(k) THEN
2203 l_tp_denom_raw_cost(k) := lx_rt_txn_raw_cost_tab(k);
2204 END IF;
2205 IF lx_rt_txn_bd_cost_tab.exists(k) THEN
2206 l_tp_denom_bd_cost(k) := lx_rt_txn_bd_cost_tab(k);
2207 END IF;
2208 /* Transfer price API supports only Txn currency for Cost and for
2209 Revenue always Project Functional Currency Amount should be passed
2210 bug 2444090 */
2211 IF lx_rt_pfunc_raw_revenue_tab.exists(k) THEN
2212 l_tp_raw_revenue(k) := lx_rt_pfunc_raw_revenue_tab(k);
2213 END IF;
2214
2215
2216 IF lx_rt_pfunc_raw_cost_tab.exists(k) THEN
2217 l_fia_projfunc_raw_cost(k) := lx_rt_pfunc_raw_cost_tab(k);
2218 END IF;
2219 IF lx_rt_pfunc_bd_cost_tab.exists(k) THEN
2220 l_fia_projfunc_bd_cost(k) := lx_rt_pfunc_bd_cost_tab(k);
2221 END IF;
2222 IF lx_rt_pfunc_raw_revenue_tab.exists(k) THEN
2223 l_fia_projfunc_revenue(k) := lx_rt_pfunc_raw_revenue_tab(k);
2224 END IF;
2225
2226 /* dbms_output.put_line('prjfunc raw cost :'||l_fia_projfunc_raw_cost(k) );
2227 dbms_output.put_line('prjfunc bd cost :'||l_fia_projfunc_bd_cost(k) );
2228 dbms_output.put_line('prjfunc rev :'||l_fia_projfunc_revenue(k) ); */
2229
2230 IF lx_rt_pfunc_cost_rt_type_tab.exists(k) THEN
2231 l_fia_projfunc_cost_rate_type(k) := lx_rt_pfunc_cost_rt_type_tab(k);
2232 END IF;
2233 IF lx_rt_pfunc_cost_rt_date_tab.exists(k) THEN
2234 l_fia_projfunc_cost_rate_date(k) := lx_rt_pfunc_cost_rt_date_tab(k);
2235 END IF;
2236 IF lx_rt_pfunc_cost_ex_rt_tab.exists(k) THEN
2237 l_fia_projfunc_cost_ex_rate(k) := lx_rt_pfunc_cost_ex_rt_tab(k);
2238 END IF;
2239
2240 IF lx_rt_pfunc_rev_rt_type_tab.exists(k) THEN
2241 l_fia_projfunc_rev_rate_type(k) := lx_rt_pfunc_rev_rt_type_tab(k);
2242 END IF;
2243
2244 IF lx_rt_pfunc_rev_rt_date_tab.exists(k) THEN
2245 l_fia_projfunc_rev_rate_date(k) := lx_rt_pfunc_rev_rt_date_tab(k);
2246 END IF;
2247 IF lx_rt_pfunc_rev_ex_rt_tab.exists(k) THEN
2248 l_fia_projfunc_rev_ex_rate(k) := lx_rt_pfunc_rev_ex_rt_tab(k);
2249 END IF;
2250
2251 IF lx_rt_proj_raw_cost_tab.exists(k) THEN
2252 l_fia_proj_raw_cost(k) := lx_rt_proj_raw_cost_tab(k);
2253 END IF;
2254 IF lx_rt_proj_bd_cost_tab.exists(k) THEN
2255 l_fia_proj_bd_cost(k) := lx_rt_proj_bd_cost_tab(k);
2256 END IF;
2257 IF lx_rt_proj_raw_revenue_tab.exists(k) THEN
2258 l_fia_proj_revenue(k) := lx_rt_proj_raw_revenue_tab(k);
2259 END IF;
2260 /* dbms_output.put_line('prj raw cost :'||l_fia_proj_raw_cost(k) );
2261 dbms_output.put_line('prj bd cost :'||l_fia_proj_bd_cost(k) );
2262 dbms_output.put_line('prj rev :'||l_fia_proj_revenue(k) ); */
2263 IF lx_rt_proj_cost_rt_type_tab.exists(k) THEN
2264 l_fia_proj_cost_rate_type(k) := lx_rt_proj_cost_rt_type_tab(k);
2265 END IF;
2266 IF lx_rt_proj_cost_rt_date_tab.exists(k) THEN
2267 l_fia_proj_cost_rate_date(k) := lx_rt_proj_cost_rt_date_tab(k);
2268 END IF;
2269 IF lx_rt_proj_cost_ex_rt_tab.exists(k) THEN
2270 l_fia_proj_cost_ex_rate(k) := lx_rt_proj_cost_ex_rt_tab(k);
2271 /* dbms_output.put_line('prj ex rt:'|| lx_rt_proj_cost_ex_rt_tab(k)); */
2272 END IF;
2273 IF lx_rt_proj_rev_rt_type_tab.exists(k) THEN
2274 l_fia_proj_rev_rate_type(k) := lx_rt_proj_rev_rt_type_tab(k);
2275 END IF;
2276 IF lx_rt_proj_rev_rt_date_tab.exists(k) THEN
2277 l_fia_proj_rev_rate_date(k) := lx_rt_proj_rev_rt_date_tab(k);
2278 END IF;
2279 IF lx_rt_proj_rev_ex_rt_tab.exists(k) THEN
2280 l_fia_proj_rev_ex_rate(k) := lx_rt_proj_rev_ex_rt_tab(k);
2281 END IF;
2282
2283 IF lx_rt_expfunc_curr_code_tab.exists(k) THEN
2284 l_fia_expfunc_curr_code(k) := lx_rt_expfunc_curr_code_tab(k);
2285 END IF;
2286 IF lx_rt_expfunc_cost_rt_type_tab.exists(k) THEN
2287 l_fia_expfunc_cost_rate_type(k) := lx_rt_expfunc_cost_rt_type_tab(k);
2288 END IF;
2289 IF lx_rt_expfunc_cost_rt_date_tab.exists(k) THEN
2290 l_fia_expfunc_cost_rate_date(k) := lx_rt_expfunc_cost_rt_date_tab(k);
2291 END IF;
2292 IF lx_rt_expfunc_cost_ex_rt_tab.exists(k) THEN
2293 l_fia_expfunc_cost_ex_rate(k) := lx_rt_expfunc_cost_ex_rt_tab(k);
2294 END IF;
2295 IF lx_rt_expfunc_raw_cst_tab.exists(k) THEN
2296 l_fia_expfunc_raw_cost(k) := lx_rt_expfunc_raw_cst_tab(k);
2297 END IF;
2298 IF lx_rt_expfunc_bd_cst_tab.exists(k) THEN
2299 l_fia_expfunc_bd_cost(k) := lx_rt_expfunc_bd_cst_tab(k);
2300 END IF;
2301
2302 IF lx_rt_cost_txn_curr_code_tab.exists(k) THEN
2303 l_fia_cost_txn_curr_code(k) := lx_rt_cost_txn_curr_code_tab(k);
2304 END IF;
2305 IF lx_rt_rev_txn_curr_code_tab.exists(k) THEN
2306 l_fia_rev_txn_curr_code(k) := lx_rt_rev_txn_curr_code_tab(k);
2307 END IF;
2308 IF lx_rt_txn_raw_cost_tab.exists(k) THEN
2309 l_fia_txn_raw_cost(k) := lx_rt_txn_raw_cost_tab(k);
2310 END IF;
2311 IF lx_rt_txn_bd_cost_tab.exists(k) THEN
2312 l_fia_txn_bd_cost(k) := lx_rt_txn_bd_cost_tab(k);
2313 END IF;
2314 IF lx_rt_txn_raw_revenue_tab.exists(k) THEN
2315 l_fia_txn_revenue(k) := lx_rt_txn_raw_revenue_tab(k);
2316 END IF;
2317
2318
2319 IF lx_rt_cst_rejct_reason_tab.exists(k) THEN
2320 l_fi_cst_rejct_reason_tab(k) := lx_rt_cst_rejct_reason_tab(k);
2321 END IF;
2322 IF lx_rt_rev_rejct_reason_tab.exists(k) THEN
2323 l_fi_rev_rejct_reason_tab(k) := lx_rt_rev_rejct_reason_tab(k);
2324 END IF;
2325 IF lx_rt_bd_rejct_reason_tab.exists(k) THEN
2326 l_fi_bd_rejct_reason_tab(k) := lx_rt_bd_rejct_reason_tab(k);
2327 END IF;
2328 IF lx_rt_others_rejct_reason_tab.exists(k) THEN
2329 l_fi_others_rejct_reason_tab(k) := lx_rt_others_rejct_reason_tab(k);
2330 END IF;
2331 IF l_fi_process_flag_tab(k) = 'Y' AND
2332 ( l_fi_rev_rejct_reason_tab(k) IS NOT NULL OR
2333 l_fi_cst_rejct_reason_tab(k) IS NOT NULL OR
2334 l_fi_bd_rejct_reason_tab(k) IS NOT NULL OR
2335 l_fi_others_rejct_reason_tab(k) IS NOT NULL ) THEN
2336 /* dbms_output.put_line('inside rate api error');
2337 dbms_output.put_line('fi id:'||l_fi_id_tab(k));
2338 dbms_output.put_line('rev rej code :'||l_fi_rev_rejct_reason_tab(k));
2339 dbms_output.put_line('cst rej code :'||l_fi_cst_rejct_reason_tab(k));
2340 dbms_output.put_line('bd rej code :'||l_fi_bd_rejct_reason_tab(k));
2341 dbms_output.put_line('ot rej code :'||l_fi_others_rejct_reason_tab(k));
2342 dbms_output.put_line('p curr:'||l_prj_curr_code_tab(k)); */
2343 lx_cc_status_tab(k) := 'E';
2344 lx_tp_error_code(k) := 'E';
2345 l_fi_process_flag_tab(k) := 'N';
2346 END IF;
2347 END IF; /* for process flag tab = Y to avoid the index problem */
2348 END LOOP;
2349
2350 l_rt_fi_id_tab.delete;
2351 l_rt_start_date_tab.delete;
2352 l_rt_qty_tab.delete;
2353 l_rt_system_linkage_tab.delete;
2354 l_rt_exp_org_id_tab.delete;
2355 l_rt_exp_organization_id_tab.delete;
2356 lx_rt_others_rejct_reason_tab.delete;
2357
2358 l_rt_pfunc_rev_rt_date_tab.delete;
2359 l_rt_pfunc_rev_rt_type_tab.delete;
2360 l_rt_pfunc_rev_ex_rt_tab.delete;
2361
2362 l_rt_pfunc_cost_rt_date_tab.delete;
2363 l_rt_pfunc_cost_rt_type_tab.delete;
2364
2365 l_rt_proj_cost_rt_date_tab.delete;
2366 l_rt_proj_cost_rt_type_tab.delete;
2367 l_rt_proj_rev_rt_date_tab.delete;
2368 l_rt_proj_rev_rt_type_tab.delete;
2369 l_rt_proj_rev_ex_rt_tab.delete;
2370 l_rt_proj_rev_rt_dt_code_tab.delete;
2371 l_rt_pfunc_rev_rt_dt_code_tab.delete;
2372 END IF; /* for rate table count > 0 */
2373
2374 l_prev_rt_prj_id := l_fi_projid_tab(m);
2375 l_prev_rt_asg_id := l_fi_asgid_tab(m);
2376 l_prev_rt_personid := l_fi_personid_tab(m);
2377 l_prev_rt_fi_itemtype := l_fi_item_type_tab(m);
2378 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2379 l_prev_rt_calling_mode := l_fi_item_type_tab(m);
2380 l_prev_rt_fcst_jobid := l_asg_fcst_jobid_tab(m);
2381 l_prev_rt_fcst_jobgroupid := l_asg_fcst_jobgroupid_tab(m);
2382 l_prev_rt_exp_type := l_fi_exptype_tab(m);
2383 l_prev_rt_org_id := l_fi_proj_orgid_tab(m);
2384 l_prev_rt_prj_type := l_prj_type_tab(m);
2385 l_prev_rt_projfunc_currcode := l_prjfunc_curr_code_tab(m);
2386 l_prev_rt_proj_currcode := l_prj_curr_code_tab(m);
2387 l_prev_rt_bill_jobgroup_id := l_bill_job_group_id_tab(m);
2388 l_prev_rt_emp_bilrate_schid := l_emp_bill_rate_sch_id_tab(m);
2389 l_prev_rt_job_bilrate_schid := l_job_bill_rate_sch_id_tab(m);
2390 l_prev_rt_dist_rule := l_distribution_rule_tab(m);
2391 l_prev_rt_cost_rate_schid := l_prj_cost_rate_schid_tab(m);
2392
2393 l_prev_rt_labor_sch_discount := l_labor_sch_discount_tab(m);
2394 l_prev_rt_asg_precedes_task := l_asg_precedes_task_tab(m);
2395 l_prev_rt_labor_bill_rt_orgid := l_labor_bill_rate_orgid_tab(m);
2396 l_prev_rt_labor_std_bl_rt_sch := l_labor_std_bill_rate_sch_tab(m);
2397 l_prev_rt_labor_sch_fixed_dt := l_labor_sch_fixed_dt_tab(m);
2398 l_prev_rt_labor_sch_type := l_labor_sch_type_tab(m);
2399
2400 /* dbms_output.put_line('inside any changes asg id :'||l_fi_asgid_tab(m)); */
2401
2402 END IF; /* if prj or asg id or person id changes */
2403 IF l_fi_process_flag_tab(m) = 'Y' THEN
2404 l_rt_fi_id_tab(m) := l_fi_id_tab(m);
2405 l_rt_start_date_tab(m) := l_fi_date_tab(m);
2406 l_rt_qty_tab(m) := l_fi_qty_tab(m);
2407 l_rt_system_linkage_tab(m) := l_fi_exptypeclass_tab(m);
2408 l_rt_exp_org_id_tab(m) := l_fi_exp_orgid_tab(m);
2409 l_rt_exp_organization_id_tab(m) := l_fi_exp_organizationid_tab(m);
2410 lx_rt_others_rejct_reason_tab(m) := l_fi_others_rejct_reason_tab(m);
2411
2412 l_rt_pfunc_rev_rt_date_tab(m) := l_prjfunc_bil_rate_date_tab(m);
2413 l_rt_pfunc_rev_rt_type_tab(m) := l_prjfunc_bil_rate_type_tab(m);
2414 l_rt_pfunc_rev_ex_rt_tab(m) := l_prjfunc_bil_ex_rate_tab(m);
2415 l_rt_pfunc_cost_rt_date_tab(m) := l_prjfunc_cost_rt_dt_tab(m);
2416 l_rt_pfunc_cost_rt_type_tab(m) := l_prjfunc_cost_rt_type_tab(m);
2417 l_rt_proj_cost_rt_date_tab(m) := l_prj_rate_date_tab(m);
2418 l_rt_proj_cost_rt_type_tab(m) := l_prj_rate_type_tab(m);
2419 l_rt_proj_rev_rt_date_tab(m) := l_prj_bil_rate_date_tab(m);
2420 l_rt_proj_rev_rt_type_tab(m) := l_prj_bil_rate_type_tab(m);
2421 l_rt_proj_rev_ex_rt_tab(m) := l_prj_bil_ex_rate_tab(m);
2422 l_rt_proj_rev_rt_dt_code_tab(m) := l_prjfunc_bil_rt_dt_code_tab(m);
2423 l_rt_pfunc_rev_rt_dt_code_tab(m) := l_prj_bil_rate_dt_code_tab(m);
2424
2425
2426 END IF;
2427 l_prev_rt_fi_proc_flag := l_fi_process_flag_tab(m);
2428 END LOOP; /* for I1 rate api call */
2429
2430 lx_cc_error_stage := NULL;
2431 lx_cc_error_code := NULL;
2432
2433 /* dbms_output.put_line('bef calling cc ident ');
2434 dbms_output.put_line('count exp orgzid :'||l_fi_exp_organizationid_tab.count);
2435 dbms_output.put_line('count exp org id :'||l_fi_exp_orgid_tab.count);
2436 dbms_output.put_line('count prj id :'||l_fi_projid_tab.count);
2437 dbms_output.put_line('count tsk id :'||l_cc_taskid_tab.count);
2438 dbms_output.put_line('count fi dt id :'||l_fi_date_tab.count);
2439 dbms_output.put_line('count ex item id :'||l_cc_expitemid_tab.count);
2440 dbms_output.put_line('count person id :'||l_fi_personid_tab.count);
2441 dbms_output.put_line('count exp type :'||l_fi_exptype_tab.count);
2442 dbms_output.put_line('count sys link :'||l_fi_exptypeclass_tab.count);
2443 dbms_output.put_line('count prj orgz id:'||l_fi_proj_organizationid_tab.count);
2444 dbms_output.put_line('count prj org id:'||l_fi_proj_orgid_tab.count);
2445 dbms_output.put_line('count tran source:'||l_cc_transsource_tab.count);
2446 dbms_output.put_line('count nl orgz id :'||l_cc_NLOrgzid_tab.count);
2447 dbms_output.put_line('count prvdr eiid :'||l_cc_prvdreid_tab.count);
2448 dbms_output.put_line('count recvr eiid :'||l_cc_recvreid_tab.count);
2449 dbms_output.put_line('count status :'||lx_cc_status_tab.count);
2450 dbms_output.put_line('count cc type :'||lx_cc_type_tab.count);
2451 dbms_output.put_line('count cc code :'||lx_cc_code_tab.count);
2452 dbms_output.put_line('count prvdr orgz :'||lx_cc_prvdr_orgzid_tab.count);
2453 dbms_output.put_line('count recvr orgz :'||lx_cc_recvr_orgzid_tab.count);
2454 dbms_output.put_line('count recvr org :'||lx_cc_recvr_orgid_tab.count);
2455 dbms_output.put_line('count prvdr org :'||lx_cc_prvdr_orgid_tab.count); */
2456
2457 IF P_PA_DEBUG_MODE = 'Y' THEN
2458 PA_DEBUG.g_err_stage := 'Bef calling CC API';
2459 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2460 END IF;
2461
2462 Pa_Cc_Ident.PA_CC_IDENTIFY_TXN_FI(
2463 P_ExpOrganizationIdTab => l_fi_exp_organizationid_tab,
2464 P_ExpOrgidTab => l_fi_exp_orgid_tab,
2465 P_ProjectIdTab => l_fi_projid_tab,
2466 P_TaskIdTab => l_cc_taskid_tab,
2467 P_ExpItemDateTab => l_fi_date_tab,
2468 P_ExpItemIdTab => l_cc_expitemid_tab,
2469 P_PersonIdTab => l_fi_personid_tab,
2470 P_ExpTypeTab => l_fi_exptype_tab,
2471 P_SysLinkTab => l_fi_exptypeclass_tab,
2472 P_PrjOrganizationIdTab => l_fi_proj_organizationid_tab,
2473 P_PrjOrgIdTab => l_fi_proj_orgid_tab,
2474 P_TransSourceTab => l_cc_transsource_tab,
2475 P_NLROrganizationIdTab => l_cc_NLOrgzid_tab,
2476 P_PrvdrLEIdTab => l_cc_prvdreid_tab,
2477 P_RecvrLEIdTab => l_cc_recvreid_tab,
2478 X_StatusTab => lx_cc_status_tab,
2479 X_CrossChargeTypeTab => lx_cc_type_tab,
2480 X_CrossChargeCodeTab => lx_cc_code_tab,
2481 X_PrvdrOrganizationIdTab => lx_cc_prvdr_orgzid_tab,
2482 X_RecvrOrganizationIdTab => lx_cc_recvr_orgzid_tab,
2483 X_RecvrOrgIdTab => lx_cc_recvr_orgid_tab,
2484 X_PrvdrOrgIdTab => lx_cc_prvdr_orgid_tab,
2485 X_Error_Stage => lx_cc_error_stage,
2486 X_Error_Code => lx_cc_error_code );
2487
2488 IF P_PA_DEBUG_MODE = 'Y' THEN
2489 PA_DEBUG.g_err_stage := 'Aft calling CC API';
2490 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2491 END IF;
2492
2493 /* dbms_output.put_line('aft calling cc ident ');
2494 dbms_output.put_line('count exp orgzid :'||l_fi_exp_organizationid_tab.count);
2495 dbms_output.put_line('count exp org id :'||l_fi_exp_orgid_tab.count);
2496 dbms_output.put_line('count prj id :'||l_fi_projid_tab.count);
2497 dbms_output.put_line('count tsk id :'||l_cc_taskid_tab.count);
2498 dbms_output.put_line('count fi dt id :'||l_fi_date_tab.count);
2499 dbms_output.put_line('count ex item id :'||l_cc_expitemid_tab.count);
2500 dbms_output.put_line('count person id :'||l_fi_personid_tab.count);
2501 dbms_output.put_line('count exp type :'||l_fi_exptype_tab.count);
2502 dbms_output.put_line('count sys link :'||l_fi_exptypeclass_tab.count);
2503 dbms_output.put_line('count prj orgz id:'||l_fi_proj_organizationid_tab.count);
2504 dbms_output.put_line('count prj org id:'||l_fi_proj_orgid_tab.count);
2505 dbms_output.put_line('count tran source:'||l_cc_transsource_tab.count);
2506 dbms_output.put_line('count nl orgz id :'||l_cc_NLOrgzid_tab.count);
2507 dbms_output.put_line('count prvdr eiid :'||l_cc_prvdreid_tab.count);
2508 dbms_output.put_line('count recvr eiid :'||l_cc_recvreid_tab.count);
2509 dbms_output.put_line('count status :'||lx_cc_status_tab.count);
2510 dbms_output.put_line('count cc type :'||lx_cc_type_tab.count);
2511 dbms_output.put_line('count cc code :'||lx_cc_code_tab.count);
2512 dbms_output.put_line('count prvdr orgz :'||lx_cc_prvdr_orgzid_tab.count);
2513 dbms_output.put_line('count recvr orgz :'||lx_cc_recvr_orgzid_tab.count);
2514 dbms_output.put_line('count recvr org :'||lx_cc_recvr_orgid_tab.count);
2515 dbms_output.put_line('count prvdr org :'||lx_cc_prvdr_orgid_tab.count); */
2516
2517 FOR l_temp IN 1 .. l_fi_date_tab.count LOOP
2518 IF lx_cc_status_tab(l_temp) IS NOT NULL AND
2519 lx_cc_status_tab(l_temp) <> 'E' THEN
2520 l_fi_tp_rejct_reason_tab(l_temp) := lx_cc_status_tab(l_temp);
2521 lx_tp_error_code(l_temp) := lx_cc_status_tab(l_temp);
2522 l_fi_process_flag_tab(l_temp) := 'N';
2523 ELSIF lx_cc_status_tab(l_temp) IS NULL AND
2524 lx_cc_code_tab(l_temp) NOT IN ( 'B','I') THEN
2525 lx_tp_error_code(l_temp) := 'E';
2526 END IF;
2527 /* dbms_output.put_line('error code :'||lx_tp_error_code(l_temp));
2528 dbms_output.put_line('exp orgid :'||l_fi_exp_orgid_tab(l_temp));
2529 dbms_output.put_line('papd :'||l_fi_pvdr_papd_tab(l_temp)); */
2530
2531 l_tp_pa_date(l_temp) := l_fi_date_tab(l_temp);
2532
2533 IF lx_tp_error_code(l_temp) IS NULL THEN
2534 BEGIN
2535 SELECT End_Date INTO l_tp_pa_date(l_temp)
2536 FROM Pa_Periods_All WHERE
2537 PERIOD_NAME = l_fi_pvdr_papd_tab(l_temp) AND
2538 -- begin:bug:5938943: NVL function has been removed to achieve the performance gain
2539 Org_Id = l_fi_exp_orgid_tab(l_temp);
2540 -- end:bug:5938943
2541 EXCEPTION
2542 WHEN NO_DATA_FOUND THEN
2543 l_fi_process_flag_tab(l_temp) := 'N';
2544 lx_tp_error_code(l_temp) := 'E';
2545 l_fi_others_rejct_reason_tab(l_temp) := 'PA_FP_PVDR_PA_PD_NOT_FOUND';
2546 END;
2547 END IF;
2548
2549 /* assign TP values */
2550
2551 l_tp_labor_nl_flag(l_temp) := 'Y';
2552 l_tp_taskid(l_temp) := NULL;
2553 l_tp_rev_distributed_flag(l_temp) := 'Y';
2554 l_tp_compute_flag(l_temp) := 'Y';
2555 l_tp_nl_resource(l_temp) := NULL;
2556 l_tp_nl_resource_orgzid(l_temp) := NULL;
2557
2558 l_tp_asg_precedes_task_tab(l_temp) := l_asg_precedes_task; -- Added for bug 3260017
2559
2560 /* set NULL to all tp out variables */
2561
2562 lx_proj_tp_rate_type(l_temp) := NULL;
2563 lx_proj_tp_rate_date(l_temp) := NULL;
2564 lx_proj_tp_exchange_rate(l_temp) := NULL;
2565 lx_proj_tp_amt(l_temp) := NULL;
2566
2567 lx_projfunc_tp_rate_type(l_temp) := NULL;
2568 lx_projfunc_tp_rate_date(l_temp) := NULL;
2569 lx_projfunc_tp_exchange_rate(l_temp) := NULL;
2570 lx_projfunc_tp_amt(l_temp) := NULL;
2571
2572 lx_denom_tp_currcode(l_temp) := NULL;
2573 lx_denom_tp_amt(l_temp) := NULL;
2574
2575 lx_expfunc_tp_rate_type(l_temp) := NULL;
2576 lx_expfunc_tp_rate_date(l_temp) := NULL;
2577 lx_expfunc_tp_exchange_rate(l_temp) := NULL;
2578 lx_expfunc_tp_amt(l_temp) := NULL;
2579
2580 lx_cc_markup_basecode(l_temp) := NULL;
2581 lx_tp_ind_compiled_setid(l_temp) := NULL;
2582 lx_tp_bill_rate(l_temp) := NULL;
2583 lx_tp_base_amount(l_temp) := NULL;
2584 lx_tp_bill_markup_percent(l_temp) := NULL;
2585 lx_tp_sch_line_percent(l_temp) := NULL;
2586 lx_tp_rule_percent(l_temp) := NULL;
2587 lx_tp_job_id(l_temp) := NULL;
2588
2589
2590
2591 END LOOP;
2592
2593 /* FOR i_dummy in 1 .. lx_tp_error_code.count loop
2594 dbms_output.put_line('tp error code <'||i_dummy||'>'||
2595 lx_tp_error_code(i_dummy));
2596 dbms_output.put_line('sys link:exp cate:'|| l_fi_exptypeclass_tab(i_dummy)
2597 ||':'||l_tp_exp_category(i_dummy) );
2598
2599 END LOOP; */
2600
2601
2602 /* dbms_output.put_line ( lx_cc_prvdr_orgid_tab.count );
2603 dbms_output.put_line ( lx_cc_prvdr_orgzid_tab.count );
2604 dbms_output.put_line ( lx_cc_recvr_orgid_tab.count );
2605 dbms_output.put_line ( lx_cc_recvr_orgzid_tab.count );
2606 dbms_output.put_line ( l_fi_exp_organizationid_tab.count );
2607 dbms_output.put_line ( l_tp_exp_itemid.count );
2608 dbms_output.put_line ( l_fi_exptype_tab.count );
2609 dbms_output.put_line ( l_tp_exp_category.count );
2610 dbms_output.put_line ( l_fi_date_tab.count );
2611 dbms_output.put_line ( l_tp_labor_nl_flag.count );
2612 dbms_output.put_line ( l_fi_exptypeclass_tab.count );
2613 dbms_output.put_line ( l_tp_taskid.count );
2614 dbms_output.put_line ( l_tp_scheduleid.count );
2615 dbms_output.put_line ( l_tp_denom_currcode.count );
2616 dbms_output.put_line ( l_prj_curr_code_tab.count );
2617 dbms_output.put_line ( l_prjfunc_curr_code_tab.count );
2618 dbms_output.put_line( l_tp_rev_distributed_flag.count );
2619 dbms_output.put_line ( l_tp_compute_flag.count );
2620 dbms_output.put_line ( l_tp_fixed_date.count );
2621 dbms_output.put_line ( l_tp_denom_raw_cost.count );
2622 dbms_output.put_line ( l_tp_denom_bd_cost.count );
2623 dbms_output.put_line ( l_tp_raw_revenue.count );
2624 dbms_output.put_line ( l_tp_asg_precedes_task_tab.count ); -- Added for bug 3260017
2625 dbms_output.put_line ( l_fi_projid_tab.count );
2626 dbms_output.put_line ( l_fi_qty_tab.count );
2627 dbms_output.put_line ( l_fi_personid_tab.count );
2628 dbms_output.put_line ( l_asg_fcst_jobid_tab.count );
2629 dbms_output.put_line ( l_tp_nl_resource.count );
2630 dbms_output.put_line( l_tp_nl_resource_orgzid.count );
2631 dbms_output.put_line ( l_tp_pa_date.count );
2632 dbms_output.put_line ( l_tp_array_size );
2633 dbms_output.put_line ( l_tp_debug_mode );
2634 dbms_output.put_line ( l_fi_amount_type_tab.count );
2635 dbms_output.put_line ( l_fi_asgid_tab.count );
2636 dbms_output.put_line ( lx_proj_tp_rate_type.count );
2637 dbms_output.put_line ( lx_proj_tp_rate_date.count );
2638 dbms_output.put_line ( lx_proj_tp_exchange_rate.count );
2639 dbms_output.put_line ( lx_proj_tp_amt.count );
2640 dbms_output.put_line ( lx_projfunc_tp_rate_type.count );
2641 dbms_output.put_line ( lx_proj_tp_rate_date.count );
2642 dbms_output.put_line ( lx_proj_tp_exchange_rate.count );
2643 dbms_output.put_line ( lx_proj_tp_amt.count );
2644 dbms_output.put_line ( lx_denom_tp_currcode.count );
2645 dbms_output.put_line ( lx_denom_tp_amt.count );
2646 dbms_output.put_line ( lx_expfunc_tp_rate_type.count );
2647 dbms_output.put_line ( lx_expfunc_tp_rate_date.count );
2648 dbms_output.put_line ( lx_expfunc_tp_exchange_rate.count );
2649 dbms_output.put_line ( lx_expfunc_tp_amt.count );
2650 dbms_output.put_line ( lx_cc_markup_basecode.count );
2651 dbms_output.put_line ( lx_tp_ind_compiled_setid.count );
2652 dbms_output.put_line ( lx_tp_bill_rate.count );
2653 dbms_output.put_line ( lx_tp_base_amount.count );
2654 dbms_output.put_line ( lx_tp_bill_markup_percent.count );
2655 dbms_output.put_line( lx_tp_sch_line_percent.count );
2656 dbms_output.put_line ( lx_tp_rule_percent.count );
2657 dbms_output.put_line ( lx_tp_job_id.count );
2658 dbms_output.put_line ( lx_tp_error_code.count ); */
2659
2660 /* call Trf Price API to get Cross Charge Amounts */
2661
2662 l_tp_asgid.delete;
2663 l_call_tp_api_flag := 'N';
2664
2665 FOR l_tp_asg_idx IN 1 .. l_fi_asgid_tab.COUNT LOOP
2666 IF l_fi_asgid_tab(l_tp_asg_idx) <= 0 THEN
2667 l_tp_asgid(l_tp_asg_idx) := NULL;
2668 ELSE
2669 l_tp_asgid(l_tp_asg_idx) := l_fi_asgid_tab(l_tp_asg_idx);
2670 END IF;
2671 IF lx_tp_error_code(l_tp_asg_idx) IS NULL THEN
2672 l_call_tp_api_flag := 'Y';
2673 END IF;
2674 END LOOP;
2675
2676 IF l_call_tp_api_flag = 'Y' THEN
2677 FORALL b_tmp IN 1 .. l_fi_id_tab.COUNT
2678 INSERT INTO Pa_Fi_Amount_Dtls_Tmp(
2679 FORECAST_ITEM_ID ,
2680 ITEM_DATE ,
2681 ITEM_UOM ,
2682 ITEM_QUANTITY ,
2683 COST_TXN_CURRENCY_CODE ,
2684 REVENUE_TXN_CURRENCY_CODE ,
2685 TXN_RAW_COST ,
2686 TXN_BURDENED_COST ,
2687 TXN_REVENUE ,
2688 TP_TXN_CURRENCY_CODE_IN ,
2689 TP_TXN_CURRENCY_CODE_OUT ,
2690 TXN_TRANSFER_PRICE ,
2691 PROJECT_CURRENCY_CODE ,
2692 PROJECT_COST_RATE_DATE ,
2693 PROJECT_COST_RATE_TYPE ,
2694 PROJECT_COST_EXCHANGE_RATE ,
2695 PROJECT_RAW_COST ,
2696 PROJECT_BURDENED_COST ,
2697 PROJECT_REVENUE_RATE_DATE ,
2698 PROJECT_REVENUE_RATE_TYPE ,
2699 PROJECT_REVENUE_EXCHANGE_RATE,
2700 PROJECT_REVENUE ,
2701 PROJECT_TP_RATE_DATE ,
2702 PROJECT_TP_RATE_TYPE ,
2703 PROJECT_TP_EXCHANGE_RATE ,
2704 PROJECT_TRANSFER_PRICE ,
2705 PROJFUNC_CURRENCY_CODE ,
2706 PROJFUNC_COST_RATE_DATE ,
2707 PROJFUNC_COST_RATE_TYPE ,
2708 PROJFUNC_COST_EXCHANGE_RATE ,
2709 PROJFUNC_RAW_COST ,
2710 PROJFUNC_BURDENED_COST ,
2711 PROJFUNC_REVENUE_RATE_DATE ,
2712 PROJFUNC_REVENUE_RATE_TYPE ,
2713 PROJFUNC_REVENUE_EXCHANGE_RATE,
2714 PROJFUNC_REVENUE ,
2715 PROJFUNC_TP_RATE_DATE ,
2716 PROJFUNC_TP_RATE_TYPE ,
2717 PROJFUNC_TP_EXCHANGE_RATE ,
2718 PROJFUNC_TRANSFER_PRICE ,
2719 EXPFUNC_CURRENCY_CODE ,
2720 EXPFUNC_COST_RATE_DATE ,
2721 EXPFUNC_COST_RATE_TYPE ,
2722 EXPFUNC_COST_EXCHANGE_RATE ,
2723 EXPFUNC_RAW_COST ,
2724 EXPFUNC_BURDENED_COST ,
2725 EXPFUNC_TP_RATE_DATE ,
2726 EXPFUNC_TP_RATE_TYPE ,
2727 EXPFUNC_TP_EXCHANGE_RATE ,
2728 EXPFUNC_TRANSFER_PRICE ,
2729 CC_PRVDR_ORG_ID ,
2730 CC_PRVDR_ORGANIZITION_ID ,
2731 CC_RECVR_ORG_ID ,
2732 CC_RECVR_ORGANIZITION_ID ,
2733 EXPENDITURE_ORGANIZATION_ID ,
2734 EXPENDITURE_TYPE ,
2735 EXPENDITURE_TYPE_CLASS ,
2736 EXPENDITURE_CATEGORY ,
2737 TP_LABOR_NL_FLAG ,
2738 TP_TASK_ID ,
2739 TP_SCHEDULE_ID ,
2740 TP_REV_DISTRIBUTED_FLAG ,
2741 TP_COMPUTE_FLAG ,
2742 TP_FIXED_DATE ,
2743 PROJECT_ID ,
2744 PERSON_ID ,
2745 FORECAST_JOB_ID ,
2746 TP_NL_RESOURCE ,
2747 TP_NL_RESOURCE_ORGZ_ID ,
2748 TP_PA_DATE ,
2749 TP_AMOUNT_TYPE ,
2750 assignment_id ,
2751 fi_process_flag ,
2752 delete_flag ,
2753 tp_error_code ,
2754 COST_REJECTION_CODE ,
2755 REV_REJECTION_CODE ,
2756 BURDEN_REJECTION_CODE ,
2757 OTHER_REJECTION_CODE ,
2758 TP_DENOM_RAW_COST ,
2759 TP_DENOM_BURDENED_COST ,
2760 TP_RAW_REVENUE ,
2761 tp_ind_compiled_setid ,
2762 tp_bill_rate ,
2763 tp_base_amount ,
2764 tp_bill_markup_percent ,
2765 tp_sch_line_percent ,
2766 tp_rule_percent ,
2767 tp_job_id ,
2768 cc_markup_basecode )
2769 VALUES(
2770 l_fi_id_tab(b_tmp),
2771 l_fi_date_tab(b_tmp),
2772 l_fi_uom_tab(b_tmp),
2773 l_fi_qty_tab(b_tmp),
2774 l_fia_cost_txn_curr_code(b_tmp),
2775 l_fia_rev_txn_curr_code(b_tmp),
2776 l_fia_txn_raw_cost(b_tmp),
2777 l_fia_txn_bd_cost(b_tmp),
2778 l_fia_txn_revenue(b_tmp),
2779 l_tp_denom_currcode(b_tmp), -- tp txn curr code
2780 lx_denom_tp_currcode(b_tmp),
2781 null, -- TXN_TRANSFER_PRICE
2782 l_prj_curr_code_tab(b_tmp),
2783 l_fia_proj_cost_rate_date(b_tmp),
2784 l_fia_proj_cost_rate_type(b_tmp),
2785 l_fia_proj_cost_ex_rate(b_tmp),
2786 l_fia_proj_raw_cost(b_tmp),
2787 l_fia_proj_bd_cost(b_tmp),
2788 l_fia_proj_rev_rate_date(b_tmp),
2789 l_fia_proj_rev_rate_type(b_tmp),
2790 l_fia_proj_rev_ex_rate(b_tmp),
2791 l_fia_proj_revenue(b_tmp),
2792 null, -- PROJECT_TP_RATE_DATE
2793 null, -- PROJECT_TP_RATE_TYPE
2794 null, -- PROJECT_TP_EXCHANGE_RATE
2795 null, -- PROJECT_TRANSFER_PRICE
2796 l_prjfunc_curr_code_tab(b_tmp),
2797 l_fia_projfunc_cost_rate_date(b_tmp),
2798 l_fia_projfunc_cost_rate_type(b_tmp),
2799 l_fia_projfunc_cost_ex_rate(b_tmp),
2800 l_fia_projfunc_raw_cost(b_tmp),
2801 l_fia_projfunc_bd_cost(b_tmp),
2802 l_fia_projfunc_rev_rate_date (b_tmp),
2803 l_fia_projfunc_rev_rate_type(b_tmp),
2804 l_fia_projfunc_rev_ex_rate (b_tmp),
2805 l_fia_projfunc_revenue(b_tmp),
2806 null, -- PROJFUNC_TP_RATE_DATE
2807 null, -- PROJFUNC_TP_RATE_TYPE
2808 null, -- PROJFUNC_TP_EXCHANGE_RATE
2809 null, -- PROJFUNC_TRANSFER_PRICE
2810 l_fia_expfunc_curr_code(b_tmp),
2811 l_fia_expfunc_cost_rate_date(b_tmp),
2812 l_fia_expfunc_cost_rate_type(b_tmp),
2813 l_fia_expfunc_cost_ex_rate(b_tmp),
2814 l_fia_expfunc_raw_cost(b_tmp),
2815 l_fia_expfunc_bd_cost(b_tmp),
2816 null, -- EXPFUNC_TP_RATE_DATE
2817 null, -- EXPFUNC_TP_RATE_TYPE
2818 null, -- EXPFUNC_TP_EXCHANGE_RATE
2819 null, -- EXPFUNC_TRANSFER_PRICE
2820 lx_cc_prvdr_orgid_tab(b_tmp),
2821 lx_cc_prvdr_orgzid_tab(b_tmp),
2822 lx_cc_recvr_orgid_tab(b_tmp),
2823 lx_cc_recvr_orgzid_tab(b_tmp),
2824 l_fi_exp_organizationid_tab(b_tmp),
2825 l_fi_exptype_tab(b_tmp),
2826 l_fi_exptypeclass_tab(b_tmp),
2827 l_tp_exp_category(b_tmp),
2828 l_tp_labor_nl_flag(b_tmp),
2829 l_tp_taskid(b_tmp),
2830 l_tp_scheduleid(b_tmp),
2831 l_tp_rev_distributed_flag(b_tmp),
2832 l_tp_compute_flag(b_tmp),
2833 l_tp_fixed_date(b_tmp),
2834 l_fi_projid_tab(b_tmp),
2835 l_fi_personid_tab(b_tmp),
2836 l_asg_fcst_jobid_tab(b_tmp),
2837 l_tp_nl_resource(b_tmp),
2838 l_tp_nl_resource_orgzid(b_tmp),
2839 l_tp_pa_date(b_tmp),
2840 l_fi_amount_type_tab(b_tmp),
2841 l_tp_asgid(b_tmp),
2842 l_fi_process_flag_tab(b_tmp),
2843 l_fi_delete_flag_tab(b_tmp),
2844 lx_tp_error_code(b_tmp),
2845 l_fi_cst_rejct_reason_tab(b_tmp),
2846 l_fi_rev_rejct_reason_tab(b_tmp),
2847 l_fi_bd_rejct_reason_tab(b_tmp),
2848 l_fi_others_rejct_reason_tab(b_tmp),
2849 l_tp_denom_raw_cost(b_tmp),
2850 l_tp_denom_bd_cost(b_tmp),
2851 l_tp_raw_revenue(b_tmp) ,
2852 lx_tp_ind_compiled_setid(b_tmp),
2853 lx_tp_bill_rate(b_tmp) ,
2854 lx_tp_base_amount(b_tmp) ,
2855 lx_tp_bill_markup_percent(b_tmp) ,
2856 lx_tp_sch_line_percent(b_tmp) ,
2857 lx_tp_rule_percent(b_tmp) ,
2858 lx_tp_job_id(b_tmp) ,
2859 lx_cc_markup_basecode(b_tmp) );
2860
2861 l_fi_id_tab.delete;
2862 l_fi_date_tab.delete;
2863 l_fi_uom_tab.delete;
2864 l_fi_qty_tab.delete;
2865 l_fia_cost_txn_curr_code.delete;
2866 l_fia_rev_txn_curr_code.delete;
2867 l_fia_txn_raw_cost.delete;
2868 l_fia_txn_bd_cost.delete;
2869 l_fia_txn_revenue.delete;
2870 l_tp_denom_currcode.delete; -- tp txn curr code
2871 lx_denom_tp_currcode.delete; -- tp txn curr code
2872 lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
2873 l_prj_curr_code_tab.delete;
2874 l_fia_proj_cost_rate_date.delete;
2875 l_fia_proj_cost_rate_type.delete;
2876 l_fia_proj_cost_ex_rate.delete;
2877 l_fia_proj_raw_cost.delete;
2878 l_fia_proj_bd_cost.delete;
2879 l_fia_proj_rev_rate_date.delete;
2880 l_fia_proj_rev_rate_type.delete;
2881 l_fia_proj_rev_ex_rate.delete;
2882 l_fia_proj_revenue.delete;
2883 lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
2884 lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
2885 lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
2886 lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
2887 l_prjfunc_curr_code_tab.delete;
2888 l_fia_projfunc_cost_rate_date.delete;
2889 l_fia_projfunc_cost_rate_type.delete;
2890 l_fia_projfunc_cost_ex_rate.delete;
2891 l_fia_projfunc_raw_cost.delete;
2892 l_fia_projfunc_bd_cost.delete;
2893 l_fia_projfunc_rev_rate_date .delete;
2894 l_fia_projfunc_rev_rate_type.delete;
2895 l_fia_projfunc_rev_ex_rate .delete;
2896 l_fia_projfunc_revenue.delete;
2897 lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
2898 lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
2899 lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
2900 lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
2901 l_fia_expfunc_curr_code.delete;
2902 l_fia_expfunc_cost_rate_date.delete;
2903 l_fia_expfunc_cost_rate_type.delete;
2904 l_fia_expfunc_cost_ex_rate.delete;
2905 l_fia_expfunc_raw_cost.delete;
2906 l_fia_expfunc_bd_cost.delete;
2907 lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
2908 lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
2909 lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
2910 lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
2911 lx_cc_prvdr_orgid_tab.delete;
2912 lx_cc_prvdr_orgzid_tab.delete;
2913 lx_cc_recvr_orgid_tab.delete;
2914 lx_cc_recvr_orgzid_tab.delete;
2915 l_fi_exp_organizationid_tab.delete;
2916 l_fi_exptype_tab.delete;
2917 l_fi_exptypeclass_tab.delete;
2918 l_tp_exp_category.delete;
2919 l_tp_labor_nl_flag.delete;
2920 l_tp_taskid.delete;
2921 l_tp_scheduleid.delete;
2922 l_tp_rev_distributed_flag.delete;
2923 l_tp_compute_flag.delete;
2924 l_tp_fixed_date.delete;
2925 l_tp_asg_precedes_task_tab.delete; -- Added for bug 3260017
2926 l_fi_projid_tab.delete;
2927 l_fi_personid_tab.delete;
2928 l_asg_fcst_jobid_tab.delete;
2929 l_tp_nl_resource.delete;
2930 l_tp_nl_resource_orgzid.delete;
2931 l_tp_pa_date.delete;
2932 l_fi_amount_type_tab.delete;
2933 l_tp_asgid.delete;
2934 l_fi_process_flag_tab.delete;
2935 l_fi_delete_flag_tab.delete;
2936 lx_tp_error_code.delete;
2937
2938 lx_tp_ind_compiled_setid.delete;
2939 lx_tp_bill_rate.delete;
2940 lx_tp_base_amount.delete;
2941 lx_tp_bill_markup_percent.delete;
2942 lx_tp_sch_line_percent.delete;
2943 lx_tp_rule_percent.delete;
2944 lx_tp_job_id.delete;
2945 lx_cc_markup_basecode.delete;
2946
2947 l_tp_denom_raw_cost.delete;
2948 l_tp_denom_bd_cost.delete;
2949 l_tp_raw_revenue.delete;
2950
2951 l_fi_cst_rejct_reason_tab.delete;
2952 l_fi_rev_rejct_reason_tab.delete;
2953 l_fi_bd_rejct_reason_tab.delete;
2954 l_fi_others_rejct_reason_tab.delete;
2955
2956
2957 /* dbms_output.put_line('tp api call flag:'||l_call_tp_api_flag); */
2958
2959 SELECT
2960 FORECAST_ITEM_ID ,
2961 ITEM_DATE ,
2962 ITEM_UOM ,
2963 ITEM_QUANTITY ,
2964 COST_TXN_CURRENCY_CODE ,
2965 REVENUE_TXN_CURRENCY_CODE ,
2966 TXN_RAW_COST ,
2967 TXN_BURDENED_COST ,
2968 TXN_REVENUE ,
2969 TP_TXN_CURRENCY_CODE_IN ,
2970 TP_TXN_CURRENCY_CODE_OUT ,
2971 TXN_TRANSFER_PRICE ,
2972 PROJECT_CURRENCY_CODE ,
2973 PROJECT_COST_RATE_DATE ,
2974 PROJECT_COST_RATE_TYPE ,
2975 PROJECT_COST_EXCHANGE_RATE ,
2976 PROJECT_RAW_COST ,
2977 PROJECT_BURDENED_COST ,
2978 PROJECT_REVENUE_RATE_DATE ,
2979 PROJECT_REVENUE_RATE_TYPE ,
2980 PROJECT_REVENUE_EXCHANGE_RATE,
2981 PROJECT_REVENUE ,
2982 PROJECT_TP_RATE_DATE ,
2983 PROJECT_TP_RATE_TYPE ,
2984 PROJECT_TP_EXCHANGE_RATE ,
2985 PROJECT_TRANSFER_PRICE ,
2986 PROJFUNC_CURRENCY_CODE ,
2987 PROJFUNC_COST_RATE_DATE ,
2988 PROJFUNC_COST_RATE_TYPE ,
2989 PROJFUNC_COST_EXCHANGE_RATE ,
2990 PROJFUNC_RAW_COST ,
2991 PROJFUNC_BURDENED_COST ,
2992 PROJFUNC_REVENUE_RATE_DATE ,
2993 PROJFUNC_REVENUE_RATE_TYPE ,
2994 PROJFUNC_REVENUE_EXCHANGE_RATE,
2995 PROJFUNC_REVENUE ,
2996 PROJFUNC_TP_RATE_DATE ,
2997 PROJFUNC_TP_RATE_TYPE ,
2998 PROJFUNC_TP_EXCHANGE_RATE ,
2999 PROJFUNC_TRANSFER_PRICE ,
3000 EXPFUNC_CURRENCY_CODE ,
3001 EXPFUNC_COST_RATE_DATE ,
3002 EXPFUNC_COST_RATE_TYPE ,
3003 EXPFUNC_COST_EXCHANGE_RATE ,
3004 EXPFUNC_RAW_COST ,
3005 EXPFUNC_BURDENED_COST ,
3006 EXPFUNC_TP_RATE_DATE ,
3007 EXPFUNC_TP_RATE_TYPE ,
3008 EXPFUNC_TP_EXCHANGE_RATE ,
3009 EXPFUNC_TRANSFER_PRICE ,
3010 CC_PRVDR_ORG_ID ,
3011 CC_PRVDR_ORGANIZITION_ID ,
3012 CC_RECVR_ORG_ID ,
3013 CC_RECVR_ORGANIZITION_ID ,
3014 EXPENDITURE_ORGANIZATION_ID ,
3015 EXPENDITURE_TYPE ,
3016 EXPENDITURE_TYPE_CLASS ,
3017 EXPENDITURE_CATEGORY ,
3018 TP_LABOR_NL_FLAG ,
3019 TP_TASK_ID ,
3020 TP_SCHEDULE_ID ,
3021 TP_REV_DISTRIBUTED_FLAG ,
3022 TP_COMPUTE_FLAG ,
3023 TP_FIXED_DATE ,
3024 PROJECT_ID ,
3025 PERSON_ID ,
3026 FORECAST_JOB_ID ,
3027 TP_NL_RESOURCE ,
3028 TP_NL_RESOURCE_ORGZ_ID ,
3029 TP_PA_DATE ,
3030 TP_AMOUNT_TYPE ,
3031 assignment_id ,
3032 fi_process_flag ,
3033 delete_flag ,
3034 tp_error_code ,
3035 COST_REJECTION_CODE ,
3036 REV_REJECTION_CODE ,
3037 BURDEN_REJECTION_CODE ,
3038 OTHER_REJECTION_CODE ,
3039 TP_DENOM_RAW_COST ,
3040 TP_DENOM_BURDENED_COST ,
3041 TP_RAW_REVENUE ,
3042 tp_ind_compiled_setid ,
3043 tp_bill_rate ,
3044 tp_base_amount ,
3045 tp_bill_markup_percent ,
3046 tp_sch_line_percent ,
3047 tp_rule_percent ,
3048 tp_job_id ,
3049 cc_markup_basecode BULK COLLECT INTO
3050 l_fi_id_tab,
3051 l_fi_date_tab,
3052 l_fi_uom_tab,
3053 l_fi_qty_tab,
3054 l_fia_cost_txn_curr_code,
3055 l_fia_rev_txn_curr_code,
3056 l_fia_txn_raw_cost,
3057 l_fia_txn_bd_cost,
3058 l_fia_txn_revenue,
3059 l_tp_denom_currcode, -- tp txn curr code
3060 lx_denom_tp_currcode,
3061 lx_denom_tp_amt,
3062 l_prj_curr_code_tab,
3063 l_fia_proj_cost_rate_date,
3064 l_fia_proj_cost_rate_type,
3065 l_fia_proj_cost_ex_rate,
3066 l_fia_proj_raw_cost,
3067 l_fia_proj_bd_cost,
3068 l_fia_proj_rev_rate_date,
3069 l_fia_proj_rev_rate_type,
3070 l_fia_proj_rev_ex_rate,
3071 l_fia_proj_revenue,
3072 lx_proj_tp_rate_date,
3073 lx_proj_tp_rate_type,
3074 lx_proj_tp_exchange_rate,
3075 lx_proj_tp_amt,
3076 l_prjfunc_curr_code_tab,
3077 l_fia_projfunc_cost_rate_date,
3078 l_fia_projfunc_cost_rate_type,
3079 l_fia_projfunc_cost_ex_rate,
3080 l_fia_projfunc_raw_cost,
3081 l_fia_projfunc_bd_cost,
3082 l_fia_projfunc_rev_rate_date ,
3083 l_fia_projfunc_rev_rate_type,
3084 l_fia_projfunc_rev_ex_rate ,
3085 l_fia_projfunc_revenue,
3086 lx_projfunc_tp_rate_date,
3087 lx_projfunc_tp_rate_type,
3088 lx_projfunc_tp_exchange_rate,
3089 lx_projfunc_tp_amt,
3090 l_fia_expfunc_curr_code,
3091 l_fia_expfunc_cost_rate_date,
3092 l_fia_expfunc_cost_rate_type,
3093 l_fia_expfunc_cost_ex_rate,
3094 l_fia_expfunc_raw_cost,
3095 l_fia_expfunc_bd_cost,
3096 lx_expfunc_tp_rate_date,
3097 lx_expfunc_tp_rate_type,
3098 lx_expfunc_tp_exchange_rate,
3099 lx_expfunc_tp_amt,
3100 lx_cc_prvdr_orgid_tab,
3101 lx_cc_prvdr_orgzid_tab,
3102 lx_cc_recvr_orgid_tab,
3103 lx_cc_recvr_orgzid_tab,
3104 l_fi_exp_organizationid_tab,
3105 l_fi_exptype_tab,
3106 l_fi_exptypeclass_tab,
3107 l_tp_exp_category,
3108 l_tp_labor_nl_flag,
3109 l_tp_taskid,
3110 l_tp_scheduleid,
3111 l_tp_rev_distributed_flag,
3112 l_tp_compute_flag,
3113 l_tp_fixed_date,
3114 l_fi_projid_tab,
3115 l_fi_personid_tab,
3116 l_asg_fcst_jobid_tab,
3117 l_tp_nl_resource,
3118 l_tp_nl_resource_orgzid,
3119 l_tp_pa_date,
3120 l_fi_amount_type_tab,
3121 l_tp_asgid,
3122 l_fi_process_flag_tab,
3123 l_fi_delete_flag_tab,
3124 lx_tp_error_code,
3125 l_fi_cst_rejct_reason_tab,
3126 l_fi_rev_rejct_reason_tab,
3127 l_fi_bd_rejct_reason_tab,
3128 l_fi_others_rejct_reason_tab,
3129 l_tp_denom_raw_cost,
3130 l_tp_denom_bd_cost,
3131 l_tp_raw_revenue ,
3132 lx_tp_ind_compiled_setid,
3133 lx_tp_bill_rate,
3134 lx_tp_base_amount,
3135 lx_tp_bill_markup_percent,
3136 lx_tp_sch_line_percent,
3137 lx_tp_rule_percent,
3138 lx_tp_job_id,
3139 lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp WHERE
3140 Tp_Error_Code IS NULL;
3141
3142 l_tp_array_size := l_fi_date_tab.count;
3143
3144
3145 -- Added this for loop for bug 3260017
3146 IF (l_tp_array_size > 0 ) THEN
3147 l_tp_asg_precedes_task_tab.delete;
3148
3149 FOR i IN l_fi_date_tab.first..l_fi_date_tab.last LOOP
3150 l_tp_asg_precedes_task_tab(i) := l_asg_precedes_task;
3151 END LOOP;
3152 END IF;
3153
3154 /* FOR trf_temp IN l_fi_id_tab.first .. l_fi_id_tab.last loop
3155 dbms_output.put_line('before index : proc flag : tp err:'||trf_temp||
3156 ' '||l_fi_process_flag_tab(trf_temp) ||
3157 ' '||lx_tp_error_code(trf_temp));
3158 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab(trf_temp));
3159 dbms_output.put_line('after index : proc flag : tp err:'||trf_temp||
3160 ' '||l_fi_process_flag_tab(trf_temp) ||
3161 ' '||lx_tp_error_code(trf_temp));
3162 dbms_output.put_line('cc pvdr orgid :'||lx_cc_prvdr_orgid_tab(trf_temp));
3163 dbms_output.put_line('cc pvdr orgzd :'||lx_cc_prvdr_orgzid_tab(trf_temp));
3164 dbms_output.put_line('cc rcvr orgid :'||lx_cc_recvr_orgid_tab(trf_temp));
3165 dbms_output.put_line('cc rcvr orgzd :'||lx_cc_recvr_orgzid_tab(trf_temp));
3166 dbms_output.put_line('fi exp orgz d :'||l_fi_exp_organizationid_tab(trf_temp));
3167 dbms_output.put_line('exp item id :'||l_fi_id_tab(trf_temp));
3168 dbms_output.put_line('exp type tab :'||l_fi_exptype_tab(trf_temp));
3169 dbms_output.put_line('index : tp error :'||trf_temp
3170 || ' :'||lx_tp_error_code(trf_temp));
3171 dbms_output.put_line('exp category :'||l_tp_exp_category(trf_temp));
3172 dbms_output.put_line('fi date tab :'||l_fi_date_tab(trf_temp));
3173 dbms_output.put_line('tp labor fg :'||l_tp_labor_nl_flag(trf_temp));
3174 dbms_output.put_line('exp type cls :'||l_fi_exptypeclass_tab(trf_temp));
3175 dbms_output.put_line('task id :'||l_tp_taskid(trf_temp));
3176 dbms_output.put_line('tp sch id :'||l_tp_scheduleid(trf_temp));
3177 dbms_output.put_line('tp denom curr :'||l_tp_denom_currcode(trf_temp));
3178 dbms_output.put_line('l prj curr :'||l_prj_curr_code_tab(trf_temp));
3179 dbms_output.put_line('l pf curr :'||l_prjfunc_curr_code_tab(trf_temp));
3180 dbms_output.put_line('rev disti fg :'||l_tp_rev_distributed_flag(trf_temp));
3181 dbms_output.put_line('tp comp fg :'||l_tp_compute_flag(trf_temp));
3182 dbms_output.put_line('tp fixed dt :'||l_tp_fixed_date(trf_temp));
3183 dbms_output.put_line('tp raw cost :'||l_tp_denom_raw_cost(trf_temp));
3184 dbms_output.put_line('bd cost :'||l_tp_denom_bd_cost(trf_temp));
3185 dbms_output.put_line('raw revenue :'||l_tp_raw_revenue(trf_temp));
3186 dbms_output.put_line('asgn precedes task :'||l_tp_asg_precedes_task_tab(trf_temp)); -- Added for bug 3260017
3187 dbms_output.put_line('projid :'||l_fi_projid_tab(trf_temp));
3188 dbms_output.put_line('fi qty :'||l_fi_qty_tab(trf_temp));
3189 dbms_output.put_line('fi person id :'||l_fi_personid_tab(trf_temp));
3190 dbms_output.put_line('fcst job id :'||l_asg_fcst_jobid_tab(trf_temp));
3191 dbms_output.put_line('nl res :'||l_tp_nl_resource(trf_temp));
3192 dbms_output.put_line('nl orgz id :'||l_tp_nl_resource_orgzid(trf_temp));
3193 dbms_output.put_line('tp pa date :'||l_tp_pa_date(trf_temp));
3194 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab(trf_temp));
3195 dbms_output.put_line('fi asgid :'||l_tp_asgid(trf_temp));
3196 END LOOP;
3197 dbms_output.put_line('tp asgid :'||l_tp_asgid.count);
3198 dbms_output.put_line('tp array siz :'||l_tp_array_size);
3199 dbms_output.put_line('tp debug mode :'||l_tp_debug_mode);
3200 dbms_output.put_line('cc pvdr orgid :'||lx_cc_prvdr_orgid_tab.count);
3201 dbms_output.put_line('cc pvdr orgzd :'||lx_cc_prvdr_orgzid_tab.count);
3202 dbms_output.put_line('cc rcvr orgid :'||lx_cc_recvr_orgid_tab.count);
3203 dbms_output.put_line('cc rcvr orgzd :'||lx_cc_recvr_orgzid_tab.count);
3204 dbms_output.put_line('fi exp orgz d :'||l_fi_exp_organizationid_tab.count);
3205 dbms_output.put_line('exp item id :'||l_fi_id_tab.count);
3206 dbms_output.put_line('exp type tab :'||l_fi_exptype_tab.count);
3207 dbms_output.put_line('exp category :'||l_tp_exp_category.count);
3208 dbms_output.put_line('fi date tab :'||l_fi_date_tab.count);
3209 dbms_output.put_line('tp labor fg :'||l_tp_labor_nl_flag.count);
3210 dbms_output.put_line('exp type cls :'||l_fi_exptypeclass_tab.count);
3211 dbms_output.put_line('task id :'||l_tp_taskid.count);
3212 dbms_output.put_line('tp sch id :'||l_tp_scheduleid.count);
3213 dbms_output.put_line('tp denom curr :'||l_tp_denom_currcode.count);
3214 dbms_output.put_line('l prj curr :'||l_prj_curr_code_tab.count);
3215 dbms_output.put_line('l pf curr :'||l_prjfunc_curr_code_tab.count);
3216 dbms_output.put_line('rev disti fg :'||l_tp_rev_distributed_flag.count);
3217 dbms_output.put_line('tp comp fg :'||l_tp_compute_flag.count);
3218 dbms_output.put_line('tp fixed dt :'||l_tp_fixed_date.count);
3219 dbms_output.put_line('tp raw cost :'||l_tp_denom_raw_cost.count);
3220 dbms_output.put_line('bd cost :'||l_tp_denom_bd_cost.count);
3221 dbms_output.put_line('raw revenue :'||l_tp_raw_revenue.count);
3222 dbms_output.put_line('asgn precedes task :'||l_tp_asg_precedes_task_tab.count); -- Added for bug 3260017
3223 dbms_output.put_line('projid :'||l_fi_projid_tab.count);
3224 dbms_output.put_line('fi qty :'||l_fi_qty_tab.count);
3225 dbms_output.put_line('fi person id :'||l_fi_personid_tab.count);
3226 dbms_output.put_line('fcst job id :'||l_asg_fcst_jobid_tab.count);
3227 dbms_output.put_line('nl res :'||l_tp_nl_resource.count);
3228 dbms_output.put_line('nl orgz id :'||l_tp_nl_resource_orgzid.count);
3229 dbms_output.put_line('tp pa date :'||l_tp_pa_date.count);
3230 dbms_output.put_line('tp array siz :'||l_tp_array_size);
3231 dbms_output.put_line('tp debug mode :'||l_tp_debug_mode);
3232 dbms_output.put_line('fi amt type :'||l_fi_amount_type_tab.count);
3233 dbms_output.put_line('tp asgid :'||l_tp_asgid.count);
3234 dbms_output.put_line('prj tp rt ty :'||lx_proj_tp_rate_type.count);
3235 dbms_output.put_line('prj tp rt dt :'||lx_proj_tp_rate_date.count);
3236 dbms_output.put_line('prj tp ex :'||lx_proj_tp_exchange_rate.count);
3237 dbms_output.put_line('prj tp amt :'||lx_proj_tp_amt.count);
3238 dbms_output.put_line('pf tp rt ty :'||lx_projfunc_tp_rate_type.count);
3239 dbms_output.put_line('pf tp rt dt :'||lx_projfunc_tp_rate_date.count);
3240 dbms_output.put_line('pf tp ex :'||lx_projfunc_tp_exchange_rate.count);
3241 dbms_output.put_line('pf tp amt :'||lx_projfunc_tp_amt.count);
3242 dbms_output.put_line('denom tp curr:'||lx_denom_tp_currcode.count);
3243 dbms_output.put_line('denom tp amt :'||lx_denom_tp_amt.count);
3244 dbms_output.put_line('ef tp rt ty :'||lx_expfunc_tp_rate_type.count);
3245 dbms_output.put_line('ef tp rt dt :'||lx_expfunc_tp_rate_date.count);
3246 dbms_output.put_line('ef tp ex :'||lx_expfunc_tp_exchange_rate.count);
3247 dbms_output.put_line('ef tp amt :'||lx_expfunc_tp_amt.count);
3248 dbms_output.put_line('cc mark :'||lx_cc_markup_basecode.count);
3249 dbms_output.put_line('ind compiled :'||lx_tp_ind_compiled_setid.count);
3250 dbms_output.put_line('tp bill rate :'||lx_tp_bill_rate.count);
3251 dbms_output.put_line('tp base amt :'||lx_tp_base_amount.count);
3252 dbms_output.put_line('tp bill mark :'||lx_tp_bill_markup_percent.count);
3253 dbms_output.put_line('tp sch perc :'||lx_tp_sch_line_percent.count);
3254 dbms_output.put_line('tp rule perc :'||lx_tp_rule_percent.count);
3255 dbms_output.put_line('tp job id :'||lx_tp_job_id.count);
3256
3257 dbms_output.put_line('bef calling trf price'); */
3258
3259 IF P_PA_DEBUG_MODE = 'Y' THEN
3260 PA_DEBUG.g_err_stage := 'Bef calling Trf Price API';
3261 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3262 END IF;
3263
3264 Pa_Cc_Transfer_Price.Get_Transfer_Price(
3265 p_module_name => 'FORECAST',
3266 p_prvdr_operating_unit => lx_cc_prvdr_orgid_tab,
3267 p_prvdr_organization_id => lx_cc_prvdr_orgzid_tab,
3268 p_recvr_org_id => lx_cc_recvr_orgid_tab,
3269 p_recvr_organization_id => lx_cc_recvr_orgzid_tab,
3270 p_expnd_organization_id => l_fi_exp_organizationid_tab,
3271 p_expenditure_item_id => l_fi_id_tab,
3272 p_expenditure_type => l_fi_exptype_tab,
3273 p_expenditure_category => l_tp_exp_category,
3274 p_expenditure_item_date => l_fi_date_tab,
3275 p_labor_non_labor_flag => l_tp_labor_nl_flag,
3276 p_system_linkage_function => l_fi_exptypeclass_tab,
3277 p_task_id => l_tp_taskid,
3278 p_tp_schedule_id => l_tp_scheduleid,
3279 p_denom_currency_code => l_tp_denom_currcode,
3280 p_project_currency_code => l_prj_curr_code_tab,
3281 p_projfunc_currency_code => l_prjfunc_curr_code_tab,
3282 p_revenue_distributed_flag=> l_tp_rev_distributed_flag,
3283 p_processed_thru_date => sysdate,
3284 p_compute_flag => l_tp_compute_flag,
3285 p_tp_fixed_date => l_tp_fixed_date,
3286 p_denom_raw_cost_amount => l_tp_denom_raw_cost,
3287 p_denom_burdened_cost_amount => l_tp_denom_bd_cost,
3288 p_raw_revenue_amount => l_tp_raw_revenue,
3289 p_assignment_precedes_task => l_tp_asg_precedes_task_tab, -- Added for bug 3260017
3290 p_project_id => l_fi_projid_tab,
3291 p_quantity => l_fi_qty_tab,
3292 p_incurred_by_person_id => l_fi_personid_tab,
3293 p_job_id => l_asg_fcst_jobid_tab,
3294 p_non_labor_resource => l_tp_nl_resource,
3295 p_nl_resource_organization_id=> l_tp_nl_resource_orgzid,
3296 p_pa_date => l_tp_pa_date,
3297 p_array_size => l_tp_array_size,
3298 p_debug_mode => l_tp_debug_mode,
3299 p_tp_amt_type_code => l_fi_amount_type_tab,
3300 p_assignment_id => l_tp_asgid,
3301 x_proj_tp_rate_type => lx_proj_tp_rate_type,
3302 x_proj_tp_rate_date => lx_proj_tp_rate_date,
3303 x_proj_tp_exchange_rate => lx_proj_tp_exchange_rate,
3304 x_proj_transfer_price => lx_proj_tp_amt,
3305 x_projfunc_tp_rate_type => lx_projfunc_tp_rate_type,
3306 x_projfunc_tp_rate_date => lx_projfunc_tp_rate_date,
3307 x_projfunc_tp_exchange_rate => lx_projfunc_tp_exchange_rate,
3308 x_projfunc_transfer_price => lx_projfunc_tp_amt,
3309 x_denom_tp_currency_code => lx_denom_tp_currcode,
3310 x_denom_transfer_price => lx_denom_tp_amt,
3311 x_acct_tp_rate_type => lx_expfunc_tp_rate_type,
3312 x_acct_tp_rate_date => lx_expfunc_tp_rate_date,
3313 x_acct_tp_exchange_rate => lx_expfunc_tp_exchange_rate,
3314 x_acct_transfer_price => lx_expfunc_tp_amt,
3315 x_cc_markup_base_code => lx_cc_markup_basecode,
3316 x_tp_ind_compiled_set_id => lx_tp_ind_compiled_setid,
3317 x_tp_bill_rate => lx_tp_bill_rate,
3318 x_tp_base_amount => lx_tp_base_amount,
3319 x_tp_bill_markup_percentage => lx_tp_bill_markup_percent,
3320 x_tp_schedule_line_percentage=> lx_tp_sch_line_percent,
3321 x_tp_rule_percentage => lx_tp_rule_percent,
3322 x_tp_job_id => lx_tp_job_id,
3323 x_error_code => lx_tp_error_code,
3324 x_return_status => lx_tp_return_status );
3325
3326 IF P_PA_DEBUG_MODE = 'Y' THEN
3327 PA_DEBUG.g_err_stage := 'Aft calling Trf Price API';
3328 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3329 END IF;
3330 /* dbms_output.put_line('aft calling trf price'); */
3331
3332 FORALL l_trf_upd IN 1 .. l_fi_date_tab.COUNT
3333 UPDATE Pa_Fi_Amount_Dtls_Tmp SET
3334 TP_TXN_CURRENCY_CODE_out = lx_denom_tp_currcode(l_trf_upd),
3335 TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_trf_upd),
3336 PROJECT_TP_RATE_DATE = lx_proj_tp_rate_date(l_trf_upd),
3337 PROJECT_TP_RATE_TYPE = lx_proj_tp_rate_type(l_trf_upd),
3338 PROJECT_TP_EXCHANGE_RATE = lx_proj_tp_exchange_rate(l_trf_upd),
3339 PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_trf_upd),
3340 PROJFUNC_TP_RATE_DATE = lx_projfunc_tp_rate_date(l_trf_upd),
3341 PROJFUNC_TP_RATE_TYPE = lx_projfunc_tp_rate_type(l_trf_upd),
3342 PROJFUNC_TP_EXCHANGE_RATE = lx_projfunc_tp_exchange_rate(l_trf_upd),
3343 PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_trf_upd),
3344 EXPFUNC_TP_RATE_DATE = lx_expfunc_tp_rate_date(l_trf_upd),
3345 EXPFUNC_TP_RATE_TYPE = lx_expfunc_tp_rate_type(l_trf_upd),
3346 EXPFUNC_TP_EXCHANGE_RATE = lx_expfunc_tp_exchange_rate(l_trf_upd),
3347 EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_trf_upd),
3348 TP_ERROR_CODE = lx_tp_error_code(l_trf_upd)
3349 WHERE Forecast_Item_Id = l_fi_id_tab(l_trf_upd);
3350
3351 /* dbms_output.put_line('no of rows upd '||sql%rowcount); */
3352
3353 l_fi_id_tab.delete;
3354 l_fi_date_tab.delete;
3355 l_fi_uom_tab.delete;
3356 l_fi_qty_tab.delete;
3357 l_fia_cost_txn_curr_code.delete;
3358 l_fia_rev_txn_curr_code.delete;
3359 l_fia_txn_raw_cost.delete;
3360 l_fia_txn_bd_cost.delete;
3361 l_fia_txn_revenue.delete;
3362 l_tp_denom_currcode.delete; -- tp txn curr code
3363 lx_denom_tp_currcode.delete; -- tp txn curr code
3364 lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
3365 l_prj_curr_code_tab.delete;
3366 l_fia_proj_cost_rate_date.delete;
3367 l_fia_proj_cost_rate_type.delete;
3368 l_fia_proj_cost_ex_rate.delete;
3369 l_fia_proj_raw_cost.delete;
3370 l_fia_proj_bd_cost.delete;
3371 l_fia_proj_rev_rate_date.delete;
3372 l_fia_proj_rev_rate_type.delete;
3373 l_fia_proj_rev_ex_rate.delete;
3374 l_fia_proj_revenue.delete;
3375 lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
3376 lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
3377 lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
3378 lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
3379 l_prjfunc_curr_code_tab.delete;
3380 l_fia_projfunc_cost_rate_date.delete;
3381 l_fia_projfunc_cost_rate_type.delete;
3382 l_fia_projfunc_cost_ex_rate.delete;
3383 l_fia_projfunc_raw_cost.delete;
3384 l_fia_projfunc_bd_cost.delete;
3385 l_fia_projfunc_rev_rate_date .delete;
3386 l_fia_projfunc_rev_rate_type.delete;
3387 l_fia_projfunc_rev_ex_rate .delete;
3388 l_fia_projfunc_revenue.delete;
3389 lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
3390 lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
3391 lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
3392 lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
3393 l_fia_expfunc_curr_code.delete;
3394 l_fia_expfunc_cost_rate_date.delete;
3395 l_fia_expfunc_cost_rate_type.delete;
3396 l_fia_expfunc_cost_ex_rate.delete;
3397 l_fia_expfunc_raw_cost.delete;
3398 l_fia_expfunc_bd_cost.delete;
3399 lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
3400 lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
3401 lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
3402 lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
3403 lx_cc_prvdr_orgid_tab.delete;
3404 lx_cc_prvdr_orgzid_tab.delete;
3405 lx_cc_recvr_orgid_tab.delete;
3406 lx_cc_recvr_orgzid_tab.delete;
3407 l_fi_exp_organizationid_tab.delete;
3408 l_fi_exptype_tab.delete;
3409 l_fi_exptypeclass_tab.delete;
3410 l_tp_exp_category.delete;
3411 l_tp_labor_nl_flag.delete;
3412 l_tp_taskid.delete;
3413 l_tp_scheduleid.delete;
3414 l_tp_rev_distributed_flag.delete;
3415 l_tp_compute_flag.delete;
3416 l_tp_fixed_date.delete;
3417 l_fi_projid_tab.delete;
3418 l_fi_personid_tab.delete;
3419 l_asg_fcst_jobid_tab.delete;
3420 l_tp_nl_resource.delete;
3421 l_tp_nl_resource_orgzid.delete;
3422 l_tp_pa_date.delete;
3423 l_fi_amount_type_tab.delete;
3424 l_tp_asgid.delete;
3425 l_fi_process_flag_tab.delete;
3426 l_fi_delete_flag_tab.delete;
3427 lx_tp_error_code.delete;
3428
3429 lx_tp_ind_compiled_setid.delete;
3430 lx_tp_bill_rate.delete;
3431 lx_tp_base_amount.delete;
3432 lx_tp_bill_markup_percent.delete;
3433 lx_tp_sch_line_percent.delete;
3434 lx_tp_rule_percent.delete;
3435 lx_tp_job_id.delete;
3436 lx_cc_markup_basecode.delete;
3437
3438 l_tp_denom_raw_cost.delete;
3439 l_tp_denom_bd_cost.delete;
3440 l_tp_raw_revenue.delete;
3441
3442 l_fi_cst_rejct_reason_tab.delete;
3443 l_fi_rev_rejct_reason_tab.delete;
3444 l_fi_bd_rejct_reason_tab.delete;
3445 l_fi_others_rejct_reason_tab.delete;
3446
3447
3448
3449 SELECT
3450 FORECAST_ITEM_ID ,
3451 ITEM_DATE ,
3452 ITEM_UOM ,
3453 ITEM_QUANTITY ,
3454 COST_TXN_CURRENCY_CODE ,
3455 REVENUE_TXN_CURRENCY_CODE ,
3456 TXN_RAW_COST ,
3457 TXN_BURDENED_COST ,
3458 TXN_REVENUE ,
3459 TP_TXN_CURRENCY_CODE_IN ,
3460 TP_TXN_CURRENCY_CODE_OUT ,
3461 TXN_TRANSFER_PRICE ,
3462 PROJECT_CURRENCY_CODE ,
3463 PROJECT_COST_RATE_DATE ,
3464 PROJECT_COST_RATE_TYPE ,
3465 PROJECT_COST_EXCHANGE_RATE ,
3466 PROJECT_RAW_COST ,
3467 PROJECT_BURDENED_COST ,
3468 PROJECT_REVENUE_RATE_DATE ,
3469 PROJECT_REVENUE_RATE_TYPE ,
3470 PROJECT_REVENUE_EXCHANGE_RATE,
3471 PROJECT_REVENUE ,
3472 PROJECT_TP_RATE_DATE ,
3473 PROJECT_TP_RATE_TYPE ,
3474 PROJECT_TP_EXCHANGE_RATE ,
3475 PROJECT_TRANSFER_PRICE ,
3476 PROJFUNC_CURRENCY_CODE ,
3477 PROJFUNC_COST_RATE_DATE ,
3478 PROJFUNC_COST_RATE_TYPE ,
3479 PROJFUNC_COST_EXCHANGE_RATE ,
3480 PROJFUNC_RAW_COST ,
3481 PROJFUNC_BURDENED_COST ,
3482 PROJFUNC_REVENUE_RATE_DATE ,
3483 PROJFUNC_REVENUE_RATE_TYPE ,
3484 PROJFUNC_REVENUE_EXCHANGE_RATE,
3485 PROJFUNC_REVENUE ,
3486 PROJFUNC_TP_RATE_DATE ,
3487 PROJFUNC_TP_RATE_TYPE ,
3488 PROJFUNC_TP_EXCHANGE_RATE ,
3489 PROJFUNC_TRANSFER_PRICE ,
3490 EXPFUNC_CURRENCY_CODE ,
3491 EXPFUNC_COST_RATE_DATE ,
3492 EXPFUNC_COST_RATE_TYPE ,
3493 EXPFUNC_COST_EXCHANGE_RATE ,
3494 EXPFUNC_RAW_COST ,
3495 EXPFUNC_BURDENED_COST ,
3496 EXPFUNC_TP_RATE_DATE ,
3497 EXPFUNC_TP_RATE_TYPE ,
3498 EXPFUNC_TP_EXCHANGE_RATE ,
3499 EXPFUNC_TRANSFER_PRICE ,
3500 CC_PRVDR_ORG_ID ,
3501 CC_PRVDR_ORGANIZITION_ID ,
3502 CC_RECVR_ORG_ID ,
3503 CC_RECVR_ORGANIZITION_ID ,
3504 EXPENDITURE_ORGANIZATION_ID ,
3505 EXPENDITURE_TYPE ,
3506 EXPENDITURE_TYPE_CLASS ,
3507 EXPENDITURE_CATEGORY ,
3508 TP_LABOR_NL_FLAG ,
3509 TP_TASK_ID ,
3510 TP_SCHEDULE_ID ,
3511 TP_REV_DISTRIBUTED_FLAG ,
3512 TP_COMPUTE_FLAG ,
3513 TP_FIXED_DATE ,
3514 PROJECT_ID ,
3515 PERSON_ID ,
3516 FORECAST_JOB_ID ,
3517 TP_NL_RESOURCE ,
3518 TP_NL_RESOURCE_ORGZ_ID ,
3519 TP_PA_DATE ,
3520 TP_AMOUNT_TYPE ,
3521 assignment_id ,
3522 fi_process_flag ,
3523 delete_flag ,
3524 tp_error_code ,
3525 COST_REJECTION_CODE ,
3526 REV_REJECTION_CODE ,
3527 BURDEN_REJECTION_CODE ,
3528 OTHER_REJECTION_CODE ,
3529 TP_DENOM_RAW_COST ,
3530 TP_DENOM_BURDENED_COST ,
3531 TP_RAW_REVENUE ,
3532 tp_ind_compiled_setid ,
3533 tp_bill_rate ,
3534 tp_base_amount ,
3535 tp_bill_markup_percent ,
3536 tp_sch_line_percent ,
3537 tp_rule_percent ,
3538 tp_job_id ,
3539 cc_markup_basecode BULK COLLECT INTO
3540 l_fi_id_tab,
3541 l_fi_date_tab,
3542 l_fi_uom_tab,
3543 l_fi_qty_tab,
3544 l_fia_cost_txn_curr_code,
3545 l_fia_rev_txn_curr_code,
3546 l_fia_txn_raw_cost,
3547 l_fia_txn_bd_cost,
3548 l_fia_txn_revenue,
3549 l_tp_denom_currcode, -- tp txn curr code
3550 lx_denom_tp_currcode,
3551 lx_denom_tp_amt,
3552 l_prj_curr_code_tab,
3553 l_fia_proj_cost_rate_date,
3554 l_fia_proj_cost_rate_type,
3555 l_fia_proj_cost_ex_rate,
3556 l_fia_proj_raw_cost,
3557 l_fia_proj_bd_cost,
3558 l_fia_proj_rev_rate_date,
3559 l_fia_proj_rev_rate_type,
3560 l_fia_proj_rev_ex_rate,
3561 l_fia_proj_revenue,
3562 lx_proj_tp_rate_date,
3563 lx_proj_tp_rate_type,
3564 lx_proj_tp_exchange_rate,
3565 lx_proj_tp_amt,
3566 l_prjfunc_curr_code_tab,
3567 l_fia_projfunc_cost_rate_date,
3568 l_fia_projfunc_cost_rate_type,
3569 l_fia_projfunc_cost_ex_rate,
3570 l_fia_projfunc_raw_cost,
3571 l_fia_projfunc_bd_cost,
3572 l_fia_projfunc_rev_rate_date ,
3573 l_fia_projfunc_rev_rate_type,
3574 l_fia_projfunc_rev_ex_rate ,
3575 l_fia_projfunc_revenue,
3576 lx_projfunc_tp_rate_date,
3577 lx_projfunc_tp_rate_type,
3578 lx_projfunc_tp_exchange_rate,
3579 lx_projfunc_tp_amt,
3580 l_fia_expfunc_curr_code,
3581 l_fia_expfunc_cost_rate_date,
3582 l_fia_expfunc_cost_rate_type,
3583 l_fia_expfunc_cost_ex_rate,
3584 l_fia_expfunc_raw_cost,
3585 l_fia_expfunc_bd_cost,
3586 lx_expfunc_tp_rate_date,
3587 lx_expfunc_tp_rate_type,
3588 lx_expfunc_tp_exchange_rate,
3589 lx_expfunc_tp_amt,
3590 lx_cc_prvdr_orgid_tab,
3591 lx_cc_prvdr_orgzid_tab,
3592 lx_cc_recvr_orgid_tab,
3593 lx_cc_recvr_orgzid_tab,
3594 l_fi_exp_organizationid_tab,
3595 l_fi_exptype_tab,
3596 l_fi_exptypeclass_tab,
3597 l_tp_exp_category,
3598 l_tp_labor_nl_flag,
3599 l_tp_taskid,
3600 l_tp_scheduleid,
3601 l_tp_rev_distributed_flag,
3602 l_tp_compute_flag,
3603 l_tp_fixed_date,
3604 l_fi_projid_tab,
3605 l_fi_personid_tab,
3606 l_asg_fcst_jobid_tab,
3607 l_tp_nl_resource,
3608 l_tp_nl_resource_orgzid,
3609 l_tp_pa_date,
3610 l_fi_amount_type_tab,
3611 l_tp_asgid,
3612 l_fi_process_flag_tab,
3613 l_fi_delete_flag_tab,
3614 lx_tp_error_code,
3615 l_fi_cst_rejct_reason_tab,
3616 l_fi_rev_rejct_reason_tab,
3617 l_fi_bd_rejct_reason_tab,
3618 l_fi_others_rejct_reason_tab,
3619 l_tp_denom_raw_cost,
3620 l_tp_denom_bd_cost,
3621 l_tp_raw_revenue ,
3622 lx_tp_ind_compiled_setid,
3623 lx_tp_bill_rate,
3624 lx_tp_base_amount,
3625 lx_tp_bill_markup_percent,
3626 lx_tp_sch_line_percent,
3627 lx_tp_rule_percent,
3628 lx_tp_job_id,
3629 lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp;
3630
3631 FOR l_trf_err_idx IN 1 .. l_fi_id_tab.COUNT LOOP
3632 IF lx_tp_error_code(l_trf_err_idx) IS NOT NULL AND
3633 lx_tp_error_code(l_trf_err_idx) <> 'E' THEN
3634 l_fi_process_flag_tab(l_trf_err_idx) := 'N';
3635 END IF;
3636 END LOOP;
3637 /* dbms_output.put_line('after calling tp api :'||l_fi_id_tab.count); */
3638 END IF;
3639
3640 FOR d IN 1 .. l_fi_id_tab.count LOOP
3641
3642 l_t_line_num := 0;
3643
3644 IF l_fi_process_flag_tab(d) IN ( 'Y' , 'X' ) OR
3645 l_fi_delete_flag_tab(d) = 'Y' OR
3646 l_fi_qty_tab(d) <= 0 THEN
3647 BEGIN
3648 SELECT
3649 LINE_NUM ,
3650 ITEM_QUANTITY ,
3651 COST_TXN_CURRENCY_CODE ,
3652 REVENUE_TXN_CURRENCY_CODE ,
3653 TXN_RAW_COST ,
3654 TXN_BURDENED_COST ,
3655 TXN_REVENUE ,
3656 TXN_TRANSFER_PRICE ,
3657 TP_TXN_CURRENCY_CODE ,
3658 PROJECT_CURRENCY_CODE ,
3659 PROJECT_RAW_COST ,
3660 PROJECT_BURDENED_COST ,
3661 PROJECT_COST_RATE_DATE ,
3662 PROJECT_COST_RATE_TYPE ,
3663 PROJECT_COST_EXCHANGE_RATE ,
3664 PROJECT_REVENUE_RATE_DATE ,
3665 PROJECT_REVENUE_RATE_TYPE ,
3666 PROJECT_REVENUE_EXCHANGE_RATE,
3667 PROJECT_REVENUE ,
3668 PROJECT_TP_RATE_DATE ,
3669 PROJECT_TP_RATE_TYPE ,
3670 PROJECT_TP_EXCHANGE_RATE ,
3671 PROJECT_TRANSFER_PRICE ,
3672 PROJFUNC_CURRENCY_CODE ,
3673 PROJFUNC_COST_RATE_DATE ,
3674 PROJFUNC_COST_RATE_TYPE ,
3675 PROJFUNC_COST_EXCHANGE_RATE ,
3676 PROJFUNC_RAW_COST ,
3677 PROJFUNC_BURDENED_COST ,
3678 PROJFUNC_REVENUE_RATE_DATE ,
3679 PROJFUNC_REVENUE_RATE_TYPE ,
3680 PROJFUNC_REVENUE_EXCHANGE_RATE,
3681 PROJFUNC_REVENUE ,
3682 PROJFUNC_TP_RATE_DATE ,
3683 PROJFUNC_TP_RATE_TYPE ,
3684 PROJFUNC_TP_EXCHANGE_RATE ,
3685 PROJFUNC_TRANSFER_PRICE ,
3686 EXPFUNC_CURRENCY_CODE ,
3687 EXPFUNC_COST_RATE_DATE ,
3688 EXPFUNC_COST_RATE_TYPE ,
3689 EXPFUNC_COST_EXCHANGE_RATE ,
3690 EXPFUNC_RAW_COST ,
3691 EXPFUNC_BURDENED_COST ,
3692 EXPFUNC_TP_RATE_DATE ,
3693 EXPFUNC_TP_RATE_TYPE ,
3694 EXPFUNC_TP_EXCHANGE_RATE ,
3695 EXPFUNC_TRANSFER_PRICE
3696 INTO
3697 l_t_LINE_NUM ,
3698 l_t_ITEM_QUANTITY ,
3699 l_t_COST_TXN_CURR_CODE ,
3700 l_t_REV_TXN_CURR_CODE ,
3701 l_t_TXN_RAW_COST ,
3702 l_t_TXN_BD_COST ,
3703 l_t_TXN_REVENUE ,
3704 l_t_TXN_TRANSFER_PRICE ,
3705 l_t_TP_TXN_CURR_CODE ,
3706 l_t_PROJ_CURR_CODE ,
3707 l_t_PROJ_RAW_COST ,
3708 l_t_PROJ_BD_COST ,
3709 l_t_PROJ_COST_RATE_DATE ,
3710 l_t_PROJ_COST_RATE_TYPE ,
3711 l_t_PROJ_COST_EX_RATE ,
3712 l_t_PROJ_REV_RATE_DATE ,
3713 l_t_PROJ_REV_RATE_TYPE ,
3714 l_t_PROJ_REV_EX_RATE ,
3715 l_t_PROJ_REVENUE ,
3716 l_t_PROJ_TP_RATE_DATE ,
3717 l_t_PROJ_TP_RATE_TYPE ,
3718 l_t_PROJ_TP_EX_RATE ,
3719 l_t_PROJ_TRANSFER_PRICE ,
3720 l_t_PFUNC_CURR_CODE ,
3721 l_t_PFUNC_COST_RATE_DATE ,
3722 l_t_PFUNC_COST_RATE_TYPE ,
3723 l_t_PFUNC_COST_EX_RATE ,
3724 l_t_PFUNC_RAW_COST ,
3725 l_t_PFUNC_BD_COST ,
3726 l_t_PFUNC_REV_RATE_DATE ,
3727 l_t_PFUNC_REV_RATE_TYPE ,
3728 l_t_PFUNC_REV_EX_RATE ,
3729 l_t_PFUNC_REVENUE ,
3730 l_t_PFUNC_TP_RATE_DATE ,
3731 l_t_PFUNC_TP_RATE_TYPE ,
3732 l_t_PFUNC_TP_EX_RATE ,
3733 l_t_PFUNC_TRANSFER_PRICE ,
3734 l_t_EFUNC_CURR_CODE ,
3735 l_t_EFUNC_COST_RATE_DATE ,
3736 l_t_EFUNC_COST_RATE_TYPE ,
3737 l_t_EFUNC_COST_EX_RATE ,
3738 l_t_EFUNC_RAW_COST ,
3739 l_t_EFUNC_BD_COST ,
3740 l_t_EFUNC_TP_RATE_DATE ,
3741 l_t_EFUNC_TP_RATE_TYPE ,
3742 l_t_EFUNC_TP_EX_RATE ,
3743 l_t_EFUNC_TRANSFER_PRICE
3744 FROM Pa_Fi_Amount_Details WHERE
3745 Forecast_Item_Id = l_fi_id_tab(d) AND
3746 Line_Num = ( SELECT MAX(LINE_NUM) FROM
3747 Pa_Fi_Amount_Details WHERE
3748 Forecast_Item_Id = l_fi_id_tab(d) );
3749
3750
3751 /* setting the variables for updating the FI amount records */
3752 IF l_t_ITEM_QUANTITY > 0 THEN
3753 l_fid_upd_fcst_itemid(l_fia_upd_index) := l_fi_id_tab(d);
3754 l_fid_upd_line_num(l_fia_upd_index) := l_t_line_num;
3755 l_fid_upd_reversed_flag(l_fia_upd_index) := 'Y';
3756 l_fid_upd_net_zero_flag(l_fia_upd_index) := 'Y';
3757 l_fia_upd_index := l_fia_upd_index + 1;
3758
3759 /* Setting the variables for the reversal record */
3760
3761 l_fid_cost_txn_curr_code(l_fia_index) := l_t_COST_TXN_CURR_CODE;
3762 l_fid_rev_txn_curr_code(l_fia_index) := l_t_REV_TXN_CURR_CODE;
3763 l_fid_txn_raw_cost(l_fia_index) := -l_t_TXN_RAW_COST;
3764 l_fid_txn_bd_cost(l_fia_index) := -l_t_TXN_BD_COST;
3765 l_fid_txn_revenue(l_fia_index) := -l_t_TXN_REVENUE;
3766
3767 l_fid_expfunc_curr_code(l_fia_index) := l_t_EFUNC_CURR_CODE;
3768 l_fid_expfunc_raw_cost(l_fia_index) := -l_t_EFUNC_RAW_COST;
3769 l_fid_expfunc_bd_cost(l_fia_index) := -l_t_EFUNC_BD_COST;
3770
3771 l_fid_projfunc_curr_code(l_fia_index) := l_t_pfunc_curr_code;
3772 l_fid_projfunc_raw_cost(l_fia_index) := -l_t_PFUNC_RAW_COST;
3773 l_fid_projfunc_bd_cost(l_fia_index) := -l_t_PFUNC_BD_COST;
3774 l_fid_projfunc_revenue(l_fia_index) := -l_t_PFUNC_REVENUE;
3775
3776 l_fid_proj_curr_code(l_fia_index) := l_t_proj_curr_code;
3777 l_fid_proj_raw_cost(l_fia_index) := -l_t_PROJ_RAW_COST;
3778 l_fid_proj_bd_cost(l_fia_index) := -l_t_PROJ_BD_COST;
3779 l_fid_proj_revenue(l_fia_index) := -l_t_PROJ_REVENUE;
3780
3781 l_fid_proj_cost_rate_type(l_fia_index) := l_t_PROJ_COST_RATE_TYPE;
3782 l_fid_proj_cost_rate_date(l_fia_index) := l_t_PROJ_COST_RATE_DATE;
3783 l_fid_proj_cost_ex_rate(l_fia_index) := l_t_PROJ_COST_EX_RATE;
3784
3785 l_fid_proj_rev_rate_type(l_fia_index) := l_t_PROJ_REV_RATE_TYPE;
3786 l_fid_proj_rev_rate_date(l_fia_index) := l_t_PROJ_REV_RATE_DATE;
3787 l_fid_proj_rev_ex_rate(l_fia_index) := l_t_PROJ_REV_EX_RATE;
3788
3789 l_fid_expfunc_cost_rate_type(l_fia_index) := l_t_EFUNC_COST_RATE_TYPE;
3790 l_fid_expfunc_cost_rate_date(l_fia_index) := l_t_EFUNC_COST_RATE_DATE;
3791 l_fid_expfunc_cost_ex_rate(l_fia_index) := l_t_EFUNC_COST_EX_RATE;
3792
3793 l_fid_projfunc_cost_rate_type(l_fia_index) := l_t_PFUNC_COST_RATE_TYPE;
3794 l_fid_projfunc_cost_rate_date(l_fia_index) := l_t_PFUNC_COST_RATE_DATE;
3795 l_fid_projfunc_cost_ex_rate(l_fia_index) := l_t_PFUNC_COST_EX_RATE;
3796
3797 l_fid_projfunc_rev_rate_type(l_fia_index) := l_t_PFUNC_REV_RATE_TYPE;
3798 l_fid_projfunc_rev_rate_date(l_fia_index) := l_t_PFUNC_REV_RATE_DATE;
3799 l_fid_projfunc_rev_ex_rate(l_fia_index) := l_t_PFUNC_REV_EX_RATE;
3800
3801 l_fid_proj_tp_rate_type(l_fia_index) := l_t_PROJ_TP_RATE_TYPE;
3802 l_fid_proj_tp_rate_date(l_fia_index) := l_t_PROJ_TP_RATE_DATE;
3803 l_fid_proj_tp_ex_rate(l_fia_index) := l_t_PROJ_TP_EX_RATE;
3804 l_fid_proj_tp_amt(l_fia_index) := -l_t_PROJ_TRANSFER_PRICE;
3805
3806 l_fid_projfunc_tp_rate_type(l_fia_index) := l_t_PFUNC_TP_RATE_TYPE;
3807 l_fid_projfunc_tp_rate_date(l_fia_index) := l_t_PFUNC_TP_RATE_DATE;
3808 l_fid_projfunc_tp_ex_rate(l_fia_index) := l_t_PFUNC_TP_EX_RATE;
3809 l_fid_projfunc_tp_amt(l_fia_index) := -l_t_PFUNC_TRANSFER_PRICE;
3810
3811 l_fid_denom_tp_currcode(l_fia_index) := l_t_TP_TXN_CURR_CODE;
3812 l_fid_denom_tp_amt(l_fia_index) := -l_t_TXN_TRANSFER_PRICE;
3813
3814 l_fid_expfunc_tp_rate_type(l_fia_index) := l_t_EFUNC_TP_RATE_TYPE;
3815 l_fid_expfunc_tp_rate_date(l_fia_index) := l_t_EFUNC_TP_RATE_DATE;
3816 l_fid_expfunc_tp_ex_rate(l_fia_index) := l_t_EFUNC_TP_EX_RATE;
3817 l_fid_expfunc_tp_amt(l_fia_index) := -l_t_EFUNC_TRANSFER_PRICE;
3818
3819 l_fid_fcst_itemid(l_fia_index) := l_fi_id_tab(d);
3820 l_fid_line_num(l_fia_index) := l_t_line_num + 1;
3821 l_fid_item_date(l_fia_index) := l_fi_date_tab(d);
3822 l_fid_item_uom(l_fia_index) := l_fi_uom_tab(d);
3823 l_fid_item_qty(l_fia_index) := -l_t_ITEM_QUANTITY;
3824 l_fid_reversed_flag(l_fia_index) := 'N';
3825 l_fid_net_zero_flag(l_fia_index) := 'Y';
3826 l_fid_line_num_reversed(l_fia_index) := l_t_line_num;
3827
3828 l_t_line_num := l_t_line_num + 1;
3829 l_fia_index := l_fia_index + 1;
3830 END IF;
3831 /* end if for l_t_ITEM_QUANTITY gt zero */
3832 EXCEPTION
3833 WHEN NO_DATA_FOUND THEN
3834 NULL;
3835 WHEN OTHERS THEN
3836 IF P_PA_DEBUG_MODE = 'Y' THEN
3837 PA_DEBUG.g_err_stage := 'Inside FI Amt Dtls others Excep';
3838 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3839 END IF;
3840 RAISE;
3841 END;
3842
3843 /* dbms_output.put_line('assigning values for the new line');
3844 dbms_output.put_line('before txn curr code and txn amts'); */
3845 IF l_fi_process_flag_tab(d) = 'Y' THEN
3846 l_fid_cost_txn_curr_code(l_fia_index) := l_fia_cost_txn_curr_code(d);
3847 l_fid_rev_txn_curr_code(l_fia_index) := l_fia_rev_txn_curr_code(d);
3848 l_fid_txn_raw_cost(l_fia_index) := l_fia_txn_raw_cost(d);
3849 l_fid_txn_bd_cost(l_fia_index) := l_fia_txn_bd_cost(d);
3850 l_fid_txn_revenue(l_fia_index) := l_fia_txn_revenue(d);
3851
3852 /* dbms_output.put_line('before exp func code and amts'); */
3853
3854 l_fid_expfunc_curr_code(l_fia_index) := l_fia_expfunc_curr_code(d);
3855 l_fid_expfunc_raw_cost(l_fia_index) := l_fia_expfunc_raw_cost(d);
3856 l_fid_expfunc_bd_cost(l_fia_index) := l_fia_expfunc_bd_cost(d);
3857
3858 l_fid_expfunc_cost_rate_type(l_fia_index) := l_fia_expfunc_cost_rate_type(d);
3859 l_fid_expfunc_cost_rate_date(l_fia_index) := l_fia_expfunc_cost_rate_date(d);
3860 l_fid_expfunc_cost_ex_rate(l_fia_index) := l_fia_expfunc_cost_ex_rate(d);
3861
3862 l_fid_expfunc_tp_rate_type(l_fia_index) := lx_expfunc_tp_rate_type(d);
3863 l_fid_expfunc_tp_rate_date(l_fia_index) := lx_expfunc_tp_rate_date(d);
3864 l_fid_expfunc_tp_ex_rate(l_fia_index) := lx_expfunc_tp_exchange_rate(d);
3865 l_fid_expfunc_tp_amt(l_fia_index) := lx_expfunc_tp_amt(d);
3866
3867 /* dbms_output.put_line('before proj func code and amts'); */
3868
3869 l_fid_projfunc_curr_code(l_fia_index) := l_prjfunc_curr_code_tab(d);
3870 l_fid_projfunc_raw_cost(l_fia_index) := l_fia_projfunc_raw_cost(d);
3871 l_fid_projfunc_bd_cost(l_fia_index) := l_fia_projfunc_bd_cost(d);
3872 l_fid_projfunc_revenue(l_fia_index) := l_fia_projfunc_revenue(d);
3873
3874 l_fid_projfunc_cost_rate_type(l_fia_index) := l_fia_projfunc_cost_rate_type(d);
3875 l_fid_projfunc_cost_rate_date(l_fia_index) := l_fia_projfunc_cost_rate_date(d);
3876 l_fid_projfunc_cost_ex_rate(l_fia_index) := l_fia_projfunc_cost_ex_rate(d);
3877
3878 l_fid_projfunc_rev_rate_type(l_fia_index) := l_fia_projfunc_rev_rate_type(d);
3879 l_fid_projfunc_rev_rate_date(l_fia_index) := l_fia_projfunc_rev_rate_date(d);
3880 l_fid_projfunc_rev_ex_rate(l_fia_index) := l_fia_projfunc_rev_ex_rate(d);
3881
3882 l_fid_projfunc_tp_rate_type(l_fia_index) := lx_projfunc_tp_rate_type(d);
3883 l_fid_projfunc_tp_rate_date(l_fia_index) := lx_projfunc_tp_rate_date(d);
3884 l_fid_projfunc_tp_ex_rate(l_fia_index) := lx_projfunc_tp_exchange_rate(d);
3885 l_fid_projfunc_tp_amt(l_fia_index) := lx_projfunc_tp_amt(d);
3886
3887 /* dbms_output.put_line('pf tp rt type :'||lx_projfunc_tp_rate_type(d));
3888 dbms_output.put_line('pf tp rt date :'||lx_projfunc_tp_rate_date(d));
3889 dbms_output.put_line('pf tp ex rate :'||lx_projfunc_tp_exchange_rate(d));
3890 dbms_output.put_line('pf tp amount :'||lx_projfunc_tp_amt(d));
3891 dbms_output.put_line('before proj code and amts'); */
3892
3893 l_fid_proj_curr_code(l_fia_index) := l_prj_curr_code_tab(d);
3894 l_fid_proj_raw_cost(l_fia_index) := l_fia_proj_raw_cost(d);
3895 l_fid_proj_bd_cost(l_fia_index) := l_fia_proj_bd_cost(d);
3896 l_fid_proj_revenue(l_fia_index) := l_fia_proj_revenue(d);
3897
3898 /* dbms_output.put_line('before assigning proj rev');
3899 dbms_output.put_line('fia proj rev :'||l_fia_proj_revenue(d));
3900 dbms_output.put_line('fid proj rev :'||l_fid_proj_revenue(l_fia_index)); */
3901
3902 l_fid_proj_cost_rate_type(l_fia_index) := l_fia_proj_cost_rate_type(d);
3903 l_fid_proj_cost_rate_date(l_fia_index) := l_fia_proj_cost_rate_date(d);
3904 l_fid_proj_cost_ex_rate(l_fia_index) := l_fia_proj_cost_ex_rate(d);
3905
3906 l_fid_proj_rev_rate_type(l_fia_index) := l_fia_proj_rev_rate_type(d);
3907 l_fid_proj_rev_rate_date(l_fia_index) := l_fia_proj_rev_rate_date(d);
3908 l_fid_proj_rev_ex_rate(l_fia_index) := l_fia_proj_rev_ex_rate(d);
3909
3910 l_fid_proj_tp_rate_type(l_fia_index) := lx_proj_tp_rate_type(d);
3911 l_fid_proj_tp_rate_date(l_fia_index) := lx_proj_tp_rate_date(d);
3912 l_fid_proj_tp_ex_rate(l_fia_index) := lx_proj_tp_exchange_rate(d);
3913 l_fid_proj_tp_amt(l_fia_index) := lx_proj_tp_amt(d);
3914
3915 /* dbms_output.put_line('before denom and amts'); */
3916
3917 l_fid_denom_tp_currcode(l_fia_index) := lx_denom_tp_currcode(d);
3918 l_fid_denom_tp_amt(l_fia_index) := lx_denom_tp_amt(d);
3919
3920
3921 l_fid_fcst_itemid(l_fia_index) := l_fi_id_tab(d);
3922 l_fid_line_num(l_fia_index) := l_t_line_num + 1;
3923 l_fid_item_date(l_fia_index) := l_fi_date_tab(d);
3924 l_fid_item_uom(l_fia_index) := l_fi_uom_tab(d);
3925 l_fid_item_qty(l_fia_index) := l_fi_qty_tab(d);
3926 l_fid_reversed_flag(l_fia_index) := 'N';
3927 l_fid_net_zero_flag(l_fia_index) := 'N';
3928 l_fid_line_num_reversed(l_fia_index) := NULL;
3929
3930 l_fia_index := l_fia_index + 1;
3931 END IF;
3932 END IF;
3933 /* forecast_process_flag = Y */
3934 END LOOP;
3935
3936 /* dbms_output.put_line('fi amt tab count :'||l_fid_fcst_itemid.count);
3937 dbms_output.put_line('bef inserting FI amount dtls :'); */
3938
3939 IF P_PA_DEBUG_MODE = 'Y' THEN
3940 PA_DEBUG.g_err_stage := 'Bef inserting FI Amt Dtls';
3941 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3942 END IF;
3943
3944 FORALL b IN 1 .. l_fid_fcst_itemid.COUNT
3945 INSERT INTO Pa_Fi_Amount_Details(
3946 FORECAST_ITEM_ID,
3947 LINE_NUM,
3948 ITEM_DATE,
3949 ITEM_UOM,
3950 ITEM_QUANTITY,
3951 NET_ZERO_FLAG,
3952 REVERSED_FLAG,
3953 LINE_NUM_REVERSED,
3954 CREATION_DATE,
3955 CREATED_BY,
3956 LAST_UPDATE_DATE,
3957 LAST_UPDATED_BY,
3958 LAST_UPDATE_LOGIN,
3959 REQUEST_ID,
3960 PROGRAM_APPLICATION_ID,
3961 PROGRAM_ID,
3962 PROGRAM_UPDATE_DATE,
3963 COST_TXN_CURRENCY_CODE,
3964 REVENUE_TXN_CURRENCY_CODE,
3965 TXN_RAW_COST,
3966 TXN_BURDENED_COST,
3967 TXN_REVENUE,
3968 TXN_TRANSFER_PRICE,
3969 TP_TXN_CURRENCY_CODE,
3970 PROJECT_CURRENCY_CODE,
3971 PROJECT_RAW_COST,
3972 PROJECT_BURDENED_COST,
3973 PROJECT_COST_RATE_DATE,
3974 PROJECT_COST_RATE_TYPE,
3975 PROJECT_COST_EXCHANGE_RATE,
3976 PROJECT_REVENUE_RATE_DATE,
3977 PROJECT_REVENUE_RATE_TYPE,
3978 PROJECT_REVENUE_EXCHANGE_RATE,
3979 PROJECT_REVENUE,
3980 PROJECT_TP_RATE_DATE,
3981 PROJECT_TP_RATE_TYPE,
3982 PROJECT_TP_EXCHANGE_RATE,
3983 PROJECT_TRANSFER_PRICE,
3984 PROJFUNC_CURRENCY_CODE,
3985 PROJFUNC_COST_RATE_DATE,
3986 PROJFUNC_COST_RATE_TYPE,
3987 PROJFUNC_COST_EXCHANGE_RATE,
3988 PROJFUNC_RAW_COST,
3989 PROJFUNC_BURDENED_COST,
3990 PROJFUNC_REVENUE_RATE_DATE,
3991 PROJFUNC_REVENUE_RATE_TYPE,
3992 PROJFUNC_REVENUE_EXCHANGE_RATE,
3993 PROJFUNC_REVENUE,
3994 PROJFUNC_TP_RATE_DATE,
3995 PROJFUNC_TP_RATE_TYPE,
3996 PROJFUNC_TP_EXCHANGE_RATE,
3997 PROJFUNC_TRANSFER_PRICE,
3998 EXPFUNC_CURRENCY_CODE,
3999 EXPFUNC_COST_RATE_DATE,
4000 EXPFUNC_COST_RATE_TYPE,
4001 EXPFUNC_COST_EXCHANGE_RATE,
4002 EXPFUNC_RAW_COST,
4003 EXPFUNC_BURDENED_COST,
4004 EXPFUNC_TP_RATE_DATE,
4005 EXPFUNC_TP_RATE_TYPE,
4006 EXPFUNC_TP_EXCHANGE_RATE,
4007 EXPFUNC_TRANSFER_PRICE )
4008 VALUES(
4009 l_fid_fcst_itemid(b),
4010 l_fid_line_num(b),
4011 l_fid_item_date(b),
4012 l_fid_item_uom(b),
4013 l_fid_item_qty(b),
4014 l_fid_net_zero_flag(b),
4015 l_fid_reversed_flag(b),
4016 l_fid_line_num_reversed(b),
4017 l_creation_date,
4018 l_created_by,
4019 l_last_update_date,
4020 l_last_updated_by,
4021 l_last_update_login,
4022 l_request_id,
4023 l_program_application_id,
4024 l_program_id,
4025 l_last_update_date,
4026 l_fid_cost_txn_curr_code(b),
4027 l_fid_rev_txn_curr_code(b),
4028 l_fid_txn_raw_cost(b),
4029 l_fid_txn_bd_cost(b),
4030 l_fid_txn_revenue(b),
4031 l_fid_denom_tp_amt(b),
4032 l_fid_denom_tp_currcode(b),
4033 l_fid_proj_curr_code(b),
4034 l_fid_proj_raw_cost(b),
4035 l_fid_proj_bd_cost(b),
4036 l_fid_proj_cost_rate_date(b),
4037 l_fid_proj_cost_rate_type(b),
4038 l_fid_proj_cost_ex_rate(b),
4039 l_fid_proj_rev_rate_date(b),
4040 l_fid_proj_rev_rate_type(b),
4041 l_fid_proj_rev_ex_rate(b),
4042 l_fid_proj_revenue(b),
4043 l_fid_proj_tp_rate_date(b),
4044 l_fid_proj_tp_rate_type(b),
4045 l_fid_proj_tp_ex_rate(b),
4046 l_fid_proj_tp_amt(b),
4047 l_fid_projfunc_curr_code(b),
4048 l_fid_projfunc_cost_rate_date(b),
4049 l_fid_projfunc_cost_rate_type(b),
4050 l_fid_projfunc_cost_ex_rate(b),
4051 l_fid_projfunc_raw_cost(b),
4052 l_fid_projfunc_bd_cost(b),
4053 l_fid_projfunc_rev_rate_date(b),
4054 l_fid_projfunc_rev_rate_type(b),
4055 l_fid_projfunc_rev_ex_rate(b),
4056 l_fid_projfunc_revenue(b),
4057 l_fid_projfunc_tp_rate_date(b),
4058 l_fid_projfunc_tp_rate_type(b),
4059 l_fid_projfunc_tp_ex_rate(b),
4060 l_fid_projfunc_tp_amt(b),
4061 l_fid_expfunc_curr_code(b),
4062 l_fid_expfunc_cost_rate_date(b),
4063 l_fid_expfunc_cost_rate_type(b),
4064 l_fid_expfunc_cost_ex_rate(b),
4065 l_fid_expfunc_raw_cost(b),
4066 l_fid_expfunc_bd_cost(b),
4067 l_fid_expfunc_tp_rate_date(b),
4068 l_fid_expfunc_tp_rate_type(b),
4069 l_fid_expfunc_tp_ex_rate(b),
4070 l_fid_expfunc_tp_amt(b) );
4071
4072 IF P_PA_DEBUG_MODE = 'Y' THEN
4073 PA_DEBUG.g_err_stage := 'Bef updating FI Amt Dtls';
4074 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4075 END IF;
4076 /* dbms_output.put_line('bef updating FI amount dtls :'); */
4077
4078 FORALL l_upd_idx IN 1 .. l_fid_upd_fcst_itemid.COUNT
4079 UPDATE Pa_FI_Amount_Details SET
4080 Reversed_flag = 'Y',
4081 Net_Zero_Flag = 'Y',
4082 LAST_UPDATE_DATE = l_last_update_date,
4083 LAST_UPDATED_BY = l_last_updated_by,
4084 LAST_UPDATE_LOGIN = l_last_update_login,
4085 REQUEST_ID = l_request_id,
4086 PROGRAM_APPLICATION_ID = l_program_application_id,
4087 PROGRAM_ID = l_program_id,
4088 PROGRAM_UPDATE_DATE = l_last_update_date
4089 WHERE
4090 Forecast_Item_Id = l_fid_upd_fcst_itemid(l_upd_idx) AND
4091 Line_Num = l_fid_upd_line_num(l_upd_idx);
4092
4093 IF P_PA_DEBUG_MODE = 'Y' THEN
4094 PA_DEBUG.g_err_stage := 'Bef updating Fcst Items';
4095 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4096 END IF;
4097 /* dbms_output.put_line('bef updating FIs :'); */
4098
4099 FOR l_tupd IN 1 .. l_fi_id_tab.COUNT LOOP
4100 IF l_fi_delete_flag_tab(l_tupd) = 'Y' OR l_fi_qty_tab(l_tupd) <= 0 THEN
4101 l_fi_process_flag_tab(l_tupd) := 'X';
4102 END IF;
4103 IF NVL(l_fi_others_rejct_reason_tab(l_tupd),'abcx') = 'E' THEN
4104 l_fi_others_rejct_reason_tab(l_tupd) := NULL;
4105 END IF;
4106 IF NVL(lx_tp_error_code(l_tupd),'abcx') = 'E' THEN
4107 lx_tp_error_code(l_tupd) := NULL;
4108 END IF;
4109 IF l_fi_process_flag_tab(l_tupd) <> 'Y' OR
4110 l_fi_delete_flag_tab(l_tupd) = 'Y' OR
4111 l_fi_qty_tab(l_tupd) <= 0 THEN
4112 l_fia_cost_txn_curr_code(l_tupd) := NULL;
4113 l_fia_rev_txn_curr_code(l_tupd) := NULL;
4114 l_fia_txn_raw_cost(l_tupd) := NULL;
4115 l_fia_txn_bd_cost(l_tupd) := NULL;
4116 l_fia_txn_revenue(l_tupd) := NULL;
4117 lx_denom_tp_currcode(l_tupd) := NULL;
4118 lx_denom_tp_amt(l_tupd) := NULL;
4119 l_fia_expfunc_curr_code(l_tupd) := NULL;
4120 l_fia_expfunc_raw_cost(l_tupd) := NULL;
4121 l_fia_expfunc_bd_cost(l_tupd) := NULL;
4122 lx_expfunc_tp_amt(l_tupd) := NULL;
4123 l_prjfunc_curr_code_tab(l_tupd) := NULL;
4124 l_fia_projfunc_raw_cost(l_tupd) := NULL;
4125 l_fia_projfunc_bd_cost(l_tupd) := NULL;
4126 l_fia_projfunc_revenue(l_tupd) := NULL;
4127 lx_projfunc_tp_amt(l_tupd) := NULL;
4128 l_prj_curr_code_tab(l_tupd) := NULL;
4129 l_fia_proj_raw_cost(l_tupd) := NULL;
4130 l_fia_proj_bd_cost(l_tupd) := NULL;
4131 l_fia_proj_revenue(l_tupd) := NULL;
4132 lx_proj_tp_amt(l_tupd) := NULL;
4133 END IF;
4134 END LOOP;
4135
4136 /* dbms_output.put_line(' pro flag '||l_fi_process_flag_tab.count);
4137 dbms_output.put_line(' amt type '||l_fi_amount_type_tab.count);
4138 dbms_output.put_line(' cst txn curr '||l_fia_cost_txn_curr_code.count);
4139 dbms_output.put_line(' rev txn curr '||l_fia_rev_txn_curr_code.count);
4140 dbms_output.put_line(' txn raw cost '||l_fia_txn_raw_cost.count);
4141 dbms_output.put_line(' txn bd cost '||l_fia_txn_bd_cost.count);
4142 dbms_output.put_line(' txn rev '||l_fia_txn_revenue.count);
4143 dbms_output.put_line(' denom tp curr '||lx_denom_tp_currcode.count);
4144 dbms_output.put_line(' denom tp amt '||lx_denom_tp_amt.count);
4145 dbms_output.put_line(' ef curr '||l_fia_expfunc_curr_code.count);
4146 dbms_output.put_line(' ef raw cst '||l_fia_expfunc_raw_cost.count);
4147 dbms_output.put_line(' ef bd cst '||l_fia_expfunc_bd_cost.count);
4148 dbms_output.put_line(' ef tp amt '||lx_expfunc_tp_amt.count);
4149 dbms_output.put_line(' pf curr '||l_prjfunc_curr_code_tab.count);
4150 dbms_output.put_line(' pf raw cst '||l_fia_projfunc_raw_cost.count);
4151 dbms_output.put_line(' pf bd cst '||l_fia_projfunc_bd_cost.count);
4152 dbms_output.put_line(' pf rev '||l_fia_projfunc_revenue.count);
4153 dbms_output.put_line(' pf tp amt '||lx_projfunc_tp_amt.count);
4154 dbms_output.put_line(' prj curr '||l_prj_curr_code_tab.count);
4155 dbms_output.put_line(' prj raw cst '||l_fia_proj_raw_cost.count);
4156 dbms_output.put_line(' prj bd cst '||l_fia_proj_bd_cost.count);
4157 dbms_output.put_line(' prj rev '||l_fia_proj_revenue.count);
4158 dbms_output.put_line(' prj tp amt '||lx_proj_tp_amt.count);
4159 dbms_output.put_line(' cst rej '||l_fi_cst_rejct_reason_tab.count);
4160 dbms_output.put_line(' rev rej '||l_fi_rev_rejct_reason_tab.count);
4161 dbms_output.put_line(' tp rej '||lx_tp_error_code.count);
4162 dbms_output.put_line(' bd rej '||l_fi_bd_rejct_reason_tab.count);
4163 dbms_output.put_line(' other rej '||l_fi_others_rejct_reason_tab.count);
4164 dbms_output.put_line(' fi id '||l_fi_id_tab.count); */
4165
4166
4167 FORALL l_fi_upd_index IN 1 .. l_fi_id_tab.COUNT
4168 UPDATE Pa_Forecast_Items SET
4169 FORECAST_AMT_CALC_FLAG = l_fi_process_flag_tab(l_fi_upd_index),
4170 TP_AMOUNT_TYPE = l_fi_amount_type_tab(l_fi_upd_index),
4171 COST_TXN_CURRENCY_CODE = l_fia_cost_txn_curr_code(l_fi_upd_index),
4172 REVENUE_TXN_CURRENCY_CODE = l_fia_rev_txn_curr_code(l_fi_upd_index),
4173 TXN_RAW_COST = l_fia_txn_raw_cost(l_fi_upd_index),
4174 TXN_BURDENED_COST = l_fia_txn_bd_cost(l_fi_upd_index),
4175 TXN_REVENUE = l_fia_txn_revenue(l_fi_upd_index),
4176 TP_TXN_CURRENCY_CODE = lx_denom_tp_currcode(l_fi_upd_index),
4177 TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_fi_upd_index),
4178 EXPFUNC_CURRENCY_CODE = l_fia_expfunc_curr_code(l_fi_upd_index),
4179 EXPFUNC_RAW_COST = l_fia_expfunc_raw_cost(l_fi_upd_index),
4180 EXPFUNC_BURDENED_COST = l_fia_expfunc_bd_cost(l_fi_upd_index),
4181 EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_fi_upd_index),
4182 PROJFUNC_CURRENCY_CODE = l_prjfunc_curr_code_tab(l_fi_upd_index),
4183 PROJFUNC_RAW_COST = l_fia_projfunc_raw_cost(l_fi_upd_index),
4184 PROJFUNC_BURDENED_COST = l_fia_projfunc_bd_cost(l_fi_upd_index),
4185 PROJFUNC_REVENUE = l_fia_projfunc_revenue(l_fi_upd_index),
4186 PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_fi_upd_index),
4187 PROJECT_CURRENCY_CODE = l_prj_curr_code_tab(l_fi_upd_index),
4188 PROJECT_RAW_COST = l_fia_proj_raw_cost(l_fi_upd_index),
4189 PROJECT_BURDENED_COST = l_fia_proj_bd_cost(l_fi_upd_index),
4190 PROJECT_REVENUE = l_fia_proj_revenue(l_fi_upd_index),
4191 PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_fi_upd_index),
4192 COST_REJECTION_CODE = l_fi_cst_rejct_reason_tab(l_fi_upd_index),
4193 REV_REJECTION_CODE = l_fi_rev_rejct_reason_tab(l_fi_upd_index),
4194 TP_REJECTION_CODE = lx_tp_error_code(l_fi_upd_index),
4195 BURDEN_REJECTION_CODE = l_fi_bd_rejct_reason_tab(l_fi_upd_index),
4196 OTHER_REJECTION_CODE = l_fi_others_rejct_reason_tab(l_fi_upd_index),
4197 LAST_UPDATE_DATE = l_last_update_date,
4198 LAST_UPDATED_BY = l_last_updated_by,
4199 LAST_UPDATE_LOGIN = l_last_update_login,
4200 REQUEST_ID = l_request_id,
4201 PROGRAM_APPLICATION_ID = l_program_application_id,
4202 PROGRAM_ID = l_program_id,
4203 PROGRAM_UPDATE_DATE = l_last_update_date
4204 WHERE Forecast_Item_Id = l_fi_id_tab(l_fi_upd_index);
4205
4206 /* dbms_output.put_line('records updated in FI :'||sql%rowcount ); */
4207
4208
4209 /* for i in 1.. l_fi_id_tab.count loop
4210 dbms_output.put_line(i ||' pid ' ||l_fi_projid_tab(i) || ' aid '||l_fi_asgid_tab(i)
4211 ||' ity ' ||l_fi_item_type_tab(I)
4212 ||' etyp ' ||l_fi_exptype_Tab(i)
4213 ||' sys ' ||l_fi_exptypeclass_tab(i)
4214 ||' cty ' ||lx_cc_type_tab(i)
4215 ||' cc ' ||lx_cc_code_tab(i)
4216 ||' csta ' ||lx_cc_status_tab(i) );
4217 dbms_output.put_line('pfunc curr:'||l_prjfunc_curr_code_tab(i));
4218 dbms_output.put_line('p curr:'||l_prj_curr_code_tab(i));
4219 end loop; */
4220
4221 COMMIT;
4222 END LOOP;
4223 /* main loop for bulk fetch from FIs */
4224
4225 /* Bug 3051110 - Code added for populating two columns in pa_project_assignments table
4226 TP Enhancement */
4227
4228 IF p_debug_mode = 'Y' THEN
4229 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Start of populating TP Rate columns', 3);
4230 END IF;
4231
4232 /* The cursor Cur_Assignments will get all the assignment_ids processed in this particular request */
4233
4234 Open Cur_Assignments(l_request_id);
4235 LOOP
4236 FETCH Cur_Assignments INTO l_assignment_id;
4237 EXIT WHEN Cur_Assignments%NOTFOUND;
4238
4239 IF p_debug_mode = 'Y' THEN
4240 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Currently Populating for assignment_id :'||l_assignment_id, 3);
4241 END IF;
4242
4243 /*
4244 Getting the sum of projfunc_transfer_price and item_quantity from pa_forecast_items table for the assignment_id,
4245 if any of the sum is NULL we populate the transfer_price_rate as NULL and transfer_pr_rate_curr as NULL and if
4246 any of the sum is 0, we populate the transfer_price_rate as 0 and transfer_pr_rate_curr as the project
4247 functional currency. If both the sums have non zero value and they are not null, we calculate the average
4248 transfer_price_rate and call the api to populate the transfer_price_rate as average Transfer_price_Rate and
4249 transfer_pr_rate_curr as the project functional currency
4250 */
4251
4252 BEGIN
4253 Select sum(PROJFUNC_TRANSFER_PRICE), Sum(ITEM_QUANTITY)
4254 INTO l_sum_transfer_price, l_sum_item_quantity
4255 From PA_FORECAST_ITEMS
4256 Where assignment_id = l_assignment_ID and delete_flag = 'N'
4257 and error_flag = 'N' And forecast_amt_Calc_flag ='Y';
4258 EXCEPTION WHEN NO_DATA_FOUND THEN
4259 l_sum_transfer_price := Null;
4260 l_sum_item_quantity := Null;
4261 END;
4262
4263 IF p_debug_mode = 'Y' THEN
4264 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'TP rate for assignment:'||l_sum_transfer_price, 3);
4265 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Item Quantity for assignment:'||l_sum_item_quantity, 3);
4266 pa_debug.write('Pa_Fi_Amt_Calc_Pkg.Calculate_Fcst_Amounts', 'Proj Func Curency:'||l_prjfunc_curr_code, 3);
4267 END IF;
4268
4269 IF l_sum_transfer_price is NULL OR l_sum_item_quantity is NULL OR l_sum_item_quantity = 0 THEN
4270 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
4271 (
4272 p_assignment_id => l_assignment_id
4273 ,p_debug_mode => p_debug_mode
4274 ,p_transfer_price_rate => NULL
4275 ,p_transfer_pr_rate_curr=> NULL
4276 ,x_return_status => l_return_status
4277 );
4278 ELSE
4279 l_average_transfer_price_rate := l_sum_transfer_price / l_sum_item_quantity;
4280 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
4281 (
4282 p_assignment_id => l_assignment_id
4283 ,p_debug_mode => p_debug_mode
4284 ,p_transfer_price_rate => l_average_transfer_price_rate
4285 ,p_transfer_pr_rate_curr=> l_prjfunc_curr_code
4286 ,x_return_status => l_return_status
4287 );
4288 END IF;
4289
4290 END LOOP;
4291 CLOSE Cur_Assignments;
4292
4293 IF p_run_mode = 'F' THEN
4294 IF P_PA_DEBUG_MODE = 'Y' THEN
4295 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_All and returning';
4296 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4297 END IF;
4298 CLOSE fcst_item_All;
4299 ELSIF p_run_mode = 'I' THEN
4300 IF P_PA_DEBUG_MODE = 'Y' THEN
4301 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Inc and returning';
4302 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4303 END IF;
4304 CLOSE fcst_item_Inc;
4305 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
4306 p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
4307 IF P_PA_DEBUG_MODE = 'Y' THEN
4308 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj and returning';
4309 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4310 END IF;
4311 CLOSE fcst_item_Prj;
4312 ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
4313 p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
4314 IF P_PA_DEBUG_MODE = 'Y' THEN
4315 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj_Asg and returning';
4316 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4317 END IF;
4318 CLOSE fcst_item_Prj_Asg;
4319 ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
4320 IF P_PA_DEBUG_MODE = 'Y' THEN
4321 PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Organization and returning';
4322 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4323 END IF;
4324 CLOSE fcst_item_Organization;
4325 END IF;
4326 /* Bug fix:4329035 */
4327 IF P_PA_DEBUG_MODE = 'Y' THEN
4328 PA_DEBUG.Reset_Curr_Function;
4329 END IF;
4330 RETURN;
4331 EXCEPTION
4332 WHEN OTHERS THEN
4333 retcode := '2';
4334 IF P_PA_DEBUG_MODE = 'Y' THEN
4335 PA_DEBUG.g_err_stage := 'Inside Main Others Excep';
4336 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4337 PA_DEBUG.Reset_Curr_Function;
4338 END IF;
4339 RAISE;
4340 END;
4341 END Pa_Fi_Amt_Calc_Pkg;