1 PACKAGE BODY gl_movemerge_requests_pkg As
2 /* $Header: glimmrqb.pls 120.9 2005/08/18 06:54:43 adesu ship $ */
3 --
4 --
5 -- PRIVATE DATA DECLARATIONS
6 --
7
8 -- Throw away number value
9 dumdum NUMBER;
10
11 --
12 -- PRIVATE METHODS
13 --
14
15 --
16 -- Function
17 -- get_unique_id
18 -- Purpose
19 -- returns nextval from gl_movemerge_requests_s
20 -- Parameters
21 -- None
22 -- Notes
23 --
24 FUNCTION get_unique_id RETURN NUMBER IS
25 CURSOR get_new_id IS
26 SELECT gl_movemerge_requests_s.NEXTVAL
27 FROM dual;
28 new_id number;
29 BEGIN
30 OPEN get_new_id;
31 FETCH get_new_id INTO new_id;
32 IF get_new_id%FOUND THEN
33 CLOSE get_new_id;
34 return(new_id);
35 ELSE
36 CLOSE get_new_id;
37 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
38 fnd_message.set_token('SEQUENCE', 'GL_MOVEMERGE_REQUESTS_S');
39 app_exception.raise_exception;
40 END IF;
41 EXCEPTION
42 WHEN app_exceptions.application_exception THEN
43 RAISE;
44 WHEN OTHERS THEN
45 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
46 fnd_message.set_token(
47 'PROCEDURE',
48 'gl_movemerge_requests_pkg.get_unique_id');
49 RAISE;
50 END get_unique_id;
51
52 --
53 -- PUBLIC METHODS
54 --
55
56 PROCEDURE must_have_accounts (mm_id IN NUMBER) IS
57 CURSOR count_rows IS
58 SELECT 1
59 FROM DUAL
60 WHERE EXISTS (SELECT 1
61 FROM gl_movemerge_accounts
62 WHERE movemerge_request_id = mm_id);
63 BEGIN
64 OPEN count_rows;
65 FETCH count_rows INTO dumdum;
66 IF count_rows%NOTFOUND THEN
67 CLOSE count_rows;
68 FND_MESSAGE.set_name('SQLGL', 'GL_MM_REQUEST_WITHOUT_ACCOUNTS');
69 APP_EXCEPTION.raise_exception;
70 ELSE
71 CLOSE count_rows;
72 END IF;
73 EXCEPTION
74 WHEN app_exceptions.application_exception THEN
75 RAISE;
76 WHEN OTHERS THEN
77 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
78 fnd_message.set_token('PROCEDURE', 'gl_mmwkb_pkg.must_have_accounts');
79 RAISE;
80 END must_have_accounts;
81
82
83 PROCEDURE delete_all_accounts (mm_id IN NUMBER) IS
84 BEGIN
85 DELETE FROM gl_movemerge_accounts
86 WHERE movemerge_request_id = mm_id;
87 EXCEPTION
88 WHEN app_exceptions.application_exception THEN
89 RAISE;
90 WHEN OTHERS THEN
91 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
92 fnd_message.set_token(
93 'PROCEDURE',
94 'gl_movemerge_requests_pkg.delete_all_accounts');
95 RAISE;
96 END delete_all_accounts;
97
98 --+replace ledger_id with chart_of_accounts_id in the SQL cursor
99 --+in 11ix, ledger_id is null if mass creation is selected.
100 --+whereas, chart_of_accounts_id is not null
101 PROCEDURE check_unique_name(X_rowid VARCHAR2,
102 X_coaid NUMBER,
103 X_name VARCHAR2) IS
104 CURSOR name_count_new_row IS
105 SELECT 1
106 FROM DUAL
107 WHERE EXISTS (SELECT 1
108 FROM gl_movemerge_requests
109 WHERE name = X_name
110 AND chart_of_accounts_id = X_coaid);
111
112 CURSOR name_count_old_row IS
113 SELECT 1
114 FROM DUAL
115 WHERE EXISTS (SELECT 1
116 FROM gl_movemerge_requests r1, gl_movemerge_requests r2
117 WHERE r1.name = X_name
118 AND r1.chart_of_accounts_id = X_coaid
119 AND r1.rowid <> X_rowid
120 AND r2.rowid = X_rowid
121 AND nvl(r1.original_movemerge_request_id, -1)
122 <> nvl(r2.original_movemerge_request_id, -1)
123 AND nvl(r1.ledger_id,-1) <> nvl(r2.ledger_id,-1));
124 BEGIN
125 IF (X_rowid IS NULL) THEN
126 OPEN name_count_new_row;
127 FETCH name_count_new_row INTO dumdum;
128 IF name_count_new_row%FOUND THEN
129 CLOSE name_count_new_row;
130 FND_MESSAGE.set_name('SQLGL', 'GL_DUPLICATE_NAME');
131 APP_EXCEPTION.raise_exception;
132 ELSE
133 CLOSE name_count_new_row;
134 END IF;
135 ELSE
136 OPEN name_count_old_row;
137 FETCH name_count_old_row INTO dumdum;
138 IF name_count_old_row%FOUND THEN
139 CLOSE name_count_old_row;
140 FND_MESSAGE.set_name('SQLGL', 'GL_DUPLICATE_NAME');
141 APP_EXCEPTION.raise_exception;
142 ELSE
143 CLOSE name_count_old_row;
144 END IF;
145 END IF;
146 EXCEPTION
147 WHEN app_exceptions.application_exception THEN
148 RAISE;
149 WHEN OTHERS THEN
150 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
151 fnd_message.set_token(
152 'PROCEDURE',
153 'gl_movemerge_requests_pkg.check_unique_name');
154 RAISE;
155 END check_unique_name;
156
157
158 PROCEDURE pre_insert(X_rowid VARCHAR2,
159 X_coaid NUMBER,
160 X_name VARCHAR2) IS
161 BEGIN
162 check_unique_name(X_rowid, X_coaid, X_name);
163 END pre_insert;
164
165 --
166 -- Function
167 -- validate_segments
168 -- Purpose
169 -- Calls validate_segs from FND_FLEX_KEYVAL
170 -- Parameters
171 -- ops_string(operation to be performed)
172 -- concatseg(accounting information)
173 -- coaid (charts of accounts id)
174 -- Notes
175 --
176
177 FUNCTION validate_segments (ops_string IN VARCHAR2,
178 concatseg IN VARCHAR2,
179 coaid IN NUMBER) RETURN VARCHAR2 IS
180 retval BOOLEAN := FALSE;
181 BEGIN
182 retval := FND_FLEX_KEYVAL.validate_segs(
183 ops_string,
184 'SQLGL',
185 'GL#',
186 coaid,
187 concatseg,
188 'V', SYSDATE, 'ALL', '', '',
189 '', '', FALSE, FALSE, '', '', '');
190 IF (retval) THEN
191 RETURN('SUCCESS');
192 ELSE
193 RETURN('FAILURE');
194 END IF;
195 END ;
196
197 PROCEDURE check_last_opened_period (ledgerid IN NUMBER)
198 IS
199 target_ledger_name VARCHAR2(30);
200
201 CURSOR ledger_id_cur IS
202 SELECT rel.target_ledger_name
203 FROM gl_ledgers lgr, gl_ledger_relationships rel, gl_ledgers alc
204 WHERE lgr.ledger_id = ledgerid
205 AND rel.source_ledger_id = lgr.ledger_id
206 AND rel.target_ledger_category_code = 'ALC'
207 AND rel.relationship_type_code <> 'BALANCE'
208 AND rel.application_id = 101
209 AND alc.ledger_id = rel.target_ledger_id
210 AND nvl(alc.latest_opened_period_name,'X') <> nvl(lgr.latest_opened_period_name,'X')
211 ORDER BY target_ledger_name;
212 BEGIN
213 OPEN ledger_id_cur;
214 LOOP
215 FETCH ledger_id_cur INTO target_ledger_name;
216 EXIT WHEN ledger_id_cur%NOTFOUND;
217 FND_MESSAGE.set_name('SQLGL', 'GLMM0115');
218 FND_MESSAGE.set_token('ALC_NAME', target_ledger_name);
219 APP_EXCEPTION.raise_exception;
220 EXIT;
221 END LOOP;
222 CLOSE ledger_id_cur;
223
224 EXCEPTION
225 WHEN app_exceptions.application_exception THEN
226 RAISE;
227 WHEN OTHERS THEN
228 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
229 fnd_message.set_token(
230 'PROCEDURE',
231 'gl_movemerge_requests_pkg.check_last_opened_period');
232 RAISE;
233 END ;
234
235 PROCEDURE get_mm_ledger_id (ledgerid IN NUMBER,
236 mm_ledger_id IN OUT NOCOPY NUMBER)
237 IS
238 object_type gl_ledgers.object_type_code%TYPE;
239 CURSOR ledger_type IS
240 SELECT object_type_code
241 FROM gl_ledgers led
242 WHERE led.ledger_id = ledgerid;
243
244 CURSOR ledger_id_cursor IS
245 SELECT ledger_id
246 FROM gl_ledger_set_assignments
247 WHERE ledger_set_id = ledgerid;
248
249 BEGIN
250 mm_ledger_id := 0; --in case this ledger set contains no ledger at all
251 OPEN ledger_type;
252 FETCH ledger_type INTO object_type;
253 IF object_type = 'S' THEN
254 OPEN ledger_id_cursor;
255 LOOP
256 FETCH ledger_id_cursor INTO mm_ledger_id;
257 EXIT WHEN ledger_id_cursor%NOTFOUND;
258 IF mm_ledger_id <> 0 THEN
259 EXIT;
260 END IF;
261 END LOOP;
262 CLOSE ledger_id_cursor;
263 CLOSE ledger_type;
264 ELSE
265 CLOSE ledger_type;
266 mm_ledger_id := ledgerid;
267 END IF;
268
269 EXCEPTION
270 WHEN app_exceptions.application_exception THEN
271 RAISE;
272 WHEN OTHERS THEN
273 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
274 fnd_message.set_token(
275 'PROCEDURE',
276 'gl_movemerge_requests_pkg.get_mm_ledger_id');
277 RAISE;
278 END ;
279
280 End gl_movemerge_requests_pkg;
[Home] [Help]