[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICE_LINES_PKG
Source
1 PACKAGE BODY AP_INVOICE_LINES_PKG as
2 /* $Header: apinlinb.pls 120.64.12010000.10 2008/12/04 11:47:49 njakkula ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_INVOICE_LINES_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_INVOICE_LINES_PKG.';
21
22 PROCEDURE Print (p_api_name IN VARCHAR2, p_debug_info IN VARCHAR2);
23
24 -----------------------------------------------------------------------
25 -- FUNCTION generate_dist_tab_for_dist_set validates distributions
26 -- to be created by a distribution set and generates a pl/sql table
27 -- of distributions to be inserted IF need be by the calling module.
28 -----------------------------------------------------------------------
29 FUNCTION Generate_Dist_Tab_For_Dist_Set(
30 X_vendor_id IN AP_INVOICES.VENDOR_ID%TYPE,
31 X_invoice_date IN AP_INVOICES.INVOICE_DATE%TYPE,
32 X_invoice_lines_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
33 X_line_source IN VARCHAR2,
34 X_dist_tab IN OUT NOCOPY AP_INVOICE_LINES_PKG.dist_tab_type,
35 X_dist_set_total_percent IN NUMBER,
36 X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
37 X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
38 X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
39 X_invoice_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
40 X_base_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
41 X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
42 X_Error_Code OUT NOCOPY VARCHAR2,
43 X_Debug_Info OUT NOCOPY VARCHAR2,
44 X_Debug_Context OUT NOCOPY VARCHAR2,
45 X_msg_application OUT NOCOPY VARCHAR2,
46 X_msg_data OUT NOCOPY VARCHAR2,
47 X_calling_sequence IN VARCHAR2) RETURN BOOLEAN
48 IS
49
50 CURSOR dist_set_lines_cur IS
51 SELECT ADSL.distribution_set_line_number,
52 ADSL.description,
53 ADSL.dist_code_combination_id,
54 GL.account_type,
55 ADSL.percent_distribution,
56 ADSL.project_id,
57 ADSL.task_id,
58 ADSL.expenditure_type,
59 ADSL.expenditure_organization_id,
60 ADSL.project_Accounting_context,
61 ADSL.award_id,
62 ADSL.attribute_category,
63 ADSL.attribute1,
64 ADSL.attribute2,
65 ADSL.attribute3,
66 ADSL.attribute4,
67 ADSL.attribute5,
68 ADSL.attribute6,
69 ADSL.attribute7,
70 ADSL.attribute8,
71 ADSL.attribute9,
72 ADSL.attribute10,
73 ADSL.attribute11,
74 ADSL.attribute12,
75 ADSL.attribute13,
76 ADSL.attribute14,
77 ADSL.attribute15,
78 ADSL.type_1099
79 FROM AP_Distribution_Set_Lines ADSL,
80 GL_Code_combinations GL
81 WHERE distribution_set_id = X_invoice_lines_rec.Distribution_Set_id
82 AND GL.code_combination_id = ADSL.dist_code_combination_id
83 ORDER BY distribution_set_line_number;
84
85
86 l_pa_allows_overrides VARCHAR2(1) := 'N';
87 l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
88 user_id NUMBER;
89 l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
90 l_dset_line_ccid
91 AP_DISTRIBUTION_SET_LINES.DIST_CODE_COMBINATION_ID%TYPE;
92 l_dset_dist_line_num
93 AP_DISTRIBUTION_SET_LINES.DISTRIBUTION_SET_LINE_NUMBER%TYPE;
94 l_dset_line_description AP_DISTRIBUTION_SET_LINES.DESCRIPTION%TYPE;
95 l_dset_line_type_1099 AP_DISTRIBUTION_SET_LINES.TYPE_1099%TYPE;
96 l_dset_line_project_id AP_DISTRIBUTION_SET_LINES.PROJECT_ID%TYPE;
97 l_dset_line_task_id AP_DISTRIBUTION_SET_LINES.TASK_ID%TYPE;
98 l_dset_line_expenditure_type AP_DISTRIBUTION_SET_LINES.EXPENDITURE_TYPE%TYPE;
99 l_dset_line_expenditure_org_id
100 AP_DISTRIBUTION_SET_LINES.EXPENDITURE_ORGANIZATION_ID%TYPE;
101 l_dset_line_proj_acct_context
102 AP_DISTRIBUTION_SET_LINES.PROJECT_ACCOUNTING_CONTEXT%TYPE;
103 l_dset_line_award_id AP_DISTRIBUTION_SET_LINES.AWARD_ID%TYPE;
104 l_award_id AP_DISTRIBUTION_SET_LINES.AWARD_ID%TYPE;
105 l_dset_line_percent_dist
106 AP_DISTRIBUTION_SET_LINES.PERCENT_DISTRIBUTION%TYPE;
107 l_dset_line_attribute_category
108 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE_CATEGORY%TYPE;
109 l_dset_line_attribute1 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE1%TYPE;
110 l_dset_line_attribute2 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE2%TYPE;
111 l_dset_line_attribute3 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE3%TYPE;
112 l_dset_line_attribute4 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE4%TYPE;
113 l_dset_line_attribute5 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE5%TYPE;
114 l_dset_line_attribute6 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE6%TYPE;
115 l_dset_line_attribute7 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE7%TYPE;
116 l_dset_line_attribute8 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE8%TYPE;
117 l_dset_line_attribute9 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE9%TYPE;
118 l_dset_line_attribute10 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE10%TYPE;
119 l_dset_line_attribute11 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE11%TYPE;
120 l_dset_line_attribute12 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE12%TYPE;
121 l_dset_line_attribute13 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE13%TYPE;
122 l_dset_line_attribute14 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE14%TYPE;
123 l_dset_line_attribute15 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE15%TYPE;
124 l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
125 l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
126 l_dist_set_percent_number NUMBER;
127 l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
128 l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
129 l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
130 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
131 l_status VARCHAR2(10);
132 l_industry VARCHAR2(10);
133 l_set_of_books_id AP_INVOICES.SET_OF_BOOKS_ID%TYPE;
134 l_pa_installed VARCHAR2(1);
135
136 l_max_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
137 l_max_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
138 l_max_i BINARY_INTEGER := 0;
139 l_max_pa_qty_i BINARY_INTEGER := 0;
140 i BINARY_INTEGER := 0;
141 l_msg_application VARCHAR2(25);
142 l_msg_type VARCHAR2(25);
143 l_msg_token1 VARCHAR2(30);
144 l_msg_token2 VARCHAR2(30);
145 l_msg_token3 VARCHAR2(30);
146 l_msg_count NUMBER;
147 l_msg_data VARCHAR2(30);
148 l_billable_flag VARCHAR2(25);
149 l_unbuilt_flex VARCHAR2(240):='';
150 l_reason_unbuilt_flex VARCHAR2(2000):='';
151 debug_context VARCHAR2(2000);
152 current_calling_sequence VARCHAR2(2000);
153 debug_info VARCHAR2(1000);
154 l_error_found VARCHAR2(1);
155 l_rounding_exists VARCHAR2(1) := 'N';
156 l_rounding_pa_qty_exists VARCHAR2(1) := 'N';
157 --For bug2938770
158 l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
159 l_prepay_dist_code_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
160 --bugfix:5725904
161 l_sys_link_function VARCHAR2(2);
162 l_message_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
163 l_api_name VARCHAR2(50);
164
165
166 BEGIN
167
168 -- Update the calling sequence
169 --
170 current_calling_sequence :=
171 'AP_INVOICE_LINES_PKG.generate_dist_tab_for_dist_set<-'
172 ||X_calling_sequence;
173 l_api_name := 'Generate_Dist_Tab_For_Dist_Set';
174 --------------------------------------------------------------
175 -- Step 1
176 -- Get type of distribution set IF not passed in as a parameter
177 --
178 --------------------------------------------------------------
179 IF (X_dist_Set_total_percent is NULL) then
180 BEGIN
181 SELECT total_percent_distribution
182 INTO l_dist_set_percent_number
183 FROM ap_distribution_sets
184 WHERE distribution_set_id = X_invoice_lines_rec.distribution_set_id;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 IF (SQLCODE <> -20001) THEN
188 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
189 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
190 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
191 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
192 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
193 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
194 END IF;
195 X_error_code := 'AP_DEBUG';
196 RETURN (FALSE);
197 END;
198 ELSE
199 l_dist_set_percent_number := X_dist_set_total_percent;
200 END IF;
201
202 --------------------------------------------------------------
203 -- Step 2 - Validate distribution level information including
204 -- accounting and project level information. This is done in
205 -- a loop that traverses all distribution lines defined in the
206 -- distribution set.
207 --------------------------------------------------------------
208 --bug 2938770 populating the local variables
209 SELECT nvl(PVS.prepay_code_combination_id,
210 SP.prepay_code_combination_id),
211 AI.invoice_type_lookup_code
212 INTO l_prepay_dist_code_ccid,
213 l_invoice_type_lookup_code
214 FROM ap_invoices AI,
215 po_vendor_sites PVS,
216 ap_system_parameters SP
217 WHERE AI.invoice_id = X_invoice_lines_rec.invoice_id
218 AND PVS.vendor_site_id = AI.vendor_site_id;
219
220 i := 0;
221 OPEN dist_set_lines_cur;
222 LOOP
223 FETCH dist_set_lines_cur
224 INTO l_dset_dist_line_num,
225 l_dset_line_description,
226 l_dset_line_ccid,
227 l_account_type,
228 l_dset_line_percent_dist,
229 l_dset_line_project_id,
230 l_dset_line_task_id,
231 l_dset_line_expenditure_type,
232 l_dset_line_expenditure_org_id,
233 l_dset_line_proj_acct_context,
234 l_dset_line_award_id,
235 l_dset_line_attribute_category,
236 l_dset_line_attribute1,
237 l_dset_line_attribute2,
238 l_dset_line_attribute3,
239 l_dset_line_attribute4,
240 l_dset_line_attribute5,
241 l_dset_line_attribute6,
242 l_dset_line_attribute7,
243 l_dset_line_attribute8,
244 l_dset_line_attribute9,
245 l_dset_line_attribute10,
246 l_dset_line_attribute11,
247 l_dset_line_attribute12,
248 l_dset_line_attribute13,
249 l_dset_line_attribute14,
250 l_dset_line_attribute15,
251 l_dset_line_type_1099;
252
253 EXIT WHEN dist_set_lines_cur%NOTFOUND;
254
255 X_dist_tab(i).dist_line_num := l_dset_dist_line_num;
256 X_dist_tab(i).description := nvl(l_dset_line_description,
257 X_invoice_lines_rec.description);
258 X_dist_tab(i).accounting_date := X_invoice_lines_rec.accounting_date;
259 X_dist_tab(i).period_name := X_invoice_lines_rec.period_name;
260 X_dist_tab(i).awt_group_id := X_invoice_lines_rec.awt_group_id;
261 X_dist_tab(i).attribute_category := l_dset_line_attribute_category;
262 X_dist_tab(i).attribute1 := l_dset_line_attribute1;
263 X_dist_tab(i).attribute2 := l_dset_line_attribute2;
264 X_dist_tab(i).attribute3 := l_dset_line_attribute3;
265 X_dist_tab(i).attribute4 := l_dset_line_attribute4;
266 X_dist_tab(i).attribute5 := l_dset_line_attribute5;
267 X_dist_tab(i).attribute6 := l_dset_line_attribute6;
268 X_dist_tab(i).attribute7 := l_dset_line_attribute7;
269 X_dist_tab(i).attribute8 := l_dset_line_attribute8;
270 X_dist_tab(i).attribute9 := l_dset_line_attribute9;
271 X_dist_tab(i).attribute10 := l_dset_line_attribute10;
272 X_dist_tab(i).attribute11 := l_dset_line_attribute11;
273 X_dist_tab(i).attribute12 := l_dset_line_attribute12;
274 X_dist_tab(i).attribute13 := l_dset_line_attribute13;
275 X_dist_tab(i).attribute14 := l_dset_line_attribute14;
276 X_dist_tab(i).attribute15 := l_dset_line_attribute15;
277
278 ---bugfix : 7022001
279 X_dist_tab(i).pay_awt_group_id :=X_invoice_lines_rec.pay_awt_group_id;
280
281 --bugfix:4674194
282 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
283 X_dist_tab(i).global_attribute3 := x_invoice_lines_rec.ship_to_location_id;
284 END IF;
285
286 --ETAX: Invwkb
287 X_dist_tab(i).intended_use := x_invoice_lines_rec.primary_intended_use;
288
289 -- Populate the amounts depending on whether the distribution
290 -- set is a skeleton or not.
291 IF (l_dist_set_percent_number <> 100) then
292 l_dist_amount := 0;
293 l_dist_base_amount := NULL; -- Bug 5199337
294 ELSE
295 --bug6653070
296 l_dist_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
297 NVL(l_dset_line_percent_dist,0)
298 * (NVL(X_invoice_lines_rec.amount,0))/100,
299 X_invoice_currency);
300 l_dist_base_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
301 NVL(l_dist_amount, 0) * X_exchange_rate,
302 X_base_currency);
303 END IF;
304
305 X_dist_tab(i).amount := l_dist_amount;
306 X_dist_tab(i).base_amount := l_dist_base_amount;
307 X_dist_tab(i).rounding_amt := 0;
308
309 -- Maintain a running total that will be used for rounding to the
310 -- line base amount as well as the total amount.
311 l_running_total_amount := l_running_total_amount + l_dist_amount;
312 l_running_total_base_amt := l_running_total_base_amt + l_dist_base_amount;
313
314 IF (ABS(l_max_amount) <= ABS(l_dist_amount) OR i = 0) then
315 l_max_amount := l_dist_amount;
316 l_max_i := i;
317 END IF;
318
319 IF (X_invoice_lines_rec.project_id IS NOT NULL) THEN
320
321 X_dist_tab(i).project_id := X_invoice_lines_rec.project_id;
322 X_dist_tab(i).task_id := X_invoice_lines_rec.task_id;
323 X_dist_tab(i).expenditure_type := X_invoice_lines_rec.expenditure_type;
324 X_dist_tab(i).expenditure_organization_id := X_invoice_lines_rec.expenditure_organization_id;
325 X_dist_tab(i).expenditure_item_date := X_invoice_lines_rec.expenditure_item_date;
326
327 IF (X_invoice_lines_rec.pa_quantity IS NOT NULL AND
328 X_invoice_lines_rec.amount <> 0) then
329 X_dist_tab(i).pa_quantity := X_invoice_lines_rec.pa_quantity * l_dist_amount /
330 X_invoice_lines_rec.amount;
331 END IF;
332
333 X_dist_tab(i).pa_addition_flag := 'N';
334
335 ELSIF (l_dset_line_project_id is not null) then
336
337 X_dist_tab(i).project_id := l_dset_line_project_id;
338 X_dist_tab(i).task_id := l_dset_line_task_id;
339 X_dist_tab(i).expenditure_type := l_dset_line_expenditure_type;
340 X_dist_tab(i).expenditure_organization_id := l_dset_line_expenditure_org_id;
341 X_dist_tab(i).project_accounting_context := l_dset_line_proj_acct_context;
342
343 IF (X_invoice_lines_rec.pa_quantity is not null AND
344 X_invoice_lines_rec.amount <> 0) then
345
346 X_dist_tab(i).pa_quantity := X_invoice_lines_rec.pa_quantity * l_dist_amount /
347 X_invoice_lines_rec.amount;
348 END IF;
349 X_dist_tab(i).pa_addition_flag := 'N';
350 ELSE
351 X_dist_tab(i).pa_addition_flag := 'E';
352 END IF;
353
354 l_running_total_pa_quantity :=
355 l_running_total_pa_quantity + nvl(X_dist_tab(i).pa_quantity,0);
356 IF (ABS(l_max_pa_quantity) <= ABS(nvl(X_dist_tab(i).pa_quantity, 0))
357 OR i = 0) THEN
358 l_max_pa_quantity := X_dist_tab(i).pa_quantity;
359 l_max_pa_qty_i := i;
360 END IF;
361
362 X_dist_tab(i).set_of_books_id := X_invoice_lines_rec.set_of_books_id;
363 X_dist_tab(i).org_id := X_invoice_lines_rec.org_id;
364
365 X_dist_tab(i).type_1099 := nvl(l_dset_line_type_1099,
366 X_invoice_lines_rec.type_1099);
367 IF (X_dist_tab(i).type_1099 IS NOT NULL) THEN
368 X_dist_tab(i).income_tax_region :=
369 X_invoice_lines_rec.income_tax_region;
370 ELSE
371 X_dist_tab(i).income_tax_region := NULL;
372 END IF;
373
374 -- Gather the data we need to call validation in PA
375 IF (X_dist_tab(i).project_id is not null) then
376 user_id := to_number(FND_PROFILE.VALUE('USER_ID'));
377 l_pa_allows_overrides :=
378 FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES');
379 BEGIN
380 SELECT employee_id
381 INTO l_employee_id
382 FROM ap_suppliers /* Bug 4718054 */
383 WHERE DECODE(SIGN(TO_DATE(TO_CHAR(START_DATE_ACTIVE,'DD-MM-YYYY'),
384 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
385 1, 'N', DECODE(SIGN(TO_DATE(TO_CHAR(END_DATE_ACTIVE ,'DD-MM-YYYY'),
386 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
387 -1, 'N', 0, 'N', 'Y')) = 'Y'
388 AND enabled_flag = 'Y'
389 AND vendor_id = X_vendor_id;
390 EXCEPTION
391 WHEN no_data_found then
392 l_employee_id := NULL;
393 WHEN OTHERS then
394 l_employee_id := NULL;
395 END;
396 END IF;
397
398 debug_info := 'Get expenditure item date IF null';
399 IF (X_dist_tab(i).project_id IS NOT NULL AND
400 X_dist_tab(i).expenditure_item_date IS NULL) THEN
401 X_dist_tab(i).expenditure_item_date :=
402 AP_INVOICES_PKG.get_expenditure_item_date(
403 X_invoice_lines_rec.invoice_id,
404 X_invoice_date,
405 X_invoice_lines_rec.accounting_date,
406 null,
407 null,
408 l_error_found);
409 IF (l_error_found = 'Y') THEN
410 CLOSE dist_set_lines_cur;
411 Debug_info := debug_info
412 || ': cannot read expenditure item date information';
413 X_debug_context := current_calling_sequence;
414 X_debug_info := debug_info;
415 X_error_code := 'AP_DEBUG';
416 RETURN(FALSE);
417 END IF;
418 END IF;
419
420 -- Do not validate project information IF called from the Import since that
421 -- should be validated in the import itself.
422
423 IF (X_dist_tab(i).project_id IS NOT NULL AND
424 nvl(X_line_source, 'OTHER') <> 'IMPORT') then
425
426 --bugfix:5725904
427 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
428 l_sys_link_function :='ER' ;
429 Else
430 l_sys_link_function :='VI' ;
431 End if;
432
433 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
434 X_PROJECT_ID => X_dist_tab(i).project_id,
435 X_TASK_ID => X_dist_tab(i).task_id,
436 X_EI_DATE => X_dist_tab(i).expenditure_item_date,
437 X_EXPENDITURE_TYPE => X_dist_tab(i).expenditure_type,
438 X_NON_LABOR_RESOURCE => null,
439 X_PERSON_ID => l_employee_id,
440 X_QUANTITY => nvl(X_dist_tab(i).pa_quantity, '1'),
441 X_denom_currency_code => X_invoice_currency,
442 X_acct_currency_code => X_base_currency,
443 X_denom_raw_cost => l_dist_amount,
444 X_acct_raw_cost => l_dist_base_amount,
445 X_acct_rate_type => X_exchange_rate_type,
446 X_acct_rate_date => X_exchange_date,
447 X_acct_exchange_rate => X_exchange_rate,
448 X_TRANSFER_EI => null,
449 X_INCURRED_BY_ORG_ID => X_dist_tab(i).expenditure_organization_id,
450 X_NL_RESOURCE_ORG_ID => null,
451 X_TRANSACTION_SOURCE => l_sys_link_function, --5725904
452 X_CALLING_MODULE => 'apinlinb.pls',
453 X_VENDOR_ID => X_vendor_id,
454 X_ENTERED_BY_USER_ID => user_id,
455 X_ATTRIBUTE_CATEGORY => X_dist_tab(i).attribute_category,
456 X_ATTRIBUTE1 => X_dist_tab(i).attribute1,
457 X_ATTRIBUTE2 => X_dist_tab(i).attribute2,
458 X_ATTRIBUTE3 => X_dist_tab(i).attribute3,
459 X_ATTRIBUTE4 => X_dist_tab(i).attribute4,
460 X_ATTRIBUTE5 => X_dist_tab(i).attribute5,
461 X_ATTRIBUTE6 => X_dist_tab(i).attribute6,
462 X_ATTRIBUTE7 => X_dist_tab(i).attribute7,
463 X_ATTRIBUTE8 => X_dist_tab(i).attribute8,
464 X_ATTRIBUTE9 => X_dist_tab(i).attribute9,
465 X_ATTRIBUTE10 => X_dist_tab(i).attribute10,
466 X_ATTRIBUTE11 => X_dist_tab(i).attribute11,
467 X_ATTRIBUTE12 => X_dist_tab(i).attribute12,
468 X_ATTRIBUTE13 => X_dist_tab(i).attribute13,
469 X_ATTRIBUTE14 => X_dist_tab(i).attribute14,
470 X_ATTRIBUTE15 => X_dist_tab(i).attribute15,
471 X_msg_application => l_msg_application,
472 X_msg_type => l_msg_type,
473 X_msg_token1 => l_msg_token1,
474 X_msg_token2 => l_msg_token2,
475 X_msg_token3 => l_msg_token3,
476 X_msg_count => l_msg_count,
477 X_msg_data => l_msg_data,
478 X_BILLABLE_FLAG => l_billable_flag);
479
480 IF (l_msg_data IS NOT NULL) THEN
481 CLOSE dist_set_lines_cur;
482 X_msg_application := l_msg_application;
483 X_msg_data := l_msg_data;
484
485 --bugfix:5725904
486 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
487 /*bug 6682104 setting the token values*/
488 IF (l_msg_token1 IS NOT NULL) THEN
489 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
490 ELSE
491 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
492 END IF;
493
494 IF (l_msg_token2 IS NOT NULL) THEN
495 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
496 ELSE
497 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
498 END IF;
499
500 IF (l_msg_token3 IS NOT NULL) THEN
501 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
502 ELSE
503 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
504 END IF;
505
506 l_message_text := Fnd_Message.get;
507 X_Error_Code := l_message_text;
508
509 RETURN(FALSE);
510 END IF;
511 END IF; -- Validate PA IF project related and not called from import
512
513 -- If the distribution will be project related and the project comes from
514 -- the invoice line and this process is called from the import, use the
515 -- account from the invoice line which has already been overlayed in the
516 -- import. Otherwise, use the account from the distribution set line and
517 -- overlay as per need be.
518 --bug 2938770 For prepayment
519 --bug 7483050 added condition to check if dist set is null
520 IF (l_invoice_type_lookup_code = 'PREPAYMENT' and l_dset_line_ccid is null) then
521 X_dist_tab(i).dist_ccid := l_prepay_dist_code_ccid;
522
523 ElSIF (X_dist_tab(i).project_id IS NOT NULL AND
524 l_dset_line_project_id IS NULL AND
525 nvl(X_line_source, 'OTHER') = 'IMPORT') THEN
526 X_dist_tab(i).dist_ccid := X_invoice_lines_rec.default_dist_ccid;
527 BEGIN
528 SELECT account_type
529 INTO l_account_type
530 FROM gl_code_combinations
531 WHERE code_combination_id = X_invoice_lines_rec.default_dist_ccid;
532
533 EXCEPTION
534 WHEN no_data_found THEN
535 CLOSE dist_set_lines_cur;
536 Debug_info := debug_info || ': cannot read account type information';
537 X_debug_context := current_calling_sequence;
538 X_debug_info := debug_info;
539 IF (SQLCODE <> -20001) THEN
540 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
541 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
542 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
543 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
544 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
545 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
546 END IF;
547 X_error_code := 'AP_DEBUG';
548 RETURN(FALSE);
549 END;
550
551 ELSE -- project information comes from distribution set line or at least
552 -- is not from IMPORT
553 X_dist_tab(i).dist_ccid := l_dset_line_ccid;
554 IF ((X_dist_tab(i).project_id is not null AND
555 l_pa_allows_overrides = 'N')) then
556 IF ( NOT (AP_UTILITIES_PKG.IS_CCID_VALID(
557 l_dset_line_ccid,
558 X_chart_of_accounts_id,
559 X_invoice_lines_rec.accounting_date,
560 current_calling_sequence))) then
561 X_error_code := 'AP_INVALID_CCID';
562 CLOSE dist_set_lines_cur;
563 RETURN(FALSE);
564 END IF;
565
566 ELSE -- project allows overrides
567 IF (X_invoice_lines_rec.overlay_dist_code_concat is NULL AND
568 X_invoice_lines_rec.balancing_segment is NULL AND
569 X_invoice_lines_rec.account_segment is NULL AND
570 X_invoice_lines_rec.cost_center_segment is NULL) then
571 IF ( NOT (AP_UTILITIES_PKG.IS_CCID_VALID(
572 l_dset_line_ccid,
573 X_chart_of_accounts_id,
574 X_invoice_lines_rec.accounting_date,
575 current_calling_sequence))) then
576 X_error_code := 'AP_INVALID_CCID';
577 CLOSE DIST_SET_LINES_CUR;
578 RETURN(FALSE);
579 END IF;
580 ELSE -- account overlay information is provided at the line
581 IF ( NOT (AP_UTILITIES_PKG.OVERLAY_SEGMENTS (
582 X_invoice_lines_rec.balancing_segment,
583 X_invoice_lines_rec.cost_center_segment,
584 X_invoice_lines_rec.account_segment,
585 X_invoice_lines_rec.overlay_dist_code_concat,
586 l_dset_line_ccid,
587 X_invoice_lines_rec.set_of_books_id,
588 'CREATE_COMB_NO_AT',
589 l_unbuilt_flex,
590 l_reason_unbuilt_flex,
591 FND_GLOBAL.RESP_APPL_ID,
592 FND_GLOBAL.RESP_ID,
593 FND_GLOBAL.USER_ID,
594 current_calling_sequence))) then
595 X_error_code := 'AP_ACCOUNT_OVERLAY_INVALID';
596 CLOSE dist_set_lines_cur;
597 RETURN(FALSE);
598
599 ELSE -- overlay segments did not fail
600 X_dist_tab(i).dist_ccid := l_dset_line_ccid;
601 debug_info := 'Get account type from overlayed account';
602 BEGIN
603 SELECT account_type
604 INTO l_account_type
605 FROM gl_code_combinations
606 WHERE code_combination_id = l_dset_line_ccid;
607
608 EXCEPTION
609 WHEN no_data_found then
610 CLOSE dist_set_lines_cur;
611 Debug_info := debug_info
612 || ': cannot read account type information';
613 X_debug_context := current_calling_sequence;
614 X_debug_info := debug_info;
615 IF (SQLCODE <> -20001) THEN
616 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
617 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
618 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
619 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
620 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
621 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
622 END IF;
623 X_error_code := 'AP_DEBUG';
624 RETURN(FALSE);
625 END;
626 END IF; -- overlay segments did not fail
627 END IF; -- account overlay information is not null
628 END IF; -- project allow overrides
629 END IF; -- project information comes from distribution set line or
630 -- at least is not from IMPORT
631
632 IF (l_account_type = 'A' OR
633 (l_account_type = 'E' AND
634 X_invoice_lines_rec.assets_tracking_flag = 'Y')) THEN
635 X_dist_tab(i).assets_tracking_flag := 'Y';
636 X_dist_tab(i).asset_book_type_code :=
637 X_invoice_lines_rec.asset_book_type_code;
638 X_dist_tab(i).asset_category_id :=
639 X_invoice_lines_rec.asset_category_id;
640 ELSE
641 X_dist_tab(i).assets_tracking_flag := 'N';
642 X_dist_tab(i).asset_book_type_code := NULL;
643 X_dist_tab(i).asset_category_id := NULL;
644 END IF;
645
646
647 --Perform Grant information validation
648
649 IF (X_invoice_lines_rec.award_id IS NOT NULL) THEN
650
651 X_dist_tab(i).award_id := X_invoice_lines_rec.award_id;
652
653 ELSIF (l_dset_line_award_id is not null) THEN
654
655 X_dist_tab(i).award_id := gms_ap_api.get_distribution_award(l_dset_line_award_id);
656
657 GMS_AP_API.validate_transaction
658 (x_project_id => l_dset_line_project_id,
659 x_task_id => l_dset_line_task_id,
660 x_award_id => l_dset_line_award_id,
661 x_expenditure_type => l_dset_line_expenditure_type,
662 x_expenditure_item_date => x_dist_tab(i).expenditure_item_date,
663 x_calling_sequence => current_calling_sequence,
664 x_msg_application => l_msg_application,
665 x_msg_type => l_msg_type,
666 x_msg_count => l_msg_count,
667 x_msg_data => l_msg_data);
668
669 IF (l_msg_data IS NOT NULL) THEN
670 x_error_code := 'AP_INVALID_GRANT_INFO';
671 CLOSE dist_set_lines_cur;
672 X_msg_application := l_msg_application;
673 X_msg_data := l_msg_data;
674 RETURN(FALSE);
675 END IF;
676 END IF;
677
678 i := i + 1;
679
680 END LOOP;
681 CLOSE dist_set_lines_cur;
682
683 --------------------------------------------------------------
684 -- Step 3 - If any rounding is required on the distribution
685 -- amounts and the distribution to round is project related
686 -- revalidate PA information. Also, IF any rounding is
687 -- required and we will generate distributions, THEN update
688 -- the plsql distributions table.
689 -- bug6653070
690 --------------------------------------------------------------
691
692 IF (l_dist_set_percent_number = 100) THEN
693 IF (l_running_total_amount <> (X_invoice_lines_rec.amount) ) THEN
694 X_dist_tab(l_max_i).amount :=
695 X_dist_tab(l_max_i).amount + ((X_invoice_lines_rec.amount)
696 - l_running_total_amount);
697 l_running_total_base_amt :=
698 l_running_total_base_amt - X_dist_tab(l_max_i).base_amount;
699 X_dist_tab(l_max_i).base_amount :=
700 AP_UTILITIES_PKG.Ap_Round_Currency(
701 NVL(X_dist_tab(l_max_i).amount, 0) * X_exchange_rate ,
702 X_base_currency);
703 l_running_total_base_amt :=
704 l_running_total_base_amt + X_dist_tab(l_max_i).base_amount;
705 l_rounding_exists := 'Y';
706 END IF;
707 IF (nvl(l_running_total_base_amt, 0) <>
708 nvl(X_invoice_lines_rec.base_amount, 0)) THEN
709 X_dist_tab(l_max_i).rounding_amt := X_invoice_lines_rec.base_amount -
710 l_running_total_base_amt;
711 X_dist_tab(l_max_i).base_amount := X_dist_tab(l_max_i).base_amount +
712 X_dist_tab(l_max_i).rounding_amt;
713 l_rounding_exists := 'Y';
714 END IF;
715 IF (nvl(l_running_total_pa_quantity, 0) <>
716 nvl(X_invoice_lines_rec.pa_quantity, 0)) THEN
717 X_dist_tab(l_max_pa_qty_i).pa_quantity :=
718 X_dist_tab(l_max_pa_qty_i).pa_quantity +
719 X_invoice_lines_rec.pa_quantity - l_running_total_pa_quantity;
720 l_rounding_pa_qty_exists := 'Y';
721 END IF;
722 END IF; -- total percent is 100
723 IF (X_dist_tab(l_max_i).project_id IS NOT NULL AND
724 l_rounding_exists = 'Y') THEN
725
726 --bugfix:5725904
727 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
728 l_sys_link_function :='ER' ;
729 Else
730 l_sys_link_function :='VI' ;
731 End if;
732
733
734 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
735 X_PROJECT_ID => X_dist_tab(l_max_i).project_id,
736 X_TASK_ID => X_dist_tab(l_max_i).task_id,
737 X_EI_DATE => X_dist_tab(l_max_i).expenditure_item_date,
738 X_EXPENDITURE_TYPE => X_dist_tab(l_max_i).expenditure_type,
739 X_NON_LABOR_RESOURCE => null,
740 X_PERSON_ID => l_employee_id,
741 X_QUANTITY => nvl(X_dist_tab(l_max_i).pa_quantity, '1'),
742 X_denom_currency_code => X_invoice_currency,
743 X_acct_currency_code => X_base_currency,
744 X_denom_raw_cost => X_dist_tab(l_max_i).amount,
745 X_acct_raw_cost => X_dist_tab(l_max_i).base_amount,
746 X_acct_rate_type => X_exchange_rate_type,
747 X_acct_rate_date => X_exchange_date,
748 X_acct_exchange_rate => X_exchange_rate,
749 X_TRANSFER_EI => null,
750 X_INCURRED_BY_ORG_ID => X_dist_tab(l_max_i).expenditure_organization_id,
751 X_NL_RESOURCE_ORG_ID => null,
752 X_TRANSACTION_SOURCE => l_sys_link_function,
753 X_CALLING_MODULE => 'apinlinb.pls',
754 X_VENDOR_ID => X_vendor_id,
755 X_ENTERED_BY_USER_ID => user_id,
756 X_ATTRIBUTE_CATEGORY => X_dist_tab(l_max_i).attribute_category,
757 X_ATTRIBUTE1 => X_dist_tab(l_max_i).attribute1,
758 X_ATTRIBUTE2 => X_dist_tab(l_max_i).attribute2,
759 X_ATTRIBUTE3 => X_dist_tab(l_max_i).attribute3,
760 X_ATTRIBUTE4 => X_dist_tab(l_max_i).attribute4,
761 X_ATTRIBUTE5 => X_dist_tab(l_max_i).attribute5,
762 X_ATTRIBUTE6 => X_dist_tab(l_max_i).attribute6,
763 X_ATTRIBUTE7 => X_dist_tab(l_max_i).attribute7,
764 X_ATTRIBUTE8 => X_dist_tab(l_max_i).attribute8,
765 X_ATTRIBUTE9 => X_dist_tab(l_max_i).attribute9,
766 X_ATTRIBUTE10 => X_dist_tab(l_max_i).attribute10,
767 X_ATTRIBUTE11 => X_dist_tab(l_max_i).attribute11,
768 X_ATTRIBUTE12 => X_dist_tab(l_max_i).attribute12,
769 X_ATTRIBUTE13 => X_dist_tab(l_max_i).attribute13,
770 X_ATTRIBUTE14 => X_dist_tab(l_max_i).attribute14,
771 X_ATTRIBUTE15 => X_dist_tab(l_max_i).attribute15,
772 X_msg_application => l_msg_application,
773 X_msg_type => l_msg_type,
774 X_msg_token1 => l_msg_token1,
775 X_msg_token2 => l_msg_token2,
776 X_msg_token3 => l_msg_token3,
777 X_msg_count => l_msg_count,
778 X_msg_data => l_msg_data,
779 X_BILLABLE_FLAG => l_billable_flag);
780
781 IF (l_msg_data is not null) then
782 X_msg_application := l_msg_application;
783 X_msg_data := l_msg_data;
784
785 --bugfix:5725904
786 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
787 /*bug 6682104 setting the token values*/
788 IF (l_msg_token1 IS NOT NULL) THEN
789 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
790 ELSE
791 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
792 END IF;
793
794 IF (l_msg_token2 IS NOT NULL) THEN
795 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
796 ELSE
797 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
798 END IF;
799
800 IF (l_msg_token3 IS NOT NULL) THEN
801 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
802 ELSE
803 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
804 END IF;
805 l_message_text := Fnd_Message.get;
806 x_error_code := l_message_text;
807
808 RETURN(FALSE);
809 END IF;
810 END IF; -- project id is not null and there was rounding
811 IF ((X_dist_tab(l_max_pa_qty_i).project_id IS NOT NULL) AND
812 l_rounding_pa_qty_exists = 'Y' AND
813 (l_rounding_exists <> 'Y' OR
814 (l_rounding_exists = 'Y' AND
815 l_max_i <> l_max_pa_qty_i))) THEN
816
817 --bugfix:5725904
818 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
819 l_sys_link_function :='ER' ;
820 Else
821 l_sys_link_function :='VI' ;
822 End if;
823
824
825 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
826 X_PROJECT_ID => X_dist_tab(l_max_pa_qty_i).project_id,
827 X_TASK_ID => X_dist_tab(l_max_pa_qty_i).task_id,
828 X_EI_DATE => X_dist_tab(l_max_pa_qty_i).expenditure_item_date,
829 X_EXPENDITURE_TYPE => X_dist_tab(l_max_pa_qty_i).expenditure_type,
830 X_NON_LABOR_RESOURCE => null,
831 X_PERSON_ID => l_employee_id,
832 X_QUANTITY => nvl(X_dist_tab(l_max_pa_qty_i).pa_quantity, '1'),
833 X_denom_currency_code => X_invoice_currency,
834 X_acct_currency_code => X_base_currency,
835 X_denom_raw_cost => X_dist_tab(l_max_pa_qty_i).amount,
836 X_acct_raw_cost => X_dist_tab(l_max_pa_qty_i).base_amount,
837 X_acct_rate_type => X_exchange_rate_type,
838 X_acct_rate_date => X_exchange_date,
839 X_acct_exchange_rate => X_exchange_rate,
840 X_TRANSFER_EI => null,
841 X_INCURRED_BY_ORG_ID =>
842 X_dist_tab(l_max_pa_qty_i).expenditure_organization_id,
843 X_NL_RESOURCE_ORG_ID => null,
844 X_TRANSACTION_SOURCE => l_sys_link_function, --5725904
845 X_CALLING_MODULE => 'apinlinb.pls',
846 X_VENDOR_ID => X_vendor_id,
847 X_ENTERED_BY_USER_ID => user_id,
848 X_ATTRIBUTE_CATEGORY => X_dist_tab(l_max_pa_qty_i).attribute_category,
849 X_ATTRIBUTE1 => X_dist_tab(l_max_pa_qty_i).attribute1,
850 X_ATTRIBUTE2 => X_dist_tab(l_max_pa_qty_i).attribute2,
851 X_ATTRIBUTE3 => X_dist_tab(l_max_pa_qty_i).attribute3,
852 X_ATTRIBUTE4 => X_dist_tab(l_max_pa_qty_i).attribute4,
853 X_ATTRIBUTE5 => X_dist_tab(l_max_pa_qty_i).attribute5,
854 X_ATTRIBUTE6 => X_dist_tab(l_max_pa_qty_i).attribute6,
855 X_ATTRIBUTE7 => X_dist_tab(l_max_pa_qty_i).attribute7,
856 X_ATTRIBUTE8 => X_dist_tab(l_max_pa_qty_i).attribute8,
857 X_ATTRIBUTE9 => X_dist_tab(l_max_pa_qty_i).attribute9,
858 X_ATTRIBUTE10 => X_dist_tab(l_max_pa_qty_i).attribute10,
859 X_ATTRIBUTE11 => X_dist_tab(l_max_pa_qty_i).attribute11,
860 X_ATTRIBUTE12 => X_dist_tab(l_max_pa_qty_i).attribute12,
861 X_ATTRIBUTE13 => X_dist_tab(l_max_pa_qty_i).attribute13,
862 X_ATTRIBUTE14 => X_dist_tab(l_max_pa_qty_i).attribute14,
863 X_ATTRIBUTE15 => X_dist_tab(l_max_pa_qty_i).attribute15,
864 X_msg_application => l_msg_application,
865 X_msg_type => l_msg_type,
866 X_msg_token1 => l_msg_token1,
867 X_msg_token2 => l_msg_token2,
868 X_msg_token3 => l_msg_token3,
869 X_msg_count => l_msg_count,
870 X_msg_data => l_msg_data,
871 X_BILLABLE_FLAG => l_billable_flag);
872
873 IF (l_msg_data is not null) then
874 X_msg_application := l_msg_application;
875 X_msg_data := l_msg_data;
876
877 --bugfix:5725904
878 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
879 /*bug 6682104 setting the token values*/
880 IF (l_msg_token1 IS NOT NULL) THEN
881 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
882 ELSE
883 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
884 END IF;
885
886 IF (l_msg_token2 IS NOT NULL) THEN
887 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
888 ELSE
889 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
890 END IF;
891
892 IF (l_msg_token3 IS NOT NULL) THEN
893 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
894 ELSE
895 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
896 END IF;
897 l_message_text := Fnd_Message.get;
898 x_error_code := l_message_text;
899
900 RETURN(FALSE);
901 END IF;
902 END IF; -- Rounding of pa qty existed and it is for a different dist
903 -- than rounding of amount IF any.
904
905 IF (X_dist_tab(l_max_i).award_id IS NOT NULL AND
906 l_rounding_exists = 'Y') THEN
907
908 GMS_AP_API.validate_transaction
909 ( x_project_id => x_dist_tab(l_max_i).project_id,
910 x_task_id => x_dist_tab(l_max_i).task_id,
911 x_award_id => x_dist_tab(l_max_i).award_id,
912 x_expenditure_type => x_dist_tab(l_max_i).expenditure_type,
913 x_expenditure_item_date => x_dist_tab(l_max_i).expenditure_item_date,
914 x_calling_sequence => 'AWARD_ID',
915 x_msg_application => l_msg_application,
916 x_msg_type => l_msg_type,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data ) ;
919
920 IF (l_msg_data is not null) then
921 x_error_code := 'AP_INVALID_GRANT_INFO';
922 x_msg_application := l_msg_application;
923 x_msg_data := l_msg_data;
924 RETURN(FALSE);
925 END IF;
926 END IF;
927
928 IF ((X_dist_tab(l_max_pa_qty_i).award_id IS NOT NULL) AND
929 l_rounding_pa_qty_exists = 'Y' AND
930 (l_rounding_exists <> 'Y' OR
931 (l_rounding_exists = 'Y' AND
932 l_max_i <> l_max_pa_qty_i))) THEN
933
934 GMS_AP_API.validate_transaction
935 ( x_project_id => X_dist_tab(l_max_pa_qty_i).project_id,
936 x_task_id => X_dist_tab(l_max_pa_qty_i).task_id,
937 x_award_id => X_dist_tab(l_max_pa_qty_i).award_id,
938 x_expenditure_type => X_dist_tab(l_max_pa_qty_i).expenditure_type,
939 x_expenditure_item_date => X_dist_tab(l_max_pa_qty_i).expenditure_item_date,
940 x_calling_sequence => 'AWARD_ID',
941 x_msg_application => l_msg_application,
942 x_msg_type => l_msg_type,
943 x_msg_count => l_msg_count,
944 x_msg_data => l_msg_data );
945
946 IF (l_msg_data is not null) then
947 x_msg_application := l_msg_application;
948 x_msg_data := l_msg_data;
949 RETURN(FALSE);
950 END IF;
951 END IF;
952
953 RETURN(TRUE);
954
955 EXCEPTION
956 WHEN OTHERS THEN
957 Debug_info := 'Error occurred';
958 X_debug_context := current_calling_sequence;
959 X_debug_info := debug_info;
960 IF (SQLCODE <> -20001) THEN
961 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
962 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
963 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
964 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
965 ||', Invoice Line Number =
966 '||TO_CHAR(X_invoice_lines_Rec.line_Number));
967 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
968 END IF;
969 X_error_code := 'AP_DEBUG';
970 RETURN(FALSE);
971
972 END generate_dist_tab_for_dist_set;
973
974 -----------------------------------------------------------------------
975 -- FUNCTION insert_from_dist_set validates the distribution_set info
976 -- and generates distributions
977 -- by calling ap_invoice_distributions_pkg.insert_from_dist_set.
978 --
979 FUNCTION Insert_From_Dist_Set(
980 X_invoice_id IN NUMBER,
981 X_line_number IN NUMBER DEFAULT NULL,
982 X_GL_Date IN DATE,
983 X_Period_Name IN VARCHAR2,
984 X_Skeleton_Allowed IN VARCHAR2 DEFAULT 'N',
985 X_Generate_Dists IN VARCHAR2 DEFAULT 'Y',
986 X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
987 X_Error_Code OUT NOCOPY VARCHAR2,
988 X_Debug_Info OUT NOCOPY VARCHAR2,
989 X_Debug_Context OUT NOCOPY VARCHAR2,
990 X_Msg_Application OUT NOCOPY VARCHAR2,
991 X_Msg_Data OUT NOCOPY VARCHAR2,
992 X_calling_sequence IN VARCHAR2) RETURN BOOLEAN
993
994 IS
995 CURSOR line_rec(X_line_number NUMBER) IS
996 SELECT invoice_id,
997 line_number,
998 line_type_lookup_code,
999 requester_id,
1000 description,
1001 line_source,
1002 org_id,
1003 line_group_number,
1004 inventory_item_id,
1005 item_description,
1006 serial_number,
1007 manufacturer,
1008 model_number,
1009 warranty_number,
1010 generate_dists,
1011 match_type,
1012 distribution_set_id,
1013 account_segment,
1014 balancing_segment,
1015 cost_center_segment,
1016 overlay_dist_code_concat,
1017 default_dist_ccid,
1018 prorate_across_all_items,
1019 accounting_date,
1020 period_name,
1021 deferred_acctg_flag,
1022 def_acctg_start_date,
1023 def_acctg_end_date,
1024 def_acctg_number_of_periods,
1025 def_acctg_period_type,
1026 set_of_books_id,
1027 amount,
1028 base_amount,
1029 rounding_amt,
1030 quantity_invoiced,
1031 unit_meas_lookup_code,
1032 unit_price,
1033 wfapproval_status,
1034 discarded_flag,
1035 original_amount,
1036 original_base_amount,
1037 original_rounding_amt,
1038 cancelled_flag,
1039 income_tax_region,
1040 type_1099,
1041 stat_amount,
1042 prepay_invoice_id,
1043 prepay_line_number,
1044 invoice_includes_prepay_flag,
1045 corrected_inv_id,
1046 corrected_line_number,
1047 po_header_id,
1048 po_line_id,
1049 po_release_id,
1050 po_line_location_id,
1051 po_distribution_id,
1052 rcv_transaction_id,
1053 final_match_flag,
1054 assets_tracking_flag,
1055 asset_book_type_code,
1056 asset_category_id,
1057 project_id,
1058 task_id,
1059 expenditure_type,
1060 expenditure_item_date,
1061 expenditure_organization_id,
1062 pa_quantity,
1063 pa_cc_ar_invoice_id,
1064 pa_cc_ar_invoice_line_num ,
1065 pa_cc_processed_code,
1066 award_id,
1067 awt_group_id,
1068 reference_1,
1069 reference_2,
1070 receipt_verified_flag,
1071 receipt_required_flag,
1072 receipt_missing_flag,
1073 justification,
1074 expense_group,
1075 start_expense_date,
1076 end_expense_date,
1077 receipt_currency_code,
1078 receipt_conversion_rate,
1079 receipt_currency_amount,
1080 daily_amount,
1081 web_parameter_id,
1082 adjustment_reason,
1083 merchant_document_number,
1084 merchant_name,
1085 merchant_reference,
1086 merchant_tax_reg_number,
1087 merchant_taxpayer_id,
1088 country_of_supply,
1089 credit_card_trx_id,
1090 company_prepaid_invoice_id,
1091 cc_reversal_flag,
1092 creation_date,
1093 created_by,
1094 last_updated_by,
1095 last_update_date,
1096 last_update_login,
1097 program_application_id,
1098 program_id,
1099 program_update_date,
1100 request_id,
1101 attribute_category,
1102 attribute1,
1103 attribute2,
1104 attribute3,
1105 attribute4,
1106 attribute5,
1107 attribute6,
1108 attribute7,
1109 attribute8,
1110 attribute9,
1111 attribute10,
1112 attribute11,
1113 attribute12,
1114 attribute13,
1115 attribute14,
1116 attribute15,
1117 global_attribute_category,
1118 global_attribute1,
1119 global_attribute2,
1120 global_attribute3,
1121 global_attribute4,
1122 global_attribute5,
1123 global_attribute6,
1124 global_attribute7,
1125 global_attribute8,
1126 global_attribute9,
1127 global_attribute10,
1128 global_attribute11,
1129 global_attribute12,
1130 global_attribute13,
1131 global_attribute14,
1132 global_attribute15,
1133 global_attribute16,
1134 global_attribute17,
1135 global_attribute18,
1136 global_attribute19,
1137 global_attribute20,
1138 --ETAX: Invwkb
1139 included_tax_amount,
1140 primary_intended_use,
1141 --Bugfix:4673607
1142 application_id,
1143 product_table,
1144 reference_key1,
1145 reference_key2,
1146 reference_key3,
1147 reference_key4,
1148 reference_key5,
1149 --bugfix:4674194
1150 ship_to_location_id,
1151 --bugfix:7022001
1152 pay_awt_group_id
1153 FROM AP_INVOICE_LINES
1154 WHERE invoice_id = X_invoice_id
1155 AND line_number = X_line_number;
1156
1157
1158 --ETAX: Invwkb
1159 CURSOR Invoice_Rec IS
1160 SELECT *
1161 FROM AP_INVOICES
1162 WHERE invoice_id = x_invoice_id;
1163
1164 l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
1165 l_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
1166 l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
1167
1168 l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
1169
1170 l_dist_set_percent_number NUMBER := 0;
1171 l_dist_set_description AP_DISTRIBUTION_SETS.DESCRIPTION%TYPE;
1172 l_dist_set_attribute_category AP_DISTRIBUTION_SETS.ATTRIBUTE_CATEGORY%TYPE;
1173 l_dist_set_attribute1 AP_DISTRIBUTION_SETS.ATTRIBUTE1%TYPE;
1174 l_dist_set_attribute2 AP_DISTRIBUTION_SETS.ATTRIBUTE2%TYPE;
1175 l_dist_set_attribute3 AP_DISTRIBUTION_SETS.ATTRIBUTE3%TYPE;
1176 l_dist_set_attribute4 AP_DISTRIBUTION_SETS.ATTRIBUTE4%TYPE;
1177 l_dist_set_attribute5 AP_DISTRIBUTION_SETS.ATTRIBUTE5%TYPE;
1178 l_dist_set_attribute6 AP_DISTRIBUTION_SETS.ATTRIBUTE6%TYPE;
1179 l_dist_set_attribute7 AP_DISTRIBUTION_SETS.ATTRIBUTE7%TYPE;
1180 l_dist_set_attribute8 AP_DISTRIBUTION_SETS.ATTRIBUTE8%TYPE;
1181 l_dist_set_attribute9 AP_DISTRIBUTION_SETS.ATTRIBUTE9%TYPE;
1182 l_dist_set_attribute10 AP_DISTRIBUTION_SETS.ATTRIBUTE10%TYPE;
1183 l_dist_set_attribute11 AP_DISTRIBUTION_SETS.ATTRIBUTE11%TYPE;
1184 l_dist_set_attribute12 AP_DISTRIBUTION_SETS.ATTRIBUTE12%TYPE;
1185 l_dist_set_attribute13 AP_DISTRIBUTION_SETS.ATTRIBUTE13%TYPE;
1186 l_dist_set_attribute14 AP_DISTRIBUTION_SETS.ATTRIBUTE14%TYPE;
1187 l_dist_set_attribute15 AP_DISTRIBUTION_SETS.ATTRIBUTE15%TYPE;
1188 l_inactive_date AP_DISTRIBUTION_SETS.INACTIVE_DATE%TYPE;
1189 l_batch_id AP_INVOICES.BATCH_ID%TYPE;
1190 l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
1191 l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
1192 l_invoice_date AP_INVOICES.INVOICE_DATE%TYPE;
1193 l_exchange_rate AP_INVOICES.EXCHANGE_RATE%TYPE;
1194 l_exchange_date AP_INVOICES.EXCHANGE_DATE%TYPE;
1195 l_exchange_rate_type AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE;
1196 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
1197 l_msg_application VARCHAR2(25);
1198 l_msg_count NUMBER;
1199 l_msg_data VARCHAR2(30);
1200 l_error_code VARCHAR2(30);
1201
1202 y_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
1203
1204 current_calling_sequence VARCHAR2(1000);
1205 debug_info VARCHAR2(2000);
1206 debug_context VARCHAR2(1000);
1207 l_error_found VARCHAR2(1);
1208
1209 BEGIN
1210 -- Update the calling sequence
1211 --
1212 current_calling_sequence := 'AP_INVOICE_LINES_PKG.insert_from_dist_set<-'
1213 ||X_calling_sequence;
1214 --------------------------------------------------------------
1215 -- Step 1 - Validate that distribution set was passed or
1216 -- get it from the line IF line already exists.
1217 -- If line already exists, THEN use this chance to read other
1218 -- Line related data we will need (performance).
1219 -- If a distribution set is not found, or IF inconsistent
1220 -- information was provided THEN exit the FUNCTION and
1221 -- RETURN false.
1222 --------------------------------------------------------------
1223 debug_info := 'Verify distribution set information available';
1224 IF (X_line_number IS NULL) THEN
1225 debug_info := debug_info ||': No Line Info is provided';
1226 X_debug_context := current_calling_sequence;
1227 X_debug_info := debug_info;
1228 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1229 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1230 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1231 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1232 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1233 X_error_code := 'AP_DEBUG';
1234 RETURN(FALSE);
1235 ELSE
1236 BEGIN
1237 OPEN line_rec(x_line_number);
1238 FETCH line_rec INTO l_invoice_line_rec;
1239 IF (line_rec%NOTFOUND) THEN
1240 CLOSE line_rec;
1241 RAISE NO_DATA_FOUND;
1242 END IF;
1243 CLOSE line_Rec;
1244
1245 EXCEPTION
1246 WHEN no_data_found THEN
1247 debug_info := debug_info ||': No valid line record was found.';
1248 X_debug_context := current_calling_sequence;
1249 X_debug_info := debug_info;
1250 IF (SQLCODE <> -20001) THEN
1251 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1252 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1253 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1254 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1255 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1256 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1257 END IF;
1258 X_error_code := 'AP_DEBUG';
1259 RETURN(FALSE);
1260 END;
1261
1262 END IF; -- Line NUMBER is null
1263
1264 --------------------------------------------------------------
1265 -- Step 2 - Now that we have the distribution set, obtain
1266 -- information required for validation and defaulting.
1267 -- Also verify that the distribution set is not inactive.
1268 --------------------------------------------------------------
1269 debug_info := 'Get total percent for distribution set';
1270 BEGIN
1271 SELECT total_percent_distribution,
1272 description,
1273 attribute_category,
1274 attribute1,
1275 attribute2,
1276 attribute3,
1277 attribute4,
1278 attribute5,
1279 attribute6,
1280 attribute7,
1281 attribute8,
1282 attribute9,
1283 attribute10,
1284 attribute11,
1285 attribute12,
1286 attribute13,
1287 attribute14,
1288 attribute15,
1289 inactive_date
1290 INTO l_dist_set_percent_number,
1291 l_dist_set_description,
1292 l_dist_set_attribute_category,
1293 l_dist_set_attribute1,
1294 l_dist_set_attribute2,
1295 l_dist_set_attribute3,
1296 l_dist_set_attribute4,
1297 l_dist_set_attribute5,
1298 l_dist_set_attribute6,
1299 l_dist_set_attribute7,
1300 l_dist_set_attribute8,
1301 l_dist_set_attribute9,
1302 l_dist_set_attribute10,
1303 l_dist_set_attribute11,
1304 l_dist_set_attribute12,
1305 l_dist_set_attribute13,
1306 l_dist_set_attribute14,
1307 l_dist_set_attribute15,
1308 l_inactive_date
1309 FROM ap_distribution_sets
1310 WHERE distribution_set_id = l_invoice_line_rec.distribution_set_id;
1311
1312 IF (nvl(l_inactive_date, trunc(sysdate) + 1) <= trunc(sysdate)) THEN
1313 X_error_code := 'AP_VEN_DIST_SET_INVALID';
1314 RETURN(FALSE);
1315 END IF;
1316
1317 EXCEPTION
1318 WHEN NO_DATA_FOUND THEN
1319 Debug_info := debug_info || ': Cannot read Dist Set';
1320 X_debug_context := current_calling_sequence;
1321 X_debug_info := debug_info;
1322 IF (SQLCODE <> -20001) THEN
1323 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1324 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1325 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1326 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1327 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1328 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1329 END IF;
1330 X_error_code := 'AP_DEBUG';
1331 RETURN(FALSE);
1332 END;
1333
1334 --------------------------------------------------------------
1335 -- Step 3 - Validate that IF the calling module requested not
1336 -- to allow skeleton distribution sets e.g. when validation
1337 -- is calling to generate distributions i.e. user intervention
1338 -- won't be possible, THEN verify that the total percent for the
1339 -- distribution set is 100.
1340 -- For these known checks we RETURN specific error codes.
1341 --------------------------------------------------------------
1342
1343 /* Bug 4928285. There is no need for this check since we should be
1344 able to create distributions using skeleton distribution set
1345 IF (X_Skeleton_Allowed = 'N' AND
1346 l_dist_set_percent_number <> 100) then
1347 X_error_code := 'AP_CANT_USE_SKELETON_DIST_SET';
1348 RETURN(FALSE);
1349 END IF;
1350 */
1351
1352 --------------------------------------------------------------
1353 -- Step 4 - Obtain information from the invoice header that would
1354 -- be necessary to validate that generation of distributions
1355 -- is possible and to create the line IF one does not
1356 -- exist already.
1357 -----------------------------------------------------------------
1358 debug_info := 'Select header, vendor information and amount to distribute';
1359 BEGIN
1360 SELECT AI.batch_id,
1361 AI.vendor_id,
1362 AI.vendor_site_id,
1363 AI.invoice_date,
1364 AI.exchange_rate,
1365 AI.exchange_date,
1366 AI.exchange_rate_type,
1367 AI.invoice_currency_code,
1368 AI.set_of_books_id
1369 INTO l_batch_id,
1370 l_vendor_id,
1371 l_vendor_site_id,
1372 l_invoice_date,
1373 l_exchange_rate,
1374 l_exchange_date,
1375 l_exchange_rate_type,
1376 l_invoice_currency_code,
1377 l_set_of_books_id
1378 FROM ap_invoices AI
1379 WHERE invoice_id = X_invoice_id;
1380
1381 EXCEPTION
1382 When no_data_found then
1383 Debug_info := debug_info || ': cannot read invoice information';
1384 X_debug_context := current_calling_sequence;
1385 X_debug_info := debug_info;
1386 IF (SQLCODE <> -20001) THEN
1387 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1388 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1389 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1390 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1391 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1392 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1393 END IF;
1394 X_error_code := 'AP_DEBUG';
1395 RETURN(FALSE);
1396
1397 END;
1398
1399 --------------------------------------------------------------
1400 -- Step 5 - Get system level information necessary for
1401 -- validation and generation of lines.
1402 --------------------------------------------------------------
1403 debug_info := 'Get system information';
1404
1405 BEGIN
1406 -- get chart_of_accounts_id from ap_system_parameters
1407 SELECT gsob.chart_of_accounts_id,
1408 ap.base_currency_code
1409 INTO l_chart_of_accounts_id,
1410 l_base_currency_code
1411 FROM ap_system_parameters ap, gl_sets_of_books gsob
1412 WHERE ap.set_of_books_id = gsob.set_of_books_id
1413 AND ap.set_of_books_id = l_set_of_books_id
1414 AND ap.org_id = l_invoice_line_rec.org_id;
1415
1416 EXCEPTION
1417 WHEN no_data_found THEN
1418 Debug_info := debug_info || ': No GL information was found';
1419 X_debug_context := current_calling_sequence;
1420 X_debug_info := debug_info;
1421 IF (SQLCODE <> -20001) THEN
1422 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1423 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1424 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1425 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1426 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1427 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1428 END IF;
1429 X_error_code := 'AP_DEBUG';
1430 RETURN(FALSE);
1431 END;
1432
1433 --------------------------------------------------------------
1434 -- Step 6: Set the gl_date and period_name to the
1435 -- parameters, since they will be used by the FUNCTION to generate
1436 -- distributions.
1437 --------------------------------------------------------------
1438
1439 l_invoice_line_rec.accounting_date := X_GL_Date;
1440 l_invoice_line_rec.period_name := X_Period_Name;
1441
1442 --------------------------------------------------------------
1443 -- Step 9 - Validate distribution level information including
1444 -- accounting and project level information.
1445 -- The call RETURNs a plsql table of distributions to be inserted
1446 -- later. Note that IF this part fails, the entire trx should
1447 -- be rolled back to ensure roll back of the line creation.
1448 --------------------------------------------------------------
1449 debug_info := 'Calling generate_dist_tab_from_dist_set ';
1450 IF (NOT (Generate_Dist_Tab_For_Dist_Set(
1451 l_vendor_id, -- IN
1452 l_invoice_date, -- IN
1453 l_invoice_line_rec, -- IN
1454 null, -- IN
1455 y_dist_tab, -- IN
1456 l_dist_set_percent_number, -- IN
1457 l_exchange_rate, -- IN
1458 l_exchange_rate_type, -- IN
1459 l_exchange_date , -- IN
1460 l_invoice_currency_code, -- IN
1461 l_base_currency_code, -- IN
1462 l_chart_of_accounts_id, -- IN
1463 l_Error_Code, -- OUT NOCOPY VARCHAR2,
1464 Debug_Info, -- OUT NOCOPY VARCHAR2,
1465 Debug_Context, -- OUT NOCOPY VARCHAR2,
1466 l_msg_application, -- OUT NOCOPY VARCHAR2,
1467 l_msg_data, -- OUT NOCOPY VARCHAR2,
1468 current_calling_sequence -- IN VARCHAR2,
1469 ))) then
1470
1471
1472 IF (l_error_code IS NOT NULL) THEN
1473 X_error_code := l_error_code;
1474 RETURN (FALSE);
1475 ELSIF (l_msg_data IS NOT NULL) THEN
1476 X_msg_application := l_msg_application;
1477 X_msg_data := l_msg_data;
1478 RETURN(FALSE);
1479 ELSE
1480 X_debug_context := current_calling_sequence;
1481 X_debug_info := debug_info;
1482 RETURN (FALSE);
1483 END IF;
1484 END IF;
1485
1486
1487 --------------------------------------------------------------
1488 -- Step 11 - If the calling module requested generation of
1489 -- distributions call the appropriate FUNCTION to generate
1490 -- them.
1491 --------------------------------------------------------------
1492 IF (X_Generate_Dists = 'Y' ) then
1493
1494 debug_info := 'Calling AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set';
1495 IF ( NOT (AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set(
1496 l_batch_id,
1497 X_invoice_id,
1498 X_line_number,
1499 y_dist_tab,
1500 X_Generate_Permanent,
1501 -- l_error_code,
1502 debug_info,
1503 debug_context,
1504 current_calling_sequence))) then
1505
1506 IF (l_error_code IS NOT NULL) THEN
1507 X_error_code := l_error_code;
1508 RETURN (FALSE);
1509 ELSE
1510 X_debug_context := current_calling_sequence;
1511 X_debug_info := debug_info;
1512 RETURN (FALSE);
1513 END IF;
1514
1515 END IF;
1516
1517
1518 END IF; -- Generate dists
1519
1520 RETURN(TRUE);
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 IF (SQLCODE <> -20001) THEN
1525 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1526 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1527 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1528 current_calling_sequence);
1529 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1530 'X_GL_Date = ' ||TO_CHAR(X_GL_Date)
1531 ||', X_Period_Name = ' ||X_Period_Name
1532 ||', X_Invoice_Id = ' ||TO_CHAR(X_invoice_id)
1533 );
1534 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1535 END IF;
1536 APP_EXCEPTION.RAISE_EXCEPTION;
1537
1538 END insert_from_dist_set;
1539
1540 -----------------------------------------------------------------------
1541 -- FUNCTION get_max_dist_line_num RETURNs the maximum distribution line
1542 -- NUMBER given an invoice and line, it RETURNs 0 IF no distributions exist.
1543 ----------------------------------------------------------------------
1544 FUNCTION Get_Max_Dist_Line_Num(
1545 X_invoice_id IN NUMBER,
1546 X_line_number IN NUMBER) RETURN NUMBER
1547 IS
1548 l_max_dist_line_num NUMBER := 0;
1549 BEGIN
1550
1551 SELECT nvl(max(distribution_line_number),0)
1552 INTO l_max_dist_line_num
1553 FROM ap_invoice_distributions_all -- Bug 7195488 Moac synonym replaced
1554 WHERE invoice_id = X_invoice_id
1555 AND invoice_line_number = X_line_number;
1556
1557 RETURN(l_max_dist_line_num);
1558
1559 END get_max_dist_line_num;
1560
1561
1562 /*===========================================================================
1563 | FUNCTION - ROUND_BASE_AMTS
1564 |
1565 | DESCRIPTION
1566 | RETURNs the rounded base amount IF there is any. it RETURNs FALSE if
1567 | no rounding amount necessary, otherwise it RETURNs TRUE.
1568 |
1569 | Business Assumption
1570 | 1. Called after all the base amount of each line is populated
1571 | 2. Same exchange rate for all the lines
1572 | 3. It will be called by Primary ledger (AP) or Reporting ledger (MRC)
1573 |
1574 | PARAMETERS
1575 | X_Invoice_Id - Invoice Id
1576 | X_Line_Number - invoice line NUMBER
1577 | X_Reporting_Ledger_Id - For ALC/MRC use only.
1578 | X_ROUND_DIST_ID_LIST - distribution list that can be adjusted
1579 | X_Rounded_Amt - rounded amount
1580 | X_Debug_Info - debug information
1581 | X_Debug_Context - error context
1582 | X_Calling_Sequence - debug usage
1583 |
1584 | KNOWN ISSUES:
1585 |
1586 | NOTES:
1587 |
1588 | MODIFICATION HISTORY
1589 | Date Author Description of Change
1590 | 19-MAY-2008 KPASIKAN modified for the bug 6892789 to get the
1591 | dists that can be adjusted
1592 *===========================================================================*/
1593
1594
1595 FUNCTION round_base_amts(
1596 X_INVOICE_ID IN NUMBER,
1597 X_LINE_NUMBER IN NUMBER,
1598 X_REPORTING_LEDGER_ID IN NUMBER DEFAULT NULL,
1599 X_ROUND_DIST_ID_LIST OUT NOCOPY distribution_id_tab_type,
1600 X_ROUNDED_AMT OUT NOCOPY NUMBER,
1601 X_Debug_Info OUT NOCOPY VARCHAR2,
1602 X_Debug_Context OUT NOCOPY VARCHAR2,
1603 X_Calling_sequence IN VARCHAR2)
1604
1605 RETURN BOOLEAN IS
1606 l_rounded_amt NUMBER := 0;
1607 l_round_dist_id_list distribution_id_tab_type;
1608 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
1609 l_line_base_amount ap_invoice_lines.base_amount%TYPE;
1610 l_line_amount ap_invoice_lines.amount%TYPE;
1611 l_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
1612 l_reporting_currency_code ap_invoices.invoice_currency_code%TYPE;
1613 l_sum_base_amt NUMBER;
1614 l_sum_rpt_base_amt NUMBER;
1615
1616 current_calling_sequence VARCHAR2(2000);
1617 debug_info VARCHAR2(100);
1618
1619 cursor invoice_line_cursor is
1620 -- inv_line_base_amt/rpt_line_base_amt
1621 SELECT decode(x_reporting_ledger_id, null, AIL.base_amount, null),
1622 AIL.amount, -- line_amount
1623 AI.invoice_currency_code, -- invoice_currency_code
1624 ASP.base_currency_code -- base_currency_code
1625 FROM ap_invoices AI, ap_system_parameters ASP, ap_invoice_lines AIL
1626 WHERE AI.invoice_id = X_invoice_id
1627 AND AIL.invoice_id = AI.invoice_id
1628 AND AIL.line_number = X_line_number
1629 AND ASP.org_id = AI.org_id;
1630 BEGIN
1631
1632 current_calling_sequence := 'ROUND_BASE_AMTS - Round_Base_Amt for line';
1633
1634 OPEN invoice_line_cursor;
1635 FETCH invoice_line_cursor
1636 INTO l_line_base_amount,
1637 l_line_amount,
1638 l_invoice_currency_code,
1639 l_base_currency_code;
1640 IF (invoice_line_cursor%NOTFOUND) THEN
1641 RAISE NO_DATA_FOUND;
1642 CLOSE invoice_line_cursor;
1643 END IF;
1644 CLOSE invoice_line_cursor;
1645
1646 IF (X_Reporting_Ledger_Id IS NULL) THEN
1647 --------------------------------------------------------------------
1648 debug_info := 'Round_base_amt Case 1 - Rounding for primary ledger';
1649 --------------------------------------------------------------------
1650
1651 IF (l_invoice_currency_code <> l_base_currency_code) THEN
1652
1653 -- Bug 5469235: Added conditions to eliminate retainage
1654 --Bug 5555622 and recoupment, and the related tax distributions
1655 BEGIN
1656 SELECT SUM(base_amount)
1657 INTO l_sum_base_amt
1658 FROM ap_invoice_distributions AID
1659 WHERE AID.invoice_id = X_INVOICE_ID
1660 AND AID.invoice_line_number = X_LINE_NUMBER
1661 AND AID.line_type_lookup_code NOT IN ('RETAINAGE', 'PREPAY')
1662 AND AID.charge_applicable_to_dist_id NOT IN
1663 (SELECT AID1.invoice_distribution_id
1664 FROM ap_invoice_distributions AID1
1665 WHERE AID1.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')
1666 AND AID1.invoice_id = X_INVOICE_ID
1667 AND AID1.invoice_line_number = X_LINE_NUMBER);
1668
1669 END;
1670
1671 l_rounded_amt := l_line_base_amount - l_sum_base_amt;
1672 ELSE
1673 ---------------------------------------------------------------------
1674 debug_info := 'Round_Base_Amt - same inv currency/base currency';
1675 ---------------------------------------------------------------------
1676 X_ROUNDED_AMT := 0;
1677 X_ROUND_DIST_ID_LIST.delete;
1678 X_debug_context := current_calling_sequence;
1679 X_debug_info := debug_info;
1680 RETURN(FALSE);
1681 END IF; -- end of check currency for primary
1682
1683 ELSE
1684
1685 Null; -- Removed the code here due to MRC obsoletion.
1686
1687 END IF; -- end of check x_reporting_ledger_id
1688
1689 IF (l_rounded_amt <> 0) THEN
1690 ------------------------------------------------------------------------
1691 debug_info := 'Round_Base_Amt - round amt exists and find distribution';
1692 ------------------------------------------------------------------------
1693 BEGIN
1694 --bugfix:4625349
1695 SELECT invoice_distribution_id
1696 BULK COLLECT INTO l_round_dist_id_list
1697 FROM AP_INVOICE_DISTRIBUTIONS aid1
1698 WHERE aid1.invoice_id = X_INVOICE_ID
1699 AND aid1.invoice_line_number = X_LINE_NUMBER
1700 AND nvl(aid1.posted_flag, 'N') = 'N'
1701 AND NVL(aid1.match_status_flag, 'N') IN ('N', 'S')
1702 AND NVL(aid1.reversal_flag, 'N') = 'N' /* Bug 4121330 */
1703 ORDER BY aid1.base_amount desc;
1704 END;
1705
1706 X_ROUNDED_AMT := l_rounded_amt;
1707 x_round_dist_id_list := l_round_dist_id_list;
1708 X_debug_context := current_calling_sequence;
1709 X_debug_info := debug_info;
1710 RETURN(TRUE);
1711 ELSE
1712 ---------------------------------------------------------------------
1713 debug_info := 'Round_Base_Amt - round_amt is 0 for diff currency';
1714 ---------------------------------------------------------------------
1715
1716 X_ROUNDED_AMT := 0;
1717 x_round_dist_id_list.delete;
1718 X_debug_context := current_calling_sequence;
1719 X_debug_info := debug_info;
1720 RETURN(FALSE);
1721 END IF; -- end of check l_rounded_amt
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 IF (SQLCODE <> -20001) THEN
1726 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1727 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1728 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1729 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1730 'Invoice Id = ' || TO_CHAR(X_Invoice_Id));
1731 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1732 END IF;
1733 debug_info := debug_info || 'Error occurred';
1734 X_debug_context := current_calling_sequence;
1735 X_debug_info := debug_info;
1736 RETURN(FALSE);
1737 END round_base_amts;
1738
1739
1740 /*=============================================================================
1741 | public FUNCTION Discard_Inv_Line
1742 |
1743 | Discard or cancel the invoice line depending on calling mode. If error
1744 | occurs, it return FALSE and error code will be populated. Otherwise,
1745 | It return TRUE.
1746 |
1747 | Parameters
1748 | P_line_rec - Invoice line record
1749 | P_calling_mode - either from DISCARD, CANCEL or UNAPPLY_PREPAY
1750 | p_inv_cancellable - 'Y' if invoice is canellable.
1751 | P_last_updated_by
1752 | P_last_update_login
1753 | P_error_code - Error code indicates why it is not discardable
1754 | P_calling_sequence - For debugging purpose
1755 |
1756 | PROGRAM FLOW
1757 |
1758 | 1. check if line is discardable
1759 | 2. if line is discardable/cancellable and matched - reverse match
1760 | 3. reset the encumberance flag, create account event
1761 | 4. if there is an active distribution - reverse distribution
1762 | 5. populate the out message and set the return value
1763 |
1764 | NOTES
1765 |
1766 | MODIFICATION HISTORY
1767 | Date Author Description of Change
1768 | 03/07/03 sfeng Created
1769 |
1770 *============================================================================*/
1771
1772 Function Discard_Inv_Line(
1773 P_line_rec IN ap_invoice_lines%ROWTYPE,
1774 P_calling_mode IN VARCHAR2,
1775 P_inv_cancellable IN VARCHAR2 DEFAULT NULL,
1776 P_last_updated_by IN NUMBER,
1777 P_last_update_login IN NUMBER,
1778 P_error_code OUT NOCOPY VARCHAR2,
1779 P_token OUT NOCOPY VARCHAR2,
1780 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
1781 IS
1782 TYPE r_global_attr_arr IS VARRAY(1000) of VARCHAR2(150);
1783 l_ok_to_cancel BOOLEAN := FALSE;
1784 l_ok_to_discard BOOLEAN := FALSE;
1785 l_debug_info VARCHAR2(4000);
1786 l_curr_calling_sequence VARCHAR2(4000);
1787
1788 l_po_distribution_id
1789 ap_invoice_distributions.po_distribution_id%TYPE;
1790
1791 l_distribution_count NUMBER := 0;
1792 l_sum_matched_qty NUMBER := 0;
1793 l_sum_matched_amt NUMBER := 0;
1794 l_dist_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
1795 l_matching_basis po_line_locations_all.matching_basis%TYPE;
1796 i NUMBER := 1;
1797 l_max_line_num
1798 ap_invoice_distributions.distribution_line_number%TYPE;
1799 l_matched_uom VARCHAR2(30);
1800 l_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1801 l_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1802 l_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
1803 l_global_attr_category ap_invoice_distributions.global_attribute_category%TYPE;
1804 l_open_gl_date DATE;
1805 l_result_string VARCHAR2(4000);
1806 l_loop_counter BINARY_INTEGER;
1807 l_awt_success VARCHAR2(2000);
1808
1809 l_invoice_validation_status VARCHAR2(100);
1810 l_success BOOLEAN;
1811 l_error_code VARCHAR2(4000);
1812 l_invoice_type_lookup_code AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
1813 l_payment_status_flag AP_INVOICES_ALL.PAYMENT_STATUS_FLAG%TYPE;
1814 l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
1815 l_included_tax_amount AP_INVOICE_LINES_ALL.INCLUDED_TAX_AMOUNT%TYPE;
1816 l_tax_distribution_count NUMBER;
1817
1818 --Contract Payments
1819 l_prepay_dist_info AP_PREPAY_PKG.prepay_dist_tab_type;
1820 l_dummy BOOLEAN;
1821 l_prepay_invoice_id NUMBER;
1822 l_prepay_line_number NUMBER;
1823 l_shipment_amt_billed NUMBER;
1824 l_shipment_qty_billed NUMBER;
1825 l_shipment_amt_recouped NUMBER;
1826 l_shipment_qty_recouped NUMBER;
1827 l_shipment_amt_retained NUMBER;
1828 l_shipment_amt_released NUMBER;
1829 l_shipment_amt_financed NUMBER;
1830 l_shipment_qty_financed NUMBER;
1831 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
1832 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
1833 l_api_name VARCHAR2(50);
1834 l_return_status VARCHAR2(4000);
1835 l_msg_data VARCHAR2(4000);
1836 l_prepay_tax_dists_count NUMBER;
1837
1838 -------------------------------------------------------------
1839 -- Query trying to find the total qty_invoiced by this inv
1840 -- line for one or more po_distributions. Only the base match
1841 -- and the corrections trying to correct this invoice will
1842 -- be considered. For PRICE CORRECTION, quantity_invoiced
1843 -- should not be included. For QUANTITY CORRECTION,
1844 -- corrected_quantity is populated and quantity_invoiced is null.
1845 -- Although at invoice line level, quantity_invoiced is always
1846 -- the same as corrected_quantity.
1847 -------------------------------------------------------------
1848
1849 CURSOR po_dists_cur IS
1850 SELECT aid.po_distribution_id,
1851 aid.matched_uom_lookup_code,
1852 SUM( decode( AID.dist_match_type,
1853 'PRICE_CORRECTION', 0,
1854 'AMOUNT_CORRECTION', 0, /* Amount Based Matching */
1855 'ITEM_TO_SERVICE_PO', 0,
1856 'ITEM_TO_SERVICE_RECEIPT', 0,
1857 NVL( AID.corrected_quantity, 0) +
1858 nvl( AID.quantity_invoiced,0 ) ) ) ,
1859 SUM(NVL(AID.amount, 0)) ,
1860 aid.line_type_lookup_code,
1861 pll.matching_basis,
1862 aid1.invoice_id prepay_invoice_id,
1863 aid1.invoice_line_number prepay_line_number
1864 FROM ap_invoice_distributions_all AID ,
1865 po_line_locations pll,
1866 ap_invoice_distributions_all AID1
1867 WHERE aid.invoice_id = p_line_rec.invoice_id
1868 AND aid.invoice_line_number = p_line_rec.line_number
1869 --Contract Payments: Added the 'PREPAY' to the clause
1870 AND aid.line_type_lookup_code in ('ITEM','ACCRUAL', 'IPV','ERV','PREPAY','RETAINAGE')
1871 AND pll.line_location_id = p_line_rec.po_line_location_id
1872 AND aid1.invoice_distribution_id(+) = aid.prepay_distribution_id
1873 GROUP BY aid1.invoice_id,aid1.invoice_line_number,
1874 aid.line_type_lookup_code,aid.po_distribution_id,pll.matching_basis,aid.matched_uom_lookup_code;
1875
1876
1877 -- Bug 5114543
1878 -- Added to allow discard of invoice lines with allocated charges
1879
1880 CURSOR c_charge_lines(c_invoice_id number,
1881 c_item_line_number number) Is
1882 SELECT ail.*
1883 FROM ap_allocation_rule_lines arl
1884 ,ap_invoice_lines_all ail
1885 WHERE arl.invoice_id = c_invoice_id
1886 AND arl.to_invoice_line_number = c_item_line_number
1887 AND arl.invoice_id = ail.invoice_id
1888 AND arl.chrg_invoice_line_number = ail.line_number
1889 AND exists
1890 (select aid.invoice_line_number
1891 from ap_invoice_distributions_all aid
1892 where aid.invoice_id = ail.invoice_id
1893 and aid.invoice_line_number = ail.line_number);
1894
1895 l_chrg_line_rec ap_invoice_lines_all%rowtype;
1896
1897 -- Bug 5396138 Start
1898 cursor c_recouped_shipment IS
1899 select pll.line_location_id
1900 ,aid.matched_uom_lookup_code
1901 ,sum(nvl(aid.amount,0)) amount
1902 ,sum(decode(AID.dist_match_type,
1903 'PRICE_CORRECTION', 0,
1904 'AMOUNT_CORRECTION', 0,
1905 'ITEM_TO_SERVICE_PO', 0,
1906 'ITEM_TO_SERVICE_RECEIPT', 0,
1907 NVL(AID.corrected_quantity, 0) + NVL(AID.quantity_invoiced,0))) quantity
1908 from ap_invoice_lines_all ail
1909 ,ap_invoice_distributions_all aid
1910 ,po_distributions_all pod
1911 ,po_line_locations_all pll
1912 where ail.invoice_id = p_line_rec.invoice_id
1913 and ail.line_number = p_line_rec.line_number
1914 and ail.invoice_id = aid.invoice_id
1915 and ail.line_number = aid.invoice_line_number
1916 and ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
1917 and aid.line_type_lookup_code = 'PREPAY'
1918 and aid.po_distribution_id = pod.po_distribution_id
1919 and pll.line_location_id = pod.line_location_id
1920 group by pll.line_location_id, aid.matched_uom_lookup_code;
1921
1922 l_recoup_dist_rec PO_AP_DIST_REC_TYPE;
1923 l_recoup_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
1924
1925 l_recouped_shipment c_recouped_shipment%rowtype;
1926 -- Bug 5396138 End
1927
1928 -- bug 5572121
1929 cursor dist_debug_cur is
1930 Select *
1931 FROM ap_invoice_distributions_all aid
1932 WHERE aid.invoice_id = p_line_rec.invoice_id;
1933
1934 --bugfix:5638822
1935 l_recouped_amount ap_invoices_all.invoice_amount%type;
1936 l_amount_paid number;
1937 l_error_message varchar2(4000);
1938 l_payment_currency_code ap_invoices_all.payment_currency_code%TYPE;
1939 l_amount_remaining number;
1940
1941 --bugfix:5697764
1942 l_tax_line_number number;
1943 l_unapplied_tax_amount number;
1944 l_unapplied_tax_amt_pay_curr number;
1945 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type;
1946 l_payment_cross_rate_date ap_invoices_all.payment_cross_rate_date%type;
1947 l_payment_cross_rate_type ap_invoices_all.payment_cross_rate_type%type;
1948 l_prepay_included ap_invoice_lines_all.invoice_includes_prepay_flag%type;
1949
1950 BEGIN
1951
1952 l_shipment_amt_billed := 0;
1953 l_shipment_qty_billed := 0;
1954 l_shipment_amt_recouped := 0;
1955 l_shipment_qty_recouped := 0;
1956 l_shipment_amt_retained := 0;
1957 l_shipment_amt_released := 0;
1958 l_prepay_tax_dists_count := 0;
1959 l_shipment_amt_financed := 0;
1960 l_shipment_qty_financed := 0;
1961
1962 l_api_name := 'Discard_Inv_Line';
1963
1964 l_curr_calling_sequence := 'AP_INVOICE_LINES_PKG.Discard_Inv_Line <-' ||
1965 P_calling_sequence;
1966
1967 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1968 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_LINES_PKG.Discard_Inv_Line(+)');
1969 END IF;
1970
1971
1972 SELECT invoice_type_lookup_code,
1973 payment_status_flag,
1974 invoice_amount,
1975 payment_currency_code,
1976 invoice_currency_code,
1977 payment_cross_rate_date,
1978 payment_cross_rate_type
1979 INTO l_invoice_type_lookup_code,
1980 l_payment_status_flag,
1981 l_invoice_amount,
1982 l_payment_currency_code,
1983 l_invoice_currency_code,
1984 l_payment_cross_rate_date,
1985 l_payment_cross_rate_type
1986 FROM ap_invoices
1987 WHERE invoice_id = p_line_rec.invoice_id;
1988
1989 /*-----------------------------------------------------------------+
1990 | Step 0. If invoice line is AWT, undo withholding before |
1991 | any other discard operation |
1992 +-----------------------------------------------------------------*/
1993
1994 l_debug_info := 'Undo AWT if it is an AWT line';
1995
1996 IF ( NVL( P_calling_mode, 'DISCARD' ) = 'DISCARD' AND
1997 p_line_rec.line_type_lookup_code = 'AWT' ) THEN
1998
1999 -- One AWT dist corresponds to one Invoice Line.
2000 AP_WITHHOLDING_PKG.Ap_Undo_Withholding (
2001 P_Parent_Id => P_line_rec.invoice_id,
2002 P_Calling_Module => 'REVERSE DIST',
2003 P_Awt_Date => P_line_rec.accounting_date,
2004 P_New_Invoice_Payment_Id => NULL,
2005 P_Last_Updated_By => P_last_updated_by,
2006 P_Last_Update_Login => P_last_update_login,
2007 P_Program_Application_Id => NULL,
2008 P_Program_Id => NULL,
2009 P_Request_Id => NULL,
2010 P_Awt_Success => l_awt_success,
2011 P_Inv_Line_No => P_line_rec.line_number,
2012 P_dist_Line_No => NULL,
2013 P_New_Invoice_Id => NULL,
2014 P_New_dist_Line_No => NULL);
2015
2016 IF ( l_awt_success = 'SUCCESS' ) THEN
2017 RETURN(TRUE);
2018 ELSE
2019 p_error_code := l_awt_success;
2020 RETURN(FALSE);
2021 END IF;
2022
2023 END IF;
2024
2025 /*-----------------------------------------------------------------+
2026 | Check if the Line is Discardable or if the Invoice is |
2027 | is Cancelable. |
2028 +-----------------------------------------------------------------*/
2029
2030 IF ( NVL( P_calling_mode, 'DISCARD' ) IN ( 'DISCARD', 'UNAPPLY_PREPAY' ) ) THEN
2031 IF ( NVL(P_line_rec.discarded_flag, 'N') <> 'Y' ) THEN
2032 /* Base Line ARU Issue */
2033 l_ok_to_discard := AP_INVOICE_LINES_UTILITY_PKG.Is_Line_Discardable(
2034 P_line_rec,
2035 l_error_code,
2036 l_curr_calling_sequence );
2037
2038 IF ( l_ok_to_discard = FALSE ) THEN
2039 p_error_code := l_error_code;
2040 RETURN (FALSE);
2041 END IF;
2042 ELSE
2043
2044 l_ok_to_discard := FALSE;
2045 p_error_code := 'AP_LINE_ALREADY_DISCARDED';
2046 RETURN (FALSE);
2047
2048 END IF;
2049
2050 END IF;
2051
2052 IF ( P_calling_mode = 'CANCEL' ) THEN
2053 IF ( p_inv_cancellable is NOT NULL and
2054 p_inv_cancellable = 'Y' and
2055 NVL(p_line_rec.cancelled_flag, 'N') <> 'Y' ) THEN
2056 l_ok_to_cancel := TRUE;
2057
2058 ELSIF ( p_inv_cancellable is NULL ) THEN
2059 l_ok_to_cancel := AP_CANCEL_PKG.Is_Invoice_Cancellable(
2060 P_invoice_id => p_line_rec.invoice_id,
2061 P_error_code => l_error_code,
2062 P_debug_info => l_debug_info,
2063 P_calling_sequence => l_curr_calling_sequence );
2064
2065 IF ( l_ok_to_cancel = FALSE ) THEN
2066 p_error_code := l_error_code;
2067 RETURN (FALSE);
2068 END IF;
2069 ELSE
2070
2071 p_error_code := 'AP_INV_CANCELLED';
2072 l_ok_to_cancel := FALSE;
2073 RETURN(FALSE);
2074
2075 END IF;
2076
2077 END IF; -- end of check P_calling_mode
2078
2079 -- Bug 5114543 Start
2080 IF (l_ok_to_discard = TRUE) THEN
2081
2082 IF p_line_rec.line_type_lookup_code = 'ITEM' THEN
2083
2084 ----------------------------------------------------------------------------
2085 l_debug_info := 'Update allocation rule to pending on related charge lines';
2086 Print (l_api_name, l_debug_info);
2087 ----------------------------------------------------------------------------
2088
2089 update ap_allocation_rules ar
2090 set status = 'PENDING'
2091 where ar.invoice_id = p_line_rec.invoice_id
2092 and exists (select arl.chrg_invoice_line_number
2093 from ap_allocation_rule_lines arl
2094 where arl.invoice_id = p_line_rec.invoice_id
2095 and arl.to_invoice_line_number = p_line_rec.line_number
2096 and arl.chrg_invoice_line_number = ar.chrg_invoice_line_number);
2097
2098 ----------------------------------------------------------------------------
2099 l_debug_info := 'Reset generate distributions flag on related charge lines';
2100 Print (l_api_name, l_debug_info);
2101 ----------------------------------------------------------------------------
2102
2103 update ap_invoice_lines_all ail
2104 set generate_dists = 'Y'
2105 where ail.invoice_id = p_line_rec.invoice_id
2106 and exists (select arl.chrg_invoice_line_number
2107 from ap_allocation_rule_lines arl
2108 where arl.invoice_id = p_line_rec.invoice_id
2109 and arl.to_invoice_line_number = p_line_rec.line_number
2110 and arl.chrg_invoice_line_number = ail.line_number);
2111
2112 open c_charge_lines (p_line_rec.invoice_id,
2113 p_line_rec.line_number);
2114 loop
2115 fetch c_charge_lines
2116 into l_chrg_line_rec;
2117 exit when c_charge_lines%notfound;
2118
2119 ----------------------------------------------------------------------------
2120 l_debug_info := 'Reverse charge distributions';
2121 Print (l_api_name,l_debug_info);
2122 ----------------------------------------------------------------------------
2123
2124 if not ap_invoice_lines_pkg.reverse_charge_distributions
2125 (p_inv_line_rec => l_chrg_line_rec
2126 ,p_calling_mode => p_calling_mode
2127 ,x_error_code => l_error_code
2128 ,x_debug_info => l_debug_info
2129 ,p_calling_sequence => l_curr_calling_sequence) then
2130
2131 l_ok_to_discard := FALSE;
2132 p_error_code := 'AP_REV_CHRG_DIST_FAIL';
2133
2134 RETURN FALSE;
2135
2136 end if;
2137 end loop;
2138 close c_charge_lines;
2139
2140 ----------------------------------------------------------------------------
2141 l_debug_info := 'Delete allocation rule lines';
2142 Print (l_api_name,l_debug_info);
2143 ----------------------------------------------------------------------------
2144
2145 delete from ap_allocation_rule_lines
2146 where invoice_id = p_line_rec.invoice_id
2147 and to_invoice_line_number = p_line_rec.line_number;
2148
2149 END IF;
2150 END IF;
2151 -- Bug 5114543 End
2152
2153 IF ( l_ok_to_discard = TRUE OR
2154 l_ok_to_cancel = TRUE ) THEN
2155
2156
2157 /*--------------------------------------------------------------+
2158 | Step 1. Call the ETAX api to unfreeze the invoice, if the |
2159 | invoice is already validated. |
2160 |---------------------------------------------------------------*/
2161 --Need not call the UNFREEZE INVOICE when the calling mode = 'CANCE',
2162 --as the call is already done in CANCEL package before control comes here.
2163 --The reason the call being placed
2164 IF (l_ok_to_discard) THEN
2165
2166 l_invoice_validation_status := ap_invoices_pkg.get_approval_status(
2167 l_invoice_id => p_line_rec.invoice_id,
2168 l_invoice_amount => l_invoice_amount,
2169 l_payment_status_flag => l_payment_status_flag,
2170 l_invoice_type_lookup_code => l_invoice_type_lookup_code );
2171
2172 IF (NVL(l_invoice_validation_status,'NEVER APPROVED') IN
2173 ('APPROVED','AVAILABLE','UNPAID','FULL')) THEN
2174
2175 l_success := ap_etax_pkg.calling_etax(
2176 P_Invoice_id => p_line_rec.invoice_id,
2177 P_Calling_Mode => 'UNFREEZE INVOICE',
2178 P_All_Error_Messages => 'N',
2179 P_error_code => l_error_code,
2180 P_Calling_Sequence => l_curr_calling_sequence);
2181
2182 IF (not l_success) THEN
2183 p_error_code := 'AP_ETX_DISC_LINE_UNFRZ_FAIL';
2184 p_token := l_error_code;
2185 RETURN(FALSE);
2186 END IF;
2187
2188 END IF;
2189
2190 SELECT included_tax_amount
2191 INTO l_included_tax_amount
2192 FROM ap_invoice_lines
2193 WHERE invoice_id = p_line_rec.invoice_id
2194 AND line_number = p_line_rec.line_number;
2195
2196 END IF; /* if l_ok_to_discard = 'Y' */
2197
2198
2199 /*-----------------------------------------------------------------+
2200 | Step 2. Reverse Match if line is matched |
2201 | a. Reverse adjust po_distributions |
2202 +-----------------------------------------------------------------*/
2203
2204 l_debug_info := 'Reverse Match - Adjust po_distributions po_line_location_id is '||p_line_rec.po_line_location_id;
2205 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2206 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2207 END IF;
2208
2209 IF ( p_line_rec.po_Line_location_id is not null OR
2210 p_line_rec.rcv_transaction_id is not null ) AND
2211 p_calling_mode <> 'UNAPPLY_PREPAY' THEN
2212
2213
2214 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
2215 l_recoup_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
2216
2217 l_debug_info := 'Open Cursor Po_Dists_Cur';
2218 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2220 END IF;
2221
2222 OPEN po_dists_cur;
2223 LOOP
2224
2225 FETCH po_dists_cur
2226 INTO l_po_distribution_id,
2227 l_matched_uom,
2228 l_sum_matched_qty,
2229 l_sum_matched_amt,
2230 l_dist_type_lookup_code,
2231 l_matching_basis,
2232 l_prepay_invoice_id,
2233 l_prepay_line_number;
2234 EXIT WHEN po_dists_cur%NOTFOUND;
2235
2236 IF (l_dist_type_lookup_code IN ('ITEM','ACCRUAL','IPV','ERV')) THEN
2237
2238 l_debug_info := 'Update billed/financed data for po distributions';
2239 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2241 END IF;
2242
2243 --Bugfix:5578026
2244 IF (l_invoice_type_lookup_code <> 'PREPAYMENT') THEN
2245
2246 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2247 p_uom_code => l_matched_uom,
2248 p_quantity_billed => l_sum_matched_qty*(-1),
2249 p_amount_billed => l_sum_matched_amt*(-1),
2250 p_quantity_financed => NULL,
2251 p_amount_financed => NULL,
2252 p_quantity_recouped => NULL,
2253 p_amount_recouped => NULL,
2254 p_retainage_withheld_amt => NULL,
2255 p_retainage_released_amt => NULL);
2256
2257
2258 l_shipment_amt_billed := l_shipment_amt_billed + nvl(l_sum_matched_amt,0) * (-1);
2259 l_shipment_qty_billed := l_shipment_qty_billed + nvl(l_sum_matched_qty,0) * (-1);
2260
2261 ELSIF (l_invoice_type_lookup_code = 'PREPAYMENT') THEN
2262
2263 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2264 p_uom_code => l_matched_uom,
2265 p_quantity_billed => NULL,
2266 p_amount_billed => NULL,
2267 p_quantity_financed => l_sum_matched_qty*(-1),
2268 p_amount_financed => l_sum_matched_amt*(-1),
2269 p_quantity_recouped => NULL,
2270 p_amount_recouped => NULL,
2271 p_retainage_withheld_amt => NULL,
2272 p_retainage_released_amt => NULL);
2273
2274 l_shipment_amt_financed := l_shipment_amt_financed + nvl(l_sum_matched_amt,0)*(-1);
2275 l_shipment_qty_financed := l_shipment_qty_financed + nvl(l_sum_matched_qty,0)*(-1);
2276
2277
2278 END IF; /*Bugfix:5578026 */
2279
2280
2281 ELSIF (l_dist_type_lookup_code = 'PREPAY') THEN
2282
2283 l_debug_info := 'Populate recouped data for po distributions ';
2284 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2285 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2286 END IF;
2287
2288 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2289 p_uom_code => l_matched_uom,
2290 p_quantity_billed => NULL,
2291 p_amount_billed => NULL,
2292 p_quantity_financed => NULL,
2293 p_amount_financed => NULL,
2294 p_quantity_recouped => l_sum_matched_qty,
2295 p_amount_recouped => l_sum_matched_amt,
2296 p_retainage_withheld_amt => NULL,
2297 p_retainage_released_amt => NULL);
2298
2299 l_shipment_amt_recouped := l_shipment_amt_recouped + nvl(l_sum_matched_amt,0);
2300 l_shipment_qty_recouped := l_shipment_qty_recouped + nvl(l_sum_matched_qty,0);
2301
2302 -- This loop will update amount/quantity recouped at the distribution level.
2303 -- As, Recouped prepay distributions belong to a different shipment. Cursor
2304 -- c_recouped_shipment is used for shipment level updates outside the loop.
2305 -- For this reason, dummy distributions are populated in l_recoup_dist_rec
2306 -- so that the subsequent call to Update_Document_Ap_Values does not fail.
2307
2308 l_recoup_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2309 p_uom_code => NULL,
2310 p_quantity_billed => NULL,
2311 p_amount_billed => NULL,
2312 p_quantity_financed => NULL,
2313 p_amount_financed => NULL,
2314 p_quantity_recouped => NULL,
2315 p_amount_recouped => NULL,
2316 p_retainage_withheld_amt => NULL,
2317 p_retainage_released_amt => NULL);
2318
2319 ELSIF (l_dist_type_lookup_code = 'RETAINAGE') THEN
2320
2321 IF p_line_rec.line_type_lookup_code <> 'RETAINAGE RELEASE' THEN
2322
2323 l_debug_info := 'Populate retainage withheld data for po distributions: '||l_po_distribution_id||': '||l_sum_matched_amt;
2324 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2325 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2326 END IF;
2327
2328 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2329 p_uom_code => NULL,
2330 p_quantity_billed => NULL,
2331 p_amount_billed => NULL,
2332 p_quantity_financed => NULL,
2333 p_amount_financed => NULL,
2334 p_quantity_recouped => NULL,
2335 p_amount_recouped => NULL,
2336 p_retainage_withheld_amt => l_sum_matched_amt,
2337 p_retainage_released_amt => NULL);
2338
2339 l_shipment_amt_retained := l_shipment_amt_retained + nvl(l_sum_matched_amt,0);
2340
2341 ELSIF p_line_rec.line_type_lookup_code = 'RETAINAGE RELEASE' THEN
2342
2343 l_debug_info := 'Populate retainage released data for po distributions: '||l_sum_matched_amt;
2344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2345 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2346 END IF;
2347
2348 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
2349 p_uom_code => NULL,
2350 p_quantity_billed => NULL,
2351 p_amount_billed => NULL,
2352 p_quantity_financed => NULL,
2353 p_amount_financed => NULL,
2354 p_quantity_recouped => NULL,
2355 p_amount_recouped => NULL,
2356 p_retainage_withheld_amt => NULL,
2357 p_retainage_released_amt => l_sum_matched_amt * (-1));
2358
2359 l_shipment_amt_released := l_shipment_amt_released + nvl(l_sum_matched_amt,0) * (-1);
2360
2361 END IF;
2362 END IF;
2363
2364 END LOOP;
2365
2366 CLOSE PO_Dists_Cur;
2367
2368 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
2369 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2370 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2371 END IF;
2372
2373
2374 IF (l_shipment_amt_billed <> 0 OR l_shipment_qty_billed <> 0 OR
2375 l_shipment_amt_financed <> 0 OR l_shipment_qty_financed <> 0 OR --bugfix:5578026
2376 l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0 OR
2377 l_shipment_amt_retained <> 0 OR l_shipment_amt_released <> 0) THEN
2378
2379 l_debug_info := ' Call add_change to populate the billed data for po shipments';
2380 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2381 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2382 END IF;
2383
2384 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2385 p_po_line_location_id => p_line_rec.po_line_location_id,
2386 p_uom_code => l_matched_uom,
2387 p_quantity_billed => l_shipment_qty_billed,
2388 p_amount_billed => l_shipment_amt_billed,
2389 p_quantity_financed => l_shipment_qty_financed,
2390 p_amount_financed => l_shipment_amt_financed,
2391 p_quantity_recouped => NULL,
2392 p_amount_recouped => NULL,
2393 p_retainage_withheld_amt => l_shipment_amt_retained,
2394 p_retainage_released_amt => l_shipment_amt_released
2395 );
2396
2397 END IF;
2398
2399 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
2400 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2401 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2402 END IF;
2403
2404 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
2405 P_Api_Version => 1.0,
2406 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
2407 P_Dist_Changes_Rec => l_po_ap_dist_rec,
2408 X_Return_Status => l_return_status,
2409 X_Msg_Data => l_msg_data);
2410
2411 IF (l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0) THEN
2412
2413 OPEN c_recouped_shipment;
2414 LOOP
2415 FETCH c_recouped_shipment
2416 INTO l_recouped_shipment;
2417 EXIT WHEN c_recouped_shipment%NOTFOUND;
2418
2419 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '1: '||l_recouped_shipment.line_location_id);
2421 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '2: '||l_recouped_shipment.matched_uom_lookup_code);
2422 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '3: '||l_recouped_shipment.quantity);
2423 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '4: '||l_recouped_shipment.amount);
2424 END IF;
2425
2426 l_recoup_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2427 p_po_line_location_id => l_recouped_shipment.line_location_id,
2428 p_uom_code => l_recouped_shipment.matched_uom_lookup_code,
2429 p_quantity_billed => NULL,
2430 p_amount_billed => NULL,
2431 p_quantity_financed => NULL,
2432 p_amount_financed => NULL,
2433 p_quantity_recouped => l_recouped_shipment.quantity,
2434 p_amount_recouped => l_recouped_shipment.amount,
2435 p_retainage_withheld_amt => NULL,
2436 p_retainage_released_amt => NULL
2437 );
2438
2439 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
2440 P_Api_Version => 1.0,
2441 P_Line_Loc_Changes_Rec => l_recoup_line_loc_rec,
2442 P_Dist_Changes_Rec => l_recoup_dist_rec,
2443 X_Return_Status => l_return_status,
2444 X_Msg_Data => l_msg_data);
2445
2446 END LOOP;
2447 CLOSE c_recouped_shipment;
2448 END IF;
2449
2450 /*-----------------------------------------------------------------+
2451 | Step 1. Reverse Match if line is matched |
2452 | c. Reverse adjust rcv_transaction |
2453 +-----------------------------------------------------------------*/
2454 /* Bug 5351931. was <>, modified to 'IS NOT NULL' */
2455
2456 --bugfix:5638822, add the AND clause below as when we are cancelling a invoice which had
2457 --TAX related to a receipt matched line (rcv_transaction_id is not null on TAX related to
2458 --receipt matched line) we were reducing the qty/amt_billed with tax amount too.
2459
2460 IF ( p_line_rec.rcv_transaction_id IS NOT NULL AND p_line_rec.line_type_lookup_code <> 'TAX' ) THEN
2461 l_debug_info := 'Reverse Match - Adject rcv_transactions ';
2462
2463 l_sum_matched_qty := 0;
2464 /* Amount Based Matching */
2465 IF ( p_line_rec.match_type IN ('AMOUNT_CORRECTION', 'PRICE_CORRECTION',
2466 'ITEM_TO_SERVICE_PO', 'ITEM_TO_SERVICE_RECEIPT') ) THEN
2467 l_sum_matched_qty := 0;
2468 ELSE
2469 l_sum_matched_qty := NVL(p_line_rec.quantity_invoiced, 0 );
2470
2471 END IF;
2472
2473 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
2474 p_line_rec.rcv_transaction_id ,
2475 l_sum_matched_qty *(-1),
2476 p_line_rec.unit_meas_lookup_code,
2477 NVL(p_line_rec.amount, 0) * (-1));
2478
2479 END IF; -- end of l_rcv_transaction_id check
2480
2481 END IF; -- end of l_po_line_location_id/l_rcv_transaction_id check
2482
2483 /*-------------------------------------------------------------------+
2484 | Step 1a. Update retained_amount_remaining on the original invoice |
2485 +--------------------------------------------------------------------*/
2486 IF p_line_rec.line_type_lookup_code = 'RETAINAGE RELEASE' AND
2487 p_line_rec.retained_invoice_id IS NOT NULL AND
2488 p_line_rec.retained_line_number IS NOT NULL THEN
2489
2490 UPDATE ap_invoice_lines_all
2491 SET retained_amount_remaining = nvl(retained_amount_remaining, 0) + p_line_rec.amount
2492 WHERE invoice_id = p_line_rec.retained_invoice_id
2493 AND line_number = p_line_rec.retained_line_number;
2494
2495 END IF;
2496
2497
2498 /*------------------------------------------------------------------
2499 --bugfix:5638822
2500 --Update amount_paid on the invoice , if this line had recouped_amount
2501 -- on it.
2502 -------------------------------------------------------------------*/
2503 l_debug_info := 'Update amount_paid on the invoice if the line had recouped amount';
2504 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2505 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2506 END IF;
2507
2508 l_recouped_amount := AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount(p_line_rec.Invoice_Id,p_line_rec.Line_Number);
2509
2510 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2511 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Recouped Amount: '||l_recouped_amount);
2512 END IF;
2513
2514 UPDATE ap_invoices
2515 SET amount_paid = nvl(amount_paid,0) - abs(l_recouped_amount) ,
2516 payment_status_flag =
2517 AP_INVOICES_UTILITY_PKG.get_payment_status(p_line_rec.invoice_id ),
2518 last_update_date = SYSDATE,
2519 last_updated_by = fnd_global.user_id,
2520 last_update_login = p_line_rec.last_update_login
2521 WHERE invoice_id = p_line_rec.invoice_id;
2522
2523
2524 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule(
2525 p_invoice_id => p_line_rec.invoice_id,
2526 p_prepay_invoice_id => NULL,
2527 p_prepay_line_num => NULL,
2528 p_apply_amount => l_recouped_amount,
2529 p_appl_type => 'UNAPPLICATION',
2530 p_payment_currency_code => l_payment_currency_code,
2531 p_user_id => FND_GLOBAL.user_id,
2532 p_last_update_login => p_line_rec.last_update_login,
2533 p_calling_sequence => p_calling_sequence,
2534 p_calling_mode => 'RECOUPMENT',
2535 p_error_message => l_error_message);
2536
2537
2538 /*-----------------------------------------------------------------+
2539 | Step 2. Zero out line level data and MRC data maintainence |
2540 +-----------------------------------------------------------------*/
2541 UPDATE ap_invoice_lines
2542 SET original_amount = amount
2543 ,original_base_amount = base_amount
2544 ,original_rounding_amt = rounding_amt
2545 ,amount = 0
2546 ,base_amount = 0
2547 ,rounding_amt = 0
2548 ,retained_amount = 0
2549 ,retained_amount_remaining = 0
2550 ,included_tax_amount = 0
2551 ,discarded_flag = decode( p_calling_mode, 'DISCARD', 'Y', 'UNAPPLY_PREPAY','Y',NULL )
2552 -- Bug 6669048. The cancelled_flag will be updated in the cancel API
2553 -- ,cancelled_flag = decode( p_calling_mode, 'CANCEL', 'Y', NULL )
2554 ,generate_dists = decode( generate_dists, 'Y',
2555 'N', generate_dists)
2556 WHERE invoice_id = p_line_rec.invoice_id
2557 AND line_number = p_line_rec.line_number;
2558
2559
2560 /*-----------------------------------------------------------------+
2561 | Step 4. Reverse Distribution |
2562 | a. Check if there is a valid distribution exists |
2563 +-----------------------------------------------------------------*/
2564
2565 l_debug_info := 'Check if there is a valid distribuition for reversal';
2566
2567 --Contract Payments: Modified the below WHERE clause to get the TAX related to
2568 --Prepay distributions , but not the TAX related to ITEM dists, since DETERMINE_RECOVERY
2569 --will take care of the TAX on ITEM dists.
2570 SELECT count(*)
2571 INTO l_distribution_count
2572 FROM ap_invoice_distributions_all
2573 WHERE invoice_id = p_line_rec.invoice_id
2574 AND invoice_line_number = p_line_rec.line_number
2575 AND ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV') and
2576 prepay_distribution_id IS NULL) OR
2577 (prepay_distribution_id IS NOT NULL)
2578 )
2579 AND NVL(reversal_flag, 'N') <> 'Y';
2580
2581 IF ( l_distribution_count <> 0 ) THEN
2582
2583 l_debug_info := 'Get the maximum distribution line number';
2584 l_max_line_num := AP_INVOICE_LINES_PKG.get_max_dist_line_num(
2585 p_line_rec.invoice_id,
2586 p_line_rec.line_number);
2587
2588 /*-----------------------------------------------------------------+
2589 | Step 4. Reverse Distribution |
2590 | b. Set encumbered flag to R only for unapproved dist |
2591 | which encumbered flag is in 'N', 'H','P' |
2592 | to prevent encumberance again when runing approval |
2593 | the second time |
2594 +-----------------------------------------------------------------*/
2595 l_debug_info := 'Set encumbered flag to N for unapproved dist.';
2596
2597 UPDATE ap_invoice_distributions
2598 SET encumbered_flag = 'R'
2599 WHERE invoice_id = p_line_rec.invoice_id
2600 AND invoice_line_number = p_line_rec.line_number
2601 AND match_status_flag <> 'A'
2602 AND encumbered_flag IN ('N','H','P');
2603
2604
2605 -- Bug fix 4748638
2606 /*-----------------------------------------------------------------+
2607 | Step 4. Reverse Distribution |
2608 | c. Create cancellation accounting event when primary |
2609 | accounting metod is Accrual basis. |
2610 | Note - It has been decided that a single invoice cancellation |
2611 | event will be created per invoice for the uptake of SLA.|
2612 | The invoice cancellation event and distributions will |
2613 | use the Invoice Header GL date as was used in 11i. |
2614 | Note 2 - to fix bug 4748638, if we finally goes with the |
2615 | option that create single cancel event. we need to |
2616 | move the event creation to cancel package |
2617 +-----------------------------------------------------------------*/
2618 /*
2619 IF (p_calling_mode = 'CANCEL' ) THEN
2620
2621 --Bug 4352723 - Added the following select to get Invoice Header
2622 --GL date to be used for Invoice cancellation event and dists
2623 SELECT gl_date
2624 INTO l_open_gl_date
2625 FROM AP_INVOICES
2626 WHERE invoice_id = P_Line_Rec.invoice_id;
2627
2628
2629 AP_ACCOUNTING_EVENTS_PKG.Create_Events (
2630 'INVOICE CANCELLATION'
2631 ,NULL -- p_doc_type
2632 ,p_line_rec.invoice_id
2633 ,l_open_gl_date
2634 ,l_Accounting_event_ID
2635 ,NULL -- checkrun_name
2636 ,P_calling_sequence);
2637
2638 END IF; -- Events Project - 2 - end
2639
2640 */
2641 /*-----------------------------------------------------------------+
2642 | Step 4. Reverse Distribution |
2643 | d.Insert reversal lines |
2644 +-----------------------------------------------------------------*/
2645 l_debug_info := 'Insert distribution reversals for existing lines';
2646
2647 --Contract Payments: Modified the WHERE and HAVING clause to take into
2648 --consideration 'Prepay' and its related Tax distributions.
2649
2650 -- Removed the below HAVING clause, for the 7376114, to allow zero amount ITEM lines to be reversed.
2651
2652 INSERT INTO ap_invoice_distributions_all(
2653 invoice_id,
2654 invoice_line_number,
2655 dist_code_combination_id,
2656 invoice_distribution_id,
2657 last_update_date,
2658 last_updated_by,
2659 accounting_date,
2660 period_name,
2661 set_of_books_id,
2662 amount,
2663 description,
2664 type_1099,
2665 tax_code_id,
2666 posted_flag,
2667 batch_id,
2668 quantity_invoiced,
2669 corrected_quantity,
2670 unit_price,
2671 match_status_flag,
2672 attribute_category,
2673 attribute1,
2674 attribute2,
2675 attribute3,
2676 attribute4,
2677 attribute5,
2678 prepay_amount_remaining,
2679 prepay_distribution_id,
2680 assets_addition_flag,
2681 assets_tracking_flag,
2682 distribution_line_number,
2683 line_type_lookup_code,
2684 po_distribution_id,
2685 base_amount,
2686 pa_addition_flag,
2687 encumbered_flag,
2688 accrual_posted_flag,
2689 cash_posted_flag,
2690 last_update_login,
2691 creation_date,
2692 created_by,
2693 stat_amount,
2694 attribute11,
2695 attribute12,
2696 attribute13,
2697 attribute14,
2698 attribute6,
2699 attribute7,
2700 attribute8,
2701 attribute9,
2702 attribute10,
2703 attribute15,
2704 reversal_flag,
2705 parent_invoice_id,
2706 income_tax_region,
2707 final_match_flag,
2708 expenditure_item_date,
2709 expenditure_organization_id,
2710 expenditure_type,
2711 pa_quantity,
2712 project_id,
2713 task_id,
2714 quantity_variance,
2715 base_quantity_variance,
2716 awt_flag,
2717 awt_group_id,
2718 awt_tax_rate_id,
2719 awt_gross_amount,
2720 reference_1,
2721 reference_2,
2722 other_invoice_id,
2723 awt_invoice_id,
2724 awt_origin_group_id,
2725 program_application_id,
2726 program_id,
2727 program_update_date,
2728 request_id,
2729 tax_recoverable_flag,
2730 award_id,
2731 start_expense_date,
2732 merchant_document_number,
2733 merchant_name,
2734 merchant_tax_reg_number,
2735 merchant_taxpayer_id,
2736 country_of_supply,
2737 merchant_reference,
2738 parent_reversal_id,
2739 rcv_transaction_id,
2740 dist_match_type,
2741 matched_uom_lookup_code,
2742 global_attribute_category,
2743 global_attribute1,
2744 global_attribute2,
2745 global_attribute3,
2746 global_attribute4,
2747 global_attribute5,
2748 global_attribute6,
2749 global_attribute7,
2750 global_attribute8,
2751 global_attribute9,
2752 global_attribute10,
2753 global_attribute11,
2754 global_attribute12,
2755 global_attribute13,
2756 global_attribute14,
2757 global_attribute15,
2758 global_attribute16,
2759 global_attribute17,
2760 global_attribute18,
2761 global_attribute19,
2762 global_attribute20,
2763 receipt_verified_flag,
2764 receipt_required_flag,
2765 receipt_missing_flag,
2766 justification,
2767 expense_Group,
2768 end_Expense_Date,
2769 receipt_Currency_Code,
2770 receipt_Conversion_Rate,
2771 receipt_Currency_Amount,
2772 daily_Amount,
2773 web_Parameter_Id,
2774 adjustment_Reason,
2775 credit_Card_Trx_Id,
2776 company_Prepaid_Invoice_Id,
2777 org_id,
2778 rounding_amt,
2779 charge_applicable_to_dist_id,
2780 corrected_invoice_dist_id,
2781 related_id,
2782 asset_book_type_code,
2783 asset_category_id,
2784 accounting_event_id,
2785 cancellation_flag,
2786 distribution_class,
2787 intended_use,
2788 --Freight and Special Charges
2789 rcv_charge_addition_flag)
2790 (SELECT
2791 Invoice_Id, -- invoice_id
2792 Invoice_Line_Number, -- invoice_line_number
2793 Dist_Code_Combination_Id, -- dist_code_combination_id
2794 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
2795 sysdate, -- last_update_date
2796 p_Last_Updated_By, -- last_updated_by
2797 /* Bug 5584997, Getting the accounting_date from line rec */
2798 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.accounting_date,
2799 ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)),
2800 -- accounting_date
2801 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.period_name,
2802 ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)),
2803 -- period_name,
2804 Set_Of_Books_Id, -- set_of_book_id
2805 -1 * Amount, -- amount
2806 Description, -- description
2807 Type_1099, -- type_1099
2808 Tax_Code_Id, -- tax_code_id
2809 'N', -- posted_flag,
2810 Batch_Id, -- batch_id
2811 DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
2812 -- quantity_invoiced
2813 DECODE(corrected_quantity, NULL, '',
2814 DECODE(dist_match_type, 'PRICE_CORRECTION',
2815 corrected_quantity, (-1)*corrected_quantity) ),
2816 -- corrected_quanity
2817 DECODE(unit_price, NULL,'',
2818 DECODE(dist_match_type, 'PRICE_CORRECTION',
2819 (-1)*unit_price, unit_price) ),
2820 -- unit_price,
2821 'N', -- match_status_flag
2822 attribute_category, -- attribute_category
2823 attribute1, -- attribute1
2824 attribute2, -- attribute2
2825 attribute3, -- attribute3
2826 attribute4, -- attribute4
2827 attribute5, -- attribute5
2828 NULL, -- prepay_amount_remaining
2829 prepay_distribution_id, -- prepay_distribution_id
2830 'U', -- assets_addition_flag
2831 Assets_Tracking_Flag, -- assets_tracking_flag
2832 Distribution_Line_Number + l_max_line_num , -- distribution_line_number
2833 Line_Type_Lookup_Code, -- line_type_lookup_code
2834 Po_Distribution_Id, -- po_distribution_id
2835 -1 * Base_Amount, -- base_amount
2836 DECODE(Pa_Addition_Flag, 'E', 'E', 'N'), -- pa_addition_flag
2837 DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
2838 'N', -- accrual_posted_flag,
2839 'N', -- cash_posted_flag,
2840 p_Last_Update_Login, -- last_update_login
2841 sysdate, -- creation_date,
2842 FND_GLOBAL.user_id, -- created_by,
2843 -1 * Stat_Amount, -- stat_amount
2844 attribute11, -- attribute11,
2845 attribute12, -- attribute12,
2846 attribute13, -- attribute13,
2847 attribute14, -- attribute14,
2848 attribute6, -- attribute6,
2849 attribute7, -- attribute7,
2850 attribute8, -- attribute8,
2851 attribute9, -- attribute9,
2852 attribute10, -- attribute10,
2853 attribute15, -- attribute15,
2854 'Y', -- reversal_flag,
2855 Parent_Invoice_Id, -- parent_invoice_id
2856 Income_Tax_Region, -- income_tax_region
2857 Final_Match_Flag, -- final_match_flag
2858 Expenditure_Item_Date, -- expenditure_item_date
2859 Expenditure_Organization_Id, -- expenditure_orgnization_id
2860 Expenditure_Type, -- expenditure_type
2861 -1 * Pa_Quantity, -- pa_quantity
2862 Project_Id, -- project_id
2863 Task_Id, -- task_id
2864 -1 * Quantity_Variance, -- quantity_variance
2865 -1 * Base_Quantity_Variance, -- base quantity_variance
2866 awt_flag, -- awt_flag
2867 awt_group_id, -- awt_group_id,
2868 awt_tax_rate_id, -- awt_tax_rate_id
2869 awt_gross_amount, -- awt_gross_amount
2870 reference_1, -- reference_1
2871 reference_2, -- reference_2
2872 other_invoice_id, -- other_invoice_id
2873 awt_invoice_id, -- awt_invoice_id
2874 awt_origin_group_id, -- awt_origin_group_id
2875 FND_GLOBAL.prog_appl_id, -- program_application_id
2876 FND_GLOBAL.conc_program_id, -- program_id
2877 SYSDATE, -- program_update_date,
2878 FND_GLOBAL.conc_request_id, -- request_id
2879 tax_recoverable_flag, -- tax_recoverable_flag
2880 award_id, -- award_id
2881 start_expense_date, -- start_expense_date
2882 merchant_document_number, -- merchant_document_number
2883 merchant_name, -- merchant_name
2884 merchant_tax_reg_number, -- merchant_tax_reg_number
2885 merchant_taxpayer_id, -- merchant_taxpayer_id
2886 country_of_supply, -- country_of_supply
2887 merchant_reference, -- merchant_reference
2888 invoice_distribution_id, -- Parent_Reversal_Id
2889 rcv_transaction_id, -- rcv_transaction_id
2890 dist_match_type, -- dist_match_type
2891 matched_uom_lookup_code, -- matched_uom_lookup_code
2892 global_attribute_category, -- global_attribute_category
2893 global_attribute1, -- global_attribute1
2894 global_attribute2, -- global_attribute2
2895 global_attribute3, -- global_attribute3
2896 global_attribute4, -- global_attribute4
2897 global_attribute5, -- global_attribute5
2898 global_attribute6, -- global_attribute6
2899 global_attribute7, -- global_attribute7
2900 global_attribute8, -- global_attribute8
2901 global_attribute9, -- global_attribute9
2902 global_attribute10, -- global_attribute10
2903 global_attribute11, -- global_attribute11
2904 global_attribute12, -- global_attribute12
2905 global_attribute13, -- global_attribute13
2906 global_attribute14, -- global_attribute14
2907 global_attribute15, -- global_attribute15
2908 global_attribute16, -- global_attribute16
2909 global_attribute17, -- global_attribute17
2910 global_attribute18, -- global_attribute18
2911 global_attribute19, -- global_attribute19
2912 global_attribute20, -- global_attribute20
2913 receipt_verified_flag, -- receipt_verified_flag
2914 receipt_required_flag, -- receipt_required_flag
2915 receipt_missing_flag, -- receipt_missing_flag
2916 justification, -- justification
2917 expense_Group, -- expense_Group
2918 end_Expense_Date, -- end_Expense_Date
2919 receipt_Currency_Code, -- receipt_Currency_Code
2920 receipt_Conversion_Rate, -- receipt_Conversion_Rate
2921 receipt_Currency_Amount, -- receipt_Currency_Amount
2922 daily_Amount, -- daily_Amount
2923 web_Parameter_Id, -- web_Parameter_Id
2924 adjustment_Reason, -- adjustment_Reason
2925 credit_Card_Trx_Id, -- credit_Card_Trx_Id
2926 company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
2927 org_id, -- MOAC project org_id
2928 -1* rounding_amt, -- rounding_amt
2929 charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
2930 corrected_invoice_dist_id, -- corrected_invoice_dist_id
2931 DECODE( related_id, NULL, NULL,
2932 invoice_distribution_id,
2933 ap_invoice_distributions_s.CURRVAL,
2934 --bugfix:4921399
2935 NULL ), -- related_id
2936 asset_book_type_code, -- asset_book_type_code
2937 asset_category_id, -- asset_category_id
2938 NULL, -- accounting_event_id
2939 decode(p_calling_mode, 'CANCEL', 'Y', NULL), -- cancellation_flag
2940 'PERMANENT',
2941 intended_use, -- intended_use
2942 'N' -- rcv_charge_addition_flag
2943 FROM ap_invoice_distributions_all
2944 WHERE invoice_id = p_line_rec.invoice_id
2945 AND invoice_line_number = p_line_rec.line_number
2946 AND line_type_lookup_code NOT IN
2947 ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
2948 AND (line_type_lookup_code <> 'AWT' OR
2949 (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
2950 AND dist_code_combination_id
2951 IN (SELECT dist_code_combination_id
2952 FROM ap_invoice_distributions_all
2953 WHERE invoice_id = p_line_rec.invoice_id
2954 AND invoice_line_number = p_line_rec.line_number
2955 GROUP BY dist_code_combination_id,
2956 po_distribution_id,
2957 line_type_lookup_code,
2958 prepay_distribution_id,
2959 assets_tracking_flag,
2960 type_1099,
2961 project_id,
2962 task_id,
2963 expenditure_organization_id,
2964 expenditure_type,
2965 expenditure_item_date,
2966 pa_addition_flag,
2967 awt_group_id,
2968 rcv_transaction_id) -- Bug 4159731
2969 AND nvl(po_distribution_id,-99) IN
2970 (SELECT
2971 NVL(po_distribution_id, -99)
2972 FROM ap_invoice_distributions_all
2973 WHERE invoice_id = p_line_rec.invoice_id
2974 AND invoice_line_number = p_line_rec.line_number
2975 GROUP BY dist_code_combination_id,
2976 po_distribution_id,
2977 line_type_lookup_code,
2978 prepay_distribution_id,
2979 assets_tracking_flag,
2980 type_1099,
2981 project_id,
2982 task_id,
2983 expenditure_organization_id,
2984 expenditure_type,
2985 expenditure_item_date,
2986 pa_addition_flag,
2987 awt_group_id,
2988 rcv_transaction_id, -- Bug 4159731
2989 tax_code_id) -- Bug 5191117
2990 ) ;
2991
2992
2993 --bugfix:4921399
2994 UPDATE ap_invoice_distributions aid
2995 SET aid.related_id =
2996 (SELECT invoice_distribution_id
2997 FROM ap_invoice_distributions aid1
2998 WHERE aid1.invoice_id = aid.invoice_id
2999 AND aid1.invoice_line_number = aid.invoice_line_number
3000 AND aid1.parent_reversal_id =
3001 (SELECT related_id
3002 FROM ap_invoice_distributions aid2
3003 WHERE aid2.invoice_id = aid.invoice_id
3004 AND aid2.invoice_line_number = aid.invoice_line_number
3005 AND aid2.invoice_distribution_id = aid.parent_reversal_id)
3006 )
3007 WHERE aid.related_id IS NULL
3008 AND aid.parent_reversal_id IS NOT NULL
3009 AND aid.invoice_id = p_line_rec.invoice_id
3010 AND aid.invoice_line_number = p_line_rec.line_number
3011 AND aid.reversal_flag = 'Y';
3012
3013 /*-----------------------------------------------------------------+
3014 | Step 4. Reverse Distribution |
3015 | e.Calling JE package - comment out for now |
3016 +-----------------------------------------------------------------*/
3017 l_debug_info := 'Calling JE Hungarian Inv Distribution Reversal';
3018
3019 /*JE_HU_INV_DIST_REVERSAL.Nullify_Global_attributes
3020 (P_INVOICE_ID => p_line_rec.invoice_id,
3021 P_DIST_MAX_LINE_NUM => l_max_line_num); */
3022
3023 -- Call GMS
3024
3025 l_debug_info := 'Call Create Prepay ADL';
3026
3027 IF (p_calling_mode = 'UNAPPLY_PREPAY') THEN
3028
3029 --bugfix:4542556
3030 SELECT invoice_distribution_id,prepay_distribution_id
3031 BULK COLLECT INTO l_key_value_list,l_key_value_list3
3032 FROM ap_invoice_distributions
3033 WHERE invoice_id = p_line_rec.invoice_id
3034 AND invoice_line_number = p_line_rec.line_number
3035 AND line_type_lookup_code = 'PREPAY'
3036 AND nvl(reversal_flag,'N') = 'Y'
3037 AND parent_reversal_id IS NOT NULL;
3038
3039 FOR l_loop_counter IN NVL(l_key_value_list.FIRST,0) .. NVL(l_key_value_list.LAST,0) LOOP
3040 l_debug_info := 'Update global context code';
3041
3042 --l_global_attr_category := l_key_value_list3(l_loop_counter);
3043 IF (AP_EXTENDED_WITHHOLDING_PKG.Ap_Extended_Withholding_Active) THEN
3044 AP_EXTENDED_WITHHOLDING_PKG.Ap_Ext_Withholding_Prepay (
3045 p_prepay_dist_id => l_key_value_list3(l_loop_counter),
3046 p_invoice_id => p_line_rec.invoice_id,
3047 p_inv_dist_id => l_key_value_list(l_loop_counter),
3048 p_user_id => fnd_global.user_id,
3049 p_last_update_login => p_last_update_login,
3050 p_calling_sequence => p_calling_sequence );
3051 END IF;
3052
3053 END LOOP;
3054
3055 END IF;
3056
3057 /*-----------------------------------------------------------------+
3058 | Step 4. Reverse Distribution |
3059 | g.Set reversal flag to existing distributions |
3060 | for this cancelled invoice |
3061 +-----------------------------------------------------------------*/
3062
3063 l_debug_info := 'Set reversal_flag to Y for existing distributions';
3064
3065 UPDATE ap_invoice_distributions
3066 SET reversal_flag = 'Y'
3067 WHERE invoice_id = p_line_rec.invoice_id
3068 AND invoice_line_number = p_line_rec.line_number;
3069
3070 END IF; -- end of l_distribution_count check
3071
3072 /*-------------------------------------------------------------------+
3073 | Step 5. Call ETAX: Discard Tax Distributions |
3074 +-------------------------------------------------------------------*/
3075
3076 IF (nvl(p_calling_mode, 'DISCARD') <> 'CANCEL') THEN
3077
3078 IF (nvl(p_calling_mode, 'DISCARD') = 'DISCARD') THEN
3079
3080 l_success := ap_etax_pkg.calling_etax(
3081 P_Invoice_id => p_line_rec.invoice_id,
3082 P_Line_Number => p_line_rec.line_number,
3083 P_Calling_Mode => 'DISCARD LINE',
3084 P_All_Error_Messages => 'N',
3085 P_error_code => l_error_code,
3086 P_Calling_Sequence => l_curr_calling_sequence);
3087
3088 IF (NOT l_success) THEN
3089 p_error_code := 'AP_ETX_DISC_LINE_CALC_TAX_FAIL';
3090 p_token := l_error_code;
3091 RETURN FALSE;
3092 END IF;
3093
3094 --bugfix:5697764
3095 ELSIF p_calling_mode = 'UNAPPLY_PREPAY' THEN
3096
3097 l_success := ap_etax_pkg.calling_etax(
3098 P_Invoice_id => p_line_rec.invoice_id,
3099 P_Line_Number => p_line_rec.line_number,
3100 P_Calling_Mode => 'UNAPPLY PREPAY',
3101 P_All_Error_Messages => 'N',
3102 P_error_code => l_error_code,
3103 P_Calling_Sequence => l_curr_calling_sequence);
3104
3105 IF (NOT l_success) THEN
3106 p_error_code := 'AP_ETX_DISC_LINE_CALC_TAX_FAIL';
3107 p_token := l_error_code;
3108 RETURN FALSE;
3109 END IF;
3110
3111 END IF;
3112
3113 END IF;
3114
3115
3116 --bugfix:5697764 start
3117 l_debug_info := 'p_calling_mode is '||p_calling_mode;
3118 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3119 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3120 END IF;
3121
3122 --bugfix:5765073 added the begin/end and exception handler
3123 BEGIN
3124 IF (nvl(p_calling_mode,'DISCARD') = 'UNAPPLY_PREPAY') THEN
3125
3126 SELECT aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N')
3127 INTO l_tax_line_number,l_prepay_included
3128 FROM ap_invoice_distributions aid, --tax dists
3129 ap_invoice_lines ail1, --item line
3130 ap_invoice_distributions aid1 --item distributions
3131 WHERE ail1.invoice_id = aid1.invoice_id
3132 AND ail1.invoice_id = p_line_rec.invoice_id
3133 AND ail1.line_number = p_line_rec.line_number
3134 AND aid.invoice_id = aid1.invoice_id
3135 AND ail1.line_number = aid1.invoice_line_number
3136 -- bug 7376110
3137 -- The below condition added to handle prepayment with inclusive tax.
3138 AND aid.invoice_line_number <> aid1.invoice_line_number
3139 AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
3140 GROUP BY aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N');
3141
3142 l_debug_info := 'l_tax_line_number is '||l_tax_line_number;
3143 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3144 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3145 END IF;
3146
3147 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
3148 l_prepay_dist_info,
3149 NULL,
3150 NULL,
3151 p_line_rec.invoice_id,
3152 l_tax_line_number,
3153 'UNAPPLICATION',
3154 NULL,
3155 l_curr_calling_sequence,
3156 l_error_code);
3157
3158 IF l_dummy = FALSE THEN
3159 RETURN (FALSE);
3160 END IF;
3161
3162 --Update the payment schedules with the unapplied tax amount
3163 IF NVL(l_prepay_included, 'N') = 'N' THEN
3164
3165 --Bug 7526679
3166 -- The query is modified to select the total amount of latest reversed tax distributions.
3167 SELECT sum(aid1.amount)
3168 INTO l_unapplied_tax_amount
3169 FROM ap_invoice_distributions_all aid1,
3170 ap_invoice_distributions_all aid2
3171 WHERE aid1.invoice_id = p_line_rec.invoice_id
3172 AND aid1.invoice_id = aid2.invoice_id
3173 AND aid1.invoice_line_number = l_tax_line_number
3174 AND aid2.invoice_line_number = p_line_rec.line_number
3175 AND NVL(aid1.reversal_flag,'N') = 'Y'
3176 AND NVL(aid2.reversal_flag,'N') = 'Y'
3177 AND aid1.parent_reversal_id IS NOT NULL
3178 AND aid2.parent_reversal_id IS NOT NULL
3179 AND aid1.charge_applicable_to_dist_id = aid2.invoice_distribution_id;
3180 --end of Bug 7526679
3181
3182 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
3183 l_unapplied_tax_amt_pay_curr := GL_Currency_API.Convert_Amount
3184 (l_invoice_currency_code,
3185 l_payment_currency_code,
3186 l_payment_cross_rate_date,
3187 l_payment_cross_rate_type,
3188 (-1)* l_unapplied_tax_amount);
3189 ELSE
3190 l_unapplied_tax_amt_pay_curr := (-1) * l_unapplied_tax_amount;
3191 END IF;
3192
3193 l_debug_info := 'Update the payment schedule with the unapplied exclusive prepay tax amount';
3194
3195 l_debug_info := 'l_unapplied_tax_amt_pay_curr,l_unapplied_tax_amount '||
3196 l_unapplied_tax_amt_pay_curr||','||l_unapplied_tax_amount;
3197 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3198 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3199 END IF;
3200
3201 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule (
3202 p_line_rec.invoice_id,
3203 NULL,
3204 NULL,
3205 l_unapplied_tax_amt_pay_curr,
3206 'UNAPPLICATION',
3207 l_payment_currency_code,
3208 FND_GLOBAL.user_id,
3209 p_last_update_login,
3210 p_calling_sequence,
3211 NULL,
3212 l_error_message);
3213
3214 IF l_dummy = FALSE THEN
3215 RETURN (FALSE) ;
3216 END IF;
3217
3218 END IF; /*l_prepay_included... */
3219
3220 END IF; /*p_calling_mode = 'UNAPPLY PREPAY'...*/
3221
3222 EXCEPTION WHEN OTHERS THEN
3223 NULL;
3224
3225 END ;
3226 --bugfix:5697764 end
3227
3228
3229 l_debug_info := 'p_line_rec.invoice_id,p_line_rec.line_number : '
3230 ||p_line_rec.invoice_id||','||p_line_rec.line_number;
3231 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3233 END IF;
3234
3235 IF (l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0) THEN
3236
3237 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
3238 l_prepay_dist_info,
3239 NULL,
3240 NULL,
3241 p_line_rec.invoice_id,
3242 p_line_rec.line_number,
3243 'UNAPPLICATION',
3244 NULL,
3245 l_curr_calling_sequence,
3246 l_error_code);
3247
3248 IF l_dummy = FALSE THEN
3249 RETURN (FALSE);
3250 END IF;
3251
3252 END IF;
3253
3254 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3255 l_debug_info := 'After Discard line the distribution look like:';
3256 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name,
3257 l_debug_info);
3258
3259 FOR l_inv_dist_rec IN dist_debug_cur
3260 LOOP
3261 l_debug_info :='they are '||
3262 'dist_type = ' || l_inv_dist_rec.line_type_lookup_code||
3263 'amount=' || l_inv_dist_rec.amount ||
3264 'base_amount =' || l_inv_dist_rec.base_amount ||
3265 'match_status_flag=' ||l_inv_dist_rec.match_status_flag ;
3266 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name,
3267 l_debug_info);
3268 END LOOP;
3269 END IF;
3270
3271 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3272 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3273 'AP_INVOICE_LINES_PKG.Discard_Inv_Line(-)');
3274 END IF;
3275
3276 p_error_code := NULL;
3277 RETURN (TRUE);
3278
3279 END IF; /* l_ok_to_discard OR l_ok_to_cancel */
3280
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 IF (SQLCODE <> -20001) THEN
3284 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3285 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3286 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
3287 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3288 ' P_invoice_id = ' || p_line_rec.invoice_id
3289 ||' P_line_number = ' || p_line_rec.line_number
3290 ||' P_last_updated_by = ' || P_last_updated_by
3291 ||' P_last_update_login = ' || P_last_update_login
3292 ||' P_calling_mode = ' || p_calling_mode);
3293 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3294 END IF;
3295
3296 IF ( po_dists_cur%ISOPEN ) THEN
3297 CLOSE po_dists_cur;
3298 END IF;
3299
3300 IF (c_charge_lines%isopen) THEN
3301 close c_charge_lines;
3302 END IF;
3303
3304 APP_EXCEPTION.RAISE_EXCEPTION;
3305
3306 END Discard_Inv_line;
3307
3308 FUNCTION Reverse_Charge_Distributions
3309 (p_inv_line_rec IN AP_INVOICE_LINES_ALL%rowtype,
3310 p_calling_mode IN VARCHAR2,
3311 x_error_code OUT NOCOPY VARCHAR2,
3312 x_debug_info OUT NOCOPY VARCHAR2,
3313 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
3314
3315 l_curr_calling_sequence VARCHAR2(2000);
3316 l_debug_info VARCHAR2(240);
3317 l_api_name VARCHAR2(50);
3318
3319 l_max_line_num ap_invoice_distributions.distribution_line_number%TYPE;
3320
3321 BEGIN
3322 l_api_name := 'Reverse_Charge_Distributions';
3323 ------------------------------------------------------
3324 Print (l_api_name,'Reverse_Charge_Distributions (+)');
3325 ------------------------------------------------------
3326 l_curr_calling_sequence := 'AP_INVOICE_LINES_PKG.Reverse_Charge_Distributions <-' || p_calling_sequence;
3327
3328
3329 l_max_line_num := AP_INVOICE_LINES_PKG.get_max_dist_line_num
3330 (p_inv_line_rec.invoice_id,
3331 p_inv_line_rec.line_number);
3332
3333 ------------------------------------------------------
3334 l_debug_info := 'Insert reverse charge distributions';
3335 Print (l_api_name,l_debug_info);
3336 ------------------------------------------------------
3337
3338 INSERT INTO ap_invoice_distributions_all(
3339 invoice_id,
3340 invoice_line_number,
3341 dist_code_combination_id,
3342 invoice_distribution_id,
3343 last_update_date,
3344 last_updated_by,
3345 accounting_date,
3346 period_name,
3347 set_of_books_id,
3348 amount,
3349 description,
3350 type_1099,
3351 tax_code_id,
3352 posted_flag,
3353 batch_id,
3354 quantity_invoiced,
3355 corrected_quantity,
3356 unit_price,
3357 match_status_flag,
3358 attribute_category,
3359 attribute1,
3360 attribute2,
3361 attribute3,
3362 attribute4,
3363 attribute5,
3364 prepay_amount_remaining,
3365 prepay_distribution_id,
3366 assets_addition_flag,
3367 assets_tracking_flag,
3368 distribution_line_number,
3369 line_type_lookup_code,
3370 po_distribution_id,
3371 base_amount,
3372 pa_addition_flag,
3373 encumbered_flag,
3374 accrual_posted_flag,
3375 cash_posted_flag,
3376 last_update_login,
3377 creation_date,
3378 created_by,
3379 stat_amount,
3380 attribute11,
3381 attribute12,
3382 attribute13,
3383 attribute14,
3384 attribute6,
3385 attribute7,
3386 attribute8,
3387 attribute9,
3388 attribute10,
3389 attribute15,
3390 reversal_flag,
3391 parent_invoice_id,
3392 income_tax_region,
3393 final_match_flag,
3394 expenditure_item_date,
3395 expenditure_organization_id,
3396 expenditure_type,
3397 pa_quantity,
3398 project_id,
3399 task_id,
3400 quantity_variance,
3401 base_quantity_variance,
3402 awt_flag,
3403 awt_group_id,
3404 awt_tax_rate_id,
3405 awt_gross_amount,
3406 reference_1,
3407 reference_2,
3408 other_invoice_id,
3409 awt_invoice_id,
3410 awt_origin_group_id,
3411 program_application_id,
3412 program_id,
3413 program_update_date,
3414 request_id,
3415 tax_recoverable_flag,
3416 award_id,
3417 start_expense_date,
3418 merchant_document_number,
3419 merchant_name,
3420 merchant_tax_reg_number,
3421 merchant_taxpayer_id,
3422 country_of_supply,
3423 merchant_reference,
3424 parent_reversal_id,
3425 rcv_transaction_id,
3426 dist_match_type,
3427 matched_uom_lookup_code,
3428 global_attribute_category,
3429 global_attribute1,
3430 global_attribute2,
3431 global_attribute3,
3432 global_attribute4,
3433 global_attribute5,
3434 global_attribute6,
3435 global_attribute7,
3436 global_attribute8,
3437 global_attribute9,
3438 global_attribute10,
3439 global_attribute11,
3440 global_attribute12,
3441 global_attribute13,
3442 global_attribute14,
3443 global_attribute15,
3444 global_attribute16,
3445 global_attribute17,
3446 global_attribute18,
3447 global_attribute19,
3448 global_attribute20,
3449 receipt_verified_flag,
3450 receipt_required_flag,
3451 receipt_missing_flag,
3452 justification,
3453 expense_Group,
3454 end_Expense_Date,
3455 receipt_Currency_Code,
3456 receipt_Conversion_Rate,
3457 receipt_Currency_Amount,
3458 daily_Amount,
3459 web_Parameter_Id,
3460 adjustment_Reason,
3461 credit_Card_Trx_Id,
3462 company_Prepaid_Invoice_Id,
3463 org_id,
3464 rounding_amt,
3465 charge_applicable_to_dist_id,
3466 corrected_invoice_dist_id,
3467 related_id,
3468 asset_book_type_code,
3469 asset_category_id,
3470 accounting_event_id,
3471 cancellation_flag,
3472 distribution_class,
3473 intended_use,
3474 --Freight and Special Charges
3475 rcv_charge_addition_flag)
3476 (SELECT
3477 Invoice_Id, -- invoice_id
3478 Invoice_Line_Number, -- invoice_line_number
3479 Dist_Code_Combination_Id, -- dist_code_combination_id
3480 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
3481 sysdate, -- last_update_date
3482 fnd_global.user_id, -- last_updated_by
3483 /* Bug 5584997, Getting the accounting_date from line rec */
3484 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.accounting_date,
3485 ap_utilities_pkg.get_reversal_gl_date(p_inv_line_rec.accounting_date, org_id)),
3486 -- accounting_date
3487 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.period_name,
3488 ap_utilities_pkg.get_reversal_period(p_inv_line_rec.accounting_date, org_id)),
3489 -- period_name,
3490 Set_Of_Books_Id, -- set_of_book_id
3491 -1 * Amount, -- amount
3492 Description, -- description
3493 Type_1099, -- type_1099
3494 Tax_Code_Id, -- tax_code_id
3495 'N', -- posted_flag,
3496 Batch_Id, -- batch_id
3497 DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
3498 -- quantity_invoiced
3499 DECODE(corrected_quantity, NULL, '',
3500 DECODE(dist_match_type, 'PRICE_CORRECTION',
3501 corrected_quantity, (-1)*corrected_quantity) ),
3502 -- corrected_quanity
3503 DECODE(unit_price, NULL,'',
3504 DECODE(dist_match_type, 'PRICE_CORRECTION',
3505 (-1)*unit_price, unit_price) ),
3506 -- unit_price,
3507 'N', -- match_status_flag
3508 attribute_category, -- attribute_category
3509 attribute1, -- attribute1
3510 attribute2, -- attribute2
3511 attribute3, -- attribute3
3512 attribute4, -- attribute4
3513 attribute5, -- attribute5
3514 NULL, -- prepay_amount_remaining
3515 prepay_distribution_id, -- prepay_distribution_id
3516 'U', -- assets_addition_flag
3517 Assets_Tracking_Flag, -- assets_tracking_flag
3518 Distribution_Line_Number + l_max_line_num , -- distribution_line_number
3519 Line_Type_Lookup_Code, -- line_type_lookup_code
3520 Po_Distribution_Id, -- po_distribution_id
3521 -1 * Base_Amount, -- base_amount
3522 DECODE(Pa_Addition_Flag, 'E', 'E', 'N'), -- pa_addition_flag
3523 DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
3524 'N', -- accrual_posted_flag,
3525 'N', -- cash_posted_flag,
3526 fnd_global.login_id, -- last_update_login
3527 sysdate, -- creation_date,
3528 FND_GLOBAL.user_id, -- created_by,
3529 -1 * Stat_Amount, -- stat_amount
3530 attribute11, -- attribute11,
3531 attribute12, -- attribute12,
3532 attribute13, -- attribute13,
3533 attribute14, -- attribute14,
3534 attribute6, -- attribute6,
3535 attribute7, -- attribute7,
3536 attribute8, -- attribute8,
3537 attribute9, -- attribute9,
3538 attribute10, -- attribute10,
3539 attribute15, -- attribute15,
3540 'Y', -- reversal_flag,
3541 Parent_Invoice_Id, -- parent_invoice_id
3542 Income_Tax_Region, -- income_tax_region
3543 Final_Match_Flag, -- final_match_flag
3544 Expenditure_Item_Date, -- expenditure_item_date
3545 Expenditure_Organization_Id, -- expenditure_orgnization_id
3546 Expenditure_Type, -- expenditure_type
3547 -1 * Pa_Quantity, -- pa_quantity
3548 Project_Id, -- project_id
3549 Task_Id, -- task_id
3550 -1 * Quantity_Variance, -- quantity_variance
3551 -1 * Base_Quantity_Variance, -- base quantity_variance
3552 awt_flag, -- awt_flag
3553 awt_group_id, -- awt_group_id,
3554 awt_tax_rate_id, -- awt_tax_rate_id
3555 awt_gross_amount, -- awt_gross_amount
3556 reference_1, -- reference_1
3557 reference_2, -- reference_2
3558 other_invoice_id, -- other_invoice_id
3559 awt_invoice_id, -- awt_invoice_id
3560 awt_origin_group_id, -- awt_origin_group_id
3561 FND_GLOBAL.prog_appl_id, -- program_application_id
3562 FND_GLOBAL.conc_program_id, -- program_id
3563 SYSDATE, -- program_update_date,
3564 FND_GLOBAL.conc_request_id, -- request_id
3565 tax_recoverable_flag, -- tax_recoverable_flag
3566 award_id, -- award_id
3567 start_expense_date, -- start_expense_date
3568 merchant_document_number, -- merchant_document_number
3569 merchant_name, -- merchant_name
3570 merchant_tax_reg_number, -- merchant_tax_reg_number
3571 merchant_taxpayer_id, -- merchant_taxpayer_id
3572 country_of_supply, -- country_of_supply
3573 merchant_reference, -- merchant_reference
3574 invoice_distribution_id, -- Parent_Reversal_Id
3575 rcv_transaction_id, -- rcv_transaction_id
3576 dist_match_type, -- dist_match_type
3577 matched_uom_lookup_code, -- matched_uom_lookup_code
3578 global_attribute_category, -- global_attribute_category
3579 global_attribute1, -- global_attribute1
3580 global_attribute2, -- global_attribute2
3581 global_attribute3, -- global_attribute3
3582 global_attribute4, -- global_attribute4
3583 global_attribute5, -- global_attribute5
3584 global_attribute6, -- global_attribute6
3585 global_attribute7, -- global_attribute7
3586 global_attribute8, -- global_attribute8
3587 global_attribute9, -- global_attribute9
3588 global_attribute10, -- global_attribute10
3589 global_attribute11, -- global_attribute11
3590 global_attribute12, -- global_attribute12
3591 global_attribute13, -- global_attribute13
3592 global_attribute14, -- global_attribute14
3593 global_attribute15, -- global_attribute15
3594 global_attribute16, -- global_attribute16
3595 global_attribute17, -- global_attribute17
3596 global_attribute18, -- global_attribute18
3597 global_attribute19, -- global_attribute19
3598 global_attribute20, -- global_attribute20
3599 receipt_verified_flag, -- receipt_verified_flag
3600 receipt_required_flag, -- receipt_required_flag
3601 receipt_missing_flag, -- receipt_missing_flag
3602 justification, -- justification
3603 expense_Group, -- expense_Group
3604 end_Expense_Date, -- end_Expense_Date
3605 receipt_Currency_Code, -- receipt_Currency_Code
3606 receipt_Conversion_Rate, -- receipt_Conversion_Rate
3607 receipt_Currency_Amount, -- receipt_Currency_Amount
3608 daily_Amount, -- daily_Amount
3609 web_Parameter_Id, -- web_Parameter_Id
3610 adjustment_Reason, -- adjustment_Reason
3611 credit_Card_Trx_Id, -- credit_Card_Trx_Id
3612 company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
3613 org_id, -- MOAC project org_id
3614 -1* rounding_amt, -- rounding_amt
3615 charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
3616 corrected_invoice_dist_id, -- corrected_invoice_dist_id
3617 DECODE( related_id, NULL, NULL,
3618 invoice_distribution_id,
3619 ap_invoice_distributions_s.CURRVAL,
3620 --bugfix:4921399
3621 NULL ), -- related_id
3622 asset_book_type_code, -- asset_book_type_code
3623 asset_category_id, -- asset_category_id
3624 NULL, -- accounting_event_id
3625 decode(p_calling_mode, 'CANCEL', 'Y', NULL), -- cancellation_flag
3626 'PERMANENT',
3627 intended_use, -- intended_use
3628 'N' -- rcv_charge_addition_flag
3629 FROM ap_invoice_distributions_all
3630 WHERE invoice_id = p_inv_line_rec.invoice_id
3631 AND invoice_line_number = p_inv_line_rec.line_number
3632 AND (reversal_flag is null
3633 or reversal_flag = 'N')
3634 AND (
3635 (line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
3636 and prepay_distribution_id IS NULL) OR
3637 prepay_distribution_id IS NOT NULL
3638 )
3639 AND (line_type_lookup_code <> 'AWT' OR
3640 (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
3641 ) ;
3642
3643 ---------------------------------------------------------------
3644 l_debug_info := 'Update reversal_flag on charge distributions';
3645 Print (l_api_name,l_debug_info);
3646 ---------------------------------------------------------------
3647
3648 UPDATE ap_invoice_distributions
3649 SET reversal_flag = 'Y'
3650 WHERE invoice_id = p_inv_line_rec.invoice_id
3651 AND invoice_line_number = p_inv_line_rec.line_number;
3652
3653 --------------------------------------------------------------
3654 Print(l_api_name,'Reverse_Charge_Distributions (-)');
3655 --------------------------------------------------------------
3656
3657 RETURN TRUE;
3658
3659 EXCEPTION
3660 WHEN OTHERS THEN
3661 IF (SQLCODE <> -20001) THEN
3662 FND_MESSAGE.SET_NAME ('SQLAP','AP_DEBUG');
3663 FND_MESSAGE.SET_TOKEN ('ERROR',SQLERRM);
3664 FND_MESSAGE.SET_TOKEN ('CALLING_SEQUENCE',l_curr_calling_sequence);
3665 FND_MESSAGE.SET_TOKEN ('PARAMETERS',
3666 ' P_invoice_id = '|| p_inv_line_rec.invoice_id
3667 ||' P_line_number = '|| p_inv_line_rec.line_number
3668 ||' P_calling_mode = '|| p_calling_mode);
3669 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3670 END IF;
3671
3672 x_debug_info := l_debug_info;
3673
3674 RETURN FALSE;
3675
3676 END Reverse_Charge_Distributions;
3677
3678 PROCEDURE Print (p_api_name IN VARCHAR2,
3679 p_debug_info IN VARCHAR2) IS
3680 BEGIN
3681 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3682 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||p_api_name,p_debug_info);
3683 END IF;
3684 END Print;
3685
3686 END AP_INVOICE_LINES_PKG;
3687