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