[Home] [Help]
PACKAGE BODY: APPS.AP_MATCHING_PKG
Source
1 PACKAGE BODY AP_MATCHING_PKG AS
2 /*$Header: apmatchb.pls 120.58.12010000.9 2009/02/12 11:56:08 mayyalas ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_MATCHING_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_MATCHING_PKG.';
21
22 --LOCAL PROCEDURES
23 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
24 X_Invoice_Line_Number IN NUMBER DEFAULT NULL,
25 X_Match_Amount IN NUMBER DEFAULT NULL,
26 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
27 X_Calling_Sequence IN VARCHAR2 );
28
29 Procedure Get_Dist_Proration_Info(
30 X_Invoice_Id IN NUMBER,
31 X_Invoice_Line_Number IN NUMBER,
32 X_Po_Line_Location_Id IN NUMBER,
33 X_Match_Mode IN VARCHAR2,
34 X_Match_Quantity IN NUMBER,
35 X_Match_Amount IN NUMBER,
36 X_Unit_Price IN NUMBER,
37 X_Overbill_Flag IN VARCHAR2,
38 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
39 X_Calling_Sequence IN VARCHAR2);
40
41 PROCEDURE Get_Total_Proration_Quantity(
42 X_PO_Line_Location_Id IN NUMBER,
43 X_Match_Mode IN VARCHAR2,
44 X_Overbill_Flag IN VARCHAR2,
45 X_Total_Quantity OUT NOCOPY NUMBER,
46 X_Calling_Sequence IN VARCHAR2);
47
48
49 Procedure Update_PO_Shipments_Dists(
50 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
51 X_Po_Line_Location_Id IN NUMBER,
52 X_Match_Amount IN NUMBER,
53 X_Match_Quantity IN NUMBER,
54 X_Uom_Lookup_Code IN VARCHAR2,
55 X_Calling_Sequence IN VARCHAR2);
56
57
58 Procedure Insert_Invoice_Line (
59 X_Invoice_Id IN NUMBER,
60 X_Invoice_Line_Number IN NUMBER,
61 X_Line_Type_Lookup_Code IN VARCHAR2,
62 X_Cost_Factor_id IN NUMBER DEFAULT NULL,
63 X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
64 X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
65 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
66 X_Amount IN NUMBER,
67 X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
68 X_Unit_Price IN NUMBER DEFAULT NULL,
69 X_Final_Match_Flag IN VARCHAR2 DEFAULT NULL,
70 X_Item_Line_Number IN NUMBER,
71 X_Charge_Line_Description IN VARCHAR2,
72 X_Retained_Amount IN NUMBER DEFAULT NULL,
73 X_Calling_Sequence IN VARCHAR2);
74
75 PROCEDURE Insert_Invoice_Distributions (
76 X_Invoice_ID IN NUMBER,
77 X_Invoice_Line_Number IN NUMBER,
78 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
79 X_Final_Match_Flag IN VARCHAR2,
80 X_Unit_Price IN NUMBER,
81 X_Total_Amount IN NUMBER,
82 X_Calling_Sequence IN VARCHAR2);
83
84
85 Procedure Create_Charge_Lines(
86 X_Invoice_Id IN NUMBER,
87 X_Freight_Cost_Factor_id IN NUMBER,
88 X_Freight_Amount IN NUMBER,
89 X_Freight_Description IN VARCHAR2,
90 X_Misc_Cost_Factor_id IN NUMBER,
91 X_Misc_Amount IN NUMBER,
92 X_Misc_Description IN VARCHAR2,
93 X_Item_Line_Number IN NUMBER,
94 X_Calling_Sequence IN VARCHAR2);
95
96 PROCEDURE Get_Corr_Dist_Proration_Info(
97 X_Corrected_Invoice_id IN NUMBER,
98 X_Corrected_Line_Number IN NUMBER,
99 X_Corr_Dist_Tab IN OUT NOCOPY CORR_DIST_TAB_TYPE,
100 X_Correction_Type IN VARCHAR2,
101 X_Correction_Amount IN NUMBER,
102 X_Correction_Quantity IN NUMBER,
103 X_Correction_Price IN NUMBER,
104 X_Match_Mode IN VARCHAR2,
105 X_Calling_Sequence IN VARCHAR2);
106
107 PROCEDURE Insert_Corr_Invoice_Line(
108 X_Invoice_Id IN NUMBER,
109 X_Invoice_Line_Number IN NUMBER,
110 X_Corrected_Invoice_Id IN NUMBER,
111 X_Corrected_Line_Number IN NUMBER,
112 X_Quantity IN NUMBER,
113 X_Amount IN NUMBER,
114 X_Unit_Price IN NUMBER,
115 X_Correction_Type IN VARCHAR2,
116 X_Final_Match_Flag IN VARCHAR2,
117 X_Po_Distribution_Id IN NUMBER,
118 X_Retained_Amount IN NUMBER DEFAULT NULL,
119 X_Calling_Sequence IN VARCHAR2);
120
121 PROCEDURE Insert_Corr_Invoice_Dists(
122 X_Invoice_Id IN NUMBER,
123 X_Invoice_Line_Number IN NUMBER,
124 X_Corrected_Invoice_Id IN NUMBER,
125 X_Corr_Dist_Tab IN OUT NOCOPY CORR_DIST_TAB_TYPE,
126 X_Correction_Type IN VARCHAR2,
127 X_Final_Match_Flag IN VARCHAR2,
128 X_Total_Amount IN NUMBER,
129 X_Calling_Sequence IN VARCHAR2);
130
131 PROCEDURE Update_Corr_Po_Shipments_Dists(
132 X_Corr_Dist_Tab IN CORR_DIST_TAB_TYPE,
133 X_Po_Line_Location_Id IN NUMBER,
134 X_Quantity IN NUMBER,
135 X_Amount IN NUMBER,
136 X_Correction_Type IN VARCHAR2,
137 X_Uom_Lookup_Code IN VARCHAR2,
138 X_Calling_Sequence IN VARCHAR2);
139
140 PROCEDURE Get_Shipment_List_For_QM(
141 X_Invoice_Id IN NUMBER,
142 X_Po_Header_Id IN NUMBER,
143 X_Match_Option IN VARCHAR2,
144 X_Match_Amount IN NUMBER,
145 X_Shipment_Table OUT NOCOPY T_SHIPMENT_TABLE,
146 X_Calling_Sequence IN VARCHAR2);
147
148 PROCEDURE Generate_Lines_For_QuickMatch (
149 X_Invoice_Id IN NUMBER,
150 X_Shipment_Table IN T_SHIPMENT_TABLE,
151 X_Match_Option IN VARCHAR2,
152 X_Calling_Sequence IN VARCHAR2);
153
154 Procedure Generate_Release_Lines (p_po_header_id IN NUMBER,
155 p_invoice_id IN NUMBER,
156 p_release_amount IN NUMBER,
157 x_calling_sequence IN VARCHAR2);
158
159 --Global Variable Declaration
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_Base_Currency_Code ap_system_parameters.base_currency_code%TYPE;
174 G_Invoice_Currency_Code ap_invoices.invoice_currency_code%TYPE;
175 G_Allow_PA_Override varchar2(1);
176 G_Pa_Expenditure_Date_Default varchar2(50);
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_Award_ID po_distributions_all.award_id%TYPE;
183 G_Expenditure_Type pa_expenditure_types.expenditure_type%TYPE;
184 G_Invoice_Date ap_invoices.invoice_date%TYPE;
185 G_Expenditure_Organization_ID pa_exp_orgs_it.organization_id%TYPE;
186 G_Asset_Book_Type_Code fa_book_controls.book_type_code%TYPE;
187 G_Asset_Category_Id mtl_system_items.asset_category_id%TYPE;
188 G_Inventory_Organization_Id financials_system_parameters.inventory_organization_id%TYPE;
189 G_Approval_Workflow_Flag ap_system_parameters.approval_workflow_flag%TYPE;
190 -- Removed for bug 4277744
191 -- G_Ussgl_Transaction_Code ap_invoices.ussgl_transaction_code%TYPE;
192 G_Allow_Flex_Override_Flag ap_system_parameters.allow_flex_override_flag%TYPE;
193 G_Shipment_Type po_line_locations.shipment_type%TYPE;
194 G_Org_id ap_invoices.org_id%TYPE;
195 G_Encumbrance_Flag financials_system_parameters.purch_encumbrance_flag%TYPE;
196 G_User_Id number;
197 G_Login_Id number;
198 G_Account_Segment ap_invoice_lines.account_segment%TYPE := NULL;
199 G_Balancing_Segment ap_invoice_lines.balancing_segment%TYPE := NULL;
200 G_Cost_Center_Segment ap_invoice_lines.cost_center_segment%TYPE := NULL;
201 G_Overlay_Dist_Code_Concat ap_invoice_lines.overlay_dist_code_concat%TYPE := NULL;
202 G_Default_Dist_CCid ap_invoice_lines.default_dist_ccid%TYPE := NULL;
203 G_Line_Project_Id ap_invoice_lines.project_id%TYPE ;
204 G_Line_Task_Id ap_invoice_lines.task_id%TYPE ;
205 G_Line_Award_ID ap_invoice_lines.award_id%TYPE ;
206 G_Line_Expenditure_Type ap_invoice_lines.expenditure_type%TYPE ;
207 G_Line_Expenditure_Item_Date ap_invoice_lines.expenditure_item_date%TYPE ;
208 G_Line_Expenditure_Org_Id ap_invoice_lines.expenditure_organization_id%TYPE ;
209 G_Line_Base_Amount ap_invoice_lines.base_amount%TYPE ;
210 G_Line_Awt_Group_Id ap_invoice_lines.awt_group_id%TYPE ;
211 G_Line_Accounting_Date ap_invoice_lines.accounting_date%TYPE;
212 G_Trx_Business_Category ap_invoices.trx_business_category%TYPE;
213 --Contract Payments
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 G_Release_Amount_Net_Of_Tax ap_invoices_all.release_amount_net_of_tax%TYPE;
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 G_recurring_payment_id ap_invoices.recurring_payment_id%TYPE; -- Bug 7305223
229 G_PAY_AWT_GROUP_ID ap_invoices_all.pay_awt_group_id%TYPE; -- bug8222382
230 G_Line_Pay_Awt_Group_Id ap_invoice_lines.pay_awt_group_id%TYPE ; -- bug8222382
231
232 PROCEDURE Base_Credit_PO_Match(X_match_mode IN VARCHAR2,
233 X_invoice_id IN NUMBER,
234 X_invoice_line_number IN NUMBER,
235 X_Po_Line_Location_id IN NUMBER,
236 X_Dist_Tab IN OUT NOCOPY 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_final_match_flag IN VARCHAR2,
242 X_overbill_flag IN VARCHAR2,
243 X_freight_cost_factor_id IN NUMBER DEFAULT NULL,
244 X_freight_amount IN NUMBER,
245 X_freight_description IN VARCHAR2,
246 X_misc_cost_factor_id IN NUMBER DEFAULT NULL,
247 X_misc_amount IN NUMBER,
248 X_misc_description IN VARCHAR2,
249 X_retained_amount IN NUMBER DEFAULT NULL,
250 X_calling_sequence IN VARCHAR2) IS
251
252 l_single_dist_flag varchar2(1) := 'N';
253 l_po_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
254 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
255 l_item_line_number ap_invoice_lines_all.line_number%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_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
259 l_retained_amount ap_invoice_lines_all.retained_amount%TYPE;
260 l_error_message varchar2(4000);
261 l_debug_info varchar2(2000);
262 l_success boolean;
263 current_calling_sequence varchar2(2000);
264 l_api_name CONSTANT VARCHAR2(200) := 'Base_Credit_PO_Match';
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 -- Update the calling sequence (for error message).
283 current_calling_sequence := 'AP_MATCHING_PKG.base_credit_po_match<-'||X_calling_sequence;
284
285 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
287 'AP_MATCHING_PKG.base_credit_po_match(+)');
288 END IF;
289
290 l_debug_info := 'Get Invoice and System Options information';
291 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
292 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
293 END IF;
294
295 get_info(X_Invoice_Id => x_invoice_id,
296 X_Invoice_Line_Number => x_invoice_line_number,
297 X_Match_Amount => x_amount,
298 X_Po_Line_Location_id => x_po_line_location_id,
299 X_Calling_Sequence => current_calling_sequence);
300
301 IF g_invoice_type_lookup_code <> 'PREPAYMENT' THEN
302 l_retained_amount := AP_INVOICE_LINES_UTILITY_PKG.Get_Retained_Amount
303 (p_line_location_id => x_po_line_location_id,
304 p_match_amount => x_amount);
305 END IF;
306
307 --If shipment level match then we need to prorate the match-quantity among the
308 --po distributions of the shipment, for distribution level match we need to
309 --derive the invoice_distribution_id, base_amount, ccid.
310
311 l_debug_info := 'Get PO information';
312 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
313 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
314 END IF;
315
316 --bugfix:5565310
317 l_po_line_location_id := x_po_line_location_id;
318
319 l_success := AP_ETAX_UTILITY_PKG.Get_PO_Info(
320 P_Po_Line_Location_Id => l_po_line_location_id,
321 P_PO_Distribution_Id => null,
322 P_Application_Id => l_ref_doc_application_id,
323 P_Entity_code => l_ref_doc_entity_code,
324 P_Event_Class_Code => l_ref_doc_event_class_code,
325 P_PO_Quantity => l_ref_doc_line_quantity,
326 P_Product_Org_Id => l_dummy,
327 P_Po_Header_Id => l_ref_doc_trx_id,
328 P_Po_Header_curr_conv_rate => l_po_header_curr_conv_rate,
329 P_Uom_Code => l_uom_code,
330 P_Dist_Qty => l_dummy,
331 P_Ship_Price => l_dummy,
332 P_Error_Code => l_error_code,
333 P_Calling_Sequence => current_calling_sequence);
334
335
336 l_debug_info := 'Get PO Tax Attributes';
337 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
339 END IF;
340
341 AP_Etax_Services_Pkg.Get_Po_Tax_Attributes(
342 p_application_id => l_ref_doc_application_id,
343 p_org_id => g_org_id,
344 p_entity_code => l_ref_doc_entity_code,
345 p_event_class_code => l_ref_doc_event_class_code,
346 p_trx_level_type => 'SHIPMENT',
347 p_trx_id => l_ref_doc_trx_id,
348 p_trx_line_id => x_po_line_location_id,
349 x_line_intended_use => g_intended_use,
350 x_product_type => g_product_type,
351 x_product_category => g_product_category,
352 x_product_fisc_classification => g_product_fisc_class,
353 x_user_defined_fisc_class => g_user_defined_fisc_class,
354 x_assessable_value => g_assessable_value,
355 x_tax_classification_code => g_dflt_tax_class_code
356 );
357
358 l_debug_info := 'g_intended_use,g_product_type,g_product_category,g_product_fisc_class '||
359 g_intended_use||','||g_product_type||','||g_product_category||','||g_product_fisc_class;
360
361 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
363 END IF;
364
365 l_debug_info := 'g_user_defined_fisc_class,g_assessable_value,g_dflt_tax_class_code '||
366 g_user_defined_fisc_class||','||g_assessable_value||','||g_dflt_tax_class_code;
367
368 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
370 END IF;
371
372 IF g_source = 'ISP'
373 and x_invoice_line_number IS NOT NULL THEN
374
375 UPDATE ap_invoice_lines_all
376 SET primary_intended_use = nvl(primary_intended_use, g_intended_use)
377 ,product_type = nvl(product_type, g_product_type)
378 ,product_category = nvl(product_category, g_product_category)
379 ,product_fisc_classification = nvl(product_fisc_classification, g_product_fisc_class)
380 ,user_defined_fisc_class = nvl(user_defined_fisc_class, g_user_defined_fisc_class)
381 ,assessable_value = nvl(assessable_value, g_assessable_value)
382 ,tax_classification_code = nvl(tax_classification_code, g_dflt_tax_class_code)
383 WHERE invoice_id = x_invoice_id
384 AND line_number = x_invoice_line_number;
385
386 END IF;
387
388 l_debug_info := 'Get Distribution Proration Info';
389
390
391 Get_Dist_Proration_Info( X_Invoice_Id => x_invoice_id,
392 X_Invoice_Line_Number => x_invoice_line_number,
393 X_Po_Line_Location_Id => x_po_line_location_id,
394 X_Match_Mode => x_match_mode,
395 X_Match_Quantity => x_quantity,
396 X_Match_Amount => x_amount,
397 X_Unit_Price => x_unit_price,
398 X_Overbill_Flag => x_overbill_flag,
399 X_Dist_Tab => x_dist_tab,
400 X_Calling_Sequence => current_calling_sequence);
401
402
403 IF (x_dist_tab.COUNT = 1) THEN
404
405 l_single_dist_flag := 'Y';
406 l_po_distribution_id := x_dist_tab.FIRST;
407 l_invoice_distribution_id := x_dist_tab(l_po_distribution_id).invoice_distribution_id;
408
409 END IF;
410
411 --Create a invoice line if one doesn't exist already.
412 IF (x_invoice_line_number IS NULL) THEN
413
414 l_debug_info := 'Create Matched Invoice Line';
415
416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
418 END IF;
419 Insert_Invoice_Line(X_Invoice_ID => x_invoice_id,
420 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
421 X_Line_Type_Lookup_Code => 'ITEM',
422 X_Single_Dist_Flag => l_single_dist_flag,
423 X_Po_Distribution_Id => l_po_distribution_id,
424 X_Po_Line_Location_id => x_po_line_location_id,
425 X_Amount => x_amount,
426 X_Quantity_Invoiced => x_quantity,
427 X_Unit_Price => x_unit_price,
428 X_Final_Match_Flag => x_final_match_flag,
429 X_Item_Line_Number => NULL,
430 X_Charge_Line_Description => NULL,
431 X_Retained_Amount => l_retained_amount,
432 X_Calling_Sequence => current_calling_sequence);
433 l_item_line_number := g_max_invoice_line_number;
434
435 END IF;
436
437 l_debug_info := 'Create Matched Invoice Distributions';
438 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
439 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
440 END IF;
441 Insert_Invoice_Distributions( X_Invoice_ID => x_invoice_id,
442 X_Invoice_Line_Number => nvl(x_invoice_line_number,
443 g_max_invoice_line_number),
444 X_Dist_Tab => x_dist_tab,
445 X_Final_Match_Flag => x_final_match_flag,
446 X_Unit_Price => x_unit_price,
447 X_Total_Amount => x_amount,
448 X_Calling_Sequence => current_calling_sequence);
449
450
451 IF (x_invoice_line_number IS NOT NULL) THEN
452
453 IF (l_single_dist_flag = 'Y') THEN
454
455 l_debug_info := 'If the line is matched down to 1 po distribution then need to
456 update the line with po_distribution_id, award_id,requester_id,
457 ,projects related information and generate_dists';
458 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
459 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
460 END IF;
461
462 UPDATE ap_invoice_lines ail
463 SET (generate_dists ,
464 requester_id ,
465 po_distribution_id ,
466 project_id ,
467 task_id ,
468 expenditure_type ,
469 expenditure_item_date ,
470 expenditure_organization_id ,
471 pa_quantity ,
472 award_id,
473 attribute_category,
474 attribute1,
475 attribute2,
476 attribute3,
477 attribute4,
478 attribute5,
479 attribute6,
480 attribute7,
481 attribute8,
482 attribute9,
483 attribute10,
484 attribute11,
485 attribute12,
486 attribute13,
487 attribute14,
488 attribute15,
489 retained_amount,
490 retained_amount_remaining
491 ) =
492 (SELECT 'D',
493 pd.deliver_to_person_id,
494 aid.po_distribution_id,
495 aid.project_id,
496 aid.task_id,
497 aid.expenditure_type,
498 aid.expenditure_item_date,
499 aid.expenditure_organization_id,
500 aid.pa_quantity,
501 gms_ap_api.get_distribution_award(aid.award_id),
502 /* Bug 7483260. If the attribute field is populated in the
503 * interface, take that value. If the attribute field from
504 * the interface is null and the transfer_desc_flex_flag is
505 * Y, take the value from the purchase order.
506 */
507 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
508 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
509 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
510 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
511 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
512 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
513 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
514 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
515 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
516 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
517 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
518 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
519 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
520 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
521 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
522 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
523 --end Bug 7483260
524 l_retained_amount,
525 -1 * l_retained_amount
526 FROM ap_invoice_distributions aid,
527 po_distributions pd,
528 po_line_locations pll
529 WHERE aid.invoice_distribution_id = l_invoice_distribution_id
530 AND pd.po_distribution_id = aid.po_distribution_id
531 AND pll.line_location_id = pd.line_location_id)
532 WHERE ail.invoice_id = x_invoice_id
533 AND ail.line_number = x_invoice_line_number;
534
535
536 ELSE
537
538 l_debug_info := 'Update the generate_dists to D after the distributions are created';
539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
541 END IF;
542
543 UPDATE ap_invoice_lines ail
544 SET (generate_dists ,
545 attribute_category,
546 attribute1,
547 attribute2,
548 attribute3,
549 attribute4,
550 attribute5,
551 attribute6,
552 attribute7,
553 attribute8,
554 attribute9,
555 attribute10,
556 attribute11,
557 attribute12,
558 attribute13,
559 attribute14,
560 attribute15,
561 retained_amount,
562 retained_amount_remaining)
563 =
564 (SELECT 'D',
565 /* Bug 7483260. If the attribute field is populated in the
566 * interface, take that value. If the attribute field from
567 * the interface is null and the transfer_desc_flex_flag is
568 * Y, take the value from the purchase order.
569 */
570 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
571 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
572 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
573 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
574 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
575 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
576 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
577 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
578 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
579 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
580 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
581 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
582 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
583 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
584 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
585 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
586 --end Bug 7483260
587 l_retained_amount,
588 -1 * l_retained_amount
589 FROM ap_invoice_lines ail1,
590 po_line_locations pll
591 WHERE ail1.invoice_id = x_invoice_id
592 AND ail1.line_number =x_invoice_line_number
593 AND pll.line_location_id = ail1.po_line_location_id)
594 WHERE invoice_id = x_invoice_id
595 AND line_number = x_invoice_line_number;
596
597 END IF;
598
599 END IF;
600
601 l_debug_info := 'Create Retainage Distributions';
602 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
603 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
604 END IF;
605
606 Ap_Retainage_Pkg.Create_Retainage_Distributions
607 (x_invoice_id => x_invoice_id,
608 x_invoice_line_number => nvl(x_invoice_line_number,l_item_line_number));
609
610 IF (G_Recoupment_Rate IS NOT NULL and x_amount > 0
611 and g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
612
613 l_debug_info := 'Calculate the maximum amount that can be recouped from this invoice line';
614 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
615 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
616 END IF;
617
618 l_line_amt_net_retainage := x_amount + nvl(l_retained_amount,0);
619
620 l_max_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
621 (x_amount * g_recoupment_rate / 100) ,g_invoice_currency_code);
622
623 IF (l_line_amt_net_retainage < l_max_amount_to_recoup) THEN
624 l_amount_to_recoup := l_line_amt_net_retainage;
625 ELSE
626 l_amount_to_recoup := l_max_amount_to_recoup;
627 END IF;
628
629 l_debug_info := 'Automatically recoup any available prepayments against the same po line';
630 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
631 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
632 END IF;
633
634 l_success := AP_Matching_Utils_Pkg.Ap_Recoup_Invoice_Line(
635 P_Invoice_Id => x_invoice_id ,
636 P_Invoice_Line_Number => nvl(x_invoice_line_number,l_item_line_number) ,
637 P_Amount_To_Recoup => l_amount_to_recoup,
638 P_Po_Line_Id => g_po_line_id,
639 P_Vendor_Id => g_vendor_id,
640 P_Vendor_Site_Id => g_vendor_site_id,
641 P_Accounting_Date => g_accounting_date,
642 P_Period_Name => g_period_name,
643 P_User_Id => g_user_id,
644 P_Last_Update_Login => g_login_id ,
645 P_Error_Message => l_error_message,
646 P_Calling_Sequence => current_calling_sequence);
647
648 END IF;
649
650 l_debug_info := 'Update Quantity/Amount Billed/Financed on the Po Shipments and Distributions';
651 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
652 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
653 END IF;
654
655 Update_PO_Shipments_Dists(X_Dist_Tab => x_dist_tab,
656 X_Po_Line_Location_Id => x_po_line_location_id,
657 X_Match_Amount => x_amount,
658 X_Match_Quantity => x_quantity,
659 X_Uom_Lookup_Code => x_uom_lookup_code,
660 X_Calling_Sequence => current_calling_sequence);
661
662
663 IF (x_freight_amount IS NOT NULL or x_misc_amount IS NOT NULL) THEN
664
665 l_debug_info := 'Call the procedure to create charge lines';
666 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
667 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
668 END IF;
669
670 --Due to the way PL/SQL binding is done for global variables, need
671 --pass the local instead of global variable for this as global variable
672 --is being updated before the x_item_line_number is used during runtime.
673
674 Create_Charge_Lines(X_Invoice_Id => x_invoice_id,
675 X_Freight_Cost_Factor_id => x_freight_cost_factor_id,
676 X_Freight_Amount => x_freight_amount,
677 X_Freight_Description => x_freight_description,
678 X_Misc_Cost_Factor_id => x_misc_cost_factor_id,
679 X_Misc_Amount => x_misc_amount,
680 X_Misc_Description => x_misc_description,
681 X_Item_Line_Number => l_item_line_number,
682 X_Calling_Sequence => current_calling_sequence);
683
684 END IF;
685
686 --Clean up the PL/SQL table
687 X_DIST_TAB.DELETE;
688
689 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.base_credit_po_match(-)');
691 END IF;
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 IF (SQLCODE <> -20001) THEN
696 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
697 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
698 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
699 FND_MESSAGE.SET_TOKEN('PARAMETERS','Match Mode = '||x_match_mode
700 ||', Invoice Id = '||to_char(x_invoice_id)
701 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
702 ||', Shipment ID = '||to_char(x_po_line_location_id)
703 ||', Match amount = '||to_char(x_amount)
704 ||', Match quantity = '||to_char(x_quantity)
705 ||', PO UOM = '||x_uom_lookup_code
706 ||', Final Match Flag = '||X_final_match_flag
707 ||', Overbill Flag = '||x_overbill_flag
708 ||', Freight Amount = '||to_char(x_freight_amount)
709 ||', Freight Description = '||x_freight_description
710 ||', Misc Amount = '||to_char(x_misc_amount)
711 ||', Misc Description = '||x_misc_description);
712 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
713 END IF;
714
715 --Clean up the PL/SQL table
716 X_DIST_TAB.DELETE;
717 APP_EXCEPTION.RAISE_EXCEPTION;
718
719 END Base_Credit_PO_Match;
720
721
722
723 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
724 X_Invoice_Line_Number IN NUMBER DEFAULT NULL,
725 X_Match_Amount IN NUMBER DEFAULT NULL,
726 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
727 X_Calling_Sequence IN VARCHAR2
728 ) IS
729
730 current_calling_sequence VARCHAR2(2000);
731 l_debug_info VARCHAR2(2000);
732 l_api_name VARCHAR2(30);
733 l_accounting_date ap_invoice_lines.accounting_date%TYPE; --7463095
734
735 BEGIN
736
737 l_api_name := 'Get_Info';
738
739 current_calling_sequence := 'Get_Info<-'||X_Calling_Sequence;
740 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
741 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Info(+)');
742 END IF;
743
744 SELECT ai.gl_date,
745 ai.batch_id,
746 ai.set_of_books_id,
747 ai.awt_group_id,
748 ai.invoice_type_lookup_code,
749 ai.exchange_rate,
750 fc.precision,
751 fc.minimum_accountable_unit,
752 nvl(asp.allow_awt_flag,'N'),
753 nvl(pvs.allow_awt_flag,'N'),
754 nvl(asp.transfer_desc_flex_flag,'N'),
755 asp.base_currency_code,
756 ai.invoice_currency_code,
757 nvl(pvs.prepay_code_combination_id,
758 asp.prepay_code_combination_id),
759 nvl(asp.build_prepayment_accounts_flag,'N'),
760 decode(pv.type_1099,'','',
761 decode(combined_filing_flag,'N',NULL,
762 decode(asp.income_tax_region_flag,'Y',pvs.state,
763 asp.income_tax_region))),
764 ai.project_id,
765 ai.task_id,
766 ai.award_id,
767 ai.expenditure_type,
768 ai.invoice_date,
769 ai.expenditure_organization_id,
770 fsp.inventory_organization_id,
771 nvl(asp.approval_workflow_flag,'N'),
772 -- ai.ussgl_transaction_code,- Bug 4277744
773 asp.allow_flex_override_flag,
774 ai.org_id,
775 nvl(fsp.purch_encumbrance_flag,'N'),
776 ai.award_id,
777 ai.trx_business_category,
778 ai.vendor_id,
779 ai.vendor_site_id,
780 ai.release_amount_net_of_tax,
781 ai.source,
782 ai.recurring_payment_id, -- Bug 7305223
783 ai.pay_awt_group_id --bug 8222382
784 INTO g_accounting_date,
785 g_batch_id,
786 g_set_of_books_id,
787 g_awt_group_id,
788 g_invoice_type_lookup_code,
789 g_exchange_rate,
790 g_precision,
791 g_min_acct_unit,
792 g_system_allow_awt_flag,
793 g_site_allow_awt_flag,
794 g_transfer_flag,
795 g_base_currency_code,
796 g_invoice_currency_code,
797 g_prepay_ccid,
798 g_build_prepay_accts_flag,
799 g_income_tax_region,
800 g_project_id,
801 g_task_id,
802 g_award_id,
803 g_expenditure_type,
804 g_invoice_date,
805 g_expenditure_organization_id,
806 g_inventory_organization_id,
807 g_approval_workflow_flag,
808 -- g_ussgl_transaction_code,- Bug 4277744
809 g_allow_flex_override_flag,
810 g_org_id,
811 g_encumbrance_flag,
812 g_award_id,
813 g_trx_business_category,
814 g_vendor_id,
815 g_vendor_site_id,
816 g_release_amount_net_of_tax,
817 g_source,
818 g_recurring_payment_id, -- Bug 7305223
819 /* Bug 5572876, using base tables */
820 g_pay_awt_group_id -- bug 8222382
821 FROM ap_invoices_all ai ,
822 ap_system_parameters_all asp,
823 ap_suppliers pv,
824 ap_supplier_sites_all pvs,
825 financials_system_params_all fsp,
826 fnd_currencies fc
827 WHERE ai.invoice_id = x_invoice_id
828 AND ai.vendor_site_id = pvs.vendor_site_id
829 AND pv.vendor_id = pvs.vendor_id
830 AND ai.org_id = asp.org_id
831 AND asp.org_id = fsp.org_id
832 AND ai.set_of_books_id = asp.set_of_books_id
833 AND asp.set_of_books_id = fsp.set_of_books_id
834 AND ai.invoice_currency_code = fc.currency_code (+);
835
836
837 IF (x_match_amount IS NOT NULL AND 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 IF (x_invoice_line_number IS NOT NULL) THEN
845
846 SELECT
847 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 ail.pay_awt_group_id
861 INTO
862 g_account_segment,
863 g_balancing_segment,
864 g_cost_center_segment,
865 g_overlay_dist_code_concat,
866 g_default_dist_ccid,
867 g_line_project_id,
868 g_line_task_id,
869 g_line_award_id,
870 g_line_expenditure_type,
871 g_line_expenditure_item_date,
872 g_line_expenditure_org_id,
873 g_line_awt_group_id,
874 g_line_accounting_date,
875 g_line_pay_awt_group_id
876 FROM ap_invoice_lines ail
877 WHERE ail.invoice_id = x_invoice_id
878 AND ail.line_number = x_invoice_line_number;
879
880 END IF;
881
882 SELECT nvl(max(ail.line_number),0)
883 INTO g_max_invoice_line_number
884 FROM ap_invoice_lines ail
885 WHERE ail.invoice_id = x_invoice_id;
886
887 /* Bug 5572876 */
888 g_asset_book_type_code := Ap_Utilities_Pkg.Ledger_Asset_Book
889 (g_set_of_books_id);
890
891 /*
892 BEGIN
893 SELECT book_type_code
894 INTO g_asset_book_type_code
895 FROM fa_book_controls fc
896 WHERE fc.book_class = 'CORP0RATE'
897 AND fc.set_of_books_id = g_set_of_books_id
898 AND fc.date_ineffective IS NULL;
899 EXCEPTION
900 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
901 g_asset_book_type_code := NULL;
902 END; */
903
904 IF (x_po_line_location_id IS NOT NULL) THEN
905
906 SELECT pll.shipment_type, pll.po_line_id, pl.recoupment_rate
907 INTO g_shipment_type, g_po_line_id, g_recoupment_rate
908 FROM po_line_locations pll, po_lines pl
909 WHERE pll.line_location_id = x_po_line_location_id
910 AND pll.po_line_id = pl.po_line_id;
911
912 END IF;
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 -- Bug 4460697. Passed the g_org_id as some cases
922 -- mo_global.get_current_org_id does not work
923 g_period_name := AP_UTILITIES_PKG.get_current_gl_date(g_accounting_date,
924 g_org_id);
925
926 IF (g_period_name IS NULL) THEN
927
928 --Get gl_period and Date from a future period for the accounting date
929 -- Bug 4460697. Passed the g_org_id as some cases
930 -- mo_global.get_current_org_id does not work
931
932 -- 7463095 Used l_accounting_date instead of g_accounting_date. Using
933 --same variable for in/out parmeters causing to make in parameters
934 -- as Null
935 ap_utilities_pkg.get_open_gl_date(p_date => g_accounting_date,
936 p_period_name => g_period_name,
937 p_gl_date => l_accounting_date,
938 p_org_id => g_org_id);
939
940 g_accounting_date := l_accounting_date; --7463095
941
942 --Bug 7305223 While generating recurring invoices no need of checking
943 --gl date is open/closed. Always allowing recurring invoices to generate
944 --any period.
945
946 IF g_recurring_payment_id is null THEN --Bug 7305223
947 IF (g_accounting_date IS NULL) THEN
948 fnd_message.set_name('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
949 app_exception.raise_exception;
950 ELSE
951 g_line_accounting_date := g_accounting_date;
952 END IF;
953 END IF;
954
955 END IF;
956
957 --Bug 6956226. Modified below statement to assign 'Yes' if this profile
958 --has defined no value.
959 g_allow_pa_override := NVL(FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES'),'Y');
960
961 -- Bug 5294998. API from PA will be used
962 -- g_pa_expenditure_date_default := FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT');
963
964 g_user_id := FND_PROFILE.VALUE('USER_ID');
965
966 g_login_id := FND_PROFILE.VALUE('LOGIN_ID');
967
968 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
969 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Info(-)');
970 END IF;
971
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 IF (SQLCODE <> -20001) THEN
976 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
977 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
978 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
979 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_Invoice_Id));
980 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
981 END IF;
982 app_exception.raise_exception;
983
984 END Get_Info;
985
986
987 Procedure Get_Dist_Proration_Info(
988 X_Invoice_Id IN NUMBER,
989 X_Invoice_Line_Number IN NUMBER,
990 X_Po_Line_Location_Id IN NUMBER,
991 X_Match_Mode IN VARCHAR2,
992 X_Match_Quantity IN NUMBER,
993 X_Match_Amount IN NUMBER,
994 X_Unit_Price IN NUMBER,
995 X_Overbill_Flag IN VARCHAR2,
996 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
997 X_Calling_Sequence IN VARCHAR2) IS
998
999
1000 CURSOR po_distributions_cursor(p_total_quantity NUMBER) IS
1001 SELECT po_distribution_id,
1002 /*PRORATED AMOUNT*/
1003 DECODE(g_min_acct_unit,
1004 '', ROUND( X_match_amount * DECODE(X_match_mode,
1005 'STD-PS',DECODE(X_overbill_flag,
1006 'Y', NVL(PD.quantity_ordered, 0),
1007 NVL(DECODE(SIGN(PD.quantity_ordered -
1008 DECODE(PD.distribution_type,'PREPAYMENT',
1009 NVL(PD.quantity_financed,0),
1010 NVL(PD.quantity_billed,0)
1011 ) -
1012 NVL(PD.quantity_cancelled,0)),
1013 -1, 0,
1014 PD.quantity_ordered -
1015 DECODE(PD.distribution_type,'PREPAYMENT',
1016 NVL(PD.quantity_financed,0),
1017 NVL(PD.quantity_billed,0)
1018 ) -
1019 NVL(PD.quantity_cancelled,0)
1020 )
1021 ,0)
1022 ),
1023 NVL(PD.quantity_billed, 0)
1024 )
1025 / p_total_quantity,
1026 g_precision),
1027 ROUND(((X_match_amount * DECODE(X_match_mode,
1028 'STD-PS',DECODE(X_overbill_flag,
1029 'Y', NVL(PD.quantity_ordered, 0),
1030 NVL(DECODE(SIGN(PD.quantity_ordered -
1031 DECODE(PD.distribution_type,'PREPAYMENT',
1032 NVL(PD.quantity_financed,0),
1033 NVL(PD.quantity_billed,0)
1034 ) -
1035 NVL(PD.quantity_cancelled,0)),
1036 -1, 0,
1037 PD.quantity_ordered -
1038 DECODE(PD.distribution_type,'PREPAYMENT',
1039 NVL(PD.quantity_financed,0),
1040 NVL(PD.quantity_billed,0)
1041 ) -
1042 NVL(PD.quantity_cancelled,0)
1043 )
1044 ,0)
1045 ),
1046 NVL(PD.quantity_billed, 0)
1047 )
1048 / p_total_quantity)
1049 / g_min_acct_unit) * g_min_acct_unit)),
1050 /*PRORATED QUANTITY*/
1051 ROUND( X_match_quantity * DECODE(X_match_mode,
1052 'STD-PS',DECODE(X_overbill_flag,
1053 'Y', NVL(PD.quantity_ordered, 0),
1054 NVL(DECODE(SIGN(PD.quantity_ordered -
1055 DECODE(PD.distribution_type,'PREPAYMENT',
1056 NVL(PD.quantity_financed,0),
1057 NVL(PD.quantity_billed,0)
1058 ) -
1059 NVL(PD.quantity_cancelled,0)),
1060 -1, 0,
1061 PD.quantity_ordered -
1062 DECODE(PD.distribution_type,'PREPAYMENT',
1063 NVL(PD.quantity_financed,0),
1064 NVL(PD.quantity_billed,0)
1065 ) -
1066 NVL(PD.quantity_cancelled,0))
1067 , 0)),
1068 DECODE(PD.distribution_type,'PREPAYMENT',
1069 NVL(PD.quantity_financed,0),
1070 NVL(PD.quantity_billed,0)
1071 )
1072 )
1073 / p_total_quantity,15),
1074 PD.code_combination_id,
1075 PD.accrue_on_receipt_flag,
1076 DECODE(PD.destination_type_code,'EXPENSE',
1077 PD.project_id,NULL), --project_id
1078 DECODE(PD.destination_type_code,'EXPENSE',
1079 PD.task_id,NULL), --task_id
1080 DECODE(PD.destination_type_code,'EXPENSE',
1081 PD.expenditure_type,NULL), --expenditure_type
1082 DECODE(PD.destination_type_code,
1083 'EXPENSE',PD.expenditure_item_date,
1084 NULL), --expenditure_item_date
1085 DECODE(PD.destination_type_code,
1086 'EXPENSE',PD.expenditure_organization_id,
1087 NULL), --expenditure_organization_id
1088 DECODE(PD.destination_type_code,
1089 'EXPENSE', PD.award_id), --award_id
1090 ap_invoice_distributions_s.nextval
1091 FROM po_distributions_ap_v PD
1092 WHERE line_location_id = x_po_line_location_id;
1093
1094
1095 l_total_quantity number;
1096 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1097 l_amount_invoiced ap_invoice_distributions_all.amount%TYPE;
1098 l_quantity_invoiced ap_invoice_distributions_all.quantity_invoiced%TYPE;
1099 l_po_dist_ccid po_distributions.code_combination_id%TYPE;
1100 l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1101 l_project_id po_distributions.project_id%TYPE;
1102 l_unbuilt_flex varchar2(240):='';
1103 l_reason_unbuilt_flex varchar2(2000):='';
1104 l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1105 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1106 l_task_id po_distributions.task_id%TYPE;
1107 l_award_set_id po_distributions_all.award_id%TYPE;
1108 l_expenditure_type po_distributions.expenditure_type%TYPE;
1109 l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110 l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111 l_max_dist_amount number := 0;
1112 l_sum_prorated_amount number := 0;
1113 l_sum_dist_base_amount number := 0;
1114 l_rounding_index po_distributions.po_distribution_id%TYPE;
1115 l_base_amount ap_invoice_distributions.base_amount%TYPE;
1116 flex_overlay_failed exception;
1117 current_calling_sequence varchar2(2000);
1118 l_debug_info varchar2(2000);
1119 l_api_name VARCHAR2(50);
1120
1121 BEGIN
1122
1123 l_api_name := 'Get_Dist_Proration_Info';
1124
1125 current_calling_sequence := 'Get_Dist_Proration_Info<-'||x_calling_sequence;
1126 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1127 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Dist_Proration_Info(+)');
1128 END IF;
1129
1130 IF(X_Match_Mode IN ('STD-PS','CR-PS')) THEN
1131
1132 l_debug_info := 'Get Total Quantity for Proration';
1133 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1134 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1135 END IF;
1136
1137 Get_Total_Proration_Quantity
1138 ( X_PO_Line_Location_Id => x_po_line_location_id,
1139 X_Match_Mode => x_match_mode,
1140 X_Overbill_Flag => x_overbill_flag,
1141 X_Total_Quantity => l_total_quantity,
1142 X_Calling_Sequence => current_calling_sequence);
1143
1144 l_debug_info := 'g_min_acct_unit, x_match_amount, x_overbill_flag, x_match_mode, x_match_quantity, x_po_line_location_id'||
1145 g_min_acct_unit||','||x_match_amount||','||x_overbill_flag||','||x_match_mode||','||x_match_quantity||','||x_po_line_location_id;
1146
1147 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1149 END IF;
1150
1151 OPEN PO_Distributions_Cursor(l_total_quantity);
1152 LOOP
1153
1154 l_debug_info := 'Fetch record from Po_Distributions_Cursor l_total_quantity is '||l_total_quantity;
1155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1157 END IF;
1158
1159 FETCH PO_Distributions_Cursor INTO l_po_distribution_id,
1160 l_amount_invoiced,
1161 l_quantity_invoiced,
1162 l_po_dist_ccid,
1163 l_accrue_on_receipt_flag,
1164 l_project_id,
1165 l_task_id,
1166 l_expenditure_type,
1167 l_po_expenditure_item_date,
1168 l_expenditure_organization_id,
1169 l_award_set_id,
1170 l_invoice_distribution_id;
1171
1172 EXIT WHEN PO_Distributions_Cursor%NOTFOUND;
1173
1174 --IF (l_amount_invoiced <> 0) THEN --Bug6321189
1175
1176 l_debug_info := 'Populate the PL/SQL table x_dist_tab with the distribution information';
1177 IF(G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1178 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1179 END IF;
1180
1181 x_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
1182 x_dist_tab(l_po_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
1183 x_dist_tab(l_po_distribution_id).amount := l_amount_invoiced;
1184 x_dist_tab(l_po_distribution_id).quantity_invoiced := l_quantity_invoiced;
1185 x_dist_tab(l_po_distribution_id).unit_price := x_unit_price;
1186 x_dist_tab(l_po_distribution_id).po_ccid := l_po_dist_ccid;
1187 x_dist_tab(l_po_distribution_id).accrue_on_receipt_flag := l_accrue_on_receipt_flag;
1188 x_dist_tab(l_po_distribution_id).project_id := l_project_id;
1189 x_dist_tab(l_po_distribution_id).task_id := l_task_id;
1190 x_dist_tab(l_po_distribution_id).expenditure_type := l_expenditure_type;
1191 x_dist_tab(l_po_distribution_id).expenditure_organization_id := l_expenditure_organization_id;
1192 x_dist_tab(l_po_distribution_id).expenditure_item_date := l_po_expenditure_item_date;
1193 --Bug 5554493
1194 x_dist_tab(l_po_distribution_id).pa_quantity := l_quantity_invoiced;
1195
1196 If l_award_set_id Is Not Null Then
1197 x_dist_tab(l_po_distribution_id).award_id := gms_ap_api.get_distribution_award(l_award_set_id);
1198 End If;
1199
1200 --For proration rounding/base amount rounding,
1201 --calculating max of the largest distribution's index
1202 IF (l_amount_invoiced >= l_max_dist_amount) THEN
1203 l_rounding_index := l_po_distribution_id;
1204 l_max_dist_amount := l_amount_invoiced;
1205 END IF;
1206
1207 l_sum_prorated_amount := l_sum_prorated_amount + l_amount_invoiced;
1208
1209 --END IF; /* (l_amount_invoiced <> 0) */
1210
1211 END LOOP;
1212
1213 CLOSE PO_Distributions_Cursor;
1214
1215 --Update the PL/SQL table's amount column with the rounding amount due
1216 --to proration, before the base_amounts are calculated.
1217
1218 IF (l_sum_prorated_amount <> x_match_amount and l_rounding_index is not null) THEN
1219
1220 x_dist_tab(l_rounding_index).amount := x_dist_tab(l_rounding_index).amount +
1221 (x_match_amount - l_sum_prorated_amount);
1222
1223 END IF;
1224
1225 ELSE
1226
1227 FOR i IN nvl(x_dist_tab.FIRST,0)..nvl(x_dist_tab.LAST,0) LOOP
1228
1229 IF (x_dist_tab.exists(i)) THEN
1230
1231 SELECT accrue_on_receipt_flag,
1232 code_combination_id,
1233 project_id,
1234 task_id,
1235 award_id,
1236 expenditure_type,
1237 expenditure_item_date,
1238 expenditure_organization_id,
1239 ap_invoice_distributions_s.nextval
1240 INTO x_dist_tab(i).accrue_on_receipt_flag,
1241 x_dist_tab(i).po_ccid,
1242 x_dist_tab(i).project_id,
1243 x_dist_tab(i).task_id,
1244 l_award_set_id,
1245 x_dist_tab(i).expenditure_type,
1246 x_dist_tab(i).expenditure_item_date,
1247 x_dist_tab(i).expenditure_organization_id,
1248 x_dist_tab(i).invoice_distribution_id
1249 FROM po_distributions_ap_v
1250 WHERE po_distribution_id = x_dist_tab(i).po_distribution_id;
1251
1252 -- Bug 5554493
1253 x_dist_tab(i).pa_quantity := x_dist_tab(i).quantity_invoiced;
1254
1255 If l_award_set_id Is Not Null Then
1256 x_dist_tab(i).award_id := gms_ap_api.get_distribution_award(l_award_set_id);
1257 End If;
1258
1259 --calculate the max of the largest distribution's index to be
1260 --used for base amount rounding. No need to perform proration
1261 --rounding for the case when the match is distributed by the user.
1262
1263 --Need to do the base_amount rounding only for foreign currency
1264 --invoices only.
1265
1266 IF (g_exchange_rate IS NOT NULL) THEN
1267
1268 IF (x_dist_tab(i).amount >= l_max_dist_amount) THEN
1269 l_rounding_index := i;
1270 l_max_dist_amount := x_dist_tab(i).amount;
1271 END IF;
1272
1273 END IF;
1274
1275 END IF;
1276
1277 END LOOP;
1278
1279 END IF;
1280
1281
1282 FOR i in nvl(x_dist_tab.first,0) .. nvl(x_dist_tab.last,0) LOOP
1283
1284 IF (x_dist_tab.exists(i)) THEN
1285
1286 l_debug_info := 'Populate Project related information';
1287 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1288 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1289 END IF;
1290
1291 --If no project info in the PL/SQL by now,
1292 --then project info was null on po distribution
1293 --,then copy it from line for line level match, else copy it from invoice header.
1294
1295 --Not doing NVL on the g_line_project_id, b'coz for the case of line level match
1296 --if the user has not provided any project information on the line regardless of
1297 --whether project info is present on header or not, we will not override what user
1298 --has explicitly provided.
1299
1300 /*
1301 IF (x_dist_tab(i).project_id IS NULL ) THEN
1302
1303 IF (l_accrue_on_receipt_flag = 'N' and g_allow_pa_override = 'Y')THEN
1304
1305 IF (x_invoice_line_number IS NOT NULL) THEN
1306
1307 x_dist_tab(i).project_id := g_line_project_id;
1308 x_dist_tab(i).task_id := g_line_task_id;
1309 x_dist_tab(i).expenditure_type := g_line_expenditure_type;
1310 x_dist_tab(i).expenditure_organization_id := g_line_expenditure_org_id;
1311
1312 ELSE
1313
1314 x_dist_tab(i).project_id := g_project_id;
1315 x_dist_tab(i).task_id := g_task_id;
1316 x_dist_tab(i).expenditure_type := g_expenditure_type;
1317 x_dist_tab(i).expenditure_organization_id := g_expenditure_organization_id;
1318
1319 END IF;
1320
1321 END IF;
1322
1323 END IF;
1324 */
1325 IF (x_dist_tab(i).project_id IS NOT NULL) THEN
1326 --Bug 5554493
1327 --x_dist_tab(i).pa_quantity := l_quantity_invoiced;
1328 -- Bug 5294998. PA API will be used
1329 /*
1330 CASE g_pa_expenditure_date_default
1331 WHEN 'PO Expenditure Item Date/Transaction Date' THEN
1332 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,g_invoice_date);
1333 WHEN 'PO Expenditure Item Date/Transaction GL Date' THEN
1334 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,g_accounting_date);
1335 WHEN 'PO Expenditure Item Date/Transaction System Date' THEN
1336 x_dist_tab(i).expenditure_item_date := nvl(x_dist_tab(i).expenditure_item_date,sysdate);
1337 WHEN ('Receipt Date/Transaction Date' ) THEN
1338 x_dist_tab(i).expenditure_item_date := g_invoice_date;
1339 WHEN ('Receipt Date/Transaction GL Date') THEN
1340 x_dist_tab(i).expenditure_item_date := g_accounting_date;
1341 WHEN ('Receipt Date/Transaction System Date' ) THEN
1342 x_dist_tab(i).expenditure_item_date := sysdate;
1343 WHEN 'Transaction Date' THEN
1344 x_dist_tab(i).expenditure_item_date := g_invoice_date;
1345 WHEN 'Transaction GL Date' THEN
1346 x_dist_tab(i).expenditure_item_date := g_accounting_date;
1347 WHEN 'Transaction System Date' THEN
1348 x_dist_tab(i).expenditure_item_date := sysdate;
1349 ELSE
1350 x_dist_tab(i).expenditure_item_date := NULL;
1351 END CASE;
1352 */
1353 x_dist_tab(i).expenditure_item_date :=
1354 PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
1355 p_transaction_date => g_invoice_date,
1356 p_gl_date => g_accounting_date,
1357 p_creation_date => sysdate,
1358 p_po_exp_item_date => x_dist_tab(i).expenditure_item_date,
1359 p_po_distribution_id => x_dist_tab(i).po_distribution_id,
1360 p_calling_program => 'PO-MATCH');
1361
1362 END IF;
1363
1364 l_debug_info := 'Populate award information';
1365 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1366 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1367 END IF;
1368
1369 IF (x_dist_tab(i).award_id IS NULL ) THEN
1370 IF (l_accrue_on_receipt_flag = 'N' and g_allow_pa_override = 'Y')THEN
1371 IF (x_invoice_line_number IS NOT NULL) THEN
1372 x_dist_tab(i).award_id := g_line_award_id;
1373 ELSE
1374 x_dist_tab(i).award_id := g_award_id;
1375 END IF;
1376 END IF;
1377 END IF; /*(x_dist_tab(i).award_id IS NULL) */
1378
1379 l_debug_info := 'Populate awt information';
1380 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1382 END IF;
1383
1384 IF (g_system_allow_awt_flag = 'Y' and g_site_allow_awt_flag = 'Y') THEN
1385
1386 IF (x_invoice_line_number IS NOT NULL) THEN
1387 x_dist_tab(i).awt_group_id := g_line_awt_group_id;
1388 x_dist_tab(i).pay_awt_group_id := g_line_pay_awt_group_id;
1389 ELSE
1390 x_dist_tab(i).awt_group_id := g_awt_group_id;
1391 x_dist_tab(i).pay_awt_group_id := g_pay_awt_group_id;
1392 END IF;
1393
1394 END IF;
1395
1396 --Populate Base Amount
1397 --Need to populate the base amount for foreign currency invoices only.
1398 IF (g_exchange_rate IS NOT NULL) THEN
1399 l_base_amount := ap_utilities_pkg.ap_round_currency(
1400 x_dist_tab(i).amount * g_exchange_rate,
1401 g_base_currency_code);
1402 x_dist_tab(i).base_amount := l_base_amount;
1403
1404 l_sum_dist_base_amount := l_sum_dist_base_amount + l_base_amount;
1405 END IF;
1406
1407 --Populate dist_code_combination_id information
1408
1409 --Can overlay account if not accruing on receipt, and either not project_related
1410 --or if project related then project account override is allowed
1411 --and encumbrance is not turned on for all invoices types, except for
1412 --prepayment invoices and system option to allow
1413 --override of matching account is turned ON.
1414
1415 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
1416
1417 --Can overlay account if not accruing on receipt, and either not project_related
1418 --or if project related then project account override is allowed
1419 --and encumbrance is not turned on and system option to allow
1420 --override of matching account is turned ON.
1421
1422 IF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'N'
1423 AND ((x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'Y')
1424 OR x_dist_tab(i).project_id IS NULL)
1425 AND g_allow_flex_override_flag = 'Y'
1426 AND g_encumbrance_flag = 'N') THEN
1427
1428 IF (g_account_segment IS NOT NULL OR
1429 g_balancing_segment IS NOT NULL OR
1430 g_cost_center_segment IS NOT NULL OR
1431 g_overlay_dist_code_concat IS NOT NULL) THEN
1432
1433 l_dist_ccid := nvl(x_dist_tab(i).dist_ccid,x_dist_tab(i).po_ccid);
1434
1435 IF (AP_UTILITIES_PKG.overlay_segments(
1436 g_balancing_segment,
1437 g_cost_center_segment,
1438 g_account_segment,
1439 g_overlay_dist_code_concat,
1440 l_dist_ccid,
1441 g_set_of_books_id ,
1442 'CREATE_COMB_NO_AT', --Bug 5005198
1443 l_unbuilt_flex ,
1444 l_reason_unbuilt_flex ,
1445 FND_GLOBAL.RESP_APPL_ID,
1446 FND_GLOBAL.RESP_ID,
1447 FND_GLOBAL.USER_ID,
1448 current_calling_sequence ,
1449 NULL) <> TRUE) THEN
1450
1451 l_debug_info := 'Overlaying Segments for this account was unsuccessful due to '||
1452 l_reason_unbuilt_flex;
1453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1455 END IF;
1456
1457 --Did not handle this exception explicitly as OTHERS handler
1458 --should be sufficient for this case.
1459
1460 /*OPEN ISSUE 3 */
1461 RAISE FLEX_OVERLAY_FAILED;
1462
1463 ELSE
1464
1465 x_dist_tab(i).dist_ccid := l_dist_ccid;
1466
1467 END IF;
1468
1469 /*No Overlay info provided */
1470 ELSE
1471
1472 --x_dist_tab.dist_ccid is already populated by the
1473 --calling module with the overriden account ccid, so
1474 --we need not do anything for the case of 'STD-PD' and 'CR-PD'.
1475
1476 IF (x_match_mode IN ('STD-PS','CR-PS')) THEN
1477 x_dist_tab(i).dist_ccid := nvl(g_default_dist_ccid,x_dist_tab(i).po_ccid);
1478 END IF;
1479
1480 END IF; /*IF g_account_segment is not null... */
1481
1482 ELSIF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'Y' OR
1483 g_allow_flex_override_flag = 'N' OR
1484 g_encumbrance_flag = 'Y' OR
1485 --bugfix:4668058 added the following clause
1486 (x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'N')
1487 ) THEN
1488
1489 --po_distributions_ap_v.code_combination_id is accrual account
1490 --if accruing on receipt or else charge account
1491
1492 x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1493
1494 END IF; /*IF (nvl(x_dist_tab(i).accrue_on_receipt_flag,'N') = 'N'...*/
1495
1496 --For Prepayment type invoice build the prepayment account if
1497 --the system option build_prepayment_accounts_flag is set to Y.
1498 ELSE
1499
1500 -- Contract Payments: If matching to an advance/financing pay item do not
1501 -- use the prepay ccid, use the po charge account.
1502
1503 IF g_shipment_type = 'PREPAYMENT' THEN
1504
1505 x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1506
1507 ELSE
1508
1509 IF (g_build_prepay_accts_flag = 'Y') THEN
1510
1511 l_debug_info := 'Calling build_prepay_account to build the prepayment account';
1512 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1513 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1514 END IF;
1515 -- Bug 5465722
1516 ap_matching_pkg.build_prepay_account(
1517 P_base_ccid => x_dist_tab(i).po_ccid,
1518 P_overlay_ccid => g_prepay_ccid,
1519 P_accounting_date => g_line_accounting_date,
1520 P_result_Ccid => l_dist_ccid,
1521 P_reason_unbuilt_flex => l_reason_unbuilt_flex,
1522 P_calling_sequence => current_calling_sequence);
1523
1524 IF (l_dist_ccid <> -1) THEN
1525
1526 x_dist_tab(i).dist_ccid := l_dist_ccid;
1527
1528 ELSE
1529 /*OPEN ISSUE 3 */
1530 l_debug_info := 'Flexbuild of prepayment account failed due to '
1531 ||l_reason_unbuilt_flex;
1532 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1533 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1534 END IF;
1535
1536 RAISE FLEX_OVERLAY_FAILED;
1537
1538 END IF;
1539
1540 ELSE
1541
1542 x_dist_tab(i).dist_ccid := g_prepay_ccid;
1543
1544 END IF;
1545 END IF;
1546 END IF; /*g_invoice_type_lookup_code <> 'PREPAYMENT'*/
1547
1548 END IF; /* x_dist_tab.exists(i) */
1549
1550 END LOOP;
1551
1552
1553 --Base Amount Rounding
1554 --Need to perform base_amount rounding for only foreign currency invoices.
1555 IF (g_line_base_amount <> l_sum_dist_base_amount AND g_exchange_rate IS NOT NULL AND l_rounding_index is not null) THEN
1556
1557 x_dist_tab(l_rounding_index).base_amount := x_dist_tab(l_rounding_index).base_amount +
1558 (g_line_base_amount - l_sum_dist_base_amount);
1559
1560 x_dist_tab(l_rounding_index).rounding_amt := g_line_base_amount - l_sum_dist_base_amount;
1561
1562 END IF;
1563
1564 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1565 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Dist_Proration_Info(-)');
1566 END IF;
1567
1568
1569
1570 EXCEPTION
1571
1572 WHEN OTHERS THEN
1573 IF (SQLCODE <> -20001) THEN
1574 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1575 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1576 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1577 FND_MESSAGE.SET_TOKEN('PARAMETERS','Shipment id '||to_char(x_po_line_location_id)
1578 ||', Match_mode = '||X_match_mode
1579 ||', Match Quantity = '||x_match_quantity
1580 ||', Match Amount = '||x_match_amount
1581 ||', Exchange Rate = '||g_exchange_rate
1582 ||', Base Currency = '||g_base_currency_code
1583 ||', Overbill = '||X_overbill_flag);
1584 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1585 END IF;
1586 APP_EXCEPTION.RAISE_EXCEPTION;
1587
1588 END Get_Dist_Proration_Info;
1589
1590
1591
1592 /*---------------------------------------------------------------------------+
1593 |This procedure will retrieve total_quantity to be used for the purpose |
1594 |of prorating amounts and quantities. |
1595 | |
1596 | The algorithm used is |
1597 | |
1598 | IF (matching to std invoices) THEN |
1599 | IF (this is an Overbill) THEN |
1600 | total_quantity = sum(qty_ordered) |
1601 | ELSE (this is not an Overbill) |
1602 | total_quantity = sum(qty_ordered - (qty_billed + |
1603 | qty_cancelled)) |
1604 | END |
1605 | ELSE |
1606 | total_quantity = sum(qty_billed) |
1607 | END |
1608 | |
1609 +---------------------------------------------------------------------------*/
1610 PROCEDURE Get_Total_Proration_Quantity
1611 ( X_PO_Line_Location_Id IN NUMBER,
1612 X_Match_Mode IN VARCHAR2,
1613 X_Overbill_Flag IN VARCHAR2,
1614 X_Total_Quantity OUT NOCOPY NUMBER,
1615 X_Calling_Sequence IN VARCHAR2) IS
1616
1617 l_debug_info VARCHAR2(2000);
1618 current_calling_sequence VARCHAR2(2000);
1619 l_api_name VARCHAR2(50);
1620
1621 BEGIN
1622
1623 l_api_name := 'Get_Total_Proration_Quantity';
1624 current_calling_sequence := 'Get_Total_Proration_Quantity<-'||x_calling_sequence;
1625
1626 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1627 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Total_Proration_Quantity(+)');
1628 END IF;
1629
1630 l_debug_info := 'Get total quantity for proration';
1631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1632 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1633 END IF;
1634
1635
1636 SELECT SUM(DECODE(X_Match_Mode,
1637 'STD-PS',DECODE(X_overbill_flag,
1638 'Y', NVL(pd.quantity_ordered, 0),
1639 NVL(DECODE(SIGN(pd.quantity_ordered
1640 - DECODE(PD.distribution_type,'PREPAYMENT',
1641 NVL(PD.quantity_financed,0),
1642 NVL(PD.quantity_billed,0)
1643 )
1644 - NVL(pd.quantity_cancelled,0)
1645 ),
1646 -1, 0,
1647 quantity_ordered -
1648 DECODE(PD.distribution_type,'PREPAYMENT',
1649 NVL(PD.quantity_financed,0),
1650 NVL(PD.quantity_billed,0)
1651 ) -
1652 NVL(pd.quantity_cancelled, 0)
1653 )
1654 ,0)
1655 ),
1656 DECODE(PD.distribution_type,'PREPAYMENT',
1657 NVL(PD.quantity_financed,0),
1658 NVL(PD.quantity_billed,0)
1659 )
1660 )
1661 )
1662 INTO X_Total_Quantity
1663 FROM po_distributions_ap_v PD
1664 WHERE line_location_id = X_Po_Line_Location_Id;
1665
1666 IF(x_total_quantity = 0) THEN
1667 x_total_quantity := 1;
1668 END IF;
1669
1670 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1671 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Total_Proration_Quantity(-)');
1672 END IF;
1673
1674
1675 EXCEPTION
1676 WHEN OTHERS THEN
1677 IF (SQLCODE <> -20001) THEN
1678 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1679 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1680 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1681 FND_MESSAGE.SET_TOKEN('PARAMETERS','Match_mode = '||X_match_mode
1682 ||', Shipment_id = '||TO_CHAR(X_PO_Line_Location_id)
1683 ||', Overbill = '||X_overbill_flag);
1684 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1685 END IF;
1686 APP_EXCEPTION.RAISE_EXCEPTION;
1687
1688 END Get_Total_Proration_Quantity;
1689
1690
1691
1692 Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
1693 X_Invoice_Line_Number IN NUMBER,
1694 X_Line_Type_Lookup_Code IN VARCHAR2,
1695 X_Cost_Factor_id IN NUMBER DEFAULT NULL,
1696 X_Single_Dist_Flag IN VARCHAR2 DEFAULT 'N',
1697 X_Po_Distribution_Id IN NUMBER DEFAULT NULL,
1698 X_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
1699 X_Amount IN NUMBER,
1700 X_Quantity_Invoiced IN NUMBER DEFAULT NULL,
1701 X_Unit_Price IN NUMBER DEFAULT NULL,
1702 X_Final_Match_Flag IN VARCHAR2 DEFAULT NULL,
1703 X_Item_Line_Number IN NUMBER,
1704 X_Charge_Line_Description IN VARCHAR2,
1705 X_Retained_Amount IN NUMBER DEFAULT NULL,
1706 X_Calling_Sequence IN VARCHAR2) IS
1707
1708 current_calling_sequence VARCHAR2(2000);
1709 l_debug_info VARCHAR2(2000);
1710 l_api_name VARCHAR2(50);
1711
1712 BEGIN
1713
1714 l_api_name := 'Insert_Invoice_Line';
1715 current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
1716
1717 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1718 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Line(+)');
1719 END IF;
1720
1721
1722
1723 IF (X_LINE_TYPE_LOOKUP_CODE = 'ITEM') THEN
1724
1725 l_debug_info := 'Inserting Item Line Matched to a PO';
1726 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1727 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1728 END IF;
1729
1730 -- bug 5061826
1731 -- go to base tables PO_LINES_ALL, PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
1732 -- instead of PO_LINE_LOCATIONS_AP_V and PO_DISTRIBUTIONS_AP_V
1733
1734 INSERT INTO AP_INVOICE_LINES (
1735 INVOICE_ID,
1736 LINE_NUMBER,
1737 LINE_TYPE_LOOKUP_CODE,
1738 REQUESTER_ID,
1739 DESCRIPTION,
1740 LINE_SOURCE,
1741 ORG_ID,
1742 INVENTORY_ITEM_ID,
1743 ITEM_DESCRIPTION,
1744 SERIAL_NUMBER,
1745 MANUFACTURER,
1746 MODEL_NUMBER,
1747 GENERATE_DISTS,
1748 MATCH_TYPE,
1749 DISTRIBUTION_SET_ID,
1750 ACCOUNT_SEGMENT,
1751 BALANCING_SEGMENT,
1752 COST_CENTER_SEGMENT,
1753 OVERLAY_DIST_CODE_CONCAT,
1754 DEFAULT_DIST_CCID,
1755 PRORATE_ACROSS_ALL_ITEMS,
1756 LINE_GROUP_NUMBER,
1757 ACCOUNTING_DATE,
1758 PERIOD_NAME,
1759 DEFERRED_ACCTG_FLAG,
1760 DEF_ACCTG_START_DATE,
1761 DEF_ACCTG_END_DATE,
1762 DEF_ACCTG_NUMBER_OF_PERIODS,
1763 DEF_ACCTG_PERIOD_TYPE,
1764 SET_OF_BOOKS_ID,
1765 AMOUNT,
1766 BASE_AMOUNT,
1767 ROUNDING_AMT,
1768 QUANTITY_INVOICED,
1769 UNIT_MEAS_LOOKUP_CODE,
1770 UNIT_PRICE,
1771 WFAPPROVAL_STATUS,
1772 -- USSGL_TRANSACTION_CODE, - Bug 4277744
1773 DISCARDED_FLAG,
1774 ORIGINAL_AMOUNT,
1775 ORIGINAL_BASE_AMOUNT,
1776 ORIGINAL_ROUNDING_AMT,
1777 CANCELLED_FLAG,
1778 INCOME_TAX_REGION,
1779 TYPE_1099,
1780 STAT_AMOUNT,
1781 PREPAY_INVOICE_ID,
1782 PREPAY_LINE_NUMBER,
1783 INVOICE_INCLUDES_PREPAY_FLAG,
1784 CORRECTED_INV_ID,
1785 CORRECTED_LINE_NUMBER,
1786 PO_HEADER_ID,
1787 PO_LINE_ID,
1788 PO_RELEASE_ID,
1789 PO_LINE_LOCATION_ID,
1790 PO_DISTRIBUTION_ID,
1791 RCV_TRANSACTION_ID,
1792 FINAL_MATCH_FLAG,
1793 ASSETS_TRACKING_FLAG,
1794 ASSET_BOOK_TYPE_CODE,
1795 ASSET_CATEGORY_ID,
1796 PROJECT_ID,
1797 TASK_ID,
1798 EXPENDITURE_TYPE,
1799 EXPENDITURE_ITEM_DATE,
1800 EXPENDITURE_ORGANIZATION_ID,
1801 PA_QUANTITY,
1802 PA_CC_AR_INVOICE_ID,
1803 PA_CC_AR_INVOICE_LINE_NUM,
1804 PA_CC_PROCESSED_CODE,
1805 AWARD_ID,
1806 AWT_GROUP_ID,
1807 REFERENCE_1,
1808 REFERENCE_2,
1809 RECEIPT_VERIFIED_FLAG,
1810 RECEIPT_REQUIRED_FLAG,
1811 RECEIPT_MISSING_FLAG,
1812 JUSTIFICATION,
1813 EXPENSE_GROUP,
1814 START_EXPENSE_DATE,
1815 END_EXPENSE_DATE,
1816 RECEIPT_CURRENCY_CODE,
1817 RECEIPT_CONVERSION_RATE,
1818 RECEIPT_CURRENCY_AMOUNT,
1819 DAILY_AMOUNT,
1820 WEB_PARAMETER_ID,
1821 ADJUSTMENT_REASON,
1822 MERCHANT_DOCUMENT_NUMBER,
1823 MERCHANT_NAME,
1824 MERCHANT_REFERENCE,
1825 MERCHANT_TAX_REG_NUMBER,
1826 MERCHANT_TAXPAYER_ID,
1827 COUNTRY_OF_SUPPLY,
1828 CREDIT_CARD_TRX_ID,
1829 COMPANY_PREPAID_INVOICE_ID,
1830 CC_REVERSAL_FLAG,
1831 ATTRIBUTE_CATEGORY,
1832 ATTRIBUTE1,
1833 ATTRIBUTE2,
1834 ATTRIBUTE3,
1835 ATTRIBUTE4,
1836 ATTRIBUTE5,
1837 ATTRIBUTE6,
1838 ATTRIBUTE7,
1839 ATTRIBUTE8,
1840 ATTRIBUTE9,
1841 ATTRIBUTE10,
1842 ATTRIBUTE11,
1843 ATTRIBUTE12,
1844 ATTRIBUTE13,
1845 ATTRIBUTE14,
1846 ATTRIBUTE15,
1847 /* GLOBAL_ATTRIBUTE_CATEGORY,
1848 GLOBAL_ATTRIBUTE1,
1849 GLOBAL_ATTRIBUTE2,
1850 GLOBAL_ATTRIBUTE3,
1851 GLOBAL_ATTRIBUTE4,
1852 GLOBAL_ATTRIBUTE5,
1853 GLOBAL_ATTRIBUTE6,
1854 GLOBAL_ATTRIBUTE7,
1855 GLOBAL_ATTRIBUTE8,
1856 GLOBAL_ATTRIBUTE9,
1857 GLOBAL_ATTRIBUTE10,
1858 GLOBAL_ATTRIBUTE11,
1859 GLOBAL_ATTRIBUTE12,
1860 GLOBAL_ATTRIBUTE13,
1861 GLOBAL_ATTRIBUTE14,
1862 GLOBAL_ATTRIBUTE15,
1863 GLOBAL_ATTRIBUTE16,
1864 GLOBAL_ATTRIBUTE17,
1865 GLOBAL_ATTRIBUTE18,
1866 GLOBAL_ATTRIBUTE19,
1867 GLOBAL_ATTRIBUTE20, */
1868 CREATION_DATE,
1869 CREATED_BY,
1870 LAST_UPDATED_BY,
1871 LAST_UPDATE_DATE,
1872 LAST_UPDATE_LOGIN,
1873 PROGRAM_APPLICATION_ID,
1874 PROGRAM_ID,
1875 PROGRAM_UPDATE_DATE,
1876 REQUEST_ID,
1877 RETAINED_AMOUNT,
1878 RETAINED_AMOUNT_REMAINING,
1879 SHIP_TO_LOCATION_ID,
1880 PRIMARY_INTENDED_USE,
1881 PRODUCT_FISC_CLASSIFICATION,
1882 TRX_BUSINESS_CATEGORY,
1883 PRODUCT_TYPE,
1884 PRODUCT_CATEGORY,
1885 USER_DEFINED_FISC_CLASS,
1886 ASSESSABLE_VALUE,
1887 tax_classification_code,
1888 pay_awt_group_id) --bug8222382
1889 SELECT X_INVOICE_ID, --invoice_id
1890 X_INVOICE_LINE_NUMBER, --invoice_line_number
1891 X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
1892 DECODE(X_SINGLE_DIST_FLAG,'Y',
1893 PD.DELIVER_TO_PERSON_ID,NULL),--requester_id
1894 --bug 5061826,5601344 (added nvl)
1895 NVL(PLL.DESCRIPTION,PL.ITEM_DESCRIPTION), --description
1896 'HEADER MATCH', --line_source
1897 PLL.ORG_ID, --org_id
1898 PL.ITEM_ID, --inventory_item_id --bug 5061826-PLL to PL
1899 NVL(PLL.DESCRIPTION,PL.ITEM_DESCRIPTION), --item_description
1900 NULL, --serial_number
1901 NULL, --manufacturer
1902 NULL, --model_number
1903 'D', --generate_dists
1904 'ITEM_TO_PO', --match_type
1905 NULL, --distribution_set_id
1906 NULL, --account_segment
1907 NULL, --balancing_segment
1908 NULL, --cost_center_segment
1909 NULL, --overlay_dist_code_concat
1910 --Bug6965650
1911 NULL, --default_dist_ccid
1912 'N', --prorate_across_all_items
1913 NULL, --line_group_number
1914 G_ACCOUNTING_DATE, --accounting_date
1915 G_PERIOD_NAME, --period_name
1916 'N', --deferred_acctg_flag
1917 NULL, --def_acctg_start_date
1918 NULL, --def_acctg_end_date
1919 NULL, --def_acctg_number_of_periods
1920 NULL, --def_acctg_period_type
1921 G_SET_OF_BOOKS_ID, --set_of_books_id
1922 X_AMOUNT, --amount
1923 AP_UTILITIES_PKG.Ap_Round_Currency(
1924 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
1925 G_BASE_CURRENCY_CODE), --base_amount
1926 NULL, --rounding_amount
1927 X_QUANTITY_INVOICED, --quantity_invoiced
1928 PLL.UNIT_MEAS_LOOKUP_CODE, --unit_meas_lookup_code
1929 X_UNIT_PRICE, --unit_price
1930 decode(g_approval_workflow_flag,'Y'
1931 ,'REQUIRED','NOT REQUIRED'),--wf_approval_status
1932 -- Removed for bug 4277744
1933 -- PLL.USSGL_TRANSACTION_CODE, --ussgl_transaction_code
1934 'N', --discarded_flag
1935 NULL, --original_amount
1936 NULL, --original_base_amount
1937 NULL, --original_rounding_amt
1938 'N', --cancelled_flag
1939 G_INCOME_TAX_REGION, --income_tax_region
1940 PL.TYPE_1099, --type_1099 -- bug 5061826-PLL to PL
1941 NULL, --stat_amount
1942 NULL, --prepay_invoice_id
1943 NULL, --prepay_line_number
1944 NULL, --invoice_includes_prepay_flag
1945 NULL, --corrected_inv_id
1946 NULL, --corrected_line_number
1947 PLL.PO_HEADER_ID, --po_header_id
1948 PLL.PO_LINE_ID, --po_line_id
1949 PLL.PO_RELEASE_ID, --po_release_id
1950 PLL.LINE_LOCATION_ID, --po_line_location_id
1951 DECODE(X_SINGLE_DIST_FLAG,'Y',
1952 X_PO_DISTRIBUTION_ID,NULL),--po_distribution_id
1953 NULL, --rcv_transaction_id
1954 X_FINAL_MATCH_FLAG, --final_match_flag
1955 'N', --assets_tracking_flag
1956 G_ASSET_BOOK_TYPE_CODE, --asset_book_type_code
1957 MSI.ASSET_CATEGORY_ID, --asset_category_id
1958 DECODE(X_SINGLE_DIST_FLAG,'Y',
1959 DECODE(PD.destination_type_code,
1960 'EXPENSE',PD.project_id,
1961 G_PROJECT_ID),
1962 NULL), --project_id
1963 DECODE(X_SINGLE_DIST_FLAG,'Y',
1964 DECODE(PD.destination_type_code,
1965 'EXPENSE',PD.task_id,
1966 G_TASK_ID),
1967 NULL), --task_id
1968 DECODE(X_SINGLE_DIST_FLAG,'Y',
1969 DECODE(PD.destination_type_code,
1970 'EXPENSE',PD.expenditure_type,
1971 G_EXPENDITURE_TYPE),
1972 NULL), --expenditure_type
1973 DECODE(X_SINGLE_DIST_FLAG,'Y',
1974 -- Bug 5294998. Calling project API
1975 PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
1976 g_invoice_date,
1977 g_accounting_date,
1978 NULL,
1979 sysdate,
1980 x_po_distribution_id,
1981 'PO-MATCH'),
1982 NULL), --expenditure_item_date
1983 /* DECODE(g_pa_expenditure_date_default,
1984 'PO Expenditure Item Date/Transaction Date',
1985 DECODE(PD.Destination_type_code,
1986 'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
1987 G_INVOICE_DATE),
1988 'PO Expenditure Item Date/Transaction GL Date',
1989 DECODE(PD.destination_type_code,
1990 'EXPENSE', PD.EXPENDITURE_ITEM_DATE,
1991 G_ACCOUNTING_DATE),
1992 'PO Expenditure Item Date/Transaction System Date',
1993 DECODE(PD.destination_type_code,
1994 'EXPENSE',PD.EXPENDITURE_ITEM_DATE,
1995 SYSDATE),
1996 'Receipt Date/Transaction Date',G_INVOICE_DATE,
1997 'Receipt Date/Transaction GL Date',G_ACCOUNTING_DATE,
1998 'Receipt Date/Transaction System Date',SYSDATE,
1999 'Transaction Date',G_INVOICE_DATE,
2000 'Transaction GL Date',G_ACCOUNTING_DATE,
2001 'Transaction System Date', SYSDATE), /
2002 NULL), */
2003 DECODE(X_SINGLE_DIST_FLAG,'Y',
2004 DECODE(PD.destination_type_code,
2005 'EXPENSE',PD.expenditure_organization_id,
2006 G_EXPENDITURE_ORGANIZATION_ID),
2007 NULL), --expenditure_organization_id
2008 DECODE( DECODE(X_SINGLE_DIST_FLAG,'Y',
2009 DECODE(PD.destination_type_code,
2010 'EXPENSE',PD.project_id,
2011 G_PROJECT_ID),
2012 NULL),
2013 '','',x_quantity_invoiced), --pa_quantity
2014 NULL, --pa_cc_ar_invoice_id
2015 NULL, --pa_cc_ar_invoice_line_num
2016 NULL, --pa_cc_processed_code
2017 DECODE(X_SINGLE_DIST_FLAG,
2018 'Y', nvl(gms_ap_api.get_distribution_award(PD.AWARD_ID), G_AWARD_ID),
2019 NULL), --award_id
2020 G_AWT_GROUP_ID, --awt_group_id
2021 NULL, --reference_1
2022 NULL, --reference_2
2023 NULL, --receipt_verified_flag
2024 NULL, --receipt_required_flag
2025 NULL, --receipt_missing_flag
2026 NULL, --justification
2027 NULL, --expense_group
2028 NULL, --start_expense_date
2029 NULL, --end_expense_date
2030 NULL, --receipt_currency_code
2031 NULL, --receipt_conversion_rate
2032 NULL, --receipt_currency_amount
2033 NULL, --daily_amount
2034 NULL, --web_parameter_id
2035 NULL, --adjustment_reason
2036 NULL, --merchant_document_number
2037 NULL, --merchant_name
2038 NULL, --merchant_reference
2039 NULL, --merchant_tax_reg_number
2040 NULL, --merchant_taxpayer_id
2041 NULL, --country_of_supply
2042 NULL, --credit_card_trx_id
2043 NULL, --company_prepaid_invoice_id
2044 NULL, --cc_reversal_flag
2045 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute_category),''),--attribute_category
2046 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute1),''), --attribute1
2047 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute2),''), --attribute2
2048 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute3),''), --attribute3
2049 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute4),''), --attribute4
2050 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute5),''), --attribute5
2051 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute6),''), --attribute6
2052 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute7),''), --attribute7
2053 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute8),''), --attribute8
2054 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute9),''), --attribute9
2055 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute10),''), --attribute10
2056 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute11),''), --attribute11
2057 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute12),''), --attribute12
2058 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute13),''), --attribute13
2059 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute14),''), --attribute14
2060 NVL(DECODE(g_transfer_flag,'Y',PLL.attribute15),''), --attribute15
2061 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
2062 X_GLOBAL_ATTRIBUTE1, --global_attribute1
2063 X_GLOBAL_ATTRIBUTE2, --global_attribute2
2064 X_GLOBAL_ATTRIBUTE3, --global_attribute3
2065 X_GLOBAL_ATTRIBUTE4, --global_attribute4
2066 X_GLOBAL_ATTRIBUTE5, --global_attribute5
2067 X_GLOBAL_ATTRIBUTE6, --global_attribute6
2068 X_GLOBAL_ATTRIBUTE7, --global_attribute7
2069 X_GLOBAL_ATTRIBUTE8, --global_attribute8
2070 X_GLOBAL_ATTRIBUTE9, --global_attribute9
2071 X_GLOBAL_ATTRIBUTE10, --global_attribute10
2072 X_GLOBAL_ATTRIBUTE11, --global_attribute11
2073 X_GLOBAL_ATTRIBUTE12, --global_attribute12
2074 X_GLOBAL_ATTRIBUTE13, --global_attribute13
2075 X_GLOBAL_ATTRIBUTE14, --global_attribute14
2076 X_GLOBAL_ATTRIBUTE15, --global_attribute15
2077 X_GLOBAL_ATTRIBUTE16, --global_attribute16
2078 X_GLOBAL_ATTRIBUTE17, --global_attribute17
2079 X_GLOBAL_ATTRIBUTE18, --global_attribute18
2080 X_GLOBAL_ATTRIBUTE19, --global_attribute19
2081 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
2082 SYSDATE, --creation_date
2083 G_USER_ID, --created_by
2084 G_USER_ID, --last_update_by
2085 SYSDATE, --last_update_date
2086 G_LOGIN_ID, --last_update_login
2087 NULL, --program_application_id
2088 NULL, --program_id
2089 NULL, --program_update_date
2090 NULL, --request_id
2091 X_RETAINED_AMOUNT, --retained_amount
2092 (-X_RETAINED_AMOUNT), --retained_amount_remaining
2093 PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
2094 --bugfix:5565310
2095 G_INTENDED_USE, --primary_intended_use
2096 G_PRODUCT_FISC_CLASS, --product_fisc_classification
2097 G_TRX_BUSINESS_CATEGORY, --trx_business_category
2098 G_PRODUCT_TYPE, --product_type
2099 G_PRODUCT_CATEGORY, --product_category
2100 G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
2101 G_ASSESSABLE_VALUE,
2102 G_dflt_tax_class_code,
2103 G_PAY_AWT_GROUP_ID --pay_awt_group_id bug8222382
2104 -- bug 5061826 -- new FROM clause that goes to base tables
2105 FROM PO_LINES_ALL PL,
2106 PO_LINE_LOCATIONS_ALL PLL,
2107 po_distributions pd,
2108 mtl_system_items msi
2109 WHERE pll.line_location_id = x_po_line_location_id
2110 and pd.line_location_id = pll.line_location_id
2111 AND PLL.PO_LINE_ID = PL.PO_LINE_ID
2112 and pd.po_distribution_id = nvl(x_po_distribution_id,pd.po_distribution_id)
2113 and msi.inventory_item_id(+) = pl.item_id
2114 and msi.organization_id(+) = g_inventory_organization_id
2115 and rownum = 1;
2116 /* -- bug 5061826 -- commented out older FROM clause
2117 FROM PO_LINE_LOCATIONS_AP_V PLL,
2118 PO_DISTRIBUTIONS_AP_V PD,
2119 MTL_SYSTEM_ITEMS MSI
2120 WHERE PLL.LINE_LOCATION_ID = X_PO_LINE_LOCATION_ID
2121 AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
2122 AND PD.PO_DISTRIBUTION_ID = NVL(X_PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID)
2123 AND MSI.INVENTORY_ITEM_ID(+) = PLL.ITEM_ID
2124 AND MSI.ORGANIZATION_ID(+) = G_INVENTORY_ORGANIZATION_ID
2125 AND ROWNUM = 1; */
2126
2127
2128 /* for charge lines (frt and misc) allocated during matching */
2129 ELSIF (x_line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')) THEN
2130
2131 l_debug_info := 'Inserting Charge Line';
2132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2133 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2134 END IF;
2135
2136 INSERT INTO AP_INVOICE_LINES (
2137 INVOICE_ID,
2138 LINE_NUMBER,
2139 LINE_TYPE_LOOKUP_CODE,
2140 REQUESTER_ID,
2141 DESCRIPTION,
2142 LINE_SOURCE,
2143 ORG_ID,
2144 INVENTORY_ITEM_ID,
2145 ITEM_DESCRIPTION,
2146 SERIAL_NUMBER,
2147 MANUFACTURER,
2148 MODEL_NUMBER,
2149 GENERATE_DISTS,
2150 MATCH_TYPE,
2151 DISTRIBUTION_SET_ID,
2152 ACCOUNT_SEGMENT,
2153 BALANCING_SEGMENT,
2154 COST_CENTER_SEGMENT,
2155 OVERLAY_DIST_CODE_CONCAT,
2156 DEFAULT_DIST_CCID,
2157 PRORATE_ACROSS_ALL_ITEMS,
2158 LINE_GROUP_NUMBER,
2159 ACCOUNTING_DATE,
2160 PERIOD_NAME,
2161 DEFERRED_ACCTG_FLAG,
2162 DEF_ACCTG_START_DATE,
2163 DEF_ACCTG_END_DATE,
2164 DEF_ACCTG_NUMBER_OF_PERIODS,
2165 DEF_ACCTG_PERIOD_TYPE,
2166 SET_OF_BOOKS_ID,
2167 AMOUNT,
2168 BASE_AMOUNT,
2169 ROUNDING_AMT,
2170 QUANTITY_INVOICED,
2171 UNIT_MEAS_LOOKUP_CODE,
2172 UNIT_PRICE,
2173 WFAPPROVAL_STATUS,
2174 -- USSGL_TRANSACTION_CODE, - Bug 4277744
2175 DISCARDED_FLAG,
2176 ORIGINAL_AMOUNT,
2177 ORIGINAL_BASE_AMOUNT,
2178 ORIGINAL_ROUNDING_AMT,
2179 CANCELLED_FLAG,
2180 INCOME_TAX_REGION,
2181 TYPE_1099,
2182 STAT_AMOUNT,
2183 PREPAY_INVOICE_ID,
2184 PREPAY_LINE_NUMBER,
2185 INVOICE_INCLUDES_PREPAY_FLAG,
2186 CORRECTED_INV_ID,
2187 CORRECTED_LINE_NUMBER,
2188 PO_HEADER_ID,
2189 PO_LINE_ID,
2190 PO_RELEASE_ID,
2191 PO_LINE_LOCATION_ID,
2192 PO_DISTRIBUTION_ID,
2193 RCV_TRANSACTION_ID,
2194 FINAL_MATCH_FLAG,
2195 ASSETS_TRACKING_FLAG,
2196 ASSET_BOOK_TYPE_CODE,
2197 ASSET_CATEGORY_ID,
2198 PROJECT_ID,
2199 TASK_ID,
2200 EXPENDITURE_TYPE,
2201 EXPENDITURE_ITEM_DATE,
2202 EXPENDITURE_ORGANIZATION_ID,
2203 PA_QUANTITY,
2204 PA_CC_AR_INVOICE_ID,
2205 PA_CC_AR_INVOICE_LINE_NUM,
2206 PA_CC_PROCESSED_CODE,
2207 AWARD_ID,
2208 AWT_GROUP_ID,
2209 REFERENCE_1,
2210 REFERENCE_2,
2211 RECEIPT_VERIFIED_FLAG,
2212 RECEIPT_REQUIRED_FLAG,
2213 RECEIPT_MISSING_FLAG,
2214 JUSTIFICATION,
2215 EXPENSE_GROUP,
2216 START_EXPENSE_DATE,
2217 END_EXPENSE_DATE,
2218 RECEIPT_CURRENCY_CODE,
2219 RECEIPT_CONVERSION_RATE,
2220 RECEIPT_CURRENCY_AMOUNT,
2221 DAILY_AMOUNT,
2222 WEB_PARAMETER_ID,
2223 ADJUSTMENT_REASON,
2224 MERCHANT_DOCUMENT_NUMBER,
2225 MERCHANT_NAME,
2226 MERCHANT_REFERENCE,
2227 MERCHANT_TAX_REG_NUMBER,
2228 MERCHANT_TAXPAYER_ID,
2229 COUNTRY_OF_SUPPLY,
2230 CREDIT_CARD_TRX_ID,
2231 COMPANY_PREPAID_INVOICE_ID,
2232 CC_REVERSAL_FLAG,
2233 ATTRIBUTE_CATEGORY,
2234 ATTRIBUTE1,
2235 ATTRIBUTE2,
2236 ATTRIBUTE3,
2237 ATTRIBUTE4,
2238 ATTRIBUTE5,
2239 ATTRIBUTE6,
2240 ATTRIBUTE7,
2241 ATTRIBUTE8,
2242 ATTRIBUTE9,
2243 ATTRIBUTE10,
2244 ATTRIBUTE11,
2245 ATTRIBUTE12,
2246 ATTRIBUTE13,
2247 ATTRIBUTE14,
2248 ATTRIBUTE15,
2249 /* GLOBAL_ATTRIBUTE_CATEGORY,
2250 GLOBAL_ATTRIBUTE1,
2251 GLOBAL_ATTRIBUTE2,
2252 GLOBAL_ATTRIBUTE3,
2253 GLOBAL_ATTRIBUTE4,
2254 GLOBAL_ATTRIBUTE5,
2255 GLOBAL_ATTRIBUTE6,
2256 GLOBAL_ATTRIBUTE7,
2257 GLOBAL_ATTRIBUTE8,
2258 GLOBAL_ATTRIBUTE9,
2259 GLOBAL_ATTRIBUTE10,
2260 GLOBAL_ATTRIBUTE11,
2261 GLOBAL_ATTRIBUTE12,
2262 GLOBAL_ATTRIBUTE13,
2263 GLOBAL_ATTRIBUTE14,
2264 GLOBAL_ATTRIBUTE15,
2265 GLOBAL_ATTRIBUTE16,
2266 GLOBAL_ATTRIBUTE17,
2267 GLOBAL_ATTRIBUTE18,
2268 GLOBAL_ATTRIBUTE19,
2269 GLOBAL_ATTRIBUTE20, */
2270 CREATION_DATE,
2271 CREATED_BY,
2272 LAST_UPDATED_BY,
2273 LAST_UPDATE_DATE,
2274 LAST_UPDATE_LOGIN,
2275 PROGRAM_APPLICATION_ID,
2276 PROGRAM_ID,
2277 PROGRAM_UPDATE_DATE,
2278 REQUEST_ID,
2279 RETAINED_AMOUNT,
2280 RETAINED_AMOUNT_REMAINING,
2281 SHIP_TO_LOCATION_ID,
2282 --bugfix:5565310
2283 PRIMARY_INTENDED_USE,
2284 PRODUCT_FISC_CLASSIFICATION,
2285 TRX_BUSINESS_CATEGORY,
2286 PRODUCT_TYPE,
2287 PRODUCT_CATEGORY,
2288 USER_DEFINED_FISC_CLASS,
2289 ASSESSABLE_VALUE,
2290 TAX_CLASSIFICATION_CODE,
2291 COST_FACTOR_ID,
2292 PAY_AWT_GROUP_ID --bug 8222382
2293 )
2294 SELECT X_INVOICE_ID, --invoice_id
2295 X_INVOICE_LINE_NUMBER, --invoice_line_number
2296 X_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
2297 AIL.REQUESTER_ID, --requester_id
2298 --bug 5102208
2299 SUBSTRB(X_CHARGE_LINE_DESCRIPTION || AIL.description, 1, 240),--description
2300 'CHRG ITEM MATCH', --line_source
2301 AIL.ORG_ID, --org_id
2302 NULL, --inventory_item_id
2303 NULL, --item_description
2304 NULL, --serial_number
2305 NULL, --manufacturer
2306 NULL, --model_number
2307 'Y', --generate_dists
2308 'NOT_MATCHED', --match_type
2309 NULL, --distribution_set_id
2310 NULL, --account_segment
2311 NULL, --balancing_segment
2312 NULL, --cost_center_segment
2313 NULL, --overlay_dist_code_concat
2314 --Bug6965650
2315 NULL, --default_dist_ccid
2316 'N', --prorate_across_all_items
2317 NULL, --line_group_number
2318 AIL.ACCOUNTING_DATE, --accounting_date
2319 AIL.PERIOD_NAME, --period_name
2320 'N', --deferred_acctg_flag
2321 NULL, --deferred_acctg_start_date
2322 NULL, --deferred_acctg_end_date
2323 NULL, --def_acctg_number_of_periods
2324 NULL, --def_acctg_period_type
2325 AIL.SET_OF_BOOKS_ID, --set_of_books_id
2326 X_AMOUNT, --amount
2327 AP_UTILITIES_PKG.Ap_Round_Currency(
2328 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
2329 G_BASE_CURRENCY_CODE), --base_amount
2330 NULL, --rounding_amount
2331 NULL, --quantity_invoiced
2332 NULL, --unit_meas_lookup_code
2333 NULL, --unit_price
2334 AIL.WFAPPROVAL_STATUS, --wf_approval_status
2335 -- Removed for bug 4277744
2336 -- NULL, --ussgl_transaction_code
2337 'N', --discarded_flag
2338 NULL, --original_amount
2339 NULL, --original_base_amount
2340 NULL, --original_rounding_amt
2341 'N', --cancelled_flag
2342 AIL.INCOME_TAX_REGION, --income_tax_region
2343 AIL.TYPE_1099, --type_1099
2344 NULL, --stat_amount
2345 NULL, --prepay_invoice_id
2346 NULL, --prepay_line_number
2347 NULL, --invoice_includes_prepay_flag
2348 NULL, --corrected_inv_id
2349 NULL, --corrected_line_number
2350 NULL, --po_header_id
2351 NULL, --po_line_id
2352 NULL, --po_release_id
2353 NULL, --po_line_location_id
2354 NULL, --po_distribution_id
2355 NULL, --rcv_transaction_id
2356 'N', --final_match_flag
2357 'N', --assets_tracking_flag
2358 NULL, --asset_book_type_code
2359 NULL, --asset_category_id
2360 AIL.PROJECT_ID, --project_id
2361 AIL.TASK_ID, --task_id
2362 AIL.EXPENDITURE_TYPE, --expenditure_type
2363 AIL.EXPENDITURE_ITEM_DATE, --expenditure_item_date
2364 AIL.EXPENDITURE_ORGANIZATION_ID, --expenditure_organization_id
2365 NULL, --pa_quantity
2366 NULL, --pa_cc_Ar_invoice_id
2367 NULL, --pa_cc_Ar_invoice_line_num
2368 NULL, --pa_cc_processed_code
2369 AIL.AWARD_ID, --award_id
2370 AIL.AWT_GROUP_ID, --awt_group_id
2371 NULL, --reference_1
2372 NULL, --reference_2
2373 NULL, --receipt_verified_flag
2374 NULL, --receipt_required_flag
2375 NULL, --receipt_missing_flag
2376 NULL, --justification
2377 NULL, --expense_group
2378 NULL, --start_expense_date
2379 NULL, --end_expense_date
2380 NULL, --receipt_currency_code
2381 NULL, --receipt_conversion_rate
2382 NULL, --receipt_currency_amount
2383 NULL, --daily_amount
2384 NULL, --web_parameter_id
2385 NULL, --adjustment_reason
2386 NULL, --merchant_document_number
2387 NULL, --merchant_name
2388 NULL, --merchant_reference
2389 NULL, --merchant_tax_reg_number
2390 NULL, --merchant_taxpayer_id
2391 NULL, --country_of_supply
2392 NULL, --credit_card_trx_id
2393 NULL, --company_prepaid_invoice_id
2394 NULL, --cc_reversal_flag
2395 AIL.attribute_category, --attribute_category
2396 AIL.attribute1, --attribute1
2397 AIL.attribute2, --attribute2
2398 AIL.attribute3, --attribute3
2399 AIL.attribute4, --attribute4
2400 AIL.attribute5, --attribute5
2401 AIL.attribute6, --attribute6
2402 AIL.attribute7, --attribute7
2403 AIL.attribute8, --attribute8
2404 AIL.attribute9, --attribute9
2405 AIL.attribute10, --attribute10
2406 AIL.attribute11, --attribute11
2407 AIL.attribute12, --attribute12
2408 AIL.attribute13, --attribute13
2409 AIL.attribute14, --attribute14
2410 AIL.attribute15, --attribute15
2411 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
2412 X_GLOBAL_ATTRIBUTE1, --global_attribute1
2413 X_GLOBAL_ATTRIBUTE2, --global_attribute2
2414 X_GLOBAL_ATTRIBUTE3, --global_attribute3
2415 X_GLOBAL_ATTRIBUTE4, --global_attribute4
2416 X_GLOBAL_ATTRIBUTE5, --global_attribute5
2417 X_GLOBAL_ATTRIBUTE6, --global_attribute6
2418 X_GLOBAL_ATTRIBUTE7, --global_attribute7
2419 X_GLOBAL_ATTRIBUTE8, --global_attribute8
2420 X_GLOBAL_ATTRIBUTE9, --global_attribute9
2421 X_GLOBAL_ATTRIBUTE10, --global_attribute10
2422 X_GLOBAL_ATTRIBUTE11, --global_attribute11
2423 X_GLOBAL_ATTRIBUTE12, --global_attribute12
2424 X_GLOBAL_ATTRIBUTE13, --global_attribute13
2425 X_GLOBAL_ATTRIBUTE14, --global_attribute14
2426 X_GLOBAL_ATTRIBUTE15, --global_attribute15
2427 X_GLOBAL_ATTRIBUTE16, --global_attribute16
2428 X_GLOBAL_ATTRIBUTE17, --global_attribute17
2429 X_GLOBAL_ATTRIBUTE18, --global_attribute18
2430 X_GLOBAL_ATTRIBUTE19, --global_attribute19
2431 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
2432 SYSDATE, --creation_date
2433 G_USER_ID, --created_by
2434 G_USER_ID, --last_updated_by
2435 SYSDATE, --last_updated_date
2436 G_LOGIN_ID, --last_update_login
2437 NULL, --program_application_id
2438 NULL, --program_id
2439 NULL, --program_update_date
2440 NULL, --request_id
2441 X_RETAINED_AMOUNT, --retained_amount
2442 -(X_RETAINED_AMOUNT), --retained_amount_remaining
2443 AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
2444 --bugfix:5565310
2445 G_INTENDED_USE, --primary_intended_use
2446 G_PRODUCT_FISC_CLASS, --product_fisc_classification
2447 G_TRX_BUSINESS_CATEGORY, --trx_business_category
2448 G_PRODUCT_TYPE, --product_type
2449 G_PRODUCT_CATEGORY, --product_category
2450 G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
2451 G_ASSESSABLE_VALUE,
2452 G_dflt_tax_class_code,
2453 X_COST_FACTOR_ID, --cost_factor_id
2454 AIL.PAY_AWT_GROUP_ID --pay_awt_group_id bug8222382
2455 FROM AP_INVOICE_LINES AIL
2456 WHERE AIL.INVOICE_ID = X_INVOICE_ID
2457 AND AIL.LINE_NUMBER = X_ITEM_LINE_NUMBER;
2458
2459 END IF;
2460
2461 g_max_invoice_line_number := g_max_invoice_line_number + 1;
2462
2463 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2464 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Line(-)');
2465 END IF;
2466
2467
2468 EXCEPTION WHEN OTHERS THEN
2469 IF (SQLCODE <> -20001) THEN
2470 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2471 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2472 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2473 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
2474 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
2475 ||', PO Distribution Id = '||to_char(x_po_distribution_id)
2476 ||', Project Id = '||to_char(g_project_id)
2477 ||', Task_Id ='||to_char(g_task_id)
2478 ||', Expenditure Type ='||g_expenditure_type
2479 ||', Expenditure_Organization_id ='||to_char(g_expenditure_organization_id));
2480 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2481 END IF;
2482 APP_EXCEPTION.RAISE_EXCEPTION;
2483
2484 END Insert_Invoice_Line;
2485
2486
2487
2488 PROCEDURE Insert_Invoice_Distributions (
2489 X_Invoice_ID IN NUMBER,
2490 X_Invoice_Line_Number IN NUMBER,
2491 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
2492 X_Final_Match_Flag IN VARCHAR2,
2493 X_Unit_Price IN NUMBER,
2494 X_Total_Amount IN NUMBER,
2495 X_Calling_Sequence IN VARCHAR2) IS
2496
2497 i NUMBER;
2498 l_distribution_line_number NUMBER := 1;
2499 l_debug_info VARCHAR2(2000);
2500 current_calling_sequence VARCHAR2(2000);
2501 l_api_name VARCHAR2(50);
2502 l_copy_line_dff_flag VARCHAR2(1); -- Bug 6837035
2503
2504 BEGIN
2505 l_api_name := 'Insert_Invoice_Distributions';
2506
2507 current_calling_sequence := 'Insert_Invoice_Distributions <-'||x_calling_sequence;
2508 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2509 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Distributions(+)');
2510 END IF;
2511
2512 l_debug_info := 'Insert Invoice Distributions';
2513 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2514 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2515 END IF;
2516
2517 -- Bug 6837035 Retrieve the profile value to check if the DFF info should be
2518 -- copied onto distributions for imported lines.
2519 l_copy_line_dff_flag := NVL(fnd_profile.value('AP_COPY_INV_LINE_DFF'),'N');
2520 FOR i in nvl(X_Dist_tab.FIRST, 0) .. nvl(X_Dist_tab.LAST, 0) LOOP
2521
2522 IF (x_dist_tab.exists(i)) THEN
2523
2524 l_debug_info := 'Insert invoice distribution corresponding to po_distribution : '||x_dist_tab(i).po_distribution_id;
2525 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2526 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2527 END IF;
2528
2529 INSERT INTO ap_invoice_distributions (
2530 batch_id,
2531 invoice_id,
2532 invoice_line_number,
2533 invoice_distribution_id,
2534 distribution_line_number,
2535 line_type_lookup_code,
2536 description,
2537 dist_match_type,
2538 distribution_class,
2539 org_id,
2540 dist_code_combination_id,
2541 accounting_date,
2542 period_name,
2543 amount_to_post,
2544 base_amount_to_post,
2545 posted_amount,
2546 posted_base_amount,
2547 je_batch_id,
2548 cash_je_batch_id,
2549 posted_flag,
2550 accounting_event_id,
2551 upgrade_posted_amt,
2552 upgrade_base_posted_amt,
2553 set_of_books_id,
2554 amount,
2555 base_amount,
2556 rounding_amt,
2557 match_status_flag,
2558 encumbered_flag,
2559 packet_id,
2560 -- ussgl_transaction_code, - Bug 4277744
2561 -- ussgl_trx_code_context, - Bug 4277744
2562 reversal_flag,
2563 parent_reversal_id,
2564 cancellation_flag,
2565 income_tax_region,
2566 type_1099,
2567 stat_amount,
2568 charge_applicable_to_dist_id,
2569 prepay_amount_remaining,
2570 prepay_distribution_id,
2571 parent_invoice_id,
2572 corrected_invoice_dist_id,
2573 corrected_quantity,
2574 other_invoice_id,
2575 po_distribution_id,
2576 rcv_transaction_id,
2577 unit_price,
2578 matched_uom_lookup_code,
2579 quantity_invoiced,
2580 final_match_flag,
2581 related_id,
2582 assets_addition_flag,
2583 assets_tracking_flag,
2584 asset_book_type_code,
2585 asset_category_id,
2586 project_id,
2587 task_id,
2588 expenditure_type,
2589 expenditure_item_date,
2590 expenditure_organization_id,
2591 pa_quantity,
2592 pa_addition_flag,
2593 pa_cc_ar_invoice_id,
2594 pa_cc_ar_invoice_line_num,
2595 pa_cc_processed_code,
2596 award_id,
2597 gms_burdenable_raw_cost,
2598 awt_flag,
2599 awt_group_id,
2600 awt_tax_rate_id,
2601 awt_gross_amount,
2602 awt_invoice_id,
2603 awt_origin_group_id,
2604 awt_invoice_payment_id,
2605 awt_withheld_amt,
2606 inventory_transfer_status,
2607 reference_1,
2608 reference_2,
2609 receipt_verified_flag,
2610 receipt_required_flag,
2611 receipt_missing_flag,
2612 justification,
2613 expense_group,
2614 start_expense_date,
2615 end_expense_date,
2616 receipt_currency_code,
2617 receipt_conversion_rate,
2618 receipt_currency_amount,
2619 daily_amount,
2620 web_parameter_id,
2621 adjustment_reason,
2622 merchant_document_number,
2623 merchant_name,
2624 merchant_reference,
2625 merchant_tax_reg_number,
2626 merchant_taxpayer_id,
2627 country_of_supply,
2628 credit_card_trx_id,
2629 company_prepaid_invoice_id,
2630 cc_reversal_flag,
2631 attribute_category,
2632 attribute1,
2633 attribute2,
2634 attribute3,
2635 attribute4,
2636 attribute5,
2637 attribute6,
2638 attribute7,
2639 attribute8,
2640 attribute9,
2641 attribute10,
2642 attribute11,
2643 attribute12,
2644 attribute13,
2645 attribute14,
2646 attribute15,
2647 /*global_attribute_category,
2648 global_attribute1,
2649 global_attribute2, */
2650 --bugfix:4674194
2651 global_attribute3,
2652 /*
2653 global_attribute4,
2654 global_attribute5,
2655 global_attribute6,
2656 global_attribute7,
2657 global_attribute8,
2658 global_attribute9,
2659 global_attribute10,
2660 global_attribute11,
2661 global_attribute12,
2662 global_attribute13,
2663 global_attribute14,
2664 global_attribute15,
2665 global_attribute16,
2666 global_attribute17,
2667 global_attribute18,
2668 global_attribute19,
2669 global_attribute20,*/
2670 created_by,
2671 creation_date,
2672 last_updated_by,
2673 last_update_date,
2674 last_update_login,
2675 program_application_id,
2676 program_id,
2677 program_update_date,
2678 request_id,
2679 intended_use,
2680 accrual_posted_flag,
2681 cash_posted_flag,
2682 --Freight and Special Charges
2683 rcv_charge_addition_flag,
2684 pay_awt_group_id) --bug 8222382
2685 SELECT g_batch_id, --batch_id
2686 x_invoice_id, --invoice_id
2687 x_invoice_line_number, --invoice_line_number
2688 x_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
2689 l_distribution_line_number, --distribution_line_number
2690 decode(pd.accrue_on_receipt_flag,'Y',
2691 'ACCRUAL','ITEM'), --line_type_lookup_code
2692 ail.item_description, --description
2693 'ITEM_TO_PO', --dist_match_type
2694 'PERMANENT', --distribution_class
2695 ail.org_id, --org_id
2696 x_dist_tab(i).dist_ccid, --dist_code_combination_id
2697 ail.accounting_date, --accounting_date
2698 ail.period_name, --period_name
2699 NULL, --amount_to_post
2700 NULL, --base_amount_to_post
2701 NULL, --posted_amount
2702 NULL, --posted_base_amount
2703 NULL, --je_batch_id
2704 NULL, --cash_je_batch_id
2705 'N', --posted_flag
2706 NULL, --accounting_event_id
2707 NULL, --upgrade_posted_amt
2708 NULL, --upgrade_base_posted_amt
2709 g_set_of_books_id, --set_of_books_id
2710 x_dist_tab(i).amount, --amount
2711 x_dist_tab(i).base_amount, --base_amount
2712 x_dist_tab(i).rounding_amt, --rounding_amount
2713 --bugfix:4959567
2714 NULL, --match_status_flag
2715 'N', --encumbered_flag
2716 NULL, --packet_id
2717 -- Removed for bug 4277744
2718 -- NVL(PD.ussgl_transaction_code,
2719 -- ail.ussgl_transaction_code), --ussgl_transaction_code
2720 -- NULL, --ussgl_trx_code_context
2721 'N', --reversal_flag
2722 NULL, --parent_reversal_id
2723 'N', --cancellation_flag
2724 DECODE(ail.type_1099,'','',
2725 ail.income_tax_region), --income_tax_region
2726 ail.type_1099, --type_1099
2727 NULL, --stat_amount
2728 NULL, --charge_applicable_to_dist_id
2729 NULL, --prepay_amount_remaining
2730 NULL, --prepay_distribution_id
2731 NULL, --parent_invoice_id
2732 NULL, --corrected_invoice_dist_id
2733 NULL, --corrected_quantity
2734 NULL, --other_invoice_id
2735 x_dist_tab(i).po_distribution_id,--po_distribution_id
2736 NULL, --rcv_transaction_id
2737 x_dist_tab(i).unit_price, --unit_price
2738 ail.unit_meas_lookup_code, --matched_uom_lookup_code
2739 x_dist_tab(i).quantity_invoiced,--quantity_invoiced
2740 x_final_match_flag, --final_match_flag
2741 NULL, --related_id
2742 'U', --assets_addition_flag
2743 decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'), --assets_tracking_flag
2744 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
2745 'Y',ail.asset_book_type_code,NULL), --asset_book_type_code
2746 decode(decode(gcc.account_type,'E',ail.assets_tracking_flag,'A','Y','N'),
2747 'Y',ail.asset_category_id,NULL), --asset_category_id
2748 x_dist_tab(i).project_id , --project_id
2749 x_dist_tab(i).task_id , --task_id
2750 x_dist_tab(i).expenditure_type, --expenditure_type
2751 x_dist_tab(i).expenditure_item_date, --expenditure_item_date
2752 x_dist_tab(i).expenditure_organization_id , --expenditure_organization_id
2753 x_dist_tab(i).pa_quantity, --pa_quantity
2754 decode(PD.project_id,NULL, 'E',
2755 decode(pd.destination_type_code,
2756 'INVENTORY','M','SHOP FLOOR','M','N')), --pa_addition_flag
2757 NULL, --pa_cc_ar_invoice_id
2758 NULL, --pa_cc_ar_invoice_line_num
2759 NULL, --pa_cc_processed_code
2760 NULL, --award_id
2761 NULL, --gms_burdenable_raw_cost
2762 NULL, --awt_flag
2763 x_dist_tab(i).awt_group_id, --awt_group_id
2764 NULL, --awt_tax_rate_id
2765 NULL, --awt_gross_amount
2766 NULL, --awt_invoice_id
2767 NULL, --awt_origin_group_id
2768 NULL, --awt_invoice_payment_id
2769 NULL, --awt_withheld_amt
2770 'N', --inventory_transfer_status
2771 NULL, --reference_1
2772 NULL, --reference_2
2773 NULL, --receipt_verified_flag
2774 NULL, --receipt_required_flag
2775 NULL, --receipt_missing_flag
2776 NULL, --justification
2777 NULL, --expense_group
2778 NULL, --start_expense_date
2779 NULL, --end_expense_date
2780 NULL, --receipt_currency_code
2781 NULL, --receipt_conversion_rate
2782 NULL, --receipt_currency_amount
2783 NULL, --daily_amount
2784 NULL, --web_parameter_id
2785 NULL, --adjustment_reason
2786 NULL, --merchant_document_number
2787 NULL, --merchant_name
2788 NULL, --merchant_reference
2789 NULL, --merchant_tax_reg_number
2790 NULL, --merchant_taxpayer_id
2791 NULL, --country_of_supply
2792 NULL, --credit_card_trx_id
2793 NULL, --company_prepaid_invoice_id
2794 NULL, --cc_reversal_flag
2795 -- Bug 6837035 Start
2796 NVL(DECODE(g_transfer_flag,'Y',pd.attribute_category,DECODE(line_source,'IMPORTED',
2797 DECODE(l_copy_line_dff_flag,'Y',ail.attribute_category))), ''),--attribute_category
2798 NVL(DECODE(g_transfer_flag,'Y',pd.attribute1,DECODE(line_source,'IMPORTED',
2799 DECODE(l_copy_line_dff_flag,'Y',ail.attribute1))), ''), --attribute1
2800 NVL(DECODE(g_transfer_flag,'Y',pd.attribute2,DECODE(line_source,'IMPORTED',
2801 DECODE(l_copy_line_dff_flag,'Y',ail.attribute2))), ''), --attribute2
2802 NVL(DECODE(g_transfer_flag,'Y',pd.attribute3,DECODE(line_source,'IMPORTED',
2803 DECODE(l_copy_line_dff_flag,'Y',ail.attribute3))), ''), --attribute3
2804 NVL(DECODE(g_transfer_flag,'Y',pd.attribute4,DECODE(line_source,'IMPORTED',
2805 DECODE(l_copy_line_dff_flag,'Y',ail.attribute4))), ''), --attribute4
2806 NVL(DECODE(g_transfer_flag,'Y',pd.attribute5,DECODE(line_source,'IMPORTED',
2807 DECODE(l_copy_line_dff_flag,'Y',ail.attribute5))), ''), --attribute5
2808 NVL(DECODE(g_transfer_flag,'Y',pd.attribute6,DECODE(line_source,'IMPORTED',
2809 DECODE(l_copy_line_dff_flag,'Y',ail.attribute6))), ''), --attribute6
2810 NVL(DECODE(g_transfer_flag,'Y',pd.attribute7,DECODE(line_source,'IMPORTED',
2811 DECODE(l_copy_line_dff_flag,'Y',ail.attribute7))), ''), --attribute7
2812 NVL(DECODE(g_transfer_flag,'Y',pd.attribute8,DECODE(line_source,'IMPORTED',
2813 DECODE(l_copy_line_dff_flag,'Y',ail.attribute8))), ''), --attribute8
2814 NVL(DECODE(g_transfer_flag,'Y',pd.attribute9,DECODE(line_source,'IMPORTED',
2815 DECODE(l_copy_line_dff_flag,'Y',ail.attribute9))), ''), --attribute9
2816 NVL(DECODE(g_transfer_flag,'Y',pd.attribute10,DECODE(line_source,'IMPORTED',
2817 DECODE(l_copy_line_dff_flag,'Y',ail.attribute10))), ''), --attribute10
2818 NVL(DECODE(g_transfer_flag,'Y',pd.attribute11,DECODE(line_source,'IMPORTED',
2819 DECODE(l_copy_line_dff_flag,'Y',ail.attribute11))), ''), --attribute11
2820 NVL(DECODE(g_transfer_flag,'Y',pd.attribute12,DECODE(line_source,'IMPORTED',
2821 DECODE(l_copy_line_dff_flag,'Y',ail.attribute12))), ''), --attribute12
2822 NVL(DECODE(g_transfer_flag,'Y',pd.attribute13,DECODE(line_source,'IMPORTED',
2823 DECODE(l_copy_line_dff_flag,'Y',ail.attribute13))), ''), --attribute13
2824 NVL(DECODE(g_transfer_flag,'Y',pd.attribute14,DECODE(line_source,'IMPORTED',
2825 DECODE(l_copy_line_dff_flag,'Y',ail.attribute14))), ''), --attribute14
2826 NVL(DECODE(g_transfer_flag,'Y',pd.attribute15,DECODE(line_source,'IMPORTED',
2827 DECODE(l_copy_line_dff_flag,'Y',ail.attribute15))), ''), --attribute15
2828 -- Bug 6837035 End
2829 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
2830 X_GLOBAL_ATTRIBUTE1,
2831 X_GLOBAL_ATTRIBUTE2, */
2832 --Bugfix:4674194
2833 DECODE(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
2834 'Y',ail.ship_to_location_id, ''),
2835 /*
2836 X_GLOBAL_ATTRIBUTE4,
2837 X_GLOBAL_ATTRIBUTE5,
2838 X_GLOBAL_ATTRIBUTE6,
2839 X_GLOBAL_ATTRIBUTE7,
2840 X_GLOBAL_ATTRIBUTE8,
2841 X_GLOBAL_ATTRIBUTE9,
2842 X_GLOBAL_ATTRIBUTE10,
2843 X_GLOBAL_ATTRIBUTE11,
2844 X_GLOBAL_ATTRIBUTE12,
2845 X_GLOBAL_ATTRIBUTE13,
2846 X_GLOBAL_ATTRIBUTE14,
2847 X_GLOBAL_ATTRIBUTE15,
2848 X_GLOBAL_ATTRIBUTE16,
2849 X_GLOBAL_ATTRIBUTE17,
2850 X_GLOBAL_ATTRIBUTE18,
2851 X_GLOBAL_ATTRIBUTE19,
2852 X_GLOBAL_ATTRIBUTE20, */
2853 ail.created_by, --created_by
2854 sysdate, --creation_date
2855 ail.last_updated_by, --last_updated_by
2856 sysdate, --last_update_date
2857 ail.last_update_login, --last_update_login
2858 NULL, --program_application_id
2859 NULL, --program_id
2860 NULL, --program_update_date
2861 NULL, --request_id
2862 --bugfix:5565310
2863 G_intended_use, --intended_use
2864 'N', --accrual_posted_flag
2865 'N', --cash_posted_flag
2866 'N', --rcv_charge_addition_flag
2867 x_dist_tab(i).pay_awt_group_id --pay_awt_group_id bug8222382
2868 FROM po_distributions pd,
2869 ap_invoice_lines ail,
2870 gl_code_combinations gcc
2871 WHERE ail.invoice_id = x_invoice_id
2872 AND ail.line_number = x_invoice_line_number
2873 AND ail.po_line_location_id = pd.line_location_id
2874 AND pd.po_distribution_id = x_dist_tab(i).po_distribution_id
2875 AND gcc.code_combination_id = decode(pd.accrue_on_receipt_flag, 'Y', --Bug6014884
2876 pd.code_combination_id,x_dist_tab(i).dist_ccid);
2877
2878
2879
2880 --Bugfix:4674635
2881 l_debug_info := 'Call the AP_EXTENDED_MATCH to populate global attributes';
2882 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2883 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2884 END IF;
2885
2886 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
2887 AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_MATCH(
2888 P_Credit_Id => NULL,
2889 P_Invoice_Id => X_invoice_id,
2890 P_Inv_Line_Num => x_invoice_line_number,
2891 P_Distribution_Id => x_dist_tab(i).invoice_distribution_id,
2892 P_Parent_Dist_Id => NULL);
2893
2894 END IF;
2895
2896
2897 GMS_AP_API.CREATE_AWARD_DISTRIBUTIONS
2898 ( p_invoice_id => x_invoice_id,
2899 p_distribution_line_number => l_distribution_line_number,
2900 p_invoice_distribution_id => x_dist_tab(i).invoice_distribution_id,
2901 p_award_id => x_dist_tab(i).award_id,
2902 p_mode => 'AP',
2903 p_dist_set_id => NULL,
2904 p_dist_set_line_number => NULL );
2905
2906 l_distribution_line_number := l_distribution_line_number + 1;
2907
2908 END IF;
2909
2910 END LOOP;
2911
2912 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2913 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Invoice_Distributions(-)');
2914 END IF;
2915
2916
2917 EXCEPTION
2918 WHEN OTHERS THEN
2919
2920 IF (SQLCODE <> -20001) THEN
2921 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2922 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2923 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2924 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch_Id = '||TO_CHAR(g_Batch_Id)
2925 ||', Invoice_id = '||TO_CHAR(X_invoice_id)
2926 ||', Invoice Line Number = '||X_Invoice_Line_Number
2927 ||', Dist_num = '||l_distribution_line_number
2928 ||', Allow_PA_Override = '||g_allow_pa_override
2929 ||', Transfer_Desc_Flag = '||g_Transfer_Flag);
2930 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2931 END IF;
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 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_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
2952 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
2953 l_api_name VARCHAR2(50);
2954 l_return_status VARCHAR2(100);
2955 l_msg_data VARCHAR2(4000);
2956 BEGIN
2957
2958 l_api_name := 'Update_PO_Shipments_Dists';
2959
2960 current_calling_sequence := 'Update_Po_Shipments_Dists<-'||x_calling_sequence;
2961
2962 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2963 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_PO_Shipments_Dists(+)');
2964 END IF;
2965
2966 l_debug_info := 'Create l_po_ap_dist_rec object';
2967 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2968 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2969 END IF;
2970
2971 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
2972
2973 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
2974 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2975 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2976 END IF;
2977
2978 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
2979
2980 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2981 p_po_line_location_id => x_po_line_location_id,
2982 p_uom_code => x_uom_lookup_code,
2983 p_quantity_billed => x_match_quantity,
2984 p_amount_billed => x_match_amount,
2985 p_quantity_financed => NULL,
2986 p_amount_financed => NULL,
2987 p_quantity_recouped => NULL,
2988 p_amount_recouped => NULL,
2989 p_retainage_withheld_amt => NULL,
2990 p_retainage_released_amt => NULL
2991 );
2992
2993 ELSIF (g_invoice_type_lookup_code = 'PREPAYMENT') THEN
2994
2995 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2996 p_po_line_location_id => x_po_line_location_id,
2997 p_uom_code => x_uom_lookup_code,
2998 p_quantity_billed => NULL,
2999 p_amount_billed => NULL,
3000 p_quantity_financed => x_match_quantity,
3001 p_amount_financed => x_match_amount,
3002 p_quantity_recouped => NULL,
3003 p_amount_recouped => NULL,
3004 p_retainage_withheld_amt => NULL,
3005 p_retainage_released_amt => NULL
3006 );
3007
3008 END IF;
3009
3010 l_debug_info := 'Populate the Po_Ap_Dist_Rec with the distribution information';
3011 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3012 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3013 END IF;
3014
3015 FOR i in nvl(x_dist_tab.first,0)..nvl(x_dist_tab.last,0) LOOP
3016
3017 IF (x_dist_tab.exists(i)) THEN
3018
3019 IF (g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
3020
3021 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
3022 p_uom_code => x_uom_lookup_code,
3023 p_quantity_billed => x_dist_tab(i).quantity_invoiced,
3024 p_amount_billed => x_dist_tab(i).amount,
3025 p_quantity_financed => NULL,
3026 p_amount_financed => NULL,
3027 p_quantity_recouped => NULL,
3028 p_amount_recouped => NULL,
3029 p_retainage_withheld_amt => NULL,
3030 p_retainage_released_amt => NULL);
3031
3032 ELSIF (g_invoice_type_lookup_code = 'PREPAYMENT') THEN
3033
3034 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
3035 p_uom_code => x_uom_lookup_code,
3036 p_quantity_billed => NULL,
3037 p_amount_billed => NULL,
3038 p_quantity_financed => x_dist_tab(i).quantity_invoiced,
3039 p_amount_financed => x_dist_tab(i).amount,
3040 p_quantity_recouped => NULL,
3041 p_amount_recouped => NULL,
3042 p_retainage_withheld_amt => NULL,
3043 p_retainage_released_amt => NULL);
3044
3045 END IF;
3046
3047 END IF;
3048
3049 END LOOP;
3050
3051 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3052 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3053 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3054 END IF;
3055
3056 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
3057 P_Api_Version => 1.0,
3058 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
3059 P_Dist_Changes_Rec => l_po_ap_dist_rec,
3060 X_Return_Status => l_return_status,
3061 X_Msg_Data => l_msg_data);
3062
3063
3064 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3065 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_PO_Shipments_Dists(-)');
3066 END IF;
3067
3068 EXCEPTION
3069 WHEN OTHERS THEN
3070 IF (SQLCODE <> -20001) THEN
3071 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3072 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3073 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3074 FND_MESSAGE.SET_TOKEN('PARAMETERS','PO Distribution Id = '||TO_CHAR(X_Dist_tab(i).po_distribution_id)
3075 ||', UOM= '||X_uom_lookup_code);
3076 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3077 END IF;
3078
3079 --Clean up the PL/SQL table
3080 X_DIST_TAB.DELETE;
3081 APP_EXCEPTION.RAISE_EXCEPTION;
3082
3083 END Update_PO_Shipments_Dists;
3084
3085
3086 Procedure Create_Charge_Lines( X_Invoice_Id IN NUMBER,
3087 X_Freight_Cost_Factor_id IN NUMBER,
3088 X_Freight_Amount IN NUMBER,
3089 X_Freight_Description IN VARCHAR2,
3090 X_Misc_Cost_Factor_id IN NUMBER,
3091 X_Misc_Amount IN NUMBER,
3092 X_Misc_Description IN VARCHAR2,
3093 X_Item_Line_Number IN NUMBER,
3094 X_Calling_Sequence IN VARCHAR2) IS
3095
3096 l_debug_info VARCHAR2(2000);
3097 current_calling_sequence VARCHAR2(2000);
3098 l_api_name VARCHAR2(50);
3099
3100 BEGIN
3101
3102 l_api_name := 'Create_Charge_Lines';
3103
3104 current_calling_sequence := 'Create_Charge_Lines<-'||X_Calling_Sequence;
3105
3106 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3107 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Create_Charge_Lines(+)');
3108 END IF;
3109
3110 IF (X_Freight_Amount IS NOT NULL) THEN
3111
3112 l_debug_info := 'Create Freight Line';
3113 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3114 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3115 END IF;
3116
3117 Insert_Invoice_Line(
3118 X_Invoice_Id => x_invoice_id,
3119 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
3120 X_Line_Type_Lookup_Code => 'FREIGHT',
3121 X_Cost_Factor_id => x_freight_cost_factor_id,
3122 X_Amount => x_freight_amount,
3123 X_Item_Line_Number => x_item_line_number,
3124 X_Charge_Line_Description => x_freight_description,
3125 X_Calling_Sequence => current_calling_sequence);
3126
3127
3128 l_debug_info := 'Create Allocation Rules for the freight line';
3129 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3130 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3131 END IF;
3132
3133 AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
3134 X_Invoice_id => x_invoice_id,
3135 X_Chrg_Line_Number => g_max_invoice_line_number,
3136 X_To_Line_Number => x_item_line_number,
3137 X_Percentage => 100,
3138 X_Calling_Sequence => x_calling_sequence);
3139
3140
3141 END IF;
3142
3143
3144 IF (X_Misc_Amount IS NOT NULL) THEN
3145
3146 l_debug_info := 'Create Misc Line';
3147 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3148 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3149 END IF;
3150
3151 Insert_Invoice_Line(
3152 X_Invoice_Id => x_invoice_id,
3153 X_Invoice_Line_Number => g_max_invoice_line_number + 1,
3154 X_Line_Type_Lookup_Code => 'MISCELLANEOUS',
3155 X_Cost_Factor_id => x_misc_cost_factor_id,
3156 X_Amount => x_misc_amount,
3157 X_Item_Line_Number => x_item_line_number,
3158 X_Charge_Line_Description => x_misc_description,
3159 X_Calling_Sequence => current_calling_sequence);
3160
3161 l_debug_info := 'Create Allocation Rules for the misc line';
3162 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3164 END IF;
3165
3166 AP_ALLOCATION_RULES_PKG.Insert_Percentage_Alloc_Rule(
3167 X_Invoice_id => x_invoice_id,
3168 X_Chrg_Line_Number => g_max_invoice_line_number,
3169 X_To_Line_Number => x_item_line_number,
3170 X_Percentage => 100,
3171 X_Calling_Sequence => x_calling_sequence);
3172
3173 END IF;
3174
3175 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3176 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Create_Charge_Lines(-)');
3177 END IF;
3178
3179 EXCEPTION
3180 WHEN OTHERS THEN
3181 IF (SQLCODE <> -20001) THEN
3182 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3183 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3184 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3185 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||to_char(X_Invoice_Id)
3186 ||', Freight Amount = '||to_char(x_freight_amount)
3187 ||', Freight Description = '||x_freight_description
3188 ||', Misc Amount = '||to_char(x_misc_amount)
3189 ||', Misc Description = '||x_misc_description
3190 ||', Item Line Number = '||TO_CHAR(X_Item_Line_Number)
3191 ||', Accounting Date = '||g_accounting_date
3192 ||', Period Name = '||g_period_name
3193 ||', Set of books id = '||to_char(g_set_of_books_id)
3194 ||', Exchange Rate = '||g_exchange_rate
3195 ||', Base Currency Code = '||g_base_currency_code
3196 ||', Income Tax Region = '||g_income_tax_region
3197 ||', Awt Group Id = '||g_awt_group_id
3198 ||', Transfer Flag = '||g_transfer_flag
3199 ||', Pay Awt Group Id = '||g_pay_awt_group_id); --bug 8222382
3200 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3201 END IF;
3202 APP_EXCEPTION.RAISE_EXCEPTION;
3203
3204 END Create_Charge_Lines;
3205
3206
3207 /*===========================================================================
3208 PRICE AND QUANTITY CORRECTION OF INVOICE MATCHED TO PO
3209 ============================================================================*/
3210
3211
3212 PROCEDURE Price_Quantity_Correct_Inv_PO(
3213 X_Invoice_Id IN NUMBER,
3214 X_Invoice_Line_Number IN NUMBER,
3215 X_Corrected_Invoice_Id IN NUMBER,
3216 X_Corrected_Line_Number IN NUMBER,
3217 X_Correction_Type IN VARCHAR2,
3218 X_Match_Mode IN VARCHAR2,
3219 X_Correction_Quantity IN NUMBER,
3220 X_Correction_Amount IN NUMBER,
3221 X_Correction_Price IN NUMBER,
3222 X_Po_Line_Location_Id IN NUMBER,
3223 X_Corr_Dist_Tab IN OUT NOCOPY CORR_DIST_TAB_TYPE,
3224 X_Final_Match_Flag IN VARCHAR2,
3225 X_Uom_Lookup_Code IN VARCHAR2,
3226 X_Retained_Amount IN NUMBER DEFAULT NULL,
3227 X_Calling_Sequence IN VARCHAR2) IS
3228
3229 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE := NULL;
3230 l_item_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
3231 l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
3232 l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3233 l_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3234 l_retained_amount ap_invoice_lines_all.retained_amount%TYPE;
3235 l_success BOOLEAN;
3236 l_error_message VARCHAR2(4000);
3237 l_debug_info VARCHAR2(2000);
3238 current_calling_sequence VARCHAR2(2000);
3239 l_api_name VARCHAR2(50);
3240
3241 BEGIN
3242
3243 l_api_name := 'Price_Quantity_Correct_Inv_PO';
3244
3245 current_calling_sequence := 'Price_Quantity_Correct_Inv_PO<-'||x_calling_sequence;
3246
3247 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3248 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Price_Quantity_Correct_Inv_PO(+)');
3249 END IF;
3250
3251 l_debug_info := 'Calling Get_Info';
3252 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3253 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3254 END IF;
3255
3256 Get_Info(x_invoice_id => x_invoice_id,
3257 x_invoice_line_number => x_invoice_line_number,
3258 x_match_amount => x_correction_amount,
3259 x_po_line_location_id => x_po_line_location_id,
3260 x_calling_sequence => current_calling_sequence);
3261
3262 IF g_invoice_type_lookup_code <> 'PREPAYMENT' THEN
3263 l_retained_amount := AP_INVOICE_LINES_UTILITY_PKG.Get_Retained_Amount
3264 (p_line_location_id => x_po_line_location_id,
3265 p_match_amount => x_correction_amount);
3266 END IF;
3267
3268 l_debug_info := 'Calling Get_Corr_Dist_Proration_Info';
3269 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3270 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3271 END IF;
3272
3273 Get_Corr_Dist_Proration_Info(
3274 x_corrected_invoice_id => x_corrected_invoice_id,
3275 x_corrected_line_number => x_corrected_line_number,
3276 x_corr_dist_tab => x_corr_dist_tab,
3277 x_correction_type => x_correction_type,
3278 x_correction_amount => x_correction_amount,
3279 x_correction_quantity => x_correction_quantity,
3280 x_correction_price => x_correction_price,
3281 x_match_mode => x_match_mode,
3282 x_calling_sequence => current_calling_sequence);
3283
3284 IF (x_corr_dist_tab.COUNT = 1) THEN
3285
3286 l_po_distribution_id := x_corr_dist_tab.FIRST;
3287
3288 END IF;
3289
3290 IF (x_invoice_line_number IS NULL) THEN
3291
3292 l_debug_info := 'Calling Insert_Corr_Invoice_Line';
3293 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3294 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3295 END IF;
3296
3297 Insert_Corr_Invoice_Line(x_invoice_id => x_invoice_id,
3298 x_invoice_line_number => g_max_invoice_line_number +1,
3299 x_corrected_invoice_id => x_corrected_invoice_id,
3300 x_corrected_line_number => x_corrected_line_number,
3301 x_quantity => x_correction_quantity,
3302 x_amount => x_correction_amount,
3303 x_unit_price => x_correction_price,
3304 x_correction_type => x_correction_type,
3305 x_final_match_flag => x_final_match_flag,
3306 x_po_distribution_id => l_po_distribution_id,
3307 x_retained_amount => l_retained_amount,
3308 x_calling_sequence => current_calling_sequence);
3309
3310 END IF;
3311
3312 l_item_line_number := g_max_invoice_line_number;
3313
3314 l_debug_info := 'Calling Insert_Corr_Invoice_Dists';
3315 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3316 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3317 END IF;
3318
3319 Insert_Corr_Invoice_Dists(x_invoice_id => x_invoice_id,
3320 x_invoice_line_number => nvl(x_invoice_line_number,
3321 g_max_invoice_line_number),
3322 x_corrected_invoice_id=> x_corrected_invoice_id,
3323 x_corr_dist_tab => x_corr_dist_tab,
3324 x_correction_type => x_correction_type,
3325 x_final_match_flag => x_final_match_flag,
3326 x_total_amount => x_correction_amount,
3327 x_calling_sequence => current_calling_sequence);
3328
3329
3330 IF(x_invoice_line_number IS NOT NULL) THEN
3331
3332 l_debug_info := 'Updating Invoice Line Attributes after matching';
3333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3335 END IF;
3336
3337 UPDATE ap_invoice_lines ail
3338 SET (generate_dists ,
3339 attribute_category,
3340 attribute1,
3341 attribute2,
3342 attribute3,
3343 attribute4,
3344 attribute5,
3345 attribute6,
3346 attribute7,
3347 attribute8,
3348 attribute9,
3349 attribute10,
3350 attribute11,
3351 attribute12,
3352 attribute13,
3353 attribute14,
3354 attribute15,
3355 retained_amount,
3356 retained_amount_remaining)
3357 =
3358 (SELECT 'D',
3359 /* Bug 7483260. If the attribute field is populated in the
3360 * interface, take that value. If the attribute field from
3361 * the interface is null and the transfer_desc_flex_flag is
3362 * Y, take the value from the purchase order.
3363 */
3364 nvl(ail.attribute_category, decode(g_transfer_flag, 'Y', pll.attribute_category, ail.attribute_category)),
3365 nvl(ail.attribute1, decode(g_transfer_flag, 'Y', pll.attribute1, ail.attribute1)),
3366 nvl(ail.attribute2, decode(g_transfer_flag, 'Y', pll.attribute2, ail.attribute2)),
3367 nvl(ail.attribute3, decode(g_transfer_flag, 'Y', pll.attribute3, ail.attribute3)),
3368 nvl(ail.attribute4, decode(g_transfer_flag, 'Y', pll.attribute4, ail.attribute4)),
3369 nvl(ail.attribute5, decode(g_transfer_flag, 'Y', pll.attribute5, ail.attribute5)),
3370 nvl(ail.attribute6, decode(g_transfer_flag, 'Y', pll.attribute6, ail.attribute6)),
3371 nvl(ail.attribute7, decode(g_transfer_flag, 'Y', pll.attribute7, ail.attribute7)),
3372 nvl(ail.attribute8, decode(g_transfer_flag, 'Y', pll.attribute8, ail.attribute8)),
3373 nvl(ail.attribute9, decode(g_transfer_flag, 'Y', pll.attribute9, ail.attribute9)),
3374 nvl(ail.attribute10, decode(g_transfer_flag, 'Y', pll.attribute10, ail.attribute10)),
3375 nvl(ail.attribute11, decode(g_transfer_flag, 'Y', pll.attribute11, ail.attribute11)),
3376 nvl(ail.attribute12, decode(g_transfer_flag, 'Y', pll.attribute12, ail.attribute12)),
3377 nvl(ail.attribute13, decode(g_transfer_flag, 'Y', pll.attribute13, ail.attribute13)),
3378 nvl(ail.attribute14, decode(g_transfer_flag, 'Y', pll.attribute14, ail.attribute14)),
3379 nvl(ail.attribute15, decode(g_transfer_flag, 'Y', pll.attribute15, ail.attribute15)),
3380 --end Bug 7483260
3381 l_retained_amount,
3382 -1 * l_retained_amount
3383 FROM ap_invoice_lines ail1,
3384 po_line_locations pll
3385 WHERE ail1.invoice_id = x_invoice_id
3386 AND ail1.line_number =x_invoice_line_number
3387 AND pll.line_location_id = ail1.po_line_location_id)
3388 WHERE invoice_id = x_invoice_id
3389 AND line_number = x_invoice_line_number;
3390
3391 END IF;
3392
3393 l_debug_info := 'Create Retainage Distributions';
3394 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3395 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3396 END IF;
3397
3398 Ap_Retainage_Pkg.Create_Retainage_Distributions
3399 (x_invoice_id => x_invoice_id,
3400 x_invoice_line_number => nvl(x_invoice_line_number,l_item_line_number));
3401
3402 IF (g_recoupment_rate is not null and x_correction_amount > 0
3403 and g_invoice_type_lookup_code <> 'PREPAYMENT') THEN
3404
3405 l_debug_info := 'Calculate the maximum amount that can be recouped from this invoice line';
3406 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3407 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3408 END IF;
3409
3410 l_line_amt_net_retainage := x_correction_amount + nvl(l_retained_amount,0);
3411
3412 l_max_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
3413 (x_correction_amount * g_recoupment_rate / 100) ,g_invoice_currency_code);
3414
3415 IF (l_line_amt_net_retainage < l_max_amount_to_recoup) THEN
3416 l_amount_to_recoup := l_line_amt_net_retainage;
3417 ELSE
3418 l_amount_to_recoup := l_max_amount_to_recoup;
3419 END IF;
3420
3421 l_debug_info := 'Automatically recoup any available prepayments against the same po line';
3422 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3423 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3424 END IF;
3425
3426 l_success := AP_Matching_Utils_Pkg.Ap_Recoup_Invoice_Line(
3427 P_Invoice_Id => x_invoice_id ,
3428 P_Invoice_Line_Number => nvl(x_invoice_line_number,l_item_line_number) ,
3429 P_Amount_To_Recoup => l_amount_to_recoup,
3430 P_Po_Line_Id => g_po_line_id,
3431 P_Vendor_Id => g_vendor_id,
3432 P_Vendor_Site_Id => g_vendor_site_id,
3433 P_Accounting_Date => g_accounting_date,
3434 P_Period_Name => g_period_name,
3435 P_User_Id => g_user_id,
3436 P_Last_Update_Login => g_login_id ,
3437 P_Error_Message => l_error_message,
3438 P_Calling_Sequence => current_calling_sequence);
3439
3440 END IF;
3441
3442
3443 l_debug_info := 'Calling Update_Corr_Po_Shipments_Dists';
3444 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3445 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3446 END IF;
3447 Update_Corr_Po_Shipments_Dists(x_corr_dist_tab => x_corr_dist_tab,
3448 x_po_line_location_id => x_po_line_location_id,
3449 x_quantity => x_correction_quantity,
3450 x_amount => x_correction_amount,
3451 x_correction_type => x_correction_type,
3452 x_uom_lookup_code => x_uom_lookup_code,
3453 x_calling_sequence => current_calling_sequence);
3454
3455 --Clean up the PL/SQL tables
3456 x_corr_dist_tab.delete;
3457
3458 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3459 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Price_Quantity_Correct_Inv_PO(-)');
3460 END IF;
3461
3462
3463 EXCEPTION
3464 WHEN others then
3465 If (SQLCODE <> -20001) Then
3466 fnd_message.set_name('SQLAP','AP_DEBUG');
3467 fnd_message.set_token('ERROR',SQLERRM);
3468 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
3469 fnd_message.set_token('PARAMETERS',
3470 ' invoice_id = '||to_char(x_invoice_id)
3471 ||', invoice_line_number = ' ||to_char(x_invoice_line_number)
3472 ||', corrected_invoice_id = '||to_char(x_corrected_invoice_id)
3473 ||', corrected_line_number = '||to_char(x_corrected_line_number)
3474 ||', correction_type = '||x_correction_type
3475 ||', match_mode = '||x_match_mode
3476 ||', correction quantity = '||to_char(x_correction_quantity)
3477 ||', correction amount = '||to_char(x_correction_amount)
3478 ||', correction price = '||to_char(x_correction_price)
3479 ||', final_match_flag = '||x_final_match_flag
3480 ||', po_line_location_id = '||to_char(x_po_line_location_id));
3481 fnd_message.set_token('DEBUG_INFO',l_debug_info);
3482 End if;
3483
3484 --Clean up the PL/SQL tables
3485 x_corr_dist_tab.delete;
3486
3487 app_exception.raise_exception;
3488
3489 END Price_Quantity_Correct_Inv_PO;
3490
3491
3492
3493 PROCEDURE Get_Corr_Dist_Proration_Info(
3494 x_corrected_invoice_id IN NUMBER,
3495 x_corrected_line_number IN NUMBER,
3496 x_corr_dist_tab IN OUT NOCOPY CORR_DIST_TAB_TYPE,
3497 x_correction_type IN VARCHAR2,
3498 x_correction_amount IN NUMBER,
3499 x_correction_quantity IN NUMBER,
3500 x_correction_price IN NUMBER,
3501 x_match_mode IN VARCHAR2,
3502 x_calling_sequence IN VARCHAR2) IS
3503
3504
3505 CURSOR Price_Correction_Cursor(c_price_variance NUMBER) IS
3506 SELECT aid.invoice_distribution_id corrected_inv_dist_id,
3507 aid.po_distribution_id,
3508 decode(g_min_acct_unit,'',
3509 round(x_correction_amount * (aid.amount+c_price_variance)/ail.amount,
3510 g_precision),
3511 round((x_correction_amount * (aid.amount+c_price_variance)/ail.amount)
3512 /g_min_acct_unit) * g_min_acct_unit
3513 ) amount,
3514 round(x_correction_quantity * (aid.amount+c_price_variance)/ail.amount,15) corrected_quantity,
3515 --bugfix:5606697
3516 DECODE(pd.destination_type_code,
3517 'EXPENSE', DECODE(pd.accrue_on_receipt_flag,
3518 'Y', pd.code_combination_id,
3519 aid.dist_code_combination_id),
3520 pd.variance_account_id),
3521 ap_invoice_distributions_s.nextval
3522 FROM ap_invoice_lines ail,
3523 ap_invoice_distributions aid,
3524 po_distributions pd
3525 WHERE ail.invoice_id = x_corrected_invoice_id
3526 AND ail.line_number = x_corrected_line_number
3527 AND aid.invoice_id = ail.invoice_id
3528 AND aid.po_distribution_id = pd.po_distribution_id /*bugfix:5606697*/
3529 AND aid.invoice_line_number = ail.line_number
3530 -- Bug 5585744, Modified the condition below
3531 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
3532 AND aid.prepay_distribution_id IS NULL;
3533 /*AND aid.line_type_lookup_code NOT IN ('PREPAY','AWT','RETAINAGE')
3534 AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3535 OR aid.prepay_distribution_id IS NULL); */
3536
3537 /*-----------------------------------------------------------
3538 Amount: Quantity_Invoiced at the line level prorated based on
3539 quantity_billed against the PO and multiplied by the unit_price
3540 at the line level.
3541
3542 Corrected Quantity: Quantity_Invoiced selected prorated as done
3543 for the Amount.
3544 ------------------------------------------------------------*/
3545 CURSOR Quantity_Correction_Cursor(p_total_quantity_billed IN NUMBER) IS
3546 SELECT aid.invoice_distribution_id corrected_inv_dist_id,
3547 aid.po_distribution_id,
3548 decode(g_min_acct_unit,'',
3549 round((x_correction_quantity *
3550 decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0))/p_total_quantity_billed
3551 ) * x_correction_price,
3552 g_precision),
3553 round(((x_correction_quantity * decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0))/
3554 p_total_quantity_billed)*x_correction_price)
3555 /g_min_acct_unit) * g_min_acct_unit
3556 ) amount,
3557 round((x_correction_quantity * decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0)))/
3558 p_total_quantity_billed , 15) corrected_quantity,
3559 aid.dist_code_combination_id,
3560 ap_invoice_distributions_s.nextval
3561 FROM ap_invoice_distributions aid,
3562 ap_invoice_lines ail,
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 pd.po_distribution_id = aid.po_distribution_id
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 l_corrected_inv_dist_id ap_invoice_distributions.corrected_invoice_dist_id%TYPE;
3577 l_amount ap_invoice_distributions.amount%TYPE;
3578 l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3579 l_total_quantity_billed number;
3580 l_base_amount ap_invoice_distributions.base_amount%TYPE;
3581 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3582 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
3583 l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
3584 l_rounding_index ap_invoice_distributions.invoice_distribution_id%TYPE;
3585 l_sum_dist_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
3586 l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
3587 l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
3588 l_price_variance ap_invoice_distributions.amount%TYPE := 0;
3589 l_debug_info varchar2(2000);
3590 current_calling_sequence varchar2(2000);
3591 l_api_name varchar2(50);
3592
3593 BEGIN
3594
3595 l_api_name := 'Get_Corr_Dist_Proration_Info';
3596
3597 current_calling_sequence := 'Get_Corr_Dist_Proration_Info<-'||x_calling_sequence;
3598 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3599 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Corr_Dist_Proration_Info(+)');
3600 END IF;
3601
3602
3603 /* Bug 5176411, Changed the index for x_corr_dist_tab from l_po_dist_id to
3604 l_invoice_distrbution_id */
3605
3606 IF (x_correction_type = 'PRICE_CORRECTION') THEN
3607
3608 IF (x_match_mode IN ('STD-PS','CR-PS')) THEN
3609
3610 BEGIN
3611 SELECT nvl(sum(amount),0) -- Bug 5629985. Added the NVL
3612 INTO l_price_variance
3613 FROM ap_invoice_distributions_all
3614 WHERE invoice_id = x_corrected_invoice_id
3615 AND invoice_line_number = x_corrected_line_number
3616 AND line_type_lookup_code = 'IPV';
3617 EXCEPTION
3618 WHEN OTHERS THEN NULL;
3619 END;
3620
3621 OPEN price_correction_cursor(l_price_variance);
3622
3623 LOOP
3624
3625 FETCH price_correction_cursor INTO l_corrected_inv_dist_id,
3626 l_po_dist_id,
3627 l_amount,
3628 l_corrected_quantity,
3629 l_dist_ccid,
3630 l_invoice_distribution_id;
3631
3632 EXIT WHEN price_correction_cursor%NOTFOUND;
3633
3634 x_corr_dist_tab(l_invoice_distribution_id).po_distribution_id := l_po_dist_id;
3635 x_corr_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
3636 x_corr_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
3637 x_corr_dist_tab(l_invoice_distribution_id).amount := l_amount;
3638 x_corr_dist_tab(l_invoice_distribution_id).corrected_quantity := l_corrected_quantity;
3639 x_corr_dist_tab(l_invoice_distribution_id).unit_price := x_correction_price;
3640 x_corr_dist_tab(l_invoice_distribution_id).pa_quantity := l_corrected_quantity;
3641 x_corr_dist_tab(l_invoice_distribution_id).dist_ccid := l_dist_ccid;
3642
3643 --Calculate the index of the max of the largest distribution for
3644 --proration/base amount rounding.
3645 IF (l_amount >= l_max_dist_amount) THEN
3646 l_rounding_index := l_invoice_distribution_id;
3647 l_max_dist_amount := l_max_dist_amount;
3648 END IF;
3649
3650 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
3651
3652 END LOOP;
3653
3654 CLOSE price_correction_cursor;
3655
3656 END IF; /*x_match_mode in 'STD-PS','CR-PS' */
3657
3658 ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3659
3660 --For Shipment Level Match
3661 IF (x_match_mode IN ('STD-PS','CR-PS')) THEN
3662
3663 SELECT sum(decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0)))
3664 INTO l_total_quantity_billed
3665 FROM po_distributions pd,
3666 ap_invoice_distributions aid
3667 WHERE pd.po_distribution_id = aid.po_distribution_id
3668 AND aid.invoice_id = x_corrected_invoice_id
3669 AND aid.invoice_line_number = x_corrected_line_number
3670 -- Bug 5585744, Modified the condition below
3671 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
3672 AND aid.prepay_distribution_id IS NULL;
3673 /*AND aid.line_type_lookup_code NOT IN ('PREPAY','AWT','RETAINAGE')
3674 AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3675 OR aid.prepay_distribution_id IS NULL); */
3676
3677 OPEN quantity_correction_cursor(l_total_quantity_billed);
3678
3679 LOOP
3680
3681 FETCH quantity_correction_cursor INTO l_corrected_inv_dist_id,
3682 l_po_dist_id,
3683 l_amount,
3684 l_corrected_quantity,
3685 l_dist_ccid,
3686 l_invoice_distribution_id;
3687
3688 EXIT WHEN quantity_correction_cursor%NOTFOUND;
3689
3690 x_corr_dist_tab(l_invoice_distribution_id).po_distribution_id := l_po_dist_id;
3691 x_corr_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
3692 x_corr_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
3693 x_corr_dist_tab(l_invoice_distribution_id).amount := l_amount;
3694 x_corr_dist_tab(l_invoice_distribution_id).corrected_quantity := l_corrected_quantity;
3695 x_corr_dist_tab(l_invoice_distribution_id).unit_price := x_correction_price;
3696 x_corr_dist_tab(l_invoice_distribution_id).pa_quantity := l_corrected_quantity;
3697 x_corr_dist_tab(l_invoice_distribution_id).dist_ccid := l_dist_ccid;
3698
3699 --Calculate the index of the max of the largest distribution for
3700 --proration/base amount rounding.
3701 IF (l_amount >= l_max_dist_amount) THEN
3702 l_rounding_index := l_invoice_distribution_id;
3703 l_max_dist_amount := l_max_dist_amount;
3704 END IF;
3705
3706 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
3707
3708 END LOOP;
3709
3710 CLOSE quantity_correction_cursor;
3711
3712 --Perform Proration Rounding before the base amounts are populated
3713
3714 IF (l_sum_prorated_amount <> x_correction_amount and l_rounding_index is not null) THEN
3715 x_corr_dist_tab(l_rounding_index).amount := x_corr_dist_tab(l_rounding_index).amount +
3716 (x_correction_amount - l_sum_prorated_amount);
3717 END IF;
3718
3719 END IF; /*x_match_mode in 'STD-PS'... */
3720
3721 END IF; /*x_correction_type ...*/
3722
3723
3724 --For the case when user distributes the correction, we still
3725 --need to populate the PL/SQL table with invoice_distribution_id...
3726 IF (x_match_mode IN ('STD-PD','CR-PD')) THEN
3727
3728 FOR i IN nvl(x_corr_dist_tab.first,0) ..nvl(x_corr_dist_tab.last,0) LOOP
3729
3730 IF (x_corr_dist_tab.exists(i)) THEN
3731
3732 SELECT ap_invoice_distributions_s.nextval
3733 INTO x_corr_dist_tab(i).invoice_distribution_id
3734 FROM DUAL;
3735
3736 x_corr_dist_tab(i).pa_quantity := x_corr_dist_tab(i).corrected_quantity;
3737
3738 --Calculate the index of the max of the largest distribution for
3739 --base amount rounding. For this case there will be no proration
3740 --rounding as the user distributes the correction quantity.
3741
3742 --Also we will need this index only for foreign currency invoices only.
3743
3744 IF (g_exchange_rate IS NOT NULL) THEN
3745 IF (x_corr_dist_tab(i).amount > l_max_dist_amount) THEN
3746 l_rounding_index := i;
3747 l_max_dist_amount := x_corr_dist_tab(i).amount;
3748 END IF;
3749 END IF;
3750
3751 END IF;
3752
3753 END LOOP;
3754
3755 END IF; /*x_match_mode IN ('STD-PD','CR-PD' */
3756
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 --Populating the base_amount column, after proration related rounding
3763 --has been done if it is a foreign currency invoice.
3764
3765 IF (g_exchange_rate IS NOT NULL) THEN
3766 x_corr_dist_tab(i).base_amount := ap_utilities_pkg.ap_round_currency(
3767 x_corr_dist_tab(i).amount * g_exchange_rate,
3768 g_base_currency_code);
3769
3770 l_sum_dist_base_amount := l_sum_dist_base_amount + x_corr_dist_tab(i).base_amount ;
3771 END IF;
3772
3773 END IF;
3774
3775 END LOOP;
3776
3777 --Base Amount Rounding for foreign currency invoices only.
3778 --If it is a foreign currency invoice g_exchange_rate not be NULL
3779 IF (g_exchange_rate IS NOT NULL AND g_line_base_amount <> l_sum_dist_base_amount and l_rounding_index is not null) THEN
3780
3781 x_corr_dist_tab(l_rounding_index).base_amount := x_corr_dist_tab(l_rounding_index).base_amount +
3782 (g_line_base_amount - l_sum_dist_base_amount);
3783
3784 x_corr_dist_tab(l_rounding_index).rounding_amt := g_line_base_amount - l_sum_dist_base_amount;
3785
3786 END IF;
3787
3788 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3789 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Corr_Dist_Proration_Info(-)');
3790 END IF;
3791
3792
3793 EXCEPTION
3794 WHEN others then
3795 If (SQLCODE <> -20001) Then
3796 fnd_message.set_name('SQLAP','AP_DEBUG');
3797 fnd_message.set_token('ERROR',SQLERRM);
3798 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
3799 fnd_message.set_token('PARAMETERS',
3800 ' corrected_invoice_id = '||to_char(x_corrected_invoice_id)
3801 ||', corrected_line_number = '||to_char(x_corrected_line_number)
3802 ||', correction quantity ='||to_char(x_correction_quantity)
3803 ||', correction amount = '||to_char(x_correction_amount)
3804 ||', correction price = '||to_char(x_correction_price)
3805 ||', correction type = '||x_correction_type);
3806 fnd_message.set_token('DEBUG_INFO',l_debug_info);
3807 End if;
3808 --Clean up the PL/SQL tables on error
3809 x_corr_dist_tab.delete;
3810
3811 app_exception.raise_exception;
3812
3813 END Get_Corr_Dist_Proration_Info;
3814
3815
3816
3817 PROCEDURE Update_Corr_Po_Shipments_Dists(
3818 X_Corr_Dist_Tab IN CORR_DIST_TAB_TYPE,
3819 X_Po_Line_Location_Id IN NUMBER,
3820 X_Quantity IN NUMBER,
3821 X_Amount IN NUMBER,
3822 X_Correction_Type IN VARCHAR2,
3823 X_Uom_Lookup_Code IN VARCHAR2,
3824 X_Calling_Sequence IN VARCHAR2) IS
3825
3826 i NUMBER;
3827 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
3828 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
3829 l_debug_info VARCHAR2(2000);
3830 current_calling_sequence VARCHAR2(2000);
3831 l_api_name VARCHAR2(50);
3832 l_return_status VARCHAR2(100);
3833 l_msg_data VARCHAR2(4000);
3834
3835 BEGIN
3836
3837 l_api_name := 'Update_Corr_Po_Shipments_Dists';
3838
3839 current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
3840
3841 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3842 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
3843 END IF;
3844
3845 l_debug_info := 'Create l_po_ap_dist_rec object';
3846 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3847 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3848 END IF;
3849
3850 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
3851
3852 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
3853 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3854 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3855 END IF;
3856
3857 IF (x_correction_type = 'QTY_CORRECTION') THEN
3858
3859 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
3860 p_po_line_location_id => x_po_line_location_id,
3861 p_uom_code => x_uom_lookup_code,
3862 p_quantity_billed => x_quantity,
3863 p_amount_billed => NULL,
3864 p_quantity_financed => NULL,
3865 p_amount_financed => NULL,
3866 p_quantity_recouped => NULL,
3867 p_amount_recouped => NULL,
3868 p_retainage_withheld_amt => NULL,
3869 p_retainage_released_amt => NULL
3870 );
3871
3872 ELSIF (x_correction_type = 'PRICE_CORRECTION') THEN
3873
3874 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
3875 p_po_line_location_id => x_po_line_location_id,
3876 p_uom_code => x_uom_lookup_code,
3877 p_quantity_billed => NULL,
3878 p_amount_billed => NULL,
3879 p_quantity_financed => NULL,
3880 p_amount_financed => NULL,
3881 p_quantity_recouped => NULL,
3882 p_amount_recouped => NULL,
3883 p_retainage_withheld_amt => NULL,
3884 p_retainage_released_amt => NULL
3885 );
3886
3887 END IF;
3888
3889
3890 l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
3891 ||' with quantity/amount billed information.';
3892
3893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3894 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3895 END IF;
3896
3897 FOR i in nvl(x_corr_dist_tab.first,0)..nvl(x_corr_dist_tab.last,0) LOOP
3898
3899 IF (x_corr_dist_tab.exists(i)) THEN
3900
3901 IF (x_correction_type = 'PRICE_CORRECTION') THEN
3902
3903 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_corr_dist_tab(i).po_distribution_id,
3904 p_uom_code => x_uom_lookup_code,
3905 p_quantity_billed => NULL,
3906 p_amount_billed => x_corr_dist_tab(i).amount,
3907 p_quantity_financed => NULL,
3908 p_amount_financed => NULL,
3909 p_quantity_recouped => NULL,
3910 p_amount_recouped => NULL,
3911 p_retainage_withheld_amt => NULL,
3912 p_retainage_released_amt => NULL);
3913
3914 ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3915
3916 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_corr_dist_tab(i).po_distribution_id,
3917 p_uom_code => x_uom_lookup_code,
3918 p_quantity_billed => x_corr_dist_tab(i).corrected_quantity,
3919 p_amount_billed => x_corr_dist_tab(i).amount,
3920 p_quantity_financed => NULL,
3921 p_amount_financed => NULL,
3922 p_quantity_recouped => NULL,
3923 p_amount_recouped => NULL,
3924 p_retainage_withheld_amt => NULL,
3925 p_retainage_released_amt => NULL);
3926
3927 END IF;
3928
3929 END IF;
3930
3931 END LOOP;
3932
3933 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3934 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3935 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3936 END IF;
3937
3938 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
3939 P_Api_Version => 1.0,
3940 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
3941 P_Dist_Changes_Rec => l_po_ap_dist_rec,
3942 X_Return_Status => l_return_status,
3943 X_Msg_Data => l_msg_data);
3944
3945
3946 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3947 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Shipments_Dists(-)');
3948 END IF;
3949
3950
3951 EXCEPTION
3952 WHEN others then
3953 If (SQLCODE <> -20001) Then
3954 fnd_message.set_name('SQLAP','AP_DEBUG');
3955 fnd_message.set_token('ERROR',SQLERRM);
3956 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
3957 fnd_message.set_token('PARAMETERS',
3958 ' po_distribution_id = '||to_char(x_corr_dist_tab(i).po_distribution_id)
3959 ||', correction_type = '||x_correction_type);
3960 fnd_message.set_token('DEBUG_INFO',l_debug_info);
3961 End if;
3962
3963 app_exception.raise_exception;
3964
3965 END Update_Corr_Po_Shipments_Dists;
3966
3967
3968
3969 PROCEDURE Update_Corr_Po_Line_Locations(
3970 x_po_line_location_id IN NUMBER,
3971 x_quantity IN NUMBER,
3972 x_amount IN NUMBER,
3973 x_correction_type IN VARCHAR2,
3974 x_uom_lookup_code IN VARCHAR2,
3975 x_calling_sequence IN VARCHAR2) IS
3976
3977 l_debug_info VARCHAR2(2000);
3978 current_calling_sequence VARCHAR2(2000);
3979 l_api_name VARCHAR2(50);
3980
3981 BEGIN
3982
3983 l_api_name := 'Update_Corr_Po_Line_Locations';
3984 current_calling_sequence := ' Update_Corr_Po_Line_Locations<-'||x_calling_sequence;
3985
3986 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3987 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Line_Locations(+)');
3988 END IF;
3989
3990 l_debug_info := 'Call PO api to update the po_line_location with quantity/amount billed
3991 information';
3992
3993 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3994 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3995 END IF;
3996
3997 IF (x_correction_type = 'QTY_CORRECTION') THEN
3998
3999 RCV_BILL_UPDATING_SV.ap_update_po_line_locations(
4000 x_po_line_location_id => x_po_line_location_id,
4001 x_quantity_billed => x_quantity,
4002 x_uom_lookup_code => x_uom_lookup_code,
4003 x_amount_billed => NULL,
4004 x_matching_basis => 'QUANTITY');
4005
4006 END IF;
4007
4008 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4009 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Line_Locations(-)');
4010 END IF;
4011
4012
4013 EXCEPTION
4014 WHEN others then
4015 If (SQLCODE <> -20001) Then
4016 fnd_message.set_name('SQLAP','AP_DEBUG');
4017 fnd_message.set_token('ERROR',SQLERRM);
4018 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4019 fnd_message.set_token('PARAMETERS',
4020 ' po_line_location_id = '||to_char(x_po_line_location_id)
4021 ||', quantity = '|| to_char(x_quantity)
4022 ||', amount = '|| to_char(x_amount)
4023 ||', correction_type = '||x_correction_type);
4024 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4025 End if;
4026 app_exception.raise_exception;
4027
4028 END Update_Corr_Po_Line_Locations;
4029
4030
4031 PROCEDURE Insert_Corr_Invoice_Line(x_invoice_id IN NUMBER,
4032 x_invoice_line_number IN NUMBER,
4033 x_corrected_invoice_id IN NUMBER,
4034 x_corrected_line_number IN NUMBER,
4035 x_quantity IN NUMBER,
4036 x_amount IN NUMBER,
4037 x_unit_price IN NUMBER,
4038 x_correction_type IN VARCHAR2,
4039 x_final_match_flag IN VARCHAR2,
4040 x_po_distribution_id IN NUMBER,
4041 x_retained_amount IN NUMBER DEFAULT NULL,
4042 x_calling_sequence IN VARCHAR2 ) IS
4043
4044 l_debug_info VARCHAR2(2000);
4045 current_calling_sequence VARCHAR2(2000);
4046 l_api_name VARCHAR2(50);
4047
4048 BEGIN
4049
4050 l_api_name := 'Insert_Corr_Invoice_Line';
4051
4052 current_calling_sequence := 'Insert_Corr_Invoice_Line<-'||x_calling_sequence;
4053
4054 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4055 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Line(+)');
4056 END IF;
4057
4058 INSERT INTO AP_INVOICE_LINES(INVOICE_ID,
4059 LINE_NUMBER,
4060 LINE_TYPE_LOOKUP_CODE,
4061 REQUESTER_ID,
4062 DESCRIPTION,
4063 LINE_SOURCE,
4064 ORG_ID,
4065 INVENTORY_ITEM_ID,
4066 ITEM_DESCRIPTION,
4067 SERIAL_NUMBER,
4068 MANUFACTURER,
4069 MODEL_NUMBER,
4070 GENERATE_DISTS,
4071 MATCH_TYPE,
4072 DISTRIBUTION_SET_ID,
4073 ACCOUNT_SEGMENT,
4074 BALANCING_SEGMENT,
4075 COST_CENTER_SEGMENT,
4076 OVERLAY_DIST_CODE_CONCAT,
4077 DEFAULT_DIST_CCID,
4078 PRORATE_ACROSS_ALL_ITEMS,
4079 LINE_GROUP_NUMBER,
4080 ACCOUNTING_DATE,
4081 PERIOD_NAME,
4082 DEFERRED_ACCTG_FLAG,
4083 DEF_ACCTG_START_DATE,
4084 DEF_ACCTG_END_DATE,
4085 DEF_ACCTG_NUMBER_OF_PERIODS,
4086 DEF_ACCTG_PERIOD_TYPE,
4087 SET_OF_BOOKS_ID,
4088 AMOUNT,
4089 BASE_AMOUNT,
4090 ROUNDING_AMT,
4091 QUANTITY_INVOICED,
4092 UNIT_MEAS_LOOKUP_CODE,
4093 UNIT_PRICE,
4094 WFAPPROVAL_STATUS,
4095 -- USSGL_TRANSACTION_CODE,- Bug 4277744
4096 DISCARDED_FLAG,
4097 ORIGINAL_AMOUNT,
4098 ORIGINAL_BASE_AMOUNT,
4099 ORIGINAL_ROUNDING_AMT,
4100 CANCELLED_FLAG,
4101 INCOME_TAX_REGION,
4102 TYPE_1099,
4103 STAT_AMOUNT,
4104 PREPAY_INVOICE_ID,
4105 PREPAY_LINE_NUMBER,
4106 INVOICE_INCLUDES_PREPAY_FLAG,
4107 CORRECTED_INV_ID,
4108 CORRECTED_LINE_NUMBER,
4109 PO_HEADER_ID,
4110 PO_LINE_ID,
4111 PO_RELEASE_ID,
4112 PO_LINE_LOCATION_ID,
4113 PO_DISTRIBUTION_ID,
4114 RCV_TRANSACTION_ID,
4115 FINAL_MATCH_FLAG,
4116 ASSETS_TRACKING_FLAG,
4117 ASSET_BOOK_TYPE_CODE,
4118 ASSET_CATEGORY_ID,
4119 PROJECT_ID,
4120 TASK_ID,
4121 EXPENDITURE_TYPE,
4122 EXPENDITURE_ITEM_DATE,
4123 EXPENDITURE_ORGANIZATION_ID,
4124 PA_QUANTITY,
4125 PA_CC_AR_INVOICE_ID,
4126 PA_CC_AR_INVOICE_LINE_NUM,
4127 PA_CC_PROCESSED_CODE,
4128 AWARD_ID,
4129 AWT_GROUP_ID,
4130 REFERENCE_1,
4131 REFERENCE_2,
4132 RECEIPT_VERIFIED_FLAG,
4133 RECEIPT_REQUIRED_FLAG,
4134 RECEIPT_MISSING_FLAG,
4135 JUSTIFICATION,
4136 EXPENSE_GROUP,
4137 START_EXPENSE_DATE,
4138 END_EXPENSE_DATE,
4139 RECEIPT_CURRENCY_CODE,
4140 RECEIPT_CONVERSION_RATE,
4141 RECEIPT_CURRENCY_AMOUNT,
4142 DAILY_AMOUNT,
4143 WEB_PARAMETER_ID,
4144 ADJUSTMENT_REASON,
4145 MERCHANT_DOCUMENT_NUMBER,
4146 MERCHANT_NAME,
4147 MERCHANT_REFERENCE,
4148 MERCHANT_TAX_REG_NUMBER,
4149 MERCHANT_TAXPAYER_ID,
4150 COUNTRY_OF_SUPPLY,
4151 CREDIT_CARD_TRX_ID,
4152 COMPANY_PREPAID_INVOICE_ID,
4153 CC_REVERSAL_FLAG,
4154 ATTRIBUTE_CATEGORY,
4155 ATTRIBUTE1,
4156 ATTRIBUTE2,
4157 ATTRIBUTE3,
4158 ATTRIBUTE4,
4159 ATTRIBUTE5,
4160 ATTRIBUTE6,
4161 ATTRIBUTE7,
4162 ATTRIBUTE8,
4163 ATTRIBUTE9,
4164 ATTRIBUTE10,
4165 ATTRIBUTE11,
4166 ATTRIBUTE12,
4167 ATTRIBUTE13,
4168 ATTRIBUTE14,
4169 ATTRIBUTE15,
4170 /* OPEN ISSUE 1*/
4171 /* GLOBAL_ATTRIBUTE_CATEGORY,
4172 GLOBAL_ATTRIBUTE1,
4173 GLOBAL_ATTRIBUTE2,
4174 GLOBAL_ATTRIBUTE3,
4175 GLOBAL_ATTRIBUTE4,
4176 GLOBAL_ATTRIBUTE5,
4177 GLOBAL_ATTRIBUTE6,
4178 GLOBAL_ATTRIBUTE7,
4179 GLOBAL_ATTRIBUTE8,
4180 GLOBAL_ATTRIBUTE9,
4181 GLOBAL_ATTRIBUTE10,
4182 GLOBAL_ATTRIBUTE11,
4183 GLOBAL_ATTRIBUTE12,
4184 GLOBAL_ATTRIBUTE13,
4185 GLOBAL_ATTRIBUTE14,
4186 GLOBAL_ATTRIBUTE15,
4187 GLOBAL_ATTRIBUTE16,
4188 GLOBAL_ATTRIBUTE17,
4189 GLOBAL_ATTRIBUTE18,
4190 GLOBAL_ATTRIBUTE19,
4191 GLOBAL_ATTRIBUTE20, */
4192 CREATION_DATE,
4193 CREATED_BY,
4194 LAST_UPDATED_BY,
4195 LAST_UPDATE_DATE,
4196 LAST_UPDATE_LOGIN,
4197 PROGRAM_APPLICATION_ID,
4198 PROGRAM_ID,
4199 PROGRAM_UPDATE_DATE,
4200 REQUEST_ID,
4201 RETAINED_AMOUNT,
4202 RETAINED_AMOUNT_REMAINING,
4203 --ETAX: Invwkb
4204 SHIP_TO_LOCATION_ID,
4205 PRIMARY_INTENDED_USE,
4206 PRODUCT_FISC_CLASSIFICATION,
4207 TRX_BUSINESS_CATEGORY,
4208 PRODUCT_TYPE,
4209 PRODUCT_CATEGORY,
4210 USER_DEFINED_FISC_CLASS,
4211 PAY_AWT_GROUP_ID
4212 )
4213 SELECT x_invoice_id, --invoice_id
4214 x_invoice_line_number, --line_number
4215 'ITEM', --line_type_lookup_code
4216 ail.requester_id, --requester_id
4217 ail.description, --description
4218 'HEADER CORRECTION', --line_source
4219 ail.org_id, --org_id
4220 ail.inventory_item_id, --inventory_item_id
4221 ail.item_description, --item_description
4222 ail.serial_number, --serial_number
4223 ail.manufacturer, --manufacturer
4224 ail.model_number, --model_number
4225 'D', --generate_dists
4226 x_correction_type, --match_type
4227 NULL, --distribution_set_id
4228 ail.account_segment, --account_segment
4229 ail.balancing_segment, --balancing_segment
4230 ail.cost_center_segment, --cost_center_segment
4231 ail.overlay_dist_code_concat, --overlay_dist_code_concat
4232 ail.default_dist_ccid, --default_dist_ccid
4233 'N', --prorate_across_all_items
4234 NULL, --line_group_number
4235 g_accounting_date, --accounting_date
4236 g_period_name, --period_name
4237 'N', --deferred_acctg_flag
4238 NULL, --def_acctg_start_date
4239 NULL, --def_acctg_end_date
4240 NULL, --def_acctg_number_of_periods
4241 NULL, --def_acctg_period_type
4242 g_set_of_books_id, --set_of_books_id
4243 x_amount, --amount
4244 AP_UTILITIES_PKG.Ap_Round_Currency(
4245 NVL(X_AMOUNT, 0) * G_EXCHANGE_RATE,
4246 G_BASE_CURRENCY_CODE), --base_amount
4247 NULL, --rounding_amount
4248 x_quantity, --quantity_invoiced
4249 ail.unit_meas_lookup_code, --unit_meas_lookup_code
4250 x_unit_price, --unit_price
4251 decode(g_approval_workflow_flag,'Y'
4252 ,'REQUIRED','NOT REQUIRED'),--wf_approval_status
4253 -- Removed for bug 4277744
4254 -- g_ussgl_transaction_code, --ussgl_transaction_code
4255 'N', --discarded_flag
4256 NULL, --original_amount
4257 NULL, --original_base_amount
4258 NULL, --original_rounding_amt
4259 'N', --cancelled_flag
4260 g_income_tax_region, --income_tax_region
4261 pll.type_1099, --type_1099
4262 NULL, --stat_amount
4263 NULL, --prepay_invoice_id
4264 NULL, --prepay_line_number
4265 NULL, --invoice_includes_prepay_flag
4266 x_corrected_invoice_id, --corrected_invoice_id
4267 x_corrected_line_number, --corrected_line_number
4268 ail.po_header_id, --po_header_id
4269 ail.po_line_id, --po_line_id
4270 ail.po_release_id, --release_id
4271 ail.po_line_location_id, --po_line_location_id
4272 nvl(ail.po_distribution_id,
4273 x_po_distribution_id), --po_distribution_id
4274 NULL, --rcv_transaction_id
4275 x_final_match_flag, --final_match_flag
4276 ail.assets_tracking_flag, --assets_tracking_flag
4277 ail.asset_book_type_code, --asset_book_type_code
4278 ail.asset_category_id, --asset_category_id
4279 ail.project_id, --project_id
4280 ail.task_id, --task_id
4281 ail.expenditure_type, --expenditure_type
4282 ail.expenditure_item_date, --expenditure_item_date
4283 ail.expenditure_organization_id, --expenditure_organization_id
4284 decode(ail.project_id,'','',
4285 decode(x_quantity,'',(ail.pa_quantity*x_amount/ail.amount),
4286 x_quantity)), --pa_quantity
4287 NULL, --pa_cc_ar_invoice_id
4288 NULL, --pa_cc_ar_invoice_line_num
4289 NULL, --pa_cc_processed_code
4290 ail.award_id, --award_id
4291 g_awt_group_id, --awt_group_id
4292 ail.reference_1, --reference_1
4293 ail.reference_2, --reference_2
4294 ail.receipt_verified_flag, --receipt_verified_flag
4295 ail.receipt_required_flag, --receipt_required_flag
4296 ail.receipt_missing_flag, --receipt_missing_flag
4297 ail.justification, --ail.justification
4298 ail.expense_group, --ail.expense_group
4299 ail.start_expense_date, --start_expense_date
4300 ail.end_expense_date, --end_expense_date
4301 ail.receipt_currency_code, --receipt_currency_code
4302 ail.receipt_conversion_rate, --receipt_conversion_rate
4303 ail.receipt_currency_amount, --receipt_currency_amount
4304 ail.daily_amount, --daily_amount
4305 ail.web_parameter_id, --web_parameter_id
4306 ail.adjustment_reason, --adjustment_reason
4307 ail.merchant_document_number, --merchant_document_number
4308 ail.merchant_name, --merchant_name
4309 ail.merchant_reference, --merchant_reference
4310 ail.merchant_tax_reg_number, --merchant_tax_reg_number
4311 ail.merchant_taxpayer_id, --merchant_taxpayer_id
4312 ail.country_of_supply, --country_of_supply
4313 ail.credit_card_trx_id, --credit_card_trx_id
4314 ail.company_prepaid_invoice_id, --cpmany_prepaid_invoice_id
4315 ail.cc_reversal_flag, --cc_reversal_flag
4316 ail.attribute_category, --attribute_category
4317 ail.attribute1, --attribute1
4318 ail.attribute2, --attribute2
4319 ail.attribute3, --attribute3
4320 ail.attribute4, --attribute4
4321 ail.attribute5, --attribute5
4322 ail.attribute6, --attribute6
4323 ail.attribute7, --attribute7
4324 ail.attribute8, --attribute8
4325 ail.attribute9, --attribute9
4326 ail.attribute10, --attribute10
4327 ail.attribute11, --attribute11
4328 ail.attribute12, --attribute12
4329 ail.attribute13, --attribute13
4330 ail.attribute14, --attribute14
4331 ail.attribute15, --attribute15
4332 /*OPEN ISSUE 1*/
4333 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
4334 X_GLOBAL_ATTRIBUTE1,
4335 X_GLOBAL_ATTRIBUTE2,
4336 X_GLOBAL_ATTRIBUTE3,
4337 X_GLOBAL_ATTRIBUTE4,
4338 X_GLOBAL_ATTRIBUTE5,
4339 X_GLOBAL_ATTRIBUTE6,
4340 X_GLOBAL_ATTRIBUTE7,
4341 X_GLOBAL_ATTRIBUTE8,
4342 X_GLOBAL_ATTRIBUTE9,
4343 X_GLOBAL_ATTRIBUTE10,
4344 X_GLOBAL_ATTRIBUTE11,
4345 X_GLOBAL_ATTRIBUTE12,
4346 X_GLOBAL_ATTRIBUTE13,
4347 X_GLOBAL_ATTRIBUTE14,
4348 X_GLOBAL_ATTRIBUTE15,
4349 X_GLOBAL_ATTRIBUTE16,
4350 X_GLOBAL_ATTRIBUTE17,
4351 X_GLOBAL_ATTRIBUTE18,
4352 X_GLOBAL_ATTRIBUTE19,
4353 X_GLOBAL_ATTRIBUTE20, */
4354 sysdate, --creation_date
4355 g_user_id, --created_by
4356 g_user_id, --last_updated_by
4357 sysdate, --last_update_date
4358 g_login_id, --user_login_id
4359 NULL, --program_application_id
4360 NULL, --program_id
4361 NULL, --program_update_date
4362 NULL, --request_id
4363 x_retained_amount, --retained_amount
4364 (-x_retained_amount), --retained_amount_remaining
4365 --ETAX: Invwkb
4366 PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
4367 AIL.PRIMARY_INTENDED_USE, --primary_intended_use
4368 AIL.PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
4369 G_TRX_BUSINESS_CATEGORY, --trx_business_category
4370 AIL.PRODUCT_TYPE, --product_type
4371 AIL.PRODUCT_CATEGORY, --product_category
4372 AIL.USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
4373 g_pay_awt_group_id --pay_awt_group_id bug8222382
4374 FROM ap_invoices ai,
4375 ap_invoice_lines ail,
4376 po_line_locations_ap_v pll
4377 WHERE ai.invoice_id = x_corrected_invoice_id
4378 AND ail.invoice_id = ai.invoice_id
4379 AND ail.line_number = x_corrected_line_number
4380 AND pll.line_location_id = ail.po_line_location_id;
4381
4382 g_max_invoice_line_number := g_max_invoice_line_number + 1;
4383
4384
4385 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4386 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Line(-)');
4387 END IF;
4388
4389
4390 EXCEPTION
4391 WHEN others then
4392 If (SQLCODE <> -20001) Then
4393 fnd_message.set_name('SQLAP','AP_DEBUG');
4394 fnd_message.set_token('ERROR',SQLERRM);
4395 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4396 fnd_message.set_token('PARAMETERS',
4397 ' invoice_id = '||to_char(x_invoice_id)
4398 ||', invoice_line_number = ' ||to_char(x_invoice_line_number)
4399 ||', corrected_invoice_id = '||to_char(x_corrected_invoice_id)
4400 ||', corrected_line_number = '||to_char(x_corrected_line_number)
4401 ||', quantity = '||to_char(x_quantity)
4402 ||', amount = '||to_char(x_amount)
4403 ||', unit_price = '||to_char(x_unit_price)
4404 ||', correction_type = '||x_correction_type
4405 ||', final_match_flag = '||x_final_match_flag
4406 ||', po_distribution_id = '||to_char(x_po_distribution_id));
4407 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4408 End if;
4409 app_exception.raise_exception;
4410
4411
4412 END Insert_Corr_Invoice_Line;
4413
4414
4415 PROCEDURE Insert_Corr_Invoice_Dists(x_invoice_id IN NUMBER,
4416 x_invoice_line_number IN NUMBER,
4417 x_corrected_invoice_id IN NUMBER,
4418 x_corr_dist_tab IN OUT NOCOPY CORR_DIST_TAB_TYPE,
4419 x_correction_type IN VARCHAR2,
4420 x_final_match_flag IN VARCHAR2,
4421 x_total_amount IN NUMBER,
4422 x_calling_sequence IN VARCHAR2) IS
4423
4424 i NUMBER;
4425 l_distribution_line_number ap_invoice_distributions.distribution_line_number%type := 1;
4426 l_debug_info VARCHAR2(2000);
4427 current_calling_sequence VARCHAR2(2000);
4428 l_api_name VARCHAR2(50);
4429
4430 BEGIN
4431
4432 l_api_name := 'Insert_Corr_Invoice_Dists';
4433
4434 current_calling_sequence := 'Insert_Corr_Invoice_Dists<-'||x_calling_sequence;
4435
4436 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4437 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Dists(+)');
4438 END IF;
4439
4440 FOR i in NVL(x_corr_dist_tab.FIRST,0) .. NVL(x_corr_dist_tab.LAST,0) LOOP
4441
4442 IF (x_corr_dist_tab.exists(i)) THEN
4443
4444 INSERT INTO AP_INVOICE_DISTRIBUTIONS(
4445 batch_id,
4446 invoice_id,
4447 invoice_line_number,
4448 invoice_distribution_id,
4449 distribution_line_number,
4450 line_type_lookup_code,
4451 description,
4452 dist_match_type,
4453 distribution_class,
4454 org_id,
4455 dist_code_combination_id,
4456 accounting_date,
4457 period_name,
4458 amount_to_post,
4459 base_amount_to_post,
4460 posted_amount,
4461 posted_base_amount,
4462 je_batch_id,
4463 cash_je_batch_id,
4464 posted_flag,
4465 accounting_event_id,
4466 upgrade_posted_amt,
4467 upgrade_base_posted_amt,
4468 set_of_books_id,
4469 amount,
4470 base_amount,
4471 rounding_amt,
4472 match_status_flag,
4473 encumbered_flag,
4474 packet_id,
4475 -- ussgl_transaction_code, - Bug 4277744
4476 -- ussgl_trx_code_context, - Bug 4277744
4477 reversal_flag,
4478 parent_reversal_id,
4479 cancellation_flag,
4480 income_tax_region,
4481 type_1099,
4482 stat_amount,
4483 charge_applicable_to_dist_id,
4484 prepay_amount_remaining,
4485 prepay_distribution_id,
4486 parent_invoice_id,
4487 corrected_invoice_dist_id,
4488 corrected_quantity,
4489 other_invoice_id,
4490 po_distribution_id,
4491 rcv_transaction_id,
4492 unit_price,
4493 matched_uom_lookup_code,
4494 quantity_invoiced,
4495 final_match_flag,
4496 related_id,
4497 assets_addition_flag,
4498 assets_tracking_flag,
4499 asset_book_type_code,
4500 asset_category_id,
4501 project_id,
4502 task_id,
4503 expenditure_type,
4504 expenditure_item_date,
4505 expenditure_organization_id,
4506 pa_quantity,
4507 pa_addition_flag,
4508 pa_cc_ar_invoice_id,
4509 pa_cc_ar_invoice_line_num,
4510 pa_cc_processed_code,
4511 award_id,
4512 gms_burdenable_raw_cost,
4513 awt_flag,
4514 awt_group_id,
4515 awt_tax_rate_id,
4516 awt_gross_amount,
4517 awt_invoice_id,
4518 awt_origin_group_id,
4519 awt_invoice_payment_id,
4520 awt_withheld_amt,
4521 inventory_transfer_status,
4522 reference_1,
4523 reference_2,
4524 receipt_verified_flag,
4525 receipt_required_flag,
4526 receipt_missing_flag,
4527 justification,
4528 expense_group,
4529 start_expense_date,
4530 end_expense_date,
4531 receipt_currency_code,
4532 receipt_conversion_rate,
4533 receipt_currency_amount,
4534 daily_amount,
4535 web_parameter_id,
4536 adjustment_reason,
4537 merchant_document_number,
4538 merchant_name,
4539 merchant_reference,
4540 merchant_tax_reg_number,
4541 merchant_taxpayer_id,
4542 country_of_supply,
4543 credit_card_trx_id,
4544 company_prepaid_invoice_id,
4545 cc_reversal_flag,
4546 attribute_category,
4547 attribute1,
4548 attribute2,
4549 attribute3,
4550 attribute4,
4551 attribute5,
4552 attribute6,
4553 attribute7,
4554 attribute8,
4555 attribute9,
4556 attribute10,
4557 attribute11,
4558 attribute12,
4559 attribute13,
4560 attribute14,
4561 attribute15,
4562 /*OPEN ISSUE 1*/
4563 /*global_attribute_category,
4564 global_attribute1,
4565 global_attribute2,*/
4566 --bugfix:4674194
4567 global_attribute3,
4568 /*global_attribute4,
4569 global_attribute5,
4570 global_attribute6,
4571 global_attribute7,
4572 global_attribute8,
4573 global_attribute9,
4574 global_attribute10,
4575 global_attribute11,
4576 global_attribute12,
4577 global_attribute13,
4578 global_attribute14,
4579 global_attribute15,
4580 global_attribute16,
4581 global_attribute17,
4582 global_attribute18,
4583 global_attribute19,
4584 global_attribute20,*/
4585 created_by,
4586 creation_date,
4587 last_updated_by,
4588 last_update_date,
4589 last_update_login,
4590 program_application_id,
4591 program_id,
4592 program_update_date,
4593 request_id,
4594 --ETAX:Invwkb
4595 intended_use ,
4596 accrual_posted_flag,
4597 cash_posted_flag,
4598 --Freight and Special Charges
4599 rcv_charge_addition_flag,
4600 pay_awt_group_id
4601 --bug8222382
4602 )
4603 SELECT g_batch_id, --batch_id
4604 x_invoice_id, --invoice_id
4605 x_invoice_line_number, --invoice_line_number
4606 x_corr_dist_tab(i).invoice_distribution_id, --invoice_distribution_id
4607 l_distribution_line_number, --distribution_line_number
4608 decode(x_correction_type,
4609 'PRICE_CORRECTION','IPV',
4610 'QTY_CORRECTION',aid.line_type_lookup_code), --line_type_lookup_code
4611 ail.description, --description
4612 x_correction_type, --dist_match_type
4613 'PERMANENT', --distribution_class
4614 ail.org_id, --org_id
4615 x_corr_dist_tab(i).dist_ccid, --dist_code_combination_id
4616 ail.accounting_date, --accounting_date
4617 ail.period_name, --period_name
4618 NULL, --amount_to_post
4619 NULL, --base_amount_to_post
4620 NULL, --posted_amount
4621 NULL, --posted_base_amount
4622 NULL, --je_batch_id
4623 NULL, --cash_je_batch_id
4624 'N', --posted_flag
4625 NULL, --accounting_event_id
4626 NULL, --upgrade_posted_amt
4627 NULL, --upgrade_base_posted_amt
4628 g_set_of_books_id, --set_of_books_id
4629 x_corr_dist_tab(i).amount, --amount
4630 x_corr_dist_tab(i).base_amount,--base_amount
4631 x_corr_dist_tab(i).rounding_amt,--rounding_amount
4632 NULL, --match_status_flag
4633 'N', --encumbered_flag
4634 NULL, --packet_id
4635 -- Removed for bug 4277744
4636 -- ail.ussgl_transaction_code, --ussgl_transaction_code
4637 -- NULL, --ussgl_trx_code_context
4638 'N', --reversal_flag
4639 NULL, --parent_reversal_id
4640 'N', --cancellation_flag
4641 DECODE(ail.type_1099,'','',ail.income_tax_region), --income_tax_region
4642 ail.type_1099, --type_1099
4643 NULL, --stat_amount
4644 NULL, --charge_applicable_to_dist_id
4645 NULL, --prepay_amount_remaining
4646 NULL, --prepay_distribution_id
4647 ail.corrected_inv_id, --parent_invoice_id
4648 x_corr_dist_tab(i).corrected_inv_dist_id, --corrected_invoice_dist_id
4649 x_corr_dist_tab(i).corrected_quantity, --corrected_quantity
4650 NULL, --other_invoice_id
4651 x_corr_dist_tab(i).po_distribution_id, --po_distribution_id
4652 NULL, --rcv_transaction_id
4653 x_corr_dist_tab(i).unit_price, --unit_price
4654 aid.matched_uom_lookup_code, --matched_uom_lookup_code
4655 NULL, --quantity_invoiced
4656 x_final_match_flag, --final_match_flag
4657 NULL, --related_id
4658 'U', --assets_addition_flag
4659 aid.assets_tracking_flag, --assets_tracking_flag
4660 decode(aid.assets_tracking_flag,'Y',
4661 ail.asset_book_type_code,NULL), --asset_book_type_code
4662 decode(aid.assets_tracking_flag,'Y',
4663 ail.asset_category_id,NULL), --asset_category_id
4664 aid.project_id, --project_id
4665 aid.task_id, --task_id
4666 aid.expenditure_type, --expenditure_type
4667 aid.expenditure_item_date, --expenditure_item_date
4668 aid.expenditure_organization_id, --expenditure_organization_id
4669 decode(aid.project_id,'','',
4670 x_corr_dist_tab(i).pa_quantity), --pa_quantity
4671 decode(aid.project_id,NULL,'E',
4672 decode(pd.destination_type_code,'SHOP FLOOR','M',
4673 'INVENTORY','M','N')), --pa_addition_flag
4674 NULL, --pa_cc_ar_invoice_id
4675 NULL, --pa_cc_ar_invoice_line_num
4676 NULL, --pa_cc_processed_code
4677 aid.award_id, --award_id
4678 NULL, --gms_burdenable_raw_cost
4679 NULL, --awt_flag
4680 decode(g_system_allow_awt_flag,'Y',
4681 decode(g_site_allow_awt_flag,'Y',
4682 ail.awt_group_id,NULL),
4683 NULL), --awt_group_id
4684 NULL, --awt_tax_rate_id
4685 NULL, --awt_gross_amount
4686 NULL, --awt_invoice_id
4687 NULL, --awt_origin_group_id
4688 NULL, --awt_invoice_payment_id
4689 NULL, --awt_withheld_amt
4690 'N', --inventory_transfer_status
4691 ail.reference_1, --reference_1
4692 ail.reference_2, --reference_2
4693 ail.receipt_verified_flag, --receipt_verified_flag
4694 ail.receipt_required_flag, --receipt_required_flag
4695 ail.receipt_missing_flag, --receipt_missing_flag
4696 ail.justification, --justification
4697 ail.expense_group, --expense_group
4698 ail.start_expense_date, --start_expense_date
4699 ail.end_expense_date, --end_expense_date
4700 ail.receipt_currency_code, --receipt_currency_code
4701 ail.receipt_conversion_rate, --receipt_conversion_rate
4702 ail.receipt_currency_amount, --receipt_currency_amount
4703 ail.daily_amount, --daily_amount
4704 ail.web_parameter_id, --web_parameter_id
4705 ail.adjustment_reason, --adjustment_reason
4706 ail.merchant_document_number, --merchant_document_number
4707 ail.merchant_name, --merchant_name
4708 ail.merchant_reference, --merchant_reference
4709 ail.merchant_tax_reg_number, --merchant_tax_reg_number
4710 ail.merchant_taxpayer_id, --merchant_taxpayer_id
4711 ail.country_of_supply, --country_of_supply
4712 ail.credit_card_trx_id, --credit_card_trx_id
4713 ail.company_prepaid_invoice_id, --company_prepaid_invoice_id
4714 ail.cc_reversal_flag, --cc_reversal_flag
4715 aid.attribute_category, --attribute_category
4716 aid.attribute1, --attribute1
4717 aid.attribute2, --attribute2
4718 aid.attribute3, --attribute3
4719 aid.attribute4, --attribute4
4720 aid.attribute5, --attribute5
4721 aid.attribute6, --attribute6
4722 aid.attribute7, --attribute7
4723 aid.attribute8, --attribute8
4724 aid.attribute9, --attribute9
4725 aid.attribute10, --attribute10
4726 aid.attribute11, --attribute11
4727 aid.attribute12, --attribute12
4728 aid.attribute13, --attribute13
4729 aid.attribute14, --attribute14
4730 aid.attribute15, --attribute15
4731 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
4732 X_GLOBAL_ATTRIBUTE1,
4733 X_GLOBAL_ATTRIBUTE2,*/
4734 --bugfix:4674194
4735 decode(ap_extended_withholding_pkg.ap_extended_withholding_option,
4736 'Y',ail.ship_to_location_id,''),
4737 /*X_GLOBAL_ATTRIBUTE4,
4738 X_GLOBAL_ATTRIBUTE5,
4739 X_GLOBAL_ATTRIBUTE6,
4740 X_GLOBAL_ATTRIBUTE7,
4741 X_GLOBAL_ATTRIBUTE8,
4742 X_GLOBAL_ATTRIBUTE9,
4743 X_GLOBAL_ATTRIBUTE10,
4744 X_GLOBAL_ATTRIBUTE11,
4745 X_GLOBAL_ATTRIBUTE12,
4746 X_GLOBAL_ATTRIBUTE13,
4747 X_GLOBAL_ATTRIBUTE14,
4748 X_GLOBAL_ATTRIBUTE15,
4749 X_GLOBAL_ATTRIBUTE16,
4750 X_GLOBAL_ATTRIBUTE17,
4751 X_GLOBAL_ATTRIBUTE18,
4752 X_GLOBAL_ATTRIBUTE19,
4753 X_GLOBAL_ATTRIBUTE20, */
4754 ail.created_by, --created_by
4755 sysdate, --creation_date
4756 ail.last_updated_by, --last_updated_by
4757 sysdate, --last_update_date
4758 ail.last_update_login, --last_update_login
4759 NULL, --program_application_id
4760 NULL, --program_id
4761 NULL, --program_update_date
4762 NULL, --request_id
4763 --ETAX: Invwkb
4764 aid.intended_use, --intended_use
4765 'N', --accrual_posted_flag
4766 'N', --cash_posted_flag
4767 'N', --rcv_charge_addition_flag
4768 decode(g_system_allow_awt_flag,'Y',
4769 decode(g_site_allow_awt_flag,'Y',
4770 ail.pay_awt_group_id,NULL),
4771 NULL) --pay_awt_group_id bug8222382
4772 FROM ap_invoice_lines ail,
4773 ap_invoice_distributions aid,
4774 po_distributions pd
4775 WHERE ail.invoice_id = x_invoice_id
4776 AND ail.line_number = x_invoice_line_number
4777 AND aid.invoice_id = ail.corrected_inv_id
4778 AND aid.invoice_line_number = ail.corrected_line_number
4779 AND aid.invoice_distribution_id = x_corr_dist_tab(i).corrected_inv_dist_id
4780 AND aid.po_distribution_id = pd.po_distribution_id;
4781
4782 --Bugfix:4674635
4783 l_debug_info := 'Call the AP_EXTENDED_MATCH to populate global attributes';
4784 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4785 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4786 END IF;
4787
4788 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
4789 AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_MATCH(
4790 P_Credit_Id => X_invoice_id,
4791 P_Invoice_Id => X_corrected_invoice_id,
4792 P_Inv_Line_Num => X_invoice_line_number,
4793 P_Distribution_id => x_corr_dist_tab(i).invoice_distribution_id,
4794 P_Parent_Dist_Id => x_corr_dist_tab(i).corrected_inv_dist_id);
4795
4796 END IF;
4797
4798 l_distribution_line_number := l_distribution_line_number + 1;
4799
4800 END IF; /*(x_corr_dist_tab.exists(i)) */
4801
4802 END LOOP;
4803
4804 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4805 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Insert_Corr_Invoice_Dists(-)');
4806 END IF;
4807
4808
4809 EXCEPTION
4810 WHEN others then
4811 If (SQLCODE <> -20001) Then
4812 fnd_message.set_name('SQLAP','AP_DEBUG');
4813 fnd_message.set_token('ERROR',SQLERRM);
4814 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4815 fnd_message.set_token('PARAMETERS',
4816 ' invoice_id = '||to_char(x_invoice_id)
4817 ||', invoice_line_number =' ||to_char(x_invoice_line_number)
4818 ||', correction_type = '||x_correction_type
4819 ||', final_match_flag = '||x_final_match_flag
4820 ||', total_amount = '||to_char(x_total_amount));
4821 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4822 End if;
4823
4824 --Clean up the PL/SQL tables on error
4825 x_corr_dist_tab.delete;
4826
4827 app_exception.raise_exception;
4828
4829 END Insert_Corr_Invoice_Dists;
4830
4831
4832 /*=============================================================================
4833 QUICK MATCH
4834 ==============================================================================*/
4835
4836
4837 PROCEDURE Quick_Match_Line_Generation(
4838 x_invoice_id IN NUMBER,
4839 x_po_header_id IN NUMBER,
4840 x_match_option IN VARCHAR2,
4841 x_invoice_amount IN NUMBER,
4842 x_calling_sequence IN VARCHAR2) IS
4843
4844 l_shipment_table T_SHIPMENT_TABLE;
4845 current_calling_sequence VARCHAR2(2000);
4846 l_debug_info VARCHAR2(2000);
4847 l_api_name VARCHAR2(50);
4848
4849 BEGIN
4850
4851 l_api_name := 'Quick_Match_Line_Generation';
4852
4853 current_calling_sequence := 'Quick_Match_PO_RCV<-'||x_calling_sequence;
4854
4855 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4856 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Quick_Match_Line_Generation(+)');
4857 END IF;
4858
4859 l_debug_info := 'Call Get_Info';
4860 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4861 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4862 END IF;
4863
4864 Get_Info(x_invoice_id => x_invoice_id,
4865 x_calling_sequence => current_calling_sequence);
4866
4867 IF g_invoice_type_lookup_code = 'RETAINAGE RELEASE' THEN
4868
4869 l_debug_info := 'Call Generate_Release_Lines';
4870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4871 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4872 END IF;
4873
4874 Generate_Release_Lines (p_po_header_id => x_po_header_id,
4875 p_invoice_id => x_invoice_id,
4876 p_release_amount => g_release_amount_net_of_tax,
4877 x_calling_sequence => current_calling_sequence);
4878
4879 ELSE
4880
4881 l_debug_info := 'Call Get_Shipment_List_For_QM';
4882 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4883 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4884 END IF;
4885
4886 Get_Shipment_List_For_QM(X_Invoice_Id => x_invoice_id,
4887 X_Po_Header_Id => x_po_header_id,
4888 X_Match_Option => x_match_option,
4889 X_Match_Amount => x_invoice_amount,
4890 X_Shipment_Table => l_shipment_table,
4891 X_Calling_Sequence => current_calling_sequence);
4892
4893 l_debug_info := 'Call Generate_Lines_For_Quickmatch';
4894 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4895 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4896 END IF;
4897
4898 Generate_Lines_for_Quickmatch(X_Invoice_Id => x_invoice_id,
4899 X_Match_Option => x_match_option,
4900 X_Shipment_Table => l_shipment_table,
4901 X_Calling_Sequence => current_calling_sequence);
4902
4903
4904 --Clean up the PL/SQL table
4905 l_shipment_table.delete;
4906
4907 END IF;
4908
4909 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4910 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Quick_Match_Line_Generation(-)');
4911 END IF;
4912
4913 EXCEPTION
4914 WHEN others then
4915 If (SQLCODE <> -20001) Then
4916 fnd_message.set_name('SQLAP','AP_DEBUG');
4917 fnd_message.set_token('ERROR',SQLERRM);
4918 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
4919 fnd_message.set_token('PARAMETERS',
4920 ' invoice_id = '||to_char(x_invoice_id)
4921 ||', Po header id = '||to_char(x_po_header_id)
4922 ||', match option = '||x_match_option
4923 ||', invoice_amount = '||to_char(x_invoice_amount));
4924 fnd_message.set_token('DEBUG_INFO',l_debug_info);
4925 End if;
4926 --Clean up the PL/SQL tables on error
4927 l_shipment_table.delete;
4928
4929 app_exception.raise_exception;
4930
4931 END Quick_Match_Line_Generation;
4932
4933
4934 PROCEDURE Get_Shipment_List_For_QM(X_Invoice_Id IN NUMBER,
4935 X_Po_Header_Id IN NUMBER,
4936 X_Match_Option IN VARCHAR2,
4937 X_Match_Amount IN NUMBER,
4938 X_Shipment_Table OUT NOCOPY T_SHIPMENT_TABLE,
4939 X_Calling_Sequence IN VARCHAR2) IS
4940
4941 CURSOR PO_Shipment_list_cursor IS
4942 SELECT pll.line_location_id,
4943 pll.po_line_id,
4944 pll.po_release_id,
4945 decode(pll.matching_basis_lookup_code,'AMOUNT',
4946 (pll.amount - decode(pll.shipment_type,'PREPAYMENT',
4947 nvl(pll.amount_financed,0),
4948 nvl(pll.amount_billed,0)
4949 )
4950 - nvl(pll.amount_cancelled,0)
4951 ),
4952 ap_utilities_pkg.ap_round_currency(
4953 (pll.quantity - decode(pll.shipment_type,'PREPAYMENT',
4954 nvl(pll.quantity_financed,0),
4955 nvl(pll.quantity_billed,0)
4956 )
4957 - nvl(pll.quantity_cancelled,0)
4958 ) * pll.price_override, g_invoice_currency_code
4959 )
4960 ) amount_available,
4961 pll.item_id,
4962 pll.unit_meas_lookup_code,
4963 pll.price_override,
4964 pll.item_description,
4965 msi.asset_category_id,
4966 -- pll.ussgl_transaction_code, - Bug 4277744
4967 pll.type_1099,
4968 pll.matching_basis_lookup_code,
4969 decode(g_transfer_flag,'Y',pll.attribute_category,NULL) attribute_category,
4970 decode(g_transfer_flag,'Y',pll.attribute1,NULL) attribute1,
4971 decode(g_transfer_flag,'Y',pll.attribute2,NULL) attribute2,
4972 decode(g_transfer_flag,'Y',pll.attribute3,NULL) attribute3,
4973 decode(g_transfer_flag,'Y',pll.attribute4,NULL) attribute4,
4974 decode(g_transfer_flag,'Y',pll.attribute5,NULL) attribute5,
4975 decode(g_transfer_flag,'Y',pll.attribute6,NULL) attribute6,
4976 decode(g_transfer_flag,'Y',pll.attribute7,NULL) attribute7,
4977 decode(g_transfer_flag,'Y',pll.attribute8,NULL) attribute8,
4978 decode(g_transfer_flag,'Y',pll.attribute9,NULL) attribute9,
4979 decode(g_transfer_flag,'Y',pll.attribute10,NULL) attribute10,
4980 decode(g_transfer_flag,'Y',pll.attribute11,NULL) attribute11,
4981 decode(g_transfer_flag,'Y',pll.attribute12,NULL) attribute12,
4982 decode(g_transfer_flag,'Y',pll.attribute13,NULL) attribute13,
4983 decode(g_transfer_flag,'Y',pll.attribute14,NULL) attribute14,
4984 decode(g_transfer_flag,'Y',pll.attribute15,NULL) attribute15
4985 --ETAX: Invwkb
4986 --OPEN ISSUE 1
4987 ,pll.ship_to_location_id ship_to_location_id
4988 /*
4989 pll.primary_intended_use primary_intended_use,
4990 pll.product_fisc_classification product_fisc_classification,
4991 pll.product_type product_type,
4992 pll.product_category product_category,
4993 pll.user_defined_fisc_class user_defined_fisc_class
4994 */
4995 FROM po_line_locations_ap_v pll,
4996 mtl_system_items msi
4997 WHERE pll.po_header_id = x_po_header_id
4998 AND msi.inventory_item_id(+) = pll.item_id
4999 AND msi.organization_id(+) = g_inventory_organization_id
5000 AND pll.match_option = 'P'
5001 --Make sure there is some quantity or amount left to be billed
5002 --based on the matching_basis of the shipment
5003 AND ((g_invoice_type_lookup_code <> 'PREPAYMENT'
5004 and (
5005 (pll.matching_basis_lookup_code = 'QUANTITY' and
5006 pll.quantity - nvl(pll.quantity_billed,0) - nvl(pll.quantity_cancelled,0) > 0
5007 ) or
5008 (pll.matching_basis_lookup_code = 'AMOUNT' and
5009 pll.amount - nvl(pll.amount_billed,0) - nvl(pll.amount_cancelled,0) > 0
5010 )
5011 )
5012 ) OR
5013 (g_invoice_type_lookup_code = 'PREPAYMENT'
5014 and (
5015 (pll.matching_basis_lookup_code = 'QUANTITY' and pll.shipment_type = 'PREPAYMENT' and
5016 pll.quantity - nvl(pll.quantity_financed,0) - nvl(pll.quantity_cancelled,0) > 0
5017 ) or
5018 (pll.matching_basis_lookup_code = 'AMOUNT' and pll.shipment_type = 'PREPAYMENT' and
5019 pll.amount - nvl(pll.amount_financed,0) - nvl(pll.amount_cancelled,0) > 0
5020 ) or
5021 (pll.matching_basis_lookup_code = 'QUANTITY' and pll.shipment_type <> 'PREPAYMENT' and
5022 pll.quantity - nvl(pll.quantity_billed,0) - nvl(pll.quantity_cancelled,0) > 0
5023 ) or
5024 (pll.matching_basis_lookup_code = 'AMOUNT' and pll.shipment_type <> 'PREPAYMENT' and
5025 pll.amount - nvl(pll.amount_billed,0) - nvl(pll.amount_cancelled,0) > 0
5026 )
5027 )
5028 )
5029 )
5030 --make sure the correct shipment type is matched to correct invoice type
5031 AND
5032 ((g_invoice_type_lookup_code <> 'PREPAYMENT' and
5033 pll.shipment_type <> 'PREPAYMENT'
5034 ) OR
5035 (g_invoice_type_lookup_code = 'PREPAYMENT' and
5036 ((pll.payment_type IS NOT NULL and
5037 pll.shipment_type = 'PREPAYMENT') or
5038 (pll.payment_type IS NULL)
5039 )
5040 )
5041 )
5042 ORDER BY pll.line_location_id ;
5043
5044
5045 CURSOR Receipt_Shipment_List_cursor IS
5046 SELECT rcv.po_line_id,
5047 rcv.po_release_id,
5048 rcv.po_line_location_id,
5049 rcv.rcv_transaction_id,
5050 rcv.receipt_uom_lookup_code,
5051 rcv.po_uom_lookup_code,
5052 rcv.po_unit_price,
5053 rcv.item_id,
5054 rcv.item_description,
5055 msi.asset_category_id,
5056 -- rsl.ussgl_transaction_code, - Bug 4277744
5057 rcv.type_1099,
5058 rcv.matching_basis_lookup_code,
5059 decode(g_transfer_flag,'Y',rsl.attribute_category,NULL) attribute_category,
5060 decode(g_transfer_flag,'Y',rsl.attribute1,NULL) attribute1,
5061 decode(g_transfer_flag,'Y',rsl.attribute2,NULL) attribute2,
5062 decode(g_transfer_flag,'Y',rsl.attribute3,NULL) attribute3,
5063 decode(g_transfer_flag,'Y',rsl.attribute4,NULL) attribute4,
5064 decode(g_transfer_flag,'Y',rsl.attribute5,NULL) attribute5,
5065 decode(g_transfer_flag,'Y',rsl.attribute6,NULL) attribute6,
5066 decode(g_transfer_flag,'Y',rsl.attribute7,NULL) attribute7,
5067 decode(g_transfer_flag,'Y',rsl.attribute8,NULL) attribute8,
5068 decode(g_transfer_flag,'Y',rsl.attribute9,NULL) attribute9,
5069 decode(g_transfer_flag,'Y',rsl.attribute10,NULL) attribute10,
5070 decode(g_transfer_flag,'Y',rsl.attribute11,NULL) attribute11,
5071 decode(g_transfer_flag,'Y',rsl.attribute12,NULL) attribute12,
5072 decode(g_transfer_flag,'Y',rsl.attribute13,NULL) attribute13,
5073 decode(g_transfer_flag,'Y',rsl.attribute14,NULL) attribute14,
5074 decode(g_transfer_flag,'Y',rsl.attribute15,NULL) attribute15
5075 --ETAX: Invwkb
5076 --OPEN ISSUE 1
5077 ,rcv.ship_to_location_id ship_to_location_id
5078 /*
5079 rcv.primary_intended_use primary_intended_use,
5080 rcv.product_fisc_classification product_fisc_classification,
5081 rcv.product_type product_type,
5082 rcv.product_category product_category,
5083 rcv.user_defined_fisc_class user_defined_fisc_class
5084 */
5085 FROM po_ap_receipt_match_v rcv,
5086 mtl_system_items msi,
5087 rcv_shipment_lines rsl
5088 WHERE rcv.po_header_id = x_po_header_id
5089 AND msi.inventory_item_id(+) = rcv.item_id
5090 AND msi.organization_id(+) = g_inventory_organization_id
5091 AND rcv.rcv_shipment_line_id = rsl.shipment_line_id
5092 AND rcv.po_match_option = 'R'
5093 ORDER BY rcv.rcv_transaction_id;
5094
5095 l_available_match_amount NUMBER := x_match_amount;
5096 l_amount ap_invoice_lines.amount%TYPE;
5097 l_base_amount ap_invoice_lines.base_amount%TYPE;
5098 l_quantity ap_invoice_lines.quantity_invoiced%TYPE;
5099 l_po_line_location_id po_line_locations.line_location_id%TYPE;
5100 l_po_line_id po_lines.po_line_id%TYPE;
5101 l_po_release_id po_releases.po_release_id%TYPE;
5102 l_rcv_transaction_id rcv_transactions.transaction_id%TYPE;
5103 l_receipt_uom_lookup_code rcv_shipment_lines.unit_of_measure%TYPE;
5104 l_po_uom_lookup_code po_lines.unit_meas_lookup_code%TYPE;
5105 l_po_unit_price po_lines.unit_price%TYPE;
5106 l_available_shipment_amount po_line_locations.amount%TYPE;
5107 l_item_id po_lines.item_id%TYPE;
5108 l_unit_meas_lookup_code po_lines.unit_meas_lookup_code%TYPE;
5109 l_price_override po_line_locations.price_override%TYPE;
5110 l_item_description po_lines.item_description%TYPE;
5111 l_asset_category_id mtl_system_items.asset_category_id%TYPE;
5112 -- Removed for bug 4277744
5113 -- l_ussgl_transaction_code po_line_locations.ussgl_transaction_code%TYPE;
5114 l_type_1099 po_lines.type_1099%TYPE;
5115 l_attribute_category po_line_locations.attribute_category%TYPE;
5116 l_attribute1 po_line_locations.attribute1%TYPE;
5117 l_attribute2 po_line_locations.attribute2%TYPE;
5118 l_attribute3 po_line_locations.attribute3%TYPE;
5119 l_attribute4 po_line_locations.attribute4%TYPE;
5120 l_attribute5 po_line_locations.attribute5%TYPE;
5121 l_attribute6 po_line_locations.attribute6%TYPE;
5122 l_attribute7 po_line_locations.attribute7%TYPE;
5123 l_attribute8 po_line_locations.attribute8%TYPE;
5124 l_attribute9 po_line_locations.attribute9%TYPE;
5125 l_attribute10 po_line_locations.attribute10%TYPE;
5126 l_attribute11 po_line_locations.attribute11%TYPE;
5127 l_attribute12 po_line_locations.attribute12%TYPE;
5128 l_attribute13 po_line_locations.attribute13%TYPE;
5129 l_attribute14 po_line_locations.attribute14%TYPE;
5130 l_attribute15 po_line_locations.attribute15%TYPE;
5131 l_invoice_line_number ap_invoice_lines.line_number%TYPE := 1;
5132 l_index po_line_locations.line_location_id%TYPE;
5133 l_conversion_factor NUMBER;
5134 l_ordered_qty NUMBER;
5135 l_cancelled_qty NUMBER;
5136 l_received_qty NUMBER;
5137 l_corrected_qty NUMBER;
5138 l_delivered_qty NUMBER;
5139 l_transaction_qty NUMBER;
5140 l_billed_qty NUMBER;
5141 l_accepted_qty NUMBER;
5142 l_rejected_qty NUMBER;
5143 l_rect_unit_price po_lines.unit_price%TYPE;
5144 l_max_line_amount ap_invoice_lines.amount%TYPE := 0;
5145 l_rounded_index po_line_locations.line_location_id%TYPE;
5146 l_sum_prorated_amount ap_invoice_lines.amount%TYPE := 0;
5147 l_available_amount po_line_locations.amount%TYPE;
5148 l_available_qty po_line_locations.quantity%TYPE;
5149 l_invoice_base_amount ap_invoice_lines.base_amount%TYPE := 0;
5150 l_sum_line_base_amount ap_invoice_lines.base_amount%TYPE := 0;
5151 l_ship_to_location_id ap_invoice_lines.ship_to_location_id%TYPE;
5152 l_primary_intended_use ap_invoice_lines.primary_intended_use%TYPE;
5153 l_product_fisc_classification ap_invoice_lines.product_fisc_classification%TYPE;
5154 l_product_type ap_invoice_lines.product_type%TYPE;
5155 l_product_category ap_invoice_lines.product_category%TYPE;
5156 l_user_defined_fisc_class ap_invoice_lines.user_defined_fisc_class%TYPE;
5157 l_amount_ordered NUMBER;
5158 l_amount_cancelled NUMBER;
5159 l_amount_delivered NUMBER;
5160 l_amount_billed NUMBER;
5161 l_amount_received NUMBER;
5162 l_amount_corrected NUMBER;
5163 l_matching_basis po_line_locations.matching_basis%TYPE;
5164 l_retained_amount ap_invoice_lines.retained_amount%TYPE;
5165 l_ret_status VARCHAR2(100);
5166 l_msg_count NUMBER;
5167 l_msg_data VARCHAR2(250);
5168 l_debug_info VARCHAR2(2000);
5169 current_calling_sequence VARCHAR2(2000);
5170 l_api_name VARCHAR2(50);
5171
5172 --bugfix:5565310
5173 l_ref_doc_application_id zx_transaction_lines_gt.ref_doc_application_id%TYPE;
5174 l_ref_doc_entity_code zx_transaction_lines_gt.ref_doc_entity_code%TYPE;
5175 l_ref_doc_event_class_code zx_transaction_lines_gt.ref_doc_event_class_code%TYPE;
5176 l_ref_doc_line_quantity zx_transaction_lines_gt.ref_doc_line_quantity%TYPE;
5177 l_po_header_curr_conv_rat po_headers_all.rate%TYPE;
5178 l_ref_doc_trx_level_type zx_transaction_lines_gt.ref_doc_trx_level_type%TYPE;
5179 l_po_header_curr_conv_rate po_headers_all.rate%TYPE;
5180 l_uom_code mtl_units_of_measure.uom_code%TYPE;
5181 l_ref_doc_trx_id po_headers_all.po_header_id%TYPE;
5182 l_error_code varchar2(2000);
5183 l_success boolean;
5184 l_dummy number;
5185 -- bug#6977104
5186 l_inv_org_id NUMBER;
5187 BEGIN
5188
5189 l_api_name := 'Get_Shipment_List_For_QuickMatch';
5190
5191 current_calling_sequence:= 'Get_Shipment_List_For_QuickMatch<-'||x_calling_sequence;
5192
5193 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5194 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Shipment_List_For_QuickMatch(+)');
5195 END IF;
5196
5197 IF (x_match_option = 'P') THEN
5198
5199 OPEN Po_Shipment_List_Cursor;
5200
5201 LOOP
5202
5203 l_debug_info := 'Fetch next record of PO_Shipment_List_Cursor';
5204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5205 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5206 END IF;
5207
5208 FETCH PO_Shipment_List_Cursor INTO l_po_line_location_id,
5209 l_po_line_id,
5210 l_po_release_id,
5211 l_available_shipment_amount,
5212 l_item_id,
5213 l_unit_meas_lookup_code,
5214 l_price_override,
5215 l_item_description,
5216 l_asset_category_id,
5217 -- l_ussgl_transaction_code, - Bug 4277744
5218 l_type_1099,
5219 l_matching_basis,
5220 l_attribute_category,
5221 l_attribute1,
5222 l_attribute2,
5223 l_attribute3,
5224 l_attribute4,
5225 l_attribute5,
5226 l_attribute6,
5227 l_attribute7,
5228 l_attribute8,
5229 l_attribute9,
5230 l_attribute10,
5231 l_attribute11,
5232 l_attribute12,
5233 l_attribute13,
5234 l_attribute14,
5235 l_attribute15,
5236 l_ship_to_location_id
5237 ;
5238
5239 EXIT WHEN (Po_Shipment_List_Cursor%NOTFOUND OR l_available_match_amount = 0);
5240
5241
5242 --calculate the amount on each line that will be generated
5243 IF(l_available_shipment_amount >= l_available_match_amount) THEN
5244 l_amount := l_available_match_amount;
5245 ELSE
5246 l_amount := l_available_shipment_amount;
5247 END IF;
5248
5249 --bugfix:5565310
5250 l_success := AP_ETAX_UTILITY_PKG.Get_PO_Info(
5251 P_Po_Line_Location_Id => l_po_line_location_id,
5252 P_PO_Distribution_Id => null,
5253 P_Application_Id => l_ref_doc_application_id,
5254 P_Entity_code => l_ref_doc_entity_code,
5255 P_Event_Class_Code => l_ref_doc_event_class_code,
5256 P_PO_Quantity => l_ref_doc_line_quantity,
5257 P_Product_Org_Id => l_inv_org_id, -- bug#6977104
5258 P_Po_Header_Id => l_ref_doc_trx_id,
5259 P_Po_Header_curr_conv_rate => l_po_header_curr_conv_rate,
5260 P_Uom_Code => l_uom_code,
5261 P_Dist_Qty => l_dummy,
5262 P_Ship_Price => l_dummy,
5263 P_Error_Code => l_error_code,
5264 P_Calling_Sequence => current_calling_sequence);
5265
5266
5267 AP_Etax_Services_Pkg.Get_Po_Tax_Attributes(
5268 p_application_id => l_ref_doc_application_id,
5269 p_org_id => g_org_id,
5270 p_entity_code => l_ref_doc_entity_code,
5271 p_event_class_code => l_ref_doc_event_class_code,
5272 p_trx_level_type => 'SHIPMENT',
5273 p_trx_id => l_ref_doc_trx_id,
5274 p_trx_line_id => l_po_line_location_id,
5275 x_line_intended_use => g_intended_use,
5276 x_product_type => g_product_type,
5277 x_product_category => g_product_category,
5278 x_product_fisc_classification => g_product_fisc_class,
5279 x_user_defined_fisc_class => g_user_defined_fisc_class,
5280 x_assessable_value => g_assessable_value,
5281 x_tax_classification_code => g_dflt_tax_class_code);
5282
5283
5284 l_debug_info := 'l_po_line_location_id is '||l_po_line_location_id;
5285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5286 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5287 END IF;
5288
5289 l_quantity := round(l_amount/l_price_override,15);
5290
5291 l_available_match_amount := l_available_match_amount - l_amount;
5292
5293 l_index := l_po_line_location_id;
5294
5295 X_Shipment_Table(l_index).po_header_id := x_po_header_id;
5296 X_Shipment_Table(l_index).po_line_id := l_po_line_id;
5297 X_Shipment_Table(l_index).po_release_id := l_po_release_id;
5298 X_Shipment_Table(l_index).po_line_location_id := l_po_line_location_id;
5299 X_Shipment_Table(l_index).uom := l_unit_meas_lookup_code;
5300 X_Shipment_Table(l_index).unit_price := l_price_override;
5301 X_Shipment_Table(l_index).line_number := l_invoice_line_number;
5302 X_Shipment_Table(l_index).quantity_invoiced := l_quantity;
5303 X_Shipment_Table(l_index).amount := l_amount;
5304
5305 l_debug_info := 'X_Shipment_Table('||l_index||').amount is'||X_Shipment_Table(l_index).amount;
5306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5308 END IF;
5309 X_Shipment_Table(l_index).inventory_item_id := l_item_id;
5310 X_Shipment_Table(l_index).item_description := l_item_description;
5311 X_Shipment_Table(l_index).asset_category_id := l_asset_category_id;
5312 -- Removed for bug 4277744
5313 -- X_Shipment_Table(l_index).ussgl_transaction_code := l_ussgl_transaction_code;
5314 X_Shipment_Table(l_index).type_1099 := l_type_1099;
5315 X_Shipment_Table(l_index).attribute_category := l_attribute_category;
5316 X_Shipment_Table(l_index).attribute1 := l_attribute1;
5317 X_Shipment_Table(l_index).attribute2 := l_attribute2;
5318 X_Shipment_Table(l_index).attribute3 := l_attribute3;
5319 X_Shipment_Table(l_index).attribute4 := l_attribute4;
5320 X_Shipment_Table(l_index).attribute5 := l_attribute5;
5321 X_Shipment_Table(l_index).attribute6 := l_attribute6;
5322 X_Shipment_Table(l_index).attribute7 := l_attribute7;
5323 X_Shipment_Table(l_index).attribute8 := l_attribute8;
5324 X_Shipment_Table(l_index).attribute9 := l_attribute9;
5325 X_Shipment_Table(l_index).attribute10 := l_attribute10;
5326 X_Shipment_Table(l_index).attribute11 := l_attribute11;
5327 X_Shipment_Table(l_index).attribute12 := l_attribute12;
5328 X_Shipment_Table(l_index).attribute13 := l_attribute13;
5329 X_Shipment_Table(l_index).attribute14 := l_attribute14;
5330 X_Shipment_Table(l_index).attribute15 := l_attribute15;
5331 X_Shipment_Table(l_index).ship_to_location_id := l_ship_to_location_id;
5332 X_Shipment_Table(l_index).primary_intended_use := g_intended_use;
5333 X_Shipment_Table(l_index).product_fisc_classification := g_product_fisc_class;
5334 X_Shipment_Table(l_index).product_type := g_product_type;
5335 X_Shipment_Table(l_index).product_category := g_product_category;
5336 X_Shipment_Table(l_index).user_defined_fisc_class := g_user_defined_fisc_class;
5337 X_shipment_Table(l_index).assessable_value := g_assessable_value;
5338 X_shipment_Table(l_index).tax_classification_code := g_dflt_tax_class_code;
5339
5340 X_Shipment_Table(l_index).matching_basis := l_matching_basis;
5341 X_Shipment_Table(l_index).retained_amount := ap_invoice_lines_utility_pkg.get_retained_amount
5342 (l_po_line_location_id, l_amount);
5343
5344 --Get the max of the largest invoice line, to be used for assignment of
5345 --rounding due to proration.
5346 IF (l_amount >= l_max_line_amount) THEN
5347
5348 l_rounded_index := l_po_line_location_id;
5349 l_max_line_amount := l_amount;
5350
5351 END IF;
5352
5353 l_invoice_line_number := l_invoice_line_number + 1;
5354 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
5355
5356 END LOOP;
5357
5358 CLOSE PO_Shipment_List_Cursor;
5359
5360 --If proration resulted in rounding error, then add the rounding amt
5361 --onto max of the largest line, index for which was calculated above
5362 --We need to do the proration rounding only if we exhausted the match amount
5363 --and not if we exhausted the shipments.
5364 IF (l_available_match_amount = 0) THEN
5365
5366 IF (x_match_amount - l_sum_prorated_amount <> 0 and l_rounded_index is not null) THEN
5367
5368 X_Shipment_Table(l_rounded_index).amount := X_Shipment_Table(l_rounded_index).amount +
5369 (l_available_match_amount - l_sum_prorated_amount);
5370
5371 END IF;
5372
5373 END IF;
5374
5375 ELSIF (x_match_option = 'R') THEN
5376
5377 OPEN Receipt_Shipment_List_Cursor;
5378
5379 LOOP
5380
5381 l_debug_info := 'Fetch next record of Receipt_Shipment_List_Cursor';
5382 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5383 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5384 END IF;
5385
5386 FETCH Receipt_Shipment_List_Cursor INTO l_po_line_id,
5387 l_po_release_id,
5388 l_po_line_location_id,
5389 l_rcv_transaction_id,
5390 l_receipt_uom_lookup_code,
5391 l_po_uom_lookup_code,
5392 l_po_unit_price,
5393 l_item_id,
5394 l_item_description,
5395 l_asset_category_id,
5396 -- Bug 4277744
5397 -- l_ussgl_transaction_code,
5398 l_type_1099,
5399 l_matching_basis,
5400 l_attribute_category,
5401 l_attribute1,
5402 l_attribute2,
5403 l_attribute3,
5404 l_attribute4,
5405 l_attribute5,
5406 l_attribute6,
5407 l_attribute7,
5408 l_attribute8,
5409 l_attribute9,
5410 l_attribute10,
5411 l_attribute11,
5412 l_attribute12,
5413 l_attribute13,
5414 l_attribute14,
5415 l_attribute15
5416 --ETAX: Invwkb
5417 --OPEN ISSUE 1
5418 ,l_ship_to_location_id
5419 /*
5420 l_primary_intended_use,
5421 l_product_fisc_classification,
5422 l_product_type,
5423 l_product_category,
5424 l_user_defined_fisc_class*/
5425 ;
5426
5427 EXIT WHEN (Receipt_Shipment_List_Cursor%NOTFOUND OR l_available_match_amount = 0);
5428
5429 IF ((l_receipt_uom_lookup_code <> l_po_uom_lookup_code) AND
5430 (l_po_uom_lookup_code is not null) AND
5431 (l_receipt_uom_lookup_code is not null)) THEN
5432 l_debug_info := 'Call PO_UOM_S.PO_UOM_CONVERT to get the conversion
5433 factor between receipt uom and po uom';
5434 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5435 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5436 END IF;
5437
5438 l_conversion_factor := PO_UOM_S.PO_UOM_CONVERT(l_receipt_uom_lookup_code,
5439 l_po_uom_lookup_code,
5440 l_item_id);
5441 l_rect_unit_price := l_po_unit_price / l_conversion_factor;
5442
5443 ELSE
5444
5445 l_rect_unit_price := l_po_unit_price;
5446
5447 END IF;
5448
5449
5450 IF (l_matching_basis = 'QUANTITY') THEN
5451
5452 ap_matching_utils_pkg.get_receipt_quantities(
5453 l_rcv_transaction_id,
5454 l_ordered_qty,
5455 l_cancelled_qty,
5456 l_received_qty,
5457 l_corrected_qty,
5458 l_delivered_qty,
5459 l_transaction_qty,
5460 l_billed_qty,
5461 l_accepted_qty,
5462 l_rejected_qty);
5463
5464 l_billed_qty := nvl(l_billed_qty,0);
5465 l_delivered_qty := nvl(l_delivered_qty,0);
5466 l_cancelled_qty := nvl(l_cancelled_qty,0);
5467 l_ordered_qty := nvl(l_ordered_qty,0);
5468
5469 l_available_qty := l_delivered_qty - l_billed_qty;
5470
5471 IF (l_available_qty > 0) THEN
5472
5473 IF (g_min_acct_unit IS NOT NULL) THEN
5474 l_available_amount := ROUND(l_available_qty * l_rect_unit_price/g_min_acct_unit)
5475 * g_min_acct_unit;
5476 ELSE
5477 l_available_amount := ROUND(l_available_qty * l_rect_unit_price, g_precision);
5478 END IF;
5479
5480 END IF; /* l_available_qty > 0 */
5481
5482 ELSIF (l_matching_basis = 'AMOUNT') THEN
5483
5484 RCV_INVOICE_MATCHING_SV.Get_ReceiveAmount(
5485 p_api_version => 1.0,
5486 p_init_msg_list => FND_API.G_TRUE,
5487 x_return_status => l_ret_status,
5488 x_msg_count => l_msg_count,
5489 x_msg_data => l_msg_data,
5490 p_receive_transaction_id => l_rcv_transaction_id,
5491 x_billed_amt => l_amount_billed,
5492 x_received_amt => l_amount_received,
5493 x_delivered_amt => l_amount_delivered,
5494 x_corrected_amt => l_amount_corrected);
5495
5496 l_amount_billed := nvl(l_amount_billed,0);
5497 l_amount_delivered := nvl(l_amount_delivered,0);
5498 l_amount_cancelled := nvl(l_amount_cancelled,0);
5499
5500 l_available_amount := l_amount_delivered - l_amount_billed;
5501
5502 END IF; /* l_matching_basis = 'QUANTITY' */
5503
5504
5505 IF (l_available_amount > 0) THEN
5506
5507 IF (l_available_amount >= l_available_match_amount) THEN
5508 l_amount := l_available_match_amount;
5509 ELSE
5510 l_amount := l_available_amount;
5511 END IF;
5512
5513 l_quantity := ROUND(l_amount/l_rect_unit_price,15); /*Bug 7515118*/
5514
5515 l_available_match_amount := l_available_match_amount - l_amount;
5516
5517 l_index := l_po_line_location_id; -- bug 5929800 l_index was not being initialized
5518
5519 X_Shipment_Table(l_index).po_header_id := x_po_header_id;
5520 X_Shipment_Table(l_index).po_line_id := l_po_line_id;
5521 X_Shipment_Table(l_index).po_release_id := l_po_release_id;
5522 X_Shipment_Table(l_index).po_line_location_id := l_po_line_location_id;
5523 X_Shipment_Table(l_index).rcv_transaction_id := l_rcv_transaction_id;
5524 X_Shipment_Table(l_index).uom := l_receipt_uom_lookup_code;
5525 X_Shipment_Table(l_index).unit_price := l_rect_unit_price;
5526 X_Shipment_Table(l_index).line_number := l_invoice_line_number;
5527 X_Shipment_Table(l_index).quantity_invoiced := l_quantity;
5528 X_Shipment_Table(l_index).amount := l_amount;
5529 X_Shipment_Table(l_index).inventory_item_id := l_item_id;
5530 X_Shipment_Table(l_index).item_description := l_item_description;
5531 X_Shipment_Table(l_index).asset_category_id := l_asset_category_id;
5532 -- Removed for bug 4277744
5533 -- X_Shipment_Table(l_index).ussgl_transaction_code := l_ussgl_transaction_code;
5534 X_Shipment_Table(l_index).type_1099 := l_type_1099;
5535 X_Shipment_Table(l_index).attribute_category := l_attribute_category;
5536 X_Shipment_Table(l_index).attribute1 := l_attribute1;
5537 X_Shipment_Table(l_index).attribute2 := l_attribute2;
5538 X_Shipment_Table(l_index).attribute3 := l_attribute3;
5539 X_Shipment_Table(l_index).attribute4 := l_attribute4;
5540 X_Shipment_Table(l_index).attribute5 := l_attribute5;
5541 X_Shipment_Table(l_index).attribute6 := l_attribute6;
5542 X_Shipment_Table(l_index).attribute7 := l_attribute7;
5543 X_Shipment_Table(l_index).attribute8 := l_attribute8;
5544 X_Shipment_Table(l_index).attribute9 := l_attribute9;
5545 X_Shipment_Table(l_index).attribute10 := l_attribute10;
5546 X_Shipment_Table(l_index).attribute11 := l_attribute11;
5547 X_Shipment_Table(l_index).attribute12 := l_attribute12;
5548 X_Shipment_Table(l_index).attribute13 := l_attribute13;
5549 X_Shipment_Table(l_index).attribute14 := l_attribute14;
5550 X_Shipment_Table(l_index).attribute15 := l_attribute15;
5551 X_Shipment_Table(l_index).ship_to_location_id := l_ship_to_location_id;
5552 X_Shipment_Table(l_index).primary_intended_use := l_primary_intended_use;
5553 X_Shipment_Table(l_index).product_fisc_classification := l_product_fisc_classification;
5554 X_Shipment_Table(l_index).product_type := l_product_type;
5555 X_Shipment_Table(l_index).product_category := l_product_category;
5556 X_Shipment_Table(l_index).user_defined_fisc_class := l_user_defined_fisc_class;
5557
5558 X_Shipment_Table(l_index).matching_basis := l_matching_basis;
5559 X_Shipment_Table(l_index).retained_amount := ap_invoice_lines_utility_pkg.get_retained_amount
5560 (l_po_line_location_id, l_amount);
5561
5562 --Get the max of the largest invoice line, to be used for assignment of
5563 --rounding due to proration.
5564 IF (l_amount >= l_max_line_amount) THEN
5565
5566 l_rounded_index := l_po_line_location_id;
5567 l_max_line_amount := l_amount;
5568
5569 END IF;
5570
5571 l_invoice_line_number := l_invoice_line_number + 1;
5572 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
5573
5574 END IF; /* (l_available_amount > 0)*/
5575
5576 END LOOP;
5577
5578 CLOSE Receipt_Shipment_List_Cursor;
5579
5580 --If proration resulted in rounding error, then add the rounding amt
5581 --onto max of the largest line, index for which was calculated above
5582 --We need to do the proration rounding only if we exhausted the match amount
5583 --and not if we exhausted the shipments.
5584 IF(l_available_match_amount = 0) THEN
5585 IF (x_match_amount - l_sum_prorated_amount <> 0 and l_rounded_index IS NOT NULL) THEN
5586
5587 X_Shipment_Table(l_rounded_index).amount := X_Shipment_Table(l_rounded_index).amount +
5588 (l_available_match_amount - l_sum_prorated_amount);
5589
5590 END IF;
5591 END IF;
5592
5593 END IF;/*(x_match option = 'P')*/
5594
5595
5596 --Calculate base_amounts and rounding for foriegn currency invoices
5597 IF (g_exchange_rate IS NOT NULL) THEN
5598
5599 l_invoice_base_amount := ap_utilities_pkg.ap_round_currency(
5600 x_match_amount * g_exchange_rate,
5601 g_base_currency_code);
5602
5603 --Populate base_amount column for foriegn currency invoice.
5604 --Base amount needs to be populated after the lines have been rounded
5605 --for proration, hence doing in the loop below.
5606
5607 FOR i IN NVL(X_Shipment_Table.first,0) .. NVL(X_Shipment_Table.last,0) LOOP
5608
5609 l_base_amount := ap_utilities_pkg.ap_round_currency(
5610 x_shipment_table(i).amount * g_exchange_rate,
5611 g_base_currency_code);
5612
5613 x_shipment_table(i).base_amount := l_base_amount;
5614
5615 l_sum_line_base_amount := l_sum_line_base_amount + l_base_amount;
5616
5617 END LOOP;
5618
5619 --Perform rounding
5620 IF (l_invoice_base_amount <> l_sum_line_base_amount) THEN
5621
5622 x_shipment_table(l_rounded_index).base_amount := x_shipment_table(l_rounded_index).base_amount +
5623 (l_invoice_base_amount - l_sum_line_base_amount);
5624 x_shipment_table(l_rounded_index).rounding_amt := l_invoice_base_amount - l_sum_line_base_amount;
5625
5626 END IF;
5627
5628 END IF;
5629
5630 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5631 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Get_Shipment_List_For_QuickMatch(-)');
5632 END IF;
5633
5634
5635 EXCEPTION
5636 WHEN others then
5637 If (SQLCODE <> -20001) Then
5638 fnd_message.set_name('SQLAP','AP_DEBUG');
5639 fnd_message.set_token('ERROR',SQLERRM);
5640 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
5641 fnd_message.set_token('PARAMETERS',
5642 ' invoice_id = '||to_char(x_invoice_id)
5643 ||', Po header id = '||to_char(x_po_header_id)
5644 ||', match option = '||x_match_option
5645 ||', match_amount = '||to_char(x_match_amount));
5646 fnd_message.set_token('DEBUG_INFO',l_debug_info);
5647 End if;
5648 --Clean up the PL/SQL tables on error
5649 x_shipment_table.delete;
5650
5651 app_exception.raise_exception;
5652
5653 END Get_Shipment_List_For_QM;
5654
5655
5656 PROCEDURE Generate_Lines_For_QuickMatch (
5657 x_invoice_id IN NUMBER,
5658 x_shipment_table IN T_SHIPMENT_TABLE,
5659 x_match_option IN VARCHAR2,
5660 x_calling_sequence IN VARCHAR2) IS
5661
5662 i NUMBER;
5663 l_debug_info VARCHAR2(2000);
5664 current_calling_sequence VARCHAR2(2000);
5665 l_api_name VARCHAR2(50);
5666
5667 BEGIN
5668
5669
5670 l_api_name := 'Generate_Lines_For_QuickMatch';
5671 current_calling_sequence := 'Generate_Lines_For_QuickMatch<-'||x_calling_sequence;
5672 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5673 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Generate_Lines_For_QuickMatch(+)');
5674 END IF;
5675
5676 FOR i IN NVL(x_shipment_table.first,0) .. NVL(x_shipment_table.last,0) LOOP
5677
5678 IF (x_shipment_table.exists(i)) THEN
5679 /* bug 6150813 */
5680 INSERT INTO AP_INVOICE_LINES_ALL
5681 (INVOICE_ID,
5682 LINE_NUMBER,
5683 LINE_TYPE_LOOKUP_CODE,
5684 REQUESTER_ID,
5685 DESCRIPTION,
5686 LINE_SOURCE,
5687 ORG_ID,
5688 INVENTORY_ITEM_ID,
5689 ITEM_DESCRIPTION,
5690 SERIAL_NUMBER,
5691 MANUFACTURER,
5692 MODEL_NUMBER,
5693 GENERATE_DISTS,
5694 MATCH_TYPE,
5695 DISTRIBUTION_SET_ID,
5696 ACCOUNT_SEGMENT,
5697 BALANCING_SEGMENT,
5698 COST_CENTER_SEGMENT,
5699 OVERLAY_DIST_CODE_CONCAT,
5700 DEFAULT_DIST_CCID,
5701 PRORATE_ACROSS_ALL_ITEMS,
5702 LINE_GROUP_NUMBER,
5703 ACCOUNTING_DATE,
5704 PERIOD_NAME,
5705 DEFERRED_ACCTG_FLAG,
5706 DEF_ACCTG_START_DATE,
5707 DEF_ACCTG_END_DATE,
5708 DEF_ACCTG_NUMBER_OF_PERIODS,
5709 DEF_ACCTG_PERIOD_TYPE,
5710 SET_OF_BOOKS_ID,
5711 AMOUNT,
5712 BASE_AMOUNT,
5713 ROUNDING_AMT,
5714 QUANTITY_INVOICED,
5715 UNIT_MEAS_LOOKUP_CODE,
5716 UNIT_PRICE,
5717 WFAPPROVAL_STATUS,
5718 -- USSGL_TRANSACTION_CODE,- Bug 4277744
5719 DISCARDED_FLAG,
5720 ORIGINAL_AMOUNT,
5721 ORIGINAL_BASE_AMOUNT,
5722 ORIGINAL_ROUNDING_AMT,
5723 CANCELLED_FLAG,
5724 INCOME_TAX_REGION,
5725 TYPE_1099,
5726 STAT_AMOUNT,
5727 PREPAY_INVOICE_ID,
5728 PREPAY_LINE_NUMBER,
5729 INVOICE_INCLUDES_PREPAY_FLAG,
5730 CORRECTED_INV_ID,
5731 CORRECTED_LINE_NUMBER,
5732 PO_HEADER_ID,
5733 PO_LINE_ID,
5734 PO_RELEASE_ID,
5735 PO_LINE_LOCATION_ID,
5736 PO_DISTRIBUTION_ID,
5737 RCV_TRANSACTION_ID,
5738 FINAL_MATCH_FLAG,
5739 ASSETS_TRACKING_FLAG,
5740 ASSET_BOOK_TYPE_CODE,
5741 ASSET_CATEGORY_ID,
5742 PROJECT_ID,
5743 TASK_ID,
5744 EXPENDITURE_TYPE,
5745 EXPENDITURE_ITEM_DATE,
5746 EXPENDITURE_ORGANIZATION_ID,
5747 PA_QUANTITY,
5748 PA_CC_AR_INVOICE_ID,
5749 PA_CC_AR_INVOICE_LINE_NUM,
5750 PA_CC_PROCESSED_CODE,
5751 AWARD_ID,
5752 AWT_GROUP_ID,
5753 REFERENCE_1,
5754 REFERENCE_2,
5755 RECEIPT_VERIFIED_FLAG,
5756 RECEIPT_REQUIRED_FLAG,
5757 RECEIPT_MISSING_FLAG,
5758 JUSTIFICATION,
5759 EXPENSE_GROUP,
5760 START_EXPENSE_DATE,
5761 END_EXPENSE_DATE,
5762 RECEIPT_CURRENCY_CODE,
5763 RECEIPT_CONVERSION_RATE,
5764 RECEIPT_CURRENCY_AMOUNT,
5765 DAILY_AMOUNT,
5766 WEB_PARAMETER_ID,
5767 ADJUSTMENT_REASON,
5768 MERCHANT_DOCUMENT_NUMBER,
5769 MERCHANT_NAME,
5770 MERCHANT_REFERENCE,
5771 MERCHANT_TAX_REG_NUMBER,
5772 MERCHANT_TAXPAYER_ID,
5773 COUNTRY_OF_SUPPLY,
5774 CREDIT_CARD_TRX_ID,
5775 COMPANY_PREPAID_INVOICE_ID,
5776 CC_REVERSAL_FLAG,
5777 ATTRIBUTE_CATEGORY,
5778 ATTRIBUTE1,
5779 ATTRIBUTE2,
5780 ATTRIBUTE3,
5781 ATTRIBUTE4,
5782 ATTRIBUTE5,
5783 ATTRIBUTE6,
5784 ATTRIBUTE7,
5785 ATTRIBUTE8,
5786 ATTRIBUTE9,
5787 ATTRIBUTE10,
5788 ATTRIBUTE11,
5789 ATTRIBUTE12,
5790 ATTRIBUTE13,
5791 ATTRIBUTE14,
5792 ATTRIBUTE15,
5793 /*OPEN ISSUE 1*/
5794 /*GLOBAL_ATTRIBUTE_CATEGORY,
5795 GLOBAL_ATTRIBUTE1,
5796 GLOBAL_ATTRIBUTE2,
5797 GLOBAL_ATTRIBUTE3,
5798 GLOBAL_ATTRIBUTE4,
5799 GLOBAL_ATTRIBUTE5,
5800 GLOBAL_ATTRIBUTE6,
5801 GLOBAL_ATTRIBUTE7,
5802 GLOBAL_ATTRIBUTE8,
5803 GLOBAL_ATTRIBUTE9,
5804 GLOBAL_ATTRIBUTE10,
5805 GLOBAL_ATTRIBUTE11,
5806 GLOBAL_ATTRIBUTE12,
5807 GLOBAL_ATTRIBUTE13,
5808 GLOBAL_ATTRIBUTE14,
5809 GLOBAL_ATTRIBUTE15,
5810 GLOBAL_ATTRIBUTE16,
5811 GLOBAL_ATTRIBUTE17,
5812 GLOBAL_ATTRIBUTE18,
5813 GLOBAL_ATTRIBUTE19,
5814 GLOBAL_ATTRIBUTE20, */
5815 CREATION_DATE,
5816 CREATED_BY,
5817 LAST_UPDATED_BY,
5818 LAST_UPDATE_DATE,
5819 LAST_UPDATE_LOGIN,
5820 PROGRAM_APPLICATION_ID,
5821 PROGRAM_ID,
5822 PROGRAM_UPDATE_DATE,
5823 REQUEST_ID,
5824 RETAINED_AMOUNT,
5825 RETAINED_AMOUNT_REMAINING,
5826 --bugfix:5565310
5827 SHIP_TO_LOCATION_ID,
5828 PRIMARY_INTENDED_USE,
5829 PRODUCT_FISC_CLASSIFICATION,
5830 TRX_BUSINESS_CATEGORY,
5831 PRODUCT_TYPE,
5832 PRODUCT_CATEGORY,
5833 USER_DEFINED_FISC_CLASS,
5834 ASSESSABLE_VALUE,
5835 TAX_CLASSIFICATION_CODE,
5836 PAY_AWT_GROUP_ID)
5837 VALUES( X_INVOICE_ID, --invoice_id
5838 X_SHIPMENT_TABLE(i).line_number, --line_number
5839 'ITEM', --line_type_lookup_code
5840 NULL, --requester_id
5841 X_SHIPMENT_TABLE(i).item_description,--description
5842 'HEADER MATCH', --line_source
5843 G_ORG_ID, --org_id
5844 X_SHIPMENT_TABLE(i).inventory_item_id,--inventory_item_id
5845 X_SHIPMENT_TABLE(i).item_description,--item_description
5846 NULL, --serial_number
5847 NULL, --manufacturer
5848 NULL, --model_number
5849 'Y', --generate_dists
5850 DECODE(X_MATCH_OPTION,'P',
5851 (DECODE(X_SHIPMENT_TABLE(i).matching_basis,
5852 'QUANTITY', 'ITEM_TO_PO', 'ITEM_TO_SERVICE_PO')),
5853 (DECODE(X_SHIPMENT_TABLE(i).matching_basis,
5854 'QUANTITY', 'ITEM_TO_RECEIPT', 'ITEM_TO_SERVICE_RECEIPT'))), --match_type
5855 NULL, --distribution_set_id
5856 NULL, --account_segment
5857 NULL, --balancing_segment
5858 NULL, --cost_center_segment
5859 NULL, --overlay_dist_code_concat
5860 NULL, --default_dist_ccid
5861 'N', --prorate_across_all_items
5862 NULL, --line_group_number
5863 G_ACCOUNTING_DATE, --accounting_date
5864 G_PERIOD_NAME, --period_name
5865 'N', --deferred_acctg_flag
5866 NULL, --def_acctg_start_date
5867 NULL, --def_acctg_end_date
5868 NULL, --def_acctg_number_of_periods
5869 NULL, --def_acctg_period_type
5870 G_SET_OF_BOOKS_ID, --set_of_books_id
5871 X_SHIPMENT_TABLE(i).AMOUNT, --amount
5872 X_SHIPMENT_TABLE(i).BASE_AMOUNT, --base_amount
5873 NULL, --rounding_amount
5874 X_SHIPMENT_TABLE(i).QUANTITY_INVOICED,--quantity_invoiced
5875 X_SHIPMENT_TABLE(i).UOM, --unit_meas_lookup_code
5876 X_SHIPMENT_TABLE(i).UNIT_PRICE, --unit_price
5877 DECODE(G_APPROVAL_WORKFLOW_FLAG,'Y'
5878 ,'REQUIRED','NOT REQUIRED'),--wf_approval_status
5879 -- Removed for bug 4277744
5880 -- X_SHIPMENT_TABLE(i).USSGL_TRANSACTION_CODE,--ussgl_transaction_code
5881 'N', --discarded_flag
5882 NULL, --original_amount
5883 NULL, --original_base_amount
5884 NULL, --original_rounding_amt
5885 'N', --cancelled_flag
5886 G_INCOME_TAX_REGION, --income_tax_region
5887 X_SHIPMENT_TABLE(i).TYPE_1099, --type_1099
5888 NULL, --stat_amount
5889 NULL, --prepay_invoice_id
5890 NULL, --prepay_line_number
5891 NULL, --invoice_includes_prepay_flag
5892 NULL, --corrected_inv_id
5893 NULL, --corrected_line_number
5894 X_SHIPMENT_TABLE(i).PO_HEADER_ID, --po_header_id
5895 X_SHIPMENT_TABLE(i).PO_LINE_ID, --po_line_id
5896 X_SHIPMENT_TABLE(i).PO_RELEASE_ID,--po_release_id
5897 X_SHIPMENT_TABLE(i).PO_LINE_LOCATION_ID,--po_line_location_id
5898 NULL, --po_distribution_id
5899 DECODE(X_MATCH_OPTION,'P',NULL,
5900 X_SHIPMENT_TABLE(i).RCV_TRANSACTION_ID), --rcv_transaction_id
5901 NULL, --final_match_flag
5902 'N', --assets_tracking_flag
5903 G_ASSET_BOOK_TYPE_CODE, --asset_book_type_code
5904 X_SHIPMENT_TABLE(i).ASSET_CATEGORY_ID,--asset_category_id
5905 NULL, --project_id
5906 NULL, --task_id
5907 NULL, --expenditure_type
5908 NULL, --expenditure_item_date
5909 NULL, --expenditure_organization_id
5910 NULL, --pa_quantity
5911 NULL, --pa_cc_ar_invoice_id
5912 NULL, --pa_cc_ar_invoice_line_num
5913 NULL, --pa_cc_processed_code
5914 NULL, --award_id
5915 G_AWT_GROUP_ID, --awt_group_id
5916 NULL, --reference_1
5917 NULL, --reference_2
5918 NULL, --receipt_verified_flag
5919 NULL, --receipt_required_flag
5920 NULL, --receipt_missing_flag
5921 NULL, --justification
5922 NULL, --expense_group
5923 NULL, --start_expense_date
5924 NULL, --end_expense_date
5925 NULL, --receipt_currency_amount
5926 NULL, --receipt_conversion_rate
5927 NULL, --receipt_currency_amount
5928 NULL, --daily_amount
5929 NULL, --web_parameter_id
5930 NULL, --adjustment_reason
5931 NULL, --merchant_document_number
5932 NULL, --merchant_name
5933 NULL, --merchant_reference
5934 NULL, --merchant_tax_reg_number
5935 NULL, --merchant_taxpayer_id
5936 NULL, --country_of_supply
5937 NULL, --credit_card_trx_id
5938 NULL, --company_prepaid_invoice_id
5939 NULL, --cc_reversal_flag
5940 X_SHIPMENT_TABLE(i).ATTRIBUTE_CATEGORY,--attribute_category
5941 X_SHIPMENT_TABLE(i).ATTRIBUTE1, --attribute1
5942 X_SHIPMENT_TABLE(i).ATTRIBUTE2, --attribute2
5943 X_SHIPMENT_TABLE(i).ATTRIBUTE3, --attribute3
5944 X_SHIPMENT_TABLE(i).ATTRIBUTE4, --attribute4
5945 X_SHIPMENT_TABLE(i).ATTRIBUTE5, --attribute5
5946 X_SHIPMENT_TABLE(i).ATTRIBUTE6, --attribute6
5947 X_SHIPMENT_TABLE(i).ATTRIBUTE7, --attribute7
5948 X_SHIPMENT_TABLE(i).ATTRIBUTE8, --attribute8
5949 X_SHIPMENT_TABLE(i).ATTRIBUTE9, --attribute9
5950 X_SHIPMENT_TABLE(i).ATTRIBUTE10, --attribute10
5951 X_SHIPMENT_TABLE(i).ATTRIBUTE11, --attribute11
5952 X_SHIPMENT_TABLE(i).ATTRIBUTE12, --attribute12
5953 X_SHIPMENT_TABLE(i).ATTRIBUTE13, --attribute13
5954 X_SHIPMENT_TABLE(i).ATTRIBUTE14, --attribute14
5955 X_SHIPMENT_TABLE(i).ATTRIBUTE15, --attribute15
5956 /*X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
5957 X_GLOBAL_ATTRIBUTE1, --global_attribute1
5958 X_GLOBAL_ATTRIBUTE2, --global_attribute2
5959 X_GLOBAL_ATTRIBUTE3, --global_attribute3
5960 X_GLOBAL_ATTRIBUTE4, --global_attribute4
5961 X_GLOBAL_ATTRIBUTE5, --global_attribute5
5962 X_GLOBAL_ATTRIBUTE6, --global_attribute6
5963 X_GLOBAL_ATTRIBUTE7, --global_attribute7
5964 X_GLOBAL_ATTRIBUTE8, --global_attribute8
5965 X_GLOBAL_ATTRIBUTE9, --global_attribute9
5966 X_GLOBAL_ATTRIBUTE10, --global_attribute10
5967 X_GLOBAL_ATTRIBUTE11, --global_attribute11
5968 X_GLOBAL_ATTRIBUTE12, --global_attribute12
5969 X_GLOBAL_ATTRIBUTE13, --global_attribute13
5970 X_GLOBAL_ATTRIBUTE14, --global_attribute14
5971 X_GLOBAL_ATTRIBUTE15, --global_attribute15
5972 X_GLOBAL_ATTRIBUTE16, --global_attribute16
5973 X_GLOBAL_ATTRIBUTE17, --global_attribute17
5974 X_GLOBAL_ATTRIBUTE18, --global_attribute18
5975 X_GLOBAL_ATTRIBUTE19, --global_attribute19
5976 X_GLOBAL_ATTRIBUTE20, --global_attribute20*/
5977 SYSDATE, --creation_date
5978 G_USER_ID, --created_by
5979 G_USER_ID, --last_update_by
5980 SYSDATE, --last_update_date
5981 G_LOGIN_ID, --last_update_login
5982 NULL, --program_application_id
5983 NULL, --program_id
5984 NULL, --program_update_date
5985 NULL, --request_id
5986 X_SHIPMENT_TABLE(i).retained_amount, -- retained_amount
5987 (-X_SHIPMENT_TABLE(i).retained_amount), -- retained_amount_remaining
5988 --bugfix:5565310
5989 X_SHIPMENT_TABLE(i).SHIP_TO_LOCATION_ID, --ship_to_location_id
5990 X_SHIPMENT_TABLE(i).PRIMARY_INTENDED_USE, --primary_intended_use
5991 X_SHIPMENT_TABLE(i).PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
5992 G_TRX_BUSINESS_CATEGORY --trx_business_category
5993 ,X_SHIPMENT_TABLE(i).PRODUCT_TYPE, --product_type
5994 X_SHIPMENT_TABLE(i).PRODUCT_CATEGORY, --product_category
5995 X_SHIPMENT_TABLE(i).USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
5996 X_SHIPMENT_TABLE(I).ASSESSABLE_VALUE,
5997 X_SHIPMENT_TABLE(i).TAX_CLASSIFICATION_CODE,
5998 G_PAY_AWT_GROUP_ID --pay_awt_group_id bug8222382
5999 );
6000
6001 END IF;
6002
6003 END LOOP;
6004
6005 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6006 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Generate_Lines_For_QuickMatch(-)');
6007 END IF;
6008
6009
6010
6011 EXCEPTION
6012 WHEN others then
6013 If (SQLCODE <> -20001) Then
6014 fnd_message.set_name('SQLAP','AP_DEBUG');
6015 fnd_message.set_token('ERROR',SQLERRM);
6016 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
6017 fnd_message.set_token('PARAMETERS',
6018 ' invoice_id = '||to_char(x_invoice_id)
6019 ||', match option = '||x_match_option);
6020 fnd_message.set_token('DEBUG_INFO',l_debug_info);
6021 End if;
6022
6023 app_exception.raise_exception;
6024
6025 END Generate_Lines_For_QuickMatch;
6026
6027 Procedure Generate_Release_Lines (p_po_header_id IN NUMBER,
6028 p_invoice_id IN NUMBER,
6029 p_release_amount IN NUMBER,
6030 x_calling_sequence IN VARCHAR2) Is
6031
6032 Cursor c_line_locations (c_po_header_id NUMBER) IS
6033 Select pll.*
6034 From po_line_locations_all pll
6035 Where pll.po_header_id = c_po_header_id
6036 And nvl(retainage_withheld_amount,0) - nvl(retainage_released_amount,0) <> 0;
6037
6038 l_line_locations c_line_locations%rowtype;
6039
6040 i number:=1;
6041 l_release_amount_rtot number;
6042 l_shipment_release_amount number;
6043 l_release_amount_remaining number;
6044
6045 l_release_shipment_tab ap_retainage_release_pkg.release_shipments_tab;
6046
6047 l_debug_info VARCHAR2(2000);
6048 current_calling_sequence VARCHAR2(2000);
6049 l_api_name VARCHAR2(50);
6050
6051 BEGIN
6052
6053 l_api_name := 'Generate_Release_Lines';
6054 current_calling_sequence := 'Generate_Release_Lines<-'||x_calling_sequence;
6055
6056 l_debug_info := 'Generate_Release_Lines (+)';
6057 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6058 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6059 END IF;
6060
6061 l_release_amount_rtot := p_release_amount;
6062
6063 Open c_line_locations (p_po_header_id);
6064 Loop
6065 Fetch c_line_locations
6066 Into l_line_locations;
6067
6068 Exit When c_line_locations%notfound OR
6069 l_release_amount_rtot = 0;
6070
6071 l_release_amount_remaining := nvl(l_line_locations.retainage_withheld_amount,0) -
6072 nvl(l_line_locations.retainage_released_amount,0);
6073
6074 If l_release_amount_rtot > l_release_amount_remaining Then
6075 l_shipment_release_amount := l_release_amount_remaining;
6076 Else
6077 l_shipment_release_amount := l_release_amount_rtot;
6078 End If;
6079
6080 l_release_shipment_tab(i).po_header_id := l_line_locations.po_header_id;
6081 l_release_shipment_tab(i).po_line_id := l_line_locations.po_line_id;
6082 l_release_shipment_tab(i).po_release_id := l_line_locations.po_release_id;
6083 l_release_shipment_tab(i).line_location_id := l_line_locations.line_location_id;
6084 l_release_shipment_tab(i).release_amount := l_shipment_release_amount;
6085 l_release_shipment_tab(i).release_amount_remaining := l_release_amount_remaining;
6086
6087 l_debug_info := 'Call Release API: '||
6088 'line_location_id: ' || l_line_locations.line_location_id||
6089 'release_amount: ' || l_shipment_release_amount;
6090 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6091 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6092 END IF;
6093
6094 ap_retainage_release_pkg.create_release
6095 (x_invoice_id => p_invoice_id,
6096 x_release_shipments_tab => l_release_shipment_tab);
6097
6098 l_release_amount_rtot := l_release_amount_rtot - l_shipment_release_amount;
6099 i:=i+1;
6100
6101 End Loop;
6102 Close c_line_locations;
6103
6104 l_debug_info := 'Generate_Release_Lines (-)';
6105 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6106 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6107 END IF;
6108
6109 EXCEPTION
6110 WHEN others then
6111 If (SQLCODE <> -20001) Then
6112 fnd_message.set_name('SQLAP','AP_DEBUG');
6113 fnd_message.set_token('ERROR',SQLERRM);
6114 fnd_message.set_token('CALLING_SEQUENCE',current_calling_sequence);
6115 fnd_message.set_token('PARAMETERS',
6116 ' invoice_id = '||to_char(p_invoice_id));
6117 fnd_message.set_token('DEBUG_INFO',l_debug_info);
6118 End if;
6119
6120 app_exception.raise_exception;
6121
6122 End Generate_Release_Lines;
6123
6124 -- Bug 5465722. Building Prepay Proper Account as per Federal Functionality.
6125 -- Only Natural Account will be overlayed
6126 PROCEDURE Build_Prepay_Account(P_base_ccid IN NUMBER
6127 ,P_overlay_ccid IN NUMBER
6128 ,P_accounting_date IN DATE
6129 ,P_result_ccid OUT NOCOPY NUMBER
6130 ,P_Reason_Unbuilt_Flex OUT NOCOPY VARCHAR2
6131 ,P_calling_sequence IN VARCHAR2 ) IS
6132
6133 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
6134 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
6135 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
6136 l_num_of_segments NUMBER ;
6137 l_result BOOLEAN ;
6138 l_curr_calling_sequence VARCHAR2(2000);
6139 G_flex_qualifier_name VARCHAR2(100);
6140 l_primary_sob_id AP_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
6141 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
6142 l_chart_of_accts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
6143 G_flex_segment_num NUMBER;
6144 l_api_name CONSTANT VARCHAR2(200) := 'Build_Prepay_Account';
6145 l_debug_info VARCHAR2(2000);
6146
6147
6148 BEGIN
6149
6150 l_curr_calling_sequence := 'Ap_Matching_Pkg.Build_Prepay_Account<-'
6151 || P_calling_sequence;
6152 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6153 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_curr_calling_sequence);
6154 END IF;
6155
6156
6157 SELECT set_of_books_id
6158 INTO l_primary_sob_id
6159 FROM ap_system_parameters;
6160
6161 SELECT chart_of_accounts_id
6162 INTO l_chart_of_accts_id
6163 FROM gl_sets_of_books
6164 WHERE set_of_books_id = l_primary_sob_id;
6165
6166
6167 G_flex_qualifier_name := 'GL_ACCOUNT' ;
6168
6169
6170 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
6171 101, 'GL#',
6172 l_chart_of_accts_id,
6173 G_flex_qualifier_name,
6174 G_flex_segment_num);
6175
6176 l_debug_info := 'G_Flex_Segment_Num: '||G_Flex_Segment_Num;
6177 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6178 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6179 END IF;
6180
6181 -- Get the segments of the two given accounts
6182 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
6183 l_chart_of_accts_id,
6184 P_base_ccid, l_num_of_segments,
6185 l_base_segments)
6186 ) THEN
6187 -- Print reason why flex failed
6188 P_result_ccid := -1;
6189 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
6190
6191 l_debug_info := 'Charge Account is Invalid -> '||FND_MESSAGE.GET;
6192 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6193 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6194 END IF;
6195
6196 RETURN ;
6197
6198 END IF;
6199
6200 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
6201 l_chart_of_accts_id,
6202 P_overlay_ccid, l_num_of_segments,
6203 l_overlay_segments)
6204 ) THEN
6205 -- Print reason why flex failed
6206 P_result_ccid := -1;
6207 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
6208
6209 l_debug_info := 'Overlay Account is Invalid -> '||FND_MESSAGE.GET;
6210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6211 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6212 END IF;
6213
6214 RETURN ;
6215
6216 END IF;
6217
6218 /*
6219 Account Segment Overlay
6220 Base A A [A] A
6221 Overlay B B [B] B
6222 Result A A [B] A
6223
6224 */
6225
6226 FOR i IN 1.. l_num_of_segments LOOP
6227
6228 l_debug_info := 'Overlaying Account Segment -> '||to_char(i);
6229 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6231 END IF;
6232
6233 -- Account segment overlay
6234 IF (i = G_flex_segment_num) THEN
6235 l_segments(i) := l_overlay_segments(i);
6236 ELSE
6237 l_segments(i) := l_base_segments(i);
6238 END IF;
6239
6240 END LOOP;
6241
6242 -- Get ccid fOR overlayed segments
6243 l_result := FND_FLEX_EXT.GET_COMBINATION_ID('SQLGL', 'GL#',
6244 l_chart_of_accts_id,
6245 P_accounting_date, l_num_of_segments,
6246 l_segments, P_result_ccid) ;
6247
6248 IF (NOT l_result) THEN
6249
6250 -- Store reason why flex failed
6251 P_result_ccid := -1;
6252 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
6253
6254 l_debug_info := 'Account Based on Overlayed Segments can not be build -> '||FND_MESSAGE.GET;
6255 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6257 END IF;
6258
6259 END IF;
6260
6261 EXCEPTION
6262 WHEN OTHERS THEN
6263 IF (SQLCODE <> -20001) THEN
6264 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
6265 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
6266 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
6267 FND_MESSAGE.SET_TOKEN('PARAMETERS','Base CCID = '||to_char(p_base_ccid)
6268 ||', Overlay CCID = '||to_char(p_overlay_ccid));
6269 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
6270
6271 l_debug_info := 'Exception occured in Building Prepay Account> '||FND_MESSAGE.GET;
6272 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6273 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
6274 END IF;
6275 END IF;
6276 APP_EXCEPTION.RAISE_EXCEPTION;
6277
6278 END Build_Prepay_Account;
6279
6280
6281 END AP_MATCHING_PKG;