[Home] [Help]
PACKAGE BODY: APPS.INL_MATCH_GRP
Source
1 PACKAGE BODY INL_MATCH_GRP AS
2 /* $Header: INLGMATB.pls 120.6.12010000.22 2008/12/10 11:22:13 acferrei ship $ */
3
4 -- API name : Create_MatchIntLines
5 -- Type : Private
6 -- Function : Create Matching Lines
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_api_version IN NUMBER
10 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
11 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
12 -- p_matches_int_tbl IN OUT NOCOPY inl_matches_type_tbl
13 --
14 -- OUT : x_return_status OUT NOCOPY VARCHAR2
15 -- x_msg_count OUT NOCOPY NUMBER
16 -- x_msg_data OUT NOCOPY VARCHAR2
17 --
18 -- Version : Current version 1.0
19 --
20 -- Notes :
21 PROCEDURE Create_MatchIntLines(p_api_version IN NUMBER,
22 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
23 p_commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_matches_int_tbl IN OUT NOCOPY inl_matches_int_type_tbl,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2) IS
28
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_MatchIntLines';
30 l_api_version CONSTANT NUMBER := 1.0;
31
32 l_debug_info VARCHAR2(2000);
33 l_return_status VARCHAR2(1);
34 l_match_id NUMBER;
35 l_aux NUMBER;
36 l_group_id NUMBER;
37 l_match_amount_int_id NUMBER;
38 l_parent_match_id NUMBER;
39
40 BEGIN
41 -- Standard Beginning of Procedure/Function Logging
42 INL_LOGGING_PVT.Log_BeginProc (
43 p_module_name => g_module_name,
44 p_procedure_name => l_api_name
45 );
46
47 -- Standard Start of API savepoint
48 SAVEPOINT Create_MatchIntLines_GRP;
49
50 -- Initialize message list if p_init_msg_list is set to TRUE.
51 IF FND_API.to_Boolean( p_init_msg_list ) THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55 -- Check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call (
57 p_current_version_number => l_api_version,
58 p_caller_version_number => p_api_version,
59 p_api_name => l_api_name,
60 p_pkg_name => g_pkg_name)
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 -- Initialize API return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 IF NVL(p_matches_int_tbl.LAST,0) > 0 THEN
69 FOR i IN NVL(p_matches_int_tbl.FIRST,0)..NVL(p_matches_int_tbl.LAST,0) LOOP
70 SELECT inl_matches_int_s.NEXTVAL
71 INTO p_matches_int_tbl(i).match_int_id
72 FROM dual;
73 IF l_group_id IS NULL then
74 l_group_id := p_matches_int_tbl(i).match_int_id;
75 END IF;
76
77 l_debug_info := 'Next Match_id';
78 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
79 p_procedure_name => l_api_name,
80 p_var_name => l_debug_info,
81 p_var_value => p_matches_int_tbl(i).match_id);
82 -- Insert Match Line
83
84 INSERT INTO inl_matches_int (
85 match_int_id , /* 01 */
86 group_id , /* 02 */
87 processing_status_code , /* 03 */
88 transaction_type , /* 04 */
89 match_type_code , /* 05 */
90 from_parent_table_name , /* 07 */
91 from_parent_table_id , /* 08 */
92 to_parent_table_name , /* 08 */
93 to_parent_table_id , /* 09 */
94 matched_qty , /* 11 */
95 matched_uom_code , /* 12 */
96 matched_amt , /* 13 */
97 matched_curr_code , /* 14 */
98 matched_curr_conversion_type , /* 15 */
99 matched_curr_conversion_date , /* 16 */
100 matched_curr_conversion_rate , /* 17 */
101 replace_estim_qty_flag , /* 18 */
102 charge_line_type_id , /* 19 */
103 party_id , /* 20 */
104 party_number , /* 21 */
105 party_site_id , /* 22 */
106 party_site_number , /* 23 */
107 tax_code , /* 24 */
108 nrec_tax_amt , /* 25 */
109 tax_amt_included_flag , /* 26 */
110 match_amount_int_id , /* 27 */
111 created_by , /* 28 */
112 creation_date , /* 29 */
113 last_updated_by , /* 30 */
114 last_update_date , /* 31 */
115 last_update_login , /* 32 */
116 request_id , /* 33 */
117 program_id , /* 34 */
118 program_application_id , /* 35 */
119 program_update_date /* 36 */
120 )
121 VALUES (
122 p_matches_int_tbl(i).match_int_id , /* 01 */
123 l_group_id , /* 02 */
124 'PENDING' , /* 03 */
125 'CREATE' , /* 04 */
126 p_matches_int_tbl(i).match_type_code , /* 05 */
127 p_matches_int_tbl(i).from_parent_table_name , /* 07 */
128 p_matches_int_tbl(i).from_parent_table_id , /* 08 */
129 p_matches_int_tbl(i).to_parent_table_name , /* 08 */
130 p_matches_int_tbl(i).to_parent_table_id , /* 09 */
131 p_matches_int_tbl(i).matched_qty , /* 11 */
132 p_matches_int_tbl(i).matched_uom_code , /* 12 */
133 p_matches_int_tbl(i).matched_amt , /* 13 */
134 p_matches_int_tbl(i).matched_curr_code , /* 14 */
135 p_matches_int_tbl(i).matched_curr_conversion_type , /* 15 */
136 p_matches_int_tbl(i).matched_curr_conversion_date , /* 16 */
137 p_matches_int_tbl(i).matched_curr_conversion_rate , /* 17 */
138 p_matches_int_tbl(i).replace_estim_qty_flag , /* 18 */
139 p_matches_int_tbl(i).charge_line_type_id , /* 19 */
140 p_matches_int_tbl(i).party_id , /* 20 */
141 NULL , /* 21 */
142 p_matches_int_tbl(i).party_site_id , /* 22 */
143 NULL , /* 23 */
144 p_matches_int_tbl(i).tax_code , /* 24 */
145 p_matches_int_tbl(i).nrec_tax_amt , /* 25 */
146 p_matches_int_tbl(i).tax_amt_included_flag , /* 26 */
147 l_match_amount_int_id , /* 27 */
148 fnd_global.user_id , /* 28 */
149 SYSDATE , /* 29 */
150 fnd_global.user_id , /* 30 */
151 SYSDATE , /* 31 */
152 fnd_global.login_id , /* 32 */
153 fnd_global.conc_request_id , /* 33 */
154 fnd_global.conc_program_id , /* 34 */
155 fnd_global.prog_appl_id , /* 35 */
156 SYSDATE /* 36 */
157 );
158 END LOOP;
159 END IF;
160
161 -- If any errors happen abort API.
162 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
163 RAISE FND_API.G_EXC_ERROR;
164 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 -- Standard check of p_commit.
169 IF FND_API.To_Boolean( p_commit ) THEN
170 COMMIT WORK;
171 END IF;
172
173 -- Standard call to get message count and if count is 1, get message info.
174 FND_MSG_PUB.Count_And_Get(
175 p_encoded => FND_API.g_false,
176 p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179
180 -- Standard End of Procedure/Function Logging
181 INL_logging_pvt.Log_EndProc (
182 p_module_name => g_module_name,
183 p_procedure_name => l_api_name
184 );
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 -- Standard Expected Error Logging
188 INL_LOGGING_PVT.Log_ExpecError (
189 p_module_name => g_module_name,
190 p_procedure_name => l_api_name
191 );
192 ROLLBACK TO Create_MatchIntLines_GRP;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 FND_MSG_PUB.Count_And_Get(
195 p_encoded => FND_API.g_false,
196 p_count => x_msg_count,
197 p_data => x_msg_data
198 );
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 -- Standard Unexpected Error Logging
201 INL_LOGGING_PVT.Log_UnexpecError (
202 p_module_name => g_module_name,
203 p_procedure_name => l_api_name
204 );
205 ROLLBACK TO Create_MatchIntLines_GRP;
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 FND_MSG_PUB.Count_And_Get(
208 p_encoded => FND_API.g_false,
209 p_count => x_msg_count,
210 p_data => x_msg_data
211 );
212 WHEN OTHERS THEN
213 -- Standard Unexpected Error Logging
214 INL_LOGGING_PVT.Log_UnexpecError (
215 p_module_name => g_module_name,
216 p_procedure_name => l_api_name
217 );
218 ROLLBACK TO Create_MatchIntLines_GRP;
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
220 IF FND_MSG_PUB.Check_Msg_Level(p_message_level=>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
221 FND_MSG_PUB.Add_Exc_Msg(
222 p_pkg_name => g_pkg_name,
223 p_procedure_name => l_api_name
224 );
225 END IF;
226 FND_MSG_PUB.Count_And_Get(
227 p_encoded => FND_API.g_false,
228 p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231 END Create_MatchIntLines;
232
233 -- Utility name: Create_MatchAmountsInt
234 -- Type : Private
235 -- Function : Create Match Amount Lines based on a given Invoice Id
236 -- Pre-reqs : None
237 -- Parameters :
238 -- IN : p_invoice_id IN NUMBER
239 --
240 -- OUT : x_return_status OUT NOCOPY VARCHAR2
241 --
242 -- Version : Current version 1.0
243 --
244 -- Notes :
245 PROCEDURE Create_MatchAmountsInt(p_invoice_id IN NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2) IS
247
248 l_proc_name CONSTANT VARCHAR2(30) := 'Create_MatchAmountsInt';
249 l_return_status VARCHAR2(1);
250 l_debug_info VARCHAR2(200);
251
252 CURSOR c_amounts IS
253 SELECT SUM(d.amount) amount,
254 MInt.match_type_code match_type_code,
255 NULL tax_code,
256 NULL tax_amt_included_flag,
257 l.cost_factor_id cost_factor_id,
258 i.invoice_currency_code curr_code,
259 i.exchange_rate curr_rate,
260 i.exchange_rate_type curr_type,
261 i.exchange_date curr_date,
262 NULL tax_cost_factor_id, --Tax Prorate should be the same basis of its charge
263 'CHARGE' amount_type
264 FROM inl_matches_int MInt,
265 ap_invoices i,
266 ap_invoice_lines l,
267 ap_invoice_distributions d
268 WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
269 AND MInt.from_parent_table_id = d.invoice_distribution_id
270 AND MInt.processing_status_code = 'PENDING'
271 AND d.invoice_id = p_invoice_id
272 AND d.invoice_id = i.invoice_id
273 AND d.invoice_id = l.invoice_id
274 AND l.line_number = d.invoice_line_number
275 AND l.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
276 GROUP BY MInt.match_type_code,
280 i.exchange_rate_type,
277 l.cost_factor_id,
278 i.invoice_currency_code,
279 i.exchange_rate,
281 i.exchange_date
282 UNION ALL
283 SELECT SUM(d.amount) amount,
284 MInt.match_type_code,
285 MInt.tax_code,
286 MInt.tax_amt_included_flag,
287 l.cost_factor_id cost_factor_id,
288 i.invoice_currency_code curr_code,
289 i.exchange_rate curr_rate,
290 i.exchange_rate_type curr_type,
291 i.exchange_date curr_date,
292 l_prorat.cost_factor_id tax_cost_factor_id, --Tax Prorate should be the same basis of its charge
293 'TAX' amount_type
294 FROM inl_matches_int MInt,
295 ap_invoices i,
296 ap_invoice_lines l,
297 ap_invoice_distributions d,
298 ap_invoice_distributions d_prorat,
299 ap_invoice_lines l_prorat
300 WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
301 AND MInt.from_parent_table_id = d.invoice_distribution_id
302 AND MInt.processing_status_code = 'PENDING'
303 AND d.invoice_id = p_invoice_id
304 AND d.invoice_id = i.invoice_id
305 AND d.invoice_id = l.invoice_id
306 AND l.line_number = d.invoice_line_number
307 AND l.line_type_lookup_code = 'TAX'
308 AND d_prorat.invoice_distribution_id = d.charge_applicable_to_dist_id
309 AND d_prorat.invoice_id = l_prorat.invoice_id
310 AND l_prorat.line_number = d_prorat.invoice_line_number
311 AND l_prorat.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
312 GROUP BY MInt.match_type_code,
313 MInt.tax_code,
314 MInt.tax_amt_included_flag,
315 l.cost_factor_id,
316 i.invoice_currency_code,
317 i.exchange_rate,
318 i.exchange_rate_type,
319 i.exchange_date,
320 l_prorat.cost_factor_id;
321
322 c_amounts_rec c_amounts%ROWTYPE;
323 l_match_amount_int_id NUMBER;
324 l_match_amount_validation NUMBER;
325 l_group_id NUMBER;
326
327 BEGIN
328
329 -- Standard Beginning of Procedure/Function Logging
330 INL_LOGGING_PVT.Log_BeginProc (
331 p_module_name => g_module_name,
332 p_procedure_name => l_proc_name
333 );
334
335 -- Initialize return status to success
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337
338 l_debug_info := 'Insert in inl_match_amounts.';
339 INL_LOGGING_PVT.Log_Statement (
340 p_module_name => g_module_name,
341 p_procedure_name => l_proc_name,
342 p_debug_info => l_debug_info
343 );
344
345 FOR c_amounts_rec IN c_amounts LOOP
346 SELECT inl_match_amounts_int_s.nextval
347 INTO l_match_amount_int_id
348 FROM dual;
349 IF l_group_id IS NULL then
350 l_group_id := l_match_amount_int_id;
351 END IF;
352
353 INSERT INTO inl_match_amounts_int(
354 match_amount_int_id , /* 01 */
355 group_id , /* 02 */
356 processing_status_code , /* 03 */
357 transaction_type , /* 04 */
358 matched_amt , /* 05 */
359 matched_curr_code , /* 06 */
360 matched_curr_conversion_type, /* 07 */
361 matched_curr_conversion_date, /* 08 */
362 matched_curr_conversion_rate, /* 09 */
363 program_id , /* 10 */
364 program_update_date , /* 11 */
365 program_application_id , /* 12 */
366 request_id , /* 13 */
367 created_by , /* 14 */
368 creation_date , /* 15 */
369 last_updated_by , /* 16 */
370 last_update_date , /* 17 */
371 last_update_login /* 18 */
372 )
373 VALUES (
374 l_match_amount_int_id , /* 01 */
375 l_group_id , /* 02 */
376 'PENDING' , /* 03 */
377 'CREATE' , /* 04 */
378 c_amounts_rec.amount , /* 05 */
379 c_amounts_rec.curr_code , /* 06 */
380 c_amounts_rec.curr_type , /* 07 */
381 c_amounts_rec.curr_date , /* 08 */
382 c_amounts_rec.curr_rate , /* 09 */
383 fnd_global.conc_program_id , /* 10 */
384 SYSDATE , /* 11 */
385 fnd_global.prog_appl_id , /* 12 */
386 fnd_global.conc_request_id , /* 13 */
387 fnd_global.user_id , /* 14 */
388 SYSDATE , /* 15 */
389 fnd_global.user_id , /* 16 */
390 SYSDATE , /* 17 */
391 fnd_global.login_id); /* 18 */
392
393
394 UPDATE inl_matches_int MInt
395 SET match_amount_int_id = l_match_amount_int_id
396 WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
397 AND MInt.match_type_code = c_amounts_rec.match_type_code
398 AND MInt.processing_status_code = 'PENDING'
399 AND ( (c_amounts_rec.amount_type = 'TAX'
400 AND MInt.tax_code = c_amounts_rec.tax_code
401 AND MInt.from_parent_table_id
402 IN (SELECT d.invoice_distribution_id
403 FROM ap_invoice_lines l,
404 ap_invoice_distributions d,
408 AND d.invoice_id = l.invoice_id
405 ap_invoice_distributions d_prorat,
406 ap_invoice_lines l_prorat
407 WHERE d.invoice_id = p_invoice_id
409 AND l.line_number = d.invoice_line_number
410 AND l.line_type_lookup_code = 'TAX'
411 AND d_prorat.invoice_distribution_id = d.charge_applicable_to_dist_id
412 AND d_prorat.invoice_id = l_prorat.invoice_id
413 AND l_prorat.line_number = d_prorat.invoice_line_number
414 AND l_prorat.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
415 AND MInt.charge_line_type_id = c_amounts_rec.tax_cost_factor_id
416 )
417 )OR(c_amounts_rec.amount_type = 'CHARGE'
418 AND MInt.charge_line_type_id = c_amounts_rec.cost_factor_id
419 AND MInt.from_parent_table_id
420 IN (SELECT d.invoice_distribution_id
421 FROM ap_invoice_lines l,
422 ap_invoice_distributions d
423 WHERE d.invoice_id = p_invoice_id
424 AND d.invoice_id = l.invoice_id
425 AND l.line_number = d.invoice_line_number
426 AND l.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
427 )
428 ))
429 ;
430
431 SELECT SUM(MInt.matched_amt)
432 INTO l_match_amount_validation
433 FROM inl_matches_int MInt
434 WHERE MInt.match_amount_int_id = l_match_amount_int_id;
435 IF l_match_amount_validation <> c_amounts_rec.amount THEN
436 l_debug_info := 'l_match_amount_validation ('||l_match_amount_validation||') <> c_amounts_rec.amount ('||c_amounts_rec.amount||')';
437 INL_LOGGING_PVT.Log_Statement (
438 p_module_name => g_module_name,
439 p_procedure_name => l_proc_name,
440 p_debug_info => l_debug_info
441 );
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444 END LOOP;
445
446 -- Standard End of Procedure/Function Logging
447 INL_logging_pvt.Log_EndProc (
448 p_module_name => g_module_name,
449 p_procedure_name => l_proc_name
450 );
451 EXCEPTION
452 WHEN FND_API.G_EXC_ERROR THEN
453 -- Standard Expected Error Logging
454 INL_LOGGING_PVT.Log_ExpecError (
455 p_module_name => g_module_name,
456 p_procedure_name => l_proc_name
457 );
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 -- Standard Unexpected Error Logging
461 INL_LOGGING_PVT.Log_UnexpecError (
462 p_module_name => g_module_name,
463 p_procedure_name => l_proc_name
464 );
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 WHEN OTHERS THEN
467 -- Standard Unexpected Error Logging
468 INL_LOGGING_PVT.Log_UnexpecError (
469 p_module_name => g_module_name,
470 p_procedure_name => l_proc_name
471 );
472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
473 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
474 FND_MSG_PUB.Add_Exc_Msg(
475 p_pkg_name => g_pkg_name,
476 p_procedure_name => l_proc_name
477 );
478 END IF;
479 END Create_MatchAmountsInt;
480
481 /*=======================================
482 |
483 | Matches From Payables
484 |
485 \=======================================*/
486
487 -- Api name : Create_MatchesFromAP
488 -- Type : Group
489 -- Function : Create matches in LCM for actuals captured from a given AP Invoice.
490 -- Pre-reqs : None
491 -- Parameters :
492 -- IN : p_api_version IN NUMBER
493 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
494 -- p_commit IN NUMBER := FND_API.G_FALSE
495 -- p_invoice_id IN NUMBER
496 --
497 -- OUT : x_return_status OUT NOCOPY VARCHAR2
498 -- x_msg_count OUT NOCOPY NUMBER
499 -- x_msg_data OUT NOCOPY VARCHAR2
500 --
501 -- Version : Current version 1.0
502 --
503 PROCEDURE Create_MatchesFromAP(p_api_version IN NUMBER,
504 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
505 p_commit IN VARCHAR2 := FND_API.G_FALSE,
506 p_invoice_id IN NUMBER,
507 x_return_status OUT NOCOPY VARCHAR2,
508 x_msg_count OUT NOCOPY NUMBER,
509 x_msg_data OUT NOCOPY VARCHAR2)
510 IS
511
512 l_api_name CONSTANT VARCHAR2(30) := 'Create_MatchesFromAP';
513 l_api_version CONSTANT NUMBER := 1.0;
514
515 l_return_status VARCHAR2(1);
516 l_msg_count NUMBER;
517 l_msg_data VARCHAR2(2000);
518 l_debug_info VARCHAR2(200);
519 l_distr_amount NUMBER;
520 l_quantity_invoiced NUMBER;
521 l_line_amount NUMBER;
522 l_par_cost_factor_id NUMBER;
523 l_par_line_type_lookup_code VARCHAR2(25);
524
525 -- This cursor if for retrieving Invoice information at Invoice
526 -- Distribution level to be sent to LCM.
527
528 -- It retrives AP Distribution lines that would affect "ITEM", "CHARGE" and "TAX" lines in LCM,
529 -- including their corrections.
530
531 -- The invoice line types we are handling for LCM integration are the
535 -- 'AWT','RETROTAX','PREPAY', the invoice line types above are the ones
532 -- following: 'ITEM', 'MISCELLANEOUS', 'FREIGHT', 'TAX'.
533
534 -- Although there are other invoice line types such as 'RETAINAGE RELEASE',
536 -- that appear in the Invoices form, when we are creating invoices.
537
538 CURSOR c_distr IS
539 SELECT decode(NVL(l.corrected_inv_id, 0), 0, decode(l.line_type_lookup_code,'ITEM','ITEM', 'TAX', 'TAX', 'CHARGE'), 'CORRECTION') line_type,
540 decode(NVL(l.corrected_inv_id, 0), 0, NULL, decode(l.line_type_lookup_code,'ITEM','ITEM', 'TAX', 'TAX', 'CHARGE')) correction_type,
541 d.amount distr_amount,
542 d.corrected_invoice_dist_id corrected_invoice_dist_id,
543 d.invoice_distribution_id invoice_distribution_id,
544 d.invoice_id invoice_id,
545 d.line_type_lookup_code line_type_lookup_code,
546 d.parent_reversal_id parent_reversal_id,
547 d.dist_match_type dist_match_type,
548 d.charge_applicable_to_dist_id,
549 l.rcv_transaction_id rcv_transaction_id,
550 muom.uom_code uom_code,
551 i.invoice_currency_code curr_code,
552 i.exchange_rate curr_rate,
553 i.exchange_rate_type curr_type,
554 i.exchange_date curr_date,
555 i.party_id party_id,
556 i.party_site_id party_site_id,
557 decode(l.line_type_lookup_code, 'TAX', l.quantity_invoiced, d.quantity_invoiced) quantity_invoiced,
558 l.cost_factor_id cost_factor_id,
559 l.tax tax_code,
560 decode(l.line_type_lookup_code, 'TAX', decode(d.tax_recoverable_flag,'Y',0,d.amount), NULL) nrec_tax_amt,
561 decode(l.line_type_lookup_code, 'TAX', 'N', NULL) tax_amt_included_flag
562 FROM rcv_transactions rt,
563 ap_invoice_distributions d,
564 ap_invoices i,
565 ap_invoice_lines l,
566 mtl_units_of_measure muom
567 WHERE l.line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS', 'FREIGHT', 'TAX')
568 AND d.match_status_flag = 'S'
569 AND rt.lcm_shipment_line_id IS NOT NULL
570 AND muom.unit_of_measure (+) = d.matched_uom_lookup_code
571 AND d.invoice_id = l.invoice_id
572 AND d.invoice_line_number = l.line_number
573 AND rt.transaction_id = l.rcv_transaction_id
574 AND l.invoice_id = i.invoice_id
575 AND d.invoice_id = p_invoice_id
576 ORDER BY invoice_distribution_id;
577
578 c_distr_rec c_distr%ROWTYPE;
579 l_matches_int_tbl inl_matches_int_type_tbl;
580 i NUMBER;
581 l_invoices_with_charge_tbl inl_int_tbl;
582 l_count_invoices_with_charge NUMBER:=1;
583 BEGIN
584
585 -- Standard Beginning of Procedure/Function Logging
586 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
587 p_procedure_name => l_api_name);
588
589 l_debug_info := 'Begining Create_MatchesFromAP';
590 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
591 p_procedure_name => l_api_name,
592 p_debug_info => l_debug_info);
593
594 -- Standard Start of API savepoint
595 SAVEPOINT Create_MatchesFromAP_GRP;
596
597 -- Initialize message list if p_init_msg_list is set to TRUE.
598 IF FND_API.to_Boolean( p_init_msg_list ) THEN
599 FND_MSG_PUB.initialize;
600 END IF;
601
602 -- Check for call compatibility.
603 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
604 p_caller_version_number => p_api_version,
605 p_api_name => l_api_name,
606 p_pkg_name => g_pkg_name)
607 THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 i := 0;
615 -- Getting AP Distribution Lines
616 l_debug_info := 'Getting AP Distribution Lines';
617 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
618 p_procedure_name => l_api_name,
619 p_debug_info => l_debug_info);
620 FOR c_distr_rec IN c_distr LOOP
621 i := i + 1;
622
623 -- Distribution
624 l_debug_info := 'Invoice Distribution id('||i||'): '||c_distr_rec.invoice_distribution_id;
625 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
626 p_procedure_name => l_api_name,
627 p_debug_info => l_debug_info);
628
629 IF c_distr_rec.line_type_lookup_code = 'ITEM' AND c_distr_rec.parent_reversal_id IS NOT NULL THEN
630 l_distr_amount := c_distr_rec.distr_amount * -1;
631 l_quantity_invoiced := c_distr_rec.quantity_invoiced * -1;
632 l_line_amount := c_distr_rec.distr_amount * -1;
633 ELSE
634 l_distr_amount := c_distr_rec.distr_amount;
635 l_quantity_invoiced := c_distr_rec.quantity_invoiced;
636 l_line_amount := c_distr_rec.distr_amount;
637 END IF;
638
639 l_debug_info := '2. c_distr_rec.invoice_distribution_id: ' || c_distr_rec.invoice_distribution_id;
640 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
641 p_procedure_name => l_api_name,
642 p_debug_info => l_debug_info);
643
644 l_matches_int_tbl(i).from_parent_table_name := 'AP_INVOICE_DISTRIBUTIONS';
645 l_matches_int_tbl(i).from_parent_table_id := c_distr_rec.invoice_distribution_id;
646
647 -- In order to make the Create Adjust process less
651 IF c_distr_rec.line_type = 'CORRECTION'
648 -- complex, the correction match should use the same
649 -- currency that is used by the corrected match.
650 -- Although the match represents the new amount of the line and not the variation inl_matches_int will have the variation.
652 AND (c_distr_rec.dist_match_type <> 'NOT_MATCHED'
653 OR c_distr_rec.correction_type = 'ITEM')
654 THEN
655 -- Correction
656 l_debug_info := 'It is a correction. Type: '||c_distr_rec.correction_type;
657 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
658 p_procedure_name => l_api_name,
659 p_debug_info => l_debug_info);
660
661 l_matches_int_tbl(i).to_parent_table_name := 'AP_INVOICE_DISTRIBUTIONS';
662 l_matches_int_tbl(i).to_parent_table_id := c_distr_rec.corrected_invoice_dist_id;
663 ELSE
664 l_debug_info := 'It is not a correction.';
665 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
666 p_procedure_name => l_api_name,
667 p_debug_info => l_debug_info);
668
669 l_matches_int_tbl(i).to_parent_table_name := 'RCV_TRANSACTIONS';
670 l_matches_int_tbl(i).to_parent_table_id := c_distr_rec.rcv_transaction_id;
671 END IF;
672
673 IF c_distr_rec.line_type = 'ITEM' OR c_distr_rec.correction_type = 'ITEM' THEN
674 -- The distribution cursor does not get IPV lines
675 -- To get the exact qty and amt we access the ap_invoice_lines
676 l_matches_int_tbl(i).charge_line_type_id := NULL;
677 l_matches_int_tbl(i).matched_qty := l_quantity_invoiced; --c_distr_rec.quantity_invoiced;
678 l_matches_int_tbl(i).matched_uom_code := c_distr_rec.uom_code;
679 l_matches_int_tbl(i).matched_amt := l_line_amount; --c_distr_rec.distr_amount;
680 l_matches_int_tbl(i).tax_code := NULL;
681 l_matches_int_tbl(i).nrec_tax_amt := NULL;
682 l_matches_int_tbl(i).tax_amt_included_flag := NULL;
683 l_matches_int_tbl(i).party_id := NULL;
684 l_matches_int_tbl(i).party_site_id := NULL;
685 ELSE
686 l_matches_int_tbl(i).matched_qty := NULL;
687 IF c_distr_rec.line_type = 'CHARGE' OR c_distr_rec.correction_type = 'CHARGE' THEN
688 IF l_count_invoices_with_charge = 1
689 OR l_invoices_with_charge_tbl(l_count_invoices_with_charge-1).num <> c_distr_rec.invoice_id then
690 l_invoices_with_charge_tbl(l_count_invoices_with_charge).num := c_distr_rec.invoice_id;
691 l_count_invoices_with_charge:=l_count_invoices_with_charge+1;
692 END IF;
693 l_matches_int_tbl(i).party_id := c_distr_rec.party_id;
694 l_matches_int_tbl(i).party_site_id := c_distr_rec.party_site_id;
695 l_matches_int_tbl(i).charge_line_type_id := c_distr_rec.cost_factor_id;
696 l_matches_int_tbl(i).matched_amt := l_distr_amount; --c_distr_rec.distr_amount;
697 l_matches_int_tbl(i).tax_code := NULL;
698 l_matches_int_tbl(i).nrec_tax_amt := NULL;
699 l_matches_int_tbl(i).tax_amt_included_flag := NULL;
700 ELSE
701 -- Gets the "parent" cost factor and line_type_lookup_code
702 -- in order to identify whether or not a given Charge that
703 -- has taxes, should keep in LCM the same cost factor
704 -- defined in its Invoice
705 SELECT ail.cost_factor_id,
706 ail.line_type_lookup_code
707 INTO l_par_cost_factor_id,
708 l_par_line_type_lookup_code
709 FROM ap_invoice_distributions aid,
710 ap_invoice_lines ail
711 WHERE aid.invoice_id = ail.invoice_id
712 AND aid.invoice_line_number = ail.line_number
713 AND aid.invoice_distribution_id = c_distr_rec.charge_applicable_to_dist_id
714 AND aid.invoice_id = c_distr_rec.invoice_id;
715
716 IF l_par_line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT') THEN
717 l_matches_int_tbl(i).charge_line_type_id := l_par_cost_factor_id;
718 ELSE
719 l_matches_int_tbl(i).charge_line_type_id := NULL;
720 END IF;
721
722 l_matches_int_tbl(i).party_id := NULL;
723 l_matches_int_tbl(i).party_site_id := NULL;
724 l_matches_int_tbl(i).matched_amt := l_distr_amount; --c_distr_rec.distr_amount;
725 l_matches_int_tbl(i).tax_code := c_distr_rec.tax_code;
726 l_matches_int_tbl(i).nrec_tax_amt := c_distr_rec.nrec_tax_amt;
727 l_matches_int_tbl(i).tax_amt_included_flag := c_distr_rec.tax_amt_included_flag;
728 END IF;
729 END IF;
730
731 IF c_distr_rec.line_type = 'CORRECTION'
732 AND c_distr_rec.dist_match_type = 'NOT_MATCHED'
733 AND c_distr_rec.correction_type <> 'ITEM'
734 THEN
735 l_matches_int_tbl(i).match_type_code := c_distr_rec.correction_type;
736 ELSE
737 l_matches_int_tbl(i).match_type_code := c_distr_rec.line_type;
738 END IF;
739 l_matches_int_tbl(i).matched_curr_code := c_distr_rec.curr_code;
740 l_matches_int_tbl(i).matched_curr_conversion_type := c_distr_rec.curr_type;
741 l_matches_int_tbl(i).matched_curr_conversion_date := c_distr_rec.curr_date;
742 l_matches_int_tbl(i).matched_curr_conversion_rate := c_distr_rec.curr_rate;
743 END LOOP;
744
745 l_debug_info := 'Call Create_MatchIntLines ';
746 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
747 p_procedure_name => l_api_name,
748 p_debug_info => l_debug_info);
749 IF NVL(l_matches_int_tbl.LAST,0) > 0 THEN
750 -- Call Create_MatchIntLines to create Matches and
751 -- set Pending Matching Flag to 'Y' in Shipment Header.
752 Create_MatchIntLines(p_api_version => 1.0,
753 p_init_msg_list => FND_API.G_FALSE,
754 p_commit => FND_API.G_FALSE,
755 p_matches_int_tbl => l_matches_int_tbl,
756 x_return_status => l_return_status,
757 x_msg_count => l_msg_count,
758 x_msg_data => l_msg_data);
759
760 -- Create INL Match Amounts
761 IF l_invoices_with_charge_tbl IS NOT NULL AND NVL(l_invoices_with_charge_tbl.LAST,0) > 0 THEN
762 l_debug_info := 'Call Create_MatchAmountsInt ';
763 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
764 p_procedure_name => l_api_name,
765 p_debug_info => l_debug_info);
766
767 FOR i IN NVL(l_invoices_with_charge_tbl.FIRST,0)..NVL(l_invoices_with_charge_tbl.LAST,0) LOOP
768 Create_MatchAmountsInt(p_invoice_id => l_invoices_with_charge_tbl(i).num,
769 x_return_status => l_return_status);
770 END LOOP;
771 END IF;
772 END IF;
773
774 -- If any errors happen abort the process.
775 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
776 RAISE FND_API.G_EXC_ERROR;
777 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 END IF;
780
781 -- Standard check of p_commit.
782 IF FND_API.To_Boolean( p_commit ) THEN
783 COMMIT WORK;
784 END IF;
785
786 -- Standard call to get message count and if count is 1, get message info.
787 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
788 p_count => x_msg_count,
789 p_data => x_msg_data);
790
791 -- Standard End of Procedure/Function Logging
792 INL_logging_pvt.Log_EndProc (p_module_name => g_module_name,
793 p_procedure_name => l_api_name);
794 EXCEPTION
795 WHEN FND_API.G_EXC_ERROR THEN
796 -- Standard Expected Error Logging
797 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
798 p_procedure_name => l_api_name);
799 ROLLBACK TO Create_MatchesFromAP_GRP;
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
802 p_count => x_msg_count,
803 p_data => x_msg_data);
804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
805 -- Standard Unexpected Error Logging
806 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
807 p_procedure_name => l_api_name);
808 ROLLBACK TO Create_MatchesFromAP_GRP;
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810
811 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
812 p_count => x_msg_count,
813 p_data => x_msg_data);
814 WHEN OTHERS THEN
815 -- Standard Unexpected Error Logging
816 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
817 p_procedure_name => l_api_name);
818 ROLLBACK TO Create_MatchesFromAP_GRP;
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
820 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
821 THEN
822 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
823 p_procedure_name => l_api_name);
824 END IF;
825 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
826 p_count => x_msg_count,
827 p_data => x_msg_data);
828 END Create_MatchesFromAP;
829
830 END INL_MATCH_GRP;