[Home] [Help]
PACKAGE BODY: APPS.PON_FORMS_SECTIONS_PVT
Source
1 PACKAGE BODY PON_FORMS_SECTIONS_PVT as
2 /* $Header: PONFMSCB.pls 120.2 2006/04/18 08:41:33 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_FORMS_SECTIONS_PVT';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
7
8 /*======================================================================
9 PROCEDURE : INSERT_FORMS_SECTIONS
10 PARAMETERS: 1. p_form_id: The id of the form/section that needs to be
11 inserted.
12 2. p_name: The name of the form/section.
13 3. p_description: The description of the form.
14 4. p_tip_text: The tip text for the section.
15 5. p_source_language: The current session language
16 6. p_result: 0->success 1-> failure
17 7. p_err_code: The error code if any
18 8. p_err_msg: The error message if any
19 COMMENT :
20 ======================================================================*/
21 PROCEDURE insert_forms_sections(p_form_id IN NUMBER,
22 p_name IN VARCHAR2,
23 p_description IN VARCHAR2,
24 p_tip_text IN VARCHAR2,
25 p_source_language IN VARCHAR2,
26 p_result OUT NOCOPY NUMBER,
27 p_err_code OUT NOCOPY VARCHAR2,
28 p_err_msg OUT NOCOPY VARCHAR2) IS
29
30 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMS_SECTIONS';
31
32 BEGIN
33 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'BEGIN: p_form_id= ' || p_form_id ||
34 ', p_name = ' || p_name ||
35 ', p_description = ' || p_description ||
36 ', p_tip_text = ' || p_tip_text ||
37 ', p_source_language = ' || p_source_language);
38
39 p_result := 0;
40
41
42 insert into pon_forms_sections_tl(FORM_ID,
43 FORM_NAME,
44 FORM_DESCRIPTION ,
45 TIP_TEXT,
46 CUSTOMIZED_FLAG ,
47 LANGUAGE ,
48 SOURCE_LANG ,
49 CREATION_DATE,
50 CREATED_BY ,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY ,
53 LAST_UPDATE_LOGIN)
54 select p_form_id,
55 p_name,
56 p_description,
57 p_tip_text,
58 'N',
59 a.language_code,
60 userenv('LANG'),
61 sysdate,
62 fnd_global.user_id,
63 sysdate,
64 fnd_global.user_id,
65 fnd_global.login_id
66 from fnd_languages a
67 where a.installed_flag in ('I', 'B');
68
69 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'END');
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception in inserting rows in pon_forms_section_tl');
74 p_result := 1;
75 p_err_msg := SQLERRM;
76 p_err_code := SQLCODE;
77
78 RAISE_APPLICATION_ERROR(-20201, 'Exception at PON_FORMS_SECTIONS_PKG.insert_forms_sections: ' || p_err_code || ' : ' || p_err_msg);
79 END;
80
81 /*======================================================================
82 PROCEDURE :UPDATE_FORMS_SECTIONS
83 PARAMETERS: 1. p_forms_sections_id: the id of the form/section
84 that needs to be updated.
85 2. p_name: The name of the form/section
86 3. p_description: The descriptio of the form/section.
87 4. p_tip_text: The tip_text for the section.
88 5. p_language: The language of the current session
89 6. p_result: 0->success 1-> failure
90 7. p_err_code: The error code if any
91 8. p_err_msg: The error message if any
92 COMMENT : This procedure will update the name, description
93 and tip text for the sectio/form identified
94 by the p_forms_sections_id
95 ======================================================================*/
96 PROCEDURE update_forms_sections(p_forms_sections_id IN NUMBER,
97 p_name IN VARCHAR2,
98 p_description IN VARCHAR2,
99 p_tip_text IN VARCHAR2,
100 p_language IN VARCHAR2,
101 p_result OUT NOCOPY NUMBER,
102 p_err_code OUT NOCOPY VARCHAR2,
103 p_err_msg OUT NOCOPY VARCHAR2) IS
104
105 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMS_SECTIONS';
106
107 BEGIN
108 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'BEGIN: p_forms_sections_id = ' || p_forms_sections_id ||
109 ', p_name = ' || p_name ||
110 ', p_description ' || p_description ||
111 ', p_tip_text = ' || p_tip_text ||
112 ', p_language = ' || p_language);
113
114 p_result := 0;
115
116 update pon_forms_sections_tl
117 set
118 form_name = p_name,
119 form_description = p_description,
120 tip_text = p_tip_text,
121 source_lang = userenv('LANG'),
122 last_updated_by = fnd_global.user_id,
123 last_update_date = sysdate,
124 last_update_login = fnd_global.login_id
125 where
126 form_id = p_forms_sections_id and
127 language = userenv('LANG');
128
129 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'END');
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception in updating rows in pon_forms_section_tl');
134 p_result := 1;
135 p_err_msg := SQLERRM;
136 p_err_code := SQLCODE;
137 RAISE_APPLICATION_ERROR(-20202, 'Exception at PON_FORMS_SECTIONS_PKG.update_forms_sections:' || p_err_code || ' : ' || p_err_msg);
138
139
140 END;
141
142 /*======================================================================
143 PROCEDURE : DELETE_FORMS_SECTIONS
144 PARAMETERS: 1. p_form_id - The formid of the form that is to be
145 deleted.
146 2. p_result - 0-> success 1-> failure
147 3. p_err_code - The error code if applicable
148 4. p_err_msg - The error message if applicable
149 COMMENT : This procedure will delete all the rows in the
150 PON_FORMS_SECTIONS_TL table corresponding to the
151 form with the id as p_form_id
152 ======================================================================*/
153 PROCEDURE delete_forms_sections(p_form_id IN NUMBER,
154 p_result OUT NOCOPY NUMBER,
155 p_err_code OUT NOCOPY VARCHAR2,
156 p_err_msg OUT NOCOPY VARCHAR2) IS
157
158 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMS_SECTIONS';
159
160 BEGIN
161
162 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'BEGIN: p_form_id = ' || p_form_id);
163
164 p_result := 0;
165
166 delete from pon_forms_sections_tl where form_id=p_form_id;
167
168 PON_FORMS_UTIL_PVT.print_debug_log (l_api_name, 'END');
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception in deleteing rows from pon_forms_section_tl');
173 p_result := 1;
174 p_err_msg := SQLERRM;
175 p_err_code := SQLCODE;
176
177 RAISE_APPLICATION_ERROR(-20203, 'Exception at PON_FORMS_SECTIONS_PKG.delete_forms_sections:'|| p_err_code || ' : ' || p_err_msg);
178
179 END;
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_FORMS_SECTIONS_TL (
191 FORM_ID,
192 FORM_NAME,
193 FORM_DESCRIPTION,
194 CUSTOMIZED_FLAG,
195 TIP_TEXT,
196 LANGUAGE,
197 SOURCE_LANG,
198 CREATED_BY,
199 CREATION_DATE,
200 LAST_UPDATED_BY,
201 LAST_UPDATE_DATE,
202 LAST_UPDATE_LOGIN
203 )
204 SELECT
205 pfs.FORM_ID,
206 pfs.FORM_NAME,
207 pfs.FORM_DESCRIPTION,
208 'N',
209 pfs.TIP_TEXT,
210 lang.language_code,
211 pfs.SOURCE_LANG,
212 pfs.CREATED_BY,
213 sysdate,
214 pfs.LAST_UPDATED_BY,
215 sysdate,
216 pfs.LAST_UPDATE_LOGIN
217 FROM PON_FORMS_SECTIONS_TL pfs, FND_LANGUAGES lang
218 WHERE pfs.language = USERENV('LANG')
219 AND lang.INSTALLED_FLAG in ('I','B')
220 AND NOT EXISTS (SELECT 'x' FROM PON_FORMS_SECTIONS_TL pfs2
221 WHERE pfs2.FORM_ID = pfs.FORM_ID
222 AND pfs2.language = lang.language_code);
223
224
225 END add_language;
226
227 END PON_FORMS_SECTIONS_PVT;