[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_PROGRAM_PKG
Source
1 PACKAGE BODY PV_PARTNER_PROGRAM_PKG as
2 /* $Header: pvxtprgb.pls 120.0 2005/05/27 16:12:37 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PARTNER_PROGRAM_PKG
7 -- Purpose
8 --
9 -- History
10 -- 28-FEB-2002 Ravi.Mikkilineni Created
11 -- 1-APR-2002 Peter.Nixon Modified
12 -- - MEMBERSHIP columns (4) made nullable
13 -- - removed SOURCE_LANG column
14 -- 15-APR-2002 Peter.Nixon Modified
15 -- 19-APR-2002 Peter.Nixon Modified
16 -- - restored SOURCE_LANG column
17 -- - removed PROGRAM_SHORT_NAME column
18 -- - changed PROGRAM_SETUP_TYPE column to PROGRAM_TYPE_ID
19 -- - added CUSTOM_SETUP_ID column
20 -- - added ENABLED_FLAG column
21 -- - added ATTRIBUTE_CATEGORY column
22 -- - added ATTRIBUTE1 thru ATTRIBUTE15 columns
23 -- 26-Jun-2002 - added user_status_id column.
24 -- 28-Jun-2002 - added submit_child_nodes column
25 -- 09-Sep-2002 - added columns inventory_item_id ,inventory_item_org_id,
26 -- bus_user_resp_id ,admin_resp_id,no_fee_flag,qsnr_ttl_all_page_dsp_flag ,
27 -- qsnr_hdr_all_page_dsp_flag ,qsnr_ftr_all_page_dsp_flag ,allow_enrl_wout_chklst_flag.
28 -- qsnr_title ,qsnr_header,qsnr_footer
29 -- 06/27/2003 pukken Code changes for 3 new columns for 11.5.10 enhancements
30 -- NOTE
31 --
32 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
33 -- All rights reserved.
34 --
35 -- End of Comments
36 -- ===============================================================
37
38 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PARTNER_PROGRAM_PKG';
39 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtprgb.pls';
40
41
42 -- ========================================================
43 --
44 -- NAME
45 -- Insert_Row
46 --
47 -- PURPOSE
48 --
49 -- NOTES
50 --
51 -- HISTORY
52 --
53 -- ========================================================
54 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
55 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
56 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
57
58 PROCEDURE Insert_Row(
59 px_program_id IN OUT NOCOPY NUMBER
60 ,p_PROGRAM_TYPE_ID NUMBER
61 ,p_custom_setup_id NUMBER
62 ,p_program_level_code VARCHAR2
63 ,p_program_parent_id NUMBER
64 ,p_program_owner_resource_id NUMBER
65 ,p_program_start_date DATE
66 ,p_program_end_date DATE
67 ,p_allow_enrl_until_date DATE
68 ,p_citem_version_id NUMBER
69 ,p_membership_valid_period NUMBER
70 ,p_membership_period_unit VARCHAR2
71 ,p_process_rule_id NUMBER
72 ,p_prereq_process_rule_Id NUMBER
73 ,p_program_status_code VARCHAR2
74 ,p_submit_child_nodes VARCHAR2
75 ,p_inventory_item_id NUMBER
76 ,p_inventory_item_org_id NUMBER
77 ,p_bus_user_resp_id NUMBER
78 ,p_admin_resp_id NUMBER
79 ,p_no_fee_flag VARCHAR2
80 ,p_vad_invite_allow_flag VARCHAR2
81 ,p_global_mmbr_reqd_flag VARCHAR2
82 ,p_waive_subsidiary_fee_flag VARCHAR2
83 ,p_qsnr_ttl_all_page_dsp_flag VARCHAR2
84 ,p_qsnr_hdr_all_page_dsp_flag VARCHAR2
85 ,p_qsnr_ftr_all_page_dsp_flag VARCHAR2
86 ,p_allow_enrl_wout_chklst_flag VARCHAR2
87 ,p_user_status_id NUMBER
88 ,p_enabled_flag VARCHAR2
89 ,p_attribute_category VARCHAR2
90 ,p_attribute1 VARCHAR2
91 ,p_attribute2 VARCHAR2
92 ,p_attribute3 VARCHAR2
93 ,p_attribute4 VARCHAR2
94 ,p_attribute5 VARCHAR2
95 ,p_attribute6 VARCHAR2
96 ,p_attribute7 VARCHAR2
97 ,p_attribute8 VARCHAR2
98 ,p_attribute9 VARCHAR2
99 ,p_attribute10 VARCHAR2
100 ,p_attribute11 VARCHAR2
101 ,p_attribute12 VARCHAR2
102 ,p_attribute13 VARCHAR2
103 ,p_attribute14 VARCHAR2
104 ,p_attribute15 VARCHAR2
105 ,p_last_update_date DATE
106 ,p_last_updated_by NUMBER
107 ,p_creation_date DATE
108 ,p_created_by NUMBER
109 ,p_last_update_login NUMBER
110 ,p_object_version_number NUMBER
111 ,p_program_name VARCHAR2
112 ,p_program_description VARCHAR2
113 ,p_source_lang VARCHAR2
114 ,p_qsnr_title VARCHAR2
115 ,p_qsnr_header VARCHAR2
116 ,p_qsnr_footer VARCHAR2
117 )
118
119 IS
120
121 BEGIN
122
123 INSERT INTO PV_PARTNER_PROGRAM_B(
124 program_id
125 ,PROGRAM_TYPE_ID
126 ,custom_setup_id
127 ,program_level_code
128 ,program_parent_id
129 ,program_owner_resource_id
130 ,program_start_date
131 ,program_end_date
132 ,allow_enrl_until_date
133 ,citem_version_id
134 ,membership_valid_period
135 ,membership_period_unit
136 ,process_rule_id
137 ,prereq_process_rule_Id
138 ,program_status_code
139 ,submit_child_nodes
140 ,inventory_item_id
141 ,inventory_item_org_id
142 ,bus_user_resp_id
143 ,admin_resp_id
144 ,no_fee_flag
145 ,vad_invite_allow_flag
146 ,global_mmbr_reqd_flag
147 ,waive_subsidiary_fee_flag
148 ,qsnr_ttl_all_page_dsp_flag
149 ,qsnr_hdr_all_page_dsp_flag
150 ,qsnr_ftr_all_page_dsp_flag
151 ,allow_enrl_wout_chklst_flag
152 ,user_status_id
153 ,enabled_flag
154 ,attribute_category
155 ,attribute1
156 ,attribute2
157 ,attribute3
158 ,attribute4
159 ,attribute5
160 ,attribute6
161 ,attribute7
162 ,attribute8
163 ,attribute9
164 ,attribute10
165 ,attribute11
166 ,attribute12
167 ,attribute13
168 ,attribute14
169 ,attribute15
170 ,last_update_date
171 ,last_updated_by
172 ,creation_date
173 ,created_by
174 ,last_update_login
175 ,object_version_number
176 ) VALUES (
177 DECODE( px_program_id, NULL, px_program_id, FND_API.g_miss_num, NULL, px_program_id)
178 ,DECODE( p_PROGRAM_TYPE_ID, NULL, p_PROGRAM_TYPE_ID, FND_API.g_miss_num, NULL, p_PROGRAM_TYPE_ID)
179 ,DECODE( p_custom_setup_id, NULL,p_custom_setup_id, FND_API.g_miss_num, NULL, p_custom_setup_id)
180 ,DECODE( p_program_level_code, NULL, p_program_level_code, FND_API.g_miss_char, NULL, p_program_level_code)
181 ,DECODE( p_program_parent_id, NULL, p_program_parent_id,FND_API.g_miss_num, NULL, p_program_parent_id)
182 ,DECODE( p_program_owner_resource_id, NULL, p_program_owner_resource_id,FND_API.g_miss_num, NULL, p_program_owner_resource_id)
183 ,DECODE( p_program_start_date, NULL, p_program_start_date, FND_API.g_miss_date, NULL, p_program_start_date)
184 ,DECODE( p_program_end_date, NULL, p_program_end_date,FND_API.g_miss_date, NULL, p_program_end_date)
185 ,DECODE( p_allow_enrl_until_date, NULL, p_allow_enrl_until_date,FND_API.g_miss_date, NULL, p_allow_enrl_until_date)
186 ,DECODE( p_citem_version_id , NULL, p_citem_version_id , FND_API.g_miss_num, NULL, p_citem_version_id)
187 ,DECODE( p_membership_valid_period, NULL, p_membership_valid_period, FND_API.g_miss_num, NULL, p_membership_valid_period)
188 ,DECODE( p_membership_period_unit, NULL, p_membership_period_unit,FND_API.g_miss_char, NULL, p_membership_period_unit)
189 ,DECODE( p_process_rule_id, NULL, p_process_rule_id, FND_API.g_miss_num, NULL, p_process_rule_id)
190 ,DECODE( p_prereq_process_rule_Id, NULL, p_prereq_process_rule_Id, FND_API.g_miss_num, NULL, p_prereq_process_rule_Id)
191 ,DECODE( p_program_status_code, NULL, p_program_status_code,FND_API.g_miss_char, NULL, p_program_status_code)
192 ,DECODE( p_submit_child_nodes, NULL, p_submit_child_nodes,FND_API.g_miss_char, NULL, p_submit_child_nodes)
193 ,DECODE( p_inventory_item_id, NULL, p_inventory_item_id,FND_API.g_miss_num, NULL, p_inventory_item_id)
194 ,DECODE( p_inventory_item_org_id, NULL, p_inventory_item_org_id,FND_API.g_miss_num, NULL, p_inventory_item_org_id)
195 ,DECODE( p_bus_user_resp_id, NULL, p_bus_user_resp_id,FND_API.g_miss_num, NULL, p_bus_user_resp_id)
196 ,DECODE( p_admin_resp_id, NULL, p_admin_resp_id,FND_API.g_miss_num, NULL, p_admin_resp_id)
197 ,DECODE( p_no_fee_flag, NULL, p_no_fee_flag,FND_API.g_miss_char, NULL, p_no_fee_flag)
198 ,DECODE( p_vad_invite_allow_flag, NULL, p_vad_invite_allow_flag,FND_API.g_miss_char, NULL, p_vad_invite_allow_flag)
199 ,DECODE( p_global_mmbr_reqd_flag, NULL, p_global_mmbr_reqd_flag,FND_API.g_miss_char, NULL, p_global_mmbr_reqd_flag)
200 ,DECODE( p_waive_subsidiary_fee_flag, NULL, p_waive_subsidiary_fee_flag,FND_API.g_miss_char, NULL, p_waive_subsidiary_fee_flag)
201 ,DECODE( p_qsnr_ttl_all_page_dsp_flag , NULL, p_qsnr_ttl_all_page_dsp_flag ,FND_API.g_miss_char, NULL, p_qsnr_ttl_all_page_dsp_flag )
202 ,DECODE( p_qsnr_hdr_all_page_dsp_flag , NULL, p_qsnr_hdr_all_page_dsp_flag ,FND_API.g_miss_char, NULL, p_qsnr_hdr_all_page_dsp_flag )
203 ,DECODE( p_qsnr_ftr_all_page_dsp_flag, NULL, p_qsnr_ftr_all_page_dsp_flag,FND_API.g_miss_char, NULL, p_qsnr_ftr_all_page_dsp_flag)
204 ,DECODE( p_allow_enrl_wout_chklst_flag, NULL, p_allow_enrl_wout_chklst_flag,FND_API.g_miss_char, NULL, p_allow_enrl_wout_chklst_flag)
205 ,DECODE( p_user_status_id, NULL, p_user_status_id, FND_API.g_miss_num, NULL, p_user_status_id)
206 ,DECODE( p_enabled_flag, NULL, p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag)
207 ,DECODE( p_attribute_category, NULL, p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category)
208 ,DECODE( p_attribute1, NULL, p_attribute1, FND_API.g_miss_char, NULL, p_attribute1)
209 ,DECODE( p_attribute2, NULL, p_attribute2, FND_API.g_miss_char, NULL, p_attribute2)
210 ,DECODE( p_attribute3, NULL, p_attribute3, FND_API.g_miss_char, NULL, p_attribute3)
211 ,DECODE( p_attribute4, NULL, p_attribute4 , FND_API.g_miss_char, NULL, p_attribute4)
212 ,DECODE( p_attribute5, NULL, p_attribute5, FND_API.g_miss_char, NULL, p_attribute5)
213 ,DECODE( p_attribute6, NULL, p_attribute6, FND_API.g_miss_char, NULL, p_attribute6)
214 ,DECODE( p_attribute7, NULL, p_attribute7, FND_API.g_miss_char, NULL, p_attribute7)
215 ,DECODE( p_attribute8, NULL, p_attribute8, FND_API.g_miss_char, NULL, p_attribute8)
216 ,DECODE( p_attribute9, NULL, p_attribute9, FND_API.g_miss_char, NULL, p_attribute9)
217 ,DECODE( p_attribute10, NULL, p_attribute10, FND_API.g_miss_char, NULL, p_attribute10)
218 ,DECODE( p_attribute11, NULL, p_attribute11, FND_API.g_miss_char, NULL, p_attribute11)
219 ,DECODE( p_attribute12, NULL, p_attribute12, FND_API.g_miss_char, NULL, p_attribute12)
220 ,DECODE( p_attribute13, NULL, p_attribute13, FND_API.g_miss_char, NULL, p_attribute13)
221 ,DECODE( p_attribute14, NULL, p_attribute14, FND_API.g_miss_char, NULL, p_attribute14)
222 ,DECODE( p_attribute15, NULL, p_attribute15, FND_API.g_miss_char, NULL, p_attribute15)
223 ,DECODE( p_last_update_date, NULL, p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
224 ,DECODE( p_last_updated_by, NULL, p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
225 ,DECODE( p_creation_date, NULL, p_creation_date, FND_API.g_miss_date, NULL, p_creation_date)
226 ,DECODE( p_created_by, NULL, p_created_by, FND_API.g_miss_num, NULL, p_created_by)
227 ,DECODE( p_last_update_login, NULL, p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
228 ,DECODE( p_object_version_number,NULL, p_object_version_number, FND_API.g_miss_num, NULL, p_object_version_number)
229 );
230
231 INSERT INTO PV_PARTNER_PROGRAM_TL(
232 program_id
233 ,last_update_date
234 ,last_updated_by
235 ,creation_date
236 ,created_by
237 ,last_update_login
238 ,language
242 ,qsnr_title
239 ,source_lang
240 ,program_name
241 ,program_description
243 ,qsnr_header
244 ,qsnr_footer
245
246 )
247 SELECT
248 DECODE( px_program_id, NULL, px_program_id, FND_API.g_miss_num, NULL, px_program_id)
249 --DECODE( px_program_id, FND_API.g_miss_num, NULL, px_program_id)
250 ,SYSDATE
251 ,FND_GLOBAL.user_id
252 ,SYSDATE
253 ,FND_GLOBAL.user_id
254 ,FND_GLOBAL.conc_login_id
255 ,l.language_code
256 ,USERENV('LANG')
257 ,DECODE( p_program_name, NULL, p_program_name, FND_API.g_miss_char, NULL, p_program_name)
258 ,DECODE( p_program_description,NULL, p_program_description, FND_API.g_miss_char, NULL, p_program_description)
259 ,DECODE( p_qsnr_title, NULL, p_qsnr_title, FND_API.g_miss_char, NULL, p_qsnr_title)
260 ,DECODE( p_qsnr_header,NULL, p_qsnr_header, FND_API.g_miss_char, NULL,p_qsnr_header)
261 ,DECODE( p_qsnr_footer, NULL, p_qsnr_footer, FND_API.g_miss_char, NULL,p_qsnr_footer)
262 FROM FND_LANGUAGES l
263 WHERE l.installed_flag in ('I', 'B')
264 AND NOT EXISTS(
265 SELECT NULL
266 FROM PV_PARTNER_PROGRAM_TL t
267 --WHERE t.program_id = DECODE( px_program_id, FND_API.g_miss_num, NULL, px_program_id)
268 WHERE t.program_id = DECODE( px_program_id, NULL, px_program_id, FND_API.g_miss_num, NULL, px_program_id)
269 AND t.language = l.language_code );
270
271 END Insert_Row;
272
273
274
275 -- ========================================================
276 --
277 -- NAME
278 -- Update_Row
279 --
280 -- PURPOSE
281 --
282 -- NOTES
283 --
284 -- HISTORY
285 --
286 -- ========================================================
287 PROCEDURE Update_Row(
288 p_program_id NUMBER
289 ,p_PROGRAM_TYPE_ID NUMBER
290 ,p_custom_setup_id NUMBER
291 ,p_program_level_code VARCHAR2
292 ,p_program_parent_id NUMBER
293 ,p_program_owner_resource_id NUMBER
294 ,p_program_start_date DATE
295 ,p_program_end_date DATE
296 ,p_allow_enrl_until_date DATE
297 ,p_citem_version_id NUMBER
298 ,p_membership_valid_period NUMBER
299 ,p_membership_period_unit VARCHAR2
300 ,p_process_rule_id NUMBER
301 ,p_prereq_process_rule_Id NUMBER
302 ,p_program_status_code VARCHAR2
303 ,p_submit_child_nodes VARCHAR2
304 ,p_inventory_item_id NUMBER
305 ,p_inventory_item_org_id NUMBER
306 ,p_bus_user_resp_id NUMBER
307 ,p_admin_resp_id NUMBER
308 ,p_no_fee_flag VARCHAR2
309 ,p_vad_invite_allow_flag VARCHAR2
310 ,p_global_mmbr_reqd_flag VARCHAR2
311 ,p_waive_subsidiary_fee_flag VARCHAR2
312 ,p_qsnr_ttl_all_page_dsp_flag VARCHAR2
313 ,p_qsnr_hdr_all_page_dsp_flag VARCHAR2
314 ,p_qsnr_ftr_all_page_dsp_flag VARCHAR2
315 ,p_allow_enrl_wout_chklst_flag VARCHAR2
316 ,p_user_status_id NUMBER
317 ,p_enabled_flag VARCHAR2
318 ,p_attribute_category VARCHAR2
319 ,p_attribute1 VARCHAR2
320 ,p_attribute2 VARCHAR2
321 ,p_attribute3 VARCHAR2
322 ,p_attribute4 VARCHAR2
323 ,p_attribute5 VARCHAR2
324 ,p_attribute6 VARCHAR2
325 ,p_attribute7 VARCHAR2
326 ,p_attribute8 VARCHAR2
327 ,p_attribute9 VARCHAR2
328 ,p_attribute10 VARCHAR2
329 ,p_attribute11 VARCHAR2
330 ,p_attribute12 VARCHAR2
331 ,p_attribute13 VARCHAR2
332 ,p_attribute14 VARCHAR2
333 ,p_attribute15 VARCHAR2
334 ,p_last_update_date DATE
335 ,p_last_updated_by NUMBER
336 ,p_last_update_login NUMBER
337 ,p_object_version_number NUMBER
338 ,p_program_name VARCHAR2
339 ,p_program_description VARCHAR2
340 ,p_qsnr_title VARCHAR2
341 ,p_qsnr_header VARCHAR2
342 ,p_qsnr_footer VARCHAR2
343 )
344
345 IS
346
347 BEGIN
348
349 IF (PV_DEBUG_HIGH_ON) THEN
350
351
352
353 PVX_Utility_PVT.debug_message('Within PV_PARTNER_PROGRAM_PKG.UPDATE_ROW API: ');
354
355 END IF;
356 IF (PV_DEBUG_HIGH_ON) THEN
357
361
358 PVX_Utility_PVT.debug_message('Within PV_PARTNER_PROGRAM_PKG.UPDATE_ROW API : object_version_number ' ||p_object_version_number );
359 END IF;
360
362 UPDATE PV_PARTNER_PROGRAM_B
363 SET
364
365 program_id = DECODE( p_program_id, NULL ,program_id, FND_API.g_miss_num, NULL , p_program_id)
366 ,PROGRAM_TYPE_ID = DECODE( p_PROGRAM_TYPE_ID, NULL ,PROGRAM_TYPE_ID,FND_API.g_miss_num, NULL , p_PROGRAM_TYPE_ID)
367 ,custom_setup_id = DECODE( p_custom_setup_id, NULL ,custom_setup_id, FND_API.g_miss_num, NULL , p_custom_setup_id)
368 ,program_level_code = DECODE( p_program_level_code, NULL , program_level_code, FND_API.g_miss_char, NULL , p_program_level_code)
369 ,program_parent_id = DECODE( p_program_parent_id, NULL , program_parent_id, FND_API.g_miss_num, NULL , p_program_parent_id)
370 ,program_owner_resource_id = DECODE( p_program_owner_resource_id, NULL , program_owner_resource_id, FND_API.g_miss_num, NULL ,p_program_owner_resource_id)
371 ,program_start_date = DECODE( p_program_start_date, NULL , program_start_date, FND_API.g_miss_date, NULL , p_program_start_date)
372 ,program_end_date = DECODE( p_program_end_date, NULL , program_end_date, FND_API.g_miss_date, NULL ,p_program_end_date)
373 ,allow_enrl_until_date = DECODE( p_allow_enrl_until_date, NULL , allow_enrl_until_date, FND_API.g_miss_date, NULL ,p_allow_enrl_until_date)
374 ,citem_version_id = DECODE( p_citem_version_id, NULL ,citem_version_id, FND_API.g_miss_num, NULL , p_citem_version_id)
375 ,membership_valid_period = DECODE( p_membership_valid_period, NULL ,membership_valid_period, FND_API.g_miss_num, NULL , p_membership_valid_period)
376 ,membership_period_unit = DECODE( p_membership_period_unit, NULL , membership_period_unit, FND_API.g_miss_char, NULL , p_membership_period_unit)
377 ,process_rule_id = DECODE( p_process_rule_id, NULL ,process_rule_id, FND_API.g_miss_num, NULL , p_process_rule_id)
378 ,prereq_process_rule_Id = DECODE( p_prereq_process_rule_Id, NULL ,prereq_process_rule_Id, FND_API.g_miss_num, NULL , p_prereq_process_rule_Id)
379 ,program_status_code = DECODE( p_program_status_code, NULL ,program_status_code,FND_API.g_miss_char, NULL , p_program_status_code)
380 ,submit_child_nodes = DECODE( p_submit_child_nodes, NULL ,submit_child_nodes,FND_API.g_miss_char, NULL , p_submit_child_nodes)
381 ,inventory_item_id = DECODE( p_inventory_item_id, NULL , inventory_item_id, FND_API.g_miss_num, NULL , p_inventory_item_id)
382 ,inventory_item_org_id = DECODE( p_inventory_item_org_id, NULL , inventory_item_org_id, FND_API.g_miss_num, NULL , p_inventory_item_org_id)
383 ,bus_user_resp_id = DECODE( p_bus_user_resp_id, NULL , bus_user_resp_id, FND_API.g_miss_num, NULL , p_bus_user_resp_id)
384 ,admin_resp_id = DECODE( p_admin_resp_id, NULL , admin_resp_id, FND_API.g_miss_num, NULL , p_admin_resp_id)
385 ,no_fee_flag = DECODE( p_no_fee_flag, NULL ,no_fee_flag,FND_API.g_miss_char, NULL , p_no_fee_flag)
386 ,vad_invite_allow_flag = DECODE( p_vad_invite_allow_flag, NULL ,vad_invite_allow_flag,FND_API.g_miss_char, NULL , p_vad_invite_allow_flag)
387 ,global_mmbr_reqd_flag = DECODE( p_global_mmbr_reqd_flag, NULL ,global_mmbr_reqd_flag,FND_API.g_miss_char, NULL , p_global_mmbr_reqd_flag)
388 ,waive_subsidiary_fee_flag = DECODE( p_waive_subsidiary_fee_flag, NULL ,waive_subsidiary_fee_flag,FND_API.g_miss_char, NULL , p_waive_subsidiary_fee_flag)
389 ,qsnr_ttl_all_page_dsp_flag = DECODE( p_qsnr_ttl_all_page_dsp_flag , NULL ,qsnr_ttl_all_page_dsp_flag ,FND_API.g_miss_char, NULL , p_qsnr_ttl_all_page_dsp_flag )
390 ,qsnr_hdr_all_page_dsp_flag = DECODE( p_qsnr_hdr_all_page_dsp_flag, NULL ,qsnr_hdr_all_page_dsp_flag,FND_API.g_miss_char, NULL , p_qsnr_hdr_all_page_dsp_flag)
391 ,qsnr_ftr_all_page_dsp_flag = DECODE( p_qsnr_ftr_all_page_dsp_flag , NULL ,qsnr_ftr_all_page_dsp_flag ,FND_API.g_miss_char, NULL , p_qsnr_ftr_all_page_dsp_flag )
392 ,allow_enrl_wout_chklst_flag= DECODE( p_allow_enrl_wout_chklst_flag, NULL ,allow_enrl_wout_chklst_flag,FND_API.g_miss_char, NULL , p_allow_enrl_wout_chklst_flag)
393 ,user_status_id = DECODE( p_user_status_id, NULL ,user_status_id, FND_API.g_miss_num, NULL , p_user_status_id)
394 ,enabled_flag = DECODE( p_enabled_flag, NULL ,enabled_flag,FND_API.g_miss_char, NULL , p_enabled_flag)
395 ,attribute_category = DECODE( p_attribute_category, attribute_category, FND_API.g_miss_char, NULL , p_attribute_category)
396 ,attribute1 = DECODE( p_attribute1, NULL , attribute1,FND_API.g_miss_char, NULL , p_attribute1)
397 ,attribute2 = DECODE( p_attribute2, NULL ,attribute2, FND_API.g_miss_char, NULL , p_attribute2)
398 ,attribute3 = DECODE( p_attribute3, NULL , attribute3, FND_API.g_miss_char, NULL , p_attribute3)
399 ,attribute4 = DECODE( p_attribute4, NULL ,attribute4, FND_API.g_miss_char, NULL , p_attribute4)
400 ,attribute5 = DECODE( p_attribute5, NULL , attribute5, FND_API.g_miss_char, NULL ,p_attribute5)
401 ,attribute6 = DECODE( p_attribute6, NULL ,attribute6, FND_API.g_miss_char, NULL ,p_attribute6)
402 ,attribute7 = DECODE( p_attribute7, NULL ,attribute7, FND_API.g_miss_char, NULL , p_attribute7)
403 ,attribute8 = DECODE( p_attribute8, NULL ,attribute8, FND_API.g_miss_char, NULL , p_attribute8)
404 ,attribute9 = DECODE( p_attribute9, NULL ,attribute9, FND_API.g_miss_char, NULL , p_attribute9)
405 ,attribute10 = DECODE( p_attribute10, NULL ,attribute10, FND_API.g_miss_char, NULL , p_attribute10)
409 ,attribute14 = DECODE( p_attribute14, NULL ,attribute14, FND_API.g_miss_char, NULL , p_attribute14)
406 ,attribute11 = DECODE( p_attribute11, NULL ,attribute11,FND_API.g_miss_char, NULL , p_attribute11)
407 ,attribute12 = DECODE( p_attribute12, NULL ,attribute12, FND_API.g_miss_char, NULL , p_attribute12)
408 ,attribute13 = DECODE( p_attribute13, NULL ,attribute13, FND_API.g_miss_char, NULL , p_attribute13)
410 ,attribute15 = DECODE( p_attribute15, NULL , attribute15, FND_API.g_miss_char, NULL , p_attribute15)
411 ,last_update_date = DECODE( p_last_update_date, NULL , last_update_date, FND_API.g_miss_date, NULL , p_last_update_date)
412 ,last_updated_by = DECODE( p_last_updated_by, NULL ,last_updated_by, FND_API.g_miss_num, NULL , p_last_updated_by)
413 ,last_update_login = DECODE( p_last_update_login, NULL , last_update_login, FND_API.g_miss_num, NULL , p_last_update_login)
414 ,object_version_number = DECODE( p_object_version_number, NULL ,object_version_number, FND_API.g_miss_num, NULL , p_object_version_number+1)
415
416
417 WHERE PROGRAM_ID = p_program_id
418 AND object_version_number = p_object_version_number;
419
420 IF (SQL%NOTFOUND) THEN
421 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
422 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
423 FND_MSG_PUB.add;
424 END IF;
425 RAISE FND_API.g_exc_error;
426 END IF;
427
428 Update PV_PARTNER_PROGRAM_TL
429 SET
430 last_update_date = SYSDATE
431 ,last_updated_by = FND_GLOBAL.user_id
432 ,last_update_login = FND_GLOBAL.conc_login_id
433 ,source_lang = USERENV('LANG')
434 ,program_name = DECODE( p_program_name, NULL, program_name, FND_API.g_miss_char, NULL, p_program_name)
435 ,program_description = DECODE( p_program_description, NULL, program_description, FND_API.g_miss_char, NULL, p_program_description)
436 ,qsnr_title = DECODE( p_qsnr_title, NULL, qsnr_title , FND_API.g_miss_char, NULL, p_qsnr_title )
437 ,qsnr_header = DECODE( p_qsnr_header, NULL, qsnr_header, FND_API.g_miss_char, NULL, p_qsnr_header)
438 ,qsnr_footer = DECODE( p_qsnr_footer, NULL,qsnr_footer, FND_API.g_miss_char, NULL, p_qsnr_footer)
439 WHERE PROGRAM_ID = p_program_id
440 AND USERENV('LANG') IN (language, source_lang);
441
442 IF (SQL%NOTFOUND) THEN
443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
444 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
445 FND_MSG_PUB.add;
446 END IF;
447 RAISE FND_API.g_exc_error;
448 END IF;
449
450 END Update_Row;
451
452
453
454 -- ========================================================
455 --
456 -- NAME
457 -- Delete_Row
458 --
459 -- PURPOSE
460 --
461 -- NOTES
462 --
463 -- HISTORY
464 --
465 -- ========================================================
466 PROCEDURE Delete_Row(
467 p_program_id NUMBER
468 ,p_object_version_number NUMBER
469 )
470 IS
471
472 BEGIN
473
474 UPDATE PV_PARTNER_PROGRAM_B
475 SET
476
477 program_id = DECODE( p_program_id, NULL ,program_id, FND_API.g_miss_num, NULL , p_program_id)
478 ,enabled_flag ='N'
479 ,last_update_date = SYSDATE
480 ,last_updated_by = FND_GLOBAL.user_id
481 ,last_update_login = FND_GLOBAL.conc_login_id
482 ,object_version_number = DECODE( p_object_version_number, NULL ,object_version_number, FND_API.g_miss_num, NULL , p_object_version_number+1)
483
484
485 WHERE PROGRAM_ID = p_program_id
486 AND object_version_number = p_object_version_number;
487
488 IF (SQL%NOTFOUND) THEN
489 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
490 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
491 FND_MSG_PUB.add;
492 END IF;
493 RAISE FND_API.g_exc_error;
494 END IF;
495
496 Update PV_PARTNER_PROGRAM_TL
497 SET
498 last_update_date = SYSDATE
499 ,last_updated_by = FND_GLOBAL.user_id
500 ,last_update_login = FND_GLOBAL.conc_login_id
501 ,source_lang = USERENV('LANG')
502 --,program_name = DECODE( p_program_name, NULL, program_name, FND_API.g_miss_char, NULL, p_program_name)
503 --,program_description = DECODE( p_program_description, NULL, program_description, FND_API.g_miss_char, NULL, p_program_description)
504 WHERE PROGRAM_ID = p_program_id
505 AND USERENV('LANG') IN (language, source_lang);
506
507 IF (SQL%NOTFOUND) THEN
508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
509 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
510 FND_MSG_PUB.add;
511 END IF;
512 RAISE FND_API.g_exc_error;
513 END IF;
514
515
516 END Delete_Row ;
517
518
519
520
521 -- ========================================================
522 --
523 -- NAME
524 -- Lock_Row
525 --
526 -- PURPOSE
527 --
528 -- NOTES
529 --
530 -- HISTORY
531 --
532 -- ========================================================
536 ,p_custom_setup_id NUMBER
533 PROCEDURE Lock_Row(
534 px_program_id IN OUT NOCOPY NUMBER
535 ,p_PROGRAM_TYPE_ID NUMBER
537 ,p_program_level_code VARCHAR2
538 ,p_program_parent_id NUMBER
539 ,p_program_owner_resource_id NUMBER
540 ,p_program_start_date DATE
541 ,p_program_end_date DATE
542 ,p_allow_enrl_until_date DATE
543 ,p_citem_version_id NUMBER
544 ,p_membership_valid_period NUMBER
545 ,p_membership_period_unit VARCHAR2
546 ,p_process_rule_id NUMBER
547 ,p_prereq_process_rule_Id NUMBER
548 ,p_program_status_code VARCHAR2
549 ,p_submit_child_nodes VARCHAR2
550 ,p_inventory_item_id NUMBER
551 ,p_inventory_item_org_id NUMBER
552 ,p_bus_user_resp_id NUMBER
553 ,p_admin_resp_id NUMBER
554 ,p_no_fee_flag VARCHAR2
555 ,p_vad_invite_allow_flag VARCHAR2
556 ,p_global_mmbr_reqd_flag VARCHAR2
557 ,p_waive_subsidiary_fee_flag VARCHAR2
558 ,p_qsnr_ttl_all_page_dsp_flag VARCHAR2
559 ,p_qsnr_hdr_all_page_dsp_flag VARCHAR2
560 ,p_qsnr_ftr_all_page_dsp_flag VARCHAR2
561 ,p_allow_enrl_wout_chklst_flag VARCHAR2
562 ,p_user_status_id NUMBER
563 ,p_enabled_flag VARCHAR2
564 ,p_attribute_category VARCHAR2
565 ,p_attribute1 VARCHAR2
566 ,p_attribute2 VARCHAR2
567 ,p_attribute3 VARCHAR2
568 ,p_attribute4 VARCHAR2
569 ,p_attribute5 VARCHAR2
570 ,p_attribute6 VARCHAR2
571 ,p_attribute7 VARCHAR2
572 ,p_attribute8 VARCHAR2
573 ,p_attribute9 VARCHAR2
574 ,p_attribute10 VARCHAR2
575 ,p_attribute11 VARCHAR2
576 ,p_attribute12 VARCHAR2
577 ,p_attribute13 VARCHAR2
578 ,p_attribute14 VARCHAR2
579 ,p_attribute15 VARCHAR2
580 ,p_last_update_date DATE
581 ,p_last_updated_by NUMBER
582 ,p_creation_date DATE
583 ,p_created_by NUMBER
584 ,p_last_update_login NUMBER
585 ,px_object_version_number IN OUT NOCOPY NUMBER
586 )
587
588 IS
589 CURSOR C IS
590 SELECT *
591 FROM PV_PARTNER_PROGRAM_B
592 WHERE PROGRAM_ID = px_program_id
593 FOR UPDATE of PROGRAM_ID NOWAIT;
594 Recinfo C%ROWTYPE;
595
596 BEGIN
597 OPEN c;
598 FETCH c INTO Recinfo;
599 If (c%NOTFOUND) then
600 CLOSE c;
601 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
602 APP_EXCEPTION.RAISE_EXCEPTION;
603 END IF;
604 CLOSE C;
605 IF (
606 ( Recinfo.program_id = px_program_id)
607 AND ( ( Recinfo.PROGRAM_TYPE_ID = p_PROGRAM_TYPE_ID)
608 OR ( ( Recinfo.PROGRAM_TYPE_ID IS NULL )
609 AND ( p_PROGRAM_TYPE_ID IS NULL )))
610 AND ( ( Recinfo.program_level_code = p_program_level_code)
611 OR ( ( Recinfo.program_level_code IS NULL )
612 AND ( p_program_level_code IS NULL )))
613 AND ( ( Recinfo.program_parent_id = p_program_parent_id)
614 OR ( ( Recinfo.program_parent_id IS NULL )
615 AND ( p_program_parent_id IS NULL )))
616 AND ( ( Recinfo.program_owner_resource_id = p_program_owner_resource_id)
617 OR ( ( Recinfo.program_owner_resource_id IS NULL )
618 AND ( p_program_owner_resource_id IS NULL )))
619 AND ( ( Recinfo.program_start_date = p_program_start_date)
620 OR ( ( Recinfo.program_start_date IS NULL )
621 AND ( p_program_start_date IS NULL )))
622 AND ( ( Recinfo.program_end_date = p_program_end_date)
623 OR ( ( Recinfo.program_end_date IS NULL )
624 AND ( p_program_end_date IS NULL )))
625 AND ( ( Recinfo.allow_enrl_until_date = p_allow_enrl_until_date)
626 OR ( ( Recinfo.allow_enrl_until_date IS NULL )
627 AND ( p_allow_enrl_until_date IS NULL )))
628 AND ( ( Recinfo.citem_version_id= p_citem_version_id)
629 OR ( ( Recinfo.citem_version_id IS NULL )
630 AND ( p_citem_version_id IS NULL )))
631 AND ( ( Recinfo.membership_valid_period = p_membership_valid_period)
632 OR ( ( Recinfo.membership_valid_period IS NULL )
633 AND ( p_membership_valid_period IS NULL )))
634 AND ( ( Recinfo.membership_period_unit = p_membership_period_unit)
635 OR ( ( Recinfo.membership_period_unit IS NULL )
636 AND ( p_membership_period_unit IS NULL )))
640 AND ( ( Recinfo.prereq_process_rule_Id = p_prereq_process_rule_Id)
637 AND ( ( Recinfo.process_rule_id = p_process_rule_id)
638 OR ( ( Recinfo.process_rule_id IS NULL )
639 AND ( p_process_rule_id IS NULL )))
641 OR ( ( Recinfo.prereq_process_rule_Id IS NULL )
642 AND ( p_prereq_process_rule_Id IS NULL )))
643 AND ( ( Recinfo.program_status_code = p_program_status_code)
644 OR ( ( Recinfo.program_status_code IS NULL )
645 AND ( p_program_status_code IS NULL )))
646 AND ( ( Recinfo.submit_child_nodes = p_submit_child_nodes)
647 OR ( ( Recinfo.submit_child_nodes IS NULL )
648 AND ( p_submit_child_nodes IS NULL )))
649 AND ( ( Recinfo.inventory_item_id = p_inventory_item_id )
650 OR ( ( Recinfo.inventory_item_id IS NULL )
651 AND ( p_inventory_item_id IS NULL )))
652 AND ( ( Recinfo.inventory_item_org_id = p_inventory_item_org_id )
653 OR ( ( Recinfo.inventory_item_org_id IS NULL )
654 AND ( p_inventory_item_org_id IS NULL )))
655 AND ( ( Recinfo.bus_user_resp_id = p_bus_user_resp_id)
656 OR ( ( Recinfo.bus_user_resp_id IS NULL )
657 AND ( p_bus_user_resp_id IS NULL )))
658 AND ( ( Recinfo.admin_resp_id = p_admin_resp_id)
659 OR ( ( Recinfo.admin_resp_id IS NULL )
660 AND ( p_admin_resp_id IS NULL )))
661 AND ( ( Recinfo.no_fee_flag = p_no_fee_flag)
662 OR ( ( Recinfo.no_fee_flag IS NULL )
663 AND ( p_no_fee_flag IS NULL )))
664 AND ( ( Recinfo.vad_invite_allow_flag = p_vad_invite_allow_flag)
665 OR ( ( Recinfo.vad_invite_allow_flag IS NULL )
666 AND ( p_vad_invite_allow_flag IS NULL )))
667 AND ( ( Recinfo.global_mmbr_reqd_flag = p_global_mmbr_reqd_flag)
668 OR ( ( Recinfo.global_mmbr_reqd_flag IS NULL )
669 AND ( p_global_mmbr_reqd_flag IS NULL )))
670 AND ( ( Recinfo.waive_subsidiary_fee_flag = p_waive_subsidiary_fee_flag)
671 OR ( ( Recinfo.waive_subsidiary_fee_flag IS NULL )
672 AND ( p_waive_subsidiary_fee_flag IS NULL )))
673 AND ( ( Recinfo.qsnr_ttl_all_page_dsp_flag = p_qsnr_ttl_all_page_dsp_flag )
674 OR ( ( Recinfo.qsnr_ttl_all_page_dsp_flag IS NULL )
675 AND ( p_qsnr_ttl_all_page_dsp_flag IS NULL )))
676 AND ( ( Recinfo.qsnr_hdr_all_page_dsp_flag = p_qsnr_hdr_all_page_dsp_flag)
677 OR ( ( Recinfo.qsnr_hdr_all_page_dsp_flag IS NULL )
678 AND ( p_qsnr_hdr_all_page_dsp_flag IS NULL )))
679 AND ( ( Recinfo.qsnr_ftr_all_page_dsp_flag= p_qsnr_ftr_all_page_dsp_flag)
680 OR ( ( Recinfo.qsnr_ftr_all_page_dsp_flag IS NULL )
681 AND ( p_qsnr_ftr_all_page_dsp_flag IS NULL )))
682 AND ( ( Recinfo.allow_enrl_wout_chklst_flag = p_allow_enrl_wout_chklst_flag)
683 OR ( ( Recinfo.allow_enrl_wout_chklst_flag IS NULL )
684 AND ( p_allow_enrl_wout_chklst_flag IS NULL )))
685 AND ( ( Recinfo.user_status_id = p_user_status_id)
686 OR ( ( Recinfo.user_status_id IS NULL )
687 AND ( p_user_status_id IS NULL )))
688 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
689 OR ( ( Recinfo.enabled_flag IS NULL )
690 AND ( p_enabled_flag IS NULL )))
691 AND ( ( Recinfo.attribute_category = p_attribute_category)
692 OR ( ( Recinfo.attribute_category IS NULL )
693 AND ( p_attribute_category IS NULL )))
694 AND ( ( Recinfo.attribute1 = p_attribute1)
695 OR ( ( Recinfo.attribute1 IS NULL )
696 AND ( p_attribute1 IS NULL )))
697 AND ( ( Recinfo.attribute2 = p_attribute2)
698 OR ( ( Recinfo.attribute2 IS NULL )
699 AND ( p_attribute2 IS NULL )))
700 AND ( ( Recinfo.attribute3 = p_attribute3)
701 OR ( ( Recinfo.attribute3 IS NULL )
702 AND ( p_attribute3 IS NULL )))
703 AND ( ( Recinfo.attribute4 = p_attribute4)
704 OR ( ( Recinfo.attribute4 IS NULL )
705 AND ( p_attribute4 IS NULL )))
706 AND ( ( Recinfo.attribute5 = p_attribute5)
707 OR ( ( Recinfo.attribute5 IS NULL )
708 AND ( p_attribute5 IS NULL )))
709 AND ( ( Recinfo.attribute6 = p_attribute6)
710 OR ( ( Recinfo.attribute6 IS NULL )
711 AND ( p_attribute6 IS NULL )))
712 AND ( ( Recinfo.attribute7 = p_attribute7)
713 OR ( ( Recinfo.attribute7 IS NULL )
714 AND ( p_attribute7 IS NULL )))
715 AND ( ( Recinfo.attribute8 = p_attribute8)
716 OR ( ( Recinfo.attribute8 IS NULL )
717 AND ( p_attribute8 IS NULL )))
718 AND ( ( Recinfo.attribute9 = p_attribute9)
719 OR ( ( Recinfo.attribute9 IS NULL )
720 AND ( p_attribute9 IS NULL )))
721 AND ( ( Recinfo.attribute10 = p_attribute10)
722 OR ( ( Recinfo.attribute10 IS NULL )
723 AND ( p_attribute10 IS NULL )))
724 AND ( ( Recinfo.attribute11 = p_attribute11)
725 OR ( ( Recinfo.attribute11 IS NULL )
726 AND ( p_attribute11 IS NULL )))
727 AND ( ( Recinfo.attribute12 = p_attribute12)
728 OR ( ( Recinfo.attribute12 IS NULL )
729 AND ( p_attribute12 IS NULL )))
730 AND ( ( Recinfo.attribute13 = p_attribute13)
734 OR ( ( Recinfo.attribute14 IS NULL )
731 OR ( ( Recinfo.attribute13 IS NULL )
732 AND ( p_attribute13 IS NULL )))
733 AND ( ( Recinfo.attribute14 = p_attribute14)
735 AND ( p_attribute14 IS NULL )))
736 AND ( ( Recinfo.attribute15 = p_attribute15)
737 OR ( ( Recinfo.attribute15 IS NULL )
738 AND ( p_attribute15 IS NULL )))
739 AND ( ( Recinfo.last_update_date = p_last_update_date)
740 OR ( ( Recinfo.last_update_date IS NULL )
741 AND ( p_last_update_date IS NULL )))
742 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
743 OR ( ( Recinfo.last_updated_by IS NULL )
744 AND ( p_last_updated_by IS NULL )))
745 AND ( ( Recinfo.creation_date = p_creation_date)
746 OR ( ( Recinfo.creation_date IS NULL )
747 AND ( p_creation_date IS NULL )))
748 AND ( ( Recinfo.created_by = p_created_by)
749 OR ( ( Recinfo.created_by IS NULL )
750 AND ( p_created_by IS NULL )))
751 AND ( ( Recinfo.last_update_login = p_last_update_login)
752 OR ( ( Recinfo.last_update_login IS NULL )
753 AND ( p_last_update_login IS NULL )))
754 AND ( ( Recinfo.object_version_number = px_object_version_number)
755 OR ( ( Recinfo.object_version_number IS NULL )
756 AND ( px_object_version_number IS NULL )))
757 ) THEN
758 RETURN;
759 ELSE
760 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
761 APP_EXCEPTION.RAISE_EXCEPTION;
762 END IF;
763
764 END Lock_Row;
765
766
767
768 -- ========================================================
769 --
770 -- NAME
771 -- Add_Language
772 --
773 -- PURPOSE
774 --
775 -- NOTES
776 --
777 -- HISTORY
778 --
779 -- ========================================================
780
781
782 PROCEDURE Add_Language
783 IS
784 BEGIN
785 -- changing by pukken as per performance team guidelines to fix performance issue
786 -- as described in bug 3723612 (*** RTIKKU 03/24/05 12:46pm ***)
787 INSERT /*+ append parallel(tt) */ INTO PV_PARTNER_PROGRAM_TL tt
788 (
789 PROGRAM_ID
790 ,LAST_UPDATE_DATE
791 ,LAST_UPDATED_BY
792 ,CREATION_DATE
793 ,CREATED_BY
794 ,LAST_UPDATE_LOGIN
795 ,LANGUAGE
796 ,SOURCE_LANG
797 ,PROGRAM_NAME
798 ,PROGRAM_DESCRIPTION
799 )
800 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.*
801 FROM
802 (
803 SELECT /*+ no_merge ordered parallel(b) */
804 B.PROGRAM_ID
805 ,B.LAST_UPDATE_DATE
806 ,B.LAST_UPDATED_BY
807 ,B.CREATION_DATE
808 ,B.CREATED_BY
809 ,B.LAST_UPDATE_LOGIN
810 ,L.LANGUAGE_CODE
811 ,B.SOURCE_LANG
812 ,B.PROGRAM_NAME
813 ,B.PROGRAM_DESCRIPTION
814 FROM PV_PARTNER_PROGRAM_TL B , FND_LANGUAGES L
815 WHERE L.INSTALLED_FLAG IN ( 'I','B' ) AND B.LANGUAGE = USERENV ( 'LANG' )
816 ) v
817 , PV_PARTNER_PROGRAM_TL t
818 WHERE t.PROGRAM_ID(+) = v.PROGRAM_ID
819 AND t.language(+) = v.language_code
820 AND t.PROGRAM_ID IS NULL ;
821
822 END Add_Language;
823
824
825
826
827 -- ========================================================
828 --
829 -- NAME
830 -- Translate_Row
831 --
832 -- PURPOSE
833 --
834 -- NOTES
835 --
836 -- HISTORY
837 --
838 -- ========================================================
839
840 PROCEDURE Translate_Row(
841 px_program_id IN VARCHAR2
842 ,p_program_name IN VARCHAR2
843 ,p_program_description IN VARCHAR2
844 ,p_owner IN VARCHAR2
845 ,p_qsnr_title IN VARCHAR2
846 ,p_qsnr_header IN VARCHAR2
847 ,p_qsnr_footer IN VARCHAR2
848 )
849
850 IS
851
852 BEGIN
853 UPDATE PV_PARTNER_PROGRAM_TL set
854 PROGRAM_NAME = NVL(p_program_name, program_name)
855 ,PROGRAM_DESCRIPTION = NVL(p_program_description, program_description)
856 ,SOURCE_LANG = USERENV('LANG')
857 ,LAST_UPDATE_DATE = SYSDATE
858 ,LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0)
859 ,LAST_UPDATE_LOGIN = 0
860 ,qsnr_title = NVL(p_qsnr_title, qsnr_title )
861 ,qsnr_header = NVL(p_qsnr_header,qsnr_header )
862 ,qsnr_footer = NVL(p_qsnr_footer,qsnr_footer )
863
864 WHERE PROGRAM_ID = px_program_id
865 AND USERENV('LANG') IN (language, source_lang);
866
867 END Translate_Row;
868
869
870 END PV_PARTNER_PROGRAM_PKG;