1 PACKAGE BODY JERX_C_TO AS
2 /* $Header: jegrctob.pls 120.7 2008/05/29 11:57:23 pakumare ship $ */
3
4
5 PROCEDURE GET_TRNOVR_DATA(
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 argument1 in varchar2, -- application short name
9 argument2 in varchar2, -- set of books id
10 argument3 in varchar2, -- GL Period from
11 argument4 in varchar2, -- GL Period To
12 argument5 in varchar2, -- Range Type
13 argument6 in varchar2, -- Cust/Supplier Name From
14 argument7 in varchar2, -- Cust/Supplier Name To
15 argument8 in varchar2, -- Cust/Supplier Number From
16 argument9 in varchar2, -- Cust/Supplier Number To
17 argument10 in varchar2, -- Currency Code
18 argument11 in varchar2, -- invoice amount lower limit
19 argument12 in varchar2, -- balance type +ve/-ve
20 argument13 in varchar2, -- Legal Entity id
21 argument14 in varchar2, ---- Rule id
22 argument15 in varchar2,
23 argument16 in varchar2,
24 argument17 in varchar2,
25 argument18 in varchar2,
26 argument19 in varchar2,
27 argument20 in varchar2,
28 argument21 in varchar2,
29 argument22 in varchar2,
30 argument23 in varchar2,
31 argument24 in varchar2,
32 argument25 in varchar2,
33 argument26 in varchar2,
34 argument27 in varchar2,
35 argument28 in varchar2,
36 argument29 in varchar2,
37 argument30 in varchar2,
38 argument31 in varchar2,
39 argument32 in varchar2,
40 argument33 in varchar2,
41 argument34 in varchar2,
42 argument35 in varchar2,
43 argument36 in varchar2,
44 argument37 in varchar2,
45 argument38 in varchar2,
46 argument39 in varchar2,
47 argument40 in varchar2,
48 argument41 in varchar2,
49 argument42 in varchar2,
50 argument43 in varchar2,
51 argument44 in varchar2,
52 argument45 in varchar2,
53 argument46 in varchar2,
54 argument47 in varchar2,
55 argument48 in varchar2,
56 argument49 in varchar2,
57 argument50 in varchar2,
58 argument51 in varchar2,
59 argument52 in varchar2,
60 argument53 in varchar2,
61 argument54 in varchar2,
62 argument55 in varchar2,
63 argument56 in varchar2,
64 argument57 in varchar2,
65 argument58 in varchar2,
66 argument59 in varchar2,
67 argument60 in varchar2,
68 argument61 in varchar2,
69 argument62 in varchar2,
70 argument63 in varchar2,
71 argument64 in varchar2,
72 argument65 in varchar2,
73 argument66 in varchar2,
74 argument67 in varchar2,
75 argument68 in varchar2,
76 argument69 in varchar2,
77 argument70 in varchar2,
78 argument71 in varchar2,
79 argument72 in varchar2,
80 argument73 in varchar2,
81 argument74 in varchar2,
82 argument75 in varchar2,
83 argument76 in varchar2,
84 argument77 in varchar2,
85 argument78 in varchar2,
86 argument79 in varchar2,
87 argument80 in varchar2,
88 argument81 in varchar2,
89 argument82 in varchar2,
90 argument83 in varchar2,
91 argument84 in varchar2,
92 argument85 in varchar2,
93 argument86 in varchar2,
94 argument87 in varchar2,
95 argument88 in varchar2,
96 argument89 in varchar2,
97 argument90 in varchar2,
98 argument91 in varchar2,
99 argument92 in varchar2,
100 argument93 in varchar2,
101 argument94 in varchar2,
102 argument95 in varchar2,
103 argument96 in varchar2,
104 argument97 in varchar2,
105 argument98 in varchar2,
106 argument99 in varchar2,
107 argument100 in varchar2)
108 IS
109 l_stage_request_id number;
110 l_conc_request_id number;
111 l_set_request_id number;
112 l_period_start_date varchar2(25);
113 l_period_end_date varchar2(25);
114 l_sob_currency_code varchar2(15);
115 l_argument10 varchar2(15);
116 l_argument12 varchar2(2);
117
118 BEGIN
119
120 -- Log All the Parameters received by the program.
121
122 fnd_file.put_line( fnd_file.log,'Application short name : ' || argument1 );
123 fnd_file.put_line( fnd_file.log,'Set of Books ID : ' || argument2 );
124 fnd_file.put_line( fnd_file.log,'GL Period From : ' || argument3 );
125 fnd_file.put_line( fnd_file.log,'GL Period To : ' || argument4 );
126 fnd_file.put_line( fnd_file.log,'Range Type : ' || argument5 );
127 fnd_file.put_line( fnd_file.log,'Cust/Sup Name From : ' || argument6 );
128 fnd_file.put_line( fnd_file.log,'Cust/Sup Name To : ' || argument7 );
129 fnd_file.put_line( fnd_file.log,'Cust/Sup Number From : ' || argument8 );
130 fnd_file.put_line( fnd_file.log,'Cust/Sup Number To : ' || argument9 );
131 fnd_file.put_line( fnd_file.log,'Currency Code : ' || argument10 );
132 fnd_file.put_line( fnd_file.log,'Amount Lower Limit : ' || argument11 );
133 fnd_file.put_line( fnd_file.log,'Amount Sign : ' || argument12 );
134 fnd_file.put_line( fnd_file.log,'Legal Entity id : ' || argument13);
135 fnd_file.put_line( fnd_file.log,'Rule id : ' || argument14 );
136 -- Assign parameters doing any necessary mappings
137 -- e.g. date/number conversion
138
139 l_conc_request_id := fnd_global.conc_request_id;
140
141 SELECT parent_request_id
142 INTO l_stage_request_id
143 FROM fnd_concurrent_requests
144 WHERE request_id = l_conc_request_id;
145
146 SELECT parent_request_id
147 INTO l_set_request_id
148 FROM fnd_concurrent_requests
149 WHERE request_id = l_stage_request_id;
150
151 l_conc_request_id := l_set_request_id;
152
153 fnd_file.put_line(FND_FILE.log,'Parent Request ID :'||to_char(l_conc_request_id));
154
155 -- Get GL Period start date and end date.
156
157 select to_char(gps.start_date,'DD/MM/YYYY')||' 00:00:00',
158 to_char(gps2.end_date,'DD/MM/YYYY')||' 23:59:59',
159 sob.currency_code
160
161 into l_period_start_date,
162 l_period_end_date,
163 l_sob_currency_code
164
165 from gl_period_statuses gps,
166 gl_period_statuses gps2,
167 --- gl_sets_of_books sob,
168 gl_ledgers_public_v sob,
169 fnd_application fa
170 where fa.application_short_name = argument1
171 and fa.application_id = gps.application_id
172 and fa.application_id = gps2.application_id
173 and gps.set_of_books_id = to_number(argument2)
174 --and gps.ledger_id = to_number(argument2)
175 and gps.set_of_books_id = sob.ledger_id
176 --and gps.ledger_id = sob.ledger_id
177 and gps.period_name = argument3
178 and gps.adjustment_period_flag <> 'Y'
179 and gps2.set_of_books_id = to_number(argument2)
180 and gps2.set_of_books_id = sob.ledger_id
181 and gps2.ledger_id = to_number(argument2)
182 and gps2.ledger_id = sob.ledger_id
183 and gps2.period_name = argument4
184 and gps2.adjustment_period_flag <> 'Y';
185
186 -- Validate Year. Year should be same for start year and end year.
187
188 IF ( substr(l_period_start_date,7,4) <> substr(l_period_end_date,7,4)) then
189
190 fnd_file.put_line( fnd_file.log,'Period Start date : '|| l_period_start_date);
191 fnd_file.put_line( fnd_file.log,'Period End date : '|| l_period_end_date);
192 fnd_file.put_line( fnd_file.log,'From Period and To Period should be in the same year');
193 retcode := 2;
194 RETURN;
195
196 END IF;
197
198 -- If Set of books currency code is equal to the parameter's currency code
199 -- or null then initialize currency code. It should consider all transactions.
200
201 IF (l_sob_currency_code = nvl(argument10,l_sob_currency_code) ) then
202
203 l_argument10 := NULL;
204
205 ELSE
206 l_argument10 := argument10;
207
208 END IF;
209
210 -- Argument12 to be converted to sign parameter.
211
212 if ( argument12 = '+' ) then
213 l_argument12 := '1';
214 elsif ( argument12 = '-' ) then
215 l_argument12 := '-1';
216 end if;
217
218 -- Run report based upon application short name passed to the procedure.
219
220 IF (argument1 = 'SQLAP') THEN
221 jerx_to.je_ap_turnover_extract(
222 errbuf,
223 retcode,
224 argument1,
225 argument2,
226 l_period_start_date,
227 l_period_end_date,
228 argument5,
229 argument6,
230 argument7,
231 argument8,
232 argument9,
233 l_argument10,
234 argument14,
235 argument11,
236 l_argument12,
237 l_conc_request_id,
238 to_number(argument13)
239 );
240 END IF;
241
242 IF (argument1 = 'AR') THEN
243 jerx_to.je_ar_turnover_extract(
244 errbuf,
245 retcode,
246 argument1,
247 argument2,
248 l_period_start_date,
249 l_period_end_date,
250 argument5,
251 argument6,
252 argument7,
253 argument8,
254 argument9,
255 l_argument10,
256 argument14,
257 argument11,
258 l_argument12,
259 l_conc_request_id,
260 to_number(argument13)
261 );
262 END IF;
263
264 EXCEPTION
265 WHEN app_exceptions.application_exception THEN
266 retcode := 2;
267 fnd_file.put_line( fnd_file.log,'An application level exception occured in JERX_C_TO package.');
268 WHEN OTHERS THEN
269 retcode := 2;
270 fnd_file.put_line( fnd_file.log,'Trapped other exception occured in JERX_C_TO package.');
271 fnd_file.put_line( fnd_file.log, SQLERRM );
272
273 END GET_TRNOVR_DATA;
274
275 END JERX_C_TO;
[Home] [Help]