[Home] [Help]
PACKAGE BODY: APPS.BSC_COLOR_PKG
Source
1 PACKAGE BODY BSC_COLOR_PKG AS
2 /* $Header: BSCCPKGB.pls 120.3.12000000.1 2007/07/17 07:43:33 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCCPKGB.pls |
10 | |
11 | Creation Date: |
12 | October 26, 2006 |
13 | |
14 | Creator: |
15 | Pradeep Pandey |
16 | |
17 | Description: Package Body version. |
18 | This package is CRUD for System level Color properties |
19 | provide CRUD APIs for BSC_SYS_COLORS_B and related table |
20 | |
21 +======================================================================================+
22 */
23
24 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_COLOR_PKG';
25
26
27 /************************************************************************************
28 ************************************************************************************/
29 PROCEDURE INSERT_ROW(
30 p_Color_Id IN NUMBER
31 ,p_Short_Name IN VARCHAR2
32 ,p_System_Color_Name IN VARCHAR2
33 ,p_System_Color_Desc IN VARCHAR2
34 ,p_prototype_label IN VARCHAR2
35 ,p_Perf_Sequence_Id IN NUMBER
36 ,p_System_Color IN NUMBER
37 ,p_User_Color IN NUMBER
38 ,p_Forecast_Color IN NUMBER
39 ,p_User_Forecast_Color IN NUMBER
40 ,p_Numeric_Equivalent IN NUMBER
41 ,p_User_Numeric_Equivalent IN NUMBER
42 ,p_Image IN NUMBER
43 ,p_Created_By IN NUMBER
44 ,p_Last_Updated_By IN NUMBER
45 ,p_Last_Update_Login IN NUMBER
46 ) IS
47 BEGIN
48
49 INSERT INTO bsc_sys_colors_b (color_id
50 ,short_name
51 ,perf_sequence
52 ,color
53 ,user_color
54 ,forecast_color
55 ,user_forecast_color
56 ,numeric_equivalent
57 ,user_numeric_equivalent
58 ,user_image_id
59 ,creation_date
60 ,created_by
61 ,last_update_date
62 ,last_updated_by
63 ,last_update_login)
64 VALUES( p_Color_Id
65 ,p_Short_Name
66 ,p_Perf_Sequence_Id
67 ,p_System_Color
68 ,p_User_Color
69 ,p_Forecast_Color
70 ,p_User_Forecast_Color
71 ,p_Numeric_Equivalent
72 ,p_User_Numeric_Equivalent
73 ,p_Image
74 ,sysdate
75 ,p_Created_By
76 ,sysdate
77 ,p_Last_Updated_By
78 ,p_Last_Update_Login
79 );
80 INSERT INTO bsc_sys_colors_tl (color_id
81 ,name
82 ,description
83 ,prototype_label
84 ,language
85 ,source_lang
86 ,creation_date
87 ,created_by
88 ,last_update_date
89 ,last_updated_by
90 ,last_update_login)
91 SELECT p_Color_Id
92 ,p_System_Color_Name
93 ,p_System_Color_Desc
94 ,p_prototype_label
95 ,L.language_code
96 ,userenv('LANG')
97 ,sysdate
98 ,p_Created_By
99 ,sysdate
100 ,p_Last_Updated_By
101 ,p_Last_Update_Login
102 FROM FND_LANGUAGES L
103 WHERE L.installed_flag in ('I', 'B');
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 raise;
108 end INSERT_ROW;
109
110 /************************************************************************************
111 ************************************************************************************/
112 PROCEDURE UPDATE_ROW (
113 p_Color_Id IN NUMBER
114 ,p_System_Color_Name IN VARCHAR2
115 ,p_System_Color_Desc IN VARCHAR2
116 ,p_prototype_label IN VARCHAR2
117 ,p_Perf_Sequence_Id IN NUMBER
118 ,p_System_Color IN NUMBER
119 ,p_User_Color IN NUMBER
120 ,p_User_Forecast_Color IN NUMBER
121 ,p_Numeric_Equivalent IN NUMBER
122 ,p_User_Numeric_Equivalent IN NUMBER
123 ,p_Image IN NUMBER
124 ,p_Last_Updated_By IN NUMBER
125 ,p_Last_Update_Login IN NUMBER
126 ) IS
127 BEGIN
128 -- Update by id or short_name
129 UPDATE bsc_sys_colors_b
130 SET perf_sequence = p_Perf_Sequence_Id
131 ,color = p_System_Color
132 ,user_color = p_User_Color
133 ,user_forecast_color = p_User_Forecast_Color
134 ,numeric_equivalent = p_Numeric_Equivalent
135 ,user_numeric_equivalent= p_User_Numeric_Equivalent
136 ,user_image_id = p_Image
137 ,creation_date = SYSDATE
138 ,created_by = NVL(p_Last_Update_Login, FND_GLOBAL.USER_ID)
139 ,last_update_date = SYSDATE
140 ,last_updated_by = NVL(p_Last_Updated_By, FND_GLOBAL.USER_ID)
141 ,last_update_login= NVL(p_Last_Update_Login, FND_GLOBAL.LOGIN_ID)
142 WHERE color_id = p_color_Id;
143
144 UPDATE bsc_sys_colors_tl
145 SET name = p_System_Color_Name
146 ,description = p_System_Color_Desc
147 ,prototype_label = p_prototype_label
148 ,source_lang = userenv('LANG')
149 WHERE color_id = p_Color_Id
150 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
151
152 END UPDATE_ROW;
153
154 /************************************************************************************
155 ************************************************************************************/
156 PROCEDURE UPDATE_ROW (
157 p_Color_Id IN NUMBER
158 ,p_Perf_Sequence_Id IN NUMBER
159 ,p_System_Color IN NUMBER
160 ,p_User_Color IN NUMBER
161 ,p_User_Forecast_Color IN NUMBER
162 ,p_Numeric_Equivalent IN NUMBER
163 ,p_User_Numeric_Equivalent IN NUMBER
164 ,p_Image IN NUMBER
165 ,p_Last_Updated_By IN NUMBER
166 ,p_Last_Update_Login IN NUMBER
167 ) IS
168 BEGIN
169 -- Update by id or short_name
170 UPDATE bsc_sys_colors_b
171 SET perf_sequence = p_Perf_Sequence_Id
172 ,color = p_System_Color
173 ,user_color = p_User_Color
174 ,user_forecast_color = p_User_Forecast_Color
175 ,numeric_equivalent = p_Numeric_Equivalent
176 ,user_numeric_equivalent= p_User_Numeric_Equivalent
177 ,user_image_id = p_Image
178 ,last_update_date = SYSDATE
179 ,last_updated_by = p_Last_Updated_By
180 ,last_update_login = p_Last_Update_Login
181 WHERE color_id = p_color_Id;
182
183 END UPDATE_ROW;
184 /************************************************************************************
185 ************************************************************************************/
186 PROCEDURE DELETE_ROW(
187 p_Bsc_Color_Id IN NUMBER
188 ,p_Bsc_Color_SN IN NUMBER
189 ) IS
190 l_bsc_color_id NUMBER;
191 BEGIN
192 IF (p_Bsc_Color_Id IS NOT NULL) THEN
193 DELETE BSC_SYS_COLORS_B
194 WHERE color_id = p_Bsc_Color_Id;
195
196 DELETE bsc_sys_colors_tl
197 WHERE color_id = p_Bsc_Color_Id;
198 ELSE -- Delete by short_name
199 SELECT color_id
200 INTO l_bsc_color_id
201 FROM bsc_sys_colors_b
202 WHERE short_name=p_Bsc_Color_SN;
203
204 DELETE BSC_SYS_COLORS_B
205 WHERE short_name = p_Bsc_Color_SN;
206
207 DELETE bsc_sys_colors_tl
208 WHERE color_id = l_bsc_color_id;
209 END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
212 RAISE;
213 END DELETE_ROW;
214
215 PROCEDURE ADD_LANGUAGE IS
216 BEGIN
217 INSERT INTO bsc_sys_colors_tl (color_id
218 ,name
219 ,description
220 ,language
221 ,source_lang
222 ,creation_date
223 ,created_by
224 ,last_update_date
225 ,last_updated_by
226 ,last_update_login)
227 SELECT B.Color_Id
228 ,B.name
229 ,B.description
230 ,L.language_code
231 ,L.language_code
232 ,sysdate
233 ,B.Created_By
234 ,sysdate
235 ,B.Last_Updated_By
236 ,B.Last_Update_Login
237 FROM BSC_SYS_COLORS_TL B, FND_LANGUAGES L
238 WHERE L.installed_flag in ('I', 'B')
239 AND B.LANGUAGE = USERENV('LANG')
240 AND NOT EXISTS
241 (SELECT NULL
242 FROM BSC_SYS_COLORS_TL C
243 WHERE C.color_id = B.color_id
244 AND C.language = L.language_code);
245 END ADD_LANGUAGE;
246
247 END BSC_COLOR_PKG;