[Home] [Help]
PACKAGE BODY: APPS.AP_OTHR_CHRG_MATCH_PKG
Source
1 PACKAGE BODY AP_OTHR_CHRG_MATCH_PKG AS
2 /* $Header: apothmtb.pls 120.27.12020000.2 2013/03/11 10:16:22 cmaredup ship $ */
3
4
5 /*
6 Procedure OTHR_CHRG_MATCH does the actual matching (linking) of a
7 standard Invoice/ CM/DM to a particular rcv transaction. It creates the
8 actual Charge Lines in AP_INVOICE_LINES and 1 invoice distribution
9 per charge line in AP_INVOICE_DISTRIBUTIONS and stores the associated
10 rcv_transaction_id for that line. One Charge Invoice line will be created
11 in AP_INVOICE_LINES for Receipt/Receipt Line the user selects to match to
12 in the Other Charge Matching Window. Invoice Distributions will be generated
13 immediately during the matching, 1 per invoice line.
14
15 Either the total amount is prorated (if
16 prorate_flag is 'Y') or the user specified amounts are stored for each
17 rcv_transaction_id checked in the form. No allocations will be created
18 for this charge line .
19
20
21 Description of the input parameters:
22 ------------------------------------
23
24 X_invoice_id Id of Invoice that needs to be matched(CM or STD)
25 X_invoice_line_number Invoice Line number when the charge match is done
26 from a invoice line or from the import.
27 X_line_type Line Type of the charge line. Can be either
28 FREIGHT or MISC
29 X_prorate_flag Flag which indicates whether x_total amount needs
30 to be prorated across all the rcv_transactions
31 X_account_id The dist_code_combination_id to be used when creating
32 the distributions. Can be NULL.
33 X_description Description to be stored on the invoice distributions
34 X_total_amount The total amount that needs to be matched(linked)
35 to the receipts
36 X_othr_chrg_tab Pl/SQL table containing the rcv_transaction_id,
37 charge_amount and rcv_transaction_qty for each
38 row checked in the form.
39 X_row_count Number of rows in thr pl/sql table
40 X_calling_sequence Calling Sequence */
41
42 --bug 11075838 :Added FND globals
43 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
44 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
45 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_OTHR_CHRG_MATCH_PKG.';
46
47 --Local Procedures
48 Procedure Get_Info(x_invoice_id IN NUMBER,
49 x_calling_sequence IN VARCHAR2);
50
51 Procedure Create_Invoice_Lines(x_invoice_id IN NUMBER,
52 x_line_type IN VARCHAR2,
53 x_cost_factor_id IN NUMBER,
54 x_othr_chrg_tab IN OUT NOCOPY OTHR_CHRG_MATCH_TABTYPE,
55 x_row_count IN NUMBER,
56 x_description IN VARCHAR2,
57 x_ccid IN NUMBER,
58 x_total_amount IN NUMBER,
59 x_calling_sequence IN VARCHAR2);
60
61 Procedure Insert_Invoice_Line( x_invoice_id IN NUMBER,
62 x_invoice_line_number IN NUMBER,
63 x_line_type IN VARCHAR2,
64 x_cost_factor_id IN NUMBER,
65 x_amount IN NUMBER,
66 x_base_amount IN NUMBER,
67 x_rounding_amt IN NUMBER,
68 x_rcv_transaction_id IN NUMBER,
69 x_ccid IN NUMBER,
70 x_description IN VARCHAR2,
71 x_calling_sequence IN VARCHAR2);
72
73 Procedure Insert_Invoice_dist (X_invoice_id IN NUMBER,
74 X_invoice_line_number IN NUMBER,
75 X_description IN VARCHAR2,
76 X_calling_sequence IN VARCHAR2) ;
77
78 Procedure Get_Proration_Info(X_Othr_Chrg_Tab IN OUT NOCOPY OTHR_CHRG_MATCH_TABTYPE,
79 X_Total_Amount IN NUMBER,
80 X_Prorate_Flag IN VARCHAR2,
81 X_Row_Count IN NUMBER,
82 X_Calling_Sequence IN VARCHAR2);
83
84 --Global Variables
85
86 g_vendor_id ap_invoices.vendor_id%TYPE;
87 g_vendor_site_id ap_invoices.vendor_site_id%TYPE;
88 g_invoice_date ap_invoices.invoice_date%TYPE;
89 g_batch_id ap_batches.batch_id%TYPE;
90 g_max_invoice_line_number ap_invoice_lines.line_number%TYPE;
91 g_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
92 g_exchange_rate ap_invoices.exchange_rate%TYPE;
93 g_base_currency_code ap_system_parameters.base_currency_code%TYPE;
94 g_accounting_date ap_invoices.gl_date%TYPE;
95 g_period_name gl_period_statuses.period_name%TYPE;
96 g_set_of_books_id ap_invoices.set_of_books_id%TYPE;
97 g_type_1099 po_vendors.type_1099%TYPE;
98 g_income_tax_region ap_system_parameters.income_tax_region%TYPE;
99 g_allow_pa_override VARCHAR2(1);
100 g_pa_expenditure_date_default VARCHAR2(50);
101 g_approval_workflow_flag ap_system_parameters.approval_workflow_flag%TYPE;
102 g_asset_book_type_code fa_book_controls.book_type_code%TYPE;
103 g_transfer_flag ap_system_parameters.transfer_desc_flex_flag%TYPE;
104 g_user_id number;
105 g_login_id number;
106 g_trx_business_category ap_invoices.trx_business_category%TYPE;
107 G_Org_Id ap_invoices.org_id%TYPE;
108 G_intended_use zx_lines_det_factors.line_intended_use%type;
109 G_product_type zx_lines_det_factors.product_type%type;
110 G_product_category zx_lines_det_factors.product_category%type;
111 G_product_fisc_class zx_lines_det_factors.product_fisc_classification%type;
112 G_user_defined_fisc_class zx_lines_det_factors.user_defined_fisc_class%type;
113 G_assessable_value zx_lines_det_factors.assessable_value%type;
114 G_dflt_tax_class_code zx_transaction_lines_gt.input_tax_classification_code%type;
115
116
117 Procedure OTHR_CHRG_MATCH (
118 X_invoice_id IN NUMBER,
119 X_invoice_line_number IN NUMBER,
120 X_line_type IN VARCHAR2,
121 X_cost_factor_id IN NUMBER DEFAULT NULL,
122 X_prorate_flag IN VARCHAR2,
123 X_account_id IN NUMBER,
124 X_description IN VARCHAR2,
125 X_total_amount IN NUMBER,
126 X_othr_chrg_tab IN OTHR_CHRG_MATCH_TABTYPE,
127 X_row_count IN NUMBER,
128 X_calling_sequence IN VARCHAR2) IS
129
130 l_total_rcv_qty NUMBER := 0;
131 I NUMBER;
132 l_charge_amount NUMBER;
133 l_charge_base_amount NUMBER;
134 l_othr_chrg_tab OTHR_CHRG_MATCH_TABTYPE := x_othr_chrg_tab;
135 l_ref_doc_application_id zx_transaction_lines_gt.ref_doc_application_id%TYPE;
136 l_ref_doc_entity_code zx_transaction_lines_gt.ref_doc_entity_code%TYPE;
137 l_ref_doc_event_class_code zx_transaction_lines_gt.ref_doc_event_class_code%TYPE;
138 l_ref_doc_line_quantity zx_transaction_lines_gt.ref_doc_line_quantity%TYPE;
139 l_ref_doc_trx_level_type zx_transaction_lines_gt.ref_doc_trx_level_type%TYPE;
140 l_po_header_curr_conv_rate po_headers_all.rate%TYPE;
141 l_uom_code mtl_units_of_measure.uom_code%TYPE;
142 l_ref_doc_trx_id po_headers_all.po_header_id%TYPE;
143 l_po_line_location_id po_line_locations.line_location_id%TYPE;
144 l_dummy number;
145 l_success BOOLEAN;
146 l_debug_info VARCHAR2 (100);
147 l_vendor_id NUMBER;
148 l_vendor_site_id NUMBER;
149 l_ship_to_location_id VARCHAR2(100);
150 l_product_org_id NUMBER;
151 current_calling_sequence VARCHAR2(2000);
152 l_allow_tax_code_override zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE;
153 l_invoice_type_lookup_code VARCHAR2(20);
154 Tax_Exception EXCEPTION;
155 l_error_code VARCHAR2(4000);
156 l_dflt_tax_class_code zx_transaction_lines_gt.input_tax_classification_code%type; -- bug 8483345
157
158 l_code_combination_id NUMBER ; -- Bug 10050094
159 Begin
160
161 current_calling_sequence := 'AP_OTHR_CHRG_MATCH_PKG.othr_chrg_match <-'
162 || X_calling_sequence;
163
164
165 --Retreive certain information from ap_invoices table and
166 --ap_system_parameters
167
168 l_debug_info := 'Select information from ap_invoices';
169
170 Get_Info(X_Invoice_Id => x_invoice_id,
171 X_Calling_Sequence => current_calling_sequence);
172
173 --ETAX: Deleted the Tax related code from here , please build
174 --the version 115.1 of the file apothmtb.pls, to see what code was deleted.
175 --
176 -- The Tax related PO attributes are not getting copied to the
177 -- Invoice, when matching through the other charge matching
178 -- screen (Bug5708602).
179 --
180 -- Added the calls to the AP_ETAX_UTILITY_PKG.Get_PO_Info
181 -- and AP_ETAX_SERVICES_PKG.get_po_tax_attributes to populate
182 -- the global variables which then will be used to populate
183 -- the attributes on the Invoice Line.
184 --
185
186 --
187 -- Get the value of the PO line location id from
188 -- the Rcv_Transaction_Id
189 --
190
191 l_debug_info := 'Get the value of the po_line_location_id from rcv_transaction_id';
192
193 BEGIN
194
195 Select RCV.po_line_location_id,
196 RCV.vendor_id,
197 RCV.vendor_site_id,
198 RSL.ship_to_location_id
199 Into l_po_line_location_id,
200 l_vendor_id,
201 l_vendor_site_id,
202 l_ship_to_location_id
203 From rcv_transactions RCV,
204 rcv_shipment_lines RSL
205 Where RCV.transaction_id = l_othr_chrg_tab(1).rcv_txn_id
206 And RCV.shipment_line_id = RSL.shipment_line_id
207 And rownum < 2;
208
209 EXCEPTION
210 When Others then
211 NULL;
212
213 END;
214
215
216 l_debug_info := 'Get the Event class code by call to the utilities pkg';
217
218 BEGIN
219
220 Select invoice_type_lookup_code
221 Into l_invoice_type_lookup_code
222 From ap_invoices_all ai
223 Where ai.invoice_id = x_invoice_id;
224
225 EXCEPTION
226 When Others then
227 NULL;
228
229 END;
230
231 l_debug_info := 'Calling the Get_PO_Info API';
232
233 l_success := AP_ETAX_UTILITY_PKG.Get_PO_Info(
234 P_Po_Line_Location_Id => l_po_line_location_id,
235 P_PO_Distribution_Id => null,
236 P_Application_Id => l_ref_doc_application_id,
237 P_Entity_code => l_ref_doc_entity_code,
238 P_Event_Class_Code => l_ref_doc_event_class_code,
239 P_PO_Quantity => l_ref_doc_line_quantity,
240 P_Product_Org_Id => l_product_org_id,
241 P_Po_Header_Id => l_ref_doc_trx_id,
242 P_Po_Header_curr_conv_rate => l_po_header_curr_conv_rate,
243 P_Uom_Code => l_uom_code,
244 P_Dist_Qty => l_dummy,
245 P_Ship_Price => l_dummy,
246 P_Error_Code => l_error_code,
247 P_Calling_Sequence => current_calling_sequence);
248
249 -- Bug 10050094
250 l_debug_info := 'Get the default CCID for calling Tax';
251 AP_ETAX_UTILITY_PKG.Get_Default_CCID( p_invoice_id => x_invoice_id,
252 p_default_dist_ccid => X_account_id,
253 p_po_line_location_id => l_po_line_location_id,
254 p_line_type_lookup_code => X_line_type,
255 p_invoice_line_number => X_invoice_line_number,
256 p_calling_sequence => current_calling_sequence,
257 x_derived_ccid => l_code_combination_id ) ;
258
259 l_debug_info := 'Get the default tax classification code';
260
261 g_dflt_tax_class_code := null; /*bug12428818*/
262
263 ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification
264 (p_ref_doc_application_id => l_ref_doc_application_id,
265 p_ref_doc_entity_code => l_ref_doc_entity_code,
266 p_ref_doc_event_class_code => l_ref_doc_event_class_code,
267 p_ref_doc_trx_id => l_ref_doc_trx_id,
268 p_ref_doc_line_id => l_po_line_location_id,
269 p_ref_doc_trx_level_type => 'SHIPMENT',
270 p_vendor_id => l_vendor_id,
271 p_vendor_site_id => l_vendor_site_id,
272 p_code_combination_id => l_code_combination_id, -- Bug 10050094 X_account_id,
273 p_concatenated_segments => null,
274 p_templ_tax_classification_cd => null,
275 p_ship_to_location_id => l_ship_to_location_id,
276 p_ship_to_loc_org_id => null,
277 p_inventory_item_id => null,
278 p_item_org_id => l_product_org_id,
279 p_tax_classification_code => g_dflt_tax_class_code,
280 p_allow_tax_code_override_flag => l_allow_tax_code_override,
281 APPL_SHORT_NAME => 'SQLAP',
282 FUNC_SHORT_NAME => 'NONE',
283 p_calling_sequence => 'AP_OTHR_CHRG_MATCH_PKG',
284 p_event_class_code => l_invoice_type_lookup_code,
285 p_entity_code => 'AP_INVOICES',
286 p_application_id => 200,
287 p_internal_organization_id => g_org_id);
288
289 l_debug_info := 'calling the Get_PO_Tax_Attributes API';
290
291
292 AP_Etax_Services_Pkg.Get_Po_Tax_Attributes(
293 p_application_id => l_ref_doc_application_id,
294 p_org_id => g_org_id,
295 p_entity_code => l_ref_doc_entity_code,
296 p_event_class_code => l_ref_doc_event_class_code,
297 p_trx_level_type => 'SHIPMENT',
298 p_trx_id => l_ref_doc_trx_id,
299 p_trx_line_id => l_po_line_location_id,
300 x_line_intended_use => g_intended_use,
301 x_product_type => g_product_type,
302 x_product_category => g_product_category,
303 x_product_fisc_classification => g_product_fisc_class,
304 x_user_defined_fisc_class => g_user_defined_fisc_class,
305 x_assessable_value => g_assessable_value,
306 x_tax_classification_code => l_dflt_tax_class_code -- bug 8483345
307 );
308
309 -- bug 8483345: start
310 -- if tax classification code not retrieved from hierarchy
311 -- retrieve it from PO
312 IF (g_dflt_tax_class_code is null) THEN
313 g_dflt_tax_class_code := l_dflt_tax_class_code;
314 END IF;
315 -- bug 8483345: end
316
317 --
318 -- end of bug5708602
319
320 IF (x_invoice_line_number IS NULL) THEN
321
322 Get_Proration_Info (X_Othr_Chrg_Tab => l_othr_chrg_tab,
323 X_Total_Amount => x_total_amount,
324 X_Prorate_Flag => x_prorate_flag,
325 X_Row_Count => x_row_count,
326 X_Calling_Sequence => current_calling_sequence) ;
327
328
329 Create_Invoice_Lines(x_invoice_id => x_invoice_id,
330 x_line_type => x_line_type,
331 x_cost_factor_id => x_cost_factor_id,
332 x_othr_chrg_tab => l_othr_chrg_tab,
333 x_row_count => x_row_count,
334 x_description => x_description,
335 x_ccid => x_account_id,
336 x_total_amount => x_total_amount,
337 x_calling_sequence => current_calling_sequence);
338
339 ELSE
340
341 Insert_Invoice_Dist(x_invoice_id => x_invoice_id,
342 x_invoice_line_number => x_invoice_line_number,
343 x_description => x_description,
344 x_calling_sequence => current_calling_sequence);
345
346 UPDATE ap_invoice_lines
347 SET generate_dists ='D'
348 WHERE invoice_id = x_invoice_id
349 AND line_number = x_invoice_line_number;
350
351 END IF;
352
353 IF X_Line_Type = 'TAX' THEN
354
355 IF NOT (AP_ETAX_SERVICES_PKG.Calculate_Tax_Receipt_Match(
356 P_Invoice_Id => x_invoice_id,
357 P_Calling_Mode => 'CALCULATE',
358 P_All_Error_Messages => 'N',
359 P_Error_Code => l_error_code,
360 P_Calling_Sequence => current_calling_sequence)) THEN
361
362 RAISE Tax_Exception;
363
364 END IF;
365 END IF;
366
367 Exception
368 WHEN Tax_Exception Then
369
370 FND_MESSAGE.SET_NAME('SQLAP','AP_TAX_EXCEPTION');
371 IF l_error_code IS NOT NULL THEN
372 FND_MESSAGE.SET_TOKEN('ERROR', l_error_code);
373 ELSE
374 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
375 END IF;
376 APP_EXCEPTION.RAISE_EXCEPTION;
377
378 WHEN others then
379 If (SQLCODE <> -20001) Then
380 fnd_message.set_name('SQLAP','AP_DEBUG');
381 fnd_message.set_token('ERROR',SQLERRM);
382 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
383 fnd_message.set_token('PARAMETERS',
384 'X_invoice_id= '||to_char(x_invoice_id)
385 ||'X_total_amount= '||to_char(x_total_amount)
386 ||'X_prorate_flag= '||x_prorate_flag
387 ||'X_account_id= '||to_char(x_account_id)
388 ||'X_line_type= '||x_line_type
389 ||'X_row_count= '||to_char(x_row_count)
390 ||'X_description= '||x_description);
391 fnd_message.set_token('DEBUG_INFO',l_debug_info);
392 End if;
393 app_exception.raise_exception;
394
395
396 End OTHR_CHRG_MATCH;
397
398 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
399 X_Calling_Sequence IN VARCHAR2) IS
400
401 current_calling_sequence VARCHAR2(2000);
402 l_debug_info VARCHAR2(100);
403 l_accounting_date ap_invoice_lines.accounting_date%TYPE; --Bug16216470
404
405 BEGIN
406
407
408 current_calling_sequence := 'Get_Info<-'||Current_Calling_Sequence;
409
410 SELECT ai.batch_id,
411 ai.invoice_currency_code,
412 ai.exchange_rate,
413 ai.vendor_id,
414 ai.vendor_site_id,
415 ai.invoice_date,
416 asp.base_currency_code,
417 ai.gl_date,
418 ai.set_of_books_id,
419 pv.type_1099,
420 decode(pv.type_1099,'','',
421 decode(combined_filing_flag,'N',NULL,
422 decode(asp.income_tax_region_flag,'Y',pvs.state,
423 asp.income_tax_region))),
424 asp.approval_workflow_flag,
425 asp.transfer_desc_flex_flag,
426 ai.trx_business_category,
427 ai.org_id
428 INTO g_batch_id,
429 g_invoice_currency_code,
430 g_exchange_rate,
431 g_vendor_id,
432 g_vendor_site_id,
433 g_invoice_date,
434 g_base_currency_code,
435 g_accounting_date,
436 g_set_of_books_id,
437 g_type_1099,
438 g_income_tax_region,
439 g_approval_workflow_flag,
440 g_transfer_flag,
441 g_trx_business_category,
442 g_org_id
443 FROM ap_invoices_all ai, --bug 5056051
444 ap_system_parameters asp,
445 ap_suppliers pv, --bug 5056051
446 ap_supplier_sites pvs --bug 5056051
447 WHERE ai.invoice_id = x_invoice_id
448 AND ai.vendor_site_id = pvs.vendor_site_id
449 AND pv.vendor_id = pvs.vendor_id
450 AND ai.org_id = asp.org_id;
451
452 SELECT nvl(max(ail.line_number),0)
453 INTO g_max_invoice_line_number
454 FROM ap_invoice_lines ail
455 WHERE ail.invoice_id = x_invoice_id;
456
457 BEGIN
458
459 SELECT book_type_code
460 INTO g_asset_book_type_code
461 FROM fa_book_controls fc
462 WHERE fc.book_class = 'CORPORATE'
463 AND fc.set_of_books_id = g_set_of_books_id
464 AND fc.date_ineffective IS NULL;
465
466 EXCEPTION WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
467 g_asset_book_type_code := NULL;
468
469 END;
470 l_debug_info := 'select period for accounting date';
471
472 --get_current_gl_date will return NULL if the date passed to it doesn't fall in a
473 --open period.
474 g_period_name := AP_UTILITIES_PKG.get_current_gl_date(g_accounting_date);
475
476 IF (g_period_name IS NULL) THEN
477
478 --Get gl_period and Date from a future period for the accounting date
479
480 -- Bug16216470 Used l_accounting_date instead of g_accounting_date. Using
481 --same variable for in/out parmeters causing to make in parameters
482 -- as Null
483 ap_utilities_pkg.get_open_gl_date(p_date => g_accounting_date,
484 p_period_name => g_period_name,
485 p_gl_date => l_accounting_date); --Bug16216470
486
487 g_accounting_date := l_accounting_date; --Bug16216470
488
489 IF (g_accounting_date IS NULL) THEN
490 fnd_message.set_name('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
491 app_exception.raise_exception;
492 END IF;
493
494 END IF;
495
496 g_allow_pa_override := FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES');
497
498 -- Bug 5294998. API from PA will be used
499 --g_pa_expenditure_date_default := FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT');
500
501 g_user_id := FND_PROFILE.VALUE('USER_ID');
502
503 g_login_id := FND_PROFILE.VALUE('LOGIN_ID');
504
505 END Get_Info;
506
507
508 Procedure Get_Proration_Info(X_Othr_Chrg_Tab IN OUT NOCOPY OTHR_CHRG_MATCH_TABTYPE,
509 X_Total_Amount IN NUMBER,
510 X_Prorate_Flag IN VARCHAR2,
511 X_Row_Count IN NUMBER,
512 X_Calling_Sequence IN VARCHAR2) IS
513
514 l_charge_amount number := 0;
515 l_total_rcv_qty number := 0;
516 I number;
517 l_sum_amount_prorated ap_invoice_lines.amount%TYPE := 0;
518 l_rounding_index number;
519 l_max_line_amount ap_invoice_lines.amount%TYPE := 0;
520 l_total_base_amount ap_invoice_lines.base_amount%TYPE ;
521 l_base_amount ap_invoice_lines.base_amount%TYPE ;
522 l_sum_line_base_amount ap_invoice_lines.base_amount%TYPE := 0;
523 l_debug_info varchar2(1000); --bug11075838: increased the size
524 current_calling_sequence varchar2(2000);
525 l_api_name CONSTANT VARCHAR2(200) := 'Get_Proration_Info'; --bug11075838
526
527 BEGIN
528
529 current_calling_sequence := 'Get_Proration_Info<-'||x_calling_sequence;
530
531 --If prorate = 'Y' then get the total rcv_qty from the pl/sql table
532 IF X_prorate_flag = 'Y' Then
533
534 l_debug_info := 'find total qty to prorate against';
535
536 FOR I IN 1..X_row_count LOOP
537 l_total_rcv_qty := l_total_rcv_qty +
538 X_othr_chrg_tab(I).rcv_qty;
539 END LOOP;
540
541 END IF;
542
543 --bug11075838: Added FND log statements
544 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
546 'Before entering the second loop. l_rounding_index: '||l_rounding_index);
547 END IF; --bug11075838
548
549 FOR I IN 1..X_row_count LOOP
550
551 l_debug_info := 'Calculate charge amount';
552
553 If x_prorate_flag = 'Y' then
554
555 l_charge_amount :=(x_othr_chrg_tab(i).rcv_qty /l_total_rcv_qty)
556 * x_total_amount;
557
558 x_othr_chrg_tab(i).charge_amt:= AP_UTILITIES_PKG.ap_round_currency(
559 l_charge_amount, g_invoice_currency_code);
560
561 --get the max of the invoice line number with largest amount
562 IF ( abs(x_othr_chrg_tab(i).charge_amt) >= abs(l_max_line_amount)) THEN --bug11075838: added ABS
563 l_rounding_index := i;
564 l_max_line_amount := x_othr_chrg_tab(i).charge_amt;
565 END IF;
566
567 l_sum_amount_prorated := l_sum_amount_prorated + x_othr_chrg_tab(i).charge_amt;
568
569 Else
570
571 --get the max of the invoice line number with largest amount
572 IF ( abs(x_othr_chrg_tab(i).charge_amt) >= abs(l_max_line_amount)) THEN --bug11075838 added ABS
573 l_rounding_index := i;
574 l_max_line_amount := x_othr_chrg_tab(i).charge_amt;
575 END IF;
576
577 l_sum_amount_prorated := l_sum_amount_prorated + x_othr_chrg_tab(i).charge_amt;
578
579 End if;
580
581 END LOOP;
582
583 --bug11075838: Added FND log statements
584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
586 'After the second loop. l_rounding_index '||l_rounding_index);
587 END IF; --bug11075838
588
589 --Perform Proration Rounding before base_amounts are populated
590 IF (l_sum_amount_prorated <> x_total_amount) THEN
591
592 --bug11075838: Added FND log statements
593 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
594 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
595 'In perform proration rounding ... ');
596 END IF; --bug11075838
597
598 x_othr_chrg_tab(l_rounding_index).charge_amt := x_othr_chrg_tab(l_rounding_index).charge_amt +
599 (x_total_amount - l_sum_amount_prorated);
600
601 END IF;
602
603 --Calculate the base amount and rounding_amount
604 --for foreign currency invoices.
605 IF (g_exchange_rate IS NOT NULL) THEN
606
607 --bug11075838: Added FND log statements
608 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
610 'Before foreign currency loop.');
611 END IF; --bug11075838
612
613 l_total_base_amount := AP_UTILITIES_PKG.ap_round_currency
614 (g_exchange_rate * X_Total_Amount,
615 g_base_currency_code);
616
617
618 FOR I IN 1..X_row_count LOOP
619
620 l_debug_info := 'calculate the base amount';
621
622 l_base_amount := AP_UTILITIES_PKG.ap_round_currency
623 (g_exchange_rate * x_othr_chrg_tab(i).charge_amt,
624 g_base_currency_code);
625
626 x_othr_chrg_tab(i).base_amt := l_base_amount;
627
628 l_sum_line_base_amount := l_sum_line_base_amount + l_base_amount;
629
630 END LOOP;
631
632 --bug11075838: Added FND log statements
633 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
635 'After foreign currency loop. l_rounding_index '||l_rounding_index);
636 END IF; --bug11075838
637
638 --Perform Base Amount Rounding
639 IF (l_total_base_amount <> l_sum_line_base_amount) THEN
640 x_othr_chrg_tab(l_rounding_index).base_amt := x_othr_chrg_tab(l_rounding_index).base_amt +
641 (l_total_base_amount - l_sum_line_base_amount);
642 x_othr_chrg_tab(l_rounding_index).rounding_amt := l_total_base_amount - l_sum_line_base_amount;
643 END IF;
644
645 END IF; /*g_exchange_rate IS NOT NULL*/
646
647 END Get_Proration_Info;
648
649
650
651 Procedure Create_Invoice_Lines(x_invoice_id IN NUMBER,
652 x_line_type IN VARCHAR2,
653 x_cost_factor_id IN NUMBER,
654 x_othr_chrg_tab IN OUT NOCOPY OTHR_CHRG_MATCH_TABTYPE,
655 x_row_count IN NUMBER,
656 x_description IN VARCHAR2,
657 x_ccid IN NUMBER,
658 x_total_amount IN NUMBER,
659 x_calling_sequence IN VARCHAR2) IS
660
661 l_debug_info VARCHAR2(100);
662 current_calling_sequence VARCHAR2(2000);
663 l_invoice_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
664 l_sum_amount_prorated NUMBER;
665 l_max_line_amount NUMBER := 0;
666 l_proration_round_amount NUMBER := 0;
667 l_rounded_line_number NUMBER;
668
669 BEGIN
670
671 current_calling_sequence := 'Create_Invoice_Lines<-'||x_calling_sequence;
672
673 l_invoice_line_number := g_max_invoice_line_number + 1;
674
675 FOR i IN 1..x_row_count LOOP
676
677 l_debug_info := 'Calling Insert_Invoice_Line';
678
679 Insert_Invoice_Line(x_invoice_id => x_invoice_id,
680 x_invoice_line_number => l_invoice_line_number,
681 x_line_type => x_line_type,
682 x_cost_factor_id => x_cost_factor_id,
683 x_amount => x_othr_chrg_tab(i).charge_amt,
684 x_base_amount => x_othr_chrg_tab(i).base_amt,
685 x_rounding_amt => x_othr_chrg_tab(i).rounding_amt,
686 x_rcv_transaction_id => x_othr_chrg_tab(i).rcv_txn_id,
687 x_ccid => x_ccid,
688 x_description => x_description,
689 x_calling_sequence => current_calling_sequence);
690
691 l_debug_info := 'Calling Insert_Invoice_Dist';
692
693 Insert_Invoice_Dist(x_invoice_id => x_invoice_id,
694 x_invoice_line_number => l_invoice_line_number,
695 x_description => x_description,
696 x_calling_sequence => current_calling_sequence);
697
698 l_invoice_line_number := l_invoice_line_number + 1;
699
700 END LOOP;
701
702
703 EXCEPTION WHEN OTHERS THEN
704 IF (SQLCODE <> -20001) THEN
705 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
706 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
707 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
708 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
709 ||', Invoice Line Type = '|| x_line_type
710 ||', Total Match Amount = '||to_char(x_total_amount)
711 ||', Row Count = '||to_char(x_row_count)
712 ||', Description = '||x_Description);
713 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
714 END IF;
715 --clean up the PL/SQL tables.
716 x_othr_chrg_tab.delete;
717
718 APP_EXCEPTION.RAISE_EXCEPTION;
719 END Create_Invoice_Lines;
720
721
722 Procedure Insert_Invoice_Line( x_invoice_id IN NUMBER,
723 x_invoice_line_number IN NUMBER,
724 x_line_type IN VARCHAR2,
725 x_cost_factor_id IN NUMBER,
726 x_amount IN NUMBER,
727 x_base_amount IN NUMBER,
728 x_rounding_amt IN NUMBER,
729 x_rcv_transaction_id IN NUMBER,
730 x_ccid IN NUMBER,
731 x_description IN VARCHAR2,
732 x_calling_sequence IN VARCHAR2) IS
733
734 l_debug_info VARCHAR2(100);
735 current_calling_sequence VARCHAR2(2000);
736
737 BEGIN
738
739 current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
740
741 INSERT INTO AP_INVOICE_LINES
742 (INVOICE_ID,
743 LINE_NUMBER,
744 LINE_TYPE_LOOKUP_CODE,
745 /*OPEN ISSUE 2*/
746 --REQUESTER_ID,
747 DESCRIPTION,
748 LINE_SOURCE,
749 ORG_ID,
750 INVENTORY_ITEM_ID,
751 ITEM_DESCRIPTION,
752 SERIAL_NUMBER,
753 MANUFACTURER,
754 MODEL_NUMBER,
755 GENERATE_DISTS,
756 MATCH_TYPE,
757 DISTRIBUTION_SET_ID,
758 ACCOUNT_SEGMENT,
759 BALANCING_SEGMENT,
760 COST_CENTER_SEGMENT,
761 OVERLAY_DIST_CODE_CONCAT,
762 DEFAULT_DIST_CCID,
763 PRORATE_ACROSS_ALL_ITEMS,
764 LINE_GROUP_NUMBER,
765 ACCOUNTING_DATE,
766 PERIOD_NAME,
767 DEFERRED_ACCTG_FLAG,
768 DEF_ACCTG_START_DATE,
769 DEF_ACCTG_END_DATE,
770 DEF_ACCTG_NUMBER_OF_PERIODS,
771 DEF_ACCTG_PERIOD_TYPE,
772 SET_OF_BOOKS_ID,
773 AMOUNT,
774 BASE_AMOUNT,
775 ROUNDING_AMT,
776 QUANTITY_INVOICED,
777 UNIT_MEAS_LOOKUP_CODE,
778 UNIT_PRICE,
779 WFAPPROVAL_STATUS,
780 -- USSGL_TRANSACTION_CODE, - Bug 4277744
781 DISCARDED_FLAG,
782 ORIGINAL_AMOUNT,
783 ORIGINAL_BASE_AMOUNT,
784 ORIGINAL_ROUNDING_AMT,
785 CANCELLED_FLAG,
786 INCOME_TAX_REGION,
787 TYPE_1099,
788 STAT_AMOUNT,
789 PREPAY_INVOICE_ID,
790 PREPAY_LINE_NUMBER,
791 INVOICE_INCLUDES_PREPAY_FLAG,
792 CORRECTED_INV_ID,
793 CORRECTED_LINE_NUMBER,
794 PO_HEADER_ID,
795 PO_LINE_ID,
796 PO_RELEASE_ID,
797 PO_LINE_LOCATION_ID,
798 PO_DISTRIBUTION_ID,
799 RCV_TRANSACTION_ID,
800 FINAL_MATCH_FLAG,
801 ASSETS_TRACKING_FLAG,
802 ASSET_BOOK_TYPE_CODE,
803 ASSET_CATEGORY_ID,
804 /*OPEN ISSUE 2*/
805 /*PROJECT_ID,
806 TASK_ID,
807 EXPENDITURE_TYPE,
808 EXPENDITURE_ITEM_DATE,
809 EXPENDITURE_ORGANIZATION_ID,*/
810 PA_QUANTITY,
811 PA_CC_AR_INVOICE_ID,
812 PA_CC_AR_INVOICE_LINE_NUM,
813 PA_CC_PROCESSED_CODE,
814 /*OPEN ISSUE 2 */
815 --AWARD_ID,
816 AWT_GROUP_ID,
817 REFERENCE_1,
818 REFERENCE_2,
819 RECEIPT_VERIFIED_FLAG,
820 RECEIPT_REQUIRED_FLAG,
821 RECEIPT_MISSING_FLAG,
822 JUSTIFICATION,
823 EXPENSE_GROUP,
824 START_EXPENSE_DATE,
825 END_EXPENSE_DATE,
826 RECEIPT_CURRENCY_CODE,
827 RECEIPT_CONVERSION_RATE,
828 RECEIPT_CURRENCY_AMOUNT,
829 DAILY_AMOUNT,
830 WEB_PARAMETER_ID,
831 ADJUSTMENT_REASON,
832 MERCHANT_DOCUMENT_NUMBER,
833 MERCHANT_NAME,
834 MERCHANT_REFERENCE,
835 MERCHANT_TAX_REG_NUMBER,
836 MERCHANT_TAXPAYER_ID,
837 COUNTRY_OF_SUPPLY,
838 CREDIT_CARD_TRX_ID,
839 COMPANY_PREPAID_INVOICE_ID,
840 CC_REVERSAL_FLAG,
841 ATTRIBUTE_CATEGORY,
842 ATTRIBUTE1,
843 ATTRIBUTE2,
844 ATTRIBUTE3,
845 ATTRIBUTE4,
846 ATTRIBUTE5,
847 ATTRIBUTE6,
848 ATTRIBUTE7,
849 ATTRIBUTE8,
850 ATTRIBUTE9,
851 ATTRIBUTE10,
852 ATTRIBUTE11,
853 ATTRIBUTE12,
854 ATTRIBUTE13,
855 ATTRIBUTE14,
856 ATTRIBUTE15,
857 /* GLOBAL_ATTRIBUTE_CATEGORY,
858 GLOBAL_ATTRIBUTE1,
859 GLOBAL_ATTRIBUTE2,
860 GLOBAL_ATTRIBUTE3,
861 GLOBAL_ATTRIBUTE4,
862 GLOBAL_ATTRIBUTE5,
863 GLOBAL_ATTRIBUTE6,
864 GLOBAL_ATTRIBUTE7,
865 GLOBAL_ATTRIBUTE8,
866 GLOBAL_ATTRIBUTE9,
867 GLOBAL_ATTRIBUTE10,
868 GLOBAL_ATTRIBUTE11,
869 GLOBAL_ATTRIBUTE12,
870 GLOBAL_ATTRIBUTE13,
871 GLOBAL_ATTRIBUTE14,
872 GLOBAL_ATTRIBUTE15,
873 GLOBAL_ATTRIBUTE16,
874 GLOBAL_ATTRIBUTE17,
875 GLOBAL_ATTRIBUTE18,
876 GLOBAL_ATTRIBUTE19,
877 GLOBAL_ATTRIBUTE20, */
878 CREATION_DATE,
879 CREATED_BY,
880 LAST_UPDATED_BY,
881 LAST_UPDATE_DATE,
882 LAST_UPDATE_LOGIN,
883 PROGRAM_APPLICATION_ID,
884 PROGRAM_ID,
885 PROGRAM_UPDATE_DATE,
886 REQUEST_ID,
887 --ETAX: Invwkb
888 --OPEN ISSUE 2
889 --bug5708602
890 SHIP_TO_LOCATION_ID,
891
892 PRIMARY_INTENDED_USE,
893 PRODUCT_FISC_CLASSIFICATION,
894 TRX_BUSINESS_CATEGORY
895
896 ,PRODUCT_TYPE,
897 PRODUCT_CATEGORY,
898 USER_DEFINED_FISC_CLASS
899 ,COST_FACTOR_ID
900 ,PAY_AWT_GROUP_ID --bug 9689194
901 )
902 SELECT X_INVOICE_ID, --invoice_id
903 X_INVOICE_LINE_NUMBER, --invoice_line_number
904 X_LINE_TYPE, --line_type_lookup_code
905 /*OPEN ISSUE 2*/
906 --NULL, --requester_id
907 x_description, --description
908 'HEADER MATCH', --line_source
909 rcv.org_id, --org_id
910 NULL, --inventory_item_id
911 NULL, --item_Description
912 NULL, --serial_number
913 NULL, --manufacturer
914 NULL, --model_number
915 'D', --generate_dists
916 'OTHER_TO_RECEIPT', --match_type
917 NULL, --distribution_set_id
918 NULL, --account_segment
919 NULL, --balancing_Segment
920 NULL, --cost_center_segment
921 NULL, --overlay_dist_code_concat
922 x_ccid, --default_dist_ccid
923 'N', --prorate_across_all_items
924 NULL, --line_group_number
925 g_accounting_date, --accounting_date
926 g_period_name, --period_name
927 'N', --deferred_acctg_flag
928 NULL, --def_acctg_start_date
929 NULL, --def_acctg_end_date
930 NULL, --def_acctg_number_of_periods
931 NULL, --def_acctg_period_type
932 g_set_of_books_id , --set_of_books_id
933 x_amount, --amount
934 x_base_amount, --base_amount
935 x_rounding_amt, --rounding_amt
936 NULL, --quantity_invoiced
937 NULL, --unit_meas_lookup_code
938 NULL, --unit_price
939 decode(g_approval_workflow_flag,'Y'
940 ,'REQUIRED','NOT REQUIRED'), --wfapproval_status
941 -- Removed for bug 4277744
942 -- rsl.ussgl_transaction_code, --ussgl_transaction_code
943 'N', --discarded_flag
944 NULL, --original_amount
945 NULL, --original_base_amount
946 NULL, --original_rounding_amt
947 'N', --cancelled_flag
948 g_income_tax_region, --income_tax_region
949 g_type_1099, --type_1099
950 NULL, --stat_amount
951 NULL, --prepay_invoice_id
952 NULL, --prepay_line_number
953 NULL, --invoice_includes_prepay_flag
954 NULL, --corrected_inv_id
955 NULL, --corrected_line_number
956 rcv.po_header_id, --po_header_id
957 rcv.po_line_id, --po_line_id
958 rcv.po_release_id, --po_release_id
959 rcv.po_line_location_id, --po_line_location_id
960 NULL, --po_distribution_id
961 x_rcv_transaction_id, --rcv_transaction_id
962 NULL, --final_match_flag
963 'N', --assets_tracking_flag
964 g_asset_book_type_code, --asset_book_type_code
965 NULL, --asset_category_id
966 /*OPEN ISSUE 2*/
967 /*
968 NULL, --project_id
969 NULL, --task_id
970 NULL, --expenditure_type
971 NULL, --expenditure_item_date
972 NULL, --expenditure_organization_id
973 */
974 NULL, --pa_quantity
975 NULL, --pa_cc_ar_invoice_id
976 NULL, --pa_cc_ar_invoice_line_num
977 NULL, --pa_cc_processed_code
978 /*OPEN ISSUE 2*/
979 /* NULL, */ --award_id
980 NULL, --awt_group_id
981 NULL, --reference_1
982 NULL, --reference_2
983 NULL, --receipt_verified_flag
984 NULL, --receipt_required_flag
985 NULL, --receipt_missing_flag
986 NULL, --justification
987 NULL, --expense_group
988 NULL, --start_expense_date
989 NULL, --end_expense_date
990 NULL, --receipt_currency_amount
991 NULL, --receipt_conversion_rate
992 NULL, --receipt_currency_amount
993 NULL, --daily_amount
994 NULL, --web_parameter_id
995 NULL, --adjustment_reason
996 NULL, --merchant_document_number
997 NULL, --merchant_name
998 NULL, --merchant_reference
999 NULL, --merchant_tax_reg_number
1000 NULL, --merchant_taxpayer_id
1001 NULL, --country_of_supply
1002 NULL, --credit_card_trx_id
1003 NULL, --company_prepaid_invoice_id
1004 NULL, --cc_reversal_flag
1005 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute_category),''),--attribute_category
1006 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute1),''), --attribute1
1007 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute2),''), --attribute2
1008 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute3),''), --attribute3
1009 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute4),''), --attribute4
1010 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute5),''), --attribute5
1011 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute6),''), --attribute6
1012 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute7),''), --attribute7
1013 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute8),''), --attribute8
1014 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute9),''), --attribute9
1015 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute10),''), --attribute10
1016 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute11),''), --attribute11
1017 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute12),''), --attribute12
1018 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute13),''), --attribute13
1019 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute14),''), --attribute14
1020 NVL(DECODE(g_transfer_flag,'Y',rsl.attribute15),''), --attribute15
1021 /* OPEN ISSUE 1 */
1022 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
1023 X_GLOBAL_ATTRIBUTE1, --global_attribute1
1024 X_GLOBAL_ATTRIBUTE2, --global_attribute2
1025 X_GLOBAL_ATTRIBUTE3, --global_attribute3
1026 X_GLOBAL_ATTRIBUTE4, --global_attribute4
1027 X_GLOBAL_ATTRIBUTE5, --global_attribute5
1028 X_GLOBAL_ATTRIBUTE6, --global_attribute6
1029 X_GLOBAL_ATTRIBUTE7, --global_attribute7
1030 X_GLOBAL_ATTRIBUTE8, --global_attribute8
1031 X_GLOBAL_ATTRIBUTE9, --global_attribute9
1032 X_GLOBAL_ATTRIBUTE10, --global_attribute10
1033 X_GLOBAL_ATTRIBUTE11, --global_attribute11
1034 X_GLOBAL_ATTRIBUTE12, --global_attribute12
1035 X_GLOBAL_ATTRIBUTE13, --global_attribute13
1036 X_GLOBAL_ATTRIBUTE14, --global_attribute14
1037 X_GLOBAL_ATTRIBUTE15, --global_attribute15
1038 X_GLOBAL_ATTRIBUTE16, --global_attribute16
1039 X_GLOBAL_ATTRIBUTE17, --global_attribute17
1040 X_GLOBAL_ATTRIBUTE18, --global_attribute18
1041 X_GLOBAL_ATTRIBUTE19, --global_attribute19
1042 X_GLOBAL_ATTRIBUTE20, */
1043 sysdate, --creation_date
1044 g_user_id, --created_by
1045 g_user_id, --last_updated_by
1046 sysdate, --last_update_date
1047 g_login_id, --last update login
1048 NULL, --program_application_id
1049 NULL, --program_id
1050 NULL, --program_update_date
1051 NULL, --request_date
1052 --ETAX: Invwkb
1053 --OPEN ISSUE 2
1054 --bug5708602
1055 RCV.SHIP_TO_LOCATION_ID, --ship_to_location_id
1056 G_intended_use, --primary_intended_use
1057 G_product_fisc_class, --product_fisc_classification
1058 G_TRX_BUSINESS_CATEGORY, --trx_business_category
1059 G_product_type, --product_type
1060 G_product_category, --product_category
1061 G_user_defined_fisc_class, --user_defined_fisc_class
1062 X_COST_FACTOR_ID, --cost_factor_id
1063 NULL --pay_awt_group_id bug9689194
1064 FROM po_ap_receipt_match_v rcv,
1065 rcv_shipment_lines rsl
1066 WHERE rcv.rcv_transaction_id = x_rcv_transaction_id
1067 AND rsl.shipment_line_id = rcv.rcv_shipment_line_id;
1068
1069 EXCEPTION WHEN OTHERS THEN
1070 IF (SQLCODE <> -20001) THEN
1071 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1072 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1073 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1074 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
1075 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
1076 ||', Invoice Line Type = '|| x_line_type
1077 ||', Rcv Transaction id ='||to_char(x_rcv_transaction_id)
1078 ||', Amount = '||to_char(x_amount)
1079 ||', Base Amount = '||to_char(x_base_amount));
1080 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1081 END IF;
1082 APP_EXCEPTION.RAISE_EXCEPTION;
1083
1084 END Insert_Invoice_Line;
1085
1086
1087
1088 /*-------------------------------------------------------------------------
1089 INSERT_INVOICE_DIST
1090 This procedure inserts a distribution into ap_invoice distributions.
1091 --------------------------------------------------------------------------*/
1092
1093 Procedure Insert_Invoice_dist (
1094 X_invoice_id IN NUMBER,
1095 X_invoice_line_number IN NUMBER,
1096 X_description IN VARCHAR2,
1097 x_calling_sequence IN VARCHAR2) IS
1098
1099 l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
1100 current_calling_sequence VARCHAR2(2000);
1101 l_debug_info VARCHAR2(2000);
1102 l_copy_line_dff_flag VARCHAR2(1); -- Bug 6837035
1103
1104 Begin
1105
1106 -- Update calling sequence
1107 current_calling_sequence := 'Insert_Invoice_Dist <-' ||X_calling_sequence;
1108
1109 -- insert into ap_invoice_distributions
1110
1111 l_debug_info := 'insert into ap_invoice_distributions';
1112 -- Bug 6837035 Retrieve the profile value to check if the DFF info should be
1113 -- copied onto distributions for imported lines.
1114 l_copy_line_dff_flag := NVL(fnd_profile.value('AP_COPY_INV_LINE_DFF'),'N');
1115
1116 INSERT INTO ap_invoice_distributions (
1117 batch_id,
1118 invoice_id,
1119 invoice_line_number,
1120 invoice_distribution_id,
1121 distribution_line_number,
1122 line_type_lookup_code,
1123 description,
1124 dist_match_type,
1125 distribution_class,
1126 org_id,
1127 dist_code_combination_id,
1128 accounting_date,
1129 period_name,
1130 accrual_posted_flag,
1131 cash_posted_flag,
1132 amount_to_post,
1133 base_amount_to_post,
1134 posted_amount,
1135 posted_base_amount,
1136 je_batch_id,
1137 cash_je_batch_id,
1138 posted_flag,
1139 accounting_event_id,
1140 upgrade_posted_amt,
1141 upgrade_base_posted_amt,
1142 set_of_books_id,
1143 amount,
1144 base_amount,
1145 rounding_amt,
1146 match_status_flag,
1147 encumbered_flag,
1148 packet_id,
1149 -- ussgl_transaction_code, - Bug 4277744
1150 -- ussgl_trx_code_context, - Bug 4277744
1151 reversal_flag,
1152 parent_reversal_id,
1153 cancellation_flag,
1154 income_tax_region,
1155 type_1099,
1156 stat_amount,
1157 charge_applicable_to_dist_id,
1158 prepay_amount_remaining,
1159 prepay_distribution_id,
1160 parent_invoice_id,
1161 corrected_invoice_dist_id,
1162 corrected_quantity,
1163 other_invoice_id,
1164 po_distribution_id,
1165 rcv_transaction_id,
1166 unit_price,
1167 matched_uom_lookup_code,
1168 quantity_invoiced,
1169 final_match_flag,
1170 related_id,
1171 assets_addition_flag,
1172 assets_tracking_flag,
1173 asset_book_type_code,
1174 asset_category_id,
1175 pa_cc_ar_invoice_id,
1176 pa_cc_ar_invoice_line_num,
1177 pa_cc_processed_code,
1178 gms_burdenable_raw_cost,
1179 awt_flag,
1180 awt_group_id,
1181 awt_tax_rate_id,
1182 awt_gross_amount,
1183 awt_invoice_id,
1184 awt_origin_group_id,
1185 awt_invoice_payment_id,
1186 awt_withheld_amt,
1187 inventory_transfer_status,
1188 reference_1,
1189 reference_2,
1190 receipt_verified_flag,
1191 receipt_required_flag,
1192 receipt_missing_flag,
1193 justification,
1194 expense_group,
1195 start_expense_date,
1196 end_expense_date,
1197 receipt_currency_code,
1198 receipt_conversion_rate,
1199 receipt_currency_amount,
1200 daily_amount,
1201 web_parameter_id,
1202 adjustment_reason,
1203 merchant_document_number,
1204 merchant_name,
1205 merchant_reference,
1206 merchant_tax_reg_number,
1207 merchant_taxpayer_id,
1208 country_of_supply,
1209 credit_card_trx_id,
1210 company_prepaid_invoice_id,
1211 cc_reversal_flag,
1212 /*Invoice Lines: OPEN ISSUE2*/
1213 -- Bug 6837035 Uncommented the DFF fields so that they can be
1214 -- populated for imported lines.
1215 attribute_category,
1216 attribute1,
1217 attribute2,
1218 attribute3,
1219 attribute4,
1220 attribute5,
1221 attribute6,
1222 attribute7,
1223 attribute8,
1224 attribute9,
1225 attribute10,
1226 attribute11,
1227 attribute12,
1228 attribute13,
1229 attribute14,
1230 attribute15,
1231 /* Invoice Lines */
1232 /*OPEN ISSUE 1*/
1233 /*global_attribute_category,
1234 global_attribute1,
1235 global_attribute2,
1236 global_attribute3,
1237 global_attribute4,
1238 global_attribute5,
1239 global_attribute6,
1240 global_attribute7,
1241 global_attribute8,
1242 global_attribute9,
1243 global_attribute10,
1244 global_attribute11,
1245 global_attribute12,
1246 global_attribute13,
1247 global_attribute14,
1248 global_attribute15,
1249 global_attribute16,
1250 global_attribute17,
1251 global_attribute18,
1252 global_attribute19,
1253 global_attribute20,*/
1254 created_by,
1255 creation_date,
1256 last_updated_by,
1257 last_update_date,
1258 last_update_login,
1259 program_application_id,
1260 program_id,
1261 program_update_date,
1262 request_id,
1263 --ETAX: Invwkb
1264 --OPEN ISSUE 1
1265 /*,intended_use*/
1266 --Freight and Special Charges
1267 rcv_charge_addition_flag,
1268 pay_awt_group_id) --bug 9689194
1269 SELECT g_batch_id, --batch_id
1270 x_invoice_id, --invoice_id
1271 x_invoice_line_number, --invoice_line_number
1272 ap_invoice_distributions_s.nextval, --invoice_distribution_id
1273 1, --distribution_line_number
1274 ail.line_type_lookup_code, --line_type_lookup_code
1275 ail.description, --description
1276 'OTHER_TO_RECEIPT', --dist_match_type
1277 'PERMANENT', --distribution_class
1278 ail.org_id, --org_id
1279 ail.default_dist_ccid, --dist_code_combination_id
1280 ail.accounting_date, --accounting_date
1281 ail.period_name, --period_name
1282 'N', --accrual_posted_flag
1283 'N', --cash_posted_flag
1284 NULL, --amount_to_post
1285 NULL, --base_amount_to_post
1286 NULL, --posted_amount
1287 NULL, --posted_base_amount
1288 NULL, --je_batch_id
1289 NULL, --cash_je_batch_id
1290 'N', --posted_flag
1291 NULL, --accounting_event_id
1292 NULL, --upgrade_posted_amt
1293 NULL, --upgrade_base_posted_amt
1294 g_set_of_books_id, --set_of_books_id
1295 ail.amount, --amount
1296 ail.base_amount, --base_amount
1297 ail.rounding_amt, --rounding_amt
1298 NULL, --match_status_flag
1299 'N', --encumbered_flag
1300 NULL, --packet_id
1301 -- ail.ussgl_transaction_code, --ussgl_transaction_code - Bug 4277744
1302 -- NULL, --ussgl_trx_code_context - Bug 4277744
1303 'N', --reversal_flag
1304 NULL, --parent_reversal_id
1305 'N', --cancellation_flag
1306 decode(g_type_1099,'','',ail.income_tax_region) , --income_tax_region
1307 ail.type_1099, --type_1099
1308 NULL, --stat_amount
1309 NULL, --charge_applicable_to_dist_id
1310 NULL, --prepay_amount_remaining
1311 NULL, --prepay_distribution_id
1312 NULL, --parent_invoice_id
1313 NULL, --corrected_invoice_dist_id
1314 NULL, --corrected_quantity
1315 NULL, --other_invoice_id
1316 NULL, --po_distribution_id
1317 ail.rcv_transaction_id, --rcv_transaction_id
1318 NULL, --unit_price
1319 NULL, --matched_uom_lookup_code
1320 NULL, --quantity_invoiced
1321 NULL, --final_match_flag
1322 NULL, --related_id
1323 'U', --assets_addition_flag
1324 decode(gcc.account_type,'E',
1325 ail.assets_tracking_flag,
1326 'A','Y','N'), --assets_tracking_flag
1327 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,
1328 'A','Y','N'),'Y',ail.asset_book_type_code,NULL), --asset_book_type_code
1329 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,
1330 'A','Y','N'),'Y',ail.asset_category_id,NULL), --asset_category_id
1331 NULL, --pa_cc_ar_invoice_id
1332 NULL, --pa_cc_ar_invoice_line_num
1333 NULL, --pa_cc_processed_code
1334 NULL, --gms_burdenable_raw_cost
1335 NULL, --awt_flag
1336 NULL, --awt_group_id
1337 NULL, --awt_tax_rate_id
1338 NULL, --awt_gross_amount
1339 NULL, --awt_invoice_id
1340 NULL, --awt_origin_group_id
1341 NULL, --awt_invoice_payment_id
1342 NULL, --awt_withheld_amt
1343 'N', --inventory_transfer_status
1344 NULL, --reference_1
1345 NULL, --reference_2
1346 NULL, --receipt_verified_flag
1347 NULL, --receipt_required_flag
1348 NULL, --receipt_missing_flag
1349 NULL, --justification
1350 NULL, --expense_group
1351 NULL, --start_expense_date
1352 NULL, --end_expense_date
1353 NULL, --receipt_currency_code
1354 NULL, --receipt_conversion_rate
1355 NULL, --receipt_currency_amount
1356 NULL, --daily_amount
1357 NULL, --web_parameter_id
1358 NULL, --adjustment_reason
1359 NULL, --merchant_document_number
1360 NULL, --merchant_name
1361 NULL, --merchant_reference
1362 NULL, --merchant_tax_reg_number
1363 NULL, --merchant_taxpayer_id
1364 NULL, --country_of_supply
1365 NULL, --credit_card_trx_id
1366 NULL, --company_prepaid_invoice_id
1367 NULL, --cc_reversal_flag
1368 -- Bug 6837035 Start Need to copy DFF info from line for imported lines
1369 DECODE(line_source,'IMPORTED'
1370 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute_category,NULL),NULL), --attribute_category
1371 DECODE(line_source,'IMPORTED'
1372 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute1,NULL),NULL), --attribute1
1373 DECODE(line_source,'IMPORTED'
1374 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute2,NULL),NULL), --attribute2
1375 DECODE(line_source,'IMPORTED'
1376 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute3,NULL),NULL), --attribute3
1377 DECODE(line_source,'IMPORTED'
1378 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute4,NULL),NULL), --attribute4
1379 DECODE(line_source,'IMPORTED'
1380 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute5,NULL),NULL), --attribute5
1381 DECODE(line_source,'IMPORTED'
1382 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute6,NULL),NULL), --attribute6
1383 DECODE(line_source,'IMPORTED'
1384 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute7,NULL),NULL), --attribute7
1385 DECODE(line_source,'IMPORTED'
1386 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute8,NULL),NULL), --attribute8
1387 DECODE(line_source,'IMPORTED'
1388 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute9,NULL),NULL), --attribute9
1389 DECODE(line_source,'IMPORTED'
1390 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute10,NULL),NULL), --attribute10
1391 DECODE(line_source,'IMPORTED'
1392 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute11,NULL),NULL), --attribute11
1393 DECODE(line_source,'IMPORTED'
1394 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute12,NULL),NULL), --attribute12
1395 DECODE(line_source,'IMPORTED'
1396 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute13,NULL),NULL), --attribute13
1397 DECODE(line_source,'IMPORTED'
1398 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute14,NULL),NULL), --attribute14
1399 DECODE(line_source,'IMPORTED'
1400 ,DECODE(l_copy_line_dff_flag,'Y',ail.attribute15,NULL),NULL), --attribute15
1401 -- Bug 6837035 End.
1402 /*OPEN ISSUE1*/
1403 /*
1404 NULL, --global_attribute_category
1405 NULL, --global_attribute1
1406 NULL, --global_attribute2
1407 NULL, --global_attribute3
1408 NULL, --global_attribute4
1409 NULL, --global_attribute5
1410 NULL, --global_attribute6
1411 NULL, --global_attribute7
1412 NULL, --global_attribute8
1413 NULL, --global_attribute9
1414 NULL, --global_attribute10
1415 NULL, --global_attribute11
1416 NULL, --global_attribute12
1417 NULL, --global_attribute13
1418 NULL, --global_attribute14
1419 NULL, --global_attribute15 */
1420 ail.created_by, --created_by
1421 sysdate, --creation_date
1422 ail.last_updated_by, --last_updated_by
1423 sysdate, --last_update_date
1424 ail.last_update_login, --last_update_login
1425 NULL, --program_application_id
1426 NULL, --program_id
1427 NULL, --program_update_date
1428 NULL, --request_id
1429 --ETAX: Invwkb
1430 --OPEN ISSUE 1
1431 /*,rcv.intended_use */
1432 'N', --rcv_charge_addition_flag
1433 NULL --pay_awt_group_id bug9689194
1434 FROM ap_invoice_lines AIL,
1435 gl_code_combinations GCC,
1436 rcv_transactions rcv
1437 WHERE ail.invoice_id = x_invoice_id
1438 AND ail.line_number = x_invoice_line_number
1439 AND ail.rcv_transaction_id = rcv.transaction_id
1440 AND gcc.code_combination_id = ail.default_dist_ccid
1441 AND rcv.transaction_id = ail.rcv_transaction_id;
1442
1443
1444 UPDATE ap_invoice_distributions_all id
1445 SET (project_id,
1446 task_id,
1447 expenditure_type,
1448 expenditure_item_date,
1449 expenditure_organization_id,
1450 award_id) =
1451 (SELECT
1452 DECODE(PD.destination_type_code,'EXPENSE',
1453 PD.project_id,'SHOP FLOOR',PD.project_id,
1454 'INVENTORY',PD.project_id), --project_id
1455 DECODE(PD.destination_type_code,'EXPENSE',
1456 PD.task_id,'SHOP FLOOR',PD.task_id,
1457 'INVENTORY',PD.task_id), --task_id
1458 DECODE(PD.destination_type_code,'EXPENSE',
1459 PD.expenditure_type,
1460 'SHOP FLOOR',PD.expenditure_type,
1461 'INVENTORY', PD.expenditure_type), --expenditure_type
1462 DECODE(PD.destination_type_code,
1463 'EXPENSE',PD.expenditure_item_date,
1464 'SHOP FLOOR', PD.expenditure_item_date,
1465 'INVENTORY',PD.expenditure_item_date), --expenditure_item_date
1466 DECODE(PD.destination_type_code,
1467 'EXPENSE',PD.expenditure_organization_id,
1468 'SHOP FLOOR', PD.expenditure_organization_id,
1469 'INVENTORY', PD.expenditure_organization_id), --expenditure_organization_id
1470 DECODE(PD.destination_type_code,
1471 'EXPENSE', PD.award_id) --award_id
1472 FROM ap_invoice_distributions_all aid,
1473 rcv_transactions rcv,
1474 po_distributions_all pd
1475 WHERE aid.invoice_distribution_id = l_invoice_distribution_id
1476 AND aid.rcv_transaction_id = rcv.transaction_id
1477 AND rcv.po_distribution_id = pd.po_distribution_id)
1478 WHERE id.invoice_distribution_id = l_invoice_distribution_id;
1479
1480 --Bug 4539462 DBI logging
1481 AP_DBI_PKG.Maintain_DBI_Summary
1482 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1483 p_operation => 'I',
1484 p_key_value1 => x_invoice_id,
1485 p_key_value2 => l_invoice_distribution_id,
1486 p_calling_sequence => current_calling_sequence);
1487
1488 Exception
1489 WHEN others then
1490 If (SQLCODE <> -20001) Then
1491 fnd_message.set_name('SQLAP','AP_DEBUG');
1492 fnd_message.set_token('ERROR',SQLERRM);
1493 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
1494 fnd_message.set_token('PARAMETERS',
1495 'X_invoice_id= '||to_char(x_invoice_id)
1496 ||'X_invoice_line_number =' ||to_char(x_invoice_line_number));
1497 fnd_message.set_token('DEBUG_INFO',l_debug_info);
1498 End if;
1499 app_exception.raise_exception;
1500
1501
1502 End Insert_Invoice_dist;
1503
1504
1505 END AP_OTHR_CHRG_MATCH_PKG;
1506