[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_DETAIL_PROCESS
Source
1 PACKAGE BODY PA_INVOICE_DETAIL_PROCESS as
2 /* $Header: PAICINDB.pls 120.7.12000000.2 2007/02/12 02:06:09 lkan ship $ */
3
4 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6
7 TYPE prv_class_rec IS RECORD ( DENOM_TP_CURRENCY_CODE VARCHAR2(15),
8 DENOM_TRANSFER_PRICE NUMBER,
9 TASK_ID NUMBER,
10 AMOUNT NUMBER,
11 ACCT_CURRENCY_CODE VARCHAR2(15),
12 ACCT_TP_RATE_TYPE VARCHAR2(30),
13 ACCT_TP_RATE_DATE DATE,
14 ACCT_TP_EXCHANGE_RATE NUMBER,
15 DR_CODE_COMBINATION_ID NUMBER,
16 CR_CODE_COMBINATION_ID NUMBER,
17 REFERENCE_2 VARCHAR2(1),
18 REFERENCE_1 VARCHAR2(30),
19 EXPENDITURE_ITEM_ID NUMBER,
20 EXPENDITURE_ITEM_DATE DATE,
21 CDL_LINE_NUM NUMBER,
22 PROJECT_ID NUMBER,
23 CROSS_CHARGE_CODE VARCHAR2(1),
24 SYSTEM_LINKAGE_FUNCTION VARCHAR2(3), /* added for 3857986 */
25 /*Added for cross proj*/
26 TP_AMT_TYPE_CODE VARCHAR2(30),
27 PROJECT_TP_RATE_TYPE VARCHAR2(30),
28 PROJECT_TP_RATE_DATE VARCHAR2(30),
29 PROJECT_TP_EXCHANGE_RATE VARCHAR2(30),
30 PROJECT_TRANSFER_PRICE VARCHAR2(30),
31 PROJFUNC_TP_RATE_TYPE VARCHAR2(30),
32 PROJFUNC_TP_RATE_DATE VARCHAR2(30),
33 PROJFUNC_TP_EXCHANGE_RATE VARCHAR2(30),
34 PROJFUNC_TRANSFER_PRICE VARCHAR2(30),
35
36 PROJECT_TP_CURRENCY_CODE VARCHAR2(15),
37 PROJFUNC_TP_CURRENCY_CODE VARCHAR2(15)
38 /* End for cross proj*/
39 );
40
41
42
43 --Local Package Body Definition
44 P_Insert_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
45 P_Delete_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
46 P_Update_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
47
48 --EI Table for marking status as IC Processed.
49 P_Ei_table PA_PLSQL_DATATYPES.IdTabTyp;
50 I_Ei_count number;
51
52 --storing Line num
53 I_Line_num Number;
54
55 --Table parameter of Transfer Price
56
57 I_DENOM_CURRENCY_CODE PA_PLSQL_DATATYPES.Char15TabTyp;
58 I_DENOM_BILL_AMOUNT PA_PLSQL_DATATYPES.Char30TabTyp;
59 I_ACCT_CURRENCY_CODE PA_PLSQL_DATATYPES.Char15TabTyp;
60 I_BILL_AMOUNT PA_PLSQL_DATATYPES.Char30TabTyp;
61 I_ACCT_RATE_TYPE PA_PLSQL_DATATYPES.Char30TabTyp;
62 I_ACCT_RATE_DATE PA_PLSQL_DATATYPES.Char30TabTyp;
63 I_ACCT_EXCHANGE_RATE PA_PLSQL_DATATYPES.Char30TabTyp;
64 I_REV_CODE_COMBINATION_ID PA_PLSQL_DATATYPES.IdTabTyp;
65 I_OUTPUT_VAT_TAX_ID PA_PLSQL_DATATYPES.IdTabTyp;
66 I_OUTPUT_TAX_CODE PA_PLSQL_DATATYPES.Char30TabTyp;
67 I_OUTPUT_TAX_EXEMPT_FLAG PA_PLSQL_DATATYPES.Char1TabTyp;
68 I_TAX_EXEMPT_REASON_CODE PA_PLSQL_DATATYPES.Char80TabTyp;
69 I_OUTPUT_TAX_EXEMPT_NUMBER PA_PLSQL_DATATYPES.Char30TabTyp;
70 I_PROJACCT_CURRENCY_CODE PA_PLSQL_DATATYPES.Char30TabTyp;
71 I_MARKUP_CALC_BASE_CODE PA_PLSQL_DATATYPES.Char1TabTyp;
72 I_IND_COMPILED_SET_ID PA_PLSQL_DATATYPES.IdTabTyp;
73 I_RULE_PERCENTAGE PA_PLSQL_DATATYPES.Char30TabTyp;
74 I_BILL_RATE PA_PLSQL_DATATYPES.Char30TabTyp;
75 I_BILL_MARKUP_PERCENTAGE PA_PLSQL_DATATYPES.Char30TabTyp;
76 I_BASE_AMOUNT PA_PLSQL_DATATYPES.Char30TabTyp;
77 I_SCHEDULE_LINE_PERCENTAGE PA_PLSQL_DATATYPES.Char30TabTyp;
78 I_MRC_REQD_FLAG PA_PLSQL_DATATYPES.Char1TabTyp;
79
80 -- Provider reclass enabled or not
81 I_PRV_ENABLED PA_PLSQL_DATATYPES.Char1TabTyp;
82 I_EXP_ITEM_USED PA_PLSQL_DATATYPES.Char1TabTyp;
83
84 /* Added for cross proj*/
85 I_TP_AMT_TYPE_CODE PA_PLSQL_DATATYPES.Char30TabTyp;
86 I_PROJECT_TP_RATE_TYPE PA_PLSQL_DATATYPES.char30tabtyp;
87 I_PROJECT_TP_RATE_DATE PA_PLSQL_DATATYPES.char30tabtyp;
88 I_PROJECT_TP_EXCHANGE_RATE PA_PLSQL_DATATYPES.char30tabtyp;
89 I_PROJECT_TRANSFER_PRICE PA_PLSQL_DATATYPES.char30tabtyp;
90 I_PROJFUNC_TP_RATE_TYPE PA_PLSQL_DATATYPES.char30tabtyp;
91 I_PROJFUNC_TP_RATE_DATE PA_PLSQL_DATATYPES.char30tabtyp;
92 I_PROJFUNC_TP_EXCHANGE_RATE PA_PLSQL_DATATYPES.char30tabtyp;
93 I_PROJFUNC_TRANSFER_PRICE PA_PLSQL_DATATYPES.char30tabtyp;
94
95 I_PROJECT_TP_CURRENCY_CODE PA_PLSQL_DATATYPES.char15tabtyp;
96 I_PROJFUNC_TP_CURRENCY_CODE PA_PLSQL_DATATYPES.char15tabtyp;
97 /*End for cross proj*
98
99
100 /* CBGA and project Jobs */
101
102 I_tp_job_id PA_PLSQL_DATATYPES.IdTabTyp;
103 I_prov_proj_bill_job_id PA_PLSQL_DATATYPES.IdTabTyp;
104
105 /* Added for bug 3857986 -- The following variables are used during call to get_period_information. */
106
107 l_return_status NUMBER;
108 l_error_code VARCHAR2(1000);
109 l_error_stage NUMBER;
110
111 l_dummy VARCHAR2(15);
112 l_dummy1 VARCHAR2(15);
113 l_dummy2 VARCHAR2(15);
114 l_dummy3 VARCHAR2(15);
115
116
117 /* End addition for bug 3857986 */
118
119 --Procedure to initialize global counter
120 PROCEDURE init
121 IS
122 BEGIN
123 PA_IC_INV_UTILS.log_message('Initializing Package Global variable......');
124 PA_INVOICE_DETAIL_PKG.G_Ins_count := 0;
125 PA_INVOICE_DETAIL_PKG.G_Del_count := 0;
126 PA_INVOICE_DETAIL_PKG.G_Upd_count := 0;
127 I_EI_count := 0;
128 PA_CC_BL_PROCESS.g_dcnt := 0;
129 PA_CC_BL_PROCESS.g_ucnt := 0;
130 PA_CC_BL_PROCESS.g_icnt := 0;
131
132 /* Initialize the BL Package for Provider Reclass */
133 PA_CC_BL_process.initialization(
134 p_request_id =>PA_IC_INV_UTILS.G_REQUEST_ID
135 ,p_program_application_id =>PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID
136 ,p_program_id => PA_IC_INV_UTILS.G_PROGRAM_ID
137 ,p_user_id => PA_IC_INV_UTILS.G_LAST_UPDATED_BY
138 ,p_login_id => PA_IC_INV_UTILS.G_LAST_UPDATE_LOGIN
139 ,p_prvdr_org_id => PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID
140 ,p_primary_sob_id => PA_MC_INVOICE_DETAIL_PKG.G_SOB);
141
142 END init;
143
144 -- Function to get invoice detail id from sequence
145 FUNCTION get_nextval
146 RETURN NUMBER
147 IS
148 l_nextval NUMBER;
149 BEGIN
150
151 SELECT pa_draft_invoice_details_s.nextval
152 INTO l_nextval
153 FROM SYS.DUAL;
154
155 RETURN(l_nextval);
156
157 EXCEPTION
158 WHEN OTHERS
159 THEN
160 RAISE;
161 END get_nextval;
162
163 -- Populate the delete table for CC Distribution
164 PROCEDURE delete_all_cc_dist (P_Draft_Inv_Id IN Number,
165 P_index IN number)
166 IS
167 cursor get_cc_del_lines
168 is
169 select rowid,cc_dist_line_id
170 from pa_cc_dist_lines
171 where reference_1 = to_char(P_Draft_Inv_Id);
172 BEGIN
173 IF g1_debug_mode = 'Y' THEN
174 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_all_cc_dist ...');
175 END IF;
176 for cc_get_cc_del_lines in get_cc_del_lines
177 loop
178 --Increase the delete counter
179 PA_CC_BL_PROCESS.g_dcnt := PA_CC_BL_PROCESS.g_dcnt + 1;
180 --Load the delete record
181 PA_CC_BL_PROCESS.g_del_rec(PA_CC_BL_PROCESS.g_dcnt).CcdRowId
182 := cc_get_cc_del_lines.rowid;
183 PA_CC_BL_PROCESS.g_del_rec(PA_CC_BL_PROCESS.g_dcnt).cc_dist_line_id
184 := cc_get_cc_del_lines.cc_dist_line_id;
185 end loop;
186 --Set the expenditure Item as Used
187 I_EXP_ITEM_USED(P_index) := 'Y';
188
189 EXCEPTION
190 When Others
191 Then
192 raise;
193 END delete_all_cc_dist;
194
195 -- Populate the delete table for CC Distribution
196 PROCEDURE delete_cc_dist (P_CC_REC IN get_cc_dist%rowtype,
197 P_index IN number)
198 IS
199 BEGIN
200 IF g1_debug_mode = 'Y' THEN
201 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_cc_dist ...');
202 END IF;
203 --Increase the delete counter
204 PA_CC_BL_PROCESS.g_dcnt := PA_CC_BL_PROCESS.g_dcnt + 1;
205 --Load the delete record
206 PA_CC_BL_PROCESS.g_del_rec(PA_CC_BL_PROCESS.g_dcnt).CcdRowId
207 := P_CC_REC.rowid;
208 PA_CC_BL_PROCESS.g_del_rec(PA_CC_BL_PROCESS.g_dcnt).cc_dist_line_id
209 := P_CC_REC.cc_dist_line_id;
210 --Set the expenditure Item as Used
211 I_EXP_ITEM_USED(P_index) := 'Y';
212
213 EXCEPTION
214 When Others
215 Then
216 raise;
217 END delete_cc_dist;
218
219
220 --Procedure to reverse the CC Distribution
221 PROCEDURE reverse_cc_dist ( P_INV_DET_ID IN NUMBER,
222 P_EI_DATE IN DATE,
223 P_Sys_linkage IN VARCHAR2, /* Added for 3857986 */
224 P_CC_REC IN OUT NOCOPY get_cc_dist%rowtype,
225 P_index IN number)
226 IS
227 l_pa_date date; /* Added for Bug2276729 */
228 BEGIN
229 IF g1_debug_mode = 'Y' THEN
230 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.reverse_cc_dist ...');
231 END IF;
232 If ((P_CC_REC.TRANSFER_STATUS_CODE = 'P')
233 and (P_CC_REC.LINE_NUM_REVERSED IS NULL ))
234 Then
235 delete_cc_dist (P_CC_REC,P_index);
236 P_CC_REC.line_num := P_CC_REC.line_num - 1;
237 Elsif (P_CC_REC.LINE_NUM_REVERSED IS NULL )
238 Then
239 /* This part will reverse the existing CC Dist */
240 PA_CC_BL_PROCESS.g_ucnt := PA_CC_BL_PROCESS.g_ucnt + 1;
241 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).upd_type := 'R';
242 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).CcdRowId :=
243 P_CC_REC.rowid;
244 /* This part will create reversing CC Distribution */
245 PA_CC_BL_PROCESS.g_icnt := PA_CC_BL_PROCESS.g_icnt + 1;
246 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).expenditure_item_id
247 := P_CC_REC.expenditure_item_id;
248 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_currency_code
249 := P_CC_REC.acct_currency_code;
250 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_exchange_rate
251 := P_CC_REC.acct_tp_exchange_rate;
252 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_rate_date
253 := P_CC_REC.acct_tp_rate_date;
254 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_rate_type
255 := P_CC_REC.acct_tp_rate_type;
256 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).amount
257 := (-1)* P_CC_REC.amount;
258 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).bill_markup_percentage
259 := P_CC_REC.bill_markup_percentage;
260 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).bill_rate
261 := P_CC_REC.bill_rate;
262 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cc_rejection_code
263 := NULL;
264 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cr_code_combination_id
265 := P_CC_REC.cr_code_combination_id;
266 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cross_charge_code
267 := P_CC_REC.cross_charge_code;
268 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).denom_tp_currency_code
269 := P_CC_REC.denom_tp_currency_code;
270 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).denom_transfer_price
271 := (-1)*P_CC_REC.denom_transfer_price;
272 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).dist_line_id_reversed
273 := P_CC_REC.cc_dist_line_id;
274 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).dr_code_combination_id
275 := P_CC_REC.dr_code_combination_id;
276 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).ind_compiled_set_id
277 := P_CC_REC.ind_compiled_set_id;
278 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_num
279 := P_CC_REC.line_num + 1;
280 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_num_reversed
281 := P_CC_REC.line_num ;
282 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_type
283 := 'PC';
284 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).markup_calc_base_code
285 := P_CC_REC.markup_calc_base_code;
286
287 /* Bug 2276729 - Begin populating the gl_date also. ** Commented for bug 3857986
288 ***CBGA - Added new parameter org_id to get_pa_date***
289 l_pa_date := pa_utils2.get_pa_date(P_EI_DATE,SYSDATE, PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID);
290 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_date
291 := l_pa_date;
292 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
293 := pa_utils2.get_prvdr_gl_date(l_pa_date,PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID, PA_MC_INVOICE_DETAIL_PKG.G_SOB);
294
295 ** Bug 2276729 - End ** Commented for 3857986 - End */
296
297 /* Added for 3857986 */
298 BEGIN
299 pa_utils2.get_period_information(
300 p_expenditure_item_date => P_EI_DATE
301 ,p_expenditure_id => NULL
302 ,p_system_linkage_function => P_Sys_linkage
303 ,p_line_type => 'R'
304 ,p_prvdr_raw_pa_date => NULL
305 ,p_recvr_raw_pa_date => NULL
306 ,p_prvdr_raw_gl_date => NULL
307 ,p_recvr_raw_gl_date => NULL
308 ,p_prvdr_org_id => PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID
309 ,p_recvr_org_id => NULL
310 ,p_prvdr_sob_id => PA_MC_INVOICE_DETAIL_PKG.G_SOB
311 ,p_recvr_sob_id => NULL
312 ,p_calling_module => 'CCDL'
313 ,x_prvdr_pa_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_date
314 ,x_prvdr_pa_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_period_name
315 ,x_prvdr_gl_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
316 ,x_prvdr_gl_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_period_name
317 ,x_recvr_pa_date => l_dummy
318 ,x_recvr_pa_period_name => l_dummy1
319 ,x_recvr_gl_date => l_dummy2
320 ,x_recvr_gl_period_name => l_dummy3
321 ,x_return_status => l_return_status
322 ,x_error_code => l_error_code
323 ,x_error_stage => l_error_stage);
324
325
326 EXCEPTION
327 When Others
328 Then
329 raise;
330 END;
331
332 /* Added for 3857986 End */
333
334 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_id
335 := P_CC_REC.project_id;
336 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reversed_flag
337 := 'N';
338 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).rule_percentage
339 := P_CC_REC.rule_percentage;
340 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).schedule_line_percentage
341 := P_CC_REC.schedule_line_percentage;
342 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).task_id
343 := P_CC_REC.task_id;
344 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_base_amount
345 := P_CC_REC.tp_base_amount;
346 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reference_1
347 := to_char(P_INV_DET_ID);
348 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reference_2
349 := P_CC_REC.reference_2;
350 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).expenditure_item_date
351 := P_EI_DATE;
352
353 /* Added for cross proj*/
354 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_amt_type_code
355 :=P_CC_REC.tp_amt_type_code;
356 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_rate_type
357 :=P_CC_REC.project_tp_rate_type;
358 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_rate_date
359 :=P_CC_REC.project_tp_rate_date;
360 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_exchange_rate
361 :=P_CC_REC.project_tp_exchange_rate;
362 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_transfer_price
363 :=(-1)*P_CC_REC.project_transfer_price;
364 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_rate_type
365 :=P_CC_REC.projfunc_tp_rate_type;
366 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_rate_date
367 :=P_CC_REC.projfunc_tp_rate_date;
368 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_exchange_rate
369 :=P_CC_REC.projfunc_tp_exchange_rate;
370 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_transfer_price
371 :=(-1)*P_CC_REC.projfunc_transfer_price;
372
373 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_currency_code
374 :=P_CC_REC.project_tp_currency_code;
375 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_currency_code
376 :=P_CC_REC.projfunc_tp_currency_code;
377 /* End for cross proj*/
378 /* CBGA and project Jobs */
379
380 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_job_id
381 := P_CC_REC.tp_job_id;
382 /* PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).prov_proj_bill_job_id
383 := P_CC_REC.prov_proj_bill_job_id; */
384
385 P_CC_REC.line_num := P_CC_REC.line_num + 1;
386 --Set the expenditure Item as Used
387 I_EXP_ITEM_USED(P_index) := 'Y';
388
389 End if;
390
391 EXCEPTION
392 WHEN Others
393 Then
394 Raise;
395 END reverse_cc_dist;
396
397 --Build CC Distribution
398 PROCEDURE build_cc_dist(P_Cdl_rec IN prv_class_rec,
399 P_Line_num IN NUMBER,
400 P_Index IN NUMBER)
401 IS
402 l_pa_date date;
403 BEGIN
404
405 /* Initialize the global record table */
406 IF g1_debug_mode = 'Y' THEN
407 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.build_cc_dist ...');
408 PA_IC_INV_UTILS.log_message('build_cc_dist: ' || 'Line Num...'||to_char(P_Line_num));
409 END IF;
410 PA_CC_BL_PROCESS.g_icnt := PA_CC_BL_PROCESS.g_icnt + 1;
411 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).expenditure_item_id
412 := P_Cdl_rec.expenditure_item_id;
413 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_currency_code
414 := P_Cdl_rec.ACCT_CURRENCY_CODE;
415 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_exchange_rate
416 := P_Cdl_rec.ACCT_TP_EXCHANGE_RATE;
417 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_rate_date
418 := P_Cdl_rec.ACCT_TP_RATE_DATE;
419 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).acct_tp_rate_type
420 := P_Cdl_rec.ACCT_TP_RATE_TYPE;
421 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).amount
422 := P_Cdl_rec.AMOUNT;
423 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).bill_markup_percentage
424 := NULL;
425 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).bill_rate
426 := NULL;
427 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cc_rejection_code
428 := NULL;
429 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cr_code_combination_id
430 := P_Cdl_rec.CR_CODE_COMBINATION_ID;
431 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).cross_charge_code
432 := P_Cdl_rec.CROSS_CHARGE_CODE;
433 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).denom_tp_currency_code
434 := P_Cdl_rec.DENOM_TP_CURRENCY_CODE;
435 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).denom_transfer_price
436 := P_Cdl_rec.DENOM_TRANSFER_PRICE;
437 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).dist_line_id_reversed
438 := NULL;
439 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).dr_code_combination_id
440 := P_Cdl_rec.DR_CODE_COMBINATION_ID;
441 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).ind_compiled_set_id
442 := NULL;
443 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_num
444 := nvl(P_line_num,0) + 1;
445 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_num_reversed
446 := NULL;
447 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).line_type := 'PC';
448 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).markup_calc_base_code
449 := NULL;
450
451 /* Bug 2276729 - Begin populating the gl_date also. ** Commented for 3857986
452 ***CBGA - Added new parameter org_id to get_pa_date***
453 l_pa_date:= pa_utils2.get_pa_date(P_Cdl_rec.EXPENDITURE_ITEM_DATE,SYSDATE, PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID);
454
455 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_date
456 := l_pa_date;
457 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
458 := pa_utils2.get_prvdr_gl_date(l_pa_date,PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID, PA_MC_INVOICE_DETAIL_PKG.G_SOB);
459
460 ** Bug 2276729 - End ** Commented for 3857986 End*/
461
462 /* Added for 3857986 */
463 BEGIN
464 pa_utils2.get_period_information(
465 p_expenditure_item_date => P_Cdl_rec.EXPENDITURE_ITEM_DATE
466 ,p_expenditure_id => NULL
467 ,p_system_linkage_function => P_CDL_REC.SYSTEM_LINKAGE_FUNCTION
468 ,p_line_type => 'R'
469 ,p_prvdr_raw_pa_date => NULL
470 ,p_recvr_raw_pa_date => NULL
471 ,p_prvdr_raw_gl_date => NULL
472 ,p_recvr_raw_gl_date => NULL
473 ,p_prvdr_org_id => PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID
474 ,p_recvr_org_id => NULL
475 ,p_prvdr_sob_id => PA_MC_INVOICE_DETAIL_PKG.G_SOB
476 ,p_recvr_sob_id => NULL
477 ,p_calling_module => 'CCDL'
478 ,x_prvdr_pa_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_date
479 ,x_prvdr_pa_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_period_name
480 ,x_prvdr_gl_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
481 ,x_prvdr_gl_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_period_name
482 ,x_recvr_pa_date => l_dummy
483 ,x_recvr_pa_period_name => l_dummy1
484 ,x_recvr_gl_date => l_dummy2
485 ,x_recvr_gl_period_name => l_dummy3
486 ,x_return_status => l_return_status
487 ,x_error_code => l_error_code
488 ,x_error_stage => l_error_stage);
489
490
491 EXCEPTION
492 When Others
493 Then
494 raise;
495 END;
496
497 /* Added for 3857986 End */
498
499 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_id
500 := P_Cdl_rec.project_id;
501 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reversed_flag
502 := NULL;
503 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).rule_percentage
504 := NULL;
505 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).schedule_line_percentage
506 := NULL;
507 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).task_id
508 := P_Cdl_rec.task_id;
509 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_base_amount
510 := NULL;
511 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).expenditure_item_date
512 := P_Cdl_rec.expenditure_item_date;
513 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reference_1
514 := P_Cdl_rec.REFERENCE_1;
515 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reference_2
516 := P_Cdl_rec.REFERENCE_2;
517 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).reference_3
518 := P_Cdl_rec.CDL_LINE_NUM;
519 /* Added for cross proj*/
520 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_amt_type_code
521 :=P_Cdl_rec.tp_amt_type_code;
522 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_rate_type
523 :=P_Cdl_rec.project_tp_rate_type;
524 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_rate_date
525 :=to_date(P_Cdl_rec.project_tp_rate_date,'YYYY/MM/DD');/*FIle.Date.5*/
526 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_exchange_rate
527 :=P_Cdl_rec.project_tp_exchange_rate;
528 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_transfer_price
529 :=P_Cdl_rec.project_transfer_price;
530 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_rate_type
531 :=P_Cdl_rec.projfunc_tp_rate_type;
532 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_rate_date
533 :=to_date(P_Cdl_rec.projfunc_tp_rate_date,'YYYY/MM/DD');/*File.Date.5*/
534 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_exchange_rate
535 :=P_Cdl_rec.projfunc_tp_exchange_rate;
536 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_transfer_price
537 :=P_Cdl_rec.projfunc_transfer_price;
538
539 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).project_tp_currency_code
540 :=P_Cdl_rec.project_tp_currency_code;
541 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).projfunc_tp_currency_code
542 :=P_Cdl_rec.projfunc_tp_currency_code;
543 /* End for cross proj*/
544
545
546 /* CBGA and project Jobs */
547
548 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).tp_job_id
549 := NULL;
550 /* PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).prov_proj_bill_job_id
551 := NULL;
552 */
553
554 --Set the expenditure Item as Used
555 I_EXP_ITEM_USED(P_index) := 'Y';
556 END build_cc_dist;
557
558 --Update CC Distribution
559 PROCEDURE update_cc_dist(P_Cdl_rec IN prv_class_rec,
560 P_CC_rec IN get_cc_dist%rowtype,
561 P_Index IN NUMBER)
562 IS
563 l_pa_date date;
564 BEGIN
565
566 IF g1_debug_mode = 'Y' THEN
567 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_cc_dist ...');
568 END IF;
569 -- Check if any attribute is changed or not
570 If not (( P_Cdl_rec.DR_CODE_COMBINATION_ID = P_CC_rec.DR_CODE_COMBINATION_ID)
571 and ( P_Cdl_rec.CR_CODE_COMBINATION_ID = P_CC_rec.CR_CODE_COMBINATION_ID)
572 and ( P_Cdl_rec.AMOUNT = P_CC_rec.AMOUNT)
573 and ( P_Cdl_rec.DENOM_TRANSFER_PRICE = P_CC_rec.DENOM_TRANSFER_PRICE)
574 and ( nvl(P_Cdl_rec.ACCT_TP_RATE_TYPE,'X')
575 = nvl(P_CC_rec.ACCT_TP_RATE_TYPE,'X') )
576 and ( nvl(P_Cdl_rec.ACCT_TP_RATE_DATE,sysdate)
577 = nvl(P_CC_rec.ACCT_TP_RATE_DATE,sysdate))
578 and ( nvl(P_Cdl_rec.ACCT_TP_EXCHANGE_RATE,0)
579 = nvl(P_CC_rec.ACCT_TP_EXCHANGE_RATE,0) )
580 and ( P_Cdl_rec.DENOM_TP_CURRENCY_CODE = P_CC_rec.DENOM_TP_CURRENCY_CODE )
581 and ( P_Cdl_rec.reference_2 = P_CC_rec.reference_2)
582 and ( P_Cdl_rec.cdl_line_num = P_CC_rec.reference_3))
583 then
584
585 PA_CC_BL_PROCESS.g_ucnt := PA_CC_BL_PROCESS.g_ucnt + 1;
586 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).upd_type := 'U';
587 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).CcdRowid
588 := P_CC_REC.rowid;
589 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).expenditure_item_id
590 := P_Cdl_rec.expenditure_item_id;
591 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).expenditure_item_date
592 := P_Cdl_rec.expenditure_item_date;
593 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).acct_currency_code
594 := P_Cdl_rec.ACCT_CURRENCY_CODE;
595 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).acct_tp_exchange_rate
596 := P_Cdl_rec.ACCT_TP_EXCHANGE_RATE;
597 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).acct_tp_rate_date
598 := P_Cdl_rec.ACCT_TP_RATE_DATE;
599 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).acct_tp_rate_type
600 := P_Cdl_rec.ACCT_TP_RATE_TYPE;
601 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).amount
602 := P_Cdl_rec.AMOUNT;
603 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).cr_code_combination_id
604 := P_Cdl_rec.CR_CODE_COMBINATION_ID;
605 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).cross_charge_code
606 := P_Cdl_rec.CROSS_CHARGE_CODE;
607 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).denom_tp_currency_code
608 := P_Cdl_rec.DENOM_TP_CURRENCY_CODE;
609 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).denom_transfer_price
610 := P_Cdl_rec.DENOM_TRANSFER_PRICE;
611 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).dr_code_combination_id
612 := P_Cdl_rec.DR_CODE_COMBINATION_ID;
613 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).line_type := 'PC' ;/* Added for Bug 5704206 */
614
615 /* Bug 2276729 - Begin populating the gl_date also. ** Commented for 3857986
616 ***CBGA - Added new parameter org_id to get_pa_date***
617 l_pa_date:= pa_utils2.get_pa_date(P_Cdl_rec.EXPENDITURE_ITEM_DATE,SYSDATE, PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID);
618 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).pa_date
619 := l_pa_date;
620 PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
621 := pa_utils2.get_prvdr_gl_date(l_pa_date,PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID, PA_MC_INVOICE_DETAIL_PKG.G_SOB);
622 ** Bug 2276729 - End ** Commented for 3857986 */
623
624 /* Added for 3857986 */
625 BEGIN
626 pa_utils2.get_period_information(
627 p_expenditure_item_date => P_Cdl_rec.EXPENDITURE_ITEM_DATE
628 ,p_expenditure_id => NULL
629 ,p_system_linkage_function => P_CDL_REC.SYSTEM_LINKAGE_FUNCTION
630 ,p_line_type => 'R'
631 ,p_prvdr_raw_pa_date => NULL
632 ,p_recvr_raw_pa_date => NULL
633 ,p_prvdr_raw_gl_date => NULL
634 ,p_recvr_raw_gl_date => NULL
635 ,p_prvdr_org_id => PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID
636 ,p_recvr_org_id => NULL
637 ,p_prvdr_sob_id => PA_MC_INVOICE_DETAIL_PKG.G_SOB
638 ,p_recvr_sob_id => NULL
639 ,p_calling_module => 'CCDL'
640 ,x_prvdr_pa_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_date
641 ,x_prvdr_pa_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).pa_period_name
642 ,x_prvdr_gl_date => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_date
643 ,x_prvdr_gl_period_name => PA_CC_BL_PROCESS.g_ins_rec(PA_CC_BL_PROCESS.g_icnt).gl_period_name
644 ,x_recvr_pa_date => l_dummy
645 ,x_recvr_pa_period_name => l_dummy1
646 ,x_recvr_gl_date => l_dummy2
647 ,x_recvr_gl_period_name => l_dummy3
648 ,x_return_status => l_return_status
649 ,x_error_code => l_error_code
650 ,x_error_stage => l_error_stage);
651
652
653 EXCEPTION
654 When Others
655 Then
656 raise;
657 END;
658
659 /* Added for 3857986 End */
660
661 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).project_id
662 := P_Cdl_rec.project_id;
663 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).task_id
664 := P_Cdl_rec.task_id;
665 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).reference_1
666 := P_CC_rec.reference_1;
667 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).reference_2
668 := P_Cdl_rec.reference_2;
669 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).reference_3
670 := P_Cdl_rec.cdl_line_num;
671
672 /* CBGA and project Jobs */
673
674 /* PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).tp_job_id
675 := P_Cdl_rec.tp_job_id;
676
677 PA_CC_BL_PROCESS.g_upd_rec(PA_CC_BL_PROCESS.g_ucnt).prov_proj_bill_job_id
678 := P_Cdl_rec.prov_proj_bill_job_id;
679
680 */
681
682 --Set the expenditure Item as Used
683 I_EXP_ITEM_USED(P_index) := 'Y';
684 End if;
685
686 EXCEPTION
687 When Others
688 Then
689 Raise;
690 END update_cc_dist;
691
692 --Procedure to add record in insert buffer
693 PROCEDURE insert_row ( p_inv_rec IN OUT NOCOPY pa_draft_invoice_details%rowtype )
694 IS
695 BEGIN
696
697 IF g1_debug_mode = 'Y' THEN
698 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.insert_row ...');
699 END IF;
700 --Increase the Insert counter
701 PA_INVOICE_DETAIL_PKG.G_Ins_count := PA_INVOICE_DETAIL_PKG.G_Ins_count + 1;
702 --Assign Invoice detail Id from sequence
703 p_inv_rec.DRAFT_INVOICE_DETAIL_ID := get_nextval;
704 --Add record to insert buffer
705 P_Insert_tab(PA_INVOICE_DETAIL_PKG.G_Ins_count) := p_inv_rec;
706
707 END insert_row;
708
709 --Procedure to add record in delete buffer
710 PROCEDURE delete_row ( p_inv_rec IN pa_draft_invoice_details%rowtype )
711 IS
712 BEGIN
713
714 IF g1_debug_mode = 'Y' THEN
715 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_row ...');
716 END IF;
717 --Increase the Delete counter
718 PA_INVOICE_DETAIL_PKG.G_Del_count := PA_INVOICE_DETAIL_PKG.G_Del_count + 1;
719 --Add record to delete buffer
720 P_Delete_tab(PA_INVOICE_DETAIL_PKG.G_Del_count) := p_inv_rec;
721
722 END delete_row;
723
724 --Procedure to add record in update buffer
725 PROCEDURE update_row ( p_inv_rec IN pa_draft_invoice_details%rowtype,
726 p_mrc_required IN varchar2 )
727 IS
728 BEGIN
729
730 IF g1_debug_mode = 'Y' THEN
731 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_row ...');
732 END IF;
733 --Increase the Update counter
734 PA_INVOICE_DETAIL_PKG.G_Upd_count := PA_INVOICE_DETAIL_PKG.G_Upd_count + 1;
735 --Add record to update buffer
736 P_Update_tab(PA_INVOICE_DETAIL_PKG.G_Upd_count) := p_inv_rec;
737 I_MRC_REQD_FLAG(PA_INVOICE_DETAIL_PKG.G_Upd_count) := p_mrc_required;
738
739 END update_row;
740
741 -- Procedure to Update EI status as IC Processed.
742 PROCEDURE update_ei
743 IS
744 BEGIN
745 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_ei ...');
746 /* For bug 2877649 Setting the Transfer_price amounts and TP rates to NULL for EIs marked as Never Processed */
747 forall i in 1..I_EI_count
748 Update PA_EXPENDITURE_ITEMS
749 Set CC_IC_PROCESSED_CODE = 'X',
750 DENOM_TP_CURRENCY_CODE = NULL,
751 DENOM_TRANSFER_PRICE = NULL,
752 ACCT_TP_RATE_TYPE = NULL,
753 ACCT_TP_RATE_DATE = NULL,
754 ACCT_TP_EXCHANGE_RATE = NULL,
755 ACCT_TRANSFER_PRICE = NULL,
756 PROJACCT_TRANSFER_PRICE = NULL,
757 CC_MARKUP_BASE_CODE = NULL,
758 TP_BASE_AMOUNT = NULL,
759 TP_IND_COMPILED_SET_ID = NULL,
760 TP_BILL_RATE = NULL,
761 TP_BILL_MARKUP_PERCENTAGE = NULL,
762 TP_SCHEDULE_LINE_PERCENTAGE = NULL,
763 TP_RULE_PERCENTAGE = NULL,
764 TP_JOB_ID = NULL,
765 PROJECT_TRANSFER_PRICE = NULL,
766 PROJFUNC_TRANSFER_PRICE = NULL,
767 PROV_PROJ_BILL_JOB_ID = NULL
768 where EXPENDITURE_ITEM_ID = P_EI_table(i);
769 EXCEPTION
770 When Others
771 Then
772 Raise;
773 END update_ei;
774
775 --Procedure to add record in update buffer
776 /* Added the parameter p_adjusted_ei for bug 2770182 */
777
778 PROCEDURE reverse_row ( p_inv_rec IN OUT NOCOPY pa_draft_invoice_details%rowtype,
779 p_adjusted_ei IN NUMBER default null )
780 IS
781 t_line_num NUMBER:=0;
782 BEGIN
783
784 IF g1_debug_mode = 'Y' THEN
785 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.reverse_row ...');
786 END IF;
787
788 IF (p_inv_rec.LINE_NUM IS NOT NULL)
789 THEN
790 p_inv_rec.REVERSED_FLAG := 'Y';
791 update_row(p_inv_rec,'N');
792 p_inv_rec.LINE_NUM_REVERSED := nvl(p_inv_rec.LINE_NUM,0);
793 END IF;
794
795 -- Reverse The amount field
796 p_inv_rec.DENOM_BILL_AMOUNT := (-1)*p_inv_rec.DENOM_BILL_AMOUNT;
797 p_inv_rec.BILL_AMOUNT := (-1)*p_inv_rec.BILL_AMOUNT;
798 p_inv_rec.LINE_NUM := nvl(p_inv_rec.LINE_NUM,0) + 1;
799 p_inv_rec.DETAIL_ID_REVERSED := p_inv_rec.DRAFT_INVOICE_DETAIL_ID;
800 p_inv_rec.ORIG_DRAFT_INVOICE_NUM:= p_inv_rec.DRAFT_INVOICE_NUM;
801 p_inv_rec.ORIG_DRAFT_INVOICE_LINE_NUM :=
802 p_inv_rec.DRAFT_INVOICE_LINE_NUM;
803 p_inv_rec.DRAFT_INVOICE_NUM := NULL;
804 p_inv_rec.DRAFT_INVOICE_LINE_NUM
805 := NULL;
806 p_inv_rec.INVOICED_FLAG := 'N';
807 /*Cross charge*/
808 p_inv_rec.project_transfer_price := (-1)* p_inv_rec.Project_transfer_price;
809 p_inv_rec.projfunc_transfer_price:=(-1)*p_inv_rec.projfunc_transfer_price;
810 /*Cross charge*/
811
812 /* Commented the below for bug 2770182 */
813
814 /* I_Line_num := I_Line_num + 1;
815
816 Add the record to input buffer
817
818 insert_row(p_inv_rec); */
819
820 /* Added for bug 2770182 */
821
822 -- If the EI is adjusted EI then check the invoice detail has record already or not
823 -- if there is no records, continue insert or mark for update
824
825 IF nvl(p_adjusted_ei,0) <> 0 THEN
826
827 t_line_num :=0;
828
829 SELECT max(line_num) INTO t_line_num
830 FROM pa_draft_invoice_details_all
831 WHERE expenditure_item_id = p_inv_rec.expenditure_item_id
832 AND draft_invoice_num IS NULL
833 AND DRAFT_INVOICE_LINE_NUM IS NULL
834 AND NVL(invoiced_flag,'N') ='N';
835
836 IF SQL%NOTFOUND THEN
837 t_line_num :=0;
838 END IF;
839 PA_IC_INV_UTILS.log_message('Value of t_line_num '||t_line_num);
840 IF NVL(t_line_num,0) <> 0 THEN
841
842 I_line_num := t_line_num;
843 ELSE
844 I_Line_num := I_Line_num + 1;
845
846 END IF;
847
848 ELSE
849
850 I_Line_num := I_Line_num + 1;
851
852 END IF;
853
854 IF NVL(t_line_num,0) = 0 THEN
855
856 -- Add the record to input buffer
857 insert_row(p_inv_rec);
858
859 ELSE
860 update_row(p_inv_rec,'N');
861
862 END IF;
863
864 END reverse_row;
865
866 -- Procedure to build new row from existing row
867 Procedure build_row ( P_Mode IN Varchar2,
868 P_Arr_position IN number,
869 P_Rev_ccid IN number,
870 P_Inv_rec IN OUT NOCOPY pa_draft_invoice_details%rowtype)
871 IS
872 BEGIN
873 IF g1_debug_mode = 'Y' THEN
874 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.build_row1...');
875 END IF;
876 P_Inv_rec.INVOICED_FLAG := 'N';
877 P_Inv_rec.DENOM_CURRENCY_CODE := I_DENOM_CURRENCY_CODE(P_Arr_position);
878 P_Inv_rec.DENOM_BILL_AMOUNT := I_DENOM_BILL_AMOUNT(P_Arr_position);
879 P_Inv_rec.ACCT_CURRENCY_CODE := I_ACCT_CURRENCY_CODE(P_Arr_position);
880 P_Inv_rec.BILL_AMOUNT := I_BILL_AMOUNT(P_Arr_position);
881 P_Inv_rec.ACCT_RATE_TYPE := I_ACCT_RATE_TYPE(P_Arr_position);
882 P_Inv_rec.ACCT_RATE_DATE
883 := to_date(I_ACCT_RATE_DATE(P_Arr_position),'YYYY/MM/DD');
884 /* 2229894 := to_date(I_ACCT_RATE_DATE(P_Arr_position),'DD-MM-RR'); */
885 P_Inv_rec.ACCT_EXCHANGE_RATE := I_ACCT_EXCHANGE_RATE(P_Arr_position);
886 P_Inv_rec.REV_CODE_COMBINATION_ID := P_Rev_ccid;
887 -- Bug 4579791 P_Inv_rec.OUTPUT_VAT_TAX_ID := I_OUTPUT_VAT_TAX_ID(P_Arr_position);
888 P_Inv_rec.OUTPUT_TAX_CLASSIFICATION_CODE := I_OUTPUT_TAX_CODE(P_Arr_Position);
889 P_Inv_rec.OUTPUT_TAX_EXEMPT_FLAG := I_OUTPUT_TAX_EXEMPT_FLAG(P_Arr_position);
890 P_Inv_rec.OUTPUT_TAX_EXEMPT_REASON_CODE
891 := I_TAX_EXEMPT_REASON_CODE(P_Arr_position);
892 P_Inv_rec.OUTPUT_TAX_EXEMPT_NUMBER
893 := I_OUTPUT_TAX_EXEMPT_NUMBER(P_Arr_position);
894 P_Inv_rec.MARKUP_CALC_BASE_CODE := I_MARKUP_CALC_BASE_CODE(P_Arr_position);
895 P_Inv_rec.IND_COMPILED_SET_ID := I_IND_COMPILED_SET_ID(P_Arr_position);
896 P_Inv_rec.RULE_PERCENTAGE := I_RULE_PERCENTAGE(P_Arr_position);
897 P_Inv_rec.BILL_RATE := I_BILL_RATE(P_Arr_position);
898 P_Inv_rec.BILL_MARKUP_PERCENTAGE := I_BILL_MARKUP_PERCENTAGE(P_Arr_position);
899 P_Inv_rec.BASE_AMOUNT := I_BASE_AMOUNT(P_Arr_position);
900 P_Inv_rec.SCHEDULE_LINE_PERCENTAGE
901 := I_SCHEDULE_LINE_PERCENTAGE(P_Arr_position);
902 P_Inv_rec.DRAFT_INVOICE_NUM := NULL;
903 P_Inv_rec.DRAFT_INVOICE_LINE_NUM := NULL;
904
905 /*Added for cross proj */
906 P_Inv_rec.tp_amt_type_code := I_tp_amt_type_code(P_Arr_position);
907 P_Inv_rec.project_tp_rate_type := I_project_tp_rate_type(P_Arr_position);
908 P_Inv_rec.project_tp_rate_date := to_date(I_project_tp_rate_date(P_Arr_position),'YYYY/MM/DD');/*file.Date.5*/
909 P_Inv_rec.project_tp_exchange_rate := I_project_tp_exchange_rate(P_Arr_position);
910 P_Inv_rec.project_transfer_price := I_project_transfer_price(P_Arr_position);
911 P_Inv_rec.projfunc_tp_rate_type := I_projfunc_tp_rate_type(P_Arr_position);
912 P_Inv_rec.projfunc_tp_rate_date := to_Date(I_projfunc_tp_rate_date(P_Arr_position),'YYYY/MM/DD');/*file.Date.5*/
913 P_Inv_rec.projfunc_tp_exchange_rate := I_projfunc_tp_exchange_rate(P_Arr_position);
914 P_Inv_rec.projfunc_transfer_price := I_projfunc_transfer_price(P_Arr_position);
915
916 P_Inv_rec.project_tp_currency_code := I_project_tp_currency_code(P_Arr_position);
917 P_Inv_rec.projfunc_tp_currency_code := I_projfunc_tp_currency_code(P_Arr_position);
918 /* End for cross proj*/
919 /* CBGA and project Jobs */
920
921 P_inv_rec.TP_JOB_ID := I_TP_JOB_ID(P_Arr_position);
922 P_inv_rec.PROV_PROJ_BILL_JOB_ID := I_PROV_PROJ_BILL_JOB_ID(P_Arr_position);
923
924 If P_Mode = 'A'
925 Then
926 /** Add the row to the insert buffer **/
927 I_Line_num := I_Line_num + 1;
928 P_Inv_rec.LINE_NUM := I_Line_num;
929 P_Inv_rec.DETAIL_ID_REVERSED := NULL;
930 P_Inv_rec.LINE_NUM_REVERSED := NULL;
931 insert_row(P_Inv_rec);
932 Elsif P_Mode = 'U'
933 Then
934 update_row(P_Inv_rec,'Y');
935 end if;
936
937 EXCEPTION
938 When Others
939 Then
940 Raise;
941 END build_row;
942
943 -- Build a fresh row for PA_DRAFT_INVOICE_DETAILS
944 Procedure build_row ( P_Expenditure_item_id IN number,
945 P_Project_Id IN number,
946 P_CC_Project_id IN number,
947 P_CC_Tax_task_id IN number,
948 P_Rev_ccid IN number,
949 P_Arr_position IN number,
950 X_Inv_rec OUT NOCOPY pa_draft_invoice_details%rowtype)
951 IS
952 l_inv_rec pa_draft_invoice_details%rowtype;
953 c number :=0;
954 BEGIN
955 IF g1_debug_mode = 'Y' THEN
956 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.build_row2...');
957 END IF;
958 I_Line_num := I_Line_num + 1;
959 /** Initialize all field of the structure **/
960 l_inv_rec.EXPENDITURE_ITEM_ID := P_Expenditure_item_id;
961 l_inv_rec.LINE_NUM := I_Line_num;
962 l_inv_rec.PROJECT_ID := P_Project_Id;
963 l_inv_rec.INVOICED_FLAG := 'N';
964 l_inv_rec.DENOM_CURRENCY_CODE := I_DENOM_CURRENCY_CODE(P_Arr_position);
965 l_inv_rec.DENOM_BILL_AMOUNT
966 := to_number(I_DENOM_BILL_AMOUNT(P_Arr_position));
967 l_inv_rec.ACCT_CURRENCY_CODE := I_ACCT_CURRENCY_CODE(P_Arr_position);
968 l_inv_rec.BILL_AMOUNT := to_number(I_BILL_AMOUNT(P_Arr_position));
969 l_inv_rec.ACCT_RATE_DATE
970 := to_date(I_ACCT_RATE_DATE(P_Arr_position),'YYYY/MM/DD');/*File.Date.5*/
971 /*2229894 := to_date(I_ACCT_RATE_DATE(P_Arr_position),'DD-MM-RR');*/
972 l_inv_rec.ACCT_RATE_TYPE := I_ACCT_RATE_TYPE(P_Arr_position);
973 l_inv_rec.ACCT_EXCHANGE_RATE
974 := to_number(I_ACCT_EXCHANGE_RATE(P_Arr_position));
975 l_inv_rec.CC_PROJECT_ID := P_CC_Project_id;
976 l_inv_rec.CC_TAX_TASK_ID := P_CC_Tax_task_id;
977 l_inv_rec.REV_CODE_COMBINATION_ID := P_Rev_ccid;
978 -- Bug 4579791 l_inv_rec.OUTPUT_VAT_TAX_ID := I_OUTPUT_VAT_TAX_ID(P_Arr_position);
979 l_inv_rec.OUTPUT_TAX_CLASSIFICATION_CODE := I_OUTPUT_TAX_CODE(P_Arr_position);
980 l_inv_rec.OUTPUT_TAX_EXEMPT_FLAG := I_OUTPUT_TAX_EXEMPT_FLAG(P_Arr_position);
981 If ( I_TAX_EXEMPT_REASON_CODE.exists(P_Arr_position))
982 Then
983 l_inv_rec.OUTPUT_TAX_EXEMPT_REASON_CODE
984 := I_TAX_EXEMPT_REASON_CODE(P_Arr_position);
985 End if;
986 c := 13;
987 if ( I_OUTPUT_TAX_EXEMPT_NUMBER.exists(P_Arr_position) )
988 Then
989 l_inv_rec.OUTPUT_TAX_EXEMPT_NUMBER
990 := I_OUTPUT_TAX_EXEMPT_NUMBER(P_Arr_position);
991 End if;
992 c := 16;
993 l_inv_rec.MARKUP_CALC_BASE_CODE := I_MARKUP_CALC_BASE_CODE(P_Arr_position);
994 c := 17;
995 l_inv_rec.IND_COMPILED_SET_ID := I_IND_COMPILED_SET_ID(P_Arr_position);
996 c := 18;
997 l_inv_rec.RULE_PERCENTAGE
998 := to_number(I_RULE_PERCENTAGE(P_Arr_position));
999 c := 19;
1000 l_inv_rec.BILL_RATE := to_number(I_BILL_RATE(P_Arr_position));
1001 l_inv_rec.BILL_MARKUP_PERCENTAGE
1002 := to_number(I_BILL_MARKUP_PERCENTAGE(P_Arr_position));
1003 l_inv_rec.BASE_AMOUNT := to_number(I_BASE_AMOUNT(P_Arr_position));
1004 l_inv_rec.SCHEDULE_LINE_PERCENTAGE
1005 := to_number(I_SCHEDULE_LINE_PERCENTAGE(P_Arr_position));
1006
1007 /* CBGA and project Jobs */
1008
1009 l_inv_rec.tp_job_id := I_tp_job_id(P_Arr_position);
1010 l_inv_rec.prov_proj_bill_job_id := I_prov_proj_bill_job_id(P_Arr_position);
1011
1012
1013 /* Bug #1374381 Assigned the org_id for inert into pa_draft_invoice_details table*/
1014 l_inv_rec.org_id := PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID;
1015
1016 /*Added for cross proj */
1017 l_inv_rec.tp_amt_type_code := I_tp_amt_type_code(P_Arr_position);
1018 l_inv_rec.project_tp_rate_type := I_project_tp_rate_type(P_Arr_position);
1019 l_inv_rec.project_tp_rate_date := to_date(I_project_tp_rate_date(P_Arr_position),'YYYY/MM/DD');/*File.Date.5*/
1020 l_inv_rec.project_tp_exchange_rate := I_project_tp_exchange_rate(P_Arr_position);
1021 l_inv_rec.project_transfer_price := I_project_transfer_price(P_Arr_position);
1022 l_inv_rec.projfunc_tp_rate_type := I_projfunc_tp_rate_type(P_Arr_position);
1023 l_inv_rec.projfunc_tp_rate_date := to_date(I_projfunc_tp_rate_date(P_Arr_position),'YYYY/MM/DD');/*File.Date.5*/
1024 l_inv_rec.projfunc_tp_exchange_rate := I_projfunc_tp_exchange_rate(P_Arr_position);
1025 l_inv_rec.projfunc_transfer_price := I_projfunc_transfer_price(P_Arr_position);
1026
1027 l_inv_rec.project_tp_currency_code := I_project_tp_currency_code(P_Arr_position);
1028 l_inv_rec.projfunc_tp_currency_code := I_projfunc_tp_currency_code(P_Arr_position);
1029 /* End for cross proj*/
1030
1031
1032
1033 /** Add the row to the insert buffer **/
1034 insert_row(l_inv_rec);
1035
1036 X_Inv_rec := l_inv_rec;
1037 EXCEPTION
1038 WHEN Others
1039 THEN
1040 Raise;
1041 END build_row;
1042
1043 --Procedure to add record in Expenditure buffer
1044 PROCEDURE add_ei ( P_Expenditure_item_id IN number )
1045 IS
1046 BEGIN
1047
1048 IF g1_debug_mode = 'Y' THEN
1049 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.add_ei...');
1050 END IF;
1051 --Increase the EI counter
1052 I_Ei_count := I_Ei_count + 1;
1053 --Add record to Expenditure Buffer buffer
1054 P_EI_table(I_Ei_count) := P_Expenditure_item_id;
1055
1056 END add_ei;
1057
1058 -- Function to check whether New invoice details to be created or not.
1059 FUNCTION is_new_details(P_IND_REC IN pa_draft_invoice_details%rowtype,
1060 P_Rev_ccid IN number,
1061 P_Index IN number)
1062 Return Boolean
1063 IS
1064 Begin
1065 IF g1_debug_mode = 'Y' THEN
1066 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.is_new_details...');
1067 END IF;
1068 Return( not
1069 (
1070 P_IND_REC.DENOM_CURRENCY_CODE = I_DENOM_CURRENCY_CODE(P_Index)
1071 AND P_IND_REC.DENOM_BILL_AMOUNT = I_DENOM_BILL_AMOUNT(P_Index)
1072 AND P_IND_REC.ACCT_CURRENCY_CODE = I_ACCT_CURRENCY_CODE(P_Index)
1073 AND P_IND_REC.BILL_AMOUNT = I_BILL_AMOUNT(P_Index)
1074 AND P_IND_REC.ACCT_RATE_TYPE = I_ACCT_RATE_TYPE(P_Index)
1075 AND P_IND_REC.ACCT_RATE_DATE = to_date(I_ACCT_RATE_DATE(P_Index),'YYYY/MM/DD') /*File.Date.5*/
1076 AND P_IND_REC.ACCT_EXCHANGE_RATE = I_ACCT_EXCHANGE_RATE(P_Index)
1077 AND P_IND_REC.REV_CODE_COMBINATION_ID = P_Rev_ccid
1078 -- Bug 4579791 AND P_IND_REC.OUTPUT_VAT_TAX_ID = I_OUTPUT_VAT_TAX_ID(P_Index)
1079 AND P_IND_REC.OUTPUT_TAX_CLASSIFICATION_CODE = I_OUTPUT_TAX_CODE(P_Index)
1080 AND P_IND_REC.OUTPUT_TAX_EXEMPT_FLAG = I_OUTPUT_TAX_EXEMPT_FLAG(P_Index)
1081 AND P_IND_REC.OUTPUT_TAX_EXEMPT_REASON_CODE
1082 = I_TAX_EXEMPT_REASON_CODE(P_Index)
1083 AND P_IND_REC.OUTPUT_TAX_EXEMPT_NUMBER
1084 = I_OUTPUT_TAX_EXEMPT_NUMBER(P_Index)
1085 AND P_IND_REC.MARKUP_CALC_BASE_CODE = I_MARKUP_CALC_BASE_CODE(P_Index)
1086 AND P_IND_REC.IND_COMPILED_SET_ID = I_IND_COMPILED_SET_ID(P_Index)
1087 AND P_IND_REC.RULE_PERCENTAGE = I_RULE_PERCENTAGE(P_Index)
1088 AND P_IND_REC.BILL_RATE = I_BILL_RATE(P_Index)
1089 AND P_IND_REC.BILL_MARKUP_PERCENTAGE = I_BILL_MARKUP_PERCENTAGE(P_Index)
1090 AND P_IND_REC.BASE_AMOUNT = I_BASE_AMOUNT(P_Index)
1091 AND P_IND_REC.SCHEDULE_LINE_PERCENTAGE
1092 = I_SCHEDULE_LINE_PERCENTAGE(P_Index)
1093 AND P_IND_REC.TP_JOB_ID = I_TP_JOB_ID(P_Index)
1094 AND P_IND_REC.PROV_PROJ_BILL_JOB_ID = I_PROV_PROJ_BILL_JOB_ID(P_Index)
1095 /*Cross proj*/
1096 AND P_IND_REC.PROJECT_TRANSFER_PRICE =I_PROJECT_TRANSFER_PRICE(P_Index)
1097 AND P_IND_REC.TP_AMT_TYPE_CODE =I_TP_AMT_TYPE_CODE(P_Index)
1098 AND P_IND_REC.PROJFUNC_TRANSFER_PRICE =I_PROJFUNC_TRANSFER_PRICE(P_Index)
1099
1100 ));
1101
1102 EXCEPTION
1103 WHEN OTHERS
1104 THEN
1105 RAISE;
1106 End is_new_details;
1107
1108 -- Read the Original Invoice details for reversing invoice details.
1109 PROCEDURE read_orig(P_Expenditure_item_id IN Number,
1110 P_Line_num IN Number,
1111 X_IND_REC
1112 OUT NOCOPY pa_draft_invoice_details%rowtype)
1113 IS
1114 BEGIN
1115 IF g1_debug_mode = 'Y' THEN
1116 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.read_orig...');
1117 END IF;
1118 Select *
1119 INTO X_IND_REC
1120 FROM PA_DRAFT_INVOICE_DETAILS
1121 WHERE EXPENDITURE_ITEM_ID = P_Expenditure_item_id
1122 AND LINE_NUM = P_Line_num ;
1123 EXCEPTION
1124 WHEN Others
1125 Then
1126 RAISE;
1127 END read_orig;
1128
1129 -- Upadate the rejection reason code of EI
1130 PROCEDURE update_rejection_reason ( P_Error_code
1131 IN PA_PLSQL_DATATYPES.Char30TabTyp,
1132 P_Exp_Id IN PA_PLSQL_DATATYPES.IdTabTyp,
1133 P_No_of_records IN number )
1134 IS
1135 BEGIN
1136 IF g1_debug_mode = 'Y' THEN
1137 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_rejection_reason...');
1138 END IF;
1139 FORALL I IN 1..P_No_of_records
1140 Update PA_EXPENDITURE_ITEMS
1141 Set CC_REJECTION_CODE = P_Error_code(I)
1142 Where EXPENDITURE_ITEM_ID = P_Exp_Id(I);
1143
1144 EXCEPTION
1145 When Others
1146 Then
1147 Raise;
1148 END update_rejection_reason;
1149
1150 -- Apply all pending DB changes in Bulk
1151 PROCEDURE apply_db_changes (P_Error_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
1152 P_Exp_Id IN PA_PLSQL_DATATYPES.IdTabTyp,
1153 P_no_of_records IN Number )
1154 IS
1155 BEGIN
1156
1157 IF g1_debug_mode = 'Y' THEN
1158 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.apply_db_changes...');
1159 END IF;
1160 /* Apply Pending Delete/Update/Insert in PA_DRAFT_INVOICE Details */
1161 PA_INVOICE_DETAIL_PKG.delete_rows(P_Delete_tab);
1162 PA_INVOICE_DETAIL_PKG.update_rows(P_Update_tab,I_MRC_REQD_FLAG);
1163 PA_INVOICE_DETAIL_PKG.insert_rows(P_Insert_tab);
1164
1165 /* Apply pending changes for PA_EXPENDITURE_ITEMS */
1166 update_ei;
1167 update_rejection_reason( P_Error_code, P_Exp_Id, P_no_of_records );
1168
1169 /* Apply changes for Provider Reclass */
1170 if (PA_CC_BL_PROCESS.g_dcnt > 0 )
1171 then
1172 PA_CC_BL_PROCESS.mass_delete;
1173 end if;
1174 if (PA_CC_BL_PROCESS.g_ucnt > 0 )
1175 then
1176 PA_CC_BL_PROCESS.mass_update;
1177 end if;
1178 if (PA_CC_BL_PROCESS.g_icnt > 0 )
1179 then
1180 PA_CC_BL_PROCESS.mass_insert;
1181 end if;
1182 /* Commit the data */
1183 Commit;
1184
1185 Exception
1186 When Others
1187 Then
1188 Raise;
1189 END apply_db_changes;
1190
1191 /* !!!!This is overloaded procedure for compilation of pro*c files of Patchset J */
1192 /* !!!!Note: This .pls with overload function should not be sent along with the patch for Patchset J customers */
1193 PROCEDURE process_invoice_details
1194 ( P_Project_Id IN number ,
1195 P_Customer_Id IN number,
1196 P_Bill_to_site_use_id IN number,
1197 P_Ship_to_site_use_id IN number ,
1198 P_Set_of_books_id IN number ,
1199 P_Acct_curr_code IN varchar2,
1200 P_Expenditure_category IN PA_PLSQL_DATATYPES.Char30TabTyp,
1201 P_CC_Project_Id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1202 P_CC_Tax_task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1203 P_EI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1204 P_AdjEI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1205 P_Net_zero_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1206 P_TP_sch_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1207 P_revenue_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1208 P_provider_cr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1209 P_provider_dr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1210 P_Task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1211 P_Cross_charge_code IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1212 P_Labor_nl_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1213 P_Revenue_distributed_flag
1214 IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1215 P_Expend_type IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1216 P_EI_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1217 P_Sys_linkage IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1218 P_Denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1219 P_Prj_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1220 P_TP_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1221 P_Denom_raw_cost_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1222 P_Denom_burdened_cost_amt
1223 IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1224 P_Raw_revenue_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1225 P_Quantity IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1226 P_Non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp ,
1227 P_Prvdr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1228 P_Recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1229 P_Recvr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1230 P_Expnd_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1231 P_NL_resource_organization
1232 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1233 P_Incurred_by_person_id
1234 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1235 P_Job_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1236 P_Denom_TP_currency_code
1237 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp ,
1238 P_Denom_transfer_price
1239 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1240 P_Acct_TP_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1241 P_Acct_TP_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1242 P_Acct_TP_exchange_rate
1243 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1244 P_Acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1245 P_CC_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1246 P_TP_ind_compiled_set_id
1247 IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1248 P_TP_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1249 P_TP_base_amt IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1250 P_TP_bill_markup_percentage
1251 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1252 P_TP_schedule_line_percentage
1253 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1254 P_TP_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1255 P_burden_disp_method IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1256 P_ca_prov_code IN Varchar2,
1257 P_nca_prov_code IN Varchar2,
1258 P_Processed_thru_date IN Date ,
1259 P_No_of_records IN NUMBER ,
1260 P_User_Id IN NUMBER ,
1261 P_Request_id IN NUMBER ,
1262 P_Error_Code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1263 P_tp_Job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1264 P_prov_proj_bill_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1265 /*Added for cross proj*/
1266 P_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1267 P_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1268 P_project_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1269 P_project_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1270 P_project_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1271 P_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1272 P_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1273 P_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1274 P_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1275 P_project_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1276 P_projfunc_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
1277 /*End for cross proj*/
1278 )
1279 IS
1280 BEGIN
1281 null;
1282 END;
1283 /* End of overload for Patchset J */
1284
1285 /* !!!This is overloaded procedure for compilation of pro*c files of Patchset H */
1286 /* !!!Note: This .pls with overload function should not be sent along with the patch for Patchset H customers */
1287 PROCEDURE process_invoice_details
1288 ( P_Project_Id IN number ,
1289 P_Customer_Id IN number,
1290 P_Bill_to_site_use_id IN number,
1291 P_Ship_to_site_use_id IN number ,
1292 P_Set_of_books_id IN number ,
1293 P_Acct_curr_code IN varchar2,
1294 P_Expenditure_category IN PA_PLSQL_DATATYPES.Char30TabTyp,
1295 P_CC_Project_Id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1296 P_CC_Tax_task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1297 P_EI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1298 P_AdjEI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1299 P_Net_zero_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1300 P_TP_sch_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1301 P_revenue_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1302 P_provider_cr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1303 P_provider_dr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1304 P_Task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1305 P_Cross_charge_code IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1306 P_Labor_nl_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1307 P_Revenue_distributed_flag
1308 IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1309 P_Expend_type IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1310 P_EI_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1311 P_Sys_linkage IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1312 P_Denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1313 P_Prj_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1314 P_TP_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1315 P_Denom_raw_cost_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1316 P_Denom_burdened_cost_amt
1317 IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1318 P_Raw_revenue_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1319 P_Quantity IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1320 P_Non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp ,
1321 P_Prvdr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1322 P_Recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1323 P_Recvr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1324 P_Expnd_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1325 P_NL_resource_organization
1326 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1327 P_Incurred_by_person_id
1328 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1329 P_Job_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1330 P_Denom_TP_currency_code
1331 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp ,
1332 P_Denom_transfer_price
1333 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1334 P_Acct_TP_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1335 P_Acct_TP_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1336 P_Acct_TP_exchange_rate
1337 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1338 P_Acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1339 P_CC_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1340 P_TP_ind_compiled_set_id
1341 IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1342 P_TP_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1343 P_TP_base_amt IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1344 P_TP_bill_markup_percentage
1345 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1346 P_TP_schedule_line_percentage
1347 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1348 P_TP_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1349 P_burden_disp_method IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1350 P_ca_prov_code IN Varchar2,
1351 P_nca_prov_code IN Varchar2,
1352 P_Processed_thru_date IN Date ,
1353 P_No_of_records IN NUMBER ,
1354 P_User_Id IN NUMBER ,
1355 P_Request_id IN NUMBER ,
1356 P_Error_Code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1357 P_tp_Job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1358 P_prov_proj_bill_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp)
1359 IS
1360 BEGIN
1361 null;
1362 END;
1363 /* End of overload for Patchset H */
1364
1365 -- Main Procedure to process invoice details
1366 PROCEDURE process_invoice_details
1367 ( P_Project_Id IN number ,
1368 P_Customer_Id IN number,
1369 P_Bill_to_site_use_id IN number,
1370 P_Ship_to_site_use_id IN number ,
1371 P_Set_of_books_id IN number ,
1372 P_Acct_curr_code IN varchar2,
1373 P_Expenditure_category IN PA_PLSQL_DATATYPES.Char30TabTyp,
1374 P_CC_Project_Id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1375 P_CC_Tax_task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1376 P_EI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1377 P_AdjEI_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1378 P_Net_zero_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1379 P_TP_sch_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1380 P_revenue_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1381 P_provider_cr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1382 P_provider_dr_ccid IN PA_PLSQL_DATATYPES.IdTabTyp ,
1383 P_Task_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1384 P_Cross_charge_code IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1385 P_Labor_nl_flag IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1386 P_Revenue_distributed_flag
1387 IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1388 P_Expend_type IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1389 P_EI_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1390 P_Sys_linkage IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1391 P_Denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1392 P_Prj_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1393 P_TP_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1394 P_Denom_raw_cost_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1395 P_Denom_burdened_cost_amt
1396 IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1397 P_Raw_revenue_amt IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1398 P_Quantity IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1399 P_Non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp ,
1400 P_Prvdr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1401 P_Recvr_org_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1402 P_Recvr_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1403 P_Expnd_Organization IN PA_PLSQL_DATATYPES.IdTabTyp ,
1404 P_NL_resource_organization
1405 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1406 P_Incurred_by_person_id
1407 IN PA_PLSQL_DATATYPES.IdTabTyp ,
1408 P_Job_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1409 P_Denom_TP_currency_code
1410 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp ,
1411 P_Denom_transfer_price
1412 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1413 P_Acct_TP_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1414 P_Acct_TP_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1415 P_Acct_TP_exchange_rate
1416 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1417 P_Acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1418 P_CC_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1419 P_TP_ind_compiled_set_id
1420 IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1421 P_TP_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1422 P_TP_base_amt IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1423 P_TP_bill_markup_percentage
1424 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1425 P_TP_schedule_line_percentage
1426 IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1427 P_TP_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1428 P_burden_disp_method IN PA_PLSQL_DATATYPES.Char1TabTyp ,
1429 P_ca_prov_code IN Varchar2,
1430 P_nca_prov_code IN Varchar2,
1431 P_Processed_thru_date IN Date ,
1432 P_No_of_records IN NUMBER ,
1433 P_User_Id IN NUMBER ,
1434 P_Request_id IN NUMBER ,
1435 P_Error_Code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1436 P_tp_Job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1437 P_prov_proj_bill_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1438 /*Added for cross proj*/
1439 P_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1440 P_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp ,
1441 P_project_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1442 P_project_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1443 P_project_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1444 P_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1445 P_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1446 P_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1447 P_assignment_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1448 P_project_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1449 P_projfunc_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp ,
1450
1451 /*End for cross proj*/
1452 /* Bill rate Discount*/
1453 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
1454 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
1455 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.Char30TabTyp,
1456 p_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
1457 /* p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.NumTabTyp, Commented for bug 3252190 */
1458 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Changed the data type from Num to char for bug3252190 */
1459 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
1460 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
1461 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
1462 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp ,
1463 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
1464 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
1465 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
1466 p_raw_cost_rate IN PA_PLSQL_DATATYPES.Char30TabTyp,
1467 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
1468 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
1469 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
1470 p_exp_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
1471 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
1472
1473 p_burden_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
1474 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1475 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1476 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
1477 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
1478 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
1479 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
1480 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
1481 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
1482 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
1483 /* The following two parameters are added for Doosan rate api enhancement */
1484 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
1485 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
1486 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
1487 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
1488 )
1489 IS
1490
1491 /** Local variable for SQL Fetch *******/
1492 /*Added L_IND_REC_NULL for bug 2296735 */
1493 L_IND_REC pa_draft_invoice_details%rowtype;
1494 L_IND_REC_NULL pa_draft_invoice_details%rowtype;
1495 L_IND_REC_NEW pa_draft_invoice_details%rowtype;
1496 X_Inv_rec pa_draft_invoice_details%rowtype;
1497 L_CC_REC get_cc_dist%rowtype;
1498 L_CDL_REC prv_class_rec;
1499 L_Compute_flag PA_PLSQL_DATATYPES.Char1TabTyp;
1500 L_AdjEI_id PA_PLSQL_DATATYPES.IdTabTyp;
1501 L_Output_vat_tax_id PA_PLSQL_DATATYPES.IdTabTyp;
1502 L_Output_tax_code PA_PLSQL_DATATYPES.Char30TabTyp;
1503 L_Output_tax_exempt_flag PA_PLSQL_DATATYPES.Char1TabTyp;
1504 L_Output_tax_exempt_number PA_PLSQL_DATATYPES.Char30TabTyp;
1505 L_Output_exempt_reason_code PA_PLSQL_DATATYPES.Char80TabTyp;
1506 RECORD_FOUND BOOLEAN;
1507 CC_RECORD_FOUND BOOLEAN;
1508 X_Error_Code PA_PLSQL_DATATYPES.Char30TabTyp;
1509 X_Error_Stage number;
1510 L_PROV_ENABLED BOOLEAN := TRUE;
1511 l_cost_accrual_flag Varchar2(1);
1512 l_denom_transfer_price NUMBER;
1513 l_denom_transfer_price1 NUMBER;
1514 l_amount NUMBER;
1515 l_denom_tp_currency_code Varchar2(15);
1516 l_acct_currency_code Varchar2(15);
1517 l_acct_tp_rate_date Date;
1518 l_acct_tp_rate_type Varchar2(30);
1519 l_acct_tp_exchange_rate NUMBER;
1520 l_line_num NUMBER;
1521 l_status_code Number;
1522 l_stage Number;
1523 l_ind_compiled_set_id Number;
1524 l_multiplier Number;
1525 l_cc_Cross_charge_code Varchar2(1); /*For bug 5370844*/
1526
1527 /* CBGA and Project Jobs */
1528
1529 l_job_group_id pa_projects_all.bill_job_group_id%TYPE;
1530
1531
1532 BEGIN
1533 IF g1_debug_mode = 'Y' THEN
1534 PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.process_invoice_details...');
1535 END IF;
1536 /* Checking Provider Reclass Enabled or not...*/
1537 If (P_ca_prov_code = 'N' and P_nca_prov_code = 'N')
1538 Then
1539 L_PROV_ENABLED := FALSE;
1540 End if;
1541
1542 /** Initialize all local and Global variable
1543 **/
1544 init;
1545
1546 /*** for cross charge code as 'B','X','N'
1547 No processing for Tax and Transfer Price is needed.
1548 ***/
1549
1550
1551
1552 IF g1_debug_mode = 'Y' THEN
1553 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Setting Compute Flag ...');
1554 END IF;
1555 For I in 1..P_No_of_records
1556 Loop
1557 If (P_Cross_charge_code(I) in ('X','N','B')
1558 or P_Net_zero_flag(I) = 'Y' )
1559 Then
1560 L_Compute_flag(I) := 'N';
1561 Else
1562 L_Compute_flag(I) := 'Y';
1563 End if;
1564
1565 If not P_Error_code.exists(I)
1566 then
1567 P_Error_code(I) := NULL;
1568 End if;
1569
1570 If not P_AdjEI_id.exists(I)
1571 Then
1572 L_AdjEI_id(I) := NULL;
1573 L_Compute_flag(I) := 'N';
1574 Else
1575 L_AdjEI_id(I) := P_AdjEI_id(I);
1576 End if;
1577
1578 If P_Denom_TP_currency_code.exists(I)
1579 then
1580 I_DENOM_CURRENCY_CODE(I) := P_Denom_TP_currency_code(I);
1581 End if;
1582
1583 If P_Denom_transfer_price.exists(I)
1584 then
1585 I_DENOM_BILL_AMOUNT(I) := P_Denom_transfer_price(I);
1586 End if;
1587
1588 If P_Acct_TP_rate_type.exists(I)
1589 then
1590 I_ACCT_RATE_TYPE(I) := P_Acct_TP_rate_type(I);
1591 End if;
1592
1593 If P_Acct_TP_rate_date.exists(I)
1594 then
1595 I_ACCT_RATE_DATE(I) := P_Acct_TP_rate_date(I);
1596 End if;
1597
1598 If P_Acct_TP_exchange_rate.exists(I)
1599 then
1600 I_ACCT_EXCHANGE_RATE(I) := P_Acct_TP_exchange_rate(I);
1601 End if;
1602
1603 If P_Acct_transfer_price.exists(I)
1604 then
1605 I_BILL_AMOUNT(I) := P_Acct_transfer_price(I);
1606 End if;
1607
1608 If P_CC_markup_base_code.exists(I)
1609 then
1610 I_MARKUP_CALC_BASE_CODE(I) := P_CC_markup_base_code(I);
1611 End if;
1612
1613 If P_TP_ind_compiled_set_id.exists(I)
1614 then
1615 I_IND_COMPILED_SET_ID(I) := P_TP_ind_compiled_set_id(I);
1616 End if;
1617
1618 If P_TP_bill_rate.exists(I)
1619 then
1620 I_BILL_RATE(I) := P_TP_bill_rate(I);
1621 End if;
1622
1623 If P_tp_job_id.exists(I)
1624 then
1625 I_tp_job_id(I) := P_tp_job_id(I);
1626 End if;
1627
1628
1629 If P_TP_base_amt.exists(I)
1630 then
1631 I_BASE_AMOUNT(I) := P_TP_base_amt(I);
1632 End if;
1633
1634 If P_TP_bill_markup_percentage.exists(I)
1635 then
1636 I_BILL_MARKUP_PERCENTAGE(I) := P_TP_bill_markup_percentage(I);
1637 End if;
1638
1639 If P_TP_rule_percentage.exists(I)
1640 then
1641 I_RULE_PERCENTAGE(I) := P_TP_rule_percentage(I);
1642 End if;
1643
1644 If P_TP_schedule_line_percentage.exists(I)
1645 then
1646 I_SCHEDULE_LINE_PERCENTAGE(I) := P_TP_schedule_line_percentage(I);
1647 End if;
1648
1649 /* Added for cross proj */
1650 If P_project_tp_rate_type.exists(I)
1651 then
1652 I_project_tp_rate_type(I) := P_project_tp_rate_type(I);
1653 End if;
1654 If P_project_tp_rate_date.exists(I)
1655 then
1656 I_project_tp_rate_date(I) := P_project_tp_rate_date(I);
1657 End if;
1658 If P_project_tp_exchange_rate.exists(I)
1659 then
1660 I_project_tp_exchange_rate(I) := P_project_tp_exchange_rate(I);
1661 End if;
1662 If P_projfunc_tp_rate_type.exists(I)
1663 then
1664 I_projfunc_tp_rate_type(I) := P_projfunc_tp_rate_type(I);
1665 End if;
1666 If P_projfunc_tp_rate_date.exists(I)
1667 then
1668 I_projfunc_tp_rate_date(I) := P_projfunc_tp_rate_date(I);
1669 End if;
1670 If P_projfunc_tp_exchange_rate.exists(I)
1671 then
1672 I_projfunc_tp_exchange_rate(I) := P_projfunc_tp_exchange_rate(I);
1673 End if;
1674 I_tp_amt_type_code(I) := P_tp_amt_type_code(I);
1675
1676 I_project_tp_currency_code(I) := P_Prj_currency_code(I);
1677 I_projfunc_tp_currency_code(I) := P_projfunc_currency_code(I);
1678 /* End for cross proj*/
1679
1680
1681
1682 I_ACCT_CURRENCY_CODE(I) := P_Acct_curr_code;
1683
1684 if (L_PROV_ENABLED)
1685 Then
1686 If (not G_project_category.exists(P_CC_Project_Id(I)))
1687 Then
1688 /* Client Extension to identify the cost accrual project */
1689 pa_cc_ca.identify_ca_project
1690 ( p_project_id => P_CC_Project_Id(I),
1691 x_cost_accrual_flag => l_cost_accrual_flag );
1692
1693 G_project_category(P_CC_Project_Id(I)) := l_cost_accrual_flag;
1694 End if;
1695
1696 If ( G_project_category(P_CC_Project_Id(I)) = 'Y'
1697 and P_ca_prov_code <> 'N' )
1698 Then
1699 I_PRV_ENABLED(I) := P_ca_prov_code;
1700 Elsif ( G_project_category(P_CC_Project_Id(I)) = 'N'
1701 and P_nca_prov_code <> 'N' )
1702 Then
1703 I_PRV_ENABLED(I) := P_nca_prov_code;
1704 Else /* else part added for bug 2770240 */
1705 I_PRV_ENABLED(I) := 'N';
1706 End if;
1707 Else
1708 I_PRV_ENABLED(I) := 'N';
1709 End if;
1710
1711
1712 End Loop;
1713
1714
1715 /** Call Transfer Price API ****/
1716 /** Not Ready till today ( 13 - JUL - 1999 ) **/
1717
1718 pa_cc_transfer_price.GET_TRANSFER_PRICE
1719 (
1720 P_MODULE_NAME => 'PAICGEN',
1721 P_PRVDR_ORGANIZATION_ID => P_Prvdr_Organization,
1722 P_RECVR_ORG_ID => P_Recvr_org_id,
1723 P_RECVR_ORGANIZATION_ID => P_Recvr_Organization,
1724 P_EXPND_ORGANIZATION_ID => P_Expnd_Organization,
1725 P_EXPENDITURE_ITEM_ID => P_EI_id,
1726 P_EXPENDITURE_TYPE => P_Expend_type,
1727 P_EXPENDITURE_ITEM_DATE => P_EI_date,
1728 P_EXPENDITURE_CATEGORY => P_Expenditure_category,
1729 P_LABOR_NON_LABOR_FLAG => P_Labor_nl_flag,
1730 P_SYSTEM_LINKAGE_FUNCTION => P_Sys_linkage,
1731 P_TASK_ID => P_Task_id,
1732 P_TP_SCHEDULE_ID => P_TP_sch_id,
1733 P_DENOM_CURRENCY_CODE => P_Denom_currency_code,
1734 P_PROJECT_CURRENCY_CODE => P_Prj_currency_code,
1735 P_REVENUE_DISTRIBUTED_FLAG => P_Revenue_distributed_flag,
1736 P_PROCESSED_THRU_DATE => P_Processed_thru_date,
1737 P_COMPUTE_FLAG => L_Compute_flag,
1738 P_TP_FIXED_DATE => P_TP_fixed_date,
1739 P_DENOM_RAW_COST_AMOUNT => P_Denom_raw_cost_amt,
1740 P_DENOM_BURDENED_COST_AMOUNT => P_Denom_burdened_cost_amt,
1741 P_RAW_REVENUE_AMOUNT => P_Raw_revenue_amt,
1742 P_PROJECT_ID => P_CC_Project_Id,
1743 P_QUANTITY => P_Quantity,
1744 P_INCURRED_BY_PERSON_ID => P_Incurred_by_person_id,
1745 P_JOB_ID => P_Job_id,
1746 P_NON_LABOR_RESOURCE => P_Non_labor_resource,
1747 P_NL_RESOURCE_ORGANIZATION_ID => P_NL_resource_organization,
1748 P_ARRAY_SIZE => P_No_of_records,
1749 P_DEBUG_MODE => PA_IC_INV_UTILS.G_DEBUG_MODE,
1750 X_DENOM_TP_CURRENCY_CODE => I_DENOM_CURRENCY_CODE,
1751 X_DENOM_TRANSFER_PRICE => I_DENOM_BILL_AMOUNT,
1752 X_ACCT_TP_RATE_TYPE => I_ACCT_RATE_TYPE,
1753 X_ACCT_TP_RATE_DATE => I_ACCT_RATE_DATE,
1754 X_ACCT_TP_EXCHANGE_RATE => I_ACCT_EXCHANGE_RATE,
1755 X_ACCT_TRANSFER_PRICE => I_BILL_AMOUNT,
1756 X_CC_MARKUP_BASE_CODE => I_MARKUP_CALC_BASE_CODE,
1757 X_TP_IND_COMPILED_SET_ID => I_IND_COMPILED_SET_ID,
1758 X_TP_BILL_RATE => I_BILL_RATE,
1759 X_TP_BASE_AMOUNT => I_BASE_AMOUNT,
1760 X_TP_BILL_MARKUP_PERCENTAGE => I_BILL_MARKUP_PERCENTAGE,
1761 X_TP_SCHEDULE_LINE_PERCENTAGE => I_SCHEDULE_LINE_PERCENTAGE,
1762 X_TP_RULE_PERCENTAGE => I_RULE_PERCENTAGE,
1763 X_tp_job_id => I_tp_job_id,
1764 X_ERROR_CODE => P_Error_code,
1765 X_RETURN_STATUS => X_Error_Stage
1766 /* Added for cross proj*/
1767 ,p_projfunc_currency_code => p_projfunc_currency_code
1768 ,p_tp_amt_type_code => p_tp_amt_type_code
1769 ,p_assignment_id => p_assignment_id
1770 ,x_proj_tp_rate_type => I_project_tp_rate_type
1771 ,x_proj_tp_rate_date => I_project_tp_rate_date
1772 ,x_proj_tp_exchange_rate => I_project_tp_exchange_rate
1773 ,x_proj_transfer_price => I_project_transfer_price
1774 ,x_projfunc_tp_rate_type => I_projfunc_tp_rate_type
1775 ,x_projfunc_tp_rate_date => I_projfunc_tp_rate_date
1776 ,x_projfunc_tp_exchange_rate => I_projfunc_tp_exchange_rate
1777 ,x_projfunc_transfer_price => I_projfunc_transfer_price,
1778 /* End for cross proj*/
1779 /*Bill rate discount */
1780 p_dist_rule => p_dist_rule,
1781 p_mcb_flag => p_mcb_flag,
1782 p_bill_rate_multiplier => p_bill_rate_multiplier,
1783 p_raw_cost => p_raw_cost,
1784 p_labor_schdl_discnt => p_labor_schdl_discnt,
1785 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date,
1786 p_bill_job_grp_id => p_bill_job_grp_id,
1787 p_labor_sch_type => p_labor_sch_type,
1788 p_project_org_id => p_project_org_id,
1789 p_project_type => p_project_type,
1790 p_exp_func_curr_code => p_exp_func_curr_code,
1791 p_incurred_by_organz_id => p_incurred_by_organz_id,
1792 p_raw_cost_rate => p_raw_cost_rate,
1793 p_override_to_organz_id => p_override_to_organz_id,
1794 p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id,
1795 p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id,
1796 p_exp_raw_cost => p_exp_raw_cost,
1797 p_assignment_precedes_task => p_assignment_precedes_task,
1798
1799 p_burden_cost => p_burden_cost,
1800 p_task_nl_bill_rate_org_id => p_task_nl_bill_rate_org_id,
1801 p_proj_nl_bill_rate_org_id => p_proj_nl_bill_rate_org_id,
1802 p_task_nl_std_bill_rate_sch => p_task_nl_std_bill_rate_sch,
1803 p_proj_nl_std_bill_rate_sch => p_proj_nl_std_bill_rate_sch,
1804 p_nl_task_sch_date => p_nl_task_sch_date,
1805 p_nl_proj_sch_date => p_nl_proj_sch_date,
1806 p_nl_task_sch_discount => p_nl_task_sch_discount,
1807 p_nl_proj_sch_discount => p_nl_proj_sch_discount,
1808 p_nl_sch_type => p_nl_sch_type,
1809 /* Added the last two parameters for Doosan rate api enhancement */
1810 p_task_nl_std_bill_rate_sch_id => p_task_nl_std_bill_rate_sch_id,
1811 p_proj_nl_std_bill_rate_sch_id => p_proj_nl_std_bill_rate_sch_id,
1812 p_uom_flag => p_uom_flag
1813
1814 );
1815
1816
1817
1818 /* CBGA and Project Jobs */
1819
1820
1821 SELECT bill_job_group_id
1822 INTO l_job_group_id
1823 FROM pa_projects_all
1824 WHERE project_id = P_project_id;
1825
1826
1827 FOR I in 1 .. P_No_of_records
1828 LOOP
1829
1830 BEGIN
1831
1832 I_prov_proj_bill_job_id(I) := PA_Cross_Business_grp.IsMappedToJob(p_job_id(I), l_job_group_id);
1833
1834 /* Added for bug 5251471 */
1835 PA_IC_INV_UTILS.log_message('process_invoice_details: job_id: ' || to_char(p_job_id(I)));
1836 PA_IC_INV_UTILS.log_message('process_invoice_details: prov_proj_bill_job_id: ' || to_char(I_prov_proj_bill_job_id(I)));
1837
1838 IF I_prov_proj_bill_job_id(I) IS NULL THEN
1839 I_prov_proj_bill_job_id(I):= p_job_id(I);
1840 END IF;
1841
1842 PA_IC_INV_UTILS.log_message('process_invoice_details: job_id: ' || to_char(p_job_id(I)));
1843 PA_IC_INV_UTILS.log_message('process_invoice_details: prov_proj_bill_job_id: ' || to_char(I_prov_proj_bill_job_id(I)));
1844 /* End of bug 5251471 */
1845
1846 END;
1847
1848 END LOOP;
1849
1850
1851
1852 /** Call Tax API **/
1853 IF g1_debug_mode = 'Y' THEN
1854 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Calling Tax API.....');
1855 END IF;
1856 /*Last two parameters added for customer account relation 2760630 */
1857 PA_OUTPUT_TAX.GET_DEFAULT_TAX_INFO_ARR
1858 ( P_Project_Id => P_Project_Id,
1859 P_Customer_Id => P_Customer_Id,
1860 P_Bill_to_site_use_id => P_Bill_to_site_use_id,
1861 P_Ship_to_site_use_id => P_Ship_to_site_use_id,
1862 P_Set_of_books_id => P_Set_of_books_id,
1863 P_Expenditure_item_id => P_EI_id,
1864 P_User_Id => P_User_Id,
1865 P_Request_id => P_Request_id,
1866 P_No_of_records => P_No_of_records,
1867 P_Compute_flag => L_Compute_flag,
1868 P_Error_Code => P_Error_Code,
1869 -- X_Output_vat_tax_id => I_OUTPUT_VAT_TAX_ID,
1870 X_Output_tax_code => I_OUTPUT_TAX_CODE,
1871 X_Output_tax_exempt_flag => I_OUTPUT_TAX_EXEMPT_FLAG,
1872 X_Output_tax_exempt_number => I_OUTPUT_TAX_EXEMPT_NUMBER,
1873 X_Output_exempt_reason_code => I_TAX_EXEMPT_REASON_CODE,
1874 Pbill_to_customer_id => P_Customer_Id,
1875 Pship_to_customer_id => P_Customer_Id);
1876
1877 /** Select the existing invoice details for EI if exists **/
1878
1879 FOR I in 1..P_No_of_records
1880 LOOP
1881 -- Checck whether any invoice detail exists for that EI
1882 -- If Yes, fetech the latest invoice details of the EI.
1883 BEGIN
1884
1885 L_IND_REC := L_IND_REC_NULL; /*Bug 2296735 */
1886
1887 IF g1_debug_mode = 'Y' THEN
1888 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Fetching Existing Invoice details for EI'||to_char(P_EI_ID(I)));
1889 END IF;
1890 SELECT DET.*
1891 INTO L_IND_REC
1892 FROM PA_DRAFT_INVOICE_DETAILS DET
1893 WHERE DET.EXPENDITURE_ITEM_ID
1894 = decode(nvl(P_AdjEI_id(I),0),0,P_EI_id(I),P_AdjEI_id(I))
1895 AND DET.LINE_NUM = ( SELECT MAX(I.LINE_NUM)
1896 FROM PA_DRAFT_INVOICE_DETAILS I
1897 WHERE I.EXPENDITURE_ITEM_ID =
1898 DET.EXPENDITURE_ITEM_ID );
1899 RECORD_FOUND := TRUE;
1900 I_Line_num := L_IND_REC.Line_Num;
1901
1902 /*Change for bug 5370844*/
1903 SELECT cc_Cross_charge_code
1904 INTO l_cc_Cross_charge_code
1905 FROM pa_expenditure_items
1906 WHERE expenditure_item_id = P_EI_id(I);
1907
1908 IF nvl(P_AdjEI_id(I),0) <> 0 AND l_cc_Cross_charge_code = 'N'
1909 THEN
1910 RECORD_FOUND := FALSE;
1911 END IF;
1912 /*End of code change for bug 5370844*/
1913
1914 EXCEPTION
1915 When NO_DATA_FOUND
1916 Then
1917 RECORD_FOUND := FALSE;
1918 I_Line_num := 0;
1919 END;
1920
1921 -- Check whether any cc distribution exists for that invoice details.
1922 BEGIN
1923
1924 IF g1_debug_mode = 'Y' THEN
1925 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Fetching Existing Cross Charge Distribution.....');
1926 END IF;
1927
1928 open get_cc_dist(L_IND_REC.EXPENDITURE_ITEM_ID);
1929 fetch get_cc_dist INTO L_CC_REC;
1930 if get_cc_dist%notfound
1931 then
1932 IF g1_debug_mode = 'Y' THEN
1933 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Fetching Cross charge distribution - not Found');
1934 END IF;
1935 CC_RECORD_FOUND := FALSE;
1936 else
1937 IF g1_debug_mode = 'Y' THEN
1938 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Fetching Cross charge distribution - Found');
1939 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Line - Found'||to_char(L_CC_REC.line_num));
1940 END IF;
1941 CC_RECORD_FOUND := TRUE;
1942 end if;
1943
1944 close get_cc_dist;
1945
1946 EXCEPTION
1947 When others
1948 Then
1949 IF g1_debug_mode = 'Y' THEN
1950 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Error in Fetching Cross charge distribution.....');
1951 END IF;
1952 raise;
1953 END;
1954
1955 /* Read attributes from CDL */
1956 IF ((I_PRV_ENABLED(I) <> 'N')
1957 AND (P_Cross_charge_code(I) = 'I')
1958 AND (P_Net_zero_flag(I) = 'N'))
1959 THEN
1960 BEGIN
1961
1962 IF g1_debug_mode = 'Y' THEN
1963 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || ' Fetching Cost Distribution Lines for EI :'||to_char(P_EI_id(I)));
1964 END IF;
1965 /*changes done for burden schedule change enhancement */
1966 SELECT decode(I_PRV_ENABLED(I),'B',(cdl.DENOM_BURDENED_COST+NVL(CDL.DENOM_BURDENED_CHANGE,0)),
1967 'R',cdl.DENOM_RAW_COST),
1968 decode(I_PRV_ENABLED(I),'B',(cdl.ACCT_BURDENED_COST+NVL(CDL.ACCT_BURDENED_CHANGE,0)),
1969 'R',cdl.ACCT_RAW_COST),
1970 cdl.DENOM_CURRENCY_CODE,
1971 cdl.ACCT_CURRENCY_CODE,
1972 cdl.ACCT_RATE_DATE,
1973 cdl.ACCT_RATE_TYPE,
1974 cdl.ACCT_EXCHANGE_RATE,
1975 cdl.LINE_NUM,
1976 cdl.IND_COMPILED_SET_ID
1977 INTO l_denom_transfer_price,
1978 l_amount,
1979 l_denom_tp_currency_code,
1980 l_acct_currency_code,
1981 l_acct_tp_rate_date,
1982 l_acct_tp_rate_type,
1983 l_acct_tp_exchange_rate,
1984 l_line_num,
1985 l_ind_compiled_set_id
1986 FROM pa_cost_distribution_lines_all cdl
1987 WHERE cdl.EXPENDITURE_ITEM_ID = P_EI_id(I)
1988 AND cdl.LINE_NUM_REVERSED IS NULL
1989 AND cdl.LINE_NUM = ( SELECT MAX(cdl1.LINE_NUM)
1990 FROM pa_cost_distribution_lines_all cdl1
1991 WHERE cdl1.EXPENDITURE_ITEM_ID =
1992 cdl.EXPENDITURE_ITEM_ID
1993 AND cdl.LINE_NUM_REVERSED IS NULL
1994 AND cdl1.line_type = 'R' );
1995 IF g1_debug_mode = 'Y' THEN
1996 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'After Fetching Cost Distribution Lines.....');
1997 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Display Method...'||P_burden_disp_method(I));
1998 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Cross Charge...'||P_Cross_Charge_code(I));
1999 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Base Method...'||I_PRV_ENABLED(I));
2000 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'date...'||P_EI_date(I));
2001 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Expn Org...'||to_char(P_Expnd_Organization(I)));
2002 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Exp type ...'||P_Expend_type(I));
2003 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Exp Id ...'||to_char(P_EI_id(I)));
2004 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Task id ...'||to_char(P_Task_id(I)));
2005 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'TP Price ...'||to_char(l_denom_transfer_price));
2006 END IF;
2007
2008 if ((P_burden_disp_method.exists(I))
2009 and (P_burden_disp_method(I) = 'D')
2010 and (I_PRV_ENABLED(I) = 'B')
2011 and (P_Cross_Charge_code(I) = 'I'))
2012 Then
2013 IF g1_debug_mode = 'Y' THEN
2014 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Enter.....');
2015 END IF;
2016 /* l_multiplier := PA_COST_PLUS.Get_Mltplr_For_Compiled_Set
2017 ( l_ind_compiled_set_id );
2018 l_amount := (1 + l_multiplier ) * l_amount;
2019 l_denom_transfer_price
2020 := ( 1 + l_multiplier ) * l_denom_transfer_price; Commented for bug 3180315 */
2021
2022 /* Start of changes for bug 3180315 */
2023 /* Made changes in burden cost calculation for bug 3180315 so that burden cost calculation process used here
2024 is in synch with that in PAXCCTPB.pls (PA_CC_TRANSFER_PRICE) package where changes were done for bug 2215942 */
2025
2026 DECLARE
2027 l_burden_sch_rev_id Number;
2028 l_Stage Number;
2029 l_Status Number;
2030 l_burden_calc_amount_l number;
2031 l_tp_ind_compiled_set_id_l Number;
2032 l_rate_schedule_id Number;
2033 t_rate_sch_rev_id number;
2034 t_sch_fixed_date date;
2035
2036
2037 BEGIN
2038
2039 IF g1_debug_mode = 'Y' THEN
2040 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Inside Burden cost Calculation.....');
2041 END IF;
2042
2043 PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
2044 'ACTUAL',
2045 P_EI_id(I), -- Transaction Item Id
2046 'PA', -- Transaction Type
2047 P_Task_id(I), -- Task Id
2048 'C', -- Schedule Type
2049 to_date(P_EI_date(I),'YYYY/MM/DD'), -- EI Date File.Date.5
2050 t_sch_fixed_date, -- Sch_fixed_date (Out)
2051 t_rate_sch_rev_id, -- Rate_sch_rev_id (Out)
2052 l_status); -- Status (Out)
2053
2054 IF (t_rate_sch_rev_id IS NOT NULL) THEN
2055 l_burden_sch_rev_id := t_rate_sch_rev_id;
2056 PA_COST_PLUS.Get_Burden_Amount1(
2057 P_Expend_type(I),
2058 P_Expnd_Organization(I),
2059 l_denom_transfer_price,
2060 l_burden_calc_amount_l,
2061 l_burden_sch_rev_id,
2062 l_tp_ind_compiled_set_id_l,
2063 l_status,
2064 l_stage
2065 );
2066
2067
2068 l_denom_transfer_price :=
2069 l_denom_transfer_price+l_burden_calc_amount_l;
2070
2071 l_Stage :=0;
2072 l_Status :=0;
2073 l_burden_calc_amount_l :=0;
2074 l_tp_ind_compiled_set_id_l :=0;
2075
2076 PA_COST_PLUS.Get_Burden_Amount1(
2077 P_Expend_type(I),
2078 P_Expnd_Organization(I),
2079 l_amount,
2080 l_burden_calc_amount_l,
2081 l_burden_sch_rev_id,
2082 l_tp_ind_compiled_set_id_l,
2083 l_status,
2084 l_stage
2085 );
2086
2087 l_amount :=
2088 l_amount+l_burden_calc_amount_l;
2089
2090 ELSE
2091
2092
2093 /* get the task level burden schedule id by considering the task level overrides */
2094 select NVL(OVR_COST_IND_RATE_SCH_ID, COST_IND_RATE_SCH_ID)
2095 into l_rate_schedule_id
2096 from pa_tasks
2097 where task_id in
2098 ( select task_id
2099 from pa_expenditure_items_all
2100 where expenditure_item_id = P_EI_id(I)
2101 );
2102 /* Get the burden amount from the call to the procedure PA_COST_PLUS.Get_Burden_Amount,
2103 which gets the revision for the given burden schedule, then burden structure,
2104 then cost base from the burden structure corresponding to the expenditure type,
2105 then sum of the compiled multipliers */
2106
2107 PA_COST_PLUS.Get_Burden_Amount(
2108 l_rate_schedule_id,
2109 to_Date(P_EI_date(I),'YYYY/MM/DD'),/*File.Date.5*/
2110 P_Expend_type(I),
2111 P_Expnd_Organization(I),
2112 l_denom_transfer_price,
2113 l_burden_calc_amount_l,
2114 l_burden_sch_rev_id,
2115 l_tp_ind_compiled_set_id_l,
2116 l_status,
2117 l_stage
2118 );
2119
2120 l_denom_transfer_price :=
2121 l_denom_transfer_price+l_burden_calc_amount_l;
2122
2123 l_burden_sch_rev_id :=0;
2124 l_Stage :=0;
2125 l_Status :=0;
2126 l_burden_calc_amount_l :=0;
2127 l_tp_ind_compiled_set_id_l :=0;
2128
2129 PA_COST_PLUS.Get_Burden_Amount(
2130 l_rate_schedule_id,
2131 to_Date(P_EI_date(I),'YYYY/MM/DD'),/*File.date.5*/
2132 P_Expend_type(I),
2133 P_Expnd_Organization(I),
2134 l_amount,
2135 l_burden_calc_amount_l,
2136 l_burden_sch_rev_id,
2137 l_tp_ind_compiled_set_id_l,
2138 l_status,
2139 l_stage
2140 );
2141
2142
2143 l_amount :=
2144 l_amount+l_burden_calc_amount_l;
2145
2146 END IF; /* If t_rate_sch_rev_id IS NOT NULL */
2147
2148 EXCEPTION
2149 when no_data_found then
2150 Raise;
2151
2152 When Others Then
2153 Raise;
2154 END;
2155
2156 /* End of changes for bug 3180315 */
2157
2158 end if;
2159
2160
2161 L_CDL_REC.DENOM_TP_CURRENCY_CODE := l_denom_tp_currency_code;
2162 L_CDL_REC.DENOM_TRANSFER_PRICE := l_denom_transfer_price;
2163 L_CDL_REC.AMOUNT := l_amount;
2164 L_CDL_REC.ACCT_CURRENCY_CODE := l_acct_currency_code;
2165 L_CDL_REC.ACCT_TP_RATE_TYPE := l_acct_tp_rate_type;
2166 L_CDL_REC.ACCT_TP_RATE_DATE := l_acct_tp_rate_date;
2167 L_CDL_REC.ACCT_TP_EXCHANGE_RATE := l_acct_tp_exchange_rate;
2168 L_CDL_REC.DR_CODE_COMBINATION_ID := P_provider_dr_ccid(I);
2169 L_CDL_REC.CR_CODE_COMBINATION_ID := P_provider_cr_ccid(I);
2170 L_CDL_REC.REFERENCE_2 := I_PRV_ENABLED(I);
2171 L_CDL_REC.CROSS_CHARGE_CODE := P_Cross_charge_code(I);
2172 L_CDL_REC.CDL_LINE_NUM := l_line_num;
2173 L_CDL_REC.EXPENDITURE_ITEM_DATE := to_date(P_EI_date(I),'YYYY/MM/DD');/*File.Date.5*/
2174 /*2229894 L_CDL_REC.EXPENDITURE_ITEM_DATE := to_date(P_EI_date(I),'DD-MM-YY'); */
2175 L_CDL_REC.EXPENDITURE_ITEM_ID := P_EI_ID(I);
2176 L_CDL_REC.PROJECT_ID := P_CC_Project_Id(I);
2177 L_CDL_REC.TASK_ID := P_Task_id(I);
2178 L_CDL_REC.SYSTEM_LINKAGE_FUNCTION:= P_Sys_linkage(I); /* Added for 3857986 */
2179 EXCEPTION
2180 WHEN others
2181 Then
2182 IF g1_debug_mode = 'Y' THEN
2183 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Error in Fetching Cost Distribution Lines.....');
2184 END IF;
2185 Raise;
2186 END;
2187 END IF;
2188
2189 -- Process the CC_Cross_charge_code as 'B'/'N'/'X'
2190
2191 If P_Cross_charge_code(I) <> 'I'
2192 Then
2193 IF g1_debug_mode = 'Y' THEN
2194 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing Cross_charge_code <> I...');
2195 END IF;
2196 /** If invoice details exist and not invoiced **/
2197 If (RECORD_FOUND)
2198 Then
2199 If ( L_IND_REC.Draft_invoice_num IS NULL
2200 AND L_IND_REC.Line_num_reversed IS NULL)
2201 Then
2202 /** The existing record is to be deleted **/
2203 delete_row(L_IND_REC);
2204 if (CC_RECORD_FOUND)
2205 then
2206 /** Delete provider reclass entry **/
2207 delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
2208 end if;
2209 /** Mark the EI 's ic processed flag as no processing
2210 required. **/
2211 add_ei(L_IND_REC.expenditure_item_id);
2212 Elsif ( L_IND_REC.Draft_invoice_num IS NULL
2213 AND L_IND_REC.Line_num_reversed IS NOT NULL)
2214 Then
2215 update_row(L_IND_REC,'N');
2216 Elsif (L_IND_REC.Draft_invoice_num IS not NULL
2217 AND L_IND_REC.Line_num_reversed IS NULL)
2218 Then
2219 /** Reverse existing invoice details **/
2220 reverse_row(p_inv_rec=>L_IND_REC);
2221 if (CC_RECORD_FOUND)
2222 then
2223 reverse_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,
2224 to_Date(P_EI_date(I),'YYYY/MM/DD'),
2225 P_Sys_linkage(I), /* Added for 3857986 */
2226 L_CC_REC,
2227 I);
2228 end if;
2229 Elsif (L_IND_REC.Draft_invoice_num IS not NULL
2230 AND L_IND_REC.Line_num_reversed IS NOT NULL)
2231 Then
2232 /** Mark the EI 's ic processed flag as no processing
2233 required. **/
2234 add_ei(L_IND_REC.expenditure_item_id);
2235 End if;
2236 Else
2237 /** Mark the EI 's ic processed flag as no processing
2238 required. **/
2239 add_ei(L_IND_REC.expenditure_item_id);
2240 End if;
2241 End if;
2242
2243 IF g1_debug_mode = 'Y' THEN
2244 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'P Error code - ' || P_Error_Code(I));
2245 END IF; /*Added for bug 2296735 */
2246 -- Process the CC_Cross_charge_code as 'I'
2247 If P_Cross_charge_code(I) = 'I'
2248 Then
2249 IF g1_debug_mode = 'Y' THEN
2250 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing Cross_charge_code = I...');
2251 END IF;
2252 If P_Error_Code(I) Is NULL
2253 Then
2254 If (L_AdjEI_id(I) is null or L_AdjEI_id(I) = 0 )
2255 Then
2256 if (P_Net_zero_flag(I) = 'N')
2257 Then
2258 If (not RECORD_FOUND)
2259 then
2260 /** Create New Rows.......**/
2261 build_row ( P_Expenditure_item_id => P_EI_Id(I),
2262 P_Project_Id => P_Project_id,
2263 P_CC_Project_id => P_CC_Project_id(I),
2264 P_CC_Tax_task_id => P_CC_Tax_task_id(I),
2265 P_Rev_ccid => P_revenue_ccid(I),
2266 P_Arr_position => I,
2267 X_Inv_rec => X_Inv_rec );
2268 /** Create CC distribution **/
2269 if (I_PRV_ENABLED(I) <> 'N' )
2270 then
2271
2272 L_CDL_REC.REFERENCE_1 := to_char(X_Inv_rec.DRAFT_INVOICE_DETAIL_ID);
2273 build_cc_dist(L_CDL_REC,
2274 0,I);
2275 end if;
2276 Elsif (L_IND_REC.Draft_invoice_num IS NULL
2277 AND L_IND_REC.Line_num_reversed IS NULL)
2278 Then
2279 if is_new_details(L_IND_REC,P_revenue_ccid(I),I)
2280 then
2281 build_row('U',I,P_revenue_ccid(I),L_IND_REC);
2282 else
2283 update_row(L_IND_REC,'N');
2284 end if;
2285 if ((I_PRV_ENABLED(I) <> 'N' )
2286 and (CC_RECORD_FOUND))
2287 then
2288 update_cc_dist(L_CDL_REC,L_CC_REC,I);
2289 end if;
2290 Elsif (L_IND_REC.Draft_invoice_num IS not NULL
2291 AND L_IND_REC.Line_num_reversed IS NULL)
2292 Then
2293 if is_new_details(L_IND_REC,P_revenue_ccid(I),I)
2294 then
2295 L_IND_REC_NEW := L_IND_REC;
2296 reverse_row(p_inv_rec=>L_IND_REC);
2297 build_row('A',I,P_revenue_ccid(I),L_IND_REC_NEW);
2298 if (I_PRV_ENABLED(I) <> 'N') AND (CC_RECORD_FOUND) /* added AND clause for bug 2293378 */
2299 then
2300 reverse_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,
2301 to_Date(P_EI_date(I),'YYYY/MM/DD'),/*File.Date.5*/
2302 P_Sys_linkage(I), /* Added for 3857986 */
2303 L_CC_REC,
2304 I);
2305 L_CDL_REC.REFERENCE_1 := to_char(L_IND_REC_NEW.DRAFT_INVOICE_DETAIL_ID);
2306 build_cc_dist(L_CDL_REC,
2307 L_CC_REC.line_num,
2308 I);
2309 end if;
2310 End if;
2311 Elsif (L_IND_REC.Draft_invoice_num IS not NULL
2312 AND L_IND_REC.Line_num_reversed IS not NULL)
2313 Then
2314 build_row('A',I,P_revenue_ccid(I),L_IND_REC);
2315 if (I_PRV_ENABLED(I) <> 'N')
2316 then
2317 L_CDL_REC.REFERENCE_1 := to_char(L_IND_REC.DRAFT_INVOICE_DETAIL_ID);
2318 build_cc_dist(L_CDL_REC,
2319 L_CC_REC.line_num,
2320 I);
2321 end if;
2322 Elsif (L_IND_REC.Draft_invoice_num IS NULL
2323 AND L_IND_REC.Line_num_reversed IS not NULL)
2324 Then
2325 update_row(L_IND_REC,'N');
2326 End If;
2327 End if;
2328 Else /* For adjusted EI */
2329 If RECORD_FOUND
2330 Then
2331 If (L_IND_REC.Draft_invoice_num IS NULL
2332 AND L_IND_REC.Line_num_reversed IS NULL)
2333 Then
2334 delete_row(L_IND_REC);
2335 if (CC_RECORD_FOUND)
2336 then
2337 delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
2338 end if;
2339 add_ei(P_EI_id(I));
2340 add_ei(P_AdjEI_id(I));
2341 Elsif (L_IND_REC.Draft_invoice_num IS not NULL
2342 AND L_IND_REC.Line_num_reversed IS NULL)
2343 Then
2344 PA_IC_INV_UTILS.log_message
2345 ('L_IND_REC.Draft_invoice_num IS not NULL, L_IND_REC.Line_num_reversed IS NULL');
2346 L_IND_REC.Expenditure_item_id := P_EI_id(I);
2347 L_IND_REC.Line_num := Null;
2348 /* Changed the call to reverse_row for bug 2770182 */
2349
2350 reverse_row(p_inv_rec=>L_IND_REC,
2351 p_adjusted_ei=>p_AdjEI_Id(I));
2352 if (CC_RECORD_FOUND)
2353 then
2354 reverse_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,
2355 to_date(P_EI_date(I),'YYYY/MM/DD'),/*File.Date.5*/
2356 P_Sys_linkage(I), /* Added for 3857986 */
2357 L_CC_REC,
2358 I);
2359 end if;
2360 Else
2361 add_ei(P_EI_id(I));
2362 add_ei(P_AdjEI_id(I));
2363 End if;
2364 Else
2365 add_ei(P_EI_id(I));
2366 add_ei(P_AdjEI_id(I));
2367 End if; /* Record Found */
2368 End if; /* Adjusted EI is null */
2369 Else
2370 if (L_IND_REC.Draft_invoice_num IS NULL
2371 AND L_IND_REC.Line_num_reversed IS NULL)
2372 Then
2373 delete_row(L_IND_REC);
2374 if (CC_RECORD_FOUND)
2375 then
2376 delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
2377 end if;
2378 End If;
2379 End If; /* P_Error_code(I) is Null */
2380 End if; /* CC_Cross_charge_code = 'I' */
2381
2382 /* Process the EI if not processed in invoice details
2383 adjustment or creation */
2384 If (not(I_EXP_ITEM_USED.exists(I)) /* Step 1 */
2385 and ( P_Net_zero_flag(I) = 'N' )
2386 and ( P_Error_code(I) IS NULL ))
2387 Then
2388 IF g1_debug_mode = 'Y' THEN
2389 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing provider reclass only part...');
2390 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Enabled...'||I_PRV_ENABLED(I));
2391 END IF;
2392 if CC_RECORD_FOUND
2393 then
2394 IF g1_debug_mode = 'Y' THEN
2395 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Record...');
2396 END IF;
2397 end if;
2398 IF g1_debug_mode = 'Y' THEN
2399 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Cross Charge Code...'||P_Cross_charge_code(I));
2400 END IF;
2401 if (( I_PRV_ENABLED(I) = 'N') /* Step 2 */
2402 and (CC_RECORD_FOUND))
2403 Then
2404 IF g1_debug_mode = 'Y' THEN
2405 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing provider reclass only reversing/delete .2');
2406 END IF;
2407 reverse_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,
2408 to_Date(P_EI_date(I),'YYYY/MM/DD'),/*file.Date.5*/
2409 P_Sys_linkage(I), /* Added for 3857986 */
2410 L_CC_REC,
2411 I);
2412 Elsif ((I_PRV_ENABLED(I) <> 'N')
2413 and (P_Cross_charge_code(I) = 'I'))
2414 Then
2415 IF g1_debug_mode = 'Y' THEN
2416 PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing provider reclass only reversing/delete .3');
2417 END IF;
2418 /* If existing line is not transferred to GL */
2419 If (( L_CC_REC.TRANSFER_STATUS_CODE = 'P')
2420 and( CC_RECORD_FOUND))
2421 Then
2422 update_cc_dist(L_CDL_REC,L_CC_REC,I);
2423 Else
2424 /* If existing line is transferred to Gl, reverse the original
2425 and create the new */
2426 l_line_num := 0;
2427 if (CC_RECORD_FOUND)
2428 then
2429 reverse_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,
2430 to_date(P_EI_date(I),'YYYY/MM/DD'),/*File.Date.5*/
2431 P_Sys_linkage(I), /* Added for 3857986 */
2432 L_CC_REC,
2433 I);
2434 l_line_num := L_CC_REC.line_num;
2435 end if;
2436 L_CDL_REC.REFERENCE_1 := to_char(L_IND_REC.DRAFT_INVOICE_DETAIL_ID);
2437 build_cc_dist(L_CDL_REC,
2438 l_line_num,
2439 I);
2440 End if; /* L_CC_REC.TRANSFERRED_STATUS_CODE = 'P' */
2441 End if;/* Step 2 */
2442 End if;/* Step 1 */
2443
2444
2445 /* Return the TP attributes */
2446 P_Denom_TP_currency_code(I) := I_DENOM_CURRENCY_CODE(I);
2447 P_Denom_transfer_price(I) := I_DENOM_BILL_AMOUNT(I) ;
2448 P_Acct_TP_rate_type(I) := I_ACCT_RATE_TYPE(I);
2449 P_Acct_TP_rate_date(I) := I_ACCT_RATE_DATE(I);
2450 P_Acct_TP_exchange_rate(I) := I_ACCT_EXCHANGE_RATE(I);
2451 P_Acct_transfer_price(I) := I_BILL_AMOUNT(I);
2452 P_CC_markup_base_code(I) := I_MARKUP_CALC_BASE_CODE(I);
2453 P_TP_ind_compiled_set_id(I) := I_IND_COMPILED_SET_ID(I);
2454 P_TP_bill_rate(I) := I_BILL_RATE(I) ;
2455 P_TP_base_amt(I) := I_BASE_AMOUNT(I);
2456 P_TP_bill_markup_percentage(I) := I_BILL_MARKUP_PERCENTAGE(I);
2457 P_TP_rule_percentage(I) := I_RULE_PERCENTAGE(I);
2458 P_TP_schedule_line_percentage(I) := I_SCHEDULE_LINE_PERCENTAGE(I);
2459
2460 /*Added for cross proj */
2461 P_project_tp_rate_type(I) := I_project_tp_rate_type(I);
2462 P_project_tp_rate_date(I) := I_project_tp_rate_date(I);
2463 P_project_tp_exchange_rate(I) := I_project_tp_exchange_rate(I);
2464 P_project_transfer_price(I) := I_project_transfer_price(I);
2465 P_projfunc_tp_rate_type(I) := I_projfunc_tp_rate_type(I);
2466 P_projfunc_tp_rate_date(I) := I_projfunc_tp_rate_date(I);
2467 P_projfunc_tp_exchange_rate(I) := I_projfunc_tp_exchange_rate(I);
2468 P_projfunc_transfer_price(I) := I_projfunc_transfer_price(I);
2469 /* End for cross proj*/
2470
2471 /* CBGA and project Jobs */
2472
2473 P_tp_job_id(I) := I_tp_job_id(I);
2474 P_prov_proj_bill_job_id(I) := I_prov_proj_bill_job_id(I);
2475
2476 End Loop;
2477
2478 -- Apply all physical database changes
2479 apply_db_changes (P_Error_Code ,
2480 P_EI_id ,
2481 P_No_of_records );
2482
2483
2484 EXCEPTION
2485 When others
2486 Then
2487 Raise;
2488 END process_invoice_details;
2489
2490 -- Apply all pending insert changes in Bulk
2491 PROCEDURE apply_ins_changes IS
2492 BEGIN
2493 PA_INVOICE_DETAIL_PKG.insert_rows(P_Insert_tab);
2494
2495 if (PA_CC_BL_PROCESS.g_icnt > 0 )
2496 then
2497 PA_CC_BL_PROCESS.mass_insert;
2498 end if;
2499 END apply_ins_changes;
2500
2501 -- Procedure to reverse provider reclass entries from
2502 -- Invoice cancellation
2503 --
2504 PROCEDURE reverse_preclass (
2505 P_inv_detail_id PA_PLSQL_DATATYPES.IdTabTyp,
2506 P_new_inv_detail_id PA_PLSQL_DATATYPES.IdTabTyp,
2507 P_EI_id PA_PLSQL_DATATYPES.IdTabTyp,
2508 P_EI_date PA_PLSQL_DATATYPES.Char30TabTyp,
2509 P_Sys_Linkage PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for 3857986 */
2510 P_tab_count NUMBER) IS
2511
2512 l_cc_dist_rec get_cc_dist%rowtype;
2513
2514 BEGIN
2515 FOR I in 1..P_tab_count
2516 LOOP
2517
2518 /* Open the cursor that checks if provider reclass rows are
2519 created corresponding to these Draft invoice details
2520
2521 If Yes then reverse the rows
2522
2523 */
2524
2525 IF g1_debug_mode = 'Y' THEN
2526 pa_ic_inv_utils.log_message('reverse_preclass: ' || 'Checking for reclasss for DID id = '
2527 ||P_inv_detail_id(I)||' EI Id = '
2528 ||P_EI_id (I) );
2529 END IF;
2530 OPEN get_cc_dist( P_EI_id (I));
2531
2532 FETCH get_cc_dist INTO l_cc_dist_rec;
2533
2534 IF get_cc_dist%FOUND THEN
2535 reverse_cc_dist (
2536 P_new_inv_detail_id(I),
2537 P_EI_date(I),
2538 P_Sys_linkage(I), /* Added for 3857986 */
2539 l_cc_dist_rec,
2540 1);
2541
2542 IF g1_debug_mode = 'Y' THEN
2543 pa_ic_inv_utils.log_message('reverse_preclass: ' || 'Created CC Dist line Id = '
2544 ||l_cc_dist_rec.CC_DIST_LINE_ID);
2545 END IF;
2546
2547 END IF;
2548
2549 CLOSE get_cc_dist;
2550
2551 END LOOP;
2552 END reverse_preclass;
2553
2554 END PA_INVOICE_DETAIL_PROCESS;