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