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