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