[Home] [Help]
PACKAGE BODY: APPS.PA_CC_TRANSFER_PRICE
Source
1 PACKAGE BODY PA_CC_TRANSFER_PRICE AS
2 /* $Header: PAXCCTPB.pls 120.18 2010/10/06 21:53:33 apaul ship $ */
3
4 -------------------------------------------------------------------------------
5 -- Define global variables
6 /** Added for Org Forecasting **/
7
8 G_prvdr_org_id_Tab PA_PLSQL_DATATYPES.IdTabTyp;
9 G_bg_id_Tab PA_PLSQL_DATATYPES.IdTabTyp;
10 G_acct_currency_code_Tab PA_PLSQL_DATATYPES.Char15TabTyp;
11 G_cc_default_rate_type_Tab PA_PLSQL_DATATYPES.Char15TabTyp;
12 G_cc_def_rate_date_code_Tab PA_PLSQL_DATATYPES.Char15TabTyp;
13 G_exp_org_struct_ver_tab PA_PLSQL_DATATYPES.IdTabTyp;
14 G_Calling_Module VARCHAR2(30);
15 /** End for Org Forecasting **/
16
17 G_prvdr_org_id NUMBER;
18 G_bg_id NUMBER;
19 G_prvdr_legal_entity_id NUMBER;
20 G_acct_currency_code VARCHAR2(15);
21 G_cc_default_rate_type VARCHAR2(30);
22 G_cc_default_rate_date_code VARCHAR2(1);
23 G_processed_thru_date DATE;
24 G_array_size Number;
25 G_Basis_Exists Boolean;
26 G_Bill_Rate_Exists Boolean;
27 G_Burden_Rate_Exists Boolean;
28 G_global_access varchar2(1):= pa_cross_business_grp.IsCrossBGProfile;
29
30 --DevDrop2 Changes
31 --Added org_struct variables.
32
33 G_exp_org_struct_ver_id Number;
34 G_prj_org_struct_ver_id Number;
35
36 G_prev_rcvr_org_id Number := -999999 ;
37
38 -- Define WHO columns
39 G_created_by Number;
40 G_last_updated_by Number;
41 G_last_update_login Number;
42 G_creation_date Date;
43 G_last_update_date Date;
44 G_sysdate Date := sysdate;
45 -------------------------------------------------------------------------------
46
47 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
48
49 PROCEDURE Get_Transfer_Price
50 (
51 p_module_name IN VARCHAR2,
52 p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
53 p_recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
54 p_recvr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
55 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
56 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
57 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
58 p_expenditure_category IN PA_PLSQL_DATATYPES.Char30TabTyp,
59 p_expenditure_item_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
60 p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
61 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
62 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
63 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
64 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
65 p_project_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
66 --Start Added for devdrop2
67 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
68 --End Added for devdrop2
69 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
70 p_processed_thru_date IN Date,
71 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
72 p_tp_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
73 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
74 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
75 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
76 p_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
77 p_quantity IN PA_PLSQL_DATATYPES.Char30TabTyp,
78 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
79 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
80 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
81 p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
82 p_pa_date IN PA_PLSQL_DATATYPES.Char30TabTyp
83 default PA_PLSQL_DATATYPES.EmptyChar30Tab,
84 p_array_size IN Number,
85 p_debug_mode IN Varchar2,
86 --Start Added for devdrop2
87 p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
88 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
89 x_proj_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
90 x_proj_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
91 x_proj_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
92 x_proj_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
93 --
94 x_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
95 x_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
96 x_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
97 x_projfunc_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
98 --End Added for devdrop2
99 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
100 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
101 x_acct_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
102 x_acct_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
103 x_acct_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
104 x_acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
105 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
106 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
107 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
108 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
109 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
110 x_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
111 x_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
112 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
113 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
114 x_return_status OUT NOCOPY NUMBER ,
115 /* Bill rate Discount*/
116 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp
117 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
118 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
119 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
120 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.Char30TabTyp
121 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
122 p_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp
123 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
124 /* bug#3221791 */
125 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp
126 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
127 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp
128 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
129 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp
130 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
131 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp
132 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
133 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp
134 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
135 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp
136 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
137 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp
138 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
139 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp
140 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
141 p_raw_cost_rate IN PA_PLSQL_DATATYPES.Char30TabTyp
142 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
143 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp
144 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
145 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp
146 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
147 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp
148 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
149 p_exp_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp
150 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
151 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp
152 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
153
154 p_burden_cost IN PA_PLSQL_DATATYPES.Char30TabTyp
155 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
156 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp
157 DEFAULT PA_PLSQL_DATATYPES.EmptyIdTab,
158 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp
159 DEFAULT PA_PLSQL_DATATYPES.EmptyIdTab,
160 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp
161 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
162 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp
163 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
164 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp
165 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
166 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp
167 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
168 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp
169 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
170 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp
171 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
172 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp
173 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
174 /* Added the two parameters for Doosan rate api enhancement */
175 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
176 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
177 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
178 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
179
180 )
181 IS
182
183 l_processed_thru_date Date;
184 l_expenditure_item_date PA_PLSQL_DATATYPES.DateTabTyp;
185 l_fixed_date PA_PLSQL_DATATYPES.DateTabTyp;
186 l_denom_raw_cost_amount PA_PLSQL_DATATYPES.NumTabTyp;
187 l_denom_burdened_cost_amount PA_PLSQL_DATATYPES.NumTabTyp;
188 l_raw_revenue_amount PA_PLSQL_DATATYPES.NumTabTyp;
189 l_quantity PA_PLSQL_DATATYPES.NumTabTyp;
190 l_pa_date PA_PLSQL_DATATYPES.DateTabTyp;
191 l_denom_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
192 l_acct_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
193 l_acct_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
194 l_acct_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
195 l_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
196 l_tp_base_amount PA_PLSQL_DATATYPES.NumTabTyp;
197 l_tp_bill_markup_percentage PA_PLSQL_DATATYPES.NumTabTyp;
198 l_tp_schedule_line_percentage PA_PLSQL_DATATYPES.NumTabTyp;
199 p_prvdr_operating_unit PA_PLSQL_DATATYPES.IdTabTyp; /*Bug 2438805 */
200 l_tp_rule_percentage PA_PLSQL_DATATYPES.NumTabTyp;
201 v_denom_transfer_price Number;
202 l_return_status Number;
203
204 /*Bill rate Discount*/
205 l_labor_schdl_fixed_date PA_PLSQL_DATATYPES.DateTabTyp;
206 l_nl_task_sch_date PA_PLSQL_DATATYPES.DateTabTyp;
207 l_nl_proj_sch_date PA_PLSQL_DATATYPES.DateTabTyp;
208 l_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
209 l_burden_cost PA_PLSQL_DATATYPES.NumTabTyp;
210 l_bill_rate_multiplier PA_PLSQL_DATATYPES.NumTabTyp;
211 l_raw_cost_rate PA_PLSQL_DATATYPES.NumTabTyp;
212 l_exp_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
213
214 --Start Added for devdrop2
215 l_proj_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
216 l_proj_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
217 l_proj_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
218 --
219 l_projfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
220 l_projfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
221 l_projfunc_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
222
223 --End Added for devdrop2
224
225 BEGIN
226
227 -- Convert the data
228
229 pa_debug.set_err_stack ('Get_Transfer_Price_Wrapper');
230 pa_debug.set_process(
231 x_process => 'PLSQL',
232 x_debug_mode => p_debug_mode);
233
234 pa_debug.G_Err_Stage := 'Starting Get_Transfer_Price wrapper';
235 IF g1_debug_mode = 'Y' THEN
236 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
237 END IF;
238 pa_debug.G_Err_Stage :=
239 'Transfer Price API Start Date and Time is '
240 ||to_char(sysdate,'DD-MON-YYYY:HH24-MI-SS');
241 IF g1_debug_mode = 'Y' THEN
242 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
243 END IF;
244 pa_debug.G_Err_Stage :=
245 '--------------------------------------------------------------------------';
246 IF g1_debug_mode = 'Y' THEN
247 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
248 END IF;
249
250 For i IN 1..p_array_size
251 LOOP
252
253 l_expenditure_item_date(i) := to_date(p_expenditure_item_date(i),'YYYY/MM/DD'); /*File.Date.5*/
254 l_fixed_date(i) := to_date(p_tp_fixed_date(i),'YYYY/MM/DD'); /*file.Date.5*/
255
256 IF p_pa_date.exists(i) THEN
257 l_pa_date(i) := to_date(p_pa_date(i),'YYYY/MM/DD'); /*File.Date.5*/
258 ELSE
259 l_pa_date(i) := null;
260 END IF;
261
262 IF (not (x_denom_tp_currency_code.exists(i))) THEN
263 x_denom_tp_currency_code(i) := null;
264 END IF;
265 IF (not (x_acct_tp_rate_type.exists(i))) THEN
266 x_acct_tp_rate_type(i) := null ;
267 END IF;
268 IF (not (x_cc_markup_base_code.exists(i))) THEN
269 x_cc_markup_base_code(i) := null;
270 END IF;
271
272 IF (not (x_tp_ind_compiled_set_id.exists(i))) THEN
273 x_tp_ind_compiled_set_id (i) := null;
274 END IF;
275
276 IF (not (x_error_code.exists(i))) THEN
277 x_error_code(i) := null;
278 END IF;
279
280 IF (x_acct_tp_rate_date.exists(i)) THEN
281 l_acct_tp_rate_date(i) := to_date (x_acct_tp_rate_date(i),'YYYY/MM/DD'); /*File.Date.5*/
282 ELSE
283 l_acct_tp_rate_date(i) := null;
284 END IF;
285
286 /*BIll rate discount*/
287 l_raw_cost(i) := to_number(p_raw_cost(i));
288 l_burden_cost(i) := to_number(p_burden_cost(i));
289 l_bill_rate_multiplier(i) := to_number(p_bill_rate_multiplier(i));
290 l_raw_cost_rate(i) := to_number(p_raw_cost_rate(i));
291 l_exp_raw_cost(i) := to_number(p_exp_raw_cost(i));
292
293 IF (p_labor_schdl_fixed_date.exists(i)) THEN
294 l_labor_schdl_fixed_date(i) := to_date (p_labor_schdl_fixed_date(i),'YYYY/MM/DD');/*File.Date.5*/
295 ELSE
296 l_labor_schdl_fixed_date(i) := null;
297 END IF;
298
299 IF (l_nl_task_sch_date.exists(i)) THEN
300 l_nl_task_sch_date(i) := to_date (l_nl_task_sch_date(i),'YYYY/MM/DD');/*File.Date.5*/
301 ELSE
302 l_nl_task_sch_date(i) := null;
303 END IF;
304
305 IF (l_nl_proj_sch_date.exists(i)) THEN
306 l_nl_proj_sch_date(i) := to_date (l_nl_proj_sch_date(i),'YYYY/MM/DD');/*File.Date.5*/
307 ELSE
308 l_nl_proj_sch_date(i) := null;
309 END IF;
310
311
312 l_denom_raw_cost_amount (i) := to_number(p_denom_raw_cost_amount(i));
313
314 l_denom_burdened_cost_amount(i) :=
315 to_number((p_denom_burdened_cost_amount(i)));
316
317 l_raw_revenue_amount(i) := to_number(p_raw_revenue_amount(i));
318 l_quantity(i) := to_number(p_quantity(i));
319
320 IF x_denom_transfer_price.exists(i) THEN
321 l_denom_transfer_price(i) := to_number(x_denom_transfer_price(i));
322 ELSE
323 l_denom_transfer_price(i) := null;
324 END IF;
325
326 IF (x_acct_tp_exchange_rate.exists(i)) THEN
327 l_acct_tp_exchange_rate(i) := to_number(x_acct_tp_exchange_rate(i));
328 ELSE
329 l_acct_tp_exchange_rate(i) := null;
330 END IF;
331
332 IF (x_acct_transfer_price.exists(i)) THEN
333 l_acct_transfer_price (i) := to_number((x_acct_transfer_price(i)));
334 ELSE
335 l_acct_transfer_price(i) := null;
336 END IF;
337
338 IF (x_tp_bill_rate.exists(i)) THEN
339 l_tp_bill_rate(i) := to_number(x_tp_bill_rate(i));
340 ELSE
341 l_tp_bill_rate(i) := null;
342 END IF;
343
344 IF (x_tp_base_amount.exists(i)) THEN
345 l_tp_base_amount (i):= to_number(x_tp_base_amount(i));
346 ELSE
347 l_tp_base_amount (i):= null;
348 END IF;
349
350 IF (x_tp_bill_markup_percentage.exists(i)) THEN
351 l_tp_bill_markup_percentage(i) :=
352 to_number(x_tp_bill_markup_percentage(i));
353 ELSE
354 l_tp_bill_markup_percentage(i) := null;
355 END IF;
356
357 IF (x_tp_schedule_line_percentage.exists(i)) THEN
358 l_tp_schedule_line_percentage(i) :=
359 to_number (x_tp_schedule_line_percentage(i));
360 ELSE
361 l_tp_schedule_line_percentage(i) := null;
362 END IF;
363
364 IF (x_tp_rule_percentage.exists(i)) THEN
365 l_tp_rule_percentage (i) := to_number(x_tp_rule_percentage(i));
366 ELSE
367 l_tp_rule_percentage (i) := null;
368 END IF;
369
370 --Start Added for devdrop2
371
372 IF (x_proj_tp_rate_date.exists(i)) THEN
373 l_proj_tp_rate_date (i) := to_date(x_proj_tp_rate_date(i),'YYYY/MM/DD');/*File.Date.5*/
374 ELSE
375 l_proj_tp_rate_date (i) := null;
376 END IF;
377
378 IF (x_proj_tp_exchange_rate.exists(i)) THEN
379 l_proj_tp_exchange_rate (i) := to_number(x_proj_tp_exchange_rate(i));
380 ELSE
381 l_proj_tp_exchange_rate (i) := null;
382 END IF;
383
384 IF (x_proj_transfer_price.exists(i)) THEN
385 l_proj_transfer_price (i) := to_number(x_proj_transfer_price(i));
386 ELSE
387 l_proj_transfer_price (i) := null;
388 END IF;
389
390
391 IF (x_projfunc_tp_rate_date.exists(i)) THEN
392 l_projfunc_tp_rate_date (i) := to_date(x_projfunc_tp_rate_date(i),'YYYY/MM/DD');/*File.Date.5*/
393 ELSE
394 l_projfunc_tp_rate_date (i) := null;
395 END IF;
396
397 IF (x_projfunc_tp_exchange_rate.exists(i)) THEN
398 l_projfunc_tp_exchange_rate (i) := to_number(x_projfunc_tp_exchange_rate(i));
399 ELSE
400 l_projfunc_tp_exchange_rate (i) := null;
401 END IF;
402
403 IF (x_projfunc_transfer_price.exists(i)) THEN
404 l_projfunc_transfer_price (i) := to_number(x_projfunc_transfer_price(i));
405 ELSE
406 l_projfunc_transfer_price (i) := null;
407 END IF;
408
409 p_prvdr_operating_unit(i) :=NULL; /* Bug 2438805 */
410
411 -- Log Information :
412
413 IF g1_debug_mode = 'Y' THEN
414 pa_debug.write_file('LOG','----------------------------------------------------');
415 pa_debug.write_file('LOG','p_expenditure_item_id '||p_expenditure_item_id(i));
416 pa_debug.write_file('LOG','x_proj_tp_rate_type '||x_proj_tp_rate_type(i));
417 pa_debug.write_file('LOG','x_proj_tp_rate_date '||x_proj_tp_rate_date(i));
418 pa_debug.write_file('LOG','x_proj_tp_exchange_rate '||x_proj_tp_exchange_rate(i));
419 pa_debug.write_file('LOG','x_projfunc_tp_rate_type '||x_projfunc_tp_rate_type(i));
420 pa_debug.write_file('LOG','x_projfunc_tp_rate_date '||x_projfunc_tp_rate_date(i));
421 pa_debug.write_file('LOG','x_projfunc_tp_exchange_rate '||x_proj_tp_rate_type(i));
422 pa_debug.write_file('LOG','x_acct_tp_rate_type '||x_acct_tp_rate_type(i));
423 pa_debug.write_file('LOG','x_acct_tp_rate_date '||x_acct_tp_rate_date(i));
424 pa_debug.write_file('LOG','x_acct_tp_exchange_rate '||x_acct_tp_exchange_rate(i));
425
426 pa_debug.write_file('LOG','p_project_currency_code '||p_project_currency_code(i));
427 pa_debug.write_file('LOG','p_projfunc_currency_code '||p_projfunc_currency_code(i));
428 pa_debug.write_file('LOG','x_denom_tp_currency_code '||x_denom_tp_currency_code(i));
429 END IF;
430
431 --End Added for devdrop2
432
433 END LOOP;
434
435 pa_debug.G_Err_Stage := 'Calling actual Transfer Price API';
436 IF g1_debug_mode = 'Y' THEN
437 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
438 END IF;
439
440 Get_Transfer_Price (
441 p_module_name => p_module_name,
442 p_prvdr_organization_id => p_prvdr_organization_id,
443 p_recvr_org_id => p_recvr_org_id,
444 p_recvr_organization_id => p_recvr_organization_id,
445 p_expnd_organization_id => p_expnd_organization_id,
446 p_expenditure_item_id => p_expenditure_item_id,
447 p_expenditure_type => p_expenditure_type,
448 p_expenditure_category => p_expenditure_category,
449 p_expenditure_item_date => l_expenditure_item_date,
450 p_labor_non_labor_flag => p_labor_non_labor_flag,
451 p_system_linkage_function => p_system_linkage_function,
452 p_task_id => p_task_id,
453 p_tp_schedule_id => p_tp_schedule_id,
454 p_denom_currency_code => p_denom_currency_code,
455 p_project_currency_code => p_project_currency_code,
456 --Start Added for devdrop2
457 p_projfunc_currency_code => p_projfunc_currency_code,
458 --End Added for devdrop2
459 p_revenue_distributed_flag => p_revenue_distributed_flag,
460 p_processed_thru_date => p_processed_thru_date,
461 p_compute_flag => p_compute_flag,
462 p_tp_fixed_date => l_fixed_date,
463 p_denom_raw_cost_amount => l_denom_raw_cost_amount,
464 p_denom_burdened_cost_amount => l_denom_burdened_cost_amount,
465 p_raw_revenue_amount => l_raw_revenue_amount,
466 p_project_id => p_project_id,
467 p_quantity => l_quantity,
468 p_incurred_by_person_id => p_incurred_by_person_id,
469 p_job_id => p_job_id,
470 p_non_labor_resource => p_non_labor_resource,
471 p_nl_resource_organization_id => p_nl_resource_organization_id,
472 p_pa_date => l_pa_date,
473 p_array_size => p_array_size,
474 p_debug_mode => p_debug_mode,
475 --Start Added for devdrop2
476 p_tp_amt_type_code => p_tp_amt_type_code,
477 p_assignment_id => p_assignment_id,
478 x_proj_tp_rate_type => x_proj_tp_rate_type,
479 x_proj_tp_rate_date => l_proj_tp_rate_date,
480 x_proj_tp_exchange_rate => l_proj_tp_exchange_rate,
481 x_proj_transfer_price => l_proj_transfer_price,
482 --
483 x_projfunc_tp_rate_type => x_projfunc_tp_rate_type,
484 x_projfunc_tp_rate_date => l_projfunc_tp_rate_date,
485 x_projfunc_tp_exchange_rate => l_projfunc_tp_exchange_rate,
486 x_projfunc_transfer_price => l_projfunc_transfer_price,
487 --End Added for devdrop2
488 x_denom_tp_currency_code => x_denom_tp_currency_code,
489 x_denom_transfer_price => l_denom_transfer_price,
490 x_acct_tp_rate_type => x_acct_tp_rate_type,
491 x_acct_tp_rate_date => l_acct_tp_rate_date,
492 x_acct_tp_exchange_rate => l_acct_tp_exchange_rate,
493 x_acct_transfer_price => l_acct_transfer_price,
494 x_cc_markup_base_code => x_cc_markup_base_code,
495 x_tp_ind_compiled_set_id => x_tp_ind_compiled_set_id,
496 x_tp_bill_rate => l_tp_bill_rate,
497 x_tp_base_amount => l_tp_base_amount,
498 x_tp_bill_markup_percentage => l_tp_bill_markup_percentage,
499 x_tp_schedule_line_percentage => l_tp_schedule_line_percentage,
500 x_tp_rule_percentage => l_tp_rule_percentage,
501 x_tp_job_id =>x_tp_job_id ,
502 p_prvdr_operating_unit => p_prvdr_operating_unit, /* Bug 2438805 */
503 x_error_code => x_error_code,
504 x_return_status => l_return_status,
505 /*Bill rate discount */
506 p_dist_rule => p_dist_rule,
507 p_mcb_flag => p_mcb_flag,
508 p_bill_rate_multiplier => l_bill_rate_multiplier,
509 p_raw_cost => l_raw_cost,
510 p_labor_schdl_discnt => p_labor_schdl_discnt,
511 p_labor_schdl_fixed_date => l_labor_schdl_fixed_date,
512 p_bill_job_grp_id => p_bill_job_grp_id,
513 p_labor_sch_type => p_labor_sch_type,
514 p_project_org_id => p_project_org_id,
515 p_project_type => p_project_type,
516 p_exp_func_curr_code => p_exp_func_curr_code,
517 p_incurred_by_organz_id => p_incurred_by_organz_id,
518 p_raw_cost_rate => l_raw_cost_rate,
519 p_override_to_organz_id => p_override_to_organz_id,
520 p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id,
521 p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id,
522 p_exp_raw_cost => l_exp_raw_cost,
523 p_assignment_precedes_task => p_assignment_precedes_task,
524
525 p_burden_cost => l_burden_cost,
526 p_task_nl_bill_rate_org_id => p_task_nl_bill_rate_org_id,
527 p_proj_nl_bill_rate_org_id => p_proj_nl_bill_rate_org_id,
528 p_task_nl_std_bill_rate_sch => p_task_nl_std_bill_rate_sch,
529 p_proj_nl_std_bill_rate_sch => p_proj_nl_std_bill_rate_sch,
530 p_nl_task_sch_date => l_nl_task_sch_date,
531 p_nl_proj_sch_date => l_nl_proj_sch_date,
532 p_nl_task_sch_discount => p_nl_task_sch_discount,
533 p_nl_proj_sch_discount => p_nl_proj_sch_discount,
534 p_nl_sch_type => p_nl_sch_type,
535 p_task_nl_std_bill_rate_sch_id => p_task_nl_std_bill_rate_sch_id,
536 p_proj_nl_std_bill_rate_sch_id => p_proj_nl_std_bill_rate_sch_id,
537 p_uom_flag => p_uom_flag
538 );
539
540
541 -- Convert the number and dates to varchar2
542 pa_debug.G_Err_Stage := 'Doing the data type Conversion ';
543 IF g1_debug_mode = 'Y' THEN
544 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
545 END IF;
546 For i in 1..p_array_size
547 LOOP
548 pa_debug.G_Err_Stage := 'Processing EI: '||
549 p_expenditure_item_id(i);
550 IF g1_debug_mode = 'Y' THEN
551 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
552 END IF;
553
554 pa_debug.G_Err_Stage := 'Rejection Code: '|| x_error_code(i);
555 IF g1_debug_mode = 'Y' THEN
556 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
557 END IF;
558
559 x_acct_tp_rate_date(i) := to_char(l_acct_tp_rate_date(i),'YYYY/MM/DD');/*File.Date.5*/
560
561 x_denom_transfer_price(i) := to_char(l_denom_transfer_price(i));
562 IF g1_debug_mode = 'Y' THEN
563 pa_debug.write_file('LOG',
564 'Denom TP ='||x_denom_transfer_price(i));
565 END IF;
566
567 /** Bug# 1063619 : while converting the tp_exchange_rate
568 to char it was causing buffer overflow
569 because the number field was returning
570 value that had more than 30 characters */
571
572 x_acct_tp_exchange_rate(i):=
573 substr(to_char(l_acct_tp_exchange_rate(i)),1,30);
574 x_acct_transfer_price (i) := to_char(l_acct_transfer_price(i));
575 IF g1_debug_mode = 'Y' THEN
576 pa_debug.write_file('LOG','Acct TP ='||x_acct_transfer_price(i));
577 END IF;
578 x_tp_bill_rate (i) := to_char(l_tp_bill_rate (i));
579 x_tp_base_amount (i) := to_char(l_tp_base_amount(i));
580 x_tp_bill_markup_percentage (i) :=
581 to_char(l_tp_bill_markup_percentage(i));
582 x_tp_schedule_line_percentage(i) :=
583 to_char (l_tp_schedule_line_percentage(i));
584 x_tp_rule_percentage(i) :=
585 to_char(l_tp_rule_percentage(i));
586
587 --Start Added for devdrop2
588
589 IF g1_debug_mode = 'Y' THEN
590 pa_debug.write_file('LOG','------END OF THE PROCESS-----------------');
591 pa_debug.write_file('LOG','l_expenditure_item_id '||to_char(p_expenditure_item_id(i)));
592 pa_debug.write_file('LOG','l_expenditure_item_date '||to_char(l_expenditure_item_date(i)));
593 pa_debug.write_file('LOG','x_denom_tp_currency_code '||x_denom_tp_currency_code(i));
594 pa_debug.write_file('LOG','l_proj_tp_rate_date '||to_char(l_proj_tp_rate_date(i)));
595 pa_debug.write_file('LOG','l_proj_tp_exchange_rate '||to_char(l_proj_tp_exchange_rate(i)));
596 pa_debug.write_file('LOG','l_proj_transfer_price '||to_char(l_proj_transfer_price(i)));
597 pa_debug.write_file('LOG','l_projfunc_tp_rate_date '||to_char(l_projfunc_tp_rate_date(i)));
598 pa_debug.write_file('LOG','l_projfunc_tp_exchange_rate '||to_char(l_projfunc_tp_exchange_rate(i)));
599 pa_debug.write_file('LOG','l_projfunc_transfer_price '||to_char(l_projfunc_transfer_price(i)));
600 pa_debug.write_file('LOG','-----------------------------------------');
601 END IF;
602
603 x_proj_tp_rate_date(i) := to_char(l_proj_tp_rate_date(i),'YYYY/MM/DD');
604 x_proj_tp_exchange_rate(i) := substr(to_char(l_proj_tp_exchange_rate(i)),1,30);
605 x_proj_transfer_price(i) := l_proj_transfer_price(i);
606 --
607 x_projfunc_tp_rate_date(i) := to_char(l_projfunc_tp_rate_date(i),'YYYY/MM/DD');
608 x_projfunc_tp_exchange_rate(i) := substr(to_char(l_projfunc_tp_exchange_rate(i)),1,30);
609 x_projfunc_transfer_price(i) := to_char(l_projfunc_transfer_price(i));
610
611 --End Added for devdrop2
612
613 pa_debug.G_Err_Stage := 'Completing the loop';
614 IF g1_debug_mode = 'Y' THEN
615 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
616 END IF;
617 END LOOP;
618
619 x_return_status := l_return_status;
620
621 pa_debug.G_Err_Stage :=
622 'Transfer Price API End Date and Time is '
623 ||to_char(sysdate,'DD-MON-YYYY:HH24-MI-SS');
624 IF g1_debug_mode = 'Y' THEN
625 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
626 END IF;
627
628 END Get_Transfer_Price;
629
630
631 --------------------------------------------------------------------------------
632 -- Procedure
633 -- Get_Transfer_Price
634 -- Purpose
635 -- Called from Borrowed and Lent Process and IC Billing
636 -- It calculates Transfer Price for Provider Cross Charge Process
637
638 PROCEDURE Get_Transfer_Price
639 (
640 p_module_name IN VARCHAR2,
641 p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
642 p_recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
643 p_recvr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
644 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
645 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
646 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
647 p_expenditure_category IN PA_PLSQL_DATATYPES.Char30TabTyp,
648 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
649 p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
650 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
651 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
652 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
653 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
654 p_project_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
655 --Start Added for devdrop2
656 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
657 --End Added for devdrop2
658 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
659 p_processed_thru_date IN Date,
660 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
661 p_tp_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
662 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
663 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
664 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
665 p_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
666 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
667 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
668 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
669 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
670 p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
671 p_pa_date IN PA_PLSQL_DATATYPES.DateTabTyp
672 default PA_PLSQL_DATATYPES.EmptyDateTab,
673 p_array_size IN Number,
674 p_debug_mode IN Varchar2,
675 --Start Added for devdrop2
676 p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
677 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
678 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp /** Added for Org Forecasting **/
679 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab ,
680 --
681 x_proj_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
682 x_proj_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp,
683 x_proj_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
684 x_proj_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
685 --
686 x_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
687 x_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp,
688 x_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
689 x_projfunc_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
690 --End Added for devdrop2
691 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
692 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
693 x_acct_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
694 x_acct_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp,
695 x_acct_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
696 x_acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
697 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
698 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
699 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
700 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
701 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
702 x_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
703 x_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
704 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
705 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
706 x_return_status OUT NOCOPY NUMBER ,/*FIle.sql.39*/
707 /* Bill rate Discount*/
708 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp
709 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
710 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
711 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
712 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.NumTabTyp
713 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
714 p_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp
715 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
716 /* bug#3221791 */
717 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp
718 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
719 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp
720 DEFAULT PA_PLSQL_DATATYPES.EmptyDateTab,
721 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp
722 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
723 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp
724 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
725 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp
726 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
727 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp
728 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
729 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp
730 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
731 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp
732 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
733 p_raw_cost_rate IN PA_PLSQL_DATATYPES.NumTabTyp
734 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
735 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp
736 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
737 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp
738 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
739 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp
740 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
741 p_exp_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp
742 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
743 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp
744 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
745
746 p_burden_cost IN PA_PLSQL_DATATYPES.NumTabTyp
747 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
748 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp
749 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab,
750 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp
751 DEFAULT PA_PLSQL_DATATYPES.EmptyIdTab,
752 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp
753 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
754 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp
755 DEFAULT PA_PLSQL_DATATYPES.EmptyChar30Tab,
756 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp
757 DEFAULT PA_PLSQL_DATATYPES.EmptyDateTab,
758 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp
759 DEFAULT PA_PLSQL_DATATYPES.EmptyDateTab,
760 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp
761 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
762 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp
763 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
764 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp
765 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab,
766 /* Added the two parameters for Doosan rate api enhancement */
767 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
768 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
769 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
770 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
771 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
772 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
773 )
774
775 IS
776
777 l_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
778 l_tp_base_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
779 l_tp_schedule_line_id PA_PLSQL_DATATYPES.IdTabTyp;
780 l_tp_rule_id PA_PLSQL_DATATYPES.IdTabTyp;
781 l_error_code VARCHAR2(30); /* bug#3115422 changed type from PA_PLSQL_DATATYPES.Char30TabTyp to VARCHAR2(30) */
782
783 l_error_message VARCHAR2(2000);
784 l_rate_date Date;
785 l_new_rate_date Date;
786 l_status Number;
787 l_rate_type Varchar2(30);
788 l_exchange_rate Number;
789 l_denominator Number;
790 l_numerator Number;
791 l_denom_tp_currency_code Varchar2(15);
792 l_denom_transfer_price Number;
793 l_acct_transfer_price Number;
794 l_tp_bill_rate Number;
795 l_tp_bill_markup_percentage Number;
796
797 --Start Added for devdrop2
798
799 l_tp_rate_ovrd Number ;
800 l_tp_currency_ovrd Varchar2(30);
801 l_tp_calc_base_code_ovrd Varchar2(15);
802 l_tp_percent_applied_ovrd Number;
803
804 l_project_bil_rate_date_code Varchar2(30);
805 l_project_bil_rate_type Varchar2(30);
806 l_project_bil_rate_date Date;
807 l_project_bil_exchange_rate Number;
808 l_project_transfer_price Number;
809
810 l_projfunc_bil_rate_date_code Varchar2(30);
811 l_projfunc_bil_rate_type Varchar2(30);
812 l_projfunc_bil_rate_date Date;
813 l_projfunc_bil_exchange_rate Number;
814 l_projfunc_transfer_price Number;
815
816 l_prev_project_id Number;
817
818 l_stage Number;
819 l_transaction_type Varchar2(20) := Null; /** Added for Org Forecasting **/
820 l_multi_currency_billing_flag Varchar2(1);
821
822 --End Added for devdrop2
823
824 /* bug#3115422 start */
825 l_exp_func_curr_code VARCHAR2(30);
826 l_sl_function NUMBER;
827
828 l_bill_rate NUMBER;
829 l_adjust_bill_rate NUMBER;--4038485
830 l_markup_percentage NUMBER;
831 l_rev_currency_code VARCHAR2(30);
832 l_return_status varchar2(240);
833 l_msg_count NUMBER;
834 l_msg_data VARCHAR2(240);
835 l_raw_cost_rate NUMBER;
836
837 l_project_currency_code varchar2(50) := null;
838 l_project_raw_cost number := null;
839
840 l_tp_base_Curr_code1 varchar2(50);
841
842 l_rate_type1 VARCHAR2(50) := NULL; -- For Bug 5276842
843
844 cursor PROJ_VALUES (p_expenditure_item_id IN NUMBER) IS
845 select project_raw_cost,
846 project_currency_code
847 from pa_expenditure_items_all where expenditure_item_id=p_expenditure_item_id;
848 /* bug#3115422 end */
849
850 unexpected_result exception;
851
852 BEGIN
853 pa_debug.Set_err_stack ('Get_Transfer_Price');
854
855 pa_debug.G_Err_Stage := 'Starting Get_Transfer_Price';
856 IF g1_debug_mode = 'Y' THEN
857 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
858 END IF;
859
860 /** Added for Org Forecasting **/
861
862 If p_module_name = 'FORECAST' then
863 l_transaction_type := 'FORECAST';
864 Else
865 l_transaction_type := 'ACTUAL';
866 End If;
867
868 G_Array_Size := p_array_size;
869 G_processed_thru_date := p_processed_thru_date;
870 G_Calling_module := p_module_name;
871
872 l_compute_flag := p_compute_flag;
873
874 Init_who_cols;
875
876 Get_Provider_Attributes(p_prvdr_operating_unit,
877 x_error_code); /** Parameter Added for Org Forecasting **/
878
879 If p_module_name <> 'FORECAST' Then /** Added for Org Forecasting **/
880
881 -- Given a org_id , get provider's legal_entity_id and business_group_id
882 Get_legal_entity (G_prvdr_org_id,G_prvdr_legal_entity_id);
883
884 -- If Callling Module is Forecast then moved this call in
885 -- Get_Schedule_Line to Set G_prvdr_legal_entity_id for every
886 -- provider_org_id separately
887
888 End If;
889
890 -- Print all the global variables
891 IF g1_debug_mode = 'Y' THEN
892 pa_debug.write_file('LOG',' Array Size = '||to_char(G_array_size));
893 pa_debug.write_file('LOG',' Processed thru date = '
894 ||to_char(G_processed_thru_date));
895 pa_debug.write_file('LOG',' Provider Org ID = '
896 ||to_char(G_prvdr_org_id));
897 pa_debug.write_file('LOG',' Provider"s Legal Entity ID = '
898 ||to_char(G_prvdr_legal_entity_id));
899 pa_debug.write_file('LOG',' Buiseness Group ID = '
900 ||to_char(G_bg_id));
901 pa_debug.write_file('LOG',' Default Curr Conversion Rate Type = '
902 ||G_cc_default_rate_type);
903 pa_debug.write_file('LOG',' Default Curr Conversion Rate Date Code = '
904 ||G_cc_default_rate_date_code);
905 pa_debug.write_file('LOG',' Default Currency Code = '
906 ||G_acct_currency_code );
907 END IF;
908
909 -- Validate input parameters and identify transactions passes for only
910 -- currency conversion and not for recalculation.
911 Validate_Array
912 (
913 p_prvdr_operating_unit, /** New Parameter Added for Org Forecasting **/
914 p_tp_schedule_id,
915 x_denom_tp_currency_code,
916 G_acct_currency_code,
917 x_denom_transfer_price,
918 x_acct_tp_rate_type,
919 x_acct_tp_rate_date,
920 x_acct_transfer_price,
921 x_acct_tp_exchange_rate,
922 l_compute_flag,
923 x_error_code
924 );
925
926 -- Call Pre Client extension to calculate transfer price
927 pa_debug.G_Err_Stage := 'Calling Pre-Client Extension............';
928 IF g1_debug_mode = 'Y' THEN
929 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
930 END IF;
931 For i in 1 .. G_Array_Size
932 LOOP
933
934 If G_Calling_module = 'FORECAST' Then /** Added for Org Forecasting **/
935 Set_Global_Variables (p_prvdr_operating_unit(i));
936 End If;
937
938 IF (l_compute_flag (i) = 'Y' and x_error_code(i) is null ) THEN
939 pa_debug.G_Err_Stage := 'Processing Expenditure ID: '||
940 to_char(p_expenditure_item_id(i));
941 IF g1_debug_mode = 'Y' THEN
942 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
943 END IF;
944
945 PA_CC_TP_CLIENT_EXTN.Determine_Transfer_Price
946 (
947 L_transaction_type,
948 G_prvdr_org_id,
949 p_prvdr_organization_id(i),
950 p_recvr_org_id(i),
951 p_recvr_organization_id(i),
952 p_expnd_organization_id(i),
953 p_expenditure_item_id(i),
954 p_expenditure_type(i),
955 p_system_linkage_function(i),
956 p_task_id(i),
957 p_project_id(i),
958 p_quantity(i),
959 p_incurred_by_person_id(i),
960 l_denom_tp_currency_code,
961 l_denom_transfer_price,
962 l_tp_bill_rate,
963 l_tp_bill_markup_percentage,
964 l_error_message,
965 l_status
966 );
967
968 -- Validate if the amount is calculated by extension
969 -- Also, mark the transactions where transfer price is calculated by
970 -- Pre-client extension.
971 IF (l_status = 0) THEN
972
973 IF (l_denom_transfer_price IS NOT NULL) THEN
974 IF g1_debug_mode = 'Y' THEN
975 pa_debug.write_file('LOG','Pre-client Bill Rate = '
976 ||to_char(l_tp_bill_rate));
977 pa_debug.write_file('LOG','Pre-client markup = '
978 ||to_char(l_tp_bill_markup_percentage));
979 END IF;
980
981 IF (l_denom_tp_currency_code IS NULL) THEN
982 x_error_code(i) := 'PA_CC_TP_PREC_CURR_NULL';
983
984 ELSE
985
986 -- Check one of Bill Rate and Markup is available
987 -- Both should not be null neither both could be not null
988
989 IF ( l_tp_bill_rate IS NULL AND
990 l_tp_bill_markup_percentage IS NULL) THEN
991
992 x_error_code(i) := 'PA_CC_TP_PREC_BILL_MRKUP_NULL';
993 ElSIF ( l_tp_bill_rate IS NOT NULL AND
994 l_tp_bill_markup_percentage IS NOT NULL) THEN
995
996 x_error_code(i) :=
997 'PA_CC_TP_PREC_BILL_MRKUP_VALUE';
998 ELSE
999 --Pre-client extension is successful
1000 l_compute_flag(i) := 'P';
1001 x_denom_transfer_price(i) := l_denom_transfer_price;
1002 x_denom_tp_currency_code(i):= l_denom_tp_currency_code;
1003 x_tp_bill_rate(i) := l_tp_bill_rate;
1004 x_tp_bill_markup_percentage(i) :=
1005 l_tp_bill_markup_percentage;
1006 IF g1_debug_mode = 'Y' THEN
1007 pa_debug.write_file('LOG',
1008 'Transfer Price calculated by Pre-Client extension');
1009 pa_debug.write_file('LOG','Transfer Price = '||
1010 to_char(x_denom_transfer_price(i)));
1011 pa_debug.write_file('LOG','Transfer Price currency = '||
1012 x_denom_tp_currency_code(i));
1013 END IF;
1014
1015 END IF;
1016
1017 END IF;
1018
1019 END IF;/** x_denom_transfer_price(i) IS NOT NULL **/
1020
1021 ELSIF (l_status > 0 ) THEN
1022 -- Application error occurred with client extension
1023 --x_error_code(i) := 'PA_CC_TP_PREC_APPS_ERROR'; -- Commented for 2661949
1024 x_error_code(i) := l_error_message; -- Added for 2661949
1025 pa_debug.G_Err_Stage :=
1026 Substr( 'Application error from Pre-Client extension'||
1027 'while processing expenditure_item :'||
1028 to_char(p_expenditure_item_id(i))||'Error Message :'
1029 || l_error_message,1,2000);
1030 IF g1_debug_mode = 'Y' THEN
1031 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1032 END IF;
1033
1034 ELSIF (l_status < 0) THEN
1035 x_error_code(i) := l_error_message; -- Added for 2661949
1036 pa_debug.G_Err_Stage :=
1037 Substr( 'Unexpected error from Pre-Client extension'||
1038 'while processing expenditure_item :'||
1039 to_char(p_expenditure_item_id(i))||'Error Message :'
1040 || l_error_message,1,2000);
1041
1042 IF g1_debug_mode = 'Y' THEN
1043 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1044 END IF;
1045 raise unexpected_result;
1046
1047 END IF; /** l_status **/
1048
1049 --Start Added for devdrop2
1050
1051 -- Add the assignment override logic here
1052 -- and set the compute_flag = 'P'
1053
1054
1055 IF ( ( l_compute_flag(i) = 'Y' ) AND
1056 ( p_assignment_id(i) is not null ) AND
1057 ( nvl(p_assignment_precedes_task(i),'N')='Y') ) then /* added for bug#3142053 */
1058
1059 BEGIN
1060
1061 Select
1062 tp_rate_override,
1063 tp_currency_override,
1064 tp_calc_base_code_override,
1065 tp_percent_applied_override
1066 into
1067 l_tp_rate_ovrd,
1068 l_tp_currency_ovrd,
1069 l_tp_calc_base_code_ovrd,
1070 l_tp_percent_applied_ovrd
1071 from
1072 pa_project_assignments
1073 where
1074 assignment_id = p_assignment_id(i)
1075 and p_expenditure_item_date(i) between
1076 start_date and end_date ;
1077
1078 if ( l_tp_rate_ovrd is not null ) then
1079 if(p_system_linkage_function(i) in ('ST','OT')) then /* Added the condition for bug 6310246 */
1080 x_denom_transfer_price(i) := p_quantity(i) * nvl(l_tp_rate_ovrd,0);
1081 x_denom_tp_currency_code(i):= l_tp_currency_ovrd;
1082 x_tp_bill_rate(i) := nvl(l_tp_rate_ovrd,0);
1083 l_compute_flag(i) := 'A';
1084 End if;
1085
1086 elsif ( l_tp_calc_base_code_ovrd is not null ) then
1087
1088 l_compute_flag(i) := 'A';
1089
1090 if ( l_tp_calc_base_code_ovrd = 'R' ) then
1091
1092 /* bug#3115422 start */
1093 if p_raw_revenue_amount(i) is null then
1094
1095 OPEN PROJ_VALUES(p_expenditure_item_id(i));
1096 FETCH proj_values into
1097 l_project_raw_cost,
1098 l_project_currency_code;
1099 ClOSE PROJ_VALUES;
1100 /* Added for bill rate disount and transfer price revenue*/
1101 IF (p_system_linkage_function(i) in ('ST','OT')) then
1102 IF g1_debug_mode = 'Y' THEN
1103 pa_debug.write_file('LOG','in assignment override within Assignment_Rev_Amt');
1104 END IF;
1105 pa_revenue.Assignment_Rev_Amt(
1106 p_project_id => P_project_id(i)
1107 ,p_task_id => P_task_id(i)
1108 ,p_item_date => P_expenditure_item_date(i)
1109 ,p_item_id => p_assignment_id(i)
1110 ,p_bill_rate_multiplier => p_bill_rate_multiplier(i)
1111 ,p_quantity => p_quantity(i)
1112 ,p_person_id => p_incurred_by_person_id(i)
1113 ,p_raw_cost => p_raw_cost(i)
1114 /* bug#3221791 added to_number */
1115 ,p_labor_schdl_discnt => to_number(p_labor_schdl_discnt(i))
1116 ,p_labor_bill_rate_org_id => NULL
1117 ,p_labor_std_bill_rate_schdl => NULL
1118 ,p_labor_schdl_fixed_date => p_labor_schdl_fixed_date(i)
1119 ,p_bill_job_grp_id => p_bill_job_grp_id(i)
1120 ,p_labor_sch_type => p_labor_sch_type(i)
1121 ,p_project_org_id => p_project_org_id(i)
1122 ,p_project_type => p_project_type(i)
1123 ,p_expenditure_type => p_expenditure_type(i)
1124 ,p_exp_func_curr_code => p_exp_func_curr_code(i)
1125 ,p_incurred_by_organz_id => p_incurred_by_organz_id(i)
1126 ,p_raw_cost_rate => p_raw_cost_rate(i)
1127 ,p_override_to_organz_id => p_override_to_organz_id(i)
1128 ,p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id(i)
1129 ,p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id(i)
1130 ,p_resource_job_id => NULL
1131 ,p_exp_raw_cost => p_exp_raw_cost(i)
1132 ,p_expenditure_org_id => p_expnd_organization_id(i)
1133 ,p_projfunc_currency_code => p_projfunc_currency_code(i)
1134 ,p_assignment_precedes_task => p_assignment_precedes_task(i)
1135 ,p_sys_linkage_function => p_system_linkage_function(i)
1136 ,x_bill_rate => l_bill_rate
1137 ,x_raw_revenue => x_tp_base_amount(i)
1138 ,x_txn_currency_code => l_tp_base_Curr_code1
1139 ,x_rev_currency_code => l_rev_currency_code
1140 ,x_markup_percentage => l_markup_percentage
1141 ,x_return_status => l_return_status
1142 ,x_msg_count => l_msg_count
1143 ,x_msg_data => l_msg_data
1144 ,p_mcb_flag => p_mcb_flag(i)
1145 ,p_denom_raw_cost => p_denom_raw_cost_amount(i)
1146 ,p_denom_curr_code => p_denom_currency_code(i)
1147 ,p_called_process => 'PA'
1148 ,p_project_raw_cost => l_project_raw_cost
1149 ,p_project_currency_code => l_project_currency_code
1150 ,x_adjusted_bill_rate => l_adjust_bill_rate); --4038485
1151 IF g1_debug_mode = 'Y' THEN
1152 pa_debug.write_file('LOG','in assignment override completed Assignment_Rev_Amt');
1153 END IF;
1154
1155 end if; /* end of system linkage */
1156 IF g1_debug_mode = 'Y' THEN
1157 pa_debug.write_file('LOG','x_base revenue amount' || x_tp_base_amount(i) ||'code : ' ||l_tp_base_curr_code1);
1158 END IF;
1159 IF l_msg_data is NULL then
1160 IF x_tp_base_amount(i) is NULL THEN
1161 l_error_code := 'PA_CC_TP_REV_AMT_NULL';
1162 END IF;
1163 ELSE
1164 l_error_code := l_msg_data;
1165 END IF;
1166 /* Added for bill rate disount and transfer price revenue*/
1167
1168 x_denom_transfer_price(i) := x_tp_base_amount(i) * (nvl(l_tp_percent_applied_ovrd,100)/100);
1169 x_denom_tp_currency_code(i) := l_tp_base_Curr_code1;
1170 else
1171 x_denom_transfer_price(i) := p_raw_revenue_amount(i) * (nvl(l_tp_percent_applied_ovrd,100)/100);
1172 x_denom_tp_currency_code(i) := p_projfunc_currency_code(i);
1173 end if; /* end for p_raw_revenue_amount */
1174
1175 /* bug#3115422 end */
1176
1177 elsif ( l_tp_calc_base_code_ovrd = 'C' ) then
1178
1179 x_denom_transfer_price(i) := p_denom_raw_cost_amount(i)* (nvl(l_tp_percent_applied_ovrd,100)/100);
1180 x_denom_tp_currency_code(i) := p_denom_currency_code(i);
1181
1182 elsif ( l_tp_calc_base_code_ovrd = 'B' ) then
1183
1184 x_denom_transfer_price(i) :=
1185 nvl(p_denom_burdened_cost_amount(i),0) * (nvl(l_tp_percent_applied_ovrd,100)/100);
1186 x_denom_tp_currency_code(i) := p_denom_currency_code(i);
1187
1188 end if; /* asg_tp_calc_base_code_ovrd = 'B' */
1189
1190 end if; /* asg_tp_calc_base_code_ovrd is not null */
1191
1192 EXCEPTION
1193 when NO_DATA_FOUND then
1194 null;
1195 when others then
1196 raise;
1197 END;
1198
1199 end if; /* l_compute_flag(i) = 'Y' */
1200
1201 --End Added for devdrop2
1202
1203 pa_debug.G_Err_Stage := 'Completed Processing Expenditure ID: '||
1204 to_char(p_expenditure_item_id(i));
1205 IF g1_debug_mode = 'Y' THEN
1206 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1207 END IF;
1208
1209 END IF; /**l_compute_flag (i) = 'Y' and x_error_code(i) IS NULL**/
1210
1211 END LOOP;
1212
1213
1214 pa_debug.G_Err_Stage := 'Completes Pre-Client Extension............';
1215 IF g1_debug_mode = 'Y' THEN
1216 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1217 END IF;
1218
1219 -- Get the schedule_line_id and the right rule_id associated with it.
1220
1221 Get_Schedule_Line(
1222 p_expenditure_item_id,
1223 p_expenditure_item_date, /* Added for Bug 3118101 */
1224 p_prvdr_organization_id,
1225 p_recvr_org_id,
1226 p_recvr_organization_id,
1227 p_labor_non_labor_flag,
1228 p_tp_schedule_id,
1229 l_compute_flag,
1230 -- Start Added for devdrop2
1231 p_tp_amt_type_code,
1232 -- End Added for devdrop2
1233 p_prvdr_operating_unit, /** Added for Org Forecasting **/
1234 x_error_code,
1235 l_tp_schedule_line_id,
1236 x_tp_schedule_line_percentage,
1237 l_tp_rule_id
1238 );
1239
1240 -- Get the transfer price amount using calc_method_code and also the
1241 -- other OUT parameters
1242
1243 Get_Transfer_Price_Amount
1244 (
1245 l_tp_rule_id,
1246 p_expenditure_item_id,
1247 p_expenditure_type,
1248 p_expenditure_item_date,
1249 p_expnd_organization_id,
1250 p_project_id,
1251 p_task_id,
1252 p_denom_currency_code,
1253 p_projfunc_currency_code,
1254 p_revenue_distributed_flag,
1255 l_compute_flag,
1256 p_denom_raw_cost_amount,
1257 p_denom_burdened_cost_amount,
1258 p_raw_revenue_amount,
1259 p_quantity,
1260 p_incurred_by_person_id,
1261 p_job_id,
1262 p_non_labor_resource,
1263 p_nl_resource_organization_id,
1264 p_system_linkage_function,
1265 x_tp_schedule_line_percentage,
1266 p_tp_fixed_date,
1267 x_denom_tp_currency_code,
1268 x_denom_transfer_price,
1269 x_cc_markup_base_code,
1270 x_tp_ind_compiled_set_id,
1271 x_tp_bill_rate,
1272 l_tp_base_curr_code,
1273 x_tp_base_amount,
1274 x_tp_bill_markup_percentage,
1275 x_tp_rule_percentage,
1276 x_tp_job_id ,
1277 x_error_code,
1278 /* Bill rate Discount*/
1279 p_dist_rule,
1280 p_mcb_flag,
1281 p_bill_rate_multiplier,
1282 p_raw_cost,
1283 p_labor_schdl_discnt,
1284 p_labor_schdl_fixed_date,
1285 p_bill_job_grp_id,
1286 p_labor_sch_type,
1287 p_project_org_id,
1288 p_project_type,
1289 p_exp_func_curr_code,
1290 p_incurred_by_organz_id,
1291 p_raw_cost_rate,
1292 p_override_to_organz_id,
1293 p_emp_bill_rate_schedule_id,
1294 p_job_bill_rate_schedule_id,
1295 p_exp_raw_cost,
1296 p_assignment_precedes_task,
1297 p_assignment_id ,
1298
1299 p_burden_cost,
1300 p_task_nl_bill_rate_org_id,
1301 p_proj_nl_bill_rate_org_id,
1302 p_task_nl_std_bill_rate_sch,
1303 p_proj_nl_std_bill_rate_sch,
1304 p_nl_task_sch_date,
1305 p_nl_proj_sch_date,
1306 p_nl_task_sch_discount,
1307 p_nl_proj_sch_discount,
1308 p_nl_sch_type,
1309 /*Added for Doosan rate api enhancement */
1310 p_task_nl_std_bill_rate_sch_id,
1311 p_proj_nl_std_bill_rate_sch_id,
1312 p_uom_flag);
1313
1314
1315 -- Call Post Client extension to calculate transfer price
1316
1317
1318 For i in 1 .. G_Array_Size
1319 LOOP
1320 IF (l_compute_flag (i) = 'Y' and x_error_code(i) IS NULL) THEN
1321
1322 If G_Calling_module = 'FORECAST' Then /** Added for Org Forecasting **/
1323 Set_Global_Variables (p_prvdr_operating_unit(i));
1324 End If;
1325
1326 -- Don't consider transactions identified for adjustment and
1327 -- also transfer price already calculated by Pre-client extension.
1328 pa_debug.G_Err_Stage := 'Calling Post-client extension';
1329 IF g1_debug_mode = 'Y' THEN
1330 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1331 END IF;
1332
1333 PA_CC_TP_CLIENT_EXTN.Override_Transfer_Price
1334 (
1335 L_Transaction_type,
1336 G_prvdr_org_id,
1337 p_prvdr_organization_id(i),
1338 p_recvr_org_id(i),
1339 p_recvr_organization_id(i),
1340 p_expnd_organization_id(i),
1341 p_expenditure_item_id(i),
1342 p_expenditure_type(i),
1343 p_system_linkage_function(i),
1344 p_task_id(i),
1345 p_project_id(i),
1346 p_quantity(i),
1347 p_incurred_by_person_id(i),
1348 l_tp_base_curr_code(i),
1349 x_tp_base_amount(i),
1350 x_denom_tp_currency_code(i),
1351 x_denom_transfer_price(i),
1352 l_denom_tp_currency_code,
1353 l_denom_transfer_price,
1354 l_tp_bill_rate,
1355 l_tp_bill_markup_percentage,
1356 l_error_message,
1357 l_status
1358 );
1359 pa_debug.G_Err_Stage := 'After Call to Post-client extension';
1360 IF g1_debug_mode = 'Y' THEN
1361 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1362 END IF;
1363
1364 -- Validate if the amount is calculated by extension
1365 -- Also, mark the transactions where transfer price is calculated by
1366 -- Post-client extension.
1367 IF (l_status = 0) THEN
1368 IF (l_denom_transfer_price IS NOT NULL) THEN
1369
1370 IF (NVL(l_denom_transfer_price,-99) <>
1371 NVL(x_denom_transfer_price(i),-99) ) THEN
1372
1373 IF (l_denom_tp_currency_code IS NULL) THEN
1374
1375 x_error_code(i) := 'PA_CC_TP_POSC_CURR_NULL';
1376
1377 ELSE
1378 -- Check one of Bill Rate and Markup is available
1379 -- Both should not be null neither both could be not null
1380
1381 IF ( l_tp_bill_rate IS NULL) AND
1382 (l_tp_bill_markup_percentage IS NULL) THEN
1383
1384 x_error_code(i) := 'PA_CC_TP_POSC_BILL_MRKUP_NULL';
1385 ELSIF ( l_tp_bill_rate IS NOT NULL) AND
1386 (l_tp_bill_markup_percentage IS NOT NULL) THEN
1387
1388 x_error_code(i) :=
1389 'PA_CC_TP_POSC_BILL_MRKUP_VALUE';
1390 ELSE
1391 --Post-client extension is successful
1392 l_compute_flag(i) := 'O';
1393 x_denom_transfer_price(i) := l_denom_transfer_price;
1394 x_tp_bill_rate(i) := l_tp_bill_rate;
1395 x_tp_bill_markup_percentage(i) :=
1396 l_tp_bill_markup_percentage;
1397 -- Bug 5263275 Assigned the from clent extn
1398 x_denom_tp_currency_code(i) := l_denom_tp_currency_code;
1399
1400 END IF;
1401
1402 END IF;/** x_denom_transfer_price(i) IS NOT NULL **/
1403
1404 END IF;
1405
1406 ELSE
1407 -- Check if IN transfer price was not null and OUT is made null
1408 -- then error out
1409
1410 IF (x_denom_transfer_price(i) IS NOT NULL) THEN
1411
1412 x_error_code (i) := 'PA_CC_TP_POSC_TP_NULL';
1413
1414 END IF;
1415
1416 END IF;
1417
1418 ELSIF (l_status > 0 ) THEN
1419 -- Application error occurred with client extension
1420 --x_error_code(i) := 'PA_CC_TP_POSC_APPS_ERROR';-- Commented for 2661949
1421 x_error_code(i) := l_error_message;-- Added for 2661949
1422 pa_debug.G_Err_Stage :=
1423 Substr( 'Application error from Post-Client extension'||
1424 'while processing expenditure_item :'||
1425 to_char(p_expenditure_item_id(i))||'Error Message :'
1426 || l_error_message,1,2000);
1427 IF g1_debug_mode = 'Y' THEN
1428 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1); -- Changed for 2661949
1429 END IF;
1430
1431 ELSIF (l_status < 0) THEN
1432 x_error_code(i) := l_error_message; -- Added for 2661949
1433 pa_debug.G_Err_Stage :=
1434 Substr( 'Unexpected error from Post-Client extension'||
1435 'while processing expenditure_item :'||
1436 to_char(p_expenditure_item_id(i))||'Error Message :'
1437 || l_error_message,1,2000);
1438 IF g1_debug_mode = 'Y' THEN
1439 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1440 END IF;
1441 raise unexpected_result;
1442
1443 END IF; /** l_status **/
1444
1445 END IF; /**l_compute_flag (i) = 'Y' and x_error_code(i) IS NULL**/
1446
1447 END LOOP;
1448
1449
1450 -- Currency Conversion
1451 pa_debug.G_Err_Stage := 'Do Currency Conversion';
1452 IF g1_debug_mode = 'Y' THEN
1453 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1454 END IF;
1455
1456 --Start devdrops2 change
1457
1458 l_prev_project_id := -1;
1459
1460 --End devdrops2 change
1461
1462 For i in 1 .. G_Array_Size
1463 LOOP
1464
1465
1466 -- Removed the p_compute_flag(i) = 'Y' check
1467
1468 IF ( x_error_code(i) IS NULL) THEN
1469
1470 If G_Calling_module = 'FORECAST' Then /** Added for Org Forecasting **/
1471 Set_Global_Variables (p_prvdr_operating_unit(i));
1472 End If;
1473
1474 IF (x_denom_transfer_price(i) IS NOT NULL) THEN
1475 IF (x_denom_tp_currency_code(i) IS NOT NULL) THEN
1476
1477 IF (l_compute_flag(i) = 'C') THEN
1478 pa_debug.G_Err_Stage := 'Converting adjustment cases';
1479 IF g1_debug_mode = 'Y' THEN
1480 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1481 END IF;
1482 -- Convert the adjustment cases
1483 IF (x_acct_tp_rate_type(i) = 'User' AND
1484 x_acct_tp_exchange_rate(i) IS NOT NULL) THEN
1485 -- Do the conversion using the rate
1486 x_acct_transfer_price(i):=
1487 pa_currency.round_currency_amt(x_denom_transfer_price(i)
1488 *x_acct_tp_exchange_rate(i));
1489 x_projfunc_transfer_price(i):=x_acct_transfer_price(i); /* 9704878 */
1490 ELSE
1491 IF (x_acct_tp_rate_type(i) IS NOT NULL
1492 AND x_acct_tp_rate_date(i) IS NOT NULL) THEN
1493
1494 begin -- Bug 7423839
1495 IF g1_debug_mode = 'Y' THEN
1496 pa_debug.write_file('LOG', '1:i= '||i|| ' f_curr '||x_denom_tp_currency_code(i)||' t_curr '||G_acct_currency_code);
1497 pa_debug.write_file('LOG', 'r_date '||x_acct_tp_rate_date(i)|| ' r_type '||x_acct_tp_rate_type(i)||' amt '||x_denom_transfer_price(i));
1498 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1499 pa_debug.write_file('LOG', 'rate '||x_acct_tp_exchange_rate(i));
1500 END IF;
1501 -- Use type and date to convert
1502 PA_MULTI_CURRENCY.Convert_Amount(
1503 p_from_currency => x_denom_tp_currency_code(i),
1504 p_to_currency => G_acct_currency_code ,
1505 p_conversion_date => x_acct_tp_rate_date(i),
1506 p_conversion_type => x_acct_tp_rate_type(i),
1507 p_amount => x_denom_transfer_price(i),
1508 p_user_validate_flag => 'Y',
1509 p_handle_exception_flag => 'Y',
1510 p_converted_amount => l_acct_transfer_price,
1511 p_denominator => l_denominator,
1512 p_numerator => l_numerator,
1513 p_rate => x_acct_tp_exchange_rate(i),
1514 x_status => l_error_message
1515 );
1516 exception
1517 when others then
1518 IF g1_debug_mode = 'Y' THEN
1519 pa_debug.write_file('LOG', 'r_date '||x_acct_tp_rate_date(i)|| ' r_type '||x_acct_tp_rate_type(i));
1520 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1521 pa_debug.write_file('LOG', 'rate '||x_acct_tp_exchange_rate(i)|| ' err msg '||substr(l_error_message,1,300));
1522 END IF;
1523 if l_error_message IS NULL THEN
1524 l_error_message := 'OTHERS';
1525 end if;
1526 end;
1527 IF l_error_message is NOT NULL THEN
1528 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1529 ELSE
1530 x_acct_transfer_price (i) := l_acct_transfer_price;
1531 /* 9704878 */
1532 x_projfunc_transfer_price (i) := l_acct_transfer_price;
1533 x_projfunc_tp_exchange_rate (i) := x_acct_tp_exchange_rate(i);
1534 x_projfunc_tp_rate_type (i) := x_acct_tp_rate_type(i);
1535 x_projfunc_tp_rate_date (i) := x_acct_tp_rate_date(i);
1536 END IF;
1537 ELSE
1538 x_error_code(i) := 'PA_CC_TP_RATE_TYPE_DATE_NULL';
1539 END IF;
1540
1541
1542 END IF;
1543
1544 ELSE /** else (l_compute_flag(i) = 'C'**/
1545
1546 -- Consider the cases where transfer price calculated by
1547 -- Pre-client extension or APIs or Post -client extension
1548 IF (G_cc_default_rate_date_code = 'E') THEN
1549 l_rate_date := p_expenditure_item_date(i);
1550
1551 ELSIF (G_cc_default_rate_date_code = 'P') THEN
1552 IF (p_pa_date.exists(i)and p_pa_date(i) IS NOT NULL) THEN
1553 l_rate_date := p_pa_date(i);
1554 ELSE
1555 -- Calculate pa_Date
1556 l_rate_date := pa_utils2.get_pa_date(
1557 p_ei_date => p_expenditure_item_date(i),
1558 p_gl_date => sysdate,
1559 p_org_id => G_prvdr_org_id /* p_prvdr_organization_id(i) modified for bug 3535443 */ /**CBGA**/
1560 );
1561 END IF;
1562 END IF;
1563
1564 -- Call extension to Override currency attributes
1565
1566 PA_MULTI_CURR_CLIENT_EXTN.Override_Curr_Conv_Attributes
1567 (
1568 p_project_id => p_project_id(i),
1569 p_task_id => p_task_id(i),
1570 p_transaction_class => 'Transfer Price',
1571 p_expenditure_item_id => p_expenditure_item_id(i),
1572 p_expenditure_type_class => p_system_linkage_function(i),
1573 p_expenditure_type => p_expenditure_type(i),
1574 p_expenditure_category => p_expenditure_category(i),
1575 p_from_currency_code => x_denom_tp_currency_code(i),
1576 p_to_currency_code => G_Acct_currency_code,
1577 p_conversion_type => G_cc_default_rate_type,
1578 p_conversion_date => l_rate_date,
1579 x_rate_type => l_rate_type,
1580 x_rate_date => l_new_rate_date,
1581 x_exchange_rate => l_exchange_rate,
1582 x_error_message => l_error_message,
1583 x_status => l_status
1584 );
1585
1586
1587 IF (l_status = 0) THEN
1588 -- success
1589 l_error_message := null;
1590
1591 -- Added the below code for Bug 5276842
1592 IF l_rate_type <> 'User' and l_rate_type IS NOT NULL THEN
1593 BEGIN
1594 SELECT conversion_type
1595 INTO l_rate_type1
1596 FROM gl_daily_conversion_types
1597 WHERE (user_conversion_type = l_rate_type
1598 OR conversion_type = l_rate_type);
1599 l_rate_type := l_rate_type1;
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND THEN
1602 l_error_message := 'PA_EXCH_RATE_TYPE_INVALID';
1603 END;
1604 END IF;
1605 -- End of changes for bug 5276842
1606
1607 IF (l_rate_type = 'User' and l_exchange_rate IS NOT NULL)
1608 THEN
1609 -- Do the conversion using the rate
1610 l_acct_transfer_price :=
1611 pa_currency.round_currency_amt(
1612 x_denom_transfer_price(i)*l_exchange_rate);
1613
1614 ELSIF (l_rate_type IS NOT NULL and l_new_rate_date IS
1615 NOT NULL and l_error_message IS NULL) THEN
1616 -- Added the condition of l_error_message for Bug 5276842
1617 -- Use type and date to convert
1618 l_rate_date := l_new_rate_date;
1619 begin --Bug 7423839
1620 IF g1_debug_mode = 'Y' THEN
1621 pa_debug.write_file('LOG', '2:i= '||i|| ' f_curr '||x_denom_tp_currency_code(i)||' t_curr '||G_acct_currency_code);
1622 pa_debug.write_file('LOG', 'r_date '||l_rate_date|| ' r_type '||l_rate_type||' amt '||x_denom_transfer_price(i));
1623 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1624 pa_debug.write_file('LOG', 'rate '||l_exchange_rate);
1625 END IF;
1626 PA_MULTI_CURRENCY.Convert_Amount(
1627 p_from_currency => x_denom_tp_currency_code(i),
1628 p_to_currency => G_acct_currency_code ,
1629 p_conversion_date => l_rate_date,
1630 p_conversion_type => l_rate_type,
1631 p_amount => x_denom_transfer_price(i),
1632 p_user_validate_flag => 'Y',
1633 p_handle_exception_flag => 'Y',
1634 p_converted_amount => l_acct_transfer_price,
1635 p_denominator => l_denominator,
1636 p_numerator => l_numerator,
1637 p_rate => l_exchange_rate,
1638 x_status => l_error_message
1639 );
1640 exception
1641 when others then
1642 IF g1_debug_mode = 'Y' THEN
1643 pa_debug.write_file('LOG', 'r_date '||l_rate_date|| ' r_type '||l_rate_type);
1644 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1645 pa_debug.write_file('LOG', 'rate '||l_exchange_rate|| ' err msg '||substr(l_error_message,1,300));
1646 END IF;
1647 if l_error_message IS NULL THEN
1648 l_error_message := 'OTHERS';
1649 end if;
1650 end;
1651 ELSIF (l_error_message IS NULL) THEN
1652 -- Added the condition of l_error_message for Bug 5276842
1653 -- Use default rate_type and rate_date to convert
1654 pa_debug.G_Err_Stage := 'Using default rate type';
1655 IF g1_debug_mode = 'Y' THEN
1656 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1657 END IF;
1658 l_rate_type := G_cc_default_rate_type;
1659
1660 begin --Bug 7423839
1661 IF g1_debug_mode = 'Y' THEN
1662 pa_debug.write_file('LOG', '3:i= '||i|| ' f_curr '||x_denom_tp_currency_code(i)||' t_curr '||G_acct_currency_code);
1663 pa_debug.write_file('LOG', 'r_date '||l_rate_date|| ' r_type '||l_rate_type||' amt '||x_denom_transfer_price(i));
1664 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1665 pa_debug.write_file('LOG', 'rate '||l_exchange_rate);
1666 END IF;
1667 PA_MULTI_CURRENCY.Convert_Amount(
1668 p_from_currency => x_denom_tp_currency_code(i),
1669 p_to_currency => G_acct_currency_code ,
1670 p_conversion_date => l_rate_date,
1671 p_conversion_type => l_rate_type,
1672 p_amount => x_denom_transfer_price(i),
1673 p_user_validate_flag => 'Y',
1674 p_handle_exception_flag => 'Y',
1675 p_converted_amount => l_acct_transfer_price,
1676 p_denominator => l_denominator,
1677 p_numerator => l_numerator,
1678 p_rate => l_exchange_rate,
1679 x_status => l_error_message
1680 );
1681 exception
1682 when others then
1683 IF g1_debug_mode = 'Y' THEN
1684 pa_debug.write_file('LOG', 'r_date '||l_rate_date|| ' r_type '||l_rate_type);
1685 pa_debug.write_file('LOG', 'xfer_price '||l_acct_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1686 pa_debug.write_file('LOG', 'rate '||l_exchange_rate|| ' err msg '||substr(l_error_message,1,300));
1687 END IF;
1688 if l_error_message IS NULL THEN
1689 l_error_message := 'OTHERS';
1690 end if;
1691 end;
1692
1693 END IF;
1694
1695 IF l_error_message IS NOT NULL THEN
1696
1697 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1698 IF g1_debug_mode = 'Y' THEN
1699 pa_debug.write_file('LOG',
1700 Substr (l_error_message,1,2000),1);
1701 END IF;
1702 ELSE
1703 x_acct_transfer_price (i) := l_acct_transfer_price;
1704 x_acct_tp_rate_type(i) := l_rate_type;
1705 x_acct_tp_rate_date(i) := l_rate_date;
1706 x_acct_tp_exchange_rate(i) := l_exchange_rate;
1707
1708 -- bug 7489360
1709 x_proj_tp_rate_type(i) := l_rate_type;
1710 x_proj_tp_rate_date(i) := l_rate_date;
1711 x_projfunc_tp_rate_type(i) := l_rate_type;
1712 x_projfunc_tp_rate_date(i) := l_rate_date;
1713
1714 pa_debug.G_Err_Stage := 'Rate Type ='||l_rate_type;
1715 IF g1_debug_mode = 'Y' THEN
1716 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1717 END IF;
1718 END IF;
1719
1720 ELSIF (l_status > 0) THEN
1721 -- Application error occurred in extension to override
1722 -- currency conversion attributes
1723 -- x_error_code(i) := 'PA_CC_TP_OVERIDE_APP_ERROR';
1724 x_error_code(i) := l_error_message; -- Added for 2661949
1725 pa_debug.G_Err_Stage :=
1726 Substr( 'Application error from Currency conversion
1727 override extension'||
1728 'while processing expenditure_item :'||
1729 to_char(p_expenditure_item_id(i))||'Error Message :'
1730 || l_error_message,1,2000);
1731 IF g1_debug_mode = 'Y' THEN
1732 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1733 END IF;
1734 ELSIF (l_status < 0) THEN
1735
1736 -- Unexpected error occurred in extension to override
1737 -- currency conversion attributes
1738 --x_error_code(i) := 'PA_CC_TP_OVERIDE_ORA_ERROR';
1739 x_error_code(i) := l_error_message; -- Added for 2661949
1740 pa_debug.G_Err_Stage :=
1741 Substr( 'Unexpected error from Currency conversion
1742 override extension'||
1743 'while processing expenditure_item :'||
1744 to_char(p_expenditure_item_id(i))||'Error Message :'
1745 || l_error_message,1,2000);
1746 IF g1_debug_mode = 'Y' THEN
1747 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1748 END IF;
1749 raise unexpected_result;
1750 ELSE
1751 x_error_code (i) := 'PA_CC_TP_INVALID_OVERIDE_STATUS';
1752 END IF;
1753
1754 l_multi_currency_billing_flag := 'N';
1755
1756 /* IF ( ( p_tp_amt_type_code(i) is not NULL )
1757 and ( p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' ) ) then commented for bug6712230*/
1758 If NVL(p_tp_amt_type_code(i),'COST_REVENUE') is not NULL THEN /* Added NVL for bug 6891120 -- bug6712230 */
1759 --Start devdrop2 changes
1760 -- Converting the denorm to projfunc and proj for amt_type 'REVENUE_TRANSFER'
1761
1762 SELECT project_bil_rate_date_code,
1763 project_bil_rate_type,
1764 project_bil_rate_date,
1765 project_bil_exchange_rate,
1766 projfunc_bil_rate_date_code,
1767 projfunc_bil_rate_type,
1768 projfunc_bil_rate_date,
1769 nvl(multi_currency_billing_flag,'N'),
1770 projfunc_bil_exchange_rate
1771 INTO l_project_bil_rate_date_code,
1772 l_project_bil_rate_type,
1773 l_project_bil_rate_date,
1774 l_project_bil_exchange_rate,
1775 l_projfunc_bil_rate_date_code,
1776 l_projfunc_bil_rate_type,
1777 l_projfunc_bil_rate_date,
1778 l_multi_currency_billing_flag,
1779 l_projfunc_bil_exchange_rate
1780 FROM pa_projects_all
1781 WHERE project_id = p_project_id(i);
1782 if p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' THEN /*bug6389559*/
1783 IF ( l_multi_currency_billing_flag = 'Y' ) THEN
1784
1785 IF g1_debug_mode = 'Y' THEN
1786 pa_debug.write_file('LOG','l_project_bil_rate_date_code: '||l_project_bil_rate_date_code);
1787 pa_debug.write_file('LOG','l_project_bil_rate_type: '||l_project_bil_rate_type);
1788 pa_debug.write_file('LOG','l_project_bil_rate_date: '||l_project_bil_rate_date);
1789 pa_debug.write_file('LOG','l_project_bil_exchange_rate: '||l_project_bil_exchange_rate);
1790
1791 pa_debug.write_file('LOG','l_projfunc_bil_rate_date_code: '||l_projfunc_bil_rate_date_code);
1792 pa_debug.write_file('LOG','l_projfunc_bil_rate_type: '||l_projfunc_bil_rate_type);
1793 pa_debug.write_file('LOG','l_projfunc_bil_rate_date: '||l_projfunc_bil_rate_date);
1794 pa_debug.write_file('LOG','l_projfunc_bil_exchange_rate: '||l_projfunc_bil_exchange_rate);
1795
1796 pa_debug.write_file('LOG','pa_date : '||p_pa_date(i));
1797 pa_debug.write_file('LOG','x_denom_tp_currency_code : '||x_denom_tp_currency_code(i));
1798 pa_debug.write_file('LOG','p_project_currency_code : '||p_project_currency_code(i));
1799 END IF;
1800 --
1801 --Converting into Project currency attributes
1802 -- for tp_amt_type_code = 'REVENUE_TRANSFER'
1803
1804 IF (l_project_bil_rate_type = 'User') THEN
1805
1806 IF (l_project_bil_exchange_rate IS NOT NULL) THEN
1807
1808 l_project_transfer_price:=
1809 pa_currency.round_trans_currency_amt
1810 (x_denom_transfer_price(i)*l_project_bil_exchange_rate,
1811 p_project_currency_code(i));
1812 ELSE
1813 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1814 IF g1_debug_mode = 'Y' THEN
1815 pa_debug.write_file('LOG','ERROR NO USER RATE ');
1816 END IF;
1817
1818 END IF;
1819 else
1820
1821 IF (l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
1822 l_project_bil_rate_date := G_processed_thru_date;
1823 END IF;
1824
1825 IF g1_debug_mode = 'Y' THEN
1826 pa_debug.write_file('LOG','BEFORE CONVERT: l_project_bil_rate_type: '||l_project_bil_rate_type);
1827 pa_debug.write_file('LOG','BEFORE CONVERT: l_project_bil_rate_date: '||l_project_bil_rate_date);
1828 END IF;
1829 begin --Bug 7423839
1830 IF g1_debug_mode = 'Y' THEN
1831 pa_debug.write_file('LOG', '4:i= '||i|| ' f_curr '||x_denom_tp_currency_code(i)||' t_curr '||p_project_currency_code(i));
1832 pa_debug.write_file('LOG', 'r_date '||l_project_bil_rate_date|| ' r_type '||l_project_bil_rate_type||' amt '||x_denom_transfer_price(i));
1833 pa_debug.write_file('LOG', 'xfer_price '||l_project_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1834 pa_debug.write_file('LOG', 'rate '||l_project_bil_exchange_rate);
1835 END IF;
1836 PA_MULTI_CURRENCY.Convert_Amount(
1837 p_from_currency => x_denom_tp_currency_code(i),
1838 p_to_currency => p_project_currency_code(i),
1839 p_conversion_date => l_project_bil_rate_date,
1840 p_conversion_type => l_project_bil_rate_type,
1841 p_amount => x_denom_transfer_price(i),
1842 p_user_validate_flag => 'Y',
1843 p_handle_exception_flag => 'Y',
1844 p_converted_amount => l_project_transfer_price,
1845 p_denominator => l_denominator,
1846 p_numerator => l_numerator,
1847 p_rate => l_project_bil_exchange_rate,
1848 x_status => l_error_message
1849 );
1850 exception
1851 when others then
1852 IF g1_debug_mode = 'Y' THEN
1853 pa_debug.write_file('LOG', 'r_date '||l_project_bil_rate_date|| ' r_type '||l_project_bil_rate_type);
1854 pa_debug.write_file('LOG', 'xfer_price '||l_project_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1855 pa_debug.write_file('LOG', 'rate '||l_project_bil_exchange_rate|| ' err msg '||substr(l_error_message,1,300));
1856 END IF;
1857 if l_error_message IS NULL THEN
1858 l_error_message := 'OTHERS';
1859 end if;
1860 end;
1861
1862
1863 IF g1_debug_mode = 'Y' THEN
1864 pa_debug.write_file('LOG','l_error_message : '||l_error_message);
1865 pa_debug.write_file('LOG','AFTER CONVERT: l_project_bil_rate_type: '||l_project_bil_rate_type);
1866 pa_debug.write_file('LOG','AFTER CONVERT: l_project_bil_rate_date: '||l_project_bil_rate_date);
1867 END IF;
1868
1869 IF l_error_message IS NOT NULL THEN
1870
1871 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1872 IF g1_debug_mode = 'Y' THEN
1873 pa_debug.write_file('LOG',
1874 Substr (l_error_message,1,2000));
1875 END IF;
1876 ELSE
1877 x_proj_transfer_price (i) := l_project_transfer_price;
1878 x_proj_tp_rate_type(i) := l_project_bil_rate_type;
1879 x_proj_tp_rate_date(i) := l_project_bil_rate_date;
1880 x_proj_tp_exchange_rate(i) := l_project_bil_exchange_rate;
1881 pa_debug.G_Err_Stage := 'Rate Type ='||l_project_bil_rate_type;
1882 IF g1_debug_mode = 'Y' THEN
1883 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1884 END IF;
1885 END IF;
1886
1887
1888 END IF; /* esle l_project_bil_rate_type = 'User'*/
1889
1890
1891 --
1892 --Converting into Project functional currency attributes
1893 -- for tp_amt_type_code = 'REVENUE_TRANSFER'
1894 --
1895
1896 IF (l_projfunc_bil_rate_type = 'User' AND
1897 l_projfunc_bil_exchange_rate IS NOT NULL) THEN
1898
1899 l_projfunc_transfer_price:=
1900 pa_currency.round_trans_currency_amt
1901 (x_denom_transfer_price(i)*l_projfunc_bil_exchange_rate,
1902 p_projfunc_currency_code(i));
1903 else
1904
1905 IF (l_projfunc_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
1906 l_projfunc_bil_rate_date := G_processed_thru_date;
1907 END IF;
1908
1909 begin -- Bug 7423839
1910 IF g1_debug_mode = 'Y' THEN
1911 pa_debug.write_file('LOG', '5:i= '||i|| ' f_curr '||x_denom_tp_currency_code(i)||' t_curr '||p_projfunc_currency_code(i));
1912 pa_debug.write_file('LOG', 'r_date '||l_projfunc_bil_rate_date|| ' r_type '||l_projfunc_bil_rate_type||' amt '||x_denom_transfer_price(i));
1913 pa_debug.write_file('LOG', 'xfer_price '||l_projfunc_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1914 pa_debug.write_file('LOG', 'rate '||l_projfunc_bil_exchange_rate);
1915 END IF;
1916 PA_MULTI_CURRENCY.Convert_Amount(
1917 p_from_currency => x_denom_tp_currency_code(i),
1918 p_to_currency => p_projfunc_currency_code(i),
1919 p_conversion_date => l_projfunc_bil_rate_date,
1920 p_conversion_type => l_projfunc_bil_rate_type,
1921 p_amount => x_denom_transfer_price(i),
1922 p_user_validate_flag => 'Y',
1923 p_handle_exception_flag => 'Y',
1924 p_converted_amount => l_projfunc_transfer_price,
1925 p_denominator => l_denominator,
1926 p_numerator => l_numerator,
1927 p_rate => l_projfunc_bil_exchange_rate,
1928 x_status => l_error_message
1929 );
1930 exception
1931 when others then
1932 IF g1_debug_mode = 'Y' THEN
1933 pa_debug.write_file('LOG', 'r_date '||l_projfunc_bil_rate_date|| ' r_type '||l_projfunc_bil_rate_type);
1934 pa_debug.write_file('LOG', 'xfer_price '||l_projfunc_transfer_price|| ' denom '||l_denominator||' num '||l_numerator);
1935 pa_debug.write_file('LOG', 'rate '||l_projfunc_bil_exchange_rate|| ' err msg '||substr(l_error_message,1,300));
1936 END IF;
1937 if l_error_message IS NULL THEN
1938 l_error_message := 'OTHERS';
1939 end if;
1940 end;
1941
1942 IF l_error_message IS NOT NULL THEN
1943
1944 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1945 IF g1_debug_mode = 'Y' THEN
1946 pa_debug.write_file('LOG',
1947 Substr (l_error_message,1,2000));
1948 END IF;
1949 ELSE
1950 x_projfunc_transfer_price (i) := l_projfunc_transfer_price;
1951 x_projfunc_tp_rate_type(i) := l_projfunc_bil_rate_type;
1952 x_projfunc_tp_rate_date(i) := l_projfunc_bil_rate_date;
1953 x_projfunc_tp_exchange_rate(i) := l_projfunc_bil_exchange_rate;
1954 pa_debug.G_Err_Stage := 'Rate Type ='||l_projfunc_bil_rate_type;
1955 IF g1_debug_mode = 'Y' THEN
1956 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1957 END IF;
1958 END IF;
1959
1960
1961 END IF; /* esle l_projfunc_bil_rate_type = 'User'*/
1962
1963 IF g1_debug_mode = 'Y' THEN
1964 pa_debug.write_file('LOG','AFTER CONVERSION');
1965 pa_debug.write_file('LOG','l_project_bil_rate_date_code: '||l_project_bil_rate_date_code);
1966 pa_debug.write_file('LOG','l_project_bil_rate_type: '||l_project_bil_rate_type);
1967 pa_debug.write_file('LOG','l_project_bil_rate_date: '||l_project_bil_rate_date);
1968 pa_debug.write_file('LOG','l_project_bil_exchange_rate: '||l_project_bil_exchange_rate);
1969
1970 pa_debug.write_file('LOG','l_projfunc_bil_rate_date_code: '||l_projfunc_bil_rate_date_code);
1971 pa_debug.write_file('LOG','l_projfunc_bil_rate_type: '||l_projfunc_bil_rate_type);
1972 pa_debug.write_file('LOG','l_projfunc_bil_rate_date: '||l_projfunc_bil_rate_date);
1973 pa_debug.write_file('LOG','l_projfunc_bil_exchange_rate: '||l_projfunc_bil_exchange_rate);
1974 END IF;
1975
1976 END IF ; /** l_multi_currency_billing_flag = 'Y' **/
1977 END IF; /** p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' **/
1978
1979 --End devdrop2 changes
1980
1981
1982
1983 --Start devdrop2 changes
1984
1985 /** Currency conversion for p_tp_amt_type_code in ( 'COST_TRANSFER','COST_REVENUE') **/
1986
1987 IF ( (nvl(p_tp_amt_type_code(i),'COST_REVENUE') in ( 'COST_TRANSFER','COST_REVENUE') )
1988 OR
1989 (l_multi_currency_billing_flag = 'N' ) ) then
1990
1991 /* call the cost currency conversion api */
1992
1993 pa_multi_currency_txn.get_currency_amounts(
1994 P_project_id => p_project_id(i),
1995 P_exp_org_id => p_prvdr_operating_unit(i),
1996 p_Calling_module => p_module_name,
1997 P_task_id => p_task_id(i),
1998 P_EI_date => p_expenditure_item_date(i),
1999 P_denom_raw_cost => x_denom_transfer_price(i),
2000 P_denom_curr_code => x_denom_tp_currency_code(i),
2001 P_acct_curr_code => G_acct_currency_code,
2002 P_accounted_flag => 'N',
2003 P_acct_rate_date => x_acct_tp_rate_date(i),
2004 P_acct_rate_type => x_acct_tp_rate_type(i),
2005 P_acct_exch_rate => x_acct_tp_exchange_rate(i),
2006 P_acct_raw_cost => x_acct_transfer_price(i),
2007 P_project_curr_code => p_project_currency_code(i),
2008 P_project_rate_type => x_proj_tp_rate_type(i),
2009 P_project_rate_date => x_proj_tp_rate_date(i),
2010 P_project_exch_rate => x_proj_tp_exchange_rate(i),
2011 P_project_raw_cost => x_proj_transfer_price(i),
2012 P_projfunc_curr_code => p_projfunc_currency_code(i),
2013 P_projfunc_cost_rate_type => x_projfunc_tp_rate_type(i),
2014 P_projfunc_cost_rate_date => x_projfunc_tp_rate_date(i),
2015 P_projfunc_cost_exch_rate => x_projfunc_tp_exchange_rate(i),
2016 P_projfunc_raw_cost => x_projfunc_transfer_price(i),
2017 P_system_linkage => p_system_linkage_function(i),
2018 P_status => x_error_code(i),
2019 P_stage => l_stage );
2020
2021 END IF; /*else p_tp_amt_type_code(i) in ( 'COST_TRANSFER','COST_REVENUE') */
2022 END IF; /* if p_tp_amt_type_code is NOT NULL - bug6389559*/
2023 --End devdrop2 changes
2024
2025 END IF; /** (l_compute_flag(i) = 'C'**/
2026
2027 ELSE
2028 x_error_code(i) := 'PA_CC_TP_CONV_DENOM_CURR_NULL';
2029 END IF; /** x_denom_tp_currency_code(i) is not null **/
2030 END IF; /** x_denom_transfer_price(i) IS NOT NULL **/
2031
2032 END IF; /** x_error_code(i) IS NULL **/
2033
2034 END LOOP;
2035
2036 pa_debug.G_Err_Stage := 'Completed Get_Transfer_Price with success';
2037 IF g1_debug_mode = 'Y' THEN
2038 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2039 END IF;
2040 x_return_status := 0;
2041 pa_debug.Reset_err_stack;
2042
2043 EXCEPTION
2044 when unexpected_result THEN
2045 IF g1_debug_mode = 'Y' THEN
2046 pa_debug.write_file('LOG','Unexpected Error in Transfer Price API' );
2047 pa_debug.write_file('LOG',pa_debug.G_Err_Stack);
2048 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2049 END IF;
2050
2051 when others then
2052
2053 IF g1_debug_mode = 'Y' THEN
2054 pa_debug.write_file('LOG','Unexpected Error in Transfer Price API' );
2055 pa_debug.write_file('LOG',pa_debug.G_Err_Stack);
2056 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2057 END IF;
2058
2059 raise;
2060
2061 END GET_transfer_price;
2062
2063 -------------------------------------------------------------------------------
2064 PROCEDURE Init_who_cols
2065 IS
2066 BEGIN
2067
2068 pa_debug.Set_err_stack ('Init_who_cols');
2069 pa_debug.G_Err_Stage := 'Inside Init_who_cols';
2070 IF g1_debug_mode = 'Y' THEN
2071 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2072 END IF;
2073
2074 G_created_by := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
2075 G_last_update_login := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1);
2076 G_last_updated_by := G_created_by;
2077 G_creation_date := G_sysdate;
2078 G_last_update_date := G_sysdate ;
2079
2080 pa_debug.Reset_err_stack;
2081
2082 END Init_who_cols ;
2083
2084 -------------------------------------------------------------------------------
2085
2086 PROCEDURE Get_Legal_Entity (
2087 p_org_id IN NUMBER,
2088 x_legal_entity_id OUT NOCOPY NUMBER /*File.sql.39*/
2089 )
2090 IS
2091
2092 Cursor c_legal_entity
2093 is
2094 SELECT org_information2 legal_entity_id
2095 FROM hr_organization_information
2096 WHERE organization_id = p_org_id
2097 AND org_information_context = 'Operating Unit Information';
2098
2099 /* Commented for bug 4920063. Added Above statement for LE
2100 select legal_entity_id
2101 from pa_implementations_all
2102 where org_id = p_org_id;*/
2103
2104 /*l_legal_entity pa_implementations_all.legal_entity_id%TYPE; Commented for bug 2920063*/
2105
2106 l_legal_entity hr_organization_information.org_information2%TYPE;
2107
2108 BEGIN
2109
2110 pa_debug.Set_err_stack ('Get_Legal_Entity');
2111 pa_debug.G_Err_Stage := 'Get Legal Entity of Org'||to_char(p_org_id);
2112 IF g1_debug_mode = 'Y' THEN
2113 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2114 END IF;
2115
2116 open c_legal_entity;
2117 fetch c_legal_entity into l_legal_entity;
2118 close c_legal_entity;
2119
2120 IF l_legal_entity IS NOT NULL THEN
2121 x_legal_entity_id := to_number(l_legal_entity);
2122 -- conversion required as legal_entity_id is stored as varchar2
2123 -- in hr_operating_units
2124 END IF;
2125
2126 pa_debug.Reset_err_stack;
2127
2128 EXCEPTION
2129
2130 WHEN OTHERS THEN
2131 raise;
2132
2133 END Get_Legal_Entity;
2134 -------------------------------------------------------------------------------
2135 PROCEDURE Set_Global_Variables /** Added for Org Forecasting **/
2136 ( p_org_id IN NUMBER)
2137 IS
2138
2139 BEGIN
2140
2141 --SS-ORG-CHANGE
2142 --Added nvl to G_prvdr_org_id
2143
2144 IF nvl(G_prvdr_org_id,-1) <> p_org_id and g_Calling_Module = 'FORECAST' Then
2145
2146 pa_debug.Set_err_stack ('Set_Global_Variables ');
2147 pa_debug.G_Err_Stage := 'Set_Global_Variables '||to_char(p_org_id);
2148 IF g1_debug_mode = 'Y' THEN
2149 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2150 END IF;
2151
2152 G_prvdr_org_id := G_prvdr_org_id_Tab(p_org_id);
2153 G_bg_id := G_bg_id_Tab(p_org_id);
2154 G_acct_currency_code := G_acct_currency_code_Tab(p_org_id);
2155 G_cc_default_rate_type := G_cc_default_rate_type_Tab(p_org_id);
2156 G_cc_default_rate_date_code := G_cc_def_rate_date_code_Tab(p_org_id);
2157 G_exp_org_struct_ver_id := G_exp_org_struct_ver_Tab(p_org_id);
2158
2159 END IF;
2160
2161 EXCEPTION
2162
2163 WHEN OTHERS THEN
2164 raise;
2165 END Set_Global_Variables;
2166
2167 -------------------------------------------------------------------------------
2168 PROCEDURE Get_business_group (
2169 p_org_id IN NUMBER,
2170 x_business_group_id OUT NOCOPY NUMBER /*File.sql.39*/
2171 )
2172 IS
2173
2174 Cursor c_business_group
2175 is
2176 select business_group_id
2177 from hr_operating_units
2178 where organization_id = p_org_id;
2179
2180 l_business_group hr_operating_units.business_group_id%TYPE;
2181
2182 BEGIN
2183 pa_debug.Set_err_stack ('Get_Business_group');
2184 pa_debug.G_Err_Stage := 'Get bisiness_group of Org'||to_char(p_org_id);
2185 IF g1_debug_mode = 'Y' THEN
2186 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2187 END IF;
2188
2189 open c_business_group;
2190 fetch c_business_group into l_business_group;
2191 close c_business_group;
2192
2193 IF l_business_group IS NOT NULL THEN
2194 x_business_group_id := l_business_group;
2195 END IF;
2196
2197 pa_debug.Reset_err_stack;
2198
2199 EXCEPTION
2200
2201 WHEN OTHERS THEN
2202 raise;
2203
2204 END Get_business_group;
2205
2206 -------------------------------------------------------
2207 PROCEDURE Get_Provider_Attributes (
2208 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2209 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab,
2210 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp )
2211 IS
2212 cursor c_ou_attributes
2213 is
2214 select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
2215 EXP_ORG_STRUCTURE_VERSION_ID
2216 from pa_implementations;
2217
2218 cursor c_ou_attributes_fcst (l_org_id number)
2219 is
2220 select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
2221 EXP_ORG_STRUCTURE_VERSION_ID
2222 from pa_implementations_all
2223 where org_id = l_org_id;
2224
2225 cursor c_ou_curr_code_fcst (l_org_id Number)
2226 is
2227 SELECT FC.Currency_Code
2228 FROM FND_CURRENCIES FC,
2229 GL_SETS_OF_BOOKS GB,
2230 PA_IMPLEMENTATIONS_ALL IMP
2231 WHERE FC.Currency_Code = DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
2232 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID
2233 AND IMP.Org_Id = l_org_id;
2234
2235 l_provider_org_id pa_implementations.org_id%type;
2236
2237 BEGIN
2238 pa_debug.Set_err_stack ('Get_Provider_Attributes');
2239 pa_debug.G_Err_Stage := 'Inside Get_Provider_Attributes';
2240 IF g1_debug_mode = 'Y' THEN
2241 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2242 END IF;
2243
2244 If G_Calling_module = 'FORECAST' Then
2245 Begin
2246 For i in 1 .. G_array_size Loop
2247 pa_debug.G_Err_Stage := 'i: '||to_char(i)||' error code : '||x_error_code(i);
2248 IF g1_debug_mode = 'Y' THEN
2249 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2250 END IF;
2251
2252
2253 l_provider_org_id := nvl(p_prvdr_operating_unit(i),-1);
2254 If G_prvdr_org_id_tab.Exists(l_provider_org_id)
2255 Then Null;
2256 IF g1_debug_mode = 'Y' THEN
2257 pa_debug.write_file('LOG','i :'||to_char(i)||' Exist ');
2258 END IF;
2259 Else
2260 open c_ou_attributes_fcst (l_provider_org_id);
2261 fetch c_ou_attributes_fcst
2262 into G_prvdr_org_id_tab(l_provider_org_id),
2263 G_bg_id_tab(l_provider_org_id),
2264 G_cc_default_rate_type_tab(l_provider_org_id),
2265 G_cc_def_rate_date_code_tab(l_provider_org_id),
2266 G_exp_org_struct_ver_tab(l_provider_org_id);
2267 close c_ou_attributes_fcst;
2268
2269 open c_ou_curr_code_fcst (l_provider_org_id);
2270
2271 fetch c_ou_curr_code_fcst
2272 into G_acct_currency_code_Tab (l_provider_org_id);
2273 Close c_ou_curr_code_fcst ;
2274
2275
2276 End If; /** G_prvdr_org_id_tab.Exists **/
2277
2278 pa_debug.G_Err_Stage := 'I : '||to_char(i)||' p_prvdr_operating_unit(i) '||to_char(l_provider_org_id)||
2279 ' G_prvdr_org_id_tab(l_provider_org_id): '||to_char(G_prvdr_org_id_tab(l_provider_org_id));
2280
2281 IF g1_debug_mode = 'Y' THEN
2282 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2283 END IF;
2284
2285 End loop;
2286 End;
2287 Else /** Since Calling Module is Not FORECAST Code need not be independant of OU **/
2288 open c_ou_attributes;
2289
2290 --DevDrop2 Changes
2291 --Added G_exp_org_struct_ver_id variable.
2292
2293 fetch c_ou_attributes
2294 into G_prvdr_org_id,G_bg_id,G_cc_default_rate_type,
2295 G_cc_default_rate_date_code,
2296 G_exp_org_struct_ver_id;
2297
2298 close c_ou_attributes;
2299
2300 -- Get accounting currency code of the provider
2301 G_acct_currency_code := PA_MULTI_CURRENCY.Get_Acct_Currency_Code;
2302 End If; /** End p_module_name = 'FORECAST' **/
2303 pa_debug.Reset_err_stack;
2304
2305 EXCEPTION
2306 when others then
2307 raise;
2308
2309 END Get_Provider_Attributes;
2310 --------------------------------------------------------------------------------
2311 PROCEDURE Validate_Array
2312 (
2313 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2314 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab ,
2315 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2316 p_denom_tp_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
2317 p_acct_currency_code IN varchar2 ,
2318 p_denom_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp,
2319 p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
2320 p_acct_tp_rate_date IN PA_PLSQL_DATATYPES.DateTabTyp,
2321 p_acct_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp,
2322 p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
2323 x_compute_flag IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
2324 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
2325 )
2326 IS
2327 l_acct_currency_code FND_CURRENCIES.CURRENCY_CODE%TYPE ; /** Added for Org Forecasting **/
2328 BEGIN
2329 pa_debug.Set_err_stack ('Validate_Array');
2330 pa_debug.G_Err_Stage := 'Starting Validate_Array';
2331 IF g1_debug_mode = 'Y' THEN
2332 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2333 END IF;
2334
2335 l_acct_currency_code := p_acct_currency_code; /** Added for Org Forecasting **/
2336
2337 For i in 1 .. G_array_Size
2338 Loop
2339 pa_debug.G_Err_Stage := 'Start Loop';
2340 -- Consider records with error_code is null and compute_flag = 'Y'
2341 IF (x_compute_flag(i) = 'Y' ) THEN
2342 IF ( x_error_code(i) IS NULL ) THEN
2343 -- Check if transfer price is already available -
2344 pa_debug.G_Err_Stage := 'Flagged as error-free';
2345 IF g1_debug_mode = 'Y' THEN
2346 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2347 END IF;
2348
2349 IF G_Calling_Module = 'FORECAST' Then /** Added for Org Forecasting **/
2350 l_acct_currency_code := G_acct_currency_code_Tab(p_prvdr_operating_unit(i));
2351 END IF; /** Module Name = FORECAST **/
2352
2353 IF (p_denom_transfer_price(i) IS NOT NULL
2354 and nvl(p_denom_tp_currency_code(i),'-1') <> nvl(l_acct_currency_code,'-1')) THEN
2355 /** Changed p_acct_currency_code to l_acct_currency_code for Org Forecasting **/
2356 pa_debug.G_Err_Stage := 'Reconversion Case';
2357 IF g1_debug_mode = 'Y' THEN
2358 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2359 END IF;
2360 -- Check if all conversion attributes are provided
2361
2362 IF( ( (p_acct_tp_rate_type(i) IS NOT NULL) AND
2363 (p_acct_tp_rate_date(i) IS NOT NULL) )
2364 OR
2365 ((p_acct_tp_rate_type(i) = 'User') AND
2366 (p_acct_tp_exchange_rate(i) IS NOT NULL) ) ) THEN
2367
2368
2369 -- Only reconversion needed
2370
2371 x_compute_flag(i) := 'C';
2372
2373 ELSE
2374 x_error_code(i) := 'PA_CC_TP_CURR_CONVERSION_ATTR';
2375 END IF;
2376
2377 ELSE
2378 pa_debug.G_Err_Stage := 'Calculate Transfer Price';
2379 -- Need to calculate transfer price
2380
2381 -- Check if schedule_id is provided
2382 IF p_tp_schedule_id(i) IS NULL THEN
2383 x_error_code(i) := 'PA_CC_TP_SCH_ID_NULL';
2384 END IF;
2385
2386 END IF; /** p_denom_transfer_price IS NOT NULL **/
2387 END IF;
2388
2389 END IF; /**x_compute_flag(i) = 'Y' AND x_error_code(i) IS NULL **/
2390
2391 END Loop;
2392
2393 pa_debug.Reset_err_stack;
2394 pa_debug.G_Err_Stage := 'Exitting Validate_Array ';
2395 IF g1_debug_mode = 'Y' THEN
2396 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2397 END IF;
2398
2399 EXCEPTION
2400 when others then
2401 raise;
2402 END Validate_Array;
2403 --------------------------------------------------------------------------------
2404 PROCEDURE Get_Schedule_Line(
2405 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2406 /* Start Added for 3118101 */
2407 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
2408 /* End Added for 3118101 */
2409 p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2410 p_recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2411 p_recvr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2412 p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
2413 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2414 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
2415 --Start Added for devdrop2
2416 p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
2417 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2418 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab ,
2419 /** Added for Org Forecasting **/
2420
2421 --End Added for devdrop2
2422 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
2423 x_tp_schedule_line_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
2424 x_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
2425 x_tp_rule_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
2426 )
2427 IS
2428 l_tp_schedule_line_id Number;
2429 l_start_date_active Date;
2430 l_end_date_active Date;
2431 l_tp_rule_id Number;
2432 l_percentage_applied Number;
2433 p_sort_order Number; -- Added for bug 5753774
2434
2435 BEGIN
2436
2437 pa_debug.Set_err_stack ('Get_Schedule_Line');
2438 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line';
2439 IF g1_debug_mode = 'Y' THEN
2440 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2441 END IF;
2442
2443 For i in 1 ..G_array_Size
2444 Loop
2445 If G_Calling_module = 'FORECAST' Then /** Added for Org Forecasting **/
2446
2447 pa_debug.G_Err_Stage := 'p_prvdr_operating_unit: '||
2448 to_char(p_prvdr_operating_unit(i));
2449 IF g1_debug_mode = 'Y' THEN
2450 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2451 END IF;
2452
2453 Set_Global_Variables (p_prvdr_operating_unit(i));
2454 Get_legal_entity (G_prvdr_org_id,G_prvdr_legal_entity_id);
2455 End If;
2456
2457
2458 pa_debug.G_Err_Stage := 'Processing Expenditure ID: '||
2459 to_char(p_expenditure_item_id(i));
2460 IF g1_debug_mode = 'Y' THEN
2461 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2462 END IF;
2463
2464 pa_debug.G_Err_Stage := 'p_compute_flag: '||p_compute_flag(i);
2465 IF g1_debug_mode = 'Y' THEN
2466 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2467 END IF;
2468
2469 pa_debug.G_Err_Stage := 'x_error_code: '||x_error_code(i);
2470 IF g1_debug_mode = 'Y' THEN
2471 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2472 END IF;
2473
2474 IF (p_compute_flag(i) = 'Y' AND
2475 x_error_code(i) is null ) THEN
2476
2477 --check if the schedule line already exists in PA_CC_TP_SCHEDULE_LINE_LOOKUP
2478 Get_Schedule_Line_From_Lookup(
2479 p_prvdr_organization_id (i),
2480 p_recvr_org_id (i),
2481 p_recvr_organization_id(i),
2482 p_tp_schedule_id (i),
2483 p_labor_non_labor_flag(i),
2484 --Start Added for devdrop2
2485 p_tp_amt_type_code(i),
2486 --End Added for devdrop2
2487 /* Start Added for 3118101 */
2488 p_expenditure_item_date(i),
2489 /* End Added for 3118101 */
2490
2491 x_tp_schedule_line_id(i) );
2492
2493 IF x_tp_schedule_line_id(i) IS NULL THEN
2494 -- Find out schedule_line_id
2495
2496 pa_debug.G_Err_Stage := 'Find out Schedule_Line_ID';
2497 Determine_Schedule_Line(
2498 p_prvdr_organization_id(i),
2499 p_recvr_org_id(i),
2500 p_recvr_organization_id(i),
2501 p_tp_schedule_id(i),
2502 p_labor_non_labor_flag(i),
2503 /* Start Added for 3118101 */
2504 p_expenditure_item_date(i),
2505 /* End Added for 3118101 */
2506 --Start Added for devdrop2
2507 p_tp_amt_type_code(i),
2508 --End Added for devdrop2
2509 x_tp_schedule_line_id(i),
2510 l_tp_rule_id,
2511 l_percentage_applied,
2512 l_start_date_active,
2513 l_end_date_active,
2514 p_sort_order, -- added for bug 5753774
2515 x_error_code(i) );
2516
2517 IF g1_debug_mode = 'Y' THEN
2518 pa_debug.write_file('LOG','Error Code'||x_error_code(i));
2519 END IF;
2520
2521 l_tp_schedule_line_id := x_tp_schedule_line_id(i);
2522
2523 IF ( x_error_code(i) IS NULL ) THEN
2524 -- No error from Determine_Schedule_Line
2525
2526 pa_debug.G_Err_Stage := 'Insert Schedule Line into Lookup';
2527 -- pa_debug.G_Err_Stage := 'Sort order is :'||x_sort_order ;/*bug 5753774*/
2528
2529 Insert_Schedule_Line_Into_Lkp(
2530 p_prvdr_organization_id(i),
2531 p_recvr_org_id (i),
2532 p_recvr_organization_id(i),
2533 p_tp_schedule_id (i),
2534 l_tp_schedule_line_id,
2535 p_labor_non_labor_flag(i),
2536 --Start Added for devdrop2
2537 p_tp_amt_type_code(i),
2538 --End Added for devdrop2
2539 l_start_date_active,
2540 l_end_date_active,
2541 p_sort_order, -- added for bug 5753774
2542 x_error_code(i)
2543 );
2544 END IF;
2545
2546 ELSE
2547
2548 l_tp_schedule_line_id := x_tp_schedule_line_id(i);
2549 -- Get Schedule Line attributes
2550 pa_debug.G_Err_Stage := 'Get Schedule Line Attributes';
2551
2552 Get_Schedule_Line_Attributes(
2553 l_tp_schedule_line_id,
2554 p_labor_non_labor_flag(i),
2555 l_tp_rule_id,
2556 l_percentage_applied,
2557 x_error_code(i) );
2558 END IF;
2559 -- Set the OUT variables
2560 x_tp_schedule_line_id(i) := l_tp_schedule_line_id;
2561 x_tp_schedule_line_percentage(i) := l_percentage_applied;
2562 x_tp_rule_id (i) := l_tp_rule_id;
2563 pa_debug.G_Err_Stage := 'Rule ID is '||to_char(x_tp_rule_id(i));
2564 IF g1_debug_mode = 'Y' THEN
2565 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2566 END IF;
2567
2568
2569 END IF; /** p_compute_flag = 'Y' and x_error_code is null **/
2570
2571 pa_debug.G_Err_Stage := 'Completed Processing the item';
2572 IF g1_debug_mode = 'Y' THEN
2573 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2574 END IF;
2575 pa_debug.G_Err_Stage := '..................................................';
2576 IF g1_debug_mode = 'Y' THEN
2577 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2578 END IF;
2579
2580 End Loop;
2581
2582 pa_debug.Reset_err_stack;
2583 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line';
2584 IF g1_debug_mode = 'Y' THEN
2585 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2586 END IF;
2587
2588 EXCEPTION
2589 WHEN OTHERS THEN
2590 raise;
2591
2592 END Get_Schedule_Line;
2593 ------------------------------------------------------------------------------
2594 PROCEDURE Get_Schedule_Line_From_Lookup(
2595 p_prvdr_organization_id IN Number,
2596 p_recvr_org_id IN Number,
2597 p_recvr_organization_id IN Number,
2598 p_tp_schedule_id IN Number,
2599 p_labor_flag IN Varchar2,
2600 --Start Added for devdrop2
2601 p_tp_amt_type_code IN Varchar2,
2602 --End Added for devdrop2
2603 /* Start Added for 3118101 */
2604 p_expenditure_item_date IN Date,
2605 /* End Added for 3118101 */
2606 x_tp_schedule_line_id OUT NOCOPY Number /*File.sql.39*/
2607 )
2608 IS
2609
2610 Cursor c_schedule_line
2611 is
2612 select tp_schedule_line_id
2613 from PA_CC_TP_SCHEDULE_LINE_LKP
2614 where
2615 tp_schedule_id = p_tp_schedule_id
2616 and prvdr_organization_id = p_prvdr_organization_id
2617 and recvr_organization_id = p_recvr_organization_id
2618 and prvdr_org_id = G_prvdr_org_id
2619 and recvr_org_id = p_recvr_org_id
2620 and labor_flag = p_labor_flag
2621 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2622 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2623 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2624 /* and trunc(G_processed_thru_date) between Commented for 3118101 */
2625 and trunc(p_expenditure_item_date) between /* Added for 3118101 */
2626 trunc(start_date_active) and
2627 /* trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2628 trunc(NVL(end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2629 ORDER BY sort_order; /*Bug 5753774*/
2630
2631 BEGIN
2632 pa_debug.Set_err_stack ('Get_Schedule_Line_From_Lookup');
2633 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line_From_Lookup';
2634 IF g1_debug_mode = 'Y' THEN
2635 Pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2636
2637 Pa_debug.write_file('LOG','prvdr_oranz_id '||p_prvdr_organization_id||
2638 ' recv_organz_id '||p_recvr_organization_id||
2639 'prvdr_org_id '||G_prvdr_org_id||
2640 'p_recvr_org_id '||p_recvr_org_id||
2641 ' sch_id '||p_tp_schedule_id);
2642 Pa_debug.write_file('LOG','p_labor_flag '||p_labor_flag||
2643 ' tp_amt_type_code '||p_tp_amt_type_code||
2644 ' p_expenditure_item_date '||to_char(p_expenditure_item_date)); /* Added for 3118101 */
2645 END IF;
2646
2647 open c_schedule_line;
2648
2649 fetch c_schedule_line
2650 into x_tp_schedule_line_id;
2651
2652 close c_schedule_line;
2653
2654
2655 pa_debug.Reset_err_stack;
2656 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line_From_Lookup';
2657 IF g1_debug_mode = 'Y' THEN
2658 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2659 END IF;
2660
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 raise;
2664 END Get_Schedule_Line_From_Lookup;
2665 -------------------------------------------------------------------------------
2666 PROCEDURE Determine_Schedule_Line(
2667 p_prvdr_organization_id IN Number,
2668 p_recvr_org_id IN Number,
2669 p_recvr_organization_id IN Number,
2670 p_tp_schedule_id IN Number,
2671 p_labor_non_labor_flag IN Varchar2,
2672 /* Start Added for 3118101 */
2673 p_expenditure_item_date IN Date,
2674 /* End Added for 3118101 */
2675 --Start Added for devdrop2
2676 p_tp_amt_type_code IN Varchar2,
2677 --End Added for devdrop2
2678 x_tp_schedule_line_id OUT NOCOPY Number,/*File.sql.39*/
2679 x_tp_rule_id OUT NOCOPY Number,/*File.sql.39*/
2680 x_percentage_applied OUT NOCOPY Number,/*File.sql.39*/
2681 x_start_date_active OUT NOCOPY Date,/*File.sql.39*/
2682 x_end_date_active OUT NOCOPY Date,/*File.sql.39*/
2683 x_sort_order OUT NOCOPY NUMBER/*bug 5753774*/,
2684 x_error_code IN OUT NOCOPY VARCHAR2 /*File.sql.39*/
2685 )
2686 IS
2687
2688 cursor c_rule( l_prvdr_organization_id Number,
2689 l_recvr_organization_id Number)
2690 is
2691 select tp_schedule_line_id,sort_order, -- Added for bug 5753774
2692 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2693 nl_tp_rule_id) rule_id,
2694 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2695 nl_percentage_applied) percentage_applied
2696 ,start_date_active
2697 ,end_date_active
2698 from pa_cc_tp_schedule_lines
2699 where tp_schedule_id = p_tp_schedule_id
2700 and prvdr_organization_id = l_prvdr_organization_id
2701 and recvr_organization_id = l_recvr_organization_id
2702 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2703 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2704 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2705 and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
2706 OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
2707 /* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
2708 and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
2709 /* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2710 and trunc(NVL(end_date_active,p_expenditure_item_date)); /* Added for 3118101 */
2711
2712 --DevDrop2 Changes start
2713
2714 cursor c_parent_rule ( l_prvdr_organization_id Number,
2715 l_recvr_organization_id Number)
2716 is
2717 select a.tp_schedule_line_id,sort_order, -- Added for bug 5753774
2718 Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
2719 a.nl_tp_rule_id) rule_id,
2720 Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
2721 a.nl_percentage_applied) percentage_applied,
2722 a.start_date_active,
2723 a.end_date_active
2724 from pa_cc_tp_schedule_lines a,
2725 pa_org_hierarchy_denorm b,
2726 pa_org_hierarchy_denorm c
2727 where a.tp_schedule_id = p_tp_schedule_id
2728 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2729 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2730 and a.RECVR_ORGANIZATION_ID = c.PARENT_ORGANIZATION_ID
2731 and c.CHILD_ORGANIZATION_ID = l_recvr_organization_id
2732 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2733 and c.org_hierarchy_version_id = G_prj_org_struct_ver_id
2734 and b.pa_org_use_type = 'TP_SCHEDULE'
2735 and c.pa_org_use_type = 'TP_SCHEDULE'
2736 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2737 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2738 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2739 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2740 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2741 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2742 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2743 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2744 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2745 order by b.parent_level desc , c.PARENT_LEVEL desc;
2746
2747
2748 cursor c_prvdr_organz_rule( l_prvdr_organization_id Number,
2749 l_recvr_organization_id Number)
2750 is
2751 select tp_schedule_line_id,sort_order, -- Added for bug 5753774
2752 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2753 nl_tp_rule_id) rule_id,
2754 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2755 nl_percentage_applied) percentage_applied
2756 ,start_date_active
2757 ,end_date_active
2758 from pa_cc_tp_schedule_lines a,
2759 pa_org_hierarchy_denorm b
2760 where a.tp_schedule_id = p_tp_schedule_id
2761 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2762 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2763 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2764 and a.recvr_organization_id = l_recvr_organization_id
2765 and b.pa_org_use_type = 'TP_SCHEDULE'
2766 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2767 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2768 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2769 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2770 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2771 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2772 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2773 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2774 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2775 order by b.parent_level desc;
2776
2777 --DevDrop2 Changes End
2778
2779
2780 --DevDrop2 Changes
2781 --Added pa_org_hierarchy_denorm join to the below cursor.
2782
2783
2784 cursor c_other_rule(l_prvdr_organization_id NUMBER)
2785 is
2786 select a.tp_schedule_line_id,sort_order, -- Added for Bug 5753774
2787 Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
2788 a.nl_tp_rule_id) rule_id,
2789 Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
2790 a.nl_percentage_applied) percentage_applied
2791 ,a.start_date_active
2792 ,a.end_date_active
2793 from pa_cc_tp_schedule_lines a,
2794 pa_org_hierarchy_denorm b
2795 where a.tp_schedule_id = p_tp_schedule_id
2796 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2797 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2798 and a.recvr_organization_id is null
2799 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2800 and b.pa_org_use_type = 'TP_SCHEDULE'
2801 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2802 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2803 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2804 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2805 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2806 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2807 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2808 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2809 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2810 order by b.parent_level desc;
2811
2812 cursor c_default_rule
2813 is
2814 select tp_schedule_line_id,sort_order, --Added for bug 5753774
2815 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2816 nl_tp_rule_id) rule_id,
2817 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2818 nl_percentage_applied) percentage_applied
2819 ,start_date_active
2820 ,end_date_active
2821 from pa_cc_tp_schedule_lines
2822 where tp_schedule_id = p_tp_schedule_id
2823 and default_flag ='Y'
2824 and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
2825 OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
2826 /* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
2827 and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
2828 /* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2829 and trunc(NVL(end_date_active,p_expenditure_item_date)); /* Added for 3118101 */
2830
2831 l_recvr_legal_entity_id Number;
2832 l_recvr_business_group_id number;
2833
2834 BEGIN
2835 pa_debug.Set_err_stack ('Determine_Schedule_Line');
2836 pa_debug.G_Err_Stage := 'Starting Determine_Schedule_Line';
2837 IF g1_debug_mode = 'Y' THEN
2838 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2839
2840
2841 pa_debug.write_file('LOG',
2842 'p_prvdr_organization_id '||p_prvdr_organization_id);
2843 pa_debug.write_file('LOG',
2844 'p_recvr_org_id '||p_recvr_org_id);
2845 pa_debug.write_file('LOG',
2846 'p_recvr_organization_id '||p_recvr_organization_id);
2847 pa_debug.write_file('LOG',
2848 'p_recvr_org_id '||p_recvr_org_id);
2849 pa_debug.write_file('LOG',
2850 'p_tp_schedule_id '||p_tp_schedule_id);
2851 pa_debug.write_file('LOG',
2852 'p_labor_non_labor_flag '||p_labor_non_labor_flag);
2853 pa_debug.write_file('LOG',
2854 'p_tp_amt_type_code '||p_tp_amt_type_code);
2855 pa_debug.write_file('LOG',
2856 'G_exp_org_struct_ver_id '||G_exp_org_struct_ver_id);
2857 pa_debug.write_file('LOG',
2858 'G_prj_org_struct_ver_id '||G_prj_org_struct_ver_id);
2859 END IF;
2860
2861 -- Use Rule1
2862
2863
2864 IF g1_debug_mode = 'Y' THEN
2865 pa_debug.write_file('LOG','Testing Rule 1');
2866 END IF;
2867
2868 open c_rule (p_prvdr_organization_id,p_recvr_organization_id);
2869 fetch c_rule
2870 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied, -- added for bug 5753774
2871 x_start_date_active,x_end_date_active;
2872
2873 IF c_rule%FOUND THEN
2874 IF (x_tp_rule_id IS NOT NULL) THEN
2875 close c_rule;
2876 pa_debug.Reset_err_stack;
2877 pa_debug.G_Err_Stage := 'Schedule Line found using Rule1';
2878 IF g1_debug_mode = 'Y' THEN
2879 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2880 pa_debug.write_file('LOG','x_tp_schedule_line_id='||x_tp_schedule_line_id); -- Added for bug 5753774
2881 pa_debug.write_file('LOG','x_sort_order='||x_sort_order);
2882
2883 END IF;
2884 Return;
2885 END IF;
2886 END IF;
2887
2888 close c_rule;
2889
2890 --DevDrop2 Changes Start
2891
2892 IF g1_debug_mode = 'Y' THEN
2893 pa_debug.write_file('LOG','Testing Parent Rule ');
2894 END IF;
2895
2896 if ( nvl(G_prev_rcvr_org_id,-1) <> nvl(p_recvr_org_id,-1) ) then
2897 G_prev_rcvr_org_id := p_recvr_org_id;
2898 select PROJ_ORG_STRUCTURE_VERSION_ID into G_prj_org_struct_ver_id
2899 from pa_implementations_all
2900 where org_id = nvl(G_prev_rcvr_org_id,-1); /* For Bug 5900371. Modified nvl(org_id,-1) to org_id as Org_id is mandatory in R12 and hence can never be Null */
2901 end if;
2902
2903
2904 open c_parent_rule (p_prvdr_organization_id,p_recvr_organization_id);
2905 fetch c_parent_rule
2906 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied, --added for bug 5753774
2907 x_start_date_active,x_end_date_active;
2908
2909 IF c_parent_rule%FOUND THEN
2910 IF (x_tp_rule_id IS NOT NULL) THEN
2911 close c_parent_rule;
2912 pa_debug.Reset_err_stack;
2913 pa_debug.G_Err_Stage := 'Schedule Line found using Parent Rule';
2914 IF g1_debug_mode = 'Y' THEN
2915 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2916 pa_debug.write_file('LOG','x_tp_schedule_line_id='||x_tp_schedule_line_id); -- Added for bug 5753774
2917 pa_debug.write_file('LOG','x_sort_order='||x_sort_order);
2918 END IF;
2919 Return;
2920 END IF;
2921 END IF;
2922
2923
2924 --DevDrop2 Changes End
2925
2926 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2927
2928 IF g1_debug_mode = 'Y' THEN
2929 pa_debug.write_file('LOG','Testing Rule 2');
2930 END IF;
2931
2932 /* **************************************************************
2933 COMMENTED FOR LEGAL ENTITY TRANSFER PRICE SCHEDULE. aFTER 12.0 THERE
2934 IS NO CONCEPT OF OU,LE AND BG check every thing should be maintained
2935 in org hierarchy
2936
2937 -- Use Rule2
2938 open c_prvdr_organz_rule (p_prvdr_organization_id,p_recvr_org_id);
2939 fetch c_prvdr_organz_rule
2940 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied --Added for bug 5753774
2941 ,x_start_date_active,x_end_date_active;
2942
2943
2944 IF c_prvdr_organz_rule%FOUND THEN
2945 IF (x_tp_rule_id IS NOT NULL) THEN
2946 close c_prvdr_organz_rule;
2947 pa_debug.Reset_err_stack;
2948 pa_debug.G_Err_Stage := 'Schedule Line found using Rule2';
2949 IF g1_debug_mode = 'Y' THEN
2950 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2951 END IF;
2952 Return;
2953 END IF;
2954 END IF;
2955
2956 close c_prvdr_organz_rule;
2957
2958 -- Get receiver's legal entity id
2959 Get_Legal_Entity (p_recvr_org_id, l_recvr_legal_entity_id);
2960
2961
2962 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2963 -- Use Rule3
2964 open c_prvdr_organz_rule (p_prvdr_organization_id,l_recvr_legal_entity_id);
2965 fetch c_prvdr_organz_rule
2966 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied --Added for bug 5753774
2967 ,x_start_date_active,x_end_date_active;
2968
2969 IF g1_debug_mode = 'Y' THEN
2970 pa_debug.write_file('LOG','Testing Rule 3');
2971 END IF;
2972 IF c_prvdr_organz_rule%FOUND THEN
2973 IF (x_tp_rule_id IS NOT NULL) THEN
2974 close c_prvdr_organz_rule;
2975 pa_debug.Reset_err_stack;
2976 pa_debug.G_Err_Stage := 'Schedule Line found using Rule3';
2977 IF g1_debug_mode = 'Y' THEN
2978 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2979 END IF;
2980 Return;
2981 END IF;
2982 END IF;
2983
2984 close c_prvdr_organz_rule;
2985
2986 --Get receiver's business group id
2987 get_business_group(p_recvr_org_id, l_recvr_business_group_id);
2988
2989 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2990 --Use Rule4
2991 if G_global_access = 'Y' then
2992 open c_prvdr_organz_rule(p_prvdr_organization_id,l_recvr_business_group_id);
2993 fetch c_prvdr_organz_rule
2994 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied --Added for bug 5753774
2995 ,x_start_date_active,x_end_date_active;
2996 IF g1_debug_mode = 'Y' THEN
2997 pa_debug.write_file('LOG','Testing Rule 4');
2998 END IF;
2999 IF c_prvdr_organz_rule%FOUND THEN
3000 IF (x_tp_rule_id IS NOT NULL) THEN
3001 close c_prvdr_organz_rule;
3002 pa_debug.Reset_err_stack;
3003 pa_debug.G_Err_Stage := 'Schedule Line found using Rule4';
3004 IF g1_debug_mode = 'Y' THEN
3005 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3006 END IF;
3007 Return;
3008 END IF;
3009 END IF;
3010
3011 close c_prvdr_organz_rule;
3012 end if; ****************************************************************
3013 ***************************************************End for comment for Legal Entity*/
3014
3015
3016 -- Use Rule2
3017 IF g1_debug_mode = 'Y' THEN
3018 pa_debug.write_file('LOG','Testing Rule 2');
3019 END IF;
3020 open c_other_rule (p_prvdr_organization_id);
3021 fetch c_other_rule
3022 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3023 ,x_start_date_active,x_end_date_active;
3024
3025 IF c_other_rule%FOUND THEN
3026 IF (x_tp_rule_id IS NOT NULL) THEN
3027 close c_other_rule;
3028 pa_debug.Reset_err_stack;
3029 pa_debug.G_Err_Stage := 'Schedule Line found using Rule5';
3030 IF g1_debug_mode = 'Y' THEN
3031 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3032 END IF;
3033 Return;
3034 END IF;
3035 END IF;
3036
3037 close c_other_rule;
3038
3039 /* ***************************************************************
3040 COMMENTED FOR LEGAL ENTITY TRANSFER PRICE SCHEDULE. aFTER 12.0 THERE
3041 IS NO CONCEPT OF OU,LE AND BG check every thing should be maintained
3042 in org hierarchy
3043 -- Use Rule6
3044 IF g1_debug_mode = 'Y' THEN
3045 pa_debug.write_file('LOG','Testing Rule 6');
3046 END IF;
3047 open c_rule (G_Prvdr_Org_id,p_recvr_org_id);
3048 fetch c_rule
3049 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3050 ,x_start_date_active,x_end_date_active;
3051
3052 IF c_rule%FOUND THEN
3053 IF (x_tp_rule_id IS NOT NULL) THEN
3054 close c_rule;
3055 pa_debug.Reset_err_stack;
3056 pa_debug.G_Err_Stage := 'Schedule Line found using Rule6';
3057 IF g1_debug_mode = 'Y' THEN
3058 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3059 END IF;
3060 Return;
3061 END IF;
3062 END IF;
3063
3064 close c_rule;
3065
3066 -- Use Rule7
3067 IF g1_debug_mode = 'Y' THEN
3068 pa_debug.write_file('LOG','Testing Rule 7');
3069 END IF;
3070 open c_rule (G_prvdr_org_id,l_recvr_legal_entity_id);
3071 fetch c_rule
3072 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3073 , x_start_date_active,x_end_date_active;
3074
3075 IF c_rule%FOUND THEN
3076 IF (x_tp_rule_id IS NOT NULL) THEN
3077 close c_rule;
3078 pa_debug.Reset_err_stack;
3079 pa_debug.G_Err_Stage := 'Schedule Line found using Rule7';
3080 IF g1_debug_mode = 'Y' THEN
3081 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3082 END IF;
3083 Return;
3084 END IF;
3085 END IF;
3086
3087 close c_rule;
3088
3089 --Use Rule 8
3090 if G_global_access='Y' then
3091 IF g1_debug_mode = 'Y' THEN
3092 pa_debug.write_file('LOG','Testing Rule 8');
3093 END IF;
3094 open c_rule(G_prvdr_org_id,l_recvr_business_group_id);
3095 fetch c_rule
3096 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3097 , x_start_date_active,x_end_date_active;
3098 IF c_rule%FOUND THEN
3099 IF (x_tp_rule_id IS NOT NULL) THEN
3100 close c_rule;
3101 pa_debug.Reset_err_stack;
3102 pa_debug.G_Err_Stage := 'Schedule Line found using Rule8';
3103 IF g1_debug_mode = 'Y' THEN
3104 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3105 END IF;
3106 Return;
3107 END IF;
3108 END IF;
3109
3110 close c_rule;
3111 end if;
3112
3113 -- Use Rule9
3114 IF g1_debug_mode = 'Y' THEN
3115 pa_debug.write_file('LOG','Testing Rule 9 '|| to_char(G_prvdr_org_id));
3116 END IF;
3117 open c_other_rule (G_prvdr_org_id);
3118 fetch c_other_rule
3119 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3120 ,x_start_date_active,x_end_date_active;
3121
3122 IF c_other_rule%FOUND THEN
3123 pa_debug.G_Err_Stage := 'Cursor matched Rule9';
3124 IF g1_debug_mode = 'Y' THEN
3125 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3126 END IF;
3127
3128 IF (x_tp_rule_id IS NOT NULL) THEN
3129 close c_other_rule;
3130 pa_debug.Reset_err_stack;
3131 pa_debug.G_Err_Stage := 'Schedule Line found using Rule9';
3132 IF g1_debug_mode = 'Y' THEN
3133 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3134 END IF;
3135 Return;
3136 END IF;
3137 END IF;
3138
3139 close c_other_rule;
3140
3141
3142 -- Use Rule10
3143 IF g1_debug_mode = 'Y' THEN
3144 pa_debug.write_file('LOG','Testing Rule 10');
3145 END IF;
3146 open c_rule (G_prvdr_legal_entity_id,l_recvr_legal_entity_id);
3147 fetch c_rule
3148 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3149 ,x_start_date_active,x_end_date_active;
3150
3151 IF c_rule%FOUND THEN
3152 IF (x_tp_rule_id IS NOT NULL) THEN
3153 close c_rule;
3154 pa_debug.Reset_err_stack;
3155 pa_debug.G_Err_Stage := 'Schedule Line found using Rule10';
3156 IF g1_debug_mode = 'Y' THEN
3157 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3158 END IF;
3159 Return;
3160 END IF;
3161 END IF;
3162
3163 close c_rule;
3164
3165 --Use Rule 11
3166 if G_global_access ='Y' then
3167 IF g1_debug_mode = 'Y' THEN
3168 pa_debug.write_file('LOG','Testing Rule 11');
3169 END IF;
3170 open c_rule (G_prvdr_legal_entity_id,l_recvr_business_group_id);
3171 fetch c_rule
3172 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for 5753774
3173 ,x_start_date_active,x_end_date_active;
3174 IF c_rule%FOUND THEN
3175 IF (x_tp_rule_id IS NOT NULL) THEN
3176 close c_rule;
3177 pa_debug.Reset_err_stack;
3178 pa_debug.G_Err_Stage := 'Schedule Line found using Rule11';
3179 IF g1_debug_mode = 'Y' THEN
3180 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3181 END IF;
3182 Return;
3183 END IF;
3184 END IF;
3185
3186 close c_rule;
3187 end if;
3188
3189 -- Use Rule12
3190 IF g1_debug_mode = 'Y' THEN
3191 pa_debug.write_file('LOG','Testing Rule 12');
3192 END IF;
3193 open c_other_rule (G_prvdr_legal_entity_id);
3194 fetch c_other_rule
3195 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3196 ,x_start_date_active,x_end_date_active;
3197
3198 IF c_other_rule%FOUND THEN
3199 IF (x_tp_rule_id IS NOT NULL) THEN
3200 close c_other_rule;
3201 pa_debug.Reset_err_stack;
3202 pa_debug.G_Err_Stage := 'Schedule Line found using Rule12';
3203 IF g1_debug_mode = 'Y' THEN
3204 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3205 END IF;
3206 Return;
3207 END IF;
3208 END IF;
3209 close c_other_rule;
3210
3211 --Use Rule 13
3212 if G_global_access='Y' then
3213 IF g1_debug_mode = 'Y' THEN
3214 pa_debug.write_file('LOG','Testing Rule 13');
3215 END IF;
3216 open c_rule(G_bg_id,l_recvr_business_group_id);
3217 fetch c_rule
3218 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3219 ,x_start_date_active,x_end_date_active;
3220 IF c_rule%FOUND THEN
3221 IF (x_tp_rule_id IS NOT NULL) THEN
3222 close c_rule;
3223 pa_debug.Reset_err_stack;
3224 pa_debug.G_Err_Stage := 'Schedule Line found using Rule13';
3225 IF g1_debug_mode = 'Y' THEN
3226 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3227 END IF;
3228 Return;
3229 END IF;
3230 END IF;
3231 close c_rule;
3232 end if;
3233
3234 -- Use Rule14
3235 open c_other_rule (G_bg_id);
3236 fetch c_other_rule
3237 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3238 ,x_start_date_active,x_end_date_active;
3239
3240 IF c_other_rule%FOUND THEN
3241 IF (x_tp_rule_id IS NOT NULL) THEN
3242 close c_other_rule;
3243 pa_debug.Reset_err_stack;
3244 pa_debug.G_Err_Stage := 'Schedule Line found using Rule14';
3245 IF g1_debug_mode = 'Y' THEN
3246 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3247 END IF;
3248 Return;
3249 END IF;
3250 END IF;
3251
3252 close c_other_rule; *********************************************
3253 *********************************************************End for Legal Entity */
3254
3255 ---Use default rule
3256 open c_default_rule;
3257 fetch c_default_rule
3258 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3259 ,x_start_date_active,x_end_date_active;
3260 if c_default_rule%found then
3261 IF (x_tp_rule_id IS NOT NULL) THEN
3262 close c_default_rule;
3263 pa_debug.Reset_err_stack;
3264 pa_debug.G_Err_Stage := 'Schedule Line found using default rule';
3265 IF g1_debug_mode = 'Y' THEN
3266 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3267 END IF;
3268 Return;
3269 END IF;
3270 END IF;
3271
3272 x_error_code := 'PA_CC_TP_NO_SCHEDULE_LINE';
3273 pa_debug.Reset_err_stack;
3274 pa_debug.G_Err_Stage := 'Exitting Determine_Schedule_Line';
3275 IF g1_debug_mode = 'Y' THEN
3276 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3277 END IF;
3278
3279 EXCEPTION
3280 WHEN OTHERS THEN
3281 raise;
3282 END Determine_Schedule_Line;
3283
3284 -------------------------------------------------------------------------------
3285 PROCEDURE Insert_Schedule_Line_Into_Lkp(
3286 p_prvdr_organization_id IN Number,
3287 p_recvr_org_id IN Number,
3288 p_recvr_organization_id IN Number,
3289 p_tp_schedule_id IN Number,
3290 p_tp_schedule_line_id IN Number,
3291 p_labor_flag IN Varchar2,
3292 --Start Added for devdrop2
3293 p_tp_amt_type_code IN Varchar2,
3294 --End Added for devdrop2
3295 p_start_date_active IN Date,
3296 p_end_date_active IN Date,
3297 p_sort_order IN Number, -- Added for bug 5753774
3298 x_error_code IN OUT NOCOPY Varchar2 /*File.sql.39*/
3299 )
3300 IS
3301
3302 PRAGMA AUTONOMOUS_TRANSACTION;
3303
3304 BEGIN
3305 pa_debug.Set_err_stack ('Insert_Schedule_Line_Into_Lkp');
3306 pa_debug.G_Err_Stage := 'Starting Insert_Schedule_Line_Into_Lkp';
3307 IF g1_debug_mode = 'Y' THEN
3308 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3309 END IF;
3310
3311 INSERT INTO
3312 PA_CC_TP_SCHEDULE_LINE_LKP
3313 (tp_schedule_id,
3314 tp_schedule_line_id,
3315 prvdr_org_id,
3316 prvdr_organization_id,
3317 recvr_org_id,
3318 recvr_organization_id,
3319 labor_flag,
3320 tp_amt_type_code,
3321 start_date_active,
3322 sort_order, -- Added for bug 5753774
3323 creation_date,
3324 created_by,
3325 last_update_date,
3326 last_updated_by,
3327 end_date_active,
3328 last_update_login)
3329 VALUES
3330 (p_tp_schedule_id,
3331 p_tp_schedule_line_id,
3332 G_prvdr_org_id,
3333 p_prvdr_organization_id,
3334 p_recvr_org_id,
3335 p_recvr_organization_id,
3336 p_labor_flag,
3337 p_tp_amt_type_code,
3338 p_start_date_active,
3339 p_sort_order, -- Added for bug 5753774
3340 G_creation_date,
3341 G_created_by,
3342 G_last_update_date,
3343 G_last_updated_by,
3344 p_end_date_active,
3345 G_last_update_login);
3346
3347 -- commit the autonomous transaction
3348 commit;
3349
3350 pa_debug.Reset_err_stack;
3351 pa_debug.G_Err_Stage := 'Exitting Insert_Schedule_Line_Into_Lkp';
3352 IF g1_debug_mode = 'Y' THEN
3353 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3354 END IF;
3355
3356 EXCEPTION
3357 WHEN Dup_val_on_index THEN
3358 pa_debug.Reset_err_stack;
3359 null;
3360 WHEN OTHERS THEN
3361 raise;
3362
3363 END Insert_Schedule_Line_Into_Lkp;
3364 --------------------------------------------------------------------------------
3365 PROCEDURE Get_Schedule_Line_Attributes(
3366 p_tp_schedule_line_id IN Number,
3367 p_labor_flag IN Varchar2,
3368 x_tp_rule_id OUT NOCOPY Number, /*File.sql.39*/
3369 x_percentage_applied OUT NOCOPY Number, /*File.sql.39*/
3370 x_error_code IN OUT NOCOPY VARCHAR2 /*File.sql.39*/
3371 )
3372 IS
3373
3374 cursor c_schedule_line_attr
3375 is
3376 select
3377 Decode (p_labor_flag ,'Y', labor_tp_rule_id,nl_tp_rule_id ),
3378 Decode (p_labor_flag,'Y',labor_percentage_applied,nl_percentage_applied)
3379 from pa_cc_tp_schedule_lines
3380 where tp_schedule_line_id = p_tp_schedule_line_id;
3381
3382 BEGIN
3383 pa_debug.Set_err_stack ('Get_Schedule_Line_Attributes');
3384 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line_Attributes';
3385 IF g1_debug_mode = 'Y' THEN
3386 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3387 END IF;
3388
3389 open c_schedule_line_attr;
3390 fetch c_schedule_line_attr
3391 into x_tp_rule_id,x_percentage_applied ;
3392
3393 IF c_schedule_line_attr%NOTFOUND THEN
3394 x_error_code := 'PA_CC_TP_NO_SCHEDULE_LINE_FOR_ID';
3395 END IF;
3396
3397 close c_schedule_line_attr;
3398
3399 pa_debug.Reset_err_stack;
3400 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line_Attributes';
3401 IF g1_debug_mode = 'Y' THEN
3402 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3403 END IF;
3404
3405 EXCEPTION
3406 WHEN OTHERS THEN
3407 raise;
3408
3409 END Get_Schedule_Line_Attributes;
3410 --------------------------------------------------------------------------------
3411 PROCEDURE Get_Transfer_Price_Amount
3412 (
3413 p_tp_rule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3414 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3415 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3416 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3417 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3418 p_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3419 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3420 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3421 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3422 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3423 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3424 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3425 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3426 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3427 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
3428 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3429 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3430 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
3431 p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3432 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
3433 p_tp_schedule_line_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
3434 p_tp_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3435 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3436 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3437 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3438 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3439 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3440 x_tp_base_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3441 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3442 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3443 x_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3444 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3445 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
3446 /* Bill rate Discount*/
3447 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
3448 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3449 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.NumTabTyp,
3450 p_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3451 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
3452 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3453 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3454 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3455 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3456 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3457 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
3458 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3459 p_raw_cost_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
3460 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3461 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3462 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3463 p_exp_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3464 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
3465 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3466
3467 p_burden_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3468 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3469 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3470 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3471 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3472 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3473 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3474 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3475 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3476 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3477 /* Added the two parameters for Doosan rate api enhancement */
3478 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
3479 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3480 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
3481 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3482 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
3483 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
3484
3485 )
3486 IS
3487
3488 l_rate_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
3489 l_calc_method_code PA_PLSQL_DATATYPES.Char1TabTyp;
3490 l_empty_calc_method_code PA_PLSQL_DATATYPES.Char1TabTyp;
3491 l_basis_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3492 l_bill_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3493 l_burden_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3494
3495 BEGIN
3496 pa_debug.Set_err_stack ('Get_Transfer_Price_Amount');
3497 pa_debug.G_Err_Stage := 'Starting Get_Transfer_Price_Amount';
3498 IF g1_debug_mode = 'Y' THEN
3499 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3500 END IF;
3501
3502
3503 l_calc_method_code := l_empty_calc_method_code;
3504
3505 -- Get Rule attributes
3506 Get_Rule_Attributes(
3507 p_tp_rule_id,
3508 p_compute_flag,
3509 l_calc_method_code,
3510 x_cc_markup_base_code,
3511 x_tp_rule_percentage,
3512 l_rate_schedule_id,
3513 x_error_code
3514 );
3515
3516 -- Set base_curr_code and base_amt based on cc_markup_base_code
3517 -- if base_code is 'burden' then recalculate burden amount if needed.
3518 -- Also set the flags basis_compute_flag, bill_rate_compute_flag dependending
3519 -- on calc_method_code.
3520
3521 Set_Base_Amount_And_Flag(
3522 p_expenditure_item_id,
3523 p_expenditure_type,
3524 p_expenditure_item_date,
3525 p_expnd_organization_id,
3526 p_project_id,
3527 p_task_id,
3528 p_tp_fixed_date,
3529 l_calc_method_code,
3530 x_cc_markup_base_code,
3531 p_denom_currency_code,
3532 p_projfunc_currency_code,
3533 p_denom_raw_cost_amount,
3534 p_denom_burdened_cost_amount,
3535 p_raw_revenue_amount,
3536 p_revenue_distributed_flag,
3537 p_compute_flag,
3538 x_tp_ind_compiled_set_id,
3539 x_error_code,
3540 l_basis_compute_flag,
3541 l_bill_rate_compute_flag,
3542 l_burden_rate_compute_flag,
3543 x_tp_base_curr_code,
3544 x_tp_base_amount,
3545 /*Bill rate Discount*/
3546 p_dist_rule,
3547 p_mcb_flag,
3548 p_bill_rate_multiplier,
3549 p_quantity,
3550 p_incurred_by_person_id,
3551 p_raw_cost,
3552 p_labor_schdl_discnt,
3553 p_labor_schdl_fixed_date,
3554 p_bill_job_grp_id,
3555 p_labor_sch_type,
3556 p_project_org_id,
3557 p_project_type,
3558 p_exp_func_curr_code,
3559 p_incurred_by_organz_id,
3560 p_raw_cost_rate,
3561 p_override_to_organz_id,
3562 p_emp_bill_rate_schedule_id,
3563 p_job_bill_rate_schedule_id,
3564 p_exp_raw_cost,
3565 p_assignment_precedes_task,
3566 p_system_linkage_function,
3567 p_assignment_id ,
3568
3569 p_burden_cost ,
3570 p_task_nl_bill_rate_org_id ,
3571 p_proj_nl_bill_rate_org_id ,
3572 p_task_nl_std_bill_rate_sch ,
3573 p_proj_nl_std_bill_rate_sch ,
3574 p_non_labor_resource ,
3575 p_nl_task_sch_date ,
3576 p_nl_proj_sch_date ,
3577 p_nl_task_sch_discount ,
3578 p_nl_proj_sch_discount ,
3579 p_nl_sch_type,
3580 /*Added for Doosan rate api enhancement */
3581 p_task_nl_std_bill_rate_sch_id,
3582 p_proj_nl_std_bill_rate_sch_id,
3583 p_uom_flag);
3584
3585
3586 Determine_Transfer_Price
3587 (
3588 p_expenditure_item_id,
3589 p_expnd_organization_id,
3590 p_expenditure_type,
3591 p_expenditure_item_date,
3592 p_tp_fixed_date,
3593 p_system_linkage_function,
3594 p_task_id,
3595 x_tp_base_curr_code,
3596 x_tp_base_amount,
3597 p_tp_schedule_line_percentage,
3598 x_tp_rule_percentage,
3599 p_compute_flag,
3600 p_quantity,
3601 p_incurred_by_person_id,
3602 p_job_id,
3603 l_rate_schedule_id,
3604 p_non_labor_resource,
3605 l_basis_compute_flag,
3606 l_bill_rate_compute_flag,
3607 l_burden_rate_compute_flag,
3608 x_denom_tp_currency_code,
3609 x_denom_transfer_price,
3610 x_tp_ind_compiled_set_id,
3611 x_tp_bill_rate,
3612 x_tp_bill_markup_percentage,
3613 x_tp_job_id,
3614 x_error_code
3615 );
3616
3617
3618 pa_debug.Reset_err_stack;
3619 pa_debug.G_Err_Stage := 'Exitting Get_Transfer_Price_Amount';
3620 IF g1_debug_mode = 'Y' THEN
3621 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3622 END IF;
3623 EXCEPTION
3624 WHEN OTHERS THEN
3625 raise;
3626
3627 END Get_Transfer_Price_Amount;
3628 -------------------------------------------------------------------------------
3629 -- Get rule attributes from pa_cc_tp_rules table
3630
3631 PROCEDURE Get_Rule_Attributes(
3632 p_tp_rule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3633 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3634 x_calc_method_code OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3635 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3636 x_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3637 x_schedule_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3638 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
3639 )
3640 IS
3641
3642 Cursor c_rule_attributes(l_tp_rule_id Number)
3643 IS
3644 select calc_method_code,markup_calc_base_code,percentage_applied,schedule_id
3645 from pa_cc_tp_rules
3646 where tp_rule_id = l_tp_rule_id;
3647
3648 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
3649
3650 BEGIN
3651 x_schedule_id := l_schedule_id;
3652 pa_debug.Set_err_stack ('Get_Rule_Attributes');
3653 pa_debug.G_Err_Stage := 'Starting Get_Rule_Attributes';
3654 IF g1_debug_mode = 'Y' THEN
3655 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3656 END IF;
3657
3658 For i in 1 .. G_Array_Size
3659 Loop
3660 IF (p_compute_flag(i) = 'Y' and x_error_code(i) is null) THEN
3661 pa_debug.G_Err_Stage := 'Fetching Get_Rule_Attributes';
3662 pa_debug.G_Err_Stage := 'Rule ID is '|| to_char(p_tp_rule_id(i));
3663
3664 open c_rule_attributes (p_tp_rule_id(i));
3665 --open c_rule_attributes (3342);
3666 fetch c_rule_attributes
3667 into x_calc_method_code(i),x_cc_markup_base_code(i),x_rule_percentage(i),
3668 x_schedule_id(i);
3669 close c_rule_attributes;
3670 END IF;
3671
3672 END Loop;
3673
3674
3675 pa_debug.Reset_err_stack;
3676 pa_debug.G_Err_Stage := 'Exitting Get_Rule_Attributes';
3677 IF g1_debug_mode = 'Y' THEN
3678 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3679 END IF;
3680
3681 EXCEPTION
3682
3683 WHEN OTHERS THEN
3684 raise;
3685 END Get_Rule_Attributes;
3686 -------------------------------------------------------------------------------
3687 -- Validate each transaction, set base amount, calculate Burdened Amount
3688 -- if actual burdened amount is not given. Also, set the Basis_Compute_Flag,
3689 -- Bill_Rate_Compute_Flag and Burden_Rate_Compute_flag appropriately
3690 -- using calc_method_code
3691
3692 PROCEDURE Set_Base_Amount_And_Flag(
3693 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3694 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3695 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3696 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3697 P_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3698 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3699 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3700 p_calc_method_code IN PA_PLSQL_DATATYPES.Char1TabTyp,
3701 p_cc_markup_base_code IN PA_PLSQL_DATATYPES.Char1TabTyp,
3702 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3703 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3704 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3705 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3706 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3707 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3708 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3709 p_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3710 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
3711 x_basis_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3712 x_bill_rate_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3713 x_burden_rate_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3714 x_tp_base_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3715 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3716 /*Bill rate Discount */
3717 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
3718 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3719 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.NumTabTyp,
3720 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
3721 p_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3722 p_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3723 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
3724 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3725 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3726 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3727 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3728 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3729 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
3730 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3731 p_raw_cost_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
3732 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3733 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3734 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3735 p_exp_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3736 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
3737 p_sys_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
3738 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3739
3740 p_burden_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3741 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3742 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3743 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3744 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3745 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
3746 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3747 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3748 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3749 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3750 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3751 /* Added the two parameters for Doosan rate api enhancement */
3752 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3753 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3754 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
3755 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
3756 )
3757
3758 IS
3759 l_base_amount NUMBER;
3760 l_burden_cost Number;
3761 l_denom_burdened_cost_amount Number;
3762 l_burdening_allowed VARCHAR2(1);
3763 l_burden_amt_display_method VARCHAR2(1);
3764 l_error_code VARCHAR2(30);
3765 l_status_code Number;
3766 l_stage Number;
3767 l_fixed_date Date;
3768 l_compiled_multiplier Number;
3769 l_compiled_set_id NUMBER;
3770 l_check_line NUMBER; /* 2469987 */
3771
3772 l_burden_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp; /* 2215942 */
3773 l_burden_calc_amount PA_PLSQL_DATATYPES.NumTabTyp; /* 2215942 */
3774 l_burden_error_code PA_PLSQL_DATATYPES.Char30TabTyp; /* 2215942 */
3775 l_rate_schedule_id PA_PLSQL_DATATYPES.IdTabTyp; /* 2215942 */
3776 l_tp_ind_compiled_set_id PA_PLSQL_DATATYPES.IdTabTyp; /* 2215942 */
3777
3778 l_exp_func_curr_code VARCHAR2(30);
3779 l_sl_function NUMBER;
3780
3781 l_bill_rate NUMBER;
3782 l_adjusted_bill_rate NUMBER; --4038485
3783 l_markup_percentage NUMBER;
3784 l_rev_currency_code VARCHAR2(30);
3785 l_return_status varchar2(240);
3786 l_msg_count NUMBER;
3787 l_msg_data VARCHAR2(240);
3788 l_raw_cost_rate NUMBER;
3789
3790 /* Added for bug 2668753 */
3791 l_project_currency_code varchar2(50) := null;
3792 l_project_raw_cost number := null;
3793 l_project_burdened_cost number := null;
3794 l_proj_func_burdened_cost number := null;
3795 l_exp_func_burdened_cost number := null;
3796
3797 /* Added for bug 2697945 */
3798 l_bill_trans_raw_revenue number := null;
3799 l_bill_trans_currency_code varchar2(50) := null;
3800 /* Added for bug 2820252 */
3801 l_bill_trans_adjusted_revenue number := null;
3802 exp_not_found exception;
3803
3804 l_dist_rule BOOLEAN :=TRUE;/*Added for bug 2863350*/
3805
3806 cursor PROJ_VALUES (p_expenditure_item_id IN NUMBER) IS
3807 select project_raw_cost,
3808 project_currency_code,
3809 project_burdened_cost,
3810 burden_cost,
3811 acct_burdened_cost,
3812 /* Added for bug 2697945 */
3813 bill_trans_raw_revenue,
3814 bill_trans_currency_code,
3815 /* Added for bug 2820252 */
3816 bill_trans_adjusted_revenue
3817 from pa_expenditure_items_all where expenditure_item_id=p_expenditure_item_id;
3818 /* End of Changes for bug 2668753 */
3819 l_nl_bill_rate NUMBER;
3820 l_nl_adjusted_bill_rate NUMBER;--4038485
3821 l_nl_markup_percentage NUMBER;
3822 BEGIN
3823 pa_debug.Set_err_stack ('Set_Base_Amount_And_Flag');
3824 pa_debug.G_Err_Stage := 'Starting Set_Base_Amount_And_Flag';
3825 IF g1_debug_mode = 'Y' THEN
3826 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3827 END IF;
3828
3829 For i in 1 .. G_Array_Size
3830 Loop
3831 -- Initialize the flags
3832
3833 x_basis_compute_flag(i) := 'N';
3834 x_bill_rate_compute_flag(i) := 'N';
3835 x_burden_rate_compute_flag(i) := 'N';
3836
3837 /* Added for bug 2668753 */
3838 /* For Information-- This piece of code added for bug 2668753 might result in performance issues */
3839
3840 l_project_currency_code := null;
3841 l_project_raw_cost := null;
3842 l_project_burdened_cost := null;
3843 l_proj_func_burdened_cost := null;
3844 l_exp_func_burdened_cost := null;
3845 /* Added for bug 2697945 */
3846
3847 l_bill_trans_raw_revenue := null;
3848 l_bill_trans_currency_code := null;
3849
3850 /* Added for bug 2820252 */
3851 l_bill_trans_adjusted_revenue := null;
3852
3853 /* IF condition added for Bug 2780325 */
3854
3855 IF (p_mcb_flag.exists(i))THEN
3856
3857 IF ( nvl(p_mcb_flag(i),'N') = 'Y' ) THEN
3858 BEGIN
3859 OPEN PROJ_VALUES(p_expenditure_item_id(i));
3860 -- IF(PROJ_VALUES%FOUND) THEN /* Commented for bug 2697945 */
3861
3862 FETCH proj_values into
3863 l_project_raw_cost,
3864 l_project_currency_code,
3865 l_project_burdened_cost,
3866 l_proj_func_burdened_cost,
3867 l_exp_func_burdened_cost,
3868 l_bill_trans_raw_revenue, --Added for bug 2697945
3869 l_bill_trans_currency_code,
3870 l_bill_trans_adjusted_revenue; --Added for bug 2820252
3871
3872 --END IF;
3873 /* Added for bug 2697945 */
3874
3875 IF(PROJ_VALUES%NOTFOUND) THEN
3876 IF g1_debug_mode = 'Y' THEN
3877 pa_debug.write_file('LOG','No Data Found for the Expenditure Item Id :'||p_expenditure_item_id(i));
3878 END IF;
3879
3880 if PROJ_VALUES%ISOPEN THEN
3881 CLOSE PROJ_VALUES;
3882 end if;
3883
3884 -- EXIT; /* Commented this line and added the following line for bug 2697945 */
3885 RAISE exp_not_found;
3886 END IF;
3887 /* End of Changes done for bug 2697945 */
3888
3889 CLOSE PROJ_VALUES;
3890
3891 EXCEPTION
3892 /* Added EXP_NOT_FOUND for bug 2697945 */
3893
3894 WHEN EXP_NOT_FOUND THEN
3895 if PROJ_VALUES%ISOPEN THEN
3896 CLOSE PROJ_VALUES;
3897 end if;
3898 RAISE;
3899 WHEN OTHERS THEN
3900 if PROJ_VALUES%ISOPEN THEN
3901 CLOSE PROJ_VALUES;
3902 end if;
3903 RAISE;
3904 END;
3905
3906 END IF;
3907
3908 /* End of Changes for bug 2668753 */
3909
3910 END IF; /* 2780325 */
3911 IF (p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL) THEN
3912 l_error_code := null; /** Fixed Bug: 1063455 **/
3913 l_burden_error_code := x_error_code; /* 2215942 */
3914
3915 pa_debug.G_Err_Stage :=
3916 'Processing EI: '||to_char(p_expenditure_item_id(i));
3917 IF g1_debug_mode = 'Y' THEN
3918 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3919 END IF;
3920 IF p_cc_markup_base_code(i) = 'C' THEN
3921 -- Raw Cost
3922
3923 -- Check If Raw Cost amount is Null
3924 IF p_denom_raw_cost_amount(i) is not null THEN
3925 x_tp_base_amount(i) := p_denom_raw_cost_amount(i);
3926 x_tp_base_curr_code(i) := p_denom_currency_code(i);
3927 IF g1_debug_mode = 'Y' THEN
3928 pa_debug.write_file('LOG','Base is raw cost');
3929 END IF;
3930 ELSE
3931 l_error_code := 'PA_CC_TP_RAW_COST_NULL';
3932 END IF;
3933
3934 ELSIF p_cc_markup_base_code(i) = 'R' THEN
3935 -- Raw Revenue
3936 IF g1_debug_mode = 'Y' THEN
3937 pa_debug.write_file('LOG','Base is raw rev');
3938 END IF;
3939 /*The below block is added for bug 2863550 */
3940 IF(p_dist_rule.exists(i)) THEN
3941 IF (substr(p_dist_rule(i),1,4) = 'WORK') THEN
3942 l_dist_rule:=TRUE;
3943 ELSE
3944 l_dist_rule :=FALSE;
3945 END IF;
3946 ELSE
3947 l_dist_rule :=TRUE;
3948 END IF;
3949 -- Check If revenue amount is distributed
3950 IF p_revenue_distributed_flag(i) IN ('Y','P') AND l_dist_rule THEN /*l_dist_rule added for 2863550*/
3951 /*substr(p_dist_rule(i),1,4) = 'WORK' THEN*//*Added P for bug 2636678 and added p_dist_rule for bug 2663736*/
3952 -- Check If Revenue amount is Null
3953 IF p_raw_revenue_amount(i) IS NOT NULL THEN
3954 /* Commented the following two lines for bug 2697945 and added the next two lines as part of fix */
3955
3956 /* x_tp_base_amount(i) := p_raw_revenue_amount(i);
3957 x_tp_base_curr_code(i) := p_projfunc_currency_code(i); */
3958
3959 /* Changed the following two lines for bug 2696945 */
3960
3961 x_tp_base_amount(i) := nvl(l_bill_trans_adjusted_revenue,nvl(l_bill_trans_raw_revenue,p_raw_revenue_amount(i)));
3962 /* Changed the above line for bug 2820252 */
3963 x_tp_base_curr_code(i) := nvl(l_bill_trans_currency_code,p_projfunc_currency_code(i));
3964
3965 ELSE
3966 /* Code Added for Bug#2469987 -- Start */
3967 BEGIN
3968 SELECT 1 INTO l_check_line
3969 FROM DUAL
3970 WHERE EXISTS (
3971 SELECT 1
3972 FROM pa_cust_rev_dist_lines_all
3973 WHERE expenditure_item_id = p_expenditure_item_id(i)
3974 UNION ALL
3975 SELECT 1
3976 FROM pa_cc_dist_lines_all
3977 WHERE expenditure_item_id = p_expenditure_item_id(i));
3978
3979 x_tp_base_amount(i) := 0;
3980 x_tp_base_curr_code(i) := p_projfunc_currency_code(i);
3981
3982 EXCEPTION
3983 WHEN NO_DATA_FOUND THEN
3984 x_tp_base_amount(i) := 0;
3985 x_tp_base_curr_code(i) := p_projfunc_currency_code(i);
3986 WHEN OTHERS THEN
3987 l_error_code := 'PA_CC_TP_REV_AMT_NULL';
3988 END;
3989 END IF; /* Revenue amount is not null */
3990 /* Code Added for Bug#2469987 -- End */
3991 ELSE
3992 IF g1_debug_mode = 'Y' THEN
3993 pa_debug.write_file('LOG',
3994 'Rev flag is '||p_revenue_distributed_flag(i));
3995 END IF;
3996 /* Added for bill rate disount and transfer price revenue*/
3997 IF (p_sys_linkage_function(i) in ('ST','OT')) then
3998 IF g1_debug_mode = 'Y' THEN
3999 pa_debug.write_file('LOG','within Assignment_Rev_Amt');
4000 END IF;
4001 pa_revenue.Assignment_Rev_Amt(
4002 p_project_id => P_project_id(i)
4003 ,p_task_id => P_task_id(i)
4004 ,p_item_date => P_expenditure_item_date(i)
4005 ,p_item_id => p_assignment_id(i)
4006 ,p_bill_rate_multiplier => p_bill_rate_multiplier(i)
4007 ,p_quantity => p_quantity(i)
4008 ,p_person_id => p_person_id(i)
4009 ,p_raw_cost => p_raw_cost(i)
4010 /* bug#3221791 added to_number */
4011 ,p_labor_schdl_discnt => to_number(p_labor_schdl_discnt(i))
4012 ,p_labor_bill_rate_org_id => NULL
4013 ,p_labor_std_bill_rate_schdl => NULL
4014 ,p_labor_schdl_fixed_date => p_labor_schdl_fixed_date(i)
4015 ,p_bill_job_grp_id => p_bill_job_grp_id(i)
4016 ,p_labor_sch_type => p_labor_sch_type(i)
4017 ,p_project_org_id => p_project_org_id(i)
4018 ,p_project_type => p_project_type(i)
4019 ,p_expenditure_type => p_expenditure_type(i)
4020 ,p_exp_func_curr_code => p_exp_func_curr_code(i)
4021 ,p_incurred_by_organz_id => p_incurred_by_organz_id(i)
4022 ,p_raw_cost_rate => p_raw_cost_rate(i)
4023 ,p_override_to_organz_id => p_override_to_organz_id(i)
4024 ,p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id(i)
4025 ,p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id(i)
4026 ,p_resource_job_id => NULL
4027 ,p_exp_raw_cost => p_exp_raw_cost(i)
4028 ,p_expenditure_org_id => p_expnd_organization_id(i)
4029 ,p_projfunc_currency_code => p_projfunc_currency_code(i)
4030 ,p_assignment_precedes_task => p_assignment_precedes_task(i)
4031 ,p_sys_linkage_function => p_sys_linkage_function(i)
4032 ,x_bill_rate => l_bill_rate
4033 ,x_raw_revenue => x_tp_base_amount(i)
4034 ,x_txn_currency_code => x_tp_base_Curr_code(i)
4035 ,x_rev_currency_code => l_rev_currency_code
4036 ,x_markup_percentage => l_markup_percentage
4037 ,x_return_status => l_return_status
4038 ,x_msg_count => l_msg_count
4039 ,x_msg_data => l_msg_data
4040 ,p_mcb_flag => p_mcb_flag(i)
4041 ,p_denom_raw_cost => p_denom_raw_cost_amount(i)
4042 ,p_denom_curr_code => p_denom_currency_code(i)
4043 ,p_called_process => 'PA'
4044 /* Added for bug 2668753 */
4045 ,p_project_raw_cost => l_project_raw_cost
4046 ,p_project_currency_code => l_project_currency_code
4047 ,x_adjusted_bill_rate => l_adjusted_bill_rate);--4038485
4048 IF g1_debug_mode = 'Y' THEN
4049 pa_debug.write_file('LOG','completed Assignment_Rev_Amt');
4050 END IF;
4051
4052 ELSE
4053 IF g1_debug_mode = 'Y' THEN
4054 pa_debug.write_file('LOG','Non Labor Revenue Amount');
4055 END IF;
4056 l_exp_func_curr_code := p_exp_func_curr_code(i);
4057 select decode(p_sys_linkage_function(i),'BTC',6,2)
4058 into l_sl_function
4059 from dual;
4060
4061 IF p_raw_cost_rate(i) IS NULL THEN
4062 SELECT DECODE(nvl(COST_RATE_FLAG,'N'),'N',1,NULL)
4063 INTO l_raw_cost_rate
4064 FROM PA_EXPENDITURE_TYPES
4065 where EXPENDITURE_TYPE = p_expenditure_type(i);
4066 ELSE
4067 l_raw_cost_rate := p_raw_cost_rate(i);
4068 END IF;
4069 pa_revenue.Non_Labor_Rev_amount(
4070 p_project_id => p_project_id(i),
4071 p_task_id => p_task_id(i),
4072 p_bill_rate_multiplier => p_bill_rate_multiplier(i),
4073 p_quantity => p_quantity(i),
4074 p_raw_cost => p_raw_cost(i),
4075 p_burden_cost => p_burden_cost(i),
4076 p_denom_raw_cost => p_denom_raw_cost_amount(i),
4077 p_denom_burdened_cost => p_denom_burdened_cost_amount(i),
4078 p_expenditure_item_date => p_expenditure_item_date(i),
4079 p_task_bill_rate_org_id => p_task_nl_bill_rate_org_id(i),
4080 p_project_bill_rate_org_id => p_proj_nl_bill_rate_org_id(i),
4081 p_task_std_bill_rate_sch => p_task_nl_std_bill_rate_sch(i),
4082 p_project_std_bill_rate_sch => p_proj_nl_std_bill_rate_sch(i),
4083 p_project_org_id => p_project_org_id(i),
4084 p_sl_function => l_sl_function,
4085 p_denom_currency_code => p_denom_currency_code(i),
4086 p_proj_func_currency => p_projfunc_currency_code(i),
4087 p_expenditure_type => p_expenditure_type(i),
4088 p_non_labor_resource => p_non_labor_resource(i),
4089 p_task_sch_date => p_nl_task_sch_date(i),
4090 p_project_sch_date => p_nl_proj_sch_date(i),
4091 p_project_sch_discount => p_nl_proj_sch_discount(i),
4092 p_task_sch_discount => p_nl_task_sch_discount(i),
4093 p_mcb_flag => p_mcb_flag(i),
4094 p_non_labor_sch_type => p_nl_sch_type(i),
4095 p_project_type => p_project_type(i),
4096 p_exp_raw_cost => p_exp_raw_cost(i),
4097 p_raw_cost_rate => l_raw_cost_rate,
4098 p_incurred_by_organz_id => p_incurred_by_organz_id(i),
4099 p_override_to_organz_id => p_override_to_organz_id(i),
4100 px_exp_func_curr_code => l_exp_func_curr_code,
4101 x_raw_revenue => x_tp_base_amount(i),
4102 x_rev_curr_code => x_tp_base_Curr_code(i),
4103 x_return_status => l_return_status,
4104 x_msg_count => l_msg_count,
4105 x_msg_data => l_msg_data,
4106 /* Added for bug 2668753 */
4107 p_project_raw_cost => l_project_raw_cost,
4108 p_project_currency_code => l_project_currency_code,
4109 p_project_burdened_cost => l_project_burdened_cost,
4110 p_proj_func_burdened_cost => l_proj_func_burdened_cost,
4111 p_exp_func_burdened_cost => l_exp_func_burdened_cost,
4112 p_task_nl_std_bill_rate_sch_id => p_task_nl_std_bill_rate_sch_id(i),
4113 p_proj_nl_std_bill_rate_sch_id => p_proj_nl_std_bill_rate_sch_id(i),
4114 x_bill_rate => l_nl_bill_rate,
4115 x_markup_percentage => l_nl_markup_percentage,
4116 x_adjusted_bill_rate => l_nl_adjusted_bill_rate,--4038485
4117 p_uom_flag => p_uom_flag(i));
4118
4119 IF g1_debug_mode = 'Y' THEN
4120 pa_debug.write_file('LOG','Completed Non Labor Revenue Amount');
4121 END IF;
4122 END IF;
4123 IF g1_debug_mode = 'Y' THEN
4124 pa_debug.write_file('LOG','x_base revenue amount' || x_tp_base_amount(i) || 'code : ' ||x_tp_base_curr_code(i));
4125 END IF;
4126 IF l_msg_data is NULL then
4127 IF x_tp_base_amount(i) is NULL THEN
4128 l_error_code := 'PA_CC_TP_REV_AMT_NULL';
4129 END IF;
4130 ELSE
4131 l_error_code := l_msg_data;
4132 END IF;
4133 /* Added for bill rate disount and transfer price revenue*/
4134 END IF;
4135 ELSIF p_cc_markup_base_code(i) = 'B' THEN
4136 -- Burdened Cost
4137 l_denom_burdened_cost_amount := p_denom_burdened_cost_amount(i);
4138 IF (l_denom_burdened_cost_amount IS NULL) THEN
4139 -- Check if Burden cost is null
4140 l_error_code := 'PA_CC_TP_BURDN_COST_NULL';
4141 ELSE
4142 -- Check if burden cost needs to be recalculated
4143 IF (p_denom_burdened_cost_amount(i) = p_denom_raw_cost_amount(i))
4144 THEN
4145
4146 Get_Burdening_Details(p_project_id (i),
4147 l_burdening_allowed,
4148 l_burden_amt_display_method
4149 );
4150
4151 IF g1_debug_mode = 'Y' THEN
4152 pa_debug.write_file('LOG','Project_id is: '
4153 ||to_char(p_project_id(i)));
4154
4155 pa_debug.write_file('LOG','Burdening flag is : '
4156 ||l_burdening_allowed);
4157
4158 pa_debug.write_file('LOG','burden amount allowed flag is: '
4159 ||l_burden_amt_display_method);
4160 END IF;
4161
4162 -- Check if project allows burdening
4163 IF (l_burdening_allowed = 'Y' ) THEN
4164 -- Check if burden amount is displayed on separate transaction
4165
4166 IF l_burden_amt_display_method = 'D' THEN
4167 -- calculate the correct burden_amount
4168
4169 l_fixed_date := p_fixed_date(i);
4170
4171 IF p_fixed_date(i) is NULL THEN
4172 l_fixed_date := p_expenditure_item_date(i);
4173 END IF;
4174 /**
4175
4176 PA_COST_PLUS.view_indirect_cost (
4177 transaction_id => p_expenditure_item_id(i),
4178 transaction_type => 'PA',
4179 task_id => p_task_id(i),
4180 effective_date => l_fixed_date,
4181 expenditure_type => p_expenditure_type(i),
4182 organization_id => p_expnd_organization_id(i),
4183 schedule_type => 'C',
4184 direct_cost => p_denom_raw_cost_amount(i),
4185 indirect_cost => l_denom_burdened_cost_amount,
4186 status => l_status_code,
4187 stage => l_stage
4188 );
4189 **/
4190 -- Get the multiplier
4191 /* Added the declare sction for the bug#2215942 */
4192
4193 DECLARE
4194 l_burden_sch_rev_id Number;
4195 l_Stage Number;
4196 l_Status Number;
4197 l_burden_calc_amount_l number;
4198 l_tp_ind_compiled_set_id_l Number;
4199 t_rate_sch_rev_id number; /* bug#3117191 */
4200 t_sch_fixed_date date; /* bug#3117191 */
4201
4202
4203 BEGIN
4204 /*Bug 1729820 */
4205 /* commented for the bug#2215942, starts here */
4206 /*
4207 SELECT cost_ind_compiled_set_id
4208 INTO l_compiled_set_id
4209 FROM pa_expenditure_items_all
4210 WHERE expenditure_item_id = p_expenditure_item_id(i);
4211 */
4212 /* commented for the bug#2215942, ends here */
4213 /* Changes ends for bug 1729820 */
4214 /* commented for the bug#2215942, starts here */
4215 /*
4216
4217 l_compiled_multiplier :=
4218 pa_cost_plus.Get_Mltplr_For_Compiled_Set
4219 (l_compiled_set_id);
4220 l_denom_burdened_cost_amount :=
4221 p_denom_raw_cost_amount(i)* (1+l_compiled_multiplier);
4222 */
4223 /* commented for the bug#2215942, ends here */
4224
4225 /* Code added for the bug 2215942, starts here */
4226
4227 x_burden_rate_compute_flag(i) := 'Y';
4228
4229 /* added for bug#3117191 */
4230 PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
4231 'ACTUAL',
4232 p_expenditure_item_id(i), -- Transaction Item Id
4233 'PA', -- Transaction Type
4234 p_task_id(i), -- Task Id
4235 'C', -- Schedule Type
4236 p_expenditure_item_date(i), -- EI Date
4237 t_sch_fixed_date, -- Sch_fixed_date (Out)
4238 t_rate_sch_rev_id, -- Rate_sch_rev_id (Out)
4239 l_status); -- Status (Out)
4240
4241 /* Begin bug 5169080 */
4242 if (nvl(l_status , 0 ) <> 0) THEN
4243 l_error_code := 'PA_CC_TP_ERROR_BURDEN_CALC';
4244 end if;
4245 /* End bug 5169080 */
4246
4247
4248 IF (t_rate_sch_rev_id IS NOT NULL) THEN
4249 l_burden_sch_rev_id := t_rate_sch_rev_id;
4250 PA_COST_PLUS.Get_Burden_Amount1(
4251 p_expenditure_type(i),
4252 p_expnd_organization_id(i),
4253 p_denom_raw_cost_amount(i),
4254 l_burden_calc_amount_l,
4255 l_burden_sch_rev_id,
4256 l_tp_ind_compiled_set_id_l,
4257 l_status,
4258 l_stage
4259 );
4260 /* end for bug#3117191 */
4261 ELSE /* bug#3117191 */
4262
4263 /* get the task level burden schedule id by considering the task level overrides */
4264 select NVL(OVR_COST_IND_RATE_SCH_ID, COST_IND_RATE_SCH_ID)
4265 into l_rate_schedule_id(i)
4266 from pa_tasks
4267 where task_id in
4268 ( select task_id
4269 from pa_expenditure_items_all
4270 where expenditure_item_id = p_expenditure_item_id(i)
4271 );
4272 /* Get the burden amount from the call to the procedure PA_COST_PLUS.Get_Burden_Amount,
4273 which gets the revision for the given burden schedule, then burden structure,
4274 then cost base from the burden structure corresponding to the expenditure type,
4275 then sum of the compiled multipliers */
4276
4277 PA_COST_PLUS.Get_Burden_Amount(
4278 l_rate_schedule_id(i),
4279 p_expenditure_item_date(i),
4280 p_expenditure_type(i),
4281 p_expnd_organization_id(i),
4282 p_denom_raw_cost_amount(i),
4283 l_burden_calc_amount_l,
4284 l_burden_sch_rev_id,
4285 l_tp_ind_compiled_set_id_l,
4286 l_status,
4287 l_stage
4288 );
4289 END IF; /* bug#3117191 */
4290
4291 l_burden_calc_amount(i) := l_burden_calc_amount_l;
4292 l_tp_ind_compiled_set_id(i) := l_tp_ind_compiled_set_id_l;
4293
4294
4295 l_denom_burdened_cost_amount :=
4296 p_denom_raw_cost_amount(i)+l_burden_calc_amount(i);
4297
4298 /* Code added for the bug 2215942, ends here */
4299
4300
4301 EXCEPTION
4302 when no_data_found then
4303
4304 l_error_code := 'PA_CC_TP_ERROR_BURDEN_CALC';
4305 END;
4306
4307
4308 END IF;/** burden_amt_display_method = 'D' **/
4309 END IF; /** l_burdening_allowed **/
4310
4311 END IF;/** p_denom_burdened_cost_amount=p_denom_raw_cost_amount **/
4312 END IF; /** Burden Cost is null **/
4313
4314 IF (l_error_code is null) THEN
4315
4316 x_tp_base_amount(i) := l_denom_burdened_cost_amount;
4317 x_tp_base_curr_code(i) := p_denom_currency_code(i);
4318
4319 END IF;
4320 ELSE
4321 l_error_code := 'PA_CC_TP_INVALID_BASE_CODE';
4322
4323 END IF; /** p_cc_markup_base_code **/
4324
4325 IF (l_error_code IS NULL) THEN
4326 IF g1_debug_mode = 'Y' THEN
4327 pa_debug.write_file('LOG','Base Amount is: '
4328 ||to_char(x_tp_base_amount(i)));
4329 pa_debug.write_file('LOG','Base currency is: '
4330 ||x_tp_base_curr_code(i));
4331 END IF;
4332 -- No error encountered yet
4333 IF p_calc_method_code(i) = 'A' THEN
4334 -- Use Basis
4335 G_Basis_Exists := TRUE;
4336 x_basis_compute_flag(i) := 'Y';
4337 ELSIF p_calc_method_code(i) = 'R' THEN
4338 -- Use Bill Rate Schedule
4339 G_Bill_Rate_Exists := TRUE;
4340 x_bill_rate_compute_flag(i) := 'Y';
4341 ELSIF p_calc_method_code(i) = 'B' THEN
4342 -- Use Burden schedule
4343 G_Burden_Rate_Exists := TRUE;
4344 x_burden_rate_compute_flag(i) := 'Y';
4345 END IF;
4346 ELSE
4347 x_error_code(i) := l_error_code;
4348
4349 END IF;
4350
4351
4352
4353 END IF;/** p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL **/
4354
4355 END LOOP;
4356
4357 pa_debug.Reset_err_stack;
4358 pa_debug.G_Err_Stage := 'Exitting Set_Base_Amount_And_Flag';
4359 IF g1_debug_mode = 'Y' THEN
4360 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4361 END IF;
4362
4363 EXCEPTION
4364
4365 WHEN OTHERS THEN
4366 raise;
4367
4368 END Set_Base_Amount_And_Flag;
4369 --------------------------------------------------------------------------------
4370 Procedure Get_Burdening_Details(p_project_id IN NUMBER,
4371 x_burdening_allowed OUT NOCOPY VARCHAR2, /*File.sql.39*/
4372 x_burden_amt_display_method OUT NOCOPY VARCHAR2 /*File.sql.39*/
4373 )
4374 IS
4375 /* Bug 1729820 _ Changed to pa_projects_all and pa_project_types_all */
4376
4377 Cursor c_burdening_details
4378 IS
4379 select type.burden_cost_flag,type.burden_amt_display_method
4380 from pa_projects_all proj ,pa_project_types_all type
4381 where proj.project_id = p_project_id
4382 and proj.project_type = type.project_type
4383 and proj.org_id = type.org_id; /** Added this condition while making changes for Org Forecasting **/
4384 BEGIN
4385 pa_debug.Set_err_stack ('Get_Burdening_Details');
4386 pa_debug.G_Err_Stage := 'Starting Get_Burdening_Details';
4387 IF g1_debug_mode = 'Y' THEN
4388 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4389 END IF;
4390
4391 open c_burdening_details;
4392 fetch c_burdening_details
4393 into x_burdening_allowed,x_burden_amt_display_method;
4394 close c_burdening_details;
4395
4396 pa_debug.Reset_err_stack;
4397 pa_debug.G_Err_Stage := 'Exitting Get_Burdening_Details';
4398 IF g1_debug_mode = 'Y' THEN
4399 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4400 END IF;
4401
4402 EXCEPTION
4403
4404 WHEN OTHERS THEN
4405 raise;
4406
4407 END Get_Burdening_Details;
4408 --------------------------------------------------------------------------------
4409
4410 PROCEDURE Determine_Transfer_Price
4411 (
4412 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4413 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4414 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
4415 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4416 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4417 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
4418 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4419 p_tp_base_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4420 p_tp_base_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4421 p_tp_schedule_line_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
4422 p_tp_rule_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
4423 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4424 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
4425 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4426 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4427 p_rate_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4428 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
4429 p_basis_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4430 p_bill_rate_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4431 p_burden_rate_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4432 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4433 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4434 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4435 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4436 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4437 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4438 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4439 )
4440 IS
4441
4442 l_basis_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4443 l_basis_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4444 l_basis_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4445 l_bill_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4446 l_bill_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4447 l_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
4448 l_tp_bill_markup_percentage PA_PLSQL_DATATYPES.NumTabTyp;
4449 l_bill_error_stage VARCHAR2(80);
4450 l_bill_reject_cnt Number;
4451 l_bill_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4452 l_burden_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4453 l_burden_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4454 l_burden_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4455 l_exp_uom PA_PLSQL_DATATYPES.Char30TabTyp;
4456 l_bill_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
4457 l_burden_status NUMBER;
4458 l_burden_stage NUMBER;
4459 l_temp_transfer_price NUMBER;
4460
4461
4462 BEGIN
4463 pa_debug.Set_err_stack ('Determine_Transfer_Price');
4464 pa_debug.G_Err_Stage := 'Starting Determine_Transfer_Price';
4465 IF g1_debug_mode = 'Y' THEN
4466 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4467 END IF;
4468 l_bill_rate_compute_flag := p_bill_rate_compute_flag;
4469
4470 l_basis_error_code := x_error_code;
4471 l_burden_error_code := x_error_code;
4472 l_bill_error_code := x_error_code;
4473
4474 IF G_Basis_Exists THEN
4475 IF g1_debug_mode = 'Y' THEN
4476 pa_debug.write_file('LOG','Using Basis');
4477 END IF;
4478 Get_Basis_Amount(
4479 p_tp_base_curr_code => p_tp_base_curr_code,
4480 p_tp_base_amount => p_tp_base_amount,
4481 p_compute_flag => p_basis_compute_flag,
4482 p_array_size => G_array_size,
4483 x_denom_tp_curr_code => l_basis_calc_curr_code,
4484 x_amount => l_basis_calc_amount,
4485 x_error_code => l_basis_error_code
4486 );
4487 END IF;
4488
4489 IF G_Bill_Rate_Exists THEN
4490 -- Call Bill rate API
4491 IF g1_debug_mode = 'Y' THEN
4492 pa_debug.write_file('LOG','Using Bill Rate');
4493 END IF;
4494
4495 pa_bill_schedule.get_computed_bill_rate(
4496 p_array_size => G_Array_Size,
4497 p_bill_rate_sch_id => p_rate_schedule_id,
4498 p_expenditure_item_id => p_expenditure_item_id,
4499 p_exp_sys_linkage => p_system_linkage_function,
4500 p_expenditure_type => p_expenditure_type,
4501 p_expenditure_item_date => p_expenditure_item_date,
4502 p_fixed_date => p_fixed_date,
4503 p_quantity => p_quantity,
4504 p_incurred_by_person_id => p_incurred_by_person_id,
4505 p_non_labor_resource => p_non_labor_resource,
4506 p_base_curr => p_tp_base_curr_code,
4507 p_base_amt => p_tp_base_amount,
4508 p_exp_uom =>l_exp_uom ,
4509 p_compute_flag => l_bill_rate_compute_flag,
4510 x_error_code => l_bill_error_code,
4511 x_reject_cnt => l_bill_reject_cnt,
4512 x_computed_rate => l_tp_bill_rate,
4513 x_computed_markup => l_tp_bill_markup_percentage,
4514 x_computed_currency => l_bill_calc_curr_code,
4515 x_computed_amount => l_bill_calc_amount,
4516 x_tp_job_id => x_tp_job_id,
4517 x_error_stage => l_bill_error_stage
4518 );
4519 END IF;
4520
4521
4522 IF G_Burden_Rate_Exists THEN
4523 -- Call Burden rate API
4524 IF g1_debug_mode = 'Y' THEN
4525 pa_debug.write_file('LOG','Using Burden schedule');
4526 END IF;
4527 get_burden_amount(
4528 p_array_size => G_array_size,
4529 p_burden_schedule_id => p_rate_schedule_id,
4530 p_expenditure_item_date => p_expenditure_item_date,
4531 p_fixed_date => p_fixed_date,
4532 p_expenditure_type => p_expenditure_type,
4533 p_organization_id => p_expnd_organization_id,
4534 p_raw_amount_curr_code => p_tp_base_curr_code,
4535 p_raw_amount => p_tp_base_amount ,
4536 p_compute_flag => p_burden_rate_compute_flag,
4537 x_computed_currency => l_burden_calc_curr_code,
4538 x_burden_amount => l_burden_calc_amount,
4539 x_compiled_set_id => x_tp_ind_compiled_set_id,
4540 x_error_code => l_burden_error_code
4541 );
4542 END IF;
4543
4544 -- Now set the out parameters transfer price ,denom transfer price currency
4545 -- code and error code.
4546 For i in 1 .. G_Array_Size
4547 Loop
4548 IF (p_compute_flag(i) = 'Y' and x_error_code(i) is null) THEN
4549
4550 IF (p_basis_compute_flag(i) = 'Y') THEN
4551
4552 IF ((l_basis_error_code.exists(i)
4553 AND l_basis_error_code(i) IS NOT NULL)) THEN
4554 x_error_code(i) := l_basis_error_code(i);
4555 ELSE
4556 x_denom_tp_currency_code(i) := l_basis_calc_curr_code(i);
4557 l_temp_transfer_price := l_basis_calc_amount(i)*
4558 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4559 * (NVL(p_tp_rule_percentage(i),100)/100);
4560 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4561 (l_temp_transfer_price,
4562 x_denom_tp_currency_code(i));
4563 END IF; /** Checking error code **/
4564
4565 ELSIF (p_bill_rate_compute_flag(i) = 'Y') THEN
4566
4567 IF (l_bill_error_code.exists(i)
4568 AND l_bill_error_code(i) IS NOT NULL) THEN
4569 x_error_code(i) := l_bill_error_code(i);
4570 ELSE
4571 x_tp_bill_rate(i) := l_tp_bill_rate(i);
4572 x_tp_bill_markup_percentage(i) := l_tp_bill_markup_percentage(i);
4573 x_denom_tp_currency_code(i) := l_bill_calc_curr_code(i);
4574 l_temp_transfer_price := l_bill_calc_amount(i)*
4575 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4576 * (NVL(p_tp_rule_percentage(i),100)/100);
4577 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4578 (l_temp_transfer_price,
4579 x_denom_tp_currency_code(i));
4580 END IF;
4581
4582 ELSIF (p_burden_rate_compute_flag(i) = 'Y') THEN
4583
4584 IF (l_burden_error_code.exists(i)
4585 AND l_burden_error_code(i) IS NOT NULL ) THEN
4586 x_error_code(i) := l_burden_error_code(i);
4587 ELSE
4588
4589 x_denom_tp_currency_code(i) := l_burden_calc_curr_code(i);
4590 l_temp_transfer_price := l_burden_calc_amount(i)*
4591 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4592 * (NVL(p_tp_rule_percentage(i),100)/100);
4593 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4594 (l_temp_transfer_price,
4595 x_denom_tp_currency_code(i));
4596 END IF;
4597 END IF;
4598
4599 END IF; /** p_compute_flag(i) = 'Y' and x_error_code(i) is null **/
4600
4601 END Loop;
4602
4603 pa_debug.Reset_err_stack;
4604 pa_debug.G_Err_Stage := 'Exitting Determine_transfer_price';
4605 IF g1_debug_mode = 'Y' THEN
4606 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4607 END IF;
4608 EXCEPTION
4609
4610 WHEN OTHERS THEN
4611 raise;
4612 END Determine_transfer_Price;
4613 --------------------------------------------------------------------------------
4614 PROCEDURE Get_Basis_Amount(
4615 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4616 p_tp_base_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4617 p_tp_base_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4618 p_array_size IN Number,
4619 x_denom_tp_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4620 x_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4621 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4622 )
4623 IS
4624 BEGIN
4625 pa_debug.Set_err_stack ('Get_Basis_Amount');
4626 pa_debug.G_Err_Stage := 'Starting Get_Basis_Amount';
4627 IF g1_debug_mode = 'Y' THEN
4628 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4629 END IF;
4630 For i in 1 .. p_Array_Size
4631 Loop
4632 IF (p_compute_flag(i) = 'Y' AND x_error_code(i) IS NULL) THEN
4633 pa_debug.G_Err_Stage := 'Processing Get_Basis_Amount';
4634 IF g1_debug_mode = 'Y' THEN
4635 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4636 END IF;
4637 IF (p_tp_base_curr_code(i) IS NOT NULL
4638 and p_tp_base_amount(i) IS NOT NULL) THEN
4639 pa_debug.G_Err_Stage:='Setting currency,amount in Get_Basis_Amount';
4640 IF g1_debug_mode = 'Y' THEN
4641 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4642 END IF;
4643 x_denom_tp_curr_code(i) := p_tp_base_curr_code(i);
4644 x_amount(i) := p_tp_base_amount(i);
4645 ELSE
4646 x_error_code(i) := 'PA_CC_TP_BASE_CURR_AMT_NULL';
4647 END IF;
4648 END IF;
4649 End Loop;
4650
4651 pa_debug.Reset_err_stack;
4652 pa_debug.G_Err_Stage := 'Exitting Get_Basis_Amount';
4653 IF g1_debug_mode = 'Y' THEN
4654 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4655 END IF;
4656
4657 EXCEPTION
4658
4659 WHEN OTHERS THEN
4660 raise;
4661 END Get_Basis_Amount;
4662 -------------------------------------------------------------------------------
4663 PROCEDURE Get_Burden_Amount(
4664 p_array_size IN Number,
4665 p_burden_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4666 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4667 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4668 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
4669 p_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4670 p_raw_amount_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4671 p_raw_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4672 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4673 x_computed_currency OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4674 x_burden_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4675 x_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4676 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4677 )
4678 IS
4679
4680 l_status NUMBER;
4681 l_stage NUMBER;
4682 l_burden_sch_rev_id Number;
4683 l_burden_amount Number;
4684 l_effective_date Date;
4685
4686 unexpected_result exception;
4687
4688 BEGIN
4689
4690 pa_debug.Set_err_stack ('Get_Burden_Amount');
4691 pa_debug.G_Err_Stage := 'Starting Get_Burden_Amount';
4692 IF g1_debug_mode = 'Y' THEN
4693 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4694 END IF;
4695
4696 For i in 1 .. p_array_size
4697 LOOP
4698
4699 IF (p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL) THEN
4700
4701 IF p_fixed_date(i) is null THEN
4702 l_effective_date := p_expenditure_item_date(i);
4703 else
4704 l_effective_date := p_fixed_date(i);
4705 END IF;
4706
4707 IF g1_debug_mode = 'Y' THEN
4708 pa_debug.write_file('LOG','Burden Schedule ID: '
4709 ||to_char(p_burden_schedule_id(i)));
4710 pa_debug.write_file('LOG','Effective Date: '
4711 ||to_char(l_effective_date));
4712 pa_debug.write_file('LOG','Expenditure Type: '
4713 ||p_expenditure_type(i));
4714 pa_debug.write_file('LOG','Expenditure Organization ID: '
4715 ||to_char(p_organization_id(i)));
4716 pa_debug.write_file('LOG','Raw Amount IS : '
4717 ||to_char(p_raw_amount(i)));
4718 END IF;
4719
4720 PA_COST_PLUS.Get_Burden_Amount(
4721 p_burden_schedule_id(i),
4722 l_effective_date,
4723 p_expenditure_type(i),
4724 p_organization_id(i),
4725 p_raw_amount(i),
4726 l_burden_amount,
4727 l_burden_sch_rev_id,
4728 x_compiled_set_id(i),
4729 l_status,
4730 l_stage
4731 );
4732
4733
4734 IF l_status = 0 THEN
4735 x_computed_currency(i) := p_raw_amount_curr_code(i);
4736 x_burden_amount(i) := l_burden_amount;
4737 IF g1_debug_mode = 'Y' THEN
4738 pa_debug.write_file('LOG','Burden Amount IS : '
4739 ||to_char(l_burden_amount));
4740 pa_debug.write_file('LOG','Burden Schedule Revision ID : '
4741 ||to_char(l_burden_sch_rev_id));
4742 pa_debug.write_file('LOG','Compilede Set ID : '
4743 ||to_char(x_compiled_set_id(i)));
4744 END IF;
4745 ELSIF l_status < 0 THEN
4746 pa_debug.G_Err_Stage := 'Error in PA_COST_PLUS.Get_Burden_Amount';
4747 -- unhandled exception
4748 raise unexpected_result;
4749 ELSIF l_status > 0 THEN
4750 x_error_code(i) := 'PA_CC_TP_ERROR_BURDEN_RATE';
4751 END IF;
4752
4753 END IF; /** (p_compute_flag = 'Y' and x_error_code IS NULL) **/
4754
4755 End Loop;
4756
4757 pa_debug.Reset_err_stack;
4758 pa_debug.G_Err_Stage := 'Exitting Get_Burden_Amount';
4759 IF g1_debug_mode = 'Y' THEN
4760 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4761 END IF;
4762
4763 EXCEPTION
4764
4765 WHEN unexpected_result THEN
4766 raise;
4767
4768 WHEN OTHERS THEN
4769 raise;
4770 END Get_Burden_Amount;
4771 --------------------------------------------------------------------------------
4772
4773
4774 /* Bug 3051110-Added procedure Get_Initial_Transfer_Price for TP Enhancement. */
4775
4776 PROCEDURE Get_Initial_Transfer_Price
4777 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
4778 ,p_start_date IN pa_project_assignments.start_date%TYPE
4779 ,p_debug_mode IN VARCHAR2 DEFAULT 'N'
4780 ,x_transfer_price_rate OUT NOCOPY pa_project_assignments.transfer_price_rate%TYPE /*file.sql.39*/
4781 ,x_transfer_pr_rate_curr OUT NOCOPY pa_project_assignments.transfer_pr_rate_curr%TYPE /*file.sql.39*/
4782 ,x_return_status OUT NOCOPY VARCHAR2 /*file.sql.39*/
4783 ,x_msg_data OUT NOCOPY VARCHAR2 /*file.sql.39*/
4784 ,x_msg_count OUT NOCOPY Number /*file.sql.39*/
4785 )
4786 IS
4787
4788 CURSOR Cur_Forecast_Items(c_assignment_id pa_project_assignments.assignment_id%TYPE,
4789 c_start_date pa_project_assignments.start_date%TYPE) IS SELECT
4790 FI.forecast_item_id,
4791 FI.forecast_item_type,
4792 FI.EXPENDITURE_ORG_ID,
4793 FI.EXPENDITURE_ORGANIZATION_ID,
4794 FI.PROJECT_ORG_ID,
4795 FI.PROJECT_ORGANIZATION_ID,
4796 FI.PROJECT_ID,
4797 FI.PROJECT_TYPE_CLASS,
4798 FI.PERSON_ID,
4799 FI.RESOURCE_ID,
4800 FI.ASSIGNMENT_ID,
4801 FI.ITEM_DATE,
4802 FI.ITEM_UOM,
4803 FI.PVDR_PA_PERIOD_NAME,
4804 FI.RCVR_PA_PERIOD_NAME,
4805 FI.EXPENDITURE_TYPE,
4806 FI.EXPENDITURE_TYPE_CLASS,
4807 FI.Tp_Amount_Type,
4808 FI.Delete_Flag
4809 FROM
4810 Pa_Forecast_Items FI
4811 WHERE FI.Assignment_id = c_assignment_id
4812 AND FI.Error_Flag = 'N'
4813 AND FI.Delete_Flag = 'N'
4814 AND FI.Item_Date = c_start_date;
4815
4816 Cursor FI_Attributes(C_PROJECT_ORG_ID pa_forecasting_options.ORG_ID%TYPE,
4817 C_EXPENDITURE_TYPE pa_expenditure_types.expenditure_type%TYPE,
4818 C_PVDR_PA_PERIOD_NAME Pa_periods_all.PERIOD_NAME%TYPE,
4819 C_EXPENDITURE_ORG_ID pa_forecasting_options.ORG_ID%TYPE)
4820 IS
4821 SELECT
4822 FCST.JOB_COST_RATE_SCHEDULE_ID,
4823 EXP.Expenditure_CATEGORY,
4824 PERIODS.End_Date
4825 FROM
4826 Pa_periods_all PERIODS,
4827 Pa_forecasting_options_all Fcst,
4828 Pa_expenditure_types Exp
4829 WHERE
4830 Exp.Expenditure_type = C_EXPENDITURE_TYPE
4831 AND PERIODS.PERIOD_NAME = C_PVDR_PA_PERIOD_NAME
4832 AND PERIODS.ORG_ID = C_EXPENDITURE_ORG_ID
4833 AND FCST.ORG_ID = C_PROJECT_ORG_ID;
4834
4835 Cursor Proj_Details(c_project_id pa_projects_all.project_id%type) IS
4836 SELECT Project_Type,
4837 DISTRIBUTION_RULE,
4838 BILL_JOB_GROUP_ID,
4839 COST_JOB_GROUP_ID,
4840 JOB_BILL_RATE_SCHEDULE_ID,
4841 EMP_BILL_RATE_SCHEDULE_ID,
4842 PROJECT_CURRENCY_CODE,
4843 PROJECT_RATE_DATE,
4844 PROJECT_RATE_TYPE,
4845 PROJECT_BIL_RATE_DATE_CODE,
4846 PROJECT_BIL_RATE_TYPE,
4847 PROJECT_BIL_RATE_DATE,
4848 PROJECT_BIL_EXCHANGE_RATE,
4849 PROJFUNC_CURRENCY_CODE,
4850 PROJFUNC_COST_RATE_TYPE,
4851 PROJFUNC_COST_RATE_DATE,
4852 PROJFUNC_BIL_RATE_DATE_CODE,
4853 PROJFUNC_BIL_RATE_TYPE,
4854 PROJFUNC_BIL_RATE_DATE,
4855 PROJFUNC_BIL_EXCHANGE_RATE,
4856 LABOR_TP_SCHEDULE_ID,
4857 LABOR_TP_FIXED_DATE,
4858 LABOR_SCHEDULE_DISCOUNT,
4859 NVL(ASSIGN_PRECEDES_TASK, 'N'),
4860 LABOR_BILL_RATE_ORG_ID,
4861 LABOR_STD_BILL_RATE_SCHDL,
4862 LABOR_SCHEDULE_FIXED_DATE,
4863 LABOR_SCH_TYPE
4864 FROM Pa_Projects_All P
4865 WHERE P.Project_Id = c_project_id;
4866
4867 Cursor Proj_Assignment(c_assignment_id pa_project_assignments.assignment_id%type) IS
4868 SELECT Fcst_Job_Id,
4869 Fcst_Job_Group_Id,
4870 Project_Role_Id,
4871 ASSIGNMENT_TYPE,
4872 STATUS_CODE
4873 FROM
4874 PA_PROJECT_ASSIGNMENTS PA
4875 WHERE PA.Assignment_id= c_assignment_id;
4876
4877 l_calling_mode VARCHAR2(20);
4878
4879 l_fi_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
4880 l_fi_item_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4881 l_fi_exp_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4882 l_fi_exp_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4883 l_fi_proj_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4884 l_fi_proj_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4885 l_fi_projid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4886 l_fi_proj_type_class_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4887 l_fi_personid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4888 l_fi_resid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4889 l_fi_asgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4890 l_fi_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4891 l_fi_uom_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4892 l_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
4893 l_fi_pvdr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4894 l_fi_rcvr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4895 l_fi_exptype_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4896 l_fi_exptypeclass_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4897 l_fi_amount_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4898 l_fi_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
4899
4900 l_cc_taskid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4901 l_cc_expitemid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4902 l_cc_transsource_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4903 l_cc_NLOrgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4904 l_cc_prvdreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4905 l_cc_recvreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4906 lx_cc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4907 lx_cc_type_tab PA_PLSQL_DATATYPES.Char3TabTyp;
4908 lx_cc_code_tab PA_PLSQL_DATATYPES.Char1TabTyp;
4909 lx_cc_prvdr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4910 lx_cc_recvr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4911 lx_cc_recvr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4912 lx_cc_prvdr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4913 lx_cc_error_stage VARCHAR2(500);
4914 lx_cc_error_code NUMBER;
4915
4916 /* Project Info */
4917 l_prj_type Pa_Projects_All.Project_Type%TYPE;
4918 l_distribution_rule Pa_Projects_All.Distribution_Rule%TYPE;
4919 l_bill_job_group_id Pa_Projects_All.Bill_Job_Group_Id%TYPE;
4920 l_cost_job_group_id Pa_Projects_All.Cost_Job_Group_Id%TYPE;
4921 l_job_bill_rate_sch_id Pa_Projects_All.JOB_BILL_RATE_SCHEDULE_ID%TYPE;
4922 l_emp_bill_rate_sch_id Pa_Projects_All.EMP_BILL_RATE_SCHEDULE_ID%TYPE;
4923 l_prj_curr_code Pa_Projects_All.PROJECT_CURRENCY_CODE%TYPE;
4924 l_prj_rate_date Pa_Projects_All.PROJECT_RATE_DATE%TYPE;
4925 l_prj_rate_type Pa_Projects_All.PROJECT_RATE_TYPE%TYPE;
4926 l_prj_bil_rate_date_code Pa_Projects_All.PROJECT_BIL_RATE_DATE_CODE%TYPE;
4927 l_prj_bil_rate_type Pa_Projects_All.PROJECT_BIL_RATE_TYPE%TYPE;
4928 l_prj_bil_rate_date Pa_Projects_All.PROJECT_BIL_RATE_DATE%TYPE;
4929 l_prj_bil_ex_rate Pa_Projects_All.PROJECT_BIL_EXCHANGE_RATE%TYPE;
4930 l_prjfunc_curr_code Pa_Projects_All.PROJFUNC_CURRENCY_CODE%TYPE;
4931 l_prjfunc_cost_rate_type Pa_Projects_All.PROJFUNC_COST_RATE_TYPE%TYPE;
4932 l_prjfunc_cost_rate_date Pa_Projects_All.PROJFUNC_COST_RATE_DATE%TYPE;
4933 l_prjfunc_bil_rate_date_code Pa_Projects_All.PROJFUNC_BIL_RATE_DATE_CODE%TYPE;
4934 l_prjfunc_bil_rate_type Pa_Projects_All.PROJFUNC_BIL_RATE_TYPE%TYPE;
4935 l_prjfunc_bil_rate_date Pa_Projects_All.PROJFUNC_BIL_RATE_DATE%TYPE;
4936 l_prjfunc_bil_ex_rate Pa_Projects_All.PROJFUNC_BIL_EXCHANGE_RATE%TYPE;
4937 l_labor_tp_schedule_id Pa_Projects_All.LABOR_TP_SCHEDULE_ID%TYPE;
4938 l_labor_tp_fixed_date Pa_Projects_All.LABOR_TP_FIXED_DATE%TYPE;
4939 l_labor_sch_discount Pa_Projects_All.LABOR_SCHEDULE_DISCOUNT%TYPE;
4940 l_asg_precedes_task Pa_Projects_All.ASSIGN_PRECEDES_TASK%TYPE;
4941 l_labor_bill_rate_orgid Pa_Projects_All.LABOR_BILL_RATE_ORG_ID%TYPE;
4942 l_labor_std_bill_rate_sch Pa_Projects_All.LABOR_STD_BILL_RATE_SCHDL%TYPE;
4943 l_labor_sch_fixed_dt Pa_Projects_All.LABOR_SCHEDULE_FIXED_DATE%TYPE;
4944 l_labor_sch_type Pa_Projects_All.LABOR_SCH_TYPE%TYPE;
4945
4946 l_fcst_opt_jobcostrate_sch_id NUMBER;
4947
4948 /* Project Assignment Info */
4949
4950 l_asg_fcst_job_id Pa_Project_Assignments.Fcst_Job_Id%TYPE;
4951 l_asg_fcst_job_group_id Pa_Project_Assignments.Fcst_Job_Group_Id%TYPE;
4952 l_asg_project_role_id Pa_Project_Assignments.Project_Role_Id%TYPE;
4953 l_prj_assignment_type PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE;
4954 l_prj_status_code PA_PROJECT_ASSIGNMENTS.STATUS_CODE%TYPE;
4955
4956 l_projfunc_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4957 l_projfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4958 l_projfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4959 l_projfunc_rev_exch_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4960 l_projfunc_cst_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4961 l_projfunc_cst_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4962 l_project_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4963 l_project_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4964 l_project_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4965 l_project_rev_exch_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4966 l_project_cst_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4967 l_project_cst_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4968
4969 /* Out Parameters */
4970 lx_rt_pfunc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
4971 lx_rt_pfunc_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
4972 lx_rt_pfunc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4973 lx_rt_pfunc_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4974 lx_rt_pfunc_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4975 lx_rt_pfunc_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4976 lx_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
4977 lx_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4978 lx_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4979 lx_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4980 lx_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4981 lx_rt_pfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4982 lx_rt_proj_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
4983 lx_rt_proj_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
4984 lx_rt_proj_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4985 lx_rt_proj_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4986 lx_rt_proj_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4987 lx_rt_proj_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4988 lx_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
4989 lx_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4990 lx_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4991 lx_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4992 lx_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4993 lx_rt_proj_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4994 lx_rt_expfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4995 lx_rt_expfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4996 lx_rt_expfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4997 lx_rt_expfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4998 lx_rt_expfunc_raw_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4999 lx_rt_expfunc_raw_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
5000 lx_rt_expfunc_bd_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
5001 lx_rt_expfunc_bd_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
5002 lx_rt_cost_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
5003 lx_rt_txn_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp ;
5004 lx_rt_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
5005 lx_rt_txn_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
5006 lx_rt_txn_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
5007 lx_rt_rev_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
5008 lx_rt_txn_rev_bill_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
5009 lx_rt_txn_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
5010 lx_rt_rev_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
5011 lx_rt_cst_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
5012 lx_rt_bd_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
5013 lx_rt_others_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
5014
5015 lx_asg_precedes_task_tab PA_PLSQL_DATATYPES.Char1TabTyp; -- Added for bug 3255061
5016
5017 lx_rt_error_msg VARCHAR2(1000);
5018 lx_rt_return_status VARCHAR2(30);
5019 lx_rt_msg_count NUMBER;
5020 lx_rt_msg_data VARCHAR2(100);
5021
5022 ERROR_OCCURED VARCHAR2(1);
5023
5024 /* Get Transfer Price Parameters */
5025
5026 l_cc_exp_category Pa_Expenditure_Types.EXPENDITURE_CATEGORY%TYPE;
5027
5028 l_tp_asgid PA_PLSQL_DATATYPES.IdTabTyp;
5029 l_tp_exp_category PA_PLSQL_DATATYPES.Char30TabTyp;
5030 l_tp_labor_nl_flag PA_PLSQL_DATATYPES.Char1TabTyp;
5031 l_tp_taskid PA_PLSQL_DATATYPES.IdTabTyp;
5032 l_tp_scheduleid PA_PLSQL_DATATYPES.IdTabTyp;
5033 l_tp_denom_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
5034 l_tp_rev_distributed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
5035 l_tp_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
5036 l_tp_fixed_date PA_PLSQL_DATATYPES.DateTabTyp;
5037 l_tp_denom_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
5038 l_tp_denom_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
5039 l_tp_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
5040 l_tp_nl_resource PA_PLSQL_DATATYPES.Char20TabTyp;
5041 l_tp_nl_resource_orgzid PA_PLSQL_DATATYPES.IdTabTyp;
5042 l_tp_pa_date PA_PLSQL_DATATYPES.DateTabTyp;
5043 l_prj_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
5044 l_prjfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
5045 l_tp_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
5046 l_asg_fcst_jobid_tab PA_PLSQL_DATATYPES.IdTabTyp;
5047
5048 lx_proj_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
5049 lx_proj_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
5050 lx_proj_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
5051 lx_proj_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
5052 lx_projfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
5053 lx_projfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
5054 lx_projfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
5055 lx_projfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
5056 lx_denom_tp_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
5057 lx_denom_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
5058 lx_expfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
5059 lx_expfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
5060 lx_expfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
5061 lx_expfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
5062 lx_cc_markup_basecode PA_PLSQL_DATATYPES.Char1TabTyp;
5063 lx_tp_ind_compiled_setid PA_PLSQL_DATATYPES.IdTabTyp;
5064 lx_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
5065 lx_tp_base_amount PA_PLSQL_DATATYPES.NumTabTyp;
5066 lx_tp_bill_markup_percent PA_PLSQL_DATATYPES.NumTabTyp;
5067 lx_tp_sch_line_percent PA_PLSQL_DATATYPES.NumTabTyp;
5068 lx_tp_rule_percent PA_PLSQL_DATATYPES.NumTabTyp;
5069 lx_tp_job_id PA_PLSQL_DATATYPES.IdTabTyp;
5070 lx_tp_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
5071 l_tp_array_size NUMBER;
5072 l_tp_debug_mode VARCHAR2(30);
5073 lx_tp_return_status NUMBER;
5074
5075
5076 BEGIN
5077
5078 IF p_debug_mode = 'Y' THEN
5079 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'ENTERING Get_Initial_Transfer_Price', 3);
5080 END IF;
5081
5082 x_return_status := FND_API.G_RET_STS_SUCCESS;
5083
5084 Open Cur_Forecast_Items(p_assignment_id, p_start_date);
5085
5086 PA_DEBUG.g_err_stage := 'Fetching Cur_Forecast_Items';
5087 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
5088
5089 l_fi_id_tab.delete;
5090 l_fi_item_type_tab.delete;
5091 l_fi_exp_orgid_tab.delete;
5092 l_fi_exp_organizationid_tab.delete;
5093 l_fi_proj_orgid_tab.delete;
5094 l_fi_proj_organizationid_tab.delete;
5095 l_fi_projid_tab.delete;
5096 l_fi_proj_type_class_tab.delete;
5097 l_fi_personid_tab.delete;
5098 l_fi_resid_tab.delete;
5099 l_fi_asgid_tab.delete;
5100 l_fi_date_tab.delete;
5101 l_fi_uom_tab.delete;
5102 l_fi_pvdr_papd_tab.delete;
5103 l_fi_rcvr_papd_tab.delete;
5104 l_fi_exptype_tab.delete;
5105 l_fi_exptypeclass_tab.delete;
5106 l_fi_amount_type_tab.delete;
5107 l_fi_delete_flag_tab.delete;
5108 l_cc_taskid_tab.delete;
5109 l_cc_expitemid_tab.delete;
5110 l_cc_transsource_tab.delete;
5111 l_cc_NLOrgzid_tab.delete;
5112 l_cc_prvdreid_tab.delete;
5113 l_cc_recvreid_tab.delete;
5114 lx_cc_status_tab.delete;
5115 lx_cc_type_tab.delete;
5116 lx_cc_code_tab.delete;
5117 lx_cc_prvdr_orgzid_tab.delete;
5118 lx_cc_recvr_orgzid_tab.delete;
5119 lx_cc_recvr_orgid_tab.delete;
5120 lx_cc_prvdr_orgid_tab.delete;
5121
5122 FETCH Cur_Forecast_Items BULK COLLECT INTO
5123 l_fi_id_tab,
5124 l_fi_item_type_tab,
5125 l_fi_exp_orgid_tab,
5126 l_fi_exp_organizationid_tab,
5127 l_fi_proj_orgid_tab,
5128 l_fi_proj_organizationid_tab,
5129 l_fi_projid_tab,
5130 l_fi_proj_type_class_tab,
5131 l_fi_personid_tab,
5132 l_fi_resid_tab,
5133 l_fi_asgid_tab,
5134 l_fi_date_tab,
5135 l_fi_uom_tab,
5136 l_fi_pvdr_papd_tab,
5137 l_fi_rcvr_papd_tab,
5138 l_fi_exptype_tab,
5139 l_fi_exptypeclass_tab,
5140 l_fi_amount_type_tab,
5141 l_fi_delete_flag_tab;
5142
5143 CLOSE Cur_Forecast_Items;
5144
5145 IF p_debug_mode = 'Y' THEN
5146 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Cursor cur_forecast_items_fetched', 3);
5147 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'f id:'||l_fi_id_tab(1)||' org id'||l_fi_exp_orgid_tab(1));
5148 END IF;
5149
5150
5151 l_cc_taskid_tab(1) := NULL;
5152 l_cc_expitemid_tab(1) := NULL;
5153 l_cc_transsource_tab(1) := NULL;
5154 l_cc_NLOrgzid_tab(1) := NULL;
5155 l_cc_prvdreid_tab(1) := NULL;
5156 l_cc_recvreid_tab(1) := NULL;
5157 lx_cc_type_tab(1) := NULL;
5158 lx_cc_code_tab(1) := NULL;
5159 lx_cc_prvdr_orgzid_tab(1) := NULL;
5160 lx_cc_recvr_orgzid_tab(1) := NULL;
5161 lx_cc_recvr_orgid_tab(1) := NULL;
5162 lx_cc_prvdr_orgid_tab(1) := NULL;
5163 lx_cc_status_tab(1) := NULL;
5164
5165 Pa_Cc_Ident.PA_CC_IDENTIFY_TXN_FI(
5166 P_ExpOrganizationIdTab => l_fi_exp_organizationid_tab,
5167 P_ExpOrgidTab => l_fi_exp_orgid_tab,
5168 P_ProjectIdTab => l_fi_projid_tab,
5169 P_TaskIdTab => l_cc_taskid_tab,
5170 P_ExpItemDateTab => l_fi_date_tab,
5171 P_ExpItemIdTab => l_cc_expitemid_tab,
5172 P_PersonIdTab => l_fi_personid_tab,
5173 P_ExpTypeTab => l_fi_exptype_tab,
5174 P_SysLinkTab => l_fi_exptypeclass_tab,
5175 P_PrjOrganizationIdTab => l_fi_proj_organizationid_tab,
5176 P_PrjOrgIdTab => l_fi_proj_orgid_tab,
5177 P_TransSourceTab => l_cc_transsource_tab,
5178 P_NLROrganizationIdTab => l_cc_NLOrgzid_tab,
5179 P_PrvdrLEIdTab => l_cc_prvdreid_tab,
5180 P_RecvrLEIdTab => l_cc_recvreid_tab,
5181 X_StatusTab => lx_cc_status_tab,
5182 X_CrossChargeTypeTab => lx_cc_type_tab,
5183 X_CrossChargeCodeTab => lx_cc_code_tab,
5184 X_PrvdrOrganizationIdTab => lx_cc_prvdr_orgzid_tab,
5185 X_RecvrOrganizationIdTab => lx_cc_recvr_orgzid_tab,
5186 X_RecvrOrgIdTab => lx_cc_recvr_orgid_tab,
5187 X_PrvdrOrgIdTab => lx_cc_prvdr_orgid_tab,
5188 X_Error_Stage => lx_cc_error_stage,
5189 X_Error_Code => lx_cc_error_code
5190 );
5191
5192 IF p_debug_mode = 'Y' THEN
5193 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Procedure PA_CC_IDENTIFY_TXN_FI executed', 3);
5194 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Status :'||lx_cc_status_tab(1), 3);
5195 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'cc code:'||lx_cc_code_tab(1), 3);
5196 END IF;
5197
5198
5199 If lx_cc_code_tab(1) in ('I', 'B') AND lx_cc_status_tab(1) is NULL THEN
5200 IF l_fi_item_type_tab(1) = 'R' THEN
5201 l_calling_mode := 'ROLE';
5202 ELSIF l_fi_item_type_tab(1) = 'A' THEN
5203 l_calling_mode := 'ASSIGNMENT';
5204 END IF;
5205 OPEN Proj_Details(l_fi_projid_tab(1));
5206 Fetch Proj_Details
5207 INTO
5208 l_prj_type,
5209 l_distribution_rule,
5210 l_bill_job_group_id,
5211 l_cost_job_group_id,
5212 l_job_bill_rate_sch_id,
5213 l_emp_bill_rate_sch_id,
5214 l_prj_curr_code,
5215 l_prj_rate_date,
5216 l_prj_rate_type,
5217 l_prj_bil_rate_date_code,
5218 l_prj_bil_rate_type,
5219 l_prj_bil_rate_date,
5220 l_prj_bil_ex_rate,
5221 l_prjfunc_curr_code,
5222 l_prjfunc_cost_rate_type,
5223 l_prjfunc_cost_rate_date,
5224 l_prjfunc_bil_rate_date_code,
5225 l_prjfunc_bil_rate_type,
5226 l_prjfunc_bil_rate_date,
5227 l_prjfunc_bil_ex_rate,
5228 l_labor_tp_schedule_id,
5229 l_labor_tp_fixed_date,
5230 l_labor_sch_discount,
5231 l_asg_precedes_task,
5232 l_labor_bill_rate_orgid,
5233 l_labor_std_bill_rate_sch,
5234 l_labor_sch_fixed_dt,
5235 l_labor_sch_type;
5236 Close Proj_Details;
5237
5238 l_projfunc_rev_rt_dt_code_tab.delete;
5239 l_projfunc_rev_rt_date_tab.delete;
5240 l_projfunc_rev_rt_type_tab.delete;
5241 l_projfunc_rev_exch_rt_tab.delete;
5242 l_projfunc_cst_rt_date_tab.delete;
5243 l_projfunc_cst_rt_type_tab.delete;
5244 l_project_rev_rt_dt_code_tab.delete;
5245 l_project_rev_rt_date_tab.delete;
5246 l_project_rev_rt_type_tab.delete;
5247 l_project_rev_exch_rt_tab.delete;
5248 l_project_cst_rt_date_tab.delete;
5249 l_project_cst_rt_type_tab.delete;
5250 l_tp_pa_date.delete;
5251
5252 l_projfunc_rev_rt_dt_code_tab(1) := l_prjfunc_bil_rate_date_code;
5253 l_projfunc_rev_rt_date_tab(1) := l_prjfunc_bil_rate_date;
5254 l_projfunc_rev_rt_type_tab(1) := l_prjfunc_bil_rate_type;
5255 l_projfunc_rev_exch_rt_tab(1) := l_prjfunc_bil_ex_rate;
5256 l_projfunc_cst_rt_date_tab(1) := l_prjfunc_cost_rate_date;
5257 l_projfunc_cst_rt_type_tab(1) := l_prjfunc_cost_rate_type;
5258 l_project_rev_rt_dt_code_tab(1) := l_prj_bil_rate_date_code;
5259 l_project_rev_rt_date_tab(1) := l_prj_bil_rate_date;
5260 l_project_rev_rt_type_tab(1) := l_prj_bil_rate_type;
5261 l_project_rev_exch_rt_tab(1) := l_prj_bil_ex_rate;
5262 l_project_cst_rt_date_tab(1) := l_prj_rate_date;
5263 l_project_cst_rt_type_tab(1) := l_prj_rate_type;
5264
5265 Open Proj_Assignment(p_assignment_id);
5266 Fetch Proj_Assignment INTO
5267 l_asg_fcst_job_id,
5268 l_asg_fcst_job_group_id,
5269 l_asg_project_role_id,
5270 l_prj_assignment_type,
5271 l_prj_status_code;
5272 Close Proj_Assignment;
5273
5274 IF l_fi_item_type_tab(1) = 'R' AND
5275 ( l_asg_fcst_job_id IS NULL OR
5276 l_asg_fcst_job_group_id IS NULL ) THEN
5277 BEGIN
5278 /* Starts here bug4004792 changed the table reference to PA_PROJECT_ROLE_TYPES_B instead of the view PA_PROJECT_ROLE_TYPES for performance reason */
5279 SELECT pa_role_job_bg_utils.get_job_id(PR.project_role_id),
5280 --PR.DEFAULT_JOB_ID,
5281 PJ.JOB_GROUP_ID
5282 INTO
5283 l_asg_fcst_job_id,
5284 l_asg_fcst_job_group_id
5285 FROM PA_PROJECT_ROLE_TYPES_B PR,
5286 PER_JOBS PJ
5287 WHERE
5288 PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
5289 -- PJ.JOB_ID = PR.DEFAULT_JOB_ID;
5290 PJ.JOB_ID =pa_role_job_bg_utils.get_job_id(PR.project_role_id);
5291 /* ends here */
5292 EXCEPTION
5293 WHEN NO_DATA_FOUND THEN
5294 IF p_debug_mode = 'Y' THEN
5295 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'No data found in pa_project_role_types', 3);
5296 END IF;
5297 l_asg_fcst_job_id := NULL;
5298 l_asg_fcst_job_group_id := NULL;
5299 WHEN OTHERS THEN
5300 PA_DEBUG.g_err_stage := 'Inside Prj Role others Excep';
5301 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
5302 END;
5303 END IF;
5304
5305 Open FI_Attributes(l_fi_proj_orgid_tab(1), l_fi_exptype_tab(1), l_fi_pvdr_papd_tab(1), l_fi_exp_orgid_tab(1));
5306 Fetch FI_Attributes into l_fcst_opt_jobcostrate_sch_id, l_cc_exp_category, l_tp_pa_date(1);
5307 Close FI_Attributes;
5308
5309 l_qty_tab.delete;
5310 l_qty_tab(1) := 1;
5311
5312 IF p_debug_mode = 'Y' THEN
5313 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Just Calling PA_RATE_PVT_PKG.CALC_RATE_AMOUNT', 3);
5314 END IF;
5315
5316 PA_RATE_PVT_PKG.CALC_RATE_AMOUNT(
5317 P_CALLING_MODE => l_calling_mode,
5318 P_RATE_CALC_DATE_TAB => l_fi_date_tab,
5319 P_ASGN_START_DATE => p_start_date,
5320 P_ITEM_ID => p_assignment_id,
5321 P_PROJECT_ID => l_fi_projid_tab(1),
5322 P_QUANTITY_TAB => l_qty_tab,
5323 P_FORECAST_JOB_ID => l_asg_fcst_job_id,
5324 P_FORECAST_JOB_GROUP_ID => l_asg_fcst_job_group_id,
5325 P_PERSON_ID => l_fi_personid_tab(1),
5326 P_EXPENDITURE_ORG_ID_TAB => l_fi_exp_orgid_tab,
5327 P_EXPENDITURE_TYPE => l_fi_exptype_tab(1),
5328 P_EXPENDITURE_ORGZ_ID_TAB => l_fi_exp_organizationid_tab,
5329 P_PROJECT_ORG_ID => l_fi_proj_orgid_tab(1),
5330 P_LABOR_COST_MULTI_NAME => NULL,
5331 P_PROJ_COST_JOB_GROUP_ID => NULL,
5332 P_JOB_COST_RATE_SCHEDULE_ID => l_fcst_opt_jobcostrate_sch_id,
5333 P_PROJECT_TYPE => l_prj_type,
5334 P_TASK_ID => NULL,
5335 P_BILL_RATE_MULTIPLIER => NULL,
5336 P_PROJECT_BILL_JOB_GROUP_ID => l_bill_job_group_id,
5337 P_EMP_BILL_RATE_SCHEDULE_ID => l_emp_bill_rate_sch_id,
5338 P_JOB_BILL_RATE_SCHEDULE_ID => l_job_bill_rate_sch_id,
5339 P_DISTRIBUTION_RULE => l_distribution_rule,
5340 p_amount_calc_mode => 'ALL',
5341 P_system_linkage => l_fi_exptypeclass_tab,
5342 p_assign_precedes_task => l_asg_precedes_task,
5343 p_labor_schdl_discnt => l_labor_sch_discount,
5344 p_labor_bill_rate_org_id => l_labor_bill_rate_orgid,
5345 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_sch,
5346 p_labor_schedule_fixed_date => l_labor_sch_fixed_dt,
5347 p_labor_sch_type => l_labor_sch_type,
5348 P_FORECAST_ITEM_ID_TAB => l_fi_id_tab,
5349 P_PROJFUNC_CURRENCY_CODE => l_prjfunc_curr_code,
5350 p_projfunc_rev_rt_dt_code_tab => l_projfunc_rev_rt_dt_code_tab,
5351 p_projfunc_rev_rt_date_tab => l_projfunc_rev_rt_date_tab,
5352 p_projfunc_rev_rt_type_tab => l_projfunc_rev_rt_type_tab,
5353 p_projfunc_rev_exch_rt_tab => l_projfunc_rev_exch_rt_tab,
5354 p_projfunc_cst_rt_date_tab => l_projfunc_cst_rt_date_tab,
5355 p_projfunc_cst_rt_type_tab => l_projfunc_cst_rt_type_tab,
5356 X_PROJFUNC_BILL_RT_TAB => lx_rt_pfunc_bill_rate_tab,
5357 x_projfunc_raw_revenue_tab => lx_rt_pfunc_raw_revenue_tab,
5358 x_projfunc_rev_rt_date_tab => lx_rt_pfunc_rev_rt_date_tab,
5359 x_projfunc_rev_rt_type_tab => lx_rt_pfunc_rev_rt_type_tab,
5360 x_projfunc_rev_exch_rt_tab => lx_rt_pfunc_rev_ex_rt_tab,
5361 x_projfunc_raw_cst_tab => lx_rt_pfunc_raw_cost_tab,
5362 x_projfunc_raw_cst_rt_tab => lx_rt_pfunc_raw_cost_rt_tab,
5363 x_projfunc_burdned_cst_tab => lx_rt_pfunc_bd_cost_tab,
5364 x_projfunc_burdned_cst_rt_tab => lx_rt_pfunc_bd_cost_rt_tab,
5365 x_projfunc_cst_rt_date_tab => lx_rt_pfunc_cost_rt_date_tab,
5366 x_projfunc_cst_rt_type_tab => lx_rt_pfunc_cost_rt_type_tab,
5367 x_projfunc_cst_exch_rt_tab => lx_rt_pfunc_cost_ex_rt_tab,
5368 p_project_currency_code => l_prj_curr_code,
5369 p_project_rev_rt_dt_code_tab => l_project_rev_rt_dt_code_tab,
5370 p_project_rev_rt_date_tab => l_project_rev_rt_date_tab,
5371 p_project_rev_rt_type_tab => l_project_rev_rt_type_tab,
5372 p_project_rev_exch_rt_tab => l_project_rev_exch_rt_tab,
5373 p_project_cst_rt_date_tab => l_project_cst_rt_date_tab,
5374 p_project_cst_rt_type_tab => l_project_cst_rt_type_tab,
5375 x_project_bill_rt_tab => lx_rt_proj_bill_rate_tab,
5376 x_project_raw_revenue_tab => lx_rt_proj_raw_revenue_tab,
5377 x_project_rev_rt_date_tab => lx_rt_proj_rev_rt_date_tab,
5378 x_project_rev_rt_type_tab => lx_rt_proj_rev_rt_type_tab,
5379 x_project_rev_exch_rt_tab => lx_rt_proj_rev_ex_rt_tab,
5380 x_project_raw_cst_tab => lx_rt_proj_raw_cost_tab,
5381 x_project_raw_cst_rt_tab => lx_rt_proj_raw_cost_rt_tab,
5382 x_project_burdned_cst_tab => lx_rt_proj_bd_cost_tab,
5383 x_project_burdned_cst_rt_tab => lx_rt_proj_bd_cost_rt_tab,
5384 x_project_cst_rt_date_tab => lx_rt_proj_cost_rt_date_tab,
5385 x_project_cst_rt_type_tab => lx_rt_proj_cost_rt_type_tab,
5386 x_project_cst_exch_rt_tab => lx_rt_proj_cost_ex_rt_tab,
5387 x_exp_func_curr_code_tab => lx_rt_expfunc_curr_code_tab,
5388 x_exp_func_raw_cst_rt_tab => lx_rt_expfunc_raw_cst_rt_tab,
5389 x_exp_func_raw_cst_tab => lx_rt_expfunc_raw_cst_tab,
5390 x_exp_func_burdned_cst_rt_tab => lx_rt_expfunc_bd_cst_rt_tab,
5391 x_exp_func_burdned_cst_tab => lx_rt_expfunc_bd_cst_tab,
5392 x_exp_func_cst_rt_date_tab => lx_rt_expfunc_cost_rt_date_tab,
5393 x_exp_func_cst_rt_type_tab => lx_rt_expfunc_cost_rt_type_tab,
5394 x_exp_func_cst_exch_rt_tab => lx_rt_expfunc_cost_ex_rt_tab,
5395 x_cst_txn_curr_code_tab => lx_rt_cost_txn_curr_code_tab,
5396 x_txn_raw_cst_rt_tab => lx_rt_txn_raw_cost_rt_tab,
5397 x_txn_raw_cst_tab => lx_rt_txn_raw_cost_tab,
5398 x_txn_burdned_cst_rt_tab => lx_rt_txn_bd_cost_rt_tab,
5399 x_txn_burdned_cst_tab => lx_rt_txn_bd_cost_tab,
5400 x_rev_txn_curr_code_tab => lx_rt_rev_txn_curr_code_tab,
5401 x_txn_rev_bill_rt_tab => lx_rt_txn_rev_bill_rt_tab,
5402 x_txn_rev_raw_revenue_tab => lx_rt_txn_raw_revenue_tab,
5403 X_ERROR_MSG => lx_rt_error_msg,
5404 X_REV_REJCT_REASON_TAB => lx_rt_rev_rejct_reason_tab,
5405 X_CST_REJCT_REASON_TAB => lx_rt_cst_rejct_reason_tab,
5406 X_BURDNED_REJCT_REASON_TAB => lx_rt_bd_rejct_reason_tab,
5407 X_OTHERS_REJCT_REASON_TAB => lx_rt_others_rejct_reason_tab,
5408 X_RETURN_STATUS => lx_rt_return_status,
5409 X_MSG_COUNT => lx_rt_msg_count,
5410 X_MSG_DATA => lx_rt_msg_data
5411 );
5412
5413 IF p_debug_mode = 'Y' THEN
5414 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Done With the PA_RATE_PVT_PKG.CALC_RATE_AMOUNT', 3);
5415 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'x_return status for calc_rate_amount is:'||x_return_status, 3);
5416 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'lx_rt_rev_rejct_reason_tab COUNT:'||lx_rt_rev_rejct_reason_tab.count, 3);
5417 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'lx_rt_cst_rejct_reason_tab.count :'||lx_rt_cst_rejct_reason_tab.count, 3);
5418 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'lx_rt_bd_rejct_reason_tab.count:'||lx_rt_bd_rejct_reason_tab.count, 3);
5419 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'lx_rt_others_rejct_reason_tab.count:'||lx_rt_others_rejct_reason_tab.count, 3);
5420 END IF;
5421
5422
5423 ERROR_OCCURED := 'N';
5424
5425 IF lx_rt_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5426 ERROR_OCCURED := 'Y';
5427 END IF;
5428
5429 If lx_rt_rev_rejct_reason_tab.exists(1) THEN
5430 IF lx_rt_rev_rejct_reason_tab(1) IS NOT NULL THEN
5431 IF p_debug_mode = 'Y' THEN
5432 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Rev Reject:'||lx_rt_rev_rejct_reason_tab(1), 3);
5433 END IF;
5434 ERROR_OCCURED := 'Y';
5435 END IF;
5436 END IF;
5437
5438 IF lx_rt_cst_rejct_reason_tab.exists(1) THEN
5439 IF lx_rt_cst_rejct_reason_tab(1) IS NOT NULL THEN
5440 IF p_debug_mode = 'Y' THEN
5441 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Raw Cost Reject:'||lx_rt_cst_rejct_reason_tab(1), 3);
5442 END IF;
5443 ERROR_OCCURED := 'Y';
5444 END IF;
5445 END IF;
5446
5447 IF lx_rt_bd_rejct_reason_tab.exists(1) THEN
5448 IF lx_rt_bd_rejct_reason_tab(1) IS NOT NULL THEN
5449 IF p_debug_mode = 'Y' THEN
5450 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Burden Cost Reject:'||lx_rt_bd_rejct_reason_tab(1), 3);
5451 END IF;
5452 ERROR_OCCURED := 'Y';
5453 END IF;
5454 END IF;
5455
5456 IF lx_rt_others_rejct_reason_tab.exists(1) THEN
5457 IF lx_rt_others_rejct_reason_tab(1) IS NOT NULL THEN
5458 IF p_debug_mode = 'Y' THEN
5459 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Other Reject:'||lx_rt_others_rejct_reason_tab(1), 3);
5460 END IF;
5461 ERROR_OCCURED := 'Y';
5462 END IF;
5463 END IF;
5464
5465 IF ERROR_OCCURED = 'Y' THEN
5466 IF p_debug_mode = 'Y' THEN
5467 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Some Error Occurred, Returning null as Rate', 3);
5468 END IF;
5469 x_transfer_price_rate := NULL;
5470 x_transfer_pr_rate_curr := NULL;
5471 x_return_status := FND_API.G_RET_STS_SUCCESS;
5472 Return;
5473 END IF;
5474
5475 l_tp_asgid.delete;
5476 l_tp_exp_category.delete;
5477 l_tp_labor_nl_flag.delete;
5478 l_tp_taskid.delete;
5479 l_tp_scheduleid.delete;
5480 l_prj_curr_code_tab.delete;
5481 l_prjfunc_curr_code_tab.delete;
5482 l_tp_rev_distributed_flag.delete;
5483 l_tp_compute_flag.delete;
5484 l_tp_fixed_date.delete;
5485 l_tp_quantity_tab.delete;
5486 l_asg_fcst_jobid_tab.delete;
5487 l_tp_nl_resource.delete;
5488 l_tp_nl_resource_orgzid.delete;
5489 lx_proj_tp_rate_type.delete;
5490 lx_proj_tp_rate_date.delete;
5491 lx_proj_tp_exchange_rate.delete;
5492 lx_proj_tp_amt.delete;
5493 lx_projfunc_tp_rate_type.delete;
5494 lx_projfunc_tp_rate_date.delete;
5495 lx_projfunc_tp_exchange_rate.delete;
5496 lx_projfunc_tp_amt.delete;
5497 lx_denom_tp_currcode.delete;
5498 lx_denom_tp_amt.delete;
5499 lx_expfunc_tp_rate_type.delete;
5500 lx_expfunc_tp_rate_date.delete;
5501 lx_expfunc_tp_exchange_rate.delete;
5502 lx_expfunc_tp_amt.delete;
5503 lx_cc_markup_basecode.delete;
5504 lx_tp_ind_compiled_setid.delete;
5505 lx_tp_bill_rate.delete;
5506 lx_tp_base_amount.delete;
5507 lx_tp_bill_markup_percent.delete;
5508 lx_tp_sch_line_percent.delete;
5509 lx_tp_rule_percent.delete;
5510 lx_tp_job_id.delete;
5511 lx_tp_error_code.delete;
5512
5513 lx_asg_precedes_task_tab.delete; -- Added for bug 3255061
5514
5515 l_tp_asgid(1) := p_assignment_id;
5516 l_tp_exp_category(1) := l_cc_exp_category;
5517 l_tp_labor_nl_flag(1) := 'Y';
5518 l_tp_taskid(1) := NULL;
5519 l_tp_scheduleid(1) := l_labor_tp_schedule_id;
5520 l_prj_curr_code_tab(1) := l_prj_curr_code;
5521 l_prjfunc_curr_code_tab(1) := l_prjfunc_curr_code;
5522 l_tp_rev_distributed_flag(1) := 'Y';
5523 l_tp_compute_flag(1) := 'Y';
5524 l_tp_fixed_date(1) := l_labor_tp_fixed_date;
5525 l_tp_quantity_tab(1) := 1;
5526 l_asg_fcst_jobid_tab(1) := l_asg_fcst_job_id;
5527 l_tp_nl_resource(1) := NULL;
5528 l_tp_nl_resource_orgzid(1) := NULL;
5529 l_tp_debug_mode := p_debug_mode;
5530 lx_proj_tp_rate_type(1) := NULL;
5531 lx_proj_tp_rate_date(1) := NULL;
5532 lx_proj_tp_exchange_rate(1) := NULL;
5533 lx_proj_tp_amt(1) := NULL;
5534 lx_projfunc_tp_rate_type(1) := NULL;
5535 lx_projfunc_tp_rate_date(1) := NULL;
5536 lx_projfunc_tp_exchange_rate(1) := NULL;
5537 lx_projfunc_tp_amt(1) := NULL;
5538 lx_denom_tp_currcode(1) := NULL;
5539 lx_denom_tp_amt(1) := NULL;
5540 lx_expfunc_tp_rate_type(1) := NULL;
5541 lx_expfunc_tp_rate_date(1) := NULL;
5542 lx_expfunc_tp_exchange_rate(1) := NULL;
5543 lx_expfunc_tp_amt(1) := NULL;
5544 lx_cc_markup_basecode(1) := NULL;
5545 lx_tp_ind_compiled_setid(1) := NULL;
5546 lx_tp_bill_rate(1) := NULL;
5547 lx_tp_base_amount(1) := NULL;
5548 lx_tp_bill_markup_percent(1) := NULL;
5549 lx_tp_sch_line_percent(1) := NULL;
5550 lx_tp_rule_percent(1) := NULL;
5551 lx_tp_job_id(1) := NULL;
5552 lx_tp_error_code(1) := NULL;
5553
5554 lx_asg_precedes_task_tab(1) := l_asg_precedes_task; -- Added for bug 3255061
5555
5556 IF p_debug_mode = 'Y' THEN
5557 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'PA_CC_TRANSFER_PRICE.GET_TRANSFER_PRICE calling', 3);
5558 END IF;
5559
5560 PA_CC_TRANSFER_PRICE.GET_TRANSFER_PRICE(
5561 p_module_name => 'FORECAST',
5562 p_prvdr_organization_id => lx_cc_prvdr_orgzid_tab,
5563 p_recvr_org_id => lx_cc_recvr_orgid_tab,
5564 p_recvr_organization_id => lx_cc_recvr_orgzid_tab,
5565 p_expnd_organization_id => l_fi_exp_organizationid_tab,
5566 p_expenditure_item_id => l_fi_id_tab,
5567 p_expenditure_type => l_fi_exptype_tab,
5568 p_expenditure_category => l_tp_exp_category,
5569 p_expenditure_item_date => l_fi_date_tab,
5570 p_labor_non_labor_flag => l_tp_labor_nl_flag,
5571 p_system_linkage_function => l_fi_exptypeclass_tab,
5572 p_task_id => l_tp_taskid,
5573 p_tp_schedule_id => l_tp_scheduleid,
5574 p_denom_currency_code => lx_rt_cost_txn_curr_code_tab,
5575 p_project_currency_code => l_prj_curr_code_tab,
5576 p_projfunc_currency_code => l_prjfunc_curr_code_tab,
5577 p_revenue_distributed_flag => l_tp_rev_distributed_flag,
5578 p_processed_thru_date => sysdate,
5579 p_compute_flag => l_tp_compute_flag ,
5580 p_tp_fixed_date => l_tp_fixed_date,
5581 p_denom_raw_cost_amount => lx_rt_txn_raw_cost_tab,
5582 p_denom_burdened_cost_amount => lx_rt_txn_bd_cost_tab,
5583 p_raw_revenue_amount => lx_rt_pfunc_raw_revenue_tab,
5584 p_project_id => l_fi_projid_tab,
5585 p_quantity => l_tp_quantity_tab,
5586 p_incurred_by_person_id => l_fi_personid_tab,
5587 p_job_id => l_asg_fcst_jobid_tab,
5588 p_non_labor_resource => l_tp_nl_resource,
5589 p_nl_resource_organization_id => l_tp_nl_resource_orgzid,
5590 p_pa_date => l_tp_pa_date,
5591 p_array_size => 1,
5592 p_debug_mode => l_tp_debug_mode,
5593 p_tp_amt_type_code => l_fi_amount_type_tab,
5594 p_assignment_id => l_tp_asgid,
5595 p_prvdr_operating_unit => lx_cc_prvdr_orgid_tab,
5596 p_assignment_precedes_task => lx_asg_precedes_task_tab, -- Added for bug 3255061
5597 x_proj_tp_rate_type => lx_proj_tp_rate_type,
5598 x_proj_tp_rate_date => lx_proj_tp_rate_date,
5599 x_proj_tp_exchange_rate => lx_proj_tp_exchange_rate,
5600 x_proj_transfer_price => lx_proj_tp_amt,
5601 x_projfunc_tp_rate_type => lx_projfunc_tp_rate_type,
5602 x_projfunc_tp_rate_date => lx_projfunc_tp_rate_date,
5603 x_projfunc_tp_exchange_rate => lx_projfunc_tp_exchange_rate,
5604 x_projfunc_transfer_price => lx_projfunc_tp_amt,
5605 x_denom_tp_currency_code => lx_denom_tp_currcode,
5606 x_denom_transfer_price => lx_denom_tp_amt,
5607 x_acct_tp_rate_type => lx_expfunc_tp_rate_type,
5608 x_acct_tp_rate_date => lx_expfunc_tp_rate_date,
5609 x_acct_tp_exchange_rate => lx_expfunc_tp_exchange_rate,
5610 x_acct_transfer_price => lx_expfunc_tp_amt,
5611 x_cc_markup_base_code => lx_cc_markup_basecode,
5612 x_tp_ind_compiled_set_id => lx_tp_ind_compiled_setid,
5613 x_tp_bill_rate => lx_tp_bill_rate,
5614 x_tp_base_amount => lx_tp_base_amount,
5615 x_tp_bill_markup_percentage => lx_tp_bill_markup_percent,
5616 x_tp_schedule_line_percentage => lx_tp_sch_line_percent,
5617 x_tp_rule_percentage => lx_tp_rule_percent,
5618 x_tp_job_id => lx_tp_job_id,
5619 x_error_code => lx_tp_error_code,
5620 x_return_status => lx_tp_return_status );
5621
5622 IF p_debug_mode = 'Y' THEN
5623 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'status is : '||lx_tp_return_status, 3);
5624 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'rate is :'||x_transfer_price_rate||' curr :'||x_transfer_pr_rate_curr, 3);
5625 END IF;
5626
5627 If lx_tp_return_status <> 0 THEN
5628 x_transfer_price_rate := NULL;
5629 x_transfer_pr_rate_curr := NULL;
5630 ELSE
5631 x_transfer_price_rate := lx_projfunc_tp_amt(1);
5632 IF x_transfer_price_rate IS NULL THEN
5633 x_transfer_pr_rate_curr:=NULL;
5634 ELSE
5635 x_transfer_pr_rate_curr := l_prjfunc_curr_code;
5636 END IF;
5637 END IF;
5638 ELSE
5639 IF p_debug_mode = 'Y' THEN
5640 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Returning with Null as value', 3);
5641 END IF;
5642 x_transfer_price_rate := NULL;
5643 x_transfer_pr_rate_curr := NULL;
5644 END IF;
5645
5646 x_return_status := FND_API.G_RET_STS_SUCCESS;
5647 EXCEPTION
5648 WHEN OTHERS THEN
5649 If p_debug_mode = 'Y' THEN
5650 pa_debug.write('PA_CC_TRANSFER_PRICE.GET_INITIAL_TRANSFER_PRICE', 'Unhandled Exception occured', 3);
5651 End if;
5652 /*Added for File.sql.39*/
5653 x_transfer_price_rate := NULL;
5654 x_transfer_pr_rate_curr := NULL;
5655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5656 END Get_Initial_Transfer_Price;
5657
5658 END PA_CC_TRANSFER_PRICE;