[Home] [Help]
PACKAGE BODY: APPS.PA_OUTPUT_TAX
Source
1 PACKAGE BODY PA_OUTPUT_TAX as
2 /* $Header: PAXOTAXB.pls 120.22.12020000.3 2013/03/22 09:16:18 rvadali ship $ */
3
4 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
7 for customer account relation enhancement bug no 2760630*/
8
9 PROCEDURE GET_DEFAULT_TAX_INFO
10 ( P_Project_Id IN NUMBER ,
11 P_Draft_Inv_Num IN NUMBER ,
12 P_Customer_Id IN NUMBER ,
13 P_Bill_to_site_use_id IN NUMBER ,
14 P_Ship_to_site_use_id IN NUMBER ,
15 P_Sets_of_books_id IN NUMBER ,
16 P_Event_id IN NUMBER ,
17 P_Expenditure_item_id IN NUMBER ,
18 P_User_Id IN NUMBER ,
19 P_Request_id IN NUMBER ,
20 X_Output_tax_exempt_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 X_Output_tax_exempt_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 X_Output_exempt_reason_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 X_Output_tax_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 Pbill_to_customer_id IN NUMBER,
25 Pship_to_customer_id IN NUMBER,
26 P_draft_inv_num_credited IN NUMBER DEFAULT NULL,
27 P_invoice_date IN DATE DEFAULT SYSDATE) /* bug 5484859 */
28
29 IS
30 l_tax_code Varchar2(30);
31 l_line_type Varchar2(20);
32 l_vat_tax_id Number;
33 l_reason_code Varchar2(80);
34 l_certificate_no Varchar2(80);
35 l_exemption_id Number;
36 l_percent_exempt Number;
37 l_inserted_flag Varchar2(1);
38 l_exemption_type Varchar2(100);
39
40 l_transaction_type_id NUMBER;
41 l_batch_source_id NUMBER;
42 l_legal_entity_id NUMBER;
43
44 l_return_status VARCHAR2(30);
45
46 l_bill_to_party_id NUMBER;
47 l_bill_to_party_site_id NUMBER;
48 l_ship_to_party_id NUMBER;
49 l_ship_to_party_site_id NUMBER;
50
51 l_bill_to_address_id number;
52 l_ship_to_address_id number;
53
54
55 l_org_id NUMBER; --eTax changes
56
57 l1_exemption_rec_tbl ZX_TCM_GET_DEF_EXEMPTION.exemption_rec_tbl_type;
58
59 --eTax changes
60 CURSOR get_org_id(c_project_id NUMBER) IS
61 select org_id from pa_projects_all
62 where project_id = c_project_id;
63
64 BEGIN
65 -- Determine the invoice line type
66
67 If nvl(P_Event_id,0)=0 -- Event id is null
68 and nvl(P_Expenditure_item_id,0) = 0 -- Expenditure Item id is NULL
69 Then
70 l_line_type := 'RETENTION';
71 Elsif nvl(P_Event_id,0)<> 0
72 and nvl(P_Expenditure_item_id,0) =0
73 Then
74 l_line_type := 'EVENT';
75 Elsif nvl(P_Event_id,0)=0
76 and nvl(P_Expenditure_item_id,0)<> 0
77 Then
78 l_line_type := 'EXPENDITURE';
79 End If;
80
81 -- Set the draft Invoice Number for Client Extension Validation
82 PA_Tax_Client_Extn_Drv.G_Draft_Invoice_Num := P_Draft_Inv_Num ;
83
84 /* Added for Bug 6524843 */
85 PA_Tax_Client_Extn_Drv.G_Invoice_Date := P_invoice_date ;
86
87 -- Call Default Tax API
88 /*Changed the parameter p_customer_id p_primary_customer_id
89 and added two_new parameters pbill_to_customer_id and pship_to_customer_id
90 for customer account relation enhancement*/
91
92 --etax changes
93 Open get_org_id(p_project_id);
94 Fetch get_org_id into l_org_id;
95 Close get_org_id;
96
97 IF g1_debug_mode = 'Y' THEN
98 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' get_pa_default_classification: Org_id: '||l_org_id);
99 END IF;
100
101 IF g1_debug_mode = 'Y' THEN
102 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' calling ZX_AR_TAX_CLASSIFICATN_DEF_PKG' );
103 PA_MCB_INVOICE_PKG.log_message('p_project_id : ' || P_Project_Id );
104 PA_MCB_INVOICE_PKG.log_message('p_bill_to_site_use_id : ' || P_Bill_to_site_use_id );
105 PA_MCB_INVOICE_PKG.log_message('p_ship_to_site_use_id : ' || P_Ship_to_site_use_id );
106 PA_MCB_INVOICE_PKG.log_message('p_project_customer_id : ' || P_Customer_Id );
107 PA_MCB_INVOICE_PKG.log_message('p_set_of_books_id : ' || P_Sets_of_books_id );
108 PA_MCB_INVOICE_PKG.log_message('p_expenditure_item_id : ' || P_Expenditure_item_id );
109 PA_MCB_INVOICE_PKG.log_message('p_trx_date : ' || trunc(sysdate) );
110 PA_MCB_INVOICE_PKG.log_message('p_event_id : ' || P_Event_id );
111 PA_MCB_INVOICE_PKG.log_message('p_line_type : ' || l_line_type );
112 PA_MCB_INVOICE_PKG.log_message('p_request_id : ' || P_Request_id );
113 PA_MCB_INVOICE_PKG.log_message('p_user_id : ' || P_User_Id );
114 PA_MCB_INVOICE_PKG.log_message('p_tax_classification_code : ' || l_tax_code );
115 PA_MCB_INVOICE_PKG.log_message('p_bill_to_customer_id : ' || Pbill_to_customer_id );
116 PA_MCB_INVOICE_PKG.log_message('p_ship_to_customer_id : ' || Pship_to_customer_id );
117 PA_MCB_INVOICE_PKG.log_message('p_application_id : ' || 275 );
118 PA_MCB_INVOICE_PKG.log_message('p_internal_organization_id: ' || l_org_id );
119 PA_MCB_INVOICE_PKG.log_message('p_draft_inv_num_credited : ' || p_draft_inv_num_credited );
120
121 END IF;
122
123 ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification
124 (
125 p_project_id => P_Project_Id,
126 p_bill_to_site_use_id => P_Bill_to_site_use_id,
127 p_ship_to_site_use_id => P_Ship_to_site_use_id,
128 p_project_customer_id => P_Customer_Id,
129 p_set_of_books_id => P_Sets_of_books_id,
130 p_expenditure_item_id => P_Expenditure_item_id,
131 /*p_trx_date => trunc(sysdate), */
132 p_trx_date => trunc(P_invoice_date), /* Sending trx_date as inv_date for bug 5484859 */
133 p_event_id => P_Event_id,
134 p_line_type => l_line_type,
135 p_request_id => P_Request_id,
136 p_user_id => P_User_Id,
137 p_tax_classification_code => l_tax_code,
138 p_bill_to_customer_id => Pbill_to_customer_id,
139 p_ship_to_customer_id => Pship_to_customer_id,
140 p_application_id => 275,
141 p_internal_organization_id => l_org_id
142 );
143
144 IF g1_debug_mode = 'Y' THEN
145 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' done ZX_AR_TAX_CLASSIFICATN_DEF_PKG' );
146 PA_MCB_INVOICE_PKG.log_message('p_tax_classification_code : ' || l_tax_code );
147 END IF;
148 -- If Tax_code is null , Return from the procedure
149 X_Output_tax_exempt_flag := 'S'; /* added this for bug 7229135*/
150 If l_tax_code IS NULL
151 Then
152 -- X_Output_vat_tax_id := NULL; --commented by hsiu
153 -- X_Output_tax_exempt_flag := 'S'; commented this for bug 7229135
154 X_Output_tax_exempt_number := NULL;
155 X_Output_exempt_reason_code := NULL;
156 Return;
157 ELSE
158 x_output_tax_code := l_tax_code;
159 End If;
160
161 -- Call Exemption API to get the exemption information
162
163 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
164 for customer account relation enhancement bug no 2760630*/
165 /*
166 IF g1_debug_mode = 'Y' THEN
167 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' calling ARP_STAX' );
168 END IF;
169
170
171
172 ARP_STAX.find_tax_exemption_id
173 (
174 bill_to_customer_id => Pbill_to_customer_id,
175 ship_to_customer_id => Pship_to_customer_id,
176 ship_to_site_id => P_Ship_to_site_use_id,
177 tax_code => l_tax_code,
178 inventory_item_id => NULL,
179 tax_exempt_flag => 'S',
180 trx_date => trunc(sysdate),
181 reason_code => l_reason_code,
182 certificate => l_certificate_no,
183 percent_exempt => l_percent_exempt,
184 inserted_flag => l_inserted_flag,
185 exemption_type => l_exemption_type,
186 tax_exemption_id => l_exemption_id);
187 */
188
189 IF g1_debug_mode = 'Y' THEN
190 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' getting bill party info' );
191 END IF;
192
193 select hz_c.party_id, hz_cs.party_site_id, pc.bill_to_address_id
194 into l_bill_to_party_id, l_bill_to_party_site_id, l_bill_to_address_id
195 from hz_cust_accounts hz_c, hz_cust_acct_sites hz_cs, pa_project_customers pc
196 where pc.project_id = p_project_id
197 and pc.customer_id = p_customer_id
198 and hz_cs.cust_acct_site_id = pc.bill_to_address_id
199 and hz_c.cust_account_id = p_customer_id;
200
201 IF g1_debug_mode = 'Y' THEN
202 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'bill partyid :' ||
203 l_bill_to_party_id || ';bill party site id :' ||
204 l_bill_to_party_site_id || ';bill address id :' || l_bill_to_address_id);
205
206 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' getting ship party info' );
207 END IF;
208
209 select hz_c.party_id, hz_cs.party_site_id, pc.ship_to_address_id
210 into l_ship_to_party_id, l_ship_to_party_site_id, l_ship_to_address_id
211 from hz_cust_accounts hz_c, hz_cust_acct_sites hz_cs, pa_project_customers pc
212 where pc.project_id = p_project_id
213 and pc.customer_id = p_customer_id
214 and hz_cs.cust_acct_site_id = pc.ship_to_address_id
215 and hz_c.cust_account_id = p_customer_id;
216
217 IF g1_debug_mode = 'Y' THEN
218 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'ship partyid :' ||
219 l_ship_to_party_id || ';ship party site id :' ||
220 l_ship_to_party_site_id || ';ship address id :' || l_ship_to_address_id);
221 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' calling get_btch_src_trans_type' );
222 END IF;
223
224 get_btch_src_trans_type ( p_project_id => p_project_id,
225 p_draft_invoice_num => p_draft_inv_num,
226 p_draft_inv_num_credited => p_draft_inv_num_credited,
227 x_transaction_type_id => l_transaction_type_id,
228 x_batch_source_id => l_batch_source_id,
229 x_return_status => l_return_status);
230
231 IF g1_debug_mode = 'Y' THEN
232
233 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' done get_btch_src_trans_type' );
234 PA_MCB_INVOICE_PKG.log_message('return status : ' || l_return_status );
235
236 END IF;
237
238 get_legal_entity_id (p_customer_id => nvl(Pbill_to_customer_id, p_customer_id),
239 p_org_id => l_org_id,
240 p_transaction_type_id => l_transaction_type_id,
241 p_batch_source_id => l_batch_source_id,
242 x_legal_entity_id => l_legal_entity_id,
243 x_return_status => l_return_status);
244
245 IF g1_debug_mode = 'Y' THEN
246
247 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' done get_legal_entity_id' );
248 PA_MCB_INVOICE_PKG.log_message('return status : ' || l_return_status );
249
250 END IF;
251
252 IF g1_debug_mode = 'Y' THEN
253
254 PA_MCB_INVOICE_PKG.log_message('=====Parameters for get_default_exemptions====' );
255 PA_MCB_INVOICE_PKG.log_message('p_bill_to_cust_acct_id' || Pbill_to_customer_id );
256 PA_MCB_INVOICE_PKG.log_message('p_ship_to_cust_acct_id' || Pship_to_customer_id );
257 PA_MCB_INVOICE_PKG.log_message('p_ship_to_site_use_id' || P_ship_to_site_use_id );
258 PA_MCB_INVOICE_PKG.log_message('p_bill_to_site_use_id' || P_bill_to_site_use_id );
259 PA_MCB_INVOICE_PKG.log_message('p_bill_to_party_id' || l_bill_to_party_id );
260 PA_MCB_INVOICE_PKG.log_message('p_bill_to_party_site_id' || l_bill_to_party_site_id );
261 PA_MCB_INVOICE_PKG.log_message('p_ship_to_party_site_id' || l_ship_to_party_site_id );
262 PA_MCB_INVOICE_PKG.log_message('p_legal_entity_id' || l_legal_entity_id );
263 PA_MCB_INVOICE_PKG.log_message('p_org_id' || l_org_id );
264 PA_MCB_INVOICE_PKG.log_message('p_trx_date' || trunc(sysdate) );
265 PA_MCB_INVOICE_PKG.log_message('p_exempt_certificate_number' || l_certificate_no );
266 PA_MCB_INVOICE_PKG.log_message('p_reason_code' || l_reason_code );
267
268
269 END IF;
270
271 /* Commenting for bug 7187173 as PA only deals with PRIMARY exemptions (p_exempt_control_flag='S')
272 All other cases of exepmtions will be handled by the ETax code while deriving Tax information
273 in Receivables.
274
275 ZX_TCM_GET_DEF_EXEMPTION.get_default_exemptions
276 (p_bill_to_cust_acct_id => Pbill_to_customer_id,
277 p_ship_to_cust_acct_id => Pship_to_customer_id,
278 p_ship_to_site_use_id => p_ship_to_site_use_id,
279 p_bill_to_site_use_id => p_bill_to_site_use_id,
280 p_bill_to_party_id => l_bill_to_party_id,
281 p_bill_to_party_site_id => l_bill_to_party_site_id,
282 p_ship_to_party_site_id => l_ship_to_party_site_id,
283 p_legal_entity_id => l_legal_entity_id,
284 p_org_id => l_org_id,
285 p_trx_date => trunc(sysdate),
286 p_exempt_certificate_number => l_certificate_no,
287 p_reason_code => l_reason_code,
288 p_exempt_control_flag => 'S',
289 p_inventory_org_id => NULL,
290 p_inventory_item_id => NULL ,
291 x_return_status => l_return_status ,
292 x_exemption_rec_tbl => l1_exemption_rec_tbl);
293
294 IF g1_debug_mode = 'Y' THEN
295
296 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' done get_default_exemptions' );
297 PA_MCB_INVOICE_PKG.log_message('return status : ' || l_return_status );
298 PA_MCB_INVOICE_PKG.log_message('exemption_rec_tbl count:' || l1_exemption_rec_tbl.COUNT );
299
300 END IF;
301
302 IF l1_exemption_rec_tbl.COUNT > 0 THEN
303
304 IF g1_debug_mode = 'Y' THEN
305
306 PA_MCB_INVOICE_PKG.log_message('tax exempt id :' || l1_exemption_rec_tbl(1).tax_exemption_id);
307 PA_MCB_INVOICE_PKG.log_message('exmpt cert no :' || l1_exemption_rec_tbl(1).exempt_certificate_number );
308 PA_MCB_INVOICE_PKG.log_message('exmpt reason code :' || l1_exemption_rec_tbl(1).exempt_reason_code );
309
310 END IF;
311 l_exemption_id := l1_exemption_rec_tbl(1).tax_exemption_id;
312 l_certificate_no := l1_exemption_rec_tbl(1).exempt_certificate_number;
313 l_reason_code := l1_exemption_rec_tbl(1).exempt_reason_code;
314
315 ELSE
316
317 l_exemption_id := NULL;
318
319 END IF;
320 -- Assign Tax Code to output
321
322 -- X_Output_vat_tax_id := l_vat_tax_id; --commented by hsiu
323
324 -- If No exemption exist
325 If l_exemption_id IS NULL
326 Then
327 X_Output_tax_exempt_flag := 'S';
328 X_Output_tax_exempt_number := NULL;
329 X_Output_exempt_reason_code := NULL;
330 Else
331 -- If Exemption exists
332 X_Output_tax_exempt_flag := 'E';
333 X_Output_tax_exempt_number := l_certificate_no;
334 X_Output_exempt_reason_code := l_reason_code;
335 End If;
336 end Commenting for bug 7187173 */
337
338 EXCEPTION
339 When OTHERS Then
340 X_Output_tax_exempt_flag := NULL; --NOCOPY
341 X_Output_tax_exempt_number := NULL; --NOCOPY
342 X_Output_exempt_reason_code := NULL; --NOCOPY
343 X_Output_tax_code := NULL; --NOCOPY
344 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || SQLERRM );
345 Raise;
346 END GET_DEFAULT_TAX_INFO;
347
348 /* Overloaded procedure added for Customer Relationship Management enhancement */
349 PROCEDURE GET_DEFAULT_TAX_INFO
350 ( P_Project_Id IN NUMBER ,
351 P_Draft_Inv_Num IN NUMBER ,
352 P_Customer_Id IN NUMBER ,
353 P_Bill_to_site_use_id IN NUMBER ,
354 P_Ship_to_site_use_id IN NUMBER ,
355 P_Sets_of_books_id IN NUMBER ,
356 P_Event_id IN NUMBER ,
357 P_Expenditure_item_id IN NUMBER ,
358 P_User_Id IN NUMBER ,
359 P_Request_id IN NUMBER ,
360 X_Output_tax_exempt_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
361 X_Output_tax_exempt_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
362 X_Output_exempt_reason_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
363 X_Output_Tax_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
364 P_invoice_date IN DATE default SYSDATE) /* bug 5484859 */
365 Is
366 new_excp exception;
367 Begin
368
369 Raise new_excp;
370
371 End GET_DEFAULT_TAX_INFO;
372
373 /*ADDED OVERLOADED PROCEDURE*/
374
375 PROCEDURE MARK_CUST_REV_DIST_LINES (
376 P_Project_Id IN NUMBER ,
377 P_Draft_Inv_Num IN NUMBER ,
378 P_Customer_Id IN NUMBER ,
379 P_agreement_id IN NUMBER ,
380 P_Bill_to_site_use_id IN NUMBER ,
381 P_Ship_to_site_use_id IN NUMBER ,
382 P_Sets_of_books_id IN NUMBER ,
383 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
384 P_Line_num IN PA_PLSQL_DATATYPES.IdTabTyp,
385 P_User_Id IN NUMBER ,
386 P_Request_id IN NUMBER ,
387 P_No_of_rec IN NUMBER ,
388 X_Rec_upd OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
389 P_bill_trans_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
390 P_bill_trans_invoice_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
391 P_bill_trans_bill_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
392 P_invproc_invoice_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
393 P_invproc_bill_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
394 p_retention_percentage IN VARCHAR2,
395 P_status_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
396 P_invoice_date IN VARCHAR2,
397 x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
398 )
399 IS
400 new_excp exception;
401 Begin
402
403 Raise new_excp;
404
405 END MARK_CUST_REV_DIST_LINES;
406 /*END OF OVERLOADED PROCEDURE*/
407 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
408 for customer account relation enhancement bug no 2760630*/
409 PROCEDURE MARK_CUST_REV_DIST_LINES (
410 P_Project_Id IN NUMBER ,
411 P_Draft_Inv_Num IN NUMBER ,
412 P_Customer_Id IN NUMBER ,
413 P_agreement_id IN NUMBER ,
414 P_Bill_to_site_use_id IN NUMBER ,
415 P_Ship_to_site_use_id IN NUMBER ,
416 P_Sets_of_books_id IN NUMBER ,
417 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp,
418 P_Line_num IN PA_PLSQL_DATATYPES.IdTabTyp,
419 P_User_Id IN NUMBER ,
420 P_Request_id IN NUMBER ,
421 P_No_of_rec IN NUMBER ,
422 X_Rec_upd OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
423 P_bill_trans_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
424 P_bill_trans_invoice_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
425 P_bill_trans_bill_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
426 P_invproc_invoice_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
427 P_invproc_bill_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
428 p_retention_percentage IN VARCHAR2,
429 P_status_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
430 P_invoice_date IN VARCHAR2,
431 Pbill_to_customer_id IN NUMBER,
432 Pship_to_customer_id IN NUMBER,
433 P_shared_funds_consumption IN NUMBER, /* Federal */
434 P_expenditure_item_date IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Federal */
435 x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
436 )
437 IS
438 loop_index NUMBER;
439 l_count NUMBER := 0;
440 l_Output_vat_tax_id NUMBER;
441 l_output_tax_code VARCHAR2(30); --added by hsiu for eTax changes
442 l_Output_tax_exempt_flag VARCHAR2(1);
443 --Modified for Bug3128094
444 l_Output_tax_exempt_number VARCHAR2(80);
445 l_Output_exempt_reason_code VARCHAR2(30);
446 --till here for Bug3128094
447
448 -- MCB Related Variables
449
450 l_multi_currency_billing_flag VARCHAR2(1);
451 l_baseline_funding_flag VARCHAR2(1);
452 l_revproc_currency_code VARCHAR2(30);
453 l_invproc_currency_code VARCHAR2(30);
454 l_project_currency_code VARCHAR2(30);
455 l_project_rate_date_code VARCHAR2(30);
456 l_project_rate_type VARCHAR2(30);
457 l_project_rate_date DATE;
458 l_project_exchange_rate NUMBER;
459 l_projfunc_currency_code VARCHAR2(30);
460 l_projfunc_rate_date_code VARCHAR2(30);
461 l_projfunc_rate_type VARCHAR2(30);
462 l_projfunc_rate_date DATE;
463 l_projfunc_exchange_rate NUMBER;
464 l_funding_rate_date_code VARCHAR2(30);
465 l_funding_rate_type VARCHAR2(30);
466 l_funding_rate_date DATE;
467 l_funding_exchange_rate NUMBER;
468 l_funding_currency_code VARCHAR2(30);
469 l_return_status VARCHAR2(30);
470 l_msg_count NUMBER;
471 l_msg_data VARCHAR2(240);
472
473
474 tmp_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
475 tmp_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
476 tmp_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
477 tmp_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
478
479 tmp_status_project_tab PA_PLSQL_DATATYPES.Char30TabTyp;
480 tmp_status_projfunc_tab PA_PLSQL_DATATYPES.Char30TabTyp;
481 tmp_status_funding_tab PA_PLSQL_DATATYPES.Char30TabTyp;
482 tmp_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
483
484 tmp_project_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
485 tmp_projfunc_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
486 tmp_funding_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
487 tmp_invproc_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
488 tmp_bill_trans_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
489
490 tmp_project_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
491 tmp_projfunc_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
492 tmp_funding_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
493 tmp_invproc_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
494
495 tmp_project_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
496 tmp_projfunc_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
497 tmp_invproc_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
498 tmp_funding_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
499
500 tmp_project_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
501 tmp_projfunc_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
502 tmp_funding_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
503 tmp_invproc_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
504
505 tmp_project_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
506 tmp_funding_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
507 tmp_projfunc_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
508 tmp_invproc_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
509 tmp_invproc_currency_type VARCHAR2(30);
510
511 tmp_invoice_eligible_flag VARCHAR2(1) :='Y';
512
513 l_request_id NUMBER:= fnd_global.conc_request_id;
514 l_program_id NUMBER:= fnd_global.conc_program_id;
515 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
516 l_program_update_date DATE := sysdate;
517 l_retention_percentage NUMBER:= NVL(TO_NUMBER(p_retention_percentage),0);
518 l_err_count NUMBER := 0;
519
520
521 /* Federal Changes */
522
523 l_agreement_start_date DATE;
524 l_agreement_exp_date DATE;
525 tmp_expenditure_item_date PA_PLSQL_DATATYPES.DateTabTyp;
526
527 /* Added for Payroll Billing ER #11847630 - Start */
528
529 l_reversal_flag VARCHAR2(1) := 'N';
530
531 CURSOR c_reversal_rdl (p_exp_item_id number,
532 p_line number) IS
533 select b.expenditure_item_id, b.line_num
534 from PA_CUST_REV_DIST_LINES_ALL a,
535 PA_CUST_REV_DIST_LINES_ALL b
536 where a.expenditure_item_id = p_exp_item_id
537 and a.line_num = p_line
538 and a.line_num_reversed is not null
539 and a.expenditure_item_id = b.expenditure_item_id
540 and a.line_num_reversed = b.line_num ;
541
542 /* CURSOR c_reversal_ei (p_exp_item_id number) IS
543 select adjusted_expenditure_item_id
544 from pa_expenditure_items_all e1
545 where e1.expenditure_item_id = p_Exp_item_id; */
546
547 CURSOR c_reversal_ei (p_exp_item_id number) IS
548 select b.expenditure_item_id, max(b.line_num ) line_num
549 from pa_expenditure_items_all e1,
550 pa_expenditure_items_all e2,
551 PA_CUST_REV_DIST_LINES_ALL b
552 where e1.expenditure_item_id = p_exp_item_id
553 and e1.adjusted_expenditure_item_id is not null
554 and e1.adjusted_expenditure_item_id = e2.expenditure_item_id
555 and e2.expenditure_item_id = b.expenditure_item_id
556 group by b.expenditure_item_id;
557
558 CURSOR c_orig_amount (p_exp_item_id number,
559 p_line number) IS
560 SELECT project_inv_rate_type,
561 project_inv_rate_date,
562 project_inv_exchange_rate,
563 -project_bill_amount,
564 projfunc_inv_rate_type,
565 projfunc_inv_rate_date,
566 projfunc_inv_exchange_rate,
567 -projfunc_bill_amount,
568 invproc_rate_type,
569 invproc_rate_date,
570 invproc_exchange_rate,
571 -bill_amount,
572 funding_inv_rate_type,
573 funding_inv_rate_date,
574 funding_inv_exchange_rate,
575 -funding_bill_amount,
576 project_currency_code,
577 projfunc_currency_code,
578 invproc_currency_code,
579 funding_currency_code,
580 output_tax_classification_code,
581 output_tax_exempt_flag,
582 output_tax_exempt_reason_code,
583 output_tax_exempt_number
584 FROM PA_CUST_REV_DIST_LINES_ALL
585 WHERE EXPENDITURE_ITEM_ID = p_exp_item_id
586 AND LINE_NUM = p_line;
587
588 l_exp_item_id NUMBER := NULL;
589 l_line_num NUMBER := NULL;
590
591 /* Added for Payroll Billing ER #11847630 - End */
592
593 BEGIN
594
595 -- Multicurrency Related Changes
596 -- Convert the Bill Transaction Curreny to IPC
597
598 -- Get the Project level defaults
599 IF g1_debug_mode = 'Y' THEN
600 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Inside MARK_CUST_REV_DIST_LINES');
601 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Call PA_MULTI_CURRENCY_BILLING.get_project_defaults');
602 END IF;
603
604 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
605 p_project_id => p_project_id,
606 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
607 x_baseline_funding_flag => l_baseline_funding_flag,
608 x_revproc_currency_code => l_revproc_currency_code,
609 x_invproc_currency_type => tmp_invproc_currency_type,
610 x_invproc_currency_code => l_invproc_currency_code,
611 x_project_currency_code => l_project_currency_code,
612 x_project_bil_rate_date_code => l_project_rate_date_code,
613 x_project_bil_rate_type => l_project_rate_type,
614 x_project_bil_rate_date => l_project_rate_date,
615 x_project_bil_exchange_rate => l_project_exchange_rate,
616 x_projfunc_currency_code => l_projfunc_currency_code,
617 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
618 x_projfunc_bil_rate_type => l_projfunc_rate_type,
619 x_projfunc_bil_rate_date => l_projfunc_rate_date,
620 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
621 x_funding_rate_date_code => l_funding_rate_date_code,
622 x_funding_rate_type => l_funding_rate_type,
623 x_funding_rate_date => l_funding_rate_date,
624 x_funding_exchange_rate => l_funding_exchange_rate,
625 x_return_status => l_return_status,
626 x_msg_count => l_msg_count,
627 x_msg_data => l_msg_data);
628
629 IF l_funding_currency_code IS NULL THEN
630
631 IF g1_debug_mode = 'Y' THEN
632 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Get Funding Currency Code ');
633 END IF;
634
635 BEGIN
636 PA_MCB_INVOICE_PKG.log_message('INVOICE DATE : ' || 'P_invoice_date : ' || TO_DATE(P_invoice_date,'YYYY/MM/DD'));
637
638 /* Federal Changes : Added start date and expiry date */
639 /* Modified the expiration_date to use Invoice date, instead of sysdate for Bug#14116344 */
640 SELECT agreement_currency_code,
641 nvl(start_date,to_date('01/01/1952', 'DD/MM/YYYY')),
642 nvl(expiration_date,TO_DATE(P_invoice_date,'YYYY/MM/DD'))
643 INTO l_funding_currency_code,
644 l_agreement_start_date, l_agreement_exp_date
645 FROM PA_AGREEMENTS_ALL
646 WHERE agreement_id = p_agreement_id;
647
648 IF g1_debug_mode = 'Y' THEN
649 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Funding Currency Code : ' || l_funding_currency_code);
650 END IF;
651
652 EXCEPTION
653
654 WHEN NO_DATA_FOUND THEN
655 IF g1_debug_mode = 'Y' THEN
656 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Oracle Error NO DATA FOUND');
657 END IF;
658 RAISE ;
659 END ;
660
661 END IF;
662
663
664 IF g1_debug_mode = 'Y' THEN
665 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Loop Begins for ' || P_No_of_rec);
666 END IF;
667
668 FOR loop_index in 1..P_No_of_rec LOOP
669
670 /* Added for Payroll Billing ER #11847630 - Start */
671
672 IF g1_debug_mode = 'Y' THEN
673 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' G_NET_INVOICE_ENABLED= ' || G_NET_INVOICE_ENABLED);
674 END IF;
675
676 l_reversal_flag := 'N';
677 If ( NVL(G_NET_INVOICE_ENABLED,'N') = 'Y' ) THEN /* Specs global variable */
678
679 -- This new variable should be populated by the pro*C code at the time
680 -- of main funding level cursor. So that the below new code is not
681 -- executed for all the customers and all projects where this
682 -- functionality is not enabled.
683
684 l_exp_item_id := null;
685
686 IF g1_debug_mode = 'Y' THEN
687 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' P_Expenditure_item_id(' || loop_index || ')=' || P_Expenditure_item_id(loop_index) || ' P_Line_num(' || loop_index || ')=' || P_Line_num(loop_index));
688 END IF;
689
690 OPEN c_reversal_rdl(P_Expenditure_item_id(loop_index),
691 P_Line_num(loop_index));
692 FETCH c_reversal_rdl
693 INTO l_exp_item_id, l_line_num;
694 CLOSE c_reversal_rdl;
695
696 IF g1_debug_mode = 'Y' THEN
697 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_exp_item_id=' || l_exp_item_id || ' l_line_num=' || l_line_num);
698 END IF;
699
700 IF (l_exp_item_id is null) THEN
701
702 OPEN c_reversal_ei(P_Expenditure_item_id(loop_index));
703 FETCH c_reversal_ei
704 INTO l_exp_item_id, l_line_num;
705 CLOSE c_reversal_ei;
706
707 IF g1_debug_mode = 'Y' THEN
708 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_exp_item_id=' || l_exp_item_id || ' l_line_num=' || l_line_num);
709 END IF;
710
711 END IF;
712
713 IF (l_exp_item_id is not null) THEN
714
715 tmp_status_tab(loop_index) := 'N';
716
717 IF g1_debug_mode = 'Y' THEN
718 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Opeinign cursor c_orig_amount with l_exp_item_id=' || l_exp_item_id || ' l_line_num=' || l_line_num);
719 END IF;
720
721 OPEN c_orig_amount(l_exp_item_id, l_line_num);
722
723 FETCH c_orig_amount
724 INTO tmp_project_rate_type(loop_index),
725 tmp_project_rate_date(loop_index),
726 tmp_project_exchange_rate(loop_index),
727 tmp_project_bill_amount(loop_index),
728 tmp_projfunc_rate_type(loop_index),
729 tmp_projfunc_rate_date(loop_index),
730 tmp_projfunc_exchange_rate(loop_index),
731 tmp_projfunc_bill_amount(loop_index),
732 tmp_invproc_rate_type(loop_index),
733 tmp_invproc_rate_date(loop_index),
734 tmp_invproc_exchange_rate(loop_index),
735 tmp_invproc_bill_amount(loop_index),
736 tmp_funding_rate_type(loop_index),
737 tmp_funding_rate_date(loop_index),
738 tmp_funding_exchange_rate(loop_index),
739 tmp_funding_bill_amount(loop_index),
740 tmp_project_currency_code(loop_index),
741 tmp_projfunc_currency_code(loop_index),
742 tmp_invproc_currency_code(loop_index),
743 tmp_funding_currency_code(loop_index),
744 l_output_tax_code,
745 l_Output_tax_exempt_flag,
746 l_Output_exempt_reason_code,
747 l_Output_tax_exempt_number;
748
749 CLOSE c_orig_amount;
750 tmp_denominator_tab(loop_index) :=0;
751 tmp_numerator_tab(loop_index) :=0;
752 tmp_rate_tab(loop_index) :=0;
753 tmp_user_validate_flag_tab(loop_index):='N';
754 tmp_status_project_tab(loop_index) :='N';
755 tmp_status_projfunc_tab(loop_index) :='N';
756 tmp_status_funding_tab(loop_index) := 'N';
757 tmp_status_tab(loop_index) := 'N';
758 tmp_bill_trans_bill_amount(loop_index):= TO_NUMBER(p_bill_trans_invoice_amount(loop_index));
759 tmp_expenditure_item_date(loop_index) := TO_DATE(p_expenditure_item_date(loop_index),'YYYY/MM/DD'); /* Federal Changes */
760
761
762 IF g1_debug_mode = 'Y' THEN
763 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_project_rate_type(' || loop_index || ')=' || tmp_project_rate_type(loop_index));
764 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_project_rate_date(' || loop_index || ')=' || tmp_project_rate_date(loop_index));
765 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_project_exchange_rate(' || loop_index || ')=' || tmp_project_exchange_rate(loop_index));
766 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_project_bill_amount(' || loop_index || ')=' || tmp_project_bill_amount(loop_index));
767 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_projfunc_rate_type(' || loop_index || ')=' || tmp_projfunc_rate_type(loop_index));
768 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_projfunc_rate_date(' || loop_index || ')=' || tmp_projfunc_rate_date(loop_index));
769 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_projfunc_exchange_rate(' || loop_index || ')=' || tmp_projfunc_exchange_rate(loop_index));
770 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_projfunc_bill_amount(' || loop_index || ')=' || tmp_projfunc_bill_amount(loop_index));
771 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_invproc_rate_type(' || loop_index || ')=' || tmp_invproc_rate_type(loop_index));
772 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_invproc_rate_date(' || loop_index || ')=' || tmp_invproc_rate_date(loop_index));
773 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_invproc_exchange_rate(' || loop_index || ')=' || tmp_invproc_exchange_rate(loop_index));
774 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_invproc_bill_amount(' || loop_index || ')=' || tmp_invproc_bill_amount(loop_index));
775 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_funding_rate_type(' || loop_index || ')=' || tmp_funding_rate_type(loop_index));
776 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_funding_rate_date(' || loop_index || ')=' || tmp_funding_rate_date(loop_index));
777 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_funding_exchange_rate(' || loop_index || ')=' || tmp_funding_exchange_rate(loop_index));
778 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_funding_bill_amount(' || loop_index || ')=' || tmp_funding_bill_amount(loop_index));
779 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_project_currency_code(' || loop_index || ')=' || tmp_project_currency_code(loop_index));
780 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_projfunc_currency_code(' || loop_index || ')=' || tmp_projfunc_currency_code(loop_index));
781 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_invproc_currency_code(' || loop_index || ')=' || tmp_invproc_currency_code(loop_index));
782 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' tmp_funding_currency_code(' || loop_index || ')=' || tmp_funding_currency_code(loop_index));
783 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_output_tax_code=' || l_output_tax_code);
784 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_Output_tax_exempt_flag=' || l_Output_tax_exempt_flag);
785 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_Output_exempt_reason_code=' || l_Output_exempt_reason_code);
786 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_Output_tax_exempt_number=' || l_Output_tax_exempt_number);
787 END IF;
788
789 l_reversal_flag := 'Y';
790 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_reversal_flag=' || l_reversal_flag);
791 END IF;
792
793 End If; /* G_NET_INVOICE_ENABLED */
794
795 IF g1_debug_mode = 'Y' THEN
796 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' l_reversal_flag=' || l_reversal_flag);
797 END IF;
798
799 /* Added for Payroll Billing ER #11847630 - End */
800
801 If l_reversal_flag = 'N' then /* uncommented for bug Bug 12584272 */
802
803 tmp_project_currency_code(loop_index) := l_project_currency_code;
804 tmp_project_rate_type(loop_index) := l_project_rate_type;
805 tmp_project_exchange_rate(loop_index) := l_project_exchange_rate;
806
807 IF l_project_rate_date_code ='FIXED' THEN
808 tmp_project_rate_date(loop_index) := l_project_rate_date;
809 ELSE
810 if l_project_rate_date IS NULL THEN
811 tmp_project_rate_date(loop_index) := TO_DATE(p_invoice_date,'YYYY/MM/DD');
812 else
813 tmp_project_rate_date(loop_index) := l_project_rate_date;
814 end if;
815 END IF;
816
817
818 tmp_projfunc_currency_code(loop_index) := l_projfunc_currency_code;
819 tmp_projfunc_rate_type(loop_index) := l_projfunc_rate_type;
820 tmp_projfunc_exchange_rate(loop_index) := l_projfunc_exchange_rate;
821
822 IF l_projfunc_rate_date_code ='FIXED' THEN
823 tmp_projfunc_rate_date(loop_index) := l_projfunc_rate_date;
824 ELSE
825 if l_projfunc_rate_date IS NULL THEN
826 tmp_projfunc_rate_date(loop_index) := TO_DATE(p_invoice_date,'YYYY/MM/DD');
827 else
828 tmp_projfunc_rate_date(loop_index) := l_projfunc_rate_date;
829 end if;
830 END IF;
831
832
833 tmp_funding_currency_code(loop_index) := l_funding_currency_code;
834 tmp_funding_rate_type(loop_index) := l_funding_rate_type;
835 tmp_funding_exchange_rate(loop_index) := l_funding_exchange_rate;
836
837 IF l_funding_rate_date_code ='FIXED' THEN
838 tmp_funding_rate_date(loop_index) := l_funding_rate_date;
839 ELSE
840 if l_funding_rate_date IS NULL THEN
841 tmp_funding_rate_date(loop_index) := TO_DATE(p_invoice_date,'YYYY/MM/DD');
842 else
843 tmp_funding_rate_date(loop_index) := l_funding_rate_date;
844 end if;
845 END IF;
846
847
848 tmp_denominator_tab(loop_index) :=0;
849 tmp_numerator_tab(loop_index) :=0;
850 tmp_rate_tab(loop_index) :=0;
851 tmp_user_validate_flag_tab(loop_index):='N';
852 tmp_status_project_tab(loop_index) :='N';
853 tmp_status_projfunc_tab(loop_index) :='N';
854 tmp_status_funding_tab(loop_index) := 'N';
855 tmp_status_tab(loop_index) := 'N';
856
857 tmp_project_bill_amount(loop_index) :=0;
858 tmp_projfunc_bill_amount(loop_index) :=0;
859 tmp_invproc_bill_amount(loop_index) :=0;
860 tmp_funding_bill_amount(loop_index) :=0;
861 tmp_bill_trans_bill_amount(loop_index):= TO_NUMBER(p_bill_trans_invoice_amount(loop_index));
862 tmp_expenditure_item_date(loop_index) := TO_DATE(p_expenditure_item_date(loop_index),'YYYY/MM/DD'); /* Federal Changes */
863
864
865 IF tmp_invproc_currency_type = 'FUNDING_CURRENCY' THEN
866 tmp_invproc_rate_type(loop_index) := tmp_funding_rate_type(loop_index);
867 tmp_invproc_rate_date(loop_index) := tmp_funding_rate_date(loop_index);
868 tmp_invproc_exchange_rate(loop_index) := tmp_funding_exchange_rate(loop_index);
869 tmp_invproc_currency_code(loop_index) := tmp_funding_currency_code(loop_index);
870 ELSIF tmp_invproc_currency_type = 'PROJECT_CURRENCY' THEN
871 tmp_invproc_rate_type(loop_index) := tmp_project_rate_type(loop_index);
872 tmp_invproc_rate_date(loop_index) := tmp_project_rate_date(loop_index);
873 tmp_invproc_exchange_rate(loop_index) := tmp_project_exchange_rate(loop_index);
874 tmp_invproc_currency_code(loop_index) := tmp_project_currency_code(loop_index);
875 ELSIF tmp_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
876 tmp_invproc_rate_type(loop_index) := tmp_projfunc_rate_type(loop_index);
877 tmp_invproc_rate_date(loop_index) := tmp_projfunc_rate_date(loop_index);
878 tmp_invproc_exchange_rate(loop_index) := tmp_projfunc_exchange_rate(loop_index);
879 tmp_invproc_currency_code(loop_index) := tmp_projfunc_currency_code(loop_index);
880 END IF;
881
882 /* Added for Payroll Billing ER #11847630 - Start */
883 end if; -- l_reversal_flag if Bug 12584272
884 /* Added for Payroll Billing ER #11847630 - End */
885
886
887 END LOOP;
888
889 /* Added for Payroll Billing ER #11847630 - Start */
890
891 --IF (l_reversal_flag = 'N') THEN
892
893 /* Added for Payroll Billing ER #11847630 - End */
894
895 IF p_bill_trans_currency_code.COUNT <> 0 THEN
896
897 -- Convert the bill transaction currecy to PFC
898 IF g1_debug_mode = 'Y' THEN
899 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Conversion CALL for BTC to PF ');
900 END IF;
901
902 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
903 p_from_currency_tab => p_bill_trans_currency_code,
904 p_to_currency_tab => tmp_projfunc_currency_code,
905 p_conversion_date_tab => tmp_projfunc_rate_date,
906 p_conversion_type_tab => tmp_projfunc_rate_type,
907 p_amount_tab => tmp_bill_trans_bill_amount,
908 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
909 p_converted_amount_tab => tmp_projfunc_bill_amount,
910 p_denominator_tab => tmp_denominator_tab,
911 p_numerator_tab => tmp_numerator_tab,
912 p_rate_tab => tmp_projfunc_exchange_rate,
913 x_status_tab => tmp_status_projfunc_tab,
914 p_conversion_between => 'BTC_PF',
915 p_cache_flag =>'Y');
916
917 IF l_project_currency_code = l_projfunc_currency_code then
918
919 IF g1_debug_mode = 'Y' THEN
920 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Proj curr = Proj func currency ..Copy ' );
921 END IF;
922
923 FOR i IN tmp_status_projfunc_tab.FIRST..tmp_status_projfunc_tab.LAST LOOP
924
925 tmp_project_rate_date(i) := tmp_projfunc_rate_date(i);
926 tmp_project_rate_type(i) := tmp_projfunc_rate_type(i);
927 tmp_project_bill_amount(i) := tmp_projfunc_bill_amount(i);
928 tmp_project_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
929 tmp_status_project_tab(i) := tmp_status_projfunc_tab(i);
930
931 END LOOP;
932
933 else
934
935 -- Convert the bill transaction currecy to PC
936 IF g1_debug_mode = 'Y' THEN
937 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Conversion CALL for BTC to PC ');
938 END IF;
939
940 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
941 p_from_currency_tab => p_bill_trans_currency_code,
942 p_to_currency_tab => tmp_project_currency_code,
943 p_conversion_date_tab => tmp_project_rate_date,
944 p_conversion_type_tab => tmp_project_rate_type,
945 p_amount_tab => tmp_bill_trans_bill_amount,
946 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
947 p_converted_amount_tab => tmp_project_bill_amount,
948 p_denominator_tab => tmp_denominator_tab,
949 p_numerator_tab => tmp_numerator_tab,
950 p_rate_tab => tmp_project_exchange_rate,
951 x_status_tab => tmp_status_project_tab,
952 p_conversion_between => 'BTC_PC',
953 p_cache_flag =>'Y');
954 end if;
955
956 IF l_funding_currency_code = l_projfunc_currency_code then
957
958 IF g1_debug_mode = 'Y' THEN
959 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Fund curr = Proj func currency ..Copy ' );
960 END IF;
961
962 FOR i IN tmp_status_projfunc_tab.FIRST..tmp_status_projfunc_tab.LAST LOOP
963
964 tmp_funding_rate_date(i) := tmp_projfunc_rate_date(i);
965 tmp_funding_rate_type(i) := tmp_projfunc_rate_type(i);
966 tmp_funding_bill_amount(i) := tmp_projfunc_bill_amount(i);
967 tmp_funding_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
968 tmp_status_funding_tab(i) := tmp_status_projfunc_tab(i);
969
970 END LOOP;
971
972 elsif l_funding_currency_code = l_project_currency_code then
973
974 IF g1_debug_mode = 'Y' THEN
975 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'funding curr = Proj currency ..Copy ' );
976 END IF;
977
978 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
979
980 tmp_funding_rate_date(i) := tmp_project_rate_date(i);
981 tmp_funding_rate_type(i) := tmp_project_rate_type(i);
982 tmp_funding_bill_amount(i) := tmp_project_bill_amount(i);
983 tmp_funding_exchange_rate(i) := tmp_project_exchange_rate(i);
984 tmp_status_funding_tab(i) := tmp_status_project_tab(i);
985
986 END LOOP;
987
988 else
989
990 IF g1_debug_mode = 'Y' THEN
991 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Conversion CALL for BTC to FC ');
992 END IF;
993
994 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
995 p_from_currency_tab => p_bill_trans_currency_code,
996 p_to_currency_tab => tmp_funding_currency_code,
997 p_conversion_date_tab => tmp_funding_rate_date,
998 p_conversion_type_tab => tmp_funding_rate_type,
999 p_amount_tab => tmp_bill_trans_bill_amount,
1000 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1001 p_converted_amount_tab => tmp_funding_bill_amount,
1002 p_denominator_tab => tmp_denominator_tab,
1003 p_numerator_tab => tmp_numerator_tab,
1004 p_rate_tab => tmp_funding_exchange_rate,
1005 x_status_tab => tmp_status_funding_tab,
1006 p_conversion_between => 'BTC_FC',
1007 p_cache_flag =>'Y');
1008
1009 tmp_denominator_tab.delete;
1010 tmp_numerator_tab.delete;
1011 end if;
1012
1013 /* Set the Invoice Processing Currency */
1014
1015 IF tmp_invproc_currency_type = 'FUNDING_CURRENCY' THEN
1016
1017
1018 IF g1_debug_mode = 'Y' THEN
1019 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' invcurr = fund curr');
1020 END IF;
1021 FOR i IN 1..tmp_status_project_tab.COUNT LOOP
1022
1023 tmp_invproc_bill_amount(i) := tmp_funding_bill_amount(i);
1024 tmp_invproc_exchange_rate(i) := tmp_funding_exchange_rate(i);
1025 tmp_invproc_rate_date(i) := tmp_funding_rate_date(i);
1026 tmp_invproc_rate_type(i) := tmp_funding_rate_type(i);
1027 tmp_invproc_currency_code(i) := tmp_funding_currency_code(i);
1028
1029 END LOOP;
1030
1031 ELSIF tmp_invproc_currency_type = 'PROJECT_CURRENCY' THEN
1032
1033 -- Invoice Processing is PC
1034 -- Move the Project Currency Amount and attributes to Invoice Processing
1035
1036 IF g1_debug_mode = 'Y' THEN
1037 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' invcurr = proj curr');
1038 END IF;
1039 FOR i IN 1..tmp_status_project_tab.COUNT LOOP
1040
1041 tmp_invproc_bill_amount(i) := tmp_project_bill_amount(i);
1042 tmp_invproc_exchange_rate(i) := tmp_project_exchange_rate(i);
1043 tmp_invproc_rate_date(i) := tmp_project_rate_date(i);
1044 tmp_invproc_rate_type(i) := tmp_project_rate_type(i);
1045 tmp_invproc_currency_code(i) := tmp_project_currency_code(i);
1046
1047 END LOOP;
1048
1049 ELSIF tmp_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
1050
1051 -- Invoice Processing is PFC
1052 -- Move the Project Functional Currency Amount and attributes to Invoice Processing
1053
1054 IF g1_debug_mode = 'Y' THEN
1055 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' invcurr = projfunc curr');
1056 END IF;
1057 FOR i IN 1..tmp_status_project_tab.COUNT LOOP
1058
1059 tmp_invproc_bill_amount(i) := tmp_projfunc_bill_amount(i);
1060 tmp_invproc_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
1061 tmp_invproc_rate_date(i) := tmp_projfunc_rate_date(i);
1062 tmp_invproc_rate_type(i) := tmp_projfunc_rate_type(i);
1063 tmp_invproc_currency_code(i) := tmp_projfunc_currency_code(i);
1064
1065 END LOOP;
1066
1067 END IF;
1068
1069 -- Set the Status code array
1070
1071 IF g1_debug_mode = 'Y' THEN
1072 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' setting status tab');
1073 END IF;
1074 FOR i IN 1..tmp_status_project_tab.COUNT LOOP
1075
1076 tmp_status_tab(i) := 'N';
1077 /*
1078 IF NVL(tmp_status_project_tab(i),'N') <> 'N' THEN
1079 tmp_status_tab(i):= 'BTC_PROJ'|| tmp_status_project_tab(i);
1080 ELSIF NVL(tmp_status_projfunc_tab(i),'N') <> 'N' THEN
1081 tmp_status_tab(i):= 'BTC_PROJFUNC_'|| tmp_status_projfunc_tab(i);
1082 ELSIF NVL(tmp_status_funding_tab(i),'N') <> 'N' THEN
1083 tmp_status_tab(i):= 'BTC_FUNDING'|| tmp_status_funding_tab(i);
1084 END IF;
1085 */
1086 IF g1_debug_mode = 'Y' THEN
1087 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'proj' || tmp_status_project_tab(i));
1088 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'projfunc' || tmp_status_projfunc_tab(i));
1089 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'funding' || tmp_status_funding_tab(i));
1090 END IF;
1091
1092
1093 /* Federal changes : begin */
1094 IF ((p_shared_funds_consumption = 1) AND
1095 (( tmp_expenditure_item_date(i) < l_agreement_start_date) OR
1096 ( tmp_expenditure_item_date(i) > l_agreement_exp_date))) THEN
1097 tmp_status_tab(i):= 'PA_EI_AGR_DATE_MISMATCH';
1098 l_err_count := l_err_count + 1;
1099 /* Federal Changes : End */
1100 ELSIF NVL(tmp_status_project_tab(i),'N') <> 'N' THEN
1101 tmp_status_tab(i):= tmp_status_project_tab(i);
1102 l_err_count := l_err_count + 1;
1103 ELSIF NVL(tmp_status_projfunc_tab(i),'N') <> 'N' THEN
1104 tmp_status_tab(i):= tmp_status_projfunc_tab(i);
1105 l_err_count := l_err_count + 1;
1106 ELSIF NVL(tmp_status_funding_tab(i),'N') <> 'N' THEN
1107 tmp_status_tab(i):= tmp_status_funding_tab(i);
1108 l_err_count := l_err_count + 1;
1109 END IF;
1110
1111 END LOOP;
1112
1113 END IF; /* Process only if the array has records */
1114
1115 -- End of MCB changes
1116
1117 IF g1_debug_mode = 'Y' THEN
1118 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' calling get default tax info ');
1119 END IF;
1120
1121 /* Added for Payroll Billing ER #11847630 - Start */
1122
1123 --END IF; -- l_reversal_flag if statement
1124
1125 /* Added for Payroll Billing ER #11847630 - End */
1126
1127 FOR loop_index in 1..P_No_of_rec LOOP
1128
1129 -- Call Tax API to get the tax Id and Related attribute
1130
1131 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
1132 for customer account relation enhancement bug no 2760630*/
1133
1134 /* Added for Payroll Billing ER #11847630 - Start */
1135 if l_reversal_flag = 'N' then
1136 /* Added for Payroll Billing ER #11847630 - End */
1137
1138 PA_OUTPUT_TAX.GET_DEFAULT_TAX_INFO
1139 ( P_Project_Id => P_Project_Id ,
1140 P_Draft_Inv_Num => P_Draft_Inv_Num ,
1141 P_Customer_Id => P_Customer_Id,
1142 P_Bill_to_site_use_id => P_Bill_to_site_use_id,
1143 P_Ship_to_site_use_id => P_Ship_to_site_use_id,
1144 P_Sets_of_books_id => P_Sets_of_books_id,
1145 P_Event_id => NULL,
1146 P_Expenditure_item_id => P_Expenditure_item_id(loop_index),
1147 P_User_Id => P_User_Id,
1148 P_Request_id => P_Request_id,
1149 -- X_Output_vat_tax_id => l_Output_vat_tax_id, --commented by hsiu
1150 X_output_tax_code => l_output_tax_code,
1151 X_Output_tax_exempt_flag => l_Output_tax_exempt_flag,
1152 X_Output_tax_exempt_number => l_Output_tax_exempt_number,
1153 X_Output_exempt_reason_code => l_Output_exempt_reason_code,
1154 Pbill_to_customer_id => Pbill_to_customer_id,
1155 Pship_to_customer_id => Pship_to_customer_id,
1156 P_invoice_date => to_date(P_invoice_date,'YYYY/MM/DD')); /* passing invoice date for bug 5484859 */
1157
1158
1159 tmp_invoice_eligible_flag :='Y';
1160
1161 IF tmp_status_tab(loop_index) <> 'N' THEN
1162
1163 tmp_invoice_eligible_flag := 'N';
1164
1165 END IF;
1166
1167 -- Update the expenditure items
1168
1169 IF g1_debug_mode = 'Y' THEN
1170 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Update RDL and EI');
1171 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Expenditure Item ID : ' || P_Expenditure_item_id(loop_index) );
1172 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Funding Amount : ' || tmp_funding_bill_amount(loop_index) );
1173 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Project Amount : ' || tmp_project_bill_amount(loop_index) );
1174 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' ProjFunc Amount : ' || tmp_projfunc_bill_amount(loop_index) );
1175 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' Status Code : ' || tmp_status_tab(loop_index) );
1176 END IF;
1177
1178 /* Added for Payroll Billing ER #11847630 - Start */
1179 end if ; -- l_reversal_flag if statement
1180 /* Added for Payroll Billing ER #11847630 - End */
1181
1182 Update PA_CUST_REV_DIST_LINES_ALL
1183 Set
1184 -- invoice_eligible_flag = tmp_invoice_eligible_flag, for bug 2649243, 2645634
1185 -- output_vat_tax_id = l_Output_vat_tax_id, --commented by hsiu
1186 output_tax_classification_code = l_output_tax_code,
1187 output_tax_exempt_flag = l_Output_tax_exempt_flag,
1188 output_tax_exempt_reason_code = l_Output_exempt_reason_code,
1189 output_tax_exempt_number = l_Output_tax_exempt_number,
1190 project_inv_rate_type =
1191 DECODE(tmp_status_tab(loop_index),'N',
1192 tmp_project_rate_type(loop_index), NULL),
1193 project_inv_rate_date =
1194 DECODE(tmp_status_tab(loop_index),'N',
1195 --tmp_project_rate_date(loop_index), NULL), --Modified for Bug 3137196
1196 decode(tmp_project_rate_type(loop_index), 'User', null, tmp_project_rate_date(loop_index)), NULL),
1197 project_inv_exchange_rate =
1198 DECODE(tmp_status_tab(loop_index),'N',
1199 tmp_project_exchange_rate(loop_index), NULL),
1200 project_bill_amount =
1201 DECODE(tmp_status_tab(loop_index),'N',
1202 tmp_project_bill_amount(loop_index), NULL),
1203 projfunc_inv_rate_type =
1204 DECODE(tmp_status_tab(loop_index),'N',
1205 tmp_projfunc_rate_type(loop_index), NULL),
1206 projfunc_inv_rate_date =
1207 DECODE(tmp_status_tab(loop_index),'N',
1208 --tmp_projfunc_rate_date(loop_index), NULL), --Modified for Bug 3137196
1209 decode(tmp_projfunc_rate_type(loop_index), 'User', null, tmp_projfunc_rate_date(loop_index)), NULL),
1210 projfunc_inv_exchange_rate =
1211 DECODE(tmp_status_tab(loop_index),'N',
1212 tmp_projfunc_exchange_rate(loop_index), NULL),
1213 projfunc_bill_amount =
1214 DECODE(tmp_status_tab(loop_index),'N',
1215 tmp_projfunc_bill_amount(loop_index), NULL),
1216 invproc_rate_type =
1217 DECODE(tmp_status_tab(loop_index),'N',
1218 tmp_invproc_rate_type(loop_index), NULL),
1219 invproc_rate_date =
1220 DECODE(tmp_status_tab(loop_index),'N',
1221 --tmp_invproc_rate_date(loop_index), NULL), --Modified for Bug 3137196
1222 decode(tmp_invproc_rate_type(loop_index), 'User', null, tmp_invproc_rate_date(loop_index)), NULL),
1223 invproc_exchange_rate =
1224 DECODE(tmp_status_tab(loop_index),'N',
1225 tmp_invproc_exchange_rate(loop_index), NULL),
1226 bill_amount =
1227 DECODE(tmp_status_tab(loop_index),'N',
1228 tmp_invproc_bill_amount(loop_index), NULL),
1229 funding_inv_rate_type =
1230 DECODE(tmp_status_tab(loop_index),'N',
1231 tmp_funding_rate_type(loop_index), NULL),
1232 funding_inv_rate_date =
1233 DECODE(tmp_status_tab(loop_index),'N',
1234 --tmp_funding_rate_date(loop_index), NULL), --Modified for Bug 3137196
1235 decode(tmp_funding_rate_type(loop_index), 'User', null, tmp_funding_rate_date(loop_index)), NULL),
1236 funding_inv_exchange_rate =
1237 DECODE(tmp_status_tab(loop_index),'N',
1238 tmp_funding_exchange_rate(loop_index), NULL),
1239 project_currency_code =
1240 DECODE(tmp_status_tab(loop_index),'N',
1241 tmp_project_currency_code(loop_index), NULL),
1242 projfunc_currency_code =
1243 DECODE(tmp_status_tab(loop_index),'N',
1244 tmp_projfunc_currency_code(loop_index), NULL),
1245 invproc_currency_code =
1246 DECODE(tmp_status_tab(loop_index),'N',
1247 tmp_invproc_currency_code(loop_index), NULL),
1248 funding_currency_code =
1249 DECODE(tmp_status_tab(loop_index),'N',
1250 tmp_funding_currency_code(loop_index), NULL),
1251 funding_bill_amount =
1252 DECODE(tmp_status_tab(loop_index),'N',
1253 tmp_funding_bill_amount(loop_index), NULL),
1254 inv_gen_rejection_code = tmp_status_tab(loop_index),
1255 request_id = P_Request_id,
1256 program_id = l_program_id,
1257 program_application_id = l_program_application_id,
1258 program_update_date = l_program_update_date
1259 Where expenditure_item_id = P_Expenditure_item_id(loop_index)
1260 and line_num = P_Line_num(loop_index);
1261
1262 -- Update the update_counter
1263
1264 l_count := l_count + 1;
1265 -- Update the EIs
1266 IF g1_debug_mode = 'Y' THEN
1267 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' updateing ei ');
1268 END IF;
1269
1270
1271 Update PA_EXPENDITURE_ITEMS_ALL
1272 Set
1273 invproc_rate_type =
1274 DECODE(tmp_status_tab(loop_index),'N',
1275 tmp_invproc_rate_type(loop_index), NULL),
1276 invproc_rate_date =
1277 DECODE(tmp_status_tab(loop_index),'N',
1278 --tmp_invproc_rate_date(loop_index), NULL), --Modified for Bug 3137196
1279 decode(tmp_invproc_rate_type(loop_index), 'User', null, tmp_invproc_rate_date(loop_index)), NULL),
1280 invproc_exchange_rate =
1281 DECODE(tmp_status_tab(loop_index),'N',
1282 tmp_invproc_exchange_rate(loop_index), NULL),
1283 bill_amount =
1284 DECODE(tmp_status_tab(loop_index),'N',NVL(bill_amount,0) +
1285 tmp_invproc_bill_amount(loop_index), NULL), --for bug#2251021,
1286 invproc_currency_code =
1287 DECODE(tmp_status_tab(loop_index),'N',
1288 tmp_invproc_currency_code(loop_index), NULL),
1289 projfunc_inv_rate_type =
1290 DECODE(tmp_status_tab(loop_index),'N',
1291 tmp_projfunc_rate_type(loop_index), NULL),
1292 projfunc_inv_rate_date =
1293 DECODE(tmp_status_tab(loop_index),'N',
1294 --tmp_projfunc_rate_date(loop_index), NULL), --Modified for Bug 3137196
1295 decode(tmp_projfunc_rate_type(loop_index), 'User', null, tmp_projfunc_rate_date(loop_index)), NULL),
1296 projfunc_inv_exchange_rate =
1297 DECODE(tmp_status_tab(loop_index),'N',
1298 tmp_projfunc_exchange_rate(loop_index), NULL),
1299 projfunc_bill_amount =
1300 DECODE(tmp_status_tab(loop_index),'N',NVL(projfunc_bill_amount,0) +
1301 tmp_projfunc_bill_amount(loop_index), NULL), --bug2251021
1302 projfunc_currency_code =
1303 DECODE(tmp_status_tab(loop_index),'N',
1304 tmp_projfunc_currency_code(loop_index), NULL),
1305 project_inv_rate_type =
1306 DECODE(tmp_status_tab(loop_index),'N',
1307 tmp_project_rate_type(loop_index), NULL),
1308 project_inv_rate_date =
1309 DECODE(tmp_status_tab(loop_index),'N',
1310 --tmp_project_rate_date(loop_index), NULL), --Modified for Bug 3137196
1311 decode(tmp_project_rate_type(loop_index), 'User', null, tmp_project_rate_date(loop_index)), NULL),
1312 project_inv_exchange_rate =
1313 DECODE(tmp_status_tab(loop_index),'N',
1314 tmp_project_exchange_rate(loop_index), NULL),
1315 project_bill_amount =
1316 DECODE(tmp_status_tab(loop_index),'N',NVL(project_bill_amount,0) +
1317 tmp_project_bill_amount(loop_index), NULL), --bug2251021
1318 inv_gen_rejection_code = tmp_status_tab(loop_index),
1319 request_id = P_Request_id,
1320 program_id = l_program_id,
1321 program_application_id = l_program_application_id,
1322 program_update_date = l_program_update_date
1323 Where expenditure_item_id = P_Expenditure_item_id(loop_index);
1324
1325 End Loop;
1326
1327 IF g1_debug_mode = 'Y' THEN
1328 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' equating invproc amounts ');
1329 END IF;
1330
1331
1332
1333 FOR loop_index in 1..P_No_of_rec
1334 LOOP
1335 p_invproc_invoice_amount(loop_index) := TO_CHAR(tmp_invproc_bill_amount(loop_index));
1336
1337 /* Bug 2645634, 2649243 Retention amount should be calculated correctly
1338 This was causing funding */
1339
1340 p_invproc_bill_amount(loop_index) := TO_CHAR(tmp_invproc_bill_amount(loop_index) *
1341 (1- NVL(TO_NUMBER(l_retention_percentage),0)) );
1342 p_status_code(loop_index) := tmp_status_tab(loop_index);
1343
1344 END LOOP;
1345
1346 -- Assign the output value
1347 X_Rec_upd := l_count;
1348 if l_err_count = P_No_of_rec then
1349 x_return_status := 'ALL'; -- All records have errored out b'cos of conversion rate
1350 end if;
1351
1352 EXCEPTION
1353 When Others
1354 Then
1355 IF g1_debug_mode = 'Y' THEN
1356 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Error in MARK_CUST_REV_DIST_LINES ' || sqlerrm);
1357 END IF;
1358
1359 x_return_status := sqlerrm( sqlcode );
1360 IF g1_debug_mode = 'Y' THEN
1361 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || x_return_status);
1362 END IF;
1363
1364 --Raise;
1365
1366 END MARK_CUST_REV_DIST_LINES;
1367
1368 Function IS_AR_INSTALLED( P_Check_prod_installed in varchar2 ,
1369 P_Check_org_installed in varchar2 )
1370 return Varchar2
1371 IS
1372 l_count NUMBER;
1373 l_status VARCHAR2(1);
1374 l_industry VARCHAR2(100);
1375 l_schema VARCHAR2(100);
1376 l_overrid_status VARCHAR2(1);
1377 BEGIN
1378
1379 -- Call AOL API to extract installation information
1380
1381 If NOT FND_INSTALLATION.GET_APP_INFO
1382 ( APPLICATION_SHORT_NAME => 'AR',
1383 STATUS => l_status,
1384 INDUSTRY => l_industry,
1385 ORACLE_SCHEMA => l_schema )
1386 Then
1387 Return('N');
1388 End If;
1389
1390 l_overrid_status := l_status;
1391
1392 -- Bug # 956364 begin
1393
1394 -- Calling Client Extension driver package to get the override installation mode
1395
1396 pa_ar_inst_client_extn.client_extn_driver(l_status,l_overrid_status);
1397
1398 -- Bug 956364 end
1399
1400 --
1401
1402 -- Return the status if only product installion info is required
1403
1404 If (P_Check_prod_installed = 'Y'
1405 and P_Check_org_installed <> 'Y')
1406 Then
1407 If l_status = 'I'
1408 Then
1409 Return('Y');
1410 Else
1411 Return('N');
1412 End If;
1413 End If;
1414
1415 -- Check the product installtion for the current organization setup
1416
1417 If P_Check_org_installed = 'Y'
1418 Then
1419
1420 Select count(*)
1421 Into l_count
1422 From AR_SYSTEM_PARAMETERS;
1423
1424 If l_count = 0
1425 Then
1426 Return('N');
1427 Else
1428 Return('Y');
1429 End If;
1430
1431 End If;
1432
1433 EXCEPTION
1434 When Others
1435 Then
1436 Raise;
1437
1438 END IS_AR_INSTALLED;
1439
1440 -- Function : GET_DRAFT_INVOICE_TAX_AMT
1441 -- Usage : This function will return 0 if invoice is not inter
1442 -- faced to AR, otherwise return tax amount for that
1443 -- invoice.
1444 -- Parameter :
1445 -- P_Trx_Id -Customer Transaction Identifier
1446
1447 Function GET_DRAFT_INVOICE_TAX_AMT( P_Trx_Id in NUMBER )
1448 return Number
1449 AS
1450 l_tax_amout NUMBER;
1451 BEGIN
1452 Select sum(TRX_LINE.EXTENDED_AMOUNT)
1453 Into l_tax_amout
1454 From RA_Customer_Trx_Lines TRX_LINE
1455 Where TRX_LINE.Customer_Trx_Id = P_Trx_Id
1456 And TRX_LINE.Line_Type = 'TAX'
1457 And TRX_LINE.LINK_TO_CUST_TRX_LINE_ID Is Not Null;
1458
1459 Return(l_tax_amout);
1460
1461 EXCEPTION
1462 When Others
1463 Then
1464 Raise;
1465
1466 END GET_DRAFT_INVOICE_TAX_AMT;
1467
1468 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
1469 for customer account relation enhancement bug no 2760630*/
1470
1471 /* For Intercompany Invoice Tax calculation, The following package is
1472 created */
1473
1474 PROCEDURE GET_DEFAULT_TAX_INFO_ARR
1475 ( P_Project_Id IN number ,
1476 P_Customer_Id IN number ,
1477 P_Bill_to_site_use_id IN number ,
1478 P_Ship_to_site_use_id IN number ,
1479 P_Set_of_books_id IN number ,
1480 P_Expenditure_item_id IN PA_PLSQL_DATATYPES.IdTabTyp ,
1481 P_User_Id IN NUMBER ,
1482 P_Request_id IN NUMBER ,
1483 P_No_of_records IN NUMBER ,
1484 P_Compute_flag IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
1485 P_Error_Code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1486 X_Output_tax_code OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1487 X_Output_tax_exempt_flag OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
1488 X_Output_tax_exempt_number OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1489 X_Output_exempt_reason_code OUT NOCOPY PA_PLSQL_DATATYPES.Char80TabTyp,
1490 Pbill_to_customer_id IN NUMBER,
1491 Pship_to_customer_id IN NUMBER)
1492 IS
1493 BEGIN
1494
1495 -- Process for each element of the array for which computation
1496 -- flag is set to Y.
1497
1498 FOR I IN 1..P_No_of_records
1499 LOOP
1500 If P_Compute_flag(I) = 'Y'
1501 Then
1502
1503 -- Call the tax and its attribute determination API for each element
1504 -- of input expenditure array.
1505
1506 /*Added the two parameters bill_to_customer_id and ship_to_customer_id
1507 for customer account relation enhancement bug no 2760630*/
1508
1509 GET_DEFAULT_TAX_INFO
1510 ( P_Project_Id => P_Project_Id,
1511 P_Draft_Inv_Num => NULL,
1512 P_Customer_Id => P_Customer_Id,
1513 P_Bill_to_site_use_id => P_Bill_to_site_use_id,
1514 P_Ship_to_site_use_id => P_Ship_to_site_use_id,
1515 P_Sets_of_books_id => P_Set_of_books_id,
1516 P_Expenditure_item_id => P_Expenditure_item_id(I),
1517 P_User_Id => P_User_Id,
1518 P_Request_id => P_Request_id,
1519 -- X_Output_vat_tax_id => X_Output_vat_tax_id(I), --commented by hsiu
1520 X_output_tax_code => X_output_tax_code(I),
1521 X_Output_tax_exempt_flag => X_Output_tax_exempt_flag(I),
1522 X_Output_tax_exempt_number => X_Output_tax_exempt_number(I),
1523 X_Output_exempt_reason_code=> X_Output_exempt_reason_code(I),
1524 Pbill_to_customer_id => Pbill_to_customer_id,
1525 Pship_to_customer_id => Pship_to_customer_id);
1526
1527 -- If Tax API returns error, set the output error code and set computation
1528 -- flag to 'N'
1529 If pa_tax_client_extn_drv.G_error_Code Is not NULL
1530 Then
1531 P_Error_Code(I) := pa_tax_client_extn_drv.G_error_Code;
1532 P_Compute_flag(I):= 'N';
1533 End if;
1534 End if;
1535 End loop;
1536
1537 Exception
1538
1539 When Others
1540 Then
1541 Raise;
1542
1543 END GET_DEFAULT_TAX_INFO_ARR;
1544
1545 FUNCTION TAXID_2_CODE_CONV (p_org_id in number,
1546 p_tax_id in number)
1547 return varchar2 is
1548
1549 l_tax_code varchar2(50);
1550
1551 begin
1552
1553 select rtrim(tax_classification_code)
1554 into l_tax_code
1555 from zx_id_tcc_mapping_all
1556 where tax_rate_code_id = p_tax_id
1557 and org_id = p_org_id
1558 and tax_class = 'OUTPUT'; -- added for bug 5061887
1559
1560 return l_tax_code;
1561
1562 exception
1563
1564 when others then
1565 l_tax_code := NULL;
1566 return l_tax_code;
1567
1568
1569 end TAXID_2_CODE_CONV;
1570
1571 procedure get_legal_entity_id (p_customer_id IN NUMBER,
1572 p_org_id IN NUMBER,
1573 p_transaction_type_id IN NUMBER,
1574 p_batch_source_id IN NUMBER,
1575 x_legal_entity_id OUT NOCOPY NUMBER,
1576 x_return_status OUT NOCOPY VARCHAR2)
1577 is
1578
1579
1580 l_msg_data varchar2(250);
1581 l_return_status varchar2(30);
1582 l_otoc_le_info xle_businessinfo_grp.otoc_le_rec;
1583
1584 begin
1585
1586 IF g1_debug_mode = 'Y' THEN
1587
1588 PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: in get_legal_entity_id' );
1589 PA_MCB_INVOICE_PKG.log_message('p_customer_id : ' || P_customer_id );
1590 PA_MCB_INVOICE_PKG.log_message('p_org_id : ' || p_org_id );
1591 PA_MCB_INVOICE_PKG.log_message('p_transaction_type_id : ' || P_transaction_type_id );
1592 PA_MCB_INVOICE_PKG.log_message('p_batch_source_id : ' || P_batch_source_id );
1593
1594 PA_MCB_INVOICE_PKG.log_message('calling XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info ');
1595 END IF;
1596
1597 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info(
1598 x_return_status => l_return_status,
1599 x_msg_data => l_msg_data,
1600 P_customer_type => 'BILL_TO',
1601 P_customer_id => p_customer_id,
1602 P_transaction_type_id => p_transaction_type_id,
1603 P_batch_source_id => p_batch_source_id,
1604 P_operating_unit_id => p_org_id,
1605 x_otoc_Le_info => l_otoc_le_info) ;
1606
1607 IF g1_debug_mode = 'Y' THEN
1608
1609 PA_MCB_INVOICE_PKG.log_message('after calling XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info ');
1610 PA_MCB_INVOICE_PKG.log_message('legal_entity_id : ' || l_otoc_le_info.legal_entity_id );
1611 PA_MCB_INVOICE_PKG.log_message('return status : ' || l_return_status);
1612
1613 PA_MCB_INVOICE_PKG.log_message('msg data : ' || l_msg_data);
1614
1615 END IF;
1616
1617 x_legal_entity_id := l_otoc_le_info.legal_entity_id;
1618 x_return_status := l_return_status;
1619
1620
1621 end get_legal_entity_id;
1622
1623 procedure get_btch_src_trans_type ( p_project_id IN NUMBER,
1624 p_draft_invoice_num IN NUMBER,
1625 p_draft_inv_num_credited IN NUMBER,
1626 x_transaction_type_id out NOCOPY number,
1627 x_batch_source_id out NOCOPY number,
1628 x_return_status OUT NOCOPY varchar2) is
1629
1630 l_business_grp_id number;
1631 l_carry_out_org_id number;
1632 l_org_struct_ver_id number;
1633 l_basic_language_code varchar2(4);
1634 l_p_trx_type_id number;
1635 l_cm_trx_type_id number;
1636 l_error_status number;
1637 l_error_message varchar2(250);
1638 l_p_trx_type varchar2(30);
1639 l_cm_trx_type varchar2(30);
1640 l_batch_source_id number;
1641 l_draft_inv_num_credited number;
1642 l_invoice_date date; /* added for bug 9246335 */
1643
1644
1645 begin
1646
1647 IF g1_debug_mode = 'Y' THEN
1648
1649 PA_MCB_INVOICE_PKG.log_message('in get_btch_src_trans_type');
1650 PA_MCB_INVOICE_PKG.log_message('project_id : ' || p_project_id);
1651 PA_MCB_INVOICE_PKG.log_message('draft_invoice_num :' || p_draft_invoice_num );
1652
1653 END IF;
1654
1655 SELECT business_group_id,
1656 proj_org_structure_version_id,
1657 invoice_batch_source_id
1658 INTO l_business_grp_id,
1659 l_org_struct_ver_id,
1660 l_batch_source_id
1661 FROM pa_implementations;
1662
1663
1664 IF g1_debug_mode = 'Y' THEN
1665
1666 PA_MCB_INVOICE_PKG.log_message('business group id : ' || l_business_grp_id);
1667 PA_MCB_INVOICE_PKG.log_message('proj org str vers id :' || l_org_struct_ver_id );
1668 PA_MCB_INVOICE_PKG.log_message('inv btch src id :' || l_batch_source_id );
1669
1670 END IF;
1671
1672 SELECT PROJ.Carrying_Out_Organization_ID
1673 INTO l_carry_out_org_id
1674 FROM pa_projects proj
1675 WHERE project_id = p_project_id;
1676
1677
1678 SELECT language_code
1679 INTO l_basic_language_code
1680 FROM fnd_languages
1681 WHERE installed_flag = 'B';
1682
1683 /*Added exception handling block for the below select into query for bug 9322678*/
1684
1685 BEGIN
1686 /* added for bug 9246335 */
1687 SELECT invoice_date
1688 INTO l_invoice_date
1689 FROM pa_draft_invoices
1690 WHERE project_id = p_project_id
1691 and draft_invoice_num = p_draft_invoice_num;
1692 /* end of code for bug 9246335 */
1693 EXCEPTION
1694 WHEN NO_DATA_FOUND THEN
1695 l_invoice_date := pa_billing.GetInvoiceDate;
1696 IF g1_debug_mode = 'Y' THEN
1697 PA_MCB_INVOICE_PKG.log_message('l_invoice_date:=' || l_invoice_date);
1698 END IF;
1699 END ;
1700
1701 IF g1_debug_mode = 'Y' THEN
1702
1703 PA_MCB_INVOICE_PKG.log_message('carrying out org id : ' || l_carry_out_org_id);
1704 PA_MCB_INVOICE_PKG.log_message('basic lang code :' || l_basic_language_code );
1705 PA_MCB_INVOICE_PKG.log_message('calling get_trx_crmemo types');
1706
1707 END IF;
1708
1709 pa_invoice_xfer.get_trx_crmemo_types(
1710 P_business_group_id => l_business_grp_id ,
1711 P_carrying_out_org_id => l_carry_out_org_id ,
1712 P_proj_org_struct_version_id => l_org_struct_ver_id ,
1713 p_basic_language => l_basic_language_code ,
1714 P_trans_type => l_p_trx_type ,
1715 p_trans_date => l_invoice_date, /* added for bug 9246335 */
1716 P_crmo_trx_type => l_cm_trx_type ,
1717 P_error_status => l_error_status ,
1718 P_error_message => l_error_message);
1719
1720 IF g1_debug_mode = 'Y' THEN
1721
1722 PA_MCB_INVOICE_PKG.log_message('trans type : ' || l_p_trx_type);
1723 PA_MCB_INVOICE_PKG.log_message('crmo trx type:' || l_cm_trx_type );
1724
1725 END IF;
1726
1727 IF nvl(p_draft_inv_num_credited,0) = 0 THEN
1728
1729 x_transaction_type_id := to_number(l_p_trx_type);
1730
1731 ELSE
1732
1733 x_transaction_type_id := to_number(l_cm_trx_type);
1734
1735 END IF;
1736 x_batch_source_id := l_batch_source_id;
1737
1738 end get_btch_src_trans_type;
1739
1740
1741 FUNCTION get_meaning( x_output_tax_class_code in varchar2 ,
1742 x_org_id in number,
1743 x_inv_date in date)
1744 RETURN varchar2
1745 IS l_org_id NUMBER;
1746 l_meaning varchar2(50);
1747 BEGIN
1748 BEGIN
1749 SELECT fnd_lk.meaning into l_meaning
1750 FROM zx_output_classifications_v fnd_lk
1751 WHERE fnd_lk.lookup_code = x_output_tax_class_code
1752 AND fnd_lk.org_id = x_org_id
1753 AND x_inv_date BETWEEN fnd_lk.start_date_active AND NVL(fnd_lk.end_date_active,x_inv_date);
1754
1755 EXCEPTION
1756
1757 WHEN NO_DATA_FOUND THEN
1758
1759 SELECT fnd_lk.meaning into l_meaning
1760 FROM zx_output_classifications_v fnd_lk
1761 WHERE fnd_lk.lookup_code = x_output_tax_class_code
1762 AND fnd_lk.org_id = -99
1763 AND x_inv_date BETWEEN fnd_lk.start_date_active AND NVL(fnd_lk.end_date_active,x_inv_date);
1764
1765 WHEN OTHERS THEN
1766
1767 RAISE;
1768 END;
1769 RETURN(l_meaning);
1770 END get_meaning;
1771
1772 END PA_OUTPUT_TAX;