[Home] [Help]
PACKAGE BODY: APPS.XTR_EFT_SCRIPT_P
Source
1 PACKAGE BODY XTR_EFT_SCRIPT_P as
2 /* $Header: xtreftsb.pls 120.14 2005/07/29 15:09:36 csutaria ship $ */
3 ---------------------------------------------------------------------------------
4 PROCEDURE CALL_SCRIPTS(l_company IN VARCHAR2,
5 l_cparty IN VARCHAR2,
6 l_account IN VARCHAR2,
7 l_currency IN VARCHAR2,
8 l_script_name VARCHAR2,
9 paydate IN VARCHAR2,
10 l_prev_run IN VARCHAR2,
11 l_transmit_payment IN VARCHAR2,
12 l_transmit_config_id IN VARCHAR2,
13 retcode OUT nocopy NUMBER) is
14
15 -- This script will call all other eft scripts where appropriate based on the scripts selected
16 -- for running on the settlement_scripts table
17 --
18 cursor SEL_SCRIPTS is
19 select distinct substr(script_name,1,4), package_name,eft_script_output_path,
20 transmission_code
21 from XTR_SETTLEMENT_SCRIPTS
22 where script_type = 'SCRIPT'
23 and script_name = l_script_name
24 and authorised = 'Y';
25 -- and run_requested_on is NOT NULL;
26 --
27 v_request_id VARCHAR2(8);
28 cursor SEL_FILE_NAME is
29 select outfile_name
30 from fnd_concurrent_requests
31 where request_id = v_request_id;
32
33 cursor UNIQUE_FILE_NOS is
34 select 1
35 from DUAL;
36 --
37 sett VARCHAR2(1);
38 l_eft_script VARCHAR2(4);
39 l_package VARCHAR2(20);
40 l_path VARCHAR2(50);
41 l_transmit_number NUMBER;
42 l_transmission_code VARCHAR2(100);
43 l_unique_file_nos NUMBER;
44 l_file_name VARCHAR2(100);
45 include_prev_generated_eft VARCHAR2(1);
46 settlement_date DATE;
47 v_procedure_call VARCHAR2(250) := 'BEGIN XTR_EXT_SETTLE_SCRIPTS.';
48 l_request_id number ;
49
50 --
51 begin
52 -- get request id
53 fnd_profile.get('CONC_REQUEST_ID', v_request_id);
54
55 IF xtr_risk_debug_pkg.g_Debug THEN
56 XTR_RISK_DEBUG_PKG.dpush('CALL_SCRIPTS: ' || 'Call scripts');
57 END IF;
58 sett := l_prev_run;
59 -- cep_standard.enable_debug;
60 IF xtr_risk_debug_pkg.g_Debug THEN
61 XTR_RISK_DEBUG_PKG.dlog('>XTR_EFT_SCRIPT_P.call_scripts');
62 END IF;
63 settlement_date := to_date(paydate, 'YYYY/MM/DD HH24:MI:SS');
64
65 IF xtr_risk_debug_pkg.g_Debug THEN
66 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>OPEN SEL_SCRIPTS');
67 END IF;
68 open SEL_SCRIPTS;
69 LOOP
70 IF xtr_risk_debug_pkg.g_Debug THEN
71 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>> LOOP');
72 END IF;
73 fetch SEL_SCRIPTS INTO l_eft_script, l_package, l_path, l_transmission_code; --, sett; RV
74 IF xtr_risk_debug_pkg.g_Debug THEN
75 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>> l_eft_script = '||l_eft_script||
76 ' l_package = '||l_package||
77 ' l_path = '||l_path ||
78 ' sett = '||sett);
79 END IF;
80 EXIT WHEN SEL_SCRIPTS%NOTFOUND;
81 v_procedure_call := v_procedure_call || l_package || '(:a, :b, :c, :d, :e, :f, :g, :h); END;';
82 -- Call the packages based on package name
83 -- Get eft file number
84 open UNIQUE_FILE_NOS;
85 fetch UNIQUE_FILE_NOS INTO l_unique_file_nos;
86 IF xtr_risk_debug_pkg.g_Debug THEN
87 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>> lunique_file_nos = ' ||l_unique_file_nos);
88 END IF;
89 close UNIQUE_FILE_NOS;
90 --
91
92
93
94 -- Updated to grab correct file name
95 --l_file_name := l_path||l_eft_script||to_char(l_unique_file_nos)||'.txt';
96
97 open SEL_FILE_NAME;
98 fetch SEL_FILE_NAME INTO l_file_name;
99 close SEL_FILE_NAME;
100
101 /*select outfile_name from fnd_concurrent_requests
102 into l_file_name
103 where request_id = v_request_id;
104 */
105
106 IF xtr_risk_debug_pkg.g_Debug THEN
107 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>> l_file_name = ' ||l_file_name);
108 END IF;
109 --
110 -- ***** As we add more scripts they need to be added in the if clause below *****
111 if upper(l_package) = 'BNZ_EFT' then
112 IF xtr_risk_debug_pkg.g_Debug THEN
113 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '> Call XTR_EFT_SCRIPT_P.bnz_eft ...');
114 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '> l_company = '|| l_company||
115 ' l_account = '||l_account||
116 ' l_currency = '|| l_currency||
117 ' l_eft_script = '||l_eft_script||
118 ' settlement_date = '||settlement_date||
119 ' include_prev_generated_eft = '||l_prev_run||
120 ' l_file_name = '||l_file_name);
121 END IF;
122
123 XTR_EFT_SCRIPT_P.BNZ_EFT(l_company,l_cparty,l_account, l_currency,l_eft_script,paydate,l_prev_run,l_file_name);
124
125 elsif upper(l_package) = 'SWT_EFT' then
126 XTR_EFT_SCRIPT_P.SWT_EFT(l_company, l_cparty,l_account, l_currency,l_eft_script,paydate,l_prev_run,l_file_name, retcode);
127
128 elsif upper(l_package) = 'X12_EFT' then
129 XTR_EFT_SCRIPT_P.X12_EFT(l_company, l_cparty,l_account, l_currency,l_eft_script,paydate,l_prev_run, l_file_name, retcode);
130
131 else
132 IF xtr_risk_debug_pkg.g_Debug THEN
133 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: procedure call ' || v_procedure_call);
134 end if;
135 EXECUTE IMMEDIATE v_procedure_call
136 USING l_company, l_cparty, l_account, l_currency, l_eft_script, paydate, l_prev_run, l_file_name, retcode;
137 IF xtr_risk_debug_pkg.g_Debug THEN
138 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: procedure call ' || v_procedure_call);
139 end if;
140 end if;
141 END LOOP;
142 IF xtr_risk_debug_pkg.g_Debug THEN
143 XTR_RISK_DEBUG_PKG.dlog('CALL_SCRIPTS: ' || '>out of Loop');
144 END IF;
145 close SEL_SCRIPTS;
146
147 IF xtr_risk_debug_pkg.g_Debug THEN
148 XTR_RISK_DEBUG_PKG.dpop('CALL_SCRIPTS: ' || 'Call scripts');
149 END IF;
150
151 update XTR_SETTLEMENT_SCRIPTS
152 set run_requested_on = trunc(sysdate),
153 last_run_on = trunc(sysdate),
154 last_run_by = fnd_global.user_id,
155 last_file_created = l_file_name
156 where script_name = l_script_name
157 and script_type = 'SCRIPT'
158 and company_code = l_company;
159
160 -- transmit if necessary
161 if (l_transmit_payment = 'Yes') then -- payment transmission project
162
163 l_request_id := FND_REQUEST.SUBMIT_REQUEST('XTR', 'XTRPAYTRANSDEF', '','', TRUE,
164 l_company,l_cparty, l_currency,
165 l_account, l_script_name, l_transmit_payment,
166 paydate, l_prev_run, l_file_name,
167 l_transmit_config_id );
168 IF l_request_id = 0 THEN
169 RAISE APP_EXCEPTION.application_exception;
170 END IF;
171
172 end if;
173
174 end CALL_SCRIPTS;
175 ----------------------------------------------------------------------------------------------------------------
176 PROCEDURE BNZ_EFT (l_company IN VARCHAR2,
177 l_cparty IN VARCHAR2,
178 l_account IN VARCHAR2,
179 l_currency IN VARCHAR2,
180 l_eft_script_name IN VARCHAR2,
181 paydate IN VARCHAR2,
182 sett IN VARCHAR2,
183 l_file_name IN VARCHAR2) is
184 --
185 -- paydate = Date to generate EFT transactions (in char format DDMMYYYY)
186 -- sett = Include transactions previously generated
187 --
188 spce VARCHAR2(1) := ',';
189 comp_acct VARCHAR2(20);
190 comp_acct_ins VARCHAR2(20);
191 deb_header VARCHAR2(255);
192 deb_rec VARCHAR2(255);
193 deb_ctrl VARCHAR2(255);
194 deb_count NUMBER := 0;
195 deb_amt NUMBER := 0;
196 deb_tot_amt NUMBER := 0;
197 deb_hash VARCHAR2(13);
198 --d_hash NUMBER := 0; RV
199 d_hash VARCHAR2(100);
200 deb_acct VARCHAR2(20);
201 cre_header VARCHAR2(255);
202 cre_rec VARCHAR2(255);
203 cre_ctrl VARCHAR2(255);
204 cre_count NUMBER := 0;
205 cre_amt NUMBER := 0;
206 cre_tot_amt NUMBER := 0;
207 cre_hash VARCHAR2(13);
208 --c_hash NUMBER := 0;
209 c_hash VARCHAR2(100);
210 cre_acct VARCHAR2(20);
211 settlement_date DATE;
212 curr NUMBER := 0;
213 mts_details VARCHAR2(255);
214 mts_file_name VARCHAR2(255);
215
216 --
217 -- Header Block
218 cursor HEADER_REC is
219 select distinct d.account_no
220 from XTR_DEAL_DATE_AMOUNTS_V d,
221 XTR_BANK_ACCOUNTS b,
222 XTR_SETTLEMENT_SCRIPTS s
223 -- RV 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
224 where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
225 and d.company_code = l_company
226 and NVL(d.beneficiary_party,d.cparty_code) like nvl(l_cparty,'%')
227 and d.account_no = NVL(l_account, d.account_no)
228 and d.currency = NVL(l_currency, d.currency)
229 and d.trans_mts = 'Y'
230 and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
231 and b.account_number = d.account_no
232 and b.party_code = l_company
233 and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
234 and s.script_name = b.eft_script_name
235 and nvl(s.currency_code,b.currency) = b.currency
236 and s.script_type = 'SCRIPT';
237 --
238 -- Direct Debits
239 cursor DEBIT_REC is
240 select abs(d.settle_amount * 100),
241 d.cparty_account_no,'2'||spce||substr(d.cparty_account_no,1,2)||
242 substr(d.cparty_account_no,4,4)||substr(d.cparty_account_no,9,7)||
243 substr(d.cparty_account_no,17)||spce||'50'||spce||to_char(abs(d.settle_amount * 100))||
244 spce||p.short_name||spce||spce||spce||spce||q.short_name||spce||
245 'Ref '||rtrim(l_file_name)||spce
246 from XTR_EFT_DEBITS_V d,
247 XTR_PARTIES_V p,
248 XTR_PARTIES_V q,
249 XTR_BANK_ACCOUNTS b
250 -- 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
251 where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
252 and d.account_no = comp_acct
253 and d.company_code = l_company
254 and NVL(d.settle_party,d.cparty_code) like nvl(l_cparty,'%')
255 and d.trans_mts = 'Y'
256 and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
257 and q.party_code = d.company_code
258 and p.party_code = d.settle_party
259 and b.account_number = d.account_no
260 and b.party_code = d.company_code;
261 --
262 -- Direct Credits
263 cursor CREDIT_REC is
264 select abs(d.settle_amount * 100),
265 d.cparty_account_no,'2'||spce||substr(d.cparty_account_no,1,2)||
266 substr(d.cparty_account_no,4,4)||substr(d.cparty_account_no,9,7)||
267 substr(d.cparty_account_no,17)||spce||'50'||spce||to_char(abs(d.settle_amount *
268 100))||spce||p.short_name||spce||spce||spce||
269 spce||q.short_name||spce||'Ref '||rtrim(l_file_name)||spce
270 from XTR_EFT_CREDITS_V d,
271 XTR_PARTIES_V p,
272 XTR_PARTIES_V q,
273 XTR_BANK_ACCOUNTS b
274 --2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
275 where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
276 and d.account_no = comp_acct
277 and d.company_code = l_company
278 and NVL(d.settle_party,d.cparty_code) like nvl(l_cparty,'%')
279 and ((upper(sett) = 'Y' ) or (upper(sett) = 'N' and d.settlement_actioned is NULL))
280 and q.party_code = d.company_code
281 and p.party_code = d.settle_party
282 and b.account_number = d.account_no
283 and b.party_code = d.company_code;
284
285
286 --
287 -- fetch from XTR_MTS_RECORDS spool to file
288 --
289 cursor MTS_REC is
290 select transfer_details, file_name
291 from XTR_MTS_RECORDS
292 order by FILE_NAME,CREATED_ON_DATE asc;
293
294 --
295 l_date DATE := sysdate;
296 --
297 begin
298 --
299 IF xtr_risk_debug_pkg.g_Debug THEN
300 XTR_RISK_DEBUG_PKG.dlog('>XTR_SETTLEMENT.bnz_eft');
301 END IF;
302 settlement_date := to_date(paydate, 'YYYY/MM/DD HH24:MI:SS');
303 IF xtr_risk_debug_pkg.g_Debug THEN
304 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>settlement_date = '||settlement_date);
305 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>OPEN Header_Rec');
306 END IF;
307 open HEADER_REC;
308 fetch HEADER_REC INTO comp_acct;
309 IF xtr_risk_debug_pkg.g_Debug THEN
310 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> comp_acct = '|| comp_acct);
311 END IF;
312 while HEADER_REC%FOUND LOOP
313 comp_acct_ins := substr(comp_acct,1,2)||substr(comp_acct,4,4)||substr(comp_acct,9,7)||
314 substr(comp_acct,17);
315 IF xtr_risk_debug_pkg.g_Debug THEN
316 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> OPEN DEBIT_REC');
317 END IF;
318 open DEBIT_REC;
319 IF xtr_risk_debug_pkg.g_Debug THEN
320 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> Fetch Debit_rec ...');
321 END IF;
322 fetch DEBIT_REC into deb_amt,deb_acct,deb_rec;
323
324 if DEBIT_REC%FOUND then
325 IF xtr_risk_debug_pkg.g_Debug THEN
326 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> IF Debit_rec Found');
327 END IF;
328
329 deb_header := '1'||spce||spce||spce||spce||comp_acct_ins||spce||'6'||spce||
330 settlement_date||spce|| sysdate||spce;
331 IF xtr_risk_debug_pkg.g_Debug THEN
332 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>deb_header = '|| deb_header);
333 END IF;
334
335 l_date := l_date + 0.000011;
336 IF xtr_risk_debug_pkg.g_Debug THEN
337 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> l_date = '|| l_date);
338 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>Insert into XTR_MTS_RECORDS');
339 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>l_eft_Script_name = '|| l_eft_script_name||
340 ' cre_header = '||cre_header||
341 ' l_date = '||l_date||
342 ' l_file_name = '|| l_file_name||
343 ' settlement_date = '|| settlement_date);
344 END IF;
345
346 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
347 file_name,settlement_date)
348 values(l_eft_script_name,deb_header,l_date,l_file_name,
349 --2305918 to_date(settlement_date,'DD-MON-RR'));
350 trunc(settlement_date));
351
352 IF xtr_risk_debug_pkg.g_Debug THEN
353 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> End of insertion');
354 END IF;
355 end if;
356 while DEBIT_REC%FOUND LOOP
357 IF xtr_risk_debug_pkg.g_Debug THEN
358 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>> while DEBIT_REC Found ');
359 END IF;
360 deb_tot_amt := deb_tot_amt + deb_amt;
361 deb_count := deb_count + 1;
362 IF xtr_risk_debug_pkg.g_Debug THEN
363 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || 'deb_acct = '||deb_acct);
364 END IF;
368 Begin
365 curr := 1;
366
367 --RV
369 d_hash := d_hash ||substr(substr(deb_acct,1,2)||substr(deb_acct,4,4)||
370 substr(deb_acct,9,7)||substr(deb_acct,17),3,11);
371 Exception
372 When Others then NULL;
373 End;
374
375 curr := 2;
376 l_date := l_date + 0.000011;
377 IF xtr_risk_debug_pkg.g_Debug THEN
378 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>>Insert into XTR_MTS_RECORDS');
379 END IF;
380 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
381 file_name,settlement_date)
382 values(l_eft_script_name,deb_rec,l_date,l_file_name,
383 --RV 2305918 to_date(settlement_date,'DD-MON-RR'));
384 trunc(settlement_date));
385 IF xtr_risk_debug_pkg.g_Debug THEN
386 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>>Fetch...');
387 END IF;
388 fetch DEBIT_REC into deb_amt,deb_acct,deb_rec;
389 END LOOP;
390 IF xtr_risk_debug_pkg.g_Debug THEN
391 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>>Close Debit_Rec');
392 END IF;
393 close DEBIT_REC;
394 if deb_count > 0 then
395 if length(d_hash) < 11 then
396 deb_hash := lpad(d_hash,11,'0');
397 elsif length(d_hash) = 12 then
398 deb_hash := substr(d_hash,2);
399 elsif length(d_hash) = 13 then
400 deb_hash := substr(d_hash,3);
401 elsif length(d_hash) = 14 then
402 deb_hash := substr(d_hash,4);
403 end if;
404 deb_ctrl := '3'||spce||to_char(deb_tot_amt)||spce||to_char(deb_count)||spce||deb_hash;
405 l_date := l_date + 0.000011;
406 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
407 file_name,settlement_date)
408 values(l_eft_script_name,deb_ctrl,l_date,l_file_name,
409 --RV 2305918 to_date(settlement_date,'DD-MON-RR'));
410 trunc(settlement_date));
411
412 end if;
413 deb_count := 0;
414 IF xtr_risk_debug_pkg.g_Debug THEN
415 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>Open CREDIT_REC');
416 END IF;
417 open CREDIT_REC;
418 IF xtr_risk_debug_pkg.g_Debug THEN
419 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>Fetch CREDIT_REC');
420 END IF;
421 fetch CREDIT_REC into cre_amt,cre_acct,cre_rec;
422 if CREDIT_REC%FOUND then
423 cre_header := '1'||spce||spce||spce||spce||comp_acct_ins||spce||'7'||spce||
424 settlement_date||spce||sysdate||spce;
425
426 l_date := l_date + 0.000011;
427 IF xtr_risk_debug_pkg.g_Debug THEN
428 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> l_date = ' || l_date);
429 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> Insert into XTR_MTS_RECORDS');
430 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>l_eft_Script_name = '|| l_eft_script_name||
431 ' cre_header = '||cre_header||
432 ' l_date = '||l_date||
433 ' l_file_name = '|| l_file_name||
434 ' settlement_date = '|| settlement_date);
435 END IF;
436 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
437 file_name,settlement_date)
438 values(l_eft_script_name,cre_header,l_date,l_file_name,
439 -- RV 2305918 to_date(settlement_date,'DD-MON-RR'));
440 trunc(settlement_date));
441 IF xtr_risk_debug_pkg.g_Debug THEN
442 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> End of Insert');
443 END IF;
444 end if;
445
446 --
447 while CREDIT_REC%FOUND LOOP
448 cre_tot_amt := cre_tot_amt + cre_amt;
449 cre_count := cre_count + 1;
450 curr := 1;
451 Begin
452 c_hash := c_hash ||substr(substr(cre_acct,1,2)||substr(cre_acct,4,4)||
453 substr(cre_acct,9,7)||substr(cre_acct,17),3,11);
454 Exception
455 When Others then NULL;
456 End;
457
458 curr := 2;
459 l_date := l_date + 0.000011;
460 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
461 file_name,settlement_date)
462 values(l_eft_script_name,cre_rec,l_date,l_file_name,
463 -- RV 2305918 to_date(settlement_date,'DD-MON-RR'));
464 trunc(settlement_date));
465
466 fetch CREDIT_REC into cre_amt,cre_acct,cre_rec;
467 END LOOP;
468 --
469 close CREDIT_REC;
470 if cre_count > 0 then
471 if length(c_hash) < 11 then
472 cre_hash := lpad(c_hash,11,'0');
473 elsif length(c_hash) = 12 then
474 cre_hash := substr(c_hash,2);
475 elsif length(c_hash) = 13 then
476 cre_hash := substr(c_hash,3);
477 elsif length(c_hash) = 14 then
478 cre_hash := substr(c_hash,4);
479 end if;
480 cre_ctrl := '3'||spce||to_char(cre_tot_amt)||spce||to_char(cre_count)||spce||cre_hash;
481 l_date := l_date + 0.000011;
482 insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
483 file_name,settlement_date)
487
484 values(l_eft_script_name,cre_ctrl,l_date,l_file_name,
485 --RV 2305198 to_date(settlement_date,'DD-MON-RR'));
486 trunc(settlement_date));
488
489 end if;
490 cre_count := 0;
491 --
492 IF xtr_risk_debug_pkg.g_Debug THEN
493 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>UPDATE xtr_settlement_scripts table');
494 END IF;
495 update XTR_SETTLEMENT_SCRIPTS
496 set run_requested_on = null,
497 only_new_transactions = null,
498 --RV 2305918 last_run_on = to_date(settlement_date,'DD-MON-RR'),
499 last_run_on = trunc(settlement_date),
500 last_run_by = fnd_global.user_id,
501 last_file_created = l_file_name
502 where substr(script_name,1,4) = l_eft_script_name
503 and script_type = 'SCRIPT';
504 --
505 IF xtr_risk_debug_pkg.g_Debug THEN
506 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>UPDATE dda table');
507 END IF;
508 update XTR_DEAL_DATE_AMOUNTS d
509 set d.SETTLEMENT_ACTIONED = 'Y',
510 d.SETTLEMENT_ACTIONED_FILE = l_file_name
511 -- RV 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
512 where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
513 and d.cashflow_amount <> 0
514 and d.account_no = comp_acct
515 and d.company_code = l_company
516 and NVL(d.beneficiary_party,d.cparty_code) like nvl(l_cparty,'%')
517 and d.trans_mts = 'Y'
518 and d.settle = 'Y'
519 and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
520 and d.account_no = (select distinct b.account_number
521 from XTR_BANK_ACCOUNTS b,
522 XTR_SETTLEMENT_SCRIPTS s
523 where b.account_number = d.account_no
524 and b.party_code = upper(l_company)
525 and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
526 and s.script_name = b.eft_script_name
527 and nvl(s.currency_code,b.currency) = b.currency
528 and s.script_type = 'SCRIPT');
529
530 --
531 fetch HEADER_REC INTO comp_acct;
532 END LOOP;
533 --
534
535 --
536 -- spool into file
537 --
538 IF xtr_risk_debug_pkg.g_Debug THEN
539 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>OPEN MTS_REC');
540 END IF;
541 OPEN MTS_REC;
542 Fetch MTS_REC into mts_details, mts_file_name;
543 IF xtr_risk_debug_pkg.g_Debug THEN
544 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> mts_details = '||mts_details||
545 ' mts_file_name = '||mts_file_name);
546 END IF;
547
548 WHILE MTS_REC%FOUND LOOP
549 IF xtr_risk_debug_pkg.g_Debug THEN
550 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> LOOP...');
551 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> mts_details = '||mts_details||
552 ' mts_file_name = '||mts_file_name);
553 END IF;
554
555 --FND_FILE.put_names ('eft.log', 'eft.out', '/tmp/');
556 FND_FILE.put_line(FND_FILE.OUTPUT, mts_details);
557 --FND_FILE.new_line(FND_FILE.OUTPUT,1);
558 --
559 -- delete data from XTR_MTS_RECORDS table
560 --
561 DELETE from XTR_MTS_RECORDS
562 WHERE file_name = mts_file_name
563 AND transfer_details = mts_details;
564
565 IF xtr_risk_debug_pkg.g_Debug THEN
566 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>fetch MTS_REC');
567 END IF;
568 Fetch MTS_REC into mts_details, mts_file_name;
569 END LOOP;
570
571 commit;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 IF (curr=1) THEN
576 IF xtr_risk_debug_pkg.g_Debug THEN
577 XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || 'EXCEPTION: Wrong Debit Account Format');
578 END IF;
579 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION: Debit Account is in the wrong format');
580 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION: Correct Format is XX-XXXX-XXXXXXX-XX');
581 RAISE;
582 ELSE
583 IF xtr_risk_debug_pkg.g_Debug THEN
584 XTR_RISK_DEBUG_PKG.dlog('EXCEPTION: XTR_EFT_SCRIPT_P.BNZ_EFT');
585 END IF;
586 RAISE;
587 END IF;
588
589 END BNZ_EFT;
590 ----------------------------------------------------------------------------------------------------------------
591
592
593
594
595
596
597
598
599
600
601
602 PROCEDURE SWT_EFT (l_company IN VARCHAR2,
603 l_cparty IN VARCHAR2,
604 l_account IN VARCHAR2,
605 l_currency IN VARCHAR2,
606 l_eft_script_name IN VARCHAR2,
607 paydate IN VARCHAR2,
608 sett IN VARCHAR2,
609 l_file_name IN VARCHAR2,
610 retcode OUT nocopy NUMBER) is
611
612 v_account_no VARCHAR2(20);
613 v_settlement_number NUMBER;
614 v_company VARCHAR2(7);
615 v_cparty VARCHAR2(7);
616 v_comp_name VARCHAR2(50);
617 v_cp_name VARCHAR2(50);
618 v_currency VARCHAR2(15);
619 v_settlement_amount NUMBER;
623 v_cparty_acct_no VARCHAR2(20);
620 v_settlement_amount_c VARCHAR2(15);
621 v_settlement_date DATE;
622 v_company_acct_no VARCHAR2(20);
624 v_comp_swift_id VARCHAR2(50);
625 v_cp_swift_id VARCHAR2(50);
626 v_comp_address1 VARCHAR2(50);
627 v_comp_address2 VARCHAR2(50);
628 v_comp_address3 VARCHAR2(50);
629 v_cp_address1 VARCHAR2(50);
630 v_cp_address2 VARCHAR2(50);
631 v_cp_address3 VARCHAR2(50);
632 v_comp_bank_code VARCHAR2(7);
633 v_cp_bank_code VARCHAR2(7);
634 v_comp_bank_name VARCHAR2(50);
635 v_cp_bank_name VARCHAR2(50);
636 v_comp_bank_location VARCHAR2(35);
637 v_cp_bank_location VARCHAR2(35);
638 v_comp_bank_street VARCHAR2(35);
639 v_cp_bank_street VARCHAR2(35);
640 v_cp_corr_bank_name VARCHAR2(100);
641 v_cp_corr_bank_no VARCHAR2(20);
642 v_output_date DATE;
643 v_paydate DATE;
644 v_request_id VARCHAR2(8);
645 v_exc_description VARCHAR2(256);
646 v_settlement_summary_id NUMBER;
647 v_netcount NUMBER;
648 v_sett_act NUMBER;
649 v_correct_cp NUMBER;
650 v_group_id NUMBER;
651 -- Gets applicable bank accounts
652 cursor HEADER_REC is
653 select distinct sw.company_acct_no
654 from XTR_SWIFT_EFT_V sw,
655 XTR_BANK_ACCOUNTS b,
656 XTR_SETTLEMENT_SCRIPTS s
657 where sw.settlement_date = NVL(trunc(settlement_date), sw.settlement_date)
658 and sw.company = l_company
659 and sw.cparty = nvl(l_cparty,sw.cparty)
660 and sw.company_acct_no = NVL(l_account, sw.company_acct_no)
661 and sw.currency = NVL(l_currency, sw.currency)
662 and b.account_number = sw.company_acct_no
663 and b.party_code = l_company
664 and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
665 and s.script_name = b.eft_script_name
666 and nvl(s.currency_code,b.currency) = b.currency
667 and s.script_type = 'SCRIPT';
668
669 cursor CREDIT_REC is
670 select s.settlement_number, s.settlement_date, s.currency,
671 abs(s.settlement_amount), comp_name, comp_address1,
672 comp_address2, comp_address3, company_acct_no, comp_swift_id,
673 comp_bank_name, comp_bank_street, comp_bank_location,
674 cparty_acct_no, cp_swift_id, cp_bank_name, cp_bank_street,
675 cp_bank_location, cp_name, cp_address1, cp_address2,
676 cp_address3, cp_corr_bank_name,cp_corr_bank_no,
677 s.settlement_summary_id, s.cparty
678 from XTR_SWIFT_EFT_V s
679 where trunc(s.settlement_date) = trunc(NVL(trunc(v_paydate), s.settlement_date))
680 and s.company_acct_no = v_account_no
681 and s.company = l_company
682 and s.cparty = nvl(l_cparty,s.cparty);
683
684 cursor NET_COUNT is
685 select count(*)
686 from xtr_settlement_summary
687 where net_id = v_settlement_summary_id;
688
689
690 cursor correct_counterparty is
691 select count(*)
692 from xtr_deal_date_amounts x
693 where x.settlement_number = v_settlement_number
694 and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
695 or
696 (x.beneficiary_account_no is null and v_cparty = x.cparty_code));
697
698 cursor correct_counterparty_net is
699 select count(*)
700 from xtr_deal_date_amounts x, xtr_settlement_summary s1,
701 xtr_settlement_summary s2
702 where s1.settlement_number = v_settlement_number
703 and s1.settlement_summary_id = s2.net_id
704 and s2.settlement_number = x.settlement_number
705 and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
706 or
707 (x.beneficiary_account_no is null and v_cparty = x.cparty_code));
708
709 cursor settlement_actioned is
710 select count(*)
711 from xtr_deal_date_amounts x
712 where x.settlement_number = v_settlement_number
713 and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
714
715 cursor settlement_actioned_net is
716 select count(*)
717 from xtr_deal_date_amounts x, xtr_settlement_summary s1,
718 xtr_settlement_summary s2
719 where s1.settlement_number = v_settlement_number
720 and s1.settlement_summary_id = s2.net_id
721 and s2.settlement_number = x.settlement_number
722 and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
723
724 --bug 3195086
725 cursor get_groupid(p_netid VARCHAR2) is
726 SELECT netoff_number
727 FROM xtr_deal_date_amounts
728 where settlement_number = (select settlement_number
729 from xtr_settlement_summary
730 WHERE net_id = p_netid
731 and ROWNUM=1);
732
733 --bug 3195086
734 procedure filter_blanks(p_string varchar2) is
735 begin
736 if (p_string is not null) then
737 FND_FILE.put_line(FND_FILE.OUTPUT, p_string);
738 end if;
739 end filter_blanks;
740
741 begin
742 v_paydate := to_date(paydate, 'YYYY/MM/DD HH24:MI:SS');
743
744 IF xtr_risk_debug_pkg.g_Debug THEN
745 XTR_RISK_DEBUG_PKG.dpush('SWT_EFT');
746 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: prev run is ' || sett);
747 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: l_company is ' || l_company);
748 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: l_cparty is' || l_cparty);
749 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: CHAR Settlement date is ' || paydate);
750 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: Settlement date is ' || v_paydate);
751 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: attempt at varchar ' || to_char( v_paydate, 'YYMMDD'));
752 END IF;
753
754 IF xtr_risk_debug_pkg.g_Debug THEN
758 open HEADER_REC;
755 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: ' || '>OPEN Header_Rec');
756 END IF;
757
759 fetch HEADER_REC INTO v_account_no;
760 IF xtr_risk_debug_pkg.g_Debug THEN
761 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: ' || '> comp_acct = '|| v_account_no);
762 END IF;
763 while HEADER_REC%FOUND LOOP
764
765
766 open CREDIT_REC;
767 IF xtr_risk_debug_pkg.g_Debug THEN
768 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: ' || '>Fetch CREDIT_REC');
769 END IF;
770 fetch CREDIT_REC into v_settlement_number, v_settlement_date, v_currency,
771 v_settlement_amount, v_comp_name, v_comp_address1, v_comp_address2,
772 v_comp_address3, v_company_acct_no, v_comp_swift_id, v_comp_bank_name,
773 v_comp_bank_street, v_comp_bank_location, v_cparty_acct_no,
774 v_cp_swift_id, v_cp_bank_name, v_cp_bank_street, v_cp_bank_location,
775 v_cp_name, v_cp_address1, v_cp_address2, v_cp_address3,
776 v_cp_corr_bank_name, v_cp_corr_bank_no, v_settlement_summary_id,
777 v_cparty;
778
779 --
780 while CREDIT_REC%FOUND LOOP
781 /* Code to take care of the case where settlements have been netted */
782 open NET_COUNT;
783 fetch NET_COUNT into v_netcount;
784 close NET_COUNT;
785 if v_netcount > 0 then
786 open settlement_actioned_net;
787 fetch settlement_actioned_net into v_sett_act;
788 close settlement_actioned_net;
789 else
790 open settlement_actioned;
791 fetch settlement_actioned into v_sett_act;
792 close settlement_actioned;
793 end if;
794
795 if v_netcount > 0 then
796 open correct_counterparty_net;
797 fetch correct_counterparty_net into v_correct_cp;
798 close correct_counterparty_net;
799 else
800 open correct_counterparty;
801 fetch correct_counterparty into v_correct_cp;
802 close correct_counterparty;
803 end if;
804
805 if v_netcount > 0 then
806 open get_groupid(v_settlement_summary_id);
807 fetch get_groupid into v_group_id;
808 close get_groupid;
809 v_settlement_number := nvl(v_group_id,v_settlement_number);
810 end if;
811
812
813 if v_sett_act > 0 and v_correct_cp > 0 then
814
815 IF xtr_risk_debug_pkg.g_Debug THEN
816 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: inside credit rec settlement number ' || v_settlement_number );
817 END IF;
818 FND_FILE.put_line(FND_FILE.OUTPUT, ':20:' || v_settlement_number);
819
820 v_settlement_amount_c := replace(to_char(v_settlement_amount), '.', ',');
821
822 if INSTR(v_settlement_amount_c, ',') = 0 THEN
823 IF xtr_risk_debug_pkg.g_Debug THEN
824 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: adding comma' );
825 END IF;
826 v_settlement_amount_c := v_settlement_amount_c || ',';
827 end if;
828
829
830
831
832 FND_FILE.put_line(FND_FILE.OUTPUT, ':32A:' || to_char( v_settlement_date, 'YYMMDD') || v_currency || v_settlement_amount_c);
833 IF xtr_risk_debug_pkg.g_Debug THEN
834 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: ' || ':32A:' || to_char( v_settlement_date, 'YYMMDD') || v_currency || v_settlement_amount_c);
835 END IF;
836
837 FND_FILE.put_line(FND_FILE.OUTPUT, ':50:' || substr(v_comp_name, 1, 35));
838 --Bug 3195086
839 FILTER_BLANKS(substr(v_comp_address1, 1, 35));
840 FILTER_BLANKS(substr(v_comp_address2, 1, 35));
841 FILTER_BLANKS(substr(v_comp_address3, 1, 35));
842 IF xtr_risk_debug_pkg.g_Debug THEN
843 XTR_RISK_DEBUG_PKG.dlog('SWT_EFT: ' ||':50:' || substr(v_comp_name, 1, 35));
844 END IF;
845
846 --Bug 3195086: FND_FILE.put_line(FND_FILE.OUTPUT, ':53B:/' || v_company_acct_no);
847
848 if ((v_cp_corr_bank_name IS NOT NULL) AND (v_cp_corr_bank_no IS NOT NULL)) THEN
849 FND_FILE.put_line(FND_FILE.OUTPUT, ':54A:' || v_cp_corr_bank_name);
850 FND_FILE.put_line(FND_FILE.OUTPUT, v_cp_corr_bank_no);
851
852 END IF;
853
854 if v_cp_swift_id is null then
855 FND_FILE.put_line(FND_FILE.OUTPUT, ':57D:/' || v_cparty_acct_no);
856 FND_FILE.put_line(FND_FILE.OUTPUT, v_cp_bank_name);
857 FND_FILE.put_line(FND_FILE.OUTPUT, v_cp_bank_street);
858 FND_FILE.put_line(FND_FILE.OUTPUT, v_cp_bank_location);
859 FND_FILE.put_line(FND_FILE.OUTPUT, '');
860 else
861 FND_FILE.put_line(FND_FILE.OUTPUT, ':57A:/' || v_cparty_acct_no);
862 FND_FILE.put_line(FND_FILE.OUTPUT, v_cp_swift_id);
863 end if;
864
865 FND_FILE.put_line(FND_FILE.OUTPUT, ':59:/' || v_cparty_acct_no);
866 --Bug 3195086
867 FILTER_BLANKS(substr(v_cp_name, 1, 35));
868 FILTER_BLANKS(substr(v_cp_address1, 1, 35));
869 FILTER_BLANKS(substr(v_cp_address2, 1, 35));
870 FILTER_BLANKS(substr(v_cp_address3, 1, 35));
871
872 FND_FILE.put_line(FND_FILE.OUTPUT, ':70:/ROC/' || v_settlement_number);
873
874 FND_FILE.put_line(FND_FILE.OUTPUT, '');
875
876 fnd_profile.get('CONC_REQUEST_ID', v_request_id);
877
878 if v_netcount > 0 then
879 update XTR_DEAL_DATE_AMOUNTS d
880 set d.SETTLEMENT_ACTIONED = 'Y',
881 d.SETTLEMENT_ACTIONED_FILE = l_file_name
882 where SETTLEMENT_NUMBER in
883 (select settlement_number from xtr_settlement_summary x
884 where x.net_id = v_settlement_summary_id);
885 else
886 update XTR_DEAL_DATE_AMOUNTS d
887 set d.SETTLEMENT_ACTIONED = 'Y',
888 d.SETTLEMENT_ACTIONED_FILE = l_file_name
889 where SETTLEMENT_NUMBER = v_settlement_number;
890 end if;
894 v_settlement_amount, v_comp_name, v_comp_address1, v_comp_address2,
891
892 end if;
893 fetch CREDIT_REC into v_settlement_number, v_settlement_date, v_currency,
895 v_comp_address3, v_company_acct_no, v_comp_swift_id, v_comp_bank_name,
896 v_comp_bank_street, v_comp_bank_location, v_cparty_acct_no,
897 v_cp_swift_id, v_cp_bank_name, v_cp_bank_street, v_cp_bank_location,
898 v_cp_name, v_cp_address1, v_cp_address2, v_cp_address3,
899 v_cp_corr_bank_name, v_cp_corr_bank_no, v_settlement_summary_id,
900 v_cparty;
901 END LOOP;
902
903 close CREDIT_REC;
904
905
906
907 fetch HEADER_REC INTO v_account_no;
908 END LOOP;
909
910 close HEADER_REC;
911 IF xtr_risk_debug_pkg.g_Debug THEN
912 XTR_RISK_DEBUG_PKG.dpop('SWT_EFT');
913 END IF;
914
915 commit;
916
917 END SWT_EFT;
918
919
920 PROCEDURE X12_EFT (l_company IN VARCHAR2,
921 l_cparty IN VARCHAR2,
922 l_account IN VARCHAR2,
923 l_currency IN VARCHAR2,
924 l_eft_script_name IN VARCHAR2,
925 paydate IN VARCHAR2,
926 sett IN VARCHAR2,
927 l_file_name IN VARCHAR2,
928 retcode OUT nocopy NUMBER) is
929
930
931 v_account_no VARCHAR2(20);
932 v_settlement_number NUMBER;
933 v_company VARCHAR2(7);
934 v_cparty VARCHAR2(7);
935 v_comp_name VARCHAR2(50);
936 v_cp_name VARCHAR2(50);
937 v_currency VARCHAR2(15);
938 v_settlement_amount NUMBER;
939 v_settlement_amount_c VARCHAR2(15);
940 v_settlement_date DATE;
941 v_company_acct_no VARCHAR2(20);
942 v_cparty_acct_no VARCHAR2(20);
943 v_comp_swift_id VARCHAR2(50);
944 v_cp_swift_id VARCHAR2(50);
945 v_comp_address1 VARCHAR2(50);
946 v_comp_address2 VARCHAR2(50);
947 v_comp_address3 VARCHAR2(50);
948 v_cp_address1 VARCHAR2(50);
949 v_cp_address2 VARCHAR2(50);
950 v_cp_address3 VARCHAR2(50);
951 v_comp_bank_code VARCHAR2(7);
952 v_cp_bank_code VARCHAR2(7);
953 v_comp_bank_name VARCHAR2(50);
954 v_cp_bank_name VARCHAR2(50);
955 v_comp_bank_location VARCHAR2(35);
956 v_cp_bank_location VARCHAR2(35);
957 v_comp_bank_street VARCHAR2(35);
958 v_cp_bank_street VARCHAR2(35);
959 v_exc_desc VARCHAR2(256);
960 v_output_date DATE;
961 v_paydate DATE;
962 v_request_id VARCHAR2(8);
963 v_settlement_summary_id NUMBER;
964 v_netcount NUMBER;
965 v_sett_act NUMBER;
966 v_correct_cp NUMBER;
967 v_prompt VARCHAR2(100);
968
969 -- Gets applicable bank accounts
970 cursor HEADER_REC is
971 select distinct sw.company_acct_no
972 from XTR_SWIFT_EFT_V sw,
973 XTR_BANK_ACCOUNTS b,
974 XTR_SETTLEMENT_SCRIPTS s
975 where sw.settlement_date = NVL(trunc(settlement_date), sw.settlement_date)
976 and sw.company = l_company
977 and sw.cparty = nvl(l_cparty,sw.cparty)
978 and sw.company_acct_no = NVL(l_account, sw.company_acct_no)
979 and sw.currency = NVL(l_currency, sw.currency)
980 and b.account_number = sw.company_acct_no
981 and b.party_code = l_company
982 and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
983 and s.script_name = b.eft_script_name
984 and nvl(s.currency_code,b.currency) = b.currency
985 and s.script_type = 'SCRIPT';
986
987 cursor CREDIT_REC is
988 select s.settlement_number, s.settlement_date, s.currency,
989 abs(s.settlement_amount), comp_name, comp_address1,
990 comp_address2, comp_address3, company_acct_no, comp_swift_id,
991 comp_bank_name, comp_bank_street, comp_bank_location,
992 cparty_acct_no, cp_swift_id, cp_bank_name, cp_bank_street,
993 cp_bank_location, cp_name, cp_address1, cp_address2,
994 cp_address3, s.company, s.cparty, s.settlement_summary_id
995 from XTR_SWIFT_EFT_V s
996 where trunc(s.settlement_date) = trunc(NVL(trunc(v_paydate), s.settlement_date))
997 and s.company_acct_no = v_account_no
998 and s.company = l_company
999 and s.cparty = nvl(l_cparty,s.cparty);
1000
1001 cursor NET_COUNT is
1002 select count(*)
1003 from xtr_settlement_summary x
1004 where x.net_id = v_settlement_summary_id;
1005
1006 cursor settlement_actioned is
1007 select count(*)
1008 from xtr_deal_date_amounts x
1009 where x.settlement_number = v_settlement_number
1010 and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
1011
1012 cursor settlement_actioned_net is
1013 select count(*)
1014 from xtr_deal_date_amounts x, xtr_settlement_summary s1,
1015 xtr_settlement_summary s2
1016 where s1.settlement_number = v_settlement_number
1017 and s1.settlement_summary_id = s2.net_id
1018 and s2.settlement_number = x.settlement_number
1019 and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
1020
1021 cursor correct_counterparty is
1022 select count(*)
1023 from xtr_deal_date_amounts x
1024 where x.settlement_number = v_settlement_number
1025 and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
1026 or
1027 (x.beneficiary_account_no is null and v_cparty = x.cparty_code));
1028
1029 cursor correct_counterparty_net is
1030 select count(*)
1031 from xtr_deal_date_amounts x, xtr_settlement_summary s1,
1032 xtr_settlement_summary s2
1036 and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
1033 where s1.settlement_number = v_settlement_number
1034 and s1.settlement_summary_id = s2.net_id
1035 and s2.settlement_number = x.settlement_number
1037 or
1038 (x.beneficiary_account_no is null and v_cparty = x.cparty_code));
1039
1040 cursor company_address_prompt is
1041 SELECT text
1042 FROM xtr_sys_languages_tl
1043 WHERE module_name='XTRSECOM'
1044 AND item_name='PTY.ADDRESS_1'
1045 AND LANGUAGE=USERENV('lang');
1046
1047 cursor cparty_address_prompt is
1048 SELECT text
1049 FROM xtr_sys_languages_tl
1050 WHERE module_name='XTRSECPY'
1051 AND item_name='PTY.P_ADDRESS_1'
1052 AND LANGUAGE=USERENV('lang');
1053
1054 begin
1055
1056
1057
1058 v_paydate := to_date(paydate, 'YYYY/MM/DD HH24:MI:SS');
1059
1060 IF xtr_risk_debug_pkg.g_Debug THEN
1061 XTR_RISK_DEBUG_PKG.dpush('X12_EFT');
1062 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: prev run is ' || sett);
1063 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: l_company is ' || l_company);
1064 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: l_cparty is' || l_cparty);
1065 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: CHAR Settlement date is ' || paydate);
1066 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: Settlement date is ' || v_paydate);
1067
1068 END IF;
1069
1070 IF xtr_risk_debug_pkg.g_Debug THEN
1071 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: ' || '>OPEN Header_Rec');
1072 END IF;
1073
1074 open HEADER_REC;
1075 fetch HEADER_REC INTO v_account_no;
1076
1077 IF xtr_risk_debug_pkg.g_Debug THEN
1078 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: ' || '> comp_acct = '|| v_account_no);
1079 END IF;
1080 while HEADER_REC%FOUND LOOP
1081
1082
1083 open CREDIT_REC;
1084 IF xtr_risk_debug_pkg.g_Debug THEN
1085 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: ' || '>Fetch CREDIT_REC');
1086 END IF;
1087 fetch CREDIT_REC into v_settlement_number, v_settlement_date, v_currency,
1088 v_settlement_amount, v_comp_name, v_comp_address1, v_comp_address2,
1089 v_comp_address3, v_company_acct_no, v_comp_swift_id, v_comp_bank_name,
1090 v_comp_bank_street, v_comp_bank_location, v_cparty_acct_no,
1091 v_cp_swift_id, v_cp_bank_name, v_cp_bank_street, v_cp_bank_location,
1092 v_cp_name, v_cp_address1, v_cp_address2, v_cp_address3, v_company,
1093 v_cparty, v_settlement_summary_id;
1094
1095 --
1096 while CREDIT_REC%FOUND LOOP
1097
1098 /* Code to take care of the case where settlements have been netted */
1099 open NET_COUNT;
1100 fetch NET_COUNT into v_netcount;
1101 close NET_COUNT;
1102
1103 if v_netcount > 0 then
1104 open settlement_actioned_net;
1105 fetch settlement_actioned_net into v_sett_act;
1106 close settlement_actioned_net;
1107 else
1108 open settlement_actioned;
1109 fetch settlement_actioned into v_sett_act;
1110 close settlement_actioned;
1111 end if;
1112
1113 if v_netcount > 0 then
1114 open correct_counterparty_net;
1115 fetch correct_counterparty_net into v_correct_cp;
1116 close correct_counterparty_net;
1117 else
1118 open correct_counterparty;
1119 fetch correct_counterparty into v_correct_cp;
1120 close correct_counterparty;
1121 end if;
1122
1123 if v_sett_act > 0 and v_correct_cp > 0 then
1124
1125 -- If settlement amount is more that 99,999,999.99 this format is not valid
1126 IF v_settlement_amount > 99999999.99 THEN
1127 FND_MESSAGE.set_name('XTR', 'XTR_X12_EXCEED_AMOUNT');
1128 FND_MESSAGE.set_token('SETT_NO',v_settlement_number);
1129 v_exc_desc := FND_MESSAGE.get;
1130 retcode := 1;
1131 FND_FILE.put_line(FND_FILE.LOG, v_exc_desc);
1132
1133 -- Must have a swift id set up for company's bank account
1134 ELSIF v_comp_swift_id is null THEN
1135 FND_MESSAGE.set_name('XTR', 'XTR_X12_NO_COMP_SID');
1136 FND_MESSAGE.set_token('COMP', v_comp_name);
1137 FND_MESSAGE.set_token('BANK_ACC',v_company_acct_no);
1138 FND_MESSAGE.set_token('SETT_NO',v_settlement_number);
1139 v_exc_desc := FND_MESSAGE.get;
1140 retcode:=1;
1141 FND_FILE.put_line(FND_FILE.LOG, v_exc_desc);
1142
1143 ELSIF v_cp_swift_id is null THEN
1144 FND_MESSAGE.set_name('XTR', 'XTR_X12_NO_CP_SID');
1145 FND_MESSAGE.set_token('CP', v_cp_name);
1146 FND_MESSAGE.set_token('BANK_ACC',v_cparty_acct_no);
1147 FND_MESSAGE.set_token('SETT_NO',v_settlement_number);
1148 v_exc_desc := FND_MESSAGE.get;
1149 retcode:=1;
1150 FND_FILE.put_line(FND_FILE.LOG, v_exc_desc);
1151
1152 --bug 3185544
1153 ELSIF v_comp_address1 is null THEN
1154 FND_MESSAGE.set_name('XTR', 'XTR_X12_NO_COMP_ADDRESS');
1155 open company_address_prompt;
1156 fetch company_address_prompt into v_prompt;
1157 close company_address_prompt;
1158 FND_MESSAGE.set_token('ADDR_TAG',v_prompt);
1159 FND_MESSAGE.set_token('COMP', v_comp_name);
1160 FND_MESSAGE.set_token('SETT_NO',v_settlement_number);
1161 v_exc_desc := FND_MESSAGE.get;
1162 retcode:=1;
1163 FND_FILE.put_line(FND_FILE.LOG, v_exc_desc);
1164
1165 --bug 3185544
1166 ELSIF v_cp_address1 is null THEN
1167 FND_MESSAGE.set_name('XTR', 'XTR_X12_NO_CP_ADDRESS');
1168 open cparty_address_prompt;
1169 fetch cparty_address_prompt into v_prompt;
1173 FND_MESSAGE.set_token('SETT_NO',v_settlement_number);
1170 close cparty_address_prompt;
1171 FND_MESSAGE.set_token('ADDR_TAG',v_prompt);
1172 FND_MESSAGE.set_token('COMP', v_cp_name);
1174 v_exc_desc := FND_MESSAGE.get;
1175 retcode:=1;
1176 FND_FILE.put_line(FND_FILE.LOG, v_exc_desc);
1177
1178 ELSE
1179
1180 IF xtr_risk_debug_pkg.g_Debug THEN
1181 XTR_RISK_DEBUG_PKG.dlog('X12_EFT: inside credit rec settlement number ' || v_settlement_number );
1182 END IF;
1183
1184 FND_FILE.put_line(FND_FILE.OUTPUT, 'ST*820*' || v_settlement_number);
1185 FND_FILE.put_line(FND_FILE.OUTPUT, 'BPR*D*'|| v_settlement_amount ||
1186 '*C*FWT**02*' || v_comp_swift_id || '*DA*'|| v_company_acct_no
1187 || '**' || v_company || '*02*' || v_cp_swift_id || '*DA*' ||
1188 v_cparty_acct_no || '*' || to_char( v_settlement_date, 'YYYYMMDD') );
1189 FND_FILE.put_line(FND_FILE.OUTPUT, 'CUR*PR*' || v_currency);
1190
1191 FND_FILE.put_line(FND_FILE.OUTPUT, 'N1*PR*' || v_comp_name);
1192 FND_FILE.put_line(FND_FILE.OUTPUT, 'N3*' || v_comp_address1 || '*'
1193 || v_comp_address2);
1194
1195 FND_FILE.put_line(FND_FILE.OUTPUT, 'N1*PE*' || v_cp_name);
1196 FND_FILE.put_line(FND_FILE.OUTPUT, 'N3*' || v_cp_address1 || '*'
1197 || v_cp_address2);
1198 FND_FILE.put_line(FND_FILE.OUTPUT, 'SE*8*' || v_settlement_number);
1199
1200
1201
1202 fnd_profile.get('CONC_REQUEST_ID', v_request_id);
1203
1204 if v_netcount > 0 then
1205 update XTR_DEAL_DATE_AMOUNTS d
1206 set d.SETTLEMENT_ACTIONED = 'Y',
1207 d.SETTLEMENT_ACTIONED_FILE = l_file_name
1208 where SETTLEMENT_NUMBER in
1209 (select settlement_number from xtr_settlement_summary x
1210 where x.net_id = v_settlement_summary_id);
1211 else
1212 update XTR_DEAL_DATE_AMOUNTS d
1213 set d.SETTLEMENT_ACTIONED = 'Y',
1214 d.SETTLEMENT_ACTIONED_FILE = l_file_name
1215 where SETTLEMENT_NUMBER = v_settlement_number;
1216 end if;
1217 END IF;
1218 end if;
1219 fetch CREDIT_REC into v_settlement_number, v_settlement_date, v_currency,
1220 v_settlement_amount, v_comp_name, v_comp_address1, v_comp_address2,
1221 v_comp_address3, v_company_acct_no, v_comp_swift_id, v_comp_bank_name,
1222 v_comp_bank_street, v_comp_bank_location, v_cparty_acct_no,
1223 v_cp_swift_id, v_cp_bank_name, v_cp_bank_street, v_cp_bank_location,
1224 v_cp_name, v_cp_address1, v_cp_address2, v_cp_address3, v_company,
1225 v_cparty, v_settlement_summary_id;
1226 END LOOP;
1227
1228 close CREDIT_REC;
1229
1230
1231
1232 fetch HEADER_REC INTO v_account_no;
1233 END LOOP;
1234
1235 close HEADER_REC;
1236 IF xtr_risk_debug_pkg.g_Debug THEN
1237 XTR_RISK_DEBUG_PKG.dpop('X12_EFT');
1238 END IF;
1239
1240 commit;
1241
1242
1243
1244 END X12_EFT;
1245
1246
1247
1248
1249
1250
1251
1252 end XTR_EFT_SCRIPT_P;