1 PACKAGE BODY PON_FIELDS_PVT as
2 /* $Header: PONFMFEB.pls 120.2 2006/04/18 08:38:19 sdewan noship $ */
3
4 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_FIELDS_PVT';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
7
8 /*======================================================================
9 PROCEDURE : INSERT_FIELD
10 PARAMETERS: 1. p_code- The field Code
11 2. p_name - The name for the field
12 3. p_description - The description for the field
13 4. p_result - The result from this procedure
14 0 -> success 1 -> failure
15 5. p_err_code - The error code if any
16 6. p_err_msg - The error message if any
17 COMMENT : When a new field is created by the user, the name and
18 description for the field are stored in the pon_fields_tl
19 table. The pon_fields_tl will have 'n' new rows
20 corresponding to this new field, here 'n' is the total number
21 of installed and base languages. The name and description
22 will remain the same for all the language rows when the
23 field is first created.
24 ======================================================================*/
25 PROCEDURE insert_field(p_code IN VARCHAR2,
26 p_name IN VARCHAR2,
27 p_description IN VARCHAR2,
28 p_result OUT NOCOPY NUMBER,
29 p_err_code OUT NOCOPY VARCHAR2,
30 p_err_msg OUT NOCOPY VARCHAR2) IS
31
32 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FIELD';
33
34 BEGIN
35 PON_FORMS_UTIL_PVT.print_debug_log(l_api_name, 'BEGIN: p_code = ' || p_code ||
36 'p_name = ' || p_name ||
37 'p_description = ' || p_description);
38
39 p_result := 0;
40
41 insert into pon_fields_tl (FIELD_CODE,
42 FIELD_NAME,
43 DESCRIPTION,
44 LANGUAGE,
45 SOURCE_LANG,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN)
51 select p_code,
52 p_name,
53 p_description,
54 a.language_code,
55 USERENV ('LANG'),
56 sysdate,
57 fnd_global.user_id,
58 sysdate,
59 fnd_global.user_id,
60 fnd_global.login_id
61 from fnd_languages a
62 where a.installed_flag in ('I', 'B');
63
64 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name,'END');
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception in inserting rows into pon_fields_tl');
69 p_result := 2;
70 p_err_msg := SQLERRM;
71 p_err_code := SQLCODE;
72
73 RAISE_APPLICATION_ERROR(-20101, 'Exception at PON_FIELDS_PVT.insert_field: ' || p_err_code || ' : ' || p_err_msg);
74
75 END;
76
77 /*======================================================================
78 PROCEDURE : DELETE_FIELD
79 PARAMETERS: 1. p_code: the field code of the field to be deleted.
80 2. p_result: 0-> indicates success 1->failure
81 3. p_err_code: The error code if any
82 4. p_err_msg: The error message if any
83 COMMENT : Given a field code this procedure will delete
84 all the entries in the PON_FIELDS_TL table
85 corresponding to this field.
86 ======================================================================*/
87 PROCEDURE delete_field (p_code IN VARCHAR2,
88 p_result OUT NOCOPY NUMBER,
89 p_err_code OUT NOCOPY VARCHAR2,
90 p_err_msg OUT NOCOPY VARCHAR2) IS
91
92 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FIELD';
93 BEGIN
94
95 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'BEGIN: p_code = ' || p_code);
96
97 p_result := 0;
98
99 delete from pon_fields_tl where field_code=p_code;
100
101 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'END');
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception while deleting rows from pon_fields_tl');
106
107 p_result := 1;
108 p_err_msg := SQLERRM;
109 p_err_code := SQLCODE;
110
111 RAISE_APPLICATION_ERROR(-20102, 'Exception at PON_FIELDS_PVT.delete_field:' || p_err_code || ' : ' || p_err_msg);
112
113 END;
114
115 /*======================================================================
116 PROCEDURE : UPDATE_FIELD
117 PARAMETERS: 1. p_code: The new field Code
118 2. p_name: The name of the field
119 3. p_description: The description of the field
120 4. p_lastUpdate: the lastUpdate date of the field in the
121 pon_fields table
122 5. p_old_code: The old field code.
123 6. p_result: 0->success 1-> failure
124 7. p_err_code: The error code if any
125 8. p_err_msg: The error message if any
126 COMMENT : This procedure will update the field_code, name and
127 description of all the row that have field_code as
128 p_old_code
129 ======================================================================*/
130 PROCEDURE update_field(p_code IN VARCHAR2,
131 p_name IN VARCHAR2,
132 p_description IN VARCHAR2,
133 p_lastUpdate IN DATE,
134 p_old_code IN VARCHAR2,
135 p_result OUT NOCOPY NUMBER,
136 p_err_code OUT NOCOPY VARCHAR2,
137 p_err_msg OUT NOCOPY VARCHAR2) IS
138
139 x_updated varchar2(1);
140 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FIELD';
141
142 BEGIN --{
143 PON_FORMS_UTIL_PVT.print_debug_log(l_api_name, 'BEGIN: p_code = ' || p_code ||
144 ', p_name = ' || p_name ||
145 ', p_description = ' || p_description ||
146 ', p_old_code = ' || p_old_code);
147
148 p_result := 0;
149
150 update pon_fields_tl
151 set
152 field_code = p_code
153 where
154 field_code = p_old_code;
155
156 update pon_fields_tl
157 set
158 field_name = p_name,
159 description = p_description,
160 source_lang = USERENV ('LANG'),
161 last_updated_by = fnd_global.user_id,
162 last_update_date = sysdate,
163 last_update_login = fnd_global.login_id
164 where
165 field_code = p_code and
166 language = USERENV ('LANG');
167
168 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'END');
169
170 EXCEPTION
171
172 WHEN OTHERS THEN
173 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception while updating rows in pon_fields_tl');
174
175 p_result := 1;
176 p_err_msg := SQLERRM;
177 p_err_code := SQLCODE;
178 RAISE_APPLICATION_ERROR(-20103, 'Exception at PON_FIELDS_PVT.update_field: ' || p_err_code || ' : ' || p_err_msg);
179 END UPDATE_FIELD; --}
180
181
182 /*======================================================================
183 PROCEDURE : add_language
184 COMMENT : Populates the tl tables.
185 ======================================================================*/
186 PROCEDURE add_language IS
187
188 begin
189
190 INSERT INTO PON_FIELDS_TL (
191 FIELD_CODE,
192 FIELD_NAME,
193 DESCRIPTION,
194 LANGUAGE,
195 SOURCE_LANG,
196 CREATED_BY,
197 CREATION_DATE,
198 LAST_UPDATED_BY,
199 LAST_UPDATE_DATE,
200 LAST_UPDATE_LOGIN
201 )
202 SELECT
203 pf.FIELD_CODE,
204 pf.FIELD_NAME,
205 pf.DESCRIPTION,
206 lang.language_code,
207 pf.SOURCE_LANG,
208 pf.CREATED_BY,
209 sysdate,
210 pf.LAST_UPDATED_BY,
211 sysdate,
212 pf.LAST_UPDATE_LOGIN
213 FROM PON_FIELDS_TL pf, FND_LANGUAGES lang
214 WHERE pf.language = USERENV('LANG')
215 AND lang.INSTALLED_FLAG in ('I','B')
216 AND NOT EXISTS (SELECT 'x' FROM PON_FIELDS_TL pf2
217 WHERE pf2.FIELD_CODE = pf.FIELD_CODE
218 AND pf2.language = lang.language_code);
219
220 END add_language;
221
222 END PON_FIELDS_PVT;
[Home] [Help]