[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PA_CC_AR_AP_TRANSFER
Source
1 PACKAGE BODY PA_CC_AR_AP_TRANSFER AS
2 /* $Header: PAXARAPB.pls 120.8.12010000.2 2008/08/22 14:08:33 nkapling ship $ */
3
4 ----------------------------------------------------------------
5 --Procedure Transfer_ar_ap_invoices is a wrapper to convert the
6 --data types for input parameters
7 ----------------------------------------------------------------
8 Procedure Transfer_ar_ap_invoices(
9 p_internal_billing_type in PA_PLSQL_DATATYPES.Char20TabTyp,
10 p_project_id in PA_PLSQL_DATATYPES.NumTabTyp,
11 p_draft_invoice_number in PA_PLSQL_DATATYPES.NumTabTyp,
12 p_ra_invoice_number in PA_PLSQL_DATATYPES.Char20TabTyp,
13 p_prvdr_org_id in PA_PLSQL_DATATYPES.Char30TabTyp,
14 p_recvr_org_id in PA_PLSQL_DATATYPES.Char30TabTyp,
15 p_customer_trx_id in PA_PLSQL_DATATYPES.Char30TabTyp,
16 p_project_customer_id in PA_PLSQL_DATATYPES.Char30TabTyp,
17 p_invoice_date in PA_PLSQL_DATATYPES.Char15TabTyp,
18 p_invoice_comment in PA_PLSQL_DATATYPES.Char240TabTyp,
19 p_inv_currency_code in PA_PLSQL_DATATYPES.Char15TabTyp,
20 p_compute_flag in PA_PLSQL_DATATYPES.Char1TabTyp,
21 p_array_size in number,
22 x_transfer_status_code out NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,/*file.sql.39*/
23 x_transfer_error_code out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,/*file.sql.39*/
24 x_status_code out NOCOPY varchar2 /*file.sql.39*/) IS
25
26 --v_project_id PA_PLSQL_DATATYPES.NumTabTyp;
27 --v_draft_invoice_number PA_PLSQL_DATATYPES.NumTabTyp;
28 v_prvdr_org_id PA_PLSQL_DATATYPES.NumTabTyp;
29 v_recvr_org_id PA_PLSQL_DATATYPES.NumTabTyp;
30 v_customer_trx_id PA_PLSQL_DATATYPES.NumTabTyp;
31 v_project_customer_id PA_PLSQL_DATATYPES.NumTabTyp;
32 v_invoice_date PA_PLSQL_DATATYPES.DateTabTyp;
33 --v_array_size number;
34 v_debug_mode varchar2(2);
35 v_process_mode varchar2(10);
36
37
38 begin
39 --v_array_size:=to_number(p_array_size);
40 for i in 1..p_array_size LOOP
41 -- v_project_id(i):=to_number(p_project_id(i));
42 -- v_draft_invoice_number(i):=to_number(p_draft_invoice_number(i));
43 v_prvdr_org_id(i):=to_number(p_prvdr_org_id(i));
44 v_recvr_org_id(i):=to_number(p_recvr_org_id(i));
45 v_customer_trx_id(i):=to_number(p_customer_trx_id(i));
46 v_project_customer_id(i):=to_number(p_project_customer_id(i));
47 v_invoice_date(i):=fnd_date.canonical_to_date(p_invoice_date(i));
48 end loop;
49
50 Transfer_ar_ap_invoices_01(
51 v_debug_mode ,
52 v_process_mode ,
53 p_internal_billing_type ,
54 p_project_id ,
55 p_draft_invoice_number ,
56 p_ra_invoice_number ,
57 v_prvdr_org_id ,
58 v_recvr_org_id ,
59 v_customer_trx_id ,
60 v_project_customer_id ,
61 v_invoice_date ,
62 p_invoice_comment ,
63 p_inv_currency_code ,
64 p_compute_flag ,
65 p_array_size ,
66 x_transfer_status_code,
67 x_transfer_error_code ,
68 x_status_code );
69
70 end Transfer_ar_ap_invoices;
71
72 --------------------------------------------------------
73 --Procedure Transfer_ar_ap_invoices_01 is the main procedure
74 --in which sub procedures are called
75 -----------------------------------------------------------
76 Procedure Transfer_ar_ap_invoices_01(
77 p_debug_mode in varchar2,
78 p_process_mode in varchar2,
79 p_internal_billing_type in PA_PLSQL_DATATYPES.Char20TabTyp,
80 p_project_id in PA_PLSQL_DATATYPES.NumTabTyp,
81 p_draft_invoice_number in PA_PLSQL_DATATYPES.NumTabTyp,
82 p_ra_invoice_number in PA_PLSQL_DATATYPES.Char20TabTyp,
83 p_prvdr_org_id in PA_PLSQL_DATATYPES.NumTabTyp,
84 p_recvr_org_id in PA_PLSQL_DATATYPES.NumTabTyp,
85 p_customer_trx_id in PA_PLSQL_DATATYPES.NumTabTyp,
86 p_project_customer_id in PA_PLSQL_DATATYPES.NumTabTyp,
87 p_invoice_date in PA_PLSQL_DATATYPES.DateTabTyp,
88 p_invoice_comment in PA_PLSQL_DATATYPES.Char240TabTyp,
89 p_inv_currency_code in PA_PLSQL_DATATYPES.Char15TabTyp,
90 p_compute_flag in PA_PLSQL_DATATYPES.Char1TabTyp,
91 p_array_size in Number,
92 x_transfer_status_code out NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,/*file.sql.39*/
93 x_transfer_error_code out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,/*file.sql.39*/
94 x_status_code out NOCOPY varchar2 /*file.sql.39*/) IS
95
96 Cursor c_setup_info(p_prvdr_org_id in number, p_recvr_org_id in number) is
97 select a.vendor_site_id vendor_site_id ,
98 a.ap_inv_exp_type expenditure_type,
99 a.ap_inv_exp_organization_id expenditure_organization_id,
100 b.vendor_id vendor_id
101 from pa_cc_org_relationships a,
102 po_vendor_sites_all b
103 where a.prvdr_org_id= p_prvdr_org_id
104 and a.recvr_org_id= p_recvr_org_id
105 and a.vendor_site_id =b.vendor_site_id;
106
107 Cursor c_invoice_amount (p_customer_trx_id in number) is
108 select sum(extended_amount) amount
109 from ra_customer_trx_lines_all
110 where customer_trx_id = p_customer_trx_id;
111
112
113 Cursor c_receiver_project_task (p_project_customer_id in number,p_project_id in number) is
114 select ppc.receiver_task_id task_id,
115 pt.project_id project_id
116 from pa_project_customers ppc,
117 pa_tasks pt
118 where pt.task_id=ppc.receiver_task_id
119 and ppc.customer_id=p_project_customer_id
120 and ppc.project_id=p_project_id;
121
122 Cursor c_invoice_lines_counter(p_project_id in number,p_draft_invoice_number in number)IS
123 select count(*) lines_counter from pa_draft_invoice_items
124 where project_id=p_project_id
125 and draft_invoice_num=p_draft_invoice_number
126 and invoice_line_type <> 'NET ZERO ADJUSTMENT';/* added as fix for Bug 1580854 */
127
128 Cursor c_invoice_lines(p_project_id in number,
129 p_draft_invoice_number in number,
130 p_recvr_org_id in number,
131 p_customer_trx_id in number)IS
132 SELECT pdii.line_num line_number,
133 pdii.inv_amount amount,
134 nvl(pdii.translated_text, pdii.text) description,
135 pdii.output_tax_classification_code tax_code,
136 --aptax.name tax_code,
137 --pdii.output_vat_tax_id tax_id,
138 pdii.cc_project_id project_id,
139 pdii.cc_tax_task_id task_id,
140 pdii.inv_amount pa_quantity,
141 arinv.line_number pa_cc_ar_invoice_line_num,
142 arinv.customer_trx_line_id cust_trx_line_id -- added for bug 5045406
143 FROM pa_draft_invoice_items pdii,
144 ra_customer_trx_lines_all arinv
145 -- ap_tax_codes_all aptax,
146 -- ar_vat_tax_all artax
147 where arinv.interface_line_attribute6= pdii.line_num
148 and arinv.customer_trx_id = p_customer_trx_id
149 and pdii.project_id=p_project_id
150 and pdii.draft_invoice_num=p_draft_invoice_number
151 and pdii.output_tax_classification_code IS NOT NULL
152 and pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT' /* added as fix for Bug 2397907 */
153 -- and pdii.output_vat_tax_id = artax.vat_tax_id
154 -- and artax.tax_code= aptax.name
155 -- and pdii.output_vat_tax_id is not null
156 -- and aptax.org_id= p_recvr_org_id
157 UNION
158 SELECT pdii.line_num line_number,
159 pdii.inv_amount amount,
160 nvl(pdii.translated_text, pdii.text) description,
161 null tax_code,
162 -- pdii.output_vat_tax_id tax_id,
163 pdii.cc_project_id project_id,
164 pdii.cc_tax_task_id task_id,
165 pdii.inv_amount pa_quantity,
166 arinv.line_number pa_cc_ar_invoice_line_num,
167 arinv.customer_trx_line_id cust_trx_line_id -- added for bug 5045406
168 FROM pa_draft_invoice_items pdii,
169 ra_customer_trx_lines_all arinv
170 where arinv.interface_line_attribute6= pdii.line_num
171 and pdii.project_id=p_project_id
172 and pdii.draft_invoice_num=p_draft_invoice_number
173 and arinv.customer_trx_id =p_customer_trx_id
174 and pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT' /* added as fix for Bug 2397907 */
175 and pdii.output_Tax_classificatioN_code IS NULL;
176 -- and pdii.output_vat_tax_id is null;
177
178 v_invoice_id number;
179 v_request_id number :=fnd_global.conc_request_id;
180 v_receiver_project_id number;
181 v_receiver_task_id number;
182 v_expenditure_type varchar2(30);
183 v_expenditure_organization_id number;
184 v_error_code number;
185 v_receiver_project_task c_receiver_project_task%ROWTYPE;
186 v_setup_info c_setup_info%ROWTYPE;
187 v_invoice_amount c_invoice_amount%ROWTYPE;
188 x_error_stage varchar2(250);
189 v_debug_mode varchar2(2);
190 v_process_mode varchar2(10);
191 v_old_stack VARCHAR2(630);
192 v_invoice_type varchar2(30); -- added for etax changes
193 v_invoice_lines_rec c_invoice_lines%ROWTYPE;
194 v_invoice_line_num PA_PLSQL_DATATYPES.NumTabTyp;
195 v_inv_amount PA_PLSQL_DATATYPES.NumTabTyp;
196 v_description PA_PLSQL_DATATYPES.Char240TabTyp;
197 v_tax_code PA_PLSQL_DATATYPES.Char50TabTyp;
198 --v_tax_id PA_PLSQL_DATATYPES.NumTabTyp;
199 v_project_id PA_PLSQL_DATATYPES.NumTabTyp;
200 v_task_id PA_PLSQL_DATATYPES.NumTabTyp;
201 v_pa_quantity PA_PLSQL_DATATYPES.NumTabTyp;
202 v_pa_cc_ar_inv_line_num PA_PLSQL_DATATYPES.NumTabTyp;
203 v_cust_trx_line_id PA_PLSQL_DATATYPES.NumTabTyp; -- bug 5045406
204 v_lines_counter_rec c_invoice_lines_counter%ROWTYPE;
205 v_lines_counter number;
206 v_counter number :=0;
207
208 -- DevDrop2 Changes Starts
209
210 l_status number;
211 l_error_stage varchar2(250);
212 l_error_code number;
213 dummy_x varchar2(1);
214 l_expenditure_type varchar2(50);
215 l_expenditure_organization_id number;
216
217 l_receiver_project_id number;
218 l_receiver_task_id number;
219
220 v_arr_exp_type PA_PLSQL_DATATYPES.Char50TabTyp;
221 v_arr_exp_organization_id PA_PLSQL_DATATYPES.NumTabTyp;
222
223 -- DevDrop2 Changes End
224
225 Begin
226
227 pa_debug.Init_err_stack ( 'Transfer_ar_ap_invoices');
228 v_debug_mode := NVL(p_debug_mode, 'Y');
229 v_process_mode := NVL(p_process_mode, 'SQL');
230 pa_debug.set_process(v_process_mode, 'LOG', v_debug_mode) ;
231 x_status_code :=null;
232 pa_debug.G_err_code:='0';
233
234 --- Is it necessary to validate p_array_size here? how about if it is null or 0?
235
236 pa_debug.G_err_stage := 'Beginning LOOP';
237 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
238 For I in 1..p_array_size LOOP
239 x_transfer_status_code(I):='P';
240 x_transfer_error_code(I):=null;
241 x_status_code:=null;
242
243 pa_debug.G_err_stage := 'Check if any mandatory input parameter is null';
244 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
245 if p_internal_billing_type(I) is null or
246 p_project_id(I) is null or
247 p_draft_invoice_number(I) is null or
248 p_ra_invoice_number(I) is null or
249 p_prvdr_org_id(I) is null or
250 p_recvr_org_id(I) is null or
251 p_customer_trx_id(I) is null or
252 p_invoice_date(I) is null or
253 /* p_invoice_comment(I) is null or */
254 p_inv_currency_code(I) is null then
255 x_transfer_status_code(I):='X';
256 x_transfer_error_code(I):='PA_CC_AR_AP_NULL_PARAMETER';
257 x_status_code:='-1';
258 end if;
259 if nvl(p_compute_flag(I),'Y')='Y' then
260 pa_debug.G_err_stage := 'Check if vendor and expenditure information is valid';
261 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
262 open c_setup_info(p_prvdr_org_id(I), p_recvr_org_id(I));
263 LOOP
264 fetch c_setup_info into v_setup_info;
265 if c_setup_info%ROWCOUNT =1 then
266 v_expenditure_type:=v_setup_info.expenditure_type;
267 v_expenditure_organization_id:=v_setup_info.expenditure_organization_id;
268 exit;
269 elsif c_setup_info%ROWCOUNT=0 then
270 x_transfer_status_code(I):='X';
271 x_transfer_error_code(I):='PA_CC_AR_AP_NO_SETUP_INFO';
272 x_status_code:='-1';
273 exit;
274 elsif c_setup_info%ROWCOUNT>1 then
275 x_transfer_status_code(I):='X';
276 x_transfer_error_code(I):='PA_CC_AR_AP_NO_UNQ_SETUP';
277 x_status_code:='-1';
278 exit;
279 end if;
280 END LOOP;
281 close c_setup_info;
282
283 pa_debug.G_err_stage := 'Check if invoice amount is valid';
284 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
285 open c_invoice_amount (p_customer_trx_id(I));
286 fetch c_invoice_amount into v_invoice_amount;
287 if c_invoice_amount%NOTFOUND then
288 x_transfer_status_code(I):='X';
289 x_transfer_error_code(I):='PA_CC_AR_AP_NO_INV_AMOUNT';
290 x_status_code:='-1';
291 end if;
292 close c_invoice_amount;
293
294 pa_debug.G_err_stage := 'Check if receiver project and task is valid';
295 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
296 if p_internal_billing_type(I) ='PA_IP_INVOICES' then
297 open c_receiver_project_task(p_project_customer_id(I),p_project_id(I));
298 LOOP
299 fetch c_receiver_project_task into v_receiver_project_task;
300 if c_receiver_project_task%ROWCOUNT =1 then
301 v_receiver_project_id:=v_receiver_project_task.project_id;
302 v_receiver_task_id:=v_receiver_project_task.task_id;
303 exit;
304 elsif c_receiver_project_task%ROWCOUNT =0 then
305 x_transfer_status_code(I):='X';
306 x_transfer_error_code(I):='PA_CC_AR_AP_NO_REC_PROJ_TASK';
307 x_status_code:='-1';
308 exit;
309 elsif c_receiver_project_task%ROWCOUNT>1 then
310 x_transfer_status_code(I):='X';
311 x_transfer_error_code(I):='PA_CC_AR_AP_MUL_REC_PROJ_TSK';
312 x_status_code:='-1';
313 exit;
314 end if;
315 END LOOP;
316 close c_receiver_project_task;
317 end if;
318
319 select ap_invoices_interface_s.nextval into v_invoice_id from sys.dual;
320
321 pa_debug.G_err_stage := 'Check if tax code is null';
322 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
323 open c_invoice_lines_counter(p_project_id(I),p_draft_invoice_number(I));
324 fetch c_invoice_lines_counter into v_lines_counter;
325 close c_invoice_lines_counter;
326
327 v_counter := 0; /* for bug 6594692 */
328
329
330 -- The following sql added for etax changes
331 select decode(draft_invoice_num_credited, NULL, 'INVOICE', 'CREDIT_MEMO')
332 into v_invoice_type
333 from pa_draft_invoices_all
334 where project_id = p_project_id(I)
335 and draft_invoice_num = p_draft_invoice_number(I);
336
337 FOR v_invoice_lines_rec IN c_invoice_lines(p_project_id(I) ,p_draft_invoice_number(I) ,p_recvr_org_id(I), p_customer_trx_id(I)) LOOP
338 v_counter:=c_invoice_lines%ROWCOUNT;
339 v_invoice_line_num(v_counter):=v_invoice_lines_rec.line_number;
340 v_inv_amount(v_counter):=v_invoice_lines_rec.amount;
341 v_description(v_counter):=v_invoice_lines_rec.description;
342 v_tax_code(v_counter):=v_invoice_lines_rec.tax_code;
343 -- v_tax_id(v_counter):=v_invoice_lines_rec.tax_id;
344 v_project_id(v_counter):=v_invoice_lines_rec.project_id;
345 v_task_id(v_counter):=v_invoice_lines_rec.task_id;
346 v_pa_quantity(v_counter):=v_invoice_lines_rec.pa_quantity;
347 v_pa_cc_ar_inv_line_num(v_counter):=v_invoice_lines_rec.pa_cc_ar_invoice_line_num;
348 v_cust_trx_line_id(v_counter) := v_invoice_lines_rec.cust_trx_line_id ; -- bug 5045406
349 END LOOP;
350 if v_lines_counter <> v_counter then
351 x_transfer_status_code(I):='X';
352 x_transfer_error_code(I):='PA_CC_AR_AP_NO_TAX_CODE';
353 x_status_code:='-1';
354 end if;
355
356
357 -- DevDrop2 Changes Start */
358 -- Calling the client extension to override the expenditure type and
359 -- expenditure organization id for each ap invoice line
360
361 if x_status_code is null then
362
363 pa_debug.G_err_stage := 'Calling Client Extension override_exp_type_exp_org';
364 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
365
366 FOR K in 1..v_counter LOOP
367
368 -- Call Client Extension to override expenditure type and
369 -- expenditure organization of the ap inovice lines.
370
371 -- driver receiver project and task id
372
373 if ( p_internal_billing_type(I) = 'PA_IC_INVOICES' ) then
374
375 l_receiver_project_id := v_project_id(K);
376 l_receiver_task_id := v_task_id(K);
377 else
378 l_receiver_project_id := v_receiver_project_id;
379 l_receiver_task_id := v_receiver_task_id;
380 end if;
381
382
383 pa_cc_ap_inv_client_extn.override_exp_type_exp_org (
384 p_internal_billing_type => p_internal_billing_type(I),
385 p_project_id => p_project_id(I),
386 p_receiver_project_id => l_receiver_project_id,
387 p_receiver_task_id => l_receiver_task_id,
388 p_draft_invoice_number => p_draft_invoice_number(I),
389 p_draft_invoice_line_num => v_invoice_line_num(K),
390 p_invoice_date => p_invoice_date(I),
391 p_ra_invoice_number => p_ra_invoice_number(I),
392 p_provider_org_id => p_prvdr_org_id(I),
393 p_receiver_org_id => p_recvr_org_id(I),
394 p_cc_ar_invoice_id => p_customer_trx_id(I),
395 p_cc_ar_invoice_line_num => v_pa_cc_ar_inv_line_num(K),
396 p_project_customer_id => p_project_customer_id(I),
397 p_vendor_id => v_setup_info.vendor_id,
398 p_vendor_site_id => v_setup_info.vendor_site_id,
399 p_expenditure_type => v_expenditure_type,
400 p_expenditure_organization_id => v_expenditure_organization_id,
401 x_expenditure_type => l_expenditure_type,
402 x_expenditure_organization_id => l_expenditure_organization_id,
403 x_status => l_status,
404 x_Error_Stage => l_error_stage,
405 X_Error_Code => l_error_code) ;
406
407 if ( l_status <> 0 ) then
408
409 pa_debug.G_err_stage := 'Error Client Extension(Call) : draft_inv_num :'||p_draft_invoice_number(I)||
410 ' draft_inv_line_num :'||v_invoice_line_num(K);
411 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
412
413 x_transfer_status_code(I):='X';
414 x_transfer_error_code(I):='PA_CC_AR_AP_ERR_CLIENT_EXTN';
415 x_status_code:= '-1';
416 end if;
417
418 if ( l_status = 0 ) then
419
420 -- Check Expenditure Type
421
422 if ( l_expenditure_type <> v_expenditure_type ) then
423
424 -- Validate the expenditure type.
425 -- l_expenditure_type should be valid one for expenditure class supplier invoice.
426
427 begin
428 select 'x'
429 into dummy_x
430 from dual
431 where EXISTS
432 ( select 'x' from
433 pa_expend_typ_sys_links
434 where system_linkage_function = 'VI'
435 and expenditure_type = l_expenditure_type);
436
437 v_arr_exp_type(K) := l_expenditure_type;
438 exception
439 when no_data_found then
440 pa_debug.G_err_stage := 'Error Client Extension(Exp_type): draft_inv_num :'||
441 p_draft_invoice_number(I)||' draft_inv_line_num :'||
442 v_invoice_line_num(K);
443 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
444
445 pa_debug.G_err_stage := 'override exp_type : '||l_expenditure_type;
446 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
447
448 x_transfer_status_code(I):='X';
449 x_transfer_error_code(I):='PA_CC_AR_AP_INVLD_EXP_TYP';
450 x_status_code:= '-1';
451 end;
452
453 else
454 v_arr_exp_type(K) := v_expenditure_type;
455 end if;
456
457
458 -- Check Expenditure Organization
459
460 if ( l_expenditure_organization_id <> v_expenditure_organization_id ) then
461
462 -- Validate the l_expenditure_organization_id.
463 -- l_expenditure_organization_id should be valid expenditure organization for
464 -- receiver operating unit.
465
466 begin
467 select 'x'
468 into dummy_x
469 from dual
470 where EXISTS
471 ( select 'x' from
472 pa_all_organizations
473 where org_id = p_recvr_org_id(I)
474 and organization_id = l_expenditure_organization_id
475 and PA_ORG_USE_TYPE = 'EXPENDITURES');
476
477 v_arr_exp_organization_id(K) := l_expenditure_organization_id;
478 exception
479 when no_data_found then
480 pa_debug.G_err_stage := 'Error Client Extension(Exp_org): draft_inv_num :'||p_draft_invoice_number(I)||
481 ' draft_inv_line_num :'||v_invoice_line_num(K);
482 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
483
484 pa_debug.G_err_stage := 'override exp_orgz_id'||l_expenditure_organization_id;
485 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
486
487 x_transfer_status_code(I):='X';
488 x_transfer_error_code(I):='PA_CC_AR_AP_INVLD_EXP_ORG';
489 x_status_code:= '-1';
490 end;
491
492 else
493 v_arr_exp_organization_id(K) := v_expenditure_organization_id;
494 end if;
495
496 end if;
497
498 END LOOP;
499 end if;
500
501 -- DevDrop2 Changes End
502
503 if x_status_code is null then
504 pa_debug.G_err_stage := 'Insert into AP_invoices_interface table';
505 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
506 populate_ap_invoices_interface (
507 p_internal_billing_type(I),
508 v_invoice_id,
509 p_ra_invoice_number(I),
510 p_invoice_date(I),
511 v_setup_info.vendor_id,
512 v_setup_info.vendor_site_id,
513 v_invoice_amount.amount,
514 p_inv_currency_code(I),
515 p_invoice_comment(I),
516 to_char(v_request_id),
517 NULL, /*1994696*:Changed workflow_flag from 'Y' to NULL*/
518 p_recvr_org_id(I));
519 pa_debug.G_err_stage := 'Insert into AP_invoice_lines_interface table';
520 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
521
522 --DevDrop2 Changes
523 --Changed expenditure type , expenditure organization id to
524 --v_arr_exp_type , v_arr_exp_organization_id respectively
525
526 Populate_ap_inv_line_interface(
527 v_invoice_id,
528 p_internal_billing_type(I) ,
529 v_receiver_project_id,
530 v_receiver_task_id ,
531 v_arr_exp_type ,
532 p_invoice_date(I) ,
533 v_arr_exp_organization_id ,
534 p_recvr_org_id(I) ,
535 p_customer_trx_id(I),
536 p_project_customer_id(I),
537 v_invoice_line_num ,
538 v_inv_amount ,
539 v_description ,
540 v_tax_code ,
541 v_project_id ,
542 v_task_id ,
543 v_pa_quantity ,
544 v_pa_cc_ar_inv_line_num ,
545 v_counter,
546 v_invoice_type, -- added for etax changes
547 v_cust_trx_line_id ); -- added for bug 5045406
548 x_transfer_status_code(I) :='A';
549 end if;
550 end if;
551 END LOOP;
552
553 If x_status_code is null then
554 x_status_code:='0';
555 end if;
556
557 pa_debug.reset_err_stack;
558
559 Exception
560 when others then
561 pa_debug.G_err_code := SQLCODE;
562 pa_debug.G_err_stage:=pa_debug.G_err_stage|| ': '||sqlerrm;
563 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
564 x_status_code:=-1;
565 RAISE;
566 end transfer_ar_ap_invoices_01;
567
568
569 ----------------------------------------------------
570 --procedure Populate_ap_invoices_interface
571 --transfer invoices to ap_invoices_interface table
572 -------------------------------------------------
573 procedure Populate_ap_invoices_interface(
574 p_internal_billing_type in varchar2,
575 p_invoice_id in number,
576 p_invoice_number in varchar2,
577 p_invoice_date in date,
578 p_vendor_id in number,
579 p_vendor_site_id in number,
580 p_invoice_amount number,
581 p_invoice_currency_code in varchar2,
582 p_description in varchar2,
583 p_group_id in varchar2,
584 p_workflow_flag in varchar2,
585 p_org_id in number)
586 IS
587 begin
588 pa_debug.set_err_stack('populate_ap_invoices_interface');
589 Insert into ap_invoices_interface (
590 invoice_id,
591 invoice_num,
592 invoice_date,
593 vendor_id,
594 vendor_site_id,
595 invoice_amount,
596 invoice_currency_code,
597 description,
598 source,
599 group_id,
600 workflow_flag,
601 calc_tax_during_import_flag, -- added for bug 5045406
602 org_id,
603 created_by ,
604 last_update_login ,
605 last_updated_by,
606 creation_date ,
607 last_update_date,
608 invoice_received_date) /* Added for bug 3658825*/
609 values (p_invoice_id,
610 p_invoice_number,
611 p_invoice_date,
612 p_vendor_id,
613 p_vendor_site_id,
614 p_invoice_amount,
615 p_invoice_currency_code,
616 p_description,
617 decode(p_internal_billing_type,'PA_IC_INVOICES','PA_IC_INVOICES','PA_IP_INVOICES'),
618 p_group_id,
619 p_workflow_flag,
620 'Y', -- added for bug 5045406
621 p_org_id,
622 G_created_by,
623 G_last_update_login,
624 G_last_updated_by ,
625 G_creation_date ,
626 G_last_update_date,
627 sysdate); /* Added for bug 3658825*/
628 pa_debug.reset_err_stack;
629 exception
630 when others then
631 pa_debug.G_err_code :=SQLCODE;
632 pa_debug.G_err_stage:= pa_debug.G_err_stage||':'||sqlerrm;
633 Raise;
634 End populate_ap_invoices_interface;
635
636
637 ----------------------------------------------------------------------------------
638 ---procedure Populate_ap_inv_line_interface
639 ----populates ap_invoice_lines_interface table
640 -------------------------------------------------------------------------------
641
642 --DevDrop2 Changes
643 --Changed datatype of p_expenditure_type
644 --Changed datatype of p_expenditure_organization_id
645
646 procedure Populate_ap_inv_line_interface(
647 p_invoice_id in number,
648 p_internal_billing_type in varchar2,
649 p_receiver_project_id in number,
650 p_receiver_task_id in number,
651 p_expenditure_type in PA_PLSQL_DATATYPES.Char50TabTyp,
652 p_invoice_date in date ,
653 p_expenditure_organization_id in PA_PLSQL_DATATYPES.NumTabTyp,
654 p_recvr_org_id in number,
655 p_customer_trx_id in number,
656 p_project_customer_id in number,
657 p_invoice_line_number in PA_PLSQL_DATATYPES.NumTabTyp,
658 p_inv_amount in PA_PLSQL_DATATYPES.NumTabTyp,
659 p_description in PA_PLSQL_DATATYPES.Char240TabTyp,
660 p_tax_code in PA_PLSQL_DATATYPES.Char50TabTyp,
661 p_project_id in PA_PLSQL_DATATYPES.NumTabTyp,
662 p_task_id in PA_PLSQL_DATATYPES.NumTabTyp,
663 p_pa_quantity in PA_PLSQL_DATATYPES.NumTabTyp,
664 p_pa_cc_ar_inv_line_num in PA_PLSQL_DATATYPES.NumTabTyp,
665 p_sub_array_size in number,
666 p_invoice_type in VARCHAR2, -- added for etax changtes
667 p_cust_trx_line_id in PA_PLSQL_DATATYPES.NumTabTyp -- added for bug 5045406
668 )
669 IS
670
671 -- the following var declarations added for etax changes
672
673 l_application_id number;
674 l_entity_code varchar2(30);
675 l_event_class_code varchar2(30);
676 l_trx_id number;
677 l_trx_level_type varchar2(30);
678
679 begin
680 /*Bug# 2042840:Modified the value of pa_addition_flag from hardcoded 'N' to
681 decode(p_internal_billing_type,'PA_IC_INVOICES','T','N').*/
682 pa_debug.set_err_stack('Populate_ap_inv_line_interface');
683
684 -- the following block added for etax changes
685 begin
686 SELECT APPLICATION_ID, ENTITY_CODE,
687 EVENT_CLASS_CODE, TRX_ID, TRX_LEVEL_TYPE
688 into l_application_id, l_entity_code, l_event_class_code, l_trx_id, l_trx_level_type
689 FROM ZX_LINES_DET_FACTORS
690 WHERE trx_id = p_customer_trx_id
691 AND application_id = 222
692 AND entity_Code = 'TRANSACTIONS'
693 AND event_class_code = p_invoice_type
694 AND rownum = 1;
695 exception
696 when others then
697 l_application_id := 222;
698 l_entity_code := 'TRANSACTIONS';
699 l_event_class_code := p_invoice_type;
700 l_trx_id := p_customer_trx_id;
701 l_trx_level_type := 'NO DATA ERR';
702
703 end;
704
705 FORALL i in 1..p_sub_array_size
706 INSERT INTO ap_invoice_lines_interface(
707 invoice_id,
708 line_number,
709 line_type_lookup_code,
710 amount,
711 description,
712 amount_includes_tax_flag,
713 prorate_across_flag,
714 tax_classification_code,/*Changed for bug 4882123 */
715 final_match_flag,
716 last_updated_by,
717 last_update_date,
718 last_update_login,
719 created_by,
720 creation_date,
721 project_id,
722 task_id,
723 expenditure_type,
724 expenditure_item_date,
725 expenditure_organization_id,
726 project_accounting_context,
727 pa_addition_flag,
728 pa_quantity,
729 org_id,
730 pa_cc_ar_invoice_id,
731 pa_cc_ar_invoice_line_num,
732 TAX_CODE_OVERRIDE_FLAG,
733 SOURCE_APPLICATION_ID,
734 SOURCE_ENTITY_CODE,
735 SOURCE_EVENT_CLASS_CODE,
736 SOURCE_TRX_ID,
737 SOURCE_TRX_LEVEL_TYPE,
738 SOURCE_LINE_ID -- added for bug 5045406
739 )
740 VALUES( p_invoice_id,
741 p_invoice_line_number(i),
742 'ITEM',
743 p_inv_amount(i),
744 p_description(i),
745 'N',
746 'N',
747 p_tax_code(i),
748 'N',
749 G_last_updated_by,
750 G_last_update_date,
751 G_last_update_login,
752 G_created_by,
753 G_creation_date,
754 decode(p_internal_billing_type, 'PA_IC_INVOICES', p_project_id(i), p_receiver_project_id),
755 decode(p_internal_billing_type,'PA_IC_INVOICES',p_task_id(i), p_receiver_task_id),
756 p_expenditure_type(i),
757 (select least(NVL(completion_date,p_invoice_date),p_invoice_date) from pa_tasks pt where pt.task_id =decode(p_internal_billing_type,'PA_IC_INVOICES',p_task_id(i), p_receiver_task_id)), /* Modified this for bug 7234925*/
758 p_expenditure_organization_id(i),
759 'Yes',
760 decode(p_internal_billing_type,'PA_IC_INVOICES','T','N'),/*Bug# 2042840*/
761 p_pa_quantity(i),
762 p_recvr_org_id,
763 p_customer_trx_id,
764 p_pa_cc_ar_inv_line_num(i),
765 'Y',
766 l_application_id, -- added for etax changes
767 l_entity_code, -- added for etax changes
768 'INTERCOMPANY_TRX', -- l_event_class_code, -- added for etax changes
769 l_trx_id, -- added for etax changes
770 'LINE' , -- l_trx_level_type -- added for etax changes
771 p_cust_trx_line_id(i) -- added for bug 5045406
772 );
773
774 pa_debug.reset_err_stack;
775 exception
776 when others then
777 pa_debug.G_err_code:=SQLCODE;
778 pa_debug.G_err_stage:=pa_debug.G_err_stage||':'||sqlerrm;
779 raise;
780 END populate_ap_inv_line_interface;
781
782 end PA_CC_AR_AP_TRANSFER;