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