eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ASO_SUP_TEMPLATE_DATA_PVT

Source


1 PACKAGE BODY ASO_SUP_TEMPLATE_DATA_PVT as
2 /* $Header: asovstmb.pls 120.1 2005/06/29 12:45:12 appldev ship $ */
3 -- Start of Comments
4 -- Package name     : ASO_SUP_TEMPLATE_DATA_PVT
5 -- Purpose          :
6 -- History          :
7 -- NOTE             :
8 -- End of Comments
9 
10 
11 G_PKG_NAME CONSTANT VARCHAR2(100):= 'ASO_SUP_TEMPLATE_DATA_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovstmb.pls';
13 
14 FUNCTION Validate_Template(
15      p_template_context  IN   VARCHAR2,
16      p_template_level      IN      VARCHAR2)
17 RETURN VARCHAR2
18 IS
19 
20 
21     x_return_status    VARCHAR2(1);
22     l_context_count    VARCHAR2(1);
23     l_level_count      VARCHAR2(1);
24     l_lookup_code      VARCHAR2(240);
25 
26 
27 CURSOR c_template_context (p_templ_context VARCHAR2) IS
28 
29          SELECT LOOKUP_CODE
30          from ASO_LOOKUPS
31          where LOOKUP_TYPE = 'ASO_SUP_TEMPLATE_CONTEXT'
32          AND    lookup_code = p_templ_context
33 	    and enabled_flag = 'Y'
34          and sysdate between nvl(start_date_active, sysdate)
35 	    and nvl(end_date_active,sysdate);
36 
37 /*
38 	SELECT  lookup_code
39      FROM   fnd_lookup_values
40      WHERE  lookup_type = 'ASO_SUP_TEMPLATE_CONTEXT'
41      AND    lookup_code = p_templ_context
42      AND    view_application_id = 697
43      AND    LANGUAGE = userenv('LANG')
44      and    ENABLED_FLAG = 'Y';
45 */
46 
47 CURSOR c_template_level (p_lookup_type VARCHAR2,p_templ_level VARCHAR2) IS
48      SELECT  'X'
49      FROM   fnd_lookup_values
50      WHERE  lookup_type = p_lookup_type
51      AND    lookup_code = p_templ_level
52      AND    view_application_id = 697
53      AND    LANGUAGE = userenv('LANG')
54      and    ENABLED_FLAG = 'Y';
55 
56 BEGIN
57     --  Initialize API return status to success
58     x_return_status := FND_API.G_RET_STS_SUCCESS;
59 
60     IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
61      aso_debug_pub.add('Validate_Template: p_template_context :'||p_template_context, 1, 'N');
62      aso_debug_pub.add('Validate_Template: p_template_level :'||p_template_level, 1, 'N');
63     END IF;
64 
65     -- Validate the template context
66     OPEN c_template_context(p_template_context);
67 
68     FETCH c_template_context INTO l_lookup_code;
69     IF c_template_context%NOTFOUND THEN
70 
71       IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
72          FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
73          FND_MESSAGE.Set_Token ('COLUMN' , 'TEMPLATE CONTEXT', FALSE );
74          FND_MESSAGE.Set_Token ('VALUE' ,p_template_context, FALSE );
75             FND_MSG_PUB.ADD;
76       END IF;
77          x_return_status :=  FND_API.G_RET_STS_ERROR;
78       RETURN x_return_status;
79     END IF;
80 
81     CLOSE c_template_context;
82 
83    -- Validate the template level
84     OPEN c_template_level(l_lookup_code,p_template_level);
85 
86     FETCH c_template_level INTO l_level_count;
87 
88     IF c_template_level%NOTFOUND THEN
89 
90          IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
91          FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
92          FND_MESSAGE.Set_Token ('COLUMN' , 'TEMPLATE LEVEL', FALSE );
93          FND_MESSAGE.Set_Token ('VALUE' ,p_template_level, FALSE );
94             FND_MSG_PUB.ADD;
95       END IF;
96          x_return_status :=  FND_API.G_RET_STS_ERROR;
97       RETURN x_return_status;
98 
99     END IF;
100 
101     CLOSE c_template_level;
102 
103      IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
104         aso_debug_pub.add('Validate_Template: x_return_status: '||x_return_status, 1, 'N');
105      END IF;
106 
107 RETURN x_return_status;
108 
109 END Validate_Template;
110 
111 PROCEDURE CREATE_TEMPLATE
112 (
113  P_Api_Version_Number          IN         NUMBER,
114  P_Init_Msg_List               IN         VARCHAR2     := FND_API.G_FALSE,
115  P_Commit                      IN         VARCHAR2     := FND_API.G_FALSE,
116  P_TEMPLATE_REC     IN          ASO_SUP_TEMPLATE_DATA_PVT.TEMPLATE_REC_TYPE,
117  X_Template_id                 OUT NOCOPY /* file.sql.39 change */           NUMBER,
118  X_Return_Status               OUT NOCOPY /* file.sql.39 change */           VARCHAR2,
119  X_Msg_Count                   OUT NOCOPY /* file.sql.39 change */           VARCHAR2,
120  X_Msg_Data                    OUT NOCOPY /* file.sql.39 change */           VARCHAR2 )
121 
122 IS
123 
124 l_api_name                    VARCHAR2 ( 150 ) := 'CREATE_TEMPLATE';
125 l_api_version_number CONSTANT NUMBER := 1.0;
126 row_id                        varchar2(64);
127 l_TEMPLATE_id                 NUMBER;
128 l_return_status               VARCHAR2(1);
129 
130 BEGIN
131 
132 
133 -- Establish a standard save point
134       SAVEPOINT CREATE_TEMPLATE_PVT;
135 
136 -- Standard call to check for call compatability
137       IF NOT FND_API.Compatible_API_Call (
138                  l_api_version_number
139               , p_api_version_number
140               , l_api_name
141               , G_PKG_NAME
142               ) THEN
143          RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144       END IF;
145 
146 -- Initialize message list if p_init_msg_list is set to TRUE.
147       IF FND_API.to_Boolean ( p_init_msg_list ) THEN
148          FND_MSG_PUB.initialize;
149       END IF;
150 
151 
152 --  Initialize API return status to success
153       x_return_status            := FND_API.G_RET_STS_SUCCESS;
154 
155 -- API BODY
156       IF aso_debug_pub.g_debug_flag = 'Y' THEN
157       aso_debug_pub.ADD ('CREATE_TEMPLATE : Begin' , 1, 'N' );
158      aso_debug_pub.add('CREATE_TEMPLATE : p_template_context :'||P_TEMPLATE_REC.TEMPLATE_CONTEXT, 1, 'N');
159      aso_debug_pub.add('CREATE_TEMPLATE : p_template_level :'||P_TEMPLATE_REC.TEMPLATE_LEVEL, 1, 'N');
160       END IF;
161 
162 l_return_status :=
163                Validate_Template(
164                    p_template_context =>P_TEMPLATE_REC.TEMPLATE_CONTEXT,
165                    p_template_level => P_TEMPLATE_REC.TEMPLATE_LEVEL);
166 
167           IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
168              x_return_status            := l_return_status;
169              RAISE FND_API.G_EXC_ERROR;
170           END IF;
171 
172  ASO_SUP_TEMPLATE_PKG.INSERT_ROW (
173   PX_ROWID => row_id,
174   PX_TEMPLATE_ID             => L_TEMPLATE_ID,
175   P_CREATION_DATE            => P_TEMPLATE_REC.CREATION_DATE ,
176   P_CREATED_BY               => P_TEMPLATE_REC.CREATED_BY ,
177   P_LAST_UPDATE_DATE         => P_TEMPLATE_REC.LAST_UPDATE_DATE ,
178   P_LAST_UPDATED_BY          => P_TEMPLATE_REC.LAST_UPDATED_BY ,
179   P_LAST_UPDATE_LOGIN        => P_TEMPLATE_REC.LAST_UPDATE_LOGIN ,
180   P_TEMPLATE_NAME            => P_TEMPLATE_REC.TEMPLATE_NAME,
181   P_DESCRIPTION              => P_TEMPLATE_REC.DESCRIPTION,
182   P_TEMPLATE_LEVEL           => P_TEMPLATE_REC.TEMPLATE_LEVEL,
183   P_TEMPLATE_CONTEXT         => P_TEMPLATE_REC.TEMPLATE_CONTEXT,
184   p_context                  => P_TEMPLATE_REC.context,
185   P_ATTRIBUTE1               => P_TEMPLATE_REC.ATTRIBUTE1,
186   P_ATTRIBUTE2               => P_TEMPLATE_REC.ATTRIBUTE2,
187   P_ATTRIBUTE3               => P_TEMPLATE_REC.ATTRIBUTE3,
188   P_ATTRIBUTE4               => P_TEMPLATE_REC.ATTRIBUTE4,
189   P_ATTRIBUTE5               => P_TEMPLATE_REC.ATTRIBUTE5,
190   P_ATTRIBUTE6               => P_TEMPLATE_REC.ATTRIBUTE6,
191   P_ATTRIBUTE7               => P_TEMPLATE_REC.ATTRIBUTE7,
192   P_ATTRIBUTE8               => P_TEMPLATE_REC.ATTRIBUTE8,
193   P_ATTRIBUTE9               => P_TEMPLATE_REC.ATTRIBUTE9,
194   P_ATTRIBUTE10              => P_TEMPLATE_REC.ATTRIBUTE10,
195   P_ATTRIBUTE11              => P_TEMPLATE_REC.ATTRIBUTE11,
196   P_ATTRIBUTE12              => P_TEMPLATE_REC.ATTRIBUTE12,
197   P_ATTRIBUTE13              => P_TEMPLATE_REC.ATTRIBUTE13,
198   P_ATTRIBUTE14              => P_TEMPLATE_REC.ATTRIBUTE14,
199   P_ATTRIBUTE15              => P_TEMPLATE_REC.ATTRIBUTE15,
200   P_ATTRIBUTE16              => P_TEMPLATE_REC.ATTRIBUTE16,
201   P_ATTRIBUTE17              => P_TEMPLATE_REC.ATTRIBUTE17,
202   P_ATTRIBUTE18              => P_TEMPLATE_REC.ATTRIBUTE18,
203   P_ATTRIBUTE19              => P_TEMPLATE_REC.ATTRIBUTE19,
204   P_ATTRIBUTE20              => P_TEMPLATE_REC.ATTRIBUTE20
205   );
206 
207 	  X_Template_id := l_template_id;
208 
209 
210       -- Standard check for p_commit
211       IF FND_API.to_Boolean ( p_commit ) THEN
212          COMMIT WORK;
213       END IF;
214 
215 
216       IF aso_debug_pub.g_debug_flag = 'Y' THEN
217       aso_debug_pub.ADD ( 'CREATE_TEMPLATE:  end' , 1 , 'N' );
218       END IF;
219 
220 EXCEPTION
221 
222      WHEN FND_API.G_EXC_ERROR THEN
223          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
224              P_API_NAME =>                   L_API_NAME
225           , P_PKG_NAME =>                    G_PKG_NAME
226           , P_EXCEPTION_LEVEL =>             FND_MSG_PUB.G_MSG_LVL_ERROR
227           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
228           , X_MSG_COUNT =>                   X_MSG_COUNT
229           , X_MSG_DATA =>                    X_MSG_DATA
230           , X_RETURN_STATUS =>               X_RETURN_STATUS
231           );
232 
233       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
235              P_API_NAME =>                   L_API_NAME
236           , P_PKG_NAME =>                    G_PKG_NAME
237           , P_EXCEPTION_LEVEL =>             FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
238           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
239           , X_MSG_COUNT =>                   X_MSG_COUNT
240           , X_MSG_DATA =>                    X_MSG_DATA
241           , X_RETURN_STATUS =>               X_RETURN_STATUS
242           );
243    WHEN OTHERS THEN
244          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
245              P_API_NAME =>                   L_API_NAME
246           , P_PKG_NAME =>                    G_PKG_NAME
247           , P_SQLCODE =>                     SQLCODE
248           , P_SQLERRM =>                     SQLERRM
249           , P_EXCEPTION_LEVEL =>             ASO_UTILITY_PVT.G_EXC_OTHERS
250           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
251           , X_MSG_COUNT =>                   X_MSG_COUNT
252           , X_MSG_DATA =>                    X_MSG_DATA
253           , X_RETURN_STATUS =>               X_RETURN_STATUS
254           );
255 
256 
257 END CREATE_TEMPLATE;
258 
259 
260 PROCEDURE Update_Template
261 (
262  P_Api_Version_Number          IN         NUMBER,
263  P_Init_Msg_List               IN         VARCHAR2     := FND_API.G_FALSE,
264  P_Commit                      IN         VARCHAR2     := FND_API.G_FALSE,
265  P_TEMPLATE_REC     IN          ASO_SUP_TEMPLATE_DATA_PVT.TEMPLATE_REC_TYPE,
266  X_Return_Status               OUT NOCOPY /* file.sql.39 change */           VARCHAR2,
267  X_Msg_Count                   OUT NOCOPY /* file.sql.39 change */           VARCHAR2,
268  X_Msg_Data                    OUT NOCOPY /* file.sql.39 change */           VARCHAR2 )
269 
270 IS
271 
272 l_api_name                    VARCHAR2 ( 150 ) := 'UPDATE_TEMPLATE';
273 l_api_version_number CONSTANT NUMBER := 1.0;
274 row_id                        varchar2(64);
275 l_TEMPLATE_id                 NUMBER;
276 l_return_status               VARCHAR2(1);
277 
278 BEGIN
279 -- Establish a standard save point
280       SAVEPOINT UPDATE_TEMPLATE_PVT;
281 
282 -- Standard call to check for call compatability
283       IF NOT FND_API.Compatible_API_Call (
284                  l_api_version_number
285               , p_api_version_number
286               , l_api_name
287               , G_PKG_NAME
288               ) THEN
289          RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290       END IF;
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 
298 --  Initialize API return status to success
299       x_return_status            := FND_API.G_RET_STS_SUCCESS;
300 
301 -- API BODY
302       IF aso_debug_pub.g_debug_flag = 'Y' THEN
303       aso_debug_pub.ADD ('UPDATE_TEMPLATE : Begin' , 1, 'N' );
304      aso_debug_pub.add('UPDATE_TEMPLATE : p_template_context :'||P_TEMPLATE_REC.TEMPLATE_CONTEXT, 1, 'N');
305      aso_debug_pub.add('UPDATE_TEMPLATE : p_template_level :'||P_TEMPLATE_REC.TEMPLATE_LEVEL, 1, 'N');
306       END IF;
307 
308 l_return_status :=
309                Validate_Template(
310                    p_template_context =>P_TEMPLATE_REC.TEMPLATE_CONTEXT,
311                    p_template_level => P_TEMPLATE_REC.TEMPLATE_LEVEL);
312 
313           IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
314              x_return_status            := l_return_status;
315              RAISE FND_API.G_EXC_ERROR;
316           END IF;
317 
318 
319 ASO_SUP_TEMPLATE_PKG.UPDATE_ROW (
320   P_TEMPLATE_ID              => P_TEMPLATE_REC.TEMPLATE_ID,
321   P_LAST_UPDATE_DATE         => P_TEMPLATE_REC.LAST_UPDATE_DATE ,
322   P_LAST_UPDATED_BY          => P_TEMPLATE_REC.LAST_UPDATED_BY ,
323   P_LAST_UPDATE_LOGIN        => P_TEMPLATE_REC.LAST_UPDATE_LOGIN ,
324   P_TEMPLATE_NAME            => P_TEMPLATE_REC.TEMPLATE_NAME,
325   P_DESCRIPTION              => P_TEMPLATE_REC.DESCRIPTION,
326   P_TEMPLATE_LEVEL           => P_TEMPLATE_REC.TEMPLATE_LEVEL,
327   P_TEMPLATE_CONTEXT         => P_TEMPLATE_REC.TEMPLATE_CONTEXT,
328   p_context                  => P_TEMPLATE_REC.context,
329   P_ATTRIBUTE1               => P_TEMPLATE_REC.ATTRIBUTE1,
330   P_ATTRIBUTE2               => P_TEMPLATE_REC.ATTRIBUTE2,
331   P_ATTRIBUTE3               => P_TEMPLATE_REC.ATTRIBUTE3,
332   P_ATTRIBUTE4               => P_TEMPLATE_REC.ATTRIBUTE4,
333   P_ATTRIBUTE5               => P_TEMPLATE_REC.ATTRIBUTE5,
334   P_ATTRIBUTE6               => P_TEMPLATE_REC.ATTRIBUTE6,
335   P_ATTRIBUTE7               => P_TEMPLATE_REC.ATTRIBUTE7,
336   P_ATTRIBUTE8               => P_TEMPLATE_REC.ATTRIBUTE8,
337   P_ATTRIBUTE9               => P_TEMPLATE_REC.ATTRIBUTE9,
338   P_ATTRIBUTE10              => P_TEMPLATE_REC.ATTRIBUTE10,
339   P_ATTRIBUTE11              => P_TEMPLATE_REC.ATTRIBUTE11,
340   P_ATTRIBUTE12              => P_TEMPLATE_REC.ATTRIBUTE12,
341   P_ATTRIBUTE13              => P_TEMPLATE_REC.ATTRIBUTE13,
342   P_ATTRIBUTE14              => P_TEMPLATE_REC.ATTRIBUTE14,
343   P_ATTRIBUTE15              => P_TEMPLATE_REC.ATTRIBUTE15,
344   P_ATTRIBUTE16              => P_TEMPLATE_REC.ATTRIBUTE16,
345   P_ATTRIBUTE17              => P_TEMPLATE_REC.ATTRIBUTE17,
346   P_ATTRIBUTE18              => P_TEMPLATE_REC.ATTRIBUTE18,
347   P_ATTRIBUTE19              => P_TEMPLATE_REC.ATTRIBUTE19,
348   P_ATTRIBUTE20              => P_TEMPLATE_REC.ATTRIBUTE20
349   );
350 
351 
352       -- Standard check for p_commit
353       IF FND_API.to_Boolean ( p_commit ) THEN
354          COMMIT WORK;
355       END IF;
356 
357 
358       IF aso_debug_pub.g_debug_flag = 'Y' THEN
359       aso_debug_pub.ADD ( 'UPDATE_TEMPLATE:  end' , 1 , 'N' );
360       END IF;
361 
362 EXCEPTION
363 
364      WHEN FND_API.G_EXC_ERROR THEN
365          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
366              P_API_NAME =>                   L_API_NAME
367           , P_PKG_NAME =>                    G_PKG_NAME
368           , P_EXCEPTION_LEVEL =>             FND_MSG_PUB.G_MSG_LVL_ERROR
369           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
370           , X_MSG_COUNT =>                   X_MSG_COUNT
371           , X_MSG_DATA =>                    X_MSG_DATA
372           , X_RETURN_STATUS =>               X_RETURN_STATUS
373           );
374 
375       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
377              P_API_NAME =>                   L_API_NAME
378           , P_PKG_NAME =>                    G_PKG_NAME
379           , P_EXCEPTION_LEVEL =>             FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
380           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
381           , X_MSG_COUNT =>                   X_MSG_COUNT
382           , X_MSG_DATA =>                    X_MSG_DATA
383           , X_RETURN_STATUS =>               X_RETURN_STATUS
384           );
385    WHEN OTHERS THEN
386          ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
387              P_API_NAME =>                   L_API_NAME
388           , P_PKG_NAME =>                    G_PKG_NAME
389           , P_SQLCODE =>                     SQLCODE
390           , P_SQLERRM =>                     SQLERRM
391           , P_EXCEPTION_LEVEL =>             ASO_UTILITY_PVT.G_EXC_OTHERS
392           , P_PACKAGE_TYPE =>                ASO_UTILITY_PVT.G_PVT
393           , X_MSG_COUNT =>                   X_MSG_COUNT
394           , X_MSG_DATA =>                    X_MSG_DATA
395           , X_RETURN_STATUS =>               X_RETURN_STATUS
396           );
397 END Update_Template;
398 End ASO_SUP_TEMPLATE_DATA_PVT;