[Home] [Help]
PACKAGE BODY: APPS.XTR_SETTLEMENT
Source
1 PACKAGE BODY XTR_SETTLEMENT as
2 /* $Header: xtrsettb.pls 120.8 2005/07/29 15:01:42 csutaria ship $ */
3 ---------------------------------------------------------------------------------
4
5
6 PROCEDURE SETTLEMENT_SCRIPTS(errbuf OUT nocopy VARCHAR2,
7 retcode OUT nocopy NUMBER,
8 l_company IN VARCHAR2,
9 l_paydate IN VARCHAR2,
10 l_setl_amt_from NUMBER,
11 l_setl_amt_to NUMBER,
12 l_account IN VARCHAR2,
13 l_currency IN VARCHAR2,
14 l_script_name IN VARCHAR2,
15 l_cparty IN VARCHAR2,
16 l_prev_run IN VARCHAR2,
17 l_display_debug IN VARCHAR2,
18 l_transmit_payment IN VARCHAR2,
19 l_transmit_config_id IN VARCHAR2 ) IS
20
21
22 script_name VARCHAR2(20);
23 script_type VARCHAR2(20);
24 package_name VARCHAR2(50);
25 req_id NUMBER;
26 request_id NUMBER;
27 reqid VARCHAR2(30);
28 number_of_copies number;
29 printer VARCHAR2(30);
30 print_style VARCHAR2(30);
31 save_output_flag VARCHAR2(30);
32 save_output_bool BOOLEAN;
33 settlement_date DATE;
34 --
35 -- This script will determine what Reports or EFT Scripts to call
36 -- based on the parameters submitted by the user
37 --
38 cursor HEADER_REC is
39 select distinct s.script_name, s.script_type, s.package_name
40 from XTR_DEAL_DATE_AMOUNTS_V dda,
41 XTR_BANK_ACCOUNTS ba,
42 XTR_SETTLEMENT_SCRIPTS s
43 WHERE trunc(dda.actual_settlement_date) = trunc(NVL(Settlement_date,dda.actual_settlement_date))
44 and dda.company_code = NVL(l_company, dda.company_code)
45 and NVL(dda.beneficiary_party,dda.cparty_code) like NVL(l_cparty,'%')
46 and dda.amount >= NVL(l_setl_amt_from, dda.amount)
47 and dda.amount <= NVL(l_setl_amt_to, dda.amount)
48 and dda.account_no = NVL(l_account, dda.account_no)
49 and dda.currency = NVL(l_currency, dda.currency)
50 and dda.trans_mts = 'Y'
51 and ((upper(l_prev_run) = 'Y') or (upper(l_prev_run) = 'N'
52 and dda.settlement_actioned is NULL))
53 and ba.account_number = dda.account_no
54 and ba.party_code = NVL(l_company, ba.party_code)
55 and ba.eft_script_name = NVL(l_script_name, ba.eft_script_name)
56 and s.company_code = dda.company_code
57 and s.script_name = ba.eft_script_name
58 and nvl(s.currency_code,ba.currency) = ba.currency;
59
60 BEGIN
61
62 xtr_risk_debug_pkg.start_conc_prog;
63
64 IF xtr_risk_debug_pkg.g_Debug THEN
65 XTR_RISK_DEBUG_PKG.dpush('SELTTLEMENT_SCRIPTS: ' || 'Settlement scripts');
66 END IF;
67
68 IF l_display_debug = 'Y' THEN
69 -- cep_standard.enable_debug;
70 xtr_debug_pkg.enable_file_debug;
71 END IF;
72 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
73 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>XTR_SETTLEMENT.settlement_script');
74 END IF;
75 settlement_date := to_date(l_paydate, 'YYYY/MM/DD HH24:MI:SS');
76
77 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
78 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>OPEN Header_Rec');
79 END IF;
80 OPEN HEADER_REC;
81 FETCH HEADER_REC INTO script_name, script_type, package_name;
82 WHILE HEADER_REC%FOUND LOOP
83
84 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
85 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>> Inside Loop... ');
86 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>> script_name = '|| script_name
87 || ' script_type = '||script_type
88 || ' package_name = ' || package_name );
89 END IF;
90 --
91 -- Loop thru and generate reports or scripts for each account
92 --
93
94 --
95 -- if script type is report then run report
96 -- else run script
97 --
98 IF (script_type = 'REPORT') THEN
99 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
100 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>> REPORT');
101 END IF;
102 --
103 -- Get original request id
104 --
105 fnd_profile.get('CONC_REQUEST_ID', reqid);
106 request_id := to_number(reqid);
107 --
108 -- Get print options
109 --
110 --* bug#2844888, rravunny
111 --* Here the second concurrent program XTRSTDAY may not necessarily be of the same format as
112 --* parent concurrent program XTRSETTL.
113 --* Hence we cannot default the print options used for parent into the child request.
114 --* commenting out the code which does that.
115 --*
116 --* IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
117 --* number_of_copies,
118 --* print_style,
119 --* printer,
120 --* save_output_flag))THEN
121 --* IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
122 --* xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || 'Message: get print options failed');
123 --* END IF;
124 --* ELSE
125 --* IF (save_output_flag = 'Y') THEN
126 --* save_output_bool := TRUE;
127 --* ELSE
128 --* save_output_bool := FALSE;
129 --* END IF;
130 --
131 -- Set print options
132 --
133 --* xtr_debug_pkg.debug('values ='||number_of_copies||' - '||print_style||' - '||printer||' - '||save_output_flag);
134 --* IF (NOT FND_REQUEST.set_print_options(printer,
135 --* print_style,
136 --* number_of_copies,
137 --* save_output_bool)) THEN
138 --* IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
139 --* xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || 'Set print options failed');
140 --* END IF;
141 --* END IF;
142 --* END IF;
143 req_id := FND_REQUEST.SUBMIT_REQUEST('XTR',
144 package_name,
145 NULL,
146 trunc(sysdate),
147 FALSE,
148 l_company,
149 l_cparty,
150 l_currency,
151 l_account,
152 l_paydate,
153 l_prev_run,
154 l_display_debug);
155
156 COMMIT;
157
158 IF (req_id = 0) THEN
159 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
160 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || 'ERROR submitting concurrent request');
161 END IF;
162 ELSE
163 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
164 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || 'EXECUTION REPORT SUBMITTED');
165 END IF;
166 END IF;
167
168 ELSIF (script_type = 'SCRIPT') THEN -- if it is EFT script
169 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
170 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>> EFT Script');
171 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '>> l_company = '|| l_company
172 || ' package_name = '|| package_name
173 || ' Settlement_date = '|| Settlement_date
174 || ' l_paydate = ' || l_paydate);
175 END IF;
176
177 --
178 -- Call concurrent program to generate EFT scripts
179 --
180 XTR_EFT_SCRIPT_P.call_scripts( l_company,
181 l_cparty,
182 l_account,
183 l_currency,
184 script_name,
185 l_paydate,
186 l_prev_run,
187 l_transmit_payment,
188 l_transmit_config_id,
189 retcode);
190
191 -- added transmit id for payments uptake project
192
193
194 END IF;
195
196 FETCH HEADER_REC INTO script_name, script_type, package_name;
197
198 END LOOP; -- end header_rec loop thru scripts
199 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
200 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '> END LOOP ');
201 xtr_debug_pkg.debug('SETTLEMENT_SCRIPTS: ' || '> Close Cursor XTR_SETTLEMENT HEADER_REC ');
202 END IF;
203 close HEADER_REC; -- close cursor
204
205 IF (l_display_debug = 'Y') THEN
206 --cep_standard.disable_debug;
207 xtr_debug_pkg.disable_file_debug;
208 END IF;
209
210 IF xtr_risk_debug_pkg.g_Debug THEN
211 XTR_RISK_DEBUG_PKG.dpop('SELTTLEMENT_SCRIPTS: ' || 'Settlement scripts');
212 end if;
213
214 xtr_risk_debug_pkg.stop_conc_debug;
215
216 END SETTLEMENT_SCRIPTS;
217 -----------------------------------------------------------------------------------
218
219 END XTR_SETTLEMENT;