[Home] [Help]
PACKAGE BODY: APPS.PA_CC_TRANSFER_PRICE
Source
1 PACKAGE BODY PA_CC_TRANSFER_PRICE AS
2 /* $Header: PAXCCTPB.pls 120.13.12010000.4 2008/10/17 05:26:31 abjacob 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 ELSE
1490 IF (x_acct_tp_rate_type(i) IS NOT NULL
1491 AND x_acct_tp_rate_date(i) IS NOT NULL) THEN
1492
1493 -- Use type and date to convert
1494 PA_MULTI_CURRENCY.Convert_Amount(
1495 p_from_currency => x_denom_tp_currency_code(i),
1496 p_to_currency => G_acct_currency_code ,
1497 p_conversion_date => x_acct_tp_rate_date(i),
1498 p_conversion_type => x_acct_tp_rate_type(i),
1499 p_amount => x_denom_transfer_price(i),
1500 p_user_validate_flag => 'Y',
1501 p_handle_exception_flag => 'Y',
1502 p_converted_amount => l_acct_transfer_price,
1503 p_denominator => l_denominator,
1504 p_numerator => l_numerator,
1505 p_rate => x_acct_tp_exchange_rate(i),
1506 x_status => l_error_message
1507 );
1508
1509 IF l_error_message is NOT NULL THEN
1510 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1511 ELSE
1512 x_acct_transfer_price (i) := l_acct_transfer_price;
1513 END IF;
1514 ELSE
1515 x_error_code(i) := 'PA_CC_TP_RATE_TYPE_DATE_NULL';
1516 END IF;
1517
1518
1519 END IF;
1520
1521 ELSE /** else (l_compute_flag(i) = 'C'**/
1522
1523 -- Consider the cases where transfer price calculated by
1524 -- Pre-client extension or APIs or Post -client extension
1525 IF (G_cc_default_rate_date_code = 'E') THEN
1526 l_rate_date := p_expenditure_item_date(i);
1527
1528 ELSIF (G_cc_default_rate_date_code = 'P') THEN
1529 IF (p_pa_date.exists(i)and p_pa_date(i) IS NOT NULL) THEN
1530 l_rate_date := p_pa_date(i);
1531 ELSE
1532 -- Calculate pa_Date
1533 l_rate_date := pa_utils2.get_pa_date(
1534 p_ei_date => p_expenditure_item_date(i),
1535 p_gl_date => sysdate,
1536 p_org_id => G_prvdr_org_id /* p_prvdr_organization_id(i) modified for bug 3535443 */ /**CBGA**/
1537 );
1538 END IF;
1539 END IF;
1540
1541 -- Call extension to Override currency attributes
1542
1543 PA_MULTI_CURR_CLIENT_EXTN.Override_Curr_Conv_Attributes
1544 (
1545 p_project_id => p_project_id(i),
1546 p_task_id => p_task_id(i),
1547 p_transaction_class => 'Transfer Price',
1548 p_expenditure_item_id => p_expenditure_item_id(i),
1549 p_expenditure_type_class => p_system_linkage_function(i),
1550 p_expenditure_type => p_expenditure_type(i),
1551 p_expenditure_category => p_expenditure_category(i),
1552 p_from_currency_code => x_denom_tp_currency_code(i),
1553 p_to_currency_code => G_Acct_currency_code,
1554 p_conversion_type => G_cc_default_rate_type,
1555 p_conversion_date => l_rate_date,
1556 x_rate_type => l_rate_type,
1557 x_rate_date => l_new_rate_date,
1558 x_exchange_rate => l_exchange_rate,
1559 x_error_message => l_error_message,
1560 x_status => l_status
1561 );
1562
1563
1564 IF (l_status = 0) THEN
1565 -- success
1566 l_error_message := null;
1567
1568 -- Added the below code for Bug 5276842
1569 IF l_rate_type <> 'User' and l_rate_type IS NOT NULL THEN
1570 BEGIN
1571 SELECT conversion_type
1572 INTO l_rate_type1
1573 FROM gl_daily_conversion_types
1574 WHERE (user_conversion_type = l_rate_type
1575 OR conversion_type = l_rate_type);
1576 l_rate_type := l_rate_type1;
1577 EXCEPTION
1578 WHEN NO_DATA_FOUND THEN
1579 l_error_message := 'PA_EXCH_RATE_TYPE_INVALID';
1580 END;
1581 END IF;
1582 -- End of changes for bug 5276842
1583
1584 IF (l_rate_type = 'User' and l_exchange_rate IS NOT NULL)
1585 THEN
1586 -- Do the conversion using the rate
1587 l_acct_transfer_price :=
1588 pa_currency.round_currency_amt(
1589 x_denom_transfer_price(i)*l_exchange_rate);
1590
1591 ELSIF (l_rate_type IS NOT NULL and l_new_rate_date IS
1592 NOT NULL and l_error_message IS NULL) THEN
1593 -- Added the condition of l_error_message for Bug 5276842
1594 -- Use type and date to convert
1595 l_rate_date := l_new_rate_date;
1596 PA_MULTI_CURRENCY.Convert_Amount(
1597 p_from_currency => x_denom_tp_currency_code(i),
1598 p_to_currency => G_acct_currency_code ,
1599 p_conversion_date => l_rate_date,
1600 p_conversion_type => l_rate_type,
1601 p_amount => x_denom_transfer_price(i),
1602 p_user_validate_flag => 'Y',
1603 p_handle_exception_flag => 'Y',
1604 p_converted_amount => l_acct_transfer_price,
1605 p_denominator => l_denominator,
1606 p_numerator => l_numerator,
1607 p_rate => l_exchange_rate,
1608 x_status => l_error_message
1609 );
1610 ELSIF (l_error_message IS NULL) THEN
1611 -- Added the condition of l_error_message for Bug 5276842
1612 -- Use default rate_type and rate_date to convert
1613 pa_debug.G_Err_Stage := 'Using default rate type';
1614 IF g1_debug_mode = 'Y' THEN
1615 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1616 END IF;
1617 l_rate_type := G_cc_default_rate_type;
1618
1619 PA_MULTI_CURRENCY.Convert_Amount(
1620 p_from_currency => x_denom_tp_currency_code(i),
1621 p_to_currency => G_acct_currency_code ,
1622 p_conversion_date => l_rate_date,
1623 p_conversion_type => l_rate_type,
1624 p_amount => x_denom_transfer_price(i),
1625 p_user_validate_flag => 'Y',
1626 p_handle_exception_flag => 'Y',
1627 p_converted_amount => l_acct_transfer_price,
1628 p_denominator => l_denominator,
1629 p_numerator => l_numerator,
1630 p_rate => l_exchange_rate,
1631 x_status => l_error_message
1632 );
1633 END IF;
1634
1635 IF l_error_message IS NOT NULL THEN
1636
1637 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1638 IF g1_debug_mode = 'Y' THEN
1639 pa_debug.write_file('LOG',
1640 Substr (l_error_message,1,2000),1);
1641 END IF;
1642 ELSE
1643 x_acct_transfer_price (i) := l_acct_transfer_price;
1644 x_acct_tp_rate_type(i) := l_rate_type;
1645 x_acct_tp_rate_date(i) := l_rate_date;
1646 x_acct_tp_exchange_rate(i) := l_exchange_rate;
1647
1648 -- bug 7489360
1649 x_proj_tp_rate_type(i) := l_rate_type;
1650 x_proj_tp_rate_date(i) := l_rate_date;
1651 x_projfunc_tp_rate_type(i) := l_rate_type;
1652 x_projfunc_tp_rate_date(i) := l_rate_date;
1653
1654 pa_debug.G_Err_Stage := 'Rate Type ='||l_rate_type;
1655 IF g1_debug_mode = 'Y' THEN
1656 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1657 END IF;
1658 END IF;
1659
1660 ELSIF (l_status > 0) THEN
1661 -- Application error occurred in extension to override
1662 -- currency conversion attributes
1663 -- x_error_code(i) := 'PA_CC_TP_OVERIDE_APP_ERROR';
1664 x_error_code(i) := l_error_message; -- Added for 2661949
1665 pa_debug.G_Err_Stage :=
1666 Substr( 'Application error from Currency conversion
1667 override extension'||
1668 'while processing expenditure_item :'||
1669 to_char(p_expenditure_item_id(i))||'Error Message :'
1670 || l_error_message,1,2000);
1671 IF g1_debug_mode = 'Y' THEN
1672 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1673 END IF;
1674 ELSIF (l_status < 0) THEN
1675
1676 -- Unexpected error occurred in extension to override
1677 -- currency conversion attributes
1678 --x_error_code(i) := 'PA_CC_TP_OVERIDE_ORA_ERROR';
1679 x_error_code(i) := l_error_message; -- Added for 2661949
1680 pa_debug.G_Err_Stage :=
1681 Substr( 'Unexpected error from Currency conversion
1682 override extension'||
1683 'while processing expenditure_item :'||
1684 to_char(p_expenditure_item_id(i))||'Error Message :'
1685 || l_error_message,1,2000);
1686 IF g1_debug_mode = 'Y' THEN
1687 pa_debug.write_file('LOG',pa_debug.G_Err_Stage,1);
1688 END IF;
1689 raise unexpected_result;
1690 ELSE
1691 x_error_code (i) := 'PA_CC_TP_INVALID_OVERIDE_STATUS';
1692 END IF;
1693
1694 l_multi_currency_billing_flag := 'N';
1695
1696 /* IF ( ( p_tp_amt_type_code(i) is not NULL )
1697 and ( p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' ) ) then commented for bug6712230*/
1698 If NVL(p_tp_amt_type_code(i),'COST_REVENUE') is not NULL THEN /* Added NVL for bug 6891120 -- bug6712230 */
1699 --Start devdrop2 changes
1700 -- Converting the denorm to projfunc and proj for amt_type 'REVENUE_TRANSFER'
1701
1702 SELECT project_bil_rate_date_code,
1703 project_bil_rate_type,
1704 project_bil_rate_date,
1705 project_bil_exchange_rate,
1706 projfunc_bil_rate_date_code,
1707 projfunc_bil_rate_type,
1708 projfunc_bil_rate_date,
1709 nvl(multi_currency_billing_flag,'N'),
1710 projfunc_bil_exchange_rate
1711 INTO l_project_bil_rate_date_code,
1712 l_project_bil_rate_type,
1713 l_project_bil_rate_date,
1714 l_project_bil_exchange_rate,
1715 l_projfunc_bil_rate_date_code,
1716 l_projfunc_bil_rate_type,
1717 l_projfunc_bil_rate_date,
1718 l_multi_currency_billing_flag,
1719 l_projfunc_bil_exchange_rate
1720 FROM pa_projects_all
1721 WHERE project_id = p_project_id(i);
1722 if p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' THEN /*bug6389559*/
1723 IF ( l_multi_currency_billing_flag = 'Y' ) THEN
1724
1725 IF g1_debug_mode = 'Y' THEN
1726 pa_debug.write_file('LOG','l_project_bil_rate_date_code: '||l_project_bil_rate_date_code);
1727 pa_debug.write_file('LOG','l_project_bil_rate_type: '||l_project_bil_rate_type);
1728 pa_debug.write_file('LOG','l_project_bil_rate_date: '||l_project_bil_rate_date);
1729 pa_debug.write_file('LOG','l_project_bil_exchange_rate: '||l_project_bil_exchange_rate);
1730
1731 pa_debug.write_file('LOG','l_projfunc_bil_rate_date_code: '||l_projfunc_bil_rate_date_code);
1732 pa_debug.write_file('LOG','l_projfunc_bil_rate_type: '||l_projfunc_bil_rate_type);
1733 pa_debug.write_file('LOG','l_projfunc_bil_rate_date: '||l_projfunc_bil_rate_date);
1734 pa_debug.write_file('LOG','l_projfunc_bil_exchange_rate: '||l_projfunc_bil_exchange_rate);
1735
1736 pa_debug.write_file('LOG','pa_date : '||p_pa_date(i));
1737 pa_debug.write_file('LOG','x_denom_tp_currency_code : '||x_denom_tp_currency_code(i));
1738 pa_debug.write_file('LOG','p_project_currency_code : '||p_project_currency_code(i));
1739 END IF;
1740 --
1741 --Converting into Project currency attributes
1742 -- for tp_amt_type_code = 'REVENUE_TRANSFER'
1743
1744 IF (l_project_bil_rate_type = 'User') THEN
1745
1746 IF (l_project_bil_exchange_rate IS NOT NULL) THEN
1747
1748 l_project_transfer_price:=
1749 pa_currency.round_trans_currency_amt
1750 (x_denom_transfer_price(i)*l_project_bil_exchange_rate,
1751 p_project_currency_code(i));
1752 ELSE
1753 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1754 IF g1_debug_mode = 'Y' THEN
1755 pa_debug.write_file('LOG','ERROR NO USER RATE ');
1756 END IF;
1757
1758 END IF;
1759 else
1760
1761 IF (l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
1762 l_project_bil_rate_date := G_processed_thru_date;
1763 END IF;
1764
1765 IF g1_debug_mode = 'Y' THEN
1766 pa_debug.write_file('LOG','BEFORE CONVERT: l_project_bil_rate_type: '||l_project_bil_rate_type);
1767 pa_debug.write_file('LOG','BEFORE CONVERT: l_project_bil_rate_date: '||l_project_bil_rate_date);
1768 END IF;
1769
1770 PA_MULTI_CURRENCY.Convert_Amount(
1771 p_from_currency => x_denom_tp_currency_code(i),
1772 p_to_currency => p_project_currency_code(i),
1773 p_conversion_date => l_project_bil_rate_date,
1774 p_conversion_type => l_project_bil_rate_type,
1775 p_amount => x_denom_transfer_price(i),
1776 p_user_validate_flag => 'Y',
1777 p_handle_exception_flag => 'Y',
1778 p_converted_amount => l_project_transfer_price,
1779 p_denominator => l_denominator,
1780 p_numerator => l_numerator,
1781 p_rate => l_project_bil_exchange_rate,
1782 x_status => l_error_message
1783 );
1784
1785 IF g1_debug_mode = 'Y' THEN
1786 pa_debug.write_file('LOG','l_error_message : '||l_error_message);
1787 pa_debug.write_file('LOG','AFTER CONVERT: l_project_bil_rate_type: '||l_project_bil_rate_type);
1788 pa_debug.write_file('LOG','AFTER CONVERT: l_project_bil_rate_date: '||l_project_bil_rate_date);
1789 END IF;
1790
1791 IF l_error_message IS NOT NULL THEN
1792
1793 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1794 IF g1_debug_mode = 'Y' THEN
1795 pa_debug.write_file('LOG',
1796 Substr (l_error_message,1,2000));
1797 END IF;
1798 ELSE
1799 x_proj_transfer_price (i) := l_project_transfer_price;
1800 x_proj_tp_rate_type(i) := l_project_bil_rate_type;
1801 x_proj_tp_rate_date(i) := l_project_bil_rate_date;
1802 x_proj_tp_exchange_rate(i) := l_project_bil_exchange_rate;
1803 pa_debug.G_Err_Stage := 'Rate Type ='||l_project_bil_rate_type;
1804 IF g1_debug_mode = 'Y' THEN
1805 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1806 END IF;
1807 END IF;
1808
1809
1810 END IF; /* esle l_project_bil_rate_type = 'User'*/
1811
1812
1813 --
1814 --Converting into Project functional currency attributes
1815 -- for tp_amt_type_code = 'REVENUE_TRANSFER'
1816 --
1817
1818 IF (l_projfunc_bil_rate_type = 'User' AND
1819 l_projfunc_bil_exchange_rate IS NOT NULL) THEN
1820
1821 l_projfunc_transfer_price:=
1822 pa_currency.round_trans_currency_amt
1823 (x_denom_transfer_price(i)*l_projfunc_bil_exchange_rate,
1824 p_projfunc_currency_code(i));
1825 else
1826
1827 IF (l_projfunc_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
1828 l_projfunc_bil_rate_date := G_processed_thru_date;
1829 END IF;
1830
1831 PA_MULTI_CURRENCY.Convert_Amount(
1832 p_from_currency => x_denom_tp_currency_code(i),
1833 p_to_currency => p_projfunc_currency_code(i),
1834 p_conversion_date => l_projfunc_bil_rate_date,
1835 p_conversion_type => l_projfunc_bil_rate_type,
1836 p_amount => x_denom_transfer_price(i),
1837 p_user_validate_flag => 'Y',
1838 p_handle_exception_flag => 'Y',
1839 p_converted_amount => l_projfunc_transfer_price,
1840 p_denominator => l_denominator,
1841 p_numerator => l_numerator,
1842 p_rate => l_projfunc_bil_exchange_rate,
1843 x_status => l_error_message
1844 );
1845
1846 IF l_error_message IS NOT NULL THEN
1847
1848 x_error_code(i) := 'PA_CC_TP_CONVERT_AMT';
1849 IF g1_debug_mode = 'Y' THEN
1850 pa_debug.write_file('LOG',
1851 Substr (l_error_message,1,2000));
1852 END IF;
1853 ELSE
1854 x_projfunc_transfer_price (i) := l_projfunc_transfer_price;
1855 x_projfunc_tp_rate_type(i) := l_projfunc_bil_rate_type;
1856 x_projfunc_tp_rate_date(i) := l_projfunc_bil_rate_date;
1857 x_projfunc_tp_exchange_rate(i) := l_projfunc_bil_exchange_rate;
1858 pa_debug.G_Err_Stage := 'Rate Type ='||l_projfunc_bil_rate_type;
1859 IF g1_debug_mode = 'Y' THEN
1860 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1861 END IF;
1862 END IF;
1863
1864
1865 END IF; /* esle l_projfunc_bil_rate_type = 'User'*/
1866
1867 IF g1_debug_mode = 'Y' THEN
1868 pa_debug.write_file('LOG','AFTER CONVERSION');
1869 pa_debug.write_file('LOG','l_project_bil_rate_date_code: '||l_project_bil_rate_date_code);
1870 pa_debug.write_file('LOG','l_project_bil_rate_type: '||l_project_bil_rate_type);
1871 pa_debug.write_file('LOG','l_project_bil_rate_date: '||l_project_bil_rate_date);
1872 pa_debug.write_file('LOG','l_project_bil_exchange_rate: '||l_project_bil_exchange_rate);
1873
1874 pa_debug.write_file('LOG','l_projfunc_bil_rate_date_code: '||l_projfunc_bil_rate_date_code);
1875 pa_debug.write_file('LOG','l_projfunc_bil_rate_type: '||l_projfunc_bil_rate_type);
1876 pa_debug.write_file('LOG','l_projfunc_bil_rate_date: '||l_projfunc_bil_rate_date);
1877 pa_debug.write_file('LOG','l_projfunc_bil_exchange_rate: '||l_projfunc_bil_exchange_rate);
1878 END IF;
1879
1880 END IF ; /** l_multi_currency_billing_flag = 'Y' **/
1881 END IF; /** p_tp_amt_type_code(i) = 'REVENUE_TRANSFER' **/
1882
1883 --End devdrop2 changes
1884
1885
1886
1887 --Start devdrop2 changes
1888
1889 /** Currency conversion for p_tp_amt_type_code in ( 'COST_TRANSFER','COST_REVENUE') **/
1890
1891 IF ( (nvl(p_tp_amt_type_code(i),'COST_REVENUE') in ( 'COST_TRANSFER','COST_REVENUE') )
1892 OR
1893 (l_multi_currency_billing_flag = 'N' ) ) then
1894
1895 /* call the cost currency conversion api */
1896
1897 pa_multi_currency_txn.get_currency_amounts(
1898 P_project_id => p_project_id(i),
1899 P_exp_org_id => p_prvdr_operating_unit(i),
1900 p_Calling_module => p_module_name,
1901 P_task_id => p_task_id(i),
1902 P_EI_date => p_expenditure_item_date(i),
1903 P_denom_raw_cost => x_denom_transfer_price(i),
1904 P_denom_curr_code => x_denom_tp_currency_code(i),
1905 P_acct_curr_code => G_acct_currency_code,
1906 P_accounted_flag => 'N',
1907 P_acct_rate_date => x_acct_tp_rate_date(i),
1908 P_acct_rate_type => x_acct_tp_rate_type(i),
1909 P_acct_exch_rate => x_acct_tp_exchange_rate(i),
1910 P_acct_raw_cost => x_acct_transfer_price(i),
1911 P_project_curr_code => p_project_currency_code(i),
1912 P_project_rate_type => x_proj_tp_rate_type(i),
1913 P_project_rate_date => x_proj_tp_rate_date(i),
1914 P_project_exch_rate => x_proj_tp_exchange_rate(i),
1915 P_project_raw_cost => x_proj_transfer_price(i),
1916 P_projfunc_curr_code => p_projfunc_currency_code(i),
1917 P_projfunc_cost_rate_type => x_projfunc_tp_rate_type(i),
1918 P_projfunc_cost_rate_date => x_projfunc_tp_rate_date(i),
1919 P_projfunc_cost_exch_rate => x_projfunc_tp_exchange_rate(i),
1920 P_projfunc_raw_cost => x_projfunc_transfer_price(i),
1921 P_system_linkage => p_system_linkage_function(i),
1922 P_status => x_error_code(i),
1923 P_stage => l_stage );
1924
1925 END IF; /*else p_tp_amt_type_code(i) in ( 'COST_TRANSFER','COST_REVENUE') */
1926 END IF; /* if p_tp_amt_type_code is NOT NULL - bug6389559*/
1927 --End devdrop2 changes
1928
1929 END IF; /** (l_compute_flag(i) = 'C'**/
1930
1931 ELSE
1932 x_error_code(i) := 'PA_CC_TP_CONV_DENOM_CURR_NULL';
1933 END IF; /** x_denom_tp_currency_code(i) is not null **/
1934 END IF; /** x_denom_transfer_price(i) IS NOT NULL **/
1935
1936 END IF; /** x_error_code(i) IS NULL **/
1937
1938 END LOOP;
1939
1940 pa_debug.G_Err_Stage := 'Completed Get_Transfer_Price with success';
1941 IF g1_debug_mode = 'Y' THEN
1942 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1943 END IF;
1944 x_return_status := 0;
1945 pa_debug.Reset_err_stack;
1946
1947 EXCEPTION
1948 when unexpected_result THEN
1949 IF g1_debug_mode = 'Y' THEN
1950 pa_debug.write_file('LOG','Unexpected Error in Transfer Price API' );
1951 pa_debug.write_file('LOG',pa_debug.G_Err_Stack);
1952 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1953 END IF;
1954
1955 when others then
1956
1957 IF g1_debug_mode = 'Y' THEN
1958 pa_debug.write_file('LOG','Unexpected Error in Transfer Price API' );
1959 pa_debug.write_file('LOG',pa_debug.G_Err_Stack);
1960 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1961 END IF;
1962
1963 raise;
1964
1965 END GET_transfer_price;
1966
1967 -------------------------------------------------------------------------------
1968 PROCEDURE Init_who_cols
1969 IS
1970 BEGIN
1971
1972 pa_debug.Set_err_stack ('Init_who_cols');
1973 pa_debug.G_Err_Stage := 'Inside Init_who_cols';
1974 IF g1_debug_mode = 'Y' THEN
1975 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
1976 END IF;
1977
1978 G_created_by := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
1979 G_last_update_login := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1980 G_last_updated_by := G_created_by;
1981 G_creation_date := G_sysdate;
1982 G_last_update_date := G_sysdate ;
1983
1984 pa_debug.Reset_err_stack;
1985
1986 END Init_who_cols ;
1987
1988 -------------------------------------------------------------------------------
1989
1990 PROCEDURE Get_Legal_Entity (
1991 p_org_id IN NUMBER,
1992 x_legal_entity_id OUT NOCOPY NUMBER /*File.sql.39*/
1993 )
1994 IS
1995
1996 Cursor c_legal_entity
1997 is
1998 SELECT org_information2 legal_entity_id
1999 FROM hr_organization_information
2000 WHERE organization_id = p_org_id
2001 AND org_information_context = 'Operating Unit Information';
2002
2003 /* Commented for bug 4920063. Added Above statement for LE
2004 select legal_entity_id
2005 from pa_implementations_all
2006 where org_id = p_org_id;*/
2007
2008 /*l_legal_entity pa_implementations_all.legal_entity_id%TYPE; Commented for bug 2920063*/
2009
2010 l_legal_entity hr_organization_information.org_information2%TYPE;
2011
2012 BEGIN
2013
2014 pa_debug.Set_err_stack ('Get_Legal_Entity');
2015 pa_debug.G_Err_Stage := 'Get Legal Entity of Org'||to_char(p_org_id);
2016 IF g1_debug_mode = 'Y' THEN
2017 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2018 END IF;
2019
2020 open c_legal_entity;
2021 fetch c_legal_entity into l_legal_entity;
2022 close c_legal_entity;
2023
2024 IF l_legal_entity IS NOT NULL THEN
2025 x_legal_entity_id := to_number(l_legal_entity);
2026 -- conversion required as legal_entity_id is stored as varchar2
2027 -- in hr_operating_units
2028 END IF;
2029
2030 pa_debug.Reset_err_stack;
2031
2032 EXCEPTION
2033
2034 WHEN OTHERS THEN
2035 raise;
2036
2037 END Get_Legal_Entity;
2038 -------------------------------------------------------------------------------
2039 PROCEDURE Set_Global_Variables /** Added for Org Forecasting **/
2040 ( p_org_id IN NUMBER)
2041 IS
2042
2043 BEGIN
2044
2045 --SS-ORG-CHANGE
2046 --Added nvl to G_prvdr_org_id
2047
2048 IF nvl(G_prvdr_org_id,-1) <> p_org_id and g_Calling_Module = 'FORECAST' Then
2049
2050 pa_debug.Set_err_stack ('Set_Global_Variables ');
2051 pa_debug.G_Err_Stage := 'Set_Global_Variables '||to_char(p_org_id);
2052 IF g1_debug_mode = 'Y' THEN
2053 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2054 END IF;
2055
2056 G_prvdr_org_id := G_prvdr_org_id_Tab(p_org_id);
2057 G_bg_id := G_bg_id_Tab(p_org_id);
2058 G_acct_currency_code := G_acct_currency_code_Tab(p_org_id);
2059 G_cc_default_rate_type := G_cc_default_rate_type_Tab(p_org_id);
2060 G_cc_default_rate_date_code := G_cc_def_rate_date_code_Tab(p_org_id);
2061 G_exp_org_struct_ver_id := G_exp_org_struct_ver_Tab(p_org_id);
2062
2063 END IF;
2064
2065 EXCEPTION
2066
2067 WHEN OTHERS THEN
2068 raise;
2069 END Set_Global_Variables;
2070
2071 -------------------------------------------------------------------------------
2072 PROCEDURE Get_business_group (
2073 p_org_id IN NUMBER,
2074 x_business_group_id OUT NOCOPY NUMBER /*File.sql.39*/
2075 )
2076 IS
2077
2078 Cursor c_business_group
2079 is
2080 select business_group_id
2081 from hr_operating_units
2082 where organization_id = p_org_id;
2083
2084 l_business_group hr_operating_units.business_group_id%TYPE;
2085
2086 BEGIN
2087 pa_debug.Set_err_stack ('Get_Business_group');
2088 pa_debug.G_Err_Stage := 'Get bisiness_group of Org'||to_char(p_org_id);
2089 IF g1_debug_mode = 'Y' THEN
2090 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2091 END IF;
2092
2093 open c_business_group;
2094 fetch c_business_group into l_business_group;
2095 close c_business_group;
2096
2097 IF l_business_group IS NOT NULL THEN
2098 x_business_group_id := l_business_group;
2099 END IF;
2100
2101 pa_debug.Reset_err_stack;
2102
2103 EXCEPTION
2104
2105 WHEN OTHERS THEN
2106 raise;
2107
2108 END Get_business_group;
2109
2110 -------------------------------------------------------
2111 PROCEDURE Get_Provider_Attributes (
2112 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2113 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab,
2114 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp )
2115 IS
2116 cursor c_ou_attributes
2117 is
2118 select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
2119 EXP_ORG_STRUCTURE_VERSION_ID
2120 from pa_implementations;
2121
2122 cursor c_ou_attributes_fcst (l_org_id number)
2123 is
2124 select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
2125 EXP_ORG_STRUCTURE_VERSION_ID
2126 from pa_implementations_all
2127 where org_id = l_org_id;
2128
2129 cursor c_ou_curr_code_fcst (l_org_id Number)
2130 is
2131 SELECT FC.Currency_Code
2132 FROM FND_CURRENCIES FC,
2133 GL_SETS_OF_BOOKS GB,
2134 PA_IMPLEMENTATIONS_ALL IMP
2135 WHERE FC.Currency_Code = DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
2136 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID
2137 AND IMP.Org_Id = l_org_id;
2138
2139 l_provider_org_id pa_implementations.org_id%type;
2140
2141 BEGIN
2142 pa_debug.Set_err_stack ('Get_Provider_Attributes');
2143 pa_debug.G_Err_Stage := 'Inside Get_Provider_Attributes';
2144 IF g1_debug_mode = 'Y' THEN
2145 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2146 END IF;
2147
2148 If G_Calling_module = 'FORECAST' Then
2149 Begin
2150 For i in 1 .. G_array_size Loop
2151 pa_debug.G_Err_Stage := 'i: '||to_char(i)||' error code : '||x_error_code(i);
2152 IF g1_debug_mode = 'Y' THEN
2153 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2154 END IF;
2155
2156
2157 l_provider_org_id := nvl(p_prvdr_operating_unit(i),-1);
2158 If G_prvdr_org_id_tab.Exists(l_provider_org_id)
2159 Then Null;
2160 IF g1_debug_mode = 'Y' THEN
2161 pa_debug.write_file('LOG','i :'||to_char(i)||' Exist ');
2162 END IF;
2163 Else
2164 open c_ou_attributes_fcst (l_provider_org_id);
2165 fetch c_ou_attributes_fcst
2166 into G_prvdr_org_id_tab(l_provider_org_id),
2167 G_bg_id_tab(l_provider_org_id),
2168 G_cc_default_rate_type_tab(l_provider_org_id),
2169 G_cc_def_rate_date_code_tab(l_provider_org_id),
2170 G_exp_org_struct_ver_tab(l_provider_org_id);
2171 close c_ou_attributes_fcst;
2172
2173 open c_ou_curr_code_fcst (l_provider_org_id);
2174
2175 fetch c_ou_curr_code_fcst
2176 into G_acct_currency_code_Tab (l_provider_org_id);
2177 Close c_ou_curr_code_fcst ;
2178
2179
2180 End If; /** G_prvdr_org_id_tab.Exists **/
2181
2182 pa_debug.G_Err_Stage := 'I : '||to_char(i)||' p_prvdr_operating_unit(i) '||to_char(l_provider_org_id)||
2183 ' G_prvdr_org_id_tab(l_provider_org_id): '||to_char(G_prvdr_org_id_tab(l_provider_org_id));
2184
2185 IF g1_debug_mode = 'Y' THEN
2186 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2187 END IF;
2188
2189 End loop;
2190 End;
2191 Else /** Since Calling Module is Not FORECAST Code need not be independant of OU **/
2192 open c_ou_attributes;
2193
2194 --DevDrop2 Changes
2195 --Added G_exp_org_struct_ver_id variable.
2196
2197 fetch c_ou_attributes
2198 into G_prvdr_org_id,G_bg_id,G_cc_default_rate_type,
2199 G_cc_default_rate_date_code,
2200 G_exp_org_struct_ver_id;
2201
2202 close c_ou_attributes;
2203
2204 -- Get accounting currency code of the provider
2205 G_acct_currency_code := PA_MULTI_CURRENCY.Get_Acct_Currency_Code;
2206 End If; /** End p_module_name = 'FORECAST' **/
2207 pa_debug.Reset_err_stack;
2208
2209 EXCEPTION
2210 when others then
2211 raise;
2212
2213 END Get_Provider_Attributes;
2214 --------------------------------------------------------------------------------
2215 PROCEDURE Validate_Array
2216 (
2217 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2218 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab ,
2219 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2220 p_denom_tp_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
2221 p_acct_currency_code IN varchar2 ,
2222 p_denom_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp,
2223 p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
2224 p_acct_tp_rate_date IN PA_PLSQL_DATATYPES.DateTabTyp,
2225 p_acct_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp,
2226 p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
2227 x_compute_flag IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
2228 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
2229 )
2230 IS
2231 l_acct_currency_code FND_CURRENCIES.CURRENCY_CODE%TYPE ; /** Added for Org Forecasting **/
2232 BEGIN
2233 pa_debug.Set_err_stack ('Validate_Array');
2234 pa_debug.G_Err_Stage := 'Starting Validate_Array';
2235 IF g1_debug_mode = 'Y' THEN
2236 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2237 END IF;
2238
2239 l_acct_currency_code := p_acct_currency_code; /** Added for Org Forecasting **/
2240
2241 For i in 1 .. G_array_Size
2242 Loop
2243 pa_debug.G_Err_Stage := 'Start Loop';
2244 -- Consider records with error_code is null and compute_flag = 'Y'
2245 IF (x_compute_flag(i) = 'Y' ) THEN
2246 IF ( x_error_code(i) IS NULL ) THEN
2247 -- Check if transfer price is already available -
2248 pa_debug.G_Err_Stage := 'Flagged as error-free';
2249 IF g1_debug_mode = 'Y' THEN
2250 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2251 END IF;
2252
2253 IF G_Calling_Module = 'FORECAST' Then /** Added for Org Forecasting **/
2254 l_acct_currency_code := G_acct_currency_code_Tab(p_prvdr_operating_unit(i));
2255 END IF; /** Module Name = FORECAST **/
2256
2257 IF (p_denom_transfer_price(i) IS NOT NULL
2258 and nvl(p_denom_tp_currency_code(i),'-1') <> nvl(l_acct_currency_code,'-1')) THEN
2259 /** Changed p_acct_currency_code to l_acct_currency_code for Org Forecasting **/
2260 pa_debug.G_Err_Stage := 'Reconversion Case';
2261 IF g1_debug_mode = 'Y' THEN
2262 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2263 END IF;
2264 -- Check if all conversion attributes are provided
2265
2266 IF( ( (p_acct_tp_rate_type(i) IS NOT NULL) AND
2267 (p_acct_tp_rate_date(i) IS NOT NULL) )
2268 OR
2269 ((p_acct_tp_rate_type(i) = 'User') AND
2270 (p_acct_tp_exchange_rate(i) IS NOT NULL) ) ) THEN
2271
2272
2273 -- Only reconversion needed
2274
2275 x_compute_flag(i) := 'C';
2276
2277 ELSE
2278 x_error_code(i) := 'PA_CC_TP_CURR_CONVERSION_ATTR';
2279 END IF;
2280
2281 ELSE
2282 pa_debug.G_Err_Stage := 'Calculate Transfer Price';
2283 -- Need to calculate transfer price
2284
2285 -- Check if schedule_id is provided
2286 IF p_tp_schedule_id(i) IS NULL THEN
2287 x_error_code(i) := 'PA_CC_TP_SCH_ID_NULL';
2288 END IF;
2289
2290 END IF; /** p_denom_transfer_price IS NOT NULL **/
2291 END IF;
2292
2293 END IF; /**x_compute_flag(i) = 'Y' AND x_error_code(i) IS NULL **/
2294
2295 END Loop;
2296
2297 pa_debug.Reset_err_stack;
2298 pa_debug.G_Err_Stage := 'Exitting Validate_Array ';
2299 IF g1_debug_mode = 'Y' THEN
2300 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2301 END IF;
2302
2303 EXCEPTION
2304 when others then
2305 raise;
2306 END Validate_Array;
2307 --------------------------------------------------------------------------------
2308 PROCEDURE Get_Schedule_Line(
2309 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2310 /* Start Added for 3118101 */
2311 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
2312 /* End Added for 3118101 */
2313 p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2314 p_recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2315 p_recvr_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2316 p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
2317 p_tp_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2318 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
2319 --Start Added for devdrop2
2320 p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
2321 p_prvdr_operating_unit IN PA_PLSQL_DATATYPES.IdTabTyp
2322 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab ,
2323 /** Added for Org Forecasting **/
2324
2325 --End Added for devdrop2
2326 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
2327 x_tp_schedule_line_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
2328 x_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
2329 x_tp_rule_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
2330 )
2331 IS
2332 l_tp_schedule_line_id Number;
2333 l_start_date_active Date;
2334 l_end_date_active Date;
2335 l_tp_rule_id Number;
2336 l_percentage_applied Number;
2337 p_sort_order Number; -- Added for bug 5753774
2338
2339 BEGIN
2340
2341 pa_debug.Set_err_stack ('Get_Schedule_Line');
2342 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line';
2343 IF g1_debug_mode = 'Y' THEN
2344 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2345 END IF;
2346
2347 For i in 1 ..G_array_Size
2348 Loop
2349 If G_Calling_module = 'FORECAST' Then /** Added for Org Forecasting **/
2350
2351 pa_debug.G_Err_Stage := 'p_prvdr_operating_unit: '||
2352 to_char(p_prvdr_operating_unit(i));
2353 IF g1_debug_mode = 'Y' THEN
2354 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2355 END IF;
2356
2357 Set_Global_Variables (p_prvdr_operating_unit(i));
2358 Get_legal_entity (G_prvdr_org_id,G_prvdr_legal_entity_id);
2359 End If;
2360
2361
2362 pa_debug.G_Err_Stage := 'Processing Expenditure ID: '||
2363 to_char(p_expenditure_item_id(i));
2364 IF g1_debug_mode = 'Y' THEN
2365 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2366 END IF;
2367
2368 pa_debug.G_Err_Stage := 'p_compute_flag: '||p_compute_flag(i);
2369 IF g1_debug_mode = 'Y' THEN
2370 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2371 END IF;
2372
2373 pa_debug.G_Err_Stage := 'x_error_code: '||x_error_code(i);
2374 IF g1_debug_mode = 'Y' THEN
2375 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2376 END IF;
2377
2378 IF (p_compute_flag(i) = 'Y' AND
2379 x_error_code(i) is null ) THEN
2380
2381 --check if the schedule line already exists in PA_CC_TP_SCHEDULE_LINE_LOOKUP
2382 Get_Schedule_Line_From_Lookup(
2383 p_prvdr_organization_id (i),
2384 p_recvr_org_id (i),
2385 p_recvr_organization_id(i),
2386 p_tp_schedule_id (i),
2387 p_labor_non_labor_flag(i),
2388 --Start Added for devdrop2
2389 p_tp_amt_type_code(i),
2390 --End Added for devdrop2
2391 /* Start Added for 3118101 */
2392 p_expenditure_item_date(i),
2393 /* End Added for 3118101 */
2394
2395 x_tp_schedule_line_id(i) );
2396
2397 IF x_tp_schedule_line_id(i) IS NULL THEN
2398 -- Find out schedule_line_id
2399
2400 pa_debug.G_Err_Stage := 'Find out Schedule_Line_ID';
2401 Determine_Schedule_Line(
2402 p_prvdr_organization_id(i),
2403 p_recvr_org_id(i),
2404 p_recvr_organization_id(i),
2405 p_tp_schedule_id(i),
2406 p_labor_non_labor_flag(i),
2407 /* Start Added for 3118101 */
2408 p_expenditure_item_date(i),
2409 /* End Added for 3118101 */
2410 --Start Added for devdrop2
2411 p_tp_amt_type_code(i),
2412 --End Added for devdrop2
2413 x_tp_schedule_line_id(i),
2414 l_tp_rule_id,
2415 l_percentage_applied,
2416 l_start_date_active,
2417 l_end_date_active,
2418 p_sort_order, -- added for bug 5753774
2419 x_error_code(i) );
2420
2421 IF g1_debug_mode = 'Y' THEN
2422 pa_debug.write_file('LOG','Error Code'||x_error_code(i));
2423 END IF;
2424
2425 l_tp_schedule_line_id := x_tp_schedule_line_id(i);
2426
2427 IF ( x_error_code(i) IS NULL ) THEN
2428 -- No error from Determine_Schedule_Line
2429
2430 pa_debug.G_Err_Stage := 'Insert Schedule Line into Lookup';
2431 -- pa_debug.G_Err_Stage := 'Sort order is :'||x_sort_order ;/*bug 5753774*/
2432
2433 Insert_Schedule_Line_Into_Lkp(
2434 p_prvdr_organization_id(i),
2435 p_recvr_org_id (i),
2436 p_recvr_organization_id(i),
2437 p_tp_schedule_id (i),
2438 l_tp_schedule_line_id,
2439 p_labor_non_labor_flag(i),
2440 --Start Added for devdrop2
2441 p_tp_amt_type_code(i),
2442 --End Added for devdrop2
2443 l_start_date_active,
2444 l_end_date_active,
2445 p_sort_order, -- added for bug 5753774
2446 x_error_code(i)
2447 );
2448 END IF;
2449
2450 ELSE
2451
2452 l_tp_schedule_line_id := x_tp_schedule_line_id(i);
2453 -- Get Schedule Line attributes
2454 pa_debug.G_Err_Stage := 'Get Schedule Line Attributes';
2455
2456 Get_Schedule_Line_Attributes(
2457 l_tp_schedule_line_id,
2458 p_labor_non_labor_flag(i),
2459 l_tp_rule_id,
2460 l_percentage_applied,
2461 x_error_code(i) );
2462 END IF;
2463 -- Set the OUT variables
2464 x_tp_schedule_line_id(i) := l_tp_schedule_line_id;
2465 x_tp_schedule_line_percentage(i) := l_percentage_applied;
2466 x_tp_rule_id (i) := l_tp_rule_id;
2467 pa_debug.G_Err_Stage := 'Rule ID is '||to_char(x_tp_rule_id(i));
2468 IF g1_debug_mode = 'Y' THEN
2469 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2470 END IF;
2471
2472
2473 END IF; /** p_compute_flag = 'Y' and x_error_code is null **/
2474
2475 pa_debug.G_Err_Stage := 'Completed Processing the item';
2476 IF g1_debug_mode = 'Y' THEN
2477 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2478 END IF;
2479 pa_debug.G_Err_Stage := '..................................................';
2480 IF g1_debug_mode = 'Y' THEN
2481 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2482 END IF;
2483
2484 End Loop;
2485
2486 pa_debug.Reset_err_stack;
2487 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line';
2488 IF g1_debug_mode = 'Y' THEN
2489 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2490 END IF;
2491
2492 EXCEPTION
2493 WHEN OTHERS THEN
2494 raise;
2495
2496 END Get_Schedule_Line;
2497 ------------------------------------------------------------------------------
2498 PROCEDURE Get_Schedule_Line_From_Lookup(
2499 p_prvdr_organization_id IN Number,
2500 p_recvr_org_id IN Number,
2501 p_recvr_organization_id IN Number,
2502 p_tp_schedule_id IN Number,
2503 p_labor_flag IN Varchar2,
2504 --Start Added for devdrop2
2505 p_tp_amt_type_code IN Varchar2,
2506 --End Added for devdrop2
2507 /* Start Added for 3118101 */
2508 p_expenditure_item_date IN Date,
2509 /* End Added for 3118101 */
2510 x_tp_schedule_line_id OUT NOCOPY Number /*File.sql.39*/
2511 )
2512 IS
2513
2514 Cursor c_schedule_line
2515 is
2516 select tp_schedule_line_id
2517 from PA_CC_TP_SCHEDULE_LINE_LKP
2518 where
2519 tp_schedule_id = p_tp_schedule_id
2520 and prvdr_organization_id = p_prvdr_organization_id
2521 and recvr_organization_id = p_recvr_organization_id
2522 and prvdr_org_id = G_prvdr_org_id
2523 and recvr_org_id = p_recvr_org_id
2524 and labor_flag = p_labor_flag
2525 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2526 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2527 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2528 /* and trunc(G_processed_thru_date) between Commented for 3118101 */
2529 and trunc(p_expenditure_item_date) between /* Added for 3118101 */
2530 trunc(start_date_active) and
2531 /* trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2532 trunc(NVL(end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2533 ORDER BY sort_order; /*Bug 5753774*/
2534
2535 BEGIN
2536 pa_debug.Set_err_stack ('Get_Schedule_Line_From_Lookup');
2537 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line_From_Lookup';
2538 IF g1_debug_mode = 'Y' THEN
2539 Pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2540
2541 Pa_debug.write_file('LOG','prvdr_oranz_id '||p_prvdr_organization_id||
2542 ' recv_organz_id '||p_recvr_organization_id||
2543 'prvdr_org_id '||G_prvdr_org_id||
2544 'p_recvr_org_id '||p_recvr_org_id||
2545 ' sch_id '||p_tp_schedule_id);
2546 Pa_debug.write_file('LOG','p_labor_flag '||p_labor_flag||
2547 ' tp_amt_type_code '||p_tp_amt_type_code||
2548 ' p_expenditure_item_date '||to_char(p_expenditure_item_date)); /* Added for 3118101 */
2549 END IF;
2550
2551 open c_schedule_line;
2552
2553 fetch c_schedule_line
2554 into x_tp_schedule_line_id;
2555
2556 close c_schedule_line;
2557
2558
2559 pa_debug.Reset_err_stack;
2560 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line_From_Lookup';
2561 IF g1_debug_mode = 'Y' THEN
2562 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2563 END IF;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567 raise;
2568 END Get_Schedule_Line_From_Lookup;
2569 -------------------------------------------------------------------------------
2570 PROCEDURE Determine_Schedule_Line(
2571 p_prvdr_organization_id IN Number,
2572 p_recvr_org_id IN Number,
2573 p_recvr_organization_id IN Number,
2574 p_tp_schedule_id IN Number,
2575 p_labor_non_labor_flag IN Varchar2,
2576 /* Start Added for 3118101 */
2577 p_expenditure_item_date IN Date,
2578 /* End Added for 3118101 */
2579 --Start Added for devdrop2
2580 p_tp_amt_type_code IN Varchar2,
2581 --End Added for devdrop2
2582 x_tp_schedule_line_id OUT NOCOPY Number,/*File.sql.39*/
2583 x_tp_rule_id OUT NOCOPY Number,/*File.sql.39*/
2584 x_percentage_applied OUT NOCOPY Number,/*File.sql.39*/
2585 x_start_date_active OUT NOCOPY Date,/*File.sql.39*/
2586 x_end_date_active OUT NOCOPY Date,/*File.sql.39*/
2587 x_sort_order OUT NOCOPY NUMBER/*bug 5753774*/,
2588 x_error_code IN OUT NOCOPY VARCHAR2 /*File.sql.39*/
2589 )
2590 IS
2591
2592 cursor c_rule( l_prvdr_organization_id Number,
2593 l_recvr_organization_id Number)
2594 is
2595 select tp_schedule_line_id,sort_order, -- Added for bug 5753774
2596 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2597 nl_tp_rule_id) rule_id,
2598 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2599 nl_percentage_applied) percentage_applied
2600 ,start_date_active
2601 ,end_date_active
2602 from pa_cc_tp_schedule_lines
2603 where tp_schedule_id = p_tp_schedule_id
2604 and prvdr_organization_id = l_prvdr_organization_id
2605 and recvr_organization_id = l_recvr_organization_id
2606 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2607 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2608 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2609 and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
2610 OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
2611 /* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
2612 and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
2613 /* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2614 and trunc(NVL(end_date_active,p_expenditure_item_date)); /* Added for 3118101 */
2615
2616 --DevDrop2 Changes start
2617
2618 cursor c_parent_rule ( l_prvdr_organization_id Number,
2619 l_recvr_organization_id Number)
2620 is
2621 select a.tp_schedule_line_id,sort_order, -- Added for bug 5753774
2622 Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
2623 a.nl_tp_rule_id) rule_id,
2624 Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
2625 a.nl_percentage_applied) percentage_applied,
2626 a.start_date_active,
2627 a.end_date_active
2628 from pa_cc_tp_schedule_lines a,
2629 pa_org_hierarchy_denorm b,
2630 pa_org_hierarchy_denorm c
2631 where a.tp_schedule_id = p_tp_schedule_id
2632 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2633 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2634 and a.RECVR_ORGANIZATION_ID = c.PARENT_ORGANIZATION_ID
2635 and c.CHILD_ORGANIZATION_ID = l_recvr_organization_id
2636 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2637 and c.org_hierarchy_version_id = G_prj_org_struct_ver_id
2638 and b.pa_org_use_type = 'TP_SCHEDULE'
2639 and c.pa_org_use_type = 'TP_SCHEDULE'
2640 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2641 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2642 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2643 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2644 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2645 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2646 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2647 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2648 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2649 order by b.parent_level desc , c.PARENT_LEVEL desc;
2650
2651
2652 cursor c_prvdr_organz_rule( l_prvdr_organization_id Number,
2653 l_recvr_organization_id Number)
2654 is
2655 select tp_schedule_line_id,sort_order, -- Added for bug 5753774
2656 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2657 nl_tp_rule_id) rule_id,
2658 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2659 nl_percentage_applied) percentage_applied
2660 ,start_date_active
2661 ,end_date_active
2662 from pa_cc_tp_schedule_lines a,
2663 pa_org_hierarchy_denorm b
2664 where a.tp_schedule_id = p_tp_schedule_id
2665 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2666 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2667 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2668 and a.recvr_organization_id = l_recvr_organization_id
2669 and b.pa_org_use_type = 'TP_SCHEDULE'
2670 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2671 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2672 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2673 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2674 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2675 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2676 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2677 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2678 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2679 order by b.parent_level desc;
2680
2681 --DevDrop2 Changes End
2682
2683
2684 --DevDrop2 Changes
2685 --Added pa_org_hierarchy_denorm join to the below cursor.
2686
2687
2688 cursor c_other_rule(l_prvdr_organization_id NUMBER)
2689 is
2690 select a.tp_schedule_line_id,sort_order, -- Added for Bug 5753774
2691 Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
2692 a.nl_tp_rule_id) rule_id,
2693 Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
2694 a.nl_percentage_applied) percentage_applied
2695 ,a.start_date_active
2696 ,a.end_date_active
2697 from pa_cc_tp_schedule_lines a,
2698 pa_org_hierarchy_denorm b
2699 where a.tp_schedule_id = p_tp_schedule_id
2700 and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
2701 and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
2702 and a.recvr_organization_id is null
2703 and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
2704 and b.pa_org_use_type = 'TP_SCHEDULE'
2705 and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
2706 'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
2707 tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
2708 and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
2709 OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
2710 /* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
2711 and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
2712 /* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
2713 and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
2714 order by b.parent_level desc;
2715
2716 cursor c_default_rule
2717 is
2718 select tp_schedule_line_id,sort_order, --Added for bug 5753774
2719 Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
2720 nl_tp_rule_id) rule_id,
2721 Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
2722 nl_percentage_applied) percentage_applied
2723 ,start_date_active
2724 ,end_date_active
2725 from pa_cc_tp_schedule_lines
2726 where tp_schedule_id = p_tp_schedule_id
2727 and default_flag ='Y'
2728 and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
2729 OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
2730 /* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
2731 and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
2732 /* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
2733 and trunc(NVL(end_date_active,p_expenditure_item_date)); /* Added for 3118101 */
2734
2735 l_recvr_legal_entity_id Number;
2736 l_recvr_business_group_id number;
2737
2738 BEGIN
2739 pa_debug.Set_err_stack ('Determine_Schedule_Line');
2740 pa_debug.G_Err_Stage := 'Starting Determine_Schedule_Line';
2741 IF g1_debug_mode = 'Y' THEN
2742 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2743
2744
2745 pa_debug.write_file('LOG',
2746 'p_prvdr_organization_id '||p_prvdr_organization_id);
2747 pa_debug.write_file('LOG',
2748 'p_recvr_org_id '||p_recvr_org_id);
2749 pa_debug.write_file('LOG',
2750 'p_recvr_organization_id '||p_recvr_organization_id);
2751 pa_debug.write_file('LOG',
2752 'p_recvr_org_id '||p_recvr_org_id);
2753 pa_debug.write_file('LOG',
2754 'p_tp_schedule_id '||p_tp_schedule_id);
2755 pa_debug.write_file('LOG',
2756 'p_labor_non_labor_flag '||p_labor_non_labor_flag);
2757 pa_debug.write_file('LOG',
2758 'p_tp_amt_type_code '||p_tp_amt_type_code);
2759 pa_debug.write_file('LOG',
2760 'G_exp_org_struct_ver_id '||G_exp_org_struct_ver_id);
2761 pa_debug.write_file('LOG',
2762 'G_prj_org_struct_ver_id '||G_prj_org_struct_ver_id);
2763 END IF;
2764
2765 -- Use Rule1
2766
2767
2768 IF g1_debug_mode = 'Y' THEN
2769 pa_debug.write_file('LOG','Testing Rule 1');
2770 END IF;
2771
2772 open c_rule (p_prvdr_organization_id,p_recvr_organization_id);
2773 fetch c_rule
2774 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied, -- added for bug 5753774
2775 x_start_date_active,x_end_date_active;
2776
2777 IF c_rule%FOUND THEN
2778 IF (x_tp_rule_id IS NOT NULL) THEN
2779 close c_rule;
2780 pa_debug.Reset_err_stack;
2781 pa_debug.G_Err_Stage := 'Schedule Line found using Rule1';
2782 IF g1_debug_mode = 'Y' THEN
2783 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2784 pa_debug.write_file('LOG','x_tp_schedule_line_id='||x_tp_schedule_line_id); -- Added for bug 5753774
2785 pa_debug.write_file('LOG','x_sort_order='||x_sort_order);
2786
2787 END IF;
2788 Return;
2789 END IF;
2790 END IF;
2791
2792 close c_rule;
2793
2794 --DevDrop2 Changes Start
2795
2796 IF g1_debug_mode = 'Y' THEN
2797 pa_debug.write_file('LOG','Testing Parent Rule ');
2798 END IF;
2799
2800 if ( nvl(G_prev_rcvr_org_id,-1) <> nvl(p_recvr_org_id,-1) ) then
2801 G_prev_rcvr_org_id := p_recvr_org_id;
2802 select PROJ_ORG_STRUCTURE_VERSION_ID into G_prj_org_struct_ver_id
2803 from pa_implementations_all
2804 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 */
2805 end if;
2806
2807
2808 open c_parent_rule (p_prvdr_organization_id,p_recvr_organization_id);
2809 fetch c_parent_rule
2810 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied, --added for bug 5753774
2811 x_start_date_active,x_end_date_active;
2812
2813 IF c_parent_rule%FOUND THEN
2814 IF (x_tp_rule_id IS NOT NULL) THEN
2815 close c_parent_rule;
2816 pa_debug.Reset_err_stack;
2817 pa_debug.G_Err_Stage := 'Schedule Line found using Parent Rule';
2818 IF g1_debug_mode = 'Y' THEN
2819 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2820 pa_debug.write_file('LOG','x_tp_schedule_line_id='||x_tp_schedule_line_id); -- Added for bug 5753774
2821 pa_debug.write_file('LOG','x_sort_order='||x_sort_order);
2822 END IF;
2823 Return;
2824 END IF;
2825 END IF;
2826
2827
2828 --DevDrop2 Changes End
2829
2830 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2831
2832 IF g1_debug_mode = 'Y' THEN
2833 pa_debug.write_file('LOG','Testing Rule 2');
2834 END IF;
2835
2836 /* **************************************************************
2837 COMMENTED FOR LEGAL ENTITY TRANSFER PRICE SCHEDULE. aFTER 12.0 THERE
2838 IS NO CONCEPT OF OU,LE AND BG check every thing should be maintained
2839 in org hierarchy
2840
2841 -- Use Rule2
2842 open c_prvdr_organz_rule (p_prvdr_organization_id,p_recvr_org_id);
2843 fetch c_prvdr_organz_rule
2844 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied --Added for bug 5753774
2845 ,x_start_date_active,x_end_date_active;
2846
2847
2848 IF c_prvdr_organz_rule%FOUND THEN
2849 IF (x_tp_rule_id IS NOT NULL) THEN
2850 close c_prvdr_organz_rule;
2851 pa_debug.Reset_err_stack;
2852 pa_debug.G_Err_Stage := 'Schedule Line found using Rule2';
2853 IF g1_debug_mode = 'Y' THEN
2854 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2855 END IF;
2856 Return;
2857 END IF;
2858 END IF;
2859
2860 close c_prvdr_organz_rule;
2861
2862 -- Get receiver's legal entity id
2863 Get_Legal_Entity (p_recvr_org_id, l_recvr_legal_entity_id);
2864
2865
2866 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2867 -- Use Rule3
2868 open c_prvdr_organz_rule (p_prvdr_organization_id,l_recvr_legal_entity_id);
2869 fetch c_prvdr_organz_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 g1_debug_mode = 'Y' THEN
2874 pa_debug.write_file('LOG','Testing Rule 3');
2875 END IF;
2876 IF c_prvdr_organz_rule%FOUND THEN
2877 IF (x_tp_rule_id IS NOT NULL) THEN
2878 close c_prvdr_organz_rule;
2879 pa_debug.Reset_err_stack;
2880 pa_debug.G_Err_Stage := 'Schedule Line found using Rule3';
2881 IF g1_debug_mode = 'Y' THEN
2882 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2883 END IF;
2884 Return;
2885 END IF;
2886 END IF;
2887
2888 close c_prvdr_organz_rule;
2889
2890 --Get receiver's business group id
2891 get_business_group(p_recvr_org_id, l_recvr_business_group_id);
2892
2893 --Devdrop2 Changes Changed c_rule cursor to c_prvdr_organz_rule
2894 --Use Rule4
2895 if G_global_access = 'Y' then
2896 open c_prvdr_organz_rule(p_prvdr_organization_id,l_recvr_business_group_id);
2897 fetch c_prvdr_organz_rule
2898 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied --Added for bug 5753774
2899 ,x_start_date_active,x_end_date_active;
2900 IF g1_debug_mode = 'Y' THEN
2901 pa_debug.write_file('LOG','Testing Rule 4');
2902 END IF;
2903 IF c_prvdr_organz_rule%FOUND THEN
2904 IF (x_tp_rule_id IS NOT NULL) THEN
2905 close c_prvdr_organz_rule;
2906 pa_debug.Reset_err_stack;
2907 pa_debug.G_Err_Stage := 'Schedule Line found using Rule4';
2908 IF g1_debug_mode = 'Y' THEN
2909 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2910 END IF;
2911 Return;
2912 END IF;
2913 END IF;
2914
2915 close c_prvdr_organz_rule;
2916 end if; ****************************************************************
2917 ***************************************************End for comment for Legal Entity*/
2918
2919
2920 -- Use Rule2
2921 IF g1_debug_mode = 'Y' THEN
2922 pa_debug.write_file('LOG','Testing Rule 2');
2923 END IF;
2924 open c_other_rule (p_prvdr_organization_id);
2925 fetch c_other_rule
2926 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
2927 ,x_start_date_active,x_end_date_active;
2928
2929 IF c_other_rule%FOUND THEN
2930 IF (x_tp_rule_id IS NOT NULL) THEN
2931 close c_other_rule;
2932 pa_debug.Reset_err_stack;
2933 pa_debug.G_Err_Stage := 'Schedule Line found using Rule5';
2934 IF g1_debug_mode = 'Y' THEN
2935 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2936 END IF;
2937 Return;
2938 END IF;
2939 END IF;
2940
2941 close c_other_rule;
2942
2943 /* ***************************************************************
2944 COMMENTED FOR LEGAL ENTITY TRANSFER PRICE SCHEDULE. aFTER 12.0 THERE
2945 IS NO CONCEPT OF OU,LE AND BG check every thing should be maintained
2946 in org hierarchy
2947 -- Use Rule6
2948 IF g1_debug_mode = 'Y' THEN
2949 pa_debug.write_file('LOG','Testing Rule 6');
2950 END IF;
2951 open c_rule (G_Prvdr_Org_id,p_recvr_org_id);
2952 fetch c_rule
2953 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
2954 ,x_start_date_active,x_end_date_active;
2955
2956 IF c_rule%FOUND THEN
2957 IF (x_tp_rule_id IS NOT NULL) THEN
2958 close c_rule;
2959 pa_debug.Reset_err_stack;
2960 pa_debug.G_Err_Stage := 'Schedule Line found using Rule6';
2961 IF g1_debug_mode = 'Y' THEN
2962 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2963 END IF;
2964 Return;
2965 END IF;
2966 END IF;
2967
2968 close c_rule;
2969
2970 -- Use Rule7
2971 IF g1_debug_mode = 'Y' THEN
2972 pa_debug.write_file('LOG','Testing Rule 7');
2973 END IF;
2974 open c_rule (G_prvdr_org_id,l_recvr_legal_entity_id);
2975 fetch c_rule
2976 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
2977 , x_start_date_active,x_end_date_active;
2978
2979 IF c_rule%FOUND THEN
2980 IF (x_tp_rule_id IS NOT NULL) THEN
2981 close c_rule;
2982 pa_debug.Reset_err_stack;
2983 pa_debug.G_Err_Stage := 'Schedule Line found using Rule7';
2984 IF g1_debug_mode = 'Y' THEN
2985 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
2986 END IF;
2987 Return;
2988 END IF;
2989 END IF;
2990
2991 close c_rule;
2992
2993 --Use Rule 8
2994 if G_global_access='Y' then
2995 IF g1_debug_mode = 'Y' THEN
2996 pa_debug.write_file('LOG','Testing Rule 8');
2997 END IF;
2998 open c_rule(G_prvdr_org_id,l_recvr_business_group_id);
2999 fetch c_rule
3000 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3001 , x_start_date_active,x_end_date_active;
3002 IF c_rule%FOUND THEN
3003 IF (x_tp_rule_id IS NOT NULL) THEN
3004 close c_rule;
3005 pa_debug.Reset_err_stack;
3006 pa_debug.G_Err_Stage := 'Schedule Line found using Rule8';
3007 IF g1_debug_mode = 'Y' THEN
3008 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3009 END IF;
3010 Return;
3011 END IF;
3012 END IF;
3013
3014 close c_rule;
3015 end if;
3016
3017 -- Use Rule9
3018 IF g1_debug_mode = 'Y' THEN
3019 pa_debug.write_file('LOG','Testing Rule 9 '|| to_char(G_prvdr_org_id));
3020 END IF;
3021 open c_other_rule (G_prvdr_org_id);
3022 fetch c_other_rule
3023 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3024 ,x_start_date_active,x_end_date_active;
3025
3026 IF c_other_rule%FOUND THEN
3027 pa_debug.G_Err_Stage := 'Cursor matched Rule9';
3028 IF g1_debug_mode = 'Y' THEN
3029 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3030 END IF;
3031
3032 IF (x_tp_rule_id IS NOT NULL) THEN
3033 close c_other_rule;
3034 pa_debug.Reset_err_stack;
3035 pa_debug.G_Err_Stage := 'Schedule Line found using Rule9';
3036 IF g1_debug_mode = 'Y' THEN
3037 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3038 END IF;
3039 Return;
3040 END IF;
3041 END IF;
3042
3043 close c_other_rule;
3044
3045
3046 -- Use Rule10
3047 IF g1_debug_mode = 'Y' THEN
3048 pa_debug.write_file('LOG','Testing Rule 10');
3049 END IF;
3050 open c_rule (G_prvdr_legal_entity_id,l_recvr_legal_entity_id);
3051 fetch c_rule
3052 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3053 ,x_start_date_active,x_end_date_active;
3054
3055 IF c_rule%FOUND THEN
3056 IF (x_tp_rule_id IS NOT NULL) THEN
3057 close c_rule;
3058 pa_debug.Reset_err_stack;
3059 pa_debug.G_Err_Stage := 'Schedule Line found using Rule10';
3060 IF g1_debug_mode = 'Y' THEN
3061 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3062 END IF;
3063 Return;
3064 END IF;
3065 END IF;
3066
3067 close c_rule;
3068
3069 --Use Rule 11
3070 if G_global_access ='Y' then
3071 IF g1_debug_mode = 'Y' THEN
3072 pa_debug.write_file('LOG','Testing Rule 11');
3073 END IF;
3074 open c_rule (G_prvdr_legal_entity_id,l_recvr_business_group_id);
3075 fetch c_rule
3076 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for 5753774
3077 ,x_start_date_active,x_end_date_active;
3078 IF c_rule%FOUND THEN
3079 IF (x_tp_rule_id IS NOT NULL) THEN
3080 close c_rule;
3081 pa_debug.Reset_err_stack;
3082 pa_debug.G_Err_Stage := 'Schedule Line found using Rule11';
3083 IF g1_debug_mode = 'Y' THEN
3084 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3085 END IF;
3086 Return;
3087 END IF;
3088 END IF;
3089
3090 close c_rule;
3091 end if;
3092
3093 -- Use Rule12
3094 IF g1_debug_mode = 'Y' THEN
3095 pa_debug.write_file('LOG','Testing Rule 12');
3096 END IF;
3097 open c_other_rule (G_prvdr_legal_entity_id);
3098 fetch c_other_rule
3099 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3100 ,x_start_date_active,x_end_date_active;
3101
3102 IF c_other_rule%FOUND THEN
3103 IF (x_tp_rule_id IS NOT NULL) THEN
3104 close c_other_rule;
3105 pa_debug.Reset_err_stack;
3106 pa_debug.G_Err_Stage := 'Schedule Line found using Rule12';
3107 IF g1_debug_mode = 'Y' THEN
3108 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3109 END IF;
3110 Return;
3111 END IF;
3112 END IF;
3113 close c_other_rule;
3114
3115 --Use Rule 13
3116 if G_global_access='Y' then
3117 IF g1_debug_mode = 'Y' THEN
3118 pa_debug.write_file('LOG','Testing Rule 13');
3119 END IF;
3120 open c_rule(G_bg_id,l_recvr_business_group_id);
3121 fetch c_rule
3122 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- Added for bug 5753774
3123 ,x_start_date_active,x_end_date_active;
3124 IF c_rule%FOUND THEN
3125 IF (x_tp_rule_id IS NOT NULL) THEN
3126 close c_rule;
3127 pa_debug.Reset_err_stack;
3128 pa_debug.G_Err_Stage := 'Schedule Line found using Rule13';
3129 IF g1_debug_mode = 'Y' THEN
3130 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3131 END IF;
3132 Return;
3133 END IF;
3134 END IF;
3135 close c_rule;
3136 end if;
3137
3138 -- Use Rule14
3139 open c_other_rule (G_bg_id);
3140 fetch c_other_rule
3141 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3142 ,x_start_date_active,x_end_date_active;
3143
3144 IF c_other_rule%FOUND THEN
3145 IF (x_tp_rule_id IS NOT NULL) THEN
3146 close c_other_rule;
3147 pa_debug.Reset_err_stack;
3148 pa_debug.G_Err_Stage := 'Schedule Line found using Rule14';
3149 IF g1_debug_mode = 'Y' THEN
3150 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3151 END IF;
3152 Return;
3153 END IF;
3154 END IF;
3155
3156 close c_other_rule; *********************************************
3157 *********************************************************End for Legal Entity */
3158
3159 ---Use default rule
3160 open c_default_rule;
3161 fetch c_default_rule
3162 into x_tp_schedule_line_id,x_sort_order,x_tp_rule_id,x_percentage_applied -- added for bug 5753774
3163 ,x_start_date_active,x_end_date_active;
3164 if c_default_rule%found then
3165 IF (x_tp_rule_id IS NOT NULL) THEN
3166 close c_default_rule;
3167 pa_debug.Reset_err_stack;
3168 pa_debug.G_Err_Stage := 'Schedule Line found using default rule';
3169 IF g1_debug_mode = 'Y' THEN
3170 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3171 END IF;
3172 Return;
3173 END IF;
3174 END IF;
3175
3176 x_error_code := 'PA_CC_TP_NO_SCHEDULE_LINE';
3177 pa_debug.Reset_err_stack;
3178 pa_debug.G_Err_Stage := 'Exitting Determine_Schedule_Line';
3179 IF g1_debug_mode = 'Y' THEN
3180 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3181 END IF;
3182
3183 EXCEPTION
3184 WHEN OTHERS THEN
3185 raise;
3186 END Determine_Schedule_Line;
3187
3188 -------------------------------------------------------------------------------
3189 PROCEDURE Insert_Schedule_Line_Into_Lkp(
3190 p_prvdr_organization_id IN Number,
3191 p_recvr_org_id IN Number,
3192 p_recvr_organization_id IN Number,
3193 p_tp_schedule_id IN Number,
3194 p_tp_schedule_line_id IN Number,
3195 p_labor_flag IN Varchar2,
3196 --Start Added for devdrop2
3197 p_tp_amt_type_code IN Varchar2,
3198 --End Added for devdrop2
3199 p_start_date_active IN Date,
3200 p_end_date_active IN Date,
3201 p_sort_order IN Number, -- Added for bug 5753774
3202 x_error_code IN OUT NOCOPY Varchar2 /*File.sql.39*/
3203 )
3204 IS
3205
3206 PRAGMA AUTONOMOUS_TRANSACTION;
3207
3208 BEGIN
3209 pa_debug.Set_err_stack ('Insert_Schedule_Line_Into_Lkp');
3210 pa_debug.G_Err_Stage := 'Starting Insert_Schedule_Line_Into_Lkp';
3211 IF g1_debug_mode = 'Y' THEN
3212 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3213 END IF;
3214
3215 INSERT INTO
3216 PA_CC_TP_SCHEDULE_LINE_LKP
3217 (tp_schedule_id,
3218 tp_schedule_line_id,
3219 prvdr_org_id,
3220 prvdr_organization_id,
3221 recvr_org_id,
3222 recvr_organization_id,
3223 labor_flag,
3224 tp_amt_type_code,
3225 start_date_active,
3226 sort_order, -- Added for bug 5753774
3227 creation_date,
3228 created_by,
3229 last_update_date,
3230 last_updated_by,
3231 end_date_active,
3232 last_update_login)
3233 VALUES
3234 (p_tp_schedule_id,
3235 p_tp_schedule_line_id,
3236 G_prvdr_org_id,
3237 p_prvdr_organization_id,
3238 p_recvr_org_id,
3239 p_recvr_organization_id,
3240 p_labor_flag,
3241 p_tp_amt_type_code,
3242 p_start_date_active,
3243 p_sort_order, -- Added for bug 5753774
3244 G_creation_date,
3245 G_created_by,
3246 G_last_update_date,
3247 G_last_updated_by,
3248 p_end_date_active,
3249 G_last_update_login);
3250
3251 -- commit the autonomous transaction
3252 commit;
3253
3254 pa_debug.Reset_err_stack;
3255 pa_debug.G_Err_Stage := 'Exitting Insert_Schedule_Line_Into_Lkp';
3256 IF g1_debug_mode = 'Y' THEN
3257 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3258 END IF;
3259
3260 EXCEPTION
3261 WHEN Dup_val_on_index THEN
3262 pa_debug.Reset_err_stack;
3263 null;
3264 WHEN OTHERS THEN
3265 raise;
3266
3267 END Insert_Schedule_Line_Into_Lkp;
3268 --------------------------------------------------------------------------------
3269 PROCEDURE Get_Schedule_Line_Attributes(
3270 p_tp_schedule_line_id IN Number,
3271 p_labor_flag IN Varchar2,
3272 x_tp_rule_id OUT NOCOPY Number, /*File.sql.39*/
3273 x_percentage_applied OUT NOCOPY Number, /*File.sql.39*/
3274 x_error_code IN OUT NOCOPY VARCHAR2 /*File.sql.39*/
3275 )
3276 IS
3277
3278 cursor c_schedule_line_attr
3279 is
3280 select
3281 Decode (p_labor_flag ,'Y', labor_tp_rule_id,nl_tp_rule_id ),
3282 Decode (p_labor_flag,'Y',labor_percentage_applied,nl_percentage_applied)
3283 from pa_cc_tp_schedule_lines
3284 where tp_schedule_line_id = p_tp_schedule_line_id;
3285
3286 BEGIN
3287 pa_debug.Set_err_stack ('Get_Schedule_Line_Attributes');
3288 pa_debug.G_Err_Stage := 'Starting Get_Schedule_Line_Attributes';
3289 IF g1_debug_mode = 'Y' THEN
3290 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3291 END IF;
3292
3293 open c_schedule_line_attr;
3294 fetch c_schedule_line_attr
3295 into x_tp_rule_id,x_percentage_applied ;
3296
3297 IF c_schedule_line_attr%NOTFOUND THEN
3298 x_error_code := 'PA_CC_TP_NO_SCHEDULE_LINE_FOR_ID';
3299 END IF;
3300
3301 close c_schedule_line_attr;
3302
3303 pa_debug.Reset_err_stack;
3304 pa_debug.G_Err_Stage := 'Exitting Get_Schedule_Line_Attributes';
3305 IF g1_debug_mode = 'Y' THEN
3306 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3307 END IF;
3308
3309 EXCEPTION
3310 WHEN OTHERS THEN
3311 raise;
3312
3313 END Get_Schedule_Line_Attributes;
3314 --------------------------------------------------------------------------------
3315 PROCEDURE Get_Transfer_Price_Amount
3316 (
3317 p_tp_rule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3318 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3319 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3320 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3321 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3322 p_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3323 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3324 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3325 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3326 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3327 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3328 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3329 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3330 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3331 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
3332 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3333 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3334 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
3335 p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3336 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
3337 p_tp_schedule_line_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
3338 p_tp_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3339 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3340 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3341 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3342 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3343 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3344 x_tp_base_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3345 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3346 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3347 x_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3348 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3349 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
3350 /* Bill rate Discount*/
3351 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
3352 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3353 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.NumTabTyp,
3354 p_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3355 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
3356 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3357 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3358 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3359 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3360 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3361 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
3362 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3363 p_raw_cost_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
3364 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3365 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3366 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3367 p_exp_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3368 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
3369 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3370
3371 p_burden_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3372 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3373 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3374 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3375 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3376 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3377 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3378 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3379 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3380 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3381 /* Added the two parameters for Doosan rate api enhancement */
3382 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
3383 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3384 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp
3385 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3386 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
3387 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
3388
3389 )
3390 IS
3391
3392 l_rate_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
3393 l_calc_method_code PA_PLSQL_DATATYPES.Char1TabTyp;
3394 l_empty_calc_method_code PA_PLSQL_DATATYPES.Char1TabTyp;
3395 l_basis_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3396 l_bill_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3397 l_burden_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3398
3399 BEGIN
3400 pa_debug.Set_err_stack ('Get_Transfer_Price_Amount');
3401 pa_debug.G_Err_Stage := 'Starting Get_Transfer_Price_Amount';
3402 IF g1_debug_mode = 'Y' THEN
3403 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3404 END IF;
3405
3406
3407 l_calc_method_code := l_empty_calc_method_code;
3408
3409 -- Get Rule attributes
3410 Get_Rule_Attributes(
3411 p_tp_rule_id,
3412 p_compute_flag,
3413 l_calc_method_code,
3414 x_cc_markup_base_code,
3415 x_tp_rule_percentage,
3416 l_rate_schedule_id,
3417 x_error_code
3418 );
3419
3420 -- Set base_curr_code and base_amt based on cc_markup_base_code
3421 -- if base_code is 'burden' then recalculate burden amount if needed.
3422 -- Also set the flags basis_compute_flag, bill_rate_compute_flag dependending
3423 -- on calc_method_code.
3424
3425 Set_Base_Amount_And_Flag(
3426 p_expenditure_item_id,
3427 p_expenditure_type,
3428 p_expenditure_item_date,
3429 p_expnd_organization_id,
3430 p_project_id,
3431 p_task_id,
3432 p_tp_fixed_date,
3433 l_calc_method_code,
3434 x_cc_markup_base_code,
3435 p_denom_currency_code,
3436 p_projfunc_currency_code,
3437 p_denom_raw_cost_amount,
3438 p_denom_burdened_cost_amount,
3439 p_raw_revenue_amount,
3440 p_revenue_distributed_flag,
3441 p_compute_flag,
3442 x_tp_ind_compiled_set_id,
3443 x_error_code,
3444 l_basis_compute_flag,
3445 l_bill_rate_compute_flag,
3446 l_burden_rate_compute_flag,
3447 x_tp_base_curr_code,
3448 x_tp_base_amount,
3449 /*Bill rate Discount*/
3450 p_dist_rule,
3451 p_mcb_flag,
3452 p_bill_rate_multiplier,
3453 p_quantity,
3454 p_incurred_by_person_id,
3455 p_raw_cost,
3456 p_labor_schdl_discnt,
3457 p_labor_schdl_fixed_date,
3458 p_bill_job_grp_id,
3459 p_labor_sch_type,
3460 p_project_org_id,
3461 p_project_type,
3462 p_exp_func_curr_code,
3463 p_incurred_by_organz_id,
3464 p_raw_cost_rate,
3465 p_override_to_organz_id,
3466 p_emp_bill_rate_schedule_id,
3467 p_job_bill_rate_schedule_id,
3468 p_exp_raw_cost,
3469 p_assignment_precedes_task,
3470 p_system_linkage_function,
3471 p_assignment_id ,
3472
3473 p_burden_cost ,
3474 p_task_nl_bill_rate_org_id ,
3475 p_proj_nl_bill_rate_org_id ,
3476 p_task_nl_std_bill_rate_sch ,
3477 p_proj_nl_std_bill_rate_sch ,
3478 p_non_labor_resource ,
3479 p_nl_task_sch_date ,
3480 p_nl_proj_sch_date ,
3481 p_nl_task_sch_discount ,
3482 p_nl_proj_sch_discount ,
3483 p_nl_sch_type,
3484 /*Added for Doosan rate api enhancement */
3485 p_task_nl_std_bill_rate_sch_id,
3486 p_proj_nl_std_bill_rate_sch_id,
3487 p_uom_flag);
3488
3489
3490 Determine_Transfer_Price
3491 (
3492 p_expenditure_item_id,
3493 p_expnd_organization_id,
3494 p_expenditure_type,
3495 p_expenditure_item_date,
3496 p_tp_fixed_date,
3497 p_system_linkage_function,
3498 p_task_id,
3499 x_tp_base_curr_code,
3500 x_tp_base_amount,
3501 p_tp_schedule_line_percentage,
3502 x_tp_rule_percentage,
3503 p_compute_flag,
3504 p_quantity,
3505 p_incurred_by_person_id,
3506 p_job_id,
3507 l_rate_schedule_id,
3508 p_non_labor_resource,
3509 l_basis_compute_flag,
3510 l_bill_rate_compute_flag,
3511 l_burden_rate_compute_flag,
3512 x_denom_tp_currency_code,
3513 x_denom_transfer_price,
3514 x_tp_ind_compiled_set_id,
3515 x_tp_bill_rate,
3516 x_tp_bill_markup_percentage,
3517 x_tp_job_id,
3518 x_error_code
3519 );
3520
3521
3522 pa_debug.Reset_err_stack;
3523 pa_debug.G_Err_Stage := 'Exitting Get_Transfer_Price_Amount';
3524 IF g1_debug_mode = 'Y' THEN
3525 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3526 END IF;
3527 EXCEPTION
3528 WHEN OTHERS THEN
3529 raise;
3530
3531 END Get_Transfer_Price_Amount;
3532 -------------------------------------------------------------------------------
3533 -- Get rule attributes from pa_cc_tp_rules table
3534
3535 PROCEDURE Get_Rule_Attributes(
3536 p_tp_rule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3537 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3538 x_calc_method_code OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3539 x_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3540 x_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3541 x_schedule_id OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3542 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
3543 )
3544 IS
3545
3546 Cursor c_rule_attributes(l_tp_rule_id Number)
3547 IS
3548 select calc_method_code,markup_calc_base_code,percentage_applied,schedule_id
3549 from pa_cc_tp_rules
3550 where tp_rule_id = l_tp_rule_id;
3551
3552 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
3553
3554 BEGIN
3555 x_schedule_id := l_schedule_id;
3556 pa_debug.Set_err_stack ('Get_Rule_Attributes');
3557 pa_debug.G_Err_Stage := 'Starting Get_Rule_Attributes';
3558 IF g1_debug_mode = 'Y' THEN
3559 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3560 END IF;
3561
3562 For i in 1 .. G_Array_Size
3563 Loop
3564 IF (p_compute_flag(i) = 'Y' and x_error_code(i) is null) THEN
3565 pa_debug.G_Err_Stage := 'Fetching Get_Rule_Attributes';
3566 pa_debug.G_Err_Stage := 'Rule ID is '|| to_char(p_tp_rule_id(i));
3567
3568 open c_rule_attributes (p_tp_rule_id(i));
3569 --open c_rule_attributes (3342);
3570 fetch c_rule_attributes
3571 into x_calc_method_code(i),x_cc_markup_base_code(i),x_rule_percentage(i),
3572 x_schedule_id(i);
3573 close c_rule_attributes;
3574 END IF;
3575
3576 END Loop;
3577
3578
3579 pa_debug.Reset_err_stack;
3580 pa_debug.G_Err_Stage := 'Exitting Get_Rule_Attributes';
3581 IF g1_debug_mode = 'Y' THEN
3582 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3583 END IF;
3584
3585 EXCEPTION
3586
3587 WHEN OTHERS THEN
3588 raise;
3589 END Get_Rule_Attributes;
3590 -------------------------------------------------------------------------------
3591 -- Validate each transaction, set base amount, calculate Burdened Amount
3592 -- if actual burdened amount is not given. Also, set the Basis_Compute_Flag,
3593 -- Bill_Rate_Compute_Flag and Burden_Rate_Compute_flag appropriately
3594 -- using calc_method_code
3595
3596 PROCEDURE Set_Base_Amount_And_Flag(
3597 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3598 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3599 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3600 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3601 P_project_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3602 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3603 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3604 p_calc_method_code IN PA_PLSQL_DATATYPES.Char1TabTyp,
3605 p_cc_markup_base_code IN PA_PLSQL_DATATYPES.Char1TabTyp,
3606 p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3607 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
3608 p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3609 p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3610 p_raw_revenue_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
3611 p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3612 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3613 p_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3614 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
3615 x_basis_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3616 x_bill_rate_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3617 x_burden_rate_compute_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
3618 x_tp_base_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
3619 x_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
3620 /*Bill rate Discount */
3621 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
3622 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
3623 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.NumTabTyp,
3624 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
3625 p_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3626 p_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3627 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
3628 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3629 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3630 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3631 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3632 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
3633 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
3634 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3635 p_raw_cost_rate IN PA_PLSQL_DATATYPES.NumTabTyp,
3636 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3637 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3638 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
3639 p_exp_raw_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3640 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
3641 p_sys_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
3642 p_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3643
3644 p_burden_cost IN PA_PLSQL_DATATYPES.NumTabTyp,
3645 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3646 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
3647 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3648 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
3649 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
3650 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3651 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.DateTabTyp,
3652 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3653 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
3654 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
3655 /* Added the two parameters for Doosan rate api enhancement */
3656 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3657 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
3658 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
3659 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
3660 )
3661
3662 IS
3663 l_base_amount NUMBER;
3664 l_burden_cost Number;
3665 l_denom_burdened_cost_amount Number;
3666 l_burdening_allowed VARCHAR2(1);
3667 l_burden_amt_display_method VARCHAR2(1);
3668 l_error_code VARCHAR2(30);
3669 l_status_code Number;
3670 l_stage Number;
3671 l_fixed_date Date;
3672 l_compiled_multiplier Number;
3673 l_compiled_set_id NUMBER;
3674 l_check_line NUMBER; /* 2469987 */
3675
3676 l_burden_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp; /* 2215942 */
3677 l_burden_calc_amount PA_PLSQL_DATATYPES.NumTabTyp; /* 2215942 */
3678 l_burden_error_code PA_PLSQL_DATATYPES.Char30TabTyp; /* 2215942 */
3679 l_rate_schedule_id PA_PLSQL_DATATYPES.IdTabTyp; /* 2215942 */
3680 l_tp_ind_compiled_set_id PA_PLSQL_DATATYPES.IdTabTyp; /* 2215942 */
3681
3682 l_exp_func_curr_code VARCHAR2(30);
3683 l_sl_function NUMBER;
3684
3685 l_bill_rate NUMBER;
3686 l_adjusted_bill_rate NUMBER; --4038485
3687 l_markup_percentage NUMBER;
3688 l_rev_currency_code VARCHAR2(30);
3689 l_return_status varchar2(240);
3690 l_msg_count NUMBER;
3691 l_msg_data VARCHAR2(240);
3692 l_raw_cost_rate NUMBER;
3693
3694 /* Added for bug 2668753 */
3695 l_project_currency_code varchar2(50) := null;
3696 l_project_raw_cost number := null;
3697 l_project_burdened_cost number := null;
3698 l_proj_func_burdened_cost number := null;
3699 l_exp_func_burdened_cost number := null;
3700
3701 /* Added for bug 2697945 */
3702 l_bill_trans_raw_revenue number := null;
3703 l_bill_trans_currency_code varchar2(50) := null;
3704 /* Added for bug 2820252 */
3705 l_bill_trans_adjusted_revenue number := null;
3706 exp_not_found exception;
3707
3708 l_dist_rule BOOLEAN :=TRUE;/*Added for bug 2863350*/
3709
3710 cursor PROJ_VALUES (p_expenditure_item_id IN NUMBER) IS
3711 select project_raw_cost,
3712 project_currency_code,
3713 project_burdened_cost,
3714 burden_cost,
3715 acct_burdened_cost,
3716 /* Added for bug 2697945 */
3717 bill_trans_raw_revenue,
3718 bill_trans_currency_code,
3719 /* Added for bug 2820252 */
3720 bill_trans_adjusted_revenue
3721 from pa_expenditure_items_all where expenditure_item_id=p_expenditure_item_id;
3722 /* End of Changes for bug 2668753 */
3723 l_nl_bill_rate NUMBER;
3724 l_nl_adjusted_bill_rate NUMBER;--4038485
3725 l_nl_markup_percentage NUMBER;
3726 BEGIN
3727 pa_debug.Set_err_stack ('Set_Base_Amount_And_Flag');
3728 pa_debug.G_Err_Stage := 'Starting Set_Base_Amount_And_Flag';
3729 IF g1_debug_mode = 'Y' THEN
3730 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3731 END IF;
3732
3733 For i in 1 .. G_Array_Size
3734 Loop
3735 -- Initialize the flags
3736
3737 x_basis_compute_flag(i) := 'N';
3738 x_bill_rate_compute_flag(i) := 'N';
3739 x_burden_rate_compute_flag(i) := 'N';
3740
3741 /* Added for bug 2668753 */
3742 /* For Information-- This piece of code added for bug 2668753 might result in performance issues */
3743
3744 l_project_currency_code := null;
3745 l_project_raw_cost := null;
3746 l_project_burdened_cost := null;
3747 l_proj_func_burdened_cost := null;
3748 l_exp_func_burdened_cost := null;
3749 /* Added for bug 2697945 */
3750
3751 l_bill_trans_raw_revenue := null;
3752 l_bill_trans_currency_code := null;
3753
3754 /* Added for bug 2820252 */
3755 l_bill_trans_adjusted_revenue := null;
3756
3757 /* IF condition added for Bug 2780325 */
3758
3759 IF (p_mcb_flag.exists(i))THEN
3760
3761 IF ( nvl(p_mcb_flag(i),'N') = 'Y' ) THEN
3762 BEGIN
3763 OPEN PROJ_VALUES(p_expenditure_item_id(i));
3764 -- IF(PROJ_VALUES%FOUND) THEN /* Commented for bug 2697945 */
3765
3766 FETCH proj_values into
3767 l_project_raw_cost,
3768 l_project_currency_code,
3769 l_project_burdened_cost,
3770 l_proj_func_burdened_cost,
3771 l_exp_func_burdened_cost,
3772 l_bill_trans_raw_revenue, --Added for bug 2697945
3773 l_bill_trans_currency_code,
3774 l_bill_trans_adjusted_revenue; --Added for bug 2820252
3775
3776 --END IF;
3777 /* Added for bug 2697945 */
3778
3779 IF(PROJ_VALUES%NOTFOUND) THEN
3780 IF g1_debug_mode = 'Y' THEN
3781 pa_debug.write_file('LOG','No Data Found for the Expenditure Item Id :'||p_expenditure_item_id(i));
3782 END IF;
3783
3784 if PROJ_VALUES%ISOPEN THEN
3785 CLOSE PROJ_VALUES;
3786 end if;
3787
3788 -- EXIT; /* Commented this line and added the following line for bug 2697945 */
3789 RAISE exp_not_found;
3790 END IF;
3791 /* End of Changes done for bug 2697945 */
3792
3793 CLOSE PROJ_VALUES;
3794
3795 EXCEPTION
3796 /* Added EXP_NOT_FOUND for bug 2697945 */
3797
3798 WHEN EXP_NOT_FOUND THEN
3799 if PROJ_VALUES%ISOPEN THEN
3800 CLOSE PROJ_VALUES;
3801 end if;
3802 RAISE;
3803 WHEN OTHERS THEN
3804 if PROJ_VALUES%ISOPEN THEN
3805 CLOSE PROJ_VALUES;
3806 end if;
3807 RAISE;
3808 END;
3809
3810 END IF;
3811
3812 /* End of Changes for bug 2668753 */
3813
3814 END IF; /* 2780325 */
3815 IF (p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL) THEN
3816 l_error_code := null; /** Fixed Bug: 1063455 **/
3817 l_burden_error_code := x_error_code; /* 2215942 */
3818
3819 pa_debug.G_Err_Stage :=
3820 'Processing EI: '||to_char(p_expenditure_item_id(i));
3821 IF g1_debug_mode = 'Y' THEN
3822 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
3823 END IF;
3824 IF p_cc_markup_base_code(i) = 'C' THEN
3825 -- Raw Cost
3826
3827 -- Check If Raw Cost amount is Null
3828 IF p_denom_raw_cost_amount(i) is not null THEN
3829 x_tp_base_amount(i) := p_denom_raw_cost_amount(i);
3830 x_tp_base_curr_code(i) := p_denom_currency_code(i);
3831 IF g1_debug_mode = 'Y' THEN
3832 pa_debug.write_file('LOG','Base is raw cost');
3833 END IF;
3834 ELSE
3835 l_error_code := 'PA_CC_TP_RAW_COST_NULL';
3836 END IF;
3837
3838 ELSIF p_cc_markup_base_code(i) = 'R' THEN
3839 -- Raw Revenue
3840 IF g1_debug_mode = 'Y' THEN
3841 pa_debug.write_file('LOG','Base is raw rev');
3842 END IF;
3843 /*The below block is added for bug 2863550 */
3844 IF(p_dist_rule.exists(i)) THEN
3845 IF (substr(p_dist_rule(i),1,4) = 'WORK') THEN
3846 l_dist_rule:=TRUE;
3847 ELSE
3848 l_dist_rule :=FALSE;
3849 END IF;
3850 ELSE
3851 l_dist_rule :=TRUE;
3852 END IF;
3853 -- Check If revenue amount is distributed
3854 IF p_revenue_distributed_flag(i) IN ('Y','P') AND l_dist_rule THEN /*l_dist_rule added for 2863550*/
3855 /*substr(p_dist_rule(i),1,4) = 'WORK' THEN*//*Added P for bug 2636678 and added p_dist_rule for bug 2663736*/
3856 -- Check If Revenue amount is Null
3857 IF p_raw_revenue_amount(i) IS NOT NULL THEN
3858 /* Commented the following two lines for bug 2697945 and added the next two lines as part of fix */
3859
3860 /* x_tp_base_amount(i) := p_raw_revenue_amount(i);
3861 x_tp_base_curr_code(i) := p_projfunc_currency_code(i); */
3862
3863 /* Changed the following two lines for bug 2696945 */
3864
3865 x_tp_base_amount(i) := nvl(l_bill_trans_adjusted_revenue,nvl(l_bill_trans_raw_revenue,p_raw_revenue_amount(i)));
3866 /* Changed the above line for bug 2820252 */
3867 x_tp_base_curr_code(i) := nvl(l_bill_trans_currency_code,p_projfunc_currency_code(i));
3868
3869 ELSE
3870 /* Code Added for Bug#2469987 -- Start */
3871 BEGIN
3872 SELECT 1 INTO l_check_line
3873 FROM DUAL
3874 WHERE EXISTS (
3875 SELECT 1
3876 FROM pa_cust_rev_dist_lines_all
3877 WHERE expenditure_item_id = p_expenditure_item_id(i)
3878 UNION ALL
3879 SELECT 1
3880 FROM pa_cc_dist_lines_all
3881 WHERE expenditure_item_id = p_expenditure_item_id(i));
3882
3883 x_tp_base_amount(i) := 0;
3884 x_tp_base_curr_code(i) := p_projfunc_currency_code(i);
3885
3886 EXCEPTION
3887 WHEN NO_DATA_FOUND THEN
3888 x_tp_base_amount(i) := 0;
3889 x_tp_base_curr_code(i) := p_projfunc_currency_code(i);
3890 WHEN OTHERS THEN
3891 l_error_code := 'PA_CC_TP_REV_AMT_NULL';
3892 END;
3893 END IF; /* Revenue amount is not null */
3894 /* Code Added for Bug#2469987 -- End */
3895 ELSE
3896 IF g1_debug_mode = 'Y' THEN
3897 pa_debug.write_file('LOG',
3898 'Rev flag is '||p_revenue_distributed_flag(i));
3899 END IF;
3900 /* Added for bill rate disount and transfer price revenue*/
3901 IF (p_sys_linkage_function(i) in ('ST','OT')) then
3902 IF g1_debug_mode = 'Y' THEN
3903 pa_debug.write_file('LOG','within Assignment_Rev_Amt');
3904 END IF;
3905 pa_revenue.Assignment_Rev_Amt(
3906 p_project_id => P_project_id(i)
3907 ,p_task_id => P_task_id(i)
3908 ,p_item_date => P_expenditure_item_date(i)
3909 ,p_item_id => p_assignment_id(i)
3910 ,p_bill_rate_multiplier => p_bill_rate_multiplier(i)
3911 ,p_quantity => p_quantity(i)
3912 ,p_person_id => p_person_id(i)
3913 ,p_raw_cost => p_raw_cost(i)
3914 /* bug#3221791 added to_number */
3915 ,p_labor_schdl_discnt => to_number(p_labor_schdl_discnt(i))
3916 ,p_labor_bill_rate_org_id => NULL
3917 ,p_labor_std_bill_rate_schdl => NULL
3918 ,p_labor_schdl_fixed_date => p_labor_schdl_fixed_date(i)
3919 ,p_bill_job_grp_id => p_bill_job_grp_id(i)
3920 ,p_labor_sch_type => p_labor_sch_type(i)
3921 ,p_project_org_id => p_project_org_id(i)
3922 ,p_project_type => p_project_type(i)
3923 ,p_expenditure_type => p_expenditure_type(i)
3924 ,p_exp_func_curr_code => p_exp_func_curr_code(i)
3925 ,p_incurred_by_organz_id => p_incurred_by_organz_id(i)
3926 ,p_raw_cost_rate => p_raw_cost_rate(i)
3927 ,p_override_to_organz_id => p_override_to_organz_id(i)
3928 ,p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id(i)
3929 ,p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id(i)
3930 ,p_resource_job_id => NULL
3931 ,p_exp_raw_cost => p_exp_raw_cost(i)
3932 ,p_expenditure_org_id => p_expnd_organization_id(i)
3933 ,p_projfunc_currency_code => p_projfunc_currency_code(i)
3934 ,p_assignment_precedes_task => p_assignment_precedes_task(i)
3935 ,p_sys_linkage_function => p_sys_linkage_function(i)
3936 ,x_bill_rate => l_bill_rate
3937 ,x_raw_revenue => x_tp_base_amount(i)
3938 ,x_txn_currency_code => x_tp_base_Curr_code(i)
3939 ,x_rev_currency_code => l_rev_currency_code
3940 ,x_markup_percentage => l_markup_percentage
3941 ,x_return_status => l_return_status
3942 ,x_msg_count => l_msg_count
3943 ,x_msg_data => l_msg_data
3944 ,p_mcb_flag => p_mcb_flag(i)
3945 ,p_denom_raw_cost => p_denom_raw_cost_amount(i)
3946 ,p_denom_curr_code => p_denom_currency_code(i)
3947 ,p_called_process => 'PA'
3948 /* Added for bug 2668753 */
3949 ,p_project_raw_cost => l_project_raw_cost
3950 ,p_project_currency_code => l_project_currency_code
3951 ,x_adjusted_bill_rate => l_adjusted_bill_rate);--4038485
3952 IF g1_debug_mode = 'Y' THEN
3953 pa_debug.write_file('LOG','completed Assignment_Rev_Amt');
3954 END IF;
3955
3956 ELSE
3957 IF g1_debug_mode = 'Y' THEN
3958 pa_debug.write_file('LOG','Non Labor Revenue Amount');
3959 END IF;
3960 l_exp_func_curr_code := p_exp_func_curr_code(i);
3961 select decode(p_sys_linkage_function(i),'BTC',6,2)
3962 into l_sl_function
3963 from dual;
3964
3965 IF p_raw_cost_rate(i) IS NULL THEN
3966 SELECT DECODE(nvl(COST_RATE_FLAG,'N'),'N',1,NULL)
3967 INTO l_raw_cost_rate
3968 FROM PA_EXPENDITURE_TYPES
3969 where EXPENDITURE_TYPE = p_expenditure_type(i);
3970 ELSE
3971 l_raw_cost_rate := p_raw_cost_rate(i);
3972 END IF;
3973 pa_revenue.Non_Labor_Rev_amount(
3974 p_project_id => p_project_id(i),
3975 p_task_id => p_task_id(i),
3976 p_bill_rate_multiplier => p_bill_rate_multiplier(i),
3977 p_quantity => p_quantity(i),
3978 p_raw_cost => p_raw_cost(i),
3979 p_burden_cost => p_burden_cost(i),
3980 p_denom_raw_cost => p_denom_raw_cost_amount(i),
3981 p_denom_burdened_cost => p_denom_burdened_cost_amount(i),
3982 p_expenditure_item_date => p_expenditure_item_date(i),
3983 p_task_bill_rate_org_id => p_task_nl_bill_rate_org_id(i),
3984 p_project_bill_rate_org_id => p_proj_nl_bill_rate_org_id(i),
3985 p_task_std_bill_rate_sch => p_task_nl_std_bill_rate_sch(i),
3986 p_project_std_bill_rate_sch => p_proj_nl_std_bill_rate_sch(i),
3987 p_project_org_id => p_project_org_id(i),
3988 p_sl_function => l_sl_function,
3989 p_denom_currency_code => p_denom_currency_code(i),
3990 p_proj_func_currency => p_projfunc_currency_code(i),
3991 p_expenditure_type => p_expenditure_type(i),
3992 p_non_labor_resource => p_non_labor_resource(i),
3993 p_task_sch_date => p_nl_task_sch_date(i),
3994 p_project_sch_date => p_nl_proj_sch_date(i),
3995 p_project_sch_discount => p_nl_proj_sch_discount(i),
3996 p_task_sch_discount => p_nl_task_sch_discount(i),
3997 p_mcb_flag => p_mcb_flag(i),
3998 p_non_labor_sch_type => p_nl_sch_type(i),
3999 p_project_type => p_project_type(i),
4000 p_exp_raw_cost => p_exp_raw_cost(i),
4001 p_raw_cost_rate => l_raw_cost_rate,
4002 p_incurred_by_organz_id => p_incurred_by_organz_id(i),
4003 p_override_to_organz_id => p_override_to_organz_id(i),
4004 px_exp_func_curr_code => l_exp_func_curr_code,
4005 x_raw_revenue => x_tp_base_amount(i),
4006 x_rev_curr_code => x_tp_base_Curr_code(i),
4007 x_return_status => l_return_status,
4008 x_msg_count => l_msg_count,
4009 x_msg_data => l_msg_data,
4010 /* Added for bug 2668753 */
4011 p_project_raw_cost => l_project_raw_cost,
4012 p_project_currency_code => l_project_currency_code,
4013 p_project_burdened_cost => l_project_burdened_cost,
4014 p_proj_func_burdened_cost => l_proj_func_burdened_cost,
4015 p_exp_func_burdened_cost => l_exp_func_burdened_cost,
4016 p_task_nl_std_bill_rate_sch_id => p_task_nl_std_bill_rate_sch_id(i),
4017 p_proj_nl_std_bill_rate_sch_id => p_proj_nl_std_bill_rate_sch_id(i),
4018 x_bill_rate => l_nl_bill_rate,
4019 x_markup_percentage => l_nl_markup_percentage,
4020 x_adjusted_bill_rate => l_nl_adjusted_bill_rate,--4038485
4021 p_uom_flag => p_uom_flag(i));
4022
4023 IF g1_debug_mode = 'Y' THEN
4024 pa_debug.write_file('LOG','Completed Non Labor Revenue Amount');
4025 END IF;
4026 END IF;
4027 IF g1_debug_mode = 'Y' THEN
4028 pa_debug.write_file('LOG','x_base revenue amount' || x_tp_base_amount(i) || 'code : ' ||x_tp_base_curr_code(i));
4029 END IF;
4030 IF l_msg_data is NULL then
4031 IF x_tp_base_amount(i) is NULL THEN
4032 l_error_code := 'PA_CC_TP_REV_AMT_NULL';
4033 END IF;
4034 ELSE
4035 l_error_code := l_msg_data;
4036 END IF;
4037 /* Added for bill rate disount and transfer price revenue*/
4038 END IF;
4039 ELSIF p_cc_markup_base_code(i) = 'B' THEN
4040 -- Burdened Cost
4041 l_denom_burdened_cost_amount := p_denom_burdened_cost_amount(i);
4042 IF (l_denom_burdened_cost_amount IS NULL) THEN
4043 -- Check if Burden cost is null
4044 l_error_code := 'PA_CC_TP_BURDN_COST_NULL';
4045 ELSE
4046 -- Check if burden cost needs to be recalculated
4047 IF (p_denom_burdened_cost_amount(i) = p_denom_raw_cost_amount(i))
4048 THEN
4049
4050 Get_Burdening_Details(p_project_id (i),
4051 l_burdening_allowed,
4052 l_burden_amt_display_method
4053 );
4054
4055 IF g1_debug_mode = 'Y' THEN
4056 pa_debug.write_file('LOG','Project_id is: '
4057 ||to_char(p_project_id(i)));
4058
4059 pa_debug.write_file('LOG','Burdening flag is : '
4060 ||l_burdening_allowed);
4061
4062 pa_debug.write_file('LOG','burden amount allowed flag is: '
4063 ||l_burden_amt_display_method);
4064 END IF;
4065
4066 -- Check if project allows burdening
4067 IF (l_burdening_allowed = 'Y' ) THEN
4068 -- Check if burden amount is displayed on separate transaction
4069
4070 IF l_burden_amt_display_method = 'D' THEN
4071 -- calculate the correct burden_amount
4072
4073 l_fixed_date := p_fixed_date(i);
4074
4075 IF p_fixed_date(i) is NULL THEN
4076 l_fixed_date := p_expenditure_item_date(i);
4077 END IF;
4078 /**
4079
4080 PA_COST_PLUS.view_indirect_cost (
4081 transaction_id => p_expenditure_item_id(i),
4082 transaction_type => 'PA',
4083 task_id => p_task_id(i),
4084 effective_date => l_fixed_date,
4085 expenditure_type => p_expenditure_type(i),
4086 organization_id => p_expnd_organization_id(i),
4087 schedule_type => 'C',
4088 direct_cost => p_denom_raw_cost_amount(i),
4089 indirect_cost => l_denom_burdened_cost_amount,
4090 status => l_status_code,
4091 stage => l_stage
4092 );
4093 **/
4094 -- Get the multiplier
4095 /* Added the declare sction for the bug#2215942 */
4096
4097 DECLARE
4098 l_burden_sch_rev_id Number;
4099 l_Stage Number;
4100 l_Status Number;
4101 l_burden_calc_amount_l number;
4102 l_tp_ind_compiled_set_id_l Number;
4103 t_rate_sch_rev_id number; /* bug#3117191 */
4104 t_sch_fixed_date date; /* bug#3117191 */
4105
4106
4107 BEGIN
4108 /*Bug 1729820 */
4109 /* commented for the bug#2215942, starts here */
4110 /*
4111 SELECT cost_ind_compiled_set_id
4112 INTO l_compiled_set_id
4113 FROM pa_expenditure_items_all
4114 WHERE expenditure_item_id = p_expenditure_item_id(i);
4115 */
4116 /* commented for the bug#2215942, ends here */
4117 /* Changes ends for bug 1729820 */
4118 /* commented for the bug#2215942, starts here */
4119 /*
4120
4121 l_compiled_multiplier :=
4122 pa_cost_plus.Get_Mltplr_For_Compiled_Set
4123 (l_compiled_set_id);
4124 l_denom_burdened_cost_amount :=
4125 p_denom_raw_cost_amount(i)* (1+l_compiled_multiplier);
4126 */
4127 /* commented for the bug#2215942, ends here */
4128
4129 /* Code added for the bug 2215942, starts here */
4130
4131 x_burden_rate_compute_flag(i) := 'Y';
4132
4133 /* added for bug#3117191 */
4134 PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
4135 'ACTUAL',
4136 p_expenditure_item_id(i), -- Transaction Item Id
4137 'PA', -- Transaction Type
4138 p_task_id(i), -- Task Id
4139 'C', -- Schedule Type
4140 p_expenditure_item_date(i), -- EI Date
4141 t_sch_fixed_date, -- Sch_fixed_date (Out)
4142 t_rate_sch_rev_id, -- Rate_sch_rev_id (Out)
4143 l_status); -- Status (Out)
4144
4145 /* Begin bug 5169080 */
4146 if (nvl(l_status , 0 ) <> 0) THEN
4147 l_error_code := 'PA_CC_TP_ERROR_BURDEN_CALC';
4148 end if;
4149 /* End bug 5169080 */
4150
4151
4152 IF (t_rate_sch_rev_id IS NOT NULL) THEN
4153 l_burden_sch_rev_id := t_rate_sch_rev_id;
4154 PA_COST_PLUS.Get_Burden_Amount1(
4155 p_expenditure_type(i),
4156 p_expnd_organization_id(i),
4157 p_denom_raw_cost_amount(i),
4158 l_burden_calc_amount_l,
4159 l_burden_sch_rev_id,
4160 l_tp_ind_compiled_set_id_l,
4161 l_status,
4162 l_stage
4163 );
4164 /* end for bug#3117191 */
4165 ELSE /* bug#3117191 */
4166
4167 /* get the task level burden schedule id by considering the task level overrides */
4168 select NVL(OVR_COST_IND_RATE_SCH_ID, COST_IND_RATE_SCH_ID)
4169 into l_rate_schedule_id(i)
4170 from pa_tasks
4171 where task_id in
4172 ( select task_id
4173 from pa_expenditure_items_all
4174 where expenditure_item_id = p_expenditure_item_id(i)
4175 );
4176 /* Get the burden amount from the call to the procedure PA_COST_PLUS.Get_Burden_Amount,
4177 which gets the revision for the given burden schedule, then burden structure,
4178 then cost base from the burden structure corresponding to the expenditure type,
4179 then sum of the compiled multipliers */
4180
4181 PA_COST_PLUS.Get_Burden_Amount(
4182 l_rate_schedule_id(i),
4183 p_expenditure_item_date(i),
4184 p_expenditure_type(i),
4185 p_expnd_organization_id(i),
4186 p_denom_raw_cost_amount(i),
4187 l_burden_calc_amount_l,
4188 l_burden_sch_rev_id,
4189 l_tp_ind_compiled_set_id_l,
4190 l_status,
4191 l_stage
4192 );
4193 END IF; /* bug#3117191 */
4194
4195 l_burden_calc_amount(i) := l_burden_calc_amount_l;
4196 l_tp_ind_compiled_set_id(i) := l_tp_ind_compiled_set_id_l;
4197
4198
4199 l_denom_burdened_cost_amount :=
4200 p_denom_raw_cost_amount(i)+l_burden_calc_amount(i);
4201
4202 /* Code added for the bug 2215942, ends here */
4203
4204
4205 EXCEPTION
4206 when no_data_found then
4207
4208 l_error_code := 'PA_CC_TP_ERROR_BURDEN_CALC';
4209 END;
4210
4211
4212 END IF;/** burden_amt_display_method = 'D' **/
4213 END IF; /** l_burdening_allowed **/
4214
4215 END IF;/** p_denom_burdened_cost_amount=p_denom_raw_cost_amount **/
4216 END IF; /** Burden Cost is null **/
4217
4218 IF (l_error_code is null) THEN
4219
4220 x_tp_base_amount(i) := l_denom_burdened_cost_amount;
4221 x_tp_base_curr_code(i) := p_denom_currency_code(i);
4222
4223 END IF;
4224 ELSE
4225 l_error_code := 'PA_CC_TP_INVALID_BASE_CODE';
4226
4227 END IF; /** p_cc_markup_base_code **/
4228
4229 IF (l_error_code IS NULL) THEN
4230 IF g1_debug_mode = 'Y' THEN
4231 pa_debug.write_file('LOG','Base Amount is: '
4232 ||to_char(x_tp_base_amount(i)));
4233 pa_debug.write_file('LOG','Base currency is: '
4234 ||x_tp_base_curr_code(i));
4235 END IF;
4236 -- No error encountered yet
4237 IF p_calc_method_code(i) = 'A' THEN
4238 -- Use Basis
4239 G_Basis_Exists := TRUE;
4240 x_basis_compute_flag(i) := 'Y';
4241 ELSIF p_calc_method_code(i) = 'R' THEN
4242 -- Use Bill Rate Schedule
4243 G_Bill_Rate_Exists := TRUE;
4244 x_bill_rate_compute_flag(i) := 'Y';
4245 ELSIF p_calc_method_code(i) = 'B' THEN
4246 -- Use Burden schedule
4247 G_Burden_Rate_Exists := TRUE;
4248 x_burden_rate_compute_flag(i) := 'Y';
4249 END IF;
4250 ELSE
4251 x_error_code(i) := l_error_code;
4252
4253 END IF;
4254
4255
4256
4257 END IF;/** p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL **/
4258
4259 END LOOP;
4260
4261 pa_debug.Reset_err_stack;
4262 pa_debug.G_Err_Stage := 'Exitting Set_Base_Amount_And_Flag';
4263 IF g1_debug_mode = 'Y' THEN
4264 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4265 END IF;
4266
4267 EXCEPTION
4268
4269 WHEN OTHERS THEN
4270 raise;
4271
4272 END Set_Base_Amount_And_Flag;
4273 --------------------------------------------------------------------------------
4274 Procedure Get_Burdening_Details(p_project_id IN NUMBER,
4275 x_burdening_allowed OUT NOCOPY VARCHAR2, /*File.sql.39*/
4276 x_burden_amt_display_method OUT NOCOPY VARCHAR2 /*File.sql.39*/
4277 )
4278 IS
4279 /* Bug 1729820 _ Changed to pa_projects_all and pa_project_types_all */
4280
4281 Cursor c_burdening_details
4282 IS
4283 select type.burden_cost_flag,type.burden_amt_display_method
4284 from pa_projects_all proj ,pa_project_types_all type
4285 where proj.project_id = p_project_id
4286 and proj.project_type = type.project_type
4287 and proj.org_id = type.org_id; /** Added this condition while making changes for Org Forecasting **/
4288 BEGIN
4289 pa_debug.Set_err_stack ('Get_Burdening_Details');
4290 pa_debug.G_Err_Stage := 'Starting Get_Burdening_Details';
4291 IF g1_debug_mode = 'Y' THEN
4292 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4293 END IF;
4294
4295 open c_burdening_details;
4296 fetch c_burdening_details
4297 into x_burdening_allowed,x_burden_amt_display_method;
4298 close c_burdening_details;
4299
4300 pa_debug.Reset_err_stack;
4301 pa_debug.G_Err_Stage := 'Exitting Get_Burdening_Details';
4302 IF g1_debug_mode = 'Y' THEN
4303 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4304 END IF;
4305
4306 EXCEPTION
4307
4308 WHEN OTHERS THEN
4309 raise;
4310
4311 END Get_Burdening_Details;
4312 --------------------------------------------------------------------------------
4313
4314 PROCEDURE Determine_Transfer_Price
4315 (
4316 p_expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4317 p_expnd_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4318 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
4319 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4320 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4321 p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp,
4322 p_task_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4323 p_tp_base_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4324 p_tp_base_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4325 p_tp_schedule_line_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
4326 p_tp_rule_percentage IN PA_PLSQL_DATATYPES.NumTabTyp,
4327 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4328 p_quantity IN PA_PLSQL_DATATYPES.NumTabTyp,
4329 p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4330 p_job_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4331 p_rate_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4332 p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp,
4333 p_basis_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4334 p_bill_rate_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4335 p_burden_rate_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4336 x_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4337 x_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4338 x_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4339 x_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4340 x_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4341 x_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4342 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4343 )
4344 IS
4345
4346 l_basis_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4347 l_basis_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4348 l_basis_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4349 l_bill_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4350 l_bill_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4351 l_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
4352 l_tp_bill_markup_percentage PA_PLSQL_DATATYPES.NumTabTyp;
4353 l_bill_error_stage VARCHAR2(80);
4354 l_bill_reject_cnt Number;
4355 l_bill_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4356 l_burden_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4357 l_burden_calc_curr_code PA_PLSQL_DATATYPES.Char15TabTyp;
4358 l_burden_calc_amount PA_PLSQL_DATATYPES.NumTabTyp;
4359 l_exp_uom PA_PLSQL_DATATYPES.Char30TabTyp;
4360 l_bill_rate_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
4361 l_burden_status NUMBER;
4362 l_burden_stage NUMBER;
4363 l_temp_transfer_price NUMBER;
4364
4365
4366 BEGIN
4367 pa_debug.Set_err_stack ('Determine_Transfer_Price');
4368 pa_debug.G_Err_Stage := 'Starting Determine_Transfer_Price';
4369 IF g1_debug_mode = 'Y' THEN
4370 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4371 END IF;
4372 l_bill_rate_compute_flag := p_bill_rate_compute_flag;
4373
4374 l_basis_error_code := x_error_code;
4375 l_burden_error_code := x_error_code;
4376 l_bill_error_code := x_error_code;
4377
4378 IF G_Basis_Exists THEN
4379 IF g1_debug_mode = 'Y' THEN
4380 pa_debug.write_file('LOG','Using Basis');
4381 END IF;
4382 Get_Basis_Amount(
4383 p_tp_base_curr_code => p_tp_base_curr_code,
4384 p_tp_base_amount => p_tp_base_amount,
4385 p_compute_flag => p_basis_compute_flag,
4386 p_array_size => G_array_size,
4387 x_denom_tp_curr_code => l_basis_calc_curr_code,
4388 x_amount => l_basis_calc_amount,
4389 x_error_code => l_basis_error_code
4390 );
4391 END IF;
4392
4393 IF G_Bill_Rate_Exists THEN
4394 -- Call Bill rate API
4395 IF g1_debug_mode = 'Y' THEN
4396 pa_debug.write_file('LOG','Using Bill Rate');
4397 END IF;
4398
4399 pa_bill_schedule.get_computed_bill_rate(
4400 p_array_size => G_Array_Size,
4401 p_bill_rate_sch_id => p_rate_schedule_id,
4402 p_expenditure_item_id => p_expenditure_item_id,
4403 p_exp_sys_linkage => p_system_linkage_function,
4404 p_expenditure_type => p_expenditure_type,
4405 p_expenditure_item_date => p_expenditure_item_date,
4406 p_fixed_date => p_fixed_date,
4407 p_quantity => p_quantity,
4408 p_incurred_by_person_id => p_incurred_by_person_id,
4409 p_non_labor_resource => p_non_labor_resource,
4410 p_base_curr => p_tp_base_curr_code,
4411 p_base_amt => p_tp_base_amount,
4412 p_exp_uom =>l_exp_uom ,
4413 p_compute_flag => l_bill_rate_compute_flag,
4414 x_error_code => l_bill_error_code,
4415 x_reject_cnt => l_bill_reject_cnt,
4416 x_computed_rate => l_tp_bill_rate,
4417 x_computed_markup => l_tp_bill_markup_percentage,
4418 x_computed_currency => l_bill_calc_curr_code,
4419 x_computed_amount => l_bill_calc_amount,
4420 x_tp_job_id => x_tp_job_id,
4421 x_error_stage => l_bill_error_stage
4422 );
4423 END IF;
4424
4425
4426 IF G_Burden_Rate_Exists THEN
4427 -- Call Burden rate API
4428 IF g1_debug_mode = 'Y' THEN
4429 pa_debug.write_file('LOG','Using Burden schedule');
4430 END IF;
4431 get_burden_amount(
4432 p_array_size => G_array_size,
4433 p_burden_schedule_id => p_rate_schedule_id,
4434 p_expenditure_item_date => p_expenditure_item_date,
4435 p_fixed_date => p_fixed_date,
4436 p_expenditure_type => p_expenditure_type,
4437 p_organization_id => p_expnd_organization_id,
4438 p_raw_amount_curr_code => p_tp_base_curr_code,
4439 p_raw_amount => p_tp_base_amount ,
4440 p_compute_flag => p_burden_rate_compute_flag,
4441 x_computed_currency => l_burden_calc_curr_code,
4442 x_burden_amount => l_burden_calc_amount,
4443 x_compiled_set_id => x_tp_ind_compiled_set_id,
4444 x_error_code => l_burden_error_code
4445 );
4446 END IF;
4447
4448 -- Now set the out parameters transfer price ,denom transfer price currency
4449 -- code and error code.
4450 For i in 1 .. G_Array_Size
4451 Loop
4452 IF (p_compute_flag(i) = 'Y' and x_error_code(i) is null) THEN
4453
4454 IF (p_basis_compute_flag(i) = 'Y') THEN
4455
4456 IF ((l_basis_error_code.exists(i)
4457 AND l_basis_error_code(i) IS NOT NULL)) THEN
4458 x_error_code(i) := l_basis_error_code(i);
4459 ELSE
4460 x_denom_tp_currency_code(i) := l_basis_calc_curr_code(i);
4461 l_temp_transfer_price := l_basis_calc_amount(i)*
4462 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4463 * (NVL(p_tp_rule_percentage(i),100)/100);
4464 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4465 (l_temp_transfer_price,
4466 x_denom_tp_currency_code(i));
4467 END IF; /** Checking error code **/
4468
4469 ELSIF (p_bill_rate_compute_flag(i) = 'Y') THEN
4470
4471 IF (l_bill_error_code.exists(i)
4472 AND l_bill_error_code(i) IS NOT NULL) THEN
4473 x_error_code(i) := l_bill_error_code(i);
4474 ELSE
4475 x_tp_bill_rate(i) := l_tp_bill_rate(i);
4476 x_tp_bill_markup_percentage(i) := l_tp_bill_markup_percentage(i);
4477 x_denom_tp_currency_code(i) := l_bill_calc_curr_code(i);
4478 l_temp_transfer_price := l_bill_calc_amount(i)*
4479 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4480 * (NVL(p_tp_rule_percentage(i),100)/100);
4481 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4482 (l_temp_transfer_price,
4483 x_denom_tp_currency_code(i));
4484 END IF;
4485
4486 ELSIF (p_burden_rate_compute_flag(i) = 'Y') THEN
4487
4488 IF (l_burden_error_code.exists(i)
4489 AND l_burden_error_code(i) IS NOT NULL ) THEN
4490 x_error_code(i) := l_burden_error_code(i);
4491 ELSE
4492
4493 x_denom_tp_currency_code(i) := l_burden_calc_curr_code(i);
4494 l_temp_transfer_price := l_burden_calc_amount(i)*
4495 (NVL(p_tp_schedule_line_percentage(i),100)/100)
4496 * (NVL(p_tp_rule_percentage(i),100)/100);
4497 x_denom_transfer_price(i) := pa_currency.round_trans_currency_amt
4498 (l_temp_transfer_price,
4499 x_denom_tp_currency_code(i));
4500 END IF;
4501 END IF;
4502
4503 END IF; /** p_compute_flag(i) = 'Y' and x_error_code(i) is null **/
4504
4505 END Loop;
4506
4507 pa_debug.Reset_err_stack;
4508 pa_debug.G_Err_Stage := 'Exitting Determine_transfer_price';
4509 IF g1_debug_mode = 'Y' THEN
4510 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4511 END IF;
4512 EXCEPTION
4513
4514 WHEN OTHERS THEN
4515 raise;
4516 END Determine_transfer_Price;
4517 --------------------------------------------------------------------------------
4518 PROCEDURE Get_Basis_Amount(
4519 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4520 p_tp_base_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4521 p_tp_base_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4522 p_array_size IN Number,
4523 x_denom_tp_curr_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4524 x_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4525 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4526 )
4527 IS
4528 BEGIN
4529 pa_debug.Set_err_stack ('Get_Basis_Amount');
4530 pa_debug.G_Err_Stage := 'Starting Get_Basis_Amount';
4531 IF g1_debug_mode = 'Y' THEN
4532 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4533 END IF;
4534 For i in 1 .. p_Array_Size
4535 Loop
4536 IF (p_compute_flag(i) = 'Y' AND x_error_code(i) IS NULL) THEN
4537 pa_debug.G_Err_Stage := 'Processing Get_Basis_Amount';
4538 IF g1_debug_mode = 'Y' THEN
4539 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4540 END IF;
4541 IF (p_tp_base_curr_code(i) IS NOT NULL
4542 and p_tp_base_amount(i) IS NOT NULL) THEN
4543 pa_debug.G_Err_Stage:='Setting currency,amount in Get_Basis_Amount';
4544 IF g1_debug_mode = 'Y' THEN
4545 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4546 END IF;
4547 x_denom_tp_curr_code(i) := p_tp_base_curr_code(i);
4548 x_amount(i) := p_tp_base_amount(i);
4549 ELSE
4550 x_error_code(i) := 'PA_CC_TP_BASE_CURR_AMT_NULL';
4551 END IF;
4552 END IF;
4553 End Loop;
4554
4555 pa_debug.Reset_err_stack;
4556 pa_debug.G_Err_Stage := 'Exitting Get_Basis_Amount';
4557 IF g1_debug_mode = 'Y' THEN
4558 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4559 END IF;
4560
4561 EXCEPTION
4562
4563 WHEN OTHERS THEN
4564 raise;
4565 END Get_Basis_Amount;
4566 -------------------------------------------------------------------------------
4567 PROCEDURE Get_Burden_Amount(
4568 p_array_size IN Number,
4569 p_burden_schedule_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4570 p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4571 p_fixed_date IN PA_PLSQL_DATATYPES.DateTabTyp,
4572 p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
4573 p_organization_id IN PA_PLSQL_DATATYPES.IdTabTyp,
4574 p_raw_amount_curr_code IN PA_PLSQL_DATATYPES.Char15TabTyp,
4575 p_raw_amount IN PA_PLSQL_DATATYPES.NumTabTyp,
4576 p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
4577 x_computed_currency OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp,
4578 x_burden_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
4579 x_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4580 x_error_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
4581 )
4582 IS
4583
4584 l_status NUMBER;
4585 l_stage NUMBER;
4586 l_burden_sch_rev_id Number;
4587 l_burden_amount Number;
4588 l_effective_date Date;
4589
4590 unexpected_result exception;
4591
4592 BEGIN
4593
4594 pa_debug.Set_err_stack ('Get_Burden_Amount');
4595 pa_debug.G_Err_Stage := 'Starting Get_Burden_Amount';
4596 IF g1_debug_mode = 'Y' THEN
4597 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4598 END IF;
4599
4600 For i in 1 .. p_array_size
4601 LOOP
4602
4603 IF (p_compute_flag(i) = 'Y' and x_error_code(i) IS NULL) THEN
4604
4605 IF p_fixed_date(i) is null THEN
4606 l_effective_date := p_expenditure_item_date(i);
4607 else
4608 l_effective_date := p_fixed_date(i);
4609 END IF;
4610
4611 IF g1_debug_mode = 'Y' THEN
4612 pa_debug.write_file('LOG','Burden Schedule ID: '
4613 ||to_char(p_burden_schedule_id(i)));
4614 pa_debug.write_file('LOG','Effective Date: '
4615 ||to_char(l_effective_date));
4616 pa_debug.write_file('LOG','Expenditure Type: '
4617 ||p_expenditure_type(i));
4618 pa_debug.write_file('LOG','Expenditure Organization ID: '
4619 ||to_char(p_organization_id(i)));
4620 pa_debug.write_file('LOG','Raw Amount IS : '
4621 ||to_char(p_raw_amount(i)));
4622 END IF;
4623
4624 PA_COST_PLUS.Get_Burden_Amount(
4625 p_burden_schedule_id(i),
4626 l_effective_date,
4627 p_expenditure_type(i),
4628 p_organization_id(i),
4629 p_raw_amount(i),
4630 l_burden_amount,
4631 l_burden_sch_rev_id,
4632 x_compiled_set_id(i),
4633 l_status,
4634 l_stage
4635 );
4636
4637
4638 IF l_status = 0 THEN
4639 x_computed_currency(i) := p_raw_amount_curr_code(i);
4640 x_burden_amount(i) := l_burden_amount;
4641 IF g1_debug_mode = 'Y' THEN
4642 pa_debug.write_file('LOG','Burden Amount IS : '
4643 ||to_char(l_burden_amount));
4644 pa_debug.write_file('LOG','Burden Schedule Revision ID : '
4645 ||to_char(l_burden_sch_rev_id));
4646 pa_debug.write_file('LOG','Compilede Set ID : '
4647 ||to_char(x_compiled_set_id(i)));
4648 END IF;
4649 ELSIF l_status < 0 THEN
4650 pa_debug.G_Err_Stage := 'Error in PA_COST_PLUS.Get_Burden_Amount';
4651 -- unhandled exception
4652 raise unexpected_result;
4653 ELSIF l_status > 0 THEN
4654 x_error_code(i) := 'PA_CC_TP_ERROR_BURDEN_RATE';
4655 END IF;
4656
4657 END IF; /** (p_compute_flag = 'Y' and x_error_code IS NULL) **/
4658
4659 End Loop;
4660
4661 pa_debug.Reset_err_stack;
4662 pa_debug.G_Err_Stage := 'Exitting Get_Burden_Amount';
4663 IF g1_debug_mode = 'Y' THEN
4664 pa_debug.write_file('LOG',pa_debug.G_Err_Stage);
4665 END IF;
4666
4667 EXCEPTION
4668
4669 WHEN unexpected_result THEN
4670 raise;
4671
4672 WHEN OTHERS THEN
4673 raise;
4674 END Get_Burden_Amount;
4675 --------------------------------------------------------------------------------
4676
4677
4678 /* Bug 3051110-Added procedure Get_Initial_Transfer_Price for TP Enhancement. */
4679
4680 PROCEDURE Get_Initial_Transfer_Price
4681 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
4682 ,p_start_date IN pa_project_assignments.start_date%TYPE
4683 ,p_debug_mode IN VARCHAR2 DEFAULT 'N'
4684 ,x_transfer_price_rate OUT NOCOPY pa_project_assignments.transfer_price_rate%TYPE /*file.sql.39*/
4685 ,x_transfer_pr_rate_curr OUT NOCOPY pa_project_assignments.transfer_pr_rate_curr%TYPE /*file.sql.39*/
4686 ,x_return_status OUT NOCOPY VARCHAR2 /*file.sql.39*/
4687 ,x_msg_data OUT NOCOPY VARCHAR2 /*file.sql.39*/
4688 ,x_msg_count OUT NOCOPY Number /*file.sql.39*/
4689 )
4690 IS
4691
4692 CURSOR Cur_Forecast_Items(c_assignment_id pa_project_assignments.assignment_id%TYPE,
4693 c_start_date pa_project_assignments.start_date%TYPE) IS SELECT
4694 FI.forecast_item_id,
4695 FI.forecast_item_type,
4696 FI.EXPENDITURE_ORG_ID,
4697 FI.EXPENDITURE_ORGANIZATION_ID,
4698 FI.PROJECT_ORG_ID,
4699 FI.PROJECT_ORGANIZATION_ID,
4700 FI.PROJECT_ID,
4701 FI.PROJECT_TYPE_CLASS,
4702 FI.PERSON_ID,
4703 FI.RESOURCE_ID,
4704 FI.ASSIGNMENT_ID,
4705 FI.ITEM_DATE,
4706 FI.ITEM_UOM,
4707 FI.PVDR_PA_PERIOD_NAME,
4708 FI.RCVR_PA_PERIOD_NAME,
4709 FI.EXPENDITURE_TYPE,
4710 FI.EXPENDITURE_TYPE_CLASS,
4711 FI.Tp_Amount_Type,
4712 FI.Delete_Flag
4713 FROM
4714 Pa_Forecast_Items FI
4715 WHERE FI.Assignment_id = c_assignment_id
4716 AND FI.Error_Flag = 'N'
4717 AND FI.Delete_Flag = 'N'
4718 AND FI.Item_Date = c_start_date;
4719
4720 Cursor FI_Attributes(C_PROJECT_ORG_ID pa_forecasting_options.ORG_ID%TYPE,
4721 C_EXPENDITURE_TYPE pa_expenditure_types.expenditure_type%TYPE,
4722 C_PVDR_PA_PERIOD_NAME Pa_periods_all.PERIOD_NAME%TYPE,
4723 C_EXPENDITURE_ORG_ID pa_forecasting_options.ORG_ID%TYPE)
4724 IS
4725 SELECT
4726 FCST.JOB_COST_RATE_SCHEDULE_ID,
4727 EXP.Expenditure_CATEGORY,
4728 PERIODS.End_Date
4729 FROM
4730 Pa_periods_all PERIODS,
4731 Pa_forecasting_options_all Fcst,
4732 Pa_expenditure_types Exp
4733 WHERE
4734 Exp.Expenditure_type = C_EXPENDITURE_TYPE
4735 AND PERIODS.PERIOD_NAME = C_PVDR_PA_PERIOD_NAME
4736 AND PERIODS.ORG_ID = C_EXPENDITURE_ORG_ID
4737 AND FCST.ORG_ID = C_PROJECT_ORG_ID;
4738
4739 Cursor Proj_Details(c_project_id pa_projects_all.project_id%type) IS
4740 SELECT Project_Type,
4741 DISTRIBUTION_RULE,
4742 BILL_JOB_GROUP_ID,
4743 COST_JOB_GROUP_ID,
4744 JOB_BILL_RATE_SCHEDULE_ID,
4745 EMP_BILL_RATE_SCHEDULE_ID,
4746 PROJECT_CURRENCY_CODE,
4747 PROJECT_RATE_DATE,
4748 PROJECT_RATE_TYPE,
4749 PROJECT_BIL_RATE_DATE_CODE,
4750 PROJECT_BIL_RATE_TYPE,
4751 PROJECT_BIL_RATE_DATE,
4752 PROJECT_BIL_EXCHANGE_RATE,
4753 PROJFUNC_CURRENCY_CODE,
4754 PROJFUNC_COST_RATE_TYPE,
4755 PROJFUNC_COST_RATE_DATE,
4756 PROJFUNC_BIL_RATE_DATE_CODE,
4757 PROJFUNC_BIL_RATE_TYPE,
4758 PROJFUNC_BIL_RATE_DATE,
4759 PROJFUNC_BIL_EXCHANGE_RATE,
4760 LABOR_TP_SCHEDULE_ID,
4761 LABOR_TP_FIXED_DATE,
4762 LABOR_SCHEDULE_DISCOUNT,
4763 NVL(ASSIGN_PRECEDES_TASK, 'N'),
4764 LABOR_BILL_RATE_ORG_ID,
4765 LABOR_STD_BILL_RATE_SCHDL,
4766 LABOR_SCHEDULE_FIXED_DATE,
4767 LABOR_SCH_TYPE
4768 FROM Pa_Projects_All P
4769 WHERE P.Project_Id = c_project_id;
4770
4771 Cursor Proj_Assignment(c_assignment_id pa_project_assignments.assignment_id%type) IS
4772 SELECT Fcst_Job_Id,
4773 Fcst_Job_Group_Id,
4774 Project_Role_Id,
4775 ASSIGNMENT_TYPE,
4776 STATUS_CODE
4777 FROM
4778 PA_PROJECT_ASSIGNMENTS PA
4779 WHERE PA.Assignment_id= c_assignment_id;
4780
4781 l_calling_mode VARCHAR2(20);
4782
4783 l_fi_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
4784 l_fi_item_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4785 l_fi_exp_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4786 l_fi_exp_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4787 l_fi_proj_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4788 l_fi_proj_organizationid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4789 l_fi_projid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4790 l_fi_proj_type_class_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4791 l_fi_personid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4792 l_fi_resid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4793 l_fi_asgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4794 l_fi_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4795 l_fi_uom_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4796 l_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
4797 l_fi_pvdr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4798 l_fi_rcvr_papd_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4799 l_fi_exptype_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4800 l_fi_exptypeclass_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4801 l_fi_amount_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4802 l_fi_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
4803
4804 l_cc_taskid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4805 l_cc_expitemid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4806 l_cc_transsource_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4807 l_cc_NLOrgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4808 l_cc_prvdreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4809 l_cc_recvreid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4810 lx_cc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4811 lx_cc_type_tab PA_PLSQL_DATATYPES.Char3TabTyp;
4812 lx_cc_code_tab PA_PLSQL_DATATYPES.Char1TabTyp;
4813 lx_cc_prvdr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4814 lx_cc_recvr_orgzid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4815 lx_cc_recvr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4816 lx_cc_prvdr_orgid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4817 lx_cc_error_stage VARCHAR2(500);
4818 lx_cc_error_code NUMBER;
4819
4820 /* Project Info */
4821 l_prj_type Pa_Projects_All.Project_Type%TYPE;
4822 l_distribution_rule Pa_Projects_All.Distribution_Rule%TYPE;
4823 l_bill_job_group_id Pa_Projects_All.Bill_Job_Group_Id%TYPE;
4824 l_cost_job_group_id Pa_Projects_All.Cost_Job_Group_Id%TYPE;
4825 l_job_bill_rate_sch_id Pa_Projects_All.JOB_BILL_RATE_SCHEDULE_ID%TYPE;
4826 l_emp_bill_rate_sch_id Pa_Projects_All.EMP_BILL_RATE_SCHEDULE_ID%TYPE;
4827 l_prj_curr_code Pa_Projects_All.PROJECT_CURRENCY_CODE%TYPE;
4828 l_prj_rate_date Pa_Projects_All.PROJECT_RATE_DATE%TYPE;
4829 l_prj_rate_type Pa_Projects_All.PROJECT_RATE_TYPE%TYPE;
4830 l_prj_bil_rate_date_code Pa_Projects_All.PROJECT_BIL_RATE_DATE_CODE%TYPE;
4831 l_prj_bil_rate_type Pa_Projects_All.PROJECT_BIL_RATE_TYPE%TYPE;
4832 l_prj_bil_rate_date Pa_Projects_All.PROJECT_BIL_RATE_DATE%TYPE;
4833 l_prj_bil_ex_rate Pa_Projects_All.PROJECT_BIL_EXCHANGE_RATE%TYPE;
4834 l_prjfunc_curr_code Pa_Projects_All.PROJFUNC_CURRENCY_CODE%TYPE;
4835 l_prjfunc_cost_rate_type Pa_Projects_All.PROJFUNC_COST_RATE_TYPE%TYPE;
4836 l_prjfunc_cost_rate_date Pa_Projects_All.PROJFUNC_COST_RATE_DATE%TYPE;
4837 l_prjfunc_bil_rate_date_code Pa_Projects_All.PROJFUNC_BIL_RATE_DATE_CODE%TYPE;
4838 l_prjfunc_bil_rate_type Pa_Projects_All.PROJFUNC_BIL_RATE_TYPE%TYPE;
4839 l_prjfunc_bil_rate_date Pa_Projects_All.PROJFUNC_BIL_RATE_DATE%TYPE;
4840 l_prjfunc_bil_ex_rate Pa_Projects_All.PROJFUNC_BIL_EXCHANGE_RATE%TYPE;
4841 l_labor_tp_schedule_id Pa_Projects_All.LABOR_TP_SCHEDULE_ID%TYPE;
4842 l_labor_tp_fixed_date Pa_Projects_All.LABOR_TP_FIXED_DATE%TYPE;
4843 l_labor_sch_discount Pa_Projects_All.LABOR_SCHEDULE_DISCOUNT%TYPE;
4844 l_asg_precedes_task Pa_Projects_All.ASSIGN_PRECEDES_TASK%TYPE;
4845 l_labor_bill_rate_orgid Pa_Projects_All.LABOR_BILL_RATE_ORG_ID%TYPE;
4846 l_labor_std_bill_rate_sch Pa_Projects_All.LABOR_STD_BILL_RATE_SCHDL%TYPE;
4847 l_labor_sch_fixed_dt Pa_Projects_All.LABOR_SCHEDULE_FIXED_DATE%TYPE;
4848 l_labor_sch_type Pa_Projects_All.LABOR_SCH_TYPE%TYPE;
4849
4850 l_fcst_opt_jobcostrate_sch_id NUMBER;
4851
4852 /* Project Assignment Info */
4853
4854 l_asg_fcst_job_id Pa_Project_Assignments.Fcst_Job_Id%TYPE;
4855 l_asg_fcst_job_group_id Pa_Project_Assignments.Fcst_Job_Group_Id%TYPE;
4856 l_asg_project_role_id Pa_Project_Assignments.Project_Role_Id%TYPE;
4857 l_prj_assignment_type PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE;
4858 l_prj_status_code PA_PROJECT_ASSIGNMENTS.STATUS_CODE%TYPE;
4859
4860 l_projfunc_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4861 l_projfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4862 l_projfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4863 l_projfunc_rev_exch_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4864 l_projfunc_cst_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4865 l_projfunc_cst_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4866 l_project_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4867 l_project_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4868 l_project_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4869 l_project_rev_exch_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4870 l_project_cst_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4871 l_project_cst_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4872
4873 /* Out Parameters */
4874 lx_rt_pfunc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
4875 lx_rt_pfunc_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
4876 lx_rt_pfunc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4877 lx_rt_pfunc_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4878 lx_rt_pfunc_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4879 lx_rt_pfunc_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4880 lx_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
4881 lx_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4882 lx_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4883 lx_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4884 lx_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4885 lx_rt_pfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4886 lx_rt_proj_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
4887 lx_rt_proj_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
4888 lx_rt_proj_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4889 lx_rt_proj_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4890 lx_rt_proj_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4891 lx_rt_proj_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4892 lx_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
4893 lx_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4894 lx_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4895 lx_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4896 lx_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4897 lx_rt_proj_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4898 lx_rt_expfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4899 lx_rt_expfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
4900 lx_rt_expfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4901 lx_rt_expfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4902 lx_rt_expfunc_raw_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4903 lx_rt_expfunc_raw_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
4904 lx_rt_expfunc_bd_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4905 lx_rt_expfunc_bd_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
4906 lx_rt_cost_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4907 lx_rt_txn_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp ;
4908 lx_rt_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4909 lx_rt_txn_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4910 lx_rt_txn_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
4911 lx_rt_rev_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4912 lx_rt_txn_rev_bill_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
4913 lx_rt_txn_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
4914 lx_rt_rev_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4915 lx_rt_cst_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4916 lx_rt_bd_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4917 lx_rt_others_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
4918
4919 lx_asg_precedes_task_tab PA_PLSQL_DATATYPES.Char1TabTyp; -- Added for bug 3255061
4920
4921 lx_rt_error_msg VARCHAR2(1000);
4922 lx_rt_return_status VARCHAR2(30);
4923 lx_rt_msg_count NUMBER;
4924 lx_rt_msg_data VARCHAR2(100);
4925
4926 ERROR_OCCURED VARCHAR2(1);
4927
4928 /* Get Transfer Price Parameters */
4929
4930 l_cc_exp_category Pa_Expenditure_Types.EXPENDITURE_CATEGORY%TYPE;
4931
4932 l_tp_asgid PA_PLSQL_DATATYPES.IdTabTyp;
4933 l_tp_exp_category PA_PLSQL_DATATYPES.Char30TabTyp;
4934 l_tp_labor_nl_flag PA_PLSQL_DATATYPES.Char1TabTyp;
4935 l_tp_taskid PA_PLSQL_DATATYPES.IdTabTyp;
4936 l_tp_scheduleid PA_PLSQL_DATATYPES.IdTabTyp;
4937 l_tp_denom_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
4938 l_tp_rev_distributed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
4939 l_tp_compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
4940 l_tp_fixed_date PA_PLSQL_DATATYPES.DateTabTyp;
4941 l_tp_denom_raw_cost PA_PLSQL_DATATYPES.NumTabTyp;
4942 l_tp_denom_bd_cost PA_PLSQL_DATATYPES.NumTabTyp;
4943 l_tp_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
4944 l_tp_nl_resource PA_PLSQL_DATATYPES.Char20TabTyp;
4945 l_tp_nl_resource_orgzid PA_PLSQL_DATATYPES.IdTabTyp;
4946 l_tp_pa_date PA_PLSQL_DATATYPES.DateTabTyp;
4947 l_prj_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4948 l_prjfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
4949 l_tp_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
4950 l_asg_fcst_jobid_tab PA_PLSQL_DATATYPES.IdTabTyp;
4951
4952 lx_proj_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
4953 lx_proj_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
4954 lx_proj_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
4955 lx_proj_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
4956 lx_projfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
4957 lx_projfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
4958 lx_projfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
4959 lx_projfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
4960 lx_denom_tp_currcode PA_PLSQL_DATATYPES.Char15TabTyp;
4961 lx_denom_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
4962 lx_expfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
4963 lx_expfunc_tp_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
4964 lx_expfunc_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
4965 lx_expfunc_tp_amt PA_PLSQL_DATATYPES.NumTabTyp;
4966 lx_cc_markup_basecode PA_PLSQL_DATATYPES.Char1TabTyp;
4967 lx_tp_ind_compiled_setid PA_PLSQL_DATATYPES.IdTabTyp;
4968 lx_tp_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
4969 lx_tp_base_amount PA_PLSQL_DATATYPES.NumTabTyp;
4970 lx_tp_bill_markup_percent PA_PLSQL_DATATYPES.NumTabTyp;
4971 lx_tp_sch_line_percent PA_PLSQL_DATATYPES.NumTabTyp;
4972 lx_tp_rule_percent PA_PLSQL_DATATYPES.NumTabTyp;
4973 lx_tp_job_id PA_PLSQL_DATATYPES.IdTabTyp;
4974 lx_tp_error_code PA_PLSQL_DATATYPES.Char30TabTyp;
4975 l_tp_array_size NUMBER;
4976 l_tp_debug_mode VARCHAR2(30);
4977 lx_tp_return_status NUMBER;
4978
4979
4980 BEGIN
4981
4982 IF p_debug_mode = 'Y' THEN
4983 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'ENTERING Get_Initial_Transfer_Price', 3);
4984 END IF;
4985
4986 x_return_status := FND_API.G_RET_STS_SUCCESS;
4987
4988 Open Cur_Forecast_Items(p_assignment_id, p_start_date);
4989
4990 PA_DEBUG.g_err_stage := 'Fetching Cur_Forecast_Items';
4991 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4992
4993 l_fi_id_tab.delete;
4994 l_fi_item_type_tab.delete;
4995 l_fi_exp_orgid_tab.delete;
4996 l_fi_exp_organizationid_tab.delete;
4997 l_fi_proj_orgid_tab.delete;
4998 l_fi_proj_organizationid_tab.delete;
4999 l_fi_projid_tab.delete;
5000 l_fi_proj_type_class_tab.delete;
5001 l_fi_personid_tab.delete;
5002 l_fi_resid_tab.delete;
5003 l_fi_asgid_tab.delete;
5004 l_fi_date_tab.delete;
5005 l_fi_uom_tab.delete;
5006 l_fi_pvdr_papd_tab.delete;
5007 l_fi_rcvr_papd_tab.delete;
5008 l_fi_exptype_tab.delete;
5009 l_fi_exptypeclass_tab.delete;
5010 l_fi_amount_type_tab.delete;
5011 l_fi_delete_flag_tab.delete;
5012 l_cc_taskid_tab.delete;
5013 l_cc_expitemid_tab.delete;
5014 l_cc_transsource_tab.delete;
5015 l_cc_NLOrgzid_tab.delete;
5016 l_cc_prvdreid_tab.delete;
5017 l_cc_recvreid_tab.delete;
5018 lx_cc_status_tab.delete;
5019 lx_cc_type_tab.delete;
5020 lx_cc_code_tab.delete;
5021 lx_cc_prvdr_orgzid_tab.delete;
5022 lx_cc_recvr_orgzid_tab.delete;
5023 lx_cc_recvr_orgid_tab.delete;
5024 lx_cc_prvdr_orgid_tab.delete;
5025
5026 FETCH Cur_Forecast_Items BULK COLLECT INTO
5027 l_fi_id_tab,
5028 l_fi_item_type_tab,
5029 l_fi_exp_orgid_tab,
5030 l_fi_exp_organizationid_tab,
5031 l_fi_proj_orgid_tab,
5032 l_fi_proj_organizationid_tab,
5033 l_fi_projid_tab,
5034 l_fi_proj_type_class_tab,
5035 l_fi_personid_tab,
5036 l_fi_resid_tab,
5037 l_fi_asgid_tab,
5038 l_fi_date_tab,
5039 l_fi_uom_tab,
5040 l_fi_pvdr_papd_tab,
5041 l_fi_rcvr_papd_tab,
5042 l_fi_exptype_tab,
5043 l_fi_exptypeclass_tab,
5044 l_fi_amount_type_tab,
5045 l_fi_delete_flag_tab;
5046
5047 CLOSE Cur_Forecast_Items;
5048
5049 IF p_debug_mode = 'Y' THEN
5050 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Cursor cur_forecast_items_fetched', 3);
5051 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));
5052 END IF;
5053
5054
5055 l_cc_taskid_tab(1) := NULL;
5056 l_cc_expitemid_tab(1) := NULL;
5057 l_cc_transsource_tab(1) := NULL;
5058 l_cc_NLOrgzid_tab(1) := NULL;
5059 l_cc_prvdreid_tab(1) := NULL;
5060 l_cc_recvreid_tab(1) := NULL;
5061 lx_cc_type_tab(1) := NULL;
5062 lx_cc_code_tab(1) := NULL;
5063 lx_cc_prvdr_orgzid_tab(1) := NULL;
5064 lx_cc_recvr_orgzid_tab(1) := NULL;
5065 lx_cc_recvr_orgid_tab(1) := NULL;
5066 lx_cc_prvdr_orgid_tab(1) := NULL;
5067 lx_cc_status_tab(1) := NULL;
5068
5069 Pa_Cc_Ident.PA_CC_IDENTIFY_TXN_FI(
5070 P_ExpOrganizationIdTab => l_fi_exp_organizationid_tab,
5071 P_ExpOrgidTab => l_fi_exp_orgid_tab,
5072 P_ProjectIdTab => l_fi_projid_tab,
5073 P_TaskIdTab => l_cc_taskid_tab,
5074 P_ExpItemDateTab => l_fi_date_tab,
5075 P_ExpItemIdTab => l_cc_expitemid_tab,
5076 P_PersonIdTab => l_fi_personid_tab,
5077 P_ExpTypeTab => l_fi_exptype_tab,
5078 P_SysLinkTab => l_fi_exptypeclass_tab,
5079 P_PrjOrganizationIdTab => l_fi_proj_organizationid_tab,
5080 P_PrjOrgIdTab => l_fi_proj_orgid_tab,
5081 P_TransSourceTab => l_cc_transsource_tab,
5082 P_NLROrganizationIdTab => l_cc_NLOrgzid_tab,
5083 P_PrvdrLEIdTab => l_cc_prvdreid_tab,
5084 P_RecvrLEIdTab => l_cc_recvreid_tab,
5085 X_StatusTab => lx_cc_status_tab,
5086 X_CrossChargeTypeTab => lx_cc_type_tab,
5087 X_CrossChargeCodeTab => lx_cc_code_tab,
5088 X_PrvdrOrganizationIdTab => lx_cc_prvdr_orgzid_tab,
5089 X_RecvrOrganizationIdTab => lx_cc_recvr_orgzid_tab,
5090 X_RecvrOrgIdTab => lx_cc_recvr_orgid_tab,
5091 X_PrvdrOrgIdTab => lx_cc_prvdr_orgid_tab,
5092 X_Error_Stage => lx_cc_error_stage,
5093 X_Error_Code => lx_cc_error_code
5094 );
5095
5096 IF p_debug_mode = 'Y' THEN
5097 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Procedure PA_CC_IDENTIFY_TXN_FI executed', 3);
5098 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Status :'||lx_cc_status_tab(1), 3);
5099 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'cc code:'||lx_cc_code_tab(1), 3);
5100 END IF;
5101
5102
5103 If lx_cc_code_tab(1) in ('I', 'B') AND lx_cc_status_tab(1) is NULL THEN
5104 IF l_fi_item_type_tab(1) = 'R' THEN
5105 l_calling_mode := 'ROLE';
5106 ELSIF l_fi_item_type_tab(1) = 'A' THEN
5107 l_calling_mode := 'ASSIGNMENT';
5108 END IF;
5109 OPEN Proj_Details(l_fi_projid_tab(1));
5110 Fetch Proj_Details
5111 INTO
5112 l_prj_type,
5113 l_distribution_rule,
5114 l_bill_job_group_id,
5115 l_cost_job_group_id,
5116 l_job_bill_rate_sch_id,
5117 l_emp_bill_rate_sch_id,
5118 l_prj_curr_code,
5119 l_prj_rate_date,
5120 l_prj_rate_type,
5121 l_prj_bil_rate_date_code,
5122 l_prj_bil_rate_type,
5123 l_prj_bil_rate_date,
5124 l_prj_bil_ex_rate,
5125 l_prjfunc_curr_code,
5126 l_prjfunc_cost_rate_type,
5127 l_prjfunc_cost_rate_date,
5128 l_prjfunc_bil_rate_date_code,
5129 l_prjfunc_bil_rate_type,
5130 l_prjfunc_bil_rate_date,
5131 l_prjfunc_bil_ex_rate,
5132 l_labor_tp_schedule_id,
5133 l_labor_tp_fixed_date,
5134 l_labor_sch_discount,
5135 l_asg_precedes_task,
5136 l_labor_bill_rate_orgid,
5137 l_labor_std_bill_rate_sch,
5138 l_labor_sch_fixed_dt,
5139 l_labor_sch_type;
5140 Close Proj_Details;
5141
5142 l_projfunc_rev_rt_dt_code_tab.delete;
5143 l_projfunc_rev_rt_date_tab.delete;
5144 l_projfunc_rev_rt_type_tab.delete;
5145 l_projfunc_rev_exch_rt_tab.delete;
5146 l_projfunc_cst_rt_date_tab.delete;
5147 l_projfunc_cst_rt_type_tab.delete;
5148 l_project_rev_rt_dt_code_tab.delete;
5149 l_project_rev_rt_date_tab.delete;
5150 l_project_rev_rt_type_tab.delete;
5151 l_project_rev_exch_rt_tab.delete;
5152 l_project_cst_rt_date_tab.delete;
5153 l_project_cst_rt_type_tab.delete;
5154 l_tp_pa_date.delete;
5155
5156 l_projfunc_rev_rt_dt_code_tab(1) := l_prjfunc_bil_rate_date_code;
5157 l_projfunc_rev_rt_date_tab(1) := l_prjfunc_bil_rate_date;
5158 l_projfunc_rev_rt_type_tab(1) := l_prjfunc_bil_rate_type;
5159 l_projfunc_rev_exch_rt_tab(1) := l_prjfunc_bil_ex_rate;
5160 l_projfunc_cst_rt_date_tab(1) := l_prjfunc_cost_rate_date;
5161 l_projfunc_cst_rt_type_tab(1) := l_prjfunc_cost_rate_type;
5162 l_project_rev_rt_dt_code_tab(1) := l_prj_bil_rate_date_code;
5163 l_project_rev_rt_date_tab(1) := l_prj_bil_rate_date;
5164 l_project_rev_rt_type_tab(1) := l_prj_bil_rate_type;
5165 l_project_rev_exch_rt_tab(1) := l_prj_bil_ex_rate;
5166 l_project_cst_rt_date_tab(1) := l_prj_rate_date;
5167 l_project_cst_rt_type_tab(1) := l_prj_rate_type;
5168
5169 Open Proj_Assignment(p_assignment_id);
5170 Fetch Proj_Assignment INTO
5171 l_asg_fcst_job_id,
5172 l_asg_fcst_job_group_id,
5173 l_asg_project_role_id,
5174 l_prj_assignment_type,
5175 l_prj_status_code;
5176 Close Proj_Assignment;
5177
5178 IF l_fi_item_type_tab(1) = 'R' AND
5179 ( l_asg_fcst_job_id IS NULL OR
5180 l_asg_fcst_job_group_id IS NULL ) THEN
5181 BEGIN
5182 /* Starts here bug4004792 changed the table reference to PA_PROJECT_ROLE_TYPES_B instead of the view PA_PROJECT_ROLE_TYPES for performance reason */
5183 SELECT pa_role_job_bg_utils.get_job_id(PR.project_role_id),
5184 --PR.DEFAULT_JOB_ID,
5185 PJ.JOB_GROUP_ID
5186 INTO
5187 l_asg_fcst_job_id,
5188 l_asg_fcst_job_group_id
5189 FROM PA_PROJECT_ROLE_TYPES_B PR,
5190 PER_JOBS PJ
5191 WHERE
5192 PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
5193 -- PJ.JOB_ID = PR.DEFAULT_JOB_ID;
5194 PJ.JOB_ID =pa_role_job_bg_utils.get_job_id(PR.project_role_id);
5195 /* ends here */
5196 EXCEPTION
5197 WHEN NO_DATA_FOUND THEN
5198 IF p_debug_mode = 'Y' THEN
5199 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'No data found in pa_project_role_types', 3);
5200 END IF;
5201 l_asg_fcst_job_id := NULL;
5202 l_asg_fcst_job_group_id := NULL;
5203 WHEN OTHERS THEN
5204 PA_DEBUG.g_err_stage := 'Inside Prj Role others Excep';
5205 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
5206 END;
5207 END IF;
5208
5209 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));
5210 Fetch FI_Attributes into l_fcst_opt_jobcostrate_sch_id, l_cc_exp_category, l_tp_pa_date(1);
5211 Close FI_Attributes;
5212
5213 l_qty_tab.delete;
5214 l_qty_tab(1) := 1;
5215
5216 IF p_debug_mode = 'Y' THEN
5217 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Just Calling PA_RATE_PVT_PKG.CALC_RATE_AMOUNT', 3);
5218 END IF;
5219
5220 PA_RATE_PVT_PKG.CALC_RATE_AMOUNT(
5221 P_CALLING_MODE => l_calling_mode,
5222 P_RATE_CALC_DATE_TAB => l_fi_date_tab,
5223 P_ASGN_START_DATE => p_start_date,
5224 P_ITEM_ID => p_assignment_id,
5225 P_PROJECT_ID => l_fi_projid_tab(1),
5226 P_QUANTITY_TAB => l_qty_tab,
5227 P_FORECAST_JOB_ID => l_asg_fcst_job_id,
5228 P_FORECAST_JOB_GROUP_ID => l_asg_fcst_job_group_id,
5229 P_PERSON_ID => l_fi_personid_tab(1),
5230 P_EXPENDITURE_ORG_ID_TAB => l_fi_exp_orgid_tab,
5231 P_EXPENDITURE_TYPE => l_fi_exptype_tab(1),
5232 P_EXPENDITURE_ORGZ_ID_TAB => l_fi_exp_organizationid_tab,
5233 P_PROJECT_ORG_ID => l_fi_proj_orgid_tab(1),
5234 P_LABOR_COST_MULTI_NAME => NULL,
5235 P_PROJ_COST_JOB_GROUP_ID => NULL,
5236 P_JOB_COST_RATE_SCHEDULE_ID => l_fcst_opt_jobcostrate_sch_id,
5237 P_PROJECT_TYPE => l_prj_type,
5238 P_TASK_ID => NULL,
5239 P_BILL_RATE_MULTIPLIER => NULL,
5240 P_PROJECT_BILL_JOB_GROUP_ID => l_bill_job_group_id,
5241 P_EMP_BILL_RATE_SCHEDULE_ID => l_emp_bill_rate_sch_id,
5242 P_JOB_BILL_RATE_SCHEDULE_ID => l_job_bill_rate_sch_id,
5243 P_DISTRIBUTION_RULE => l_distribution_rule,
5244 p_amount_calc_mode => 'ALL',
5245 P_system_linkage => l_fi_exptypeclass_tab,
5246 p_assign_precedes_task => l_asg_precedes_task,
5247 p_labor_schdl_discnt => l_labor_sch_discount,
5248 p_labor_bill_rate_org_id => l_labor_bill_rate_orgid,
5249 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_sch,
5250 p_labor_schedule_fixed_date => l_labor_sch_fixed_dt,
5251 p_labor_sch_type => l_labor_sch_type,
5252 P_FORECAST_ITEM_ID_TAB => l_fi_id_tab,
5253 P_PROJFUNC_CURRENCY_CODE => l_prjfunc_curr_code,
5254 p_projfunc_rev_rt_dt_code_tab => l_projfunc_rev_rt_dt_code_tab,
5255 p_projfunc_rev_rt_date_tab => l_projfunc_rev_rt_date_tab,
5256 p_projfunc_rev_rt_type_tab => l_projfunc_rev_rt_type_tab,
5257 p_projfunc_rev_exch_rt_tab => l_projfunc_rev_exch_rt_tab,
5258 p_projfunc_cst_rt_date_tab => l_projfunc_cst_rt_date_tab,
5259 p_projfunc_cst_rt_type_tab => l_projfunc_cst_rt_type_tab,
5260 X_PROJFUNC_BILL_RT_TAB => lx_rt_pfunc_bill_rate_tab,
5261 x_projfunc_raw_revenue_tab => lx_rt_pfunc_raw_revenue_tab,
5262 x_projfunc_rev_rt_date_tab => lx_rt_pfunc_rev_rt_date_tab,
5263 x_projfunc_rev_rt_type_tab => lx_rt_pfunc_rev_rt_type_tab,
5264 x_projfunc_rev_exch_rt_tab => lx_rt_pfunc_rev_ex_rt_tab,
5265 x_projfunc_raw_cst_tab => lx_rt_pfunc_raw_cost_tab,
5266 x_projfunc_raw_cst_rt_tab => lx_rt_pfunc_raw_cost_rt_tab,
5267 x_projfunc_burdned_cst_tab => lx_rt_pfunc_bd_cost_tab,
5268 x_projfunc_burdned_cst_rt_tab => lx_rt_pfunc_bd_cost_rt_tab,
5269 x_projfunc_cst_rt_date_tab => lx_rt_pfunc_cost_rt_date_tab,
5270 x_projfunc_cst_rt_type_tab => lx_rt_pfunc_cost_rt_type_tab,
5271 x_projfunc_cst_exch_rt_tab => lx_rt_pfunc_cost_ex_rt_tab,
5272 p_project_currency_code => l_prj_curr_code,
5273 p_project_rev_rt_dt_code_tab => l_project_rev_rt_dt_code_tab,
5274 p_project_rev_rt_date_tab => l_project_rev_rt_date_tab,
5275 p_project_rev_rt_type_tab => l_project_rev_rt_type_tab,
5276 p_project_rev_exch_rt_tab => l_project_rev_exch_rt_tab,
5277 p_project_cst_rt_date_tab => l_project_cst_rt_date_tab,
5278 p_project_cst_rt_type_tab => l_project_cst_rt_type_tab,
5279 x_project_bill_rt_tab => lx_rt_proj_bill_rate_tab,
5280 x_project_raw_revenue_tab => lx_rt_proj_raw_revenue_tab,
5281 x_project_rev_rt_date_tab => lx_rt_proj_rev_rt_date_tab,
5282 x_project_rev_rt_type_tab => lx_rt_proj_rev_rt_type_tab,
5283 x_project_rev_exch_rt_tab => lx_rt_proj_rev_ex_rt_tab,
5284 x_project_raw_cst_tab => lx_rt_proj_raw_cost_tab,
5285 x_project_raw_cst_rt_tab => lx_rt_proj_raw_cost_rt_tab,
5286 x_project_burdned_cst_tab => lx_rt_proj_bd_cost_tab,
5287 x_project_burdned_cst_rt_tab => lx_rt_proj_bd_cost_rt_tab,
5288 x_project_cst_rt_date_tab => lx_rt_proj_cost_rt_date_tab,
5289 x_project_cst_rt_type_tab => lx_rt_proj_cost_rt_type_tab,
5290 x_project_cst_exch_rt_tab => lx_rt_proj_cost_ex_rt_tab,
5291 x_exp_func_curr_code_tab => lx_rt_expfunc_curr_code_tab,
5292 x_exp_func_raw_cst_rt_tab => lx_rt_expfunc_raw_cst_rt_tab,
5293 x_exp_func_raw_cst_tab => lx_rt_expfunc_raw_cst_tab,
5294 x_exp_func_burdned_cst_rt_tab => lx_rt_expfunc_bd_cst_rt_tab,
5295 x_exp_func_burdned_cst_tab => lx_rt_expfunc_bd_cst_tab,
5296 x_exp_func_cst_rt_date_tab => lx_rt_expfunc_cost_rt_date_tab,
5297 x_exp_func_cst_rt_type_tab => lx_rt_expfunc_cost_rt_type_tab,
5298 x_exp_func_cst_exch_rt_tab => lx_rt_expfunc_cost_ex_rt_tab,
5299 x_cst_txn_curr_code_tab => lx_rt_cost_txn_curr_code_tab,
5300 x_txn_raw_cst_rt_tab => lx_rt_txn_raw_cost_rt_tab,
5301 x_txn_raw_cst_tab => lx_rt_txn_raw_cost_tab,
5302 x_txn_burdned_cst_rt_tab => lx_rt_txn_bd_cost_rt_tab,
5303 x_txn_burdned_cst_tab => lx_rt_txn_bd_cost_tab,
5304 x_rev_txn_curr_code_tab => lx_rt_rev_txn_curr_code_tab,
5305 x_txn_rev_bill_rt_tab => lx_rt_txn_rev_bill_rt_tab,
5306 x_txn_rev_raw_revenue_tab => lx_rt_txn_raw_revenue_tab,
5307 X_ERROR_MSG => lx_rt_error_msg,
5308 X_REV_REJCT_REASON_TAB => lx_rt_rev_rejct_reason_tab,
5309 X_CST_REJCT_REASON_TAB => lx_rt_cst_rejct_reason_tab,
5310 X_BURDNED_REJCT_REASON_TAB => lx_rt_bd_rejct_reason_tab,
5311 X_OTHERS_REJCT_REASON_TAB => lx_rt_others_rejct_reason_tab,
5312 X_RETURN_STATUS => lx_rt_return_status,
5313 X_MSG_COUNT => lx_rt_msg_count,
5314 X_MSG_DATA => lx_rt_msg_data
5315 );
5316
5317 IF p_debug_mode = 'Y' THEN
5318 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Done With the PA_RATE_PVT_PKG.CALC_RATE_AMOUNT', 3);
5319 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'x_return status for calc_rate_amount is:'||x_return_status, 3);
5320 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);
5321 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);
5322 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);
5323 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);
5324 END IF;
5325
5326
5327 ERROR_OCCURED := 'N';
5328
5329 IF lx_rt_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5330 ERROR_OCCURED := 'Y';
5331 END IF;
5332
5333 If lx_rt_rev_rejct_reason_tab.exists(1) THEN
5334 IF lx_rt_rev_rejct_reason_tab(1) IS NOT NULL THEN
5335 IF p_debug_mode = 'Y' THEN
5336 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Rev Reject:'||lx_rt_rev_rejct_reason_tab(1), 3);
5337 END IF;
5338 ERROR_OCCURED := 'Y';
5339 END IF;
5340 END IF;
5341
5342 IF lx_rt_cst_rejct_reason_tab.exists(1) THEN
5343 IF lx_rt_cst_rejct_reason_tab(1) IS NOT NULL THEN
5344 IF p_debug_mode = 'Y' THEN
5345 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Raw Cost Reject:'||lx_rt_cst_rejct_reason_tab(1), 3);
5346 END IF;
5347 ERROR_OCCURED := 'Y';
5348 END IF;
5349 END IF;
5350
5351 IF lx_rt_bd_rejct_reason_tab.exists(1) THEN
5352 IF lx_rt_bd_rejct_reason_tab(1) IS NOT NULL THEN
5353 IF p_debug_mode = 'Y' THEN
5354 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Burden Cost Reject:'||lx_rt_bd_rejct_reason_tab(1), 3);
5355 END IF;
5356 ERROR_OCCURED := 'Y';
5357 END IF;
5358 END IF;
5359
5360 IF lx_rt_others_rejct_reason_tab.exists(1) THEN
5361 IF lx_rt_others_rejct_reason_tab(1) IS NOT NULL THEN
5362 IF p_debug_mode = 'Y' THEN
5363 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Other Reject:'||lx_rt_others_rejct_reason_tab(1), 3);
5364 END IF;
5365 ERROR_OCCURED := 'Y';
5366 END IF;
5367 END IF;
5368
5369 IF ERROR_OCCURED = 'Y' THEN
5370 IF p_debug_mode = 'Y' THEN
5371 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Some Error Occurred, Returning null as Rate', 3);
5372 END IF;
5373 x_transfer_price_rate := NULL;
5374 x_transfer_pr_rate_curr := NULL;
5375 x_return_status := FND_API.G_RET_STS_SUCCESS;
5376 Return;
5377 END IF;
5378
5379 l_tp_asgid.delete;
5380 l_tp_exp_category.delete;
5381 l_tp_labor_nl_flag.delete;
5382 l_tp_taskid.delete;
5383 l_tp_scheduleid.delete;
5384 l_prj_curr_code_tab.delete;
5385 l_prjfunc_curr_code_tab.delete;
5386 l_tp_rev_distributed_flag.delete;
5387 l_tp_compute_flag.delete;
5388 l_tp_fixed_date.delete;
5389 l_tp_quantity_tab.delete;
5390 l_asg_fcst_jobid_tab.delete;
5391 l_tp_nl_resource.delete;
5392 l_tp_nl_resource_orgzid.delete;
5393 lx_proj_tp_rate_type.delete;
5394 lx_proj_tp_rate_date.delete;
5395 lx_proj_tp_exchange_rate.delete;
5396 lx_proj_tp_amt.delete;
5397 lx_projfunc_tp_rate_type.delete;
5398 lx_projfunc_tp_rate_date.delete;
5399 lx_projfunc_tp_exchange_rate.delete;
5400 lx_projfunc_tp_amt.delete;
5401 lx_denom_tp_currcode.delete;
5402 lx_denom_tp_amt.delete;
5403 lx_expfunc_tp_rate_type.delete;
5404 lx_expfunc_tp_rate_date.delete;
5405 lx_expfunc_tp_exchange_rate.delete;
5406 lx_expfunc_tp_amt.delete;
5407 lx_cc_markup_basecode.delete;
5408 lx_tp_ind_compiled_setid.delete;
5409 lx_tp_bill_rate.delete;
5410 lx_tp_base_amount.delete;
5411 lx_tp_bill_markup_percent.delete;
5412 lx_tp_sch_line_percent.delete;
5413 lx_tp_rule_percent.delete;
5414 lx_tp_job_id.delete;
5415 lx_tp_error_code.delete;
5416
5417 lx_asg_precedes_task_tab.delete; -- Added for bug 3255061
5418
5419 l_tp_asgid(1) := p_assignment_id;
5420 l_tp_exp_category(1) := l_cc_exp_category;
5421 l_tp_labor_nl_flag(1) := 'Y';
5422 l_tp_taskid(1) := NULL;
5423 l_tp_scheduleid(1) := l_labor_tp_schedule_id;
5424 l_prj_curr_code_tab(1) := l_prj_curr_code;
5425 l_prjfunc_curr_code_tab(1) := l_prjfunc_curr_code;
5426 l_tp_rev_distributed_flag(1) := 'Y';
5427 l_tp_compute_flag(1) := 'Y';
5428 l_tp_fixed_date(1) := l_labor_tp_fixed_date;
5429 l_tp_quantity_tab(1) := 1;
5430 l_asg_fcst_jobid_tab(1) := l_asg_fcst_job_id;
5431 l_tp_nl_resource(1) := NULL;
5432 l_tp_nl_resource_orgzid(1) := NULL;
5433 l_tp_debug_mode := p_debug_mode;
5434 lx_proj_tp_rate_type(1) := NULL;
5435 lx_proj_tp_rate_date(1) := NULL;
5436 lx_proj_tp_exchange_rate(1) := NULL;
5437 lx_proj_tp_amt(1) := NULL;
5438 lx_projfunc_tp_rate_type(1) := NULL;
5439 lx_projfunc_tp_rate_date(1) := NULL;
5440 lx_projfunc_tp_exchange_rate(1) := NULL;
5441 lx_projfunc_tp_amt(1) := NULL;
5442 lx_denom_tp_currcode(1) := NULL;
5443 lx_denom_tp_amt(1) := NULL;
5444 lx_expfunc_tp_rate_type(1) := NULL;
5445 lx_expfunc_tp_rate_date(1) := NULL;
5446 lx_expfunc_tp_exchange_rate(1) := NULL;
5447 lx_expfunc_tp_amt(1) := NULL;
5448 lx_cc_markup_basecode(1) := NULL;
5449 lx_tp_ind_compiled_setid(1) := NULL;
5450 lx_tp_bill_rate(1) := NULL;
5451 lx_tp_base_amount(1) := NULL;
5452 lx_tp_bill_markup_percent(1) := NULL;
5453 lx_tp_sch_line_percent(1) := NULL;
5454 lx_tp_rule_percent(1) := NULL;
5455 lx_tp_job_id(1) := NULL;
5456 lx_tp_error_code(1) := NULL;
5457
5458 lx_asg_precedes_task_tab(1) := l_asg_precedes_task; -- Added for bug 3255061
5459
5460 IF p_debug_mode = 'Y' THEN
5461 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'PA_CC_TRANSFER_PRICE.GET_TRANSFER_PRICE calling', 3);
5462 END IF;
5463
5464 PA_CC_TRANSFER_PRICE.GET_TRANSFER_PRICE(
5465 p_module_name => 'FORECAST',
5466 p_prvdr_organization_id => lx_cc_prvdr_orgzid_tab,
5467 p_recvr_org_id => lx_cc_recvr_orgid_tab,
5468 p_recvr_organization_id => lx_cc_recvr_orgzid_tab,
5469 p_expnd_organization_id => l_fi_exp_organizationid_tab,
5470 p_expenditure_item_id => l_fi_id_tab,
5471 p_expenditure_type => l_fi_exptype_tab,
5472 p_expenditure_category => l_tp_exp_category,
5473 p_expenditure_item_date => l_fi_date_tab,
5474 p_labor_non_labor_flag => l_tp_labor_nl_flag,
5475 p_system_linkage_function => l_fi_exptypeclass_tab,
5476 p_task_id => l_tp_taskid,
5477 p_tp_schedule_id => l_tp_scheduleid,
5478 p_denom_currency_code => lx_rt_cost_txn_curr_code_tab,
5479 p_project_currency_code => l_prj_curr_code_tab,
5480 p_projfunc_currency_code => l_prjfunc_curr_code_tab,
5481 p_revenue_distributed_flag => l_tp_rev_distributed_flag,
5482 p_processed_thru_date => sysdate,
5483 p_compute_flag => l_tp_compute_flag ,
5484 p_tp_fixed_date => l_tp_fixed_date,
5485 p_denom_raw_cost_amount => lx_rt_txn_raw_cost_tab,
5486 p_denom_burdened_cost_amount => lx_rt_txn_bd_cost_tab,
5487 p_raw_revenue_amount => lx_rt_pfunc_raw_revenue_tab,
5488 p_project_id => l_fi_projid_tab,
5489 p_quantity => l_tp_quantity_tab,
5490 p_incurred_by_person_id => l_fi_personid_tab,
5491 p_job_id => l_asg_fcst_jobid_tab,
5492 p_non_labor_resource => l_tp_nl_resource,
5493 p_nl_resource_organization_id => l_tp_nl_resource_orgzid,
5494 p_pa_date => l_tp_pa_date,
5495 p_array_size => 1,
5496 p_debug_mode => l_tp_debug_mode,
5497 p_tp_amt_type_code => l_fi_amount_type_tab,
5498 p_assignment_id => l_tp_asgid,
5499 p_prvdr_operating_unit => lx_cc_prvdr_orgid_tab,
5500 p_assignment_precedes_task => lx_asg_precedes_task_tab, -- Added for bug 3255061
5501 x_proj_tp_rate_type => lx_proj_tp_rate_type,
5502 x_proj_tp_rate_date => lx_proj_tp_rate_date,
5503 x_proj_tp_exchange_rate => lx_proj_tp_exchange_rate,
5504 x_proj_transfer_price => lx_proj_tp_amt,
5505 x_projfunc_tp_rate_type => lx_projfunc_tp_rate_type,
5506 x_projfunc_tp_rate_date => lx_projfunc_tp_rate_date,
5507 x_projfunc_tp_exchange_rate => lx_projfunc_tp_exchange_rate,
5508 x_projfunc_transfer_price => lx_projfunc_tp_amt,
5509 x_denom_tp_currency_code => lx_denom_tp_currcode,
5510 x_denom_transfer_price => lx_denom_tp_amt,
5511 x_acct_tp_rate_type => lx_expfunc_tp_rate_type,
5512 x_acct_tp_rate_date => lx_expfunc_tp_rate_date,
5513 x_acct_tp_exchange_rate => lx_expfunc_tp_exchange_rate,
5514 x_acct_transfer_price => lx_expfunc_tp_amt,
5515 x_cc_markup_base_code => lx_cc_markup_basecode,
5516 x_tp_ind_compiled_set_id => lx_tp_ind_compiled_setid,
5517 x_tp_bill_rate => lx_tp_bill_rate,
5518 x_tp_base_amount => lx_tp_base_amount,
5519 x_tp_bill_markup_percentage => lx_tp_bill_markup_percent,
5520 x_tp_schedule_line_percentage => lx_tp_sch_line_percent,
5521 x_tp_rule_percentage => lx_tp_rule_percent,
5522 x_tp_job_id => lx_tp_job_id,
5523 x_error_code => lx_tp_error_code,
5524 x_return_status => lx_tp_return_status );
5525
5526 IF p_debug_mode = 'Y' THEN
5527 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'status is : '||lx_tp_return_status, 3);
5528 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'rate is :'||x_transfer_price_rate||' curr :'||x_transfer_pr_rate_curr, 3);
5529 END IF;
5530
5531 If lx_tp_return_status <> 0 THEN
5532 x_transfer_price_rate := NULL;
5533 x_transfer_pr_rate_curr := NULL;
5534 ELSE
5535 x_transfer_price_rate := lx_projfunc_tp_amt(1);
5536 IF x_transfer_price_rate IS NULL THEN
5537 x_transfer_pr_rate_curr:=NULL;
5538 ELSE
5539 x_transfer_pr_rate_curr := l_prjfunc_curr_code;
5540 END IF;
5541 END IF;
5542 ELSE
5543 IF p_debug_mode = 'Y' THEN
5544 pa_debug.write('PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 'Returning with Null as value', 3);
5545 END IF;
5546 x_transfer_price_rate := NULL;
5547 x_transfer_pr_rate_curr := NULL;
5548 END IF;
5549
5550 x_return_status := FND_API.G_RET_STS_SUCCESS;
5551 EXCEPTION
5552 WHEN OTHERS THEN
5553 If p_debug_mode = 'Y' THEN
5554 pa_debug.write('PA_CC_TRANSFER_PRICE.GET_INITIAL_TRANSFER_PRICE', 'Unhandled Exception occured', 3);
5555 End if;
5556 /*Added for File.sql.39*/
5557 x_transfer_price_rate := NULL;
5558 x_transfer_pr_rate_curr := NULL;
5559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5560 END Get_Initial_Transfer_Price;
5561
5562 END PA_CC_TRANSFER_PRICE;