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