[Home] [Help]
PACKAGE BODY: APPS.PA_CC_BL_PROCESS
Source
1 PACKAGE BODY PA_CC_BL_PROCESS AS
2 /* $Header: PAXBLPRB.pls 120.13.12020000.4 2013/04/03 10:29:44 ayushsha ship $ */
3
4 -- Specification of private procedures for this package
5 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7 PROCEDURE new_distribution;
8 PROCEDURE delete_distribution;
9 PROCEDURE update_distribution;
10 PROCEDURE reverse_distribution;
11 PROCEDURE update_ei(p_upd_type IN VARCHAR2);
12 PROCEDURE ei_mass_update;
13 PROCEDURE clean_tables;
14 PROCEDURE log_message( p_message IN VARCHAR2);
15 PROCEDURE set_curr_function(p_function IN VARCHAR2);
16 PROCEDURE reset_curr_function;
17
18 -- Record to hold attributes of current row being processed
19 lcur CcdRecType;
20
21 -- Record to hold attributes of last distribution processed earlier
22 MaxRec CcdRecType;
23
24 g_eicnt PLS_INTEGER;
25
26
27 g_initialization_done BOOLEAN := FALSE;
28 --g_mrc_enabled boolean;
29
30 G_BL_LINE_TYPE CONSTANT pa_cc_dist_lines_all.line_type%TYPE := 'BL';
31
32 -- g_org_id stores the current OU.
33 g_org_id pa_implementations_all.org_id%type ;
34
35 -- Declaration of individual elements to avoid ORA-3113 error because
36 -- FORALL does not allow insert of elements of %rowtype
37
38 in_acct_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
39 in_acct_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
40 in_acct_tp_rate_date PA_PLSQL_DATATYPES.Char30TabTyp;
41 in_acct_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
42 in_amount PA_PLSQL_DATATYPES.NumTabTyp;
43 in_bill_markup_percentage PA_PLSQL_DATATYPES.NumTabTyp;
44 in_bill_rate PA_PLSQL_DATATYPES.NumTabTyp;
45 in_RowId PA_PLSQL_DATATYPES.RowidTabTyp;
46 in_cc_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
47 in_cc_dist_line_id PA_PLSQL_DATATYPES.IDTabTyp;
48 in_cr_code_combination_id PA_PLSQL_DATATYPES.IDTabTyp;
49 in_cross_charge_code PA_PLSQL_DATATYPES.Char1TabTyp;
50 in_denom_tp_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
51 in_denom_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
52 in_dist_line_id_reversed PA_PLSQL_DATATYPES.IDTabTyp;
53 in_dr_code_combination_id PA_PLSQL_DATATYPES.IDTabTyp;
54 in_expenditure_item_id PA_PLSQL_DATATYPES.IDTabTyp;
55 in_expenditure_item_date PA_PLSQL_DATATYPES.DateTabTyp;
56 in_ind_compiled_set_id PA_PLSQL_DATATYPES.IDTabTyp;
57 in_line_num PA_PLSQL_DATATYPES.IDTabTyp;
58 in_line_num_reversed PA_PLSQL_DATATYPES.NumTabTyp;
59 in_line_type PA_PLSQL_DATATYPES.Char2TabTyp;
60 in_markup_calc_base_code PA_PLSQL_DATATYPES.Char1TabTyp;
61 in_org_id PA_PLSQL_DATATYPES.IDTabTyp;
62 in_pa_date PA_PLSQL_DATATYPES.Char30TabTyp;
63 in_gl_date PA_PLSQL_DATATYPES.Char30TabTyp; /* EPP */
64 in_pa_period_name PA_PLSQL_DATATYPES.Char30TabTyp; /* EPP */
65 in_gl_period_name PA_PLSQL_DATATYPES.Char30TabTyp; /* EPP */
66 in_project_id PA_PLSQL_DATATYPES.IDTabTyp;
67 in_prvdr_org_id PA_PLSQL_DATATYPES.IDTabTyp;
68 in_reference_1 PA_PLSQL_DATATYPES.NumTabTyp;
69 in_reference_2 PA_PLSQL_DATATYPES.Char240TabTyp;
70 in_reference_3 PA_PLSQL_DATATYPES.NumTabTyp;
71 in_reversed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
72 in_rule_percentage PA_PLSQL_DATATYPES.NumTabTyp;
73 in_schedule_line_percentage PA_PLSQL_DATATYPES.NumTabTyp;
74 in_task_id PA_PLSQL_DATATYPES.IDTabTyp;
75 in_tp_base_amount PA_PLSQL_DATATYPES.NumTabTyp;
76 in_tp_job_id PA_PLSQL_DATATYPES.NumTabTyp;
77 in_upd_type PA_PLSQL_DATATYPES.Char1TabTyp;
78
79 /*Added for cross proj*/
80 in_tp_amt_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
81 in_project_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
82 in_project_tp_rate_date PA_PLSQL_DATATYPES.Char30TabTyp;
83 in_project_tp_exchange_rate PA_PLSQL_DATATYPES.Char30TabTyp;
84 in_project_transfer_price PA_PLSQL_DATATYPES.Char30TabTyp;
85 in_projfunc_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
86 in_projfunc_tp_rate_date PA_PLSQL_DATATYPES.Char30TabTyp;
87 in_projfunc_tp_exchange_rate PA_PLSQL_DATATYPES.Char30TabTyp;
88 in_projfunc_transfer_price PA_PLSQL_DATATYPES.Char30TabTyp;
89
90 in_project_tp_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
91 in_projfunc_tp_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
92 /*End for cross proj*/
93
94 -- Keeps track of the current line number for the distribution
95
96 l_new_line_num PLS_INTEGER;
97
98 lb_attributes_same boolean;
99 lb_attributes_diff boolean;
100 lb_regular_ei boolean;
101 lb_adjusting_ei boolean;
102 lb_borrlent boolean;
103 lb_non_borrlent boolean;
104 lb_net_zero boolean;
105 lb_non_net_zero boolean;
106 lb_have_last_line boolean;
107 lb_no_last_line boolean;
108 lb_xfaced_last_line boolean;
109 lb_non_xfaced_last_line boolean;
110 lb_regular_last_line boolean;
111 lb_irregular_last_line boolean;
112 lb_ei_denom_tp_null boolean;
113 lb_not_ei_denom_tp_null boolean;
114 lb_reverse_future_period boolean; -- Bug 8538911
115 -------------------------------------------------------------------------------
116 -- pa_bl_pr
117 -------------------------------------------------------------------------------
118
119 PROCEDURE pa_bl_pr
120 (
121 p_module_name IN VARCHAR2
122 ,p_debug_mode IN VARCHAR2
123 ,p_acct_currency_code IN OUT NOCOPY pa_expenditure_items_all.acct_currency_code%TYPE
124 ,p_acct_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
125 ,p_acct_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
126 ,p_acct_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
127 ,p_acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
128 ,p_adjusted_exp_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
129 ,p_array_size IN Number
130 ,p_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp
131 ,p_cc_rejection_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
132 ,p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
133 ,p_cr_code_combination_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
134 ,p_cross_charge_code IN PA_PLSQL_DATATYPES.Char1TabTyp
135 ,p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.char30tabtyp
136 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
137 ,p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.char30tabtyp
138 ,p_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp
139 ,p_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
140 ,p_dr_code_combination_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
141 ,p_exp_item_rowid IN PA_PLSQL_DATATYPES.char30tabtyp
142 ,p_expenditure_category IN PA_PLSQL_DATATYPES.char30tabtyp
143 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.char30tabtyp
144 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
145 ,p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp
146 ,p_expnd_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
147 ,p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IDTabTyp
148 ,p_job_id IN PA_PLSQL_DATATYPES.IDTabTyp
149 ,p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
150 ,p_login_id IN NUMBER
151 ,p_net_zero_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
152 ,p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
153 ,p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp
154 ,p_pa_date IN PA_PLSQL_DATATYPES.char30tabtyp
155 ,p_gl_date IN PA_PLSQL_DATATYPES.char30tabtyp
156 ,p_pa_period_name IN PA_PLSQL_DATATYPES.char30tabtyp
157 ,p_gl_period_name IN PA_PLSQL_DATATYPES.char30tabtyp
158 ,p_primary_sob_id IN gl_sets_of_books.set_of_books_id%TYPE
159 ,p_processed_thru_date IN Date
160 ,p_program_application_id IN NUMBER
161 ,p_program_id IN NUMBER
162 ,p_project_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
163 ,p_project_id IN PA_PLSQL_DATATYPES.IDTabTyp
164 ,p_prvdr_org_id IN pa_implementations_all.org_id%TYPE
165 ,p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
166 ,p_quantity IN PA_PLSQL_DATATYPES.char30tabtyp
167 ,p_raw_revenue_amount IN PA_PLSQL_DATATYPES.char30tabtyp
168 ,p_recvr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
169 ,p_recvr_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
170 ,p_request_id IN NUMBER
171 ,p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
172 ,p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp
173 ,p_task_id IN PA_PLSQL_DATATYPES.IDTabTyp
174 ,p_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
175 ,p_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
176 ,p_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
177 ,p_tp_fixed_date IN PA_PLSQL_DATATYPES.char30tabtyp
178 ,p_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
179 ,p_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
180 ,p_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
181 ,p_tp_schedule_id IN PA_PLSQL_DATATYPES.IDTabTyp
182 ,p_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
183 ,p_user_id IN NUMBER
184 /*Added for cross proj*/
185 ,p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp
186 ,p_assignment_id IN PA_PLSQL_DATATYPES.IDTabTyp
187 ,p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
188 ,p_project_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
189 ,p_project_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
190 ,p_project_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
191 ,p_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
192 ,p_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
193 ,p_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
194 /*End for cross proj*/
195 /* Bill rate Discount*/
196 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
197 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
198 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.Char30TabTyp,
199 p_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
200 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
201 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
202 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
203 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
204 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp ,
205 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
206 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
207 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
208 p_raw_cost_rate IN PA_PLSQL_DATATYPES.Char30TabTyp,
209 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
210 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
211 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
212 p_exp_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
213 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
214
215 p_burden_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
216 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
217 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
218 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
219 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
220 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
221 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
222 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
223 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
224 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
225 /* Added the last two parameters for Doosan Rate api changes */
226 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
227 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
228 /* Added for UOM enhancement */
229 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
230 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
231
232 ) IS
233
234 i PLS_INTEGER;
235 x_return_status NUMBER;
236 l_reversal_reqd BOOLEAN;
237 l_prev_rec_exist BOOLEAN;
238 l_parent_dist_code VARCHAR2(2);
239
240 l_source_eid pa_expenditure_items_all.expenditure_item_id%TYPE;
241 l_transfer_status_code pa_cc_dist_lines_all.transfer_status_code%TYPE;
242
243 /*Added for cross proj*/
244 p_project_transfer_price PA_PLSQL_DATATYPES.Char30TabTyp;
245 p_projfunc_transfer_price PA_PLSQL_DATATYPES.Char30TabTyp;
246 /*End for cross proj*/
247
248 l_exists NUMBER; -- Bug 8538911
249
250 BEGIN
251
252
253 -- Set Debugging info
254 IF p_debug_mode = 'Y'
255 THEN
256 pa_cc_utils.g_debug_mode := TRUE;
257 ELSE
258 pa_cc_utils.g_debug_mode := FALSE;
259 END IF;
260
261
262 set_curr_function('pa_bl_pr');
263
264 pa_debug.set_process( x_process => 'PLSQL',
265 x_debug_mode => p_debug_mode);
266 pa_debug.G_Err_Stage := 'Starting pa_bl_pr' ;
267
268
269
270 IF P_DEBUG_MODE = 'Y' THEN
271 log_message('reverse_distribution: ' || '50: Entered pa_bl_pr');
272 END IF;
273
274 -- Perform initalization if it is not already done
275 IF g_initialization_done
276 THEN
277 null;
278 ELSE
279 -- Perform initialization like determining reporting sob, etc.
280 initialization (
281 p_request_id => p_request_id
282 ,p_program_application_id => p_program_application_id
283 ,p_program_id => p_program_id
284 ,p_user_id => p_user_id
285 ,p_login_id => p_login_id
286 ,p_prvdr_org_id => p_prvdr_org_id
287 ,p_primary_sob_id => p_primary_sob_id
288 );
289
290 END IF;
291
292
293 -- Initialize record counters
294 g_ucnt := 0;
295 g_icnt := 0;
296 g_dcnt := 0;
297 g_eicnt:= 0;
298
299 IF P_DEBUG_MODE = 'Y' THEN
300 log_message('reverse_distribution: ' || '100: Selected org id');
301 END IF;
302
303 -----------------------------------------------------------------
304 -- Compute Transfer Price for all eligible items
305 -----------------------------------------------------------------
306
307 -- Call the Transfer Price API to get determine the Transfer Price and
308 -- its attributes
309
310 IF P_DEBUG_MODE = 'Y' THEN
311 log_message('reverse_distribution: ' || '150: Calling transfer price API');
312 END IF;
313
314
315 pa_cc_transfer_price.get_transfer_price
316 (
317 p_module_name => p_module_name
318 ,p_prvdr_organization_id => p_prvdr_organization_id
319 ,p_recvr_org_id => p_recvr_org_id
320 ,p_recvr_organization_id => p_recvr_organization_id
321 ,p_expnd_organization_id => p_expnd_organization_id
322 ,p_expenditure_item_id => p_expenditure_item_id
323 ,p_expenditure_type => p_expenditure_type
324 ,p_expenditure_category => p_expenditure_category
325 ,p_expenditure_item_date => p_expenditure_item_date
326 ,p_labor_non_labor_flag => p_labor_non_labor_flag
327 ,p_system_linkage_function => p_system_linkage_function
328 ,p_task_id => p_task_id
329 ,p_tp_schedule_id => p_tp_schedule_id
330 ,p_denom_currency_code => p_denom_currency_code
331 ,p_project_currency_code => p_project_currency_code
332 ,p_revenue_distributed_flag => p_revenue_distributed_flag
333 ,p_processed_thru_date => p_processed_thru_date
334 ,p_compute_flag => p_compute_flag
335 ,p_tp_fixed_date => p_tp_fixed_date
336 ,p_denom_raw_cost_amount => p_denom_raw_cost_amount
337 ,p_denom_burdened_cost_amount => p_denom_burdened_cost_amount
338 ,p_raw_revenue_amount => p_raw_revenue_amount
339 ,p_project_id => p_project_id
340 ,p_quantity => p_quantity
341 ,p_incurred_by_person_id => p_incurred_by_person_id
342 ,p_job_id => p_job_id
343 ,p_non_labor_resource => p_non_labor_resource
344 ,p_nl_resource_organization_id => p_nl_resource_organization_id
345 ,p_pa_date => p_pa_date
346 ,p_array_size => p_array_size
347 ,p_debug_mode => p_debug_mode
348 ,x_denom_tp_currency_code => p_denom_tp_currency_code
349 ,x_denom_transfer_price => p_denom_transfer_price
350 ,x_acct_tp_rate_type => p_acct_tp_rate_type
351 ,x_acct_tp_rate_date => p_acct_tp_rate_date
352 ,x_acct_tp_exchange_rate => p_acct_tp_exchange_rate
353 ,x_acct_transfer_price => p_acct_transfer_price
354 ,x_cc_markup_base_code => p_cc_markup_base_code
355 ,x_tp_ind_compiled_set_id => p_tp_ind_compiled_set_id
356 ,x_tp_bill_rate => p_tp_bill_rate
357 ,x_tp_base_amount => p_tp_base_amount
358 ,x_tp_bill_markup_percentage => p_tp_bill_markup_percentage
359 ,x_tp_job_id => p_tp_job_id
360 ,x_tp_schedule_line_percentage => p_tp_schedule_line_percentage
361 ,x_tp_rule_percentage => p_tp_rule_percentage
362 ,x_error_code => p_cc_rejection_code
363 ,x_return_status => x_return_status
364 /* Added for cross proj*/
365 ,p_projfunc_currency_code => p_projfunc_currency_code
366 ,p_tp_amt_type_code => p_tp_amt_type_code
367 ,p_assignment_id => p_assignment_id
368 ,x_proj_tp_rate_type => p_project_tp_rate_type
369 ,x_proj_tp_rate_date => p_project_tp_rate_date
370 ,x_proj_tp_exchange_rate => p_project_tp_exchange_rate
371 ,x_proj_transfer_price => p_project_transfer_price
372 ,x_projfunc_tp_rate_type => p_projfunc_tp_rate_type
373 ,x_projfunc_tp_rate_date => p_projfunc_tp_rate_date
374 ,x_projfunc_tp_exchange_rate => p_projfunc_tp_exchange_rate
375 ,x_projfunc_transfer_price => p_projfunc_transfer_price,
376 /* End for cross proj*/
377 /*Bill rate discount */
378 p_dist_rule => p_dist_rule,
379 p_mcb_flag => p_mcb_flag,
380 p_bill_rate_multiplier => p_bill_rate_multiplier,
381 p_raw_cost => p_raw_cost,
382 p_labor_schdl_discnt => p_labor_schdl_discnt,
383 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date,
384 p_bill_job_grp_id => p_bill_job_grp_id,
385 p_labor_sch_type => p_labor_sch_type,
386 p_project_org_id => p_project_org_id,
387 p_project_type => p_project_type,
388 p_exp_func_curr_code => p_exp_func_curr_code,
389 p_incurred_by_organz_id => p_incurred_by_organz_id,
390 p_raw_cost_rate => p_raw_cost_rate,
391 p_override_to_organz_id => p_override_to_organz_id,
392 p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id,
393 p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id,
394 p_exp_raw_cost => p_exp_raw_cost,
395 p_assignment_precedes_task => p_assignment_precedes_task,
396
397 p_burden_cost => p_burden_cost,
398 p_task_nl_bill_rate_org_id => p_task_nl_bill_rate_org_id,
399 p_proj_nl_bill_rate_org_id => p_proj_nl_bill_rate_org_id,
400 p_task_nl_std_bill_rate_sch => p_task_nl_std_bill_rate_sch,
401 p_proj_nl_std_bill_rate_sch => p_proj_nl_std_bill_rate_sch,
402 p_nl_task_sch_date => p_nl_task_sch_date,
403 p_nl_proj_sch_date => p_nl_proj_sch_date,
404 p_nl_task_sch_discount => p_nl_task_sch_discount,
405 p_nl_proj_sch_discount => p_nl_proj_sch_discount,
406 p_nl_sch_type => p_nl_sch_type,
407 p_task_nl_std_bill_rate_sch_id => p_task_nl_std_bill_rate_sch_id,
408 p_proj_nl_std_bill_rate_sch_id => p_proj_nl_std_bill_rate_sch_id,
409 p_uom_flag => p_uom_flag
410 );
411
412 IF P_DEBUG_MODE = 'Y' THEN
413 log_message('reverse_distribution: ' || '200: Finished transfer price API');
414 END IF;
415
416
417 -----------------------------------------------------------
418 -- Start checking individual EIs
419 -----------------------------------------------------------
420
421 IF P_DEBUG_MODE = 'Y' THEN
422 log_message('reverse_distribution: ' || '250: Checking elements');
423 END IF;
424
425 FOR i in 1 .. p_array_size
426 LOOP
427
428 IF P_DEBUG_MODE = 'Y' THEN
429 log_message('reverse_distribution: ' || '300: Processing EI Id: ' || to_char(p_expenditure_item_id(i)));
430 END IF;
431
432 /* remove this later */
433 IF P_DEBUG_MODE = 'Y' THEN
434 log_message('reverse_distribution: ' || '300: pa_date [' || p_pa_date(i) ||
435 '] p_gl_date [' || p_gl_date(i) ||
436 '] p_pa_period_name [' || p_pa_period_name(i) ||
437 '] p_gl_period_name [' || p_gl_period_name(i) || ']');
438 END IF;
439
440 /* remove this later */
441
442 -- If there are no rejections on the line, only then it needs to be
443 -- processed. If there are rejections, just update the EI with the
444 -- appropriate status code and move on to the next item
445
446 lcur.cc_rejection_code := p_cc_rejection_code(i);
447 lcur.EIRowId := chartorowid(p_exp_item_rowid(i));
448
449 IF lcur.cc_rejection_code IS NOT NULL
450 THEN
451 -- If there is a rejection recorded already, then no processing needs
452 -- to be done other than recording the fact that the EI needs to be
453 -- updated with a rejection code
454
455 IF P_DEBUG_MODE = 'Y' THEN
456 log_message('reverse_distribution: ' || '350: Rejection in EI : ' || lcur.cc_rejection_code);
457 END IF;
458 update_ei('R');
459 IF P_DEBUG_MODE = 'Y' THEN
460 log_message('reverse_distribution: ' || '400: Back from call to update_ei');
461 END IF;
462
463 ELSE
464 IF P_DEBUG_MODE = 'Y' THEN
465 log_message('reverse_distribution: ' || '450: No rejection in EI...processing');
466 log_message('reverse_distribution: ' || '500: Transfer price amount = ' || p_acct_transfer_price(i));
467 END IF;
468 -- Set flags to record status of record retrieved
469
470 -- Copy all attributes of the current record of the table to globally
471 -- accessible single record
472 lcur.acct_currency_code := p_acct_currency_code;
473 lcur.acct_tp_exchange_rate := p_acct_tp_exchange_rate(i);
474 lcur.acct_tp_rate_date := to_date(p_acct_tp_rate_date(i),'YYYY/MM/DD');
475 lcur.acct_tp_rate_type := p_acct_tp_rate_type(i);
476 lcur.amount := p_acct_transfer_price(i);
477 lcur.adjusted_exp_item_id := p_adjusted_exp_item_id(i);
478 lcur.bill_markup_percentage := p_tp_bill_markup_percentage(i);
479 lcur.bill_rate := p_tp_bill_rate(i);
480 lcur.CcdRowId := null;
481 lcur.cr_code_combination_id := p_cr_code_combination_id(i);
482 lcur.cross_charge_code := p_cross_charge_code(i);
483 lcur.denom_tp_currency_code := p_denom_tp_currency_code(i);/*Added for bug 2150468 */
484 lcur.denom_transfer_price := p_denom_transfer_price(i);
485 lcur.dist_line_id_reversed := null;
486 lcur.dr_code_combination_id := p_dr_code_combination_id(i);
487 lcur.expenditure_item_id := p_expenditure_item_id(i);
488 lcur.expenditure_item_date := to_date(p_expenditure_item_date(i),'YYYY/MM/DD');
489 lcur.ind_compiled_set_id := p_tp_ind_compiled_set_id(i);
490 lcur.line_num := null;
491 lcur.line_num_reversed := null;
492 lcur.markup_calc_base_code := p_cc_markup_base_code(i);
493 lcur.pa_date := to_date(p_pa_date(i),'YYYY/MM/DD');
494 lcur.gl_date := to_date(p_gl_date(i),'YYYY/MM/DD'); /* EPP */
495 lcur.pa_period_name := p_pa_period_name(i); /* EPP */
496 lcur.gl_period_name := p_gl_period_name(i); /* EPP */
497 lcur.project_id := p_project_id(i);
498 lcur.reversed_flag := null;
499 lcur.rule_percentage := p_tp_rule_percentage(i);
500 lcur.schedule_line_percentage := p_tp_schedule_line_percentage(i);
501 lcur.task_id := p_task_id(i);
502 lcur.tp_base_amount := p_tp_base_amount(i);
503 lcur.tp_job_id := p_tp_job_id(i);
504
505 /*Added for cross proj*/
506 lcur.tp_amt_type_code := p_tp_amt_type_code(i);
507 lcur.project_tp_rate_type := p_project_tp_rate_type(i);
508 lcur.project_tp_rate_date := to_date(p_project_tp_rate_date(i),'YYYY/MM/DD');
509 lcur.project_tp_exchange_rate := p_project_tp_exchange_rate(i);
510 lcur.project_transfer_price := p_project_transfer_price(i);
511 lcur.projfunc_tp_rate_type := p_projfunc_tp_rate_type(i);
512 lcur.projfunc_tp_rate_date := to_date(p_projfunc_tp_rate_date(i),'YYYY/MM/DD');
513 lcur.projfunc_tp_exchange_rate:= p_projfunc_tp_exchange_rate(i);
514 lcur.projfunc_transfer_price := p_projfunc_transfer_price(i);
515
516 lcur.project_tp_currency_code := p_project_currency_code(i);
517 lcur.projfunc_tp_currency_code:= p_projfunc_currency_code(i);
518 /*End for cross proj*/
519
520 -- Determine if adjusting EI or original EI. Also determine which EI
521 -- is the source for further distributions. For regular EIs, this is
522 -- the same EI while for adjusting EIs, it is the EI that is being
523 -- adjusted
524
525 IF lcur.adjusted_exp_item_id IS NULL
526 THEN
527 IF P_DEBUG_MODE = 'Y' THEN
528 log_message('reverse_distribution: ' || '550: Detected regular EI');
529 END IF;
530 lb_regular_ei := TRUE;
531 lb_adjusting_ei := FALSE;
532 l_source_eid := lcur.expenditure_item_id;
533 ELSE
534 IF P_DEBUG_MODE = 'Y' THEN
535 log_message('reverse_distribution: ' || '600: Detected reversing EI');
536 END IF;
537 lb_regular_ei := FALSE;
538 lb_adjusting_ei := TRUE;
539 l_source_eid := lcur.adjusted_exp_item_id;
540 END IF;
541
542 -- Determine if net zero or not. Net zero will always be true for
543 -- adjusting EI but set it here anyway
544
545 /* Bug 8538911 . Modified the below code section to distinguish the reverse future period EIs from other adjusting EIs*/
546 lb_reverse_future_period := FALSE;
547 l_exists := 0;
548
549 IF p_net_zero_flag(i) = 'Y'
550 THEN
551 if lb_regular_ei then
552 begin
553 select 1 into l_exists
554 from dual
555 where exists
556 ( select 'Reverse in future period net-zero pair'
557 from pa_expenditure_items_all ei,
558 pa_cost_distribution_lines_all cdl,
559 pa_cost_distribution_lines_all reversal_cdl,
560 -- Start Changes for bug 15892024
561 pa_expenditures_all exp,
562 pa_expenditure_groups_all exp_grp
563 -- End Changes for bug 15892024
564 where ei.adjusted_expenditure_item_id = lcur.expenditure_item_id
565 and ei.expenditure_item_id = reversal_cdl.expenditure_item_id
569 and exp.expenditure_id = ei.expenditure_id
566 and cdl.expenditure_item_id = lcur.expenditure_item_id
567 and cdl.pa_period_name <> reversal_cdl.pa_period_name
568 -- Start Changes for bug 15892024
570 and exp_grp.expenditure_group = exp.expenditure_group
571 and exp_grp.period_accrual_flag = 'Y'
572 -- End Changes for bug 15892024
573 );
574
575 IF P_DEBUG_MODE = 'Y' THEN
576 log_message('reverse_distribution: ' || '620: Reverse in Future period net-zero pair, original EI');
577 END IF;
578 exception
579 when others then
580 l_exists := 0;
581 end;
582 else -- lb_regular_ei
583 begin
584 select 1 into l_exists
585 from dual
586 where exists
587 ( select 'Reverse in future period net-zero pair'
588 from pa_expenditure_items_all parent_ei,
589 pa_cost_distribution_lines_all cdl,
590 pa_cost_distribution_lines_all parent_cdl,
591 -- Start Changes for bug 15892024
592 pa_expenditures_all exp,
593 pa_expenditure_groups_all exp_grp
594 -- End Changes for bug 15892024
595 where parent_ei.expenditure_item_id = lcur.adjusted_exp_item_id
596 and parent_ei.expenditure_item_id = parent_cdl.expenditure_item_id
597 and cdl.expenditure_item_id = lcur.expenditure_item_id
598 and parent_cdl.pa_period_name <> cdl.pa_period_name
599 -- Start Changes for bug 15892024
600 and exp.expenditure_id = parent_ei.expenditure_id
601 and exp_grp.expenditure_group = exp.expenditure_group
602 and exp_grp.period_accrual_flag = 'Y'
603 -- End Changes for bug 15892024
604 );
605 IF l_exists = 1 THEN
606 lb_reverse_future_period := TRUE;
607 lb_regular_ei := TRUE;
608 lb_adjusting_ei := FALSE;
609 END IF;
610 IF P_DEBUG_MODE = 'Y' THEN
611 log_message('reverse_distribution: ' || '630: Reverse in Future period net-zero pair, reversal EI');
612 END IF;
613 exception
614 when others then
615 l_exists := 0;
616 end;
617 end if; -- lb_regular_ei
618 END IF; --p_net_zero_flag (shweta)
619
620 IF l_exists = 0 and p_net_zero_flag(i) = 'Y' THEN
621 -- IF p_net_zero_flag(i) = 'Y'
622 IF P_DEBUG_MODE = 'Y' THEN
623 log_message('reverse_distribution: ' || '650: Detected net zero EI');
624 END IF;
625 lb_net_zero := TRUE;
626 lb_non_net_zero := FALSE;
627 ELSE
628 IF P_DEBUG_MODE = 'Y' THEN
629 log_message('reverse_distribution: ' || '700: Detected non-net-zero EI');
630 END IF;
631 lb_net_zero := FALSE;
632 lb_non_net_zero := TRUE;
633 END IF;
634 /* Bug 8538911 changes end */
635
636 -- Determine the cross charge code of the EI
637
638 IF lcur.cross_charge_code = 'B'
639 THEN
640 IF P_DEBUG_MODE = 'Y' THEN
641 log_message('reverse_distribution: ' || '750: Borrowed and Lent EI');
642 END IF;
643 lb_borrlent := TRUE;
644 lb_non_borrlent := FALSE;
645 ELSE
646 IF P_DEBUG_MODE = 'Y' THEN
647 log_message('reverse_distribution: ' || '800: Non-borrowed and lent EI');
648 END IF;
649 lb_borrlent := FALSE;
650 lb_non_borrlent := TRUE;
651 END IF;
652
653 -- Reset line attributes before looking for the last distribution
654
655 lb_have_last_line := FALSE;
656 lb_no_last_line := TRUE;
657 lb_regular_last_line := FALSE ;
658 lb_irregular_last_line := FALSE;
659 lb_xfaced_last_line := FALSE;
660 lb_non_xfaced_last_line := FALSE;
661 l_new_line_num := 0; -- Keep at zero; increment before insert
662
663 -- Select the attributes of the last line
664 -- For adjusting EIs, the last line is picked up from the adjusted EI
665
666 BEGIN
667
668 l_new_line_num := 0;
669 IF P_DEBUG_MODE = 'Y' THEN
670 log_message('reverse_distribution: ' || '850: Examine last line');
671 END IF;
672
673 SELECT rowid,
674 cc_dist_line_id,
675 acct_currency_code,
676 acct_tp_exchange_rate,
677 acct_tp_rate_date,
678 acct_tp_rate_type,
679 amount,
680 bill_markup_percentage,
681 bill_rate,
682 cr_code_combination_id,
683 cross_charge_code,
684 denom_tp_currency_code,
685 denom_transfer_price,
686 dist_line_id_reversed,
687 dr_code_combination_id,
688 expenditure_item_id,
689 ind_compiled_set_id,
690 line_num,
691 line_num_reversed,
692 markup_calc_base_code,
693 project_id,
694 reversed_flag,
695 rule_percentage,
696 schedule_line_percentage,
697 task_id,
698 tp_base_amount,
699 tp_job_id,
700 transfer_status_code,
701 /* Added for cross proj*/
702 tp_amt_type_code,
703 project_tp_rate_type,
704 project_tp_rate_date,
705 project_tp_exchange_rate,
706 project_transfer_price,
707 projfunc_tp_rate_type,
708 projfunc_tp_rate_date,
709 projfunc_tp_exchange_rate,
710 projfunc_transfer_price,
711
712 project_tp_currency_code,
713 projfunc_tp_currency_code
714 /*End for cross proj*/
715 INTO
716 maxrec.CcdRowid,
717 maxrec.cc_dist_line_id,
718 maxrec.acct_currency_code,
719 maxrec.acct_tp_exchange_rate,
720 maxrec.acct_tp_rate_date,
721 maxrec.acct_tp_rate_type,
722 maxrec.amount,
723 maxrec.bill_markup_percentage,
724 maxrec.bill_rate,
725 maxrec.cr_code_combination_id,
726 maxrec.cross_charge_code,
727 maxrec.denom_tp_currency_code,
728 maxrec.denom_transfer_price,
729 maxrec.dist_line_id_reversed,
730 maxrec.dr_code_combination_id,
731 maxrec.expenditure_item_id,
732 maxrec.ind_compiled_set_id,
733 maxrec.line_num,
734 maxrec.line_num_reversed,
735 maxrec.markup_calc_base_code,
736 maxrec.project_id,
737 maxrec.reversed_flag,
738 maxrec.rule_percentage,
739 maxrec.schedule_line_percentage,
740 maxrec.task_id,
741 maxrec.tp_base_amount,
742 maxrec.tp_job_id,
743 l_transfer_status_code,
744 /*Added for cross proj*/
745 maxrec.tp_amt_type_code,
746 maxrec.project_tp_rate_type,
747 maxrec.project_tp_rate_date,
748 maxrec.project_tp_exchange_rate,
749 maxrec.project_transfer_price,
750 maxrec.projfunc_tp_rate_type,
751 maxrec.projfunc_tp_rate_date,
752 maxrec.projfunc_tp_exchange_rate,
753 maxrec.projfunc_transfer_price,
754
755 maxrec.project_tp_currency_code,
756 maxrec.projfunc_tp_currency_code
757 /*end for cross proj*/
758 FROM pa_cc_dist_lines
759 WHERE expenditure_item_id = l_source_eid
760 AND line_type = G_BL_LINE_TYPE
761 AND line_num = (SELECT max(line_num)
762 FROM pa_cc_dist_lines
763 WHERE expenditure_item_id = l_source_eid
764 AND line_type = G_BL_LINE_TYPE);
765
766 IF P_DEBUG_MODE = 'Y' THEN
767 log_message('reverse_distribution: ' || '900: Found last line');
768 END IF;
769
770 lb_have_last_line := TRUE;
771 lb_no_last_line := FALSE;
772
773
774 -- Determine whether the last distribution is a reversing distribution
775 -- or a regular one
776
777 IF MaxRec.dist_line_id_reversed IS NULL
778
779 THEN
780 IF P_DEBUG_MODE = 'Y' THEN
781 log_message('reverse_distribution: ' || '950: Regular line');
782 END IF;
783 lb_regular_last_line := TRUE;
784 lb_irregular_last_line := FALSE;
785 ELSE
786 IF P_DEBUG_MODE = 'Y' THEN
787 log_message('reverse_distribution: ' || '1000: Reversing line');
788 END IF;
789 lb_regular_last_line := FALSE;
790 lb_irregular_last_line := TRUE;
791 END IF;
792
793 -- Determine whether the last distribution has been interfaced to GL
794
795 IF l_transfer_status_code = 'A'
796
797 THEN
798 IF P_DEBUG_MODE = 'Y' THEN
799 log_message('reverse_distribution: ' || '1050: Interfaced line');
800 END IF;
801 lb_xfaced_last_line := TRUE;
802 lb_non_xfaced_last_line := FALSE;
803 ELSE
804 IF P_DEBUG_MODE = 'Y' THEN
805 log_message('reverse_distribution: ' || '1100: Non Interfaced line');
806 END IF;
807 lb_xfaced_last_line := FALSE;
808 lb_non_xfaced_last_line := TRUE;
809 END IF;
810
811 -- For a regular EI, the new distribution has a line number greater
812 -- than the last distribution of the same type. For an adjusting EI,
813 -- the distribution line number is always 1 (since an adjusting EI
814 -- cannot be adjusted further and will always have only a single line)
815 -- Need not check for a regular EI as there will be a max distribution
816 -- only for a regular EI
817 -- Keep at max; increment before insert
818
819 /* Bug 5263823 */
820 IF lcur.adjusted_exp_item_id IS NULL THEN
821 l_new_line_num := MaxRec.line_num;
822 ELSE
823 l_new_line_num := 0;
824 END if;
825
826
827 EXCEPTION
828 WHEN NO_DATA_FOUND
829 THEN
830 IF P_DEBUG_MODE = 'Y' THEN
831 log_message('reverse_distribution: ' || '1150: No last line found');
832 END IF;
833 l_new_line_num := 0; -- True for adjusting EIs also
834 lb_have_last_line := FALSE;
835 lb_no_last_line := TRUE;
836
837 WHEN OTHERS
838 THEN
839 IF P_DEBUG_MODE = 'Y' THEN
840 log_message('reverse_distribution: ' || '1200: Exception in getting last line');
841 END IF;
842 raise;
843 END;
844
845
846 IF P_DEBUG_MODE = 'Y' THEN
847 log_message('reverse_distribution: ' || '1250: Comparing attributes');
848 END IF;
849 lb_attributes_same := FALSE;
850
851 -- If a reversal line is available, check if reversal is required by
852 -- comparing all attributes
853 -- If it is not a Borrowed and Lent transaction, then the attributes
854 -- are different by default as Transfer Price need not be calculated
855
856 IF lb_have_last_line and lb_borrlent
857 AND
858 (
859 ((MaxRec.schedule_line_percentage IS NULL
860 AND lcur.schedule_line_percentage IS NULL ) OR
861 (MaxRec.schedule_line_percentage IS NULL ))/*Cross proj*/
862 AND ((MaxRec.bill_rate IS NULL AND lcur.bill_rate IS NULL ) OR
863 (MaxRec.bill_rate = lcur.bill_rate ))
864 AND ((MaxRec.bill_markup_percentage IS NULL
865 AND lcur.bill_markup_percentage IS NULL ) OR
866 (MaxRec.bill_markup_percentage = lcur.bill_markup_percentage ))
867 AND ((MaxRec.ind_compiled_set_id IS NULL
868 AND lcur.ind_compiled_set_id IS NULL ) OR
869 (MaxRec.ind_compiled_set_id = lcur.ind_compiled_set_id ))
870 AND ((MaxRec.markup_calc_base_code IS NULL
871 AND lcur.markup_calc_base_code IS NULL ) OR
872 (MaxRec.markup_calc_base_code = lcur.markup_calc_base_code ))
873 AND ((MaxRec.tp_base_amount IS NULL
874 AND lcur.tp_base_amount IS NULL ) OR
875 (MaxRec.tp_base_amount = lcur.tp_base_amount ))
876 AND ((MaxRec.tp_job_id IS NULL
877 AND lcur.tp_job_id IS NULL ) OR
878 (MaxRec.tp_job_id = lcur.tp_job_id ))
879 AND ((MaxRec.acct_tp_rate_date IS NULL
880 AND lcur.acct_tp_rate_date IS NULL ) OR
881 (MaxRec.acct_tp_rate_date = lcur.acct_tp_rate_date ))
882 AND ((MaxRec.acct_tp_rate_type IS NULL
883 AND lcur.acct_tp_rate_type IS NULL ) OR
884 (MaxRec.acct_tp_rate_type = lcur.acct_tp_rate_type ))
885 AND ((MaxRec.acct_tp_exchange_rate IS NULL
886 AND lcur.acct_tp_exchange_rate IS NULL ) OR
887 (MaxRec.acct_tp_exchange_rate = lcur.acct_tp_exchange_rate ))
888 /*Added cross proj*/
889 AND ((MaxRec.tp_amt_type_code IS NULL
890 AND lcur.tp_amt_type_code IS NULL ) OR
891 (MaxRec.tp_amt_type_code = lcur.tp_amt_type_code ))
892 AND ((MaxRec.project_tp_rate_date IS NULL
893 AND lcur.project_tp_rate_date IS NULL ) OR
894 (MaxRec.project_tp_rate_date = lcur.project_tp_rate_date ))
895 AND ((MaxRec.project_tp_rate_type IS NULL
896 AND lcur.project_tp_rate_type IS NULL ) OR
897 (MaxRec.project_tp_rate_type = lcur.project_tp_rate_type ))
898 AND ((MaxRec.project_tp_exchange_rate IS NULL
899 AND lcur.project_tp_exchange_rate IS NULL ) OR
900 (MaxRec.project_tp_exchange_rate = lcur.project_tp_exchange_rate ))
901 AND ((MaxRec.project_transfer_price IS NULL
902 AND lcur.project_transfer_price IS NULL ) OR
903 (MaxRec.project_transfer_price = lcur.project_transfer_price ))
904 AND ((MaxRec.projfunc_tp_rate_date IS NULL
905 AND lcur.projfunc_tp_rate_date IS NULL ) OR
906 (MaxRec.projfunc_tp_rate_date = lcur.projfunc_tp_rate_date ))
907 AND ((MaxRec.projfunc_tp_rate_type IS NULL
908 AND lcur.projfunc_tp_rate_type IS NULL ) OR
909 (MaxRec.projfunc_tp_rate_type = lcur.projfunc_tp_rate_type ))
910 AND ((MaxRec.projfunc_tp_exchange_rate IS NULL
911 AND lcur.projfunc_tp_exchange_rate IS NULL ) OR
912 (MaxRec.projfunc_tp_exchange_rate = lcur.projfunc_tp_exchange_rate ))
913 AND ((MaxRec.projfunc_transfer_price IS NULL
914 AND lcur.projfunc_transfer_price IS NULL ) OR
915 (MaxRec.projfunc_transfer_price = lcur.projfunc_transfer_price ))
916
917 AND ((MaxRec.project_tp_currency_code IS NULL
918 AND lcur.project_tp_currency_code IS NULL ) OR
919 (MaxRec.project_tp_currency_code =
920 lcur.project_tp_currency_code ))
921 AND ((MaxRec.projfunc_tp_currency_code IS NULL
922 AND lcur.projfunc_tp_currency_code IS NULL ) OR
923 (MaxRec.projfunc_tp_currency_code =
924 lcur.projfunc_tp_currency_code ))
925 /*End for cross proj*/
926 AND (MaxRec.denom_tp_currency_code = lcur.denom_tp_currency_code)
927 AND (MaxRec.denom_transfer_price = lcur.denom_transfer_price)
928 AND (MaxRec.dr_code_combination_id = lcur.dr_code_combination_id)
929 AND (MaxRec.cr_code_combination_id = lcur.cr_code_combination_id)
930 )
931 THEN
932 IF P_DEBUG_MODE = 'Y' THEN
933 log_message('reverse_distribution: ' || '1300: Attributes same');
934 END IF;
935 lb_attributes_same := TRUE;
936 lb_attributes_diff := FALSE;
937 ELSE
938 IF P_DEBUG_MODE = 'Y' THEN
939 log_message('reverse_distribution: ' || '1350: Attributes NOT same');
940 END IF;
941 lb_attributes_same := FALSE;
942 lb_attributes_diff := TRUE;
943 END IF;
944
945 -- Messages for debug
946 -- bug 8538911 begin
947 IF lb_regular_ei
948 THEN
949 if lb_reverse_future_period then
950 IF P_DEBUG_MODE = 'Y' THEN
951 log_message('reverse_distribution: ' || '1400: Check: Future Period Reversed EI');
952 END IF;
953 else
954 IF P_DEBUG_MODE = 'Y' THEN
955 log_message('reverse_distribution: ' || '1400: Check: Regular EI');
956 END IF;
957 end if;
958 END IF;
959 -- bug 8538911 end
960
961 IF lb_adjusting_ei
962
963 THEN
964 IF P_DEBUG_MODE = 'Y' THEN
965 log_message('reverse_distribution: ' || '1450: Check: Adjusting EI');
966 END IF;
967 END IF;
968
969 IF lb_non_net_zero
970
971 THEN
972 IF P_DEBUG_MODE = 'Y' THEN
973 log_message('reverse_distribution: ' || '1500: Check: Not net zero');
974 END IF;
975 END IF;
976
977 IF lb_net_zero
978
979 THEN
980 IF P_DEBUG_MODE = 'Y' THEN
981 log_message('reverse_distribution: ' || '1550: Check: Net zero');
982 END IF;
983 END IF;
984
985 IF lb_borrlent
986
987 THEN
988 IF P_DEBUG_MODE = 'Y' THEN
989 log_message('reverse_distribution: ' || '1600: Check: Borrowed and Lent');
990 END IF;
991 END IF;
992
993 IF lb_non_borrlent
994
995 THEN
996 IF P_DEBUG_MODE = 'Y' THEN
997 log_message('reverse_distribution: ' || '1650: Check: Not Borrowed and Lent');
998 END IF;
999 END IF;
1000
1001 IF lb_have_last_line
1002 THEN
1003 IF P_DEBUG_MODE = 'Y' THEN
1004 log_message('reverse_distribution: ' || '1700: Check: Have last line');
1005 END IF;
1006 IF lb_regular_last_line
1007 THEN
1008 IF P_DEBUG_MODE = 'Y' THEN
1009 log_message('reverse_distribution: ' || '1750: Check: Regular last line');
1010 END IF;
1011 END IF;
1012
1013 IF lb_irregular_last_line
1014 THEN
1015 IF P_DEBUG_MODE = 'Y' THEN
1016 log_message('reverse_distribution: ' || '1800: Check: Reversing last line');
1017 END IF;
1018 END IF;
1019
1020 IF lb_xfaced_last_line
1021 THEN
1022 IF P_DEBUG_MODE = 'Y' THEN
1023 log_message('reverse_distribution: ' || '1850: Check: Interfaced last line');
1024 END IF;
1025 END IF;
1026
1027 IF lb_non_xfaced_last_line
1028 THEN
1029 IF P_DEBUG_MODE = 'Y' THEN
1030 log_message('reverse_distribution: ' || '1900: Check: Non - interfaced last line');
1031 END IF;
1032 END IF;
1033 END IF;
1034
1035 IF lb_no_last_line
1036 THEN
1037 IF P_DEBUG_MODE = 'Y' THEN
1038 log_message('reverse_distribution: ' || '1950: Check: No last line');
1039 END IF;
1040 END IF;
1041
1042 -- End of debug messages
1043
1044 --
1045 -- The logic for deciding the action to be taken on an item is based
1046 -- on the following table. The first few columns (up to Attr Same)
1047 -- specify the attributes of the transaction while the last few
1048 -- columns specify the action to be taken. The columns are as follows:
1049 --
1050 -- 1 - Serial number of the rule
1051 --
1052 -- Conditions:
1053 --
1054 -- 2 - The type of item. An item can be an original EI or a reversing
1055 -- EI. Orig and Rev refer to the two possible values
1056 --
1057 -- 3 - Net Zero. Whether the item is a net zero item. For example,
1058 -- case 7 referes to an original item which is not a net zero item,
1059 -- case 12 refers to an original net zero item while Case 20 refers to
1060 -- a reversing net zero item
1061 --
1062 -- 4 - The current value of the cross charge code on the EI
1063 -- 5 - Whether the Item has a CC Distribution
1064 --
1065 -- 6 - Whether the last distribution has been transferred to Oracle
1066 -- General Ledger
1067 --
1068 -- 7 - Whether the last distribution is a regular distribution. The
1069 -- last line can be a regular or reversing distribution
1070 --
1071 -- 8 - Whether the current attributes for the EI (e.g. Transfer price
1072 -- calculated in the current run) are the same as the attributes of
1073 -- the last distribution line
1074 --
1075 -- Actions:
1076 --
1077 -- 9 - Whether the last distribution should be reversed (X-indicates reverse)
1078 -- X - Reverse
1079 --
1080 -- 10 - Whether a new distribution should be created (X-indicates reverse)
1081 -- X - Create new
1082 --
1083 -- 11 - Whether the existing distribution should be updated
1084 -- X - update
1085 --
1086 -- 12 - Whether the existing distribution should be deleted
1087 -- X - delete
1088 -- X* - delete distribution of reversed EI
1089 --
1090 -- 13 - How the EI should be updated
1091 -- U - Update with current values determined
1092 -- N - Update with current values determined
1093 -- but reverse the amounts
1094 -- G - Wipe out all attributes on reversed EI
1095 -- X - Leave attributes unchanged (except
1096 -- processed flag)
1097 --
1098 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1099 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1100 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1101 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1102 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1103 -- | | | | |Line|line|line| |v |w |d |l |p |
1104 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1105 -- | 1|Orig| No| B| Yes| Yes| Yes| No| X| X| | | A|
1106 -- | 1a|Orig| No| B| Yes| Yes| Yes| Yes| | | | | A|
1107 -- | 2|Orig| No| B| Yes| Yes| No| | | X| | | A|
1108 -- | 3|Orig| No| B| Yes| No| Yes| No| | | X| | A|
1109 -- | 3a|Orig| No| B| Yes| No| Yes| Yes| | | | | A|
1110 -- | 4|Orig| No| B| Yes| No| No| | | X| | | A|
1111 -- | 5|Orig| No| B| No| | | | | X| | | A|
1112 -- | 6|Orig| No| NXI| No| | | | | | | | X|
1113 -- | 7|Orig| No| NXI| Yes| Yes| Yes| | X| | | | X|
1114 -- | 8|Orig| No| NXI| Yes| Yes| No| | | | | | X|
1115 -- | 9|Orig| No| NXI| Yes| No| Yes| | | | | X| X|
1116 -- | 10|Orig| No| NXI| Yes| No| No| | | | | | X|
1117 -- | 11|Orig| Yes| B| Yes| Yes| Yes| | | | | | A|
1118 -- | 12|Orig| Yes| B| Yes| Yes| No| | | | | | A|
1119 -- | 13|Orig| Yes| B| Yes| No| Yes| | | | | | A|
1120 -- | 14|Orig| Yes| B| Yes| No| No| | | | | | A|
1121 -- | 15|Orig| Yes| B| No| | | | | | | | X|
1122 -- | 16|Orig| Yes| NXI| Yes| Yes| Yes| | X| | | | X|
1123 -- | 17|Orig| Yes| NXI| Yes| Yes| No| | | | | | X|
1124 -- | 18|Orig| Yes| NXI| Yes| No| Yes| | | | | X| X|
1125 -- | 19|Orig| Yes| NXI| Yes| No| No| | | | | | X|
1126 -- | 20|Orig| Yes| NXI| No| | | | | | | | X|
1127 -- | 21| Adj| Yes| B| Yes| Yes| Yes| | X| | | | N|
1128 -- | 22| Adj| Yes| B| Yes| No| Yes| | | | |X*| G|
1129 -- | 23| Adj| Yes| B| Yes| | No| | | | | | X|
1130 -- | 24| Adj| Yes| B| No| | | | | | | | X|
1131 -- | 25| Adj| Yes| NXI| | | | | | | | | X|
1132 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1133 --
1134 --
1135 --
1136 -- DELETE the existing distribution
1137 -- Note that in case 22 below, the existing distribution
1138 -- is on the adjusted EI. The delete_distribution procedure
1139 -- simply deletes the distribution based on the rowid
1140 -- of the distribution retrieved. For adjusting EIs
1141 -- the distribution retrieved is the last distribution
1142 -- of the adjusted EI
1143 --
1144 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1145 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1146 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1147 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1148 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1149 -- | | | | |Line|line|line| |v |w |d |l |p |
1150 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1151 -- | 9|Orig| No| NXI| Yes| No| Yes| | | | | X| X|
1152 -- | 18|Orig| Yes| NXI| Yes| No| Yes| | | | | X| X|
1153 -- | 22| Adj| Yes| B| Yes| No| Yes| | | | |X*| G|
1154 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1155 --
1156
1157 IF (lb_regular_last_line AND lb_non_xfaced_last_line) AND
1161 -- Delete the existing distribution if required
1158 ((lb_regular_ei AND lb_non_borrlent) OR
1159 (lb_adjusting_ei AND lb_borrlent ))
1160 THEN
1162 IF P_DEBUG_MODE = 'Y' THEN
1163 log_message('reverse_distribution: ' || '2000: Deleting distribution');
1164 END IF;
1165 delete_distribution;
1166 IF P_DEBUG_MODE = 'Y' THEN
1167 log_message('reverse_distribution: ' || '2050: delete_distribution call over');
1168 END IF;
1169 END IF;
1170
1171 --
1172 -- UPDATE the existing distribution
1173 --
1174 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1175 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1176 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1177 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1178 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1179 -- | | | | |Line|line|line| |v |w |d |l |p |
1180 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1181 -- | 3|Orig| No| B| Yes| No| Yes| No| | | X| | A|
1182 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1183 --
1184
1185 IF lb_regular_ei AND lb_non_net_zero
1186 AND lb_borrlent AND lb_non_xfaced_last_line
1187 AND lb_regular_last_line AND lb_attributes_diff
1188 THEN
1189 IF P_DEBUG_MODE = 'Y' THEN
1190 log_message('reverse_distribution: ' || '2100: Updating distribution');
1191 END IF;
1192 update_distribution;
1193 IF P_DEBUG_MODE = 'Y' THEN
1194 log_message('reverse_distribution: ' || '2150: Update_distribution call over');
1195 END IF;
1196 END IF;
1197
1198 --
1199 -- REVERSE a distribution under the following conditions
1200 --
1201 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1202 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1203 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1204 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1205 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1206 -- | | | | |Line|line|line| |v |w |d |l |p |
1207 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1208 -- | 1|Orig| No| B| Yes| Yes| Yes| No| X| X| | | A|
1209 -- | 7|Orig| No| NXI| Yes| Yes| Yes| | X| | | | X|
1210 -- | 16|Orig| Yes| NXI| Yes| Yes| Yes| | X| | | | X|
1211 -- | 21| Adj| Yes| B| Yes| Yes| Yes| | X| | | | N|
1212 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1213 --
1214
1215 IF (lb_regular_last_line AND lb_xfaced_last_line)
1216 AND ( (lb_regular_ei AND lb_non_borrlent) OR
1217 (lb_adjusting_ei AND lb_borrlent) OR
1218 (lb_regular_ei AND lb_non_net_zero AND lb_borrlent AND lb_attributes_diff))
1219 THEN
1220 IF P_DEBUG_MODE = 'Y' THEN
1221 log_message('reverse_distribution: ' || '2200: Reversing distribution');
1222 END IF;
1223 reverse_distribution;
1224 IF P_DEBUG_MODE = 'Y' THEN
1225 log_message('reverse_distribution: ' || '2250: Reverse distribution call over');
1226 END IF;
1227 END IF;
1228
1229
1230 -- NEW distribution
1231 --
1232 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1233 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1234 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1235 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1236 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1237 -- | | | | |Line|line|line| |v |w |d |l |p |
1238 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1239 -- | 1|Orig| No| B| Yes| Yes| Yes| No| X| X| | | A|
1240 -- | 2|Orig| No| B| Yes| Yes| No| | | X| | | A|
1241 -- | 4|Orig| No| B| Yes| No| No| | | X| | | A|
1242 -- | 5|Orig| No| B| No| | | | | X| | | A|
1243 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1244 --
1245
1246 IF (lb_regular_ei AND lb_non_net_zero AND lb_borrlent) AND
1247 ( (lb_no_last_line) OR
1248 (lb_xfaced_last_line AND lb_regular_last_line AND lb_attributes_diff) OR
1249 (lb_irregular_last_line))
1250 THEN
1251 IF P_DEBUG_MODE = 'Y' THEN
1252 log_message('reverse_distribution: ' || '2300: New distribution');
1253 END IF;
1254 --Bug 10211579 added the if condition
1255 --Bug 13614976 changed the below condition
1256 --IF lb_reverse_future_period= FALSE THEN
1257 -- IF lb_xfaced_last_line= FALSE THEN -- Commented for bug 15892024
1258 new_distribution;
1259 -- END IF; -- Commented for bug 15892024
1260 IF P_DEBUG_MODE = 'Y' THEN
1261 log_message('reverse_distribution: ' || '2350: New distribution call over');
1262 END IF;
1263 END IF;
1264
1265 --------------------------------------------------------------------
1266 -- Update the values/flags on the EI
1267 --------------------------------------------------------------------
1268
1269 -- After the distributions have been created, the EI has to be updated
1270 -- with the relevant values/flags. There are a few choices here:
1271 -- 1. Update with currently derived values
1272 -- 2. Update with negated amounts of last distribution (for reversing
1273 -- EIs)
1274 -- 3. Do not change any values
1275 -- 4. Wipe out attributes on reversed EI as its distribution is
1276 -- being deleted
1277 --
1278 -- Update with All currently derived values
1279 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1280 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1281 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1282 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1283 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1284 -- | | | | |Line|line|line| |v |w |d |l |p |
1285 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1286 -- | 1|Orig| No| B| Yes| Yes| Yes| No| X| X| | | A|
1287 -- | 1a|Orig| No| B| Yes| Yes| Yes| Yes| | | | | A|
1288 -- | 2|Orig| No| B| Yes| Yes| No| | | X| | | A|
1289 -- | 3|Orig| No| B| Yes| No| Yes| No| | | X| | A|
1290 -- | 3a|Orig| No| B| Yes| No| Yes| Yes| | | | | A|
1291 -- | 4|Orig| No| B| Yes| No| No| | | X| | | A|
1292 -- | 5|Orig| No| B| No| | | | | X| | | A|
1293 -- | 11|Orig| Yes| B| Yes| Yes| Yes| | | | | | A|
1294 -- | 12|Orig| Yes| B| Yes| Yes| No| | | | | | A|
1295 -- | 13|Orig| Yes| B| Yes| No| Yes| | | | | | A|
1296 -- | 14|Orig| Yes| B| Yes| No| No| | | | | | A|
1297 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1298 --
1299
1300 -- Bug#1073836: Table was incorrect. Changed the table and modified
1301 -- the code to have conditions to update the EI as per the table.
1302 -- Originally, the problem was if there was no change in the Transfer
1303 -- Price attributes (when comparing with the last line) and the cross
1304 -- charge code was changed from B to I and back to B, then the
1305 -- transfer price attributes get wiped out but the procedure performed
1306 -- no updates.
1307
1308 IF ( (lb_regular_ei AND lb_borrlent) AND
1309 ( (lb_non_net_zero AND lb_no_last_line) OR
1310 (lb_have_last_line)
1311 )
1312 )
1313 THEN
1314
1315 IF P_DEBUG_MODE = 'Y' THEN
1316 log_message('reverse_distribution: ' || '2400: Updating EI with current values');
1317 END IF;
1318
1319 update_ei('A');
1320
1321 IF P_DEBUG_MODE = 'Y' THEN
1322 log_message('reverse_distribution: ' || '2450: Back from update_ei-A');
1323 END IF;
1324
1325 -- Otherwise, Update EI with reversed amounts
1326 --
1327 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1328 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1329 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1330 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1331 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1332 -- | | | | |Line|line|line| |v |w |d |l |p |
1333 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1334 -- | 21| Adj| Yes| B| Yes| Yes| Yes| | X| | | | N|
1335 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1336 --
1337 /* Bug 2377743 changed variable lb_non_xfaced_last_line to
1338 ** lb_xfaced_last_line as last line should be interface */
1339 ELSIF lb_adjusting_ei AND lb_borrlent AND lb_xfaced_last_line AND
1340 lb_regular_last_line
1341 THEN
1342 IF P_DEBUG_MODE = 'Y' THEN
1343 log_message('reverse_distribution: ' || '2500: Updating EI with reversed amounts');
1344 END IF;
1345 update_ei('N');
1346 IF P_DEBUG_MODE = 'Y' THEN
1347 log_message('reverse_distribution: ' || '2550: Back from update_ei-N');
1348 END IF;
1349
1350 --
1351 -- Null out the attributes on the reversed EI (in this case the
1352 -- corresponding distribution on the original EI is deleted)
1353 --
1354 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1355 -- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |9 |10|11|12|13|
1356 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1357 -- |Sr |Type|Net | CC |Have|Xfer|Reg |Attr|R |N |U |D |EI|
1358 -- | | |zero|Code|Last|last|last|Same|e |e |p |e |U |
1359 -- | | | | |Line|line|line| |v |w |d |l |p |
1360 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1361 -- | 22| Adj| Yes| B| Yes| No| Yes| | | | |X*| G|
1362 -- +===+====+====+====+====+====+====+====+==+==+==+==+==+
1363 --
1364
1365 ELSIF (lb_adjusting_ei AND lb_borrlent AND lb_non_xfaced_last_line AND
1366 lb_regular_last_line)
1367 THEN
1368 IF P_DEBUG_MODE = 'Y' THEN
1369 log_message('reverse_distribution: ' || '2600: Update EI with null in reversed EI');
1370 END IF;
1371 update_ei('G');
1372 IF P_DEBUG_MODE = 'Y' THEN
1373 log_message('reverse_distribution: ' || '2650: Back from update_ei-G');
1374 END IF;
1375 ELSE
1376
1377 -- Otherwise update EI as processed without any changes to existing
1378 -- values
1379
1380 IF P_DEBUG_MODE = 'Y' THEN
1381 log_message('reverse_distribution: ' || '2700: Updating EI without any changes');
1382 END IF;
1383 update_ei('X');
1384 IF P_DEBUG_MODE = 'Y' THEN
1385 log_message('reverse_distribution: ' || '2750: Back from update_ei-X');
1386 END IF;
1387 END IF;
1388
1389 END IF; -- if no rejection
1390
1391 IF P_DEBUG_MODE = 'Y' THEN
1392 log_message('reverse_distribution: ' || '2800: Finished processing for current item');
1393 END IF;
1394
1395 END LOOP; -- Finished processing all input records
1396
1397 ------------------- End of individual item processing ------------
1398
1399 IF P_DEBUG_MODE = 'Y' THEN
1400 log_message('reverse_distribution: ' || '2850: Finished processing for ALL input items');
1401 END IF;
1402
1403 ---------------------------------------------------------------
1404 -- Mass database operations
1405 ---------------------------------------------------------------
1406
1407 -- In the above steps, each EI was examined and the corresponding
1408 -- arrays were filled with information to Insert/Update/Delete data in
1409 -- the distributions and Update the EI table. The following section
1410 -- performs these mass operations
1411
1412 IF g_dcnt > 0
1413 THEN
1414 IF P_DEBUG_MODE = 'Y' THEN
1415 log_message('reverse_distribution: ' || '2900: Calling mass delete');
1416 END IF;
1417 mass_delete;
1418 END IF;
1419
1420 IF P_DEBUG_MODE = 'Y' THEN
1421 log_message('reverse_distribution: ' || '2950: Checking update required, g_ucnt = ' || g_ucnt);
1422 END IF;
1423 IF g_ucnt > 0
1424 THEN
1425 IF P_DEBUG_MODE = 'Y' THEN
1426 log_message('reverse_distribution: ' || '3000: Calling mass update');
1427 END IF;
1428 mass_update;
1429 END IF;
1430
1431 IF P_DEBUG_MODE = 'Y' THEN
1432 log_message('reverse_distribution: ' || '3050: Checking insert required');
1433 END IF;
1434
1435 IF g_icnt > 0
1436 THEN
1437 IF P_DEBUG_MODE = 'Y' THEN
1438 log_message('reverse_distribution: ' || '3100: Calling mass insert');
1439 END IF;
1440 mass_insert;
1441 END IF;
1442
1443 /* Bug 9672705 start */
1444
1445 /* ***** This is a handling done for 'Reverse in Future Period' B/L transactions for the reversal trxns.
1446 The underlying architecture does not allow cc_dist_line_id to be picked up from the original CCDL, as the records would
1447 get inserted only during mass_insert. ****** */
1448
1449 update pa_cc_dist_lines ccdl2
1450 set dist_line_id_reversed =
1451 (select ccdl1.cc_dist_line_id from pa_cc_dist_lines_all ccdl1,
1452 pa_expenditure_items_all ei1,
1453 pa_expenditure_items_all ei2
1454 where ccdl1.expenditure_item_id = ei1.expenditure_item_id
1455 and ccdl2.expenditure_item_id = ei2.expenditure_item_id
1456 and ei2.adjusted_expenditure_item_id = ei1.expenditure_item_id
1457 and ccdl1.line_type = 'BL'
1458 and ccdl1.line_num =
1459 (select max(line_num)
1460 from pa_cc_dist_lines
1461 where expenditure_item_id = ei1.expenditure_item_id
1462 and line_type = 'BL')
1463 )
1464 where ccdl2.request_id = pa_cc_utils.g_request_id
1465 and ccdl2.dist_line_id_reversed is null
1466 and ccdl2.line_type = 'BL'
1467 and exists
1468 (select 1 from pa_expenditure_items_all ei
1469 where ei.expenditure_item_id = ccdl2.expenditure_item_id
1470 and ei.adjusted_expenditure_item_id is not null
1471 and ei.net_zero_adjustment_flag = 'Y'
1472 --and ei.PRVDR_ACCRUAL_DATE is not null
1473 );
1474
1475
1476 /* Bug 9672705 end */
1477
1478 IF P_DEBUG_MODE = 'Y' THEN
1479 log_message('reverse_distribution: ' || '3150: Calling mass EI updates');
1480 END IF;
1481 ei_mass_update;
1482 IF P_DEBUG_MODE = 'Y' THEN
1483 log_message('reverse_distribution: ' || '3200: All updates over');
1484 END IF;
1485
1486 reset_curr_function;
1487
1488 EXCEPTION
1489 WHEN OTHERS
1490 THEN
1491 IF P_DEBUG_MODE = 'Y' THEN
1492 log_message('reverse_distribution: ' || '3250: ERROR in main procedure at '|| pa_debug.g_err_stack);
1493 log_message('reverse_distribution: ' || '3300: ERROR: ' || sqlerrm);
1494 END IF;
1495 raise;
1496
1497 END pa_bl_pr;
1498
1499 -------------------------------------------------------------------------------
1500 -- initialization
1501 -------------------------------------------------------------------------------
1502
1503 PROCEDURE initialization(
1504 p_request_id IN NUMBER
1505 ,p_program_application_id IN NUMBER
1506 ,p_program_id IN NUMBER
1507 ,p_user_id IN NUMBER
1508 ,p_login_id IN NUMBER
1509 ,p_prvdr_org_id IN NUMBER
1510 ,p_primary_sob_id IN NUMBER
1511 ) IS
1512
1513 i PLS_INTEGER;
1514 l_mrc_enabled VARCHAR2(1) := 'N';
1515
1516 CURSOR c1 (p_primary_sob_id IN NUMBER,
1517 p_org_id IN NUMBER) IS
1518 SELECT ledger_id,
1519 currency_code
1520 FROM gl_alc_ledger_rships_v
1521 WHERE source_ledger_id = p_primary_sob_id
1522 AND application_id = pa_cc_utils.g_program_application_id
1523 AND relationship_enabled_flag = 'Y'
1524 AND (org_id = -99 OR org_id = p_org_id); -- R12 MRC changes
1525 -- AND nvl(org_id,-99) = nvl(p_org_id, -99);
1526 -- AND trunc(sysdate) BETWEEN start_date AND nvl(end_date, sysdate);
1527 BEGIN
1528
1529 set_curr_function('initialization');
1530
1531 IF P_DEBUG_MODE = 'Y' THEN
1532 log_message('reverse_distribution: ' || '3350: Entered initialization');
1533 END IF;
1534
1535 -- Copy who columns into global variables
1536
1537 pa_cc_utils.g_request_id := p_request_id;
1538 pa_cc_utils.g_program_application_id := p_program_application_id;
1539 pa_cc_utils.g_program_id := p_program_id;
1540 pa_cc_utils.g_user_id := p_user_id;
1541 pa_cc_utils.g_login_id := p_login_id;
1542
1543 -- Set global variables
1544 pa_cc_utils.g_prvdr_org_id := p_prvdr_org_id;
1545 pa_cc_utils.g_primary_sob_id := p_primary_sob_id;
1546
1547
1548 -- Set initialization flag so that this procedure is not performed in
1549 -- the second call to this package for the next set of expenditure
1550 -- items to be processed
1551
1552 g_initialization_done := TRUE;
1553
1554
1555 -- Determine whether MRC is enabled and remember it within this
1556 -- package so that it does not have to be called again
1557
1558 i := 0;
1559
1560 IF P_DEBUG_MODE = 'Y' THEN
1561 log_message('reverse_distribution: ' || '3400: Getting reporting SOBs');
1562 END IF;
1563
1564 FOR c in c1 (p_primary_sob_id, p_prvdr_org_id)
1565 LOOP
1566
1567 i := i + 1;
1568
1569 pa_cc_utils.g_reporting_sob_id(i) := c.ledger_id;
1570
1571 IF P_DEBUG_MODE = 'Y' THEN
1572 log_message('reverse_distribution: ' || '3450: Got a reporting SOB: ' || pa_cc_utils.g_reporting_sob_id(i));
1573 END IF;
1574
1575 pa_cc_utils.g_reporting_curr_code(i) := c.currency_code;
1576
1577 END LOOP;
1578
1579 -- MRC is enabled if at least one reporting set of books is present
1580
1581 IF pa_cc_utils.g_reporting_sob_id.exists(1)
1582 THEN
1583 --g_mrc_enabled := TRUE;
1584 IF P_DEBUG_MODE = 'Y' THEN
1585 log_message('reverse_distribution: ' || '3500: MRC ENABLED');
1586 END IF;
1587 ELSE
1588 --g_mrc_enabled := FALSE;
1589 IF P_DEBUG_MODE = 'Y' THEN
1590 log_message('reverse_distribution: ' || '3550: MRC DISABLED');
1591 END IF;
1592 END IF;
1593
1594
1595 IF P_DEBUG_MODE = 'Y' THEN
1596 log_message('reverse_distribution: ' || '3600: Leaving initalization');
1597 END IF;
1598
1599 reset_curr_function;
1600
1601 EXCEPTION
1602
1603 WHEN OTHERS
1604 THEN
1605 IF P_DEBUG_MODE = 'Y' THEN
1606 log_message('reverse_distribution: ' || '3650: ERROR in initalization');
1607 END IF;
1608 raise;
1609
1610 END initialization;
1611
1612
1613
1614 -------------------------------------------------------------------------------
1615 -- clean_tables
1616 -------------------------------------------------------------------------------
1617
1618 PROCEDURE clean_tables IS
1619 BEGIN
1620
1621 set_curr_function('clean_tables');
1622
1623 IF P_DEBUG_MODE = 'Y' THEN
1624 log_message('reverse_distribution: ' || '3700: Entered clean_tables');
1625 END IF;
1626
1627 in_acct_currency_code.delete;
1628 in_acct_tp_exchange_rate.delete;
1629 in_acct_tp_rate_date.delete;
1630 in_acct_tp_rate_type.delete;
1631 in_amount.delete;
1632 in_bill_markup_percentage.delete;
1633 in_bill_rate.delete;
1634 in_RowId.delete;
1635 in_cc_rejection_code.delete;
1636 in_cc_dist_line_id.delete;
1637 in_cr_code_combination_id.delete;
1638 in_cross_charge_code.delete;
1639 in_denom_tp_currency_code.delete;
1640 in_denom_transfer_price.delete;
1641 in_dist_line_id_reversed.delete;
1642 in_dr_code_combination_id.delete;
1643 in_expenditure_item_id.delete;
1647 in_line_num_reversed.delete;
1644 in_expenditure_item_date.delete;
1645 in_ind_compiled_set_id.delete;
1646 in_line_num.delete;
1648 in_line_type.delete;
1649 in_markup_calc_base_code.delete;
1650 in_org_id.delete;
1651 in_pa_date.delete;
1652 in_project_id.delete;
1653 in_prvdr_org_id.delete;
1654 in_reference_1.delete;
1655 in_reference_2.delete;
1656 in_reference_3.delete;
1657 in_reversed_flag.delete;
1658 in_rule_percentage.delete;
1659 in_schedule_line_percentage.delete;
1660 in_task_id.delete;
1661 in_tp_base_amount.delete;
1662 in_tp_job_id.delete;
1663 in_upd_type.delete;
1664
1665 IF P_DEBUG_MODE = 'Y' THEN
1666 log_message('reverse_distribution: ' || '3750: Finished clean_tables');
1667 END IF;
1668 reset_curr_function;
1669
1670 EXCEPTION
1671
1672 WHEN OTHERS
1673 THEN
1674 IF P_DEBUG_MODE = 'Y' THEN
1675 log_message('reverse_distribution: ' || '3800: Exception in clean_tables');
1676 END IF;
1677 raise;
1678
1679 END clean_tables;
1680
1681 -------------------------------------------------------------------------------
1682 -- update_distribution
1683 -------------------------------------------------------------------------------
1684
1685 PROCEDURE update_distribution IS
1686 BEGIN
1687
1688 set_curr_function('update_distribution');
1689
1690 IF P_DEBUG_MODE = 'Y' THEN
1691 log_message('reverse_distribution: ' || '3850: Entered update_distribution');
1692 END IF;
1693 g_ucnt := g_ucnt + 1;
1694
1695 -- Copy all attributes from the current derived record
1696
1697 -- Store the rowid. It is used to update the record with the best
1698 -- performance
1699
1700 g_upd_rec(g_ucnt).CcdRowid := maxrec.CcdRowid;
1701 g_upd_rec(g_ucnt).expenditure_item_id:= lcur.expenditure_item_id;
1702 g_upd_rec(g_ucnt).line_num := maxrec.line_num;
1703
1704 g_upd_rec(g_ucnt).acct_currency_code := lcur.acct_currency_code;
1705 g_upd_rec(g_ucnt).acct_tp_exchange_rate := lcur.acct_tp_exchange_rate;
1706 g_upd_rec(g_ucnt).acct_tp_rate_date := lcur.acct_tp_rate_date;
1707 g_upd_rec(g_ucnt).acct_tp_rate_type := lcur.acct_tp_rate_type;
1708 g_upd_rec(g_ucnt).amount := lcur.amount;
1709 g_upd_rec(g_ucnt).bill_markup_percentage := lcur.bill_markup_percentage;
1710 g_upd_rec(g_ucnt).bill_rate := lcur.bill_rate;
1711 g_upd_rec(g_ucnt).cc_dist_line_id := maxrec.cc_dist_line_id;
1712 g_upd_rec(g_ucnt).cr_code_combination_id := lcur.cr_code_combination_id;
1713 g_upd_rec(g_ucnt).cross_charge_code := lcur.cross_charge_code;
1714 g_upd_rec(g_ucnt).denom_tp_currency_code := lcur.denom_tp_currency_code;
1715 g_upd_rec(g_ucnt).denom_transfer_price := lcur.denom_transfer_price;
1716 g_upd_rec(g_ucnt).dr_code_combination_id := lcur.dr_code_combination_id;
1717 g_upd_rec(g_ucnt).expenditure_item_date := lcur.expenditure_item_date;
1718 g_upd_rec(g_ucnt).ind_compiled_set_id := lcur.ind_compiled_set_id;
1719 g_upd_rec(g_ucnt).line_type := G_BL_LINE_TYPE;
1720 g_upd_rec(g_ucnt).markup_calc_base_code := lcur.markup_calc_base_code;
1721 g_upd_rec(g_ucnt).pa_date := lcur.pa_date;
1722 g_upd_rec(g_ucnt).rule_percentage := lcur.rule_percentage;
1723 g_upd_rec(g_ucnt).tp_base_amount := lcur.tp_base_amount;
1724 g_upd_rec(g_ucnt).tp_job_id := lcur.tp_job_id;
1725 g_upd_rec(g_ucnt).schedule_line_percentage :=
1726 lcur.schedule_line_percentage;
1727 /* Added for cross proj*/
1728 g_upd_rec(g_ucnt).tp_amt_type_code :=lcur.tp_amt_type_code;
1729 g_upd_rec(g_ucnt).project_tp_rate_type :=lcur.project_tp_rate_type;
1730 g_upd_rec(g_ucnt).project_tp_rate_date :=lcur.project_tp_rate_date;
1731 g_upd_rec(g_ucnt).project_tp_exchange_rate:=lcur.project_tp_exchange_rate;
1732 g_upd_rec(g_ucnt).project_transfer_price:=lcur.project_transfer_price;
1733 g_upd_rec(g_ucnt).projfunc_tp_rate_type :=lcur.projfunc_tp_rate_type;
1734 g_upd_rec(g_ucnt).projfunc_tp_rate_date :=lcur.projfunc_tp_rate_date;
1735 g_upd_rec(g_ucnt).projfunc_tp_exchange_rate := lcur.projfunc_tp_exchange_rate;
1736 g_upd_rec(g_ucnt).projfunc_transfer_price:= lcur.projfunc_transfer_price;
1737
1738 g_upd_rec(g_ucnt).project_tp_currency_code:= lcur.project_tp_currency_code;
1739 g_upd_rec(g_ucnt).projfunc_tp_currency_code:= lcur.projfunc_tp_currency_code;
1740 /* End for cross proj*/
1741
1742 -- The upd_type tells the mass update routine that all TP related
1743 -- fields are to be updated for this row
1744 g_upd_rec(g_ucnt).upd_type := 'U';
1745
1746 IF P_DEBUG_MODE = 'Y' THEN
1747 log_message('reverse_distribution: ' || '3900: Leaving update_distribution');
1748 END IF;
1749
1750 reset_curr_function;
1751
1752
1753 EXCEPTION
1754 WHEN OTHERS
1755 THEN
1756 IF P_DEBUG_MODE = 'Y' THEN
1757 log_message('reverse_distribution: ' || '3950: ERROR in update_distribution');
1758 END IF;
1759 raise;
1760
1761 END update_distribution;
1762
1763 -------------------------------------------------------------------------------
1764 -- reverse_distribution
1765 -------------------------------------------------------------------------------
1766
1767 PROCEDURE reverse_distribution IS
1768 BEGIN
1769
1770 set_curr_function('reverse_distribution');
1771
1772 IF P_DEBUG_MODE = 'Y' THEN
1773 log_message('4000: Entered reverse_distribution');
1774 END IF;
1775
1776 g_icnt := g_icnt + 1;
1777 -- Copy all attributes, reversing amounts
1778
1779 -- Reverse amounts
1780 g_ins_rec(g_icnt).amount := -maxrec.amount;
1781 g_ins_rec(g_icnt).denom_transfer_price := -maxrec.denom_transfer_price;
1782
1783 -- Base amount is negative for an adjusting EI but does not change
1784 -- sign for a regular EI. This is because the amounts (say cost) on
1785 -- the adjusting EI are negative while they remain positive on the
1786 -- regular EI and the distribution needs to reflect the amount on the
1787 -- EI
1788
1789 -- Also, if this is a regular EI, then the distribution is reversing
1790 -- another line in the same EI. The reversed line needs to be marked
1791 -- with reversed_flag = 'Y'
1792
1793 IF lb_regular_ei
1794 THEN
1795 g_ins_rec(g_icnt).tp_base_amount:= maxrec.tp_base_amount;
1796 g_ucnt := g_ucnt + 1;
1797 g_upd_rec(g_ucnt).CcdRowid := maxrec.CcdRowid;
1798
1799 -- The upd_type tells the mass_update routine that only this field
1800 -- needs to be updated. R stands for reversal
1801 g_upd_rec(g_ucnt).upd_type := 'R';
1802
1803 ELSE
1804 g_ins_rec(g_icnt).tp_base_amount:= -maxrec.tp_base_amount;
1805 END IF;
1806
1807 -- Reversing distribution is always created under current expenditure
1808 -- item
1809
1810 g_ins_rec(g_icnt).expenditure_item_id:= lcur.expenditure_item_id;
1811
1812 g_ins_rec(g_icnt).acct_currency_code := maxrec.acct_currency_code;
1813 g_ins_rec(g_icnt).acct_tp_exchange_rate := maxrec.acct_tp_exchange_rate;
1814 g_ins_rec(g_icnt).acct_tp_rate_date := maxrec.acct_tp_rate_date;
1815 g_ins_rec(g_icnt).acct_tp_rate_type := maxrec.acct_tp_rate_type;
1816 g_ins_rec(g_icnt).bill_markup_percentage := maxrec.bill_markup_percentage;
1817 g_ins_rec(g_icnt).bill_rate := maxrec.bill_rate;
1818 g_ins_rec(g_icnt).cr_code_combination_id := maxrec.cr_code_combination_id;
1819 g_ins_rec(g_icnt).cross_charge_code := maxrec.cross_charge_code;
1820 g_ins_rec(g_icnt).denom_tp_currency_code := maxrec.denom_tp_currency_code;
1821
1822 /*Bug# 1995400: Added this as pa_date was populated as null on reversal line*/
1823 g_ins_rec(g_icnt).pa_date := lcur.pa_date; /*Added for Bug# 1995400*/
1824 /*Bug# 2619217: Added this as gl_date was populated as null on reversal line*/
1825 g_ins_rec(g_icnt).gl_date := lcur.gl_date; /*Added for Bug# 2619217*/
1826 g_ins_rec(g_icnt).dr_code_combination_id := maxrec.dr_code_combination_id;
1827 --- g_ins_rec(g_icnt).expenditure_item_id := maxrec.expenditure_item_id; /* Bug 5263823 */
1828 g_ins_rec(g_icnt).ind_compiled_set_id := maxrec.ind_compiled_set_id;
1829
1830 -- Line number is the new line number derived earlier
1831 l_new_line_num := l_new_line_num + 1;
1832 g_ins_rec(g_icnt).line_num := l_new_line_num;
1833
1834 -- Line number reversed needs to be populated only if within the same
1835 -- EI. If this distribution is being created in an adjusting EI, then
1836 -- this field is Null
1837
1838 IF lb_regular_ei
1839 THEN
1840 g_ins_rec(g_icnt).line_num_reversed:= maxrec.line_num_reversed;
1841 ELSE
1842 g_ins_rec(g_icnt).line_num_reversed := NULL;
1843 END IF;
1844
1845 -- For records inserted by this process, the line type will always be
1846 -- Borrowed and Lent
1847 g_ins_rec(g_icnt).line_type := G_BL_LINE_TYPE;
1848
1849
1850 -- The line id reversed is the max line and is always populated, even
1851 -- for reversing EIs
1852
1853 g_ins_rec(g_icnt).dist_line_id_reversed := maxrec.cc_dist_line_id;
1854
1855 g_ins_rec(g_icnt).markup_calc_base_code := maxrec.markup_calc_base_code;
1856 g_ins_rec(g_icnt).project_id := maxrec.project_id;
1857 g_ins_rec(g_icnt).reversed_flag := NULL;
1858 g_ins_rec(g_icnt).rule_percentage := maxrec.rule_percentage;
1859 g_ins_rec(g_icnt).schedule_line_percentage :=
1860 maxrec.schedule_line_percentage;
1861 g_ins_rec(g_icnt).task_id := maxrec.task_id;
1862 g_ins_rec(g_icnt).tp_job_id := maxrec.tp_job_id;
1863
1864 /* Added for cross proj*/
1865 g_ins_rec(g_icnt).tp_amt_type_code :=maxrec.tp_amt_type_code;
1866 g_ins_rec(g_icnt).project_tp_rate_type :=maxrec.project_tp_rate_type;
1867 g_ins_rec(g_icnt).project_tp_rate_date :=maxrec.project_tp_rate_date;
1868 g_ins_rec(g_icnt).project_tp_exchange_rate:=maxrec.project_tp_exchange_rate;
1869 g_ins_rec(g_icnt).project_transfer_price:=(-1)*maxrec.project_transfer_price;
1870 g_ins_rec(g_icnt).projfunc_tp_rate_type :=maxrec.projfunc_tp_rate_type;
1871 g_ins_rec(g_icnt).projfunc_tp_rate_date :=maxrec.projfunc_tp_rate_date;
1872 g_ins_rec(g_icnt).projfunc_tp_exchange_rate := maxrec.projfunc_tp_exchange_rate;
1873 g_ins_rec(g_icnt).projfunc_transfer_price:=(-1)* maxrec.projfunc_transfer_price;
1874
1875 g_ins_rec(g_icnt).project_tp_currency_code :=maxrec.project_tp_currency_code;
1876 g_ins_rec(g_icnt).projfunc_tp_currency_code :=
1877 maxrec.projfunc_tp_currency_code;
1878 /* End for cross proj*/
1879
1880 IF P_DEBUG_MODE = 'Y' THEN
1881 log_message('4050: Leaving reverse_distribution');
1882 END IF;
1883 reset_curr_function;
1884
1885
1886 EXCEPTION
1887 WHEN OTHERS
1888 THEN
1889 IF P_DEBUG_MODE = 'Y' THEN
1890 log_message('4100: ERROR in reverse_distribution');
1891 END IF;
1892 raise;
1893
1894 END reverse_distribution;
1895
1896 -------------------------------------------------------------------------------
1897 -- new_distribution
1898 -------------------------------------------------------------------------------
1899
1900
1901 PROCEDURE new_distribution IS
1902 BEGIN
1903
1904 set_curr_function('new_distribution');
1905 log_message('4150: Entered new_distribution');
1906
1907 /* Added the following IF condition for Bug#2469987 */
1908 IF lcur.amount <> 0 THEN
1909 g_icnt := g_icnt + 1;
1910
1911 -- Copy all attributes from the current derived record
1912
1913 g_ins_rec(g_icnt).expenditure_item_id:= lcur.expenditure_item_id;
1914 -- Line number is the new line number derived earlier
1915 l_new_line_num := l_new_line_num + 1;
1916 g_ins_rec(g_icnt).line_num := l_new_line_num;
1917
1918 g_ins_rec(g_icnt).acct_currency_code := lcur.acct_currency_code;
1919 g_ins_rec(g_icnt).acct_tp_exchange_rate := lcur.acct_tp_exchange_rate;
1920 g_ins_rec(g_icnt).acct_tp_rate_date := lcur.acct_tp_rate_date;
1921 g_ins_rec(g_icnt).acct_tp_rate_type := lcur.acct_tp_rate_type;
1922 g_ins_rec(g_icnt).amount := lcur.amount;
1923 g_ins_rec(g_icnt).bill_markup_percentage := lcur.bill_markup_percentage;
1924 g_ins_rec(g_icnt).bill_rate := lcur.bill_rate;
1925 g_ins_rec(g_icnt).cr_code_combination_id := lcur.cr_code_combination_id;
1926 g_ins_rec(g_icnt).cross_charge_code := lcur.cross_charge_code;
1927 g_ins_rec(g_icnt).denom_tp_currency_code := lcur.denom_tp_currency_code;
1928 g_ins_rec(g_icnt).denom_transfer_price := lcur.denom_transfer_price;
1929 g_ins_rec(g_icnt).dist_line_id_reversed := lcur.dist_line_id_reversed;
1930 g_ins_rec(g_icnt).dr_code_combination_id := lcur.dr_code_combination_id;
1931
1932 -- Expenditure Item date is required for MRC conversions
1933 g_ins_rec(g_icnt).expenditure_item_date := lcur.expenditure_item_date;
1934
1935 g_ins_rec(g_icnt).expenditure_item_id := lcur.expenditure_item_id;
1936 g_ins_rec(g_icnt).ind_compiled_set_id := lcur.ind_compiled_set_id;
1937 g_ins_rec(g_icnt).line_num_reversed := NULL;
1938 g_ins_rec(g_icnt).line_type := G_BL_LINE_TYPE;
1939 g_ins_rec(g_icnt).markup_calc_base_code := lcur.markup_calc_base_code;
1940 g_ins_rec(g_icnt).pa_date := lcur.pa_date;
1941 g_ins_rec(g_icnt).gl_date := lcur.gl_date; /* EPP */
1942 g_ins_rec(g_icnt).pa_period_name := lcur.pa_period_name; /* EPP */
1943 g_ins_rec(g_icnt).gl_period_name := lcur.gl_period_name; /* EPP */
1944 g_ins_rec(g_icnt).project_id := lcur.project_id;
1945 g_ins_rec(g_icnt).reversed_flag := NULL;
1946 g_ins_rec(g_icnt).rule_percentage := lcur.rule_percentage;
1947 g_ins_rec(g_icnt).tp_base_amount := lcur.tp_base_amount;
1948 g_ins_rec(g_icnt).tp_job_id := lcur.tp_job_id;
1949 g_ins_rec(g_icnt).schedule_line_percentage :=
1950 lcur.schedule_line_percentage;
1951 g_ins_rec(g_icnt).task_id := lcur.task_id;
1952
1953 /* Added for cross proj*/
1954 g_ins_rec(g_icnt).tp_amt_type_code :=lcur.tp_amt_type_code;
1955 g_ins_rec(g_icnt).project_tp_rate_type :=lcur.project_tp_rate_type;
1956 g_ins_rec(g_icnt).project_tp_rate_date :=lcur.project_tp_rate_date;
1957 g_ins_rec(g_icnt).project_tp_exchange_rate:=lcur.project_tp_exchange_rate;
1958 g_ins_rec(g_icnt).project_transfer_price:=lcur.project_transfer_price;
1959 g_ins_rec(g_icnt).projfunc_tp_rate_type :=lcur.projfunc_tp_rate_type;
1960 g_ins_rec(g_icnt).projfunc_tp_rate_date :=lcur.projfunc_tp_rate_date;
1961 g_ins_rec(g_icnt).projfunc_tp_exchange_rate := lcur.projfunc_tp_exchange_rate;
1962 g_ins_rec(g_icnt).projfunc_transfer_price:= lcur.projfunc_transfer_price;
1963
1964 g_ins_rec(g_icnt).project_tp_currency_code:= lcur.project_tp_currency_code;
1965 g_ins_rec(g_icnt).projfunc_tp_currency_code:= lcur.projfunc_tp_currency_code;
1966 /* End for cross proj*/
1967 END IF; /* Added for Bug#2469987 */
1968
1969 log_message('4200: Leaving new_distribution');
1970 reset_curr_function;
1971
1972 EXCEPTION
1973 WHEN OTHERS
1974 THEN
1975 log_message('4250: ERROR in new_distribution');
1976 raise;
1977
1978 END new_distribution;
1979
1980 -------------------------------------------------------------------------------
1981 -- delete_distribution
1982 -------------------------------------------------------------------------------
1983
1984 PROCEDURE delete_distribution IS
1985 BEGIN
1986 set_curr_function('delete_distribution');
1987 log_message('4300: Entered delete_distribution');
1988
1989 g_dcnt := g_dcnt + 1;
1990 g_del_rec(g_dcnt).CcdRowId := maxrec.CcdRowid;
1991 g_del_rec(g_dcnt).cc_dist_line_id := maxrec.cc_dist_line_id;
1992
1993 log_message('4350: Leaving delete_distribution');
1994 reset_curr_function;
1995
1996 EXCEPTION
1997 WHEN OTHERS
1998 THEN
1999 log_message('4400: ERROR in delete_distribution');
2000 raise;
2001
2002 END delete_distribution;
2003
2004 -------------------------------------------------------------------------------
2005 -- mass_delete
2006 -------------------------------------------------------------------------------
2007
2008 -- Procedure to delete all records marked for deletion enmasse
2009 PROCEDURE mass_delete IS
2010 BEGIN
2011
2012 set_curr_function('mass_delete');
2013 log_message('4450: Entered mass_delete for '|| to_char(g_dcnt));
2014
2015 FOR i in 1..g_dcnt
2016 LOOP
2020
2017 in_cc_dist_line_id(i) := g_del_rec(i).cc_dist_line_id;
2018 in_RowId(i) := g_del_rec(i).CcdRowId;
2019 END LOOP;
2021 IF g_dcnt <= 0
2022 THEN
2023
2024 log_message('4500: NO RECORDS TO DELETE');
2025
2026 ELSE
2027
2028 log_message('4550: Mass deletion of distributions for ' || g_dcnt || ' records');
2029
2030 FORALL i in 1..g_dcnt
2031
2032 DELETE FROM PA_CC_DIST_LINES
2033 WHERE rowid = in_RowId(i);
2034
2035 log_message('4600: -- Rows deleted = ' || to_char(sql%ROWCOUNT));
2036
2037 /*IF g_mrc_enabled
2038 THEN
2039
2040 log_message('4650: Performing mass_delete for MRC');
2041
2042 pa_mc_borrlent.bl_mc_delete
2043 (
2044 p_cc_dist_line_id => in_cc_dist_line_id
2045 ,p_debug_mode => pa_cc_utils.g_debug_mode
2046 );
2047
2048 log_message('4700: Finished delete for MRC');
2049
2050 END IF;*/
2051 END IF;
2052
2053 log_message('4750: cleaning up');
2054 clean_tables;
2055
2056 -- Clean up array
2057 g_del_rec.delete;
2058 g_dcnt := 0;
2059
2060 log_message('4800: Leaving mass_delete');
2061
2062 reset_curr_function;
2063
2064 EXCEPTION
2065 WHEN OTHERS
2066 THEN
2067 log_message('4850: ERROR in mass_delete');
2068 raise;
2069
2070 END mass_delete;
2071
2072 -------------------------------------------------------------------------------
2073 -- mass_insert
2074 -------------------------------------------------------------------------------
2075
2076 PROCEDURE mass_insert IS
2077 i PLS_INTEGER;
2078 rec_count integer; /* bug:8406827 */
2079 BEGIN
2080
2081 set_curr_function('mass_insert');
2082
2083 log_message('4900: Entered mass_insert for '|| g_icnt);
2084
2085 -- Download all values into single table arrays to avoid Oracle errors
2086 -- caused by using rec(i).field
2087
2088
2089 -- g_org_id is set with the current OU.
2090 g_org_id := mo_global.get_current_org_id ;
2091
2092
2093 FOR i IN 1..g_icnt
2094 LOOP
2095
2096 -- Select the next line id into the in_ variable. This is done here
2097 -- and not directly in the INSERT statement because the line_id has to
2098 -- be passed to MRC
2099
2100 SELECT pa_cc_dist_lines_s.nextval
2101 INTO in_cc_dist_line_id(i)
2102 FROM dual;
2103
2104 in_expenditure_item_id(i) := g_ins_rec(i).expenditure_item_id;
2105 in_line_num(i) := g_ins_rec(i).line_num;
2106 in_acct_currency_code(i) := g_ins_rec(i).acct_currency_code;
2107 in_acct_tp_exchange_rate(i) := g_ins_rec(i).acct_tp_exchange_rate;
2108 in_acct_tp_rate_date(i) := g_ins_rec(i).acct_tp_rate_date;
2109 in_acct_tp_rate_type(i) := g_ins_rec(i).acct_tp_rate_type;
2110 in_amount(i) := g_ins_rec(i).amount;
2111 in_bill_markup_percentage(i) := g_ins_rec(i).bill_markup_percentage;
2112 in_bill_rate(i) := g_ins_rec(i).bill_rate;
2113 in_cr_code_combination_id(i) := g_ins_rec(i).cr_code_combination_id;
2114 in_cross_charge_code(i) := g_ins_rec(i).cross_charge_code;
2115 in_denom_tp_currency_code(i) := g_ins_rec(i).denom_tp_currency_code;
2116 in_denom_transfer_price(i) := g_ins_rec(i).denom_transfer_price;
2117 in_dist_line_id_reversed(i) := g_ins_rec(i).dist_line_id_reversed;
2118 in_dr_code_combination_id(i) := g_ins_rec(i).dr_code_combination_id;
2119 in_expenditure_item_date(i) := g_ins_rec(i).expenditure_item_date;
2120 in_ind_compiled_set_id(i) := g_ins_rec(i).ind_compiled_set_id;
2121 in_line_num_reversed(i) := g_ins_rec(i).line_num_reversed;
2122 in_line_type(i) := g_ins_rec(i).line_type;
2123 in_markup_calc_base_code(i) := g_ins_rec(i).markup_calc_base_code;
2124 in_pa_date(i) := g_ins_rec(i).pa_date;
2125 in_gl_date(i) := g_ins_rec(i).gl_date; /* EPP */
2126 in_pa_period_name(i) := g_ins_rec(i).pa_period_name; /* EPP */
2127 in_gl_period_name(i) := g_ins_rec(i).gl_period_name; /* EPP */
2128 in_project_id(i) := g_ins_rec(i).project_id;
2129
2130 -- prvdr_org_id is sent as an array to the MRC procedure because it
2131 -- expects it as such. The reason for this is that MRC upgrade is
2132 -- performed for a reporting set of books across operating units and
2133 -- hence a combination of operating units may be present in a single
2134 -- call
2135
2136 in_prvdr_org_id(i) := pa_cc_utils.g_prvdr_org_id;
2137
2138 -- The following reference_1, reference_2 and reference_3 columns are
2139 -- only populated by the InterCompany invoicing and the MRC upgrade
2140 -- processes. Reference_2 (type of provider reclass base) and
2141 -- reference_3 (cdl_line_num) are used in MRC upgrade
2142
2143 in_reference_1(i) := g_ins_rec(i).reference_1;
2144 in_reference_2(i) := g_ins_rec(i).reference_2;
2145 in_reference_3(i) := g_ins_rec(i).reference_3;
2146
2147 in_reversed_flag(i) := g_ins_rec(i).reversed_flag;
2148 in_rule_percentage(i) := g_ins_rec(i).rule_percentage;
2149 in_tp_base_amount(i) := g_ins_rec(i).tp_base_amount;
2150 in_tp_job_id(i) := g_ins_rec(i).tp_job_id;
2151 in_schedule_line_percentage(i) := g_ins_rec(i).schedule_line_percentage;
2152 in_task_id(i) := g_ins_rec(i).task_id;
2153
2154 /* Added for cross proj*/
2155 in_tp_amt_type_code(i) := g_ins_rec(i).tp_amt_type_code;
2156 in_project_tp_rate_type(i) := g_ins_rec(i).project_tp_rate_type;
2157 in_project_tp_rate_date(i) := g_ins_rec(i).project_tp_rate_date;
2161 in_projfunc_tp_rate_date(i) := g_ins_rec(i).projfunc_tp_rate_date;
2158 in_project_tp_exchange_rate(i) := g_ins_rec(i).project_tp_exchange_rate;
2159 in_project_transfer_price(i) := g_ins_rec(i).project_transfer_price;
2160 in_projfunc_tp_rate_type(i) := g_ins_rec(i).projfunc_tp_rate_type;
2162 in_projfunc_tp_exchange_rate(i):= g_ins_rec(i).projfunc_tp_exchange_rate;
2163 in_projfunc_transfer_price(i) := g_ins_rec(i).projfunc_transfer_price;
2164
2165 in_project_tp_currency_code(i) := g_ins_rec(i).project_tp_currency_code;
2166 in_projfunc_tp_currency_code(i) := g_ins_rec(i).projfunc_tp_currency_code;
2167 /* End for cross proj*/
2168
2169 END LOOP;
2170
2171 log_message('4950: Set all values about to perform insert');
2172
2173 IF g_icnt <= 0
2174 THEN
2175
2176 log_message('5000: NO RECORDS TO INSERT');
2177
2178 ELSE
2179
2180 log_message('5050: Performing insert for ' || to_char(g_icnt));
2181
2182 /* Bug 8406827 BEGIN */
2183 FOR i in 1..g_icnt LOOP
2184
2185 select count(*) into rec_count
2186 from pa_cc_dist_lines
2187 where
2188 expenditure_item_id=in_expenditure_item_id(i)
2189 AND line_num=in_line_num(i) ;
2190
2191
2192 log_message('For exp_item: ' ||in_expenditure_item_id(i)||' line_num:'||in_line_num(i)||' cc_dist_line count: '||rec_count );
2193
2194 if (rec_count=0)then
2195 log_message('Inserting into pa_cc_dist_lines');
2196 INSERT
2197 INTO pa_cc_dist_lines
2198 (
2199 org_id ,
2200 cc_dist_line_id,
2201 expenditure_item_id,
2202 line_num,
2203 line_type,
2204 cross_charge_code,
2205 acct_currency_code,
2206 amount,
2207 project_id,
2208 task_id,
2209 request_id,
2210 last_update_date,
2211 last_updated_by,
2212 creation_date,
2213 created_by,
2214 last_update_login,
2215 line_num_reversed,
2216 dist_line_id_reversed,
2217 reversed_flag,
2218 denom_tp_currency_code,
2219 denom_transfer_price,
2220 acct_tp_rate_type,
2221 acct_tp_rate_date,
2222 acct_tp_exchange_rate,
2223 dr_code_combination_id,
2224 cr_code_combination_id,
2225 pa_date,
2226 gl_date,
2227 pa_period_name, /* EPP */
2228 gl_period_name, /* EPP */
2229 gl_batch_name,
2230 transfer_status_code,
2231 transferred_date,
2232 transfer_rejection_code,
2233 markup_calc_base_code,
2234 ind_compiled_set_id,
2235 bill_rate,
2236 tp_base_amount,
2237 tp_job_id,
2238 bill_markup_percentage,
2239 schedule_line_percentage,
2240 rule_percentage,
2241 reference_1,
2242 reference_2,
2243 reference_3,
2244 program_application_id,
2245 program_id,
2246 program_update_date,
2247 /* Added for cross proj*/
2248 tp_amt_type_code,
2249 project_tp_rate_type,
2250 project_tp_rate_date,
2251 project_tp_exchange_rate,
2252 project_transfer_price,
2253 projfunc_tp_rate_type,
2254 projfunc_tp_rate_date,
2255 projfunc_tp_exchange_rate,
2256 projfunc_transfer_price,
2257
2258 project_tp_currency_code,
2259 projfunc_tp_currency_code
2260 /* End for cross proj*/
2261 )
2262 VALUES
2263 (
2264 g_org_id , -- Current OU
2265 in_cc_dist_line_id(i), -- cc_dist_line_id
2266 in_expenditure_item_id(i), -- expenditure_item_id
2267 in_line_num(i), -- line_num
2268 in_line_type(i), -- line_type
2269 in_cross_charge_code(i), -- cross_charge_code
2270 in_acct_currency_code(i), -- acct_currency_code
2271 in_amount(i), -- amount
2272 in_project_id(i), -- project_id
2273 in_task_id(i), -- task_id
2274 pa_cc_utils.g_request_id, -- request_id
2275 sysdate, -- last_update_date
2276 pa_cc_utils.g_user_id, -- last_updated_by
2277 sysdate, -- creation_date
2278 pa_cc_utils.g_user_id, -- created_by
2279 pa_cc_utils.g_login_id, -- last_update_login
2280 in_line_num_reversed(i), -- line_num_reversed
2281 in_dist_line_id_reversed(i), -- dist_line_id_reversed
2282 in_reversed_flag(i), -- reversed_flag
2283 in_denom_tp_currency_code(i), -- denom_tp_currency_code
2284 in_denom_transfer_price(i), -- denom_transfer_price
2285 in_acct_tp_rate_type(i), -- acct_tp_rate_type
2286 in_acct_tp_rate_date(i), -- acct_tp_rate_date
2287 in_acct_tp_exchange_rate(i), -- acct_tp_exchange_rate
2288 in_dr_code_combination_id(i), -- dr_code_combination_id
2289 in_cr_code_combination_id(i), -- cr_code_combination_id
2290 in_pa_date(i), -- pa_date
2291 in_gl_date(i), -- gl_date -- EPP
2292 in_pa_period_name(i), -- pa_period_name -- EPP
2293 in_gl_period_name(i), -- gl_period_name -- EPP
2294 NULL, -- gl_batch_name
2295 'P', -- transfer_status_code
2296 NULL, -- transferred_date
2297 NULL, -- transfer_rejection_code
2298 in_markup_calc_base_code(i), -- markup_calc_base_code
2299 in_ind_compiled_set_id(i), -- ind_compiled_set_id
2300 in_bill_rate(i), -- bill_rate
2301 in_tp_base_amount(i), -- tp_base_amount
2302 in_tp_job_id(i), -- tp_job_id
2303 in_bill_markup_percentage(i), -- bill_markup_percentage
2304 in_schedule_line_percentage(i),-- schedule_line_percentage
2305 in_rule_percentage(i), -- rule_percentage
2306 in_reference_1(i), -- reference_1
2307 in_reference_2(i), -- reference_2
2308 in_reference_3(i), -- reference_3
2309 pa_cc_utils.g_program_application_id, -- program_application_id
2310 pa_cc_utils.g_program_id, -- program_id
2311 sysdate, -- program_update_date
2312 /* Added for cross proj*/
2313 in_tp_amt_type_code(i),
2314 in_project_tp_rate_type(i),
2315 in_project_tp_rate_date(i),
2316 in_project_tp_exchange_rate(i),
2317 in_project_transfer_price(i),
2318 in_projfunc_tp_rate_type(i),
2319 in_projfunc_tp_rate_date(i),
2320 in_projfunc_tp_exchange_rate(i),
2321 in_projfunc_transfer_price(i),
2322
2323 in_project_tp_currency_code(i),
2324 in_projfunc_tp_currency_code(i)
2325 /* End for cross proj*/
2326
2327 );
2328
2329 else
2330 log_message('Pa_cc_dist_lines Insert aborted.');
2331 END IF;
2332 END LOOP;
2333 /*End-Chages for Bug:8406827*/
2334
2335 log_message('5100: -- Rows inserted = ' || to_char(sql%ROWCOUNT));
2336
2337
2338 -- Call MRC procedure to create MRC records if MRC is enabled
2339
2340 /*IF g_mrc_enabled
2341 THEN
2342 log_message('5150: MRC Enabled; calling mass insert for MRC:'||
2343 pa_cc_utils.g_program_application_id);
2344
2345 pa_mc_borrlent.bl_mc_insert
2346 (
2347 p_primary_sob_id => pa_cc_utils.g_primary_sob_id
2348 ,p_prvdr_org_id => in_prvdr_org_id
2349 ,p_rsob_id => pa_cc_utils.g_reporting_sob_id
2350 ,p_rcurrency_code => pa_cc_utils.g_reporting_curr_code
2351 ,p_cc_dist_line_id => in_cc_dist_line_id
2352 ,p_expenditure_item_id => in_expenditure_item_id
2353 ,p_line_num => in_line_num
2354 ,p_line_type => in_line_type
2355 ,p_denom_currency_code => in_denom_tp_currency_code
2356 ,p_acct_tp_rate_type => in_acct_tp_rate_type
2357 ,p_expenditure_item_date => in_expenditure_item_date
2358 ,p_acct_tp_exchange_rate => in_acct_tp_exchange_rate
2359 ,p_denom_transfer_price => in_denom_transfer_price
2360 ,p_dist_line_id_reversed => in_dist_line_id_reversed
2361 ,p_prvdr_cost_reclass_code => in_reference_2
2362 ,p_cdl_line_num => in_reference_3
2363 ,p_login_id => pa_cc_utils.g_login_id
2364 ,p_program_id => pa_cc_utils.g_program_id
2365 ,p_program_application_id => pa_cc_utils.g_program_application_id
2366 ,p_request_id => pa_cc_utils.g_request_id
2367 ,p_debug_mode => pa_cc_utils.g_debug_mode
2368 );
2369 END IF;*/
2370
2371 END IF; -- If g_icnt > 0
2372
2373 clean_tables;
2374
2375 -- Clean up array
2376 g_ins_rec.delete;
2377 g_icnt := 0;
2378
2379 log_message('5200: Leaving mass_insert');
2380 reset_curr_function;
2381
2382 EXCEPTION
2383
2384 WHEN OTHERS
2385 THEN
2386 log_message('5250: ERROR in mass_insert');
2387 raise;
2388
2389 END mass_insert;
2390
2391 -------------------------------------------------------------------------------
2392 -- mass_update
2393 -------------------------------------------------------------------------------
2394
2395 PROCEDURE mass_update IS
2396 i PLS_INTEGER;
2397 BEGIN
2398
2399 set_curr_function('mass_update');
2400 log_message('5300: About to perform mass update for ' || g_ucnt);
2401
2402 FOR i in 1..g_ucnt
2403 LOOP
2404 in_RowId(i) := g_upd_rec(i).CcdRowid;
2405
2406 -- The upd_type variable tells this routine about the fields that need
2407 -- to be updated. For reversal of existing lines, the reversed line
2408 -- needs to be updated with a reversed flag of 'Y' and other fields
2409 -- are untouched (the corresponding table values will not contain
2410 -- anything as they are uninitialized to conserve memory). For the
2411 -- up_type of 'U', all transfer price fields are updated
2412
2413 IF g_upd_rec(i).upd_type = 'U'
2414 THEN
2415 in_acct_currency_code(i) := g_upd_rec(i).acct_currency_code;
2416 in_acct_tp_exchange_rate(i) := g_upd_rec(i).acct_tp_exchange_rate;
2417 in_acct_tp_rate_date(i) := g_upd_rec(i).acct_tp_rate_date;
2418 in_acct_tp_rate_type(i) := g_upd_rec(i).acct_tp_rate_type;
2419 in_amount(i) := g_upd_rec(i).amount;
2420 in_bill_markup_percentage(i) := g_upd_rec(i).bill_markup_percentage;
2421 in_bill_rate(i) := g_upd_rec(i).bill_rate;
2422 in_cc_dist_line_id(i) := g_upd_rec(i).cc_dist_line_id;
2423 in_cr_code_combination_id(i) := g_upd_rec(i).cr_code_combination_id;
2424 in_cross_charge_code(i) := g_upd_rec(i).cross_charge_code;
2425 in_denom_tp_currency_code(i) := g_upd_rec(i).denom_tp_currency_code;
2426 in_denom_transfer_price(i) := g_upd_rec(i).denom_transfer_price;
2427 in_dr_code_combination_id(i) := g_upd_rec(i).dr_code_combination_id;
2428
2429 -- EI date is required for MRC conversions
2430 in_expenditure_item_date(i) := g_upd_rec(i).expenditure_item_date;
2431
2432
2433 in_ind_compiled_set_id(i) := g_upd_rec(i).ind_compiled_set_id;
2434 in_line_type(i) := g_upd_rec(i).line_type;
2435 in_markup_calc_base_code(i) := g_upd_rec(i).markup_calc_base_code;
2436
2437 in_pa_date(i) := g_upd_rec(i).pa_date;
2438
2439 -- prvdr_org_id is sent as an array to the MRC procedure because it
2440 -- expects it as such. The reason for this is that MRC upgrade is
2441 -- performed for a reporting set of books across operating units and
2442 -- hence a combination of operating units may be present in a single
2443 -- call
2444
2445 in_prvdr_org_id(i) := pa_cc_utils.g_prvdr_org_id;
2446
2447 -- The following reference_1, reference_2 and reference_3 columns are
2448 -- only populated by the InterCompany invoicing and the MRC upgrade
2449 -- processes. Reference_2 (type of provider reclass base) and
2450 -- reference_3 (cdl_line_num) are used in MRC upgrade
2451
2452 in_reference_1(i) := g_upd_rec(i).reference_1;
2453 in_reference_2(i) := g_upd_rec(i).reference_2;
2454 in_reference_3(i) := g_upd_rec(i).reference_3;
2455
2456 in_rule_percentage(i) := g_upd_rec(i).rule_percentage;
2457 in_tp_base_amount(i) := g_upd_rec(i).tp_base_amount;
2458 in_tp_job_id(i) := g_upd_rec(i).tp_job_id;
2459 in_schedule_line_percentage(i) := g_upd_rec(i).schedule_line_percentage;
2460 in_upd_type(i) := 'U';
2461 /* Added for cross proj*/
2462 in_tp_amt_type_code(i) :=g_upd_rec(i).tp_amt_type_code;
2463 in_project_tp_rate_type(i) :=g_upd_rec(i).project_tp_rate_type;
2464 in_project_tp_rate_date(i) :=g_upd_rec(i).project_tp_rate_date;
2465 in_project_tp_exchange_rate(i):=g_upd_rec(i).project_tp_exchange_rate;
2466 in_project_transfer_price(i):=g_upd_rec(i).project_transfer_price;
2467 in_projfunc_tp_rate_type(i) :=g_upd_rec(i).projfunc_tp_rate_type;
2468 in_projfunc_tp_rate_date(i) :=g_upd_rec(i).projfunc_tp_rate_date;
2469 in_projfunc_tp_exchange_rate(i) := g_upd_rec(i).projfunc_tp_exchange_rate;
2470 in_projfunc_transfer_price(i):= g_upd_rec(i).projfunc_transfer_price;
2471
2472 in_project_tp_currency_code(i):= g_upd_rec(i).project_tp_currency_code;
2473 in_projfunc_tp_currency_code(i):= g_upd_rec(i).projfunc_tp_currency_code;
2474 /* End for cross proj*/
2475 ELSE
2476 in_upd_type(i) := 'R';
2477 in_acct_currency_code(i) := NULL;
2478 in_acct_tp_exchange_rate(i) := NULL;
2479 in_acct_tp_rate_date(i) := NULL;
2480 in_acct_tp_rate_type(i) := NULL;
2481 in_amount(i) := NULL;
2482 in_bill_markup_percentage(i) := NULL;
2483 in_bill_rate(i) := NULL;
2484 in_cc_dist_line_id(i) := NULL;
2485 in_cr_code_combination_id(i) := NULL;
2486 in_cross_charge_code(i) := NULL;
2487 in_denom_tp_currency_code(i) := NULL;
2488 in_denom_transfer_price(i) := NULL;
2489 in_dr_code_combination_id(i) := NULL;
2490 in_expenditure_item_date(i) := NULL;
2491 in_ind_compiled_set_id(i) := NULL;
2492 in_line_type(i) := NULL;
2493 in_markup_calc_base_code(i) := NULL;
2494 in_pa_date(i) := NULL;
2495 in_prvdr_org_id(i) := NULL;
2496 in_reference_1(i) := NULL;
2497 in_reference_2(i) := NULL;
2498 in_reference_3(i) := NULL;
2499 in_rule_percentage(i) := NULL;
2500 in_tp_base_amount(i) := NULL;
2504 in_tp_amt_type_code(i) :=NULL;
2501 in_tp_job_id(i) := NULL;
2502 in_schedule_line_percentage(i) := NULL;
2503 /* Added for cross proj*/
2505 in_project_tp_rate_type(i) :=NULL;
2506 in_project_tp_rate_date(i) :=NULL;
2507 in_project_tp_exchange_rate(i):=NULL;
2508 in_project_transfer_price(i):=NULL;
2509 in_projfunc_tp_rate_type(i) :=NULL;
2510 in_projfunc_tp_rate_date(i) :=NULL;
2511 in_projfunc_tp_exchange_rate(i) := NULL;
2512 in_projfunc_transfer_price(i):= NULL;
2513
2514 in_project_tp_currency_code(i):= NULL;
2515 in_projfunc_tp_currency_code(i):= NULL;
2516 /* End for cross proj*/
2517 END IF;
2518
2519 END LOOP;
2520
2521 log_message('5350: Applying updates to database');
2522
2523 -- Update all records
2524
2525 IF g_ucnt <= 0
2526 THEN
2527 log_message('5400: NO RECORDS TO UPDATE');
2528 ELSE
2529
2530 log_message('5450: Updating for ' || to_char(g_ucnt));
2531
2532 FORALL i in 1..g_ucnt
2533 UPDATE pa_cc_dist_lines
2534 SET
2535 reversed_flag =
2536 decode(in_upd_type(i), 'U', reversed_flag, 'Y'),
2537 acct_currency_code =
2538 decode(in_upd_type(i), 'U',
2539 in_acct_currency_code(i), acct_currency_code),
2540 acct_tp_exchange_rate =
2541 decode(in_upd_type(i), 'U',
2542 in_acct_tp_exchange_rate(i), acct_tp_exchange_rate),
2543 acct_tp_rate_date =
2544 decode(in_upd_type(i), 'U',
2545 in_acct_tp_rate_date(i), acct_tp_rate_date),
2546 acct_tp_rate_type =
2547 decode(in_upd_type(i), 'U',
2548 in_acct_tp_rate_type(i), acct_tp_rate_type),
2549 amount =
2550 decode(in_upd_type(i), 'U',
2551 in_amount(i), amount),
2552 bill_markup_percentage =
2553 decode(in_upd_type(i), 'U',
2554 in_bill_markup_percentage(i), bill_markup_percentage),
2555 bill_rate =
2556 decode(in_upd_type(i), 'U',
2557 in_bill_rate(i), bill_rate),
2558 cr_code_combination_id =
2559 decode(in_upd_type(i), 'U',
2560 in_cr_code_combination_id(i), cr_code_combination_id),
2561 cross_charge_code =
2562 decode(in_upd_type(i), 'U',
2563 in_cross_charge_code(i), cross_charge_code),
2564 denom_tp_currency_code =
2565 decode(in_upd_type(i), 'U',
2566 in_denom_tp_currency_code(i), denom_tp_currency_code),
2567 denom_transfer_price =
2568 decode(in_upd_type(i), 'U',
2569 in_denom_transfer_price(i), denom_transfer_price),
2570 dr_code_combination_id =
2571 decode(in_upd_type(i), 'U',
2572 in_dr_code_combination_id(i), dr_code_combination_id),
2573 ind_compiled_set_id =
2574 decode(in_upd_type(i), 'U',
2575 in_ind_compiled_set_id(i), ind_compiled_set_id),
2576 markup_calc_base_code =
2577 decode(in_upd_type(i), 'U',
2578 in_markup_calc_base_code(i), markup_calc_base_code),
2579 reference_1 =
2580 decode(in_upd_type(i), 'U',
2581 in_reference_1(i), reference_1),
2582 reference_2 =
2583 decode(in_upd_type(i), 'U',
2584 in_reference_2(i), reference_2),
2585 reference_3 =
2586 decode(in_upd_type(i), 'U',
2587 in_reference_3(i), reference_3),
2588 rule_percentage =
2589 decode(in_upd_type(i), 'U',
2590 in_rule_percentage(i), rule_percentage),
2591 tp_base_amount =
2592 decode(in_upd_type(i), 'U',
2593 in_tp_base_amount(i), tp_base_amount),
2594 tp_job_id =
2595 decode(in_upd_type(i), 'U',
2596 in_tp_job_id(i), tp_job_id),
2597 schedule_line_percentage =
2598 decode(in_upd_type(i), 'U',
2599 in_schedule_line_percentage(i), schedule_line_percentage),
2600 /*Added Cross proj*/
2601 tp_amt_type_code = decode(in_upd_type(i), 'U',
2602 in_tp_amt_type_code(i),tp_amt_type_code),
2603 project_tp_rate_type = decode(in_upd_type(i), 'U',
2604 in_project_tp_rate_type(i),project_tp_rate_type),
2605 project_tp_rate_date = decode(in_upd_type(i), 'U',
2606 in_project_tp_rate_date(i),project_tp_rate_date),
2607 project_tp_exchange_rate= decode(in_upd_type(i), 'U',
2608 in_project_tp_exchange_rate(i),project_tp_exchange_rate),
2609 project_transfer_price = decode(in_upd_type(i), 'U',
2610 in_project_transfer_price(i),project_transfer_price),
2611 projfunc_tp_rate_type = decode(in_upd_type(i), 'U',
2612 in_projfunc_tp_rate_type(i),projfunc_tp_rate_type),
2613 projfunc_tp_rate_date = decode(in_upd_type(i), 'U',
2614 in_projfunc_tp_rate_date(i),projfunc_tp_rate_date),
2615 projfunc_tp_exchange_rate= decode(in_upd_type(i), 'U',
2616 (in_projfunc_tp_exchange_rate(i)),projfunc_tp_exchange_rate),
2617 projfunc_transfer_price = decode(in_upd_type(i), 'U',
2618 in_projfunc_transfer_price(i),projfunc_transfer_price),
2619
2620 project_tp_currency_code = decode(in_upd_type(i), 'U',
2621 in_project_tp_currency_code(i),project_tp_currency_code),
2622 projfunc_tp_currency_code = decode(in_upd_type(i), 'U',
2623 in_projfunc_tp_currency_code(i),projfunc_tp_currency_code),
2624 /*End Cross proj*/
2625 last_updated_by = pa_cc_utils.g_user_id,
2626 last_update_login = pa_cc_utils.g_login_id,
2627 last_update_date = sysdate,
2628 request_id = pa_cc_utils.g_request_id,
2629 program_application_id = pa_cc_utils.g_program_application_id,
2630 program_id = pa_cc_utils.g_program_id,
2631 program_update_date = sysdate
2632 WHERE rowid = in_RowId(i);
2633
2634 log_message('5500: -- Rows updated = ' || to_char(sql%ROWCOUNT));
2635
2636
2637
2638 /*IF g_mrc_enabled
2639 THEN
2640
2641 log_message('5550: Performing MRC for mass_update');
2642 log_message('5600: first line id passed ' || in_cc_dist_line_id(1));
2643
2644 pa_mc_borrlent.bl_mc_update
2645 (
2646 p_primary_sob_id => pa_cc_utils.g_primary_sob_id
2647 ,p_prvdr_org_id => in_prvdr_org_id
2648 ,p_rsob_id => pa_cc_utils.g_reporting_sob_id
2649 ,p_rcurrency_code => pa_cc_utils.g_reporting_curr_code
2650 ,p_cc_dist_line_id => in_cc_dist_line_id
2651 ,p_line_type => in_line_type
2652 ,p_upd_type => in_upd_type
2653 ,p_expenditure_item_date => in_expenditure_item_date
2654 ,p_denom_currency_code => in_denom_tp_currency_code
2655 ,p_acct_tp_rate_type => in_acct_tp_rate_type
2656 ,p_acct_tp_exchange_rate => in_acct_tp_exchange_rate
2657 ,p_denom_transfer_price => in_denom_transfer_price
2658 ,p_prvdr_cost_reclass_code => in_reference_2
2659 ,p_cdl_line_num => in_reference_3
2660 ,p_login_id => pa_cc_utils.g_login_id
2661 ,p_program_id => pa_cc_utils.g_program_id
2662 ,p_program_application_id => pa_cc_utils.g_program_application_id
2663 ,p_request_id => pa_cc_utils.g_request_id
2664 ,p_debug_mode => pa_cc_utils.g_debug_mode
2665 );
2666
2667 log_message('5650: Finished MRC update');
2668
2669 END IF;*/
2670
2671 END IF; -- IF g_ucnt > 0
2672
2673 clean_tables;
2674
2675 -- Clear up mass update records
2676 g_upd_rec.delete;
2677 g_ucnt := 0;
2678
2679 log_message('5700: Leaving mass_update');
2680 reset_curr_function;
2681
2682 EXCEPTION
2683 WHEN OTHERS
2684 THEN
2685 log_message('5750: ERROR in mass_update');
2686 raise;
2687
2688 END mass_update;
2689
2690
2691 -------------------------------------------------------------------------------
2692 -- update_ei
2693 -------------------------------------------------------------------------------
2694 PROCEDURE update_ei(p_upd_type IN VARCHAR2) IS
2695 BEGIN
2696
2697 set_curr_function('update_ei');
2698 IF P_DEBUG_MODE = 'Y' THEN
2699 log_message('5800: Entered update_ei');
2700 END IF;
2701
2702 -- Mark current EI as rejected
2703 IF p_upd_type = 'R'
2704 THEN
2705 IF P_DEBUG_MODE = 'Y' THEN
2706 log_message('update_ei: ' || '5850: Rejected EI');
2707 END IF;
2708 g_eicnt := g_eicnt + 1;
2709 g_ei_rec(g_eicnt).EiRowId := lcur.EIRowId;
2710 g_ei_rec(g_eicnt).upd_type := 'R';
2711 g_ei_rec(g_eicnt).cc_rejection_code := lcur.cc_rejection_code;
2712
2713 -- IF all fields need updating with current values
2714 ELSIF p_upd_type = 'A'
2715 THEN
2716 IF P_DEBUG_MODE = 'Y' THEN
2717 log_message('update_ei: ' || '5900: Update with current values');
2718 END IF;
2719 g_eicnt := g_eicnt + 1;
2720 g_ei_rec(g_eicnt).EiRowId := lcur.EIRowId;
2721 g_ei_rec(g_eicnt).upd_type := 'A';
2722 g_ei_rec(g_eicnt).acct_tp_exchange_rate := lcur.acct_tp_exchange_rate;
2723 g_ei_rec(g_eicnt).acct_tp_rate_date := lcur.acct_tp_rate_date;
2724 g_ei_rec(g_eicnt).acct_tp_rate_type := lcur.acct_tp_rate_type;
2725 g_ei_rec(g_eicnt).amount := lcur.amount;
2726 g_ei_rec(g_eicnt).bill_markup_percentage := lcur.bill_markup_percentage;
2727 g_ei_rec(g_eicnt).bill_rate := lcur.bill_rate;
2728 g_ei_rec(g_eicnt).denom_tp_currency_code := lcur.denom_tp_currency_code;
2729 g_ei_rec(g_eicnt).denom_transfer_price := lcur.denom_transfer_price;
2730 g_ei_rec(g_eicnt).ind_compiled_set_id := lcur.ind_compiled_set_id;
2731 g_ei_rec(g_eicnt).markup_calc_base_code := lcur.markup_calc_base_code;
2732 g_ei_rec(g_eicnt).rule_percentage := lcur.rule_percentage;
2733 g_ei_rec(g_eicnt).tp_base_amount := lcur.tp_base_amount;
2734 g_ei_rec(g_eicnt).tp_job_id := lcur.tp_job_id;
2735 g_ei_rec(g_eicnt).schedule_line_percentage :=
2736 lcur.schedule_line_percentage;
2737 /* Added for cross proj*/
2738 g_ei_rec(g_eicnt).tp_amt_type_code :=lcur.tp_amt_type_code;
2739 g_ei_rec(g_eicnt).project_tp_rate_type :=lcur.project_tp_rate_type;
2740 g_ei_rec(g_eicnt).project_tp_rate_date :=lcur.project_tp_rate_date;
2741 g_ei_rec(g_eicnt).project_tp_exchange_rate:=lcur.project_tp_exchange_rate;
2742 g_ei_rec(g_eicnt).project_transfer_price:=lcur.project_transfer_price;
2743 g_ei_rec(g_eicnt).projfunc_tp_rate_type :=lcur.projfunc_tp_rate_type;
2744 g_ei_rec(g_eicnt).projfunc_tp_rate_date :=lcur.projfunc_tp_rate_date;
2745 g_ei_rec(g_eicnt).projfunc_tp_exchange_rate := lcur.projfunc_tp_exchange_rate;
2746 g_ei_rec(g_eicnt).projfunc_transfer_price:= lcur.projfunc_transfer_price;
2747
2748 g_ei_rec(g_eicnt).project_tp_currency_code:= lcur.project_tp_currency_code;
2749 g_ei_rec(g_eicnt).projfunc_tp_currency_code:= lcur.projfunc_tp_currency_code;
2750 /* End for cross proj*/
2751
2752
2753 -- Update EI with reversed amounts
2754 ELSIF p_upd_type = 'N'
2755 THEN
2756
2757 IF P_DEBUG_MODE = 'Y' THEN
2758 log_message('update_ei: ' || '5950: Update EI with reversed amounts');
2759 END IF;
2760 g_eicnt := g_eicnt + 1;
2761 g_ei_rec(g_eicnt).EiRowId := lcur.EIRowId;
2762 g_ei_rec(g_eicnt).upd_type := 'N';
2763
2764 -- Reverse amounts and copy other attributes
2765 g_ei_rec(g_eicnt).amount := -maxrec.amount;
2766 g_ei_rec(g_eicnt).denom_transfer_price := -maxrec.denom_transfer_price;
2767 g_ei_rec(g_eicnt).tp_base_amount := -maxrec.tp_base_amount;
2768 g_ei_rec(g_eicnt).acct_tp_exchange_rate := maxrec.acct_tp_exchange_rate;
2769 g_ei_rec(g_eicnt).acct_tp_rate_date := maxrec.acct_tp_rate_date;
2770 g_ei_rec(g_eicnt).acct_tp_rate_type := maxrec.acct_tp_rate_type;
2771 g_ei_rec(g_eicnt).bill_markup_percentage := maxrec.bill_markup_percentage;
2772 g_ei_rec(g_eicnt).bill_rate := maxrec.bill_rate;
2773 g_ei_rec(g_eicnt).denom_tp_currency_code := maxrec.denom_tp_currency_code;
2774 g_ei_rec(g_eicnt).ind_compiled_set_id := maxrec.ind_compiled_set_id;
2775 g_ei_rec(g_eicnt).markup_calc_base_code := maxrec.markup_calc_base_code;
2776 g_ei_rec(g_eicnt).rule_percentage := maxrec.rule_percentage;
2777 g_ei_rec(g_eicnt).schedule_line_percentage :=
2778 maxrec.schedule_line_percentage;
2779 g_ei_rec(g_eicnt).tp_job_id := maxrec.tp_job_id;
2780 /* Added for cross proj*/
2781 g_ei_rec(g_eicnt).tp_amt_type_code :=maxrec.tp_amt_type_code;
2782 g_ei_rec(g_eicnt).project_tp_rate_type :=maxrec.project_tp_rate_type;
2783 g_ei_rec(g_eicnt).project_tp_rate_date :=maxrec.project_tp_rate_date;
2784 g_ei_rec(g_eicnt).project_tp_exchange_rate:=maxrec.project_tp_exchange_rate;
2785 g_ei_rec(g_eicnt).project_transfer_price:=(-1)*maxrec.project_transfer_price;
2786 g_ei_rec(g_eicnt).projfunc_tp_rate_type :=maxrec.projfunc_tp_rate_type;
2787 g_ei_rec(g_eicnt).projfunc_tp_rate_date :=maxrec.projfunc_tp_rate_date;
2788 g_ei_rec(g_eicnt).projfunc_tp_exchange_rate := maxrec.projfunc_tp_exchange_rate;
2789 g_ei_rec(g_eicnt).projfunc_transfer_price:=(-1)* maxrec.projfunc_transfer_price;
2790
2791 g_ei_rec(g_eicnt).project_tp_currency_code := maxrec.project_tp_currency_code;
2792 g_ei_rec(g_eicnt).projfunc_tp_currency_code
2793 := maxrec.projfunc_tp_currency_code;
2794 /* End for cross proj*/
2795
2796 -- Null out the attributes on the reversed EI (in this case the
2797 -- corresponding distribution on the original EI is deleted)
2798 -- Also null out attributes of current EI
2799 ELSIF p_upd_type = 'G'
2800 THEN
2801
2802 -- Make sure the attributes on the current EI are marked for nulling out
2803 IF P_DEBUG_MODE = 'Y' THEN
2804 log_message('update_ei: ' || '6000: Null out current EI');
2805 END IF;
2806 g_eicnt := g_eicnt + 1;
2807 g_ei_rec(g_eicnt).EiRowId := lcur.EIRowId;
2808 g_ei_rec(g_eicnt).upd_type := 'G';
2809
2810 -- Also null out the attributes on the reversed EI
2811 -- The mass update for EI works on the rowid of the EI. Since the
2812 -- rowid of the adjusting EI is not known, it is read here and
2813 -- populated into the array. This is not a big performance hit as this
2814 -- is a rare case
2815
2816 IF P_DEBUG_MODE = 'Y' THEN
2817 log_message('update_ei: ' || '6050: Null out reversed EI');
2818 END IF;
2819 g_eicnt := g_eicnt + 1;
2820 g_ei_rec(g_eicnt).upd_type := 'G';
2821
2822 SELECT rowid
2823 INTO g_ei_rec(g_eicnt).EIRowId
2824 FROM pa_expenditure_items_all -- _ALL table used for better performance
2825 WHERE expenditure_item_id = lcur.adjusted_exp_item_id;
2826
2827 IF P_DEBUG_MODE = 'Y' THEN
2828 log_message('update_ei: ' || '6100: Successfully got rowid of reversed EI');
2829 END IF;
2830
2831 -- Otherwise update EI as processed without any changes to existing
2832 -- values
2833 ELSE
2834
2835 IF P_DEBUG_MODE = 'Y' THEN
2836 log_message('update_ei: ' || '6150: No change to current EI attributes');
2837 END IF;
2838 g_eicnt := g_eicnt + 1;
2839 g_ei_rec(g_eicnt).EiRowId := lcur.EIRowId;
2840 g_ei_rec(g_eicnt).upd_type := 'X'; /* bug2438805 changed assignment from 'G' to 'X'*/
2841
2842 END IF;
2843
2844 IF P_DEBUG_MODE = 'Y' THEN
2845 log_message('6200: Leaving update_ei');
2846 END IF;
2847
2848 reset_curr_function;
2849
2850 EXCEPTION
2851 WHEN OTHERS
2852 THEN
2853 IF P_DEBUG_MODE = 'Y' THEN
2854 log_message('6250: ERROR in update_ei');
2855 END IF;
2856 raise;
2857
2858 END update_ei;
2859
2860 -------------------------------------------------------------------------------
2861 -- ei_mass_update
2862 -------------------------------------------------------------------------------
2863 PROCEDURE ei_mass_update IS
2864 i PLS_INTEGER;
2865 BEGIN
2866
2867 set_curr_function('ei_mass_update');
2868 log_message('6300: Entered ei_mass_update');
2869
2870 log_message('6350: Rows to EI mass update: ' ||to_char(g_eicnt));
2871
2872 FOR i in 1..g_eicnt
2873 LOOP
2874
2875 in_RowId(i) := g_ei_rec(i).EIRowid;
2876
2877 -- The g_ei_rec.upd_type variable tells this routine about the fields
2878 -- that need to be updated. For upd_type with 'A' or 'N', all fields
2879 -- need to be updated. For upd_types of 'G', the values should be set
2880 -- to NULL while for upd_types of 'X' or 'R', the values should be
2881 -- untouched. When the EI value is left untouched, the corresponding
2882 -- in_ variables are still to be initialized (to NULL) to avoid
2883 -- runtime errors. The g_ei_rec.upd_type is mapped to two
2884 -- in_rec.upd_types depending on the operation to be performed. The
2885 -- operations are either to update with the values in the in_ array or
2886 -- leave them untouched. For 'X' and 'R' the values are left untouched
2887 -- while for 'A', 'N' and 'G', the values on the EI are updated from
2888 -- the values in the in_ array
2889
2890 IF g_ei_rec(i).upd_type in ('A', 'N')
2891 THEN
2892 in_upd_type(i) := 'U';
2893 in_acct_tp_exchange_rate(i) := g_ei_rec(i).acct_tp_exchange_rate;
2894 in_acct_tp_rate_date(i) := g_ei_rec(i).acct_tp_rate_date;
2895 in_acct_tp_rate_type(i) := g_ei_rec(i).acct_tp_rate_type;
2896 in_amount(i) := g_ei_rec(i).amount;
2897 in_bill_markup_percentage(i) := g_ei_rec(i).bill_markup_percentage;
2898 in_bill_rate(i) := g_ei_rec(i).bill_rate;
2899 in_cc_rejection_code(i) := NULL;
2900 in_denom_tp_currency_code(i) := g_ei_rec(i).denom_tp_currency_code;
2901 in_denom_transfer_price(i) := g_ei_rec(i).denom_transfer_price;
2902 in_ind_compiled_set_id(i) := g_ei_rec(i).ind_compiled_set_id;
2903 in_markup_calc_base_code(i) := g_ei_rec(i).markup_calc_base_code;
2904 in_rule_percentage(i) := g_ei_rec(i).rule_percentage;
2905 in_tp_base_amount(i) := g_ei_rec(i).tp_base_amount;
2906 in_tp_job_id(i) := g_ei_rec(i).tp_job_id;
2907 in_schedule_line_percentage(i) := g_ei_rec(i).schedule_line_percentage;
2908 /* Added for cross proj*/
2909 in_tp_amt_type_code(i) := g_ei_rec(i).tp_amt_type_code;
2910 in_project_tp_rate_type(i) := g_ei_rec(i).project_tp_rate_type;
2911 in_project_tp_rate_date(i) := g_ei_rec(i).project_tp_rate_date;
2912 in_project_tp_exchange_rate(i) := g_ei_rec(i).project_tp_exchange_rate;
2913 in_project_transfer_price(i) := g_ei_rec(i).project_transfer_price;
2914 in_projfunc_tp_rate_type(i) := g_ei_rec(i).projfunc_tp_rate_type;
2915 in_projfunc_tp_rate_date(i) := g_ei_rec(i).projfunc_tp_rate_date;
2916 in_projfunc_tp_exchange_rate(i):= g_ei_rec(i).projfunc_tp_exchange_rate;
2917 in_projfunc_transfer_price(i) := g_ei_rec(i).projfunc_transfer_price;
2918 in_project_tp_currency_code(i) := g_ei_rec(i).project_tp_currency_code;
2919 in_projfunc_tp_currency_code(i) := g_ei_rec(i).projfunc_tp_currency_code;
2920
2921 /* End for cross proj*/
2922 ELSE
2923 IF g_ei_rec(i).upd_type = 'G'
2924 THEN
2925 in_upd_type(i) := 'U'; -- Update EI but use null values
2926 ELSE
2927 in_upd_type(i) := 'X'; -- Do not update attributes on EI
2928 END IF;
2929
2930 -- Nullify attributes, either for the purpose of initializing them so
2931 -- that the UPDATE statement does not bomb when they are not used or
2932 -- for the purose of actually updating the EI with null values
2933
2934 in_acct_tp_exchange_rate(i) := NULL;
2935 in_acct_tp_rate_date(i) := NULL;
2936 in_acct_tp_rate_type(i) := NULL;
2937 in_amount(i) := NULL;
2938 in_bill_markup_percentage(i) := NULL;
2939 in_bill_rate(i) := NULL;
2940 in_denom_tp_currency_code(i) := NULL;
2941 in_denom_transfer_price(i) := NULL;
2942 in_ind_compiled_set_id(i) := NULL;
2943 in_markup_calc_base_code(i) := NULL;
2944 in_rule_percentage(i) := NULL;
2945 in_tp_base_amount(i) := NULL;
2946 in_tp_job_id(i) := NULL;
2947 in_schedule_line_percentage(i) := NULL;
2948 /* Moved this code here from if condition mentioned below */
2949 in_tp_amt_type_code(i) :=NULL;
2950 in_project_tp_rate_type(i) :=NULL;
2951 in_project_tp_rate_date(i) :=NULL;
2952 in_project_tp_exchange_rate(i):=NULL;
2953 in_project_transfer_price(i):=NULL;
2954 in_projfunc_tp_rate_type(i) :=NULL;
2955 in_projfunc_tp_rate_date(i) :=NULL;
2956 in_projfunc_tp_exchange_rate(i) := NULL;
2957 in_projfunc_transfer_price(i):= NULL;
2958
2959 in_project_tp_currency_code(i):= NULL;
2960 in_projfunc_tp_currency_code(i):= NULL;
2961 /* End of code addition for bug 2438805 */
2962 END IF;
2963
2964 -- Set the rejecton code for rejected transactions
2965 IF g_ei_rec(i).upd_type = 'R'
2966 THEN
2967 in_cc_rejection_code(i) := g_ei_rec(i).cc_rejection_code;
2968 /* Added for cross proj and added for bug 2165410 */
2969 /*** Commented this code and moved it to else part of earlier if condition
2970 ***in_tp_amt_type_code(i) :=NULL;
2971 ***in_project_tp_rate_type(i) :=NULL;
2972 ***in_project_tp_rate_date(i) :=NULL;
2973 ***in_project_tp_exchange_rate(i):=NULL;
2974 ***in_project_transfer_price(i):=NULL;
2975 ***in_projfunc_tp_rate_type(i) :=NULL;
2976 ***in_projfunc_tp_rate_date(i) :=NULL;
2977 ***in_projfunc_tp_exchange_rate(i) := NULL;
2978 ***in_projfunc_transfer_price(i):= NULL;
2979
2980 ***in_project_tp_currency_code(i):= NULL;
2981 ***in_projfunc_tp_currency_code(i):= NULL;
2982 *** end of comment Bug 2438805 */
2983 /* End for cross proj and end for bug 2165410*/
2984 ELSE
2985 in_cc_rejection_code(i) := NULL;
2986 END IF;
2987
2988 END LOOP;
2989
2990 -- Update all records
2991 -- Set cc_bl_distributed_code = 'N' if there is a rejection.
2992 -- Otherwise, set it to 'Y' if the cross_charge_code = 'B', 'X'
2993 -- otherwise.
2994
2995 log_message('6400: About to perform mass_update' );
2996
2997 IF g_eicnt > 0
2998 THEN
2999 FORALL i in 1..g_eicnt
3000
3001 UPDATE pa_expenditure_items_all --_All table for better performance
3002 SET
3003 cc_rejection_code = in_cc_rejection_code(i),
3004 cc_bl_distributed_code = decode(in_cc_rejection_code(i), NULL,
3005 decode(cc_cross_charge_code, 'B', 'Y', 'X'), 'N'),
3006 acct_tp_exchange_rate =
3007 decode(in_upd_type(i), 'U',
3008 in_acct_tp_exchange_rate(i), acct_tp_exchange_rate),
3009 acct_tp_rate_date =
3010 decode(in_upd_type(i), 'U',
3011 in_acct_tp_rate_date(i), acct_tp_rate_date),
3012 acct_tp_rate_type =
3013 decode(in_upd_type(i), 'U',
3014 in_acct_tp_rate_type(i), acct_tp_rate_type),
3015 acct_transfer_price =
3016 decode(in_upd_type(i), 'U',
3017 in_amount(i), acct_transfer_price),
3018 tp_bill_markup_percentage =
3019 decode(in_upd_type(i), 'U',
3020 in_bill_markup_percentage(i), tp_bill_markup_percentage),
3021 tp_bill_rate =
3022 decode(in_upd_type(i), 'U',
3023 in_bill_rate(i), tp_bill_rate),
3024 denom_tp_currency_code =
3025 decode(in_upd_type(i), 'U',
3026 in_denom_tp_currency_code(i), denom_tp_currency_code),
3027 denom_transfer_price =
3028 decode(in_upd_type(i), 'U',
3029 in_denom_transfer_price(i), denom_transfer_price),
3030 tp_ind_compiled_set_id =
3031 decode(in_upd_type(i), 'U',
3032 in_ind_compiled_set_id(i), tp_ind_compiled_set_id),
3033 cc_markup_base_code =
3034 decode(in_upd_type(i), 'U',
3035 in_markup_calc_base_code(i), cc_markup_base_code),
3036 tp_rule_percentage =
3037 decode(in_upd_type(i), 'U',
3038 in_rule_percentage(i), tp_rule_percentage),
3039 tp_base_amount =
3040 decode(in_upd_type(i), 'U',
3041 in_tp_base_amount(i), tp_base_amount),
3042 tp_job_id =
3043 decode(in_upd_type(i), 'U',
3044 in_tp_job_id(i), tp_job_id),
3045 tp_schedule_line_percentage =
3046 decode(in_upd_type(i), 'U',
3047 in_schedule_line_percentage(i), tp_schedule_line_percentage),
3048 /*Added Cross proj*/
3049 project_tp_rate_type = decode(in_upd_type(i), 'U',
3050 in_project_tp_rate_type(i),project_tp_rate_type),
3051 project_tp_rate_date = decode(in_upd_type(i), 'U',
3052 in_project_tp_rate_date(i),project_tp_rate_date),
3053 project_tp_exchange_rate= decode(in_upd_type(i), 'U',
3054 in_project_tp_exchange_rate(i),project_tp_exchange_rate),
3055 project_transfer_price = decode(in_upd_type(i), 'U',
3056 in_project_transfer_price(i),project_transfer_price),
3057 projfunc_tp_rate_type = decode(in_upd_type(i), 'U',
3058 in_projfunc_tp_rate_type(i),projfunc_tp_rate_type),
3059 projfunc_tp_rate_date = decode(in_upd_type(i), 'U',
3060 in_projfunc_tp_rate_date(i),projfunc_tp_rate_date),
3061 projfunc_tp_exchange_rate= decode(in_upd_type(i), 'U',
3062 (in_projfunc_tp_exchange_rate(i)),projfunc_tp_exchange_rate),
3063 projfunc_transfer_price = decode(in_upd_type(i), 'U',
3064 in_projfunc_transfer_price(i),projfunc_transfer_price),
3065 /*End Cross proj*/
3066 last_updated_by = pa_cc_utils.g_user_id,
3067 last_update_login = pa_cc_utils.g_login_id,
3068 last_update_date = sysdate,
3069 request_id = pa_cc_utils.g_request_id,
3070 program_application_id = pa_cc_utils.g_program_application_id,
3071 program_id = pa_cc_utils.g_program_id,
3072 program_update_date = sysdate
3073 WHERE rowid = in_RowId(i);
3074
3075 log_message('6450: -- Rows updated = ' || to_char(sql%ROWCOUNT));
3076
3077 END IF;
3078
3079 clean_tables;
3080
3081 -- Clean up array
3082 g_ei_rec.delete;
3083
3084 log_message('6500: Leaving ei_mass_update');
3085
3086
3087 reset_curr_function;
3088
3089 EXCEPTION
3090
3091 WHEN OTHERS
3092 THEN
3093 log_message('6550: ERROR in ei_mass_update');
3094 raise;
3095
3096 END ei_mass_update;
3097
3098 -------------------------------------------------------------------------------
3099 -- log_message
3100 -------------------------------------------------------------------------------
3101
3102 PROCEDURE log_message( p_message IN VARCHAR2) IS
3103 BEGIN
3104
3105 IF P_DEBUG_MODE = 'Y' THEN
3106 pa_cc_utils.log_message('log_message: ' || p_message);
3107 END IF;
3108
3109 END log_message;
3110
3111 -------------------------------------------------------------------------------
3112 -- set_curr_function
3113 -------------------------------------------------------------------------------
3114
3115 PROCEDURE set_curr_function(p_function IN VARCHAR2) IS
3116 BEGIN
3117
3118 pa_cc_utils.set_curr_function(p_function);
3119
3120 END;
3121
3122 -------------------------------------------------------------------------------
3123 -- reset_curr_function
3124 -------------------------------------------------------------------------------
3125
3126 PROCEDURE reset_curr_function IS
3127 BEGIN
3128
3129 pa_cc_utils.reset_curr_function;
3130
3131 END;
3132
3133
3134 END pa_cc_bl_process;