[Home] [Help]
PACKAGE BODY: APPS.AP_RECT_MATCH_PKG
Source
1 PACKAGE BODY AP_RECT_MATCH_PKG AS
2 /*$Header: aprcvmtb.pls 120.80.12020000.7 2013/03/20 09:33:24 harsanan ship $*/
3
4 --LOCAL PROCEDURES
5 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
6 X_Invoice_Line_Number IN NUMBER DEFAULT NULL,
7 X_Match_Amount IN NUMBER,
8 X_Po_Line_Location_Id IN NUMBER,
9 X_Calling_Sequence IN VARCHAR2
10 ) ;
11
12
13 Procedure Get_Dist_Proration_Info(
14 X_Invoice_Id IN NUMBER,
15 X_Invoice_Line_Number IN NUMBER,
16 X_Rcv_Transaction_Id IN NUMBER,
17 X_Po_Line_Location_Id IN NUMBER,
18 X_Match_Mode IN VARCHAR2,
19 X_Match_Quantity IN NUMBER,
20 X_Match_Amount IN NUMBER,
21 X_Unit_Price IN NUMBER,
22 X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.DIST_TAB_TYPE,
23 X_Calling_Sequence IN VARCHAR2);
24
25
26 PROCEDURE Get_Total_Proration_Quantity(
27 X_Rcv_Transaction_id IN NUMBER,
28 X_PO_Line_Location_Id IN NUMBER,
29 X_Match_Quantity IN NUMBER,
30 X_Total_Quantity OUT NOCOPY NUMBER,
31 X_Matching_Case OUT NOCOPY VARCHAR2,
32 X_Calling_Sequence IN VARCHAR2);
33
34
35 Procedure Insert_Invoice_Line(
36 X_Invoice_Id IN NUMBER,
37 X_Invoice_Line_Number IN NUMBER,
38 X_Line_Type_Lookup_Code IN VARCHAR2,
39 X_Cost_Factor_Id IN NUMBER DEFAULT NULL,
40 X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
41 X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
42 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
43 X_Rcv_Transaction_Id IN NUMBER,
44 X_Amount IN NUMBER,
45 X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
46 X_Unit_Price IN NUMBER DEFAULT NULL,
47 X_Item_Line_Number IN NUMBER,
48 X_Charge_Line_Description IN VARCHAR2,
49 X_Retained_Amount IN NUMBER DEFAULT NULL,
50 X_Calling_Sequence IN VARCHAR2);
51
52
53 PROCEDURE Insert_Invoice_Distributions (
54 X_Invoice_ID IN NUMBER,
55 X_Invoice_Line_Number IN NUMBER,
56 X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.DIST_TAB_TYPE,
57 X_Rcv_Transaction_Id IN NUMBER,
58 X_Total_Amount IN NUMBER,
59 X_Calling_Sequence IN VARCHAR2);
60
61
62 Procedure Update_PO_Shipments_Dists(
63 X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.Dist_Tab_Type,
64 X_Po_Line_Location_Id IN NUMBER,
65 X_Match_Amount IN NUMBER,
66 X_Match_Quantity IN NUMBER,
67 X_Uom_Lookup_Code IN VARCHAR2,
68 X_Calling_Sequence IN VARCHAR2) ;
69
70
71 PROCEDURE Update_RCV_Transaction(
72 X_Rcv_Transaction_Id IN NUMBER,
73 X_Match_Amount IN NUMBER,
74 X_Match_Quantity IN NUMBER,
75 X_Uom_Lookup_Code IN VARCHAR2,
76 X_Calling_Sequence IN VARCHAR2);
77
78
79 Procedure Create_Charge_Lines(
80 X_Invoice_Id IN NUMBER,
81 X_Rcv_Transaction_id IN NUMBER,
82 X_Freight_Cost_Factor_Id IN NUMBER,
83 X_Freight_Amount IN NUMBER,
84 X_Freight_Description IN VARCHAR2,
85 X_Misc_Cost_Factor_Id IN NUMBER,
86 X_Misc_Amount IN NUMBER,
87 X_Misc_Description IN VARCHAR2,
88 X_Item_Line_Number IN NUMBER,
89 X_Calling_Sequence IN VARCHAR2);
90
91
92 PROCEDURE Get_Corr_Dist_Proration_Info(
93 X_Corrected_Invoice_Id IN NUMBER,
94 X_Corrected_Line_Number IN NUMBER,
95 X_Corr_Dist_Tab IN OUT NOCOPY
96 AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
97 X_Correction_Type IN VARCHAR2,
98 X_Correction_Amount IN NUMBER,
99 X_Correction_Quantity IN NUMBER,
100 X_Correction_Price IN NUMBER,
101 X_Match_Mode IN VARCHAR2,
102 X_Calling_Sequence IN VARCHAR2);
103
104
105 PROCEDURE Insert_Corr_Invoice_Line(
106 X_Invoice_Id IN NUMBER,
107 X_Invoice_Line_Number IN NUMBER,
108 X_Corrected_Invoice_Id IN NUMBER,
109 X_Corrected_Line_Number IN NUMBER,
110 X_Quantity IN NUMBER,
111 X_Amount IN NUMBER,
112 X_Unit_Price IN NUMBER,
113 X_Correction_Type IN VARCHAR2,
114 X_Po_Distribution_Id IN NUMBER,
115 X_Retained_Amount IN NUMBER DEFAULT NULL,
116 X_Calling_Sequence IN VARCHAR2);
117
118
119 PROCEDURE Insert_Corr_Invoice_Dists(
120 X_Invoice_Id IN NUMBER,
121 X_Invoice_Line_Number IN NUMBER,
122 X_Corrected_Invoice_id IN NUMBER,
123 X_Corr_Dist_Tab IN OUT NOCOPY
124 AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
125 X_Correction_Type IN VARCHAR2,
126 X_Total_Amount IN NUMBER,
127 X_Calling_Sequence IN VARCHAR2);
128
129
130 PROCEDURE Update_Corr_Po_Shipments_Dists(
131 X_Corr_Dist_Tab IN AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
132 X_Po_Line_Location_Id IN NUMBER,
133 X_Quantity IN NUMBER,
134 X_Amount IN NUMBER,
135 X_Correction_Type IN VARCHAR2,
136 X_Uom_Lookup_Code IN VARCHAR2,
137 X_Calling_Sequence IN VARCHAR2);
138
139
140 --Global Variable Declaration
141 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_RECT_MATCH_PKG';
142 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
143 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
144 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
145 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
146 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
147 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
148 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
149
150 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
151 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
152 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
153 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
154 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
155 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
156 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
157 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_RECT_MATCH_PKG.';
158
159
160 G_max_invoice_line_number ap_invoice_lines.line_number%TYPE := 0 ;
161 G_batch_id ap_batches.batch_id%TYPE;
162 G_accounting_date ap_invoice_lines.accounting_date%TYPE;
163 G_period_name gl_period_statuses.period_name%TYPE;
164 G_set_of_books_id ap_system_parameters.set_of_books_id%TYPE;
165 G_awt_group_id ap_awt_groups.group_id%TYPE;
166 G_pay_awt_group_id ap_awt_groups.group_id%TYPE; --bug 9689194
167 G_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
168 G_exchange_rate ap_invoices.exchange_rate%TYPE;
169 G_precision fnd_currencies.precision%TYPE;
170 G_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
171 G_system_allow_awt_flag ap_system_parameters.allow_awt_flag%TYPE;
172 G_site_allow_awt_flag po_vendor_sites.allow_awt_flag%TYPE;
173 G_transfer_flag ap_system_parameters.transfer_desc_flex_flag%TYPE;
174 G_allow_pa_override varchar2(1);
175 G_pa_expenditure_date_default varchar2(50);
176 G_base_currency_code ap_system_parameters.base_currency_code%TYPE;
177 G_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
178 G_prepay_ccid ap_system_parameters.prepay_code_combination_id%TYPE;
179 G_Build_Prepay_Accts_Flag ap_system_parameters.build_prepayment_accounts_flag%TYPE;
180 G_income_tax_region ap_system_parameters.income_tax_region%TYPE;
181 G_project_id pa_projects_all.project_id%TYPE;
182 G_task_id pa_tasks.task_id%TYPE;
183 G_expenditure_type pa_expenditure_types.expenditure_type%TYPE;
184 G_invoice_date ap_invoices.invoice_date%TYPE;
185 G_expenditure_organization_id pa_exp_orgs_it.organization_id%TYPE;
186 G_asset_book_type_code fa_book_controls.book_type_code%TYPE;
187 G_inventory_organization_id financials_system_parameters.inventory_organization_id%TYPE;
188 G_allow_account_override ap_system_parameters.allow_flex_override_flag%TYPE;
189 G_encumbrance_flag financials_system_parameters.purch_encumbrance_flag%TYPE;
190 G_match_option po_line_locations.match_option%TYPE;
191 G_approval_workflow_flag ap_system_parameters.approval_workflow_flag%TYPE;
192 --G_Ussgl_Transaction_Code ap_invoices.ussgl_transaction_code%TYPE; - Bug 4277744
193 G_login_id number;
194 G_User_id number;
195 G_Account_Segment ap_invoice_lines.account_segment%TYPE := NULL;
196 G_Balancing_Segment ap_invoice_lines.balancing_segment%TYPE := NULL;
197 G_Cost_Center_Segment ap_invoice_lines.cost_center_segment%TYPE := NULL;
198 G_Overlay_Dist_Code_Concat ap_invoice_lines.overlay_dist_code_concat%TYPE := NULL;
199 G_Default_Dist_CCid ap_invoice_lines.default_dist_ccid%TYPE := NULL;
200 G_Line_Project_id ap_invoice_lines.project_id%TYPE;
201 G_Line_Task_Id ap_invoice_lines.task_id%TYPE ;
202 G_Line_Award_id ap_invoice_lines.award_id%TYPE;
203 G_Line_Expenditure_Type ap_invoice_lines.expenditure_type%TYPE ;
204 G_Line_Expenditure_Item_Date ap_invoice_lines.expenditure_item_date%TYPE ;
205 G_Line_Expenditure_Org_Id ap_invoice_lines.expenditure_organization_id%TYPE ;
206 G_Award_Id ap_invoices.award_id%TYPE;
207 G_Line_Base_Amount ap_invoice_lines.base_amount%TYPE ;
208 G_Line_Awt_Group_Id ap_invoice_lines.awt_group_id%TYPE ;
209 G_Line_Pay_Awt_Group_Id ap_invoice_lines.pay_awt_group_id%TYPE ; --bug 9689194
210 G_Line_Accounting_Date ap_invoice_lines.accounting_date%TYPE;
211 G_Org_Id ap_invoices.org_id%TYPE;
212 G_Trx_Business_Category ap_invoices.trx_business_category%TYPE;
213
214 --Contract Payments
215 G_Shipment_Type po_line_locations_all.shipment_type%TYPE;
216 G_Vendor_Id ap_invoices.vendor_id%TYPE;
217 G_Vendor_Site_Id ap_invoices.vendor_site_id%TYPE;
218 G_Po_Line_Id po_lines_all.po_line_id%TYPE;
219 G_Recoupment_Rate po_lines_all.recoupment_rate%TYPE;
220
221 --Bugfix:5565310
222 G_intended_use zx_lines_det_factors.line_intended_use%type;
223 G_product_type zx_lines_det_factors.product_type%type;
224 G_product_category zx_lines_det_factors.product_category%type;
225 G_product_fisc_class zx_lines_det_factors.product_fisc_classification%type;
226 G_user_defined_fisc_class zx_lines_det_factors.user_defined_fisc_class%type;
227 G_assessable_value zx_lines_det_factors.assessable_value%type;
228 G_dflt_tax_class_code zx_transaction_lines_gt.input_tax_classification_code%type;
229 G_source ap_invoices_all.source%type;
230
231
232 PROCEDURE Base_Credit_RCV_Match(
233 X_match_mode IN VARCHAR2,
234 X_invoice_id IN NUMBER,
235 X_invoice_line_number IN NUMBER,
236 X_Po_Line_Location_id IN NUMBER,
237 X_rcv_transaction_id IN NUMBER,
238 X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.DIST_TAB_TYPE,
239 X_amount IN NUMBER,
240 X_quantity IN NUMBER,
241 X_unit_price IN NUMBER,
242 X_uom_lookup_code IN VARCHAR2,
243 X_freight_cost_factor_id IN NUMBER DEFAULT NULL,
244 X_freight_amount IN NUMBER,
245 X_freight_description IN VARCHAR2,
246 X_misc_cost_factor_id IN NUMBER DEFAULT NULL,
247 X_misc_amount IN NUMBER,
248 X_misc_description IN VARCHAR2,
249 X_retained_amount IN NUMBER DEFAULT NULL,
250 X_calling_sequence IN VARCHAR2) IS
251
252 l_single_dist_flag VARCHAR2(1) := 'N';
253 l_first NUMBER;
254 l_po_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
255 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
256 l_item_line_number ap_invoice_lines.line_number%TYPE;
257 l_amount_to_recoup ap_invoice_lines.amount%TYPE;
258 l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
259 l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
260 l_retained_amount ap_invoice_lines_all.retained_amount%TYPE;
261 l_debug_info VARCHAR2(2000);
262 current_calling_sequence VARCHAR2(2000);
263 l_Sqlerrm VARCHAR2(2000);
264 l_success BOOLEAN;
265 l_error_message VARCHAR2(4000);
266 l_api_name VARCHAR2(32);
267
268 --bugfix:5565310
269 l_ref_doc_application_id zx_transaction_lines_gt.ref_doc_application_id%TYPE;
270 l_ref_doc_entity_code zx_transaction_lines_gt.ref_doc_entity_code%TYPE;
271 l_ref_doc_event_class_code zx_transaction_lines_gt.ref_doc_event_class_code%TYPE;
272 l_ref_doc_line_quantity zx_transaction_lines_gt.ref_doc_line_quantity%TYPE;
273 l_po_header_curr_conv_rat po_headers_all.rate%TYPE;
274 l_ref_doc_trx_level_type zx_transaction_lines_gt.ref_doc_trx_level_type%TYPE;
275 l_po_header_curr_conv_rate po_headers_all.rate%TYPE;
276 l_uom_code mtl_units_of_measure.uom_code%TYPE;
277 l_ref_doc_trx_id po_headers_all.po_header_id%TYPE;
278 l_error_code varchar2(2000);
279 l_po_line_location_id po_line_locations.line_location_id%TYPE;
280 l_dummy number;
281
282 -- bug 8483345: start
283 l_product_org_id ap_invoices.org_id%TYPE;
284 l_allow_tax_code_override varchar2(10);
285 l_dflt_tax_class_code zx_transaction_lines_gt.input_tax_classification_code%type;
286 -- bug 8483345: end
287
288 l_code_combination_id NUMBER ; -- Bug 10050094
289 l_full_mtch_amt NUMBER; --Bug13505998
290 l_line_amt NUMBER; --Bug13505998
291
292 BEGIN
293
294 l_api_name := 'Base_Credit_RCV_Match';
295
296 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
297 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Base_Credit_Rcv_Match(+)');
298 END IF;
299
300 -- Update the calling sequence (for error message).
301 current_calling_sequence := 'AP_MATCHING_PKG.base_credit_rcv_match<-'||X_calling_sequence;
302
303 l_debug_info := 'Get Invoice and System Options information';
304 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
306 END IF;
307
308 get_info( X_Invoice_Id => x_invoice_id,
309 X_Invoice_Line_Number => x_invoice_line_number,
310 X_Match_Amount => x_amount,
311 X_Po_Line_Location_Id => x_po_line_location_id,
312 X_Calling_Sequence => current_calling_sequence);
313
314
315 IF g_invoice_type_lookup_code <> 'PREPAYMENT' THEN
316 l_retained_amount := AP_INVOICE_LINES_UTILITY_PKG.Get_Retained_Amount
317 (p_line_location_id => x_po_line_location_id,
318 p_match_amount => x_amount);
319 END IF;
320
321 l_debug_info := 'Get PO information';
322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
323 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
324 END IF;
325
326 l_po_line_location_id := x_po_line_location_id;
327
328 l_success := AP_ETAX_UTILITY_PKG.Get_PO_Info(
329 P_Po_Line_Location_Id => l_po_line_location_id,
330 P_PO_Distribution_Id => null,
331 P_Application_Id => l_ref_doc_application_id,
332 P_Entity_code => l_ref_doc_entity_code,
333 P_Event_Class_Code => l_ref_doc_event_class_code,
334 P_PO_Quantity => l_ref_doc_line_quantity,
335 P_Product_Org_Id => l_product_org_id, -- 8483345
336 P_Po_Header_Id => l_ref_doc_trx_id,
337 P_Po_Header_curr_conv_rate => l_po_header_curr_conv_rate,
338 P_Uom_Code => l_uom_code,
339 P_Dist_Qty => l_dummy,
340 P_Ship_Price => l_dummy,
341 P_Error_Code => l_error_code,
342 P_Calling_Sequence => current_calling_sequence);
343
344 -- Bug 10050094
345 AP_ETAX_UTILITY_PKG.Get_Default_CCID( p_invoice_id => x_invoice_id,
346 p_default_dist_ccid => g_default_dist_ccid,
347 p_po_line_location_id => l_po_line_location_id,
348 p_invoice_line_number => X_invoice_line_number,
349 p_calling_sequence => current_calling_sequence,
350 x_derived_ccid => l_code_combination_id ) ;
351
352 -- bug 8483345: start
353 l_debug_info := 'Get Default Tax Classification';
354 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
355 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
356 END IF;
357
358 g_dflt_tax_class_code := null; /*bug12428818*/
359
360 ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification
361 (p_ref_doc_application_id => l_ref_doc_application_id,
362 p_ref_doc_entity_code => l_ref_doc_entity_code,
363 p_ref_doc_event_class_code => l_ref_doc_event_class_code,
364 p_ref_doc_trx_id => l_ref_doc_trx_id,
365 p_ref_doc_line_id => x_po_line_location_id,
366 p_ref_doc_trx_level_type => 'SHIPMENT',
367 p_vendor_id => g_vendor_id,
368 p_vendor_site_id => g_vendor_site_id,
369 p_code_combination_id => l_code_combination_id, -- Bug 10050094 g_default_dist_ccid,
370 p_concatenated_segments => null,
371 p_templ_tax_classification_cd => null,
372 p_ship_to_location_id => null,
373 p_ship_to_loc_org_id => null,
374 p_inventory_item_id => null,
375 p_item_org_id => l_product_org_id,
376 p_tax_classification_code => g_dflt_tax_class_code,
377 p_allow_tax_code_override_flag => l_allow_tax_code_override,
378 APPL_SHORT_NAME => 'SQLAP',
379 FUNC_SHORT_NAME => 'NONE',
380 p_calling_sequence => 'AP_ETAX_SERVICES_PKG',
381 p_event_class_code => l_ref_doc_event_class_code,
382 p_entity_code => 'AP_INVOICES',
383 p_application_id => 200,
384 p_internal_organization_id => g_org_id);
385 -- bug 8483345: end
386
387 l_debug_info := 'Get PO Tax Attributes';
388 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
389 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
390 END IF;
391
392 AP_Etax_Services_Pkg.Get_Po_Tax_Attributes(
393 p_application_id => l_ref_doc_application_id,
394 p_org_id => g_org_id,
395 p_entity_code => l_ref_doc_entity_code,
396 p_event_class_code => l_ref_doc_event_class_code,
397 p_trx_level_type => 'SHIPMENT',
398 p_trx_id => l_ref_doc_trx_id,
399 p_trx_line_id => x_po_line_location_id,
400 x_line_intended_use => g_intended_use,
401 x_product_type => g_product_type,
402 x_product_category => g_product_category,
403 x_product_fisc_classification => g_product_fisc_class,
404 x_user_defined_fisc_class => g_user_defined_fisc_class,
405 x_assessable_value => g_assessable_value,
406 x_tax_classification_code => l_dflt_tax_class_code -- 8483345
407 );
408
409 -- bug 8483345: start
410 -- if tax classification code not retrieved from hierarchy
411 -- retrieve it from PO
412 IF (g_dflt_tax_class_code is null) THEN
413 g_dflt_tax_class_code := l_dflt_tax_class_code;
414 END IF;
415 -- bug 8483345: end
416
417 l_debug_info := 'g_intended_use,g_product_type,g_product_category,g_product_fisc_class '
418 ||g_intended_use||','||g_product_type||','||g_product_category||','||g_product_fisc_class;
419
420 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
422 END IF;
423
424 l_debug_info := 'g_user_defined_fisc_class,g_assessable_value,g_dflt_tax_class_code '
425 ||g_user_defined_fisc_class||','||g_assessable_value||','||g_dflt_tax_class_code;
426 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
428 END IF;
429
430 IF g_source = 'ISP'
431 and x_invoice_line_number IS NOT NULL THEN
432
433 --Bug#13505998
434 l_full_mtch_amt :=
435 ap_matching_utils_pkg.get_full_mtch_amt
436 (X_Po_Line_Location_id,G_Invoice_Currency_Code);
437
438 l_debug_info := 'l_full_mtch_amt '||l_full_mtch_amt;
439
440 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
441 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
442 END IF;
443
444 SELECT AMOUNT
445 INTO l_line_amt
446 FROM ap_invoice_lines_all
447 WHERE invoice_id = x_invoice_id
448 AND line_number = x_invoice_line_number;
449
450 l_debug_info := 'l_line_amt '||l_line_amt;
451
452 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
454 END IF;
455
456 --End Bug#13505998
457
458 UPDATE ap_invoice_lines_all
459 SET primary_intended_use = nvl(primary_intended_use, g_intended_use)
460 ,product_type = nvl(product_type, g_product_type)
461 ,product_category = nvl(product_category, g_product_category)
462 ,product_fisc_classification = nvl(product_fisc_classification, g_product_fisc_class)
463 ,user_defined_fisc_class = nvl(user_defined_fisc_class, g_user_defined_fisc_class)
464 ,assessable_value = nvl(assessable_value,
465 decode(l_full_mtch_amt,l_line_amt,
466 g_assessable_value,NULL)) /*Introduced decode for bug#13505998*/
467 ,tax_classification_code = nvl(tax_classification_code, g_dflt_tax_class_code)
468 WHERE invoice_id = x_invoice_id
469 AND line_number = x_invoice_line_number;
470
471 END IF;
472
473 l_debug_info := 'Get Distribution information';
474
475 Get_Dist_Proration_Info( X_Invoice_Id => x_invoice_id,
476 X_Invoice_Line_Number => x_invoice_line_number,
477 X_Rcv_Transaction_Id => x_rcv_transaction_id,
478 X_Po_Line_Location_Id => x_po_line_location_id,
479 X_Match_Mode => x_match_mode,
480 X_Match_Quantity => x_quantity,
481 X_Match_Amount => x_amount,
482 X_Unit_Price => x_unit_price,
483 X_Dist_Tab => x_dist_tab,
484 X_Calling_Sequence => current_calling_sequence);
485
486 IF (x_dist_tab.COUNT = 1) THEN
487
488 l_single_dist_flag := 'Y';
489 l_po_distribution_id := x_dist_tab.FIRST;
490 l_invoice_distribution_id := x_dist_tab(l_po_distribution_id).invoice_distribution_id;
491
492 END IF;
493
494 --Create a invoice line if one doesn't exist already.
495 IF (x_invoice_line_number IS NULL) THEN
496
497 l_debug_info := 'Create Matched Invoice Line';
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
500 END IF;
501 Insert_Invoice_Line(X_Invoice_ID => x_invoice_id,
502 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
503 X_Line_Type_Lookup_Code => 'ITEM',
504 X_Single_Dist_Flag => l_single_dist_flag,
505 X_Po_Distribution_Id => l_po_distribution_id,
506 X_Po_Line_Location_id => x_po_line_location_id,
507 X_Rcv_Transaction_id => x_rcv_transaction_id,
508 X_Amount => x_amount,
509 X_Quantity_Invoiced => x_quantity,
510 X_Unit_Price => x_unit_price,
511 X_Item_Line_Number => NULL,
512 X_Charge_Line_Description => NULL,
513 X_Retained_Amount => l_retained_amount,
514 X_Calling_Sequence => current_calling_sequence);
515
516 END IF;
517
518 l_debug_info := 'Create Matched Invoice Distributions';
519 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
520 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
521 END IF;
522 Insert_Invoice_Distributions(
523 X_Invoice_ID => x_invoice_id,
524 X_Invoice_Line_Number => nvl(x_invoice_line_number,
525 g_max_invoice_line_number),
526 X_Dist_Tab => x_dist_tab,
527 X_rcv_Transaction_Id => x_rcv_transaction_id,
528 X_Total_Amount => x_amount,
529 X_Calling_Sequence => current_calling_sequence);
530
531
532 IF (x_invoice_line_number IS NOT NULL) THEN
533
534 IF (l_single_dist_flag = 'Y') THEN
535
536 l_debug_info := 'If the line is matched down to 1 po distribution then need to
537 update the line with po_distribution_id, award_id,requester_id,
538 ,projects related information and generate_dists';
539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
541 END IF;
542
543 UPDATE ap_invoice_lines ail
544 SET (generate_dists ,
545 requester_id ,
546 po_distribution_id ,
547 rcv_shipment_line_id,
548 project_id ,
549 task_id ,
550 expenditure_type ,
551 expenditure_item_date ,
552 expenditure_organization_id ,
553 pa_quantity ,
554 award_id,
555 attribute_category,
556 attribute1,
557 attribute2,
558 attribute3,
559 attribute4,
560 attribute5,
561 attribute6,
562 attribute7,
563 attribute8,
564 attribute9,
565 attribute10,
566 attribute11,
567 attribute12,
568 attribute13,
569 attribute14,
570 attribute15,
571 retained_amount,
572 retained_amount_remaining) =
573 (SELECT 'D',
574 pd.deliver_to_person_id,
575 aid.po_distribution_id,
576 rcv.shipment_line_id,
577 aid.project_id,
578 aid.task_id,
579 aid.expenditure_type,
580 aid.expenditure_item_date,
581 aid.expenditure_organization_id,
582 aid.pa_quantity,
583 gms_ap_api.get_distribution_award(aid.award_id),
584 /* Bug 7483260. If the attribute field is populated in the
585 * interface, take that value. If the attribute field from
586 * the interface is null and the transfer_desc_flex_flag is
587 * Y, take the value from the purchase order.
588 */
589 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
590 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
591 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
592 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
593 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
594 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
595 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
596 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
597 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
598 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
599 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
600 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
601 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
602 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
603 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
604 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
605 --end Bug 7483260
606 l_retained_amount,
607 -1 * l_retained_amount
608 FROM ap_invoice_distributions aid,
609 po_distributions pd,
610 po_line_locations pll,
611 rcv_transactions rcv
612 WHERE aid.invoice_distribution_id = l_invoice_distribution_id
613 AND pd.po_distribution_id = aid.po_distribution_id
614 AND pll.line_location_id = pd.line_location_id
615 AND rcv.po_line_location_id = pll.line_location_id
616 AND rcv.transaction_id = x_rcv_transaction_id )
617 WHERE ail.invoice_id = x_invoice_id
618 AND ail.line_number = x_invoice_line_number;
619
620
621 ELSE
622
623 l_debug_info := 'Update the generate_dists to D after the distributions are created';
624 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
626 END IF;
627
628 UPDATE ap_invoice_lines ail
629 SET (generate_dists ,
630 rcv_shipment_line_id,
631 attribute_category,
632 attribute1,
633 attribute2,
634 attribute3,
635 attribute4,
636 attribute5,
637 attribute6,
638 attribute7,
639 attribute8,
640 attribute9,
641 attribute10,
642 attribute11,
643 attribute12,
644 attribute13,
645 attribute14,
646 attribute15,
647 retained_amount,
648 retained_amount_remaining)
649 =
650 (SELECT 'D',
651 rcv.shipment_line_id,
652 /* Bug 7483260. If the attribute field is populated in the
653 * interface, take that value. If the attribute field from
654 * the interface is null and the transfer_desc_flex_flag is
655 * Y, take the value from the purchase order.
656 */
657 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
658 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
659 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
660 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
661 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
662 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
663 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
664 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
665 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
666 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
667 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
668 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
669 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
670 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
671 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
672 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
673 --end Bug 7483260
674 l_retained_amount,
675 -1 * l_retained_amount
676 FROM ap_invoice_lines ail1,
677 po_line_locations pll,
678 rcv_transactions rcv
679 WHERE ail1.invoice_id = x_invoice_id
680 AND ail1.line_number =x_invoice_line_number
681 AND pll.line_location_id = ail1.po_line_location_id
682 AND rcv.po_line_location_id = pll.line_location_id
683 AND rcv.transaction_id = x_rcv_transaction_id)
684 WHERE invoice_id = x_invoice_id
685 AND line_number = x_invoice_line_number;
686
687 END IF;
688
689 END IF;
690
691 l_debug_info := 'Create Retainage Distributions';
692 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
693 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
694 END IF;
695
696 Ap_Retainage_Pkg.Create_Retainage_Distributions
697 (x_invoice_id => x_invoice_id,
698 x_invoice_line_number => nvl(x_invoice_line_number,g_max_invoice_line_number));
699
700 IF (G_Recoupment_Rate IS NOT NULL and x_amount > 0
701 and g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
702
703 l_debug_info := 'Calculate the maximum amount that can be recouped from this invoice line';
704 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
705 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
706 END IF;
707 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
708 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
709 END IF;
710
711 l_line_amt_net_retainage := x_amount + nvl(l_retained_amount,0);
712
713 l_max_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
714 (x_amount * g_recoupment_rate / 100) ,g_invoice_currency_code);
715
716 IF (l_line_amt_net_retainage < l_max_amount_to_recoup) THEN
717 l_amount_to_recoup := l_line_amt_net_retainage;
718 ELSE
719 l_amount_to_recoup := l_max_amount_to_recoup;
720 END IF;
721
722 l_debug_info := 'Automatically recoup any available prepayments against the same po line';
723 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
724 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
725 END IF;
726
727 l_success := AP_Matching_Utils_Pkg.Ap_Recoup_Invoice_Line(
728 P_Invoice_Id => x_invoice_id ,
729 P_Invoice_Line_Number => nvl(x_invoice_line_number,g_max_invoice_line_number) ,
730 P_Amount_To_Recoup => l_amount_to_recoup,
731 P_Po_Line_Id => g_po_line_id,
732 P_Vendor_Id => g_vendor_id,
733 P_Vendor_Site_Id => g_vendor_site_id,
734 P_Accounting_Date => g_accounting_date,
735 P_Period_Name => g_period_name,
736 P_User_Id => g_user_id,
737 P_Last_Update_Login => g_login_id ,
738 P_Error_Message => l_error_message,
739 P_Calling_Sequence => current_calling_sequence);
740
741 END IF;
742
743 l_debug_info := 'Update Quantity/Amount Billed on the PO/Shipments Distributions';
744 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
745 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
746 END IF;
747 Update_PO_Shipments_Dists(X_Dist_Tab => x_dist_tab,
748 X_Po_Line_Location_id => x_po_line_location_id,
749 X_Match_Amount => x_amount,
750 X_Match_Quantity => x_quantity,
751 X_Uom_Lookup_Code => x_uom_lookup_code,
752 X_Calling_Sequence => current_calling_sequence);
753
754
755 l_debug_info := 'Update Quantity/Amount Billed on the Receipt';
756 Update_RCV_Transaction (X_Rcv_Transaction_Id => x_rcv_transaction_id,
757 X_Match_Amount => x_amount,
758 X_Match_Quantity => x_quantity,
759 X_Uom_Lookup_code => x_uom_lookup_code,
760 X_Calling_Sequence => current_calling_sequence);
761
762 IF (x_freight_amount IS NOT NULL or x_misc_amount IS NOT NULL) THEN
763
764 l_debug_info := 'Call the procedure to create charge lines';
765 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
766 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
767 END IF;
768
769 --Due to the way PL/SQL binding is done for global variables, need
770 --pass the local instead of global variable for this as global variable
771 --is being updated before the x_item_line_number is used during runtime.
772
773 l_item_line_number := g_max_invoice_line_number;
774
775 Create_Charge_Lines(X_Invoice_Id => x_invoice_id,
776 X_Rcv_Transaction_Id => x_rcv_transaction_id,
777 X_Freight_Cost_Factor_Id => x_freight_cost_factor_id,
778 X_Freight_Amount => x_freight_amount,
779 X_Freight_Description => x_freight_description,
780 X_Misc_Cost_Factor_Id => x_misc_cost_factor_id,
781 X_Misc_Amount => x_misc_amount,
782 X_Misc_Description => x_misc_description,
783 X_Item_Line_Number => l_item_line_number,
784 X_Calling_Sequence => current_calling_sequence);
785
786 END IF;
787
788 --Clean up the PL/SQL table
789 x_dist_tab.delete;
790
791 EXCEPTION
792 WHEN OTHERS THEN
793 IF (SQLCODE <> -20001) THEN
794 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
795 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
796 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
797 FND_MESSAGE.SET_TOKEN('PARAMETERS','Match Mode = '||x_match_mode
798 ||', Invoice Id = '||to_char(x_invoice_id)
799 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
800 ||', Shipment ID = '||to_char(x_po_line_location_id)
801 ||', Rcv Transaction ID = '||TO_CHAR(X_Rcv_Transaction_id)
802 ||', Match amount = '||to_char(x_amount)
803 ||', Match quantity = '||to_char(x_quantity)
804 ||', PO UOM = '||x_uom_lookup_code
805 ||', Freight Amount = '||to_char(x_freight_amount)
806 ||', Freight Description = '||x_freight_description
807 ||', Misc Amount = '||to_char(x_misc_amount)
808 ||', Misc Description = '||x_misc_description);
809 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
810 END IF;
811
812 --Clean up the PL/SQL table
813 x_dist_tab.delete;
814
815 APP_EXCEPTION.RAISE_EXCEPTION;
816
817
818 END Base_Credit_RCV_Match;
819
820
821
822 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
823 X_Invoice_Line_Number IN NUMBER DEFAULT NULL,
824 X_Match_Amount IN NUMBER ,
825 X_Po_Line_Location_Id IN NUMBER,
826 X_Calling_Sequence IN VARCHAR2 ) IS
827
828 current_calling_sequence VARCHAR2(2000);
829 l_accounting_date ap_invoices_all.gl_date%TYPE;
830 l_debug_info VARCHAR2(2000);
831 l_api_name VARCHAR2(32);
832 BEGIN
833
834 l_api_name := 'Get_Info';
835 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
836 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Get_Info(+)');
837 END IF;
838 current_calling_sequence := 'Get_Info<-'||Current_Calling_Sequence;
839
840 SELECT ai.gl_date,
841 ai.batch_id,
842 ai.set_of_books_id,
843 ai.awt_group_id,
844 ai.invoice_type_lookup_code,
845 ai.exchange_rate,
846 fc.precision,
847 fc.minimum_accountable_unit,
848 nvl(asp.allow_awt_flag,'N'),
849 nvl(pvs.allow_awt_flag,'N'),
850 nvl(asp.transfer_desc_flex_flag,'N'),
851 asp.base_currency_code,
852 ai.invoice_currency_code,
853 nvl(pvs.prepay_code_combination_id,
854 asp.prepay_code_combination_id),
855 nvl(asp.build_prepayment_accounts_flag,'N'),
856 decode(pv.type_1099,'','',
857 decode(combined_filing_flag,'N',NULL,
858 decode(nvl(asp.income_tax_region_flag,'N'),'Y',pvs.state,
859 asp.income_tax_region))),
860 ai.project_id,
861 ai.task_id,
862 ai.expenditure_type,
863 ai.invoice_date,
864 ai.expenditure_organization_id,
865 fsp.inventory_organization_id,
866 nvl(asp.allow_flex_override_flag,'N'),
867 nvl(fsp.purch_encumbrance_flag,'N'),
868 nvl(asp.approval_workflow_flag,'N'),
869 -- ai.ussgl_transaction_code, -- Removed for bug 4277744
870 ai.award_id,
871 ai.org_id,
872 --ETAX: Invwkb
873 ai.trx_business_category,
874 ai.vendor_id,
875 ai.vendor_site_id,
876 ai.source,
877 ai.pay_awt_group_id --bug 9689194
878 INTO g_accounting_date,
879 g_batch_id,
880 g_set_of_books_id,
881 g_awt_group_id,
882 g_invoice_type_lookup_code,
883 g_exchange_rate,
884 g_precision,
885 g_min_acct_unit,
886 g_system_allow_awt_flag,
887 g_site_allow_awt_flag,
888 g_transfer_flag,
889 g_base_currency_code,
890 g_invoice_currency_code,
891 g_prepay_ccid,
892 g_build_prepay_accts_flag,
893 g_income_tax_region,
894 g_project_id,
895 g_task_id,
896 g_expenditure_type,
897 g_invoice_date,
898 g_expenditure_organization_id,
899 g_inventory_organization_id,
900 g_allow_account_override,
901 g_encumbrance_flag,
902 g_approval_workflow_flag,
903 -- g_ussgl_transaction_code, -- Removed for bug 4277744
904 g_award_id,
905 g_org_id,
906 --ETAX: Invwkb
907 g_trx_business_category,
908 g_vendor_id,
909 g_vendor_site_id,
910 g_source,
911 g_pay_awt_group_id --bug 9689194
912 /* Bug 5572876, using base tables */
913 FROM ap_invoices_all ai ,
914 ap_system_parameters_all asp,
915 ap_suppliers pv,
916 ap_supplier_sites_all pvs,
917 financials_system_params_all fsp,
918 fnd_currencies fc
919 WHERE ai.invoice_id = x_invoice_id
920 AND ai.vendor_site_id = pvs.vendor_site_id
921 AND pv.vendor_id = pvs.vendor_id
922 AND ai.org_id = asp.org_id
923 AND asp.org_id = fsp.org_id
924 AND ai.set_of_books_id = asp.set_of_books_id
925 AND asp.set_of_books_id = fsp.set_of_books_id
926 AND ai.invoice_currency_code = fc.currency_code (+);
927
928 IF (g_invoice_currency_code <> g_base_currency_code) THEN
929 g_line_base_amount := ap_utilities_pkg.ap_round_currency(
930 x_match_amount * g_exchange_rate,
931 g_base_currency_code);
932 END IF;
933
934
935
936 IF (x_invoice_line_number IS NOT NULL) THEN
937
938 SELECT ail.account_segment,
939 ail.balancing_segment,
940 ail.cost_center_segment,
941 ail.overlay_dist_code_concat,
942 ail.default_dist_ccid,
943 ail.project_id,
944 ail.task_id,
945 ail.award_id,
946 ail.expenditure_type,
947 ail.expenditure_item_date,
948 ail.expenditure_organization_id,
949 ail.awt_group_id,
950 ail.accounting_date,
951 ail.pay_awt_group_id
952 INTO g_account_segment,
953 g_balancing_segment,
954 g_cost_center_segment,
955 g_overlay_dist_code_concat,
956 g_default_dist_ccid,
957 g_line_project_id,
958 g_line_task_id,
959 g_line_award_id,
960 g_line_expenditure_type,
961 g_line_expenditure_item_date,
962 g_line_expenditure_org_id,
963 g_line_awt_group_id,
964 g_line_accounting_date,
965 g_line_pay_awt_group_id --bug 9689194
966 FROM ap_invoice_lines ail
967 WHERE ail.invoice_id = x_invoice_id
968 AND ail.line_number = x_invoice_line_number;
969
970 END IF;
971
972 SELECT nvl(max(ail.line_number),0)
973 INTO g_max_invoice_line_number
974 FROM ap_invoice_lines ail
975 WHERE ail.invoice_id = x_invoice_id;
976
977
978 IF (x_po_line_location_id is not null) THEN
979
980 SELECT pll.match_option,pll.shipment_type, pll.po_line_id, pl.recoupment_rate
981 INTO g_match_option,g_shipment_type, g_po_line_id, g_recoupment_rate
982 FROM po_line_locations pll, po_lines pl
983 WHERE pll.line_location_id = x_po_line_location_id
984 AND pll.po_line_id = pl.po_line_id;
985
986 END IF;
987
988
989 /* Bug 5572876 */
990 g_asset_book_type_code := Ap_Utilities_Pkg.Ledger_Asset_Book
991 (g_set_of_books_id);
992
993 /*
994 BEGIN
995 SELECT book_type_code
996 INTO g_asset_book_type_code
997 FROM fa_book_controls fc
998 WHERE fc.book_class = 'CORP0RATE'
999 AND fc.set_of_books_id = g_set_of_books_id
1000 AND fc.date_ineffective IS NULL;
1001 EXCEPTION
1002 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1003 g_asset_book_type_code := NULL;
1004 END; */
1005
1006 l_debug_info := 'select period for accounting date';
1007 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1009 END IF;
1010
1011 --Get_current_gl_date will return NULL if the date passed to it doesn't fall in a
1012 --open period.
1013
1014 g_period_name := AP_UTILITIES_PKG.get_current_gl_date(g_accounting_date,
1015 g_org_id); /* bug 5174518 */
1016
1017
1018 IF (g_period_name IS NULL) THEN
1019
1020 --Get gl_period and Date from a future period for the accounting date
1021 -- 8714506 Used l_accounting_date instead of g_accounting_date. Using
1022 -- same variable for in/out parmeters causing to make in parameters
1023 -- as Null
1024 ap_utilities_pkg.get_only_open_gl_date(p_date => g_accounting_date,
1025 p_period_name => g_period_name,
1026 p_gl_date => l_accounting_date,
1027 p_org_id => g_org_id); /*bug 5174518 */
1028
1029 g_accounting_date := l_accounting_date; /* Bug#8714506 */
1030
1031 IF (g_accounting_date IS NULL) THEN
1032 fnd_message.set_name('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
1033 app_exception.raise_exception;
1034 ELSE
1035 g_line_accounting_date := g_accounting_date;
1036 END IF;
1037
1038 END IF;
1039
1040 g_allow_pa_override := FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES');
1041
1042 -- Bug 5294998. API from PA will be used
1043 --g_pa_expenditure_date_default := FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT');
1044
1045 g_user_id := FND_PROFILE.VALUE('USER_ID');
1046
1047 g_login_id := FND_PROFILE.VALUE('LOGIN_ID');
1048
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 IF (SQLCODE <> -20001) THEN
1053 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1054 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1055 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1056 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_Invoice_Id));
1057 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1058 END IF;
1059 app_exception.raise_exception;
1060
1061 END Get_Info;
1062
1063
1064
1065 Procedure Get_Dist_Proration_Info(X_Invoice_Id IN NUMBER,
1066 X_Invoice_Line_Number IN NUMBER,
1067 X_Rcv_Transaction_Id IN NUMBER,
1068 X_Po_Line_Location_Id IN NUMBER,
1069 X_Match_Mode IN VARCHAR2,
1070 X_Match_Quantity IN NUMBER,
1071 X_Match_Amount IN NUMBER,
1072 X_Unit_Price IN NUMBER,
1073 X_Dist_Tab IN OUT NOCOPY
1074 AP_MATCHING_PKG.DIST_TAB_TYPE,
1075 X_Calling_Sequence IN VARCHAR2) IS
1076
1077 --Modified below cursor for bug #7015553 to receive projec_id and task_id
1078 --values from receivables tables if these are different in PO and receipt.
1079
1080 CURSOR po_distributions_cursor IS
1081 SELECT pd.po_distribution_id,
1082 pd.code_combination_id,
1083 accrue_on_receipt_flag ,
1084 DECODE(PD.destination_type_code,'EXPENSE',
1085 NVL(RT.project_id,PD.project_id),
1086 NULL), --project_id
1087 DECODE(PD.destination_type_code,'EXPENSE',
1088 NVL(RT.task_id,PD.task_id),
1089 NULL), --task_id
1090 DECODE(PD.destination_type_code,'EXPENSE',
1091 PD.expenditure_type,NULL), --expenditure_type
1092 DECODE(PD.destination_type_code,
1093 'EXPENSE',PD.expenditure_item_date,
1094 NULL), --expenditure_item_date
1095 DECODE(PD.destination_type_code,
1096 'EXPENSE',PD.expenditure_organization_id,
1097 NULL), --expenditure_organization_id
1098 DECODE(PD.destination_type_code,
1099 'EXPENSE', PD.award_id), --award_id
1100 ap_invoice_distributions_s.nextval
1101 FROM po_distributions_ap_v PD,
1102 rcv_transactions RT
1103 WHERE PD.line_location_id = x_po_line_location_id
1104 and Pd.line_location_id = RT.po_line_location_id
1105 and Pd.po_distribution_id = NVL(RT.po_distribution_id, Pd.po_distribution_id) -- bug 8771037
1106 and RT.TRANSACTION_ID= X_Rcv_Transaction_Id;
1107
1108
1109 l_po_distribution_id PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID%TYPE;
1110 l_ordered_po_qty NUMBER;
1111 l_cancelled_po_qty NUMBER;
1112 l_delivered_po_qty NUMBER;
1113 l_returned_po_qty NUMBER;
1114 l_corrected_po_qty NUMBER;
1115 l_ordered_txn_qty NUMBER;
1116 l_delivered_txn_qty NUMBER;
1117 l_returned_txn_qty NUMBER;
1118 l_corrected_txn_qty NUMBER;
1119 l_cancelled_txn_qty NUMBER;
1120 l_base_match_amt NUMBER;
1121 l_dist_match_amount NUMBER;
1122 l_invoiced_qty NUMBER;
1123 l_billed_txn_qty NUMBER;
1124 l_tot_shipment_qty_billed NUMBER;
1125 l_total_quantity NUMBER;
1126 l_matching_case VARCHAR2(2);
1127 l_amount NUMBER;
1128 l_quantity_invoiced NUMBER;
1129 l_po_ccid po_distributions.code_combination_id%TYPE;
1130 l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131 l_project_id po_distributions.project_id%TYPE;
1132 l_task_id po_distributions.task_id%TYPE;
1133 l_award_set_id po_distributions.award_id%TYPE;
1134 l_expenditure_type po_distributions.expenditure_type%TYPE;
1135 l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136 l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1137 i NUMBER;
1138 l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1139 l_unbuilt_flex VARCHAR2(240):='';
1140 l_reason_unbuilt_flex VARCHAR2(2000):='';
1141 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1142 l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
1143 l_sum_dist_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
1144 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
1145 l_rounding_index po_distributions.po_distribution_id%TYPE;
1146 l_base_amount ap_invoice_distributions.base_amount%TYPE;
1147 FLEX_OVERLAY_FAILED EXCEPTION;
1148 current_calling_sequence VARCHAR2(2000);
1149 l_debug_info VARCHAR2(2000);
1150 l_api_name VARCHAR2(32);
1151
1152 BEGIN
1153 l_api_name := 'Get_Dist_Proration_Info';
1154 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1155 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Get_Dist_Proration_Info(+)');
1156 END IF;
1157
1158
1159 current_calling_sequence := 'Get_Dist_Proration_Info<-'||x_calling_sequence;
1160
1161
1162 IF(X_Match_Mode IN ('STD-PS','CR-PS')) THEN
1163
1164 l_debug_info := 'Get Total Quantity for Proration';
1165 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1167 END IF;
1168
1169 Get_Total_Proration_Quantity
1170 ( X_Rcv_Transaction_Id => x_rcv_transaction_id,
1171 X_PO_Line_Location_Id => x_po_line_location_id,
1172 X_Match_Quantity => x_match_quantity,
1173 X_Total_Quantity => l_total_quantity,
1174 X_Matching_Case => l_matching_case,
1175 X_Calling_Sequence => current_calling_sequence);
1176
1177 OPEN PO_Distributions_Cursor;
1178
1179 LOOP
1180
1181 Fetch PO_Distributions_Cursor INTO l_po_distribution_id,
1182 l_po_ccid,
1183 l_accrue_on_receipt_flag,
1184 l_project_id,
1185 l_task_id,
1186 l_expenditure_type,
1187 l_po_expenditure_item_date,
1188 l_expenditure_organization_id,
1189 l_award_set_id,
1190 l_invoice_distribution_id;
1191
1192 EXIT WHEN PO_Distributions_Cursor%NOTFOUND;
1193
1194 l_debug_info := 'Get distribution level quantities from RCV API ';
1195 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1196 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1197 END IF;
1198
1199 RCV_INVOICE_MATCHING_SV.get_delivered_quantity(
1200 X_rcv_transaction_id,
1201 l_po_distribution_id,
1202 l_ordered_po_qty,
1203 l_cancelled_po_qty,
1204 l_delivered_po_qty,
1205 l_returned_po_qty,
1206 l_corrected_po_qty,
1207 l_ordered_txn_qty,
1208 l_cancelled_txn_qty,
1209 l_delivered_txn_qty,
1210 l_returned_txn_qty,
1211 l_corrected_txn_qty);
1212
1213 -- calculate actual qty delivered
1214 l_delivered_txn_qty := nvl(l_delivered_txn_qty,0)
1215 + nvl(l_corrected_txn_qty,0)
1216 - nvl(l_returned_txn_qty,0);
1217
1218 -- calculate actual qty ordered
1219 --bug5093924
1220 --We will subtract the cancelled quantity only if quantity ordered is not
1221 --same as cancelled quantity.Else it wont create any invoice distributions.
1222 --This is to bring the same behaviour as in PO matching.
1223 if(l_ordered_txn_qty <> l_cancelled_txn_qty or l_matching_case<>'O') then
1224 l_ordered_txn_qty := nvl(l_ordered_txn_qty,0)
1225 - nvl(l_cancelled_txn_qty,0);
1226 end if;
1227
1228
1229 --Commented below code for bug #8858939
1230
1231 /* -- calculate actual qty ordered
1232 l_ordered_txn_qty := nvl(l_ordered_txn_qty,0)
1233 - nvl(l_cancelled_txn_qty,0); */
1234
1235 l_debug_info := 'Get Distribution level qty billed';
1236 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1237 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1238 END IF;
1239
1240 --Added IF condition and ELSE clause for bug#14502266
1241 IF g_match_option = 'R' THEN
1242
1243 AP_MATCHING_UTILS_PKG.Get_Recpt_Dist_Qty_Billed (
1244 X_rcv_transaction_id,
1245 l_po_distribution_id,
1246 l_billed_txn_qty);
1247 ELSE
1248 SELECT nvl(sum(DECODE(aid.dist_match_type,
1249 'PRICE_CORRECTION', 0,
1250 'AMOUNT_CORRECTION',0,
1251 'ITEM_TO_SERVICE_PO',0,
1252 'ITEM_TO_SERVICE_RECEIPT',0,
1253 NVL(aid.corrected_quantity, 0) +
1254 NVL(aid.quantity_invoiced, 0))),0)
1255 INTO l_billed_txn_qty
1256 FROM AP_INVOICE_DISTRIBUTIONS_ALL aid
1257 WHERE aid.PO_DISTRIBUTION_ID = l_po_distribution_id;
1258 END IF;
1259
1260 l_billed_txn_qty := nvl(l_billed_txn_qty,0);
1261
1262 l_debug_info := 'Populate x_dist_tab with po /invoice info';
1263 x_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
1264 x_dist_tab(l_po_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
1265
1266 IF l_matching_case = 'DB' THEN
1267
1268 IF( x_match_mode = 'STD-PS') THEN
1269
1270 l_amount := ap_utilities_pkg.ap_round_currency(
1271 (X_match_amount * (l_delivered_txn_qty - l_billed_txn_qty)/
1272 l_total_quantity),g_invoice_currency_code);
1273
1274 l_quantity_invoiced := ROUND(X_match_quantity *
1275 ((l_delivered_txn_qty - l_billed_txn_qty)/
1276 l_total_quantity),15);
1277
1278 --bugfix:5639042
1279 ELSE /*-VE invoices */
1280
1281 l_amount := ap_utilities_pkg.ap_round_currency(
1282 (X_match_amount * l_billed_txn_qty)/l_total_quantity,
1283 g_invoice_currency_code);
1284
1285 l_quantity_invoiced := ROUND((X_match_quantity * l_billed_txn_qty)
1286 /l_total_quantity,15) ;
1287
1288
1289 END IF;
1290
1291 ELSIF l_matching_case = 'OB' THEN
1292
1293 IF(x_match_mode = 'STD-PS') THEN
1294 l_amount := ap_utilities_pkg.ap_round_currency(
1295 (X_match_amount * (l_ordered_txn_qty - l_billed_txn_qty)/
1296 l_total_quantity),g_invoice_currency_code);
1297
1298 l_quantity_invoiced := ROUND(x_match_quantity *
1299 ((l_ordered_txn_qty - l_billed_txn_qty)/
1300 l_total_quantity),15);
1301
1302 ELSE /*for -ve invoices */
1303 l_amount := ap_utilities_pkg.ap_round_currency(
1304 (X_match_amount * l_billed_txn_qty)/l_total_quantity,
1305 g_invoice_currency_code);
1306
1307 l_quantity_invoiced := ROUND((X_match_quantity * l_billed_txn_qty)
1308 /l_total_quantity,15) ;
1309 END IF;
1310
1311 ELSE
1312 --Introduced IF clause and ELSE part for bug#13555741
1313 IF(x_match_mode = 'STD-PS') THEN
1314 l_amount := ap_utilities_pkg.ap_round_currency(
1315 x_match_amount * l_ordered_txn_qty/l_total_quantity,
1316 g_invoice_currency_code);
1317 l_quantity_invoiced := ROUND(x_match_quantity * l_ordered_txn_qty/l_total_quantity,15);
1318 ELSE
1319 l_amount := ap_utilities_pkg.ap_round_currency(
1320 (X_match_amount * l_billed_txn_qty)/l_total_quantity,
1321 g_invoice_currency_code);
1322
1323 l_quantity_invoiced := ROUND((X_match_quantity * l_billed_txn_qty)
1324 /l_total_quantity,15) ;
1325 END IF;
1326
1327 END IF;
1328
1329 x_dist_tab(l_po_distribution_id).quantity_invoiced := l_quantity_invoiced;
1330
1331 x_dist_tab(l_po_distribution_id).amount := l_amount;
1332 x_dist_tab(l_po_distribution_id).unit_price := x_unit_price;
1333 x_dist_tab(l_po_distribution_id).po_ccid := l_po_ccid;
1334 x_dist_tab(l_po_distribution_id).accrue_on_receipt_flag := l_accrue_on_receipt_flag;
1335 x_dist_tab(l_po_distribution_id).project_id := l_project_id;
1336 --Bugfix:4651898
1337 x_dist_tab(l_po_distribution_id).task_id := l_task_id;
1338 x_dist_tab(l_po_distribution_id).expenditure_type := l_expenditure_type;
1339 x_dist_tab(l_po_distribution_id).expenditure_organization_id := l_expenditure_organization_id;
1340 x_dist_tab(l_po_distribution_id).expenditure_item_date := l_po_expenditure_item_date;
1341 -- Bug 5554493
1342 x_dist_tab(l_po_distribution_id).pa_quantity := l_quantity_invoiced;
1343
1344
1345 If l_award_set_id Is Not Null Then
1346 x_dist_tab(l_po_distribution_id).award_id := gms_ap_api.get_distribution_award(l_award_set_id);
1347 End If;
1348
1349 --For proration rounding/base amount rounding,
1350 --calculating max of the largest distribution's index
1351 --Added abs() for bug#14315066
1352 IF (abs(l_amount) >= abs(l_max_dist_amount)) THEN
1353 l_rounding_index := l_po_distribution_id;
1354 l_max_dist_amount := l_amount;
1355 END IF;
1356
1357 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
1358
1359 --Start Bug#9913906
1360 IF (l_matching_case = 'DB' AND
1361 x_match_mode = 'STD-PS'AND
1362 NVL(l_delivered_txn_qty,0) = 0 AND
1363 NVL(l_billed_txn_qty,0) = 0) THEN /*Bug#14787841*/
1364 x_dist_tab.delete(l_po_distribution_id);
1365 END IF;
1366 --End bug#9913906
1367
1368 END LOOP;
1369
1370 CLOSE PO_Distributions_Cursor;
1371
1372 --Perform Proration Rounding
1373 --Update the PL/SQL table's amount column with the rounding amount due
1374 --to proration, before the base_amounts are calculated.
1375
1376 IF (l_sum_prorated_amount <> x_match_amount and l_rounding_index is not null) THEN
1377 x_dist_tab(l_rounding_index).amount := x_dist_tab(l_rounding_index).amount +
1378 (x_match_amount - l_sum_prorated_amount);
1379 END IF;
1380
1381 ELSE /* for distribution level match */
1382
1383 FOR i IN nvl(x_dist_tab.FIRST,0)..nvl(x_dist_tab.LAST,0) LOOP
1384
1385 IF (x_dist_tab.exists(i)) THEN
1386
1387 --Bugfix:4651898
1388 SELECT pd.code_combination_id,
1389 pd.accrue_on_receipt_flag,
1390 NVL(rt.project_id,pd.project_id), --Modified for bug 13845471
1391 NVL(rt.task_id,pd.task_id), --Modified for bug 13845471
1392 pd.award_id,
1393 pd.expenditure_type,
1394 pd.expenditure_item_date,
1395 pd.expenditure_organization_id,
1396 ap_invoice_distributions_s.nextval
1397 INTO x_dist_tab(i).po_ccid,
1398 x_dist_tab(i).accrue_on_receipt_flag,
1399 x_dist_tab(i).project_id,
1400 x_dist_tab(i).task_id,
1401 l_award_set_id,
1402 x_dist_tab(i).expenditure_type,
1403 x_dist_tab(i).expenditure_item_date,
1404 x_dist_tab(i).expenditure_organization_id,
1405 x_dist_tab(i).invoice_distribution_id
1406 FROM po_distributions_ap_v pd
1407 ,rcv_transactions RT --Added for bug 13845471
1408 WHERE pd.po_distribution_id = x_dist_tab(i).po_distribution_id
1409 AND pd.po_distribution_id = NVL(rt.po_distribution_id,pd.po_distribution_id) --Added for bug 13845471
1410 AND rt.TRANSACTION_ID= X_Rcv_Transaction_Id; --Added for bug 13845471
1411
1412 -- Bug 5554493
1413 x_dist_tab(i).pa_quantity := x_dist_tab(i).quantity_invoiced;
1414
1415 If l_award_set_id Is Not Null Then
1416 x_dist_tab(i).award_id := gms_ap_api.get_distribution_award(l_award_set_id);
1417 End If;
1418
1419 --calculate the max of the largest distribution's index to be
1420 --used for base amount rounding. No need to perform proration
1421 --rounding for the case when the match is distributed by the user.
1422
1423 --Need to do the base_amount rounding only for foreign currency
1424 --invoices only, for non-foriegn currency invoices g_line_base_amount
1425 --will be NULL.
1426
1427 --IF (g_line_base_amount IS NOT NULL) THEN
1428 IF (g_exchange_rate IS NOT NULL) THEN --Bug#14315066
1429 --Added abs() for bug#14315066
1430 IF (abs(x_dist_tab(i).amount) >= abs(l_max_dist_amount)) THEN
1431 l_rounding_index := i;
1432 l_max_dist_amount := x_dist_tab(i).amount;
1433 END IF;
1434 END IF;
1435
1436 END IF;
1437
1438 END LOOP;
1439
1440 END IF;
1441
1442 FOR i in nvl(x_dist_tab.first,0) .. nvl(x_dist_tab.last,0) LOOP
1443
1444 IF (x_dist_tab.exists(i)) THEN
1445
1446 l_debug_info := 'Populate Project related information';
1447 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1448 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1449 END IF;
1450
1451 --If no project info in the PL/SQL by now,
1452 --then project info was null on po distribution
1453 --,then copy it from line for line level match, else copy it from invoice header.
1454
1455 --Not doing NVL on the g_line_project_id, b'coz for the case of line level match
1456 --if the user has not provided any project information on the line regardless of
1457 --whether project info is present on header or not, we will not override what user
1458 --has explicitly provided.
1459
1460 /*
1461 IF (x_dist_tab(i).project_id IS NULL ) THEN
1462
1463 IF (l_accrue_on_receipt_flag = 'N' and g_allow_pa_override = 'Y')THEN
1464
1465 IF (x_invoice_line_number IS NOT NULL) THEN
1466
1467 x_dist_tab(i).project_id := g_line_project_id;
1468 x_dist_tab(i).task_id := g_line_task_id;
1469 x_dist_tab(i).expenditure_type := g_line_expenditure_type;
1470 x_dist_tab(i).expenditure_organization_id := g_line_expenditure_org_id;
1471
1472 ELSE
1473
1474 x_dist_tab(i).project_id := g_project_id;
1475 x_dist_tab(i).task_id := g_task_id;
1476 x_dist_tab(i).expenditure_type := g_expenditure_type;
1477 x_dist_tab(i).expenditure_organization_id := g_expenditure_organization_id;
1478
1479 END IF;
1480
1481 END IF;
1482
1483 END IF;
1484 */
1485 IF (x_dist_tab(i).project_id IS NOT NULL) THEN
1486 -- Bug 5554493
1487 --x_dist_tab(i).pa_quantity := l_quantity_invoiced;
1488 -- Bug 5294998. PA API will be used
1489 /*CASE g_pa_expenditure_date_default
1490 WHEN 'PO Expenditure Item Date/Transaction Date' THEN
1491 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,
1492 g_invoice_date);
1493 WHEN 'PO Expenditure Item Date/Transaction GL Date' THEN
1494 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,
1495 g_accounting_date);
1496 WHEN 'PO Expenditure Item Date/Transaction System Date' THEN
1497 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,sysdate);
1498 WHEN ('Receipt Date/Transaction Date' ) THEN
1499 x_dist_tab(i).expenditure_item_date := g_invoice_date;
1500 WHEN ('Receipt Date/Transaction GL Date') THEN
1501 x_dist_tab(i).expenditure_item_date := g_accounting_date;
1502 WHEN ('Receipt Date/Transaction System Date' ) THEN
1503 x_dist_tab(i).expenditure_item_date := sysdate;
1504 WHEN 'Transaction Date' THEN
1505 x_dist_tab(i).expenditure_item_date := g_invoice_date;
1506 WHEN 'Transaction GL Date' THEN
1507 x_dist_tab(i).expenditure_item_date := g_accounting_date;
1508 WHEN 'Transaction System Date' THEN
1509 x_dist_tab(i).expenditure_item_date := sysdate;
1510 ELSE
1511 x_dist_tab(i).expenditure_item_date := NULL;
1512 END CASE; */
1513
1514 x_dist_tab(i).expenditure_item_date :=
1515 PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
1516 p_transaction_date => g_invoice_date,
1517 p_gl_date => g_accounting_date,
1518 p_creation_date => sysdate,
1519 p_po_exp_item_date => x_dist_tab(i).expenditure_item_date,
1520 p_po_distribution_id => x_dist_tab(i).po_distribution_id,
1521 p_calling_program => 'PO-MATCH');
1522
1523 END IF;
1524
1525 l_debug_info := 'Populate award information';
1526 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1527 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1528 END IF;
1529
1530 IF (x_dist_tab(i).award_id IS NULL ) THEN
1531 IF (l_accrue_on_receipt_flag = 'N' and g_allow_pa_override = 'Y')THEN
1532 IF (x_invoice_line_number IS NOT NULL) THEN
1533 x_dist_tab(i).award_id := g_line_award_id;
1534 ELSE
1535 x_dist_tab(i).award_id := g_award_id;
1536 END IF;
1537 END IF;
1538 END IF; /*(x_dist_tab(i).award_id IS NULL) */
1539
1540 l_debug_info := 'Populate awt information';
1541 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1542 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1543 END IF;
1544
1545 IF (g_system_allow_awt_flag = 'Y' and g_site_allow_awt_flag = 'Y') THEN
1546
1547 IF (x_invoice_line_number IS NOT NULL) THEN
1548 x_dist_tab(i).awt_group_id := g_line_awt_group_id;
1549 x_dist_tab(i).pay_awt_group_id := g_line_pay_awt_group_id; --bug 9689194
1550 ELSE
1551 x_dist_tab(i).awt_group_id := g_awt_group_id;
1552 x_dist_tab(i).pay_awt_group_id := g_pay_awt_group_id; --bug 9689194
1553 END IF;
1554
1555 END IF;
1556
1557 --Populate Base Amount
1558 --Need to populate the base amount for foreign currency invoices only
1559
1560 IF (g_exchange_rate IS NOT NULL) THEN
1561 l_base_amount := ap_utilities_pkg.ap_round_currency(
1562 x_dist_tab(i).amount * g_exchange_rate,
1563 g_base_currency_code);
1564 x_dist_tab(i).base_amount := l_base_amount;
1565
1566 l_sum_dist_base_amount := l_sum_dist_base_amount + l_base_amount;
1567 END IF;
1568
1569 --Populate dist_code_combination_id information
1570
1571 --Can overlay account if not accruing on receipt, and either not project_related
1572 --or if project related then project account override is allowed
1573 --and encumbrance is not turned on and system option to allow
1574 --override of matching account is turned ON.
1575
1576 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
1577
1578 --Can overlay account if not accruing on receipt, and either not project_related
1579 --or if project related then project account override is allowed
1580 --and encumbrance is not turned on and system option to allow
1581 --override of matching account is turned ON.
1582
1583 IF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'N'
1584 AND ((x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'Y')
1585 OR x_dist_tab(i).project_id IS NULL)
1586 AND g_allow_account_override = 'Y'
1587 AND g_encumbrance_flag = 'N') THEN
1588
1589 IF (g_account_segment IS NOT NULL OR
1590 g_balancing_segment IS NOT NULL OR
1591 g_cost_center_segment IS NOT NULL OR
1592 g_overlay_dist_code_concat IS NOT NULL) THEN
1593
1594 l_dist_ccid := nvl(x_dist_tab(i).dist_ccid,x_dist_tab(i).po_ccid);
1595
1596 IF (AP_UTILITIES_PKG.overlay_segments(
1597 g_balancing_segment,
1598 g_cost_center_segment,
1599 g_account_segment,
1600 g_overlay_dist_code_concat,
1601 l_dist_ccid,
1602 g_set_of_books_id ,
1603 'CREATE' ,
1604 l_unbuilt_flex ,
1605 l_reason_unbuilt_flex ,
1606 FND_GLOBAL.RESP_APPL_ID,
1607 FND_GLOBAL.RESP_ID,
1608 FND_GLOBAL.USER_ID,
1609 current_calling_sequence ,
1610 NULL) <> TRUE) THEN
1611
1612 l_debug_info := 'Overlaying Segments for this account was unsuccessful due to '||
1613 l_reason_unbuilt_flex;
1614 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1615 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1616 END IF;
1617
1618 --Did not handle this exception explicitly as OTHERS handler
1619 --should be sufficient for this case.
1620
1621 /*OPEN ISSUE 3 */
1622 RAISE FLEX_OVERLAY_FAILED;
1623
1624 ELSE
1625
1626 x_dist_tab(i).dist_ccid := l_dist_ccid;
1627
1628 END IF;
1629
1630 /*No Overlay info provided */
1631 ELSE
1632
1633 --x_dist_tab.dist_ccid is already populated by the
1634 --calling module with the overriden account ccid, so
1635 --we need not do anything for the case of 'STD-PD' and 'CR-PD'.
1636
1637 IF (x_match_mode IN ('STD-PS','CR-PS')) THEN
1638 x_dist_tab(i).dist_ccid := nvl(g_default_dist_ccid,x_dist_tab(i).po_ccid);
1639 END IF;
1640
1641 END IF; /*IF g_account_segment is not null... */
1642
1643 ELSIF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'Y' OR
1644 g_allow_account_override = 'N' OR
1645 g_encumbrance_flag = 'Y' OR
1646 --bugfix:4668058 added the following clause
1647 (x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'N')
1648 ) THEN
1649
1650 --po_distributions_ap_v.code_combination_id is accrual account
1651 --if accruing on receipt or else charge account
1652
1653 x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1654
1655 END IF; /*IF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'N'...*/
1656
1657 --For Prepayment type invoice build the prepayment account if
1658 --the system option build_prepayment_accounts_flag is set to Y.
1659 ELSE
1660
1661 -- Contract Payments: If matching to an advance/financing pay item do not
1662 -- use the prepay ccid, use the po charge account.
1663
1664 IF g_shipment_type = 'PREPAYMENT' THEN
1665
1666 x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1667
1668 ELSE
1669
1670 IF (g_build_prepay_accts_flag = 'Y') THEN
1671
1672 l_debug_info := 'Calling build_prepay_account to build the prepayment account';
1673 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1674 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1675 END IF;
1676 -- Bug 5465722
1677 ap_matching_pkg.build_prepay_account(
1678 P_base_ccid => x_dist_tab(i).po_ccid,
1679 P_overlay_ccid => g_prepay_ccid,
1680 P_accounting_date => g_line_accounting_date,
1681 P_result_Ccid => l_dist_ccid,
1682 P_reason_unbuilt_flex => l_reason_unbuilt_flex,
1683 P_calling_sequence => current_calling_sequence);
1684
1685 IF (l_dist_ccid <> -1) THEN
1686
1687 x_dist_tab(i).dist_ccid := l_dist_ccid;
1688
1689 ELSE
1690 /*OPEN ISSUE 3 */
1691 l_debug_info := 'Flexbuild of prepayment account failed due to '
1692 ||l_reason_unbuilt_flex;
1693 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1694 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1695 END IF;
1696
1697 RAISE FLEX_OVERLAY_FAILED;
1698
1699 END IF;
1700
1701 ELSE
1702
1703 x_dist_tab(i).dist_ccid := g_prepay_ccid;
1704
1705 END IF;
1706 END IF;
1707 END IF; /*g_invoice_type_lookup_code <> 'PREPAYMENT'*/
1708
1709 END IF; /*IF (x_dist_tab.exists(i)) */
1710
1711 END LOOP;
1712
1713 --Base Amount Rounding
1714 --Need to perform base_amount rounding for only foreign currency invoices only
1715 IF (g_line_base_amount <> l_sum_dist_base_amount AND g_exchange_rate IS NOT NULL and
1716 l_rounding_index is not null) THEN
1717
1718 x_dist_tab(l_rounding_index).base_amount := x_dist_tab(l_rounding_index).base_amount +
1719 (g_line_base_amount - l_sum_dist_base_amount);
1720
1721 x_dist_tab(l_rounding_index).rounding_amt := g_line_base_amount - l_sum_dist_base_amount;
1722
1723 END IF;
1724
1725
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 IF (SQLCODE <> -20001) THEN
1729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1730 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1732 FND_MESSAGE.SET_TOKEN('PARAMETERS','Shipment id '||to_char(x_po_line_location_id)
1733 ||', Match Quantity = '||x_match_quantity
1734 ||', Match Amount = '||x_match_amount
1735 ||', Exchange Rate = '||g_exchange_rate
1736 ||', Base Currency = '||g_base_currency_code
1737 );
1738 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1739 END IF;
1740
1741 --Clean up the PL/SQL table
1742 x_dist_tab.delete;
1743
1744 APP_EXCEPTION.RAISE_EXCEPTION;
1745
1746 END Get_Dist_Proration_Info;
1747
1748
1749
1750 /**----------------------------------------------------------------------
1751 GET_TOTAL_PRORATION_QUANTITY :: returns the Total Quantity and Receipt Match Case
1752 given the rcv_transaction_id. These values are calculated as follows:
1753
1754 O -- qty ORDERED on PO shipment
1755 C -- qty CANCELLED on PO shipment
1756 D -- qty DELIVERED on rcv_transaction
1757 B -- Total qty BILLED on the rcv_transaction
1758 a -- qty being Invoiced right now (X_quantity)
1759
1760 If a < = D - B then Case = 'DB' , Total_Quantity = (D - B)
1761 If a < = (O - C) - B then Case = 'OB' , Total_quantity = (O - C) - B
1762 Else Case = 'O' , Total_Quantity = O
1763 -----------------------------------------------------------------------**/
1764 PROCEDURE Get_Total_Proration_Quantity
1765 ( X_Rcv_Transaction_id IN NUMBER,
1766 X_PO_Line_Location_Id IN NUMBER,
1767 X_Match_Quantity IN NUMBER,
1768 X_Total_Quantity OUT NOCOPY NUMBER,
1769 X_Matching_Case OUT NOCOPY VARCHAR2,
1770 X_Calling_Sequence IN VARCHAR2) IS
1771
1772 l_ordered_qty NUMBER;
1773 l_cancelled_qty NUMBER;
1774 l_received_qty NUMBER;
1775 l_corrected_qty NUMBER;
1776 l_delivered_qty NUMBER;
1777 l_transaction_qty NUMBER;
1778 l_billed_qty NUMBER;
1779 l_accepted_qty NUMBER;
1780 l_rejected_qty NUMBER;
1781 l_debug_info VARCHAR2(2000);
1782 current_calling_sequence VARCHAR2(2000);
1783 l_api_name VARCHAR2(32);
1784
1785 BEGIN
1786
1787 l_api_name := 'Get_Total_Proration_Quantity';
1788 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1789 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Get_Total_Proration_Quantity(+)');
1790 END IF;
1791
1792 current_calling_sequence := 'Get_Total_Proration_Quantity<-'||current_calling_sequence;
1793 l_debug_info := 'Get total quantity for proration';
1794 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1795 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1796 END IF;
1797
1798 -- Call RCV API to get receipt quantities in the receipt UOM
1799 -- Actually calling the AP wrapper around the RCV API to get the
1800 -- quantities in the receipt UOM only
1801
1802 l_debug_info := ' Getting receipt quantities';
1803 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1804 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1805 END IF;
1806
1807 AP_MATCHING_UTILS_PKG.Get_receipt_Quantities(
1808 X_rcv_transaction_id,
1809 l_ordered_qty,
1810 l_cancelled_qty,
1811 l_received_qty,
1812 l_corrected_qty,
1813 l_delivered_qty,
1814 l_transaction_qty,
1815 l_billed_qty,
1816 l_accepted_qty,
1817 l_rejected_qty);
1818
1819
1820 l_billed_qty := nvl(l_billed_qty,0);
1821 l_delivered_qty := nvl(l_delivered_qty,0);
1822 l_cancelled_qty := nvl(l_cancelled_qty,0);
1823 l_ordered_qty := nvl(l_ordered_qty,0);
1824
1825 -- Calculate the Total quantity and Receipt matching case
1826 If (X_match_quantity <= (l_delivered_qty - l_billed_qty)) Then
1827
1828 X_total_quantity := l_delivered_qty - l_billed_qty;
1829 X_matching_case := 'DB';
1830 Elsif (X_match_quantity <= (l_ordered_qty - l_cancelled_qty) - l_billed_qty)
1831 Then
1832 X_total_quantity := (l_ordered_qty - l_cancelled_qty)- l_billed_qty;
1833 X_matching_case := 'OB';
1834 Else
1835 X_total_quantity := l_ordered_qty;
1836 X_matching_case := 'O';
1837 End if;
1838
1839 --For the case of RTS debit memos:
1840 --Quantity_Billed should be selected from rcv_transactions
1841 --only when the invoice is matched to a receipt and from
1842 --po_line_locations when the invoice is matched to a PO.
1843
1844 IF X_Match_Quantity < 0 THEN
1845 --For credit/debit memos if match_option is receipt
1846 --then the total_quantity is the quantity_billed on
1847 --the rcv_transaction else quantity_billed on the po_shipment.
1848
1849 IF g_match_option = 'R' THEN
1850
1851 --Bugfix: 5546861 backed out the fix 5513689, and made sure
1852 --both the bugs are fixed. Due to the fix done by 5513689,
1853 --we were creating distributions for debit memo with +ve quantities, since
1854 --in the proration formula (match quantity and total quantity being negative) (-a/-b)
1855 --will give +ve value.
1856
1857 x_total_quantity := l_billed_qty;
1858 ELSE
1859 SELECT quantity_billed
1860 INTO x_total_quantity
1861 FROM po_line_locations
1862 WHERE line_location_id = x_po_line_location_id;
1863 END IF;
1864
1865 END IF;
1866
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 IF (SQLCODE <> -20001) THEN
1870 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1871 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1872 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1873 FND_MESSAGE.SET_TOKEN('PARAMETERS','Match Option = '||g_match_option
1874 ||', Shipment_id = '||to_char(X_PO_Line_Location_id)
1875 ||', Rcv Transaction Id = '||to_char(x_rcv_transaction_id)
1876 ||', Match Quantity = '||to_char(x_match_quantity));
1877 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1878 END IF;
1879 APP_EXCEPTION.RAISE_EXCEPTION;
1880
1881 END Get_Total_Proration_Quantity;
1882
1883
1884
1885 Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
1886 X_Invoice_Line_Number IN NUMBER,
1887 X_Line_Type_Lookup_Code IN VARCHAR2,
1888 X_Cost_Factor_Id IN NUMBER DEFAULT NULL,
1889 X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
1890 X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
1891 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
1892 X_Rcv_Transaction_Id IN NUMBER,
1893 X_Amount IN NUMBER,
1894 X_Quantity_Invoiced IN NUMBER,
1895 X_Unit_Price IN NUMBER DEFAULT NULL,
1896 X_Item_Line_Number IN NUMBER,
1897 X_Charge_Line_Description IN VARCHAR2,
1898 X_Retained_Amount IN NUMBER DEFAULT NULL,
1899 X_Calling_Sequence IN VARCHAR2) IS
1900
1901 current_calling_sequence VARCHAR2(2000);
1902 l_debug_info VARCHAR2(2000);
1903 l_api_name VARCHAR2(4000);
1904 l_source AP_INVOICES_ALL.SOURCE%TYPE ; -- Bug 9160534
1905 l_full_mtch_amt NUMBER; --Bug13505998
1906
1907 BEGIN
1908
1909 l_api_name := 'Insert_Invoice_line';
1910 current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
1911
1912 IF (X_LINE_TYPE_LOOKUP_CODE = 'ITEM') THEN
1913
1914 l_debug_info := 'Inserting Item Line Matched to a RCV';
1915 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1917 END IF;
1918
1919 --Bug#13505998
1920 l_full_mtch_amt :=
1921 ap_matching_utils_pkg.get_full_mtch_amt
1922 (X_Po_Line_Location_id,G_Invoice_Currency_Code);
1923
1924 l_debug_info := 'l_full_mtch_amt '||l_full_mtch_amt;
1925
1926 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1927 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1928 END IF;
1929 --End Bug#13505998
1930
1931 --Modified below insert for bug #7015553 to insert project and task
1932 --values from receipt. If those are not existed on receipt then takes
1933 --from PO.
1934
1935 -- Bug 9160534. Start
1936 select nvl(ai.source, 'DUMMY') into l_source
1937 from ap_invoices_all ai
1938 where ai.invoice_id = X_Invoice_Id;
1939 -- Bug 9160534. End
1940
1941 INSERT INTO AP_INVOICE_LINES
1942 (INVOICE_ID,
1943 LINE_NUMBER,
1944 LINE_TYPE_LOOKUP_CODE,
1945 REQUESTER_ID,
1946 DESCRIPTION,
1947 LINE_SOURCE,
1948 ORG_ID,
1949 INVENTORY_ITEM_ID,
1950 ITEM_DESCRIPTION,
1951 SERIAL_NUMBER,
1952 MANUFACTURER,
1953 MODEL_NUMBER,
1954 GENERATE_DISTS,
1955 MATCH_TYPE,
1956 DISTRIBUTION_SET_ID,
1957 ACCOUNT_SEGMENT,
1958 BALANCING_SEGMENT,
1959 COST_CENTER_SEGMENT,
1960 OVERLAY_DIST_CODE_CONCAT,
1961 DEFAULT_DIST_CCID,
1962 PRORATE_ACROSS_ALL_ITEMS,
1963 LINE_GROUP_NUMBER,
1964 ACCOUNTING_DATE,
1965 PERIOD_NAME,
1966 DEFERRED_ACCTG_FLAG,
1967 DEF_ACCTG_START_DATE,
1968 DEF_ACCTG_END_DATE,
1969 DEF_ACCTG_NUMBER_OF_PERIODS,
1970 DEF_ACCTG_PERIOD_TYPE,
1971 SET_OF_BOOKS_ID,
1972 AMOUNT,
1973 BASE_AMOUNT,
1974 ROUNDING_AMT,
1975 QUANTITY_INVOICED,
1976 UNIT_MEAS_LOOKUP_CODE,
1977 UNIT_PRICE,
1978 WFAPPROVAL_STATUS,
1979 -- USSGL_TRANSACTION_CODE, -- Removed for bug 4277744
1980 DISCARDED_FLAG,
1981 ORIGINAL_AMOUNT,
1982 ORIGINAL_BASE_AMOUNT,
1983 ORIGINAL_ROUNDING_AMT,
1984 CANCELLED_FLAG,
1985 INCOME_TAX_REGION,
1986 TYPE_1099,
1987 STAT_AMOUNT,
1988 PREPAY_INVOICE_ID,
1989 PREPAY_LINE_NUMBER,
1990 INVOICE_INCLUDES_PREPAY_FLAG,
1991 CORRECTED_INV_ID,
1992 CORRECTED_LINE_NUMBER,
1993 PO_HEADER_ID,
1994 PO_LINE_ID,
1995 PO_RELEASE_ID,
1996 PO_LINE_LOCATION_ID,
1997 PO_DISTRIBUTION_ID,
1998 RCV_TRANSACTION_ID,
1999 RCV_SHIPMENT_LINE_ID,
2000 FINAL_MATCH_FLAG,
2001 ASSETS_TRACKING_FLAG,
2002 ASSET_BOOK_TYPE_CODE,
2003 ASSET_CATEGORY_ID,
2004 PROJECT_ID,
2005 TASK_ID,
2006 EXPENDITURE_TYPE,
2007 EXPENDITURE_ITEM_DATE,
2008 EXPENDITURE_ORGANIZATION_ID,
2009 PA_QUANTITY,
2010 PA_CC_AR_INVOICE_ID,
2011 PA_CC_AR_INVOICE_LINE_NUM,
2012 PA_CC_PROCESSED_CODE,
2013 AWARD_ID,
2014 AWT_GROUP_ID,
2015 REFERENCE_1,
2016 REFERENCE_2,
2017 RECEIPT_VERIFIED_FLAG,
2018 RECEIPT_REQUIRED_FLAG,
2019 RECEIPT_MISSING_FLAG,
2020 JUSTIFICATION,
2021 EXPENSE_GROUP,
2022 START_EXPENSE_DATE,
2023 END_EXPENSE_DATE,
2024 RECEIPT_CURRENCY_CODE,
2025 RECEIPT_CONVERSION_RATE,
2026 RECEIPT_CURRENCY_AMOUNT,
2027 DAILY_AMOUNT,
2028 WEB_PARAMETER_ID,
2029 ADJUSTMENT_REASON,
2030 MERCHANT_DOCUMENT_NUMBER,
2031 MERCHANT_NAME,
2032 MERCHANT_REFERENCE,
2033 MERCHANT_TAX_REG_NUMBER,
2034 MERCHANT_TAXPAYER_ID,
2035 COUNTRY_OF_SUPPLY,
2036 CREDIT_CARD_TRX_ID,
2037 COMPANY_PREPAID_INVOICE_ID,
2038 CC_REVERSAL_FLAG,
2039 ATTRIBUTE_CATEGORY,
2040 ATTRIBUTE1,
2041 ATTRIBUTE2,
2042 ATTRIBUTE3,
2043 ATTRIBUTE4,
2044 ATTRIBUTE5,
2045 ATTRIBUTE6,
2046 ATTRIBUTE7,
2047 ATTRIBUTE8,
2048 ATTRIBUTE9,
2049 ATTRIBUTE10,
2050 ATTRIBUTE11,
2051 ATTRIBUTE12,
2052 ATTRIBUTE13,
2053 ATTRIBUTE14,
2054 ATTRIBUTE15,
2055 /* GLOBAL_ATTRIBUTE_CATEGORY,
2056 GLOBAL_ATTRIBUTE1,
2057 GLOBAL_ATTRIBUTE2,
2058 GLOBAL_ATTRIBUTE3,
2059 GLOBAL_ATTRIBUTE4,
2060 GLOBAL_ATTRIBUTE5,
2061 GLOBAL_ATTRIBUTE6,
2062 GLOBAL_ATTRIBUTE7,
2063 GLOBAL_ATTRIBUTE8,
2064 GLOBAL_ATTRIBUTE9,
2065 GLOBAL_ATTRIBUTE10,
2066 GLOBAL_ATTRIBUTE11,
2067 GLOBAL_ATTRIBUTE12,
2068 GLOBAL_ATTRIBUTE13,
2069 GLOBAL_ATTRIBUTE14,
2070 GLOBAL_ATTRIBUTE15,
2071 GLOBAL_ATTRIBUTE16,
2072 GLOBAL_ATTRIBUTE17,
2073 GLOBAL_ATTRIBUTE18,
2074 GLOBAL_ATTRIBUTE19,
2075 GLOBAL_ATTRIBUTE20, */
2076 CREATION_DATE,
2077 CREATED_BY,
2078 LAST_UPDATED_BY,
2079 LAST_UPDATE_DATE,
2080 LAST_UPDATE_LOGIN,
2081 PROGRAM_APPLICATION_ID,
2082 PROGRAM_ID,
2083 PROGRAM_UPDATE_DATE,
2084 REQUEST_ID,
2085 RETAINED_AMOUNT,
2086 RETAINED_AMOUNT_REMAINING,
2087 --BUGFIX:5565310
2088 SHIP_TO_LOCATION_ID,
2089 PRIMARY_INTENDED_USE,
2090 PRODUCT_FISC_CLASSIFICATION,
2091 TRX_BUSINESS_CATEGORY,
2092 PRODUCT_TYPE,
2093 PRODUCT_CATEGORY,
2094 USER_DEFINED_FISC_CLASS,
2095 ASSESSABLE_VALUE,
2096 TAX_CLASSIFICATION_CODE,
2097 PAY_AWT_GROUP_ID) --bug 9689194
2098 SELECT X_INVOICE_ID, --invoice_id
2099 X_INVOICE_LINE_NUMBER, --invoice_line_number
2100 X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
2101 DECODE(X_SINGLE_DIST_FLAG,'Y',
2102 PD.DELIVER_TO_PERSON_ID,NULL), --requester_id
2103 RCV.ITEM_DESCRIPTION, --description
2104 'HEADER MATCH', --line_source
2105 G_ORG_ID, --org_id
2106 RCV.ITEM_ID, --inventory_item_id
2107 RCV.ITEM_DESCRIPTION, --item_description
2108 NULL, --serial_number
2109 NULL, --manufacturer
2110 NULL, --model_number
2111 'D', --generate_dists
2112 /* Modified for bug#14502266 'ITEM_TO_RECEIPT' */
2113 decode(g_match_option,'R',
2114 'ITEM_TO_RECEIPT',
2115 'ITEM_TO_PO'), --match_type
2116 NULL, --distribution_set_id
2117 NULL, --account_segment
2118 NULL, --balancing_segment
2119 NULL, --cost_center_segment
2120 NULL, --overlay_dist_code_concat
2121 --Bug6908977
2122 NULL, --default_dist_ccid
2123 'N', --prorate_across_all_items
2124 NULL, --line_group_number
2125 G_ACCOUNTING_DATE, --accounting_date
2126 G_PERIOD_NAME, --period_name
2127 'N', --deferred_acctg_flag
2128 NULL, --def_acctg_start_date
2129 NULL, --def_acctg_end_date
2130 NULL, --def_acctg_number_of_periods
2131 NULL, --def_acctg_period_type
2132 G_SET_OF_BOOKS_ID, --set_of_books_id
2133 X_AMOUNT, --amount
2134 AP_UTILITIES_PKG.Ap_Round_Currency(
2135 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
2136 G_BASE_CURRENCY_CODE), --base_amount
2137 NULL, --rounding_amount
2138 X_QUANTITY_INVOICED, --quantity_invoiced
2139 RCV.RECEIPT_UOM_LOOKUP_CODE,--unit_meas_lookup_code
2140 X_UNIT_PRICE, --unit_price
2141 DECODE(l_source, 'RTS', 'NOT REQUIRED',
2142 DECODE(G_APPROVAL_WORKFLOW_FLAG,'Y',
2143 'REQUIRED','NOT REQUIRED')), --wf_approval_status, Bug 9160534
2144 -- RCV.USSGL_TRANSACTION_CODE,--ussgl_transaction_code - Bug 4277744
2145 'N', --discarded_flag
2146 NULL, --original_amount
2147 NULL, --original_base_amount
2148 NULL, --original_rounding_amt
2149 'N', --cancelled_flag
2150 G_INCOME_TAX_REGION, --income_tax_region
2151 RCV.TYPE_1099, --type_1099
2152 NULL, --stat_amount
2153 NULL, --prepay_invoice_id
2154 NULL, --prepay_line_number
2155 NULL, --invoice_includes_prepay_flag
2156 NULL, --corrected_inv_id
2157 NULL, --corrected_line_number
2158 RCV.PO_HEADER_ID, --po_header_id
2159 RCV.PO_LINE_ID, --po_line_id
2160 RCV.PO_RELEASE_ID, --po_release_id
2161 RCV.PO_LINE_LOCATION_ID, --po_line_location_id
2162 DECODE(X_SINGLE_DIST_FLAG,'Y',
2163 X_PO_DISTRIBUTION_ID,NULL),--po_distribution_id
2164 /*Modified for bug#14502266
2165 X_RCV_TRANSACTION_ID,
2166 RCV.RCV_SHIPMENT_LINE_ID, */
2167 decode(g_match_option,'R',
2168 x_rcv_transaction_id,NULL), --rcv_transaction_id
2169 decode(g_match_option,'R',
2170 RCV.RCV_SHIPMENT_LINE_ID,NULL),--rcv_shipment_line_id
2171 NULL, --final_match_flag
2172 'N', --assets_tracking_flag
2173 G_ASSET_BOOK_TYPE_CODE, --asset_book_type_code
2174 MSI.ASSET_CATEGORY_ID, --asset_category_id
2175 DECODE(X_SINGLE_DIST_FLAG,'Y',
2176 DECODE(PD.destination_type_code,
2177 'EXPENSE',NVL(RT.PROJECT_ID,PD.project_id),
2178 G_PROJECT_ID),
2179 NULL), --project_id modified for bug #7015553
2180 DECODE(X_SINGLE_DIST_FLAG,'Y',
2181 DECODE(PD.destination_type_code,
2182 'EXPENSE',NVL(RT.TASK_ID,PD.task_id),
2183 G_TASK_ID),
2184 NULL), --task_id modified for bug #7015553
2185 DECODE(X_SINGLE_DIST_FLAG,'Y',
2186 DECODE(PD.destination_type_code,
2187 'EXPENSE',PD.expenditure_type,
2188 G_EXPENDITURE_TYPE),
2189 NULL), --expenditure_type
2190 DECODE(X_SINGLE_DIST_FLAG,'Y',
2191 -- Bug 5294998. Calling project API
2192 PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
2193 g_invoice_date,
2194 g_accounting_date,
2195 NULL,
2196 sysdate,
2197 x_po_distribution_id,
2198 'PO-MATCH'),
2199 NULL), --expenditure_item_date
2200 /*DECODE(g_pa_expenditure_date_default,
2201 'PO Expenditure Item Date/Transaction Date',
2202 DECODE(PD.Destination_type_code,
2203 'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
2204 G_INVOICE_DATE),
2205 'PO Expenditure Item Date/Transaction GL Date',
2206 DECODE(PD.destination_type_code,
2207 'EXPENSE', PD.EXPENDITURE_ITEM_DATE,
2208 G_ACCOUNTING_DATE),
2209 'PO Expenditure Item Date/Transaction System Date',
2210 DECODE(PD.destination_type_code,
2211 'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
2212 SYSDATE),
2213 'Receipt Date/Transaction Date',G_INVOICE_DATE,
2214 'Receipt Date/Transaction GL Date',G_ACCOUNTING_DATE,
2215 'Receipt Date/Transaction System Date',SYSDATE,
2216 'Transaction Date',G_INVOICE_DATE,
2217 'Transaction GL Date',G_ACCOUNTING_DATE,
2218 'Transaction System Date', SYSDATE),
2219 NULL), */
2220 DECODE(X_SINGLE_DIST_FLAG,'Y',
2221 DECODE(PD.destination_type_code,
2222 'EXPENSE',PD.expenditure_organization_id,
2223 G_EXPENDITURE_ORGANIZATION_ID),
2224 NULL), --expenditure_organization_id
2225 DECODE( DECODE(X_SINGLE_DIST_FLAG,'Y',
2226 DECODE(PD.destination_type_code,
2227 'EXPENSE',PD.project_id,
2228 G_PROJECT_ID),
2229 NULL),
2230 '','',x_quantity_invoiced), --pa_quantity
2231 NULL, --pa_cc_ar_invoice_id
2232 NULL, --pa_cc_ar_invoice_line_num
2233 NULL, --pa_cc_processed_code
2234 DECODE(X_SINGLE_DIST_FLAG,
2235 'Y', nvl(gms_ap_api.get_distribution_award(PD.AWARD_ID), G_AWARD_ID),
2236 NULL), --award_id
2237 G_AWT_GROUP_ID, --awt_group_id
2238 NULL, --reference_1
2239 NULL, --reference_2
2240 NULL, --receipt_verified_flag
2241 NULL, --receipt_required_flag
2242 NULL, --receipt_missing_flag
2243 NULL, --justification
2244 NULL, --expense_group
2245 NULL, --start_expense_date
2246 NULL, --end_expense_date
2247 NULL, --receipt_currency_amount
2248 NULL, --receipt_conversion_rate
2249 NULL, --receipt_currency_amount
2250 NULL, --daily_amount
2251 NULL, --web_parameter_id
2252 NULL, --adjustment_reason
2253 NULL, --merchant_document_number
2254 NULL, --merchant_name
2255 NULL, --merchant_reference
2256 NULL, --merchant_tax_reg_number
2257 NULL, --merchant_taxpayer_id
2258 NULL, --country_of_supply
2259 NULL, --credit_card_trx_id
2260 NULL, --company_prepaid_invoice_id
2261 NULL, --cc_reversal_flag
2262 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute_category),''),--attribute_category
2263 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute1),''), --attribute1
2264 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute2),''), --attribute2
2265 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute3),''), --attribute3
2266 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute4),''), --attribute4
2267 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute5),''), --attribute5
2268 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute6),''), --attribute6
2269 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute7),''), --attribute7
2270 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute8),''), --attribute8
2271 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute9),''), --attribute9
2272 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute10),''), --attribute10
2273 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute11),''), --attribute11
2274 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute12),''), --attribute12
2275 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute13),''), --attribute13
2276 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute14),''), --attribute14
2277 NVL(DECODE(g_transfer_flag,'Y',RSL.attribute15),''), --attribute15
2278 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
2279 X_GLOBAL_ATTRIBUTE1, --global_attribute1
2280 X_GLOBAL_ATTRIBUTE2, --global_attribute2
2281 X_GLOBAL_ATTRIBUTE3, --global_attribute3
2282 X_GLOBAL_ATTRIBUTE4, --global_attribute4
2283 X_GLOBAL_ATTRIBUTE5, --global_attribute5
2284 X_GLOBAL_ATTRIBUTE6, --global_attribute6
2285 X_GLOBAL_ATTRIBUTE7, --global_attribute7
2286 X_GLOBAL_ATTRIBUTE8, --global_attribute8
2287 X_GLOBAL_ATTRIBUTE9, --global_attribute9
2288 X_GLOBAL_ATTRIBUTE10, --global_attribute10
2289 X_GLOBAL_ATTRIBUTE11, --global_attribute11
2290 X_GLOBAL_ATTRIBUTE12, --global_attribute12
2291 X_GLOBAL_ATTRIBUTE13, --global_attribute13
2292 X_GLOBAL_ATTRIBUTE14, --global_attribute14
2293 X_GLOBAL_ATTRIBUTE15, --global_attribute15
2294 X_GLOBAL_ATTRIBUTE16, --global_attribute16
2295 X_GLOBAL_ATTRIBUTE17, --global_attribute17
2296 X_GLOBAL_ATTRIBUTE18, --global_attribute18
2297 X_GLOBAL_ATTRIBUTE19, --global_attribute19
2298 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
2299 SYSDATE, --creation_date
2300 G_USER_ID, --created_by
2301 G_USER_ID, --last_update_by
2302 SYSDATE, --last_update_date
2303 G_LOGIN_ID, --last_update_login
2304 NULL, --program_application_id
2305 NULL, --program_id
2306 NULL, --program_update_date
2307 NULL, --request_id
2308 X_RETAINED_AMOUNT, --retained_amount
2309 (-X_RETAINED_AMOUNT), --retained_amount_remaining
2310 --bugfix:5565310
2311 RCV.SHIP_TO_LOCATION_ID, --ship_to_location_id
2312 G_INTENDED_USE, --primary_intended_use
2313 G_PRODUCT_FISC_CLASS, --product_fisc_classification
2314 G_TRX_BUSINESS_CATEGORY, --trx_business_category
2315 G_PRODUCT_TYPE, --product_type
2316 G_PRODUCT_CATEGORY, --product_category
2317 G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_clas
2318 --Modified for bug#13505998 G_ASSESSABLE_VALUE,
2319 DECODE(X_Amount,l_full_mtch_amt,G_ASSESSABLE_VALUE, NULL),
2320 G_dflt_tax_class_code,
2321 G_PAY_AWT_GROUP_ID --bug 9689194
2322 FROM PO_DISTRIBUTIONS_AP_V PD,
2323 MTL_SYSTEM_ITEMS MSI,
2324 PO_AP_RECEIPT_MATCH_V RCV,
2325 RCV_SHIPMENT_LINES RSL,
2326 RCV_TRANSACTIONS RT --for 7015553
2327 WHERE RCV.RCV_TRANSACTION_ID = X_RCV_TRANSACTION_ID
2328 AND RCV.PO_LINE_LOCATION_ID = PD.LINE_LOCATION_ID
2329 AND RSL.SHIPMENT_LINE_ID = RCV.RCV_SHIPMENT_LINE_ID
2330 AND PD.PO_DISTRIBUTION_ID = NVL(X_PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID)
2331 AND MSI.INVENTORY_ITEM_ID(+) = RCV.ITEM_ID
2332 AND MSI.ORGANIZATION_ID(+) = G_INVENTORY_ORGANIZATION_ID
2333 AND RT.TRANSACTION_ID = RCV.RCV_TRANSACTION_ID --7015553
2334 AND PD.PO_DISTRIBUTION_ID = NVL(RT.PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID) --Added for bug#13845471
2335 AND ROWNUM = 1;
2336
2337
2338 /* for charge lines (frt and misc) allocated during matching */
2339 ELSIF (x_line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')) THEN
2340
2341 l_debug_info := 'Inserting Charge Line';
2342 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2343 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2344 END IF;
2345
2346 INSERT INTO AP_INVOICE_LINES
2347 (INVOICE_ID,
2348 LINE_NUMBER,
2349 LINE_TYPE_LOOKUP_CODE,
2350 REQUESTER_ID,
2351 DESCRIPTION,
2352 LINE_SOURCE,
2353 ORG_ID,
2354 INVENTORY_ITEM_ID,
2355 ITEM_DESCRIPTION,
2356 SERIAL_NUMBER,
2357 MANUFACTURER,
2358 MODEL_NUMBER,
2359 GENERATE_DISTS,
2360 MATCH_TYPE,
2361 DISTRIBUTION_SET_ID,
2362 ACCOUNT_SEGMENT,
2363 BALANCING_SEGMENT,
2364 COST_CENTER_SEGMENT,
2365 OVERLAY_DIST_CODE_CONCAT,
2366 DEFAULT_DIST_CCID,
2367 PRORATE_ACROSS_ALL_ITEMS,
2368 LINE_GROUP_NUMBER,
2369 ACCOUNTING_DATE,
2370 PERIOD_NAME,
2371 DEFERRED_ACCTG_FLAG,
2372 DEF_ACCTG_START_DATE,
2373 DEF_ACCTG_END_DATE,
2374 DEF_ACCTG_NUMBER_OF_PERIODS,
2375 DEF_ACCTG_PERIOD_TYPE,
2376 SET_OF_BOOKS_ID,
2377 AMOUNT,
2378 BASE_AMOUNT,
2379 ROUNDING_AMT,
2380 QUANTITY_INVOICED,
2381 UNIT_MEAS_LOOKUP_CODE,
2382 UNIT_PRICE,
2383 WFAPPROVAL_STATUS,
2384 -- USSGL_TRANSACTION_CODE, -- Removed for bug 4277744
2385 DISCARDED_FLAG,
2386 ORIGINAL_AMOUNT,
2387 ORIGINAL_BASE_AMOUNT,
2388 ORIGINAL_ROUNDING_AMT,
2389 CANCELLED_FLAG,
2390 INCOME_TAX_REGION,
2391 TYPE_1099,
2392 STAT_AMOUNT,
2393 PREPAY_INVOICE_ID,
2394 PREPAY_LINE_NUMBER,
2395 INVOICE_INCLUDES_PREPAY_FLAG,
2396 CORRECTED_INV_ID,
2397 CORRECTED_LINE_NUMBER,
2398 PO_HEADER_ID,
2399 PO_LINE_ID,
2400 PO_RELEASE_ID,
2401 PO_LINE_LOCATION_ID,
2402 PO_DISTRIBUTION_ID,
2403 RCV_TRANSACTION_ID,
2404 FINAL_MATCH_FLAG,
2405 ASSETS_TRACKING_FLAG,
2406 ASSET_BOOK_TYPE_CODE,
2407 ASSET_CATEGORY_ID,
2408 PROJECT_ID,
2409 TASK_ID,
2410 EXPENDITURE_TYPE,
2411 EXPENDITURE_ITEM_DATE,
2412 EXPENDITURE_ORGANIZATION_ID,
2413 PA_QUANTITY,
2414 PA_CC_AR_INVOICE_ID,
2415 PA_CC_AR_INVOICE_LINE_NUM,
2416 PA_CC_PROCESSED_CODE,
2417 AWARD_ID,
2418 AWT_GROUP_ID,
2419 REFERENCE_1,
2420 REFERENCE_2,
2421 RECEIPT_VERIFIED_FLAG,
2422 RECEIPT_REQUIRED_FLAG,
2423 RECEIPT_MISSING_FLAG,
2424 JUSTIFICATION,
2425 EXPENSE_GROUP,
2426 START_EXPENSE_DATE,
2427 END_EXPENSE_DATE,
2428 RECEIPT_CURRENCY_CODE,
2429 RECEIPT_CONVERSION_RATE,
2430 RECEIPT_CURRENCY_AMOUNT,
2431 DAILY_AMOUNT,
2432 WEB_PARAMETER_ID,
2433 ADJUSTMENT_REASON,
2434 MERCHANT_DOCUMENT_NUMBER,
2435 MERCHANT_NAME,
2436 MERCHANT_REFERENCE,
2437 MERCHANT_TAX_REG_NUMBER,
2438 MERCHANT_TAXPAYER_ID,
2439 COUNTRY_OF_SUPPLY,
2440 CREDIT_CARD_TRX_ID,
2441 COMPANY_PREPAID_INVOICE_ID,
2442 CC_REVERSAL_FLAG,
2443 ATTRIBUTE_CATEGORY,
2444 ATTRIBUTE1,
2445 ATTRIBUTE2,
2446 ATTRIBUTE3,
2447 ATTRIBUTE4,
2448 ATTRIBUTE5,
2449 ATTRIBUTE6,
2450 ATTRIBUTE7,
2451 ATTRIBUTE8,
2452 ATTRIBUTE9,
2453 ATTRIBUTE10,
2454 ATTRIBUTE11,
2455 ATTRIBUTE12,
2456 ATTRIBUTE13,
2457 ATTRIBUTE14,
2458 ATTRIBUTE15,
2459 /* GLOBAL_ATTRIBUTE_CATEGORY,
2460 GLOBAL_ATTRIBUTE1,
2461 GLOBAL_ATTRIBUTE2,
2462 GLOBAL_ATTRIBUTE3,
2463 GLOBAL_ATTRIBUTE4,
2464 GLOBAL_ATTRIBUTE5,
2465 GLOBAL_ATTRIBUTE6,
2466 GLOBAL_ATTRIBUTE7,
2467 GLOBAL_ATTRIBUTE8,
2468 GLOBAL_ATTRIBUTE9,
2469 GLOBAL_ATTRIBUTE10,
2470 GLOBAL_ATTRIBUTE11,
2471 GLOBAL_ATTRIBUTE12,
2472 GLOBAL_ATTRIBUTE13,
2473 GLOBAL_ATTRIBUTE14,
2474 GLOBAL_ATTRIBUTE15,
2475 GLOBAL_ATTRIBUTE16,
2476 GLOBAL_ATTRIBUTE17,
2477 GLOBAL_ATTRIBUTE18,
2478 GLOBAL_ATTRIBUTE19,
2479 GLOBAL_ATTRIBUTE20, */
2480 CREATION_DATE,
2481 CREATED_BY,
2482 LAST_UPDATED_BY,
2483 LAST_UPDATE_DATE,
2484 LAST_UPDATE_LOGIN,
2485 PROGRAM_APPLICATION_ID,
2486 PROGRAM_ID,
2487 PROGRAM_UPDATE_DATE,
2488 REQUEST_ID,
2489 --bugfix:5565310
2490 SHIP_TO_LOCATION_ID,
2491 PRIMARY_INTENDED_USE,
2492 PRODUCT_FISC_CLASSIFICATION,
2493 TRX_BUSINESS_CATEGORY,
2494 PRODUCT_TYPE,
2495 PRODUCT_CATEGORY,
2496 USER_DEFINED_FISC_CLASS,
2497 ASSESSABLE_VALUE,
2498 TAX_CLASSIFICATION_CODE,
2499 COST_FACTOR_ID,
2500 PAY_AWT_GROUP_ID --bug 9689194
2501 )
2502 SELECT X_INVOICE_ID, --invoice_id
2503 X_INVOICE_LINE_NUMBER, --invoice_line_number
2504 X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
2505 AIL.REQUESTER_ID, --requester_id
2506 --bug 5102208
2507 -- RTRIM added in BUG 10329867
2508 RTRIM(SUBSTRB(X_CHARGE_LINE_DESCRIPTION || AIL.description, 1, 240)),--description
2509 'CHRG ITEM MATCH', --line_source
2510 AIL.ORG_ID, --org_id
2511 NULL, --inventory_item_id
2512 NULL, --item_description
2513 NULL, --serial_number
2514 NULL, --manufacturer
2515 NULL, --model_number
2516 'Y', --generate_dists
2517 'NOT_MATCHED', --match_type
2518 NULL, --distribution_set_id
2519 NULL, --account_segment
2520 NULL, --balancing_segment
2521 NULL, --cost_center_segment
2522 NULL, --overlay_dist_code_concat
2523 --Bug6908977
2524 NULL, --default_dist_ccid
2525 'N', --prorate_across_all_items
2526 NULL, --line_group_number
2527 AIL.ACCOUNTING_DATE, --accounting_date
2528 AIL.PERIOD_NAME, --period_name
2529 'N', --deferred_acctg_flag
2530 NULL, --deferred_acctg_start_date
2531 NULL, --deferred_acctg_end_date
2532 NULL, --def_acctg_number_of_periods
2533 NULL, --def_acctg_period_type
2534 AIL.SET_OF_BOOKS_ID, --set_of_books_id
2535 X_AMOUNT, --amount
2536 AP_UTILITIES_PKG.AP_Round_Currency(
2537 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
2538 G_BASE_CURRENCY_CODE), --base_amount
2539 NULL, --rounding_amount
2540 NULL, --quantity_invoiced
2541 NULL, --unit_meas_lookup_code
2542 NULL, --unit_price
2543 AIL.WFAPPROVAL_STATUS, --wf_approval_status
2544 -- NULL, --ussgl_transaction_code -- Bug 4277744
2545 'N', --discarded_flag
2546 NULL, --original_amount
2547 NULL, --original_base_amount
2548 NULL, --original_rounding_amt
2549 'N', --cancelled_flag
2550 AIL.INCOME_TAX_REGION, --income_tax_region
2551 AIL.TYPE_1099, --type_1099
2552 NULL, --stat_amount
2553 NULL, --prepay_invoice_id
2554 NULL, --prepay_line_number
2555 NULL, --invoice_includes_prepay_flag
2556 NULL, --corrected_inv_id
2557 NULL, --corrected_line_number
2558 NULL, --po_header_id
2559 NULL, --po_line_id
2560 NULL, --po_release_id
2561 NULL, --po_line_location_id
2562 NULL, --po_distribution_id
2563 NULL, --rcv_transaction_id
2564 'N', --final_match_flag
2565 'N', --assets_tracking_flag
2566 NULL, --asset_book_type_code
2567 NULL, --asset_category_id
2568 AIL.PROJECT_ID, --project_id
2569 AIL.TASK_ID, --task_id
2570 AIL.EXPENDITURE_TYPE, --expenditure_type
2571 AIL.EXPENDITURE_ITEM_DATE,--expenditure_item_date
2572 AIL.EXPENDITURE_ORGANIZATION_ID, --expenditure_organization_id
2573 NULL, --pa_quantity
2574 NULL, --pa_cc_Ar_invoice_id
2575 NULL, --pa_cc_Ar_invoice_line_num
2576 NULL, --pa_cc_processed_code
2577 AIL.AWARD_ID, --award_id
2578 AIL.AWT_GROUP_ID, --awt_group_id
2579 NULL, --reference_1
2580 NULL, --reference_2
2581 NULL, --receipt_verified_flag
2582 NULL, --receipt_required_flag
2583 NULL, --receipt_missing_flag
2584 NULL, --justification
2585 NULL, --expense_group
2586 NULL, --start_expense_date
2587 NULL, --end_expense_date
2588 NULL, --receipt_currency_code
2589 NULL, --receipt_conversion_rate
2590 NULL, --receipt_currency_amount
2591 NULL, --daily_amount
2592 NULL, --web_parameter_id
2593 NULL, --adjustment_reason
2594 NULL, --merchant_document_number
2595 NULL, --merchant_name
2596 NULL, --merchant_reference
2597 NULL, --merchant_tax_reg_number
2598 NULL, --merchant_taxpayer_id
2599 NULL, --country_of_supply
2600 NULL, --credit_card_trx_id
2601 NULL, --company_prepaid_invoice_id
2602 NULL, --cc_reversal_flag
2603 AIL.attribute_category, --attribute_category
2604 AIL.attribute1, --attribute1
2605 AIL.attribute2, --attribute2
2606 AIL.attribute3, --attribute3
2607 AIL.attribute4, --attribute4
2608 AIL.attribute5, --attribute5
2609 AIL.attribute6, --attribute6
2610 AIL.attribute7, --attribute7
2611 AIL.attribute8, --attribute8
2612 AIL.attribute9, --attribute9
2613 AIL.attribute10, --attribute10
2614 AIL.attribute11, --attribute11
2615 AIL.attribute12, --attribute12
2616 AIL.attribute13, --attribute13
2617 AIL.attribute14, --attribute14
2618 AIL.attribute15, --attribute15
2619 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
2620 X_GLOBAL_ATTRIBUTE1, --global_attribute1
2621 X_GLOBAL_ATTRIBUTE2, --global_attribute2
2622 X_GLOBAL_ATTRIBUTE3, --global_attribute3
2623 X_GLOBAL_ATTRIBUTE4, --global_attribute4
2624 X_GLOBAL_ATTRIBUTE5, --global_attribute5
2625 X_GLOBAL_ATTRIBUTE6, --global_attribute6
2626 X_GLOBAL_ATTRIBUTE7, --global_attribute7
2627 X_GLOBAL_ATTRIBUTE8, --global_attribute8
2628 X_GLOBAL_ATTRIBUTE9, --global_attribute9
2629 X_GLOBAL_ATTRIBUTE10, --global_attribute10
2630 X_GLOBAL_ATTRIBUTE11, --global_attribute11
2631 X_GLOBAL_ATTRIBUTE12, --global_attribute12
2632 X_GLOBAL_ATTRIBUTE13, --global_attribute13
2633 X_GLOBAL_ATTRIBUTE14, --global_attribute14
2634 X_GLOBAL_ATTRIBUTE15, --global_attribute15
2635 X_GLOBAL_ATTRIBUTE16, --global_attribute16
2636 X_GLOBAL_ATTRIBUTE17, --global_attribute17
2637 X_GLOBAL_ATTRIBUTE18, --global_attribute18
2638 X_GLOBAL_ATTRIBUTE19, --global_attribute19
2639 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
2640 SYSDATE, --creation_date
2641 G_USER_ID, --created_by
2642 G_USER_ID, --last_updated_by
2643 SYSDATE, --last_updated_date
2644 G_LOGIN_ID, --last_update_login
2645 NULL, --program_application_id
2646 NULL, --program_id
2647 NULL, --program_update_date
2648 NULL, --request_id
2649 --bugfix:5565310
2650 AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
2651 G_INTENDED_USE, --primary_intended_use
2652 G_PRODUCT_FISC_CLASS, --product_fisc_classification
2653 G_TRX_BUSINESS_CATEGORY, --trx_business_category
2654 G_PRODUCT_TYPE, --product_type
2655 G_PRODUCT_CATEGORY, --product_category
2656 G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
2657 --G_ASSESSABLE_VALUE, --Bug#13505998
2658 DECODE(X_Amount,l_full_mtch_amt,X_Amount, NULL),
2659 G_dflt_tax_class_code,
2660 X_COST_FACTOR_ID, --cost_factor_id
2661 AIL.PAY_AWT_GROUP_ID --bug 9689194
2662 FROM AP_INVOICE_LINES AIL
2663 WHERE AIL.INVOICE_ID = X_INVOICE_ID
2664 AND AIL.LINE_NUMBER = X_ITEM_LINE_NUMBER;
2665
2666 END IF;
2667
2668 g_max_invoice_line_number := g_max_invoice_line_number + 1;
2669
2670 EXCEPTION WHEN OTHERS THEN
2671 IF (SQLCODE <> -20001) THEN
2672 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2673 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2674 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2675 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
2676 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
2677 ||', PO Distribution Id = '||to_char(x_po_distribution_id)
2678 ||', Project Id = '||to_char(g_project_id)
2679 ||', Task_Id ='||to_char(g_task_id)
2680 ||', Expenditure Type ='||g_expenditure_type
2681 ||', Expenditure_Organization_id ='||to_char(g_expenditure_organization_id));
2682 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2683 END IF;
2684 APP_EXCEPTION.RAISE_EXCEPTION;
2685
2686 END Insert_Invoice_Line;
2687
2688
2689
2690 PROCEDURE Insert_Invoice_Distributions (X_Invoice_ID IN NUMBER,
2691 X_Invoice_Line_Number IN NUMBER,
2692 X_Dist_Tab IN OUT NOCOPY
2693 AP_MATCHING_PKG.Dist_Tab_Type,
2694 X_Rcv_Transaction_Id IN NUMBER,
2695 X_Total_Amount IN NUMBER,
2696 X_Calling_Sequence IN VARCHAR2) IS
2697
2698 l_distribution_line_number NUMBER := 1;
2699 l_rounding_amount NUMBER;
2700 l_sum_prorated_amount NUMBER := 0;
2701 l_rounded_index NUMBER;
2702 l_rounded_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
2703 l_max_dist_amount NUMBER := 0;
2704 l_debug_info VARCHAR2(2000);
2705 current_calling_sequence VARCHAR2(2000);
2706 i NUMBER;
2707 l_api_name VARCHAR2(4000);
2708 l_copy_line_dff_flag VARCHAR2(1); -- Bug 6837035
2709
2710 BEGIN
2711 l_api_name := 'Insert Invoice Distributions';
2712
2713 current_calling_sequence := 'Insert_Invoice_Distributions <-'||current_calling_sequence;
2714
2715 l_debug_info := 'Insert Invoice Distributions';
2716 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2717 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2718 END IF;
2719
2720 -- Bug 6837035 Retrieve the profile value to check if the DFF info should be
2721 -- copied onto distributions for imported lines.
2722 l_copy_line_dff_flag := NVL(fnd_profile.value('AP_COPY_INV_LINE_DFF'),'N');
2723 FOR i in nvl(X_Dist_tab.FIRST, 0) .. nvl(X_Dist_tab.LAST, 0) LOOP
2724
2725 IF (x_dist_tab.exists(i)) THEN
2726
2727 INSERT INTO ap_invoice_distributions (
2728 batch_id,
2729 invoice_id,
2730 invoice_line_number,
2731 invoice_distribution_id,
2732 distribution_line_number,
2733 line_type_lookup_code,
2734 description,
2735 dist_match_type,
2736 distribution_class,
2737 org_id,
2738 dist_code_combination_id,
2739 accounting_date,
2740 period_name,
2741 amount_to_post,
2742 base_amount_to_post,
2743 posted_amount,
2744 posted_base_amount,
2745 je_batch_id,
2746 cash_je_batch_id,
2747 posted_flag,
2748 accounting_event_id,
2749 upgrade_posted_amt,
2750 upgrade_base_posted_amt,
2751 set_of_books_id,
2752 amount,
2753 base_amount,
2754 rounding_amt,
2755 match_status_flag,
2756 encumbered_flag,
2757 packet_id,
2758 -- ussgl_transaction_code, -- Removed for bug 4277744
2759 -- ussgl_trx_code_context, -- Removed for bug 4277744
2760 reversal_flag,
2761 parent_reversal_id,
2762 cancellation_flag,
2763 income_tax_region,
2764 type_1099,
2765 stat_amount,
2766 charge_applicable_to_dist_id,
2767 prepay_amount_remaining,
2768 prepay_distribution_id,
2769 parent_invoice_id,
2770 corrected_invoice_dist_id,
2771 corrected_quantity,
2772 other_invoice_id,
2773 po_distribution_id,
2774 rcv_transaction_id,
2775 unit_price,
2776 matched_uom_lookup_code,
2777 quantity_invoiced,
2778 final_match_flag,
2779 related_id,
2780 assets_addition_flag,
2781 assets_tracking_flag,
2782 asset_book_type_code,
2783 asset_category_id,
2784 project_id,
2785 task_id,
2786 expenditure_type,
2787 expenditure_item_date,
2788 expenditure_organization_id,
2789 pa_quantity,
2790 pa_addition_flag,
2791 pa_cc_ar_invoice_id,
2792 pa_cc_ar_invoice_line_num,
2793 pa_cc_processed_code,
2794 award_id,
2795 gms_burdenable_raw_cost,
2796 awt_flag,
2797 awt_group_id,
2798 awt_tax_rate_id,
2799 awt_gross_amount,
2800 awt_invoice_id,
2801 awt_origin_group_id,
2802 awt_invoice_payment_id,
2803 awt_withheld_amt,
2804 inventory_transfer_status,
2805 reference_1,
2806 reference_2,
2807 receipt_verified_flag,
2808 receipt_required_flag,
2809 receipt_missing_flag,
2810 justification,
2811 expense_group,
2812 start_expense_date,
2813 end_expense_date,
2814 receipt_currency_code,
2815 receipt_conversion_rate,
2816 receipt_currency_amount,
2817 daily_amount,
2818 web_parameter_id,
2819 adjustment_reason,
2820 merchant_document_number,
2821 merchant_name,
2822 merchant_reference,
2823 merchant_tax_reg_number,
2824 merchant_taxpayer_id,
2825 country_of_supply,
2826 credit_card_trx_id,
2827 company_prepaid_invoice_id,
2828 cc_reversal_flag,
2829 attribute_category,
2830 attribute1,
2831 attribute2,
2832 attribute3,
2833 attribute4,
2834 attribute5,
2835 attribute6,
2836 attribute7,
2837 attribute8,
2838 attribute9,
2839 attribute10,
2840 attribute11,
2841 attribute12,
2842 attribute13,
2843 attribute14,
2844 attribute15,
2845 /*global_attribute_category,
2846 global_attribute1,
2847 global_attribute2,*/
2848 --Bugfix:4674194
2849 global_attribute3,
2850 /*global_attribute4,
2851 global_attribute5,
2852 global_attribute6,
2853 global_attribute7,
2854 global_attribute8,
2855 global_attribute9,
2856 global_attribute10,
2857 global_attribute11,
2858 global_attribute12,
2859 global_attribute13,
2860 global_attribute14,
2861 global_attribute15,
2862 global_attribute16,
2863 global_attribute17,
2864 global_attribute18,
2865 global_attribute19,
2866 global_attribute20,*/
2867 created_by,
2868 creation_date,
2869 last_updated_by,
2870 last_update_date,
2871 last_update_login,
2872 program_application_id,
2873 program_id,
2874 program_update_date,
2875 request_id,
2876 intended_use,
2877 accrual_posted_flag,
2878 cash_posted_flag,
2879 --Freight and Special Charges
2880 rcv_charge_addition_flag,
2881 pay_awt_group_id --bug 9689194
2882 )
2883 SELECT g_batch_id, --batch_id
2884 x_invoice_id, --invoice_id
2885 x_invoice_line_number, --invoice_line_number
2886 x_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
2887 l_distribution_line_number, --distribution_line_number
2888 decode(g_invoice_type_lookup_code, 'PREPAYMENT', 'ITEM', -- bug 9081676: modify
2889 decode (pd.accrue_on_receipt_flag,'Y',
2890 'ACCRUAL','ITEM')), --line_type_lookup_code
2891 --Modified ail.item_description as description for bug#10200174
2892 nvl(ail.description, ail.item_description), --description
2893 /*Modified for bug#14502266 'ITEM_TO_RECEIPT', */
2894 decode(g_match_option,'R',
2895 'ITEM_TO_RECEIPT',
2896 'ITEM_TO_PO'), --dist_match_type --Modified for bug#10200174
2897 'PERMANENT', --distribution_class
2898 ail.org_id, --org_id
2899 x_dist_tab(i).dist_ccid, --dist_code_combination_id
2900 ail.accounting_date, --accounting_date
2901 ail.period_name, --period_name
2902 NULL, --amount_to_post
2903 NULL, --base_amount_to_post
2904 NULL, --posted_amount
2905 NULL, --posted_base_amount
2906 NULL, --je_batch_id
2907 NULL, --cash_je_batch_id
2908 'N', --posted_flag
2909 NULL, --accounting_event_id
2910 NULL, --upgrade_posted_amt
2911 NULL, --upgrade_base_posted_amt
2912 g_set_of_books_id, --set_of_books_id
2913 x_dist_tab(i).amount, --amount
2914 x_dist_tab(i).base_amount, --base_amount
2915 x_dist_tab(i).rounding_amt, --rounding_amount
2916 --bugfix:4959567
2917 NULL, --match_status_flag
2918 'N', --encumbered_flag
2919 NULL, --packet_id
2920 -- NVL(PD.ussgl_transaction_code, -- Bug 4277744
2921 -- ail.ussgl_transaction_code),--ussgl_transaction_code
2922 -- NULL, --ussgl_trx_code_context
2923 'N', --reversal_flag
2924 NULL, --parent_reversal_id
2925 'N', --cancellation_flag
2926 DECODE(ail.type_1099,'','',ail.income_tax_region),--income_tax_region
2927 ail.type_1099, --type_1099
2928 NULL, --stat_amount
2929 NULL, --charge_applicable_to_dist_id
2930 NULL, --prepay_amount_remaining
2931 NULL, --prepay_distribution_id
2932 NULL, --parent_invoice_id
2933 NULL, --corrected_invoice_dist_id
2934 NULL, --corrected_quantity
2935 NULL, --other_invoice_id
2936 x_dist_tab(i).po_distribution_id,--po_distribution_id
2937 decode(g_match_option,'R',
2938 x_rcv_transaction_id,NULL), --rcv_transaction_id
2939 x_dist_tab(i).unit_price, --unit_price
2940 rcv.receipt_uom_lookup_code, --matched_uom_lookup_code
2941 x_dist_tab(i).quantity_invoiced,--quantity_invoiced
2942 NULL, --final_match_flag
2943 NULL, --related_id
2944 'U', --assets_addition_flag
2945 decode(gcc.account_type,'E',
2946 ail.assets_tracking_flag,'A','Y','N'), --assets_tracking_flag
2947 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
2948 'Y',ail.asset_book_type_code,NULL), --asset_book_type_code
2949 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
2950 'Y',ail.asset_category_id), --asset_category_id
2951 x_dist_tab(i).project_id, --project_id
2952 x_dist_tab(i).task_id, --task_id
2953 x_dist_tab(i).expenditure_type, --expenditure_type
2954 x_dist_tab(i).expenditure_item_date, --expenditure_item_date
2955 x_dist_tab(i).expenditure_organization_id, --expenditure_organization_id
2956 x_dist_tab(i).pa_quantity, --pa_quantity
2957 decode(PD.project_id,NULL, 'E',
2958 decode(pd.destination_type_code,'SHOP FLOOR','M',
2959 'INVENTORY','M','N')), --pa_addition_flag
2960 NULL, --pa_cc_ar_invoice_id
2961 NULL, --pa_cc_ar_invoice_line_num
2962 NULL, --pa_cc_processed_code
2963 NULL, --award_id
2964 NULL, --gms_burdenable_raw_cost
2965 NULL, --awt_flag
2966 x_dist_tab(i).awt_group_id, --awt_group_id
2967 NULL, --awt_tax_rate_id
2968 NULL, --awt_gross_amount
2969 NULL, --awt_invoice_id
2970 NULL, --awt_origin_group_id
2971 NULL, --awt_invoice_payment_id
2972 NULL, --awt_withheld_amt
2973 'N', --inventory_transfer_status
2974 NULL, --reference_1
2975 NULL, --reference_2
2976 NULL, --receipt_verified_flag
2977 NULL, --receipt_required_flag
2978 NULL, --receipt_missing_flag
2979 NULL, --justification
2980 NULL, --expense_group
2981 NULL, --start_expense_date
2982 NULL, --end_expense_date
2983 NULL, --receipt_currency_code
2984 NULL, --receipt_conversion_rate
2985 NULL, --receipt_currency_amount
2986 NULL, --daily_amount
2987 NULL, --web_parameter_id
2988 NULL, --adjustment_reason
2989 NULL, --merchant_document_number
2990 NULL, --merchant_name
2991 NULL, --merchant_reference
2992 NULL, --merchant_tax_reg_number
2993 NULL, --merchant_taxpayer_id
2994 NULL, --country_of_supply
2995 NULL, --credit_card_trx_id
2996 NULL, --company_prepaid_invoice_id
2997 NULL, --cc_reversal_flag
2998 -- Bug 6837035 Start
2999 NVL(DECODE(g_transfer_flag,'Y',pd.attribute_category,DECODE(line_source,'IMPORTED',
3000 DECODE(l_copy_line_dff_flag,'Y',ail.attribute_category))), ''),--attribute_category
3001 NVL(DECODE(g_transfer_flag,'Y',pd.attribute1,DECODE(line_source,'IMPORTED',
3002 DECODE(l_copy_line_dff_flag,'Y',ail.attribute1))), ''), --attribute1
3003 NVL(DECODE(g_transfer_flag,'Y',pd.attribute2,DECODE(line_source,'IMPORTED',
3004 DECODE(l_copy_line_dff_flag,'Y',ail.attribute2))), ''), --attribute2
3005 NVL(DECODE(g_transfer_flag,'Y',pd.attribute3,DECODE(line_source,'IMPORTED',
3006 DECODE(l_copy_line_dff_flag,'Y',ail.attribute3))), ''), --attribute3
3007 NVL(DECODE(g_transfer_flag,'Y',pd.attribute4,DECODE(line_source,'IMPORTED',
3008 DECODE(l_copy_line_dff_flag,'Y',ail.attribute4))), ''), --attribute4
3009 NVL(DECODE(g_transfer_flag,'Y',pd.attribute5,DECODE(line_source,'IMPORTED',
3010 DECODE(l_copy_line_dff_flag,'Y',ail.attribute5))), ''), --attribute5
3011 NVL(DECODE(g_transfer_flag,'Y',pd.attribute6,DECODE(line_source,'IMPORTED',
3012 DECODE(l_copy_line_dff_flag,'Y',ail.attribute6))), ''), --attribute6
3013 NVL(DECODE(g_transfer_flag,'Y',pd.attribute7,DECODE(line_source,'IMPORTED',
3014 DECODE(l_copy_line_dff_flag,'Y',ail.attribute7))), ''), --attribute7
3015 NVL(DECODE(g_transfer_flag,'Y',pd.attribute8,DECODE(line_source,'IMPORTED',
3016 DECODE(l_copy_line_dff_flag,'Y',ail.attribute8))), ''), --attribute8
3017 NVL(DECODE(g_transfer_flag,'Y',pd.attribute9,DECODE(line_source,'IMPORTED',
3018 DECODE(l_copy_line_dff_flag,'Y',ail.attribute9))), ''), --attribute9
3019 NVL(DECODE(g_transfer_flag,'Y',pd.attribute10,DECODE(line_source,'IMPORTED',
3020 DECODE(l_copy_line_dff_flag,'Y',ail.attribute10))), ''), --attribute10
3021 NVL(DECODE(g_transfer_flag,'Y',pd.attribute11,DECODE(line_source,'IMPORTED',
3022 DECODE(l_copy_line_dff_flag,'Y',ail.attribute11))), ''), --attribute11
3023 NVL(DECODE(g_transfer_flag,'Y',pd.attribute12,DECODE(line_source,'IMPORTED',
3024 DECODE(l_copy_line_dff_flag,'Y',ail.attribute12))), ''), --attribute12
3025 NVL(DECODE(g_transfer_flag,'Y',pd.attribute13,DECODE(line_source,'IMPORTED',
3026 DECODE(l_copy_line_dff_flag,'Y',ail.attribute13))), ''), --attribute13
3027 NVL(DECODE(g_transfer_flag,'Y',pd.attribute14,DECODE(line_source,'IMPORTED',
3028 DECODE(l_copy_line_dff_flag,'Y',ail.attribute14))), ''), --attribute14
3029 NVL(DECODE(g_transfer_flag,'Y',pd.attribute15,DECODE(line_source,'IMPORTED',
3030 DECODE(l_copy_line_dff_flag,'Y',ail.attribute15))), ''), --attribute15
3031 -- Bug 6837035 End
3032 /* X_GLOBAL_ATTRIBUTE_CATEGORY
3033 X_GLOBAL_ATTRIBUTE1,
3034 X_GLOBAL_ATTRIBUTE2,*/
3035 --Bugfix:4674194
3036 DECODE(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
3037 'Y',ail.ship_to_location_id, ''),
3038 /*X_GLOBAL_ATTRIBUTE4,
3039 X_GLOBAL_ATTRIBUTE5,
3040 X_GLOBAL_ATTRIBUTE6,
3041 X_GLOBAL_ATTRIBUTE7,
3042 X_GLOBAL_ATTRIBUTE8,
3043 X_GLOBAL_ATTRIBUTE9,
3044 X_GLOBAL_ATTRIBUTE10,
3045 X_GLOBAL_ATTRIBUTE11,
3046 X_GLOBAL_ATTRIBUTE12,
3047 X_GLOBAL_ATTRIBUTE13,
3048 X_GLOBAL_ATTRIBUTE14,
3049 X_GLOBAL_ATTRIBUTE15,
3050 X_GLOBAL_ATTRIBUTE16,
3051 X_GLOBAL_ATTRIBUTE17,
3052 X_GLOBAL_ATTRIBUTE18,
3053 X_GLOBAL_ATTRIBUTE19,
3054 X_GLOBAL_ATTRIBUTE20, */
3055 ail.created_by, --created_by
3056 sysdate, --creation_date
3057 ail.last_updated_by, --last_updated_by
3058 sysdate, --last_update_date
3059 ail.last_update_login, --last_update_login
3060 NULL, --program_application_id
3061 NULL, --program_id
3062 NULL, --program_update_date
3063 NULL, --request_id
3064 --bugfix:5565310
3065 g_intended_use, --intended_use
3066 'N', --accrual_posted_flag
3067 'N', --cash_posted_flag
3068 'N', --rcv_charge_addition_flag
3069 x_dist_tab(i).pay_awt_group_id --pay_awt_group_id bug9689194
3070 FROM po_distributions pd,--bug6683010
3071 ap_invoice_lines ail,
3072 po_ap_receipt_match_v rcv,
3073 gl_code_combinations gcc
3074 WHERE ail.invoice_id = x_invoice_id
3075 AND ail.line_number = x_invoice_line_number
3076 AND ail.po_line_location_id = pd.line_location_id
3077 AND pd.po_distribution_id = x_dist_tab(i).po_distribution_id
3078 --bug6683010
3079 AND gcc.code_combination_id = decode(pd.accrue_on_receipt_flag, 'Y',
3080 pd.code_combination_id,x_dist_tab(i).dist_ccid)
3081 AND pd.line_location_id = rcv.po_line_location_id
3082 AND rcv.rcv_transaction_id = x_rcv_transaction_id;
3083
3084 --Bugfix:4674635
3085 l_debug_info := 'Call the AP_EXTENDED_MATCH to populate global attributes';
3086 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3088 END IF;
3089
3090 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
3091 AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_MATCH(
3092 P_Credit_Id => NULL,
3093 P_Invoice_Id => X_invoice_id,
3094 P_Inv_Line_Num => x_invoice_line_number,
3095 P_Distribution_Id => x_dist_tab(i).invoice_distribution_id,
3096 P_Parent_Dist_Id => NULL);
3097
3098 END IF;
3099
3100
3101 GMS_AP_API.CREATE_AWARD_DISTRIBUTIONS
3102 ( p_invoice_id => x_invoice_id,
3103 p_distribution_line_number => l_distribution_line_number,
3104 p_invoice_distribution_id => x_dist_tab(i).invoice_distribution_id,
3105 p_award_id => x_dist_tab(i).award_id,
3106 p_mode => 'AP',
3107 p_dist_set_id => NULL,
3108 p_dist_set_line_number => NULL );
3109
3110 l_distribution_line_number := l_distribution_line_number + 1;
3111
3112 AP_DBI_PKG.Maintain_DBI_Summary
3113 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
3114 p_operation => 'I',
3115 p_key_value1 => x_invoice_id,
3116 p_key_value2 => x_dist_tab(i).invoice_distribution_id,
3117 p_calling_sequence => current_calling_sequence);
3118
3119 END IF; /*IF (x_dist_tab.exists(i))*/
3120
3121 END LOOP;
3122
3123
3124 EXCEPTION
3125 WHEN OTHERS THEN
3126
3127 IF (SQLCODE <> -20001) THEN
3128 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3129 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3130 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3131 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch_Id = '||TO_CHAR(g_Batch_Id)
3132 ||', Invoice_id = '||TO_CHAR(X_invoice_id)
3133 ||', Invoice Line Number = '||X_Invoice_Line_Number
3134 ||', Dist_num = '||l_distribution_line_number
3135 ||', Allow_PA_Override = '||g_allow_pa_override
3136 ||', Transfer_Desc_Flag = '||g_Transfer_Flag);
3137 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3138 END IF;
3139
3140 --Clean up the PL/SQL table
3141 x_dist_tab.delete;
3142
3143 APP_EXCEPTION.RAISE_EXCEPTION;
3144
3145 END Insert_Invoice_Distributions;
3146
3147
3148 Procedure Update_PO_Shipments_Dists(
3149 X_Dist_Tab IN OUT NOCOPY AP_MATCHING_PKG.Dist_Tab_Type,
3150 X_Po_Line_Location_Id IN NUMBER,
3151 X_Match_Amount IN NUMBER,
3152 X_Match_Quantity IN NUMBER,
3153 X_Uom_Lookup_Code IN VARCHAR2,
3154 X_Calling_Sequence IN VARCHAR2) IS
3155
3156 current_calling_sequence VARCHAR2(2000);
3157 l_debug_info VARCHAR2(2000);
3158 i NUMBER;
3159 l_quantity_invoiced number;
3160 l_amount number;
3161 l_po_dist_id number;
3162 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
3163 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
3164 l_api_name VARCHAR2(4000);
3165 l_return_status VARCHAR2(100);
3166 l_msg_data VARCHAR2(4000);
3167 BEGIN
3168
3169 l_api_name := 'Update_PO_Shipments_Dists';
3170
3171 current_calling_sequence := 'Update_Po_Shipments_Dists<-'||x_calling_sequence;
3172
3173 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3174 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Distributions(+)');
3175 END IF;
3176
3177 l_debug_info := 'Create l_po_ap_dist_rec object';
3178 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3179 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3180 END IF;
3181
3182 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
3183
3184 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
3185 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3186 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3187 END IF;
3188
3189 --bugfix:5509730 added the IF condition and the ELSE part
3190 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
3191
3192 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
3193 p_po_line_location_id => x_po_line_location_id,
3194 p_uom_code => x_uom_lookup_code,
3195 p_quantity_billed => x_match_quantity,
3196 p_amount_billed => x_match_amount,
3197 p_quantity_financed => NULL,
3198 p_amount_financed => NULL,
3199 p_quantity_recouped => NULL,
3200 p_amount_recouped => NULL,
3201 p_retainage_withheld_amt => NULL,
3202 p_retainage_released_amt => NULL
3203 );
3204
3205 ELSIF (g_invoice_type_lookup_code = 'PREPAYMENT') THEN
3206
3207
3208 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
3209 p_po_line_location_id => x_po_line_location_id,
3210 p_uom_code => x_uom_lookup_code,
3211 p_quantity_billed => NULL,
3212 p_amount_billed => NULL,
3213 p_quantity_financed => x_match_quantity,
3214 p_amount_financed => x_match_amount,
3215 p_quantity_recouped => NULL,
3216 p_amount_recouped => NULL,
3217 p_retainage_withheld_amt => NULL,
3218 p_retainage_released_amt => NULL
3219 );
3220
3221 END IF;
3222
3223 l_debug_info := 'Populate the Po_Ap_Dist_Rec with the distribution information';
3224 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3225 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3226 END IF;
3227 FOR i in x_dist_tab.first..x_dist_tab.last LOOP
3228
3229 IF (x_dist_tab.exists(i)) THEN
3230
3231 --bugfix:5509730 added the IF condition and the ELSE part
3232 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
3233
3234 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
3235 p_uom_code => x_uom_lookup_code,
3236 p_quantity_billed => x_dist_tab(i).quantity_invoiced,
3237 p_amount_billed => x_dist_tab(i).amount,
3238 p_quantity_financed => NULL,
3239 p_amount_financed => NULL,
3240 p_quantity_recouped => NULL,
3241 p_amount_recouped => NULL,
3242 p_retainage_withheld_amt => NULL,
3243 p_retainage_released_amt => NULL);
3244
3245 ELSIF (g_invoice_type_lookup_code = 'PREPAYMENT') THEN
3246
3247 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
3248 p_uom_code => x_uom_lookup_code,
3249 p_quantity_billed => NULL,
3250 p_amount_billed => NULL,
3251 p_quantity_financed => x_dist_tab(i).quantity_invoiced,
3252 p_amount_financed => x_dist_tab(i).amount,
3253 p_quantity_recouped => NULL,
3254 p_amount_recouped => NULL,
3255 p_retainage_withheld_amt => NULL,
3256 p_retainage_released_amt => NULL);
3257
3258 END IF;
3259
3260 END IF;
3261
3262 END LOOP;
3263
3264 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3265 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3266 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3267 END IF;
3268
3269 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
3270 P_Api_Version => 1.0,
3271 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
3272 P_Dist_Changes_Rec => l_po_ap_dist_rec,
3273 X_Return_Status => l_return_status,
3274 X_Msg_Data => l_msg_data);
3275
3276
3277 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3278 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Shipments_Dists(-)');
3279 END IF;
3280
3281
3282
3283 EXCEPTION
3284 WHEN OTHERS THEN
3285 IF (SQLCODE <> -20001) THEN
3286 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3287 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3288 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3289 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3290 'PO Distribution Id = '||TO_CHAR(X_Dist_tab(i).po_distribution_id)
3291 ||', UOM= '||X_uom_lookup_code);
3292 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3293 END IF;
3294 APP_EXCEPTION.RAISE_EXCEPTION;
3295
3296 END Update_PO_Shipments_Dists;
3297
3298 PROCEDURE Update_RCV_Transaction(X_Rcv_Transaction_Id IN NUMBER,
3299 X_Match_Amount IN NUMBER,
3300 X_Match_Quantity IN NUMBER,
3301 X_Uom_lookup_code IN VARCHAR2,
3302 X_Calling_Sequence IN VARCHAR2) IS
3303
3304 current_calling_sequence VARCHAR2(2000);
3305 l_debug_info VARCHAR2(2000);
3306 l_api_name VARCHAR2(4000);
3307 BEGIN
3308
3309 l_api_name := 'Update_Rcv_Transaction';
3310 current_calling_sequence := 'Update_Rcv_Transaction<-'||x_calling_sequence;
3311
3312 --For the case of automatic debit memo creation from RTS transaction,
3313 --we do match the debit memo to the receipt regardless of the match
3314 --option on the po shipment. So when updating the quantities need to
3315 --make sure we do not update the quantities/amounts on the receipt
3316 --if the match option is 'P'. So, Need the below IF condition so that
3317 --quantity_billed on the parent 'RECEIVE' transaction for this RTS transaction,
3318 --does not become negative.
3319
3320 IF( g_match_option = 'R') THEN
3321 l_debug_info := 'Call RCV api to update amount/quantity billed on the
3322 rcv_transaction';
3323 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3325 END IF;
3326
3327 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
3328 X_rcv_transaction_id => x_rcv_transaction_id,
3329 X_quantity_billed => x_match_quantity,
3330 X_uom_lookup_code => x_uom_lookup_code,
3331 X_amount_billed => x_match_amount,
3332 X_matching_basis => 'QUANTITY');
3333
3334 END IF;
3335
3336 EXCEPTION
3337 WHEN OTHERS THEN
3338 IF (SQLCODE <> -20001) THEN
3339 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3340 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3341 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3342 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3343 'Rcv Transaction ID = '||TO_CHAR(X_Rcv_Transaction_id)
3344 ||', Match amount = '||to_char(x_match_amount)
3345 ||', Match quantity = '||to_char(x_match_quantity)
3346 ||', PO Uom = '||x_uom_lookup_code);
3347 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3348 END IF;
3349 APP_EXCEPTION.RAISE_EXCEPTION;
3350
3351 END Update_RCV_Transaction;
3352
3353
3354 Procedure Create_Charge_Lines( X_Invoice_Id IN NUMBER,
3355 X_Rcv_Transaction_Id IN NUMBER,
3356 X_Freight_Cost_Factor_Id IN NUMBER,
3357 X_Freight_Amount IN NUMBER,
3358 X_Freight_Description IN VARCHAR2,
3359 X_Misc_Cost_Factor_Id IN NUMBER,
3360 X_Misc_Amount IN NUMBER,
3361 X_Misc_Description IN VARCHAR2,
3362 X_Item_Line_Number IN NUMBER,
3363 X_Calling_Sequence IN VARCHAR2) IS
3364
3365 l_debug_info VARCHAR2(2000);
3366 current_calling_sequence VARCHAR2(2000);
3367 l_api_name VARCHAR2(4000);
3368
3369 BEGIN
3370
3371 current_calling_sequence := 'Create_Charge_Lines<-'||X_Calling_Sequence;
3372 l_api_name := 'Create_Charge_lines';
3373
3374 IF (X_Freight_Amount IS NOT NULL) THEN
3375
3376 l_debug_info := 'Create Freight Line';
3377 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3378 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3379 END IF;
3380
3381 Insert_Invoice_Line
3382 (X_Invoice_Id => x_invoice_id,
3383 X_Rcv_Transaction_id => x_rcv_transaction_id,
3384 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
3385 X_Line_Type_Lookup_Code => 'FREIGHT',
3386 X_Cost_Factor_Id => x_freight_cost_factor_id,
3387 X_Amount => x_freight_amount,
3388 X_Item_Line_Number => x_item_line_number,
3389 X_Charge_Line_Description => x_freight_description,
3390 X_Calling_Sequence => current_calling_sequence);
3391
3392 l_debug_info := 'Create Allocation Rules for the freight line';
3393 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3394 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3395 END IF;
3396
3397 AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
3398 X_Invoice_id => x_invoice_id,
3399 X_Chrg_Line_Number => g_max_invoice_line_number,
3400 X_To_Line_Number => x_item_line_number,
3401 X_Percentage => 100,
3402 X_Calling_Sequence => x_calling_sequence);
3403
3404 END IF;
3405
3406
3407 IF (X_Misc_Amount IS NOT NULL) THEN
3408
3409 l_debug_info := 'Create Misc Line';
3410 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3411 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3412 END IF;
3413
3414 Insert_Invoice_Line
3415 (X_Invoice_Id => x_invoice_id,
3416 X_Rcv_Transaction_id => x_rcv_transaction_id,
3417 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
3418 X_Line_Type_Lookup_Code => 'MISCELLANEOUS',
3419 X_Cost_Factor_Id => x_misc_cost_factor_id,
3420 X_Amount => x_misc_amount,
3421 X_Item_Line_Number => x_item_line_number,
3422 X_Charge_Line_Description => x_misc_description,
3423 X_Calling_Sequence => current_calling_sequence);
3424
3425 l_debug_info := 'Create Allocation Rules for the misc line';
3426 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3427 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3428 END IF;
3429
3430 AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
3431 X_Invoice_id => x_invoice_id,
3432 X_Chrg_Line_Number => g_max_invoice_line_number,
3433 X_To_Line_Number => x_item_line_number,
3434 X_Percentage => 100,
3435 X_Calling_Sequence => x_calling_sequence);
3436 END IF;
3437
3438
3439 EXCEPTION
3440 WHEN OTHERS THEN
3441 IF (SQLCODE <> -20001) THEN
3442 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3443 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3444 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3445 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||to_char(X_Invoice_Id)
3446 ||', Freight Amount = '||to_char(x_freight_amount)
3447 ||', Freight Description = '||x_freight_description
3448 ||', Misc Amount = '||to_char(x_misc_amount)
3449 ||', Misc Description = '||x_misc_description
3450 ||', Item Line Number = '||TO_CHAR(X_Item_Line_Number)
3451 ||', Accounting Date = '||g_accounting_date
3452 ||', Period Name = '||g_period_name
3453 ||', Set of books id = '||to_char(g_set_of_books_id)
3454 ||', Exchange Rate = '||g_exchange_rate
3455 ||', Base Currency Code = '||g_base_currency_code
3456 ||', Income Tax Region = '||g_income_tax_region
3457 ||', Awt Group Id = '||g_awt_group_id
3458 ||', Transfer Flag = '||g_transfer_flag
3459 ||', Pay Awt Group Id = '||g_pay_awt_group_id);
3460 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3461 END IF;
3462 APP_EXCEPTION.RAISE_EXCEPTION;
3463
3464 END Create_Charge_Lines;
3465
3466
3467 /*========================================================================================
3468 **************PRICE AND QUANTITY CORRECTION OF INVOICE MATCHED TO RECEIPT*****************
3469 =========================================================================================*/
3470
3471
3472 PROCEDURE Price_Quantity_Correct_Inv_RCV(
3473 X_Invoice_Id IN NUMBER,
3474 X_Invoice_Line_Number IN NUMBER,
3475 X_Corrected_Invoice_Id IN NUMBER,
3476 X_Corrected_Line_Number IN NUMBER,
3477 X_Correction_Type IN VARCHAR2,
3478 X_Correction_Quantity IN NUMBER,
3479 X_Correction_Amount IN NUMBER,
3480 X_Correction_Price IN NUMBER,
3481 X_Match_Mode IN VARCHAR2,
3482 X_Po_Line_Location_Id IN NUMBER,
3483 X_Rcv_Transaction_Id IN NUMBER,
3484 x_CORR_dIST_tAB IN OUT NOCOPY
3485 AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
3486 X_Uom_Lookup_Code IN VARCHAR2,
3487 X_Retained_Amount IN NUMBER DEFAULT NULL,
3488 X_Calling_Sequence IN VARCHAR2) IS
3489
3490 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE := NULL;
3491 l_item_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
3492 l_amount_to_recoup AP_INVOICE_LINES_ALL.AMOUNT%TYPE;
3493 l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
3494 l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3495 l_retained_amount ap_invoice_lines_all.retained_amount%TYPE;
3496 l_success BOOLEAN;
3497 l_error_message VARCHAR2(4000);
3498 l_debug_info VARCHAR2(2000);
3499 current_calling_sequence VARCHAR2(2000);
3500 l_api_name VARCHAR2(4000);
3501
3502 BEGIN
3503
3504 l_api_name := 'Price_Quantity_Correct_Inv_Rcv';
3505
3506 current_calling_sequence := 'Price_Quantity_Correct_Inv_RCV<-'||x_calling_sequence;
3507
3508 Get_Info(x_invoice_id => x_invoice_id,
3509 x_invoice_line_number => x_invoice_line_number,
3510 x_match_amount => x_correction_amount,
3511 x_po_line_location_id => x_po_line_location_id,
3512 x_calling_sequence => current_calling_sequence);
3513
3514 IF g_invoice_type_lookup_code <> 'PREPAYMENT' THEN
3515 l_retained_amount := AP_INVOICE_LINES_UTILITY_PKG.Get_Retained_Amount
3516 (p_line_location_id => x_po_line_location_id,
3517 p_match_amount => x_correction_amount);
3518 END IF;
3519
3520 Get_Corr_Dist_Proration_Info(
3521 x_corrected_invoice_id => x_corrected_invoice_id,
3522 x_corrected_line_number => x_corrected_line_number,
3523 x_corr_dist_tab => x_corr_dist_tab,
3524 x_correction_type => x_correction_type,
3525 x_correction_amount => x_correction_amount,
3526 x_correction_quantity => x_correction_quantity,
3527 x_correction_price => x_correction_price,
3528 x_match_mode => x_match_mode,
3529 x_calling_sequence => current_calling_sequence);
3530
3531 IF (x_corr_dist_tab.COUNT = 1) THEN
3532
3533 l_po_distribution_id := x_corr_dist_tab.FIRST;
3534
3535 END IF;
3536
3537 IF (x_invoice_line_number IS NULL) THEN
3538
3539 Insert_Corr_Invoice_Line(x_invoice_id => x_invoice_id,
3540 x_invoice_line_number => g_max_invoice_line_number +1,
3541 x_corrected_invoice_id => x_corrected_invoice_id,
3542 x_corrected_line_number => x_corrected_line_number,
3543 x_quantity => x_correction_quantity,
3544 x_amount => x_correction_amount,
3545 x_unit_price => x_correction_price,
3546 x_correction_type => x_correction_type,
3547 x_po_distribution_id => l_po_distribution_id,
3548 x_retained_amount => l_retained_amount,
3549 x_calling_sequence => current_calling_sequence);
3550
3551 END IF;
3552
3553 l_item_line_number := g_max_invoice_line_number;
3554
3555
3556 Insert_Corr_Invoice_Dists(x_invoice_id => x_invoice_id,
3557 x_invoice_line_number => nvl(x_invoice_line_number,
3558 g_max_invoice_line_number),
3559 x_corrected_invoice_id => x_corrected_invoice_id,
3560 x_corr_dist_tab => x_corr_dist_tab,
3561 x_correction_type => x_correction_type,
3562 x_total_amount => x_correction_amount,
3563 x_calling_sequence => current_calling_sequence);
3564
3565
3566 IF(x_invoice_line_number IS NOT NULL) THEN
3567
3568 UPDATE ap_invoice_lines ail
3569 SET (generate_dists ,
3570 attribute_category,
3571 attribute1,
3572 attribute2,
3573 attribute3,
3574 attribute4,
3575 attribute5,
3576 attribute6,
3577 attribute7,
3578 attribute8,
3579 attribute9,
3580 attribute10,
3581 attribute11,
3582 attribute12,
3583 attribute13,
3584 attribute14,
3585 attribute15,
3586 retained_amount,
3587 retained_amount_remaining)
3588 =
3589 (SELECT 'D',
3590 /* Bug 7483260. If the attribute field is populated in the
3591 * interface, take that value. If the attribute field from
3592 * the interface is null and the transfer_desc_flex_flag is
3593 * Y, take the value from the purchase order.
3594 */
3595 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
3596 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
3597 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
3598 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
3599 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
3600 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
3601 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
3602 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
3603 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
3604 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
3605 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
3606 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
3607 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
3608 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
3609 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
3610 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
3611 --end Bug 7483260
3612 l_retained_amount,
3613 -1 * l_retained_amount
3614 FROM ap_invoice_lines ail1,
3615 po_line_locations pll
3616 WHERE ail1.invoice_id = x_invoice_id
3617 AND ail1.line_number =x_invoice_line_number
3618 AND pll.line_location_id = ail1.po_line_location_id)
3619 WHERE invoice_id = x_invoice_id
3620 AND line_number = x_invoice_line_number;
3621
3622 END IF;
3623
3624 l_debug_info := 'Create Retainage Distributions';
3625 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3626 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3627 END IF;
3628
3629 Ap_Retainage_Pkg.Create_Retainage_Distributions
3630 (x_invoice_id => x_invoice_id,
3631 x_invoice_line_number => nvl(x_invoice_line_number,g_max_invoice_line_number));
3632
3633 IF (g_recoupment_rate is not null and x_correction_amount > 0
3634 and g_invoice_type_lookup_code <>'PREPAYMENT') THEN
3635
3636 l_debug_info := 'Calculate the maximum amount that can be recouped from this invoice line';
3637 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3638 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3639 END IF;
3640
3641 l_line_amt_net_retainage := x_correction_amount + nvl(l_retained_amount,0);
3642
3643 l_max_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
3644 (x_correction_amount * g_recoupment_rate / 100) ,g_invoice_currency_code);
3645
3646 IF (l_line_amt_net_retainage < l_max_amount_to_recoup) THEN
3647 l_amount_to_recoup := l_line_amt_net_retainage;
3648 ELSE
3649 l_amount_to_recoup := l_max_amount_to_recoup;
3650 END IF;
3651
3652
3653 l_debug_info := 'Automatically recoup any available prepayments against the same po line';
3654 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3655 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3656 END IF;
3657
3658 l_success := AP_Matching_Utils_Pkg.Ap_Recoup_Invoice_Line(
3659 P_Invoice_Id => x_invoice_id ,
3660 P_Invoice_Line_Number => nvl(x_invoice_line_number,g_max_invoice_line_number) ,
3661 P_Amount_To_Recoup => l_amount_to_recoup,
3662 P_Po_Line_Id => g_po_line_id,
3663 P_Vendor_Id => g_vendor_id,
3664 P_Vendor_Site_Id => g_vendor_site_id,
3665 P_Accounting_Date => g_accounting_date,
3666 P_Period_Name => g_period_name,
3667 P_User_Id => g_user_id,
3668 P_Last_Update_Login => g_login_id ,
3669 P_Error_Message => l_error_message,
3670 P_Calling_Sequence => current_calling_sequence);
3671
3672 END IF;
3673
3674 l_debug_info := 'Calling Update_Corr_Po_Shipments_Dists';
3675 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3676 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3677 END IF;
3678
3679 Update_Corr_Po_Shipments_Dists(x_corr_dist_tab => x_corr_dist_tab,
3680 x_po_line_location_id => x_po_line_location_id,
3681 x_quantity => x_correction_quantity,
3682 x_amount => x_correction_amount,
3683 x_correction_type => x_correction_type,
3684 x_uom_lookup_code => x_uom_lookup_code,
3685 x_calling_sequence => current_calling_sequence);
3686
3687
3688 IF (x_correction_type = 'PRICE_CORRECTION') THEN
3689
3690 Update_Rcv_Transaction(x_rcv_transaction_id => x_rcv_transaction_id,
3691 x_match_quantity => 0,
3692 x_match_amount => x_correction_amount,
3693 x_uom_lookup_code => x_uom_lookup_code,
3694 x_calling_sequence => current_calling_sequence);
3695
3696 ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3697
3698 Update_Rcv_Transaction(x_rcv_transaction_id => x_rcv_transaction_id,
3699 x_match_quantity => x_correction_quantity,
3700 x_match_amount => x_correction_amount,
3701 x_uom_lookup_code => x_uom_lookup_code,
3702 x_calling_sequence => current_calling_sequence);
3703
3704 END IF;
3705
3706 --Clean up the PL/SQL table
3707 x_corr_dist_tab.delete;
3708
3709
3710 EXCEPTION
3711 WHEN others then
3712 If (SQLCODE <> -20001) Then
3713 fnd_message.set_name('SQLAP','AP_DEBUG');
3714 fnd_message.set_token('ERROR',SQLERRM);
3715 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
3716 fnd_message.set_token('PARAMETERS',
3717 ' invoice_id = '||to_char(x_invoice_id)
3718 ||', invoice_line_number = ' ||to_char(x_invoice_line_number)
3719 ||', corrected_invoice_id = '||to_char(x_corrected_invoice_id)
3720 ||', corrected_line_number = '||to_char(x_corrected_line_number)
3721 ||', correction_type = '||x_correction_type
3722 ||', match_mode = '||x_match_mode
3723 ||', correction quantity = '||to_char(x_correction_quantity)
3724 ||', correction amount = '||to_char(x_correction_amount)
3725 ||', correction price = '||to_char(x_correction_price)
3726 ||', po_line_location_id = '||to_char(x_po_line_location_id)
3727 ||', rcv_transaction_id = '||to_char(x_rcv_transaction_id));
3728 fnd_message.set_token('DEBUG_INFO',l_debug_info);
3729 End if;
3730
3731 --Clean up the PL/SQL table
3732 x_corr_dist_tab.delete;
3733
3734 app_exception.raise_exception;
3735
3736 END Price_Quantity_Correct_Inv_RCV;
3737
3738
3739
3740 PROCEDURE Get_Corr_Dist_Proration_Info(
3741 x_corrected_invoice_id IN NUMBER,
3742 x_corrected_line_number IN NUMBER,
3743 x_corr_dist_tab IN OUT NOCOPY
3744 AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
3745 x_correction_type IN VARCHAR2,
3746 x_correction_amount IN NUMBER,
3747 x_correction_quantity IN NUMBER,
3748 x_correction_price IN NUMBER,
3749 x_match_mode IN VARCHAR2,
3750 x_calling_sequence IN VARCHAR2) IS
3751
3752 --Modified cursor for bug#11886930
3753 --CURSOR Price_Correction_Cursor (c_price_variance NUMBER) IS
3754 CURSOR Price_Correction_Cursor IS
3755 SELECT aid1.invoice_distribution_id corrected_inv_dist_id,
3756 aid1.po_distribution_id,
3757 decode(g_min_acct_unit,'',
3758 round(x_correction_amount * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount,
3759 g_precision),
3760 round((x_correction_amount * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount)
3761 /g_min_acct_unit) * g_min_acct_unit
3762 ) amount,
3763 round(x_correction_quantity * (aid1.amount + /*c_price_variance*/nvl(aid2.amount,0))/ail.amount,15) corrected_quantity,
3764 --bugfix:5606697
3765 DECODE(pd.destination_type_code,
3766 'EXPENSE', DECODE(pd.accrue_on_receipt_flag,
3767 'Y', pd.code_combination_id,
3768 aid1.dist_code_combination_id),
3769 pd.variance_account_id),
3770 ap_invoice_distributions_s.nextval
3771 FROM ap_invoice_lines ail,
3772 ap_invoice_distributions aid1,
3773 po_distributions pd,
3774 ap_invoice_distributions aid2
3775 WHERE ail.invoice_id = x_corrected_invoice_id
3776 AND ail.line_number = x_corrected_line_number
3777 AND aid1.invoice_id = ail.invoice_id
3778 AND aid1.invoice_line_number = ail.line_number
3779 AND aid1.po_distribution_id = pd.po_distribution_id --bugfix:5606697
3780 -- Bug 5585744, Modified the condition below
3781 AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
3782 AND aid1.prepay_distribution_id IS NULL
3783 AND aid1.invoice_distribution_id = aid2.related_id(+)
3784 AND aid2.line_type_lookup_code(+) = 'IPV';
3785 /*AND aid.line_type_lookup_code NOT IN ('PREPAY','AWT','RETAINAGE')
3786 AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3787 OR aid.prepay_distribution_id IS NULL); */
3788
3789 /*-----------------------------------------------------------
3790 Amount: Quantity_Invoiced at the line level prorated based on
3791 quantity_billed against the PO and multiplied by the unit_price
3792 at the line level.
3793
3794 Corrected Quantity: Quantity_Invoiced selected prorated as done
3795 for the Amount.
3796 ------------------------------------------------------------*/
3797 CURSOR Quantity_Correction_Cursor(p_total_quantity_billed IN NUMBER) IS
3798 SELECT aid.invoice_distribution_id corrected_inv_dist_id,
3799 aid.po_distribution_id,
3800 decode(g_min_acct_unit,'',
3801 round((x_correction_quantity *
3802 pd.quantity_billed/p_total_quantity_billed
3803 --bugfix:4749294, modified ail.unit_price to x_correction_price
3804 ) * x_correction_price,
3805 g_precision),
3806 round(((x_correction_quantity * pd.quantity_billed/
3807 --bugfix:4749294, modified ail.unit_price to x_correction_price
3808 p_total_quantity_billed)*x_correction_price)
3809 /g_min_acct_unit) * g_min_acct_unit
3810 ) amount,
3811 round((x_correction_quantity * pd.quantity_billed)/
3812 p_total_quantity_billed , 15) corrected_quantity,
3813 aid.dist_code_combination_id,
3814 ap_invoice_distributions_s.nextval
3815 FROM ap_invoice_distributions aid,
3816 ap_invoice_lines ail,
3817 po_distributions pd
3818 WHERE ail.invoice_id = x_corrected_invoice_id
3819 AND ail.line_number = x_corrected_line_number
3820 AND aid.invoice_id = ail.invoice_id
3821 AND aid.invoice_line_number = ail.line_number
3822 AND pd.po_distribution_id = aid.po_distribution_id
3823 -- Bug 5585744, Modified the condition below
3824 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
3825 AND aid.prepay_distribution_id IS NULL;
3826 /*AND aid.line_type_lookup_code NOT IN ('PREPAY','AWT','RETAINAGE')
3827 AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3828 OR aid.prepay_distribution_id IS NULL); */
3829
3830 l_corrected_inv_dist_id ap_invoice_distributions.corrected_invoice_dist_id%TYPE;
3831 l_po_dist_id po_distributions.po_distribution_id%TYPE;
3832 l_amount ap_invoice_distributions.amount%TYPE;
3833 l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3834 l_base_amount ap_invoice_distributions.base_amount%TYPE;
3835 l_total_quantity_billed po_distributions.quantity_billed%TYPE;
3836 l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
3837 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3838 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
3839 l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
3840 l_rounding_index ap_invoice_distributions.invoice_distribution_id%TYPE;
3841 l_sum_dist_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
3842 l_price_variance ap_invoice_distributions.amount%TYPE := 0;
3843 l_debug_info varchar2(2000);
3844 current_calling_sequence varchar2(2000);
3845 l_api_name varchar2(4000);
3846
3847 BEGIN
3848
3849 current_calling_sequence := 'Get_Corr_Dist_Proration_Info<-'||x_calling_sequence;
3850 l_api_name := 'Get_Corr_Dist_Proration_info';
3851
3852 /* Bug 5585744, Changed the index for x_corr_dist_tab from l_po_dist_id to
3853 l_invoice_distrbution_id */
3854
3855 IF (x_match_mode IN ('STD-PS','CR-PS'))THEN
3856
3857 IF (x_correction_type = 'PRICE_CORRECTION') THEN
3858
3859 --Commented for bug#11886930
3860 /*
3861 BEGIN
3862 SELECT nvl(sum(amount),0) -- Bug 5629985. Added the NVL
3863 INTO l_price_variance
3864 FROM ap_invoice_distributions_all
3865 WHERE invoice_id = x_corrected_invoice_id
3866 AND invoice_line_number = x_corrected_line_number
3867 AND line_type_lookup_code = 'IPV';
3868 EXCEPTION
3869 WHEN OTHERS THEN NULL;
3870 END;
3871
3872 OPEN price_correction_cursor (l_price_variance); */
3873 OPEN price_correction_cursor;
3874
3875 LOOP
3876
3877 FETCH price_correction_cursor INTO l_corrected_inv_dist_id,
3878 l_po_dist_id,
3879 l_amount,
3880 l_corrected_quantity,
3881 l_dist_ccid,
3882 l_invoice_distribution_id;
3883
3884 EXIT WHEN price_correction_cursor%NOTFOUND;
3885
3886 x_corr_dist_tab(l_invoice_distribution_id).po_distribution_id := l_po_dist_id;
3887 x_corr_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
3888 x_corr_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
3889 x_corr_dist_tab(l_invoice_distribution_id).amount := l_amount;
3890 x_corr_dist_tab(l_invoice_distribution_id).corrected_quantity := l_corrected_quantity;
3891 x_corr_dist_tab(l_invoice_distribution_id).pa_quantity := l_corrected_quantity;
3892 x_corr_dist_tab(l_invoice_distribution_id).unit_price := x_correction_price;
3893 x_corr_dist_tab(l_invoice_distribution_id).dist_ccid := l_dist_ccid;
3894
3895 --Calculate the index of the max of the largest distribution for
3896 --proration/base amount rounding.
3897 --Added abs() for bug#14315066
3898 IF (abs(l_amount) >= abs(l_max_dist_amount)) THEN
3899 l_rounding_index := l_invoice_distribution_id;
3900 l_max_dist_amount := l_max_dist_amount;
3901 END IF;
3902
3903 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
3904
3905 END LOOP;
3906
3907 CLOSE price_correction_cursor;
3908
3909 ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3910
3911 SELECT sum(pd.quantity_billed)
3912 INTO l_total_quantity_billed
3913 FROM po_distributions pd,
3914 ap_invoice_distributions aid
3915 WHERE pd.po_distribution_id = aid.po_distribution_id
3916 AND aid.invoice_id = x_corrected_invoice_id
3917 AND aid.invoice_line_number = x_corrected_line_number
3918 -- Bug 5585744, Modified the condition below
3919 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
3920 AND aid.prepay_distribution_id IS NULL;
3921 /*AND aid.line_type_lookup_code NOT IN ('PREPAY','AWT','RETAINAGE')
3922 AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3923 OR aid.prepay_distribution_id IS NULL); */
3924
3925 OPEN quantity_correction_cursor(l_total_quantity_billed);
3926
3927 LOOP
3928
3929
3930 FETCH quantity_correction_cursor INTO l_corrected_inv_dist_id,
3931 l_po_dist_id,
3932 l_amount,
3933 l_corrected_quantity,
3934 l_dist_ccid,
3935 l_invoice_distribution_id;
3936
3937 EXIT WHEN quantity_correction_cursor%NOTFOUND;
3938
3939 x_corr_dist_tab(l_invoice_distribution_id).po_distribution_id := l_po_dist_id;
3940 x_corr_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
3941 x_corr_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
3942 x_corr_dist_tab(l_invoice_distribution_id).amount := l_amount;
3943 x_corr_dist_tab(l_invoice_distribution_id).corrected_quantity := l_corrected_quantity;
3944 x_corr_dist_tab(l_invoice_distribution_id).pa_quantity := l_corrected_quantity;
3945 x_corr_dist_tab(l_invoice_distribution_id).unit_price := x_correction_price;
3946 x_corr_dist_tab(l_invoice_distribution_id).dist_ccid := l_dist_ccid;
3947
3948 --Calculate the index of the max of the largest distribution for
3949 --proration/base amount rounding.
3950 --Added abs() for bug#14315066
3951 IF (abs(l_amount) >= abs(l_max_dist_amount)) THEN
3952 l_rounding_index := l_invoice_distribution_id;
3953 l_max_dist_amount := l_max_dist_amount;
3954 END IF;
3955
3956 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
3957
3958 END LOOP;
3959
3960 CLOSE quantity_correction_cursor;
3961
3962 --Perform Proration Rounding before the base amounts are populated
3963
3964 IF (l_sum_prorated_amount <> x_correction_amount and l_rounding_index is not null) THEN
3965 x_corr_dist_tab(l_rounding_index).amount := x_corr_dist_tab(l_rounding_index).amount +
3966 (x_correction_amount - l_sum_prorated_amount);
3967 END IF;
3968
3969 END IF ; /* x_match_type = 'PRICE_CORRECTION' */
3970
3971
3972 --For the case when user distributes the correction, we still
3973 --need to populate the PL/SQL table base_amount, invoice_distribution_id...
3974 ELSE /* match_mode in 'STD-PD','CR-PD' ...*/
3975
3976 FOR i IN nvl(x_corr_dist_tab.first,0) ..nvl(x_corr_dist_tab.last,0) LOOP
3977
3978 IF (x_corr_dist_tab.exists(i)) THEN
3979
3980 SELECT ap_invoice_distributions_s.nextval
3981 INTO x_corr_dist_tab(i).invoice_distribution_id
3982 FROM dual ;
3983
3984 x_corr_dist_tab(i).pa_quantity := x_corr_dist_tab(i).corrected_quantity;
3985
3986 --Calculate the index of the max of the largest distribution for
3987 --base amount rounding. For this case there will be no proration
3988 --rounding as the user distributes the correction quantity.
3989
3990 --Also we will need this index only for foreign currency invoices only.
3991
3992 IF (g_exchange_rate IS NOT NULL) THEN
3993 --Added abs() for bug#14315066
3994 IF (abs(x_corr_dist_tab(i).amount) > abs(l_max_dist_amount)) THEN
3995 l_rounding_index := i;
3996 l_max_dist_amount := x_corr_dist_tab(i).amount;
3997 END IF;
3998 END IF;
3999
4000 END IF; /*(x_corr_dist_tab.exists(i))*/
4001
4002 END LOOP;
4003
4004 END IF; /* x_match_mode IN ('STD-PS','CR-PS')...*/
4005
4006 FOR i in nvl(x_corr_dist_tab.first,0) .. nvl(x_corr_dist_tab.last,0) LOOP
4007
4008 --Populating the base_amount column, after proration related rounding
4009 --has been done if it is a foreign currency invoice.
4010
4011 IF (g_exchange_rate IS NOT NULL) THEN
4012 x_corr_dist_tab(i).base_amount := ap_utilities_pkg.ap_round_currency(
4013 x_corr_dist_tab(i).amount * g_exchange_rate,
4014 g_base_currency_code);
4015
4016 l_sum_dist_base_amount := l_sum_dist_base_amount + x_corr_dist_tab(i).base_amount ;
4017 END IF;
4018
4019 END LOOP;
4020
4021 --Perform Base Amount rounding for foreign currency invoices only
4022 IF (g_exchange_rate IS NOT NULL AND g_line_base_amount <> l_sum_dist_base_amount
4023 and l_rounding_index is not null) THEN
4024
4025 x_corr_dist_tab(l_rounding_index).base_amount := x_corr_dist_tab(l_rounding_index).base_amount +
4026 (g_line_base_amount - l_sum_dist_base_amount);
4027
4028 x_corr_dist_tab(l_rounding_index).rounding_amt := g_line_base_amount - l_sum_dist_base_amount;
4029
4030 END IF;
4031
4032 EXCEPTION
4033 WHEN others then
4034 If (SQLCODE <> -20001) Then
4035 fnd_message.set_name('SQLAP','AP_DEBUG');
4036 fnd_message.set_token('ERROR',SQLERRM);
4037 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4038 fnd_message.set_token('PARAMETERS',
4039 ' corrected_invoice_id = '||to_char(x_corrected_invoice_id)
4040 ||', corrected_line_number = '||to_char(x_corrected_line_number)
4041 ||', correction quantity ='||to_char(x_correction_quantity)
4042 ||', correction amount = '||to_char(x_correction_amount)
4043 ||', correction type = '||x_correction_type);
4044 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4045 End if;
4046
4047 --Clean up the PL/SQL table
4048 x_corr_dist_tab.delete;
4049
4050 app_exception.raise_exception;
4051
4052 END Get_Corr_Dist_Proration_Info;
4053
4054
4055
4056 PROCEDURE Update_Corr_Po_Shipments_Dists(
4057 X_Corr_Dist_Tab IN AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
4058 X_Po_Line_Location_Id IN NUMBER,
4059 X_Quantity IN NUMBER,
4060 X_Amount IN NUMBER,
4061 X_Correction_Type IN VARCHAR2,
4062 X_Uom_Lookup_Code IN VARCHAR2,
4063 X_Calling_Sequence IN VARCHAR2 ) IS
4064
4065 i NUMBER;
4066 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
4067 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
4068 l_debug_info VARCHAR2(2000);
4069 current_calling_sequence VARCHAR2(2000);
4070 l_return_status VARCHAR2(100);
4071 l_msg_data VARCHAR2(4000);
4072 l_api_name VARCHAR(4000);
4073
4074 BEGIN
4075
4076 l_api_name := 'Update_Corr_Po_Shipments_Dists';
4077
4078 current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
4079
4080 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4081 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
4082 END IF;
4083
4084 l_debug_info := 'Create l_po_ap_dist_rec object';
4085 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4086 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4087 END IF;
4088
4089 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
4090
4091 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
4092 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4093 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4094 END IF;
4095
4096 IF (x_correction_type = 'QTY_CORRECTION') THEN
4097
4098 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
4099 p_po_line_location_id => x_po_line_location_id,
4100 p_uom_code => x_uom_lookup_code,
4101 p_quantity_billed => x_quantity,
4102 p_amount_billed => X_Amount, --Modified for bug#9718642 --NULL,
4103 p_quantity_financed => NULL,
4104 p_amount_financed => NULL,
4105 p_quantity_recouped => NULL,
4106 p_amount_recouped => NULL,
4107 p_retainage_withheld_amt => NULL,
4108 p_retainage_released_amt => NULL
4109 );
4110
4111 ELSIF (x_correction_type = 'PRICE_CORRECTION') THEN
4112
4113 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
4114 p_po_line_location_id => x_po_line_location_id,
4115 p_uom_code => x_uom_lookup_code,
4116 p_quantity_billed => NULL,
4117 p_amount_billed => X_Amount, --Modified for bug#9718642 --NULL,
4118 p_quantity_financed => NULL,
4119 p_amount_financed => NULL,
4120 p_quantity_recouped => NULL,
4121 p_amount_recouped => NULL,
4122 p_retainage_withheld_amt => NULL,
4123 p_retainage_released_amt => NULL
4124 );
4125
4126 END IF;
4127
4128
4129 l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
4130 ||' with quantity/amount billed information.';
4131
4132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4133 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4134 END IF;
4135
4136 FOR i in nvl(x_corr_dist_tab.first,0)..nvl(x_corr_dist_tab.last,0) LOOP
4137
4138 IF (x_corr_dist_tab.exists(i)) THEN
4139
4140 IF (x_correction_type = 'PRICE_CORRECTION') THEN
4141
4142 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_corr_dist_tab(i).po_distribution_id,
4143 p_uom_code => x_uom_lookup_code,
4144 p_quantity_billed => NULL,
4145 p_amount_billed => x_corr_dist_tab(i).amount,
4146 p_quantity_financed => NULL,
4147 p_amount_financed => NULL,
4148 p_quantity_recouped => NULL,
4149 p_amount_recouped => NULL,
4150 p_retainage_withheld_amt => NULL,
4151 p_retainage_released_amt => NULL);
4152
4153 ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
4154
4155 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_corr_dist_tab(i).po_distribution_id,
4156 p_uom_code => x_uom_lookup_code,
4157 p_quantity_billed => x_corr_dist_tab(i).corrected_quantity,
4158 p_amount_billed => x_corr_dist_tab(i).amount,
4159 p_quantity_financed => NULL,
4160 p_amount_financed => NULL,
4161 p_quantity_recouped => NULL,
4162 p_amount_recouped => NULL,
4163 p_retainage_withheld_amt => NULL,
4164 p_retainage_released_amt => NULL);
4165
4166 END IF;
4167
4168 END IF;
4169
4170 END LOOP;
4171
4172 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
4173 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4174 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4175 END IF;
4176
4177 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
4178 P_Api_Version => 1.0,
4179 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
4180 P_Dist_Changes_Rec => l_po_ap_dist_rec,
4181 X_Return_Status => l_return_status,
4182 X_Msg_Data => l_msg_data);
4183
4184
4185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4186 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Shipments_Dists(-)');
4187 END IF;
4188
4189
4190 EXCEPTION
4191 WHEN others then
4192 If (SQLCODE <> -20001) Then
4193 fnd_message.set_name('SQLAP','AP_DEBUG');
4194 fnd_message.set_token('ERROR',SQLERRM);
4195 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4196 fnd_message.set_token('PARAMETERS',
4197 ' po_distribution_id = '||to_char(x_corr_dist_tab(i).po_distribution_id)
4198 ||', correction_type = '||x_correction_type);
4199 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4200 End if;
4201 app_exception.raise_exception;
4202
4203 END Update_Corr_Po_Shipments_Dists;
4204
4205
4206
4207 PROCEDURE Insert_Corr_Invoice_Line(x_invoice_id IN NUMBER,
4208 x_invoice_line_number IN NUMBER,
4209 x_corrected_invoice_id IN NUMBER,
4210 x_corrected_line_number IN NUMBER,
4211 x_quantity IN NUMBER,
4212 x_amount IN NUMBER,
4213 x_unit_price IN NUMBER,
4214 x_correction_type IN VARCHAR2,
4215 x_po_distribution_id IN NUMBER,
4216 x_retained_amount IN NUMBER DEFAULT NULL,
4217 x_calling_sequence IN VARCHAR2 ) IS
4218
4219 l_debug_info VARCHAR2(2000);
4220 current_calling_sequence VARCHAR2(2000);
4221
4222 BEGIN
4223
4224 current_calling_sequence := 'Insert_Corr_Invoice_Line<-'||x_calling_sequence;
4225
4226 INSERT INTO AP_INVOICE_LINES(INVOICE_ID,
4227 LINE_NUMBER,
4228 LINE_TYPE_LOOKUP_CODE,
4229 REQUESTER_ID,
4230 DESCRIPTION,
4231 LINE_SOURCE,
4232 ORG_ID,
4233 INVENTORY_ITEM_ID,
4234 ITEM_DESCRIPTION,
4235 SERIAL_NUMBER,
4236 MANUFACTURER,
4237 MODEL_NUMBER,
4238 GENERATE_DISTS,
4239 MATCH_TYPE,
4240 DISTRIBUTION_SET_ID,
4241 ACCOUNT_SEGMENT,
4242 BALANCING_SEGMENT,
4243 COST_CENTER_SEGMENT,
4244 OVERLAY_DIST_CODE_CONCAT,
4245 DEFAULT_DIST_CCID,
4246 PRORATE_ACROSS_ALL_ITEMS,
4247 LINE_GROUP_NUMBER,
4248 ACCOUNTING_DATE,
4249 PERIOD_NAME,
4250 DEFERRED_ACCTG_FLAG,
4251 DEF_ACCTG_START_DATE,
4252 DEF_ACCTG_END_DATE,
4253 DEF_ACCTG_NUMBER_OF_PERIODS,
4254 DEF_ACCTG_PERIOD_TYPE,
4255 SET_OF_BOOKS_ID,
4256 AMOUNT,
4257 BASE_AMOUNT,
4258 ROUNDING_AMT,
4259 QUANTITY_INVOICED,
4260 UNIT_MEAS_LOOKUP_CODE,
4261 UNIT_PRICE,
4262 WFAPPROVAL_STATUS,
4263 -- USSGL_TRANSACTION_CODE, - Bug 4277744
4264 DISCARDED_FLAG,
4265 ORIGINAL_AMOUNT,
4266 ORIGINAL_BASE_AMOUNT,
4267 ORIGINAL_ROUNDING_AMT,
4268 CANCELLED_FLAG,
4269 INCOME_TAX_REGION,
4270 TYPE_1099,
4271 STAT_AMOUNT,
4272 PREPAY_INVOICE_ID,
4273 PREPAY_LINE_NUMBER,
4274 INVOICE_INCLUDES_PREPAY_FLAG,
4275 CORRECTED_INV_ID,
4276 CORRECTED_LINE_NUMBER,
4277 PO_HEADER_ID,
4278 PO_LINE_ID,
4279 PO_RELEASE_ID,
4280 PO_LINE_LOCATION_ID,
4281 PO_DISTRIBUTION_ID,
4282 RCV_TRANSACTION_ID,
4283 FINAL_MATCH_FLAG,
4284 ASSETS_TRACKING_FLAG,
4285 ASSET_BOOK_TYPE_CODE,
4286 ASSET_CATEGORY_ID,
4287 PROJECT_ID,
4288 TASK_ID,
4289 EXPENDITURE_TYPE,
4290 EXPENDITURE_ITEM_DATE,
4291 EXPENDITURE_ORGANIZATION_ID,
4292 PA_QUANTITY,
4293 PA_CC_AR_INVOICE_ID,
4294 PA_CC_AR_INVOICE_LINE_NUM,
4295 PA_CC_PROCESSED_CODE,
4296 AWARD_ID,
4297 AWT_GROUP_ID,
4298 REFERENCE_1,
4299 REFERENCE_2,
4300 RECEIPT_VERIFIED_FLAG,
4301 RECEIPT_REQUIRED_FLAG,
4302 RECEIPT_MISSING_FLAG,
4303 JUSTIFICATION,
4304 EXPENSE_GROUP,
4305 START_EXPENSE_DATE,
4306 END_EXPENSE_DATE,
4307 RECEIPT_CURRENCY_CODE,
4308 RECEIPT_CONVERSION_RATE,
4309 RECEIPT_CURRENCY_AMOUNT,
4310 DAILY_AMOUNT,
4311 WEB_PARAMETER_ID,
4312 ADJUSTMENT_REASON,
4313 MERCHANT_DOCUMENT_NUMBER,
4314 MERCHANT_NAME,
4315 MERCHANT_REFERENCE,
4316 MERCHANT_TAX_REG_NUMBER,
4317 MERCHANT_TAXPAYER_ID,
4318 COUNTRY_OF_SUPPLY,
4319 CREDIT_CARD_TRX_ID,
4320 COMPANY_PREPAID_INVOICE_ID,
4321 CC_REVERSAL_FLAG,
4322 ATTRIBUTE_CATEGORY,
4323 ATTRIBUTE1,
4324 ATTRIBUTE2,
4325 ATTRIBUTE3,
4326 ATTRIBUTE4,
4327 ATTRIBUTE5,
4328 ATTRIBUTE6,
4329 ATTRIBUTE7,
4330 ATTRIBUTE8,
4331 ATTRIBUTE9,
4332 ATTRIBUTE10,
4333 ATTRIBUTE11,
4334 ATTRIBUTE12,
4335 ATTRIBUTE13,
4336 ATTRIBUTE14,
4337 ATTRIBUTE15,
4338 /* OPEN ISSUE 1*/
4339 /* GLOBAL_ATTRIBUTE_CATEGORY,
4340 GLOBAL_ATTRIBUTE1,
4341 GLOBAL_ATTRIBUTE2,
4342 GLOBAL_ATTRIBUTE3,
4343 GLOBAL_ATTRIBUTE4,
4344 GLOBAL_ATTRIBUTE5,
4345 GLOBAL_ATTRIBUTE6,
4346 GLOBAL_ATTRIBUTE7,
4347 GLOBAL_ATTRIBUTE8,
4348 GLOBAL_ATTRIBUTE9,
4349 GLOBAL_ATTRIBUTE10,
4350 GLOBAL_ATTRIBUTE11,
4351 GLOBAL_ATTRIBUTE12,
4352 GLOBAL_ATTRIBUTE13,
4353 GLOBAL_ATTRIBUTE14,
4354 GLOBAL_ATTRIBUTE15,
4355 GLOBAL_ATTRIBUTE16,
4356 GLOBAL_ATTRIBUTE17,
4357 GLOBAL_ATTRIBUTE18,
4358 GLOBAL_ATTRIBUTE19,
4359 GLOBAL_ATTRIBUTE20, */
4360 CREATION_DATE,
4361 CREATED_BY,
4362 LAST_UPDATED_BY,
4363 LAST_UPDATE_DATE,
4364 LAST_UPDATE_LOGIN,
4365 PROGRAM_APPLICATION_ID,
4366 PROGRAM_ID,
4367 PROGRAM_UPDATE_DATE,
4368 REQUEST_ID,
4369 RETAINED_AMOUNT,
4370 RETAINED_AMOUNT_REMAINING,
4371 --ETAX: Invwkb
4372 SHIP_TO_LOCATION_ID,
4373 PRIMARY_INTENDED_USE,
4374 PRODUCT_FISC_CLASSIFICATION,
4375 TRX_BUSINESS_CATEGORY,
4376 PRODUCT_TYPE,
4377 PRODUCT_CATEGORY,
4378 USER_DEFINED_FISC_CLASS,
4379 PAY_AWT_GROUP_ID, --bug 9689194
4380 RCV_SHIPMENT_LINE_ID) /*BUG :15946088*/
4381 SELECT x_invoice_id, --invoice_id
4382 x_invoice_line_number, --line_number
4383 'ITEM', --line_type_lookup_code
4384 ail.requester_id, --requester_id
4385 ail.description, --description
4386 'HEADER CORRECTION', --line_source
4387 ail.org_id, --org_id
4388 ail.inventory_item_id, --inventory_item_id
4389 ail.item_description, --item_description
4390 ail.serial_number, --serial_number
4391 ail.manufacturer, --manufacturer
4392 ail.model_number, --model_number
4393 'D', --generate_dists
4394 x_correction_type, --match_type
4395 NULL, --distribution_set_id
4396 ail.account_segment, --account_segment
4397 ail.balancing_segment, --balancing_segment
4398 ail.cost_center_segment, --cost_center_segment
4399 ail.overlay_dist_code_concat, --overlay_dist_code_concat
4400 ail.default_dist_ccid, --default_dist_ccid
4401 'N', --prorate_across_all_items
4402 NULL, --line_group_number
4403 g_accounting_date, --accounting_date
4404 g_period_name, --period_name
4405 'N', --deferred_acctg_flag
4406 NULL, --def_acctg_start_date
4407 NULL, --def_acctg_end_date
4408 NULL, --def_acctg_number_of_periods
4409 NULL, --def_acctg_period_type
4410 g_set_of_books_id, --set_of_books_id
4411 x_amount, --amount
4412 AP_UTILITIES_PKG.Ap_Round_Currency(
4413 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
4414 G_BASE_CURRENCY_CODE), --base_amount
4415 NULL, --rounding_amount
4416 x_quantity, --quantity_invoiced
4417 ail.unit_meas_lookup_code, --unit_meas_lookup_code
4418 x_unit_price, --unit_price
4419 decode(g_approval_workflow_flag,'Y'
4420 ,'REQUIRED','NOT REQUIRED'),--wf_approval_status
4421 -- Removed for bug 4277744
4422 -- g_ussgl_transaction_code, --ussgl_transaction_code
4423 'N', --discarded_flag
4424 NULL, --original_amount
4425 NULL, --original_base_amount
4426 NULL, --original_rounding_amt
4427 'N', --cancelled_flag
4428 g_income_tax_region, --income_tax_region
4429 pll.type_1099, --type_1099
4430 NULL, --stat_amount
4431 NULL, --prepay_invoice_id
4432 NULL, --prepay_line_number
4433 NULL, --invoice_includes_prepay_flag
4434 x_corrected_invoice_id, --corrected_invoice_id
4435 x_corrected_line_number, --corrected_line_number
4436 ail.po_header_id, --po_header_id
4437 ail.po_line_id, --po_line_id
4438 ail.po_release_id, --release_id
4439 ail.po_line_location_id, --po_line_location_id
4440 nvl(ail.po_distribution_id,
4441 x_po_distribution_id), --po_distribution_id
4442 ail.rcv_transaction_id, --rcv_transaction_id
4443 NULL, --final_match_flag
4444 ail.assets_tracking_flag, --assets_tracking_flag
4445 ail.asset_book_type_code, --asset_book_type_code
4446 ail.asset_category_id, --asset_category_id
4447 ail.project_id, --project_id
4448 ail.task_id, --task_id
4449 ail.expenditure_type, --expenditure_type
4450 ail.expenditure_item_date, --expenditure_item_date
4451 ail.expenditure_organization_id, --expenditure_organization_id
4452 decode(ail.project_id,'','',
4453 decode(x_quantity,'',(ail.pa_quantity*x_amount/ail.amount),
4454 x_quantity)), --pa_quantity
4455 NULL, --pa_cc_ar_invoice_id
4456 NULL, --pa_cc_ar_invoice_line_num
4457 NULL, --pa_cc_processed_code
4458 ail.award_id, --award_id
4459 g_awt_group_id, --awt_group_id
4460 ail.reference_1, --reference_1
4461 ail.reference_2, --reference_2
4462 ail.receipt_verified_flag, --receipt_verified_flag
4463 ail.receipt_required_flag, --receipt_required_flag
4464 ail.receipt_missing_flag, --receipt_missing_flag
4465 ail.justification, --ail.justification
4466 ail.expense_group, --ail.expense_group
4467 ail.start_expense_date, --start_expense_date
4468 ail.end_expense_date, --end_expense_date
4469 ail.receipt_currency_code, --receipt_currency_code
4470 ail.receipt_conversion_rate, --receipt_conversion_rate
4471 ail.receipt_currency_amount, --receipt_currency_amount
4472 ail.daily_amount, --daily_amount
4473 ail.web_parameter_id, --web_parameter_id
4474 ail.adjustment_reason, --adjustment_reason
4475 ail.merchant_document_number, --merchant_document_number
4476 ail.merchant_name, --merchant_name
4477 ail.merchant_reference, --merchant_reference
4478 ail.merchant_tax_reg_number, --merchant_tax_reg_number
4479 ail.merchant_taxpayer_id, --merchant_taxpayer_id
4480 ail.country_of_supply, --country_of_supply
4481 ail.credit_card_trx_id, --credit_card_trx_id
4482 ail.company_prepaid_invoice_id, --cpmany_prepaid_invoice_id
4483 ail.cc_reversal_flag, --cc_reversal_flag
4484 ail.attribute_category, --attribute_category
4485 ail.attribute1, --attribute1
4486 ail.attribute2, --attribute2
4487 ail.attribute3, --attribute3
4488 ail.attribute4, --attribute4
4489 ail.attribute5, --attribute5
4490 ail.attribute6, --attribute6
4491 ail.attribute7, --attribute7
4492 ail.attribute8, --attribute8
4493 ail.attribute9, --attribute9
4494 ail.attribute10, --attribute10
4495 ail.attribute11, --attribute11
4496 ail.attribute12, --attribute12
4497 ail.attribute13, --attribute13
4498 ail.attribute14, --attribute14
4499 ail.attribute15, --attribute15
4500 /*OPEN ISSUE 1*/
4501 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
4502 X_GLOBAL_ATTRIBUTE1,
4503 X_GLOBAL_ATTRIBUTE2,
4504 X_GLOBAL_ATTRIBUTE3,
4505 X_GLOBAL_ATTRIBUTE4,
4506 X_GLOBAL_ATTRIBUTE5,
4507 X_GLOBAL_ATTRIBUTE6,
4508 X_GLOBAL_ATTRIBUTE7,
4509 X_GLOBAL_ATTRIBUTE8,
4510 X_GLOBAL_ATTRIBUTE9,
4511 X_GLOBAL_ATTRIBUTE10,
4512 X_GLOBAL_ATTRIBUTE11,
4513 X_GLOBAL_ATTRIBUTE12,
4514 X_GLOBAL_ATTRIBUTE13,
4515 X_GLOBAL_ATTRIBUTE14,
4516 X_GLOBAL_ATTRIBUTE15,
4517 X_GLOBAL_ATTRIBUTE16,
4518 X_GLOBAL_ATTRIBUTE17,
4519 X_GLOBAL_ATTRIBUTE18,
4520 X_GLOBAL_ATTRIBUTE19,
4521 X_GLOBAL_ATTRIBUTE20, */
4522 sysdate, --creation_date
4523 g_user_id, --created_by
4524 g_user_id, --last_updated_by
4525 sysdate, --last_update_date
4526 g_login_id, --user_login_id
4527 NULL, --program_application_id
4528 NULL, --program_id
4529 NULL, --program_update_date
4530 NULL, --request_id
4531 X_RETAINED_AMOUNT, --retained_amount
4532 (-X_RETAINED_AMOUNT), --retained_amount_remaining
4533 --ETAX: Invwkb
4534 PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
4535 AIL.PRIMARY_INTENDED_USE, --primary_intended_use
4536 AIL.PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
4537 AIL.TRX_BUSINESS_CATEGORY, --trx_business_category --bug10085965
4538 AIL.PRODUCT_TYPE, --product_type
4539 AIL.PRODUCT_CATEGORY, --product_category
4540 AIL.USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
4541 G_PAY_AWT_GROUP_ID, --bug 9689194
4542 AIL.RCV_SHIPMENT_LINE_ID /*BUG :15946088*/
4543 FROM ap_invoices ai,
4544 ap_invoice_lines ail,
4545 po_line_locations_ap_v pll
4546 WHERE ai.invoice_id = x_corrected_invoice_id
4547 AND ail.invoice_id = ai.invoice_id
4548 AND ail.line_number = x_corrected_line_number
4549 AND pll.line_location_id = ail.po_line_location_id;
4550
4551 g_max_invoice_line_number := g_max_invoice_line_number + 1;
4552
4553 EXCEPTION
4554 WHEN others then
4555 If (SQLCODE <> -20001) Then
4556 fnd_message.set_name('SQLAP','AP_DEBUG');
4557 fnd_message.set_token('ERROR',SQLERRM);
4558 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4559 fnd_message.set_token('PARAMETERS',
4560 ' invoice_id = '||to_char(x_invoice_id)
4561 ||', invoice_line_number = ' ||to_char(x_invoice_line_number)
4562 ||', corrected_invoice_id = '||to_char(x_corrected_invoice_id)
4563 ||', corrected_line_number = '||to_char(x_corrected_line_number)
4564 ||', quantity = '||to_char(x_quantity)
4565 ||', amount = '||to_char(x_amount)
4566 ||', unit_price = '||to_char(x_unit_price)
4567 ||', correction_type = '||x_correction_type
4568 ||', po_distribution_id = '||to_char(x_po_distribution_id));
4569 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4570 End if;
4571 app_exception.raise_exception;
4572
4573 END Insert_Corr_Invoice_Line;
4574
4575
4576
4577 PROCEDURE Insert_Corr_Invoice_Dists(x_invoice_id IN NUMBER,
4578 x_invoice_line_number IN NUMBER,
4579 x_corrected_invoice_id IN NUMBER,
4580 x_corr_dist_tab IN OUT NOCOPY
4581 AP_MATCHING_PKG.CORR_DIST_TAB_TYPE,
4582 x_correction_type IN VARCHAR2,
4583 x_total_amount IN NUMBER,
4584 x_calling_sequence IN VARCHAR2) IS
4585
4586 l_distribution_line_number ap_invoice_distributions.distribution_line_number%type := 1;
4587 l_rounded_dist_id ap_invoice_distributions.invoice_distribution_id%type := NULL;
4588 l_sum_amount_prorated ap_invoice_distributions.amount%TYPE := 0;
4589 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
4590 l_po_distribution_id po_distributions.po_distribution_id%TYPE;
4591 l_debug_info VARCHAR2(2000);
4592 current_calling_sequence VARCHAR2(2000);
4593 l_api_name VARCHAR2(50);
4594
4595 BEGIN
4596
4597 current_calling_sequence := 'Insert_Corr_Invoice_Dists<-'||x_calling_sequence;
4598 l_api_name := 'Insert_Corr_Invoice_Dists';
4599 FOR I in NVL(x_corr_dist_tab.FIRST,0) .. NVL(x_corr_dist_tab.LAST,0) LOOP
4600
4601 IF (x_corr_dist_tab.exists(i)) THEN
4602
4603 INSERT INTO AP_INVOICE_DISTRIBUTIONS(
4604 batch_id,
4605 invoice_id,
4606 invoice_line_number,
4607 invoice_distribution_id,
4608 distribution_line_number,
4609 line_type_lookup_code,
4610 description,
4611 dist_match_type,
4612 distribution_class,
4613 org_id,
4614 dist_code_combination_id,
4615 accounting_date,
4616 period_name,
4617 amount_to_post,
4618 base_amount_to_post,
4619 posted_amount,
4620 posted_base_amount,
4621 je_batch_id,
4622 cash_je_batch_id,
4623 posted_flag,
4624 accounting_event_id,
4625 upgrade_posted_amt,
4626 upgrade_base_posted_amt,
4627 set_of_books_id,
4628 amount,
4629 base_amount,
4630 rounding_amt,
4631 match_status_flag,
4632 encumbered_flag,
4633 packet_id,
4634 -- ussgl_transaction_code, - Bug 4277744
4635 -- ussgl_trx_code_context, - Bug 4277744
4636 reversal_flag,
4637 parent_reversal_id,
4638 cancellation_flag,
4639 income_tax_region,
4640 type_1099,
4641 stat_amount,
4642 charge_applicable_to_dist_id,
4643 prepay_amount_remaining,
4644 prepay_distribution_id,
4645 parent_invoice_id,
4646 corrected_invoice_dist_id,
4647 corrected_quantity,
4648 other_invoice_id,
4649 po_distribution_id,
4650 rcv_transaction_id,
4651 unit_price,
4652 matched_uom_lookup_code,
4653 quantity_invoiced,
4654 final_match_flag,
4655 related_id,
4656 assets_addition_flag,
4657 assets_tracking_flag,
4658 asset_book_type_code,
4659 asset_category_id,
4660 project_id,
4661 task_id,
4662 expenditure_type,
4663 expenditure_item_date,
4664 expenditure_organization_id,
4665 pa_quantity,
4666 pa_addition_flag,
4667 pa_cc_ar_invoice_id,
4668 pa_cc_ar_invoice_line_num,
4669 pa_cc_processed_code,
4670 award_id,
4671 gms_burdenable_raw_cost,
4672 awt_flag,
4673 awt_group_id,
4674 awt_tax_rate_id,
4675 awt_gross_amount,
4676 awt_invoice_id,
4677 awt_origin_group_id,
4678 awt_invoice_payment_id,
4679 awt_withheld_amt,
4680 inventory_transfer_status,
4681 reference_1,
4682 reference_2,
4683 receipt_verified_flag,
4684 receipt_required_flag,
4685 receipt_missing_flag,
4686 justification,
4687 expense_group,
4688 start_expense_date,
4689 end_expense_date,
4690 receipt_currency_code,
4691 receipt_conversion_rate,
4692 receipt_currency_amount,
4693 daily_amount,
4694 web_parameter_id,
4695 adjustment_reason,
4696 merchant_document_number,
4697 merchant_name,
4698 merchant_reference,
4699 merchant_tax_reg_number,
4700 merchant_taxpayer_id,
4701 country_of_supply,
4702 credit_card_trx_id,
4703 company_prepaid_invoice_id,
4704 cc_reversal_flag,
4705 attribute_category,
4706 attribute1,
4707 attribute2,
4708 attribute3,
4709 attribute4,
4710 attribute5,
4711 attribute6,
4712 attribute7,
4713 attribute8,
4714 attribute9,
4715 attribute10,
4716 attribute11,
4717 attribute12,
4718 attribute13,
4719 attribute14,
4720 attribute15,
4721 /*OPEN ISSUE 1*/
4722 /*global_attribute_category,
4723 global_attribute1,
4724 global_attribute2,*/
4725 --Bugfix:4674194
4726 global_attribute3,
4727 /*global_attribute4,
4728 global_attribute5,
4729 global_attribute6,
4730 global_attribute7,
4731 global_attribute8,
4732 global_attribute9,
4733 global_attribute10,
4734 global_attribute11,
4735 global_attribute12,
4736 global_attribute13,
4737 global_attribute14,
4738 global_attribute15,
4739 global_attribute16,
4740 global_attribute17,
4741 global_attribute18,
4742 global_attribute19,
4743 global_attribute20,*/
4744 created_by,
4745 creation_date,
4746 last_updated_by,
4747 last_update_date,
4748 last_update_login,
4749 program_application_id,
4750 program_id,
4751 program_update_date,
4752 request_id,
4753 --ETAX: Invwkb
4754 intended_use,
4755 accrual_posted_flag,
4756 cash_posted_flag,
4757 --Freight and Special Charges
4758 rcv_charge_addition_flag,
4759 pay_awt_group_id) --bug 9689194
4760 SELECT g_batch_id, --batch_id
4761 x_invoice_id, --invoice_id
4762 x_invoice_line_number, --invoice_line_number
4763 x_corr_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
4764 l_distribution_line_number, --distribution_line_number
4765 decode(x_correction_type,
4766 'PRICE_CORRECTION','IPV',
4767 'QTY_CORRECTION',aid.line_type_lookup_code), --line_type_lookup_code
4768 ail.description, --description
4769 x_correction_type, --dist_match_type
4770 'PERMANENT', --distribution_class
4771 ail.org_id, --org_id
4772 x_corr_dist_tab(i).dist_ccid, --dist_code_combination_id
4773 ail.accounting_date, --accounting_date
4774 ail.period_name, --period_name
4775 NULL, --amount_to_post
4776 NULL, --base_amount_to_post
4777 NULL, --posted_amount
4778 NULL, --posted_base_amount
4779 NULL, --je_batch_id
4780 NULL, --cash_je_batch_id
4781 'N', --posted_flag
4782 NULL, --accounting_event_id
4783 NULL, --upgrade_posted_amt
4784 NULL, --upgrade_base_posted_amt
4785 g_set_of_books_id, --set_of_books_id
4786 x_corr_dist_tab(i).amount, --amount
4787 x_corr_dist_tab(i).base_amount,--base_amount
4788 x_corr_dist_tab(i).rounding_amt,--rounding_amount
4789 --bugfix:4959567
4790 NULL, --match_status_flag
4791 'N', --encumbered_flag
4792 NULL, --packet_id
4793 -- Removed for bug 4277744
4794 -- ail.ussgl_transaction_code, --ussgl_transaction_code
4795 -- NULL, --ussgl_trx_code_context
4796 'N', --reversal_flag
4797 NULL, --parent_reversal_id
4798 'N', --cancellation_flag
4799 DECODE(ail.type_1099,'','',ail.income_tax_region), --income_tax_region
4800 ail.type_1099, --type_1099
4801 NULL, --stat_amount
4802 NULL, --charge_applicable_to_dist_id
4803 NULL, --prepay_amount_remaining
4804 NULL, --prepay_distribution_id
4805 ail.corrected_inv_id, --parent_invoice_id
4806 x_corr_dist_tab(i).corrected_inv_dist_id, --corrected_invoice_dist_id
4807 x_corr_dist_tab(i).corrected_quantity, --corrected_quantity
4808 NULL, --other_invoice_id
4809 x_corr_dist_tab(i).po_distribution_id, --po_distribution_id
4810 ail.rcv_transaction_id /*NULL*/, --rcv_transaction_id /*Bug13691308*/
4811 x_corr_dist_tab(i).unit_price, --unit_price
4812 aid.matched_uom_lookup_code, --matched_uom_lookup_code
4813 NULL, --quantity_invoiced
4814 NULL, --final_match_flag
4815 NULL, --related_id
4816 'U', --assets_addition_flag
4817 aid.assets_tracking_flag, --assets_tracking_flag
4818 decode(aid.assets_tracking_flag,'Y',
4819 ail.asset_book_type_code,NULL), --asset_book_type_code
4820 decode(aid.assets_tracking_flag,'Y',
4821 ail.asset_category_id,NULL), --asset_category_id
4822 aid.project_id, --project_id
4823 aid.task_id, --task_id
4824 aid.expenditure_type, --expenditure_type
4825 aid.expenditure_item_date, --expenditure_item_date
4826 aid.expenditure_organization_id, --expenditure_organization_id
4827 decode(aid.project_id,'','',
4828 x_corr_dist_tab(i).pa_quantity), --pa_quantity
4829 decode(aid.project_id,NULL,'E',
4830 decode(pd.destination_type_code,'SHOP FLOOR','M',
4831 'INVENTORY','M','N')), --pa_addition_flag
4832 NULL, --pa_cc_ar_invoice_id
4833 NULL, --pa_cc_ar_invoice_line_num
4834 NULL, --pa_cc_processed_code
4835 aid.award_id, --award_id
4836 NULL, --gms_burdenable_raw_cost
4837 NULL, --awt_flag
4838 decode(g_system_allow_awt_flag,'Y',
4839 decode(g_site_allow_awt_flag,'Y',ail.awt_group_id,NULL),
4840 NULL), --awt_group_id
4841 NULL, --awt_tax_rate_id
4842 NULL, --awt_gross_amount
4843 NULL, --awt_invoice_id
4844 NULL, --awt_origin_group_id
4845 NULL, --awt_invoice_payment_id
4846 NULL, --awt_withheld_amt
4847 'N', --inventory_transfer_status
4848 ail.reference_1, --reference_1
4849 ail.reference_2, --reference_2
4850 ail.receipt_verified_flag, --receipt_verified_flag
4851 ail.receipt_required_flag, --receipt_required_flag
4852 ail.receipt_missing_flag, --receipt_missing_flag
4853 ail.justification, --justification
4854 ail.expense_group, --expense_group
4855 ail.start_expense_date, --start_expense_date
4856 ail.end_expense_date, --end_expense_date
4857 ail.receipt_currency_code, --receipt_currency_code
4858 ail.receipt_conversion_rate, --receipt_conversion_rate
4859 ail.receipt_currency_amount, --receipt_currency_amount
4860 ail.daily_amount, --daily_amount
4861 ail.web_parameter_id, --web_parameter_id
4862 ail.adjustment_reason, --adjustment_reason
4863 ail.merchant_document_number, --merchant_document_number
4864 ail.merchant_name, --merchant_name
4865 ail.merchant_reference, --merchant_reference
4866 ail.merchant_tax_reg_number, --merchant_tax_reg_number
4867 ail.merchant_taxpayer_id, --merchant_taxpayer_id
4868 ail.country_of_supply, --country_of_supply
4869 ail.credit_card_trx_id, --credit_card_trx_id
4870 ail.company_prepaid_invoice_id, --company_prepaid_invoice_id
4871 ail.cc_reversal_flag, --cc_reversal_flag
4872 aid.attribute_category, --attribute_category
4873 aid.attribute1, --attribute1
4874 aid.attribute2, --attribute2
4875 aid.attribute3, --attribute3
4876 aid.attribute4, --attribute4
4877 aid.attribute5, --attribute5
4878 aid.attribute6, --attribute6
4879 aid.attribute7, --attribute7
4880 aid.attribute8, --attribute8
4881 aid.attribute9, --attribute9
4882 aid.attribute10, --attribute10
4883 aid.attribute11, --attribute11
4884 aid.attribute12, --attribute12
4885 aid.attribute13, --attribute13
4886 aid.attribute14, --attribute14
4887 aid.attribute15, --attribute15
4888 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
4889 X_GLOBAL_ATTRIBUTE1,
4890 X_GLOBAL_ATTRIBUTE2,*/
4891 decode(ap_extended_withholding_pkg.ap_extended_withholding_option,
4892 'Y',ail.ship_to_location_id,''),
4893 /*X_GLOBAL_ATTRIBUTE4,
4894 X_GLOBAL_ATTRIBUTE5,
4895 X_GLOBAL_ATTRIBUTE6,
4896 X_GLOBAL_ATTRIBUTE7,
4897 X_GLOBAL_ATTRIBUTE8,
4898 X_GLOBAL_ATTRIBUTE9,
4899 X_GLOBAL_ATTRIBUTE10,
4900 X_GLOBAL_ATTRIBUTE11,
4901 X_GLOBAL_ATTRIBUTE12,
4902 X_GLOBAL_ATTRIBUTE13,
4903 X_GLOBAL_ATTRIBUTE14,
4904 X_GLOBAL_ATTRIBUTE15,
4905 X_GLOBAL_ATTRIBUTE16,
4906 X_GLOBAL_ATTRIBUTE17,
4907 X_GLOBAL_ATTRIBUTE18,
4908 X_GLOBAL_ATTRIBUTE19,
4909 X_GLOBAL_ATTRIBUTE20, */
4910 ail.created_by, --created_by
4911 sysdate, --creation_date
4912 ail.last_updated_by, --last_updated_by
4913 sysdate, --last_update_date
4914 ail.last_update_login, --last_update_login
4915 NULL, --program_application_id
4916 NULL, --program_id
4917 NULL, --program_update_date
4918 NULL, --request_id
4919 --ETAX: Invwkb
4920 aid.intended_use, --intended_use
4921 'N', --accrual_posted_flag
4922 'N', --cash_posted_flag
4923 'N', --rcv_charge_addition_flag
4924 decode(g_system_allow_awt_flag,'Y',
4925 decode(g_site_allow_awt_flag,'Y',ail.pay_awt_group_id,NULL),
4926 NULL) --pay_awt_group_id Bug9689194
4927 FROM ap_invoice_lines ail,
4928 ap_invoice_distributions aid,
4929 po_distributions pd
4930 WHERE ail.invoice_id = x_invoice_id
4931 AND ail.line_number = x_invoice_line_number
4932 AND aid.invoice_id = ail.corrected_inv_id
4933 AND aid.invoice_line_number = ail.corrected_line_number
4934 AND aid.invoice_distribution_id = x_corr_dist_tab(i).corrected_inv_dist_id
4935 AND pd.po_distribution_id = aid.po_distribution_id;
4936
4937 l_debug_info := 'Call the AP_EXTENDED_MATCH to populate global attributes';
4938 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4939 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4940 END IF;
4941
4942 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
4943 AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_MATCH(
4944 P_Credit_Id => X_invoice_id,
4945 P_Invoice_Id => X_corrected_invoice_id,
4946 P_Inv_Line_Num => X_invoice_line_number,
4947 P_Distribution_id => x_corr_dist_tab(i).invoice_distribution_id,
4948 P_Parent_Dist_Id => x_corr_dist_tab(i).corrected_inv_dist_id);
4949
4950 END IF;
4951
4952 l_distribution_line_number := l_distribution_line_number + 1;
4953
4954
4955 END IF; /*(x_corr_dist_tab.exists(i)) */
4956
4957 END LOOP;
4958
4959
4960 EXCEPTION
4961 WHEN others then
4962 If (SQLCODE <> -20001) Then
4963 fnd_message.set_name('SQLAP','AP_DEBUG');
4964 fnd_message.set_token('ERROR',SQLERRM);
4965 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4966 fnd_message.set_token('PARAMETERS',
4967 ' invoice_id = '||to_char(x_invoice_id)
4968 ||', invoice_line_number =' ||to_char(x_invoice_line_number)
4969 ||', correction_type = '||x_correction_type
4970 ||', total_amount = '||to_char(x_total_amount));
4971 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4972 End if;
4973
4974 --Clean up the PL/SQL table
4975 x_corr_dist_tab.delete;
4976
4977 app_exception.raise_exception;
4978
4979 END Insert_Corr_Invoice_Dists;
4980
4981
4982 END AP_RECT_MATCH_PKG;