[Home] [Help]
PACKAGE BODY: APPS.OKL_SYSTEM_ACCT_OPT_PVT
Source
1 PACKAGE BODY OKL_SYSTEM_ACCT_OPT_PVT AS
2 /* $Header: OKLRSYOB.pls 120.4 2006/12/11 15:25:36 rgooty noship $ */
3
4 -- R12 SLA Uptake : Begin
5 G_INVALID_ADO_MSG CONSTANT VARCHAR2(200) := 'OKL_INVALID_ACC_DERIVATION_OPT';
6 G_FROM_ADO CONSTANT VARCHAR2(30) := 'FROM_ADO';
7 G_TO_ADO CONSTANT VARCHAR2(30) := 'TO_ADO';
8 G_ADO_LOOKUP_TYPE CONSTANT VARCHAR2(30) := 'OKL_ACCOUNT_DERIVATION_OPTION';
9 G_ADO_ATS CONSTANT VARCHAR2(30) := 'ATS';
10 G_ADO_AMB CONSTANT VARCHAR2(30) := 'AMB';
11
12 FUNCTION GET_LOOKUP_MEANING( p_lookup_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
13 ,p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE)
14 RETURN VARCHAR
15 IS
16 CURSOR fnd_lookup_csr( p_lookup_type fnd_lookups.lookup_type%type
17 ,p_lookup_code fnd_lookups.lookup_code%type)
18 IS
19 SELECT MEANING
20 FROM FND_LOOKUPS FND
21 WHERE FND.LOOKUP_TYPE = p_lookup_type
22 AND FND.LOOKUP_CODE = p_lookup_code;
23
24 l_return_value VARCHAR2(200) := OKL_API.G_MISS_CHAR;
25 BEGIN
26 IF ( p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL )
27 THEN
28 OPEN fnd_lookup_csr( p_lookup_type, p_lookup_code );
29 FETCH fnd_lookup_csr INTO l_return_value;
30 CLOSE fnd_lookup_csr;
31 END IF;
32 return l_return_value;
33 END;
34 -- R12 SLA Uptake : End
35
36 PROCEDURE GET_SYSTEM_ACCT_OPT(p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_set_of_books_id IN NUMBER,
42 x_saov_rec OUT NOCOPY saov_rec_type)
43
44 IS
45
46 l_api_name CONSTANT VARCHAR2(40) := 'GET_SYSTEM_ACCT_OPT';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_row_count NUMBER;
49 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
50 l_saov_rec saov_rec_type;
51 l_stmt VARCHAR2(3500);
52 l_org_id NUMBER;
53 TYPE ref_cursor IS REF CURSOR;
54 sys_acct_csr ref_cursor;
55
56
57 BEGIN
58
59 x_return_status := OKL_API.G_RET_STS_SUCCESS;
60
61 l_stmt := ' SELECT ID
62 ,CC_REP_CURRENCY_CODE
63 ,CODE_COMBINATION_ID
64 ,AEL_REP_CURRENCY_CODE
65 ,SET_OF_BOOKS_ID
66 ,OBJECT_VERSION_NUMBER
67 ,REC_CCID
68 ,REALIZED_GAIN_CCID
69 ,REALIZED_LOSS_CCID
70 ,TAX_CCID
71 ,CROSS_CURRENCY_CCID
72 ,ROUNDING_CCID
73 ,AR_CLEARING_CCID
74 ,PAYABLES_CCID
75 ,LIABLITY_CCID
76 ,PRE_PAYMENT_CCID
77 ,FUT_DATE_PAY_CCID
78 ,CC_ROUNDING_RULE
79 ,CC_PRECISION
80 ,CC_MIN_ACCT_UNIT
81 ,DIS_TAKEN_CCID
82 ,AP_CLEARING_CCID
83 ,AEL_ROUNDING_RULE
84 ,AEL_PRECISION
85 ,AEL_MIN_ACCT_UNIT
86 ,ORG_ID
87 ,ATTRIBUTE_CATEGORY
88 ,ATTRIBUTE1
89 ,ATTRIBUTE2
90 ,ATTRIBUTE3
91 ,ATTRIBUTE4
92 ,ATTRIBUTE5
93 ,ATTRIBUTE6
94 ,ATTRIBUTE7
95 ,ATTRIBUTE8
96 ,ATTRIBUTE9
97 ,ATTRIBUTE10
98 ,ATTRIBUTE11
99 ,ATTRIBUTE12
100 ,ATTRIBUTE13
101 ,ATTRIBUTE14
102 ,ATTRIBUTE15
103 ,CREATED_BY
104 ,CREATION_DATE
105 ,LAST_UPDATED_BY
106 ,LAST_UPDATE_DATE
107 ,LAST_UPDATE_LOGIN
108 ,CC_APPLY_ROUNDING_DIFFERENCE
109 ,AEL_APPLY_ROUNDING_DIFFERENCE
110 ,LKE_HOLD_DAYS
111 --Added by Keerthi 12-Sep-2003
112 ,STM_APPLY_ROUNDING_DIFFERENCE
113 ,STM_ROUNDING_RULE
114 --Added new field for bug 4884618(H)
115 ,VALIDATE_KHR_START_DATE
116 ,ACCOUNT_DERIVATION -- R12 SLA Uptake
117 FROM OKL_SYS_ACCT_OPTS ';
118
119 OPEN sys_acct_csr FOR l_stmt;
120
121 FETCH sys_acct_csr INTO
122 l_saov_rec.ID
123 ,l_saov_rec.CC_REP_CURRENCY_CODE
124 ,l_saov_rec.CODE_COMBINATION_ID
125 ,l_saov_rec.AEL_REP_CURRENCY_CODE
126 ,l_saov_rec.SET_OF_BOOKS_ID
127 ,l_saov_rec.OBJECT_VERSION_NUMBER
128 ,l_saov_rec.REC_CCID
129 ,l_saov_rec.REALIZED_GAIN_CCID
130 ,l_saov_rec.REALIZED_LOSS_CCID
131 ,l_saov_rec.TAX_CCID
132 ,l_saov_rec.CROSS_CURRENCY_CCID
133 ,l_saov_rec.ROUNDING_CCID
134 ,l_saov_rec.AR_CLEARING_CCID
135 ,l_saov_rec.PAYABLES_CCID
136 ,l_saov_rec.LIABLITY_CCID
137 ,l_saov_rec.PRE_PAYMENT_CCID
138 ,l_saov_rec.FUT_DATE_PAY_CCID
139 ,l_saov_rec.CC_ROUNDING_RULE
140 ,l_saov_rec.CC_PRECISION
141 ,l_saov_rec.CC_MIN_ACCT_UNIT
142 ,l_saov_rec.DIS_TAKEN_CCID
143 ,l_saov_rec.AP_CLEARING_CCID
144 ,l_saov_rec.AEL_ROUNDING_RULE
145 ,l_saov_rec.AEL_PRECISION
146 ,l_saov_rec.AEL_MIN_ACCT_UNIT
147 ,l_saov_rec.ORG_ID
148 ,l_saov_rec.ATTRIBUTE_CATEGORY
149 ,l_saov_rec.ATTRIBUTE1
150 ,l_saov_rec.ATTRIBUTE2
151 ,l_saov_rec.ATTRIBUTE3
152 ,l_saov_rec.ATTRIBUTE4
153 ,l_saov_rec.ATTRIBUTE5
154 ,l_saov_rec.ATTRIBUTE6
155 ,l_saov_rec.ATTRIBUTE7
156 ,l_saov_rec.ATTRIBUTE8
157 ,l_saov_rec.ATTRIBUTE9
158 ,l_saov_rec.ATTRIBUTE10
159 ,l_saov_rec.ATTRIBUTE11
160 ,l_saov_rec.ATTRIBUTE12
161 ,l_saov_rec.ATTRIBUTE13
162 ,l_saov_rec.ATTRIBUTE14
163 ,l_saov_rec.ATTRIBUTE15
164 ,l_saov_rec.CREATED_BY
165 ,l_saov_rec.CREATION_DATE
166 ,l_saov_rec.LAST_UPDATED_BY
167 ,l_saov_rec.LAST_UPDATE_DATE
168 ,l_saov_rec.LAST_UPDATE_LOGIN
169 ,l_saov_rec.CC_APPLY_ROUNDING_DIFFERENCE
170 ,l_saov_rec.AEL_APPLY_ROUNDING_DIFFERENCE
171 ,l_saov_rec.LKE_HOLD_DAYS
172 -- Added by Keerthi 10-Sep-2003 for Rounding of Amounts in Streams
173 ,l_saov_rec.STM_APPLY_ROUNDING_DIFFERENCE
174 ,l_saov_rec.STM_ROUNDING_RULE
175 --Added new field for bug 4746246
176 ,l_saov_rec.VALIDATE_KHR_START_DATE
177 ,l_saov_rec.ACCOUNT_DERIVATION; -- R12 SLA Uptake
178
179 CLOSE sys_acct_csr;
180
181 x_saov_rec := l_saov_rec;
182
183 EXCEPTION
184
185 WHEN OTHERS THEN
186 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
187
188 END GET_SYSTEM_ACCT_OPT;
189
190
191
192
193 PROCEDURE UPDT_SYSTEM_ACCT_OPT(p_api_version IN NUMBER,
194 p_init_msg_list IN VARCHAR2,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2,
198 p_saov_rec IN saov_rec_type,
199 x_saov_rec OUT NOCOPY saov_rec_type)
200 IS
201
202 l_api_name CONSTANT VARCHAR2(40) := 'UPDT_SYSTEM_ACCT_OPT';
203 l_api_version CONSTANT NUMBER := 1.0;
204 l_row_count NUMBER;
205 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
206
207 l_init_msg_list VARCHAR2(1);
208 l_msg_count NUMBER;
209 l_msg_data VARCHAR2(2000);
210
211 l_saov_rec_in saov_rec_type;
212 l_saov_rec_out saov_rec_type;
213
214 BEGIN
215
216 x_return_status := OKL_API.G_RET_STS_SUCCESS;
217
218 l_return_status := OKL_API.START_ACTIVITY( l_api_name
219 ,g_pkg_name
220 ,p_init_msg_list
221 ,l_api_version
222 ,p_api_version
223 ,'_PVT'
224 ,x_return_status);
225
226 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
228 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
229 RAISE OKL_API.G_EXCEPTION_ERROR;
230 END IF;
231
232 OKL_SYSTEM_ACCT_OPT_PVT.GET_SYSTEM_ACCT_OPT(p_api_version => 1.0,
233 p_init_msg_list => l_init_msg_list,
234 x_return_status => l_return_status,
235 x_msg_count => l_msg_count,
236 x_msg_data => l_msg_data,
237 p_set_of_books_id => p_saov_rec.set_of_books_id,
238 x_saov_rec => l_saov_rec_out);
239
240 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
241 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
242 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
243 RAISE OKL_API.G_EXCEPTION_ERROR;
244 END IF;
245
246 IF (l_saov_rec_out.ID = OKL_API.G_MISS_NUM) OR
247 (l_saov_rec_out.ID IS NULL) THEN
248
249 OKL_SYS_ACCT_OPTS_PUB.INSERT_SYS_ACCT_OPTS(p_api_version => 1.0,
250 p_init_msg_list => l_init_msg_list,
251 x_return_status => l_return_status,
252 x_msg_count => l_msg_count,
253 x_msg_data => l_msg_data,
254 p_saov_rec => p_saov_rec,
255 x_saov_rec => l_saov_rec_out);
256
257 ELSE
258
259 l_saov_rec_in := p_saov_rec;
260 -- R12 SLA Uptake : Begin
261 -- Restrict the updation of the Account Derivation from AMB to ATS
262 -- as only one time upgradation is possible from ATS to AMB.
263 IF l_saov_rec_out.account_derivation = G_ADO_AMB AND
264 l_saov_rec_in.account_derivation = G_ADO_ATS
265 THEN
266 -- Set the Error message
267 OKL_API.SET_MESSAGE(
268 p_app_name => G_APP_NAME
269 ,p_msg_name => G_INVALID_ADO_MSG
270 ,p_token1 => G_FROM_ADO
271 ,p_token1_value => GET_LOOKUP_MEANING(
272 G_ADO_LOOKUP_TYPE,
273 l_saov_rec_out.account_derivation)
274 ,p_token2 => G_TO_ADO
275 ,p_token2_value => GET_LOOKUP_MEANING(
276 G_ADO_LOOKUP_TYPE,
277 l_saov_rec_in.account_derivation));
278 -- Set the return status with Error
279 x_return_status := Okc_Api.G_RET_STS_ERROR;
280 -- Raise the Exception
281 RAISE OKL_API.G_EXCEPTION_ERROR;
282 END IF;
283 -- R12 SLA Uptake : End
284 IF (p_saov_rec.id = OKL_API.G_MISS_NUM) THEN
285 l_saov_rec_in.id := l_saov_rec_out.id;
286 END IF;
287
288 OKL_SYS_ACCT_OPTS_PUB.UPDATE_SYS_ACCT_OPTS(p_api_version => 1.0,
289 p_init_msg_list => l_init_msg_list,
290 x_return_status => l_return_status,
291 x_msg_count => l_msg_count,
292 x_msg_data => l_msg_data,
293 p_saov_rec => l_saov_rec_in,
294 x_saov_rec => l_saov_rec_out);
295
296
297 END IF;
298
299 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
300 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
301 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
302 RAISE OKL_API.G_EXCEPTION_ERROR;
303 END IF;
304
305 x_saov_rec := l_saov_rec_out;
306
307 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
308
309
310 EXCEPTION
311
312 WHEN OKL_API.G_EXCEPTION_ERROR THEN
313 x_return_status := OKL_API.HANDLE_EXCEPTIONS
314 (l_api_name,
315 G_PKG_NAME,
316 'OKL_API.G_RET_STS_ERROR',
317 x_msg_count,
318 x_msg_data,
319 '_PVT');
320 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
321 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
322 ( l_api_name,
323 G_PKG_NAME,
324 'OKL_API.G_RET_STS_UNEXP_ERROR',
325 x_msg_count,
326 x_msg_data,
327 '_PVT');
328 WHEN OTHERS THEN
329 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
330 ( l_api_name,
331 G_PKG_NAME,
332 'OTHERS',
333 x_msg_count,
334 x_msg_data,
335 '_PVT');
336 END UPDT_SYSTEM_ACCT_OPT;
337
338 END OKL_SYSTEM_ACCT_OPT_PVT;