[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICE_LINES_PKG
Source
1 PACKAGE BODY AP_INVOICE_LINES_PKG as
2 /* $Header: apinlinb.pls 120.109.12020000.4 2012/11/23 12:03:45 cmaredup 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 --Bug14383132: Start
23 l_user_id ap_invoices_all.created_by%TYPE := FND_GLOBAL.user_id;
24 l_reverse_dist_count NUMBER := 0; --Bug14383132
25 l_sa_reverse_dist_count NUMBER:= 0; --Bug14383132
26
27 FUNCTION TAX_DISTRIBUTIONS_EXIST
28 (p_invoice_id IN NUMBER,
29 p_line_number IN NUMBER) RETURN BOOLEAN;
30
31 FUNCTION SELF_ASSESS_TAX_DIST_EXIST
32 (p_invoice_id IN NUMBER,
33 p_line_number IN NUMBER) RETURN BOOLEAN;
34 --Bug14383132: End
35
36 PROCEDURE Print (p_api_name IN VARCHAR2, p_debug_info IN VARCHAR2);
37
38 -----------------------------------------------------------------------
39 -- FUNCTION generate_dist_tab_for_dist_set validates distributions
40 -- to be created by a distribution set and generates a pl/sql table
41 -- of distributions to be inserted IF need be by the calling module.
42 -----------------------------------------------------------------------
43 FUNCTION Generate_Dist_Tab_For_Dist_Set(
44 X_vendor_id IN AP_INVOICES.VENDOR_ID%TYPE,
45 X_invoice_date IN AP_INVOICES.INVOICE_DATE%TYPE,
46 X_invoice_lines_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
47 X_line_source IN VARCHAR2,
48 X_dist_tab IN OUT NOCOPY AP_INVOICE_LINES_PKG.dist_tab_type,
49 X_dist_set_total_percent IN NUMBER,
50 X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
51 X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
52 X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
53 X_invoice_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
54 X_base_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
55 X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
56 X_Error_Code OUT NOCOPY VARCHAR2,
57 X_Debug_Info OUT NOCOPY VARCHAR2,
58 X_Debug_Context OUT NOCOPY VARCHAR2,
59 X_msg_application OUT NOCOPY VARCHAR2,
60 X_msg_data OUT NOCOPY VARCHAR2,
61 X_calling_sequence IN VARCHAR2) RETURN BOOLEAN
62 IS
63
64 CURSOR dist_set_lines_cur IS
65 SELECT ADSL.distribution_set_line_number,
66 ADSL.description,
67 ADSL.dist_code_combination_id,
68 GL.account_type,
69 ADSL.percent_distribution,
70 ADSL.project_id,
71 ADSL.task_id,
72 ADSL.expenditure_type,
73 ADSL.expenditure_organization_id,
74 ADSL.project_Accounting_context,
75 ADSL.award_id,
76 ADSL.attribute_category,
77 ADSL.attribute1,
78 ADSL.attribute2,
79 ADSL.attribute3,
80 ADSL.attribute4,
81 ADSL.attribute5,
82 ADSL.attribute6,
83 ADSL.attribute7,
84 ADSL.attribute8,
85 ADSL.attribute9,
86 ADSL.attribute10,
87 ADSL.attribute11,
88 ADSL.attribute12,
89 ADSL.attribute13,
90 ADSL.attribute14,
91 ADSL.attribute15,
92 ADSL.type_1099
93 FROM AP_Distribution_Set_Lines ADSL,
94 GL_Code_combinations GL
95 WHERE distribution_set_id = X_invoice_lines_rec.Distribution_Set_id
96 AND GL.code_combination_id = ADSL.dist_code_combination_id
97 ORDER BY distribution_set_line_number;
98
99
100 l_pa_allows_overrides VARCHAR2(1) := 'N';
101 l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
102 user_id NUMBER;
103 l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
104 l_dset_line_ccid
105 AP_DISTRIBUTION_SET_LINES.DIST_CODE_COMBINATION_ID%TYPE;
106 l_dset_dist_line_num
107 AP_DISTRIBUTION_SET_LINES.DISTRIBUTION_SET_LINE_NUMBER%TYPE;
108 l_dset_line_description AP_DISTRIBUTION_SET_LINES.DESCRIPTION%TYPE;
109 l_dset_line_type_1099 AP_DISTRIBUTION_SET_LINES.TYPE_1099%TYPE;
110 l_dset_line_project_id AP_DISTRIBUTION_SET_LINES.PROJECT_ID%TYPE;
111 l_dset_line_task_id AP_DISTRIBUTION_SET_LINES.TASK_ID%TYPE;
112 l_dset_line_expenditure_type AP_DISTRIBUTION_SET_LINES.EXPENDITURE_TYPE%TYPE;
113 l_dset_line_expenditure_org_id
114 AP_DISTRIBUTION_SET_LINES.EXPENDITURE_ORGANIZATION_ID%TYPE;
115 l_dset_line_proj_acct_context
116 AP_DISTRIBUTION_SET_LINES.PROJECT_ACCOUNTING_CONTEXT%TYPE;
117 l_dset_line_award_id AP_DISTRIBUTION_SET_LINES.AWARD_ID%TYPE;
118 l_award_id AP_DISTRIBUTION_SET_LINES.AWARD_ID%TYPE;
119 l_dset_line_percent_dist
120 AP_DISTRIBUTION_SET_LINES.PERCENT_DISTRIBUTION%TYPE;
121 l_dset_line_attribute_category
122 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE_CATEGORY%TYPE;
123 l_dset_line_attribute1 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE1%TYPE;
124 l_dset_line_attribute2 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE2%TYPE;
125 l_dset_line_attribute3 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE3%TYPE;
126 l_dset_line_attribute4 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE4%TYPE;
127 l_dset_line_attribute5 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE5%TYPE;
128 l_dset_line_attribute6 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE6%TYPE;
129 l_dset_line_attribute7 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE7%TYPE;
130 l_dset_line_attribute8 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE8%TYPE;
131 l_dset_line_attribute9 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE9%TYPE;
132 l_dset_line_attribute10 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE10%TYPE;
133 l_dset_line_attribute11 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE11%TYPE;
134 l_dset_line_attribute12 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE12%TYPE;
135 l_dset_line_attribute13 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE13%TYPE;
136 l_dset_line_attribute14 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE14%TYPE;
137 l_dset_line_attribute15 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE15%TYPE;
138 l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
139 l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140 l_dist_set_percent_number NUMBER;
141 l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142 l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
143 l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
144 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
145 l_status VARCHAR2(10);
146 l_industry VARCHAR2(10);
147 l_set_of_books_id AP_INVOICES.SET_OF_BOOKS_ID%TYPE;
148 l_pa_installed VARCHAR2(1);
149
150 l_max_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
151 l_max_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
152 l_max_i BINARY_INTEGER := 0;
153 l_max_pa_qty_i BINARY_INTEGER := 0;
154 i BINARY_INTEGER := 0;
155 l_msg_application VARCHAR2(25);
156 l_msg_type VARCHAR2(25);
157 l_msg_token1 VARCHAR2(30);
158 l_msg_token2 VARCHAR2(30);
159 l_msg_token3 VARCHAR2(30);
160 l_msg_count NUMBER;
161 l_msg_data VARCHAR2(30);
162 l_billable_flag VARCHAR2(25);
163 l_unbuilt_flex VARCHAR2(240):='';
164 l_reason_unbuilt_flex VARCHAR2(2000):='';
165 debug_context VARCHAR2(2000);
166 current_calling_sequence VARCHAR2(2000);
167 debug_info VARCHAR2(1000);
168 l_error_found VARCHAR2(1);
169 l_rounding_exists VARCHAR2(1) := 'N';
170 l_rounding_pa_qty_exists VARCHAR2(1) := 'N';
171 --For bug2938770
172 l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
173 l_prepay_dist_code_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
174 --bugfix:5725904
175 l_sys_link_function VARCHAR2(2);
176 l_message_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
177 l_api_name VARCHAR2(50);
178
179 l_line_base_amount NUMBER; --10182226
180 BEGIN
181
182 -- Update the calling sequence
183 --
184 current_calling_sequence :=
185 'AP_INVOICE_LINES_PKG.generate_dist_tab_for_dist_set<-'
186 ||X_calling_sequence;
187 l_api_name := 'Generate_Dist_Tab_For_Dist_Set';
188
189 --Bug 10182226: Populate line base amount.
190 if ( X_exchange_rate is not null and X_invoice_lines_rec.base_amount is null) then
191 l_line_base_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
192 NVL(X_invoice_lines_rec.amount, 0) * X_exchange_rate,
193 X_base_currency);
194
195 end if;
196 --End of Bug10182226
197 --------------------------------------------------------------
198 -- Step 1
199 -- Get type of distribution set IF not passed in as a parameter
200 --
201 --------------------------------------------------------------
202 IF (X_dist_Set_total_percent is NULL) then
203 BEGIN
204 SELECT total_percent_distribution
205 INTO l_dist_set_percent_number
206 FROM ap_distribution_sets
207 WHERE distribution_set_id = X_invoice_lines_rec.distribution_set_id;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 IF (SQLCODE <> -20001) THEN
211 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
212 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
213 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
214 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
215 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
216 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
217 END IF;
218 X_error_code := 'AP_DEBUG';
219 RETURN (FALSE);
220 END;
221 ELSE
222 l_dist_set_percent_number := X_dist_set_total_percent;
223 END IF;
224
225 --------------------------------------------------------------
226 -- Step 2 - Validate distribution level information including
227 -- accounting and project level information. This is done in
228 -- a loop that traverses all distribution lines defined in the
229 -- distribution set.
230 --------------------------------------------------------------
231 --bug 2938770 populating the local variables
232 SELECT nvl(PVS.prepay_code_combination_id,
233 SP.prepay_code_combination_id),
234 AI.invoice_type_lookup_code
235 INTO l_prepay_dist_code_ccid,
236 l_invoice_type_lookup_code
237 FROM ap_invoices AI,
238 po_vendor_sites PVS,
239 ap_system_parameters SP
240 WHERE AI.invoice_id = X_invoice_lines_rec.invoice_id
241 AND PVS.vendor_site_id = AI.vendor_site_id;
242
243 i := 0;
244 OPEN dist_set_lines_cur;
245 LOOP
246 FETCH dist_set_lines_cur
247 INTO l_dset_dist_line_num,
248 l_dset_line_description,
249 l_dset_line_ccid,
250 l_account_type,
251 l_dset_line_percent_dist,
252 l_dset_line_project_id,
253 l_dset_line_task_id,
254 l_dset_line_expenditure_type,
255 l_dset_line_expenditure_org_id,
256 l_dset_line_proj_acct_context,
257 l_dset_line_award_id,
258 l_dset_line_attribute_category,
259 l_dset_line_attribute1,
260 l_dset_line_attribute2,
261 l_dset_line_attribute3,
262 l_dset_line_attribute4,
263 l_dset_line_attribute5,
264 l_dset_line_attribute6,
265 l_dset_line_attribute7,
266 l_dset_line_attribute8,
267 l_dset_line_attribute9,
268 l_dset_line_attribute10,
269 l_dset_line_attribute11,
270 l_dset_line_attribute12,
271 l_dset_line_attribute13,
272 l_dset_line_attribute14,
273 l_dset_line_attribute15,
274 l_dset_line_type_1099;
275
276 EXIT WHEN dist_set_lines_cur%NOTFOUND;
277
278 X_dist_tab(i).dist_line_num := l_dset_dist_line_num;
279 X_dist_tab(i).description := nvl(l_dset_line_description,
280 X_invoice_lines_rec.description);
281 X_dist_tab(i).accounting_date := X_invoice_lines_rec.accounting_date;
282 X_dist_tab(i).period_name := X_invoice_lines_rec.period_name;
283 X_dist_tab(i).awt_group_id := X_invoice_lines_rec.awt_group_id;
284 X_dist_tab(i).attribute_category := l_dset_line_attribute_category;
285 X_dist_tab(i).attribute1 := l_dset_line_attribute1;
286 X_dist_tab(i).attribute2 := l_dset_line_attribute2;
287 X_dist_tab(i).attribute3 := l_dset_line_attribute3;
288 X_dist_tab(i).attribute4 := l_dset_line_attribute4;
289 X_dist_tab(i).attribute5 := l_dset_line_attribute5;
290 X_dist_tab(i).attribute6 := l_dset_line_attribute6;
291 X_dist_tab(i).attribute7 := l_dset_line_attribute7;
292 X_dist_tab(i).attribute8 := l_dset_line_attribute8;
293 X_dist_tab(i).attribute9 := l_dset_line_attribute9;
294 X_dist_tab(i).attribute10 := l_dset_line_attribute10;
295 X_dist_tab(i).attribute11 := l_dset_line_attribute11;
296 X_dist_tab(i).attribute12 := l_dset_line_attribute12;
297 X_dist_tab(i).attribute13 := l_dset_line_attribute13;
298 X_dist_tab(i).attribute14 := l_dset_line_attribute14;
299 X_dist_tab(i).attribute15 := l_dset_line_attribute15;
300
301 --bugfix : 7022001
302 X_dist_tab(i).pay_awt_group_id :=X_invoice_lines_rec.pay_awt_group_id;
303
304 --Bug9296445
305 X_dist_tab(i).reference_1 := X_invoice_lines_rec.reference_1;
306 X_dist_tab(i).reference_2 := X_invoice_lines_rec.reference_2;
307
308 --bugfix:4674194
309 IF (AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_ACTIVE) THEN
310 X_dist_tab(i).global_attribute3 := x_invoice_lines_rec.ship_to_location_id;
311 END IF;
312
313 --ETAX: Invwkb
314 X_dist_tab(i).intended_use := x_invoice_lines_rec.primary_intended_use;
315
316 -- Populate the amounts depending on whether the distribution
317 -- set is a skeleton or not.
318 IF (l_dist_set_percent_number <> 100) then
319 l_dist_amount := 0;
320 l_dist_base_amount := NULL; -- Bug 5199337
321 ELSE
322 --bug6653070
323 l_dist_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
324 NVL(l_dset_line_percent_dist,0)
325 * (NVL(X_invoice_lines_rec.amount,0))/100,
326 X_invoice_currency);
327 l_dist_base_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
328 NVL(l_dist_amount, 0) * X_exchange_rate,
329 X_base_currency);
330
331 END IF;
332
333 X_dist_tab(i).amount := l_dist_amount;
334 X_dist_tab(i).base_amount := l_dist_base_amount;
335 X_dist_tab(i).rounding_amt := 0;
336 X_dist_tab(i).stat_amount := X_invoice_lines_rec.stat_amount; --Bug#10241241
337
338 -- Maintain a running total that will be used for rounding to the
339 -- line base amount as well as the total amount.
340 l_running_total_amount := l_running_total_amount + l_dist_amount;
341 l_running_total_base_amt := l_running_total_base_amt + l_dist_base_amount;
342
343 IF (ABS(l_max_amount) <= ABS(l_dist_amount) OR i = 0) then
344 l_max_amount := l_dist_amount;
345 l_max_i := i;
346 END IF;
347
348 IF (X_invoice_lines_rec.project_id IS NOT NULL) THEN
349
350 X_dist_tab(i).project_id := X_invoice_lines_rec.project_id;
351 X_dist_tab(i).task_id := X_invoice_lines_rec.task_id;
352 X_dist_tab(i).expenditure_type := X_invoice_lines_rec.expenditure_type;
353 X_dist_tab(i).expenditure_organization_id := X_invoice_lines_rec.expenditure_organization_id;
354 X_dist_tab(i).expenditure_item_date := X_invoice_lines_rec.expenditure_item_date;
355
356 IF (X_invoice_lines_rec.pa_quantity IS NOT NULL AND
357 X_invoice_lines_rec.amount <> 0) then
358 X_dist_tab(i).pa_quantity := X_invoice_lines_rec.pa_quantity * l_dist_amount /
359 X_invoice_lines_rec.amount;
360 END IF;
361
362 X_dist_tab(i).pa_addition_flag := 'N';
363
364 ELSIF (l_dset_line_project_id is not null) then
365
366 X_dist_tab(i).project_id := l_dset_line_project_id;
367 X_dist_tab(i).task_id := l_dset_line_task_id;
368 X_dist_tab(i).expenditure_type := l_dset_line_expenditure_type;
369 X_dist_tab(i).expenditure_organization_id := l_dset_line_expenditure_org_id;
370 X_dist_tab(i).project_accounting_context := l_dset_line_proj_acct_context;
371
372 IF (X_invoice_lines_rec.pa_quantity is not null AND
373 X_invoice_lines_rec.amount <> 0) then
374
375 X_dist_tab(i).pa_quantity := X_invoice_lines_rec.pa_quantity * l_dist_amount /
376 X_invoice_lines_rec.amount;
377 END IF;
378 X_dist_tab(i).pa_addition_flag := 'N';
379 ELSE
380 X_dist_tab(i).pa_addition_flag := 'E';
381 END IF;
382
383 l_running_total_pa_quantity :=
384 l_running_total_pa_quantity + nvl(X_dist_tab(i).pa_quantity,0);
385 IF (ABS(l_max_pa_quantity) <= ABS(nvl(X_dist_tab(i).pa_quantity, 0))
386 OR i = 0) THEN
387 l_max_pa_quantity := X_dist_tab(i).pa_quantity;
388 l_max_pa_qty_i := i;
389 END IF;
390
391 X_dist_tab(i).set_of_books_id := X_invoice_lines_rec.set_of_books_id;
392 X_dist_tab(i).org_id := X_invoice_lines_rec.org_id;
393
394 X_dist_tab(i).type_1099 := nvl(l_dset_line_type_1099,
395 X_invoice_lines_rec.type_1099);
396 IF (X_dist_tab(i).type_1099 IS NOT NULL) THEN
397 X_dist_tab(i).income_tax_region :=
398 X_invoice_lines_rec.income_tax_region;
399 ELSE
400 X_dist_tab(i).income_tax_region := NULL;
401 END IF;
402
403 -- Gather the data we need to call validation in PA
404 IF (X_dist_tab(i).project_id is not null) then
405 user_id := to_number(FND_PROFILE.VALUE('USER_ID'));
406 l_pa_allows_overrides :=
407 FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES');
408 BEGIN
409 SELECT employee_id
410 INTO l_employee_id
411 FROM ap_suppliers /* Bug 4718054 */
412 WHERE DECODE(SIGN(TO_DATE(TO_CHAR(START_DATE_ACTIVE,'DD-MM-YYYY'),
413 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
414 1, 'N', DECODE(SIGN(TO_DATE(TO_CHAR(END_DATE_ACTIVE ,'DD-MM-YYYY'),
415 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
416 -1, 'N', 0, 'N', 'Y')) = 'Y'
417 AND enabled_flag = 'Y'
418 AND vendor_id = X_vendor_id;
419 EXCEPTION
420 WHEN no_data_found then
421 l_employee_id := NULL;
422 WHEN OTHERS then
423 l_employee_id := NULL;
424 END;
425 END IF;
426
427 debug_info := 'Get expenditure item date IF null';
428 IF (X_dist_tab(i).project_id IS NOT NULL AND
429 X_dist_tab(i).expenditure_item_date IS NULL) THEN
430 X_dist_tab(i).expenditure_item_date :=
431 AP_INVOICES_PKG.get_expenditure_item_date(
432 X_invoice_lines_rec.invoice_id,
433 X_invoice_date,
434 X_invoice_lines_rec.accounting_date,
435 null,
436 null,
437 l_error_found);
438 IF (l_error_found = 'Y') THEN
439 CLOSE dist_set_lines_cur;
440 Debug_info := debug_info
441 || ': cannot read expenditure item date information';
442 X_debug_context := current_calling_sequence;
443 X_debug_info := debug_info;
444 X_error_code := 'AP_DEBUG';
445 RETURN(FALSE);
446 END IF;
447 END IF;
448
449 -- Do not validate project information IF called from the Import since that
450 -- should be validated in the import itself.
451
452 IF (X_dist_tab(i).project_id IS NOT NULL AND
453 nvl(X_line_source, 'OTHER') <> 'IMPORT') then
454
455 --bugfix:5725904
456 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
457 l_sys_link_function :='ER' ;
458 Else
459 l_sys_link_function :='VI' ;
460 End if;
461
462 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
463 X_PROJECT_ID => X_dist_tab(i).project_id,
464 X_TASK_ID => X_dist_tab(i).task_id,
465 X_EI_DATE => X_dist_tab(i).expenditure_item_date,
466 X_EXPENDITURE_TYPE => X_dist_tab(i).expenditure_type,
467 X_NON_LABOR_RESOURCE => null,
468 X_PERSON_ID => l_employee_id,
469 X_QUANTITY => nvl(X_dist_tab(i).pa_quantity, '1'),
470 X_denom_currency_code => X_invoice_currency,
471 X_acct_currency_code => X_base_currency,
472 X_denom_raw_cost => l_dist_amount,
473 X_acct_raw_cost => l_dist_base_amount,
474 X_acct_rate_type => X_exchange_rate_type,
475 X_acct_rate_date => X_exchange_date,
476 X_acct_exchange_rate => X_exchange_rate,
477 X_TRANSFER_EI => null,
478 X_INCURRED_BY_ORG_ID => X_dist_tab(i).expenditure_organization_id,
479 X_NL_RESOURCE_ORG_ID => null,
480 X_TRANSACTION_SOURCE => l_sys_link_function, --5725904
481 X_CALLING_MODULE => 'apinlinb.pls',
482 X_VENDOR_ID => X_vendor_id,
483 X_ENTERED_BY_USER_ID => user_id,
484 X_ATTRIBUTE_CATEGORY => X_dist_tab(i).attribute_category,
485 X_ATTRIBUTE1 => X_dist_tab(i).attribute1,
486 X_ATTRIBUTE2 => X_dist_tab(i).attribute2,
487 X_ATTRIBUTE3 => X_dist_tab(i).attribute3,
488 X_ATTRIBUTE4 => X_dist_tab(i).attribute4,
489 X_ATTRIBUTE5 => X_dist_tab(i).attribute5,
490 X_ATTRIBUTE6 => X_dist_tab(i).attribute6,
491 X_ATTRIBUTE7 => X_dist_tab(i).attribute7,
492 X_ATTRIBUTE8 => X_dist_tab(i).attribute8,
493 X_ATTRIBUTE9 => X_dist_tab(i).attribute9,
494 X_ATTRIBUTE10 => X_dist_tab(i).attribute10,
495 X_ATTRIBUTE11 => X_dist_tab(i).attribute11,
496 X_ATTRIBUTE12 => X_dist_tab(i).attribute12,
497 X_ATTRIBUTE13 => X_dist_tab(i).attribute13,
498 X_ATTRIBUTE14 => X_dist_tab(i).attribute14,
499 X_ATTRIBUTE15 => X_dist_tab(i).attribute15,
500 X_msg_application => l_msg_application,
501 X_msg_type => l_msg_type,
502 X_msg_token1 => l_msg_token1,
503 X_msg_token2 => l_msg_token2,
504 X_msg_token3 => l_msg_token3,
505 X_msg_count => l_msg_count,
506 X_msg_data => l_msg_data,
507 X_BILLABLE_FLAG => l_billable_flag);
508
509 IF (l_msg_data IS NOT NULL) THEN
510 CLOSE dist_set_lines_cur;
511 --Bug 7490877 Converting PA error code to AP error code
512 x_error_code := 'AP'||substr(l_msg_data,3);
513
514 --Bug 7490877 Commented below code
515 /*
516 X_msg_application := l_msg_application;
517 X_msg_data := l_msg_data;
518
519 --bugfix:5725904
520 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
521 --bug 6682104 setting the token values
522 IF (l_msg_token1 IS NOT NULL) THEN
523 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
524 ELSE
525 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
526 END IF;
527
528 IF (l_msg_token2 IS NOT NULL) THEN
529 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
530 ELSE
531 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
532 END IF;
533
534 IF (l_msg_token3 IS NOT NULL) THEN
535 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
536 ELSE
537 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
538 END IF;
539
540 l_message_text := Fnd_Message.get;
541 X_Error_Code := l_message_text;
542 */
543
544 RETURN(FALSE);
545 END IF;
546 END IF; -- Validate PA IF project related and not called from import
547
548 -- If the distribution will be project related and the project comes from
549 -- the invoice line and this process is called from the import, use the
550 -- account from the invoice line which has already been overlayed in the
551 -- import. Otherwise, use the account from the distribution set line and
552 -- overlay as per need be.
553 --bug 2938770 For prepayment
554 --bug 7483050 added condition to check if dist set is null
555 IF (l_invoice_type_lookup_code = 'PREPAYMENT' and l_dset_line_ccid is null) then
556 X_dist_tab(i).dist_ccid := l_prepay_dist_code_ccid;
557
558 ElSIF (X_dist_tab(i).project_id IS NOT NULL AND
559 l_dset_line_project_id IS NULL AND
560 nvl(X_line_source, 'OTHER') = 'IMPORT') THEN
561 X_dist_tab(i).dist_ccid := X_invoice_lines_rec.default_dist_ccid;
562 BEGIN
563 SELECT account_type
564 INTO l_account_type
565 FROM gl_code_combinations
566 WHERE code_combination_id = X_invoice_lines_rec.default_dist_ccid;
567
568 EXCEPTION
569 WHEN no_data_found THEN
570 CLOSE dist_set_lines_cur;
571 Debug_info := debug_info || ': cannot read account type information';
572 X_debug_context := current_calling_sequence;
573 X_debug_info := debug_info;
574 IF (SQLCODE <> -20001) THEN
575 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
576 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
577 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
578 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
579 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
580 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
581 END IF;
582 X_error_code := 'AP_DEBUG';
583 RETURN(FALSE);
584 END;
585
586 ELSE -- project information comes from distribution set line or at least
587 -- is not from IMPORT
588 X_dist_tab(i).dist_ccid := l_dset_line_ccid;
589 IF ((X_dist_tab(i).project_id is not null AND
590 l_pa_allows_overrides = 'N')) then
591 IF ( NOT (AP_UTILITIES_PKG.IS_CCID_VALID(
592 l_dset_line_ccid,
593 X_chart_of_accounts_id,
594 X_invoice_lines_rec.accounting_date,
595 current_calling_sequence))) then
596 X_error_code := 'AP_INVALID_CCID';
597 CLOSE dist_set_lines_cur;
598 RETURN(FALSE);
599 END IF;
600
601 ELSE -- project allows overrides
602 IF (X_invoice_lines_rec.overlay_dist_code_concat is NULL AND
603 X_invoice_lines_rec.balancing_segment is NULL AND
604 X_invoice_lines_rec.account_segment is NULL AND
605 X_invoice_lines_rec.cost_center_segment is NULL) then
606 IF ( NOT (AP_UTILITIES_PKG.IS_CCID_VALID(
607 l_dset_line_ccid,
608 X_chart_of_accounts_id,
609 X_invoice_lines_rec.accounting_date,
610 current_calling_sequence))) then
611 X_error_code := 'AP_INVALID_CCID';
612 CLOSE DIST_SET_LINES_CUR;
613 RETURN(FALSE);
614 END IF;
615 ELSE -- account overlay information is provided at the line
616 IF ( NOT (AP_UTILITIES_PKG.OVERLAY_SEGMENTS (
617 X_invoice_lines_rec.balancing_segment,
618 X_invoice_lines_rec.cost_center_segment,
619 X_invoice_lines_rec.account_segment,
620 X_invoice_lines_rec.overlay_dist_code_concat,
621 l_dset_line_ccid,
622 X_invoice_lines_rec.set_of_books_id,
623 'CREATE_COMB_NO_AT',
624 l_unbuilt_flex,
625 l_reason_unbuilt_flex,
626 FND_GLOBAL.RESP_APPL_ID,
627 FND_GLOBAL.RESP_ID,
628 FND_GLOBAL.USER_ID,
629 current_calling_sequence))) then
630 X_error_code := 'AP_ACCOUNT_OVERLAY_INVALID';
631 CLOSE dist_set_lines_cur;
632 RETURN(FALSE);
633
634 ELSE -- overlay segments did not fail
635 X_dist_tab(i).dist_ccid := l_dset_line_ccid;
636 debug_info := 'Get account type from overlayed account';
637 BEGIN
638 SELECT account_type
639 INTO l_account_type
640 FROM gl_code_combinations
641 WHERE code_combination_id = l_dset_line_ccid;
642
643 EXCEPTION
644 WHEN no_data_found then
645 CLOSE dist_set_lines_cur;
646 Debug_info := debug_info
647 || ': cannot read account type information';
648 X_debug_context := current_calling_sequence;
649 X_debug_info := debug_info;
650 IF (SQLCODE <> -20001) THEN
651 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
652 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
653 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
654 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
655 ||', Invoice Line Number = '||TO_CHAR(X_invoice_lines_Rec.line_Number));
656 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
657 --Bug7539216
658 X_error_code := FND_MESSAGE.GET;
659 ELSE
660 X_error_code := 'AP_DEBUG';
661 --End of Bug7539216
662 END IF;
663 RETURN(FALSE);
664 END;
665 END IF; -- overlay segments did not fail
666 END IF; -- account overlay information is not null
667 END IF; -- project allow overrides
668 END IF; -- project information comes from distribution set line or
669 -- at least is not from IMPORT
670
671 IF (l_account_type = 'A' OR
672 (l_account_type = 'E' AND
673 X_invoice_lines_rec.assets_tracking_flag = 'Y')) THEN
674 X_dist_tab(i).assets_tracking_flag := 'Y';
675 X_dist_tab(i).asset_book_type_code :=
676 X_invoice_lines_rec.asset_book_type_code;
677 X_dist_tab(i).asset_category_id :=
678 X_invoice_lines_rec.asset_category_id;
679 ELSE
680 X_dist_tab(i).assets_tracking_flag := 'N';
681 X_dist_tab(i).asset_book_type_code := NULL;
682 X_dist_tab(i).asset_category_id := NULL;
683 END IF;
684
685
686 --Perform Grant information validation
687
688 IF (X_invoice_lines_rec.award_id IS NOT NULL) THEN
689
690 X_dist_tab(i).award_id := X_invoice_lines_rec.award_id;
691
692 ELSIF (l_dset_line_award_id is not null) THEN
693
694 X_dist_tab(i).award_id := gms_ap_api.get_distribution_award(l_dset_line_award_id);
695
696 GMS_AP_API.validate_transaction
697 (x_project_id => l_dset_line_project_id,
698 x_task_id => l_dset_line_task_id,
699 x_award_id => l_dset_line_award_id,
700 x_expenditure_type => l_dset_line_expenditure_type,
701 x_expenditure_item_date => x_dist_tab(i).expenditure_item_date,
702 x_calling_sequence => current_calling_sequence,
703 x_msg_application => l_msg_application,
704 x_msg_type => l_msg_type,
705 x_msg_count => l_msg_count,
706 x_msg_data => l_msg_data);
707
708 IF (l_msg_data IS NOT NULL) THEN
709 x_error_code := 'AP_INVALID_GRANT_INFO';
710 CLOSE dist_set_lines_cur;
711 X_msg_application := l_msg_application;
712 X_msg_data := l_msg_data;
713 RETURN(FALSE);
714 END IF;
715 END IF;
716
717 i := i + 1;
718
719 END LOOP;
720 CLOSE dist_set_lines_cur;
721
722 --------------------------------------------------------------
723 -- Step 3 - If any rounding is required on the distribution
724 -- amounts and the distribution to round is project related
725 -- revalidate PA information. Also, IF any rounding is
726 -- required and we will generate distributions, THEN update
727 -- the plsql distributions table.
728 -- bug6653070
729 --------------------------------------------------------------
730
731 IF (l_dist_set_percent_number = 100) THEN
732 IF (l_running_total_amount <> (X_invoice_lines_rec.amount) ) THEN
733 X_dist_tab(l_max_i).amount :=
734 X_dist_tab(l_max_i).amount + ((X_invoice_lines_rec.amount)
735 - l_running_total_amount);
736 l_running_total_base_amt :=
737 l_running_total_base_amt - X_dist_tab(l_max_i).base_amount;
738 X_dist_tab(l_max_i).base_amount :=
739 AP_UTILITIES_PKG.Ap_Round_Currency(
740 NVL(X_dist_tab(l_max_i).amount, 0) * X_exchange_rate ,
741 X_base_currency);
742 l_running_total_base_amt :=
743 l_running_total_base_amt + X_dist_tab(l_max_i).base_amount;
744 l_rounding_exists := 'Y';
745 END IF;
746 IF (nvl(l_running_total_base_amt, 0) <>
747 nvl(X_invoice_lines_rec.base_amount, 0)) THEN
748 X_dist_tab(l_max_i).rounding_amt := X_invoice_lines_rec.base_amount -
749 l_running_total_base_amt;
750 X_dist_tab(l_max_i).base_amount := X_dist_tab(l_max_i).base_amount +
751 X_dist_tab(l_max_i).rounding_amt;
752 l_rounding_exists := 'Y';
753 END IF;
754 IF (nvl(l_running_total_pa_quantity, 0) <>
755 nvl(X_invoice_lines_rec.pa_quantity, 0)) THEN
756 X_dist_tab(l_max_pa_qty_i).pa_quantity :=
757 X_dist_tab(l_max_pa_qty_i).pa_quantity +
758 X_invoice_lines_rec.pa_quantity - l_running_total_pa_quantity;
759 l_rounding_pa_qty_exists := 'Y';
760 END IF;
761 END IF; -- total percent is 100
762 IF (X_dist_tab(l_max_i).project_id IS NOT NULL AND
763 l_rounding_exists = 'Y') THEN
764
765 --bugfix:5725904
766 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
767 l_sys_link_function :='ER' ;
768 Else
769 l_sys_link_function :='VI' ;
770 End if;
771
772
773 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
774 X_PROJECT_ID => X_dist_tab(l_max_i).project_id,
775 X_TASK_ID => X_dist_tab(l_max_i).task_id,
776 X_EI_DATE => X_dist_tab(l_max_i).expenditure_item_date,
777 X_EXPENDITURE_TYPE => X_dist_tab(l_max_i).expenditure_type,
778 X_NON_LABOR_RESOURCE => null,
779 X_PERSON_ID => l_employee_id,
780 X_QUANTITY => nvl(X_dist_tab(l_max_i).pa_quantity, '1'),
781 X_denom_currency_code => X_invoice_currency,
782 X_acct_currency_code => X_base_currency,
783 X_denom_raw_cost => X_dist_tab(l_max_i).amount,
784 X_acct_raw_cost => X_dist_tab(l_max_i).base_amount,
785 X_acct_rate_type => X_exchange_rate_type,
786 X_acct_rate_date => X_exchange_date,
787 X_acct_exchange_rate => X_exchange_rate,
788 X_TRANSFER_EI => null,
789 X_INCURRED_BY_ORG_ID => X_dist_tab(l_max_i).expenditure_organization_id,
790 X_NL_RESOURCE_ORG_ID => null,
791 X_TRANSACTION_SOURCE => l_sys_link_function,
792 X_CALLING_MODULE => 'apinlinb.pls',
793 X_VENDOR_ID => X_vendor_id,
794 X_ENTERED_BY_USER_ID => user_id,
795 X_ATTRIBUTE_CATEGORY => X_dist_tab(l_max_i).attribute_category,
796 X_ATTRIBUTE1 => X_dist_tab(l_max_i).attribute1,
797 X_ATTRIBUTE2 => X_dist_tab(l_max_i).attribute2,
798 X_ATTRIBUTE3 => X_dist_tab(l_max_i).attribute3,
799 X_ATTRIBUTE4 => X_dist_tab(l_max_i).attribute4,
800 X_ATTRIBUTE5 => X_dist_tab(l_max_i).attribute5,
801 X_ATTRIBUTE6 => X_dist_tab(l_max_i).attribute6,
802 X_ATTRIBUTE7 => X_dist_tab(l_max_i).attribute7,
803 X_ATTRIBUTE8 => X_dist_tab(l_max_i).attribute8,
804 X_ATTRIBUTE9 => X_dist_tab(l_max_i).attribute9,
805 X_ATTRIBUTE10 => X_dist_tab(l_max_i).attribute10,
806 X_ATTRIBUTE11 => X_dist_tab(l_max_i).attribute11,
807 X_ATTRIBUTE12 => X_dist_tab(l_max_i).attribute12,
808 X_ATTRIBUTE13 => X_dist_tab(l_max_i).attribute13,
809 X_ATTRIBUTE14 => X_dist_tab(l_max_i).attribute14,
810 X_ATTRIBUTE15 => X_dist_tab(l_max_i).attribute15,
811 X_msg_application => l_msg_application,
812 X_msg_type => l_msg_type,
813 X_msg_token1 => l_msg_token1,
814 X_msg_token2 => l_msg_token2,
815 X_msg_token3 => l_msg_token3,
816 X_msg_count => l_msg_count,
817 X_msg_data => l_msg_data,
818 X_BILLABLE_FLAG => l_billable_flag);
819
820 IF (l_msg_data is not null) then
821
822 --Bug 7490877 Converting PA error code to AP error code
823 x_error_code := 'AP'||substr(l_msg_data,3);
824
825 --Bug 7490877 Commented below code
826 /*
827
828 X_msg_application := l_msg_application;
829 X_msg_data := l_msg_data;
830
831 --bugfix:5725904
832 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
833 --bug 6682104 setting the token values
834 IF (l_msg_token1 IS NOT NULL) THEN
835 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
836 ELSE
837 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
838 END IF;
839
840 IF (l_msg_token2 IS NOT NULL) THEN
841 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
842 ELSE
843 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
844 END IF;
845
846 IF (l_msg_token3 IS NOT NULL) THEN
847 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
848 ELSE
849 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
850 END IF;
851 l_message_text := Fnd_Message.get;
852 x_error_code := l_message_text;
853 */
854
855 RETURN(FALSE);
856 END IF;
857 END IF; -- project id is not null and there was rounding
858 IF ((X_dist_tab(l_max_pa_qty_i).project_id IS NOT NULL) AND
859 l_rounding_pa_qty_exists = 'Y' AND
860 (l_rounding_exists <> 'Y' OR
861 (l_rounding_exists = 'Y' AND
862 l_max_i <> l_max_pa_qty_i))) THEN
863
864 --bugfix:5725904
865 If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then
866 l_sys_link_function :='ER' ;
867 Else
868 l_sys_link_function :='VI' ;
869 End if;
870
871
872 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
873 X_PROJECT_ID => X_dist_tab(l_max_pa_qty_i).project_id,
874 X_TASK_ID => X_dist_tab(l_max_pa_qty_i).task_id,
875 X_EI_DATE => X_dist_tab(l_max_pa_qty_i).expenditure_item_date,
876 X_EXPENDITURE_TYPE => X_dist_tab(l_max_pa_qty_i).expenditure_type,
877 X_NON_LABOR_RESOURCE => null,
878 X_PERSON_ID => l_employee_id,
879 X_QUANTITY => nvl(X_dist_tab(l_max_pa_qty_i).pa_quantity, '1'),
880 X_denom_currency_code => X_invoice_currency,
881 X_acct_currency_code => X_base_currency,
882 X_denom_raw_cost => X_dist_tab(l_max_pa_qty_i).amount,
883 X_acct_raw_cost => X_dist_tab(l_max_pa_qty_i).base_amount,
884 X_acct_rate_type => X_exchange_rate_type,
885 X_acct_rate_date => X_exchange_date,
886 X_acct_exchange_rate => X_exchange_rate,
887 X_TRANSFER_EI => null,
888 X_INCURRED_BY_ORG_ID =>
889 X_dist_tab(l_max_pa_qty_i).expenditure_organization_id,
890 X_NL_RESOURCE_ORG_ID => null,
891 X_TRANSACTION_SOURCE => l_sys_link_function, --5725904
892 X_CALLING_MODULE => 'apinlinb.pls',
893 X_VENDOR_ID => X_vendor_id,
894 X_ENTERED_BY_USER_ID => user_id,
895 X_ATTRIBUTE_CATEGORY => X_dist_tab(l_max_pa_qty_i).attribute_category,
896 X_ATTRIBUTE1 => X_dist_tab(l_max_pa_qty_i).attribute1,
897 X_ATTRIBUTE2 => X_dist_tab(l_max_pa_qty_i).attribute2,
898 X_ATTRIBUTE3 => X_dist_tab(l_max_pa_qty_i).attribute3,
899 X_ATTRIBUTE4 => X_dist_tab(l_max_pa_qty_i).attribute4,
900 X_ATTRIBUTE5 => X_dist_tab(l_max_pa_qty_i).attribute5,
901 X_ATTRIBUTE6 => X_dist_tab(l_max_pa_qty_i).attribute6,
902 X_ATTRIBUTE7 => X_dist_tab(l_max_pa_qty_i).attribute7,
903 X_ATTRIBUTE8 => X_dist_tab(l_max_pa_qty_i).attribute8,
904 X_ATTRIBUTE9 => X_dist_tab(l_max_pa_qty_i).attribute9,
905 X_ATTRIBUTE10 => X_dist_tab(l_max_pa_qty_i).attribute10,
906 X_ATTRIBUTE11 => X_dist_tab(l_max_pa_qty_i).attribute11,
907 X_ATTRIBUTE12 => X_dist_tab(l_max_pa_qty_i).attribute12,
908 X_ATTRIBUTE13 => X_dist_tab(l_max_pa_qty_i).attribute13,
909 X_ATTRIBUTE14 => X_dist_tab(l_max_pa_qty_i).attribute14,
910 X_ATTRIBUTE15 => X_dist_tab(l_max_pa_qty_i).attribute15,
911 X_msg_application => l_msg_application,
912 X_msg_type => l_msg_type,
913 X_msg_token1 => l_msg_token1,
914 X_msg_token2 => l_msg_token2,
915 X_msg_token3 => l_msg_token3,
916 X_msg_count => l_msg_count,
917 X_msg_data => l_msg_data,
918 X_BILLABLE_FLAG => l_billable_flag);
919
920 IF (l_msg_data is not null) then
921 --Bug 7490877 Converting PA error code to AP error code
922 x_error_code := 'AP'||substr(l_msg_data,3);
923
924 --Bug 7490877 Commented below code
925 /*
926
927 X_msg_application := l_msg_application;
928 X_msg_data := l_msg_data;
929
930 --bugfix:5725904
931 Fnd_Message.Set_Name(l_msg_application, l_msg_data);
932 --bug 6682104 setting the token values
933 IF (l_msg_token1 IS NOT NULL) THEN
934 fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1);
935 ELSE
936 fnd_message.set_token('PATC_MSG_TOKEN1',FND_API.G_MISS_CHAR);
937 END IF;
938
939 IF (l_msg_token2 IS NOT NULL) THEN
940 fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2);
941 ELSE
942 fnd_message.set_token('PATC_MSG_TOKEN2',FND_API.G_MISS_CHAR);
943 END IF;
944
945 IF (l_msg_token3 IS NOT NULL) THEN
946 fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3);
947 ELSE
948 fnd_message.set_token('PATC_MSG_TOKEN3',FND_API.G_MISS_CHAR);
949 END IF;
950 l_message_text := Fnd_Message.get;
951 x_error_code := l_message_text;
952 */
953
954 RETURN(FALSE);
955 END IF;
956 END IF; -- Rounding of pa qty existed and it is for a different dist
957 -- than rounding of amount IF any.
958
959 IF (X_dist_tab(l_max_i).award_id IS NOT NULL AND
960 l_rounding_exists = 'Y') THEN
961
962 GMS_AP_API.validate_transaction
963 ( x_project_id => x_dist_tab(l_max_i).project_id,
964 x_task_id => x_dist_tab(l_max_i).task_id,
965 x_award_id => x_dist_tab(l_max_i).award_id,
966 x_expenditure_type => x_dist_tab(l_max_i).expenditure_type,
967 x_expenditure_item_date => x_dist_tab(l_max_i).expenditure_item_date,
968 x_calling_sequence => 'AWARD_ID',
969 x_msg_application => l_msg_application,
970 x_msg_type => l_msg_type,
971 x_msg_count => l_msg_count,
972 x_msg_data => l_msg_data ) ;
973
974 IF (l_msg_data is not null) then
975 x_error_code := 'AP_INVALID_GRANT_INFO';
976 x_msg_application := l_msg_application;
977 x_msg_data := l_msg_data;
978 RETURN(FALSE);
979 END IF;
980 END IF;
981
982 IF ((X_dist_tab(l_max_pa_qty_i).award_id IS NOT NULL) AND
983 l_rounding_pa_qty_exists = 'Y' AND
984 (l_rounding_exists <> 'Y' OR
985 (l_rounding_exists = 'Y' AND
986 l_max_i <> l_max_pa_qty_i))) THEN
987
988 GMS_AP_API.validate_transaction
989 ( x_project_id => X_dist_tab(l_max_pa_qty_i).project_id,
990 x_task_id => X_dist_tab(l_max_pa_qty_i).task_id,
991 x_award_id => X_dist_tab(l_max_pa_qty_i).award_id,
992 x_expenditure_type => X_dist_tab(l_max_pa_qty_i).expenditure_type,
993 x_expenditure_item_date => X_dist_tab(l_max_pa_qty_i).expenditure_item_date,
994 x_calling_sequence => 'AWARD_ID',
995 x_msg_application => l_msg_application,
996 x_msg_type => l_msg_type,
997 x_msg_count => l_msg_count,
998 x_msg_data => l_msg_data );
999
1000 IF (l_msg_data is not null) then
1001 x_msg_application := l_msg_application;
1002 x_msg_data := l_msg_data;
1003 RETURN(FALSE);
1004 END IF;
1005 END IF;
1006
1007 RETURN(TRUE);
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 Debug_info := 'Error occurred';
1012 X_debug_context := current_calling_sequence;
1013 X_debug_info := debug_info;
1014 IF (SQLCODE <> -20001) THEN
1015 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1016 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1017 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1018 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_lines_Rec.invoice_id)
1019 ||', Invoice Line Number =
1020 '||TO_CHAR(X_invoice_lines_Rec.line_Number));
1021 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1022 END IF;
1023 X_error_code := 'AP_DEBUG';
1024 RETURN(FALSE);
1025
1026 END generate_dist_tab_for_dist_set;
1027
1028 -----------------------------------------------------------------------
1029 -- FUNCTION insert_from_dist_set validates the distribution_set info
1030 -- and generates distributions
1031 -- by calling ap_invoice_distributions_pkg.insert_from_dist_set.
1032 --
1033 FUNCTION Insert_From_Dist_Set(
1034 X_invoice_id IN NUMBER,
1035 X_line_number IN NUMBER DEFAULT NULL,
1036 X_GL_Date IN DATE,
1037 X_Period_Name IN VARCHAR2,
1038 X_Skeleton_Allowed IN VARCHAR2 DEFAULT 'N',
1039 X_Generate_Dists IN VARCHAR2 DEFAULT 'Y',
1040 X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
1041 X_Error_Code OUT NOCOPY VARCHAR2,
1042 X_Debug_Info OUT NOCOPY VARCHAR2,
1043 X_Debug_Context OUT NOCOPY VARCHAR2,
1044 X_Msg_Application OUT NOCOPY VARCHAR2,
1045 X_Msg_Data OUT NOCOPY VARCHAR2,
1046 X_calling_sequence IN VARCHAR2) RETURN BOOLEAN
1047
1048 IS
1049 CURSOR line_rec(X_line_number NUMBER) IS
1050 SELECT invoice_id,
1051 line_number,
1052 line_type_lookup_code,
1053 requester_id,
1054 description,
1055 line_source,
1056 org_id,
1057 line_group_number,
1058 inventory_item_id,
1059 item_description,
1060 serial_number,
1061 manufacturer,
1062 model_number,
1063 warranty_number,
1064 generate_dists,
1065 match_type,
1066 distribution_set_id,
1067 account_segment,
1068 balancing_segment,
1069 cost_center_segment,
1070 overlay_dist_code_concat,
1071 default_dist_ccid,
1072 prorate_across_all_items,
1073 accounting_date,
1074 period_name,
1075 deferred_acctg_flag,
1076 def_acctg_start_date,
1077 def_acctg_end_date,
1078 def_acctg_number_of_periods,
1079 def_acctg_period_type,
1080 set_of_books_id,
1081 amount,
1082 base_amount,
1083 rounding_amt,
1084 quantity_invoiced,
1085 unit_meas_lookup_code,
1086 unit_price,
1087 wfapproval_status,
1088 discarded_flag,
1089 original_amount,
1090 original_base_amount,
1091 original_rounding_amt,
1092 cancelled_flag,
1093 income_tax_region,
1094 type_1099,
1095 stat_amount,
1096 prepay_invoice_id,
1097 prepay_line_number,
1098 invoice_includes_prepay_flag,
1099 corrected_inv_id,
1100 corrected_line_number,
1101 po_header_id,
1102 po_line_id,
1103 po_release_id,
1104 po_line_location_id,
1105 po_distribution_id,
1106 rcv_transaction_id,
1107 final_match_flag,
1108 assets_tracking_flag,
1109 asset_book_type_code,
1110 asset_category_id,
1111 project_id,
1112 task_id,
1113 expenditure_type,
1114 expenditure_item_date,
1115 expenditure_organization_id,
1116 pa_quantity,
1117 pa_cc_ar_invoice_id,
1118 pa_cc_ar_invoice_line_num ,
1119 pa_cc_processed_code,
1120 award_id,
1121 awt_group_id,
1122 reference_1,
1123 reference_2,
1124 receipt_verified_flag,
1125 receipt_required_flag,
1126 receipt_missing_flag,
1127 justification,
1128 expense_group,
1129 start_expense_date,
1130 end_expense_date,
1131 receipt_currency_code,
1132 receipt_conversion_rate,
1133 receipt_currency_amount,
1134 daily_amount,
1135 web_parameter_id,
1136 adjustment_reason,
1137 merchant_document_number,
1138 merchant_name,
1139 merchant_reference,
1140 merchant_tax_reg_number,
1141 merchant_taxpayer_id,
1142 country_of_supply,
1143 credit_card_trx_id,
1144 company_prepaid_invoice_id,
1145 cc_reversal_flag,
1146 creation_date,
1147 created_by,
1148 last_updated_by,
1149 last_update_date,
1150 last_update_login,
1151 program_application_id,
1152 program_id,
1153 program_update_date,
1154 request_id,
1155 attribute_category,
1156 attribute1,
1157 attribute2,
1158 attribute3,
1159 attribute4,
1160 attribute5,
1161 attribute6,
1162 attribute7,
1163 attribute8,
1164 attribute9,
1165 attribute10,
1166 attribute11,
1167 attribute12,
1168 attribute13,
1169 attribute14,
1170 attribute15,
1171 global_attribute_category,
1172 global_attribute1,
1173 global_attribute2,
1174 global_attribute3,
1175 global_attribute4,
1176 global_attribute5,
1177 global_attribute6,
1178 global_attribute7,
1179 global_attribute8,
1180 global_attribute9,
1181 global_attribute10,
1182 global_attribute11,
1183 global_attribute12,
1184 global_attribute13,
1185 global_attribute14,
1186 global_attribute15,
1187 global_attribute16,
1188 global_attribute17,
1189 global_attribute18,
1190 global_attribute19,
1191 global_attribute20,
1192 --ETAX: Invwkb
1193 included_tax_amount,
1194 primary_intended_use,
1195 --Bugfix:4673607
1196 application_id,
1197 product_table,
1198 reference_key1,
1199 reference_key2,
1200 reference_key3,
1201 reference_key4,
1202 reference_key5,
1203 --bugfix:4674194
1204 ship_to_location_id,
1205 --bugfix:7022001
1206 pay_awt_group_id
1207 FROM AP_INVOICE_LINES
1208 WHERE invoice_id = X_invoice_id
1209 AND line_number = X_line_number;
1210
1211
1212 --ETAX: Invwkb
1213 CURSOR Invoice_Rec IS
1214 SELECT *
1215 FROM AP_INVOICES
1216 WHERE invoice_id = x_invoice_id;
1217
1218 l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
1219 l_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
1220 l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
1221
1222 l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
1223
1224 l_dist_set_percent_number NUMBER := 0;
1225 l_dist_set_description AP_DISTRIBUTION_SETS.DESCRIPTION%TYPE;
1226 l_dist_set_attribute_category AP_DISTRIBUTION_SETS.ATTRIBUTE_CATEGORY%TYPE;
1227 l_dist_set_attribute1 AP_DISTRIBUTION_SETS.ATTRIBUTE1%TYPE;
1228 l_dist_set_attribute2 AP_DISTRIBUTION_SETS.ATTRIBUTE2%TYPE;
1229 l_dist_set_attribute3 AP_DISTRIBUTION_SETS.ATTRIBUTE3%TYPE;
1230 l_dist_set_attribute4 AP_DISTRIBUTION_SETS.ATTRIBUTE4%TYPE;
1231 l_dist_set_attribute5 AP_DISTRIBUTION_SETS.ATTRIBUTE5%TYPE;
1232 l_dist_set_attribute6 AP_DISTRIBUTION_SETS.ATTRIBUTE6%TYPE;
1233 l_dist_set_attribute7 AP_DISTRIBUTION_SETS.ATTRIBUTE7%TYPE;
1234 l_dist_set_attribute8 AP_DISTRIBUTION_SETS.ATTRIBUTE8%TYPE;
1235 l_dist_set_attribute9 AP_DISTRIBUTION_SETS.ATTRIBUTE9%TYPE;
1236 l_dist_set_attribute10 AP_DISTRIBUTION_SETS.ATTRIBUTE10%TYPE;
1237 l_dist_set_attribute11 AP_DISTRIBUTION_SETS.ATTRIBUTE11%TYPE;
1238 l_dist_set_attribute12 AP_DISTRIBUTION_SETS.ATTRIBUTE12%TYPE;
1239 l_dist_set_attribute13 AP_DISTRIBUTION_SETS.ATTRIBUTE13%TYPE;
1240 l_dist_set_attribute14 AP_DISTRIBUTION_SETS.ATTRIBUTE14%TYPE;
1241 l_dist_set_attribute15 AP_DISTRIBUTION_SETS.ATTRIBUTE15%TYPE;
1242 l_inactive_date AP_DISTRIBUTION_SETS.INACTIVE_DATE%TYPE;
1243 l_batch_id AP_INVOICES.BATCH_ID%TYPE;
1244 l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
1245 l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
1246 l_invoice_date AP_INVOICES.INVOICE_DATE%TYPE;
1247 l_exchange_rate AP_INVOICES.EXCHANGE_RATE%TYPE;
1248 l_exchange_date AP_INVOICES.EXCHANGE_DATE%TYPE;
1249 l_exchange_rate_type AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE;
1250 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
1251 l_msg_application VARCHAR2(25);
1252 l_msg_count NUMBER;
1253 l_msg_data VARCHAR2(30);
1254 l_error_code VARCHAR2(4000); --Bug7539216
1255
1256 y_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
1257
1258 current_calling_sequence VARCHAR2(1000);
1259 debug_info VARCHAR2(2000);
1260 debug_context VARCHAR2(1000);
1261 l_error_found VARCHAR2(1);
1262
1263 BEGIN
1264 -- Update the calling sequence
1265 --
1266 current_calling_sequence := 'AP_INVOICE_LINES_PKG.insert_from_dist_set<-'
1267 ||X_calling_sequence;
1268 --------------------------------------------------------------
1269 -- Step 1 - Validate that distribution set was passed or
1270 -- get it from the line IF line already exists.
1271 -- If line already exists, THEN use this chance to read other
1272 -- Line related data we will need (performance).
1273 -- If a distribution set is not found, or IF inconsistent
1274 -- information was provided THEN exit the FUNCTION and
1275 -- RETURN false.
1276 --------------------------------------------------------------
1277 debug_info := 'Verify distribution set information available';
1278 IF (X_line_number IS NULL) THEN
1279 debug_info := debug_info ||': No Line Info is provided';
1280 X_debug_context := current_calling_sequence;
1281 X_debug_info := debug_info;
1282 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1283 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1284 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1285 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1286 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1287 X_error_code := 'AP_DEBUG';
1288 RETURN(FALSE);
1289 ELSE
1290 BEGIN
1291 OPEN line_rec(x_line_number);
1292 FETCH line_rec INTO l_invoice_line_rec;
1293 IF (line_rec%NOTFOUND) THEN
1294 CLOSE line_rec;
1295 RAISE NO_DATA_FOUND;
1296 END IF;
1297 CLOSE line_Rec;
1298
1299 EXCEPTION
1300 WHEN no_data_found THEN
1301 debug_info := debug_info ||': No valid line record was found.';
1302 X_debug_context := current_calling_sequence;
1303 X_debug_info := debug_info;
1304 IF (SQLCODE <> -20001) THEN
1305 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1306 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1307 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1308 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1309 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1310 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1311 END IF;
1312 X_error_code := 'AP_DEBUG';
1313 RETURN(FALSE);
1314 END;
1315
1316 END IF; -- Line NUMBER is null
1317
1318 --------------------------------------------------------------
1319 -- Step 2 - Now that we have the distribution set, obtain
1320 -- information required for validation and defaulting.
1321 -- Also verify that the distribution set is not inactive.
1322 --------------------------------------------------------------
1323 debug_info := 'Get total percent for distribution set';
1324 BEGIN
1325 SELECT total_percent_distribution,
1326 description,
1327 attribute_category,
1328 attribute1,
1329 attribute2,
1330 attribute3,
1331 attribute4,
1332 attribute5,
1333 attribute6,
1334 attribute7,
1335 attribute8,
1336 attribute9,
1337 attribute10,
1338 attribute11,
1339 attribute12,
1340 attribute13,
1341 attribute14,
1342 attribute15,
1343 inactive_date
1344 INTO l_dist_set_percent_number,
1345 l_dist_set_description,
1346 l_dist_set_attribute_category,
1347 l_dist_set_attribute1,
1348 l_dist_set_attribute2,
1349 l_dist_set_attribute3,
1350 l_dist_set_attribute4,
1351 l_dist_set_attribute5,
1352 l_dist_set_attribute6,
1353 l_dist_set_attribute7,
1354 l_dist_set_attribute8,
1355 l_dist_set_attribute9,
1356 l_dist_set_attribute10,
1357 l_dist_set_attribute11,
1358 l_dist_set_attribute12,
1359 l_dist_set_attribute13,
1360 l_dist_set_attribute14,
1361 l_dist_set_attribute15,
1362 l_inactive_date
1363 FROM ap_distribution_sets
1364 WHERE distribution_set_id = l_invoice_line_rec.distribution_set_id;
1365
1366 IF (nvl(l_inactive_date, trunc(sysdate) + 1) <= trunc(sysdate)) THEN
1367 X_error_code := 'AP_VEN_DIST_SET_INVALID';
1368 RETURN(FALSE);
1369 END IF;
1370
1371 EXCEPTION
1372 WHEN NO_DATA_FOUND THEN
1373 Debug_info := debug_info || ': Cannot read Dist Set';
1374 X_debug_context := current_calling_sequence;
1375 X_debug_info := debug_info;
1376 IF (SQLCODE <> -20001) THEN
1377 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1378 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1379 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1380 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1381 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1382 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1383 END IF;
1384 X_error_code := 'AP_DEBUG';
1385 RETURN(FALSE);
1386 END;
1387
1388 --------------------------------------------------------------
1389 -- Step 3 - Validate that IF the calling module requested not
1390 -- to allow skeleton distribution sets e.g. when validation
1391 -- is calling to generate distributions i.e. user intervention
1392 -- won't be possible, THEN verify that the total percent for the
1393 -- distribution set is 100.
1394 -- For these known checks we RETURN specific error codes.
1395 --------------------------------------------------------------
1396
1397 /* Bug 4928285. There is no need for this check since we should be
1398 able to create distributions using skeleton distribution set
1399 IF (X_Skeleton_Allowed = 'N' AND
1400 l_dist_set_percent_number <> 100) then
1401 X_error_code := 'AP_CANT_USE_SKELETON_DIST_SET';
1402 RETURN(FALSE);
1403 END IF;
1404 */
1405
1406 --------------------------------------------------------------
1407 -- Step 4 - Obtain information from the invoice header that would
1408 -- be necessary to validate that generation of distributions
1409 -- is possible and to create the line IF one does not
1410 -- exist already.
1411 -----------------------------------------------------------------
1412 debug_info := 'Select header, vendor information and amount to distribute';
1413 BEGIN
1414 SELECT AI.batch_id,
1415 AI.vendor_id,
1416 AI.vendor_site_id,
1417 AI.invoice_date,
1418 AI.exchange_rate,
1419 AI.exchange_date,
1420 AI.exchange_rate_type,
1421 AI.invoice_currency_code,
1422 AI.set_of_books_id
1423 INTO l_batch_id,
1424 l_vendor_id,
1425 l_vendor_site_id,
1426 l_invoice_date,
1427 l_exchange_rate,
1428 l_exchange_date,
1429 l_exchange_rate_type,
1430 l_invoice_currency_code,
1431 l_set_of_books_id
1432 FROM ap_invoices AI
1433 WHERE invoice_id = X_invoice_id;
1434
1435 EXCEPTION
1436 When no_data_found then
1437 Debug_info := debug_info || ': cannot read invoice information';
1438 X_debug_context := current_calling_sequence;
1439 X_debug_info := debug_info;
1440 IF (SQLCODE <> -20001) THEN
1441 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1442 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1443 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1444 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1445 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1446 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1447 END IF;
1448 X_error_code := 'AP_DEBUG';
1449 RETURN(FALSE);
1450
1451 END;
1452
1453 --------------------------------------------------------------
1454 -- Step 5 - Get system level information necessary for
1455 -- validation and generation of lines.
1456 --------------------------------------------------------------
1457 debug_info := 'Get system information';
1458
1459 BEGIN
1460 -- get chart_of_accounts_id from ap_system_parameters
1461 SELECT gsob.chart_of_accounts_id,
1462 ap.base_currency_code
1463 INTO l_chart_of_accounts_id,
1464 l_base_currency_code
1465 FROM ap_system_parameters ap, gl_sets_of_books gsob
1466 WHERE ap.set_of_books_id = gsob.set_of_books_id
1467 AND ap.set_of_books_id = l_set_of_books_id
1468 AND ap.org_id = l_invoice_line_rec.org_id;
1469
1470 EXCEPTION
1471 WHEN no_data_found THEN
1472 Debug_info := debug_info || ': No GL information was found';
1473 X_debug_context := current_calling_sequence;
1474 X_debug_info := debug_info;
1475 IF (SQLCODE <> -20001) THEN
1476 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1477 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1478 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1479 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
1480 ||', Invoice Line Number = '||TO_CHAR(X_line_Number));
1481 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1482 END IF;
1483 X_error_code := 'AP_DEBUG';
1484 RETURN(FALSE);
1485 END;
1486
1487 --------------------------------------------------------------
1488 -- Step 6: Set the gl_date and period_name to the
1489 -- parameters, since they will be used by the FUNCTION to generate
1490 -- distributions.
1491 --------------------------------------------------------------
1492
1493 l_invoice_line_rec.accounting_date := X_GL_Date;
1494 l_invoice_line_rec.period_name := X_Period_Name;
1495
1496 --------------------------------------------------------------
1497 -- Step 9 - Validate distribution level information including
1498 -- accounting and project level information.
1499 -- The call RETURNs a plsql table of distributions to be inserted
1500 -- later. Note that IF this part fails, the entire trx should
1501 -- be rolled back to ensure roll back of the line creation.
1502 --------------------------------------------------------------
1503 debug_info := 'Calling generate_dist_tab_from_dist_set ';
1504 IF (NOT (Generate_Dist_Tab_For_Dist_Set(
1505 l_vendor_id, -- IN
1506 l_invoice_date, -- IN
1507 l_invoice_line_rec, -- IN
1508 null, -- IN
1509 y_dist_tab, -- IN
1510 l_dist_set_percent_number, -- IN
1511 l_exchange_rate, -- IN
1512 l_exchange_rate_type, -- IN
1513 l_exchange_date , -- IN
1514 l_invoice_currency_code, -- IN
1515 l_base_currency_code, -- IN
1516 l_chart_of_accounts_id, -- IN
1517 l_Error_Code, -- OUT NOCOPY VARCHAR2,
1518 Debug_Info, -- OUT NOCOPY VARCHAR2,
1519 Debug_Context, -- OUT NOCOPY VARCHAR2,
1520 l_msg_application, -- OUT NOCOPY VARCHAR2,
1521 l_msg_data, -- OUT NOCOPY VARCHAR2,
1522 current_calling_sequence -- IN VARCHAR2,
1523 ))) then
1524
1525
1526 IF (l_error_code IS NOT NULL) THEN
1527 X_error_code := l_error_code;
1528 RETURN (FALSE);
1529 ELSIF (l_msg_data IS NOT NULL) THEN
1530 X_msg_application := l_msg_application;
1531 X_msg_data := l_msg_data;
1532 RETURN(FALSE);
1533 ELSE
1534 X_debug_context := current_calling_sequence;
1535 X_debug_info := debug_info;
1536 RETURN (FALSE);
1537 END IF;
1538 END IF;
1539
1540
1541 --------------------------------------------------------------
1542 -- Step 11 - If the calling module requested generation of
1543 -- distributions call the appropriate FUNCTION to generate
1544 -- them.
1545 --------------------------------------------------------------
1546 IF (X_Generate_Dists = 'Y' ) then
1547
1548 debug_info := 'Calling AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set';
1549 IF ( NOT (AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set(
1550 l_batch_id,
1551 X_invoice_id,
1552 X_line_number,
1553 y_dist_tab,
1554 X_Generate_Permanent,
1555 -- l_error_code,
1556 debug_info,
1557 debug_context,
1558 current_calling_sequence))) then
1559
1560 IF (l_error_code IS NOT NULL) THEN
1561 X_error_code := l_error_code;
1562 RETURN (FALSE);
1563 ELSE
1564 X_debug_context := current_calling_sequence;
1565 X_debug_info := debug_info;
1566 RETURN (FALSE);
1567 END IF;
1568
1569 END IF;
1570
1571
1572 END IF; -- Generate dists
1573
1574 RETURN(TRUE);
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 IF (SQLCODE <> -20001) THEN
1579 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1580 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1581 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1582 current_calling_sequence);
1583 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1584 'X_GL_Date = ' ||TO_CHAR(X_GL_Date)
1585 ||', X_Period_Name = ' ||X_Period_Name
1586 ||', X_Invoice_Id = ' ||TO_CHAR(X_invoice_id)
1587 );
1588 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1589 END IF;
1590 APP_EXCEPTION.RAISE_EXCEPTION;
1591
1592 END insert_from_dist_set;
1593
1594 -----------------------------------------------------------------------
1595 -- FUNCTION get_max_dist_line_num RETURNs the maximum distribution line
1596 -- NUMBER given an invoice and line, it RETURNs 0 IF no distributions exist.
1597 ----------------------------------------------------------------------
1598 FUNCTION Get_Max_Dist_Line_Num(
1599 X_invoice_id IN NUMBER,
1600 X_line_number IN NUMBER) RETURN NUMBER
1601 IS
1602 l_max_dist_line_num NUMBER := 0;
1603 BEGIN
1604
1605 SELECT nvl(max(distribution_line_number),0)
1606 INTO l_max_dist_line_num
1607 FROM ap_invoice_distributions_all -- Bug 7195488 Moac synonym replaced
1608 WHERE invoice_id = X_invoice_id
1609 AND invoice_line_number = X_line_number;
1610
1611 RETURN(l_max_dist_line_num);
1612
1613 END get_max_dist_line_num;
1614
1615
1616 /*===========================================================================
1617 | FUNCTION - ROUND_BASE_AMTS
1618 |
1619 | DESCRIPTION
1620 | RETURNs the rounded base amount IF there is any. it RETURNs FALSE if
1621 | no rounding amount necessary, otherwise it RETURNs TRUE.
1622 |
1623 | Business Assumption
1624 | 1. Called after all the base amount of each line is populated
1625 | 2. Same exchange rate for all the lines
1626 | 3. It will be called by Primary ledger (AP) or Reporting ledger (MRC)
1627 |
1628 | PARAMETERS
1629 | X_Invoice_Id - Invoice Id
1630 | X_Line_Number - invoice line NUMBER
1631 | X_Reporting_Ledger_Id - For ALC/MRC use only.
1632 | X_ROUND_DIST_ID_LIST - distribution list that can be adjusted
1633 | X_Rounded_Amt - rounded amount
1634 | X_Debug_Info - debug information
1635 | X_Debug_Context - error context
1636 | X_Calling_Sequence - debug usage
1637 |
1638 | KNOWN ISSUES:
1639 |
1640 | NOTES:
1641 |
1642 | MODIFICATION HISTORY
1643 | Date Author Description of Change
1644 | 19-MAY-2008 KPASIKAN modified for the bug 6892789 to get the
1645 | dists that can be adjusted
1646 *===========================================================================*/
1647
1648
1649 FUNCTION round_base_amts(
1650 X_INVOICE_ID IN NUMBER,
1651 X_LINE_NUMBER IN NUMBER,
1652 X_REPORTING_LEDGER_ID IN NUMBER DEFAULT NULL,
1653 X_ROUND_DIST_ID_LIST OUT NOCOPY distribution_id_tab_type,
1654 X_ROUNDED_AMT OUT NOCOPY NUMBER,
1655 X_Debug_Info OUT NOCOPY VARCHAR2,
1656 X_Debug_Context OUT NOCOPY VARCHAR2,
1657 X_Calling_sequence IN VARCHAR2)
1658
1659 RETURN BOOLEAN IS
1660 l_rounded_amt NUMBER := 0;
1661 l_round_dist_id_list distribution_id_tab_type;
1662 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
1663 l_line_base_amount ap_invoice_lines.base_amount%TYPE;
1664 l_line_amount ap_invoice_lines.amount%TYPE;
1665 l_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
1666 l_reporting_currency_code ap_invoices.invoice_currency_code%TYPE;
1667 l_sum_base_amt NUMBER;
1668 l_sum_rpt_base_amt NUMBER;
1669
1670 current_calling_sequence VARCHAR2(2000);
1671 debug_info VARCHAR2(100);
1672
1673 cursor invoice_line_cursor is
1674 -- inv_line_base_amt/rpt_line_base_amt
1675 SELECT decode(x_reporting_ledger_id, null, AIL.base_amount, null),
1676 AIL.amount, -- line_amount
1677 AI.invoice_currency_code, -- invoice_currency_code
1678 ASP.base_currency_code -- base_currency_code
1679 FROM ap_invoices AI, ap_system_parameters ASP, ap_invoice_lines AIL
1680 WHERE AI.invoice_id = X_invoice_id
1681 AND AIL.invoice_id = AI.invoice_id
1682 AND AIL.line_number = X_line_number
1683 AND ASP.org_id = AI.org_id;
1684 BEGIN
1685
1686 current_calling_sequence := 'ROUND_BASE_AMTS - Round_Base_Amt for line';
1687
1688 OPEN invoice_line_cursor;
1689 FETCH invoice_line_cursor
1690 INTO l_line_base_amount,
1691 l_line_amount,
1692 l_invoice_currency_code,
1693 l_base_currency_code;
1694 IF (invoice_line_cursor%NOTFOUND) THEN
1695 RAISE NO_DATA_FOUND;
1696 CLOSE invoice_line_cursor;
1697 END IF;
1698 CLOSE invoice_line_cursor;
1699
1700 IF (X_Reporting_Ledger_Id IS NULL) THEN
1701 --------------------------------------------------------------------
1702 debug_info := 'Round_base_amt Case 1 - Rounding for primary ledger';
1703 --------------------------------------------------------------------
1704
1705 IF (l_invoice_currency_code <> l_base_currency_code) THEN
1706
1707 -- Bug 5469235: Added conditions to eliminate retainage
1708 --Bug 5555622 and recoupment, and the related tax distributions
1709 BEGIN
1710 SELECT SUM(base_amount)
1711 INTO l_sum_base_amt
1712 FROM ap_invoice_distributions AID
1713 WHERE AID.invoice_id = X_INVOICE_ID
1714 AND AID.invoice_line_number = X_LINE_NUMBER
1715 AND AID.line_type_lookup_code NOT IN ('RETAINAGE', 'PREPAY')
1716 AND AID.charge_applicable_to_dist_id NOT IN
1717 (SELECT AID1.invoice_distribution_id
1718 FROM ap_invoice_distributions AID1
1719 WHERE AID1.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')
1720 AND AID1.invoice_id = X_INVOICE_ID
1721 AND AID1.invoice_line_number = X_LINE_NUMBER);
1722
1723 END;
1724
1725 l_rounded_amt := l_line_base_amount - l_sum_base_amt;
1726 ELSE
1727 ---------------------------------------------------------------------
1728 debug_info := 'Round_Base_Amt - same inv currency/base currency';
1729 ---------------------------------------------------------------------
1730 X_ROUNDED_AMT := 0;
1731 X_ROUND_DIST_ID_LIST.delete;
1732 X_debug_context := current_calling_sequence;
1733 X_debug_info := debug_info;
1734 RETURN(FALSE);
1735 END IF; -- end of check currency for primary
1736
1737 ELSE
1738
1739 Null; -- Removed the code here due to MRC obsoletion.
1740
1741 END IF; -- end of check x_reporting_ledger_id
1742
1743 IF (l_rounded_amt <> 0) THEN
1744 ------------------------------------------------------------------------
1745 debug_info := 'Round_Base_Amt - round amt exists and find distribution';
1746 ------------------------------------------------------------------------
1747 BEGIN
1748 --bugfix:4625349
1749 SELECT invoice_distribution_id
1750 BULK COLLECT INTO l_round_dist_id_list
1751 FROM AP_INVOICE_DISTRIBUTIONS aid1
1752 WHERE aid1.invoice_id = X_INVOICE_ID
1753 AND aid1.invoice_line_number = X_LINE_NUMBER
1754 AND nvl(aid1.posted_flag, 'N') = 'N'
1755 AND NVL(aid1.match_status_flag, 'N') IN ('N', 'S')
1756 AND NVL(aid1.reversal_flag, 'N') = 'N' /* Bug 4121330 */
1757 AND LINE_TYPE_LOOKUP_CODE NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV') -- bug 9582952
1758 ORDER BY aid1.base_amount desc;
1759 END;
1760
1761 X_ROUNDED_AMT := l_rounded_amt;
1762 x_round_dist_id_list := l_round_dist_id_list;
1763 X_debug_context := current_calling_sequence;
1764 X_debug_info := debug_info;
1765 RETURN(TRUE);
1766 ELSE
1767 ---------------------------------------------------------------------
1768 debug_info := 'Round_Base_Amt - round_amt is 0 for diff currency';
1769 ---------------------------------------------------------------------
1770
1771 X_ROUNDED_AMT := 0;
1772 x_round_dist_id_list.delete;
1773 X_debug_context := current_calling_sequence;
1774 X_debug_info := debug_info;
1775 RETURN(FALSE);
1776 END IF; -- end of check l_rounded_amt
1777
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 IF (SQLCODE <> -20001) THEN
1781 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1782 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1783 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1784 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1785 'Invoice Id = ' || TO_CHAR(X_Invoice_Id));
1786 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1787 END IF;
1788 debug_info := debug_info || 'Error occurred';
1789 X_debug_context := current_calling_sequence;
1790 X_debug_info := debug_info;
1791 RETURN(FALSE);
1792 END round_base_amts;
1793
1794
1795 /*=============================================================================
1796 | public FUNCTION Discard_Inv_Line
1797 |
1798 | Discard or cancel the invoice line depending on calling mode. If error
1799 | occurs, it return FALSE and error code will be populated. Otherwise,
1800 | It return TRUE.
1801 |
1802 | Parameters
1803 | P_line_rec - Invoice line record
1804 | P_calling_mode - either from DISCARD, CANCEL or UNAPPLY_PREPAY
1805 | p_inv_cancellable - 'Y' if invoice is canellable.
1806 | P_last_updated_by
1807 | P_last_update_login
1808 | P_error_code - Error code indicates why it is not discardable
1809 | P_calling_sequence - For debugging purpose
1810 |
1811 | PROGRAM FLOW
1812 |
1813 | 1. check if line is discardable
1814 | 2. if line is discardable/cancellable and matched - reverse match
1815 | 3. reset the encumberance flag, create account event
1816 | 4. if there is an active distribution - reverse distribution
1817 | 5. populate the out message and set the return value
1818 |
1819 | NOTES
1820 |
1821 | MODIFICATION HISTORY
1822 | Date Author Description of Change
1823 | 03/07/03 sfeng Created
1824 |
1825 *============================================================================*/
1826
1827 Function Discard_Inv_Line(
1828 P_line_rec IN ap_invoice_lines%ROWTYPE,
1829 P_calling_mode IN VARCHAR2,
1830 P_inv_cancellable IN VARCHAR2 DEFAULT NULL,
1831 P_last_updated_by IN NUMBER,
1832 P_last_update_login IN NUMBER,
1833 P_error_code OUT NOCOPY VARCHAR2,
1834 P_token OUT NOCOPY VARCHAR2,
1835 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
1836 IS
1837 TYPE r_global_attr_arr IS VARRAY(1000) of VARCHAR2(150);
1838 l_ok_to_cancel BOOLEAN := FALSE;
1839 l_ok_to_discard BOOLEAN := FALSE;
1840 l_debug_info VARCHAR2(4000);
1841 l_curr_calling_sequence VARCHAR2(4000);
1842
1843 l_po_distribution_id
1844 ap_invoice_distributions.po_distribution_id%TYPE;
1845
1846 l_distribution_count NUMBER := 0;
1847 l_sum_matched_qty NUMBER := 0;
1848 l_sum_matched_amt NUMBER := 0;
1849 l_dist_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
1850 l_matching_basis po_line_locations_all.matching_basis%TYPE;
1851 i NUMBER := 1;
1852 l_max_line_num
1853 ap_invoice_distributions.distribution_line_number%TYPE;
1854 l_matched_uom VARCHAR2(30);
1855 l_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1856 l_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1857 l_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
1858 l_global_attr_category ap_invoice_distributions.global_attribute_category%TYPE;
1859 l_open_gl_date DATE;
1860 l_result_string VARCHAR2(4000);
1861 l_loop_counter BINARY_INTEGER;
1862 l_awt_success VARCHAR2(2000);
1863
1864 l_invoice_validation_status VARCHAR2(100);
1865 l_success BOOLEAN;
1866 l_error_code VARCHAR2(4000);
1867 l_invoice_type_lookup_code AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
1868 l_payment_status_flag AP_INVOICES_ALL.PAYMENT_STATUS_FLAG%TYPE;
1869 l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
1870 l_included_tax_amount AP_INVOICE_LINES_ALL.INCLUDED_TAX_AMOUNT%TYPE;
1871 l_tax_distribution_count NUMBER;
1872
1873 --Contract Payments
1874 l_prepay_dist_info AP_PREPAY_PKG.prepay_dist_tab_type;
1875 l_dummy BOOLEAN;
1876 l_prepay_invoice_id NUMBER;
1877 l_prepay_line_number NUMBER;
1878 l_shipment_amt_billed NUMBER;
1879 l_shipment_qty_billed NUMBER;
1880 l_shipment_amt_recouped NUMBER;
1881 l_shipment_qty_recouped NUMBER;
1882 l_shipment_amt_retained NUMBER;
1883 l_shipment_amt_released NUMBER;
1884 l_shipment_amt_financed NUMBER;
1885 l_shipment_qty_financed NUMBER;
1886 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
1887 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
1888 l_api_name VARCHAR2(50);
1889 l_return_status1 VARCHAR2(4000); --bug14383132: changed the variable name
1890 l_msg_data VARCHAR2(4000);
1891 l_prepay_tax_dists_count NUMBER;
1892
1893 l_manual_tax_lines NUMBER; --Bug9133464
1894 prob_dist_list VARCHAR2(1000):=NULL; --9100425
1895 prob_dist_count NUMBER :=0; --9100425
1896 l_cancel_proactive_flag varchar2(1); --9100425
1897 l_return_status_service VARCHAR2(4000); --Bug14383132
1898 l_msg_count NUMBER; --Bug14383132
1899 l_zx_reverse_dist_gt_count NUMBER; --Bug14383132
1900 l_return_status BOOLEAN := TRUE; --Bug14383132
1901 l_tax_distributions_exist BOOLEAN := FALSE ; --Bug14383132
1902 l_self_assess_tax_dist_exist BOOLEAN := FALSE ; --Bug14383132
1903 l_inv_cancel_date DATE ; --Bug14383132
1904 l_open_gl_date date; --Bug14383132
1905 l_open_gl_period varchar2(100); --Bug14383132
1906 l_rev_dist_cursor_count NUMBER := 0 ; --Bug14383132
1907 l_rev_sa_dist_cursor_count NUMBER := 0 ; --Bug14383132
1908 l_sysdate DATE := sysdate; --Bug14383132
1909
1910 -------------------------------------------------------------
1911 -- Query trying to find the total qty_invoiced by this inv
1912 -- line for one or more po_distributions. Only the base match
1913 -- and the corrections trying to correct this invoice will
1914 -- be considered. For PRICE CORRECTION, quantity_invoiced
1915 -- should not be included. For QUANTITY CORRECTION,
1916 -- corrected_quantity is populated and quantity_invoiced is null.
1917 -- Although at invoice line level, quantity_invoiced is always
1918 -- the same as corrected_quantity.
1919 -------------------------------------------------------------
1920
1921 --Added below cursor for bug#8928639
1922
1923 CURSOR prepay_inv_cur IS
1924 SELECT distinct aid1.invoice_id,aid1.invoice_line_number
1925 FROM ap_invoice_distributions_all AID, --Inv dists
1926 ap_invoice_distributions_all AID1 --prepay dists
1927 WHERE aid.invoice_id = p_line_rec.invoice_id
1928 AND aid.invoice_line_number = p_line_rec.line_number
1929 AND aid1.invoice_distribution_id = aid.prepay_distribution_id;
1930
1931 CURSOR po_dists_cur IS
1932 SELECT aid.po_distribution_id,
1933 aid.matched_uom_lookup_code,
1934 SUM( decode( AID.dist_match_type,
1935 'PRICE_CORRECTION', 0,
1936 'AMOUNT_CORRECTION', 0, /* Amount Based Matching */
1937 'ITEM_TO_SERVICE_PO', 0,
1938 'ITEM_TO_SERVICE_RECEIPT', 0,
1939 NVL( AID.corrected_quantity, 0) +
1940 nvl( AID.quantity_invoiced,0 ) ) ) ,
1941 SUM(NVL(AID.amount, 0)) ,
1942 aid.line_type_lookup_code,
1943 pll.matching_basis,
1944 aid1.invoice_id prepay_invoice_id,
1945 aid1.invoice_line_number prepay_line_number
1946 FROM ap_invoice_distributions_all AID ,
1947 po_line_locations pll,
1948 ap_invoice_distributions_all AID1
1949 WHERE aid.invoice_id = p_line_rec.invoice_id
1950 AND aid.invoice_line_number = p_line_rec.line_number
1951 --Contract Payments: Added the 'PREPAY' to the clause
1952 AND aid.line_type_lookup_code in ('ITEM','ACCRUAL', 'IPV','ERV','PREPAY','RETAINAGE')
1953 AND pll.line_location_id = p_line_rec.po_line_location_id
1954 AND aid1.invoice_distribution_id(+) = aid.prepay_distribution_id
1955 GROUP BY aid1.invoice_id,aid1.invoice_line_number,
1956 aid.line_type_lookup_code,aid.po_distribution_id,pll.matching_basis,aid.matched_uom_lookup_code;
1957
1958
1959 -- Bug 5114543
1960 -- Added to allow discard of invoice lines with allocated charges
1961
1962 CURSOR c_charge_lines(c_invoice_id number,
1963 c_item_line_number number) Is
1964 SELECT ail.*
1965 FROM ap_allocation_rule_lines arl
1966 ,ap_invoice_lines_all ail
1967 WHERE arl.invoice_id = c_invoice_id
1968 AND arl.to_invoice_line_number = c_item_line_number
1969 AND arl.invoice_id = ail.invoice_id
1970 AND arl.chrg_invoice_line_number = ail.line_number
1971 and ail.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
1972 AND exists
1973 (select aid.invoice_line_number
1974 from ap_invoice_distributions_all aid
1975 where aid.invoice_id = ail.invoice_id
1976 and aid.invoice_line_number = ail.line_number);
1977
1978 l_chrg_line_rec ap_invoice_lines_all%rowtype;
1979
1980 -- Bug 5396138 Start
1981 cursor c_recouped_shipment IS
1982 select pll.line_location_id
1983 ,aid.matched_uom_lookup_code
1984 ,sum(nvl(aid.amount,0)) amount
1985 ,sum(decode(AID.dist_match_type,
1986 'PRICE_CORRECTION', 0,
1987 'AMOUNT_CORRECTION', 0,
1988 'ITEM_TO_SERVICE_PO', 0,
1989 'ITEM_TO_SERVICE_RECEIPT', 0,
1990 NVL(AID.corrected_quantity, 0) + NVL(AID.quantity_invoiced,0))) quantity
1991 from ap_invoice_lines_all ail
1992 ,ap_invoice_distributions_all aid
1993 ,po_distributions_all pod
1994 ,po_line_locations_all pll
1995 where ail.invoice_id = p_line_rec.invoice_id
1996 and ail.line_number = p_line_rec.line_number
1997 and ail.invoice_id = aid.invoice_id
1998 and ail.line_number = aid.invoice_line_number
1999 and ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE','PREPAY') --Added PREPAY for bug#9298560
2000 and aid.line_type_lookup_code = 'PREPAY'
2001 and aid.po_distribution_id = pod.po_distribution_id
2002 and pll.line_location_id = pod.line_location_id
2003 group by pll.line_location_id, aid.matched_uom_lookup_code;
2004
2005 l_recoup_dist_rec PO_AP_DIST_REC_TYPE;
2006 l_recoup_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
2007
2008 l_recouped_shipment c_recouped_shipment%rowtype;
2009 -- Bug 5396138 End
2010
2011 -- bug 5572121
2012 cursor dist_debug_cur is
2013 Select *
2014 FROM ap_invoice_distributions_all aid
2015 WHERE aid.invoice_id = p_line_rec.invoice_id;
2016
2017 --bugfix:5638822
2018 l_recouped_amount ap_invoices_all.invoice_amount%type;
2019 l_amount_paid number;
2020 l_error_message varchar2(4000);
2021 l_payment_currency_code ap_invoices_all.payment_currency_code%TYPE;
2022 l_amount_remaining number;
2023
2024 TYPE r_line_number_arr IS TABLE OF ap_invoice_distributions.distribution_line_number%TYPE; /*Bug 9841966*/
2025 TYPE r_ppay_incl_arr IS TABLE OF ap_invoice_lines_all.invoice_includes_prepay_flag%TYPE; /*Bug 9841966*/
2026
2027 --bugfix:5697764
2028 l_tax_line_number r_line_number_arr; --number; /*Bug 9841966: Commented old datatype and changed it to pl/sql array*/
2029 l_unapplied_tax_amount number;
2030 l_unapplied_tax_amt_pay_curr number;
2031 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type;
2032 l_payment_cross_rate_date ap_invoices_all.payment_cross_rate_date%type;
2033 l_payment_cross_rate_type ap_invoices_all.payment_cross_rate_type%type;
2034 l_prepay_included r_ppay_incl_arr;--ap_invoice_lines_all.invoice_includes_prepay_flag%type; /*Bug 9841966: Commented old datatype and changed it to pl/sql array*/
2035
2036 --bug 8361741
2037 l_itm_dist_count NUMBER;
2038 l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
2039 l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
2040
2041 -- Bug 8623061 - Start
2042 l_award_id ap_invoice_distributions_all.award_id%TYPE;
2043
2044 cursor c_distribution_list_cur is
2045 SELECT *
2046 FROM ap_invoice_distributions_all aid
2047 WHERE aid.parent_reversal_id IS NOT NULL
2048 AND aid.invoice_id = p_line_rec.invoice_id
2049 AND aid.invoice_line_number = p_line_rec.line_number
2050 AND aid.reversal_flag = 'Y'
2051 AND NOT EXISTS (
2052 SELECT invoice_distribution_id
2053 FROM gms_award_distributions gad
2054 WHERE aid.invoice_distribution_id = gad.invoice_distribution_id
2055 );
2056 -- Bug 8623061 - End
2057
2058 -- Bug14383132: Start
2059
2060 cursor c_reverse_tax_dist_1(c_invoice_id number,
2061 c_item_line_number number) is
2062 select nvl(item_dist.accounting_date,
2063 zx_dist.gl_date) accounting_date,
2064 'N' accrual_posted_flag,
2065 'U' assets_addition_flag,
2066 tax_dist.assets_tracking_flag assets_tracking_flag, --Bug14772756
2067 'N' cash_posted_flag,
2068 AP_INVOICE_LINES_PKG.get_max_dist_line_num(
2069 p_line_rec.invoice_id,
2070 tax_dist.invoice_line_number)+1
2071 distribution_line_number,
2072 tax_dist.dist_code_combination_id dist_code_combination_id,
2073 tax_dist.invoice_id invoice_id,
2074 l_user_id last_updated_by,
2075 l_sysdate last_update_date,
2076 tax_dist.line_type_lookup_code line_type_lookup_code,
2077 ap_utilities_pkg.get_gl_period_name(
2078 zx_dist.gl_date,
2079 tax_dist.org_id) period_name,
2080 tax_dist.set_of_books_id set_of_books_id,
2081 (-tax_dist.amount) amount,
2082 (-tax_dist.base_amount) base_amount,
2083 l_user_id created_by,
2084 l_sysdate creation_date,
2085 tax_dist.description description,
2086 NULL final_match_flag,
2087 tax_dist.income_tax_region income_tax_region,
2088 l_user_id last_update_login,
2089 NULL match_status_flag,
2090 'N' posted_flag,
2091 tax_dist.po_distribution_id po_distribution_id,
2092 NULL program_application_id,
2093 NULL program_id,
2094 NULL program_update_date,
2095 NULL quantity_invoiced,
2096 NULL request_id,
2097 'Y' reversal_flag,
2098 tax_dist.type_1099 type_1099,
2099 tax_dist.unit_price unit_price,
2100 DECODE(tax_dist.encumbered_flag,
2101 'R', 'R', 'N') encumbered_flag,
2102 NULL stat_amount,
2103 tax_dist.attribute1 attribute1,
2104 tax_dist.attribute10 attribute10,
2105 tax_dist.attribute11 attribute11,
2106 tax_dist.attribute12 attribute12,
2107 tax_dist.attribute13 attribute13,
2108 tax_dist.attribute14 attribute14,
2109 tax_dist.attribute15 attribute15,
2110 tax_dist.attribute2 attribute2,
2111 tax_dist.attribute3 attribute3,
2112 tax_dist.attribute4 attribute4,
2113 tax_dist.attribute5 attribute5,
2114 tax_dist.attribute6 attribute6,
2115 tax_dist.attribute7 attribute7,
2116 tax_dist.attribute8 attribute8,
2117 tax_dist.attribute9 attribute9,
2118 tax_dist.attribute_category attribute_category,
2119 decode(zx_dist.recoverable_flag,
2120 'Y', NULL,
2121 tax_dist.expenditure_item_date) expenditure_item_date,
2122 decode(zx_dist.recoverable_flag,
2123 'Y', NULL,
2124 tax_dist.expenditure_organization_id) expenditure_organization_id,
2125 decode(zx_dist.recoverable_flag,
2126 'Y', NULL,
2127 tax_dist.expenditure_type) expenditure_type,
2128 tax_dist.parent_invoice_id parent_invoice_id,
2129 decode(zx_dist.recoverable_flag,
2130 'Y', 'E',
2131 item_dist.pa_addition_flag) pa_addition_flag,
2132 decode(zx_dist.recoverable_flag,
2133 'Y', NULL,
2134 tax_dist.pa_quantity) pa_quantity,
2135 NULL prepay_amount_remaining,
2136 decode(zx_dist.recoverable_flag,
2137 'Y', NULL,
2138 tax_dist.project_accounting_context) project_accounting_context,
2139 decode(zx_dist.recoverable_flag,
2140 'Y', NULL,
2141 tax_dist.project_id) project_id,
2142 decode(zx_dist.recoverable_flag,
2143 'Y', NULL,
2144 tax_dist.task_id) task_id,
2145 NULL packet_id,
2146 'N' awt_flag,
2147 tax_dist.awt_group_id awt_group_id,
2148 NULL awt_tax_rate_id,
2149 NULL awt_gross_amount,
2150 NULL awt_invoice_id,
2151 NULL awt_origin_group_id,
2152 NULL reference_1,
2153 NULL reference_2,
2154 tax_dist.org_id org_id,
2155 NULL awt_invoice_payment_id,
2156 tax_dist.global_attribute_category global_attribute_category,
2157 tax_dist.global_attribute1 global_attribute1,
2158 tax_dist.global_attribute2 global_attribute2,
2159 tax_dist.global_attribute3 global_attribute3,
2160 tax_dist.global_attribute4 global_attribute4,
2161 tax_dist.global_attribute5 global_attribute5,
2162 tax_dist.global_attribute6 global_attribute6,
2163 tax_dist.global_attribute7 global_attribute7,
2164 tax_dist.global_attribute8 global_attribute8,
2165 tax_dist.global_attribute9 global_attribute9,
2166 tax_dist.global_attribute10 global_attribute10,
2167 tax_dist.global_attribute11 global_attribute11,
2168 tax_dist.global_attribute12 global_attribute12,
2169 tax_dist.global_attribute13 global_attribute13,
2170 tax_dist.global_attribute14 global_attribute14,
2171 tax_dist.global_attribute15 global_attribute15,
2172 tax_dist.global_attribute16 global_attribute16,
2173 tax_dist.global_attribute17 global_attribute17,
2174 tax_dist.global_attribute18 global_attribute18,
2175 tax_dist.global_attribute19 global_attribute19,
2176 tax_dist.global_attribute20 global_attribute20,
2177 NULL receipt_verified_flag,
2178 NULL receipt_required_flag,
2179 NULL receipt_missing_flag,
2180 NULL justification,
2181 NULL expense_group,
2182 NULL start_expense_date,
2183 NULL end_expense_date,
2184 NULL receipt_currency_code,
2185 NULL receipt_conversion_rate,
2186 NULL receipt_currency_amount,
2187 NULL daily_amount,
2188 NULL web_parameter_id,
2189 NULL adjustment_reason,
2190 decode(zx_dist.recoverable_flag,
2191 'Y', NULL,
2192 tax_dist.award_id) award_id,
2193 NULL credit_card_trx_id,
2194 tax_dist.dist_match_type dist_match_type,
2195 tax_dist.rcv_transaction_id rcv_transaction_id,
2196 ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
2197 tax_dist.invoice_distribution_id parent_reversal_id,
2198 tax_dist.tax_recoverable_flag tax_recoverable_flag,
2199 NULL merchant_document_number,
2200 NULL merchant_name,
2201 NULL merchant_reference,
2202 NULL merchant_tax_reg_number,
2203 NULL merchant_taxpayer_id,
2204 NULL country_of_supply,
2205 NULL matched_uom_lookup_code,
2206 NULL gms_burdenable_raw_cost,
2207 NULL accounting_event_id,
2208 tax_dist.prepay_distribution_id prepay_distribution_id,
2209 NULL upgrade_posted_amt,
2210 NULL upgrade_base_posted_amt,
2211 'N' inventory_transfer_status,
2212 NULL company_prepaid_invoice_id,
2213 NULL cc_reversal_flag,
2214 NULL awt_withheld_amt,
2215 NULL pa_cmt_xface_flag,
2216 decode(p_calling_mode,'CANCEL INVOICE',
2217 DECODE(tax_dist.prepay_distribution_id,NULL, 'Y',NULL),Null) cancellation_flag,
2218 tax_dist.invoice_line_number invoice_line_number,
2219 tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
2220 tax_dist.rounding_amt rounding_amt,
2221 zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
2222 NULL corrected_quantity,
2223 DECODE( tax_dist.related_id, NULL, NULL,
2224 tax_dist.invoice_distribution_id,
2225 ap_invoice_distributions_s.CURRVAL, NULL) related_id,
2226
2227 NULL asset_book_type_code,
2228 NULL asset_category_id,
2229 tax_dist.distribution_class distribution_class,
2230 tax_dist.tax_code_id tax_code_id,
2231 tax_dist.intended_use intended_use,
2232 zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
2233 zx_dist.rec_nrec_rate rec_nrec_rate,
2234 zx_dist.recovery_rate_id recovery_rate_id,
2235 zx_dist.recovery_type_code recovery_type_code,
2236 NULL withholding_tax_code_id,
2237 NULL taxable_amount,
2238 NULL taxable_base_amount,
2239 tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
2240 tax_dist.summary_tax_line_id summary_tax_line_id,
2241 'N' rcv_charge_addition_flag,
2242 (-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount,
2243 tax_dist.pay_awt_group_id pay_awt_group_id
2244 from ap_invoice_distributions_all tax_dist,
2245 ap_invoice_distributions_all item_dist,
2246 zx_rec_nrec_dist zx_dist
2247 where tax_dist.invoice_id = c_invoice_id
2248 and tax_dist.invoice_id = zx_dist.trx_id
2249 and zx_dist.application_id = 200
2250 and zx_dist.entity_code = 'AP_INVOICES'
2251 and zx_dist.event_class_code IN ('STANDARD INVOICES',
2252 'PREPAYMENT INVOICES',
2253 'EXPENSE REPORTS')
2254 and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TERV', 'TRV')
2255 and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
2256 and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
2257 and item_dist.invoice_id = c_invoice_id
2258 and item_dist.invoice_line_number = c_item_line_number
2259 and item_dist.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
2260 and zx_dist.reverse_flag = 'Y'
2261 and not exists(select detail_tax_dist_id
2262 from ap_invoice_distributions aid
2263 where aid.invoice_id = c_invoice_id
2264 and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id);
2265
2266 l_reverse_tax_dist c_reverse_tax_dist_1%rowtype ;
2267
2268 cursor c_rev_self_assess_tax_dist_1(c_invoice_id number,
2269 c_item_line_number number) is
2270 select
2271 nvl(item_dist.accounting_date,
2272 zx_dist.gl_date) accounting_date,
2273 'N' accrual_posted_flag,
2274 'U' assets_addition_flag,
2275 tax_dist.assets_tracking_flag assets_tracking_flag, --Bug14772756
2276 'N' cash_posted_flag,
2277 AP_ETAX_UTILITY_PKG.Get_Max_Dist_Num_Self(
2278 p_line_rec.invoice_id,
2279 tax_dist.invoice_line_number)+1
2280 distribution_line_number,
2281 tax_dist.dist_code_combination_id dist_code_combination_id,
2282 tax_dist.invoice_id invoice_id,
2283 l_user_id last_updated_by,
2284 l_sysdate last_update_date,
2285 tax_dist.line_type_lookup_code line_type_lookup_code,
2286 tax_dist.period_name period_name,
2287 tax_dist.set_of_books_id set_of_books_id,
2288 (-tax_dist.amount) amount,
2289 (-tax_dist.base_amount) base_amount,
2290 l_user_id created_by,
2291 l_sysdate creation_date,
2292 tax_dist.description description,
2293 NULL final_match_flag,
2294 tax_dist.income_tax_region income_tax_region,
2295 l_user_id last_update_login,
2296 NULL match_status_flag,
2297 'N' posted_flag,
2298 tax_dist.po_distribution_id po_distribution_id,
2299 NULL program_application_id,
2300 NULL program_id,
2301 NULL program_update_date,
2302 NULL quantity_invoiced,
2303 NULL request_id,
2304 'Y' reversal_flag,
2305 tax_dist.type_1099 type_1099,
2306 tax_dist.unit_price unit_price,
2307 DECODE(tax_dist.encumbered_flag,
2308 'R', 'R', 'N') encumbered_flag,
2309 NULL stat_amount,
2310 tax_dist.attribute1 attribute1,
2311 tax_dist.attribute10 attribute10,
2312 tax_dist.attribute11 attribute11,
2313 tax_dist.attribute12 attribute12,
2314 tax_dist.attribute13 attribute13,
2315 tax_dist.attribute14 attribute14,
2316 tax_dist.attribute15 attribute15,
2317 tax_dist.attribute2 attribute2,
2318 tax_dist.attribute3 attribute3,
2319 tax_dist.attribute4 attribute4,
2320 tax_dist.attribute5 attribute5,
2321 tax_dist.attribute6 attribute6,
2322 tax_dist.attribute7 attribute7,
2323 tax_dist.attribute8 attribute8,
2324 tax_dist.attribute9 attribute9,
2325 tax_dist.attribute_category attribute_category,
2326 decode(zx_dist.recoverable_flag,
2327 'Y', NULL,
2328 tax_dist.expenditure_item_date) expenditure_item_date,
2329 decode(zx_dist.recoverable_flag,
2330 'Y', NULL,
2331 tax_dist.expenditure_organization_id) expenditure_organization_id,
2332 decode(zx_dist.recoverable_flag,
2333 'Y', NULL,
2334 tax_dist.expenditure_type) expenditure_type,
2335 tax_dist.parent_invoice_id parent_invoice_id,
2336 decode(zx_dist.recoverable_flag,
2337 'Y', 'E',
2338 item_dist.pa_addition_flag) pa_addition_flag,
2339 decode(zx_dist.recoverable_flag,
2340 'Y', NULL,
2341 tax_dist.pa_quantity) pa_quantity,
2342 NULL prepay_amount_remaining,
2343 decode(zx_dist.recoverable_flag,
2344 'Y', NULL,
2345 tax_dist.project_accounting_context) project_accounting_context,
2346 decode(zx_dist.recoverable_flag,
2347 'Y', NULL,
2348 tax_dist.project_id) project_id,
2349 decode(zx_dist.recoverable_flag,
2350 'Y', NULL,
2351 tax_dist.task_id) task_id,
2352 NULL packet_id,
2353 'N' awt_flag,
2354 tax_dist.awt_group_id awt_group_id,
2355 NULL awt_tax_rate_id,
2356 NULL awt_gross_amount,
2357 NULL awt_invoice_id,
2358 NULL awt_origin_group_id,
2359 NULL reference_1,
2360 NULL reference_2,
2361 tax_dist.org_id org_id,
2362 NULL awt_invoice_payment_id,
2363 tax_dist.global_attribute_category global_attribute_category,
2364 tax_dist.global_attribute1 global_attribute1,
2365 tax_dist.global_attribute2 global_attribute2,
2366 tax_dist.global_attribute3 global_attribute3,
2367 tax_dist.global_attribute4 global_attribute4,
2368 tax_dist.global_attribute5 global_attribute5,
2369 tax_dist.global_attribute6 global_attribute6,
2370 tax_dist.global_attribute7 global_attribute7,
2371 tax_dist.global_attribute8 global_attribute8,
2372 tax_dist.global_attribute9 global_attribute9,
2373 tax_dist.global_attribute10 global_attribute10,
2374 tax_dist.global_attribute11 global_attribute11,
2375 tax_dist.global_attribute12 global_attribute12,
2376 tax_dist.global_attribute13 global_attribute13,
2377 tax_dist.global_attribute14 global_attribute14,
2378 tax_dist.global_attribute15 global_attribute15,
2379 tax_dist.global_attribute16 global_attribute16,
2380 tax_dist.global_attribute17 global_attribute17,
2381 tax_dist.global_attribute18 global_attribute18,
2382 tax_dist.global_attribute19 global_attribute19,
2383 tax_dist.global_attribute20 global_attribute20,
2384 NULL receipt_verified_flag,
2385 NULL receipt_required_flag,
2386 NULL receipt_missing_flag,
2387 NULL justification,
2388 NULL expense_group,
2389 NULL start_expense_date,
2390 NULL end_expense_date,
2391 NULL receipt_currency_code,
2392 NULL receipt_conversion_rate,
2393 NULL receipt_currency_amount,
2394 NULL daily_amount,
2395 NULL web_parameter_id,
2396 NULL adjustment_reason,
2397 decode(zx_dist.recoverable_flag,
2398 'Y', NULL,
2399 tax_dist.award_id) award_id,
2400 NULL credit_card_trx_id,
2401 tax_dist.dist_match_type dist_match_type,
2402 tax_dist.rcv_transaction_id rcv_transaction_id,
2403 ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
2404 tax_dist.invoice_distribution_id parent_reversal_id,
2405 tax_dist.tax_recoverable_flag tax_recoverable_flag,
2406 NULL merchant_document_number,
2407 NULL merchant_name,
2408 NULL merchant_reference,
2409 NULL merchant_tax_reg_number,
2410 NULL merchant_taxpayer_id,
2411 NULL country_of_supply,
2412 NULL matched_uom_lookup_code,
2413 NULL gms_burdenable_raw_cost,
2414 NULL accounting_event_id,
2415 tax_dist.prepay_distribution_id prepay_distribution_id,
2416 NULL upgrade_posted_amt,
2417 NULL upgrade_base_posted_amt,
2418 'N' inventory_transfer_status,
2419 NULL company_prepaid_invoice_id,
2420 NULL cc_reversal_flag,
2421 NULL awt_withheld_amt,
2422 NULL pa_cmt_xface_flag,
2423 decode(p_calling_mode,'CANCEL INVOICE',
2424 DECODE(tax_dist.prepay_distribution_id,NULL,'Y',NULL),Null) cancellation_flag,
2425 tax_dist.invoice_line_number invoice_line_number,
2426 tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
2427 tax_dist.rounding_amt rounding_amt,
2428 zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
2429 NULL corrected_quantity,
2430 DECODE( tax_dist.related_id, NULL, NULL,
2431 tax_dist.invoice_distribution_id,
2432 ap_invoice_distributions_s.CURRVAL, NULL) related_id,
2433
2434 NULL asset_book_type_code,
2435 NULL asset_category_id,
2436 tax_dist.distribution_class distribution_class,
2437 tax_dist.tax_code_id tax_code_id,
2438 tax_dist.intended_use intended_use,
2439 zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
2440 zx_dist.rec_nrec_rate rec_nrec_rate,
2441 zx_dist.recovery_rate_id recovery_rate_id,
2442 zx_dist.recovery_type_code recovery_type_code,
2443 NULL withholding_tax_code_id,
2444 NULL taxable_amount,
2445 NULL taxable_base_amount,
2446 tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
2447 tax_dist.summary_tax_line_id summary_tax_line_id,
2448 'N' rcv_charge_addition_flag,
2449 zx_dist.self_assessed_flag self_assessed_flag,
2450 tax_dist.self_assessed_tax_liab_ccid self_assessed_tax_liab_ccid,
2451 (-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount
2452 from ap_self_assessed_tax_dist_all tax_dist,
2453 ap_invoice_distributions_all item_dist,
2454 zx_rec_nrec_dist zx_dist
2455 where tax_dist.invoice_id = c_invoice_id
2456 and tax_dist.invoice_id = zx_dist.trx_id
2457 and zx_dist.application_id = 200
2458 and zx_dist.entity_code = 'AP_INVOICES'
2459 and zx_dist.event_class_code IN ('STANDARD INVOICES',
2460 'PREPAYMENT INVOICES',
2461 'EXPENSE REPORTS')
2462 and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
2463 and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
2464 and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
2465 and item_dist.invoice_id = c_invoice_id
2466 and item_dist.invoice_line_number = c_item_line_number
2467 and item_dist.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
2468 and zx_dist.reverse_flag = 'Y'
2469
2470 AND NOT EXISTS
2471 (SELECT aid.detail_tax_dist_id
2472 FROM ap_self_assessed_tax_dist_all aid
2473 WHERE aid.invoice_id = c_invoice_id
2474 AND aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
2475 AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
2476 ;
2477
2478 l_self_assess_rev_tax_dist_1 c_rev_self_assess_tax_dist_1%rowtype ;
2479
2480 -- Bug14383132: End
2481
2482 BEGIN
2483
2484 SAVEPOINT CANCEL_CHECK; --9100425
2485 fnd_profile.get('AP_ENHANCED_DEBUGGING',l_cancel_proactive_flag); --9100425
2486
2487 l_shipment_amt_billed := 0;
2488 l_shipment_qty_billed := 0;
2489 l_shipment_amt_recouped := 0;
2490 l_shipment_qty_recouped := 0;
2491 l_shipment_amt_retained := 0;
2492 l_shipment_amt_released := 0;
2493 l_prepay_tax_dists_count := 0;
2494 l_shipment_amt_financed := 0;
2495 l_shipment_qty_financed := 0;
2496 l_reverse_dist_count := 0;
2497 l_sa_reverse_dist_count := 0;
2498
2499
2500 l_api_name := 'Discard_Inv_Line';
2501
2502 l_curr_calling_sequence := 'AP_INVOICE_LINES_PKG.Discard_Inv_Line <-' ||
2503 P_calling_sequence;
2504
2505 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2506 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_LINES_PKG.Discard_Inv_Line(+)');
2507 END IF;
2508
2509 -- Added additional columns for bug 8361741
2510 SELECT ai.invoice_type_lookup_code,
2511 ai.payment_status_flag,
2512 ai.invoice_amount,
2513 ai.payment_currency_code,
2514 ai.invoice_currency_code,
2515 ai.payment_cross_rate_date,
2516 ai.payment_cross_rate_type,
2517 asp.base_currency_code,
2518 ai.invoice_id,
2519 ai.invoice_num,
2520 ai.org_id,
2521 ai.invoice_amount,
2522 ai.base_amount,
2523 ai.exchange_rate,
2524 ai.invoice_currency_code,
2525 ai.invoice_type_lookup_code,
2526 ai.exchange_date,
2527 ai.exchange_rate_type,
2528 ai.vendor_id,
2529 ai.invoice_date,
2530 ai.disc_is_inv_less_tax_flag,
2531 ai.exclude_freight_from_discount
2532 INTO l_invoice_type_lookup_code,
2533 l_payment_status_flag,
2534 l_invoice_amount,
2535 l_payment_currency_code,
2536 l_invoice_currency_code,
2537 l_payment_cross_rate_date,
2538 l_payment_cross_rate_type,
2539 l_base_currency_code,
2540 l_invoice_rec.invoice_id,
2541 l_invoice_rec.invoice_num,
2542 l_invoice_rec.org_id,
2543 l_invoice_rec.invoice_amount,
2544 l_invoice_rec.base_amount,
2545 l_invoice_rec.exchange_rate,
2546 l_invoice_rec.invoice_currency_code,
2547 l_invoice_rec.invoice_type_lookup_code,
2548 l_invoice_rec.exchange_date,
2549 l_invoice_rec.exchange_rate_type,
2550 l_invoice_rec.vendor_id,
2551 l_invoice_rec.invoice_date,
2552 l_invoice_rec.disc_is_inv_less_tax_flag,
2553 l_invoice_rec.exclude_freight_from_discount
2554 FROM ap_invoices ai,
2555 ap_system_parameters_all asp
2556 WHERE invoice_id = p_line_rec.invoice_id
2557 AND ai.org_id = asp.org_id;
2558
2559 /*-----------------------------------------------------------------+
2560 | Step 0. If invoice line is AWT, undo withholding before |
2561 | any other discard operation |
2562 +-----------------------------------------------------------------*/
2563
2564 l_debug_info := 'Undo AWT if it is an AWT line';
2565
2566 IF ( NVL( P_calling_mode, 'DISCARD' ) = 'DISCARD' AND
2567 p_line_rec.line_type_lookup_code = 'AWT' AND
2568 p_line_rec.line_source = 'AUTO WITHHOLDING') THEN -- bug 7568031
2569
2570 -- One AWT dist corresponds to one Invoice Line.
2571 AP_WITHHOLDING_PKG.Ap_Undo_Withholding (
2572 P_Parent_Id => P_line_rec.invoice_id,
2573 P_Calling_Module => 'REVERSE DIST',
2574 P_Awt_Date => P_line_rec.accounting_date,
2575 P_New_Invoice_Payment_Id => NULL,
2576 P_Last_Updated_By => P_last_updated_by,
2577 P_Last_Update_Login => P_last_update_login,
2578 P_Program_Application_Id => NULL,
2579 P_Program_Id => NULL,
2580 P_Request_Id => NULL,
2581 P_Awt_Success => l_awt_success,
2582 P_Inv_Line_No => P_line_rec.line_number,
2583 P_dist_Line_No => NULL,
2584 P_New_Invoice_Id => NULL,
2585 P_New_dist_Line_No => NULL);
2586
2587 IF ( l_awt_success = 'SUCCESS' ) THEN
2588 IF nvl(l_cancel_proactive_flag,'N') ='C' THEN --if proactive cancel profile set
2589 --9100425
2590 For I in(select invoice_distribution_id
2591 from ap_invoice_distributions aid1
2592 where aid1.invoice_id=P_line_rec.invoice_id
2593 and aid1.line_type_lookup_code='AWT'
2594 and aid1.awt_flag='A'
2595 and aid1.parent_reversal_id is null --original dist
2596 --for original dists there is no reversal dist created
2597 and ( not exists (select 1 from ap_invoice_distributions aid2
2598 where aid1.invoice_id=aid2.invoice_id
2599 and aid2.invoice_id=P_line_rec.invoice_id
2600 and aid2.invoice_line_number=aid1.invoice_line_number
2601 and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2602 --the reversal dist does not reverse the amount correctly
2603 or exists (select 1 from ap_invoice_distributions aid2
2604 where aid1.invoice_id=aid2.invoice_id
2605 and aid2.invoice_id=P_line_rec.invoice_id
2606 and aid2.invoice_line_number=aid1.invoice_line_number
2607 and aid2.parent_reversal_id =aid1.invoice_distribution_id
2608 and -1 * aid2.amount <> aid1.amount))) --dists updated today
2609
2610 LOOP
2611 prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
2612 prob_dist_count:=prob_dist_count+1;
2613
2614 end loop;
2615
2616 IF prob_dist_count > 0 then
2617
2618 p_error_code :='AP_INV_DIS_CAN_FAIL';
2619 P_token:=prob_dist_list;
2620 ROLLBACK TO SAVEPOINT CANCEL_CHECK;
2621 RETURN(FALSE);
2622
2623 ELSE
2624 RETURN(TRUE);
2625 END IF;
2626 ELSE --if cancel proactive profile is not set
2627 RETURN(TRUE);
2628 END IF; --cancel proactive profile
2629 --9100425
2630
2631 ELSE
2632 p_error_code := l_awt_success;
2633 RETURN(FALSE);
2634 END IF; --l_awt_success
2635
2636 END IF;
2637
2638 /*-----------------------------------------------------------------+
2639 | Check if the Line is Discardable or if the Invoice is |
2640 | is Cancelable. |
2641 +-----------------------------------------------------------------*/
2642
2643 IF ( NVL( P_calling_mode, 'DISCARD' ) IN ( 'DISCARD', 'UNAPPLY_PREPAY' ) ) THEN
2644 IF ( NVL(P_line_rec.discarded_flag, 'N') <> 'Y' ) THEN
2645 /* Base Line ARU Issue */
2646 l_ok_to_discard := AP_INVOICE_LINES_UTILITY_PKG.Is_Line_Discardable(
2647 P_line_rec,
2648 l_error_code,
2649 l_curr_calling_sequence );
2650
2651 IF ( l_ok_to_discard = FALSE ) THEN
2652 p_error_code := l_error_code;
2653 RETURN (FALSE);
2654
2655 --Bug9133464 START
2656 ELSE
2657
2658 SELECT COUNT(1)
2659 INTO l_manual_tax_lines
2660 FROM ap_invoice_lines_all ail
2661 WHERE invoice_id = P_line_rec.invoice_id
2662 AND line_type_lookup_code = 'TAX'
2663 AND summary_tax_line_id IS NULL;
2664
2665 IF l_manual_tax_lines > 0 THEN
2666
2667 l_success := ap_etax_pkg.calling_etax(
2668 p_invoice_id => p_line_rec.invoice_id,
2669 p_calling_mode => 'CALCULATE',
2670 p_all_error_messages => 'N',
2671 p_error_code => l_error_code,
2672 p_calling_sequence => l_curr_calling_sequence);
2673
2674 IF (NOT l_success) THEN
2675 p_error_code := 'AP_ETX_DISC_LINE_CALC_TAX_FAIL';
2676 p_token := l_error_code;
2677 RETURN FALSE;
2678 END IF;
2679
2680 END IF;
2681 --Bug9133464 END
2682 END IF;
2683 ELSE
2684
2685 l_ok_to_discard := FALSE;
2686 p_error_code := 'AP_LINE_ALREADY_DISCARDED';
2687 RETURN (FALSE);
2688
2689 END IF;
2690
2691 END IF;
2692
2693 IF ( P_calling_mode = 'CANCEL' ) THEN
2694 IF ( p_inv_cancellable is NOT NULL and
2695 p_inv_cancellable = 'Y' and
2696 NVL(p_line_rec.cancelled_flag, 'N') <> 'Y' ) THEN
2697 l_ok_to_cancel := TRUE;
2698
2699 ELSIF ( p_inv_cancellable is NULL ) THEN
2700 l_ok_to_cancel := AP_CANCEL_PKG.Is_Invoice_Cancellable(
2701 P_invoice_id => p_line_rec.invoice_id,
2702 P_error_code => l_error_code,
2703 P_debug_info => l_debug_info,
2704 P_calling_sequence => l_curr_calling_sequence );
2705
2706 IF ( l_ok_to_cancel = FALSE ) THEN
2707 p_error_code := l_error_code;
2708 RETURN (FALSE);
2709 END IF;
2710 ELSE
2711
2712 p_error_code := 'AP_INV_CANCELLED';
2713 l_ok_to_cancel := FALSE;
2714 RETURN(FALSE);
2715
2716 END IF;
2717
2718 -- Block to handle generate distributions for the Item line
2719 -- where prepay application and prepay unapplication has distributions
2720 -- and user tried to cancle the invoice bug 8361741
2721
2722 IF ( l_ok_to_cancel = TRUE AND l_invoice_type_lookup_code <> 'PREPAYMENT' ) THEN
2723
2724 l_itm_dist_count := 0;
2725 -- query to get the Distributions for that standard invoice
2726 SELECT COUNT(*) INTO l_itm_dist_count
2727 FROM ap_invoice_distributions_all aid
2728 WHERE aid.invoice_id = p_line_rec.invoice_id
2729 AND aid.prepay_distribution_id is not null
2730 AND NOT EXISTS (SELECT 1
2731 FROM ap_invoice_distributions_all item
2732 WHERE item.invoice_id = p_line_rec.invoice_id
2733 AND item.prepay_distribution_id is null)
2734 AND ROWNUM =1;
2735
2736 IF (l_itm_dist_count = 1) THEN
2737
2738 AP_APPROVAL_PKG.Generate_Distributions
2739 (p_invoice_rec => l_invoice_rec,
2740 p_base_currency_code => l_base_currency_code,
2741 p_inv_batch_id => NULL,
2742 p_run_option => NULL,
2743 p_calling_sequence => l_curr_calling_sequence,
2744 x_error_code => l_error_code,
2745 p_calling_mode => 'APPROVE' );
2746 END IF;
2747
2748 END IF; -- l_ok_to_cancel = TRUE 8361741 ends
2749
2750 END IF; -- end of check P_calling_mode
2751 -- Bug 5114543 Start
2752 IF (l_ok_to_discard = TRUE) THEN
2753
2754 IF p_line_rec.line_type_lookup_code = 'ITEM' THEN
2755
2756 ----------------------------------------------------------------------------
2757 l_debug_info := 'Update allocation rule to pending on related charge lines';
2758 Print (l_api_name, l_debug_info);
2759 ----------------------------------------------------------------------------
2760
2761 update ap_allocation_rules ar
2762 set status = 'PENDING'
2763 where ar.invoice_id = p_line_rec.invoice_id
2764 and exists (select arl.chrg_invoice_line_number
2765 from ap_allocation_rule_lines arl
2766 where arl.invoice_id = p_line_rec.invoice_id
2767 and arl.to_invoice_line_number = p_line_rec.line_number
2768 and arl.chrg_invoice_line_number = ar.chrg_invoice_line_number);
2769
2770 ----------------------------------------------------------------------------
2771 l_debug_info := 'Reset generate distributions flag on related charge lines';
2772 Print (l_api_name, l_debug_info);
2773 ----------------------------------------------------------------------------
2774
2775 update ap_invoice_lines_all ail
2776 set generate_dists = 'Y'
2777 where ail.invoice_id = p_line_rec.invoice_id
2778 and exists (select arl.chrg_invoice_line_number
2779 from ap_allocation_rule_lines arl
2780 where arl.invoice_id = p_line_rec.invoice_id
2781 and arl.to_invoice_line_number = p_line_rec.line_number
2782 and arl.chrg_invoice_line_number = ail.line_number);
2783
2784 DELETE zx_reverse_dist_gt; --Bug14383132
2785
2786 open c_charge_lines (p_line_rec.invoice_id,
2787 p_line_rec.line_number);
2788 loop
2789 fetch c_charge_lines
2790 into l_chrg_line_rec;
2791 exit when c_charge_lines%notfound;
2792
2793 ----------------------------------------------------------------------------
2794 l_debug_info := 'Reverse charge distributions';
2795 Print (l_api_name,l_debug_info);
2796 ----------------------------------------------------------------------------
2797
2798 if not ap_invoice_lines_pkg.reverse_charge_distributions
2799 (p_inv_line_rec => l_chrg_line_rec
2800 ,p_calling_mode => p_calling_mode
2801 ,x_error_code => l_error_code
2802 ,x_debug_info => l_debug_info
2803 ,p_calling_sequence => l_curr_calling_sequence) then
2804
2805 l_ok_to_discard := FALSE;
2806 p_error_code := 'AP_REV_CHRG_DIST_FAIL';
2807
2808 RETURN FALSE;
2809
2810 end if;
2811
2812 -- 9100425
2813
2814 IF nvl(l_cancel_proactive_flag,'N') ='C' and NVL(P_calling_mode,'DISCARD') = 'DISCARD' THEN
2815 --proactive cancel check profile on
2816
2817 For I in(select invoice_distribution_id
2818 from ap_invoice_distributions aid1
2819 where aid1.invoice_id=l_chrg_line_rec.invoice_id
2820 and aid1.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec. line_number
2821 and aid1.parent_reversal_id is null --original dist
2822 and ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
2823 and prepay_distribution_id IS NULL) OR
2824 prepay_distribution_id IS NOT NULL
2825 )
2826 and (line_type_lookup_code <> 'AWT' OR
2827 (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
2828 --for original dists there is no reversal dist created
2829 and ( not exists (select 1 from ap_invoice_distributions aid2
2830 where aid1.invoice_id=aid2.invoice_id
2831 and aid2.invoice_id=l_chrg_line_rec.invoice_id
2832 and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
2833 and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2834 --the reversal dist does not reverse the amount correctly
2835 or exists (select 1 from ap_invoice_distributions aid2
2836 where aid1.invoice_id=aid2.invoice_id
2837 and aid2.invoice_id=l_chrg_line_rec.invoice_id
2838 and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
2839 and aid2.parent_reversal_id =aid1.invoice_distribution_id
2840 and -1 * aid2.amount <> aid1.amount)) )
2841 LOOP
2842 prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
2843 prob_dist_count:=prob_dist_count+1;
2844
2845 end loop;
2846 END IF; --proactive cancel check profile on
2847 --9100425
2848 end loop;
2849
2850 --Bug14383132 : Start
2851
2852 close c_charge_lines;
2853
2854 IF (l_reverse_dist_count>0 OR l_sa_reverse_dist_count>0) THEN
2855 ---------------------------------------------------------------
2856 l_debug_info := 'Calling ZX_API_PUB.Reverse_Distributions()';
2857 Print (l_api_name,l_debug_info);
2858 ---------------------------------------------------------------
2859 zx_api_pub.reverse_distributions(
2860 p_api_version => 1.0,
2861 p_init_msg_list => FND_API.G_TRUE,
2862 p_commit => FND_API.G_FALSE,
2863 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2864 x_return_status => l_return_status_service,
2865 x_msg_count => l_msg_count,
2866 x_msg_data => l_msg_data);
2867
2868 END IF;
2869
2870 IF (l_return_status_service <> 'S') THEN -- handle errors --Marker 0
2871 l_return_status := FALSE;
2872 -----------------------------------------------------------------
2873 l_debug_info := 'Handle errors returned by API';
2874 Print (l_api_name,l_debug_info);
2875 -----------------------------------------------------------------
2876
2877 IF NOT(AP_ETAX_UTILITY_PKG.Return_Error_Messages(
2878 P_All_Error_Messages => l_msg_data,
2879 P_Msg_Count => l_msg_count,
2880 P_Msg_Data => l_msg_data,
2881 P_Error_Code => l_Error_Code,
2882 P_Calling_Sequence => l_curr_calling_sequence)) THEN
2883 NULL;
2884 END IF;
2885 DELETE zx_transaction_lines_gt;
2886 DELETE zx_import_tax_lines_gt;
2887 DELETE zx_trx_tax_link_gt;
2888 DELETE zx_reverse_dist_gt;
2889 RETURN l_return_status;
2890 ELSE -- update the tax only line amount to 0
2891
2892 IF l_return_status THEN
2893
2894 open c_charge_lines (p_line_rec.invoice_id,
2895 p_line_rec.line_number);
2896 loop
2897 fetch c_charge_lines
2898 into l_chrg_line_rec;
2899 exit when c_charge_lines%notfound;
2900
2901 IF l_reverse_dist_count>0 THEN
2902
2903 ---------------------------------------------------------------------------------------------------
2904 l_debug_info := 'Inserting reverse entries into ap_invoice_distributions_all after line discard';
2905 Print (l_api_name,l_debug_info);
2906 ---------------------------------------------------------------------------------------------------
2907 OPEN c_reverse_tax_dist_1(l_chrg_line_rec.invoice_id,
2908 l_chrg_line_rec.line_number);
2909 LOOP
2910 FETCH c_reverse_tax_dist_1
2911 INTO l_reverse_tax_dist;
2912 EXIT WHEN c_reverse_tax_dist_1%NOTFOUND;
2913
2914 INSERT INTO ap_invoice_distributions_all (
2915 accounting_date,
2916 accrual_posted_flag,
2917 assets_addition_flag,
2918 assets_tracking_flag,
2919 cash_posted_flag,
2920 distribution_line_number,
2921 dist_code_combination_id,
2922 invoice_id,
2923 last_updated_by,
2924 last_update_date,
2925 line_type_lookup_code,
2926 period_name,
2927 set_of_books_id,
2928 amount,
2929 base_amount,
2930 --batch_id,
2931 created_by,
2932 creation_date,
2933 description,
2934 final_match_flag,
2935 income_tax_region,
2936 last_update_login,
2937 match_status_flag,
2938 posted_flag,
2939 po_distribution_id,
2940 program_application_id,
2941 program_id,
2942 program_update_date,
2943 quantity_invoiced,
2944 request_id,
2945 reversal_flag,
2946 type_1099,
2947 unit_price,
2948 encumbered_flag,
2949 stat_amount,
2950 attribute1,
2951 attribute10,
2952 attribute11,
2953 attribute12,
2954 attribute13,
2955 attribute14,
2956 attribute15,
2957 attribute2,
2958 attribute3,
2959 attribute4,
2960 attribute5,
2961 attribute6,
2962 attribute7,
2963 attribute8,
2964 attribute9,
2965 attribute_category,
2966 expenditure_item_date,
2967 expenditure_organization_id,
2968 expenditure_type,
2969 parent_invoice_id,
2970 pa_addition_flag,
2971 pa_quantity,
2972 prepay_amount_remaining,
2973 project_accounting_context,
2974 project_id,
2975 task_id,
2976 packet_id,
2977 awt_flag,
2978 awt_group_id,
2979 awt_tax_rate_id,
2980 awt_gross_amount,
2981 awt_invoice_id,
2982 awt_origin_group_id,
2983 reference_1,
2984 reference_2,
2985 org_id,
2986 awt_invoice_payment_id,
2987 global_attribute_category,
2988 global_attribute1,
2989 global_attribute2,
2990 global_attribute3,
2991 global_attribute4,
2992 global_attribute5,
2993 global_attribute6,
2994 global_attribute7,
2995 global_attribute8,
2996 global_attribute9,
2997 global_attribute10,
2998 global_attribute11,
2999 global_attribute12,
3000 global_attribute13,
3001 global_attribute14,
3002 global_attribute15,
3003 global_attribute16,
3004 global_attribute17,
3005 global_attribute18,
3006 global_attribute19,
3007 global_attribute20,
3008 receipt_verified_flag,
3009 receipt_required_flag,
3010 receipt_missing_flag,
3011 justification,
3012 expense_group,
3013 start_expense_date,
3014 end_expense_date,
3015 receipt_currency_code,
3016 receipt_conversion_rate,
3017 receipt_currency_amount,
3018 daily_amount,
3019 web_parameter_id,
3020 adjustment_reason,
3021 award_id,
3022 credit_card_trx_id,
3023 dist_match_type,
3024 rcv_transaction_id,
3025 invoice_distribution_id,
3026 parent_reversal_id,
3027 tax_recoverable_flag,
3028 merchant_document_number,
3029 merchant_name,
3030 merchant_reference,
3031 merchant_tax_reg_number,
3032 merchant_taxpayer_id,
3033 country_of_supply,
3034 matched_uom_lookup_code,
3035 gms_burdenable_raw_cost,
3036 accounting_event_id,
3037 prepay_distribution_id,
3038 upgrade_posted_amt,
3039 upgrade_base_posted_amt,
3040 inventory_transfer_status,
3041 company_prepaid_invoice_id,
3042 cc_reversal_flag,
3043 awt_withheld_amt,
3044 pa_cmt_xface_flag,
3045 cancellation_flag,
3046 invoice_line_number,
3047 corrected_invoice_dist_id,
3048 rounding_amt,
3049 charge_applicable_to_dist_id,
3050 corrected_quantity,
3051 related_id,
3052 asset_book_type_code,
3053 asset_category_id,
3054 distribution_class,
3055 tax_code_id,
3056 intended_use,
3057 detail_tax_dist_id,
3058 rec_nrec_rate,
3059 recovery_rate_id,
3060 recovery_type_code,
3061 withholding_tax_code_id,
3062 taxable_amount,
3063 taxable_base_amount,
3064 tax_already_distributed_flag,
3065 summary_tax_line_id,
3066 rcv_charge_addition_flag,
3067 prepay_tax_diff_amount,
3068 pay_awt_group_id)
3069 VALUES
3070 (
3071 l_reverse_tax_dist.accounting_date,
3072 l_reverse_tax_dist.accrual_posted_flag,
3073 l_reverse_tax_dist.assets_addition_flag,
3074 l_reverse_tax_dist.assets_tracking_flag,
3075 l_reverse_tax_dist.cash_posted_flag,
3076 l_reverse_tax_dist.distribution_line_number,
3077 l_reverse_tax_dist.dist_code_combination_id,
3078 l_reverse_tax_dist.invoice_id,
3079 l_reverse_tax_dist.last_updated_by,
3080 l_reverse_tax_dist.last_update_date,
3081 l_reverse_tax_dist.line_type_lookup_code,
3082 l_reverse_tax_dist.period_name,
3083 l_reverse_tax_dist.set_of_books_id,
3084 l_reverse_tax_dist.amount,
3085 l_reverse_tax_dist.base_amount,
3086 --l_reverse_tax_dist.batch_id,
3087 l_reverse_tax_dist.created_by,
3088 l_reverse_tax_dist.creation_date,
3089 l_reverse_tax_dist.description,
3090 l_reverse_tax_dist.final_match_flag,
3091 l_reverse_tax_dist.income_tax_region,
3092 l_reverse_tax_dist.last_update_login,
3093 l_reverse_tax_dist.match_status_flag,
3094 l_reverse_tax_dist.posted_flag,
3095 l_reverse_tax_dist.po_distribution_id,
3096 l_reverse_tax_dist.program_application_id,
3097 l_reverse_tax_dist.program_id,
3098 l_reverse_tax_dist.program_update_date,
3099 l_reverse_tax_dist.quantity_invoiced,
3100 l_reverse_tax_dist.request_id,
3101 l_reverse_tax_dist.reversal_flag,
3102 l_reverse_tax_dist.type_1099,
3103 l_reverse_tax_dist.unit_price,
3104 l_reverse_tax_dist.encumbered_flag,
3105 l_reverse_tax_dist.stat_amount,
3106 l_reverse_tax_dist.attribute1,
3107 l_reverse_tax_dist.attribute10,
3108 l_reverse_tax_dist.attribute11,
3109 l_reverse_tax_dist.attribute12,
3110 l_reverse_tax_dist.attribute13,
3111 l_reverse_tax_dist.attribute14,
3112 l_reverse_tax_dist.attribute15,
3113 l_reverse_tax_dist.attribute2,
3114 l_reverse_tax_dist.attribute3,
3115 l_reverse_tax_dist.attribute4,
3116 l_reverse_tax_dist.attribute5,
3117 l_reverse_tax_dist.attribute6,
3118 l_reverse_tax_dist.attribute7,
3119 l_reverse_tax_dist.attribute8,
3120 l_reverse_tax_dist.attribute9,
3121 l_reverse_tax_dist.attribute_category,
3122 l_reverse_tax_dist.expenditure_item_date,
3123 l_reverse_tax_dist.expenditure_organization_id,
3124 l_reverse_tax_dist.expenditure_type,
3125 l_reverse_tax_dist.parent_invoice_id,
3126 l_reverse_tax_dist.pa_addition_flag,
3127 l_reverse_tax_dist.pa_quantity,
3128 l_reverse_tax_dist.prepay_amount_remaining,
3129 l_reverse_tax_dist.project_accounting_context,
3130 l_reverse_tax_dist.project_id,
3131 l_reverse_tax_dist.task_id,
3132 l_reverse_tax_dist.packet_id,
3133 l_reverse_tax_dist.awt_flag,
3134 l_reverse_tax_dist.awt_group_id,
3135 l_reverse_tax_dist.awt_tax_rate_id,
3136 l_reverse_tax_dist.awt_gross_amount,
3137 l_reverse_tax_dist.awt_invoice_id,
3138 l_reverse_tax_dist.awt_origin_group_id,
3139 l_reverse_tax_dist.reference_1,
3140 l_reverse_tax_dist.reference_2,
3141 l_reverse_tax_dist.org_id,
3142 l_reverse_tax_dist.awt_invoice_payment_id,
3143 l_reverse_tax_dist.global_attribute_category,
3144 l_reverse_tax_dist.global_attribute1,
3145 l_reverse_tax_dist.global_attribute2,
3146 l_reverse_tax_dist.global_attribute3,
3147 l_reverse_tax_dist.global_attribute4,
3148 l_reverse_tax_dist.global_attribute5,
3149 l_reverse_tax_dist.global_attribute6,
3150 l_reverse_tax_dist.global_attribute7,
3151 l_reverse_tax_dist.global_attribute8,
3152 l_reverse_tax_dist.global_attribute9,
3153 l_reverse_tax_dist.global_attribute10,
3154 l_reverse_tax_dist.global_attribute11,
3155 l_reverse_tax_dist.global_attribute12,
3156 l_reverse_tax_dist.global_attribute13,
3157 l_reverse_tax_dist.global_attribute14,
3158 l_reverse_tax_dist.global_attribute15,
3159 l_reverse_tax_dist.global_attribute16,
3160 l_reverse_tax_dist.global_attribute17,
3161 l_reverse_tax_dist.global_attribute18,
3162 l_reverse_tax_dist.global_attribute19,
3163 l_reverse_tax_dist.global_attribute20,
3164 l_reverse_tax_dist.receipt_verified_flag,
3165 l_reverse_tax_dist.receipt_required_flag,
3166 l_reverse_tax_dist.receipt_missing_flag,
3167 l_reverse_tax_dist.justification,
3168 l_reverse_tax_dist.expense_group,
3169 l_reverse_tax_dist.start_expense_date,
3170 l_reverse_tax_dist.end_expense_date,
3171 l_reverse_tax_dist.receipt_currency_code,
3172 l_reverse_tax_dist.receipt_conversion_rate,
3173 l_reverse_tax_dist.receipt_currency_amount,
3174 l_reverse_tax_dist.daily_amount,
3175 l_reverse_tax_dist.web_parameter_id,
3176 l_reverse_tax_dist.adjustment_reason,
3177 l_reverse_tax_dist.award_id,
3178 l_reverse_tax_dist.credit_card_trx_id,
3179 l_reverse_tax_dist.dist_match_type,
3180 l_reverse_tax_dist.rcv_transaction_id,
3181 l_reverse_tax_dist.invoice_distribution_id,
3182 l_reverse_tax_dist.parent_reversal_id,
3183 l_reverse_tax_dist.tax_recoverable_flag,
3184 l_reverse_tax_dist.merchant_document_number,
3185 l_reverse_tax_dist.merchant_name,
3186 l_reverse_tax_dist.merchant_reference,
3187 l_reverse_tax_dist.merchant_tax_reg_number,
3188 l_reverse_tax_dist.merchant_taxpayer_id,
3189 l_reverse_tax_dist.country_of_supply,
3190 l_reverse_tax_dist.matched_uom_lookup_code,
3191 l_reverse_tax_dist.gms_burdenable_raw_cost,
3192 l_reverse_tax_dist.accounting_event_id,
3193 l_reverse_tax_dist.prepay_distribution_id,
3194 l_reverse_tax_dist.upgrade_posted_amt,
3195 l_reverse_tax_dist.upgrade_base_posted_amt,
3196 l_reverse_tax_dist.inventory_transfer_status,
3197 l_reverse_tax_dist.company_prepaid_invoice_id,
3198 l_reverse_tax_dist.cc_reversal_flag,
3199 l_reverse_tax_dist.awt_withheld_amt,
3200 l_reverse_tax_dist.pa_cmt_xface_flag,
3201 l_reverse_tax_dist.cancellation_flag,
3202 l_reverse_tax_dist.invoice_line_number,
3203 l_reverse_tax_dist.corrected_invoice_dist_id,
3204 l_reverse_tax_dist.rounding_amt,
3205 l_reverse_tax_dist.charge_applicable_to_dist_id,
3206 l_reverse_tax_dist.corrected_quantity,
3207 l_reverse_tax_dist.related_id,
3208 l_reverse_tax_dist.asset_book_type_code,
3209 l_reverse_tax_dist.asset_category_id,
3210 l_reverse_tax_dist.distribution_class,
3211 l_reverse_tax_dist.tax_code_id,
3212 l_reverse_tax_dist.intended_use,
3213 l_reverse_tax_dist.detail_tax_dist_id,
3214 l_reverse_tax_dist.rec_nrec_rate,
3215 l_reverse_tax_dist.recovery_rate_id,
3216 l_reverse_tax_dist.recovery_type_code,
3217 l_reverse_tax_dist.withholding_tax_code_id,
3218 l_reverse_tax_dist.taxable_amount,
3219 l_reverse_tax_dist.taxable_base_amount,
3220 l_reverse_tax_dist.tax_already_distributed_flag,
3221 l_reverse_tax_dist.summary_tax_line_id,
3222 l_reverse_tax_dist.rcv_charge_addition_flag,
3223 l_reverse_tax_dist.prepay_tax_diff_amount,
3224 l_reverse_tax_dist.pay_awt_group_id);
3225
3226 INSERT into ZX_TAX_DIST_ID_GT (TAX_DIST_ID) values (l_reverse_tax_dist.detail_tax_dist_id) ;
3227 l_inv_cancel_date := l_reverse_tax_dist.accounting_date;
3228
3229 END LOOP;
3230
3231 l_rev_dist_cursor_count := c_reverse_tax_dist_1%ROWCOUNT ;
3232 CLOSE c_reverse_tax_dist_1;
3233
3234 ----------------------------------------------------------------------------
3235 l_debug_info := '(1) l_rev_dist_cursor_count: ' || l_rev_dist_cursor_count;
3236 Print (l_api_name,l_debug_info);
3237 ----------------------------------------------------------------------------
3238 END IF;
3239
3240 IF l_sa_reverse_dist_count>0 THEN
3241
3242 ----------------------------------------------------------------------------
3243 l_debug_info := 'Inserting reverse entries into ap_self_assessed_tax_dist_all';
3244 Print (l_api_name,l_debug_info);
3245 ----------------------------------------------------------------------------
3246 OPEN c_rev_self_assess_tax_dist_1(l_chrg_line_rec.invoice_id,
3247 l_chrg_line_rec.line_number);
3248 LOOP
3249 FETCH c_rev_self_assess_tax_dist_1
3250 INTO l_self_assess_rev_tax_dist_1;
3251 EXIT WHEN c_rev_self_assess_tax_dist_1%NOTFOUND;
3252
3253 INSERT INTO ap_self_assessed_tax_dist_all (
3254 accounting_date,
3255 accrual_posted_flag,
3256 assets_addition_flag,
3257 assets_tracking_flag,
3258 cash_posted_flag,
3259 distribution_line_number,
3260 dist_code_combination_id,
3261 invoice_id,
3262 last_updated_by,
3263 last_update_date,
3264 line_type_lookup_code,
3265 period_name,
3266 set_of_books_id,
3267 amount,
3268 base_amount,
3269 --batch_id,
3270 created_by,
3271 creation_date,
3272 description,
3273 final_match_flag,
3274 income_tax_region,
3275 last_update_login,
3276 match_status_flag,
3277 posted_flag,
3278 po_distribution_id,
3279 program_application_id,
3280 program_id,
3281 program_update_date,
3282 quantity_invoiced,
3283 request_id,
3284 reversal_flag,
3285 type_1099,
3286 unit_price,
3287 encumbered_flag,
3288 stat_amount,
3289 attribute1,
3290 attribute10,
3291 attribute11,
3292 attribute12,
3293 attribute13,
3294 attribute14,
3295 attribute15,
3296 attribute2,
3297 attribute3,
3298 attribute4,
3299 attribute5,
3300 attribute6,
3301 attribute7,
3302 attribute8,
3303 attribute9,
3304 attribute_category,
3305 expenditure_item_date,
3306 expenditure_organization_id,
3307 expenditure_type,
3308 parent_invoice_id,
3309 pa_addition_flag,
3310 pa_quantity,
3311 prepay_amount_remaining,
3312 project_accounting_context,
3313 project_id,
3314 task_id,
3315 packet_id,
3316 awt_flag,
3317 awt_group_id,
3318 awt_tax_rate_id,
3319 awt_gross_amount,
3320 awt_invoice_id,
3321 awt_origin_group_id,
3322 reference_1,
3323 reference_2,
3324 org_id,
3325 awt_invoice_payment_id,
3326 global_attribute_category,
3327 global_attribute1,
3328 global_attribute2,
3329 global_attribute3,
3330 global_attribute4,
3331 global_attribute5,
3332 global_attribute6,
3333 global_attribute7,
3334 global_attribute8,
3335 global_attribute9,
3336 global_attribute10,
3337 global_attribute11,
3338 global_attribute12,
3339 global_attribute13,
3340 global_attribute14,
3341 global_attribute15,
3342 global_attribute16,
3343 global_attribute17,
3344 global_attribute18,
3345 global_attribute19,
3346 global_attribute20,
3347 receipt_verified_flag,
3348 receipt_required_flag,
3349 receipt_missing_flag,
3350 justification,
3351 expense_group,
3352 start_expense_date,
3353 end_expense_date,
3354 receipt_currency_code,
3355 receipt_conversion_rate,
3356 receipt_currency_amount,
3357 daily_amount,
3358 web_parameter_id,
3359 adjustment_reason,
3360 award_id,
3361 credit_card_trx_id,
3362 dist_match_type,
3363 rcv_transaction_id,
3364 invoice_distribution_id,
3365 parent_reversal_id,
3366 tax_recoverable_flag,
3367 merchant_document_number,
3368 merchant_name,
3369 merchant_reference,
3370 merchant_tax_reg_number,
3371 merchant_taxpayer_id,
3372 country_of_supply,
3373 matched_uom_lookup_code,
3374 gms_burdenable_raw_cost,
3375 accounting_event_id,
3376 prepay_distribution_id,
3377 upgrade_posted_amt,
3378 upgrade_base_posted_amt,
3379 inventory_transfer_status,
3380 company_prepaid_invoice_id,
3381 cc_reversal_flag,
3382 awt_withheld_amt,
3383 pa_cmt_xface_flag,
3384 cancellation_flag,
3385 invoice_line_number,
3386 corrected_invoice_dist_id,
3387 rounding_amt,
3388 charge_applicable_to_dist_id,
3389 corrected_quantity,
3390 related_id,
3391 asset_book_type_code,
3392 asset_category_id,
3393 distribution_class,
3394 tax_code_id,
3395 intended_use,
3396 detail_tax_dist_id,
3397 rec_nrec_rate,
3398 recovery_rate_id,
3399 recovery_type_code,
3400 withholding_tax_code_id,
3401 taxable_amount,
3402 taxable_base_amount,
3403 tax_already_distributed_flag,
3404 summary_tax_line_id,
3405 rcv_charge_addition_flag,
3406 self_assessed_flag,
3407 self_assessed_tax_liab_ccid,
3408 prepay_tax_diff_amount
3409 )
3410 VALUES
3411 (
3412 l_self_assess_rev_tax_dist_1.accounting_date,
3413 l_self_assess_rev_tax_dist_1.accrual_posted_flag,
3414 l_self_assess_rev_tax_dist_1.assets_addition_flag,
3415 l_self_assess_rev_tax_dist_1.assets_tracking_flag,
3416 l_self_assess_rev_tax_dist_1.cash_posted_flag,
3417 l_self_assess_rev_tax_dist_1.distribution_line_number,
3418 l_self_assess_rev_tax_dist_1.dist_code_combination_id,
3419 l_self_assess_rev_tax_dist_1.invoice_id,
3420 l_self_assess_rev_tax_dist_1.last_updated_by,
3421 l_self_assess_rev_tax_dist_1.last_update_date,
3422 l_self_assess_rev_tax_dist_1.line_type_lookup_code,
3423 l_self_assess_rev_tax_dist_1.period_name,
3424 l_self_assess_rev_tax_dist_1.set_of_books_id,
3425 l_self_assess_rev_tax_dist_1.amount,
3426 l_self_assess_rev_tax_dist_1.base_amount,
3427 --l_self_assess_rev_tax_dist_1.batch_id,
3428 l_self_assess_rev_tax_dist_1.created_by,
3429 l_self_assess_rev_tax_dist_1.creation_date,
3430 l_self_assess_rev_tax_dist_1.description,
3431 l_self_assess_rev_tax_dist_1.final_match_flag,
3432 l_self_assess_rev_tax_dist_1.income_tax_region,
3433 l_self_assess_rev_tax_dist_1.last_update_login,
3434 l_self_assess_rev_tax_dist_1.match_status_flag,
3435 l_self_assess_rev_tax_dist_1.posted_flag,
3436 l_self_assess_rev_tax_dist_1.po_distribution_id,
3437 l_self_assess_rev_tax_dist_1.program_application_id,
3438 l_self_assess_rev_tax_dist_1.program_id,
3439 l_self_assess_rev_tax_dist_1.program_update_date,
3440 l_self_assess_rev_tax_dist_1.quantity_invoiced,
3441 l_self_assess_rev_tax_dist_1.request_id,
3442 l_self_assess_rev_tax_dist_1.reversal_flag,
3443 l_self_assess_rev_tax_dist_1.type_1099,
3444 l_self_assess_rev_tax_dist_1.unit_price,
3445 l_self_assess_rev_tax_dist_1.encumbered_flag,
3446 l_self_assess_rev_tax_dist_1.stat_amount,
3447 l_self_assess_rev_tax_dist_1.attribute1,
3448 l_self_assess_rev_tax_dist_1.attribute10,
3449 l_self_assess_rev_tax_dist_1.attribute11,
3450 l_self_assess_rev_tax_dist_1.attribute12,
3451 l_self_assess_rev_tax_dist_1.attribute13,
3452 l_self_assess_rev_tax_dist_1.attribute14,
3453 l_self_assess_rev_tax_dist_1.attribute15,
3454 l_self_assess_rev_tax_dist_1.attribute2,
3455 l_self_assess_rev_tax_dist_1.attribute3,
3456 l_self_assess_rev_tax_dist_1.attribute4,
3457 l_self_assess_rev_tax_dist_1.attribute5,
3458 l_self_assess_rev_tax_dist_1.attribute6,
3459 l_self_assess_rev_tax_dist_1.attribute7,
3460 l_self_assess_rev_tax_dist_1.attribute8,
3461 l_self_assess_rev_tax_dist_1.attribute9,
3462 l_self_assess_rev_tax_dist_1.attribute_category,
3463 l_self_assess_rev_tax_dist_1.expenditure_item_date,
3464 l_self_assess_rev_tax_dist_1.expenditure_organization_id,
3465 l_self_assess_rev_tax_dist_1.expenditure_type,
3466 l_self_assess_rev_tax_dist_1.parent_invoice_id,
3467 l_self_assess_rev_tax_dist_1.pa_addition_flag,
3468 l_self_assess_rev_tax_dist_1.pa_quantity,
3469 l_self_assess_rev_tax_dist_1.prepay_amount_remaining,
3470 l_self_assess_rev_tax_dist_1.project_accounting_context,
3471 l_self_assess_rev_tax_dist_1.project_id,
3472 l_self_assess_rev_tax_dist_1.task_id,
3473 l_self_assess_rev_tax_dist_1.packet_id,
3474 l_self_assess_rev_tax_dist_1.awt_flag,
3475 l_self_assess_rev_tax_dist_1.awt_group_id,
3476 l_self_assess_rev_tax_dist_1.awt_tax_rate_id,
3477 l_self_assess_rev_tax_dist_1.awt_gross_amount,
3478 l_self_assess_rev_tax_dist_1.awt_invoice_id,
3479 l_self_assess_rev_tax_dist_1.awt_origin_group_id,
3480 l_self_assess_rev_tax_dist_1.reference_1,
3481 l_self_assess_rev_tax_dist_1.reference_2,
3482 l_self_assess_rev_tax_dist_1.org_id,
3483 l_self_assess_rev_tax_dist_1.awt_invoice_payment_id,
3484 l_self_assess_rev_tax_dist_1.global_attribute_category,
3485 l_self_assess_rev_tax_dist_1.global_attribute1,
3486 l_self_assess_rev_tax_dist_1.global_attribute2,
3487 l_self_assess_rev_tax_dist_1.global_attribute3,
3488 l_self_assess_rev_tax_dist_1.global_attribute4,
3489 l_self_assess_rev_tax_dist_1.global_attribute5,
3490 l_self_assess_rev_tax_dist_1.global_attribute6,
3491 l_self_assess_rev_tax_dist_1.global_attribute7,
3492 l_self_assess_rev_tax_dist_1.global_attribute8,
3493 l_self_assess_rev_tax_dist_1.global_attribute9,
3494 l_self_assess_rev_tax_dist_1.global_attribute10,
3495 l_self_assess_rev_tax_dist_1.global_attribute11,
3496 l_self_assess_rev_tax_dist_1.global_attribute12,
3497 l_self_assess_rev_tax_dist_1.global_attribute13,
3498 l_self_assess_rev_tax_dist_1.global_attribute14,
3499 l_self_assess_rev_tax_dist_1.global_attribute15,
3500 l_self_assess_rev_tax_dist_1.global_attribute16,
3501 l_self_assess_rev_tax_dist_1.global_attribute17,
3502 l_self_assess_rev_tax_dist_1.global_attribute18,
3503 l_self_assess_rev_tax_dist_1.global_attribute19,
3504 l_self_assess_rev_tax_dist_1.global_attribute20,
3505 l_self_assess_rev_tax_dist_1.receipt_verified_flag,
3506 l_self_assess_rev_tax_dist_1.receipt_required_flag,
3507 l_self_assess_rev_tax_dist_1.receipt_missing_flag,
3508 l_self_assess_rev_tax_dist_1.justification,
3509 l_self_assess_rev_tax_dist_1.expense_group,
3510 l_self_assess_rev_tax_dist_1.start_expense_date,
3511 l_self_assess_rev_tax_dist_1.end_expense_date,
3512 l_self_assess_rev_tax_dist_1.receipt_currency_code,
3513 l_self_assess_rev_tax_dist_1.receipt_conversion_rate,
3514 l_self_assess_rev_tax_dist_1.receipt_currency_amount,
3515 l_self_assess_rev_tax_dist_1.daily_amount,
3516 l_self_assess_rev_tax_dist_1.web_parameter_id,
3517 l_self_assess_rev_tax_dist_1.adjustment_reason,
3518 l_self_assess_rev_tax_dist_1.award_id,
3519 l_self_assess_rev_tax_dist_1.credit_card_trx_id,
3520 l_self_assess_rev_tax_dist_1.dist_match_type,
3521 l_self_assess_rev_tax_dist_1.rcv_transaction_id,
3522 l_self_assess_rev_tax_dist_1.invoice_distribution_id,
3523 l_self_assess_rev_tax_dist_1.parent_reversal_id,
3524 l_self_assess_rev_tax_dist_1.tax_recoverable_flag,
3525 l_self_assess_rev_tax_dist_1.merchant_document_number,
3526 l_self_assess_rev_tax_dist_1.merchant_name,
3527 l_self_assess_rev_tax_dist_1.merchant_reference,
3528 l_self_assess_rev_tax_dist_1.merchant_tax_reg_number,
3529 l_self_assess_rev_tax_dist_1.merchant_taxpayer_id,
3530 l_self_assess_rev_tax_dist_1.country_of_supply,
3531 l_self_assess_rev_tax_dist_1.matched_uom_lookup_code,
3532 l_self_assess_rev_tax_dist_1.gms_burdenable_raw_cost,
3533 l_self_assess_rev_tax_dist_1.accounting_event_id,
3534 l_self_assess_rev_tax_dist_1.prepay_distribution_id,
3535 l_self_assess_rev_tax_dist_1.upgrade_posted_amt,
3536 l_self_assess_rev_tax_dist_1.upgrade_base_posted_amt,
3537 l_self_assess_rev_tax_dist_1.inventory_transfer_status,
3538 l_self_assess_rev_tax_dist_1.company_prepaid_invoice_id,
3539 l_self_assess_rev_tax_dist_1.cc_reversal_flag,
3540 l_self_assess_rev_tax_dist_1.awt_withheld_amt,
3541 l_self_assess_rev_tax_dist_1.pa_cmt_xface_flag,
3542 l_self_assess_rev_tax_dist_1.cancellation_flag,
3543 l_self_assess_rev_tax_dist_1.invoice_line_number,
3544 l_self_assess_rev_tax_dist_1.corrected_invoice_dist_id,
3545 l_self_assess_rev_tax_dist_1.rounding_amt,
3546 l_self_assess_rev_tax_dist_1.charge_applicable_to_dist_id,
3547 l_self_assess_rev_tax_dist_1.corrected_quantity,
3548 l_self_assess_rev_tax_dist_1.related_id,
3549 l_self_assess_rev_tax_dist_1.asset_book_type_code,
3550 l_self_assess_rev_tax_dist_1.asset_category_id,
3551 l_self_assess_rev_tax_dist_1.distribution_class,
3552 l_self_assess_rev_tax_dist_1.tax_code_id,
3553 l_self_assess_rev_tax_dist_1.intended_use,
3554 l_self_assess_rev_tax_dist_1.detail_tax_dist_id,
3555 l_self_assess_rev_tax_dist_1.rec_nrec_rate,
3556 l_self_assess_rev_tax_dist_1.recovery_rate_id,
3557 l_self_assess_rev_tax_dist_1.recovery_type_code,
3558 l_self_assess_rev_tax_dist_1.withholding_tax_code_id,
3559 l_self_assess_rev_tax_dist_1.taxable_amount,
3560 l_self_assess_rev_tax_dist_1.taxable_base_amount,
3561 l_self_assess_rev_tax_dist_1.tax_already_distributed_flag,
3562 l_self_assess_rev_tax_dist_1.summary_tax_line_id,
3563 l_self_assess_rev_tax_dist_1.rcv_charge_addition_flag,
3564 l_self_assess_rev_tax_dist_1.self_assessed_flag,
3565 l_self_assess_rev_tax_dist_1.self_assessed_tax_liab_ccid,
3566 l_self_assess_rev_tax_dist_1.prepay_tax_diff_amount
3567 );
3568
3569 INSERT into ZX_TAX_DIST_ID_GT (TAX_DIST_ID) values (l_self_assess_rev_tax_dist_1.detail_tax_dist_id) ;
3570 l_inv_cancel_date := l_self_assess_rev_tax_dist_1.accounting_date ;
3571
3572 END LOOP;
3573
3574 l_rev_sa_dist_cursor_count := c_rev_self_assess_tax_dist_1%ROWCOUNT ;
3575
3576 CLOSE c_rev_self_assess_tax_dist_1;
3577 ----------------------------------------------------------------------------
3578 l_debug_info := '(1) l_rev_sa_dist_cursor_count: ' || l_rev_sa_dist_cursor_count;
3579 Print (l_api_name,l_debug_info);
3580 ----------------------------------------------------------------------------
3581 END IF;
3582
3583 END LOOP;
3584
3585 close c_charge_lines;
3586
3587 ZX_API_PUB.Update_Tax_dist_gl_date (
3588 1.0,
3589 FND_API.G_TRUE,
3590 FND_API.G_FALSE,
3591 FND_API.G_VALID_LEVEL_FULL,
3592 l_return_status_service,
3593 l_msg_count,
3594 l_msg_data,
3595 l_inv_cancel_date );
3596
3597 IF (l_return_status_service <> FND_API.G_RET_STS_SUCCESS) THEN -- handle errors
3598
3599 l_return_status := FALSE;
3600
3601 -----------------------------------------------------------------
3602 l_debug_info := 'Handle errors returned by API';
3603 Print (l_api_name,l_debug_info);
3604 -----------------------------------------------------------------
3605
3606 IF NOT(AP_ETAX_UTILITY_PKG.Return_Error_Messages(
3607 P_All_Error_Messages => 'N',
3608 P_Msg_Count => l_msg_count,
3609 P_Msg_Data => l_msg_data,
3610 P_Error_Code => l_Error_Code,
3611 P_Calling_Sequence => l_curr_calling_sequence)) THEN
3612 NULL;
3613 END IF;
3614
3615 RETURN l_return_status;
3616 END IF;
3617
3618 IF l_tax_distributions_exist THEN
3619
3620 -----------------------------------------------------------------
3621 l_debug_info := 'Update reversal_flag';
3622 Print (l_api_name,l_debug_info);
3623 -----------------------------------------------------------------
3624
3625 UPDATE ap_invoice_distributions_all aid
3626 SET reversal_flag = (select reverse_flag
3627 from zx_rec_nrec_dist zx
3628 where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
3629 WHERE aid.invoice_id = p_line_rec.invoice_id
3630 AND aid.detail_tax_dist_id IS NOT NULL;
3631
3632 -----------------------------------------------------------------
3633 l_debug_info := 'Update related_flag';
3634 Print (l_api_name,l_debug_info);
3635 -----------------------------------------------------------------
3636
3637 UPDATE ap_invoice_distributions aid
3638 SET aid.related_id =
3639 (SELECT invoice_distribution_id
3640 FROM ap_invoice_distributions_all aid1
3641 WHERE aid1.invoice_id = aid.invoice_id
3642 AND aid1.invoice_line_number = aid.invoice_line_number
3643 AND aid1.parent_reversal_id =
3644 (SELECT related_id
3645 FROM ap_invoice_distributions_all aid2
3646 WHERE aid2.invoice_id = aid.invoice_id
3647 AND aid2.invoice_line_number = aid.invoice_line_number
3648 AND aid2.invoice_distribution_id = aid.parent_reversal_id)
3649 )
3650 WHERE aid.related_id IS NULL
3651 AND aid.parent_reversal_id IS NOT NULL
3652 AND aid.invoice_id = p_line_rec.invoice_id
3653 AND aid.reversal_flag = 'Y'
3654 AND aid.detail_tax_dist_id IS NOT NULL;
3655
3656 END IF;
3657
3658 IF l_self_assess_tax_dist_exist THEN
3659
3660 -----------------------------------------------------------------
3661 l_debug_info := 'Update reversal_flag';
3662 Print (l_api_name,l_debug_info);
3663 -----------------------------------------------------------------
3664
3665 UPDATE ap_self_assessed_tax_dist_all aid
3666 SET reversal_flag = (select reverse_flag
3667 from zx_rec_nrec_dist zx
3668 where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
3669 WHERE aid.invoice_id = p_line_rec.invoice_id
3670 AND aid.detail_tax_dist_id IS NOT NULL;
3671
3672 -----------------------------------------------------------------
3673 l_debug_info := 'Update related_flag';
3674 Print (l_api_name,l_debug_info);
3675 -----------------------------------------------------------------
3676
3677 UPDATE ap_self_assessed_tax_dist_all aid
3678 SET aid.related_id =
3679 (SELECT invoice_distribution_id
3680 FROM ap_self_assessed_tax_dist_all aid1
3681 WHERE aid1.invoice_id = aid.invoice_id
3682 AND aid1.invoice_line_number = aid.invoice_line_number
3683 AND aid1.parent_reversal_id =
3684 (SELECT related_id
3685 FROM ap_self_assessed_tax_dist_all aid2
3686 WHERE aid2.invoice_id = aid.invoice_id
3687 AND aid2.invoice_line_number = aid.invoice_line_number
3688 AND aid2.invoice_distribution_id = aid.parent_reversal_id)
3689 )
3690 WHERE aid.related_id IS NULL
3691 AND aid.parent_reversal_id IS NOT NULL
3692 AND aid.invoice_id = p_line_rec.invoice_id
3693 AND aid.reversal_flag = 'Y'
3694 AND aid.detail_tax_dist_id IS NOT NULL;
3695
3696 END IF;
3697
3698 END IF;
3699 END IF; --Marker 0
3700
3701 DELETE zx_transaction_lines_gt;
3702 DELETE zx_import_tax_lines_gt;
3703 DELETE zx_trx_tax_link_gt;
3704 DELETE zx_reverse_dist_gt;
3705
3706
3707 --Bug14383132 : End
3708
3709
3710 -- 9100425 kept the error check outside charge loop so that it fires only once
3711 IF nvl(l_cancel_proactive_flag,'N') ='C' and NVL(P_calling_mode,'DISCARD') = 'DISCARD'
3712 and prob_dist_count > 0 THEN
3713
3714 p_error_code :='AP_INV_DIS_CAN_FAIL';
3715 P_token:=prob_dist_list;
3716 ROLLBACK TO SAVEPOINT CANCEL_CHECK;
3717 RETURN(FALSE);
3718 END IF;
3719 -- 9100425
3720
3721
3722
3723 ----------------------------------------------------------------------------
3724 l_debug_info := 'Delete allocation rule lines';
3725 Print (l_api_name,l_debug_info);
3726 ----------------------------------------------------------------------------
3727
3728 delete from ap_allocation_rule_lines
3729 where invoice_id = p_line_rec.invoice_id
3730 and to_invoice_line_number = p_line_rec.line_number;
3731
3732 END IF;
3733 END IF;
3734 -- Bug 5114543 End
3735
3736 IF ( l_ok_to_discard = TRUE OR
3737 l_ok_to_cancel = TRUE ) THEN
3738
3739
3740 --Start of bug 8733916
3741 --bug 9293911, added 'UNAPPLY_PREPAY' to the 'IF' condition
3742 /*-----------------------------------------------------------------+
3743 | Step 0. Delete all the unprocessed bc events for this invoice |
3744 +-----------------------------------------------------------------*/
3745
3746 IF(p_calling_mode IN ('DISCARD', 'UNAPPLY_PREPAY')) THEN
3747
3748 AP_FUNDS_CONTROL_PKG.Encum_Unprocessed_Events_Del
3749 (p_invoice_id => p_line_rec.invoice_id,
3750 p_calling_sequence => l_curr_calling_sequence);
3751
3752
3753 UPDATE ap_invoice_distributions aid
3754 SET aid.encumbered_flag = 'R'
3755 WHERE aid.invoice_id = p_line_rec.invoice_id
3756 AND aid.invoice_line_number = p_line_rec.line_number
3757 AND nvl(aid.encumbered_flag,'N') IN ('N','H','P')
3758 AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
3759 AND nvl(aid.reversal_flag,'N')<>'Y'
3760 AND EXISTS (SELECT 1
3761 FROM financials_system_params_all fsp
3762 WHERE fsp.org_id = aid.org_id
3763 AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
3764
3765 END IF;
3766
3767 --End of bug 8733916
3768
3769
3770 /*--------------------------------------------------------------+
3771 | Step 1. Call the ETAX api to unfreeze the invoice, if the |
3772 | invoice is already validated. |
3773 |---------------------------------------------------------------*/
3774 --Need not call the UNFREEZE INVOICE when the calling mode = 'CANCE',
3775 --as the call is already done in CANCEL package before control comes here.
3776 --The reason the call being placed
3777 IF (l_ok_to_discard) THEN
3778
3779 l_invoice_validation_status := ap_invoices_pkg.get_approval_status(
3780 l_invoice_id => p_line_rec.invoice_id,
3781 l_invoice_amount => l_invoice_amount,
3782 l_payment_status_flag => l_payment_status_flag,
3783 l_invoice_type_lookup_code => l_invoice_type_lookup_code );
3784
3785 IF (NVL(l_invoice_validation_status,'NEVER APPROVED') IN
3786 ('APPROVED','AVAILABLE','UNPAID','FULL')) THEN
3787
3788 l_success := ap_etax_pkg.calling_etax(
3789 P_Invoice_id => p_line_rec.invoice_id,
3790 P_Calling_Mode => 'UNFREEZE INVOICE',
3791 P_All_Error_Messages => 'N',
3792 P_error_code => l_error_code,
3793 P_Calling_Sequence => l_curr_calling_sequence);
3794
3795 IF (not l_success) THEN
3796 p_error_code := 'AP_ETX_DISC_LINE_UNFRZ_FAIL';
3797 p_token := l_error_code;
3798 RETURN(FALSE);
3799 END IF;
3800
3801 END IF;
3802
3803 SELECT included_tax_amount
3804 INTO l_included_tax_amount
3805 FROM ap_invoice_lines
3806 WHERE invoice_id = p_line_rec.invoice_id
3807 AND line_number = p_line_rec.line_number;
3808
3809 END IF; /* if l_ok_to_discard = 'Y' */
3810
3811
3812 /*-----------------------------------------------------------------+
3813 | Step 2. Reverse Match if line is matched |
3814 | a. Reverse adjust po_distributions |
3815 +-----------------------------------------------------------------*/
3816
3817 l_debug_info := 'Reverse Match - Adjust po_distributions po_line_location_id is '||p_line_rec.po_line_location_id;
3818 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3819 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3820 END IF;
3821
3822 IF ( p_line_rec.po_Line_location_id is not null OR
3823 p_line_rec.rcv_transaction_id is not null ) THEN
3824 --Commented this condition for bug#9298560
3825 -- AND p_calling_mode <> 'UNAPPLY_PREPAY' THEN
3826
3827
3828 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
3829 l_recoup_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
3830
3831 l_debug_info := 'Open Cursor Po_Dists_Cur';
3832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3833 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3834 END IF;
3835
3836 OPEN po_dists_cur;
3837 LOOP
3838
3839 FETCH po_dists_cur
3840 INTO l_po_distribution_id,
3841 l_matched_uom,
3842 l_sum_matched_qty,
3843 l_sum_matched_amt,
3844 l_dist_type_lookup_code,
3845 l_matching_basis,
3846 l_prepay_invoice_id,
3847 l_prepay_line_number;
3848 EXIT WHEN po_dists_cur%NOTFOUND;
3849
3850 IF (l_dist_type_lookup_code IN ('ITEM','ACCRUAL','IPV','ERV')) THEN
3851
3852 l_debug_info := 'Update billed/financed data for po distributions';
3853 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3854 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3855 END IF;
3856
3857 --Bugfix:5578026
3858 IF (l_invoice_type_lookup_code <> 'PREPAYMENT') THEN
3859
3860 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3861 p_uom_code => l_matched_uom,
3862 p_quantity_billed => l_sum_matched_qty*(-1),
3863 p_amount_billed => l_sum_matched_amt*(-1),
3864 p_quantity_financed => NULL,
3865 p_amount_financed => NULL,
3866 p_quantity_recouped => NULL,
3867 p_amount_recouped => NULL,
3868 p_retainage_withheld_amt => NULL,
3869 p_retainage_released_amt => NULL);
3870
3871
3872 l_shipment_amt_billed := l_shipment_amt_billed + nvl(l_sum_matched_amt,0) * (-1);
3873 l_shipment_qty_billed := l_shipment_qty_billed + nvl(l_sum_matched_qty,0) * (-1);
3874
3875 ELSIF (l_invoice_type_lookup_code = 'PREPAYMENT') THEN
3876
3877 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3878 p_uom_code => l_matched_uom,
3879 p_quantity_billed => NULL,
3880 p_amount_billed => NULL,
3881 p_quantity_financed => l_sum_matched_qty*(-1),
3882 p_amount_financed => l_sum_matched_amt*(-1),
3883 p_quantity_recouped => NULL,
3884 p_amount_recouped => NULL,
3885 p_retainage_withheld_amt => NULL,
3886 p_retainage_released_amt => NULL);
3887
3888 l_shipment_amt_financed := l_shipment_amt_financed + nvl(l_sum_matched_amt,0)*(-1);
3889 l_shipment_qty_financed := l_shipment_qty_financed + nvl(l_sum_matched_qty,0)*(-1);
3890
3891
3892 END IF; /*Bugfix:5578026 */
3893
3894
3895 ELSIF (l_dist_type_lookup_code = 'PREPAY') THEN
3896
3897 l_debug_info := 'Populate recouped data for po distributions ';
3898 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3899 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3900 END IF;
3901
3902 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3903 p_uom_code => l_matched_uom,
3904 p_quantity_billed => NULL,
3905 p_amount_billed => NULL,
3906 p_quantity_financed => NULL,
3907 p_amount_financed => NULL,
3908 p_quantity_recouped => l_sum_matched_qty,
3909 p_amount_recouped => l_sum_matched_amt,
3910 p_retainage_withheld_amt => NULL,
3911 p_retainage_released_amt => NULL);
3912
3913 l_shipment_amt_recouped := l_shipment_amt_recouped + nvl(l_sum_matched_amt,0);
3914 l_shipment_qty_recouped := l_shipment_qty_recouped + nvl(l_sum_matched_qty,0);
3915
3916 -- This loop will update amount/quantity recouped at the distribution level.
3917 -- As, Recouped prepay distributions belong to a different shipment. Cursor
3918 -- c_recouped_shipment is used for shipment level updates outside the loop.
3919 -- For this reason, dummy distributions are populated in l_recoup_dist_rec
3920 -- so that the subsequent call to Update_Document_Ap_Values does not fail.
3921
3922 l_recoup_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3923 p_uom_code => NULL,
3924 p_quantity_billed => NULL,
3925 p_amount_billed => NULL,
3926 p_quantity_financed => NULL,
3927 p_amount_financed => NULL,
3928 p_quantity_recouped => NULL,
3929 p_amount_recouped => NULL,
3930 p_retainage_withheld_amt => NULL,
3931 p_retainage_released_amt => NULL);
3932
3933 ELSIF (l_dist_type_lookup_code = 'RETAINAGE') THEN
3934
3935 IF p_line_rec.line_type_lookup_code <> 'RETAINAGE RELEASE' THEN
3936
3937 l_debug_info := 'Populate retainage withheld data for po distributions: '||l_po_distribution_id||': '||l_sum_matched_amt;
3938 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3939 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3940 END IF;
3941
3942 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3943 p_uom_code => NULL,
3944 p_quantity_billed => NULL,
3945 p_amount_billed => NULL,
3946 p_quantity_financed => NULL,
3947 p_amount_financed => NULL,
3948 p_quantity_recouped => NULL,
3949 p_amount_recouped => NULL,
3950 p_retainage_withheld_amt => l_sum_matched_amt,
3951 p_retainage_released_amt => NULL);
3952
3953 l_shipment_amt_retained := l_shipment_amt_retained + nvl(l_sum_matched_amt,0);
3954
3955 ELSIF p_line_rec.line_type_lookup_code = 'RETAINAGE RELEASE' THEN
3956
3957 l_debug_info := 'Populate retainage released data for po distributions: '||l_sum_matched_amt;
3958 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3959 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3960 END IF;
3961
3962 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
3963 p_uom_code => NULL,
3964 p_quantity_billed => NULL,
3965 p_amount_billed => NULL,
3966 p_quantity_financed => NULL,
3967 p_amount_financed => NULL,
3968 p_quantity_recouped => NULL,
3969 p_amount_recouped => NULL,
3970 p_retainage_withheld_amt => NULL,
3971 p_retainage_released_amt => l_sum_matched_amt * (-1));
3972
3973 l_shipment_amt_released := l_shipment_amt_released + nvl(l_sum_matched_amt,0) * (-1);
3974
3975 END IF;
3976 END IF;
3977
3978 END LOOP;
3979
3980 CLOSE PO_Dists_Cur;
3981
3982 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
3983 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3984 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3985 END IF;
3986
3987
3988 IF (l_shipment_amt_billed <> 0 OR l_shipment_qty_billed <> 0 OR
3989 l_shipment_amt_financed <> 0 OR l_shipment_qty_financed <> 0 OR --bugfix:5578026
3990 l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0 OR
3991 l_shipment_amt_retained <> 0 OR l_shipment_amt_released <> 0) THEN
3992
3993 l_debug_info := ' Call add_change to populate the billed data for po shipments';
3994 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3995 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3996 END IF;
3997
3998 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
3999 p_po_line_location_id => p_line_rec.po_line_location_id,
4000 p_uom_code => l_matched_uom,
4001 p_quantity_billed => l_shipment_qty_billed,
4002 p_amount_billed => l_shipment_amt_billed,
4003 p_quantity_financed => l_shipment_qty_financed,
4004 p_amount_financed => l_shipment_amt_financed,
4005 p_quantity_recouped => NULL,
4006 p_amount_recouped => NULL,
4007 p_retainage_withheld_amt => l_shipment_amt_retained,
4008 p_retainage_released_amt => l_shipment_amt_released
4009 );
4010
4011 END IF;
4012
4013 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
4014 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4015 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4016 END IF;
4017
4018 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
4019 P_Api_Version => 1.0,
4020 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
4021 P_Dist_Changes_Rec => l_po_ap_dist_rec,
4022 X_Return_Status => l_return_status1,
4023 X_Msg_Data => l_msg_data);
4024
4025 IF (l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0) THEN
4026
4027 OPEN c_recouped_shipment;
4028 LOOP
4029 FETCH c_recouped_shipment
4030 INTO l_recouped_shipment;
4031 EXIT WHEN c_recouped_shipment%NOTFOUND;
4032
4033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '1: '||l_recouped_shipment.line_location_id);
4035 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '2: '||l_recouped_shipment.matched_uom_lookup_code);
4036 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '3: '||l_recouped_shipment.quantity);
4037 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, '4: '||l_recouped_shipment.amount);
4038 END IF;
4039
4040 l_recoup_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
4041 p_po_line_location_id => l_recouped_shipment.line_location_id,
4042 p_uom_code => l_recouped_shipment.matched_uom_lookup_code,
4043 p_quantity_billed => NULL,
4044 p_amount_billed => NULL,
4045 p_quantity_financed => NULL,
4046 p_amount_financed => NULL,
4047 p_quantity_recouped => l_recouped_shipment.quantity,
4048 p_amount_recouped => l_recouped_shipment.amount,
4049 p_retainage_withheld_amt => NULL,
4050 p_retainage_released_amt => NULL
4051 );
4052
4053 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
4054 P_Api_Version => 1.0,
4055 P_Line_Loc_Changes_Rec => l_recoup_line_loc_rec,
4056 P_Dist_Changes_Rec => l_recoup_dist_rec,
4057 X_Return_Status => l_return_status1,
4058 X_Msg_Data => l_msg_data);
4059
4060 END LOOP;
4061 CLOSE c_recouped_shipment;
4062 END IF;
4063
4064 /*-----------------------------------------------------------------+
4065 | Step 1. Reverse Match if line is matched |
4066 | c. Reverse adjust rcv_transaction |
4067 +-----------------------------------------------------------------*/
4068 /* Bug 5351931. was <>, modified to 'IS NOT NULL' */
4069
4070 --bugfix:5638822, add the AND clause below as when we are cancelling a invoice which had
4071 --TAX related to a receipt matched line (rcv_transaction_id is not null on TAX related to
4072 --receipt matched line) we were reducing the qty/amt_billed with tax amount too.
4073 --Bug11783854 : Added line type FREIGHT and MISC in below if condition.
4074 --Qty/amt are not updated for special charge matching.
4075 IF ( p_line_rec.rcv_transaction_id IS NOT NULL AND
4076 p_line_rec.line_type_lookup_code NOT IN ('TAX', 'FREIGHT', 'MISCELLANEOUS') ) THEN
4077 l_debug_info := 'Reverse Match - Adject rcv_transactions ';
4078
4079 l_sum_matched_qty := 0;
4080 /* Amount Based Matching */
4081 IF ( p_line_rec.match_type IN ('AMOUNT_CORRECTION', 'PRICE_CORRECTION',
4082 'ITEM_TO_SERVICE_PO', 'ITEM_TO_SERVICE_RECEIPT') ) THEN
4083 l_sum_matched_qty := 0;
4084 ELSE
4085 l_sum_matched_qty := NVL(p_line_rec.quantity_invoiced, 0 );
4086
4087 END IF;
4088
4089 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
4090 p_line_rec.rcv_transaction_id ,
4091 l_sum_matched_qty *(-1),
4092 p_line_rec.unit_meas_lookup_code,
4093 NVL(p_line_rec.amount, 0) * (-1));
4094
4095 END IF; -- end of l_rcv_transaction_id check
4096
4097 END IF; -- end of l_po_line_location_id/l_rcv_transaction_id check
4098
4099 /*-------------------------------------------------------------------+
4100 | Step 1a. Update retained_amount_remaining on the original invoice |
4101 +--------------------------------------------------------------------*/
4102 IF p_line_rec.line_type_lookup_code = 'RETAINAGE RELEASE' AND
4103 p_line_rec.retained_invoice_id IS NOT NULL AND
4104 p_line_rec.retained_line_number IS NOT NULL THEN
4105
4106 UPDATE ap_invoice_lines_all
4107 SET retained_amount_remaining = nvl(retained_amount_remaining, 0) + p_line_rec.amount
4108 WHERE invoice_id = p_line_rec.retained_invoice_id
4109 AND line_number = p_line_rec.retained_line_number;
4110
4111 END IF;
4112
4113
4114 /*------------------------------------------------------------------
4115 --bugfix:5638822
4116 --Update amount_paid on the invoice , if this line had recouped_amount
4117 -- on it.
4118 -------------------------------------------------------------------*/
4119 l_debug_info := 'Update amount_paid on the invoice if the line had recouped amount';
4120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4121 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4122 END IF;
4123
4124 l_recouped_amount := AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount(p_line_rec.Invoice_Id,p_line_rec.Line_Number);
4125
4126 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4127 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Recouped Amount: '||l_recouped_amount);
4128 END IF;
4129
4130 --Commented below update for bug #8928639
4131
4132 /* UPDATE ap_invoices
4133 SET amount_paid = nvl(amount_paid,0) - abs(l_recouped_amount) ,
4134 payment_status_flag =
4135 AP_INVOICES_UTILITY_PKG.get_payment_status(p_line_rec.invoice_id ),
4136 last_update_date = SYSDATE,
4137 last_updated_by = fnd_global.user_id,
4138 last_update_login = p_line_rec.last_update_login
4139 WHERE invoice_id = p_line_rec.invoice_id; */
4140
4141
4142 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule(
4143 p_invoice_id => p_line_rec.invoice_id,
4144 p_prepay_invoice_id => NULL,
4145 p_prepay_line_num => NULL,
4146 p_apply_amount => l_recouped_amount,
4147 p_appl_type => 'UNAPPLICATION',
4148 p_payment_currency_code => l_payment_currency_code,
4149 p_user_id => FND_GLOBAL.user_id,
4150 p_last_update_login => p_line_rec.last_update_login,
4151 p_calling_sequence => p_calling_sequence,
4152 p_calling_mode => 'RECOUPMENT',
4153 p_error_message => l_error_message);
4154
4155
4156 /*-----------------------------------------------------------------+
4157 | Step 2. Zero out line level data and MRC data maintainence |
4158 +-----------------------------------------------------------------*/
4159 UPDATE ap_invoice_lines
4160 SET original_amount = amount
4161 ,original_base_amount = base_amount
4162 ,original_rounding_amt = rounding_amt
4163 ,amount = 0
4164 ,base_amount = 0
4165 ,rounding_amt = 0
4166 ,retained_amount = 0
4167 ,retained_amount_remaining = 0
4168 ,included_tax_amount = 0
4169 -- Bug 14305530: Added last_updated_by, last_update_login and last_update_date
4170 ,last_updated_by = p_last_updated_by
4171 ,last_update_login = p_last_update_login
4172 ,last_update_date = sysdate
4173 ,discarded_flag = decode( p_calling_mode, 'DISCARD', 'Y', 'UNAPPLY_PREPAY','Y',NULL )
4174 -- Bug 6669048. The cancelled_flag will be updated in the cancel API
4175 -- ,cancelled_flag = decode( p_calling_mode, 'CANCEL', 'Y', NULL )
4176 ,generate_dists = decode( generate_dists, 'Y', 'N', generate_dists)
4177 ,quantity_invoiced = decode( p_calling_mode,
4178 'DISCARD', quantity_invoiced - quantity_invoiced, --8560785
4179 'CANCEL', quantity_invoiced - quantity_invoiced, --Introduced for bug#9570774
4180 'UNAPPLY_PREPAY', quantity_invoiced - quantity_invoiced, --Introduced for bug#9298560
4181 quantity_invoiced)
4182 WHERE invoice_id = p_line_rec.invoice_id
4183 AND line_number = p_line_rec.line_number;
4184
4185
4186 /*-----------------------------------------------------------------+
4187 | Step 4. Reverse Distribution |
4188 | a. Check if there is a valid distribution exists |
4189 +-----------------------------------------------------------------*/
4190
4191 l_debug_info := 'Check if there is a valid distribuition for reversal';
4192
4193 --Contract Payments: Modified the below WHERE clause to get the TAX related to
4194 --Prepay distributions , but not the TAX related to ITEM dists, since DETERMINE_RECOVERY
4195 --will take care of the TAX on ITEM dists.
4196 SELECT count(*)
4197 INTO l_distribution_count
4198 FROM ap_invoice_distributions_all
4199 WHERE invoice_id = p_line_rec.invoice_id
4200 AND invoice_line_number = p_line_rec.line_number
4201 AND ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV') and
4202 prepay_distribution_id IS NULL) OR
4203 (prepay_distribution_id IS NOT NULL)
4204 )
4205 AND NVL(reversal_flag, 'N') <> 'Y';
4206
4207 IF ( l_distribution_count <> 0 ) THEN
4208
4209 l_debug_info := 'Get the maximum distribution line number';
4210 l_max_line_num := AP_INVOICE_LINES_PKG.get_max_dist_line_num(
4211 p_line_rec.invoice_id,
4212 p_line_rec.line_number);
4213
4214 -- Bug fix 4748638
4215 /*-----------------------------------------------------------------+
4216 | Step 4. Reverse Distribution |
4217 | c. Create cancellation accounting event when primary |
4218 | accounting metod is Accrual basis. |
4219 | Note - It has been decided that a single invoice cancellation |
4220 | event will be created per invoice for the uptake of SLA.|
4221 | The invoice cancellation event and distributions will |
4222 | use the Invoice Header GL date as was used in 11i. |
4223 | Note 2 - to fix bug 4748638, if we finally goes with the |
4224 | option that create single cancel event. we need to |
4225 | move the event creation to cancel package |
4226 +-----------------------------------------------------------------*/
4227 /*
4228 IF (p_calling_mode = 'CANCEL' ) THEN
4229
4230 --Bug 4352723 - Added the following select to get Invoice Header
4231 --GL date to be used for Invoice cancellation event and dists
4232 SELECT gl_date
4233 INTO l_open_gl_date
4234 FROM AP_INVOICES
4235 WHERE invoice_id = P_Line_Rec.invoice_id;
4236
4237
4238 AP_ACCOUNTING_EVENTS_PKG.Create_Events (
4239 'INVOICE CANCELLATION'
4240 ,NULL -- p_doc_type
4241 ,p_line_rec.invoice_id
4242 ,l_open_gl_date
4243 ,l_Accounting_event_ID
4244 ,NULL -- checkrun_name
4245 ,P_calling_sequence);
4246
4247 END IF; -- Events Project - 2 - end
4248
4249 */
4250 /*-----------------------------------------------------------------+
4251 | Step 4. Reverse Distribution |
4252 | d.Insert reversal lines |
4253 +-----------------------------------------------------------------*/
4254 l_debug_info := 'Insert distribution reversals for existing lines';
4255
4256 --Contract Payments: Modified the WHERE and HAVING clause to take into
4257 --consideration 'Prepay' and its related Tax distributions.
4258
4259 -- Removed the below HAVING clause, for the 7376114, to allow zero amount ITEM lines to be reversed.
4260
4261 INSERT INTO ap_invoice_distributions_all(
4262 invoice_id,
4263 invoice_line_number,
4264 dist_code_combination_id,
4265 invoice_distribution_id,
4266 last_update_date,
4267 last_updated_by,
4268 accounting_date,
4269 period_name,
4270 set_of_books_id,
4271 amount,
4272 description,
4273 type_1099,
4274 tax_code_id,
4275 posted_flag,
4276 batch_id,
4277 quantity_invoiced,
4278 corrected_quantity,
4279 unit_price,
4280 match_status_flag,
4281 attribute_category,
4282 attribute1,
4283 attribute2,
4284 attribute3,
4285 attribute4,
4286 attribute5,
4287 prepay_amount_remaining,
4288 prepay_distribution_id,
4289 assets_addition_flag,
4290 assets_tracking_flag,
4291 distribution_line_number,
4292 line_type_lookup_code,
4293 po_distribution_id,
4294 base_amount,
4295 pa_addition_flag,
4296 encumbered_flag,
4297 accrual_posted_flag,
4298 cash_posted_flag,
4299 last_update_login,
4300 creation_date,
4301 created_by,
4302 stat_amount,
4303 attribute11,
4304 attribute12,
4305 attribute13,
4306 attribute14,
4307 attribute6,
4308 attribute7,
4309 attribute8,
4310 attribute9,
4311 attribute10,
4312 attribute15,
4313 reversal_flag,
4314 parent_invoice_id,
4315 income_tax_region,
4316 final_match_flag,
4317 expenditure_item_date,
4318 expenditure_organization_id,
4319 expenditure_type,
4320 pa_quantity,
4321 project_id,
4322 task_id,
4323 quantity_variance,
4324 base_quantity_variance,
4325 awt_flag,
4326 awt_group_id,
4327 awt_tax_rate_id,
4328 awt_gross_amount,
4329 reference_1,
4330 reference_2,
4331 other_invoice_id,
4332 awt_invoice_id,
4333 awt_origin_group_id,
4334 program_application_id,
4335 program_id,
4336 program_update_date,
4337 request_id,
4338 tax_recoverable_flag,
4339 award_id,
4340 start_expense_date,
4341 merchant_document_number,
4342 merchant_name,
4343 merchant_tax_reg_number,
4344 merchant_taxpayer_id,
4345 country_of_supply,
4346 merchant_reference,
4347 parent_reversal_id,
4348 rcv_transaction_id,
4349 dist_match_type,
4350 matched_uom_lookup_code,
4351 global_attribute_category,
4352 global_attribute1,
4353 global_attribute2,
4354 global_attribute3,
4355 global_attribute4,
4356 global_attribute5,
4357 global_attribute6,
4358 global_attribute7,
4359 global_attribute8,
4360 global_attribute9,
4361 global_attribute10,
4362 global_attribute11,
4363 global_attribute12,
4364 global_attribute13,
4365 global_attribute14,
4366 global_attribute15,
4367 global_attribute16,
4368 global_attribute17,
4369 global_attribute18,
4370 global_attribute19,
4371 global_attribute20,
4372 receipt_verified_flag,
4373 receipt_required_flag,
4374 receipt_missing_flag,
4375 justification,
4376 expense_Group,
4377 end_Expense_Date,
4378 receipt_Currency_Code,
4379 receipt_Conversion_Rate,
4380 receipt_Currency_Amount,
4381 daily_Amount,
4382 web_Parameter_Id,
4383 adjustment_Reason,
4384 credit_Card_Trx_Id,
4385 company_Prepaid_Invoice_Id,
4386 org_id,
4387 rounding_amt,
4388 charge_applicable_to_dist_id,
4389 corrected_invoice_dist_id,
4390 related_id,
4391 asset_book_type_code,
4392 asset_category_id,
4393 accounting_event_id,
4394 cancellation_flag,
4395 distribution_class,
4396 intended_use,
4397 --Freight and Special Charges
4398 rcv_charge_addition_flag,
4399 awt_related_id, --bug 8745752
4400 retained_invoice_dist_id, -- Bug 8824235
4401 pay_awt_group_id, /* Bug 9821980 */
4402 inventory_transfer_status /* Bug#11067286 */ )
4403 (SELECT
4404 Invoice_Id, -- invoice_id
4405 Invoice_Line_Number, -- invoice_line_number
4406 Dist_Code_Combination_Id, -- dist_code_combination_id
4407 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
4408 sysdate, -- last_update_date
4409 p_Last_Updated_By, -- last_updated_by
4410 /* Bug 5584997, Getting the accounting_date from line rec
4411 --Bug9345786. Commented the following code.
4412 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.accounting_date,
4413 ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)),
4414 -- accounting_date
4415 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.period_name,
4416 ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)),
4417 -- period_name, */
4418 --Bug9345786. Added following code instead.
4419 (CASE
4420 WHEN (P_calling_mode = 'UNAPPLY_PREPAY') THEN p_line_rec.accounting_date
4421 WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date > p_line_rec.accounting_date) THEN
4422 ap_utilities_pkg.get_reversal_gl_date(accounting_date, org_id)
4423 WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date <= p_line_rec.accounting_date) THEN
4424 ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)
4425 ELSE
4426 ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)
4427 END), -- accounting_date
4428 (CASE
4429 WHEN (P_calling_mode = 'UNAPPLY_PREPAY') THEN p_line_rec.period_name
4430 WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date > p_line_rec.accounting_date) THEN
4431 ap_utilities_pkg.get_reversal_period(accounting_date, org_id)
4432 WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date <= p_line_rec.accounting_date) THEN
4433 ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)
4434 ELSE
4435 ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)
4436 END ), -- period_name
4437 Set_Of_Books_Id, -- set_of_book_id
4438 -1 * Amount, -- amount
4439 Description, -- description
4440 Type_1099, -- type_1099
4441 Tax_Code_Id, -- tax_code_id
4442 'N', -- posted_flag,
4443 Batch_Id, -- batch_id
4444 DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
4445 -- quantity_invoiced
4446 DECODE(corrected_quantity, NULL, '',
4447 DECODE(dist_match_type, 'PRICE_CORRECTION',
4448 corrected_quantity, (-1)*corrected_quantity) ),
4449 -- corrected_quanity
4450 DECODE(unit_price, NULL,'',
4451 DECODE(dist_match_type, 'PRICE_CORRECTION',
4452 (-1)*unit_price, unit_price) ),
4453 -- unit_price,
4454 NULL , -- match_status_flag bug 11810934
4455 attribute_category, -- attribute_category
4456 attribute1, -- attribute1
4457 attribute2, -- attribute2
4458 attribute3, -- attribute3
4459 attribute4, -- attribute4
4460 attribute5, -- attribute5
4461 NULL, -- prepay_amount_remaining
4462 prepay_distribution_id, -- prepay_distribution_id
4463 'U', -- assets_addition_flag
4464 Assets_Tracking_Flag, -- assets_tracking_flag
4465 Distribution_Line_Number + l_max_line_num , -- distribution_line_number
4466 Line_Type_Lookup_Code, -- line_type_lookup_code
4467 Po_Distribution_Id, -- po_distribution_id
4468 -1 * Base_Amount, -- base_amount
4469 DECODE(Pa_Addition_Flag, 'E', 'E', 'R', 'R', 'N'), -- pa_addition_flag bug10012305
4470 DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
4471 'N', -- accrual_posted_flag,
4472 'N', -- cash_posted_flag,
4473 p_Last_Update_Login, -- last_update_login
4474 sysdate, -- creation_date,
4475 FND_GLOBAL.user_id, -- created_by,
4476 -1 * Stat_Amount, -- stat_amount
4477 attribute11, -- attribute11,
4478 attribute12, -- attribute12,
4479 attribute13, -- attribute13,
4480 attribute14, -- attribute14,
4481 attribute6, -- attribute6,
4482 attribute7, -- attribute7,
4483 attribute8, -- attribute8,
4484 attribute9, -- attribute9,
4485 attribute10, -- attribute10,
4486 attribute15, -- attribute15,
4487 'Y', -- reversal_flag,
4488 Parent_Invoice_Id, -- parent_invoice_id
4489 Income_Tax_Region, -- income_tax_region
4490 Final_Match_Flag, -- final_match_flag
4491 Expenditure_Item_Date, -- expenditure_item_date
4492 Expenditure_Organization_Id, -- expenditure_orgnization_id
4493 Expenditure_Type, -- expenditure_type
4494 -1 * Pa_Quantity, -- pa_quantity
4495 Project_Id, -- project_id
4496 Task_Id, -- task_id
4497 -1 * Quantity_Variance, -- quantity_variance
4498 -1 * Base_Quantity_Variance, -- base quantity_variance
4499 awt_flag, -- awt_flag
4500 awt_group_id, -- awt_group_id,
4501 awt_tax_rate_id, -- awt_tax_rate_id
4502 awt_gross_amount, -- awt_gross_amount
4503 reference_1, -- reference_1
4504 reference_2, -- reference_2
4505 other_invoice_id, -- other_invoice_id
4506 awt_invoice_id, -- awt_invoice_id
4507 awt_origin_group_id, -- awt_origin_group_id
4508 FND_GLOBAL.prog_appl_id, -- program_application_id
4509 FND_GLOBAL.conc_program_id, -- program_id
4510 SYSDATE, -- program_update_date,
4511 FND_GLOBAL.conc_request_id, -- request_id
4512 tax_recoverable_flag, -- tax_recoverable_flag
4513 award_id, -- award_id
4514 start_expense_date, -- start_expense_date
4515 merchant_document_number, -- merchant_document_number
4516 merchant_name, -- merchant_name
4517 merchant_tax_reg_number, -- merchant_tax_reg_number
4518 merchant_taxpayer_id, -- merchant_taxpayer_id
4519 country_of_supply, -- country_of_supply
4520 merchant_reference, -- merchant_reference
4521 invoice_distribution_id, -- Parent_Reversal_Id
4522 rcv_transaction_id, -- rcv_transaction_id
4523 dist_match_type, -- dist_match_type
4524 matched_uom_lookup_code, -- matched_uom_lookup_code
4525 global_attribute_category, -- global_attribute_category
4526 global_attribute1, -- global_attribute1
4527 global_attribute2, -- global_attribute2
4528 global_attribute3, -- global_attribute3
4529 global_attribute4, -- global_attribute4
4530 global_attribute5, -- global_attribute5
4531 global_attribute6, -- global_attribute6
4532 global_attribute7, -- global_attribute7
4533 global_attribute8, -- global_attribute8
4534 global_attribute9, -- global_attribute9
4535 global_attribute10, -- global_attribute10
4536 global_attribute11, -- global_attribute11
4537 global_attribute12, -- global_attribute12
4538 global_attribute13, -- global_attribute13
4539 global_attribute14, -- global_attribute14
4540 global_attribute15, -- global_attribute15
4541 global_attribute16, -- global_attribute16
4542 global_attribute17, -- global_attribute17
4543 global_attribute18, -- global_attribute18
4544 global_attribute19, -- global_attribute19
4545 global_attribute20, -- global_attribute20
4546 receipt_verified_flag, -- receipt_verified_flag
4547 receipt_required_flag, -- receipt_required_flag
4548 receipt_missing_flag, -- receipt_missing_flag
4549 justification, -- justification
4550 expense_Group, -- expense_Group
4551 end_Expense_Date, -- end_Expense_Date
4552 receipt_Currency_Code, -- receipt_Currency_Code
4553 receipt_Conversion_Rate, -- receipt_Conversion_Rate
4554 receipt_Currency_Amount, -- receipt_Currency_Amount
4555 daily_Amount, -- daily_Amount
4556 web_Parameter_Id, -- web_Parameter_Id
4557 adjustment_Reason, -- adjustment_Reason
4558 credit_Card_Trx_Id, -- credit_Card_Trx_Id
4559 company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
4560 org_id, -- MOAC project org_id
4561 -1* rounding_amt, -- rounding_amt
4562 charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
4563 corrected_invoice_dist_id, -- corrected_invoice_dist_id
4564 DECODE( related_id, NULL, NULL,
4565 invoice_distribution_id,
4566 ap_invoice_distributions_s.CURRVAL,
4567 --bugfix:4921399
4568 NULL ), -- related_id
4569 asset_book_type_code, -- asset_book_type_code
4570 asset_category_id, -- asset_category_id
4571 NULL, -- accounting_event_id
4572 decode(p_calling_mode, 'CANCEL',decode ( line_type_lookup_code ,'PREPAY' , NULL ,'Y'),null), -- cancellation_flag bug9173973
4573 'PERMANENT',
4574 intended_use, -- intended_use
4575 'N', -- rcv_charge_addition_flag
4576 awt_related_id, -- Bug 8745752
4577 retained_invoice_dist_id, -- Bug 8824235
4578 pay_awt_group_id, /* Bug 9821980 */
4579 'N' /*Bug#11067286*/
4580 FROM ap_invoice_distributions_all
4581 WHERE invoice_id = p_line_rec.invoice_id
4582 AND invoice_line_number = p_line_rec.line_number
4583 AND line_type_lookup_code NOT IN
4584 ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
4585 AND (line_type_lookup_code <> 'AWT' OR
4586 (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
4587 AND dist_code_combination_id
4588 IN (SELECT dist_code_combination_id
4589 FROM ap_invoice_distributions_all
4590 WHERE invoice_id = p_line_rec.invoice_id
4591 AND invoice_line_number = p_line_rec.line_number
4592 GROUP BY dist_code_combination_id,
4593 po_distribution_id,
4594 line_type_lookup_code,
4595 prepay_distribution_id,
4596 assets_tracking_flag,
4597 type_1099,
4598 project_id,
4599 task_id,
4600 expenditure_organization_id,
4601 expenditure_type,
4602 expenditure_item_date,
4603 pa_addition_flag,
4604 awt_group_id,
4605 rcv_transaction_id) -- Bug 4159731
4606 AND nvl(po_distribution_id,-99) IN
4607 (SELECT
4608 NVL(po_distribution_id, -99)
4609 FROM ap_invoice_distributions_all
4610 WHERE invoice_id = p_line_rec.invoice_id
4611 AND invoice_line_number = p_line_rec.line_number
4612 GROUP BY dist_code_combination_id,
4613 po_distribution_id,
4614 line_type_lookup_code,
4615 prepay_distribution_id,
4616 assets_tracking_flag,
4617 type_1099,
4618 project_id,
4619 task_id,
4620 expenditure_organization_id,
4621 expenditure_type,
4622 expenditure_item_date,
4623 pa_addition_flag,
4624 awt_group_id,
4625 rcv_transaction_id, -- Bug 4159731
4626 tax_code_id) -- Bug 5191117
4627 AND nvl(reversal_flag,'N') <> 'Y' -- Bug 8326344
4628 ) ;
4629
4630
4631 --bugfix:4921399
4632 UPDATE ap_invoice_distributions aid
4633 SET aid.related_id =
4634 (SELECT invoice_distribution_id
4635 FROM ap_invoice_distributions aid1
4636 WHERE aid1.invoice_id = aid.invoice_id
4637 AND aid1.invoice_line_number = aid.invoice_line_number
4638 AND aid1.parent_reversal_id =
4639 (SELECT related_id
4640 FROM ap_invoice_distributions aid2
4641 WHERE aid2.invoice_id = aid.invoice_id
4642 AND aid2.invoice_line_number = aid.invoice_line_number
4643 AND aid2.invoice_distribution_id = aid.parent_reversal_id)
4644 )
4645 WHERE aid.related_id IS NULL
4646 AND aid.parent_reversal_id IS NOT NULL
4647 AND aid.invoice_id = p_line_rec.invoice_id
4648 AND aid.invoice_line_number = p_line_rec.line_number
4649 AND aid.reversal_flag = 'Y';
4650
4651 -- Updating Retained_Amount_Remaining 8824235
4652 UPDATE ap_invoice_distributions_all aid
4653 SET aid.retained_amount_remaining = aid.retained_amount_remaining -
4654 NVL((SELECT sum(d2.amount)
4655 FROM ap_invoice_distributions_all d2
4656 WHERE d2.parent_reversal_id is not null
4657 AND d2.reversal_flag = 'Y'
4658 AND d2.invoice_id = p_line_rec.invoice_id
4659 AND d2.invoice_line_number = p_line_rec.line_number
4660 AND d2.match_status_flag = 'N'
4661 AND d2.retained_invoice_dist_id = aid.invoice_distribution_id), 0)
4662 WHERE invoice_distribution_id in
4663 (SELECT DISTINCT retained_invoice_dist_id
4664 FROM ap_invoice_distributions_all d3
4665 WHERE d3.reversal_flag = 'Y'
4666 AND d3.invoice_id = p_line_rec.invoice_id
4667 AND d3.invoice_line_number = p_line_rec.line_number
4668 AND d3.parent_reversal_id is not null
4669 AND d3.match_status_flag = 'N'
4670 AND d3.retained_invoice_dist_id = aid.invoice_distribution_id);
4671
4672 -- Bug 8623061 - Start
4673 FOR l_distribution_list_cur in c_distribution_list_cur
4674 LOOP
4675
4676 IF l_distribution_list_cur.award_id Is Not Null Then
4677 l_award_id := GMS_AP_API.GET_DISTRIBUTION_AWARD(l_distribution_list_cur.award_id);
4678
4679 GMS_AP_API.CREATE_AWARD_DISTRIBUTIONS
4680 (p_invoice_id => l_distribution_list_cur.invoice_id,
4681 p_distribution_line_number => l_distribution_list_cur.distribution_line_number,
4682 p_invoice_distribution_id => l_distribution_list_cur.invoice_distribution_id,
4683 p_award_id => l_award_id
4684 );
4685 End If;
4686
4687 END LOOP;
4688 -- Bug 8623061 - End
4689
4690 /*-----------------------------------------------------------------+
4691 | Step 4. Reverse Distribution |
4692 | e.Calling JE package - comment out for now |
4693 +-----------------------------------------------------------------*/
4694 l_debug_info := 'Calling JE Hungarian Inv Distribution Reversal';
4695
4696 /*JE_HU_INV_DIST_REVERSAL.Nullify_Global_attributes
4697 (P_INVOICE_ID => p_line_rec.invoice_id,
4698 P_DIST_MAX_LINE_NUM => l_max_line_num); */
4699
4700 -- Call GMS
4701
4702 l_debug_info := 'Call Create Prepay ADL';
4703
4704 IF (p_calling_mode = 'UNAPPLY_PREPAY') THEN
4705
4706 --bugfix:4542556
4707 SELECT invoice_distribution_id,prepay_distribution_id
4708 BULK COLLECT INTO l_key_value_list,l_key_value_list3
4709 FROM ap_invoice_distributions
4710 WHERE invoice_id = p_line_rec.invoice_id
4711 AND invoice_line_number = p_line_rec.line_number
4712 AND line_type_lookup_code = 'PREPAY'
4713 AND nvl(reversal_flag,'N') = 'Y'
4714 AND parent_reversal_id IS NOT NULL;
4715
4716 FOR l_loop_counter IN NVL(l_key_value_list.FIRST,0) .. NVL(l_key_value_list.LAST,0) LOOP
4717 l_debug_info := 'Update global context code';
4718
4719 --l_global_attr_category := l_key_value_list3(l_loop_counter);
4720 IF (AP_EXTENDED_WITHHOLDING_PKG.Ap_Extended_Withholding_Active) THEN
4721 AP_EXTENDED_WITHHOLDING_PKG.Ap_Ext_Withholding_Prepay (
4722 p_prepay_dist_id => l_key_value_list3(l_loop_counter),
4723 p_invoice_id => p_line_rec.invoice_id,
4724 p_inv_dist_id => l_key_value_list(l_loop_counter),
4725 p_user_id => fnd_global.user_id,
4726 p_last_update_login => p_last_update_login,
4727 p_calling_sequence => p_calling_sequence );
4728 END IF;
4729
4730 END LOOP;
4731
4732 END IF;
4733
4734 /*-----------------------------------------------------------------+
4735 | Step 4. Reverse Distribution |
4736 | g.Set reversal flag to existing distributions |
4737 | for this cancelled invoice |
4738 +-----------------------------------------------------------------*/
4739
4740 l_debug_info := 'Set reversal_flag to Y for existing distributions';
4741
4742 UPDATE ap_invoice_distributions
4743 SET reversal_flag = 'Y'
4744 WHERE invoice_id = p_line_rec.invoice_id
4745 AND invoice_line_number = p_line_rec.line_number;
4746
4747 END IF; -- end of l_distribution_count check
4748
4749 /*-------------------------------------------------------------------+
4750 | Step 5. Call ETAX: Discard Tax Distributions |
4751 +-------------------------------------------------------------------*/
4752
4753 IF (nvl(p_calling_mode, 'DISCARD') <> 'CANCEL') THEN
4754
4755 IF (nvl(p_calling_mode, 'DISCARD') = 'DISCARD') THEN
4756
4757 l_success := ap_etax_pkg.calling_etax(
4758 P_Invoice_id => p_line_rec.invoice_id,
4759 P_Line_Number => p_line_rec.line_number,
4760 P_Calling_Mode => 'DISCARD LINE',
4761 P_All_Error_Messages => 'N',
4762 P_error_code => l_error_code,
4763 P_Calling_Sequence => l_curr_calling_sequence);
4764
4765 IF (NOT l_success) THEN
4766 p_error_code := 'AP_ETX_DISC_LINE_CALC_TAX_FAIL';
4767 p_token := l_error_code;
4768 RETURN FALSE;
4769 END IF;
4770
4771 --bugfix:5697764
4772 ELSIF p_calling_mode = 'UNAPPLY_PREPAY' THEN
4773
4774 l_success := ap_etax_pkg.calling_etax(
4775 P_Invoice_id => p_line_rec.invoice_id,
4776 P_Line_Number => p_line_rec.line_number,
4777 P_Calling_Mode => 'UNAPPLY PREPAY',
4778 P_All_Error_Messages => 'N',
4779 P_error_code => l_error_code,
4780 P_Calling_Sequence => l_curr_calling_sequence);
4781
4782 IF (NOT l_success) THEN
4783 p_error_code := 'AP_ETX_DISC_LINE_CALC_TAX_FAIL';
4784 p_token := l_error_code;
4785 RETURN FALSE;
4786 END IF;
4787
4788 END IF;
4789
4790 END IF;
4791
4792
4793 --bugfix:5697764 start
4794 l_debug_info := 'p_calling_mode is '||p_calling_mode;
4795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4796 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4797 END IF;
4798
4799 --bugfix:5765073 added the begin/end and exception handler
4800 BEGIN
4801 IF (nvl(p_calling_mode,'DISCARD') = 'UNAPPLY_PREPAY') THEN
4802
4803 SELECT aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N')
4804 BULK COLLECT INTO l_tax_line_number,l_prepay_included /*Bug 9841966: Change to bulk collect*/
4805 FROM ap_invoice_distributions aid, --tax dists
4806 ap_invoice_lines ail1, --item line
4807 ap_invoice_distributions aid1 --item distributions
4808 WHERE ail1.invoice_id = aid1.invoice_id
4809 AND ail1.invoice_id = p_line_rec.invoice_id
4810 AND ail1.line_number = p_line_rec.line_number
4811 AND aid.invoice_id = aid1.invoice_id
4812 AND ail1.line_number = aid1.invoice_line_number
4813 -- bug 7376110
4814 -- The below condition added to handle prepayment with inclusive tax.
4815 AND aid.invoice_line_number <> aid1.invoice_line_number
4816 AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
4817 GROUP BY aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N');
4818
4819 l_debug_info := 'First l_tax_line_number is '||l_tax_line_number(1); /*Bug 9841966: Print the first element*/
4820 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4821 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4822 END IF;
4823
4824 IF l_tax_line_number.exists(1) then /*Bug 9841966*/
4825 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
4826 l_prepay_dist_info,
4827 -- replaced NULLS with correct column values for bug #8897523
4828 l_prepay_invoice_id, --NULL
4829 l_prepay_line_number, --NULL
4830 p_line_rec.invoice_id,
4831 l_tax_line_number(1), /*Bug 9841966: Passed the first tax element, this is dummy*/
4832 'UNAPPLICATION',
4833 NULL,
4834 l_curr_calling_sequence,
4835 l_error_code);
4836
4837 IF l_dummy = FALSE THEN
4838 RETURN (FALSE);
4839 END IF;
4840
4841 END IF; /*Bug 9841966*/
4842
4843 FOR l_loop_counter IN NVL(l_tax_line_number.FIRST,0) .. NVL(l_tax_line_number.LAST,-1) LOOP /*Bug 9841966*/
4844 --Update the payment schedules with the unapplied tax amount
4845 IF NVL(l_prepay_included(l_loop_counter), 'N') = 'N' THEN /*Bug 9841966*/
4846
4847 --Bug 7526679
4848 -- The query is modified to select the total amount of latest reversed tax distributions.
4849 SELECT sum(aid1.amount)
4850 INTO l_unapplied_tax_amount
4851 FROM ap_invoice_distributions_all aid1,
4852 ap_invoice_distributions_all aid2
4853 WHERE aid1.invoice_id = p_line_rec.invoice_id
4854 AND aid1.invoice_id = aid2.invoice_id
4855 AND aid1.invoice_line_number = l_tax_line_number(l_loop_counter) /*Bug 9841966: added loop counter*/
4856 AND aid2.invoice_line_number = p_line_rec.line_number
4857 AND NVL(aid1.reversal_flag,'N') = 'Y'
4858 AND NVL(aid2.reversal_flag,'N') = 'Y'
4859 AND aid1.parent_reversal_id IS NOT NULL
4860 AND aid2.parent_reversal_id IS NOT NULL
4861 AND aid1.charge_applicable_to_dist_id = aid2.invoice_distribution_id;
4862 --end of Bug 7526679
4863
4864 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
4865 l_unapplied_tax_amt_pay_curr := GL_Currency_API.Convert_Amount
4866 (l_invoice_currency_code,
4867 l_payment_currency_code,
4868 l_payment_cross_rate_date,
4869 l_payment_cross_rate_type,
4870 (-1)* l_unapplied_tax_amount);
4871 ELSE
4872 l_unapplied_tax_amt_pay_curr := (-1) * l_unapplied_tax_amount;
4873 END IF;
4874
4875 l_debug_info := 'Update the payment schedule with the unapplied exclusive prepay tax amount';
4876
4877 l_debug_info := 'l_unapplied_tax_amt_pay_curr,l_unapplied_tax_amount '||
4878 l_unapplied_tax_amt_pay_curr||','||l_unapplied_tax_amount;
4879 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4880 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4881 END IF;
4882
4883 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule (
4884 p_line_rec.invoice_id,
4885 NULL,
4886 NULL,
4887 l_unapplied_tax_amt_pay_curr,
4888 'UNAPPLICATION',
4889 l_payment_currency_code,
4890 FND_GLOBAL.user_id,
4891 p_last_update_login,
4892 p_calling_sequence,
4893 NULL,
4894 l_error_message);
4895
4896 IF l_dummy = FALSE THEN
4897 RETURN (FALSE) ;
4898 END IF;
4899
4900 END IF; /*l_prepay_included... */
4901 END LOOP; /*Bug 9841966*/
4902
4903 END IF; /*p_calling_mode = 'UNAPPLY PREPAY'...*/
4904
4905 EXCEPTION WHEN OTHERS THEN
4906 NULL;
4907
4908 END ;
4909 --bugfix:5697764 end
4910
4911
4912 /*-----------------------------------------------------------------+
4913 | Step 6 Proactive Cancellation Check--fires Based on profile |
4914 +-----------------------------------------------------------------*/
4915
4916 --9100425
4917
4918
4919 BEGIN
4920 If (nvl(l_cancel_proactive_flag,'N') ='C') and (NVL( P_calling_mode,'DISCARD') = 'DISCARD') THEN
4921
4922 For I in(select invoice_distribution_id
4923 from ap_invoice_distributions aid1
4924 where aid1.invoice_id=P_line_rec.invoice_id
4925 and aid1.invoice_line_number=P_line_rec. line_number
4926 and aid1.parent_reversal_id is null --original dist
4927 --for original dists there is no reversal dist created
4928 and ( not exists (select 1 from ap_invoice_distributions aid2
4929 where aid1.invoice_id=aid2.invoice_id
4930 and aid2.invoice_id=P_line_rec.invoice_id
4931 and aid2.invoice_line_number=P_line_rec.line_number
4932 and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4933 --the reversal dist does not reverse the amount correctly
4934 or exists (select 1 from ap_invoice_distributions aid2
4935 where aid1.invoice_id=aid2.invoice_id
4936 and aid2.invoice_id=P_line_rec.invoice_id
4937 and aid2.invoice_line_number=P_line_rec.line_number
4938 and aid2.parent_reversal_id =aid1.invoice_distribution_id
4939 and -1 * aid2.amount <> aid1.amount))
4940 UNION
4941 select invoice_distribution_id
4942 from ap_invoice_distributions aid1
4943 where aid1.invoice_id=P_line_rec.invoice_id
4944 and aid1.charge_applicable_to_dist_id in (
4945 select aid2.invoice_distribution_id from ap_invoice_distributions aid2
4946 where aid2.invoice_id=P_line_rec.invoice_id
4947 and aid2.invoice_line_number=P_line_rec.line_number)
4948 and aid1.parent_reversal_id is null --original dist
4949 and aid1.line_type_lookup_code not in('MISCELLANEOUS','FREIGHT')
4950 --for original dists there is no reversal dist created
4951 and ( not exists (select 1 from ap_invoice_distributions aid2
4952 where aid1.invoice_id=aid2.invoice_id
4953 and aid2.invoice_id=P_line_rec.invoice_id
4954 and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4955 --the reversal dist does not reverse the amount correctly
4956 or exists (select 1 from ap_invoice_distributions aid2
4957 where aid1.invoice_id=aid2.invoice_id
4958 and aid2.invoice_id=P_line_rec.invoice_id
4959 and aid2.parent_reversal_id =aid1.invoice_distribution_id
4960 and -1 * aid2.amount <> aid1.amount)))
4961
4962 LOOP
4963 prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
4964 prob_dist_count:=prob_dist_count+1;
4965 end loop;
4966 IF prob_dist_count > 0 then
4967 p_error_code :='AP_INV_DIS_CAN_FAIL';
4968 P_Token := prob_dist_list;
4969 ROLLBACK TO SAVEPOINT CANCEL_CHECK;
4970 RETURN(FALSE);
4971 end if;
4972
4973 END IF;
4974 EXCEPTION WHEN OTHERS THEN
4975 NULL;
4976 END;
4977 --9100425
4978
4979 l_debug_info := 'p_line_rec.invoice_id,p_line_rec.line_number : '
4980 ||p_line_rec.invoice_id||','||p_line_rec.line_number;
4981 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4982 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4983 END IF;
4984
4985 IF (l_shipment_amt_recouped <> 0 OR l_shipment_qty_recouped <> 0) THEN
4986
4987 --Start of 8928639
4988 l_debug_info := 'Open Cursor prepay_inv_cur';
4989 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4990 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4991 END IF;
4992
4993 OPEN prepay_inv_cur;
4994 LOOP
4995
4996 FETCH prepay_inv_cur
4997 INTO l_prepay_invoice_id,
4998 l_prepay_line_number;
4999 EXIT WHEN prepay_inv_cur%NOTFOUND;
5000
5001 l_debug_info := 'l_prepay_invoice_id: '||l_prepay_invoice_id||
5002 'l_prepay_line_number: '||l_prepay_line_number;
5003
5004 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5005 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
5006 END IF;
5007
5008 --End 8928639
5009
5010 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
5011 l_prepay_dist_info,
5012 -- replaced NULLS with correct column values for bug #8897523
5013 l_prepay_invoice_id, --NULL
5014 l_prepay_line_number, --NULL
5015 p_line_rec.invoice_id,
5016 p_line_rec.line_number,
5017 'UNAPPLICATION',
5018 NULL,
5019 l_curr_calling_sequence,
5020 l_error_code);
5021
5022 IF l_dummy = FALSE THEN
5023 RETURN (FALSE);
5024 END IF;
5025
5026 END LOOP;
5027 CLOSE prepay_inv_cur;
5028
5029 END IF;
5030
5031 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5032 l_debug_info := 'After Discard line the distribution look like:';
5033 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name,
5034 l_debug_info);
5035
5036 FOR l_inv_dist_rec IN dist_debug_cur
5037 LOOP
5038 l_debug_info :='they are '||
5039 'dist_type = ' || l_inv_dist_rec.line_type_lookup_code||
5040 'amount=' || l_inv_dist_rec.amount ||
5041 'base_amount =' || l_inv_dist_rec.base_amount ||
5042 'match_status_flag=' ||l_inv_dist_rec.match_status_flag ;
5043 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name,
5044 l_debug_info);
5045 END LOOP;
5046 END IF;
5047
5048 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5049 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
5050 'AP_INVOICE_LINES_PKG.Discard_Inv_Line(-)');
5051 END IF;
5052
5053 p_error_code := NULL;
5054 RETURN (TRUE);
5055
5056 END IF; /* l_ok_to_discard OR l_ok_to_cancel */
5057
5058 EXCEPTION
5059 WHEN OTHERS THEN
5060 IF (SQLCODE <> -20001) THEN
5061 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5062 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5063 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
5064 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5065 ' P_invoice_id = ' || p_line_rec.invoice_id
5066 ||' P_line_number = ' || p_line_rec.line_number
5067 ||' P_last_updated_by = ' || P_last_updated_by
5068 ||' P_last_update_login = ' || P_last_update_login
5069 ||' P_calling_mode = ' || p_calling_mode);
5070 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5071 END IF;
5072
5073 IF ( po_dists_cur%ISOPEN ) THEN
5074 CLOSE po_dists_cur;
5075 END IF;
5076
5077 IF (c_charge_lines%isopen) THEN
5078 close c_charge_lines;
5079 END IF;
5080
5081 APP_EXCEPTION.RAISE_EXCEPTION;
5082
5083 END Discard_Inv_line;
5084
5085 FUNCTION Reverse_Charge_Distributions
5086 (p_inv_line_rec IN AP_INVOICE_LINES_ALL%rowtype,
5087 p_calling_mode IN VARCHAR2,
5088 x_error_code OUT NOCOPY VARCHAR2,
5089 x_debug_info OUT NOCOPY VARCHAR2,
5090 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
5091
5092 l_curr_calling_sequence VARCHAR2(2000);
5093 l_debug_info VARCHAR2(240);
5094 l_api_name VARCHAR2(50);
5095 l_max_line_num ap_invoice_distributions.distribution_line_number%TYPE;
5096 l_tax_distributions_exist BOOLEAN := FALSE ; --Bug14383132
5097 l_self_assess_tax_dist_exist BOOLEAN := FALSE ; --Bug14383132
5098
5099 BEGIN
5100 l_api_name := 'Reverse_Charge_Distributions';
5101 ------------------------------------------------------
5102 Print (l_api_name,'Reverse_Charge_Distributions (+)');
5103 ------------------------------------------------------
5104 l_curr_calling_sequence := 'AP_INVOICE_LINES_PKG.Reverse_Charge_Distributions <-' || p_calling_sequence;
5105
5106 l_max_line_num := AP_INVOICE_LINES_PKG.get_max_dist_line_num
5107 (p_inv_line_rec.invoice_id,
5108 p_inv_line_rec.line_number);
5109
5110 --Bug 8733916
5111
5112 UPDATE ap_invoice_distributions aid
5113 SET aid.encumbered_flag = 'R'
5114 WHERE aid.invoice_id = p_inv_line_rec.invoice_id
5115 AND aid.invoice_line_number = p_inv_line_rec.line_number
5116 AND nvl(aid.match_status_flag,'N') <> 'A'
5117 AND nvl(aid.encumbered_flag,'N') IN ('N','H','P')
5118 AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
5119 AND nvl(aid.reversal_flag,'N')<>'Y'
5120 AND EXISTS (SELECT 1
5121 FROM financials_system_params_all fsp
5122 WHERE fsp.org_id = aid.org_id
5123 AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
5124
5125 ------------------------------------------------------
5126 l_debug_info := 'Insert reverse charge distributions';
5127 Print (l_api_name,l_debug_info);
5128 ------------------------------------------------------
5129
5130 INSERT INTO ap_invoice_distributions_all(
5131 invoice_id,
5132 invoice_line_number,
5133 dist_code_combination_id,
5134 invoice_distribution_id,
5135 last_update_date,
5136 last_updated_by,
5137 accounting_date,
5138 period_name,
5139 set_of_books_id,
5140 amount,
5141 description,
5142 type_1099,
5143 tax_code_id,
5144 posted_flag,
5145 batch_id,
5146 quantity_invoiced,
5147 corrected_quantity,
5148 unit_price,
5149 match_status_flag,
5150 attribute_category,
5151 attribute1,
5152 attribute2,
5153 attribute3,
5154 attribute4,
5155 attribute5,
5156 prepay_amount_remaining,
5157 prepay_distribution_id,
5158 assets_addition_flag,
5159 assets_tracking_flag,
5160 distribution_line_number,
5161 line_type_lookup_code,
5162 po_distribution_id,
5163 base_amount,
5164 pa_addition_flag,
5165 encumbered_flag,
5166 accrual_posted_flag,
5167 cash_posted_flag,
5168 last_update_login,
5169 creation_date,
5170 created_by,
5171 stat_amount,
5172 attribute11,
5173 attribute12,
5174 attribute13,
5175 attribute14,
5176 attribute6,
5177 attribute7,
5178 attribute8,
5179 attribute9,
5180 attribute10,
5181 attribute15,
5182 reversal_flag,
5183 parent_invoice_id,
5184 income_tax_region,
5185 final_match_flag,
5186 expenditure_item_date,
5187 expenditure_organization_id,
5188 expenditure_type,
5189 pa_quantity,
5190 project_id,
5191 task_id,
5192 quantity_variance,
5193 base_quantity_variance,
5194 awt_flag,
5195 awt_group_id,
5196 awt_tax_rate_id,
5197 awt_gross_amount,
5198 reference_1,
5199 reference_2,
5200 other_invoice_id,
5201 awt_invoice_id,
5202 awt_origin_group_id,
5203 program_application_id,
5204 program_id,
5205 program_update_date,
5206 request_id,
5207 tax_recoverable_flag,
5208 award_id,
5209 start_expense_date,
5210 merchant_document_number,
5211 merchant_name,
5212 merchant_tax_reg_number,
5213 merchant_taxpayer_id,
5214 country_of_supply,
5215 merchant_reference,
5216 parent_reversal_id,
5217 rcv_transaction_id,
5218 dist_match_type,
5219 matched_uom_lookup_code,
5220 global_attribute_category,
5221 global_attribute1,
5222 global_attribute2,
5223 global_attribute3,
5224 global_attribute4,
5225 global_attribute5,
5226 global_attribute6,
5227 global_attribute7,
5228 global_attribute8,
5229 global_attribute9,
5230 global_attribute10,
5231 global_attribute11,
5232 global_attribute12,
5233 global_attribute13,
5234 global_attribute14,
5235 global_attribute15,
5236 global_attribute16,
5237 global_attribute17,
5238 global_attribute18,
5239 global_attribute19,
5240 global_attribute20,
5241 receipt_verified_flag,
5242 receipt_required_flag,
5243 receipt_missing_flag,
5244 justification,
5245 expense_Group,
5246 end_Expense_Date,
5247 receipt_Currency_Code,
5248 receipt_Conversion_Rate,
5249 receipt_Currency_Amount,
5250 daily_Amount,
5251 web_Parameter_Id,
5252 adjustment_Reason,
5253 credit_Card_Trx_Id,
5254 company_Prepaid_Invoice_Id,
5255 org_id,
5256 rounding_amt,
5257 charge_applicable_to_dist_id,
5258 corrected_invoice_dist_id,
5259 related_id,
5260 asset_book_type_code,
5261 asset_category_id,
5262 accounting_event_id,
5263 cancellation_flag,
5264 distribution_class,
5265 intended_use,
5266 --Freight and Special Charges
5267 rcv_charge_addition_flag,
5268 pay_awt_group_id, /* Bug 9821980 */
5269 tax_already_distributed_flag) --bug14383132
5270 (SELECT
5271 Invoice_Id, -- invoice_id
5272 Invoice_Line_Number, -- invoice_line_number
5273 Dist_Code_Combination_Id, -- dist_code_combination_id
5274 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
5275 sysdate, -- last_update_date
5276 fnd_global.user_id, -- last_updated_by
5277 /* Bug 5584997, Getting the accounting_date from line rec */
5278 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.accounting_date,
5279 ap_utilities_pkg.get_reversal_gl_date(p_inv_line_rec.accounting_date, org_id)),
5280 -- accounting_date
5281 DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.period_name,
5282 ap_utilities_pkg.get_reversal_period(p_inv_line_rec.accounting_date, org_id)),
5283 -- period_name,
5284 Set_Of_Books_Id, -- set_of_book_id
5285 -1 * Amount, -- amount
5286 Description, -- description
5287 Type_1099, -- type_1099
5288 Tax_Code_Id, -- tax_code_id
5289 'N', -- posted_flag,
5290 Batch_Id, -- batch_id
5291 DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
5292 -- quantity_invoiced
5293 DECODE(corrected_quantity, NULL, '',
5294 DECODE(dist_match_type, 'PRICE_CORRECTION',
5295 corrected_quantity, (-1)*corrected_quantity) ),
5296 -- corrected_quanity
5297 DECODE(unit_price, NULL,'',
5298 DECODE(dist_match_type, 'PRICE_CORRECTION',
5299 (-1)*unit_price, unit_price) ),
5300 -- unit_price,
5301 NULL , -- match_status_flag bug 11810934
5302 attribute_category, -- attribute_category
5303 attribute1, -- attribute1
5304 attribute2, -- attribute2
5305 attribute3, -- attribute3
5306 attribute4, -- attribute4
5307 attribute5, -- attribute5
5308 NULL, -- prepay_amount_remaining
5309 prepay_distribution_id, -- prepay_distribution_id
5310 'U', -- assets_addition_flag
5311 Assets_Tracking_Flag, -- assets_tracking_flag
5312 Distribution_Line_Number + l_max_line_num , -- distribution_line_number
5313 Line_Type_Lookup_Code, -- line_type_lookup_code
5314 Po_Distribution_Id, -- po_distribution_id
5315 -1 * Base_Amount, -- base_amount
5316 DECODE(Pa_Addition_Flag, 'E', 'E', 'R', 'R', 'N'), -- pa_addition_flag bug10012305
5317 DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
5318 'N', -- accrual_posted_flag,
5319 'N', -- cash_posted_flag,
5320 fnd_global.login_id, -- last_update_login
5321 sysdate, -- creation_date,
5322 FND_GLOBAL.user_id, -- created_by,
5323 -1 * Stat_Amount, -- stat_amount
5324 attribute11, -- attribute11,
5325 attribute12, -- attribute12,
5326 attribute13, -- attribute13,
5327 attribute14, -- attribute14,
5328 attribute6, -- attribute6,
5329 attribute7, -- attribute7,
5330 attribute8, -- attribute8,
5331 attribute9, -- attribute9,
5332 attribute10, -- attribute10,
5333 attribute15, -- attribute15,
5334 'Y', -- reversal_flag,
5335 Parent_Invoice_Id, -- parent_invoice_id
5336 Income_Tax_Region, -- income_tax_region
5337 Final_Match_Flag, -- final_match_flag
5338 Expenditure_Item_Date, -- expenditure_item_date
5339 Expenditure_Organization_Id, -- expenditure_orgnization_id
5340 Expenditure_Type, -- expenditure_type
5341 -1 * Pa_Quantity, -- pa_quantity
5342 Project_Id, -- project_id
5343 Task_Id, -- task_id
5344 -1 * Quantity_Variance, -- quantity_variance
5345 -1 * Base_Quantity_Variance, -- base quantity_variance
5346 awt_flag, -- awt_flag
5347 awt_group_id, -- awt_group_id,
5348 awt_tax_rate_id, -- awt_tax_rate_id
5349 awt_gross_amount, -- awt_gross_amount
5350 reference_1, -- reference_1
5351 reference_2, -- reference_2
5352 other_invoice_id, -- other_invoice_id
5353 awt_invoice_id, -- awt_invoice_id
5354 awt_origin_group_id, -- awt_origin_group_id
5355 FND_GLOBAL.prog_appl_id, -- program_application_id
5356 FND_GLOBAL.conc_program_id, -- program_id
5357 SYSDATE, -- program_update_date,
5358 FND_GLOBAL.conc_request_id, -- request_id
5359 tax_recoverable_flag, -- tax_recoverable_flag
5360 award_id, -- award_id
5361 start_expense_date, -- start_expense_date
5362 merchant_document_number, -- merchant_document_number
5363 merchant_name, -- merchant_name
5364 merchant_tax_reg_number, -- merchant_tax_reg_number
5365 merchant_taxpayer_id, -- merchant_taxpayer_id
5366 country_of_supply, -- country_of_supply
5367 merchant_reference, -- merchant_reference
5368 invoice_distribution_id, -- Parent_Reversal_Id
5369 rcv_transaction_id, -- rcv_transaction_id
5370 dist_match_type, -- dist_match_type
5371 matched_uom_lookup_code, -- matched_uom_lookup_code
5372 global_attribute_category, -- global_attribute_category
5373 global_attribute1, -- global_attribute1
5374 global_attribute2, -- global_attribute2
5375 global_attribute3, -- global_attribute3
5376 global_attribute4, -- global_attribute4
5377 global_attribute5, -- global_attribute5
5378 global_attribute6, -- global_attribute6
5379 global_attribute7, -- global_attribute7
5380 global_attribute8, -- global_attribute8
5381 global_attribute9, -- global_attribute9
5382 global_attribute10, -- global_attribute10
5383 global_attribute11, -- global_attribute11
5384 global_attribute12, -- global_attribute12
5385 global_attribute13, -- global_attribute13
5386 global_attribute14, -- global_attribute14
5387 global_attribute15, -- global_attribute15
5388 global_attribute16, -- global_attribute16
5389 global_attribute17, -- global_attribute17
5390 global_attribute18, -- global_attribute18
5391 global_attribute19, -- global_attribute19
5392 global_attribute20, -- global_attribute20
5393 receipt_verified_flag, -- receipt_verified_flag
5394 receipt_required_flag, -- receipt_required_flag
5395 receipt_missing_flag, -- receipt_missing_flag
5396 justification, -- justification
5397 expense_Group, -- expense_Group
5398 end_Expense_Date, -- end_Expense_Date
5399 receipt_Currency_Code, -- receipt_Currency_Code
5400 receipt_Conversion_Rate, -- receipt_Conversion_Rate
5401 receipt_Currency_Amount, -- receipt_Currency_Amount
5402 daily_Amount, -- daily_Amount
5403 web_Parameter_Id, -- web_Parameter_Id
5404 adjustment_Reason, -- adjustment_Reason
5405 credit_Card_Trx_Id, -- credit_Card_Trx_Id
5406 company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
5407 org_id, -- MOAC project org_id
5408 -1* rounding_amt, -- rounding_amt
5409 charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
5410 corrected_invoice_dist_id, -- corrected_invoice_dist_id
5411 DECODE( related_id, NULL, NULL,
5412 invoice_distribution_id,
5413 ap_invoice_distributions_s.CURRVAL,
5414 --bugfix:4921399
5415 NULL ), -- related_id
5416 asset_book_type_code, -- asset_book_type_code
5417 asset_category_id, -- asset_category_id
5418 NULL, -- accounting_event_id
5419 decode(p_calling_mode, 'CANCEL',decode ( line_type_lookup_code ,'PREPAY' , NULL ,'Y'),null), -- cancellation_flag bug9173973
5420 'PERMANENT',
5421 intended_use, -- intended_use
5422 'N', -- rcv_charge_addition_flag
5423 pay_awt_group_id, /* Bug 9821980 */
5424 'Y'
5425 FROM ap_invoice_distributions_all
5426 WHERE invoice_id = p_inv_line_rec.invoice_id
5427 AND invoice_line_number = p_inv_line_rec.line_number
5428 AND (reversal_flag is null
5429 or reversal_flag = 'N')
5430 AND (
5431 (line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
5432 and prepay_distribution_id IS NULL) OR
5433 prepay_distribution_id IS NOT NULL
5434 )
5435 AND (line_type_lookup_code <> 'AWT' OR
5436 (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
5437 ) ;
5438
5439 ---------------------------------------------------------------
5440 l_debug_info := 'Update reversal_flag on charge distributions';
5441 Print (l_api_name,l_debug_info);
5442 ---------------------------------------------------------------
5443 --Bug8733916
5444
5445 UPDATE ap_invoice_distributions
5446 SET reversal_flag = 'Y'
5447 WHERE invoice_id = p_inv_line_rec.invoice_id
5448 AND invoice_line_number = p_inv_line_rec.line_number
5449 AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV');
5450
5451 --------------------------------------------------------------
5452 Print(l_api_name,'Reverse_Charge_Distributions (-)');
5453 --------------------------------------------------------------
5454 --Bug14383132 : Call New API To INSERT above REVERSED CHARGE DISTRIBUTIONS
5455 --INTO zx_reverse_dist_gt. Once distributions are inserted CALL
5456 --ZX_API_PUB.Reverse_Distributions(). Parameter list to this
5457 --Function is listed in update *** PGAYEN 07/31/12 12:00 am *** on
5458 --bug14383132
5459 --Similar code is present in AP_ETAX_SERVICES_PKG.CANCEL_INVOICe
5460
5461 --Bug14383132: Start
5462
5463 ---------------------------------------------------------------
5464 l_debug_info := 'p_inv_line_rec.invoice_id: '||p_inv_line_rec.invoice_id||' p_inv_line_rec.line_number: '||p_inv_line_rec.line_number;
5465 Print (l_api_name,l_debug_info);
5466 ---------------------------------------------------------------
5467
5468 l_tax_distributions_exist := tax_distributions_exist (p_invoice_id=>p_inv_line_rec.invoice_id,
5469 p_line_number=>p_inv_line_rec.line_number);
5470
5471 l_self_assess_tax_dist_exist := self_assess_tax_dist_exist (p_invoice_id=>p_inv_line_rec.invoice_id,
5472 p_line_number=>p_inv_line_rec.line_number);
5473
5474 IF l_tax_distributions_exist THEN
5475 ---------------------------------------------------------------
5476 l_debug_info := 'Insert into zx_reverse_dist_gt';
5477 Print (l_api_name,l_debug_info);
5478 ---------------------------------------------------------------
5479 INSERT INTO zx_reverse_dist_gt(
5480 internal_organization_id,
5481 reversing_appln_id,
5482 reversing_entity_code,
5483 reversing_evnt_cls_code,
5484 reversing_trx_id,
5485 reversing_trx_level_type,
5486 reversing_trx_line_id,
5487 reversing_trx_line_dist_id,
5488 reversing_tax_line_id,
5489 reversed_appln_id,
5490 reversed_entity_code,
5491 reversed_evnt_cls_code,
5492 reversed_trx_id,
5493 reversed_trx_level_type,
5494 reversed_trx_line_id,
5495 reversed_trx_line_dist_id,
5496 reversed_tax_line_id
5497 )
5498 select distinct
5499 item_dist.org_id internal_organization_id,
5500 zx_dist.application_id reversing_appln_id,
5501 zx_dist.entity_code reversing_entity_code,
5502 zx_dist.event_class_code reversing_evnt_cls_code,
5503 zx_dist.trx_id reversing_trx_id,
5504 zx_dist.trx_level_type reversing_trx_level_type,
5505 zx_dist.trx_line_id reversing_trx_line_id,
5506 reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
5507 zx_dist.tax_line_id reversing_tax_line_id,
5508 zx_dist.application_id reversed_appln_id,
5509 zx_dist.entity_code reversed_entity_code,
5510 zx_dist.event_class_code reversed_evnt_cls_code,
5511 zx_dist.trx_id reversed_trx_id,
5512 zx_dist.trx_level_type reversed_trx_level_type,
5513 zx_dist.trx_line_id reversed_trx_line_id,
5514 zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5515 zx_dist.tax_line_id reversed_tax_line_id
5516 from ap_invoice_distributions_all item_dist,
5517 ap_invoice_distributions_all tax_dist,
5518 ap_invoice_distributions_all reverse_dist,
5519 zx_rec_nrec_dist zx_dist
5520 where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5521 and tax_dist.invoice_id = item_dist.invoice_id
5522 and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5523 and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
5524 and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')
5525 and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
5526 and nvl(zx_dist.reverse_flag, 'N') = 'N'
5527 and item_dist.invoice_line_number = p_inv_line_rec.line_number;
5528
5529 l_reverse_dist_count := l_reverse_dist_count+SQL%ROWCOUNT;
5530 --------------------------------------------------------------------------------------------
5531 l_debug_info := 'Row Count inserted into zx_reverse_dist_gt: ' || l_reverse_dist_count;
5532 Print (l_api_name,l_debug_info);
5533 --------------------------------------------------------------------------------------------
5534
5535 END IF;
5536
5537 IF l_self_assess_tax_dist_exist THEN
5538 ---------------------------------------------------------------
5539 l_debug_info := 'Inserting self assessed tax entries into zx_reverse_dist_gt';
5540 Print (l_api_name,l_debug_info);
5541 ---------------------------------------------------------------
5542 INSERT INTO zx_reverse_dist_gt(
5543 internal_organization_id,
5544 reversing_appln_id,
5545 reversing_entity_code,
5546 reversing_evnt_cls_code,
5547 reversing_trx_id,
5548 reversing_trx_level_type,
5549 reversing_trx_line_id,
5550 reversing_trx_line_dist_id,
5551 reversing_tax_line_id,
5552 reversed_appln_id,
5553 reversed_entity_code,
5554 reversed_evnt_cls_code,
5555 reversed_trx_id,
5556 reversed_trx_level_type,
5557 reversed_trx_line_id,
5558 reversed_trx_line_dist_id,
5559 reversed_tax_line_id
5560 )
5561 select distinct
5562 item_dist.org_id internal_organization_id,
5563 zx_dist.application_id reversing_appln_id,
5564 zx_dist.entity_code reversing_entity_code,
5565 zx_dist.event_class_code reversing_evnt_cls_code,
5566 zx_dist.trx_id reversing_trx_id,
5567 zx_dist.trx_level_type reversing_trx_level_type,
5568 zx_dist.trx_line_id reversing_trx_line_id,
5569 reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
5570 zx_dist.tax_line_id reversing_tax_line_id,
5571 zx_dist.application_id reversed_appln_id,
5572 zx_dist.entity_code reversed_entity_code,
5573 zx_dist.event_class_code reversed_evnt_cls_code,
5574 zx_dist.trx_id reversed_trx_id,
5575 zx_dist.trx_level_type reversed_trx_level_type,
5576 zx_dist.trx_line_id reversed_trx_line_id,
5577 zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5578 zx_dist.tax_line_id reversed_tax_line_id
5579 from ap_invoice_distributions_all item_dist,
5580 ap_self_assessed_tax_dist_all tax_dist,
5581 ap_invoice_distributions_all reverse_dist,
5582 zx_rec_nrec_dist zx_dist
5583 where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5584 and tax_dist.invoice_id = item_dist.invoice_id
5585 and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5586 and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
5587 and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
5588 and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
5589 and nvl(zx_dist.reverse_flag, 'N') = 'N'
5590 and item_dist.invoice_line_number = p_inv_line_rec.line_number;
5591
5592 l_sa_reverse_dist_count := l_sa_reverse_dist_count+SQL%ROWCOUNT;
5593 --------------------------------------------------------------------------------------------
5594 l_debug_info := 'Row Count inserted into zx_reverse_dist_gt: ' || l_sa_reverse_dist_count;
5595 Print (l_api_name,l_debug_info);
5596 --------------------------------------------------------------------------------------------
5597
5598 END IF;
5599 --Bug14383132: End
5600
5601 RETURN TRUE;
5602
5603 EXCEPTION
5604 WHEN OTHERS THEN
5605 IF (SQLCODE <> -20001) THEN
5606 FND_MESSAGE.SET_NAME ('SQLAP','AP_DEBUG');
5607 FND_MESSAGE.SET_TOKEN ('ERROR',SQLERRM);
5608 FND_MESSAGE.SET_TOKEN ('CALLING_SEQUENCE',l_curr_calling_sequence);
5609 FND_MESSAGE.SET_TOKEN ('PARAMETERS',
5610 ' P_invoice_id = '|| p_inv_line_rec.invoice_id
5611 ||' P_line_number = '|| p_inv_line_rec.line_number
5612 ||' P_calling_mode = '|| p_calling_mode);
5613 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5614 END IF;
5615
5616 x_debug_info := l_debug_info;
5617
5618 RETURN FALSE;
5619
5620 END Reverse_Charge_Distributions;
5621
5622 --Bug14383132: Start
5623 FUNCTION TAX_DISTRIBUTIONS_EXIST
5624 (p_invoice_id IN NUMBER,
5625 p_line_number IN NUMBER) RETURN BOOLEAN IS
5626
5627 l_dummy VARCHAR2(40);
5628 l_api_name VARCHAR2(50);
5629 l_debug_info VARCHAR2(240);
5630
5631 BEGIN
5632 l_api_name := 'TAX_DISTRIBUTIONS_EXIST';
5633 --------------------------------------------------------------------------------------------
5634 l_debug_info := 'TAX_DISTRIBUTIONS_EXIST: Checking if there is tax distributions exist';
5635 Print (l_api_name,l_debug_info);
5636 --------------------------------------------------------------------------------------------
5637
5638 SELECT 'Tax Distributions Exist'
5639 INTO l_dummy
5640 FROM ap_invoice_distributions_all item_dist,
5641 ap_invoice_distributions_all tax_dist
5642 WHERE tax_dist.invoice_id = p_invoice_id
5643 and tax_dist.invoice_id = item_dist.invoice_id
5644 and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5645 and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')
5646 and item_dist.invoice_line_number = p_line_number
5647 AND NVL(tax_dist.reversal_flag, 'N') <> 'Y'
5648 AND rownum = 1;
5649
5650 --------------------------------
5651 Print (l_api_name,l_dummy);
5652 --------------------------------
5653
5654 RETURN (l_dummy IS NOT NULL);
5655
5656 EXCEPTION
5657 WHEN OTHERS THEN
5658 RETURN FALSE;
5659 END TAX_DISTRIBUTIONS_EXIST;
5660
5661 FUNCTION SELF_ASSESS_TAX_DIST_EXIST
5662 (p_invoice_id IN NUMBER,
5663 p_line_number IN NUMBER) RETURN BOOLEAN IS
5664
5665 l_dummy VARCHAR2(40);
5666 l_api_name VARCHAR2(50);
5667 l_debug_info VARCHAR2(240);
5668
5669 BEGIN
5670 l_api_name := 'SELF_ASSESS_TAX_DIST_EXIST';
5671 --------------------------------------------------------------------------------------------
5672 l_debug_info := 'SELF_ASSESS_TAX_DIST_EXIST: Checking if there is self assess tax distributions exist';
5673 Print (l_api_name,l_debug_info);
5674 --------------------------------------------------------------------------------------------
5675
5676 SELECT ' self assess Tax Distributions Exist'
5677 INTO l_dummy
5678 FROM ap_self_assessed_tax_dist_all asat,
5679 zx_rec_nrec_dist zx_dist
5680 WHERE asat.invoice_id = p_invoice_id
5681 AND asat.invoice_line_number = p_line_number
5682 AND asat.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
5683 AND zx_dist.self_assessed_flag = 'Y'
5684 AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
5685 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
5686 AND rownum = 1;
5687
5688 ---------------------------------
5689 Print (l_api_name,l_dummy);
5690 ---------------------------------
5691
5692
5693 RETURN (l_dummy IS NOT NULL);
5694
5695 EXCEPTION
5696 WHEN OTHERS THEN
5697 RETURN FALSE;
5698 END SELF_ASSESS_TAX_DIST_EXIST;
5699
5700 --Bug14383132: End
5701
5702 PROCEDURE Print (p_api_name IN VARCHAR2,
5703 p_debug_info IN VARCHAR2) IS
5704 BEGIN
5705 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5706 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||p_api_name,p_debug_info);
5707 END IF;
5708 END Print;
5709
5710 END AP_INVOICE_LINES_PKG;
5711