[Home] [Help]
PACKAGE BODY: APPS.OKL_PAY_INVOICES_CONS_PVT
Source
1 PACKAGE BODY okl_pay_invoices_cons_pvt AS
2 /* $Header: OKLRPICB.pls 120.41.12010000.2 2010/02/11 06:30:28 nikshah ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.INVOICE';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 G_PROG_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROG_NAME';
9
10 G_DB_ERROR CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
11 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
12 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
13 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
14 --start:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
15 G_ACC_SYS_OPTION VARCHAR2(4);
16 --end:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
17
18 TYPE cnsld_invs_type IS RECORD (
19
20 cin_rec OKL_CIN_PVT.cin_rec_type,
21 tplv_tbl OKL_TPL_PVT.tplv_tbl_type
22
23 );
24
25 TYPE cnsld_invs_tbl_type IS TABLE OF cnsld_invs_type
26 INDEX BY BINARY_INTEGER;
27
28 --start: 31-Oct-2007 cklee -- bug: 6508575 fixed
29 ----------------------------------------------------------------------------------
30 -- Start of comments
31 --
32 -- Procedure Name : get_Disbursement_term
33 -- Description : Get Disbursement term
34 -- Business Rules :
35 -- Parameters :
36 -- Version : 1.0
37 -- End of comments
38 ----------------------------------------------------------------------------------
39 FUNCTION get_Disbursement_term(
40 p_transaction_date IN DATE -- reserved for future use
41 ,p_vendor_id IN NUMBER
42 ,p_vendor_site_id IN NUMBER
43 ,p_stream_type_purpose IN VARCHAR2
44 ,x_rule_found OUT NOCOPY BOOLEAN
45 ,x_return_status OUT NOCOPY VARCHAR2
46 ) return disb_rules_type IS
47
48 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'get_Disbursement_term';
49 lx_disb_rules disb_rules_type;
50
51 cursor c_disb_rules( p_vendor_id NUMBER,
52 p_vendor_site_id NUMBER,
53 p_stream_type_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%type,
54 p_transaction_date DATE) -- reserved for future use
55 IS
56 select dra.disb_rule_id,
57 dra.rule_name,
58 dra.fee_option,
59 dra.fee_basis,
60 dra.fee_amount,
61 dra.fee_percent,
62 nvl(dra.consolidate_by_due_date, 'N') consolidate_by_due_date,
63 dra.frequency,
64 dra.day_of_month,
65 dra.scheduled_month,
66 nvl(dra.consolidate_strm_type, 'N') consolidate_strm_type,
67 drs.stream_type_purpose,
68 drv.invoice_seq_start,
69 drv.invoice_seq_end,
70 drv.next_inv_seq,
71 drv.disb_rule_vendor_site_id
72 from okl_disb_rules_all_b dra,
73 okl_disb_rule_vendor_sites drv,
74 okl_disb_rule_sty_types drs
75 where drv.disb_rule_id = dra.disb_rule_id
76 and drs.disb_rule_id = dra.disb_rule_id
77 and drv.vendor_id = p_vendor_id
78 and drv.vendor_site_id = p_vendor_site_id
79 and drs.stream_type_purpose = p_stream_type_purpose
80 and TRUNC(sysdate) between TRUNC(NVL(drv.start_date, dra.start_date)) and
81 TRUNC(NVL(NVL(drv.end_date, dra.end_date),TRUNC(sysdate)));
82 /* reserved for future use
83 and TRUNC(p_transaction_date) between TRUNC(NVL(drv.start_date, dra.start_date)) and
84 TRUNC(NVL(NVL(drv.end_date, dra.end_date),p_transaction_date));
85 */
86 r_disb_rules c_disb_rules%ROWTYPE;
87
88 BEGIN
89 -- initial return variables
90 x_return_status := G_RET_STS_SUCCESS;
91 x_rule_found := FALSE;
92
93 OPEN c_disb_rules(p_vendor_id => p_vendor_id,
94 p_vendor_site_id => p_vendor_site_id,
95 p_stream_type_purpose => p_stream_type_purpose,
96 p_transaction_date => p_transaction_date);
97 FETCH c_disb_rules into r_disb_rules;
98
99 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_term: p_vendor_id/p_vendor_site_id/p_stream_type_purpose/'
100 || p_vendor_id || '/' || p_vendor_site_id || '/' || p_stream_type_purpose || '/');
101
102 IF c_disb_rules%FOUND THEN
103
104 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_term: rule_name:' || r_disb_rules.rule_name);
105 x_rule_found := TRUE;
106 lx_disb_rules.dra_rec.disb_rule_id := r_disb_rules.disb_rule_id;
107 lx_disb_rules.dra_rec.rule_name := r_disb_rules.rule_name;
108 lx_disb_rules.dra_rec.fee_option := r_disb_rules.fee_option;
109 lx_disb_rules.dra_rec.fee_basis := r_disb_rules.fee_basis;
110 lx_disb_rules.dra_rec.fee_amount := r_disb_rules.fee_amount;
111 lx_disb_rules.dra_rec.fee_percent := r_disb_rules.fee_percent;
112 lx_disb_rules.dra_rec.consolidate_by_due_date := r_disb_rules.consolidate_by_due_date;
113 lx_disb_rules.dra_rec.frequency := r_disb_rules.frequency;
114 lx_disb_rules.dra_rec.day_of_month := r_disb_rules.day_of_month;
115 lx_disb_rules.dra_rec.scheduled_month := r_disb_rules.scheduled_month;
116 lx_disb_rules.dra_rec.consolidate_strm_type := r_disb_rules.consolidate_strm_type;
117
118 lx_disb_rules.drs_rec.stream_type_purpose := r_disb_rules.stream_type_purpose;
119
120 lx_disb_rules.drv_rec.invoice_seq_start := r_disb_rules.invoice_seq_start;
121 lx_disb_rules.drv_rec.invoice_seq_end := r_disb_rules.invoice_seq_end;
122 lx_disb_rules.drv_rec.next_inv_seq := r_disb_rules.next_inv_seq;
123 lx_disb_rules.drv_rec.disb_rule_vendor_site_id := r_disb_rules.disb_rule_vendor_site_id;
124
125 END IF;
126
127 RETURN lx_disb_rules;
128
129 EXCEPTION
130
131 WHEN OKL_API.G_EXCEPTION_ERROR THEN
132
133 x_return_status := G_RET_STS_ERROR;
134 RETURN lx_disb_rules;
135
136 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
137
138 x_return_status := G_RET_STS_UNEXP_ERROR;
139 RETURN lx_disb_rules;
140
141 WHEN OTHERS THEN
142
143 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
144 p_msg_name => G_DB_ERROR,
145 p_token1 => G_PROG_NAME_TOKEN,
146 p_token1_value => l_prog_name,
147 p_token2 => G_SQLCODE_TOKEN,
148 p_token2_value => sqlcode,
149 p_token3 => G_SQLERRM_TOKEN,
150 p_token3_value => sqlerrm);
151
152 x_return_status := G_RET_STS_UNEXP_ERROR;
153 RETURN lx_disb_rules;
154
155 END;
156 ----------------------------------------------------------------------------------
157 -- Start of comments
158 --
159 -- Procedure Name : handle_next_invoice_seq
160 -- Description :
161 -- In p_transaction_date : OKL internal invoice transaction date
162 -- In p_vendor_id : vendor id
163 -- In p_vendor_site_id : vendor site id
164 -- In p_stream_type_purpose : stream type purpose
165 -- In p_adv_grouping_flag : advance grouping flag
166 -- OUT: vendor sequence number
167 -- Business Rules :
168 -- Parameters :
169 -- Version : 1.0
170 -- End of comments
171 ----------------------------------------------------------------------------------
172 PROCEDURE handle_next_invoice_seq(
173 p_api_version IN NUMBER
174 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
175 ,x_return_status OUT NOCOPY VARCHAR2
176 ,x_msg_count OUT NOCOPY NUMBER
177 ,x_msg_data OUT NOCOPY VARCHAR2
178 ,p_transaction_date IN DATE
179 ,p_vendor_id IN NUMBER
180 ,p_vendor_site_id IN NUMBER
181 ,p_stream_type_purpose IN VARCHAR2
182 ,p_adv_grouping_flag IN VARCHAR2 -- reserved for future use
183 ,x_next_inv_seq OUT NOCOPY NUMBER
184 ) IS
185
186 ------------------------------------------------------------
187 -- Declare variables required by APIs
188 ------------------------------------------------------------
189 l_api_version CONSTANT NUMBER := 1;
190 l_api_name CONSTANT VARCHAR2(30) := 'handle_next_invoice_seq';
191 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
192 --
193 l_inv_seq_start okl_disb_rule_vendor_sites.invoice_seq_start%type;
194 l_inv_seq_end okl_disb_rule_vendor_sites.invoice_seq_end%type;
195 l_next_inv_seq okl_disb_rule_vendor_sites.next_inv_seq%type;
196 l_new_next_inv_seq okl_disb_rule_vendor_sites.next_inv_seq%type := NULL;
197
198 l_disb_rules disb_rules_type;
199 lx_rule_found BOOLEAN := FALSE;
200 lx_return_status VARCHAR2(30) := G_RET_STS_SUCCESS;
201
202 l_update_flag boolean := TRUE; -- bug: 6662247
203 l_curr_next_inv_seq okl_disb_rule_vendor_sites.next_inv_seq%type; -- bug: 6662247
204
205 BEGIN
206 ------------------------------------------------------------
207 -- Start processing
208 ------------------------------------------------------------
209 x_return_status := OKL_API.G_RET_STS_SUCCESS;
210
211 l_return_status := OKL_API.START_ACTIVITY(
212 p_api_name => l_api_name,
213 p_pkg_name => g_pkg_name,
214 p_init_msg_list => p_init_msg_list,
215 l_api_version => l_api_version,
216 p_api_version => p_api_version,
217 p_api_type => '_PVT',
218 x_return_status => l_return_status);
219
220 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
221 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
222 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
223 RAISE OKL_API.G_EXCEPTION_ERROR;
224 END IF;
225
226 x_next_inv_seq := NULL; -- initial to null
227
228 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq: p_vendor_id/p_vendor_site_id/p_stream_type_purpose/'
229 || p_vendor_id || '/' || p_vendor_site_id || '/' || p_stream_type_purpose || '/');
230 IF p_adv_grouping_flag = 'Y' THEN -- reserved for future use
231
232 l_disb_rules := get_Disbursement_term(
233 p_transaction_date => p_transaction_date,
234 p_vendor_id => p_vendor_id,
235 p_vendor_site_id => p_vendor_site_id,
236 p_stream_type_purpose => p_stream_type_purpose,
237 x_return_status => lx_return_status,
238 x_rule_found => lx_rule_found);
239
240 IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN
241 -----------------------------------------------------------------------------
242 --
243 --case start next end note
244 ------------------------------------
245 -- 1 100 100
246 -- 2 100 -1 100 No next seq #
247 -- 3 100 200
248 -- 4 100 200 200 Next # is the last seq #
249 -- 5 100 -1 200 No next seq #
250 -- 6 100 150 200
251 --+7 100 150 120 User enter end seq # and system didn't check the next seq # (bug)
252 --+8 120 140 User delete previous seq # setup and setup a new seq range (refer to case 9) - bug
253 -- 9 100 No end seq #
254 --10 100 101 No end seq #
255 --
256 -----------------------------------------------------------------------------
257 -- if vendor invoice sequence has been set, system will find the next available sequence #.
258 -- IF l_disb_rules.drv_rec.invoice_seq_start IS NOT NULL AND l_disb_rules.drv_rec.invoice_seq_end IS NOT NULL THEN
259 IF l_disb_rules.drv_rec.invoice_seq_start IS NOT NULL THEN --bug: 6662247
260
261 l_inv_seq_start := l_disb_rules.drv_rec.invoice_seq_start;
262 l_inv_seq_end := l_disb_rules.drv_rec.invoice_seq_end;
263 --start: bug: 6662247
264 -- l_next_inv_seq := nvl(l_disb_rules.drv_rec.next_inv_seq, l_inv_seq_start);
265 l_next_inv_seq := l_disb_rules.drv_rec.next_inv_seq;
266
267 IF l_disb_rules.drv_rec.invoice_seq_end IS NOT NULL THEN --(case 1-8)
268 IF l_inv_seq_end > l_inv_seq_start THEN -- (case 3-8) note: 3=8
269 IF l_next_inv_seq IS NULL THEN --(case 3,8) note: 3=8
270 l_curr_next_inv_seq := l_inv_seq_start;
271 l_new_next_inv_seq := l_inv_seq_start + 1;
272 ELSE --(case 4,5,6,7)
273 IF l_next_inv_seq = -1 THEN -- case 5
274 l_update_flag := FALSE;
275 l_curr_next_inv_seq := NULL;
276 ELSIF l_next_inv_seq < l_inv_seq_end THEN -- case 6
277 l_curr_next_inv_seq := l_next_inv_seq;
278 l_new_next_inv_seq := l_next_inv_seq + 1;
279 ELSIF l_next_inv_seq = l_inv_seq_end THEN -- case 4
280 l_curr_next_inv_seq := l_next_inv_seq;
281 l_new_next_inv_seq := -1;
282 ELSIF l_next_inv_seq > l_inv_seq_end THEN -- case 7
283 l_curr_next_inv_seq := NULL;
284 l_new_next_inv_seq := -1;
285 END IF;
286 END IF;
287 ELSIF l_inv_seq_end = l_inv_seq_start THEN -- (case 1,2)
288 IF l_next_inv_seq IS NULL THEN -- case 1
289 l_curr_next_inv_seq := l_inv_seq_start;
290 l_new_next_inv_seq := -1; -- no next sequence # for the next visit
291 ELSIF l_next_inv_seq = -1 THEN -- case 2
292 l_update_flag := FALSE;
293 l_curr_next_inv_seq := NULL;
294 ELSE
295 NULL;
296 END IF;
297 ELSE -- shall not happened
298 l_curr_next_inv_seq := NULL;
299 l_new_next_inv_seq := -1;
300 END IF;
301 ELSE -- no end sequence. So add 1 to the start seq # or next seq # (case 9,10)
302 IF l_next_inv_seq IS NULL THEN -- case 9
303 l_curr_next_inv_seq := l_inv_seq_start;
304 l_new_next_inv_seq := l_inv_seq_start + 1;
305 ELSE -- case 10
306 l_curr_next_inv_seq := l_next_inv_seq;
307 l_new_next_inv_seq := l_next_inv_seq + 1;
308 END IF;
309 END IF;
310
311 -- -- Maintain the new next sequence #
312 -- IF ( l_next_inv_seq < l_inv_seq_end ) THEN
313 -- l_new_next_inv_seq := l_next_inv_seq + 1;
314 -- ELSE
315 -- l_new_next_inv_seq := l_inv_seq_end;
316 -- END IF;
317
318
319 IF l_update_flag = TRUE THEN
320 -- Update okl_disb_rule_vendor_sites
321 UPDATE okl_disb_rule_vendor_sites
322 SET next_inv_seq = l_new_next_inv_seq
323 WHERE disb_rule_vendor_site_id = l_disb_rules.drv_rec.disb_rule_vendor_site_id
324 AND disb_rule_id = l_disb_rules.dra_rec.disb_rule_id;
325 END IF;
326
327 --end: bug: 6662247
328 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq: l_next_inv_seq:' || l_next_inv_seq);
329 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq: l_new_next_inv_seq:' || l_new_next_inv_seq);
330
331 END IF; -- if vendor invoice sequence has been set
332 END IF; -- if rule found
333 END IF; --IF p_adv_grouping_flag = 'Y' THEN
334
335 x_next_inv_seq := l_curr_next_inv_seq; --bug: 6662247
336 ------------------------------------------------------------
337 -- End processing
338 ------------------------------------------------------------
339
340 Okl_Api.END_ACTIVITY (
341 x_msg_count => x_msg_count,
342 x_msg_data => x_msg_data);
343
344
345 EXCEPTION
346 ------------------------------------------------------------
347 -- Exception handling
348 ------------------------------------------------------------
349 WHEN OKL_API.G_EXCEPTION_ERROR THEN
350 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq*=> ERROR: '||SQLERRM);
351 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
352 p_api_name => l_api_name,
353 p_pkg_name => G_PKG_NAME,
354 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_api_type => '_PVT');
358
359 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
360 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq*=> ERROR: '||SQLERRM);
361 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
362 p_api_name => l_api_name,
363 p_pkg_name => G_PKG_NAME,
364 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
365 x_msg_count => x_msg_count,
366 x_msg_data => x_msg_data,
367 p_api_type => '_PVT');
368
369 WHEN OTHERS THEN
370 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'handle_next_invoice_seq*=> ERROR: '||SQLERRM);
371 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
372 p_api_name => l_api_name,
373 p_pkg_name => G_PKG_NAME,
374 p_exc_name => 'OTHERS',
375 x_msg_count => x_msg_count,
376 x_msg_data => x_msg_data,
377 p_api_type => '_PVT');
378
379 END;
380
381 --end: 31-Oct-2007 cklee -- bug: 6508575 fixed
382 ---------------------------------------------------------------------------
383 -- FUNCTION get_months_factor
384 ---------------------------------------------------------------------------
385 ----------------------------------------------------------------------------------
386 -- Start of comments
387 --
388 -- Procedure Name : get_months_factor
389 -- Description :
390 -- Business Rules :
391 -- Parameters :
392 -- Version : 1.0
393 -- End of comments
394 ----------------------------------------------------------------------------------
395 FUNCTION get_months_factor( p_frequency IN VARCHAR2,
396 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
397
398 l_months NUMBER;
399
400 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'get_months_factor';
401
402
403 BEGIN
404
405 IF p_frequency = 'M' THEN
406 l_months := 1;
407 ELSIF p_frequency = 'Q' THEN
408 l_months := 3;
409 ELSIF p_frequency = 'S' THEN
410 l_months := 6;
411 ELSIF p_frequency = 'A' THEN
412 l_months := 12;
413 END IF;
414
415 IF l_months IS NOT NULL THEN
416 x_return_status := G_RET_STS_SUCCESS;
417 RETURN l_months;
418
419 ELSE
420
421 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
422 p_msg_name => 'OKL_INVALID_FREQUENCY_CODE',
423 p_token1 => 'FRQ_CODE',
424 p_token1_value => p_frequency);
425
426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
427
428 END IF;
429
430 EXCEPTION
431
432 WHEN OKL_API.G_EXCEPTION_ERROR THEN
433
434 x_return_status := G_RET_STS_ERROR;
435
436 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
437
438 x_return_status := G_RET_STS_UNEXP_ERROR;
439
440 WHEN OTHERS THEN
441
442 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
443 p_msg_name => G_DB_ERROR,
444 p_token1 => G_PROG_NAME_TOKEN,
445 p_token1_value => l_prog_name,
446 p_token2 => G_SQLCODE_TOKEN,
447 p_token2_value => sqlcode,
448 p_token3 => G_SQLERRM_TOKEN,
449 p_token3_value => sqlerrm);
450
451 x_return_status := G_RET_STS_UNEXP_ERROR;
452
453 END get_months_factor;
454
455 ----------------------------------------------------------------------------------
456 -- Start of comments
457 --
458 -- Procedure Name : get_cnsld_invoiced_date
459 -- Description :
460 -- Business Rules :
461 -- Parameters :
462 -- Version : 1.0
463 -- End of comments
464 ----------------------------------------------------------------------------------
465 FUNCTION get_cnsld_invoiced_date(p_invoiced_date DATE,
466 p_frequency VARCHAR,
467 p_day_of_month NUMBER,
468 p_scheduled_month VARCHAR)
469
470 RETURN DATE
471 IS
472
473 l_cnsld_invoiced_date DATE := p_invoiced_date;
474 l_sys_date DATE := TRUNC(sysdate);
475 l_scheduled_date DATE;
476
477 lx_return_status VARCHAR2(1);
478 l_months_factor NUMBER;
479
480 BEGIN
481
482 l_scheduled_date := TO_DATE(p_scheduled_month || '-' || to_char(l_sys_date, 'YYYY') || '-' || to_char(p_day_of_month), 'MM-YYYY-DD');
483
484 l_months_factor := get_months_factor( p_frequency => p_frequency,
485 x_return_status => lx_return_status);
486
487 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR OR lx_return_status = OKL_API.G_RET_STS_ERROR THEN
488 l_months_factor := 1; -- Default monthly ??
489 END IF;
490
491 /* -- OKL invoice dates can be earlier to current date (ssiruvol)
492
493 WHILE ( l_scheduled_date < l_sys_date )
494 LOOP
495
496 l_scheduled_date := ADD_MONTHS(l_scheduled_date, l_months_factor);
497
498 End LOOP;
499 */
500
501 WHILE ( l_scheduled_date <= p_invoiced_date )
502 LOOP
503
504 l_scheduled_date := ADD_MONTHS(l_scheduled_date, l_months_factor);
505
506 End LOOP;
507
508 l_cnsld_invoiced_date := l_scheduled_date;
509
510 RETURN l_cnsld_invoiced_date;
511
512 END get_cnsld_invoiced_date;
513 --start: 31-Oct-2007 cklee -- bug: 6508575 fixed
514 ----------------------------------------------------------------------------------
515 -- Start of comments
516 --
517 -- Procedure Name : get_ap_invoice_date
518 -- Description : Get the AP invoice date based on the following:
519 -- In p_transaction_date : OKL internal invoice transaction date
520 -- In p_vendor_id : vendor id
521 -- In p_vendor_site_id : vendor site id
522 -- In p_stream_type_purpose : stream type purpose
523 -- In p_adv_grouping_flag : advance grouping flag
524 -- OUT: Consolidation invoice date
525 -- Business Rules :
526 -- Parameters :
527 -- Version : 1.0
528 -- End of comments
529 ----------------------------------------------------------------------------------
530 FUNCTION get_ap_invoice_date(
531 p_transaction_date IN DATE
532 ,p_vendor_id IN NUMBER
533 ,p_vendor_site_id IN NUMBER
534 ,p_stream_type_purpose IN VARCHAR2
535 ,p_adv_grouping_flag IN VARCHAR2 -- reserved for future use
536 ) RETURN DATE IS
537
538 l_invoice_date okl_cnsld_ap_invs_all.date_invoiced%type := p_transaction_date;
539 l_disb_rules disb_rules_type;
540 lx_rule_found BOOLEAN := FALSE;
541 lx_return_status VARCHAR2(30) := G_RET_STS_SUCCESS;
542
543 BEGIN
544 /*
545 Business logic:
546 If criteria meet, set new invoice date as a grouping cirteria. Otherwise, set
547 invoice date as passed in transaction date.
548
549 1. Get rule
550 2. If rule found and p_adv_grouping_flag = 'Y' then
551 get the consoldiate invoice date;
552 else
553 set inoice date as pass in transaction date;
554 end if;
555 */
556
557 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_ap_invoice_date: p_vendor_id/p_vendor_site_id/p_stream_type_purpose/consolidate_by_due_date'
558 || p_vendor_id || '/' || p_vendor_site_id || '/' || p_stream_type_purpose || '/'|| l_disb_rules.dra_rec.consolidate_by_due_date);
559 IF p_adv_grouping_flag = 'Y' THEN -- reserved for future use
560
561 l_disb_rules := get_Disbursement_term(
562 p_transaction_date => p_transaction_date,
563 p_vendor_id => p_vendor_id,
564 p_vendor_site_id => p_vendor_site_id,
565 p_stream_type_purpose => p_stream_type_purpose,
566 x_return_status => lx_return_status,
567 x_rule_found => lx_rule_found);
568
569 IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN
570
571 IF l_disb_rules.dra_rec.consolidate_by_due_date = 'Y' THEN
572
573 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_ap_invoice_date: p_invoiced_date/p_frequency/p_day_of_month/p_scheduled_month'
574 || p_transaction_date || '/' || l_disb_rules.dra_rec.frequency || '/' || l_disb_rules.dra_rec.day_of_month || '/'|| l_disb_rules.dra_rec.scheduled_month);
575 -- getting the invoice date for the consolidated invoice based on the schedule setup on the rule (ssiruvol 5/10/2007)
576 l_invoice_date := get_cnsld_invoiced_date (p_invoiced_date => p_transaction_date,
577 p_frequency => l_disb_rules.dra_rec.frequency,
578 p_day_of_month => l_disb_rules.dra_rec.day_of_month,
579 p_scheduled_month => l_disb_rules.dra_rec.scheduled_month);
580 END IF;
581 END IF;
582 END IF; --IF p_adv_grouping_flag = 'Y' THEN
583
584 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_ap_invoice_date: l_invoice_date' || l_invoice_date);
585
586 return l_invoice_date;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 -- store SQL error message on message stack for caller
591 Okl_Api.Set_Message(p_app_name => OKL_API.G_APP_NAME,
592 p_msg_name => 'OKL_UNEXPECTED_ERROR',
593 p_token1 => 'OKL_SQLCODE',
594 p_token1_value => SQLCODE,
595 p_token2 => 'OKL_SQLERRM',
596 p_token2_value => SQLERRM);
597 RETURN l_invoice_date;
598 END;
599
600 ----------------------------------------------------------------------------------
601 -- Start of comments
602 --
603 -- Procedure Name : get_Disbursement_group
604 -- Description : Get Disbursement group
605 -- In p_transaction_date : OKL internal invoice transaction date
606 -- In p_vendor_id : vendor id
607 -- In p_vendor_site_id : vendor site id
608 -- In p_stream_type_purpose : stream type purpose
609 -- In p_adv_grouping_flag : advance grouping flag
610 -- OUT: Disbursement term
611 -- Business Rules :
612 -- Parameters :
613 -- Version : 1.0
614 -- End of comments
615 ----------------------------------------------------------------------------------
616 FUNCTION get_Disbursement_group(
617 p_transaction_date IN DATE -- reserved for future use
618 ,p_vendor_id IN NUMBER
619 ,p_vendor_site_id IN NUMBER
620 ,p_stream_type_purpose IN VARCHAR2
621 ,p_adv_grouping_flag IN VARCHAR2 -- reserved for future use
622 ) RETURN VARCHAR2 IS
623
624 l_disb_group varchar2(150) := p_stream_type_purpose; -- defualt to stream type purpose as a group criteria
625 l_disb_rules disb_rules_type;
626 lx_rule_found BOOLEAN := FALSE;
627 lx_return_status VARCHAR2(30) := G_RET_STS_SUCCESS;
628 BEGIN
629 /*
630 Business logic:
631 If criteria meet, set Term name as a grouping cirteria. Otherwise, set
632 stream type purpose as a grouping criteria.
633
634 1. get Disbursement term
635 2. If rule found and p_adv_grouping_flag = 'Y' then
636 if consolidate_strm_type = 'Y' then
637 If p_stream_type_purpose in rule's streanm type purposes then
638 set l_disb_term := rule_name;
639 end if;
640 end if;
641 else
642 set l_disb_term := null;
643 end if;
644 */
645
646 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_group: p_vendor_id/p_vendor_site_id/p_stream_type_purpose/consolidate_strm_type '
647 || p_vendor_id || '/' || p_vendor_site_id || '/' || p_stream_type_purpose || '/'|| l_disb_rules.dra_rec.consolidate_strm_type );
648 IF p_adv_grouping_flag = 'Y' THEN -- reserved for future use
649
650 l_disb_rules := get_Disbursement_term(
651 p_transaction_date => p_transaction_date,
652 p_vendor_id => p_vendor_id,
653 p_vendor_site_id => p_vendor_site_id,
654 p_stream_type_purpose => p_stream_type_purpose,
655 x_return_status => lx_return_status,
656 x_rule_found => lx_rule_found);
657
658 IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN
659
660 IF l_disb_rules.dra_rec.consolidate_strm_type = 'Y' THEN
661 -- if passed in stream type within the Disbursement term's stream type purpose pool
662 IF l_disb_rules.drs_rec.stream_type_purpose = p_stream_type_purpose THEN
663 l_disb_group := l_disb_rules.dra_rec.rule_name; -- set disb group as Term name
664 END IF;
665 END IF;
666 END IF;
667 END IF; --IF p_adv_grouping_flag = 'Y' THEN
668
669 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_group:l_disb_group' || l_disb_group);
670
671 return l_disb_group;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675
676 -- store SQL error message on message stack for caller
677 Okl_Api.Set_Message(p_app_name => OKL_API.G_APP_NAME,
678 p_msg_name => 'OKL_UNEXPECTED_ERROR',
679 p_token1 => 'OKL_SQLCODE',
680 p_token1_value => SQLCODE,
681 p_token2 => 'OKL_SQLERRM',
682 p_token2_value => SQLERRM);
683 RETURN l_disb_group;
684 END;
685
686 ----------------------------------------------------------------------------------
687 -- Start of comments
688 --
689 -- Procedure Name : get_contract_group
690 -- Description : get_contract_group
691 -- In p_transaction_date : OKL internal invoice transaction date
692 -- In p_vendor_id : vendor id
693 -- In p_vendor_site_id : vendor site id
694 -- In p_stream_type_purpose : stream type purpose
695 -- In p_adv_grouping_flag : advance grouping flag
696 -- OUT: Disbursement term
697 -- Business Rules :
698 -- Parameters :
699 -- Version : 1.0
700 -- End of comments
701 ----------------------------------------------------------------------------------
702 FUNCTION get_contract_group(
703 p_transaction_date IN DATE -- reserved for future use
704 ,p_vendor_id IN NUMBER
705 ,p_vendor_site_id IN NUMBER
706 ,p_stream_type_purpose IN VARCHAR2
707 ,p_contract_number IN VARCHAR2
708 ,p_adv_grouping_flag IN VARCHAR2 DEFAULT 'Y' -- reserved for future use
709 ) RETURN VARCHAR2 IS
710
711 l_contract_group okc_k_headers_all_b.contract_number%TYPE := p_contract_number; -- defualt to contract_number as a group criteria
712 l_disb_rules disb_rules_type;
713 lx_rule_found BOOLEAN := FALSE;
714 lx_return_status VARCHAR2(30) := G_RET_STS_SUCCESS;
715
716 BEGIN
717 /*
718 Business logic:
719 If criteria meet, set contract number as a grouping cirteria. Otherwise, set
720 contract number as null.
721
722 1. get Disbursement term
723 2. If rule found and p_adv_grouping_flag = 'Y' then
724 if consolidate_strm_type = 'Y' or consolidate_by_date = 'Y' then
725 set contract group = null;
726 end if;
727 else
728 set lcontract group := contract number;
729 end if;
730 */
731 IF p_adv_grouping_flag = 'Y' THEN -- reserved for future use
732
733 l_disb_rules := get_Disbursement_term(
734 p_transaction_date => p_transaction_date,
735 p_vendor_id => p_vendor_id,
736 p_vendor_site_id => p_vendor_site_id,
737 p_stream_type_purpose => p_stream_type_purpose,
738 x_return_status => lx_return_status,
739 x_rule_found => lx_rule_found);
740
741 IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN
742
743 IF l_disb_rules.dra_rec.consolidate_strm_type = 'Y' OR l_disb_rules.dra_rec.consolidate_by_due_date = 'Y' THEN
744 l_contract_group := 'x'; -- set disb group as NULL: group across contracts
745 END IF;
746 END IF;
747 END IF; --IF p_adv_grouping_flag = 'Y' THEN
748
749 return l_contract_group;
750
751 EXCEPTION
752 WHEN OTHERS THEN
753
754 -- store SQL error message on message stack for caller
755 Okl_Api.Set_Message(p_app_name => OKL_API.G_APP_NAME,
756 p_msg_name => 'OKL_UNEXPECTED_ERROR',
757 p_token1 => 'OKL_SQLCODE',
758 p_token1_value => SQLCODE,
759 p_token2 => 'OKL_SQLERRM',
760 p_token2_value => SQLERRM);
761 RETURN l_contract_group;
762 END;
763
764 ----------------------------------------------------------------------------------
765 -- Start of comments
766 --
767 -- Procedure Name : get_Disbursement_rule
768 -- Description : Get Disbursement rule
769 -- In p_transaction_date : OKL internal invoice transaction date
770 -- In p_vendor_id : vendor id
771 -- In p_vendor_site_id : vendor site id
772 -- In p_stream_type_purpose : stream type purpose
773 -- In p_adv_grouping_flag : advance grouping flag
774 -- OUT: Disbursement term
775 -- Business Rules :
776 -- Parameters :
777 -- Version : 1.0
778 -- End of comments
779 ----------------------------------------------------------------------------------
780 FUNCTION get_Disbursement_rule(
781 p_transaction_date IN DATE -- reserved for future use
782 ,p_okl_invoice_line_id IN NUMBER
783 ,p_vendor_id IN NUMBER
784 ,p_vendor_site_id IN NUMBER
785 ,p_stream_type_purpose IN VARCHAR2
786 ,p_adv_grouping_flag IN VARCHAR2 -- reserved for future use
787 ) RETURN VARCHAR2 IS
788
789 l_disb_rule varchar2(150) := to_char(p_okl_invoice_line_id); -- defualt to okl_txl_ap_inv_lns_all_b.id as a group criteria
790 l_disb_rules disb_rules_type;
791 lx_rule_found BOOLEAN := FALSE;
792 lx_return_status VARCHAR2(30) := G_RET_STS_SUCCESS;
793 BEGIN
794 /*
795 Business logic:
796 If criteria meet, set Term name as a grouping cirteria. Otherwise, set
797 okl_txl_ap_inv_lns_all_b.id as a grouping criteria.
798
799 */
800
801 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_rule: p_vendor_id/p_vendor_site_id/p_stream_type_purpose/'
802 || p_vendor_id || '/' || p_vendor_site_id || '/' || p_stream_type_purpose || '/');
803 IF p_adv_grouping_flag = 'Y' THEN -- reserved for future use
804
805 l_disb_rules := get_Disbursement_term(
806 p_transaction_date => p_transaction_date,
807 p_vendor_id => p_vendor_id,
808 p_vendor_site_id => p_vendor_site_id,
809 p_stream_type_purpose => p_stream_type_purpose,
810 x_return_status => lx_return_status,
811 x_rule_found => lx_rule_found);
812
813 IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN
814 l_disb_rule := l_disb_rules.dra_rec.rule_name; -- set disb group as Term name
815 END IF;
816 END IF; --IF p_adv_grouping_flag = 'Y' THEN
817
818 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_Disbursement_rule: l_disb_rule' || l_disb_rule);
819 return l_disb_rule;
820
821 EXCEPTION
822 WHEN OTHERS THEN
823
824 -- store SQL error message on message stack for caller
825 Okl_Api.Set_Message(p_app_name => OKL_API.G_APP_NAME,
826 p_msg_name => 'OKL_UNEXPECTED_ERROR',
827 p_token1 => 'OKL_SQLCODE',
828 p_token1_value => SQLCODE,
829 p_token2 => 'OKL_SQLERRM',
830 p_token2_value => SQLERRM);
831 RETURN l_disb_rule;
832 END;
833
834 --end: 31-Oct-2007 cklee -- bug: 6508575 fixed
835
836 ----------------------------------------------------------------------------------
837 -- Start of comments
838 --
839 -- Procedure Name : apply_consolidation_rules
840 -- Description :
841 -- Business Rules :
842 -- Parameters :
843 -- Version : 1.0
844 -- End of comments
845 ----------------------------------------------------------------------------------
846 -- OBSOLETE THE FOLOWING PROCEDURE 9/25/07
847 PROCEDURE apply_consolidation_rules_ssir(
848 p_api_version IN NUMBER,
849 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_count OUT NOCOPY NUMBER,
852 x_msg_data OUT NOCOPY VARCHAR2,
853 x_cnsld_invs OUT NOCOPY cnsld_invs_tbl_type,
854 p_contract_id IN NUMBER DEFAULT NULL,
855 p_vendor_id IN NUMBER DEFAULT NULL,
856 p_vendor_site_id IN NUMBER DEFAULT NULL,
857 p_vpa_id IN NUMBER DEFAULT NULL,
858 --start:| 24-APR-2007 cklee Disbursement changes for R12B |
859 p_stream_type_purpose IN VARCHAR2 DEFAULT NULL,
860 --end:| 24-APR-2007 cklee Disbursement changes for R12B |
861 p_from_date IN DATE,
862 p_to_date IN DATE)
863 IS
864 ------------------------------------------------------------
865 -- Declare variables required by APIs
866 ------------------------------------------------------------
867 l_api_version CONSTANT NUMBER := 1;
868 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_CONSOLIDATION_RULES';
869 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
870 ------------------------------------------------------------
871 -- Declare Process variables
872 ------------------------------------------------------------
873 l_msg_index_out NUMBER;
874
875 l_cin_tbl OKL_CIN_PVT.cin_tbl_type;
876 lx_cin_tbl OKL_CIN_PVT.cin_tbl_type;
877
878 l_cin_rec OKL_CIN_PVT.cin_rec_type;
879 lx_cin_rec OKL_CIN_PVT.cin_rec_type;
880
881 l_tplv_rec OKL_TPL_PVT.tplv_rec_type;
882 lx_tplv_rec OKL_TPL_PVT.tplv_rec_type;
883
884 l_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
885 lx_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
886
887 l_cnsld_invs cnsld_invs_tbl_type;
888
889 l_gen_seq okl_trx_ap_invoices_v.invoice_number%TYPE;
890
891 CURSOR c_invoice IS
892 SELECT
893 tap.id tap_id
894 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
895 ,tap.org_id
896 ,tap.invoice_category_code
897 ,tap.invoice_number
898 --,tap.vendor_invoice_number
899 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
900 ,tap.object_version_number
901 ,tap.code_combination_id tap_ccid
902 ,tap.date_invoiced date_invoiced
903 ,tap.pay_group_lookup_code
904 ,tap.ipvs_id
905 ,tap.ippt_id
906 ,tap.IPT_FREQUENCY
907 ,tap.IPT_ID
908 ,tap.payment_method_code
909 ,tap.currency_code
910 ,tap.currency_conversion_type
911 ,tap.currency_conversion_rate
912 ,tap.currency_conversion_date
913 ,tap.workflow_yn
914 ,tap.date_gl gl_date
915 ,tap.nettable_yn
916 --start:| 02-May-2007 cklee Disbursement changes for R12B |
917 -- ,tap.khr_id
918 ,tpl.khr_id
919 --end:| 02-May-2007 cklee Disbursement changes for R12B |
920 ,tap.wait_vendor_invoice_yn
921 ,tap.try_id
922 ,tpl.id tpl_id
923 ,tpl.code_combination_id tpl_ccid
924 ,tpl.sty_id
925 ,acc_db.code_combination_id db_ccid
926 ,acc_cr.code_combination_id cr_ccid
927 ,tpl.amount invoice_amount
928 ,acc_db.amount line_amount
929 ,povs.vendor_id
930 ,tap.trx_status_code
931 ,tpl.line_number
932 ,tap.INVOICE_TYPE
933 ,tap.SET_OF_BOOKS_ID
934 ,tap.DATE_GL
935 ,tap.LEGAL_ENTITY_ID
936 ,tap.VPA_ID
937 ,tpl.INV_DISTR_LINE_CODE
938 ,tpl.DISBURSEMENT_BASIS_CODE
939 ,tpl.TPL_ID_REVERSES
940 ,tpl.CODE_COMBINATION_ID
941 ,tpl.LSM_ID
942 ,tpl.KLE_ID
943 ,tpl.ITC_ID
944 ,tpl.DATE_ACCOUNTING
945 ,tpl.PAYABLES_INVOICE_ID
946 ,tpl.REQUEST_ID
947 ,tpl.FUNDING_REFERENCE_NUMBER
948 ,tpl.FUNDING_REFERENCE_TYPE_CODE
949 ,tpl.SEL_ID
950 ,tpl.TAXABLE_YN
951 --start:| 02-May-2007 cklee Disbursement changes for R12B |
952 ,tpl.REF_LINE_NUMBER
953 ,tpl.CNSLD_LINE_NUMBER
954 --end:| 02-May-2007 cklee Disbursement changes for R12B |
955 FROM
956 okl_trns_acc_dstrs acc_db
957 ,okl_trns_acc_dstrs acc_cr
958 ,po_vendor_sites_all povs
959 ,okl_txl_ap_inv_lns_b tpl
960 ,okl_trx_ap_invoices_b tap
961 WHERE
962 NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
963 trunc(tap.date_invoiced) BETWEEN
964 NVL(p_from_date, SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
965 AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
966 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
967 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
968 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
969 AND tpl.id = acc_db.source_id (+)
970 AND tpl.id = acc_cr.source_id (+)
971 AND povs.vendor_site_id = tap.ipvs_id
972 AND tap.id = tpl.tap_id
973 AND tap.FUNDING_TYPE_CODE IS NULL
974 AND nvl(tap.khr_id,-1) = nvl(p_contract_id, nvl(tap.khr_id,-1))
975 AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
976 AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
977 --start:| 24-APR-2007 cklee Disbursement changes for R12B |
978 -- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
979 AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')),nvl(tpl.sty_id,-1))
980 --end:| 24-APR-2007 cklee Disbursement changes for R12B |
981 AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
982 UNION ALL
983 SELECT
984 tap.id tap_id
985 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
986 ,tap.org_id
987 ,tap.invoice_category_code
988 ,tap.invoice_number
989 --,tap.vendor_invoice_number
990 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
991 ,tap.object_version_number
992 ,tap.code_combination_id tap_ccid
993 ,tap.date_invoiced date_invoiced
994 ,tap.pay_group_lookup_code
995 ,tap.ipvs_id
996 ,tap.ippt_id
997 ,tap.IPT_FREQUENCY
998 ,tap.IPT_ID
999 ,tap.payment_method_code
1000 ,tap.currency_code
1001 ,tap.currency_conversion_type
1002 ,tap.currency_conversion_rate
1003 ,tap.currency_conversion_date
1004 ,tap.workflow_yn
1005 ,tap.date_gl gl_date
1006 ,tap.nettable_yn
1007 --start:| 02-May-2007 cklee Disbursement changes for R12B |
1008 -- ,tap.khr_id
1009 ,tpl.khr_id
1010 --end:| 02-May-2007 cklee Disbursement changes for R12B |
1011 ,tap.wait_vendor_invoice_yn
1012 ,tap.try_id
1013 ,tpl.id tpl_id
1014 ,tpl.code_combination_id tpl_ccid
1015 ,tpl.sty_id
1016 ,acc_db.code_combination_id db_ccid
1017 ,acc_cr.code_combination_id cr_ccid
1018 ,tpl.amount invoice_amount
1019 ,acc_db.amount line_amount
1020 ,povs.vendor_id
1021 ,tap.trx_status_code
1022 ,tpl.line_number
1023 ,tap.INVOICE_TYPE
1024 ,tap.SET_OF_BOOKS_ID
1025 ,tap.DATE_GL
1026 ,tap.LEGAL_ENTITY_ID
1027 ,tap.VPA_ID
1028 ,tpl.INV_DISTR_LINE_CODE
1029 ,tpl.DISBURSEMENT_BASIS_CODE
1030 ,tpl.TPL_ID_REVERSES
1031 ,tpl.CODE_COMBINATION_ID
1032 ,tpl.LSM_ID
1033 ,tpl.KLE_ID
1034 ,tpl.ITC_ID
1035 ,tpl.DATE_ACCOUNTING
1036 ,tpl.PAYABLES_INVOICE_ID
1037 ,tpl.REQUEST_ID
1038 ,tpl.FUNDING_REFERENCE_NUMBER
1039 ,tpl.FUNDING_REFERENCE_TYPE_CODE
1040 ,tpl.SEL_ID
1041 ,tpl.TAXABLE_YN
1042 --start:| 02-May-2007 cklee Disbursement changes for R12B |
1043 ,tpl.REF_LINE_NUMBER
1044 ,tpl.CNSLD_LINE_NUMBER
1045 --end:| 02-May-2007 cklee Disbursement changes for R12B |
1046 FROM
1047 okl_trns_acc_dstrs acc_db
1048 ,okl_trns_acc_dstrs acc_cr
1049 ,po_vendor_sites_all povs
1050 ,okl_txl_ap_inv_lns_b tpl
1051 ,okl_trx_ap_invoices_b tap
1052 WHERE
1053 NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED') AND
1054 trunc(tap.date_invoiced) BETWEEN
1055 NVL(p_from_date, SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
1056 AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
1057 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
1058 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1059 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1060 AND tpl.id = acc_db.source_id (+)
1061 AND tpl.id = acc_cr.source_id (+)
1062 AND povs.vendor_site_id = tap.ipvs_id
1063 AND tap.id = tpl.tap_id
1064 AND tap.FUNDING_TYPE_CODE IS NOT NULL
1065 AND nvl(tap.khr_id,-1) = nvl(p_contract_id, nvl(tap.khr_id,-1))
1066 AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
1067 AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
1068 --start:| 24-APR-2007 cklee Disbursement changes for R12B |
1069 -- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
1070 AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')), nvl(tpl.sty_id,-1))
1071 --end:| 24-APR-2007 cklee Disbursement changes for R12B |
1072 AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
1073 ORDER BY vendor_id,
1074 ipvs_id,
1075 pay_group_lookup_code,
1076 payment_method_code,
1077 ippt_id,
1078 set_of_books_id,
1079 code_combination_id,
1080 currency_code,
1081 currency_conversion_type,
1082 currency_conversion_rate,
1083 currency_conversion_date,
1084 legal_entity_id,
1085 vpa_id,
1086 invoice_type,
1087 ipt_id,
1088 ipt_frequency,
1089 sty_id,
1090 date_invoiced;
1091
1092 r_invoice c_invoice%ROWTYPE;
1093
1094 cursor c_get_sty_id(p_sty_id NUMBER) IS
1095 select id
1096 from OKL_STRM_TYPE_B
1097 where STREAM_TYPE_PURPOSE = p_stream_type_purpose;
1098
1099 cursor c_disb_rules( vId NUMBER,
1100 vSiteid NUMBER,
1101 styId NUMBER) IS
1102 select dra.rule_name,
1103 dra.fee_option,
1104 dra.fee_basis,
1105 dra.fee_amount,
1106 dra.fee_percent,
1107 nvl(dra.consolidate_by_due_date, 'N') consolidate_by_due_date,
1108 dra.frequency,
1109 dra.day_of_month,
1110 dra.scheduled_month,
1111 nvl(dra.consolidate_strm_type, 'N') consolidate_strm_type,
1112 drs.stream_type_purpose,
1113 drv.invoice_seq_start,
1114 drv.invoice_seq_end,
1115 drv.next_inv_seq,
1116 drv.disb_rule_vendor_site_id,
1117 drv.disb_rule_id
1118 from okl_disb_rules_all_b dra,
1119 okl_disb_rule_vendor_sites drv,
1120 okl_disb_rule_sty_types drs
1121 where drv.DISB_RULE_ID = dra.disb_rule_id
1122 and drs.disb_rule_id = dra.disb_rule_id
1123 and drv.vendor_id = vId
1124 and drv.vendor_site_id = vSiteid
1125 and drs.stream_type_purpose = nvl((select stream_type_purpose from OKL_STRM_TYPE_B where id = nvl(styId, -1)),nvl(drs.stream_type_purpose,'XXX'))
1126 and TRUNC(sysdate) between TRUNC(NVL(drv.start_date, dra.start_date)) and TRUNC(NVL(NVL(drv.end_date, dra.end_date),TRUNC(sysdate)));
1127
1128 r_disb_rules c_disb_rules%ROWTYPE;
1129
1130 l_okl_application_id NUMBER(3) := 540;
1131 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
1132 lX_dbseqnm VARCHAR2(2000):= '';
1133 lX_dbseqid NUMBER(38):= NULL;
1134
1135
1136 l_inv_seq_start NUMBER := 0;
1137 l_inv_seq_end NUMBER := 0;
1138 l_next_inv_seq NUMBER := 0;
1139 l_new_next_inv_seq NUMBER := 0;
1140
1141 l_curr_vendor_id NUMBER := NULL;
1142 l_curr_vendor_site_id NUMBER := NULL;
1143 l_curr_invoiced_date DATE := NULL;
1144 l_curr_sty_id NUMBER := NULL;
1145 l_curr_inv_date DATE := NULL;
1146
1147 l_curr_ipvs_id NUMBER := NULL;
1148 l_curr_pay_group_lookup_code VARCHAR2(30) := NULL;
1149 l_curr_payment_method_code VARCHAR2(30) := NULL;
1150 l_curr_ippt_id NUMBER := NULL;
1151 l_curr_set_of_books_id NUMBER := NULL;
1152 l_curr_code_combination_id NUMBER := NULL;
1153 l_curr_currency_code VARCHAR2(15) := NULL;
1154 l_curr_currency_conv_type VARCHAR2(30) := NULL;
1155 l_curr_currency_conv_rate NUMBER := NULL;
1156 l_curr_currency_conv_date DATE := NULL;
1157 l_curr_legal_entity_id NUMBER := NULL;
1158 l_curr_vpa_id NUMBER := NULL;
1159 l_curr_invoice_type VARCHAR2(240) := NULL;
1160 l_curr_ipt_id NUMBER := NULL;
1161 l_curr_ipt_frequency VARCHAR2(3) := NULL;
1162
1163 l_last_vendor_id NUMBER := NULL;
1164 l_last_vendor_site_id NUMBER := NULL;
1165 l_last_invoiced_date DATE := NULL;
1166 l_last_sty_id NUMBER := NULL;
1167 l_last_inv_date DATE := NULL;
1168
1169
1170 l_last_ipvs_id NUMBER := NULL;
1171 l_last_pay_group_lookup_code VARCHAR2(30) := NULL;
1172 l_last_payment_method_code VARCHAR2(30) := NULL;
1173 l_last_ippt_id NUMBER := NULL;
1174 l_last_set_of_books_id NUMBER := NULL;
1175 l_last_code_combination_id NUMBER := NULL;
1176 l_last_currency_code VARCHAR2(15) := NULL;
1177 l_last_currency_conv_type VARCHAR2(30) := NULL;
1178 l_last_currency_conv_rate NUMBER := NULL;
1179 l_last_currency_conv_date DATE := SYSDATE-10000;
1180 l_last_legal_entity_id NUMBER := NULL;
1181 l_last_vpa_id NUMBER := NULL;
1182 l_last_invoice_type VARCHAR2(240) := NULL;
1183 l_last_ipt_id NUMBER := NULL;
1184 l_last_ipt_frequency VARCHAR2(3) := NULL;
1185
1186
1187 l_found_rule_YN VARCHAR2(1) := 'N';
1188 l_new_invoiced_date DATE;
1189
1190 i BINARY_INTEGER := 0;
1191 j BINARY_INTEGER := 0;
1192 l BINARY_INTEGER := 0;
1193
1194 l_consolidate_yn VARCHAR2(1) := 'N';
1195
1196 BEGIN
1197 ------------------------------------------------------------
1198 -- Start processing
1199 ------------------------------------------------------------
1200
1201 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 1');
1202
1203 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1204
1205 l_return_status := OKL_API.START_ACTIVITY(
1206 p_api_name => l_api_name,
1207 p_pkg_name => g_pkg_name,
1208 p_init_msg_list => p_init_msg_list,
1209 l_api_version => l_api_version,
1210 p_api_version => p_api_version,
1211 p_api_type => '_PVT',
1212 x_return_status => l_return_status);
1213
1214 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217 RAISE OKL_API.G_EXCEPTION_ERROR;
1218 END IF;
1219
1220 i := 0;
1221
1222 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 2');
1223
1224 FOR r_invoice IN c_invoice LOOP -- loop through all invoices ordered by the header values (ssiruvol 5/10/2007)
1225
1226 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 3');
1227
1228 l_consolidate_yn := 'N'; -- Flag to know whether current invoice is being consolidated or not (ssiruvol 5/10/2007)
1229
1230 l_curr_vendor_id := r_invoice.vendor_id;
1231 l_curr_vendor_site_id := r_invoice.ipvs_id;
1232 l_curr_invoiced_date := r_invoice.date_invoiced;
1233 l_curr_sty_id := r_invoice.sty_id;
1234
1235
1236 l_curr_ipvs_id := r_invoice.ipvs_id;
1237 l_curr_pay_group_lookup_code := r_invoice.pay_group_lookup_code;
1238 l_curr_payment_method_code := r_invoice.payment_method_code;
1239 l_curr_ippt_id := r_invoice.ippt_id;
1240 l_curr_set_of_books_id := r_invoice.set_of_books_id;
1241 l_curr_code_combination_id := r_invoice.code_combination_id;
1242 l_curr_currency_code := r_invoice.currency_code;
1243 l_curr_currency_conv_type := r_invoice.currency_conversion_type;
1244 l_curr_currency_conv_rate := r_invoice.currency_conversion_rate;
1245 l_curr_currency_conv_date := r_invoice.currency_conversion_date;
1246 l_curr_legal_entity_id := r_invoice.legal_entity_id;
1247 l_curr_vpa_id := r_invoice.vpa_id;
1248 l_curr_invoice_type := r_invoice.invoice_type;
1249 l_curr_ipt_id := r_invoice.ipt_id;
1250 l_curr_ipt_frequency := r_invoice.ipt_frequency;
1251
1252
1253 r_disb_rules := NULL;
1254 l_found_rule_YN := 'N';
1255
1256
1257 l_inv_seq_start := 0;
1258 l_inv_seq_end := 0;
1259 l_next_inv_seq := 0;
1260 l_new_next_inv_seq := 0;
1261
1262 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 3.1 vendor id ' || to_char(l_curr_vendor_id));
1263 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 3.1 vendor site id ' || to_char(l_curr_vendor_site_id));
1264 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 3.1 sty id ' || to_char(l_curr_sty_id));
1265
1266 -- Checking if a rule exists on the an invoice (ssiruvol 5/10/2007)
1267 OPEN c_disb_rules(vId => l_curr_vendor_id,
1268 vSiteid => l_curr_vendor_site_id,
1269 styId => l_curr_sty_id);
1270
1271 FETCH c_disb_rules into r_disb_rules;
1272
1273 If c_disb_rules%FOUND THEN
1274
1275 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4 found rule ' );
1276 l_found_rule_YN := 'Y';
1277
1278 -- getting the correct seqence number (ssiruvol 5/10/2007)
1279 l_inv_seq_start := nvl(r_disb_rules.invoice_seq_start,0);
1280 l_inv_seq_end := nvl(r_disb_rules.invoice_seq_end,0);
1281 l_next_inv_seq := nvl(r_disb_rules.next_inv_seq, l_inv_seq_start);
1282 If ( l_next_inv_seq < l_inv_seq_end ) THen
1283 l_new_next_inv_seq := l_next_inv_seq + 1;
1284 else
1285 l_neW_next_inv_seq := l_inv_seq_end;
1286 l_next_inv_seq := NULL;
1287 End If;
1288
1289 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1 l_inv_seq_start ' || to_char(l_inv_seq_start) );
1290 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1 l_inv_seq_end ' || to_char(l_inv_seq_end) );
1291 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1 l_next_inv_seq ' || to_char(l_next_inv_seq) );
1292 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1 l_new_next_inv_seq ' || to_char(l_new_next_inv_seq) );
1293
1294 IF r_disb_rules.consolidate_by_due_date = 'Y' THEN
1295
1296 -- getting the invoice date for the consolidated invoice based on the schedule setup on the rule (ssiruvol 5/10/2007)
1297 l_curr_inv_date := get_cnsld_invoiced_date (p_invoiced_date => l_curr_invoiced_date,
1298 p_frequency => r_disb_rules.frequency,
1299 p_day_of_month => r_disb_rules.day_of_month,
1300 p_scheduled_month => r_disb_rules.scheduled_month);
1301 ELSE
1302 l_curr_inv_date := l_curr_invoiced_date;
1303 END IF;
1304 Else
1305 -- if there is no rule then the cnsld invoice date is same as the original invoice date (ssiruvol 5/10/2007)
1306 l_curr_inv_date := l_curr_invoiced_date;
1307 END If;
1308 CLOSE c_disb_rules;
1309
1310 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 5 cnsld inv date ' || to_char(l_curr_inv_date) );
1311
1312 /*
1313 If ( l_curr_vendor_id <> nvl(l_last_vendor_id, -99)
1314 AND l_curr_vendor_site_id <> nvl(l_last_vendor_site_id, -99)
1315 AND l_curr_ipvs_id <> nvl(l_last_ipvs_id, -99)
1316 AND l_curr_pay_group_lookup_code <> nvl(l_last_pay_group_lookup_code, 'XXX')
1317 AND l_curr_payment_method_code <> nvl(l_last_payment_method_code, 'XXX')
1318 AND l_curr_ippt_id <> nvl(l_last_ippt_id, -99)
1319 AND l_curr_set_of_books_id <> nvl(l_last_set_of_books_id, -99)
1320 AND l_curr_code_combination_id <> nvl(l_last_code_combination_id, -99)
1321 AND l_curr_currency_code <> nvl(l_last_currency_code, 'XXX')
1322 AND l_curr_currency_conv_type <> nvl(l_last_currency_conv_type, 'XXX')
1323 AND l_curr_currency_conv_rate <> nvl(l_last_currency_conv_rate, -99)
1324 AND l_curr_currency_conv_date <> nvl(l_last_currency_conv_date, sysdate-10000)
1325 AND l_curr_legal_entity_id <> nvl(l_last_legal_entity_id, -99)
1326 AND l_curr_vpa_id <> nvl(l_last_vpa_id, -99)
1327 AND l_curr_ipt_id <> nvl(l_last_ipt_id, -99)
1328 AND l_curr_ipt_frequency <> nvl(l_last_ipt_frequency, 'XXX') )
1329 -- If any one of the above header values is different than we cannot consolidate (ssiruvol 5/10/2007)
1330 OR ( l_curr_inv_date <> nvl(l_last_inv_date,sysdate-10000)) Then
1331 -- If the dates are different, then there is no scope of consolidation. However, if the dates are same
1332 -- then further validation against rules is necessary before consolidating (ssiruvol 5/10/2007)
1333
1334 i := i + 1; -- i is a new consolidation invoice (ssiruvol 5/10/2007)
1335 l_cnsld_invs(i).cin_rec.amount := r_invoice.invoice_amount;
1336
1337 ELSE
1338
1339 If l_found_rule_YN = 'N' THEN
1340 i := i + 1; -- Do not consolidate if there are no rules against the invoices, even if everything else matches
1341 -- for consolidation (ssiruvol 5/10/2007)
1342 l_cnsld_invs(i).cin_rec.amount := r_invoice.invoice_amount;
1343 Else
1344
1345 -- date rule (ssiruvol 5/10/2007)
1346 If ( l_curr_invoiced_date = l_last_invoiced_date ) OR ( r_disb_rules.consolidate_by_due_date = 'Y') Then
1347
1348 -- stream type purpose rule (ssiruvol 5/10/2007)
1349 If ( l_curr_sty_id = l_last_sty_id ) OR ( r_disb_rules.consolidate_strm_type = 'Y') Then
1350 l_consolidate_yn := 'Y';
1351 Else
1352 i := i + 1;
1353 l_cnsld_invs(i).cin_rec.amount := r_invoice.invoice_amount;
1354 END If;
1355
1356 Else
1357 i := i + 1;
1358 l_cnsld_invs(i).cin_rec.amount := r_invoice.invoice_amount;
1359 END If;
1360
1361 -- More rules will go here. (For future - ssiruvol 5/10/2007)
1362
1363 End If;
1364
1365 END If;
1366
1367 */
1368 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' last ccd ' || to_char(nvl(l_last_code_combination_id, -99)) );
1369 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' curr ccd ' || to_char(l_curr_code_combination_id) );
1370
1371 If ( nvl(l_curr_vendor_id, -99) = nvl(l_last_vendor_id, -99)
1372 AND nvl(l_curr_vendor_site_id, -99) = nvl(l_last_vendor_site_id, -99)
1373 AND nvl(l_curr_ipvs_id, -99) = nvl(l_last_ipvs_id, -99)
1374 AND nvl(l_curr_pay_group_lookup_code, 'XXX') = nvl(l_last_pay_group_lookup_code, 'XXX')
1375 AND nvl(l_curr_payment_method_code, 'XXX') = nvl(l_last_payment_method_code, 'XXX')
1376 AND nvl(l_curr_ippt_id, -99) = nvl(l_last_ippt_id, -99)
1377 AND nvl(l_curr_set_of_books_id, -99) = nvl(l_last_set_of_books_id, -99)
1378 AND nvl(l_curr_code_combination_id, -99) = nvl(l_last_code_combination_id, -99)
1379 AND nvl(l_curr_currency_code, 'XXX') = nvl(l_last_currency_code, 'XXX')
1380 AND nvl(l_curr_currency_conv_type, 'XXX') = nvl(l_last_currency_conv_type, 'XXX')
1381 AND nvl(l_curr_currency_conv_rate, -99) = nvl(l_last_currency_conv_rate, -99)
1382 AND nvl(l_curr_currency_conv_date, sysdate-10000) = nvl(l_last_currency_conv_date, sysdate-10000)
1383 AND nvl(l_curr_legal_entity_id, -99) = nvl(l_last_legal_entity_id, -99)
1384 AND nvl(l_curr_vpa_id, -99) = nvl(l_last_vpa_id, -99)
1385 AND nvl(l_curr_ipt_id, -99) = nvl(l_last_ipt_id, -99)
1386 AND nvl(l_curr_ipt_frequency, 'XXX') = nvl(l_last_ipt_frequency, 'XXX')
1387 -- If any one of the above header values is different than we cannot consolidate (ssiruvol 5/10/2007)
1388
1389 AND l_found_rule_YN = 'Y') Then
1390
1391 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 6 tyrig to cnsld inv date ' || to_char(l_curr_inv_date) );
1392
1393 -- date rule (ssiruvol 5/10/2007)
1394 If ( l_curr_invoiced_date = l_last_invoiced_date OR r_disb_rules.consolidate_by_due_date = 'Y' ) Then
1395
1396 -- stream type purpose rule (ssiruvol 5/10/2007)
1397 If ( l_curr_sty_id = l_last_sty_id ) OR ( r_disb_rules.consolidate_strm_type = 'Y') Then
1398 l_consolidate_yn := 'Y';
1399 END If;
1400
1401 End If;
1402
1403 If l_consolidate_yn = 'N' AND -- consolidate_by_due_date and consolidate_strm_type are mutually exclusive. This
1404 -- particular check is not met by the above condition.
1405 -- THE INVOICE CURSOR HAS STREAM TYPE PURPOSES sorted
1406 r_disb_rules.consolidate_strm_type = 'Y' AND
1407 r_disb_rules.consolidate_by_due_date = 'N' Then
1408
1409 l_consolidate_yn := 'Y';
1410
1411 End If;
1412
1413 END If;
1414
1415 If l_consolidate_yn = 'Y' and l_curr_inv_date = nvl(l_last_inv_date,sysdate-10000) Then
1416
1417 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 7 tyrig to cnsld inv date ' || to_char(l_curr_inv_date) );
1418
1419 l_cnsld_invs(i).cin_rec.amount := l_cnsld_invs(i).cin_rec.amount + r_invoice.invoice_amount;
1420 l_cnsld_invs(i).cin_rec.vendor_invoice_number := NULL;
1421
1422 ElsIf l_consolidate_yn = 'Y' and l_curr_inv_date <> nvl(l_last_inv_date,sysdate-10000) Then
1423
1424 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 8 ' );
1425
1426 l := nvl(l_cnsld_invs.FIRST, l_cnsld_invs.COUNT + 1);
1427 i := l_cnsld_invs.COUNT + 1; -- making sure to create a new consld transaction incase the while loop is missed
1428
1429 WHILE ( l <= l_cnsld_invs.COUNT ) -- finding the correct invoice to consolidate the current transaction into,
1430 -- based on the invoice date and stream type
1431 LOOP
1432 If (l_cnsld_invs(l).cin_rec.date_invoiced = l_curr_inv_date) AND
1433 ( r_disb_rules.consolidate_strm_type = 'Y' OR l_curr_sty_id = l_cnsld_invs(l).tplv_tbl(1).STY_ID ) Then
1434 i := l;
1435 l := l_cnsld_invs.COUNT + 1;
1436 else
1437 l := l + 1;
1438 i := l;
1439 end if;
1440 End LOOP;
1441
1442 If (i > l_cnsld_invs.COUNT ) Then -- did not find a match, hence a new consold transaction
1443 l_consolidate_yn := 'N';
1444 Else
1445 l_cnsld_invs(i).cin_rec.amount := l_cnsld_invs(i).cin_rec.amount + r_invoice.invoice_amount;
1446 l_cnsld_invs(i).cin_rec.vendor_invoice_number := NULL;
1447 End If;
1448
1449 End If;
1450
1451 If l_consolidate_yn = 'N' Then -- processing a brand new transaction
1452
1453 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 9 ' );
1454
1455 i := l_cnsld_invs.COUNT + 1;
1456 l_cnsld_invs(i).cin_rec.amount := r_invoice.invoice_amount;
1457
1458 l_cnsld_invs(i).cin_rec.date_invoiced := l_curr_inv_date;
1459
1460 -- This is valid ONLY when NOT consolidating the invoices. When consolidating, the vendor_invoice_number
1461 -- on the consolidated invoice has no relation to the orginal set of consolidated invoices. (ssiruvol 5/10/2007)
1462 l_cnsld_invs(i).cin_rec.vendor_invoice_number := r_invoice.vendor_invoice_number;
1463
1464 l_cnsld_invs(i).cin_rec.org_id := r_invoice.org_id;
1465 l_cnsld_invs(i).cin_rec.trx_status_code := 'ENTERED';--r_invoice.trx_status_code;
1466 l_cnsld_invs(i).cin_rec.currency_code := r_invoice.currency_code;
1467 l_cnsld_invs(i).cin_rec.try_id := r_invoice.try_id;
1468 l_cnsld_invs(i).cin_rec.ipvs_id := r_invoice.ipvs_id;
1469 l_cnsld_invs(i).cin_rec.CURRENCY_CONVERSION_TYPE := r_invoice.CURRENCY_CONVERSION_TYPE;
1470 l_cnsld_invs(i).cin_rec.CURRENCY_CONVERSION_RATE := r_invoice.CURRENCY_CONVERSION_RATE;
1471 l_cnsld_invs(i).cin_rec.CURRENCY_CONVERSION_DATE := r_invoice.CURRENCY_CONVERSION_DATE;
1472 l_cnsld_invs(i).cin_rec.PAYMENT_METHOD_CODE := r_invoice.PAYMENT_METHOD_CODE;
1473 l_cnsld_invs(i).cin_rec.PAY_GROUP_LOOKUP_CODE := r_invoice.PAY_GROUP_LOOKUP_CODE ;
1474 l_cnsld_invs(i).cin_rec.INVOICE_TYPE := r_invoice.INVOICE_TYPE;
1475 l_cnsld_invs(i).cin_rec.SET_OF_BOOKS_ID := r_invoice.SET_OF_BOOKS_ID;
1476 l_cnsld_invs(i).cin_rec.IPPT_ID := r_invoice.IPPT_ID;
1477 l_cnsld_invs(i).cin_rec.DATE_GL := r_invoice.DATE_GL;
1478 l_cnsld_invs(i).cin_rec.VENDOR_ID := r_invoice.VENDOR_ID;
1479 l_cnsld_invs(i).cin_rec.LEGAL_ENTITY_ID := r_invoice.LEGAL_ENTITY_ID ;
1480 l_cnsld_invs(i).cin_rec.VPA_ID := r_invoice.VPA_ID ;
1481 l_cnsld_invs(i).cin_rec.OBJECT_VERSION_NUMBER := i ;
1482 -- l_cnsld_invs(i).cin_rec.CREATED_BY := i ; -- cklee 04/26/07
1483 l_cnsld_invs(i).cin_rec.CREATED_BY := fnd_global.user_id ;
1484 l_cnsld_invs(i).cin_rec.CREATION_DATE := sysdate ;
1485 -- l_cnsld_invs(i).cin_rec.LAST_UPDATED_BY := i ; -- cklee 04/26/07
1486 l_cnsld_invs(i).cin_rec.LAST_UPDATED_BY := fnd_global.user_id ;
1487 l_cnsld_invs(i).cin_rec.LAST_UPDATE_DATE := sysdate ;
1488
1489 --start:| 03-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
1490 l_cnsld_invs(i).cin_rec.accts_pay_cc_id := NVL(r_invoice.tap_ccid,r_invoice.cr_ccid);
1491 --end:| 03-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
1492
1493
1494 If l_found_rule_YN = 'Y' THEN
1495
1496 l_cnsld_invs(i).cin_rec.self_bill_inv_num := l_next_inv_seq;
1497 update okl_disb_rule_vendor_sites
1498 set next_inv_seq = l_new_next_inv_seq
1499 where disb_rule_vendor_site_id = r_disb_rules.disb_rule_vendor_site_id
1500 and disb_rule_id = r_disb_rules.disb_rule_id;
1501
1502 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1.1 updating self_bill_inv_num ' || to_char(l_next_inv_seq) );
1503 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 4.1.2 updating next_inv_seq ' || to_char( l_new_next_inv_seq) );
1504
1505 END IF;
1506
1507 End If;
1508
1509 j := l_cnsld_invs(i).tplv_tbl.COUNT + 1;
1510 l_cnsld_invs(i).tplv_tbl(j).id := r_invoice.tpl_id;
1511 l_cnsld_invs(i).tplv_tbl(j).tap_id := r_invoice.tap_id;
1512 l_cnsld_invs(i).tplv_tbl(j).amount := r_invoice.invoice_amount;
1513 l_cnsld_invs(i).tplv_tbl(j).line_number := r_invoice.line_number;
1514 l_cnsld_invs(i).tplv_tbl(j).INV_DISTR_LINE_CODE := r_invoice.INV_DISTR_LINE_CODE;
1515 l_cnsld_invs(i).tplv_tbl(j).DISBURSEMENT_BASIS_CODE := r_invoice.DISBURSEMENT_BASIS_CODE;
1516 l_cnsld_invs(i).tplv_tbl(j).TPL_ID_REVERSES := r_invoice.TPL_ID_REVERSES;
1517 l_cnsld_invs(i).tplv_tbl(j).CODE_COMBINATION_ID := r_invoice.CODE_COMBINATION_ID;
1518 l_cnsld_invs(i).tplv_tbl(j).LSM_ID := r_invoice.LSM_ID;
1519 l_cnsld_invs(i).tplv_tbl(j).KLE_ID := r_invoice.KLE_ID ;
1520 l_cnsld_invs(i).tplv_tbl(j).ITC_ID := r_invoice.ITC_ID ;
1521 l_cnsld_invs(i).tplv_tbl(j).STY_ID := r_invoice.STY_ID;
1522 l_cnsld_invs(i).tplv_tbl(j).DATE_ACCOUNTING := r_invoice.DATE_ACCOUNTING;
1523 l_cnsld_invs(i).tplv_tbl(j).PAYABLES_INVOICE_ID := r_invoice.PAYABLES_INVOICE_ID;
1524 l_cnsld_invs(i).tplv_tbl(j).REQUEST_ID := r_invoice.REQUEST_ID;
1525 l_cnsld_invs(i).tplv_tbl(j).FUNDING_REFERENCE_NUMBER := r_invoice.FUNDING_REFERENCE_NUMBER;
1526 l_cnsld_invs(i).tplv_tbl(j).FUNDING_REFERENCE_TYPE_CODE := r_invoice.FUNDING_REFERENCE_TYPE_CODE;
1527 l_cnsld_invs(i).tplv_tbl(j).SEL_ID := r_invoice.SEL_ID;
1528 l_cnsld_invs(i).tplv_tbl(j).TAXABLE_YN := r_invoice.TAXABLE_YN;
1529 l_cnsld_invs(i).tplv_tbl(j).ORG_ID := r_invoice.ORG_ID;
1530 -- l_cnsld_invs(i).tplv_tbl(j).CREATED_BY := i; -- cklee 4/26/07
1531 l_cnsld_invs(i).tplv_tbl(j).CREATED_BY := fnd_global.user_id;
1532 l_cnsld_invs(i).tplv_tbl(j).CREATION_DATE := sysdate;
1533 -- l_cnsld_invs(i).tplv_tbl(j).LAST_UPDATED_BY := i; -- cklee 4/26/07
1534 l_cnsld_invs(i).tplv_tbl(j).LAST_UPDATED_BY := fnd_global.user_id;
1535 l_cnsld_invs(i).tplv_tbl(j).LAST_UPDATE_DATE := sysdate;
1536 --start:| 02-May-2007 cilee Disbursement changes for R12B |
1537 l_cnsld_invs(i).tplv_tbl(j).KHR_ID := r_invoice.KHR_ID;
1538 l_cnsld_invs(i).tplv_tbl(j).REF_LINE_NUMBER := r_invoice.REF_LINE_NUMBER;
1539 l_cnsld_invs(i).tplv_tbl(j).CNSLD_LINE_NUMBER := r_invoice.CNSLD_LINE_NUMBER;
1540 --end:| 02-May-2007 cklie Disbursement changes for R12B |
1541
1542
1543
1544 l_last_vendor_id := l_curr_vendor_id;
1545 l_last_vendor_site_id := l_curr_vendor_site_id;
1546 l_last_invoiced_date := l_curr_invoiced_date;
1547 l_last_sty_id := l_curr_sty_id;
1548 l_last_inv_date := l_curr_inv_date;
1549
1550 l_last_ipvs_id := l_curr_ipvs_id;
1551 l_last_pay_group_lookup_code := l_curr_pay_group_lookup_code;
1552 l_last_payment_method_code := l_curr_payment_method_code;
1553 l_last_ippt_id := l_curr_ippt_id;
1554 l_last_set_of_books_id := l_curr_set_of_books_id;
1555 l_last_code_combination_id := l_curr_code_combination_id;
1556 l_last_currency_code := l_curr_currency_code;
1557 l_last_currency_conv_type := l_curr_currency_conv_type;
1558 l_last_currency_conv_rate := l_curr_currency_conv_rate;
1559 l_last_currency_conv_date := l_curr_currency_conv_date;
1560 l_last_legal_entity_id := l_curr_legal_entity_id;
1561 l_last_vpa_id := l_curr_vpa_id;
1562 l_last_ipt_id := l_curr_ipt_id;
1563 l_last_ipt_frequency := l_curr_ipt_frequency;
1564
1565 END LOOP;
1566
1567 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 100');
1568
1569 If ( l_cnsld_invs.COUNT > 0 ) then
1570
1571 i := 0;
1572 FOR i in l_cnsld_invs.FIRST..l_cnsld_invs.LAST
1573 LOOP
1574
1575 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 102');
1576 l_gen_seq := NULL;
1577
1578 l_gen_seq := fnd_seqnum.get_next_sequence
1579 (appid => l_okl_application_id,
1580 cat_code => l_document_category,
1581 sobid => l_cnsld_invs(i).cin_rec.set_of_books_id,
1582 met_code => 'A',
1583 trx_date => SYSDATE,
1584 dbseqnm => lx_dbseqnm,
1585 dbseqid => lx_dbseqid);
1586
1587 l_cnsld_invs(i).cin_rec.invoice_number := l_gen_seq;
1588 --start:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
1589 l_cnsld_invs(i).cin_rec.vendor_invoice_number := NVL(l_cnsld_invs(i).cin_rec.vendor_invoice_number,l_gen_seq);
1590 --end:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
1591
1592 END LOOP;
1593
1594 end If;
1595
1596 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PVT 103');
1597 x_cnsld_invs := l_cnsld_invs;
1598
1599 ------------------------------------------------------------
1600 -- End processing
1601 ------------------------------------------------------------
1602
1603 Okl_Api.END_ACTIVITY (
1604 x_msg_count => x_msg_count,
1605 x_msg_data => x_msg_data);
1606
1607
1608 EXCEPTION
1609 ------------------------------------------------------------
1610 -- Exception handling
1611 ------------------------------------------------------------
1612 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1613 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
1614 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1615 p_api_name => l_api_name,
1616 p_pkg_name => G_PKG_NAME,
1617 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1618 x_msg_count => x_msg_count,
1619 x_msg_data => x_msg_data,
1620 p_api_type => '_PVT');
1621
1622 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1623 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
1624 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1625 p_api_name => l_api_name,
1626 p_pkg_name => G_PKG_NAME,
1627 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data,
1630 p_api_type => '_PVT');
1631
1632 WHEN OTHERS THEN
1633 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
1634 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1635 p_api_name => l_api_name,
1636 p_pkg_name => G_PKG_NAME,
1637 p_exc_name => 'OTHERS',
1638 x_msg_count => x_msg_count,
1639 x_msg_data => x_msg_data,
1640 p_api_type => '_PVT');
1641 END apply_consolidation_rules_ssir;
1642
1643 ------------------------------------------------
1644 ------------------------------------------------
1645 --start: 31-Oct-2007 cklee -- bug: 6508575 fixed
1646 ----------------------------------------------------------------------------------
1647 -- Start of comments
1648 --
1649 -- Procedure Name : apply_consolidation_rules
1650 -- Description :
1651 -- Business Rules :
1652 -- Parameters :
1653 -- Version : 1.0
1654 -- End of comments
1655 ----------------------------------------------------------------------------------
1656 PROCEDURE apply_consolidation_rules(
1657 p_api_version IN NUMBER,
1658 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1659 x_return_status OUT NOCOPY VARCHAR2,
1660 x_msg_count OUT NOCOPY NUMBER,
1661 x_msg_data OUT NOCOPY VARCHAR2,
1662 -- x_cnsld_invs OUT NOCOPY cnsld_invs_tbl_type,
1663 p_contract_id IN NUMBER DEFAULT NULL,
1664 p_vendor_id IN NUMBER DEFAULT NULL,
1665 p_vendor_site_id IN NUMBER DEFAULT NULL,
1666 p_vpa_id IN NUMBER DEFAULT NULL,
1667 p_stream_type_purpose IN VARCHAR2 DEFAULT NULL,
1668 p_from_date IN DATE,
1669 p_to_date IN DATE)
1670 IS
1671 ------------------------------------------------------------
1672 -- Declare variables required by APIs
1673 ------------------------------------------------------------
1674 l_api_version CONSTANT NUMBER := 1;
1675 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_CONSOLIDATION_RULES';
1676 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1677 ------------------------------------------------------------
1678 -- Declare Process variables
1679 ------------------------------------------------------------
1680 l_msg_index_out NUMBER;
1681
1682 -- l_cin_tbl OKL_CIN_PVT.cin_tbl_type;
1683 -- lx_cin_tbl OKL_CIN_PVT.cin_tbl_type;
1684
1685 l_cin_rec OKL_CIN_PVT.cin_rec_type;
1686 lx_cin_rec OKL_CIN_PVT.cin_rec_type;
1687
1688 l_tplv_rec OKL_TPL_PVT.tplv_rec_type;
1689 lx_tplv_rec OKL_TPL_PVT.tplv_rec_type;
1690
1691 l_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
1692 lx_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
1693
1694 -- l_cnsld_invs cnsld_invs_tbl_type;
1695
1696 l_gen_seq okl_trx_ap_invoices_v.invoice_number%TYPE;
1697 l_conc_status VARCHAR2(1);
1698
1699 -- Main cursor to get the consolidate invoice header
1700 CURSOR c_cin_invoice IS
1701 SELECT
1702 TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
1703 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) cin_date_invoiced -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1704 ,tap.pay_group_lookup_code
1705 ,tap.ipvs_id
1706 ,tap.ippt_id
1707 ,tap.payment_method_code
1708 ,tap.currency_code
1709 ,tap.currency_conversion_type
1710 ,tap.currency_conversion_rate
1711 ,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
1712 --futrue release ,okl_pay_invoices_cons_pvt.get_contract_group(
1713 -- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')) contract_group
1714 ,okl_pay_invoices_cons_pvt.get_Disbursement_rule(
1715 tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_rule
1716 ,okl_pay_invoices_cons_pvt.get_Disbursement_group(
1717 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_group
1718 ,SUM(tpl.amount) cin_invoice_amount
1719 ,povs.vendor_id
1720 ,tap.INVOICE_TYPE
1721 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
1722 ,tap.LEGAL_ENTITY_ID
1723 ,tap.VPA_ID
1724 ,acc_cr.code_combination_id
1725 ,COUNT(1) line_count
1726 FROM
1727 okl_trns_acc_dstrs acc_cr
1728 ,okl_txl_ap_inv_lns_b tpl
1729 ,okl_trx_ap_invoices_b tap
1730 ,po_vendor_sites_all povs
1731 -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1732 ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1733 WHERE
1734 NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
1735 trunc(tap.date_invoiced) BETWEEN
1736 NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1737 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C'
1738 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1739 AND tpl.id = acc_cr.source_id (+)
1740 AND tap.id = tpl.tap_id
1741 AND povs.vendor_site_id = tap.ipvs_id
1742 AND tap.FUNDING_TYPE_CODE IS NULL
1743 AND nvl(tpl.khr_id,-1) = nvl(p_contract_id, nvl(tpl.khr_id,-1)) -- cklee 09/13/07
1744 AND nvl(povs.vendor_id,-1) = nvl(p_vendor_id, nvl(povs.vendor_id,-1) )
1745 AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
1746 -- AND tpl.khr_id = chr.id
1747 AND tpl.sty_id = sty.id
1748 AND nvl(sty.stream_type_purpose, 'xxx') = nvl(p_stream_type_purpose,nvl(sty.stream_type_purpose, 'xxx'))
1749 AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
1750 GROUP BY
1751 povs.vendor_id,
1752 tap.ipvs_id,
1753 tap.pay_group_lookup_code,
1754 tap.payment_method_code,
1755 tap.ippt_id,
1756 NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id),
1757 acc_cr.code_combination_id,
1758 tap.currency_code,
1759 tap.currency_conversion_type,
1760 tap.currency_conversion_rate,
1761 TRUNC(tap.currency_conversion_date), -- cklee 09/13/07
1762 tap.legal_entity_id,
1763 tap.vpa_id,
1764 tap.invoice_type,
1765 okl_pay_invoices_cons_pvt.get_Disbursement_rule(
1766 tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
1767 okl_pay_invoices_cons_pvt.get_Disbursement_group(
1768 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
1769 TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
1770 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1771 UNION ALL
1772 SELECT
1773 TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
1774 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) cin_date_invoiced -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1775 ,tap.pay_group_lookup_code
1776 ,tap.ipvs_id
1777 ,tap.ippt_id
1778 ,tap.payment_method_code
1779 ,tap.currency_code
1780 ,tap.currency_conversion_type
1781 ,tap.currency_conversion_rate
1782 ,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
1783 --futrue release ,okl_pay_invoices_cons_pvt.get_contract_group(
1784 -- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')) contract_group
1785 ,okl_pay_invoices_cons_pvt.get_Disbursement_rule(
1786 tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_rule
1787 ,okl_pay_invoices_cons_pvt.get_Disbursement_group(
1788 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_group
1789 ,SUM(tpl.amount) cin_invoice_amount
1790 ,povs.vendor_id
1791 ,tap.INVOICE_TYPE
1792 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
1793 ,tap.LEGAL_ENTITY_ID
1794 ,tap.VPA_ID
1795 ,acc_cr.code_combination_id
1796 ,COUNT(1) line_count
1797 FROM
1798 okl_trns_acc_dstrs acc_cr
1799 ,okl_txl_ap_inv_lns_b tpl
1800 ,okl_trx_ap_invoices_b tap
1801 ,po_vendor_sites_all povs
1802 -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1803 ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1804 WHERE
1805 NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED') AND
1806 trunc(tap.date_invoiced) BETWEEN
1807 NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1808 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C'
1809 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1810 AND tpl.id = acc_cr.source_id (+)
1811 AND tap.id = tpl.tap_id
1812 AND povs.vendor_site_id = tap.ipvs_id
1813 AND tap.FUNDING_TYPE_CODE IS NOT NULL
1814 AND nvl(tpl.khr_id,-1) = nvl(p_contract_id, nvl(tpl.khr_id,-1)) -- cklee 09/13/07
1815 AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
1816 AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
1817 -- AND tpl.khr_id = chr.id
1818 AND tpl.sty_id = sty.id
1819 AND nvl(sty.stream_type_purpose, 'xxx') = nvl(p_stream_type_purpose,nvl(sty.stream_type_purpose, 'xxx'))
1820 AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
1821 GROUP BY
1822 povs.vendor_id,
1823 tap.ipvs_id,
1824 tap.pay_group_lookup_code,
1825 tap.payment_method_code,
1826 tap.ippt_id,
1827 NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id),
1828 acc_cr.code_combination_id,
1829 tap.currency_code,
1830 tap.currency_conversion_type,
1831 tap.currency_conversion_rate,
1832 TRUNC(tap.currency_conversion_date), -- cklee 09/13/07
1833 tap.legal_entity_id,
1834 tap.vpa_id,
1835 tap.invoice_type,
1836 okl_pay_invoices_cons_pvt.get_Disbursement_rule(
1837 tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
1838 okl_pay_invoices_cons_pvt.get_Disbursement_group(
1839 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
1840 TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
1841 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')); -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1842
1843 r_cin_invoice c_cin_invoice%ROWTYPE;
1844
1845 --
1846 -- details cursor to get all invoice lines
1847 CURSOR c_invoice (
1848 p_vendor_id po_vendor_sites_all.vendor_id%type,
1849 p_ipvs_id okl_trx_ap_invs_all_b.ipvs_id%type,
1850 p_pay_group_lookup_code okl_trx_ap_invs_all_b.pay_group_lookup_code%type,
1851 p_payment_method_code okl_trx_ap_invs_all_b.payment_method_code%type,
1852 p_ippt_id okl_trx_ap_invs_all_b.ippt_id%type,
1853 p_set_of_books_id okl_trx_ap_invs_all_b.set_of_books_id%type,
1854 p_code_combination_id okl_trns_acc_dstrs.code_combination_id%type,
1855 p_currency_code okl_trx_ap_invs_all_b.currency_code%type,
1856 p_currency_conversion_type okl_trx_ap_invs_all_b.currency_conversion_type%type,
1857 p_currency_conversion_rate okl_trx_ap_invs_all_b.currency_conversion_rate%type,
1858 p_currency_conversion_date date,
1859 p_legal_entity_id okl_trx_ap_invs_all_b.legal_entity_id%type,
1860 p_vpa_id okl_trx_ap_invs_all_b.vpa_id%type,
1861 p_invoice_type okl_trx_ap_invs_all_b.invoice_type%type,
1862 -- p_contract_group okc_k_headers_all_b.contract_number%type,
1863 p_disbursement_rule varchar2,
1864 p_disbursement_group varchar2,
1865 p_cin_date_invoiced date
1866 )IS
1867 SELECT
1868 tap.id tap_id
1869 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
1870 ,tap.org_id
1871 ,tap.invoice_number
1872 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
1873 ,tap.code_combination_id tap_ccid
1874 ,tap.date_invoiced date_invoiced
1875 ,tap.pay_group_lookup_code
1876 ,tap.ipvs_id
1877 ,tap.ippt_id
1878 ,tap.payment_method_code
1879 ,tap.currency_code
1880 ,tap.currency_conversion_type
1881 ,tap.currency_conversion_rate
1882 ,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
1883 ,tpl.khr_id
1884 ,tap.try_id
1885 ,tpl.id tpl_id
1886 ,tpl.sty_id
1887 ,acc_db.code_combination_id db_ccid
1888 ,acc_cr.code_combination_id cr_ccid
1889 ,povs.vendor_id
1890 ,tap.INVOICE_TYPE
1891 ,tap.DATE_GL
1892 ,tap.LEGAL_ENTITY_ID
1893 ,tap.VPA_ID
1894 -- ,nvl(tpl.adv_grouping_flag, 'Y') adv_grouping_flag -- cklee 09/14/07
1895 ,sty.stream_type_purpose -- cklee 09/14/07
1896 FROM
1897 okl_trns_acc_dstrs acc_db
1898 ,okl_trns_acc_dstrs acc_cr
1899 ,po_vendor_sites_all povs
1900 ,okl_txl_ap_inv_lns_b tpl
1901 ,okl_trx_ap_invoices_b tap
1902 -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1903 ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1904 WHERE
1905 NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
1906 AND trunc(tap.date_invoiced) BETWEEN
1907 NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1908 AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
1909 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
1910 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1911 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1912 AND tpl.id = acc_db.source_id (+)
1913 AND tpl.id = acc_cr.source_id (+)
1914 AND povs.vendor_site_id = tap.ipvs_id
1915 AND tap.id = tpl.tap_id
1916 AND tap.FUNDING_TYPE_CODE IS NULL
1917 -- AND tpl.khr_id = chr.id
1918 AND tpl.sty_id = sty.id
1919 and tpl.khr_id = nvl(p_contract_id, tpl.khr_id) -- cklee 09/13/07
1920 and sty.stream_type_purpose = nvl(p_stream_type_purpose, sty.stream_type_purpose)
1921 and povs.vendor_id = p_vendor_id
1922 and tap.ipvs_id = p_ipvs_id
1923 and NVL(tap.pay_group_lookup_code,'x') = NVL(p_pay_group_lookup_code,'x')
1924 and tap.payment_method_code = p_payment_method_code
1925 and NVL(tap.ippt_id,-123) = NVL(p_ippt_id,-123)
1926 and NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) = p_set_of_books_id
1927 and nvl(acc_cr.code_combination_id, -1) = nvl(p_code_combination_id, nvl(acc_cr.code_combination_id, -1))
1928 and tap.currency_code = p_currency_code
1929 and nvl(tap.currency_conversion_type, 'x') = nvl(p_currency_conversion_type,nvl(tap.currency_conversion_type, 'x'))
1930 and nvl(tap.currency_conversion_rate, -1) = nvl(p_currency_conversion_rate,nvl(tap.currency_conversion_rate, -1))
1931 and nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate)) = nvl(TRUNC(p_currency_conversion_date),nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate))) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1932 and tap.legal_entity_id = p_legal_entity_id
1933 and nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
1934 and nvl(tap.invoice_type, 'x') = nvl(p_invoice_type,nvl(tap.invoice_type, 'x'))
1935 and okl_pay_invoices_cons_pvt.get_Disbursement_rule(
1936 tap.date_invoiced,tpl.id, povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
1937 = p_disbursement_rule
1938 and okl_pay_invoices_cons_pvt.get_Disbursement_group(
1939 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
1940 = p_disbursement_group
1941 -- and nvl(okl_pay_invoices_cons_pvt.get_contract_group(
1942 -- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')), 'x')
1943 -- = nvl(p_contract_group, 'x')
1944 and TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
1945 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
1946 = p_cin_date_invoiced
1947 UNION ALL
1948 SELECT
1949 tap.id tap_id
1950 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
1951 ,tap.org_id
1952 ,tap.invoice_number
1953 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
1954 ,tap.code_combination_id tap_ccid
1955 ,tap.date_invoiced date_invoiced
1956 ,tap.pay_group_lookup_code
1957 ,tap.ipvs_id
1958 ,tap.ippt_id
1959 ,tap.payment_method_code
1960 ,tap.currency_code
1961 ,tap.currency_conversion_type
1962 ,tap.currency_conversion_rate
1963 ,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
1964 ,tpl.khr_id
1965 ,tap.try_id
1966 ,tpl.id tpl_id
1967 ,tpl.sty_id
1968 ,acc_db.code_combination_id db_ccid
1969 ,acc_cr.code_combination_id cr_ccid
1970 ,povs.vendor_id
1971 ,tap.INVOICE_TYPE
1972 ,tap.DATE_GL
1973 ,tap.LEGAL_ENTITY_ID
1974 ,tap.VPA_ID
1975 -- ,nvl(tpl.adv_grouping_flag, 'Y') adv_grouping_flag -- cklee 09/14/07
1976 ,sty.stream_type_purpose -- cklee 09/14/07
1977 FROM
1978 okl_trns_acc_dstrs acc_db
1979 ,okl_trns_acc_dstrs acc_cr
1980 ,po_vendor_sites_all povs
1981 ,okl_txl_ap_inv_lns_b tpl
1982 ,okl_trx_ap_invoices_b tap
1983 -- ,okc_k_headers_all_b chr -- cklee 09/13/07
1984 ,OKL_STRM_TYPE_B sty -- cklee 09/13/07
1985 WHERE
1986 NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
1987 AND trunc(tap.date_invoiced) BETWEEN
1988 NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
1989 AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
1990 AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
1991 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1992 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
1993 AND tpl.id = acc_db.source_id (+)
1994 AND tpl.id = acc_cr.source_id (+)
1995 AND povs.vendor_site_id = tap.ipvs_id
1996 AND tap.id = tpl.tap_id
1997 AND tap.FUNDING_TYPE_CODE IS NOT NULL
1998 -- AND tpl.khr_id = chr.id
1999 AND tpl.sty_id = sty.id
2000 and tpl.khr_id = nvl(p_contract_id, tpl.khr_id) -- cklee 09/13/07
2001 and sty.stream_type_purpose = nvl(p_stream_type_purpose, sty.stream_type_purpose)
2002 and povs.vendor_id = p_vendor_id
2003 and tap.ipvs_id = p_ipvs_id
2004 and NVL(tap.pay_group_lookup_code,'x') = NVL(p_pay_group_lookup_code,'x')
2005 and tap.payment_method_code = p_payment_method_code
2006 and NVL(tap.ippt_id,-123) = NVL(p_ippt_id,-123)
2007 and NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) = p_set_of_books_id
2008 and nvl(acc_cr.code_combination_id, -1) = nvl(p_code_combination_id, nvl(acc_cr.code_combination_id, -1))
2009 and tap.currency_code = p_currency_code
2010 and nvl(tap.currency_conversion_type, 'x') = nvl(p_currency_conversion_type,nvl(tap.currency_conversion_type, 'x'))
2011 and nvl(tap.currency_conversion_rate, -1) = nvl(p_currency_conversion_rate,nvl(tap.currency_conversion_rate, -1))
2012 and nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate)) = nvl(TRUNC(p_currency_conversion_date),nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate))) -- -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
2013 and tap.legal_entity_id = p_legal_entity_id
2014 and nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
2015 and nvl(tap.invoice_type, 'x') = nvl(p_invoice_type,nvl(tap.invoice_type, 'x'))
2016 and okl_pay_invoices_cons_pvt.get_Disbursement_rule(
2017 tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
2018 = p_disbursement_rule
2019 and okl_pay_invoices_cons_pvt.get_Disbursement_group(
2020 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
2021 = p_disbursement_group
2022 -- and nvl(okl_pay_invoices_cons_pvt.get_contract_group(
2023 -- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')), 'x')
2024 -- = nvl(p_contract_group, 'x')
2025 and TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
2026 tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'))
2027 = p_cin_date_invoiced; -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
2028
2029 r_invoice c_invoice%ROWTYPE;
2030
2031
2032 l_okl_application_id NUMBER(3) := 540;
2033 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
2034 lX_dbseqnm VARCHAR2(2000):= '';
2035 lX_dbseqid NUMBER(38):= NULL;
2036
2037 cin_cnt number := 0;
2038 cin_ln_cnt number := 0;
2039
2040 BEGIN
2041 ------------------------------------------------------------
2042 -- Start processing
2043 ------------------------------------------------------------
2044
2045 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 1');
2046
2047 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2048
2049 l_return_status := OKL_API.START_ACTIVITY(
2050 p_api_name => l_api_name,
2051 p_pkg_name => g_pkg_name,
2052 p_init_msg_list => p_init_msg_list,
2053 l_api_version => l_api_version,
2054 p_api_version => p_api_version,
2055 p_api_type => '_PVT',
2056 x_return_status => l_return_status);
2057
2058 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2059 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2060 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2061 RAISE OKL_API.G_EXCEPTION_ERROR;
2062 END IF;
2063
2064 BEGIN -- block to handel trx status update
2065 cin_ln_cnt := 0; -- initial to 0
2066 -- go through each cin header
2067 FOR r_cin_invoice IN c_cin_invoice LOOP
2068
2069 cin_cnt := 0; -- initial to 0
2070
2071 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 1.1');
2072
2073
2074 -- FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 cin_cnt:' || cin_cnt);
2075 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2');
2076 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 p_contract_id => '|| p_contract_id);
2077 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 p_from_date => '|| p_from_date);
2078 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 p_to_date => '|| p_to_date);
2079 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 p_stream_type_purpose => '|| p_stream_type_purpose);
2080
2081 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 p_vendor_id => '|| r_cin_invoice.vendor_id);
2082 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_ipvs_id =>'|| r_cin_invoice.ipvs_id);
2083 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_pay_group_lookup_code =>'|| r_cin_invoice.pay_group_lookup_code);
2084 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_payment_method_code =>'|| r_cin_invoice.payment_method_code);
2085 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_ippt_id =>'|| r_cin_invoice.ippt_id);
2086 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_set_of_books_id =>'|| r_cin_invoice.sob_id);
2087 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_code_combination_id =>'|| r_cin_invoice.code_combination_id);
2088 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_currency_code =>'|| r_cin_invoice.currency_code);
2089 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_currency_conversion_type =>'|| r_cin_invoice.currency_conversion_type);
2090 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_currency_conversion_rate =>'|| r_cin_invoice.currency_conversion_rate);
2091 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_currency_conversion_date =>'|| r_cin_invoice.currency_conversion_date);
2092 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_legal_entity_id =>'|| r_cin_invoice.legal_entity_id);
2093 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_vpa_id =>'|| r_cin_invoice.vpa_id);
2094 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_invoice_type =>'|| r_cin_invoice.invoice_type);
2095 -- ,p_contract_group => r_cin_invoice.contract_group
2096 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_disbursement_rule =>'|| r_cin_invoice.disbursement_rule);
2097 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_disbursement_group =>'|| r_cin_invoice.disbursement_group);
2098 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2 ,p_cin_date_invoiced =>'|| r_cin_invoice.cin_date_invoiced);
2099
2100 -- go through all cin lines
2101 FOR r_invoice IN c_invoice(
2102 p_vendor_id => r_cin_invoice.vendor_id
2103 ,p_ipvs_id => r_cin_invoice.ipvs_id
2104 ,p_pay_group_lookup_code => r_cin_invoice.pay_group_lookup_code
2105 ,p_payment_method_code => r_cin_invoice.payment_method_code
2106 ,p_ippt_id => r_cin_invoice.ippt_id
2107 ,p_set_of_books_id => r_cin_invoice.sob_id
2108 ,p_code_combination_id => r_cin_invoice.code_combination_id
2109 ,p_currency_code => r_cin_invoice.currency_code
2110 ,p_currency_conversion_type => r_cin_invoice.currency_conversion_type
2111 ,p_currency_conversion_rate => r_cin_invoice.currency_conversion_rate
2112 ,p_currency_conversion_date => r_cin_invoice.currency_conversion_date
2113 ,p_legal_entity_id => r_cin_invoice.legal_entity_id
2114 ,p_vpa_id => r_cin_invoice.vpa_id
2115 ,p_invoice_type => r_cin_invoice.invoice_type
2116 -- ,p_contract_group => r_cin_invoice.contract_group
2117 ,p_disbursement_rule => r_cin_invoice.disbursement_rule
2118 ,p_disbursement_group => r_cin_invoice.disbursement_group
2119 ,p_cin_date_invoiced => r_cin_invoice.cin_date_invoiced
2120 ) LOOP
2121
2122 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 2: in loop:cin_cnt<' || cin_cnt || '>');
2123 -- 1. create cin header
2124 IF cin_cnt = 0 THEN
2125 -- get all attributes and assign to cin record
2126 l_cin_rec.amount := r_cin_invoice.cin_invoice_amount;
2127
2128 l_cin_rec.date_invoiced := r_cin_invoice.cin_date_invoiced;
2129
2130 --start:| 30-Nov-2007 cklee -- bug: 6628542 fixed |
2131 -- 1 header and 1 line case
2132 -- FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 3r_cin_invoice.line_count: '
2133 -- || r_cin_invoice.line_count );
2134 -- IF r_cin_invoice.line_count = 1 THEN
2135 -- l_cin_rec.vendor_invoice_number := r_invoice.vendor_invoice_number;
2136 -- l_cin_rec.invoice_number := r_invoice.invoice_number;
2137 -- ELSE -- 1 header and multiple lines case
2138 l_gen_seq := NULL;
2139 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 4 fnd_seqnum.get_next_sequence: r_invoice.sob_id' || r_invoice.sob_id );
2140
2141 FOR v_cnt In 1..2 LOOP
2142 l_gen_seq := fnd_seqnum.get_next_sequence
2143 (appid => l_okl_application_id,
2144 cat_code => l_document_category,
2145 sobid => r_invoice.sob_id,
2146 met_code => 'A',
2147 trx_date => SYSDATE,
2148 dbseqnm => lx_dbseqnm,
2149 dbseqid => lx_dbseqid);
2150
2151 IF v_cnt = 1 THEN
2152 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 5 fnd_seqnum.get_next_sequence: l_gen_seq(l_cin_rec.vendor_invoice_number- AP invoice number)' || l_gen_seq );
2153 l_cin_rec.vendor_invoice_number := l_gen_seq;
2154 ELSE
2155 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'apply_consolidation_rules 5 fnd_seqnum.get_next_sequence: l_gen_seq(l_cin_rec.invoice_number- AP voucher number)' || l_gen_seq );
2156 l_cin_rec.invoice_number := l_gen_seq;
2157 END IF;
2158 END LOOP;
2159
2160 -- END IF;
2161 --end:| 30-Nov-2007 cklee -- bug: 6628542 fixed |
2162
2163 -- 1. get vendor sequence number if any
2164 -- 2. reset the vendor sequence number at vendor site, rule
2165 -- 3. set vendor sequence number at cin header
2166 handle_next_invoice_seq(
2167 p_api_version => p_api_version,
2168 p_init_msg_list => p_init_msg_list,
2169 x_return_status => x_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data,
2172 p_transaction_date => r_invoice.date_invoiced,
2173 p_vendor_id => r_invoice.vendor_id,
2174 p_vendor_site_id => r_invoice.ipvs_id,
2175 p_stream_type_purpose => r_invoice.stream_type_purpose,
2176 p_adv_grouping_flag => 'Y',
2177 x_next_inv_seq => l_cin_rec.self_bill_inv_num);
2178
2179
2180 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2181 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: get and set vendor invoice sequence.');
2182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2183 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2184 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: get and set vendor invoice sequence.');
2185 RAISE OKL_API.G_EXCEPTION_ERROR;
2186 ELSIF (x_return_status ='S') THEN
2187 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' handle_next_invoice_seq: ' || l_cin_rec.self_bill_inv_num);
2188 END IF;
2189
2190 l_cin_rec.org_id := r_invoice.org_id;
2191 l_cin_rec.trx_status_code := 'ENTERED';--r_invoice.trx_status_code;
2192 l_cin_rec.currency_code := r_invoice.currency_code;
2193 l_cin_rec.try_id := r_invoice.try_id;
2194 l_cin_rec.ipvs_id := r_invoice.ipvs_id;
2195 l_cin_rec.CURRENCY_CONVERSION_TYPE := r_invoice.CURRENCY_CONVERSION_TYPE;
2196 l_cin_rec.CURRENCY_CONVERSION_RATE := r_invoice.CURRENCY_CONVERSION_RATE;
2197 l_cin_rec.CURRENCY_CONVERSION_DATE := r_invoice.CURRENCY_CONVERSION_DATE;
2198 l_cin_rec.PAYMENT_METHOD_CODE := r_invoice.PAYMENT_METHOD_CODE;
2199 l_cin_rec.PAY_GROUP_LOOKUP_CODE := r_invoice.PAY_GROUP_LOOKUP_CODE ;
2200 l_cin_rec.INVOICE_TYPE := r_invoice.INVOICE_TYPE;
2201 l_cin_rec.SET_OF_BOOKS_ID := r_invoice.sob_id;
2202 l_cin_rec.IPPT_ID := r_invoice.IPPT_ID;
2203 l_cin_rec.DATE_GL := r_invoice.DATE_GL;
2204 l_cin_rec.VENDOR_ID := r_invoice.VENDOR_ID;
2205 l_cin_rec.LEGAL_ENTITY_ID := r_invoice.LEGAL_ENTITY_ID ;
2206 l_cin_rec.VPA_ID := r_invoice.VPA_ID ;
2207 l_cin_rec.OBJECT_VERSION_NUMBER := 1 ;
2208 l_cin_rec.CREATED_BY := fnd_global.user_id ;
2209 l_cin_rec.CREATION_DATE := sysdate ;
2210 l_cin_rec.LAST_UPDATED_BY := fnd_global.user_id ;
2211 l_cin_rec.LAST_UPDATE_DATE := sysdate ;
2212 l_cin_rec.accts_pay_cc_id := NVL(r_invoice.tap_ccid,r_invoice.cr_ccid);
2213 l_cin_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
2214
2215 OKL_CIN_PVT.insert_row(
2216 p_api_version => p_api_version,
2217 p_init_msg_list => p_init_msg_list,
2218 x_return_status => x_return_status,
2219 x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data,
2221 p_cin_rec => l_cin_rec,
2222 x_cin_rec => lx_cin_rec);
2223
2224 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2225 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' (apply_consolidation_rules)ERROR: Creating Consolidated invoices.');
2226 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2227 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2228 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' (apply_consolidation_rules)ERROR: Creating Consolidated invoices.');
2229 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2230 ELSIF (x_return_status ='S') THEN
2231 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' (apply_consolidation_rules)Created Consolidated invoices: '||lx_cin_rec.cnsld_ap_inv_id);
2232 END IF;
2233
2234 cin_cnt := cin_cnt + 1; -- create header only once
2235 END IF; -- IF cin_cnt = 0 THEN
2236
2237 cin_ln_cnt := cin_ln_cnt + 1; -- increase the count
2238 -- 2. update okl_txl_ap_inv_lns_all_b with FK: cnsld_ap_inv_id and cnsld_line_number
2239 UPDATE okl_txl_ap_inv_lns_all_b txl
2240 SET txl.cnsld_ap_inv_id = lx_cin_rec.cnsld_ap_inv_id,
2241 txl.cnsld_line_number = cin_ln_cnt,
2242 txl.LAST_UPDATED_BY = fnd_global.user_id,
2243 txl.LAST_UPDATE_DATE = sysdate,
2244 txl.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
2245 WHERE txl.id = r_invoice.tpl_id;
2246 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' update okl_txl_ap_inv_lns_all_b with FK: cnsld_ap_inv_id and cnsld_line_number. cin_ln_cnt:<'
2247 || cin_ln_cnt || '>r_invoice.tpl_id:' ||r_invoice.tpl_id || 'lx_cin_rec.cnsld_ap_inv_id:'|| lx_cin_rec.cnsld_ap_inv_id);
2248
2249 l_tplv_tbl(cin_ln_cnt).tap_id := r_invoice.tap_id;
2250 END LOOP; -- end of c_invoice loop
2251 END LOOP;
2252
2253 -- Since the status stored at header, we only need to update once if
2254 -- one of the children record has been processed.
2255 --------------------------------------------------------------------
2256 -- note: we shall add another trx_status_code at line level okl_txl_ap_inv_lns_all_b
2257 -- in case if lines merged with diferent invoice header
2258 --------------------------------------------------------------------
2259 IF l_tplv_tbl.COUNT > 0 THEN
2260 FOR k in l_tplv_tbl.FIRST..l_tplv_tbl.LAST
2261 LOOP
2262 UPDATE OKL_TRX_AP_INVS_ALL_B trx
2263 SET trx.TRX_STATUS_CODE = 'PROCESSED',
2264 trx.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
2265 WHERE trx.ID = l_tplv_tbl(k).tap_id
2266 AND trx.TRX_STATUS_CODE IN ('ENTERED', 'APPROVED');
2267 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' UPDATE OKL_TRX_AP_INVS_ALL_B with Processed status.l_tplv_tbl('
2268 ||k||').tap_id'|| l_tplv_tbl(k).tap_id);
2269
2270 END LOOP;
2271 END IF;
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275
2276 IF l_tplv_tbl.COUNT > 0 THEN
2277 FOR j in l_tplv_tbl.FIRST..l_tplv_tbl.LAST
2278 LOOP
2279
2280 UPDATE OKL_TRX_AP_INVS_ALL_B trx
2281 SET trx.TRX_STATUS_CODE = 'ERROR',
2282 trx.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
2283 WHERE trx.ID = l_tplv_tbl(j).tap_id
2284 AND trx.TRX_STATUS_CODE IN ('ENTERED', 'APPROVED');
2285 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' UPDATE OKL_TRX_AP_INVS_ALL_B with Error status.l_tplv_tbl(' ||j||').tap_id'|| l_tplv_tbl(j).tap_id);
2286
2287 END LOOP;
2288 END IF;
2289 END;
2290
2291 ------------------------------------------------------------
2292 -- End processing
2293 ------------------------------------------------------------
2294
2295 Okl_Api.END_ACTIVITY (
2296 x_msg_count => x_msg_count,
2297 x_msg_data => x_msg_data);
2298
2299
2300 EXCEPTION
2301 ------------------------------------------------------------
2302 -- Exception handling
2303 ------------------------------------------------------------
2304 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2305 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2306 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2307 p_api_name => l_api_name,
2308 p_pkg_name => G_PKG_NAME,
2309 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2310 x_msg_count => x_msg_count,
2311 x_msg_data => x_msg_data,
2312 p_api_type => '_PVT');
2313
2314 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2315 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2316 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2317 p_api_name => l_api_name,
2318 p_pkg_name => G_PKG_NAME,
2319 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2320 x_msg_count => x_msg_count,
2321 x_msg_data => x_msg_data,
2322 p_api_type => '_PVT');
2323
2324 WHEN OTHERS THEN
2325 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2326 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2327 p_api_name => l_api_name,
2328 p_pkg_name => G_PKG_NAME,
2329 p_exc_name => 'OTHERS',
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data,
2332 p_api_type => '_PVT');
2333 END apply_consolidation_rules;
2334
2335 ----------------------------------------------------------------------------------
2336 -- Start of comments
2337 --
2338 -- Procedure Name : handle_processing_fee
2339 -- Description :
2340 -- Business Rules :
2341 -- Parameters :
2342 -- Version : 1.0
2343 -- End of comments
2344 ----------------------------------------------------------------------------------
2345 PROCEDURE handle_processing_fee(p_api_version IN NUMBER
2346 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
2347 ,x_return_status OUT NOCOPY VARCHAR2
2348 ,x_msg_count OUT NOCOPY NUMBER
2349 ,x_msg_data OUT NOCOPY VARCHAR2)
2350 IS
2351 ------------------------------------------------------------
2352 -- Declare Process variables
2353 ------------------------------------------------------------
2354 l_api_version CONSTANT NUMBER := 1;
2355 l_api_name CONSTANT VARCHAR2(30) := 'handle_processing_fee';
2356 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2357 l_msg_index_out NUMBER;
2358 i NUMBER;
2359
2360 /*
2361 -- find existing 'no further processing" flag
2362 CURSOR c_no_adv_grp(p_cnsld_ap_invs_id okl_cnsld_ap_invs_all.cnsld_ap_inv_id%type) IS
2363 SELECT 1
2364 FROM OKL_TRX_AP_INVS_ALL_B TAP
2365 where NVL(TAP.ADV_GROUPING_FLAG, 'Y') = 'N'
2366 and tap.id in
2367 (select txl.tap_id
2368 from okl_txl_ap_invs_all_b txl,
2369 okl_cnsld_ap_invs_all cin
2370 where txl.cnsld_ap_inv_id = tap.cnsld_ap_inv_id
2371 and txl.tap_id = tap.id);
2372 */
2373
2374 CURSOR c_cnsld_hdr IS
2375 SELECT *
2376 FROM OKL_CNSLD_AP_INVS_ALL
2377 WHERE trx_status_code = 'ENTERED';
2378
2379 l_row_found BOOLEAN;
2380 -- r_no_adv_grp c_no_adv_grp%ROWTYPE;
2381 BEGIN
2382
2383 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2384
2385 l_return_status := OKL_API.START_ACTIVITY(
2386 p_api_name => l_api_name,
2387 p_pkg_name => g_pkg_name,
2388 p_init_msg_list => p_init_msg_list,
2389 l_api_version => l_api_version,
2390 p_api_version => p_api_version,
2391 p_api_type => '_PVT',
2392 x_return_status => l_return_status);
2393
2394 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2395 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2396 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2397 RAISE OKL_API.G_EXCEPTION_ERROR;
2398 END IF;
2399 /*
2400
2401 Question 1:
2402 Processing fee:
2403 For each OKL internal invoice line, system guarantee to get only one unique Disbursement rule that based on vendor site,
2404 stream type purpose, and date range vs transaction's vendor site, stream type purpose, and transaction date.
2405 This implies that once a consoldiate invoice has been generated, one invoice may refer to multiple Disbursement
2406 rules due to the changes of the final invoice date as well as combine stream type purposes.
2407 So how does the system to determine which rule need to be picked for processing fee process?
2408
2409 Question 2:
2410 There is no relationship between lines within the same invoice. Hence this is no way to find out the link between
2411 individual invoice line and individual processing fee within the invoice.
2412
2413 Question 3:
2414 Processing fee:
2415 if system subtract the fee amount from invoice amount, we may lose the track of the original transaaction amount
2416
2417 Business logic:
2418 if Account Derivation option = AMB then
2419 if Fee Option = Per Invoice then
2420 --The fee is charged as one separate line on the AP invoice with a negative amount. fee option: fixed amount
2421 "1. Create a new OKL internal invoice header and line with invoice type = 'Mixed' with negative amount.
2422 (Assume the processing fee amount < invoice amount.)
2423 2. Update # 1 line FK with #1 consoldiate invoice
2424 3. Assume processing fee is only applicable for ""Standard"" invoice.
2425 4. Mark the OKL internal invoice header status to 'Processed"" once complete the transaction of consoldation."
2426
2427 elsif Fee Option = Per Invoice line then
2428 --The fee is charged as multiple separate lines, one corresponding to each invoice line on the AP invoice with a negative amounts.
2429 -- The amount of fee corresponding to an invoice line = Invoice line amount * %
2430 "1. Create a new OKL internal invoice line with negative amount and associated with the corresponding OKL internal invoice header.
2431 (Assume the processing fee amount < invoice amount.)
2432 2. Update # 1 line FK with #1 consoldiate invoice.
2433 3. Assume processing fee is only applicable for ""Standard"" invoice.
2434
2435 end if;
2436
2437 elsif Account Derivation option = ATS then
2438 if Fee Option = Per Invoice then
2439 --The fee amount is approportioned across invoice line amounts and subtracted from line amounts.
2440 "1. Find out the propotion amount for each line and update each line.
2441 formula:
2442 new line amount := old line amount - (fixed fee * (line amount / header amount))
2443 2. Update the OKL internal invoice header amount accordingly
2444 formula:
2445 new OKL internal header amount := old header amount - (fixed fee * (line amount / header amount))
2446 3. Update the consolidation invoice header amount acordingly.
2447 formula:
2448 3. new header amount := sum of all line amount (rounding issue may happen)
2449 (Assume the processing fee amount < invoice amount.)
2450 4. Assume processing fee is only applicable for ""Standard"" invoice.
2451
2452
2453 elsif Fee Option = Per Invoice line then
2454 --The fee amount per line is calculated as % of line amount and subtracted from each line
2455 "1. Find out the propotion amount for each line and update each line.
2456 formula:
2457 new line amount := old line amount - (old line amount * fee %)
2458 2. Update the OKL internal invoice header amount accordingly
2459 formula:
2460 new OKL internal header amount := old header amount - (old line amount * fee %)
2461 3. Update the consolidation invoice header amount acordingly.
2462 formula:
2463 3. new header amount := sum of all line amount (rounding issue may happen)
2464 (Assume the processing fee amount < invoice amount.)
2465 4. Assume processing fee is only applicable for ""Standard"" invoice.
2466
2467 end if;
2468 end if;
2469
2470 */
2471
2472 FOR r_cnsld_hdr IN c_cnsld_hdr LOOP
2473
2474 /* OPEN c_no_adv_grp(r_cnsld_hdr.cnsld_ap_inv_id); */
2475 /* FETCH c_no_adv_grp INTO r_no_adv_grp; */
2476 /* l_row_found := c_no_adv_grp%FOUND; */
2477 /* CLOSE c_no_adv_grp; */
2478 /* */
2479 /* IF NOT l_row_found THEN */
2480 /* */
2481 /* -- 1. Get Disbursement rule for each consoldiation invoice (need to resolve multiple rules issue) */
2482 /* l_disb_rules := get_Disbursement_term( */
2483 /* p_transaction_date => r_cnsld_hdr.date_invoiced, --p_transaction_date, */
2484 /* p_vendor_id => r_cnsld_hdr.vendor_id, */
2485 /* p_vendor_site_id => r_cnsld_hdr.ipvs_id, */
2486 /* p_stream_type_purpose => p_stream_type_purpose, --? */
2487 /* x_return_status => lx_return_status, */
2488 /* x_rule_found => lx_rule_found); */
2489 /* */
2490 /* IF lx_return_status = G_RET_STS_SUCCESS AND lx_rule_found = TRUE THEN */
2491 /* */
2492 /* END IF; -- if rule found */
2493 /* END IF; --IF p_adv_grouping_flag = 'Y' THEN */
2494
2495 -- get_disb_rule_options
2496 -- IF G_ACC_SYS_OPTION = 'ATS' THEN -- Account Template Set
2497 -- handle_ATS_per_invoice
2498 -- ELSE
2499 -- handle_AMB_per_invoice
2500 -- END IF;
2501 null;
2502 END LOOP; --> Header
2503
2504 COMMIT;
2505
2506 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2507 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* End Processing Records ******* ');
2508 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2509
2510 ------------------------------------------------------------
2511 -- End processing
2512 ------------------------------------------------------------
2513
2514 Okl_Api.END_ACTIVITY (
2515 x_msg_count => x_msg_count,
2516 x_msg_data => x_msg_data);
2517
2518 EXCEPTION
2519 ------------------------------------------------------------
2520 -- Exception handling
2521 ------------------------------------------------------------
2522 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2523 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2524 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2525 p_api_name => l_api_name,
2526 p_pkg_name => G_PKG_NAME,
2527 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2528 x_msg_count => x_msg_count,
2529 x_msg_data => x_msg_data,
2530 p_api_type => '_PVT');
2531
2532 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2533 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2534 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2535 p_api_name => l_api_name,
2536 p_pkg_name => G_PKG_NAME,
2537 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2538 x_msg_count => x_msg_count,
2539 x_msg_data => x_msg_data,
2540 p_api_type => '_PVT');
2541
2542 WHEN OTHERS THEN
2543 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2544 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2545 p_api_name => l_api_name,
2546 p_pkg_name => G_PKG_NAME,
2547 p_exc_name => 'OTHERS',
2548 x_msg_count => x_msg_count,
2549 x_msg_data => x_msg_data,
2550 p_api_type => '_PVT');
2551 END handle_processing_fee;
2552
2553 --end: 31-Oct-2007 cklee -- bug: 6508575 fixed
2554
2555 -- End of wraper code generated automatically by Debug code generator
2556
2557 -- Start of Comments
2558 -- API name : Transfer_to_External
2559 -- Pre-reqs : None
2560 -- Function :
2561 -- Parameters :
2562 -- IN :p_api_version IN NUMBER Required
2563 -- p_init_msg_list IN VARCHAR2 Required
2564 -- p_from_date IN DATE Required
2565 -- p_to_date IN DATE Required
2566 -- Version : 1.0
2567 -- History : RKUTTIYA 01/26/07 Created
2568 -- End of Comments
2569 PROCEDURE transfer_to_external(p_api_version IN NUMBER
2570 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
2571 ,x_return_status OUT NOCOPY VARCHAR2
2572 ,x_msg_count OUT NOCOPY NUMBER
2573 ,x_msg_data OUT NOCOPY VARCHAR2)
2574 --start: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2575 -- ,p_from_date IN DATE
2576 -- ,p_to_date IN DATE)
2577 --end: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2578 IS
2579 ------------------------------------------------------------
2580 -- Declare Process variables
2581 ------------------------------------------------------------
2582 l_api_version CONSTANT NUMBER := 1;
2583 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATION';
2584 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2585 l_msg_index_out NUMBER;
2586 i NUMBER;
2587
2588 --Cursor for selecting the Consolidated invoice header attributes
2589 -- CURSOR c_cnsld_hdr(p_date_from IN DATE,
2590 -- p_to_date IN DATE) IS
2591 CURSOR c_cnsld_hdr IS
2592 SELECT *
2593 FROM OKL_CNSLD_AP_INVS_ALL
2594 WHERE trx_status_code = 'ENTERED';
2595 --start: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2596 -- AND date_invoiced BETWEEN
2597 -- p_from_date AND p_to_date;
2598 --end: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2599
2600 --Cursor for selecting the Consolidated Invoice lines
2601 CURSOR c_cnsld_lns(p_cnsld_inv_id IN NUMBER) IS
2602 SELECT TPL.ID,
2603 TPL.cnsld_line_number,
2604 TPL.inv_distr_line_code,
2605 TPL.org_id,
2606 TPL.ATTRIBUTE_CATEGORY,
2607 TPL.ATTRIBUTE1,
2608 TPL.ATTRIBUTE2,
2609 TPL.ATTRIBUTE3,
2610 TPL.ATTRIBUTE4,
2611 TPL.ATTRIBUTE5,
2612 TPL.ATTRIBUTE6,
2613 TPL.ATTRIBUTE7,
2614 TPL.ATTRIBUTE8,
2615 TPL.ATTRIBUTE9,
2616 TPL.ATTRIBUTE10,
2617 TPL.ATTRIBUTE11,
2618 TPL.ATTRIBUTE12,
2619 TPL.ATTRIBUTE13,
2620 TPL.ATTRIBUTE14,
2621 TPL.ATTRIBUTE15,
2622 DSTRS.AMOUNT,
2623 DSTRS.CODE_COMBINATION_ID,
2624 DSTRS.CR_DR_FLAG
2625 FROM OKL_TXL_AP_INV_LNS_B TPL,
2626 OKL_TRNS_ACC_DSTRS DSTRS
2627 WHERE TPL.CNSLD_AP_INV_ID = p_cnsld_inv_id
2628 AND TPL.ID = DSTRS.SOURCE_ID
2629 AND DSTRS.SOURCE_TABLE = 'OKL_TXL_AP_INV_LNS_B';
2630
2631
2632 -----------------------------------------------------------------
2633 -- Fetch Ap Interface Sequence Number
2634 -----------------------------------------------------------------
2635 CURSOR seq_csr IS
2636 SELECT ap_invoices_interface_s.nextval
2637 FROM dual;
2638
2639 ------------------------------------------------------------
2640 -- Declare records: Payable Invoice Headers and Lines
2641 ------------------------------------------------------------
2642 l_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
2643
2644 -- Nulling record
2645 l_init_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
2646
2647 lx_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
2648 l_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
2649
2650 -- Nulling record
2651 l_init_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
2652
2653 lx_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
2654
2655 /* l_taiv_rec okl_tai_pvt.taiv_rec_type; */
2656 /* lx_taiv_rec okl_tai_pvt.taiv_rec_type; */
2657 /* -- Nulling record */
2658 /* l_init_taiv_rec okl_tai_pvt.taiv_rec_type; */
2659 /* */
2660 /* l_tilv_rec okl_til_pvt.tilv_rec_type; */
2661 /* lx_tilv_rec okl_til_pvt.tilv_rec_type; */
2662 /* -- Nulling record */
2663 /* l_init_tilv_rec okl_til_pvt.tilv_rec_type; */
2664 --
2665
2666 l_gen_seq okl_trx_ap_invoices_v.invoice_number%TYPE;
2667 l_okl_application_id NUMBER(3) := 540;
2668 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
2669 lX_dbseqnm VARCHAR2(2000):= '';
2670 lX_dbseqid NUMBER(38):= NULL;
2671
2672
2673 l_conc_status VARCHAR2(1) := 'S';
2674
2675 l_commit_cnt NUMBER;
2676 l_MAX_commit_cnt NUMBER := 500;
2677
2678
2679 BEGIN
2680
2681 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2682
2683 l_return_status := OKL_API.START_ACTIVITY(
2684 p_api_name => l_api_name,
2685 p_pkg_name => g_pkg_name,
2686 p_init_msg_list => p_init_msg_list,
2687 l_api_version => l_api_version,
2688 p_api_version => p_api_version,
2689 p_api_type => '_PVT',
2690 x_return_status => l_return_status);
2691
2692 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2693 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2694 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2695 RAISE OKL_API.G_EXCEPTION_ERROR;
2696 END IF;
2697
2698
2699 --start: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2700 -- FOR r_cnsld_hdr IN c_cnsld_hdr(p_from_date, p_to_date) LOOP
2701 FOR r_cnsld_hdr IN c_cnsld_hdr LOOP
2702 --end: 24-APR-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2703 l_xpiv_rec := l_init_xpiv_rec;
2704 l_xpiv_rec.vendor_invoice_number := r_cnsld_hdr.vendor_invoice_number;
2705 l_xpiv_rec.invoice_num := r_cnsld_hdr.invoice_number;
2706
2707
2708 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2709 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Vendor Invoice Number: '||r_cnsld_hdr.vendor_invoice_number);
2710 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2711
2712 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2713 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Set of Books: '||r_cnsld_hdr.set_of_books_id||' Org Id: '||r_cnsld_hdr.org_id||' Invoice Number: '||r_cnsld_hdr.invoice_number );
2714 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Vendor Site (ipvs_id): '||r_cnsld_hdr.ipvs_id||' Terms Id(ippt_id): '||r_cnsld_hdr.ippt_id||' Payment Method Code: '||r_cnsld_hdr.payment_method_code );
2715 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Currency: '||r_cnsld_hdr.currency_code||' GL Date: '||r_cnsld_hdr.date_gl );
2716 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ try_id: '||r_cnsld_hdr.try_id);
2717 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ ccid: '||r_cnsld_hdr.accts_pay_cc_id);
2718 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ invoice_amount: '||r_cnsld_hdr.amount||' Vendor Id: '||r_cnsld_hdr.vendor_id);
2719 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2720
2721
2722
2723 OPEN seq_csr;
2724 FETCH seq_csr INTO l_xpiv_rec.invoice_id;
2725 CLOSE seq_csr;
2726
2727 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Invoice id: '||l_xpiv_rec.invoice_id);
2728
2729 l_xpiv_rec.invoice_type := r_cnsld_hdr.invoice_type;
2730 l_xpiv_rec.invoice_date := r_cnsld_hdr.date_invoiced;
2731 l_xpiv_rec.vendor_id := r_cnsld_hdr.vendor_id;
2732 l_xpiv_rec.vendor_site_id := r_cnsld_hdr.ipvs_id;
2733 l_xpiv_rec.invoice_amount := r_cnsld_hdr.amount;
2734 l_xpiv_rec.invoice_currency_code := r_cnsld_hdr.currency_code;
2735 l_xpiv_rec.terms_id := r_cnsld_hdr.ippt_id;
2736 l_xpiv_rec.workflow_flag := NULL;
2737 l_xpiv_rec.pay_group_lookup_code := r_cnsld_hdr.pay_group_lookup_code;
2738 l_xpiv_rec.doc_category_code := NULL;
2739 l_xpiv_rec.payment_method := r_cnsld_hdr.payment_method_code;
2740 l_xpiv_rec.gl_date := r_cnsld_hdr.date_gl;
2741 l_xpiv_rec.accts_pay_cc_id := r_cnsld_hdr.accts_pay_cc_id;
2742 --start:| 03-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2743 -- l_xpiv_rec.nettable_yn := NULL;
2744 l_xpiv_rec.nettable_yn := 'N';
2745 --end:| 03-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2746 l_xpiv_rec.pay_alone_flag := NULL;
2747 l_xpiv_rec.wait_vendor_invoice_yn := NULL;
2748 l_xpiv_rec.payables_invoice_id := NULL;
2749 l_xpiv_rec.org_id := r_cnsld_hdr.org_id;
2750 l_xpiv_rec.attribute_category := r_cnsld_hdr.attribute_category;
2751 l_xpiv_rec.attribute1 := r_cnsld_hdr.attribute1;
2752 l_xpiv_rec.attribute2 := r_cnsld_hdr.attribute2;
2753 l_xpiv_rec.attribute3 := r_cnsld_hdr.attribute3;
2754 l_xpiv_rec.attribute4 := r_cnsld_hdr.attribute4;
2755 l_xpiv_rec.attribute5 := r_cnsld_hdr.attribute5;
2756 l_xpiv_rec.attribute6 := r_cnsld_hdr.attribute6;
2757 l_xpiv_rec.attribute7 := r_cnsld_hdr.attribute7;
2758 l_xpiv_rec.attribute8 := r_cnsld_hdr.attribute8;
2759 l_xpiv_rec.attribute9 := r_cnsld_hdr.attribute9;
2760 l_xpiv_rec.attribute10 := r_cnsld_hdr.attribute10;
2761 l_xpiv_rec.attribute11 := r_cnsld_hdr.attribute11;
2762 l_xpiv_rec.attribute12 := r_cnsld_hdr.attribute12;
2763 l_xpiv_rec.attribute13 := r_cnsld_hdr.attribute13;
2764 l_xpiv_rec.attribute14 := r_cnsld_hdr.attribute14;
2765 l_xpiv_rec.attribute15 := r_cnsld_hdr.attribute15;
2766 l_xpiv_rec.trx_status_code := 'ENTERED';
2767 l_xpiv_rec.currency_conversion_rate := r_cnsld_hdr.currency_conversion_rate;
2768 l_xpiv_rec.currency_conversion_type := r_cnsld_hdr.currency_conversion_type;
2769 l_xpiv_rec.currency_conversion_date := r_cnsld_hdr.currency_conversion_date;
2770 l_xpiv_rec.legal_entity_id := r_cnsld_hdr.legal_entity_id;
2771 l_xpiv_rec.cnsld_ap_inv_id := r_cnsld_hdr.cnsld_ap_inv_id;
2772 l_xpiv_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
2773
2774
2775 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Header..');
2776
2777 -- Start of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
2778 IF(L_DEBUG_ENABLED='Y') THEN
2779 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2780 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2781 END IF;
2782 IF(IS_DEBUG_PROCEDURE_ON) THEN
2783 BEGIN
2784 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
2785 END;
2786 END IF;
2787
2788
2789 OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
2790 p_api_version => p_api_version
2791 ,p_init_msg_list => p_init_msg_list
2792 ,x_return_status => x_return_status
2793 ,x_msg_count => x_msg_count
2794 ,x_msg_data => x_msg_data
2795 ,p_xpiv_rec => l_xpiv_rec
2796 ,x_xpiv_rec => lx_xpiv_rec);
2797
2798 IF(IS_DEBUG_PROCEDURE_ON) THEN
2799 BEGIN
2800 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs '|| x_return_status);
2801 END;
2802 END IF;
2803 -- End of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
2804 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2805 l_conc_status := 'E';
2806 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
2807 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2808 l_conc_status := 'E';
2809 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
2810 ELSIF (x_return_status ='S') THEN
2811 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Header with Id: '||lx_xpiv_rec.id);
2812 END IF;
2813
2814 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Lines..');
2815 --Processing the Consolidated lines for transfer to external lines table
2816 FOR r_cnsld_lns IN c_cnsld_lns(r_cnsld_hdr.cnsld_ap_inv_id) LOOP
2817 l_commit_cnt := l_commit_cnt + 1;
2818 IF nvl(r_cnsld_lns.CR_DR_FLAG, 'D') = 'D' THEN
2819 l_xlpv_rec.xpi_id_details := lx_xpiv_rec.id;
2820 l_xlpv_rec.pid_id := NULL;
2821 l_xlpv_rec.ibi_id := NULL;
2822 l_xlpv_rec.tpl_id := r_cnsld_lns.id;
2823 l_xlpv_rec.tap_id := NULL;
2824 l_xlpv_rec.invoice_line_id := NULL;
2825 l_xlpv_rec.line_number := r_cnsld_lns.cnsld_line_number;
2826
2827 --validate line type and set it to ITEM if invoice distr line type is any of the below
2828 IF r_cnsld_lns.inv_distr_line_code in ('ITEM','INVESTOR','MANUAL','AUTO_DISBURSEMENT',NULL) THEN
2829 l_xlpv_rec.line_type := 'ITEM';
2830 ELSE
2831 l_xlpv_rec.line_type := r_cnsld_lns.inv_distr_line_code;
2832 END IF;
2833
2834 l_xlpv_rec.amount := r_cnsld_lns.amount;
2835 l_xlpv_rec.accounting_date := r_cnsld_hdr.date_gl;
2836
2837 l_xlpv_rec.dist_code_combination_id := r_cnsld_lns.code_combination_id;
2838 --start:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2839 /*
2840 IF G_ACC_SYS_OPTION = 'ATS' THEN -- Account Template Set
2841 l_xlpv_rec.dist_code_combination_id := r_cnsld_lns.code_combination_id;
2842 ELSE -- Account Method Builder(AMB),
2843 l_xlpv_rec.dist_code_combination_id := -1;
2844 END IF;
2845 */
2846 -- l_xlpv_rec.dist_code_combination_id := r_cnsld_lns.code_combination_id;
2847 --end:| 01-MAY-2007 cklee -- Disbursement changes for R12B, bug fixed: |
2848 l_xlpv_rec.tax_code := NULL;
2849 l_xlpv_rec.org_id := r_cnsld_lns.org_id;
2850 l_xlpv_rec.attribute_category := r_cnsld_lns.attribute_category;
2851 l_xlpv_rec.attribute1 := r_cnsld_lns.attribute1;
2852 l_xlpv_rec.attribute2 := r_cnsld_lns.attribute2;
2853 l_xlpv_rec.attribute3 := r_cnsld_lns.attribute3;
2854 l_xlpv_rec.attribute4 := r_cnsld_lns.attribute4;
2855 l_xlpv_rec.attribute5 := r_cnsld_lns.attribute5;
2856 l_xlpv_rec.attribute6 := r_cnsld_lns.attribute6;
2857 l_xlpv_rec.attribute7 := r_cnsld_lns.attribute7;
2858 l_xlpv_rec.attribute8 := r_cnsld_lns.attribute8;
2859 l_xlpv_rec.attribute9 := r_cnsld_lns.attribute9;
2860 l_xlpv_rec.attribute10 := r_cnsld_lns.attribute10;
2861 l_xlpv_rec.attribute11 := r_cnsld_lns.attribute11;
2862 l_xlpv_rec.attribute12 := r_cnsld_lns.attribute12;
2863 l_xlpv_rec.attribute13 := r_cnsld_lns.attribute13;
2864 l_xlpv_rec.attribute14 := r_cnsld_lns.attribute14;
2865 l_xlpv_rec.attribute15 := r_cnsld_lns.attribute15;
2866 l_xlpv_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
2867
2868
2869 -- Start of wraper code generated automatically by Debug code generator for okl_xtl_pay_invs_pub.insert_xtl_pay_invs
2870 IF(IS_DEBUG_PROCEDURE_ON) THEN
2871 BEGIN
2872 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
2873 END;
2874 END IF;
2875
2876 OKL_XTL_PAY_INVS_PUB.insert_xtl_pay_invs(
2877 p_api_version => p_api_version
2878 ,p_init_msg_list => p_init_msg_list
2879 ,x_return_status => x_return_status
2880 ,x_msg_count => x_msg_count
2881 ,x_msg_data => x_msg_data
2882 ,p_xlpv_rec => l_xlpv_rec
2883 ,x_xlpv_rec => lx_xlpv_rec);
2884
2885 IF(IS_DEBUG_PROCEDURE_ON) THEN
2886 BEGIN
2887 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs '||x_return_status);
2888 END;
2889 END IF;
2890 -- End of wraper code generated automatically by Debug code generator for okl_xtl_pay_invs_pub.insert_xtl_pay_invs
2891
2892 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2893 l_conc_status := 'E';
2894 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
2895 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2896 l_conc_status := 'E';
2897 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
2898 ELSIF (x_return_status ='S') THEN
2899 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Line with Id: '||lx_xlpv_rec.id);
2900 END IF;
2901
2902 IF l_commit_cnt > l_MAX_commit_cnt THEN
2903 COMMIT;
2904 l_commit_cnt := 0;
2905 END IF;
2906 END IF; --> Debit check
2907 END LOOP; -- > Lines
2908
2909 IF ( NVL(l_conc_status, 'E') = okl_api.g_ret_sts_success ) THEN
2910 -----------------------------------------------------------------
2911 -- Update internal AP table for Success
2912 -----------------------------------------------------------------
2913 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || r_cnsld_hdr.invoice_number);
2914
2915 UPDATE OKL_CNSLD_AP_INVS
2916 SET TRX_STATUS_CODE = 'PROCESSED'
2917 WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
2918 ELSE
2919 -----------------------------------------------------------------
2920 -- Update internal AP table for Error
2921 -----------------------------------------------------------------
2922 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || r_cnsld_hdr.invoice_number);
2923 IF x_msg_count >= 1 THEN
2924 FOR i in 1..x_msg_count LOOP
2925 fnd_msg_pub.get (p_msg_index => i,
2926 p_encoded => 'F',
2927 p_data => x_msg_data,
2928 p_msg_index_out => l_msg_index_out);
2929
2930 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,to_char(i) || ': ' || x_msg_data);
2931
2932 END LOOP;
2933 END IF;
2934 --start: cklee 4/26/07
2935 UPDATE OKL_CNSLD_AP_INVS
2936 SET TRX_STATUS_CODE = 'ERROR'
2937 WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
2938
2939 UPDATE OKL_EXT_PAY_INVS_ALL_B
2940 SET TRX_STATUS_CODE = 'ERROR'
2941 WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
2942 --end: cklee 4/26/07
2943
2944 END IF; --> Status Check
2945 END LOOP; --> Header
2946
2947 COMMIT;
2948
2949 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2950 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* End Processing Records ******* ');
2951 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
2952
2953 ------------------------------------------------------------
2954 -- End processing
2955 ------------------------------------------------------------
2956
2957 Okl_Api.END_ACTIVITY (
2958 x_msg_count => x_msg_count,
2959 x_msg_data => x_msg_data);
2960
2961 EXCEPTION
2962 ------------------------------------------------------------
2963 -- Exception handling
2964 ------------------------------------------------------------
2965 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2966 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2967 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2968 p_api_name => l_api_name,
2969 p_pkg_name => G_PKG_NAME,
2970 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2971 x_msg_count => x_msg_count,
2972 x_msg_data => x_msg_data,
2973 p_api_type => '_PVT');
2974
2975 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2976 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2977 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2978 p_api_name => l_api_name,
2979 p_pkg_name => G_PKG_NAME,
2980 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2981 x_msg_count => x_msg_count,
2982 x_msg_data => x_msg_data,
2983 p_api_type => '_PVT');
2984
2985 WHEN OTHERS THEN
2986 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
2987 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2988 p_api_name => l_api_name,
2989 p_pkg_name => G_PKG_NAME,
2990 p_exc_name => 'OTHERS',
2991 x_msg_count => x_msg_count,
2992 x_msg_data => x_msg_data,
2993 p_api_type => '_PVT');
2994 END transfer_to_external;
2995
2996
2997 PROCEDURE consolidation_11i(p_api_version IN NUMBER
2998 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
2999 ,x_return_status OUT NOCOPY VARCHAR2
3000 ,x_msg_count OUT NOCOPY NUMBER
3001 ,x_msg_data OUT NOCOPY VARCHAR2
3002 ,p_from_date IN DATE
3003 ,p_to_date IN DATE)
3004 IS
3005 ------------------------------------------------------------
3006 -- Declare variables required by APIs
3007 ------------------------------------------------------------
3008 l_api_version CONSTANT NUMBER := 1;
3009 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATION';
3010 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3011 ------------------------------------------------------------
3012 -- Declare Process variables
3013 ------------------------------------------------------------
3014 l_msg_index_out NUMBER;
3015 i NUMBER;
3016 -----------------------------------------------------------------
3017 -- Extract all Invoices due for submission
3018 -----------------------------------------------------------------
3019
3020 CURSOR c_invoice IS
3021 SELECT
3022 tap.id tap_id
3023 ,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
3024 ,tap.org_id
3025 ,tap.invoice_category_code
3026 ,tap.invoice_number
3027 --,tap.vendor_invoice_number
3028 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
3029 ,tap.object_version_number
3030 ,tap.code_combination_id tap_ccid
3031 ,tap.date_invoiced date_invoiced
3032 ,tap.pay_group_lookup_code
3033 ,tap.ipvs_id
3034 ,tap.ippt_id
3035 ,tap.payment_method_code
3036 ,tap.currency_code
3037 ,tap.currency_conversion_type
3038 ,tap.currency_conversion_rate
3039 ,tap.currency_conversion_date
3040 ,tap.workflow_yn
3041 ,tap.date_gl gl_date
3042 ,tap.nettable_yn
3043 ,tap.khr_id
3044 ,tap.wait_vendor_invoice_yn
3045 ,tap.try_id
3046 ,tpl.id tpl_id
3047 ,tpl.code_combination_id tpl_ccid
3048 ,tpl.sty_id
3049 ,acc_db.code_combination_id db_ccid
3050 ,acc_cr.code_combination_id cr_ccid
3051 ,tpl.amount invoice_amount
3052 ,acc_db.amount line_amount
3053 ,povs.vendor_id
3054 FROM
3055 okl_trns_acc_dstrs acc_db
3056 ,okl_trns_acc_dstrs acc_cr
3057 ,po_vendor_sites_all povs
3058 ,okl_txl_ap_inv_lns_b tpl
3059 ,okl_trx_ap_invoices_b tap
3060 WHERE
3061 NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
3062 trunc(tap.date_invoiced) BETWEEN
3063 NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
3064 AND acc_db.cr_dr_flag (+) = 'D'
3065 AND acc_cr.cr_dr_flag (+) = 'C'
3066 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
3067 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
3068 AND tpl.id = acc_db.source_id (+)
3069 AND tpl.id = acc_cr.source_id (+)
3070 AND povs.vendor_site_id = tap.ipvs_id
3071 AND tap.id = tpl.tap_id
3072 AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
3073 AND tap.FUNDING_TYPE_CODE IS NULL
3074 ORDER BY tap.id;
3075
3076
3077 CURSOR c_funding_csr IS
3078 SELECT * FROM (
3079 SELECT
3080 -- NULL tpl_id -- cklee comment out
3081 tap.id tap_id
3082 ,NVL(tap.set_of_books_id,
3083 OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
3084 ,tap.org_id
3085 ,tap.invoice_category_code
3086 ,tap.invoice_number
3087 --,tap.vendor_invoice_number
3088 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
3089 ,tap.object_version_number
3090 ,tap.code_combination_id tap_ccid
3091 ,tap.date_invoiced date_invoiced
3092 ,tap.pay_group_lookup_code
3093 ,tap.ipvs_id
3094 ,tap.ippt_id
3095 ,tap.payment_method_code
3096 ,tap.currency_code
3097 ,tap.currency_conversion_type
3098 ,tap.currency_conversion_rate
3099 ,tap.currency_conversion_date
3100 ,tap.workflow_yn
3101 ,nvl(tap.date_gl,tap.date_invoiced) gl_date
3102 ,tap.nettable_yn
3103 ,tap.khr_id
3104 ,tap.wait_vendor_invoice_yn
3105 ,tap.try_id
3106 ,acc_db.code_combination_id db_ccid
3107 ,acc_cr.code_combination_id cr_ccid
3108 ,tap.amount invoice_amount
3109 ,acc_db.amount line_amount
3110 ,povs.vendor_id
3111 -- ,tap.FUNDING_TYPE_CODE -cklee
3112 FROM
3113 okl_trns_acc_dstrs acc_db
3114 ,okl_trns_acc_dstrs acc_cr
3115 ,po_vendor_sites_all povs
3116 ,okl_trx_ap_invoices_b tap
3117 WHERE
3118 NVL(tap.trx_status_code, 'ENTERED') in ( 'APPROVED' ) AND
3119 trunc(tap.date_invoiced) BETWEEN
3120 NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date,SYSDATE+10000)
3121 AND acc_db.cr_dr_flag (+) = 'D'
3122 AND acc_cr.cr_dr_flag (+) = 'C'
3123 AND acc_db.source_table (+)= 'OKL_TRX_AP_INVOICES_B'
3124 AND acc_cr.source_table (+)= 'OKL_TRX_AP_INVOICES_B'
3125 AND tap.id = acc_db.source_id (+)
3126 AND tap.id = acc_cr.source_id (+)
3127 AND povs.vendor_site_id = tap.ipvs_id
3128 AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
3129 AND tap.FUNDING_TYPE_CODE IS NOT NULL
3130 --AND tap.FUNDING_TYPE_CODE <> 'ASSET_SUBSIDY'
3131 AND tap.FUNDING_TYPE_CODE not in ('ASSET_SUBSIDY', 'MANUAL_DISB')
3132 UNION
3133 SELECT
3134 -- tpl.id tpl_id -- cklee comment out
3135 tap.id tap_id
3136 ,NVL(tap.set_of_books_id,OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
3137 ,tap.org_id
3138 ,tap.invoice_category_code
3139 ,tap.invoice_number
3140 --,tap.vendor_invoice_number
3141 ,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
3142 ,tap.object_version_number
3143 ,tap.code_combination_id tap_ccid
3144 ,tap.date_invoiced date_invoiced
3145 ,tap.pay_group_lookup_code
3146 ,tap.ipvs_id
3147 ,tap.ippt_id
3148 ,tap.payment_method_code
3149 ,tap.currency_code
3150 ,tap.currency_conversion_type
3151 ,tap.currency_conversion_rate
3152 ,tap.currency_conversion_date
3153 ,tap.workflow_yn
3154 ,nvl(tap.date_gl,tap.date_invoiced) gl_date
3155 ,tap.nettable_yn
3156 ,tap.khr_id
3157 ,tap.wait_vendor_invoice_yn
3158 ,tap.try_id
3159 ,acc_db.code_combination_id db_ccid
3160 ,acc_cr.code_combination_id cr_ccid
3161 ,tap.amount invoice_amount
3162 ,acc_db.amount line_amount
3163 ,povs.vendor_id
3164 -- ,tap.FUNDING_TYPE_CODE -- cklee
3165 FROM
3166 okl_trns_acc_dstrs acc_db
3167 ,okl_trns_acc_dstrs acc_cr
3168 ,po_vendor_sites_all povs
3169 ,okl_trx_ap_invoices_b tap
3170 ,okl_txl_ap_inv_lns_b tpl
3171 WHERE
3172 NVL(tap.trx_status_code, 'ENTERED') in ( 'APPROVED' )
3173 AND trunc(tap.date_invoiced) BETWEEN
3174 NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
3175 AND acc_db.cr_dr_flag (+) = 'D'
3176 AND acc_cr.cr_dr_flag (+) = 'C'
3177 AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
3178 AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
3179 AND tpl.id = acc_db.source_id (+)
3180 AND tpl.id = acc_cr.source_id (+)
3181 AND povs.vendor_site_id = tap.ipvs_id
3182 AND tap.id = tpl.tap_id
3183 AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
3184 --AND tap.FUNDING_TYPE_CODE = 'ASSET_SUBSIDY' ;
3185 AND tap.FUNDING_TYPE_CODE in ('ASSET_SUBSIDY', 'MANUAL_DISB')
3186 ) ORDER BY tap_id;
3187
3188
3189
3190 -----------------------------------------------------------------
3191 -- Fetch Ap Interface Sequence Number
3192 -----------------------------------------------------------------
3193 CURSOR seq_csr IS
3194 SELECT ap_invoices_interface_s.nextval
3195 FROM dual;
3196
3197 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
3198
3199 ------------------------------------------------------------
3200 -- Declare records: Payable Invoice Headers and Lines
3201 ------------------------------------------------------------
3202 l_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
3203
3204 -- Nulling record
3205 l_init_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
3206
3207 lx_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
3208 l_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
3209
3210 -- Nulling record
3211 l_init_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
3212
3213 lx_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
3214
3215 l_taiv_rec okl_tai_pvt.taiv_rec_type;
3216 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
3217 -- Nulling record
3218 l_init_taiv_rec okl_tai_pvt.taiv_rec_type;
3219
3220 l_tilv_rec okl_til_pvt.tilv_rec_type;
3221 lx_tilv_rec okl_til_pvt.tilv_rec_type;
3222 -- Nulling record
3223 l_init_tilv_rec okl_til_pvt.tilv_rec_type;
3224
3225 -- Multi Currency Compliance
3226 l_currency_code okl_ext_sell_invs_b.currency_code%type;
3227 l_currency_conversion_type okl_ext_sell_invs_b.currency_conversion_type%type;
3228 l_currency_conversion_rate okl_ext_sell_invs_b.currency_conversion_rate%type;
3229 l_currency_conversion_date okl_ext_sell_invs_b.currency_conversion_date%type;
3230
3231 l_previous_tap_id okl_trx_ap_invoices_b.id%TYPE := NULL;
3232 l_previous_xpi_id okl_ext_sell_invs_b.id%TYPE := NULL;
3233
3234 CURSOR l_curr_conv_csr( cp_khr_id NUMBER ) IS
3235 SELECT currency_code
3236 ,currency_conversion_type
3237 ,currency_conversion_rate
3238 ,currency_conversion_date
3239 FROM okl_k_headers_full_v
3240 WHERE id = cp_khr_id;
3241
3242 -----------------------------------------------------------
3243 -- Variables for Bug 2949640, Sunil Mathew, 12-May-2003
3244 -----------------------------------------------------------
3245 l_gen_seq okl_trx_ap_invoices_v.invoice_number%TYPE;
3246 l_okl_application_id NUMBER(3) := 540;
3247 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
3248 lX_dbseqnm VARCHAR2(2000):= '';
3249 lX_dbseqid NUMBER(38):= NULL;
3250
3251
3252 l_conc_status VARCHAR2(1);
3253
3254 l_commit_cnt NUMBER;
3255 l_MAX_commit_cnt NUMBER := 500;
3256
3257 BEGIN
3258 ------------------------------------------------------------
3259 -- Start processing
3260 ------------------------------------------------------------
3261
3262 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3263
3264 l_return_status := OKL_API.START_ACTIVITY(
3265 p_api_name => l_api_name,
3266 p_pkg_name => g_pkg_name,
3267 p_init_msg_list => p_init_msg_list,
3268 l_api_version => l_api_version,
3269 p_api_version => p_api_version,
3270 p_api_type => '_PVT',
3271 x_return_status => l_return_status);
3272
3273 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3275 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3276 RAISE OKL_API.G_EXCEPTION_ERROR;
3277 END IF;
3278
3279 -----------------------------------------------------------------
3280 -- Open Invoice cursor in block
3281 -----------------------------------------------------------------
3282
3283 l_commit_cnt := 0;
3284
3285 FOR r_invoice IN c_invoice LOOP
3286 IF (NVL(l_previous_tap_id, -99) <> r_invoice.tap_id) THEN
3287 l_commit_cnt := l_commit_cnt + 1;
3288
3289 l_conc_status := 'S';
3290
3291 -- Null records
3292 l_xpiv_rec := l_init_xpiv_rec;
3293 l_xlpv_rec := l_init_xlpv_rec;
3294 l_taiv_rec := l_init_taiv_rec;
3295 l_tilv_rec := l_init_tilv_rec;
3296
3297 ------------------------------------------------------------
3298 -- Generate sequence while consolidating, Bug 2949640
3299 -- Sunil Mathew, 12-May-2003
3300 ------------------------------------------------------------
3301 l_gen_seq := NULL;
3302 IF r_invoice.invoice_number IS NULL THEN
3303 BEGIN
3304 l_gen_seq := fnd_seqnum.get_next_sequence
3305 (appid => l_okl_application_id,
3306 cat_code => l_document_category,
3307 sobid => r_invoice.sob_id,
3308 met_code => 'A',
3309 trx_date => SYSDATE,
3310 dbseqnm => lx_dbseqnm,
3311 dbseqid => lx_dbseqid);
3312 EXCEPTION
3313 WHEN OTHERS THEN
3314 l_gen_seq := -1;
3315 END;
3316
3317 END IF;
3318
3319 r_invoice.vendor_invoice_number := NVL(r_invoice.vendor_invoice_number,l_gen_seq);
3320 r_invoice.invoice_number := NVL(r_invoice.invoice_number,l_gen_seq);
3321
3322 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3323 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Vendor Invoice Number: '||r_invoice.vendor_invoice_number);
3324 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3325
3326 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
3327 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Set of Books: '||r_invoice.sob_id||' Org Id: '||r_invoice.org_id||' Invoice Number: '||r_invoice.invoice_number );
3328 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Vendor Site (ipvs_id): '||r_invoice.ipvs_id||' Terms Id(ippt_id): '||r_invoice.ippt_id||' Payment Method Code: '||r_invoice.payment_method_code );
3329 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Currency: '||r_invoice.currency_code||' GL Date: '||r_invoice.gl_date||' Nettable: '||r_invoice.nettable_yn );
3330 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ khr_id: '||r_invoice.khr_id||' try_id: '||r_invoice.try_id);
3331 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ db_ccid: '||r_invoice.db_ccid||' cr_ccid: '||r_invoice.cr_ccid);
3332 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ invoice_amount: '||r_invoice.invoice_amount||' Vendor Id: '||r_invoice.vendor_id);
3333 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
3334 END IF;
3335
3336 -- SAVEPOINT C_INVOICE_POINT;
3337
3338 IF NVL(r_invoice.nettable_yn, 'N') = 'N' OR r_invoice.invoice_amount > 0 THEN
3339 IF (NVL(l_previous_tap_id, -99) <> r_invoice.tap_id) THEN
3340 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Creating Payables Invoice');
3341
3342 -----------------------------------------------------------------
3343 -- Populate and insert external AP invoice header
3344 -----------------------------------------------------------------
3345 l_xpiv_rec.invoice_id := NULL;
3346
3347 OPEN seq_csr;
3348 FETCH seq_csr INTO l_xpiv_rec.invoice_id;
3349 CLOSE seq_csr;
3350
3351 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Invoice id: '||l_xpiv_rec.invoice_id);
3352
3353 l_xpiv_rec.doc_category_code := r_invoice.invoice_category_code;
3354 l_xpiv_rec.invoice_num := r_invoice.invoice_number;
3355 l_xpiv_rec.vendor_invoice_number:= r_invoice.vendor_invoice_number;
3356 l_xpiv_rec.gl_date := r_invoice.gl_date;
3357 l_xpiv_rec.invoice_amount := r_invoice.invoice_amount;
3358 l_xpiv_rec.pay_group_lookup_code:= r_invoice.pay_group_lookup_code;
3359 l_xpiv_rec.nettable_yn := r_invoice.nettable_yn;
3360
3361 l_xpiv_rec.accts_pay_cc_id := NVL(r_invoice.tap_ccid,r_invoice.cr_ccid);
3362 l_xpiv_rec.invoice_currency_code:= r_invoice.currency_code;
3363 l_xpiv_rec.invoice_date := r_invoice.date_invoiced;
3364 l_xpiv_rec.object_version_number:= r_invoice.object_version_number;
3365 l_xpiv_rec.org_id := r_invoice.org_id;
3366 l_xpiv_rec.payment_method := r_invoice.payment_method_code;
3367 l_xpiv_rec.terms_id := r_invoice.ippt_id;
3368 l_xpiv_rec.trx_status_code := 'ENTERED';
3369 l_xpiv_rec.vendor_site_id := r_invoice.ipvs_id;
3370 l_xpiv_rec.wait_vendor_invoice_yn := r_invoice.wait_vendor_invoice_yn;
3371 l_xpiv_rec.workflow_flag := r_invoice.workflow_yn;
3372
3373 l_xpiv_rec.CURRENCY_CONVERSION_TYPE := r_invoice.CURRENCY_CONVERSION_TYPE;
3374 l_xpiv_rec.CURRENCY_CONVERSION_RATE := r_invoice.CURRENCY_CONVERSION_RATE;
3375 l_xpiv_rec.CURRENCY_CONVERSION_DATE := r_invoice.CURRENCY_CONVERSION_DATE;
3376
3377
3378 -- Multi Currency Parameters
3379 IF ( (l_xpiv_rec.CURRENCY_CONVERSION_TYPE IS NULL) OR
3380 (l_xpiv_rec.CURRENCY_CONVERSION_RATE IS NULL) OR
3381 (l_xpiv_rec.CURRENCY_CONVERSION_DATE IS NULL) OR
3382 (l_xpiv_rec.INVOICE_CURRENCY_CODE IS NULL) ) THEN
3383
3384 l_currency_code := NULL;
3385 l_currency_conversion_type := NULL;
3386 l_currency_conversion_rate := NULL;
3387 l_currency_conversion_date := NULL;
3388
3389 OPEN l_curr_conv_csr ( r_invoice.khr_id );
3390 FETCH l_curr_conv_csr INTO l_currency_code,
3391 l_currency_conversion_type,
3392 l_currency_conversion_rate,
3393 l_currency_conversion_date;
3394 CLOSE l_curr_conv_csr;
3395
3396 l_xpiv_rec.INVOICE_CURRENCY_CODE := l_currency_code;
3397 l_xpiv_rec.CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
3398 l_xpiv_rec.CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
3399 l_xpiv_rec.CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
3400
3401 -- If Currency Code is null then default functional currency
3402 IF ( l_xpiv_rec.INVOICE_CURRENCY_CODE IS NULL ) THEN
3403 l_xpiv_rec.INVOICE_CURRENCY_CODE
3404 := okl_accounting_util.get_func_curr_code;
3405 END IF;
3406
3407 -- If the type were not captured in authoring
3408 IF l_xpiv_rec.currency_conversion_type IS NULL THEN
3409 l_xpiv_rec.currency_conversion_type := 'User';
3410 l_xpiv_rec.currency_conversion_rate := 1;
3411 l_xpiv_rec.currency_conversion_date := SYSDATE;
3412 END IF;
3413
3414 -- For date
3415 IF l_xpiv_rec.currency_conversion_date IS NULL THEN
3416 l_xpiv_rec.currency_conversion_date := SYSDATE;
3417 END IF;
3418
3419 -- For rate -- Work out the rate in a Spot or Corporate
3420 IF (l_xpiv_rec.currency_conversion_type = 'User') THEN
3421 IF l_xpiv_rec.currency_conversion_rate IS NULL THEN
3422 l_xpiv_rec.currency_conversion_rate := 1;
3423 END IF;
3424 END IF;
3425 END IF;
3426
3427 IF (l_xpiv_rec.currency_conversion_type = 'Spot'
3428 OR l_xpiv_rec.currency_conversion_type = 'Corporate') THEN
3429
3430 l_xpiv_rec.currency_conversion_rate := NULL;
3431 /*
3432 := okl_accounting_util.get_curr_con_rate
3433 (p_from_curr_code => l_xpiv_rec.INVOICE_CURRENCY_CODE,
3434 p_to_curr_code => okl_accounting_util.get_func_curr_code,
3435 p_con_date => l_xpiv_rec.currency_conversion_date,
3436 p_con_type => l_xpiv_rec.currency_conversion_type);
3437 */
3438 END IF;
3439
3440 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Header..');
3441
3442 -- Start of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
3443 IF(L_DEBUG_ENABLED='Y') THEN
3444 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3445 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3446 END IF;
3447 IF(IS_DEBUG_PROCEDURE_ON) THEN
3448 BEGIN
3449 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
3450 END;
3451 END IF;
3452
3453
3454 OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
3455 p_api_version => p_api_version
3456 ,p_init_msg_list => p_init_msg_list
3457 ,x_return_status => x_return_status
3458 ,x_msg_count => x_msg_count
3459 ,x_msg_data => x_msg_data
3460 ,p_xpiv_rec => l_xpiv_rec
3461 ,x_xpiv_rec => lx_xpiv_rec);
3462 IF(IS_DEBUG_PROCEDURE_ON) THEN
3463 BEGIN
3464 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
3465 END;
3466 END IF;
3467 -- End of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
3468 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3469 l_conc_status := 'E';
3470 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
3471 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3472 l_conc_status := 'E';
3473 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
3474 ELSIF (x_return_status ='S') THEN
3475 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Header with Id: '||lx_xpiv_rec.id);
3476 END IF;
3477
3478 l_previous_tap_id := r_invoice.tap_id;
3479 l_previous_xpi_id := lx_xpiv_rec.id;
3480 END IF;
3481 -----------------------------------------------------------------
3482 -- Populate and Insert external AP invoice Lines
3483 -----------------------------------------------------------------
3484 --l_xlpv_rec.xpi_id_details := lx_xpiv_rec.id;
3485 l_xlpv_rec.xpi_id_details := l_previous_xpi_id;
3486 l_xlpv_rec.amount := r_invoice.line_amount;
3487 l_xlpv_rec.org_id := r_invoice.org_id;
3488 l_xlpv_rec.object_version_number:= r_invoice.object_version_number;
3489 l_xlpv_rec.dist_code_combination_id := NVL(r_invoice.tpl_ccid,r_invoice.db_ccid);
3490 l_xlpv_rec.tpl_id := r_invoice.tpl_id;
3491
3492
3493 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Lines..');
3494 -- Start of wraper code generated automatically by Debug code generator for okl_xtl_pay_invs_pub.insert_xtl_pay_invs
3495 IF(IS_DEBUG_PROCEDURE_ON) THEN
3496 BEGIN
3497 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
3498 END;
3499 END IF;
3500 okl_xtl_pay_invs_pub.insert_xtl_pay_invs(
3501 p_api_version => p_api_version
3502 ,p_init_msg_list => p_init_msg_list
3503 ,x_return_status => x_return_status
3504 ,x_msg_count => x_msg_count
3505 ,x_msg_data => x_msg_data
3506 ,p_xlpv_rec => l_xlpv_rec
3507 ,x_xlpv_rec => lx_xlpv_rec);
3508 IF(IS_DEBUG_PROCEDURE_ON) THEN
3509 BEGIN
3510 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
3511 END;
3512 END IF;
3513 -- End of wraper code generated automatically by Debug code generator for okl_xtl_pay_invs_pub.insert_xtl_pay_invs
3514
3515 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3516 l_conc_status := 'E';
3517 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
3518 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3519 l_conc_status := 'E';
3520 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
3521 ELSIF (x_return_status ='S') THEN
3522 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Line with Id: '||lx_xlpv_rec.id);
3523 END IF;
3524
3525 ELSE
3526 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Creating Receviables Invoice');
3527
3528 -----------------------------------------------------------------
3529 -- Populate external AR invoice header and line record
3530 -----------------------------------------------------------------
3531 l_taiv_rec.amount := -r_invoice.invoice_amount;
3532 l_taiv_rec.amount_applied := 0;
3533 l_taiv_rec.currency_code := r_invoice.currency_code;
3534 l_taiv_rec.date_entered := SYSDATE;
3535 l_taiv_rec.date_invoiced := SYSDATE;
3536 l_taiv_rec.khr_id := r_invoice.khr_id;
3537 l_taiv_rec.object_version_number := l_api_version;
3538 l_taiv_rec.org_id := r_invoice.org_id;
3539 l_taiv_rec.set_of_books_id := r_invoice.sob_id;
3540 l_taiv_rec.trx_status_code := 'ENTERED';
3541 l_taiv_rec.try_id := r_invoice.try_id ;
3542
3543
3544 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating Internal AR Invoice Header..');
3545
3546 -- Start of wraper code generated automatically by Debug code generator for okl_trx_ar_invoices_pub.insert_trx_ar_invoices
3547 IF(IS_DEBUG_PROCEDURE_ON) THEN
3548 BEGIN
3549 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
3550 END;
3551 END IF;
3552 okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
3553 p_api_version => p_api_version
3554 ,p_init_msg_list => p_init_msg_list
3555 ,x_return_status => x_return_status
3556 ,x_msg_count => x_msg_count
3557 ,x_msg_data => x_msg_data
3558 ,p_taiv_rec => l_taiv_rec
3559 ,x_taiv_rec => lx_taiv_rec);
3560 IF(IS_DEBUG_PROCEDURE_ON) THEN
3561 BEGIN
3562 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
3563 END;
3564 END IF;
3565 -- End of wraper code generated automatically by Debug code generator for okl_trx_ar_invoices_pub.insert_trx_ar_invoices
3566
3567 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3568 l_conc_status := 'E';
3569 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Header.');
3570 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3571 l_conc_status := 'E';
3572 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Header.');
3573 ELSIF (x_return_status ='S') THEN
3574 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Internal AR Invoice Header with Id: '||lx_taiv_rec.id);
3575 END IF;
3576
3577 -----------------------------------------------------------------
3578 -- Insert external AR invoice line
3579 -----------------------------------------------------------------
3580 l_tilv_rec.tai_id := lx_taiv_rec.id;
3581 l_tilv_rec.amount := -r_invoice.invoice_amount;
3582 l_tilv_rec.amount_applied := 0;
3583 l_tilv_rec.line_number := 1;
3584 l_tilv_rec.object_version_number := l_api_version;
3585 l_tilv_rec.org_id := r_invoice.org_id;
3586 l_tilv_rec.inv_receiv_line_code := 'LINE';
3587 l_taiv_rec.attribute1 := 'AUTO_DISBURSEMENT';
3588 l_tilv_rec.attribute1 := 'AUTO_DISBURSEMENT';
3589
3590 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating Internal AR Invoice Line..');
3591 -- Start of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
3592 IF(IS_DEBUG_PROCEDURE_ON) THEN
3593 BEGIN
3594 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
3595 END;
3596 END IF;
3597 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
3598 p_api_version => p_api_version
3599 ,p_init_msg_list => p_init_msg_list
3600 ,x_return_status => x_return_status
3601 ,x_msg_count => x_msg_count
3602 ,x_msg_data => x_msg_data
3603 ,p_tilv_rec => l_tilv_rec
3604 ,x_tilv_rec => lx_tilv_rec);
3605 IF(IS_DEBUG_PROCEDURE_ON) THEN
3606 BEGIN
3607 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
3608 END;
3609 END IF;
3610 -- End of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
3611
3612 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3613 l_conc_status := 'E';
3614 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Line.');
3615 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3616 l_conc_status := 'E';
3617 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Line.');
3618 ELSIF (x_return_status ='S') THEN
3619 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Internal AR Invoice Line with Id: '||lx_tilv_rec.id);
3620 END IF;
3621
3622 p_bpd_acc_rec.id := lx_tilv_rec.id;
3623 p_bpd_acc_rec.source_table := 'OKL_TXL_AR_INV_LNS_B';
3624 ----------------------------------------------------
3625 -- Create Accounting Distributions
3626 ----------------------------------------------------
3627 -- Start of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
3628 IF(IS_DEBUG_PROCEDURE_ON) THEN
3629 BEGIN
3630 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
3631 END;
3632 END IF;
3633 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
3634 p_api_version
3635 ,p_init_msg_list
3636 ,x_return_status
3637 ,x_msg_count
3638 ,x_msg_data
3639 ,p_bpd_acc_rec
3640 );
3641 IF(IS_DEBUG_PROCEDURE_ON) THEN
3642 BEGIN
3643 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
3644 END;
3645 END IF;
3646 -- End of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
3647 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3648 l_conc_status := 'E';
3649 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Accounting Distributions.');
3650 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3651 l_conc_status := 'E';
3652 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Accounting Distributions.');
3653 ELSIF (x_return_status ='S') THEN
3654 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... Accounting Distributions Created.');
3655 END IF;
3656
3657 END IF;
3658
3659 IF ( NVL(l_conc_status, 'E') = okl_api.g_ret_sts_success )
3660 THEN
3661 -----------------------------------------------------------------
3662 -- Update internal AP table for Success
3663 -----------------------------------------------------------------
3664 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || r_invoice.invoice_number);
3665
3666 UPDATE okl_trx_ap_invoices_b
3667 SET trx_status_code = 'PROCESSED'
3668 WHERE id = r_invoice.tap_id;
3669
3670 ELSE
3671 -----------------------------------------------------------------
3672 -- Update internal AP table for Error
3673 -----------------------------------------------------------------
3674 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || r_invoice.invoice_number);
3675
3676 IF x_msg_count >= 1 THEN
3677
3678 FOR i in 1..x_msg_count LOOP
3679 fnd_msg_pub.get (p_msg_index => i,
3680 p_encoded => 'F',
3681 p_data => x_msg_data,
3682 p_msg_index_out => l_msg_index_out);
3683
3684 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,to_char(i) || ': ' || x_msg_data);
3685
3686 END LOOP;
3687 END IF;
3688
3689 UPDATE okl_trx_ap_invoices_b
3690 SET trx_status_code = 'ERROR'
3691 WHERE id = r_invoice.tap_id;
3692
3693 UPDATE OKL_EXT_PAY_INVS_b
3694 SET trx_status_code = 'ERROR'
3695 WHERE id = lx_xpiv_rec.id;
3696
3697 UPDATE okl_trx_ar_invoices_b
3698 SET trx_status_code = 'ERROR'
3699 WHERE id = lx_taiv_rec.id;
3700
3701 END IF;
3702
3703
3704 IF l_commit_cnt > l_MAX_commit_cnt THEN
3705 COMMIT;
3706 l_commit_cnt := 0;
3707 END IF;
3708
3709 END LOOP;
3710
3711 COMMIT;
3712
3713 l_commit_cnt := 0;
3714 l_previous_tap_id := null;
3715 l_previous_xpi_id := null;
3716
3717 -- FUNDING CURSOR
3718
3719 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3720 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* Begin Funding Records ******* ');
3721 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3722
3723 FOR fund_rec IN c_funding_csr LOOP
3724 IF (NVL(l_previous_tap_id, -99) <> fund_rec.tap_id) THEN
3725
3726 l_conc_status := 'S';
3727
3728 l_commit_cnt := l_commit_cnt + 1;
3729
3730 -- Consider activating
3731 -- Null records
3732 --l_xpiv_rec := l_init_xpiv_rec;
3733 --l_xlpv_rec := l_init_xlpv_rec;
3734
3735 -- Null records
3736 l_xpiv_rec := l_init_xpiv_rec;
3737 l_xlpv_rec := l_init_xlpv_rec;
3738 l_taiv_rec := l_init_taiv_rec;
3739 l_tilv_rec := l_init_tilv_rec;
3740
3741 ------------------------------------------------------------
3742 -- Generate sequence while consolidating, Bug 2949640
3743 -- Sunil Mathew, 12-May-2003
3744 ------------------------------------------------------------
3745 l_gen_seq := NULL;
3746 IF fund_rec.invoice_number IS NULL THEN
3747 BEGIN
3748 l_gen_seq := fnd_seqnum.get_next_sequence
3749 (appid => l_okl_application_id,
3750 cat_code => l_document_category,
3751 sobid => fund_rec.sob_id,
3752 met_code => 'A',
3753 trx_date => SYSDATE,
3754 dbseqnm => lx_dbseqnm,
3755 dbseqid => lx_dbseqid);
3756 EXCEPTION
3757 WHEN OTHERS THEN
3758 l_gen_seq := -1;
3759 END;
3760 END IF;
3761
3762 fund_rec.vendor_invoice_number := NVL(fund_rec.vendor_invoice_number,l_gen_seq);
3763 fund_rec.invoice_number := NVL(fund_rec.invoice_number,l_gen_seq);
3764
3765 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3766 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Vendor Invoice Number: '||fund_rec.vendor_invoice_number);
3767 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
3768
3769 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
3770 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Set of Books: '||fund_rec.sob_id||' Org Id: '||fund_rec.org_id||' Invoice Number: '||fund_rec.invoice_number );
3771 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Vendor Site (ipvs_id): '||fund_rec.ipvs_id||' Terms Id(ippt_id): '||fund_rec.ippt_id||' Payment Method Code: '||fund_rec.payment_method_code );
3772 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ Currency: '||fund_rec.currency_code||' GL Date: '||fund_rec.gl_date||' Nettable: '||fund_rec.nettable_yn );
3773 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ khr_id: '||fund_rec.khr_id||' try_id: '||fund_rec.try_id);
3774 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ db_ccid: '||fund_rec.db_ccid||' cr_ccid: '||fund_rec.cr_ccid);
3775 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++ invoice_amount: '||fund_rec.invoice_amount||' Vendor Id: '||fund_rec.vendor_id);
3776 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
3777 END IF;
3778
3779 -- SAVEPOINT C_INVOICE_POINT;
3780
3781 IF NVL(fund_rec.nettable_yn, 'N') = 'N' OR fund_rec.invoice_amount > 0 THEN
3782 IF (NVL(l_previous_tap_id, -99) <> fund_rec.tap_id) THEN
3783 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Creating Payables Invoice');
3784
3785 -----------------------------------------------------------------
3786 -- Populate and insert external AP invoice header
3787 -----------------------------------------------------------------
3788 l_xpiv_rec.invoice_id := NULL;
3789
3790 OPEN seq_csr;
3791 FETCH seq_csr INTO l_xpiv_rec.invoice_id;
3792 CLOSE seq_csr;
3793
3794 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Invoice id: '||l_xpiv_rec.invoice_id);
3795
3796 l_xpiv_rec.doc_category_code := fund_rec.invoice_category_code;
3797 l_xpiv_rec.invoice_num := fund_rec.invoice_number;
3798 l_xpiv_rec.vendor_invoice_number:= fund_rec.vendor_invoice_number;
3799 l_xpiv_rec.gl_date := fund_rec.gl_date ;
3800 l_xpiv_rec.invoice_amount := fund_rec.invoice_amount;
3801 l_xpiv_rec.pay_group_lookup_code:= fund_rec.pay_group_lookup_code;
3802 l_xpiv_rec.nettable_yn := fund_rec.nettable_yn;
3803
3804 l_xpiv_rec.accts_pay_cc_id := NVL(fund_rec.tap_ccid,fund_rec.cr_ccid);
3805 l_xpiv_rec.invoice_currency_code:= fund_rec.currency_code;
3806 l_xpiv_rec.invoice_date := fund_rec.date_invoiced;
3807 l_xpiv_rec.object_version_number:= fund_rec.object_version_number;
3808 l_xpiv_rec.org_id := fund_rec.org_id;
3809 l_xpiv_rec.payment_method := fund_rec.payment_method_code;
3810 l_xpiv_rec.terms_id := fund_rec.ippt_id;
3811 l_xpiv_rec.trx_status_code := 'ENTERED';
3812 l_xpiv_rec.vendor_site_id := fund_rec.ipvs_id;
3813 l_xpiv_rec.wait_vendor_invoice_yn := fund_rec.wait_vendor_invoice_yn;
3814 l_xpiv_rec.workflow_flag := fund_rec.workflow_yn;
3815
3816
3817 l_xpiv_rec.CURRENCY_CONVERSION_TYPE := fund_rec.CURRENCY_CONVERSION_TYPE;
3818 l_xpiv_rec.CURRENCY_CONVERSION_RATE := fund_rec.CURRENCY_CONVERSION_RATE;
3819 l_xpiv_rec.CURRENCY_CONVERSION_DATE := fund_rec.CURRENCY_CONVERSION_DATE;
3820
3821
3822 -- Multi Currency Parameters
3823 IF ( (l_xpiv_rec.CURRENCY_CONVERSION_TYPE IS NULL) OR
3824 (l_xpiv_rec.CURRENCY_CONVERSION_RATE IS NULL) OR
3825 (l_xpiv_rec.CURRENCY_CONVERSION_DATE IS NULL) OR
3826 (l_xpiv_rec.INVOICE_CURRENCY_CODE IS NULL) ) THEN
3827
3828 l_currency_code := NULL;
3829 l_currency_conversion_type := NULL;
3830 l_currency_conversion_rate := NULL;
3831 l_currency_conversion_date := NULL;
3832
3833 OPEN l_curr_conv_csr ( fund_rec.khr_id );
3834 FETCH l_curr_conv_csr INTO l_currency_code,
3835 l_currency_conversion_type,
3836 l_currency_conversion_rate,
3837 l_currency_conversion_date;
3838 CLOSE l_curr_conv_csr;
3839
3840 l_xpiv_rec.INVOICE_CURRENCY_CODE := l_currency_code;
3841 l_xpiv_rec.CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
3842 l_xpiv_rec.CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
3843 l_xpiv_rec.CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
3844
3845 -- If Currency Code is null then default functional currency
3846 IF ( l_xpiv_rec.INVOICE_CURRENCY_CODE IS NULL ) THEN
3847 l_xpiv_rec.INVOICE_CURRENCY_CODE
3848 := okl_accounting_util.get_func_curr_code;
3849 END IF;
3850
3851 -- If the type were not captured in authoring
3852 IF l_xpiv_rec.currency_conversion_type IS NULL THEN
3853 l_xpiv_rec.currency_conversion_type := 'User';
3854 l_xpiv_rec.currency_conversion_rate := 1;
3855 l_xpiv_rec.currency_conversion_date := SYSDATE;
3856 END IF;
3857
3858 -- For date
3859 IF l_xpiv_rec.currency_conversion_date IS NULL THEN
3860 l_xpiv_rec.currency_conversion_date := SYSDATE;
3861 END IF;
3862
3863 -- For rate -- Work out the rate in a Spot or Corporate
3864 IF (l_xpiv_rec.currency_conversion_type = 'User') THEN
3865 IF l_xpiv_rec.currency_conversion_rate IS NULL THEN
3866 l_xpiv_rec.currency_conversion_rate := 1;
3867 END IF;
3868 END IF;
3869 END IF;
3870
3871 IF (l_xpiv_rec.currency_conversion_type = 'Spot'
3872 OR l_xpiv_rec.currency_conversion_type = 'Corporate') THEN
3873
3874 l_xpiv_rec.currency_conversion_rate := NULL;
3875 /*l_xpiv_rec.currency_conversion_rate
3876 := okl_accounting_util.get_curr_con_rate
3877 (p_from_curr_code => l_xpiv_rec.INVOICE_CURRENCY_CODE,
3878 p_to_curr_code => okl_accounting_util.get_func_curr_code,
3879 p_con_date => l_xpiv_rec.currency_conversion_date,
3880 p_con_type => l_xpiv_rec.currency_conversion_type);
3881 */
3882 END IF;
3883
3884
3885 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Header..');
3886
3887 -- Start of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
3888 IF(IS_DEBUG_PROCEDURE_ON) THEN
3889 BEGIN
3890 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
3891 END;
3892 END IF;
3893
3894
3895 OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
3896 p_api_version => p_api_version
3897 ,p_init_msg_list => p_init_msg_list
3898 ,x_return_status => x_return_status
3899 ,x_msg_count => x_msg_count
3900 ,x_msg_data => x_msg_data
3901 ,p_xpiv_rec => l_xpiv_rec
3902 ,x_xpiv_rec => lx_xpiv_rec);
3903 IF(IS_DEBUG_PROCEDURE_ON) THEN
3904 BEGIN
3905 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
3906 END;
3907 END IF;
3908 -- End of wraper code generated automatically by Debug code generator for OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs
3909
3910 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3911 l_conc_status := 'E';
3912 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
3913 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3914 l_conc_status := 'E';
3915 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Header.');
3916 ELSIF (x_return_status ='S') THEN
3917 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Header with Id: '||lx_xpiv_rec.id);
3918 END IF;
3919
3920 l_previous_tap_id := fund_rec.tap_id;
3921 l_previous_xpi_id := lx_xpiv_rec.id;
3922 END IF;
3923
3924 -----------------------------------------------------------------
3925 -- Populate and Insert external AP invoice Lines
3926 -----------------------------------------------------------------
3927 --l_xlpv_rec.xpi_id_details := lx_xpiv_rec.id;
3928 l_xlpv_rec.xpi_id_details := l_previous_xpi_id;
3929 l_xlpv_rec.amount := fund_rec.line_amount;
3930 l_xlpv_rec.org_id := fund_rec.org_id;
3931 l_xlpv_rec.object_version_number:= fund_rec.object_version_number;
3932 l_xlpv_rec.dist_code_combination_id := NVL(fund_rec.tap_ccid,fund_rec.db_ccid);
3933
3934 -- Usually Line id but for funding, an exception!
3935 -- IF (fund_rec.FUNDING_TYPE_CODE = 'ASSET_SUBSIDY') THEN
3936 -- l_xlpv_rec.tpl_id := fund_rec.tpl_id;
3937 -- ELSE
3938 l_xlpv_rec.tap_id := fund_rec.tap_id;
3939 -- END IF;
3940
3941 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating External AP Invoice Lines..');
3942 okl_xtl_pay_invs_pub.insert_xtl_pay_invs(
3943 p_api_version => p_api_version
3944 ,p_init_msg_list => p_init_msg_list
3945 ,x_return_status => x_return_status
3946 ,x_msg_count => x_msg_count
3947 ,x_msg_data => x_msg_data
3948 ,p_xlpv_rec => l_xlpv_rec
3949 ,x_xlpv_rec => lx_xlpv_rec);
3950
3951
3952 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3953 l_conc_status := 'E';
3954 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
3955 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3956 l_conc_status := 'E';
3957 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating External AP Invoice Line.');
3958 ELSIF (x_return_status ='S') THEN
3959 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created External AP Invoice Line with Id: '||lx_xlpv_rec.id);
3960 END IF;
3961
3962 ELSE
3963 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Creating Receviables Invoice');
3964
3965 -----------------------------------------------------------------
3966 -- Populate external AR invoice header and line record
3967 -----------------------------------------------------------------
3968 l_taiv_rec.amount := -fund_rec.invoice_amount;
3969 l_taiv_rec.amount_applied := 0;
3970 l_taiv_rec.currency_code := fund_rec.currency_code;
3971 l_taiv_rec.date_entered := SYSDATE;
3972 l_taiv_rec.date_invoiced := SYSDATE;
3973 l_taiv_rec.khr_id := fund_rec.khr_id;
3974 l_taiv_rec.object_version_number := l_api_version;
3975 l_taiv_rec.org_id := fund_rec.org_id;
3976 l_taiv_rec.set_of_books_id := fund_rec.sob_id;
3977 l_taiv_rec.trx_status_code := 'ENTERED';
3978 l_taiv_rec.try_id := fund_rec.try_id ;
3979
3980
3981 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating Internal AR Invoice Header..');
3982
3983 -- Start of wraper code generated automatically by Debug code generator for okl_trx_ar_invoices_pub.insert_trx_ar_invoices
3984 IF(IS_DEBUG_PROCEDURE_ON) THEN
3985 BEGIN
3986 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
3987 END;
3988 END IF;
3989 okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
3990 p_api_version => p_api_version
3991 ,p_init_msg_list => p_init_msg_list
3992 ,x_return_status => x_return_status
3993 ,x_msg_count => x_msg_count
3994 ,x_msg_data => x_msg_data
3995 ,p_taiv_rec => l_taiv_rec
3996 ,x_taiv_rec => lx_taiv_rec);
3997 IF(IS_DEBUG_PROCEDURE_ON) THEN
3998 BEGIN
3999 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
4000 END;
4001 END IF;
4002 -- End of wraper code generated automatically by Debug code generator for okl_trx_ar_invoices_pub.insert_trx_ar_invoices
4003
4004 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4005 l_conc_status := 'E';
4006 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Header.');
4007 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4008 l_conc_status := 'E';
4009 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Header.');
4010 ELSIF (x_return_status ='S') THEN
4011 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Internal AR Invoice Header with Id: '||lx_taiv_rec.id);
4012 END IF;
4013
4014 -----------------------------------------------------------------
4015 -- Insert external AR invoice line
4016 -----------------------------------------------------------------
4017 l_tilv_rec.tai_id := lx_taiv_rec.id;
4018 l_tilv_rec.amount := -fund_rec.invoice_amount;
4019 l_tilv_rec.amount_applied := 0;
4020 l_tilv_rec.line_number := 1;
4021 l_tilv_rec.object_version_number := l_api_version;
4022 l_tilv_rec.org_id := fund_rec.org_id;
4023 l_tilv_rec.inv_receiv_line_code := 'LINE';
4024 l_taiv_rec.attribute1 := 'AUTO_DISBURSEMENT';
4025 l_tilv_rec.attribute1 := 'AUTO_DISBURSEMENT';
4026
4027 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Creating Internal AR Invoice Line..');
4028 -- Start of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
4029 IF(IS_DEBUG_PROCEDURE_ON) THEN
4030 BEGIN
4031 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
4032 END;
4033 END IF;
4034 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
4035 p_api_version => p_api_version
4036 ,p_init_msg_list => p_init_msg_list
4037 ,x_return_status => x_return_status
4038 ,x_msg_count => x_msg_count
4039 ,x_msg_data => x_msg_data
4040 ,p_tilv_rec => l_tilv_rec
4041 ,x_tilv_rec => lx_tilv_rec);
4042 IF(IS_DEBUG_PROCEDURE_ON) THEN
4043 BEGIN
4044 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
4045 END;
4046 END IF;
4047 -- End of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
4048
4049 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4050 l_conc_status := 'E';
4051 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Line.');
4052 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4053 l_conc_status := 'E';
4054 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Internal AR Invoice Line.');
4055 ELSIF (x_return_status ='S') THEN
4056 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Internal AR Invoice Line with Id: '||lx_tilv_rec.id);
4057 END IF;
4058
4059 p_bpd_acc_rec.id := lx_tilv_rec.id;
4060 p_bpd_acc_rec.source_table := 'OKL_TXL_AR_INV_LNS_B';
4061 ----------------------------------------------------
4062 -- Create Accounting Distributions
4063 ----------------------------------------------------
4064 -- Start of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
4065 IF(IS_DEBUG_PROCEDURE_ON) THEN
4066 BEGIN
4067 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
4068 END;
4069 END IF;
4070 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
4071 p_api_version
4072 ,p_init_msg_list
4073 ,x_return_status
4074 ,x_msg_count
4075 ,x_msg_data
4076 ,p_bpd_acc_rec
4077 );
4078 IF(IS_DEBUG_PROCEDURE_ON) THEN
4079 BEGIN
4080 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
4081 END;
4082 END IF;
4083 -- End of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
4084
4085 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4086 l_conc_status := 'E';
4087 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Accounting Distributions.');
4088 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4089 l_conc_status := 'E';
4090 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Accounting Distributions.');
4091 ELSIF (x_return_status ='S') THEN
4092 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... Accounting Distributions Created.');
4093 END IF;
4094 END IF;
4095
4096
4097 IF ( NVL(l_conc_status, 'E') = okl_api.g_ret_sts_success )
4098 THEN
4099 -----------------------------------------------------------------
4100 -- Update internal AP table for Success
4101 -----------------------------------------------------------------
4102 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || fund_rec.invoice_number);
4103
4104 UPDATE okl_trx_ap_invoices_b
4105 SET trx_status_code = 'PROCESSED'
4106 WHERE id = fund_rec.tap_id;
4107 ELSE
4108 -----------------------------------------------------------------
4109 -- Update internal AP table for Error
4110 -----------------------------------------------------------------
4111 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || fund_rec.invoice_number);
4112
4113 IF x_msg_count >= 1 THEN
4114
4115 FOR i in 1..x_msg_count LOOP
4116 fnd_msg_pub.get (p_msg_index => i,
4117 p_encoded => 'F',
4118 p_data => x_msg_data,
4119 p_msg_index_out => l_msg_index_out);
4120
4121 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,to_char(i) || ': ' || x_msg_data);
4122
4123 END LOOP;
4124 END IF;
4125
4126 UPDATE okl_trx_ap_invoices_b
4127 SET trx_status_code = 'ERROR'
4128 WHERE id = fund_rec.tap_id;
4129
4130 UPDATE OKL_EXT_PAY_INVS_b
4131 SET trx_status_code = 'ERROR'
4132 WHERE id = lx_xpiv_rec.id;
4133
4134 UPDATE okl_trx_ar_invoices_b
4135 SET trx_status_code = 'ERROR'
4136 WHERE id = lx_taiv_rec.id;
4137
4138 END IF;
4139
4140 IF l_commit_cnt > l_MAX_commit_cnt THEN
4141 COMMIT;
4142 l_commit_cnt := 0;
4143 END IF;
4144
4145 END LOOP; -- End Funding Cursor
4146
4147 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
4148 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* End Funding Records ******* ');
4149 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '============================================================');
4150
4151 ------------------------------------------------------------
4152 -- End processing
4153 ------------------------------------------------------------
4154
4155 Okl_Api.END_ACTIVITY (
4156 x_msg_count => x_msg_count,
4157 x_msg_data => x_msg_data);
4158
4159
4160 EXCEPTION
4161 ------------------------------------------------------------
4162 -- Exception handling
4163 ------------------------------------------------------------
4164 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4165 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4166 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4167 p_api_name => l_api_name,
4168 p_pkg_name => G_PKG_NAME,
4169 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4170 x_msg_count => x_msg_count,
4171 x_msg_data => x_msg_data,
4172 p_api_type => '_PVT');
4173
4174 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4175 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4176 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4177 p_api_name => l_api_name,
4178 p_pkg_name => G_PKG_NAME,
4179 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4180 x_msg_count => x_msg_count,
4181 x_msg_data => x_msg_data,
4182 p_api_type => '_PVT');
4183
4184 WHEN OTHERS THEN
4185 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4186 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4187 p_api_name => l_api_name,
4188 p_pkg_name => G_PKG_NAME,
4189 p_exc_name => 'OTHERS',
4190 x_msg_count => x_msg_count,
4191 x_msg_data => x_msg_data,
4192 p_api_type => '_PVT');
4193 END consolidation_11i;
4194
4195
4196 PROCEDURE consolidation_ssiruvol(
4197 p_api_version IN NUMBER,
4198 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4199 x_return_status OUT NOCOPY VARCHAR2,
4200 x_msg_count OUT NOCOPY NUMBER,
4201 x_msg_data OUT NOCOPY VARCHAR2,
4202 p_contract_number IN VARCHAR2 DEFAULT NULL,
4203 p_vendor IN VARCHAr2 DEFAULT NULL,
4204 p_vendor_site IN VARCHAr2 DEFAULT NULL,
4205 p_vpa_number IN VARCHAR2 DEFAULT NULL,
4206 p_stream_type_purpose IN VARCHAR2 DEFAULT NULL,
4207 p_from_date IN DATE DEFAULT NULL,
4208 p_to_date IN DATE DEFAULT NULL)
4209 IS
4210 ------------------------------------------------------------
4211 -- Declare variables required by APIs
4212 ------------------------------------------------------------
4213 l_api_version CONSTANT NUMBER := 1;
4214 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATION';
4215 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4216 ------------------------------------------------------------
4217 -- Declare Process variables
4218 ------------------------------------------------------------
4219 l_msg_index_out NUMBER;
4220 i NUMBER;
4221
4222 -----------------------------------------------------------------
4223 -- Fetch Ap Interface Sequence Number
4224 -----------------------------------------------------------------
4225 CURSOR seq_csr IS
4226 SELECT ap_invoices_interface_s.nextval
4227 FROM dual;
4228
4229 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
4230
4231 ------------------------------------------------------------
4232 -- Declare records: Payable Invoice Headers and Lines
4233 ------------------------------------------------------------
4234 l_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
4235
4236 -- Nulling record
4237 l_init_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
4238
4239 lx_xpiv_rec Okl_xpi_Pvt.xpiv_rec_type;
4240 l_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
4241
4242 -- Nulling record
4243 l_init_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
4244
4245 lx_xlpv_rec okl_xlp_pvt.xlpv_rec_type;
4246
4247 l_taiv_rec okl_tai_pvt.taiv_rec_type;
4248 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
4249 -- Nulling record
4250 l_init_taiv_rec okl_tai_pvt.taiv_rec_type;
4251
4252 l_tilv_rec okl_til_pvt.tilv_rec_type;
4253 lx_tilv_rec okl_til_pvt.tilv_rec_type;
4254 -- Nulling record
4255 l_init_tilv_rec okl_til_pvt.tilv_rec_type;
4256
4257 -- Multi Currency Compliance
4258 l_currency_code okl_ext_sell_invs_b.currency_code%type;
4259 l_currency_conversion_type okl_ext_sell_invs_b.currency_conversion_type%type;
4260 l_currency_conversion_rate okl_ext_sell_invs_b.currency_conversion_rate%type;
4261 l_currency_conversion_date okl_ext_sell_invs_b.currency_conversion_date%type;
4262
4263 l_previous_tap_id okl_trx_ap_invoices_b.id%TYPE := NULL;
4264 l_previous_xpi_id okl_ext_sell_invs_b.id%TYPE := NULL;
4265
4266 CURSOR l_curr_conv_csr( cp_khr_id NUMBER ) IS
4267 SELECT currency_code
4268 ,currency_conversion_type
4269 ,currency_conversion_rate
4270 ,currency_conversion_date
4271 FROM okl_k_headers_full_v
4272 WHERE id = cp_khr_id;
4273
4274 CURSOR l_contract_id_csr (cNum VARCHAR2) Is
4275 select id
4276 from okc_K_headers_b
4277 where contract_number = cNum;
4278
4279 l_contract_id_rec l_contract_id_csr%ROWTYPE;
4280
4281 CURSOR l_vendor_csr (vendorN VARCHAR2) IS
4282 select vendor_id
4283 from po_vendors
4284 where vendor_name = nvl(vendorN, 'XX');
4285
4286 l_vendor_rec l_vendor_csr%ROWTYPE;
4287
4288 CURSOR l_vendor_site_csr (vId NUMBER, VSite VARCHAR) IS
4289 select vendor_site_id
4290 from po_vendor_sites_all
4291 where vendor_id = nvl(vId, -1)
4292 and vendor_site_code = nvl(VSite, 'XX');
4293
4294 l_vendor_site_rec l_vendor_site_csr%ROWTYPE;
4295
4296 -----------------------------------------------------------
4297 -- Variables for Bug 2949640, Sunil Mathew, 12-May-2003
4298 -----------------------------------------------------------
4299 l_gen_seq okl_trx_ap_invoices_v.invoice_number%TYPE;
4300 l_okl_application_id NUMBER(3) := 540;
4301 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
4302 lX_dbseqnm VARCHAR2(2000):= '';
4303 lX_dbseqid NUMBER(38):= NULL;
4304
4305
4306 l_conc_status VARCHAR2(1);
4307
4308 l_commit_cnt NUMBER;
4309 l_MAX_commit_cnt NUMBER := 500;
4310
4311 l_cin_tbl OKL_CIN_PVT.cin_tbl_type;
4312 lx_cin_tbl OKL_CIN_PVT.cin_tbl_type;
4313
4314 l_cin_rec OKL_CIN_PVT.cin_rec_type;
4315 lx_cin_rec OKL_CIN_PVT.cin_rec_type;
4316
4317 l_tplv_rec OKL_TPL_PVT.tplv_rec_type;
4318 lx_tplv_rec OKL_TPL_PVT.tplv_rec_type;
4319
4320 l_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
4321 lx_tplv_tbl OKL_TPL_PVT.tplv_tbl_type;
4322
4323
4324
4325 l_cnsld_invs cnsld_invs_tbl_type;
4326
4327 l_contract_id NUMBER;
4328 l_vendor_id NUMBER;
4329 l_vendor_site_id NUMBER;
4330 l_vpa_id NUMBER;
4331
4332 l_cnsld_line_number BINARY_INTEGER := 0;
4333
4334 --start: | 01-May-07 cklee R12 DIsb enhancement project
4335 CURSOR acc_sys_option is
4336 select account_derivation
4337 from okl_sys_acct_opts;
4338 --end: | 01-May-07 cklee R12 DIsb enhancement project
4339
4340 BEGIN
4341 ------------------------------------------------------------
4342 -- Start processing
4343 ------------------------------------------------------------
4344
4345 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'OKL Prepare Payables Invoices IN pvt consol');
4346
4347 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4348
4349 l_return_status := OKL_API.START_ACTIVITY(
4350 p_api_name => l_api_name,
4351 p_pkg_name => g_pkg_name,
4352 p_init_msg_list => p_init_msg_list,
4353 l_api_version => l_api_version,
4354 p_api_version => p_api_version,
4355 p_api_type => '_PVT',
4356 x_return_status => l_return_status);
4357
4358 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4359 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4360 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4361 RAISE OKL_API.G_EXCEPTION_ERROR;
4362 END IF;
4363
4364 --start: | 01-May-07 cklee R12 DIsb enhancement project
4365 OPEN acc_sys_option;
4366 FETCH acc_sys_option INTO G_ACC_SYS_OPTION;
4367 CLOSE acc_sys_option;
4368 --end: | 01-May-07 cklee R12 DIsb enhancement project
4369 -----------------------------------------------------------------
4370 -- Open Invoice cursor in block
4371 -----------------------------------------------------------------
4372
4373 l_commit_cnt := 0;
4374
4375 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol getting contarct id for contarct ' || p_contract_number);
4376
4377 OPEN l_contract_id_csr(p_contract_number);
4378 FETCH l_contract_id_csr into l_contract_id_rec;
4379 If l_contract_id_csr%FOUND THEN
4380 l_contract_id := l_contract_id_rec.id;
4381 ELse
4382 l_contract_id := NULL;
4383 END IF;
4384 CLOSE l_contract_id_csr;
4385
4386 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol got contarct id ' || to_char(nvl(l_contract_id, -1)));
4387
4388 l_contract_id_rec := NULL;
4389 OPEN l_contract_id_csr(p_vpa_number);
4390 FETCH l_contract_id_csr into l_contract_id_rec;
4391 If l_contract_id_csr%FOUND THEN
4392 l_vpa_id := l_contract_id_rec.id;
4393 ELse
4394 l_vpa_id := NULL;
4395 END IF;
4396 CLOSE l_contract_id_csr;
4397
4398 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol got vpa_id id ' || to_char(nvl(l_vpa_id, -1)));
4399
4400 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol getting vendor program ' || nvl(p_vpa_number, 'XXX'));
4401 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol getting vendor name ' || nvl(p_vendor, 'XXX'));
4402 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol getting vendor site ' || nvl(p_vendor_site, 'XXX'));
4403
4404 /*
4405 OPEN l_vendor_csr(p_vendor);
4406 FETCH l_vendor_csr into l_vendor_rec;
4407 If l_vendor_csr%FOUND THEN
4408 l_vendor_id := l_vendor_rec.vendor_id;
4409 ELse
4410 l_vendor_id := NULL;
4411 END IF;
4412 CLOSE l_vendor_csr;
4413 */
4414
4415 l_vendor_id := to_number(p_vendor);
4416
4417
4418 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol got vendor name id ' || to_char(nvl(l_vendor_id, -1)));
4419 /*
4420
4421 OPEN l_vendor_site_csr(l_vendor_id, p_vendor_site );
4422 FETCH l_vendor_site_csr into l_vendor_site_rec;
4423 If l_vendor_site_csr%FOUND THEN
4424 l_vendor_site_id := l_vendor_site_rec.vendor_site_id;
4425 ELse
4426 l_vendor_site_id := NULL;
4427 END IF;
4428 CLOSE l_vendor_site_csr;
4429
4430 */
4431
4432 l_vendor_site_id := to_number(p_vendor_site);
4433
4434 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'IN pvt consol got vendor site id ' || to_char(nvl(l_vendor_site_id, -1)));
4435
4436 apply_consolidation_rules_ssir(
4437 p_api_version => p_api_version,
4438 p_init_msg_list => p_init_msg_list,
4439 x_return_status => x_return_status,
4440 x_msg_count => x_msg_count,
4441 x_msg_data => x_msg_data,
4442 x_cnsld_invs => l_cnsld_invs,
4443 p_contract_id => l_contract_id,
4444 p_vendor_id => l_vendor_id,
4445 p_vendor_site_id => l_vendor_site_id,
4446 p_vpa_id => l_vpa_id,
4447 p_stream_type_purpose => p_stream_type_purpose,
4448 p_from_date => p_from_date,
4449 p_to_date => p_to_date);
4450
4451
4452
4453 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4454 l_conc_status := 'E';
4455 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Cannot Consolidate.');
4456 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4457 l_conc_status := 'E';
4458 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Cannot Consolidate.');
4459 ELSIF (x_return_status ='S') THEN
4460 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Consolidated invoices: ');
4461 END IF;
4462
4463 if l_cnsld_invs.count > 0 Then
4464
4465 FOR i in l_cnsld_invs.FIRST..l_cnsld_invs.LAST
4466 LOOP
4467
4468 l_cin_rec := l_cnsld_invs(i).cin_rec;
4469
4470 OKL_CIN_PVT.insert_row(
4471 p_api_version => p_api_version,
4472 p_init_msg_list => p_init_msg_list,
4473 x_return_status => x_return_status,
4474 x_msg_count => x_msg_count,
4475 x_msg_data => x_msg_data,
4476 p_cin_rec => l_cin_rec,
4477 x_cin_rec => lx_cin_rec);
4478
4479
4480 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4481 l_conc_status := 'E';
4482 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Consolidated invoices.');
4483 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4484 l_conc_status := 'E';
4485 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Consolidated invoices.');
4486 ELSIF (x_return_status ='S') THEN
4487 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Consolidated invoices: '||to_char(l_cin_rec.cnsld_ap_inv_id));
4488 END IF;
4489
4490 l_tplv_tbl := l_cnsld_invs(i).tplv_tbl;
4491 l_cnsld_line_number := 0;
4492 FOR j in l_tplv_tbl.FIRST..l_tplv_tbl.LAST
4493 LOOP
4494 l_tplv_tbl(j).cnsld_ap_inv_id := lx_cin_rec.cnsld_ap_inv_id;
4495 l_cnsld_line_number := l_cnsld_line_number + 1;
4496 l_tplv_tbl(j).cnsld_line_number := l_cnsld_line_number;
4497 END LOOP;
4498
4499
4500 OKL_TPL_PVT.update_row(
4501 p_api_version => p_api_version,
4502 p_init_msg_list => p_init_msg_list,
4503 x_return_status => x_return_status,
4504 x_msg_count => x_msg_count,
4505 x_msg_data => x_msg_data,
4506 p_tplv_tbl => l_tplv_tbl,
4507 x_tplv_tbl => lx_tplv_tbl);
4508
4509
4510 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4511 l_conc_status := 'E';
4512 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Consolidated invoices lines.');
4513 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4514 l_conc_status := 'E';
4515 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Creating Consolidated invoices lines.');
4516 ELSIF (x_return_status ='S') THEN
4517 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Consolidated invoices lines: '||to_char(lx_tplv_rec.id));
4518 END IF;
4519
4520 If (x_return_status ='S') THEN
4521
4522 For j in l_tplv_tbl.FIRST..l_tplv_tbl.LAST
4523 LOOP
4524
4525 UPDATE OKL_TRX_AP_INVS_ALL_B
4526 SET TRX_STATUS_CODE = 'PROCESSED'
4527 WHERE ID = l_tplv_tbl(j).tap_id;
4528
4529 END LOOP;
4530
4531 -- strat: cklee 4/26/07
4532 Else
4533
4534 For j in l_tplv_tbl.FIRST..l_tplv_tbl.LAST
4535 LOOP
4536
4537 UPDATE OKL_TRX_AP_INVS_ALL_B
4538 SET TRX_STATUS_CODE = 'ERROR'
4539 WHERE ID = l_tplv_tbl(j).tap_id;
4540
4541 END LOOP;
4542 -- end: cklee 4/26/07
4543
4544 End If;
4545
4546 END LOOP;
4547
4548 End If; -- if l_cnsld_invs.count > 0 Then
4549
4550
4551 transfer_to_external(
4552 p_api_version => p_api_version,
4553 p_init_msg_list => p_init_msg_list,
4554 x_return_status => x_return_status,
4555 x_msg_count => x_msg_count,
4556 x_msg_data => x_msg_data);
4557 -- p_from_date => p_from_date,
4558 -- p_to_date => p_to_date);
4559
4560 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4561 l_conc_status := 'E';
4562 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: transferring invoices lines.');
4563 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4564 l_conc_status := 'E';
4565 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: transferring invoices lines.');
4566 ELSIF (x_return_status ='S') THEN
4567 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' transferring invoices lines: ');
4568 END IF;
4569
4570 ------------------------------------------------------------
4571 -- End processing
4572 ------------------------------------------------------------
4573
4574 Okl_Api.END_ACTIVITY (
4575 x_msg_count => x_msg_count,
4576 x_msg_data => x_msg_data);
4577
4578
4579 EXCEPTION
4580 ------------------------------------------------------------
4581 -- Exception handling
4582 ------------------------------------------------------------
4583 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4584 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4585 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4586 p_api_name => l_api_name,
4587 p_pkg_name => G_PKG_NAME,
4588 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4589 x_msg_count => x_msg_count,
4590 x_msg_data => x_msg_data,
4591 p_api_type => '_PVT');
4592
4593 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4594 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4595 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4596 p_api_name => l_api_name,
4597 p_pkg_name => G_PKG_NAME,
4598 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4599 x_msg_count => x_msg_count,
4600 x_msg_data => x_msg_data,
4601 p_api_type => '_PVT');
4602
4603 WHEN OTHERS THEN
4604 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4605 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4606 p_api_name => l_api_name,
4607 p_pkg_name => G_PKG_NAME,
4608 p_exc_name => 'OTHERS',
4609 x_msg_count => x_msg_count,
4610 x_msg_data => x_msg_data,
4611 p_api_type => '_PVT');
4612 END consolidation_ssiruvol;
4613
4614 --start: 31-Oct-2007 cklee -- bug: 6508575 fixed
4615 ----------------------------------------------------------------------------------
4616 -- Start of comments
4617 --
4618 -- Procedure Name : get consolidation parameters
4619 -- Description : get consolidation parameters
4620 -- Business Rules :
4621 -- Parameters :
4622 -- Version : 1.0
4623 -- End of comments
4624 ----------------------------------------------------------------------------------
4625 PROCEDURE get_con_parameters(
4626 p_api_version IN NUMBER,
4627 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4628 x_return_status OUT NOCOPY VARCHAR2,
4629 x_msg_count OUT NOCOPY NUMBER,
4630 x_msg_data OUT NOCOPY VARCHAR2,
4631 p_contract_number IN VARCHAR2 DEFAULT NULL,
4632 p_vendor IN VARCHAr2 DEFAULT NULL,
4633 p_vendor_site IN VARCHAr2 DEFAULT NULL,
4634 p_vpa_number IN VARCHAR2 DEFAULT NULL,
4635 x_contract_id OUT NOCOPY NUMBER,
4636 x_vendor_id OUT NOCOPY NUMBER,
4637 x_vendor_site_id OUT NOCOPY NUMBER,
4638 x_vpa_id OUT NOCOPY NUMBER
4639 )
4640 IS
4641 ------------------------------------------------------------
4642 -- Declare variables required by APIs
4643 ------------------------------------------------------------
4644 l_api_version CONSTANT NUMBER := 1;
4645 l_api_name CONSTANT VARCHAR2(30) := 'get_con_parameters';
4646 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4647 ------------------------------------------------------------
4648 -- Declare Process variables
4649 ------------------------------------------------------------
4650 -- l_msg_index_out NUMBER;
4651 -- i NUMBER;
4652
4653 CURSOR l_contract_id_csr (cNum VARCHAR2) Is
4654 select id
4655 from okc_k_headers_all_b
4656 where contract_number = cNum;
4657
4658 -- l_contract_id_rec l_contract_id_csr%ROWTYPE;
4659
4660 CURSOR l_vendor_csr (vendorN VARCHAR2) IS
4661 select pv.vendor_id
4662 from po_vendors pv
4663 where pv.vendor_name = vendorN;
4664
4665 -- l_vendor_rec l_vendor_csr%ROWTYPE;
4666
4667 CURSOR l_vendor_site_csr (VSite VARCHAR) IS
4668 select vendor_site_id
4669 from po_vendor_sites_all povs
4670 where povs.vendor_site_code = VSite;
4671
4672 -- l_vendor_site_rec l_vendor_site_csr%ROWTYPE;
4673
4674 l_conc_status VARCHAR2(1);
4675
4676 -- l_contract_id NUMBER;
4677 -- l_vendor_id NUMBER;
4678 -- l_vendor_site_id NUMBER;
4679 -- l_vpa_id NUMBER;
4680
4681 -- l_cnsld_line_number BINARY_INTEGER := 0;
4682
4683
4684 BEGIN
4685 ------------------------------------------------------------
4686 -- Start processing
4687 ------------------------------------------------------------
4688
4689 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 1');
4690
4691 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4692
4693 l_return_status := OKL_API.START_ACTIVITY(
4694 p_api_name => l_api_name,
4695 p_pkg_name => g_pkg_name,
4696 p_init_msg_list => p_init_msg_list,
4697 l_api_version => l_api_version,
4698 p_api_version => p_api_version,
4699 p_api_type => '_PVT',
4700 x_return_status => l_return_status);
4701
4702 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4703 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4704 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4705 RAISE OKL_API.G_EXCEPTION_ERROR;
4706 END IF;
4707 -----------------------------------------------------------------
4708 -- Open Invoice cursor in block
4709 -----------------------------------------------------------------
4710
4711 -- l_commit_cnt := 0;
4712 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 2 contarct number:' || p_contract_number);
4713
4714 OPEN l_contract_id_csr(p_contract_number);
4715 FETCH l_contract_id_csr into x_contract_id;
4716 CLOSE l_contract_id_csr;
4717
4718 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 3 got contarct id ' || to_char(nvl(x_contract_id, -1)));
4719
4720 -- l_contract_id_rec := NULL;
4721 OPEN l_contract_id_csr(p_vpa_number);
4722 FETCH l_contract_id_csr into x_vpa_id;
4723 CLOSE l_contract_id_csr;
4724
4725 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 4 got vpa_id id ' || to_char(nvl(x_vpa_id, -1)));
4726
4727 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 5 getting vendor program ' || nvl(p_vpa_number, 'XXX'));
4728 --start:| 26-Nov-2007 cklee -- bug: 6620557 fixed |
4729 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 6 getting vendor ID ' || nvl(p_vendor, 'XXX'));
4730 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 7 getting vendor site ID ' || nvl(p_vendor_site, 'XXX'));
4731 x_vendor_id := p_vendor;
4732 x_vendor_site_id := p_vendor_site;
4733 /*
4734 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 6 getting vendor name ' || nvl(p_vendor, 'XXX'));
4735 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 7 getting vendor site ' || nvl(p_vendor_site, 'XXX'));
4736
4737 OPEN l_vendor_csr(p_vendor);
4738 FETCH l_vendor_csr into x_vendor_id;
4739 CLOSE l_vendor_csr;
4740
4741 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 8 got vendor name id ' || to_char(nvl(x_vendor_id, -1)));
4742
4743 OPEN l_vendor_site_csr(p_vendor_site);
4744 FETCH l_vendor_site_csr into x_vendor_site_id;
4745 CLOSE l_vendor_site_csr;
4746
4747 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'get_con_parameters: 9 got vendor site id ' || to_char(nvl(x_vendor_site_id, -1)));*/
4748 --end:| 26-Nov-2007 cklee -- bug: 6620557 fixed |
4749
4750 ------------------------------------------------------------
4751 -- End processing
4752 ------------------------------------------------------------
4753
4754 Okl_Api.END_ACTIVITY (
4755 x_msg_count => x_msg_count,
4756 x_msg_data => x_msg_data);
4757
4758
4759 EXCEPTION
4760 ------------------------------------------------------------
4761 -- Exception handling
4762 ------------------------------------------------------------
4763 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4764 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4765 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4766 p_api_name => l_api_name,
4767 p_pkg_name => G_PKG_NAME,
4768 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4769 x_msg_count => x_msg_count,
4770 x_msg_data => x_msg_data,
4771 p_api_type => '_PVT');
4772
4773 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4774 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4775 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4776 p_api_name => l_api_name,
4777 p_pkg_name => G_PKG_NAME,
4778 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4779 x_msg_count => x_msg_count,
4780 x_msg_data => x_msg_data,
4781 p_api_type => '_PVT');
4782
4783 WHEN OTHERS THEN
4784 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4785 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4786 p_api_name => l_api_name,
4787 p_pkg_name => G_PKG_NAME,
4788 p_exc_name => 'OTHERS',
4789 x_msg_count => x_msg_count,
4790 x_msg_data => x_msg_data,
4791 p_api_type => '_PVT');
4792 END get_con_parameters;
4793
4794 ----------------------------------------------------------------------------------
4795 -- Start of comments
4796 --
4797 -- Procedure Name : consolidation
4798 -- Description : consolidation
4799 -- Business Rules :
4800 -- Parameters :
4801 -- Version : 1.0
4802 -- End of comments
4803 ----------------------------------------------------------------------------------
4804 PROCEDURE consolidation(
4805 p_api_version IN NUMBER,
4806 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4807 x_return_status OUT NOCOPY VARCHAR2,
4808 x_msg_count OUT NOCOPY NUMBER,
4809 x_msg_data OUT NOCOPY VARCHAR2,
4810 p_contract_number IN VARCHAR2 DEFAULT NULL,
4811 p_vendor IN VARCHAr2 DEFAULT NULL,
4812 p_vendor_site IN VARCHAr2 DEFAULT NULL,
4813 p_vpa_number IN VARCHAR2 DEFAULT NULL,
4814 p_stream_type_purpose IN VARCHAR2 DEFAULT NULL,
4815 p_from_date IN DATE DEFAULT NULL,
4816 p_to_date IN DATE DEFAULT NULL)
4817 IS
4818 ------------------------------------------------------------
4819 -- Declare variables required by APIs
4820 ------------------------------------------------------------
4821 l_api_version CONSTANT NUMBER := 1;
4822 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATION';
4823 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4824 ------------------------------------------------------------
4825 -- Declare Process variables
4826 ------------------------------------------------------------
4827 -- l_msg_index_out NUMBER;
4828 -- i NUMBER;
4829
4830 l_contract_id NUMBER;
4831 l_vendor_id NUMBER;
4832 l_vendor_site_id NUMBER;
4833 l_vpa_id NUMBER;
4834
4835 l_conc_status VARCHAR2(1);
4836
4837 BEGIN
4838 ------------------------------------------------------------
4839 -- Start processing
4840 ------------------------------------------------------------
4841
4842 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'consolidation 1');
4843
4844 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4845
4846 l_return_status := OKL_API.START_ACTIVITY(
4847 p_api_name => l_api_name,
4848 p_pkg_name => g_pkg_name,
4849 p_init_msg_list => p_init_msg_list,
4850 l_api_version => l_api_version,
4851 p_api_version => p_api_version,
4852 p_api_type => '_PVT',
4853 x_return_status => l_return_status);
4854
4855 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4856 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4857 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4858 RAISE OKL_API.G_EXCEPTION_ERROR;
4859 END IF;
4860 --
4861 get_con_parameters(
4862 p_api_version => p_api_version,
4863 p_init_msg_list => p_init_msg_list,
4864 x_return_status => x_return_status,
4865 x_msg_count => x_msg_count,
4866 x_msg_data => x_msg_data,
4867 p_contract_number => p_contract_number,
4868 p_vendor => p_vendor,
4869 p_vendor_site => p_vendor_site,
4870 p_vpa_number => p_vpa_number,
4871 x_contract_id => l_contract_id,
4872 x_vendor_id => l_vendor_id,
4873 x_vendor_site_id => l_vendor_site_id,
4874 x_vpa_id => l_vpa_id);
4875
4876 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4877 l_conc_status := 'E';
4878 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: get_con_parameters failed.');
4879 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4880 l_conc_status := 'E';
4881 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: get_con_parameters failed.');
4882 ELSIF (x_return_status ='S') THEN
4883 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' get_con_parameters done: ');
4884 END IF;
4885
4886 apply_consolidation_rules(
4887 p_api_version => p_api_version,
4888 p_init_msg_list => p_init_msg_list,
4889 x_return_status => x_return_status,
4890 x_msg_count => x_msg_count,
4891 x_msg_data => x_msg_data,
4892 p_contract_id => l_contract_id,
4893 p_vendor_id => l_vendor_id,
4894 p_vendor_site_id => l_vendor_site_id,
4895 p_vpa_id => l_vpa_id,
4896 p_stream_type_purpose => p_stream_type_purpose,
4897 p_from_date => p_from_date,
4898 p_to_date => p_to_date);
4899
4900 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4901 l_conc_status := 'E';
4902 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: apply_consolidation_rules failed.');
4903 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4904 l_conc_status := 'E';
4905 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: apply_consolidation_rules failed.');
4906 ELSIF (x_return_status ='S') THEN
4907 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Consolidated invoices: ');
4908 END IF;
4909 /* future release
4910 handle_processing_fee(
4911 p_api_version => p_api_version,
4912 p_init_msg_list => p_init_msg_list,
4913 x_return_status => x_return_status,
4914 x_msg_count => x_msg_count,
4915 x_msg_data => x_msg_data);
4916
4917 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4918 l_conc_status := 'E';
4919 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Handle porcessing feet.');
4920 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4921 l_conc_status := 'E';
4922 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: Handle porcessing feet.');
4923 ELSIF (x_return_status ='S') THEN
4924 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Handle porcessing feet.');
4925 END IF;
4926 */
4927 transfer_to_external(
4928 p_api_version => p_api_version,
4929 p_init_msg_list => p_init_msg_list,
4930 x_return_status => x_return_status,
4931 x_msg_count => x_msg_count,
4932 x_msg_data => x_msg_data);
4933
4934 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4935 l_conc_status := 'E';
4936 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: transferring invoices lines.');
4937 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4938 l_conc_status := 'E';
4939 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ERROR: transferring invoices lines.');
4940 ELSIF (x_return_status ='S') THEN
4941 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' transferring invoices lines: ');
4942 END IF;
4943
4944 ------------------------------------------------------------
4945 -- End processing
4946 ------------------------------------------------------------
4947
4948 Okl_Api.END_ACTIVITY (
4949 x_msg_count => x_msg_count,
4950 x_msg_data => x_msg_data);
4951
4952
4953 EXCEPTION
4954 ------------------------------------------------------------
4955 -- Exception handling
4956 ------------------------------------------------------------
4957 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4958 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4959 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4960 p_api_name => l_api_name,
4961 p_pkg_name => G_PKG_NAME,
4962 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4963 x_msg_count => x_msg_count,
4964 x_msg_data => x_msg_data,
4965 p_api_type => '_PVT');
4966
4967 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4968 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4969 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4970 p_api_name => l_api_name,
4971 p_pkg_name => G_PKG_NAME,
4972 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4973 x_msg_count => x_msg_count,
4974 x_msg_data => x_msg_data,
4975 p_api_type => '_PVT');
4976
4977 WHEN OTHERS THEN
4978 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
4979 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
4980 p_api_name => l_api_name,
4981 p_pkg_name => G_PKG_NAME,
4982 p_exc_name => 'OTHERS',
4983 x_msg_count => x_msg_count,
4984 x_msg_data => x_msg_data,
4985 p_api_type => '_PVT');
4986 END consolidation;
4987 --end: 31-Oct-2007 cklee -- bug: 6508575 fixed
4988
4989
4990
4991 END okl_pay_invoices_cons_pvt;