[Home] [Help]
PACKAGE BODY: APPS.GMF_ALLOCATIONDEFINITION_PVT
Source
1 PACKAGE BODY GMF_AllocationDefinition_PVT AS
2 /* $Header: GMFVALCB.pls 120.2 2005/11/02 04:19:16 jboppana noship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMFPALCS.pls |
11 --| Package Name : GMF_AllocationDefinition_PVT |
12 --| API name : GMF_AllocationDefinition_PVT |
13 --| Type : Private |
14 --| Pre-reqs : N/A |
15 --| Function : Allocation Definition creation, updatation and |
16 --| deletetion. |
17 --| Parameters : N/A |
18 --| |
19 --| Current Vers : 3.0 |
20 --| Previous Vers : 2.0 |
21 --| Initial Vers : 1.0 |
22 --| |
23 --| Contents |
24 --| Create_Allocation_Definition |
25 --| Update_Allocation_Definition |
26 --| Delete_Allocation_Definition |
27 --| |
28 --| Notes |
29 --| This package contains public functions relating to Allocation |
30 --| Definition creation, updatation and deletetion DMLs. |
31 --| |
32 --| HISTORY |
33 --| 27/Feb/2001 Uday Moogala Created Bug# 1418689 |
34 --| |
35 --| 30/Oct/2002 R.Sharath Kumar Bug# 2641405 Added NOCOPY hint |
36 --| 05/NOV/2002 Uday Moogala Bug# 2659435 |
37 --| Performance related fixes. |
38 --| 1. remove G_MISS_xxx assignments. |
39 --| 2. Conditionally calling debug routine. |
40 --| Also, fixed issues found during unit testing. Search for the bug |
41 --| number to find the fixes. |
42 --| 24/DEC/2002 Uday Moogala Bug# 2722404 |
43 --| Removed creation_date and created_by from update stmts. |
44 --+==========================================================================+
45 -- End of comments
46
47 -- Global variables
48 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_AllocationDefinition_PVT';
49
50 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
51 -- to decide to log a debug msg.
52
53
54
55 PROCEDURE log_msg -- Bug 2659435: Removed first paramter for debug level
56 (
57 p_msg_text IN VARCHAR2
58 );
59
60 --Start of comments
61 --+========================================================================+
62 --| API Name : Create_Allocation_Definition |
63 --| TYPE : Public |
64 --| Function : Creates a new Allocation Definition based on the input |
65 --| into table GL_ALOC_BAS |
66 --| Pre-reqa : None. |
67 --| Parameters : |
68 --| IN : |
69 --| p_api_version IN NUMBER - Required |
70 --| p_init_msg_list IN VARCHAR2 - Optional |
71 --| p_commit IN VARCHAR2 - Optional |
72 --| p_allocation_definition_rec |
73 --| IN GMF_ALLOCATIONDEFINITION_PUB.Allocation_Definition_Rec_Type |
74 --| OUT : |
75 --| x_return_status OUT VARCHAR2 |
76 --| x_msg_count OUT NUMBER |
77 --| x_msg_data OUT VARCHAR2 |
78 --| |
79 --| Version : |
80 --| Current Version : 3.0 |
81 --| Previous Version : 2.0 |
82 --| Initial Version : 1.0 |
83 --| |
84 --| Notes : |
85 --| |
86 --| HISTORY |
87 --| 01-Mar-01 Uday Moogala - Created |
88 --| |
89 --+========================================================================+
90 -- End of comments
91
92 PROCEDURE Create_Allocation_Definition
93 (
94 p_api_version IN NUMBER ,
95 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
96 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
97
98 x_return_status OUT NOCOPY VARCHAR2 ,
99 x_msg_count OUT NOCOPY NUMBER ,
100 x_msg_data OUT NOCOPY VARCHAR2 ,
101
102 p_allocation_definition_rec IN GMF_ALLOCATIONDEFINITION_PUB.Allocation_Definition_Rec_Type,
103 p_user_id IN NUMBER -- Bug 2659435 Removed defaults
104 )
105 IS
106
107 l_api_name CONSTANT VARCHAR2(30) := 'Create_Allocation_Definition' ;
108 l_api_version CONSTANT NUMBER := 3.0 ;
109 l_line_no gl_aloc_bas.line_no%TYPE ;
110
111 BEGIN
112
113 -- Standard Start of API savepoint
114 SAVEPOINT Create_Alloc_Definition_PVT;
115
116 -- Initialize message list if p_init_msg_list is set to TRUE.
117 IF FND_API.to_Boolean( p_init_msg_list ) THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120
121 -- Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
123 p_api_version ,
124 l_api_name ,
125 G_PKG_NAME )
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 -- Initialize API return status to success
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133
134 -- Generate line_no for the alloc_id
135 SELECT NVL(MAX(line_no), 0)+1
136 INTO l_line_no
137 FROM gl_aloc_bas
138 WHERE alloc_id = p_allocation_definition_rec.alloc_id ;
139
140 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
141 log_msg('Inserting record for alloc_id : ' ||
142 p_allocation_definition_rec.alloc_id || ' line_no : ' || l_line_no);
143 END IF;
144
145 INSERT INTO gl_aloc_bas
146 (
147 alloc_id
148 , line_no
149 , alloc_method
150 , inventory_item_id
151 , basis_account_id
152 , balance_type
153 , bas_ytd_ptd
154 , basis_type
155 , fixed_percent
156 , cmpntcls_id
157 , analysis_code
158 , organization_id
159 , creation_date
160 , created_by
161 , last_update_date
162 , last_updated_by
163 , last_update_login
164 , trans_cnt
165 , text_code
166 , delete_mark
167 )
168 VALUES
169 (
170 p_allocation_definition_rec.alloc_id
171 , l_line_no
172 , p_allocation_definition_rec.alloc_method
173 , p_allocation_definition_rec.item_id
174 , p_allocation_definition_rec.basis_account_id
175 , p_allocation_definition_rec.balance_type
176 , p_allocation_definition_rec.bas_ytd_ptd
177 , p_allocation_definition_rec.basis_type
178 , p_allocation_definition_rec.fixed_percent
179 , p_allocation_definition_rec.cmpntcls_id
180 , p_allocation_definition_rec.analysis_code
181 , p_allocation_definition_rec.organization_id
182 , sysdate
183 , p_user_id
184 , sysdate
185 , p_user_id
186 , FND_GLOBAL.LOGIN_ID
187 , '' -- transaction count (not in use)
188 , '' -- text code
189 , 0 -- p_allocation_definition_rec.delete_mark
190 )
191 ;
192
193 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
194 log_msg('1 row inserted');
195 END IF;
196
197 -- Standard call to get message count and if count is 1, get message info.
198 FND_MSG_PUB.Count_And_Get
199 ( p_count => x_msg_count ,
200 p_data => x_msg_data
201 );
202
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR THEN
205 ROLLBACK TO Create_Alloc_Definition_PVT;
206 x_return_status := FND_API.G_RET_STS_ERROR ;
207 FND_MSG_PUB.Count_And_Get
208 ( p_count => x_msg_count ,
209 p_data => x_msg_data
210 );
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 ROLLBACK TO Create_Alloc_Definition_PVT;
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
214 FND_MSG_PUB.Count_And_Get
215 ( p_count => x_msg_count ,
216 p_data => x_msg_data
217 );
218 WHEN OTHERS THEN
219 ROLLBACK TO Create_Alloc_Definition_PVT;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
221 IF FND_MSG_PUB.Check_Msg_Level
222 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
223 THEN
224 FND_MSG_PUB.Add_Exc_Msg
225 ( G_PKG_NAME ,
226 l_api_name
227 );
228 END IF;
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count ,
231 p_data => x_msg_data
232 );
233
234 END Create_Allocation_Definition;
235
236
237 --Start of comments
238 --+========================================================================+
239 --| API Name : Update_Allocation_Definition |
240 --| TYPE : Public |
241 --| Function : Updates Allocation Definition based on the input |
242 --| into GL_ALOC_BAS |
243 --| Pre-reqa : None. |
244 --| Parameters : |
245 --| IN : |
246 --| p_api_version IN NUMBER - Required |
247 --| p_init_msg_list IN VARCHAR2 - Optional |
248 --| p_commit IN VARCHAR2 - Optional |
249 --| p_allocation_definition_rec |
250 --| IN GMF_ALLOCATIONDEFINITION_PUB.Allocation_Definition_Rec_Type |
251 --| OUT : |
252 --| x_return_status OUT VARCHAR2 |
253 --| x_msg_count OUT NUMBER |
254 --| x_msg_data OUT VARCHAR2 |
255 --| |
256 --| Version : |
257 --| Current Version : 3.0 |
258 --| Previous Version : 2.0 |
259 --| Initial Version : 1.0 |
260 --| |
261 --| Notes : |
262 --| |
263 --| HISTORY |
264 --| 01-Mar-01 Uday Moogala - Created |
265 --| |
266 --+========================================================================+
267 -- End of comments
268
269 PROCEDURE Update_Allocation_Definition
270 (
271 p_api_version IN NUMBER ,
272 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
273 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
274
275 x_return_status OUT NOCOPY VARCHAR2 ,
276 x_msg_count OUT NOCOPY NUMBER ,
277 x_msg_data OUT NOCOPY VARCHAR2 ,
278
279 p_allocation_definition_rec IN GMF_ALLOCATIONDEFINITION_PUB.Allocation_Definition_Rec_Type,
280 p_user_id IN NUMBER -- Bug 2659435 Removed defaults
281 )
282 IS
283
284 l_api_name CONSTANT VARCHAR2(30) := 'Create_Allocation_Definition' ;
285 l_api_version CONSTANT NUMBER := 3.0 ;
286 l_no_rows_upd NUMBER(10) ;
287
288 BEGIN
289 -- Standard Start of API savepoint
290 SAVEPOINT Update_Alloc_Definition_PVT;
291
292 -- Initialize message list if p_init_msg_list is set to TRUE.
293 IF FND_API.to_Boolean( p_init_msg_list ) THEN
294 FND_MSG_PUB.initialize;
295 END IF;
296
297 -- Standard call to check for call compatibility.
298 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
299 p_api_version ,
300 l_api_name ,
301 G_PKG_NAME )
302 THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305
306 -- Initialize API return status to success
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
310 log_msg('Updating record for alloc_id : ' ||
311 p_allocation_definition_rec.alloc_id || ' line_no : ' || p_allocation_definition_rec.line_no);
312 END IF;
313
314 update gl_aloc_bas
315 SET
316 inventory_item_id = decode(p_allocation_definition_rec.item_id,
317 FND_API.G_MISS_NUM, NULL,
318 NULL, inventory_item_id,
319 p_allocation_definition_rec.item_id)
320 ,basis_account_id = decode(p_allocation_definition_rec.basis_account_id,
321 FND_API.G_MISS_NUM, NULL,
322 NULL, basis_account_id,
323 p_allocation_definition_rec.basis_account_id)
324 ,balance_type = decode(p_allocation_definition_rec.balance_type,
325 FND_API.G_MISS_NUM, NULL,
326 NULL, balance_type,
327 p_allocation_definition_rec.balance_type)
328 ,bas_ytd_ptd = decode(p_allocation_definition_rec.bas_ytd_ptd,
329 FND_API.G_MISS_NUM, NULL,
330 NULL, bas_ytd_ptd,
331 p_allocation_definition_rec.bas_ytd_ptd)
332 ,basis_type = decode(p_allocation_definition_rec.basis_type,
333 FND_API.G_MISS_NUM, NULL,
334 NULL, basis_type,
335 p_allocation_definition_rec.basis_type)
336 ,fixed_percent = decode(p_allocation_definition_rec.fixed_percent,
337 FND_API.G_MISS_NUM, NULL,
338 NULL, fixed_percent,
339 p_allocation_definition_rec.fixed_percent)
340 ,cmpntcls_id = decode(p_allocation_definition_rec.cmpntcls_id,
341 FND_API.G_MISS_NUM, NULL,
342 NULL, cmpntcls_id,
343 p_allocation_definition_rec.cmpntcls_id)
344 ,analysis_code = decode(p_allocation_definition_rec.analysis_code,
345 FND_API.G_MISS_CHAR, NULL,
346 NULL, analysis_code,
347 p_allocation_definition_rec.analysis_code)
348 ,organization_id = decode(p_allocation_definition_rec.organization_id,
349 FND_API.G_MISS_NUM, NULL,
350 NULL, organization_id,
351 p_allocation_definition_rec.organization_id)
352 -- ,creation_date = sysdate -- Bug 2722404
353 -- ,created_by = p_user_id
354 ,last_update_date = sysdate
355 ,last_updated_by = p_user_id
356 ,last_update_login = FND_GLOBAL.LOGIN_ID
357 ,delete_mark = decode(p_allocation_definition_rec.delete_mark,
358 FND_API.G_MISS_NUM, NULL,
359 NULL, delete_mark,
360 p_allocation_definition_rec.delete_mark)
361 WHERE
362 alloc_id = p_allocation_definition_rec.alloc_id
363 AND line_no = p_allocation_definition_rec.line_no
364 ;
365
366 -- Standard call to get message count and if count is 1, get message info.
367 FND_MSG_PUB.Count_And_Get
368 ( p_count => x_msg_count ,
369 p_data => x_msg_data
370 );
371
372 EXCEPTION
373 WHEN FND_API.G_EXC_ERROR THEN
374 ROLLBACK TO Update_Alloc_Definition_PVT;
375 x_return_status := FND_API.G_RET_STS_ERROR ;
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count ,
378 p_data => x_msg_data
379 );
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
381 ROLLBACK TO Update_Alloc_Definition_PVT;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 FND_MSG_PUB.Count_And_Get
384 ( p_count => x_msg_count ,
385 p_data => x_msg_data
386 );
387 WHEN OTHERS THEN
388 ROLLBACK TO Update_Alloc_Definition_PVT;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
390 IF FND_MSG_PUB.Check_Msg_Level
391 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
392 THEN
393 FND_MSG_PUB.Add_Exc_Msg
394 ( G_PKG_NAME ,
395 l_api_name
396 );
397 END IF;
398 FND_MSG_PUB.Count_And_Get
399 ( p_count => x_msg_count ,
400 p_data => x_msg_data
401 );
402
403 END Update_Allocation_Definition ;
404
405 -- Func start of comments
406 --+==========================================================================+
407 --| Procedure Name |
408 --| log_msg |
409 --| |
410 --| DESCRIPTION |
411 --| This procedure logs messages to message stack. |
412 --| |
413 --| PARAMETERS |
414 --| p_msg_lvl IN NUMBER(10) - Message Level |
415 --| p_msg_text IN NUMBER(10) - Actual Message Text |
416 --| |
417 --| RETURNS |
418 --| |
419 --| HISTORY |
420 --| 27/02/2001 Uday Moogla - Created |
421 --| |
422 --+==========================================================================+
423 -- Func end of comments
424
425 PROCEDURE log_msg
426 (
427 p_msg_text IN VARCHAR2
428 )
429 IS
430 BEGIN
431
432 -- IF FND_MSG_PUB.Check_Msg_Level (p_msg_lvl) THEN -- Bug 2659435
433 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
434 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
435 FND_MSG_PUB.Add;
436 -- END IF;
437
438 END log_msg ;
439
440 END GMF_AllocationDefinition_PVT;