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