[Home] [Help]
PACKAGE BODY: APPS.FV_THIRD_PARTY_REMIT_PKG
Source
1 PACKAGE BODY FV_THIRD_PARTY_REMIT_PKG AS
2 /* $Header: FVTPREMB.pls 120.3 2005/11/23 12:15:50 bnarang ship $ */
3
4 -- -------------------------------------------------------------
5 -- GLOBAL VARIABLES AND PROCEDURES DECLARATION
6 -- -------------------------------------------------------------
7 g_module_name VARCHAR2(100) ;
8 g_errbuf VARCHAR2(1000);
9 g_retcode NUMBER := 0;
10 g_org_id Fv_Tpp_Check_Details.org_id%TYPE;
11 g_sob_id gl_ledgers.ledger_id%TYPE;
12 g_sob_name gl_ledgers.name%TYPE;
13 g_pay_date_from DATE;
14 g_pay_date_to DATE;
15 g_from_supp_id Fv_Tpp_Assignments.original_supplier_id%TYPE;
16 g_from_site_id Fv_Tpp_Assignments.original_supplier_site_id%TYPE;
17 g_to_supp_id Fv_Tpp_Assignments.third_party_agent_id%TYPE;
18 g_to_site_id Fv_Tpp_Assignments.third_party_site_id%TYPE;
19 g_from_supp_name Po_Vendors.vendor_name%TYPE;
20 g_to_supp_name Po_Vendors.vendor_name%TYPE;
21 g_from_site_code Po_Vendor_Sites.vendor_site_code%TYPE;
22 g_to_site_code Po_Vendor_Sites.vendor_site_code%TYPE;
23 g_sort_by VARCHAR2(1);
24 g_checkrun_name VARCHAR2(50);
25 g_debug_flag VARCHAR2(1) ;
26 g_tpp_flag VARCHAR2(1) ;
27 g_message VARCHAR2(3000);
28 g_data_found VARCHAR2(1) ;
29
30 PROCEDURE LOG_MESSAGE (p_level NUMBER, p_module VARCHAR2, p_message VARCHAR2, p_debug VARCHAR2 DEFAULT NULL);
31
32 PROCEDURE INITIALIZATION;
33
34 PROCEDURE POPULATE_THIRD_PARTY_TEMP (p_from_supp_name VARCHAR2,
35 p_from_supp_site VARCHAR2,
36 p_to_supp_name VARCHAR2,
37 p_to_supp_site VARCHAR2,
38 p_check_number NUMBER,
39 p_check_date DATE,
40 p_check_amount NUMBER,
41 p_invoice_number VARCHAR2,
42 p_invoice_amount NUMBER,
43 p_discount_amount NUMBER);
44
45 FUNCTION VENDOR_NAME(p_vendor_id NUMBER) RETURN VARCHAR2;
46
47 FUNCTION VENDOR_SITE(p_vendor_site_id NUMBER) RETURN VARCHAR2;
48
49 PROCEDURE PROCESS_TPP_CHECK_DETAIL_RECS;
50
51 PROCEDURE SUBMIT_REPORT;
52
53 -- -------------------------------------------------------------
54 -- PROCEDURE MAIN
55 -- -------------------------------------------------------------
56 -- This is called from the concurrent program to execute Third
57 -- Party Remittance Process. The purpose of this process is to
58 -- call all the subsequent procedures.
59 -- -------------------------------------------------------------
60 PROCEDURE MAIN(x_errbuf OUT NOCOPY VARCHAR2,
61 x_retcode OUT NOCOPY NUMBER,
62 p_pay_date_from VARCHAR2,
63 p_pay_date_to VARCHAR2,
64 p_checkrun_name VARCHAR2,
65 p_from_supp_id NUMBER,
66 p_from_supp_site_id NUMBER,
67 p_to_supp_id NUMBER,
68 p_to_supp_site_id NUMBER,
69 p_sort_by VARCHAR2)
70 IS
71 l_module_name VARCHAR2(200) ;
72 BEGIN
73
74 l_module_name := g_module_name || 'MAIN';
75 g_message := 'Starting Third Party Remittance process ...';
76 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
77
78 g_message := ' Third Party Profile Option: '|| g_tpp_flag;
79 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
80
81 g_message := ' Set Of Books Id: '|| g_sob_id;
82 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
83
84 g_message := ' Organization Id: '|| g_org_id;
85 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
86
87 g_pay_date_from := FND_DATE.CANONICAL_TO_DATE(p_pay_date_from);
88 g_message := ' Payment Date From: '|| to_char(g_pay_date_from);
89 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
90
91 g_pay_date_to := FND_DATE.CANONICAL_TO_DATE(p_pay_date_to);
92 g_message := ' Payment Date To: '|| to_char(g_pay_date_to);
93 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
94
95 g_checkrun_name := p_checkrun_name;
96 g_message := ' Payment Batch Name: '|| g_checkrun_name;
97 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
98
99 g_from_supp_id := p_from_supp_id;
100 g_message := ' Original Supplier Id: '|| g_from_supp_id;
101 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
102
103 IF (g_from_supp_id IS NOT NULL)
104 THEN
105 IF (g_retcode = 0)
106 THEN
107 g_from_supp_name := Vendor_Name(g_from_supp_id);
108 g_message := ' Original Supplier Name: '|| g_from_supp_name;
109 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
110 END IF;
111 END IF;
112
113 g_from_site_id := p_from_supp_site_id;
114 g_message := ' Original Supplier Site Id: '|| g_from_site_id;
115 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
116
117 IF (g_from_site_id IS NOT NULL)
118 THEN
119 IF (g_retcode = 0)
120 THEN
121 g_from_site_code := Vendor_Site(g_from_site_id);
122 g_message := ' Original Supplier Site: '|| g_from_site_code;
123 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
124 END IF;
125 END IF;
126
127 g_to_supp_id := p_to_supp_id;
128 g_message := ' Third Party Agent Id: '|| g_to_supp_id;
129 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
130
131 IF (g_to_supp_id IS NOT NULL)
132 THEN
133 IF (g_retcode = 0)
134 THEN
135 g_to_supp_name := Vendor_Name(g_to_supp_id);
136 g_message := ' Third Party Agent: '|| g_to_supp_name;
137 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
138 END IF;
139 END IF;
140
141 g_to_site_id := p_to_supp_site_id;
142 g_message := ' Third Party Site Id: '|| g_to_site_id;
143 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
144
145 IF (g_to_site_id IS NOT NULL)
146 THEN
147 IF (g_retcode = 0)
148 THEN
149 g_to_site_code := Vendor_Site(g_to_site_id);
150 g_message := ' Third Party Site: '|| g_to_site_code;
151 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
152 END IF;
153 END IF;
154
155 g_sort_by := p_sort_by;
156 g_message := ' Sort By: '|| g_sort_by;
157 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
158
159 IF (g_tpp_flag = 'N')
160 THEN
161 g_errbuf := 'Erroring out from Third Party Remittance Process because Third Party Profile Option is set to NO !!';
162 g_retcode := 2;
163 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
164 ELSE
165 -- Purge fv_third_party_temp table using Initialization procedure
166 IF (g_retcode = 0)
167 THEN
168 Initialization;
169 END IF;
170
171 -- Process records in Fv_Tpp_Checks_Details table
172 IF (g_retcode = 0)
173 THEN
174 Process_Tpp_Check_Detail_Recs;
175 END IF;
176
177 -- Print the Third Party Remittance Report
178 IF (g_retcode = 0)
179 THEN
180 Submit_Report;
181 END IF;
182 END IF; /* Third Party Profile */
183
184 -- Check for errors
185 IF g_retcode <> 0
186 THEN
187 x_errbuf := g_errbuf;
188 x_retcode := g_retcode;
189 ROLLBACK;
190 ELSE
191 COMMIT;
192 END IF;
193
194 IF (g_retcode = 0) AND (g_data_found = 'N')
195 THEN
196 x_errbuf := 'NO DATA FOUND for Third Party Remittance reporting ...';
197 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,x_errbuf, 'N');
198 END IF;
199
200 g_message := 'Ending Third Party Remittance process ...';
201 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
202 EXCEPTION
203 WHEN OTHERS THEN
204 g_retcode := SQLCODE;
205 g_errbuf := SQLERRM || ' -- Error in the Main procedure';
206 x_retcode := g_retcode;
207 x_errbuf := g_errbuf;
208 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
209 END MAIN;
210
211
212 -- -------------------------------------------------------------
213 -- PROCEDURE LOG_MESSAGE
214 -- -------------------------------------------------------------
215 -- The purpose of this procedures is to accept a message and
216 -- print it to the log file.
217 -- -------------------------------------------------------------
218 PROCEDURE LOG_MESSAGE
219 (
220 p_level NUMBER,
221 p_module VARCHAR2,
222 p_message VARCHAR2,
223 p_debug VARCHAR2 DEFAULT NULL
224 ) IS
225 l_module_name VARCHAR2(200) ;
226 l_debug VARCHAR2(1);
227 BEGIN
228 IF p_debug IS NULL THEN
229 l_debug := 'Y' ;
230 ELSE
231 l_debug := p_debug;
232 END IF;
233 l_module_name := g_module_name || 'LOG_MESSAGE';
234 IF (l_debug = 'Y') THEN
235 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
236 FV_UTILITY.DEBUG_MESG(p_level, p_module, p_message);
237 END IF;
238 ELSE
239 FV_UTILITY.LOG_MESG(p_level, p_module, p_message);
240 END IF;
241 EXCEPTION
242 WHEN OTHERS THEN
243 g_errbuf := SQLERRM;
244 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
245 END LOG_MESSAGE;
246
247
248 -- -------------------------------------------------------------
249 -- PROCEDURE INITIALIZATION
250 -- -------------------------------------------------------------
251 -- The purpose of this procedure is to delete any existing data
252 -- from Fv_Third_Party_Temp table
253 -- -------------------------------------------------------------
254 PROCEDURE INITIALIZATION IS
255 l_module_name VARCHAR2(200) ;
256 l_count NUMBER := 0;
257 BEGIN
258 g_message := ' Purging Fv_Third_Party_Temp table ...';
259 l_module_name := g_module_name || 'INITIALIZATION';
260 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
261
262 DELETE FROM fv_third_party_temp;
263 EXCEPTION
264 WHEN OTHERS THEN
265 g_retcode := SQLCODE;
266 g_errbuf := SQLERRM ||
267 ' -- Error in Initialization Procedure when purging fv_third_party_temp table';
268 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
269 END INITIALIZATION;
270
271
272 -- -------------------------------------------------------------
273 -- PROCEDURE POPULATE_THIRD_PARTY_TEMP
274 -- -------------------------------------------------------------
275 -- The purpose of this procedure is to populate fv_third_party_
276 -- temp table.
277 -- -------------------------------------------------------------
278 PROCEDURE POPULATE_THIRD_PARTY_TEMP (p_from_supp_name VARCHAR2,
279 p_from_supp_site VARCHAR2,
280 p_to_supp_name VARCHAR2,
281 p_to_supp_site VARCHAR2,
282 p_check_number NUMBER,
283 p_check_date DATE,
284 p_check_amount NUMBER,
285 p_invoice_number VARCHAR2,
286 p_invoice_amount NUMBER,
287 p_discount_amount NUMBER) IS
288 l_module_name VARCHAR2(200) ;
289 BEGIN
290 g_message := ' Inserting a record in Fv_Third_Party_Temp ...';
291 l_module_name := g_module_name || 'POPULATE_THIRD_PARTY_TEMP';
292 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
293 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,'');
294
295 INSERT INTO fv_third_party_temp
296 (original_supplier_name,
297 original_supplier_site,
298 third_party_agent,
299 third_party_site,
300 check_number,
301 check_date,
302 check_amount,
303 invoice_number,
304 invoice_amount,
305 discount_amount,
306 org_id,
307 set_of_books_id)
308 VALUES
309 (p_from_supp_name,
310 p_from_supp_site,
311 p_to_supp_name,
312 p_to_supp_site,
313 p_check_number,
314 p_check_date,
315 p_check_amount,
316 p_invoice_number,
317 p_invoice_amount,
318 p_discount_amount,
319 g_org_id,
320 g_sob_id);
321 EXCEPTION
322 WHEN OTHERS THEN
323 g_retcode := SQLCODE;
324 g_errbuf := SQLERRM ||
325 ' -- Error in Populate_Third_Party_Temp Procedure when inserting record into fv_third_party_temp table';
326 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
327 END POPULATE_THIRD_PARTY_TEMP;
328
329
330 -- -------------------------------------------------------------
331 -- FUNCTION VENDOR_NAME
332 -- -------------------------------------------------------------
333 -- The purpose of this function is return a vendor_name
334 -- corresponding to a vendor_id
335 -- -------------------------------------------------------------
336 FUNCTION VENDOR_NAME(p_vendor_id NUMBER) RETURN VARCHAR2 IS
337 l_module_name VARCHAR2(200);
338 l_vendor_name Po_Vendors.vendor_name%TYPE;
339 BEGIN
340
341 l_module_name := g_module_name || 'VENDOR_NAME';
342
343 SELECT vendor_name
344 INTO l_vendor_name
345 FROM po_vendors
346 WHERE vendor_id = p_vendor_id;
347
348 RETURN(l_vendor_name);
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 l_vendor_name := NULL;
352
353 WHEN OTHERS THEN
354 g_retcode := SQLCODE;
355 g_errbuf := SQLERRM ||
356 ' -- Error in Vendor_Name Function finding vendor name';
357 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
358 END VENDOR_NAME;
359
360
361 -- -------------------------------------------------------------
362 -- FUNCTION VENDOR_SITE
363 -- -------------------------------------------------------------
364 -- The purpose of this function is return a vendor_site_code
365 -- corresponding to a vendor_site_id
366 -- -------------------------------------------------------------
367 FUNCTION VENDOR_SITE(p_vendor_site_id NUMBER) RETURN VARCHAR2 IS
368 l_module_name VARCHAR2(200) ;
369 l_vendor_site_code Po_Vendor_Sites.vendor_site_code%TYPE;
370 BEGIN
371
372 l_module_name := g_module_name || 'VENDOR_SITE';
373
374 SELECT vendor_site_code
375 INTO l_vendor_site_code
376 FROM po_vendor_sites
377 WHERE vendor_site_id = p_vendor_site_id;
378
379 RETURN(l_vendor_site_code);
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 l_vendor_site_code := NULL;
383
384 WHEN OTHERS THEN
385 g_retcode := SQLCODE;
386 g_errbuf := SQLERRM ||
387 ' -- Error in Vendor_Site Function finding vendor site code';
388 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
389 END VENDOR_SITE;
390
391
392 -- -------------------------------------------------------------
393 -- PROCEDURE PROCESS_TPP_CHECK_DETAIL_RECS
394 -- -------------------------------------------------------------
395 -- The purpose of this procedure is to process records in
396 -- fv_tpp_check_details table for Third Party Remittance
397 -- reporting
398 -- -------------------------------------------------------------
399 PROCEDURE PROCESS_TPP_CHECK_DETAIL_RECS IS
400 l_module_name VARCHAR2(200) ;
401 l_where_clause VARCHAR2(1000);
402 l_row_select VARCHAR2(3000);
403 l_row_cursor INTEGER;
404 l_row_fetch INTEGER;
405 l_exec_ret INTEGER;
406 l_original_supplier Po_Vendors.vendor_name%TYPE;
407 l_original_site Po_Vendor_Sites.vendor_site_code%TYPE;
408 l_third_party_agent Po_Vendors.vendor_name%TYPE;
409 l_third_party_site Po_Vendor_Sites.vendor_site_code%TYPE;
410 l_original_supplier_id Po_Vendors.vendor_id%TYPE;
411 l_original_site_id Po_Vendor_Sites.vendor_site_id%TYPE;
412 l_third_party_agent_id Po_Vendors.vendor_id%TYPE;
413 l_third_party_site_id Po_Vendor_Sites.vendor_site_id%TYPE;
414 l_check_id Ap_Checks.check_id%TYPE;
415 l_check_number Ap_Checks.check_number%TYPE;
416 l_check_date Ap_Checks.check_date%TYPE;
417 l_check_amount Ap_Checks.amount%TYPE;
418 l_invoice_num Ap_Invoices.invoice_num%TYPE;
419 l_invoice_amount Ap_Invoices.invoice_amount%TYPE;
420 l_discount_amount Ap_Invoices.discount_amount_taken%TYPE;
421 l_assignment_id Fv_Tpp_Assignments.assignment_id%TYPE;
422 l_checkrun_name Fv_Tpp_Check_Details.checkrun_name%TYPE;
423 l_check_num_fvtpp Fv_Tpp_Check_Details.check_number%TYPE;
424 l_bank_account_id Ap_Invoice_Selection_Criteria.bank_account_id%TYPE;
425 l_invoice_id Ap_Invoices.invoice_id%TYPE;
426 l_vendor_name Po_Vendors.vendor_name%TYPE;
427 l_site_code Po_Vendor_Sites.vendor_site_code%TYPE;
428 l_exists VARCHAR2(1);
429 i NUMBER := 0;
430 l NUMBER;
431 j NUMBER;
432 k NUMBER := 0;
433
434 CURSOR l_checks_invoices_cur IS
435 SELECT apc.check_number, apc.check_date, apc.amount,
436 invoice_num, invoice_amount, discount_amount_taken
437 FROM ap_checks apc, ap_invoice_payments apip, ap_invoices api
438 WHERE apc.bank_account_id = l_bank_account_id
439 AND apc.checkrun_name = l_checkrun_name
440 AND apc.check_number = l_check_num_fvtpp
441 AND apc.vendor_id = l_third_party_agent_id
442 AND apc.vendor_site_id = l_third_party_site_id
443 AND apc.check_id = apip.check_id
444 AND apip.set_of_books_id = g_sob_id
445 AND apip.invoice_id = api.invoice_id
446 AND api.vendor_id = l_original_supplier_id
447 AND api.vendor_site_id = l_original_site_id
448 AND api.set_of_books_id = g_sob_id
449 AND status_lookup_code IN ('CLEARED', 'CLEARED BUT UNACCOUNTED', 'ISSUED',
450 'NEGOTIABLE', 'RECONCILED', 'RECONCILED UNACCOUNTED');
451 BEGIN
452 l_module_name := g_module_name || 'PROCESS_TPP_CHECK_DETAIL_RECS';
453 g_message := ' Processing Fv_Tpp_Check_Detail records ...';
454 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
455
456 BEGIN
457 l_row_cursor := DBMS_SQL.OPEN_CURSOR;
458 EXCEPTION
459 WHEN OTHERS THEN
460 g_retcode := SQLCODE;
461 g_errbuf := SQLERRM ||
462 ' -- Error when opening the cursor in Process_Tpp_Check_Detail_Recs Procedure';
463 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
464 END;
465
466 -- Build up the where clause and the from clause based on the
467 -- parameters entered in the SRS request for Third Party
468 -- Remittance process
469
470 IF (g_from_supp_id IS NOT NULL) OR (g_from_site_id IS NOT NULL) OR
471 (g_to_supp_id IS NOT NULL) OR (g_to_site_id IS NOT NULL)
472 THEN
473 l_where_clause := ' tpp.assignment_id = fvcd.assignment_id AND ';
474 END IF;
475
476 IF (g_checkrun_name IS NOT NULL)
477 THEN
478 l_where_clause := l_where_clause || ' fvcd.checkrun_name = ' || '''' || g_checkrun_name || '''' || ' AND ';
479 END IF;
480
481 IF (g_from_supp_id IS NOT NULL)
482 THEN
483 l_where_clause := l_where_clause || ' tpp.original_supplier_id = '|| g_from_supp_id || ' AND ';
484 END IF;
485
486 IF (g_from_site_id IS NOT NULL)
487 THEN
488 l_where_clause := l_where_clause || ' tpp.original_supplier_site_id = '|| g_from_site_id || ' AND ';
489 END IF;
490
491 IF (g_to_supp_id IS NOT NULL)
492 THEN
493 l_where_clause := l_where_clause || ' tpp.third_party_agent_id = '|| g_to_supp_id || ' AND ';
494 END IF;
495
496 IF (g_to_site_id IS NOT NULL)
497 THEN
498 l_where_clause := l_where_clause || ' tpp.third_party_site_id = '|| g_to_site_id || ' AND ';
499 END IF;
500
501 IF (g_pay_date_from IS NOT NULL)
502 THEN
503 l_where_clause := l_where_clause || ' apisc.check_date >= ' || '''' || g_pay_date_from || '''' || ' AND ';
504 END IF;
505
506 IF (g_pay_date_to IS NOT NULL)
507 THEN
508 l_where_clause := l_where_clause || ' apisc.check_date <= ' || '''' || g_pay_date_to || '''' || ' AND ';
509 END IF;
510
511 l_row_select := '
512 SELECT fvcd.assignment_id, tpp.original_supplier_id, tpp.original_supplier_site_id,
513 tpp.third_party_agent_id, tpp.third_party_site_id, fvcd.checkrun_name, fvcd.check_number
514 FROM fv_tpp_check_details fvcd, fv_tpp_assignments_all tpp, ap_invoice_selection_criteria apisc
515 WHERE '|| l_where_clause ||'
516 fvcd.checkrun_name = apisc.checkrun_name
517 AND apisc.status = '||''''|| 'CONFIRMED' ||''''||'
518 AND fvcd.set_of_books_id = '|| g_sob_id ||'
519 AND tpp.set_of_books_id = '|| g_sob_id ||'
520 AND tpp.org_id = '|| g_org_id ||'
521 AND fvcd.assignment_id = tpp.assignment_id';
522
523 g_message := l_row_select;
524 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
525 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,'');
526
527 BEGIN
528 DBMS_SQL.PARSE(l_row_cursor, l_row_select, DBMS_SQL.V7);
529 EXCEPTION
530 WHEN OTHERS THEN
531 g_retcode := SQLCODE;
532 g_errbuf := SQLERRM ||
533 ' -- Error when parsing through the cursor in Process_Tpp_Check_Detail_Recs Procedure';
534 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
535 END;
536
537 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 1, l_assignment_id);
538 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 2, l_original_supplier_id);
539 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 3, l_original_site_id);
540 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 4, l_third_party_agent_id);
541 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 5, l_third_party_site_id);
542 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 6, l_checkrun_name, 50);
543 DBMS_SQL.DEFINE_COLUMN(l_row_cursor, 7, l_check_num_fvtpp);
544
545 BEGIN
546 l_exec_ret := DBMS_SQL.EXECUTE(l_row_cursor);
547 EXCEPTION
548 WHEN OTHERS THEN
549 g_retcode := SQLCODE;
550 g_errbuf := SQLERRM ||
551 ' -- Error when executing the cursor in Process_Tpp_Check_Detail_Recs Procedure';
552 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
553 END;
554
555 LOOP
556 l_assignment_id := NULL;
557 l_checkrun_name := NULL;
558 l_check_number := NULL;
559 l_bank_account_id := NULL;
560 l_invoice_id := NULL;
561 l_vendor_name := NULL;
562 l_site_code := NULL;
563 l_original_supplier := NULL;
564 l_original_site := NULL;
565 l_third_party_agent := NULL;
566 l_third_party_site := NULL;
567 l_original_supplier_id := NULL;
568 l_original_site_id := NULL;
569 l_third_party_agent_id := NULL;
570 l_third_party_site_id := NULL;
571 l_check_number := NULL;
572 l_check_date := NULL;
573 l_check_amount := NULL;
574 l_invoice_num := NULL;
575 l_invoice_amount := NULL;
576 l_discount_amount := NULL;
577 l_check_num_fvtpp := NULL;
578
579 l_row_fetch := DBMS_SQL.FETCH_ROWS(l_row_cursor);
580
581 IF (l_row_fetch = 0)
582 THEN
583 IF (k = 0)
584 THEN
585 g_message := ' NO DATA FOUND : Found no rows that satisfy the search criteria - Exiting from Process_Tpp_Check_Detail_Recs Procedure';
586 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_message, 'N');
587 g_data_found := 'N';
588 END IF;
589
590 EXIT; -- Exit the loop
591 ELSE
592 g_data_found := 'Y';
593 END IF;
594
595 k := k + 1;
596
597 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 1, l_assignment_id);
598 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 2, l_original_supplier_id);
599 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 3, l_original_site_id);
600 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 4, l_third_party_agent_id);
601 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 5, l_third_party_site_id);
602 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 6, l_checkrun_name);
603 DBMS_SQL.COLUMN_VALUE(l_row_cursor, 7, l_check_num_fvtpp);
604
605 g_message := ' Assignment Id: '|| l_assignment_id;
606 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
607
608 IF (g_checkrun_name IS NULL)
609 THEN
610 g_message := ' Payment Batch Name: '|| l_checkrun_name;
611 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
612 END IF;
613
614 BEGIN
615 SELECT bank_account_id
616 INTO l_bank_account_id
617 FROM ap_invoice_selection_criteria
618 WHERE checkrun_name = l_checkrun_name;
619
620 g_message := ' Bank Account Id: '|| l_bank_account_id;
621 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
622 EXCEPTION
623 WHEN OTHERS THEN
624 g_retcode := SQLCODE;
625 g_errbuf := SQLERRM ||
626 ' -- Error in Process_Tpp_Check_Detail_Recs Procedure when finding bank_account_id for the Payment Batch';
627 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
628 END;
629
630 FOR l_checks_invoices_rec IN l_checks_invoices_cur
631 LOOP
632 l_check_number := l_checks_invoices_rec.check_number;
633 l_check_date := l_checks_invoices_rec.check_date;
634 l_check_amount := l_checks_invoices_rec.amount;
635 l_invoice_num := l_checks_invoices_rec.invoice_num;
636 l_invoice_amount := l_checks_invoices_rec.invoice_amount;
637 l_discount_amount := l_checks_invoices_rec.discount_amount_taken;
638
639 g_message := ' Check Number: '|| l_check_number;
640 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
641
642 g_message := ' Check Date: '|| l_check_date;
643 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
644
645 g_message := ' Check Amount: '|| l_check_amount;
646 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
647
648 g_message := ' Invoice Number: '|| l_invoice_num;
649 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
650
651 g_message := ' Invoice Amount: '|| l_invoice_amount;
652 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
653
654 g_message := ' Discount Amount Taken: '|| l_discount_amount;
655 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
656
657 -- Set the values for the FOR loop range below, depending
658 -- on the Original/Third Party Agent name and site
659 -- parameter values for Third Party Remittance SRS
660
661 IF (g_from_supp_id IS NULL) OR (g_from_site_id IS NULL) OR
662 (g_to_supp_id IS NULL) OR (g_to_site_id IS NULL)
663 THEN
664 l := 1;
665 j := 2;
666 ELSIF (g_from_supp_id IS NULL) OR (g_from_site_id IS NULL)
667 THEN
668 l := 1;
669 j := 1;
670 ELSIF (g_to_supp_id IS NULL) OR (g_to_site_id IS NULL)
671 THEN
672 l := 2;
673 j := 2;
674 ELSE
675 l := 0;
676 j := 0;
677 l_original_supplier := g_from_supp_name;
678 l_original_site := g_from_site_code;
679 l_third_party_agent := g_to_supp_name;
680 l_third_party_site := g_to_site_code;
681 END IF;
682
683 -- Select the Original and Third Party info
684 -- if not specified as SRS parameters
685
686 IF (l <> 0) AND (j <> 0)
687 THEN
688 FOR i IN l .. j
689 LOOP
690 BEGIN
691 SELECT pv.vendor_name, ps.vendor_site_code
692 INTO l_vendor_name, l_site_code
693 FROM po_vendors pv, po_vendor_sites ps,
694 fv_tpp_assignments_all fv
695 WHERE fv.assignment_id = l_assignment_id
696 AND fv.set_of_books_id = g_sob_id
697 AND fv.org_id = g_org_id
698 AND pv.vendor_id = DECODE(i, 1, fv.original_supplier_id,
699 2, fv.third_party_agent_id)
700 AND ps.vendor_site_id = DECODE(i, 1, fv.original_supplier_site_id,
701 2, fv.third_party_site_id);
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 g_retcode := SQLCODE;
706 IF i = 1
707 THEN
708 g_errbuf := SQLERRM ||
709 ' -- Error in Process_Tpp_Check_Detail_Recs Procedure when finding Original vendor info';
710 ELSIF i = 2
711 THEN
712 g_errbuf := SQLERRM ||
713 ' -- Error in Process_Tpp_Check_Detail_Recs Procedure when finding Third Party vendor info';
714
715 END IF;
716 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_errbuf, 'N');
717 END;
718
719 IF i = 1
720 THEN
721 l_original_supplier := l_vendor_name;
722 l_original_site := l_site_code;
723
724 g_message := ' Original Supplier: '|| l_original_supplier;
725 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
726
727 g_message := ' Original Supplier Site: '|| l_original_site;
728 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
729
730 ELSE
731 l_third_party_agent := l_vendor_name;
732 l_third_party_site := l_site_code;
733
734 g_message := ' Third Party Agent: '|| l_third_party_agent;
735 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
736
737 g_message := ' Third Party Site: '|| l_third_party_site;
738 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
739 END IF;
740 END LOOP;
741 END IF; /* l <> 0 AND j <> 0 */
742
743 POPULATE_THIRD_PARTY_TEMP (l_original_supplier, l_original_site, l_third_party_agent,
744 l_third_party_site, l_check_number, l_check_date,
745 l_check_amount, l_invoice_num, l_invoice_amount,
746 l_discount_amount);
747 END LOOP; /* l_checks_invoices_rec */
748 END LOOP;
749
750 DBMS_SQL.CLOSE_CURSOR(l_row_cursor);
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 g_retcode := SQLCODE;
755 g_errbuf := SQLERRM ||
756 ' -- Error in Process_Tpp_Check_Detail_Recs Procedure';
757 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
758 END PROCESS_TPP_CHECK_DETAIL_RECS;
759
760
761 -- -------------------------------------------------------------
762 -- PROCEDURE SUBMIT_REPORT
763 -- -------------------------------------------------------------
764 -- The purpose of this procedure is to submit Third Party
765 -- Remittance Report.
766 -- -------------------------------------------------------------
767 PROCEDURE SUBMIT_REPORT IS
768 l_module_name VARCHAR2(200) ;
769 l_req_id NUMBER;
770 l_call_status BOOLEAN;
771 l_rphase VARCHAR2(30);
772 l_rstatus VARCHAR2(30);
773 l_dphase VARCHAR2(30);
774 l_dstatus VARCHAR2(30);
775 l_message VARCHAR2(240);
776 BEGIN
777 g_message := '';
778 l_module_name := g_module_name || 'SUBMIT_REPORT';
779 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
780
781 g_message := ' Submitting the Third Party Remittance report ...';
782 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
783
784 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV', 'FVTPPREM', '', '', FALSE,
785 g_sob_id, g_org_id,
786 g_checkrun_name, g_pay_date_from,
787 g_pay_date_to, g_from_supp_name,
788 g_from_site_code, g_to_supp_name,
789 g_to_site_code, g_sort_by);
790
791 g_message := ' Request Id: '|| l_req_id;
792 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
793
794 IF (l_req_id = 0)
795 THEN
796 g_message := ' Cannot submit Third Party Remittance report';
797 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_message, 'N');
798
799 g_retcode := 2;
800 g_errbuf := g_message;
801
802 ROLLBACK;
803 RETURN;
804 ELSE
805 g_message := ' Third Party Remittance report submitted';
806 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
807
808 COMMIT;
809 END IF;
810
811 l_call_status := Fnd_Concurrent.Wait_For_Request(l_req_id, 20, 0, l_rphase , l_rstatus,
812 l_dphase, l_dstatus, l_message);
813
814 IF (l_call_status = FALSE)
815 THEN
816 g_message := ' Cannot wait for the status of Third Party Payments report: '|| l_message;
817 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_message, 'N');
818
819 g_retcode := 2;
820 g_errbuf := g_message;
821 ELSE
822 IF (l_dphase = 'COMPLETE' AND l_dstatus = 'NORMAL')
823 THEN
824 g_message := ' Third Party Payments report completed normal';
825 Log_Message(FND_LOG.LEVEL_STATEMENT,l_module_name,g_message);
826
827 COMMIT;
828 ELSE
829 g_message := ' Third Party Payments report did not complete normally: '|| l_message;
830 Log_Message(FND_LOG.LEVEL_ERROR,l_module_name,g_message, 'N');
831
832 g_retcode := 2;
833 g_errbuf := g_message;
834 END IF;
835 END IF;
836 EXCEPTION
837 WHEN OTHERS THEN
838 g_retcode := SQLCODE;
839 g_errbuf := SQLERRM ||
840 ' -- Error in Submit_Report Procedure';
841 Log_Message(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',g_errbuf, 'N');
842 END SUBMIT_REPORT;
843 BEGIN
844
845 --MOAC changes, to derive the org_id and sob_id
846
847 g_module_name := 'fv.plsql.FV_THIRD_PARTY_REMIT_PKG.';
848 --g_org_id := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
849 g_org_id := MO_GLOBAL.get_current_org_id;
850 --g_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
851 g_debug_flag := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'), 'N');
852 g_tpp_flag := NVL(FND_PROFILE.VALUE('FV_THIRD_PARTY_PAYMENT'), 'N');
853 g_data_found := 'N';
854 MO_UTILS.get_ledger_info(p_operating_unit => g_org_id,
855 p_ledger_id => g_sob_id,
856 p_ledger_name => g_sob_name);
857
858 END FV_THIRD_PARTY_REMIT_PKG;