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