[Home] [Help]
PACKAGE BODY: APPS.AD_PA_INSERT_PACKAGE
Source
1 package body ad_pa_insert_package as
2 /* $Header: adpaipb.pls 120.2 2006/03/27 08:07:14 msailoz noship $ */
3
4 --
5 -- Procedure to insert/update in table ad_pm_patches
6 --
7
8 procedure insert_ad_pm_patches
9 (
10 bug_number_value number,
11 aru_update_date_value varchar2,
12 product_abbreviation_value varchar2,
13 product_family_abbv_value varchar2,
14 patch_name_value varchar2,
15 conc_request_id number,
16 bug_description_value varchar2,
17 is_family_pack_flag varchar2,
18 is_mini_pack_flag varchar2,
19 is_high_priority_flag varchar2,
20 is_maint_pack_flag varchar2,
21 X_in_reference_family_pack varchar2,
22 X_reference_family_pack varchar2,
23 X_in_reference_mini_pack varchar2,
24 X_reference_mini_pack varchar2,
25 X_in_reference_maint_pack varchar2,
26 X_reference_maint_pack varchar2,
27 X_infobundle_upload_date varchar2,
28 X_creation_date varchar2,
29 X_last_updated_by number,
30 X_created_by number,
31 X_last_update_date varchar2
32 )
33 is
34 begin
35 insert into ad_pm_patches
36 (
37 patch_metadata_key ,
38 bug_number ,
39 product_abbreviation,
40 product_family_abbreviation,
41 patch_name,
42 upload_run_id ,
43 bug_description ,
44 is_family_pack ,
45 is_mini_pack ,
46 is_high_priority ,
47 is_maint_pack ,
48 in_reference_family_pack ,
49 reference_family_pack ,
50 in_reference_mini_pack ,
51 reference_mini_pack ,
52 in_reference_maint_pack ,
53 reference_maint_pack,
54 creation_date ,
55 last_updated_by ,
56 created_by ,
57 last_update_date
58 )
59 values
60 (
61 'DEFAULT' ,
62 bug_number_value ,
63 product_abbreviation_value,
64 nvl(product_family_abbv_value,'Not Found'),
65 patch_name_value,
66 conc_request_id ,
67 bug_description_value,
68 is_family_pack_flag,
69 is_mini_pack_flag,
70 is_high_priority_flag,
71 is_maint_pack_flag,
72 X_in_reference_family_pack ,
73 X_reference_family_pack,
74 X_in_reference_mini_pack ,
75 X_reference_mini_pack,
76 X_in_reference_maint_pack,
77 X_reference_maint_pack ,
78 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss'),
79 X_last_updated_by,
80 X_created_by ,
81 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss')
82 );
83
84 exception
85 when dup_val_on_index then
86 update ad_pm_patches
87 set
88 product_abbreviation = product_abbreviation_value ,
89 product_family_abbreviation = nvl(product_family_abbv_value,'Not Found'),
90 patch_name = patch_name_value ,
91 upload_run_id = conc_request_id ,
92 bug_description = bug_description_value ,
93 is_family_pack = is_family_pack_flag ,
94 is_mini_pack = is_mini_pack_flag,
95 is_high_priority = is_high_priority_flag ,
96 is_maint_pack = is_maint_pack_flag ,
97 in_reference_family_pack = X_in_reference_family_pack ,
98 reference_family_pack = X_reference_family_pack ,
99 in_reference_mini_pack = X_in_reference_mini_pack ,
100 reference_mini_pack = X_reference_mini_pack,
101 in_reference_maint_pack = X_in_reference_maint_pack,
102 reference_maint_pack = X_reference_maint_pack,
103 last_updated_by = X_last_updated_by,
104 last_update_date =
105 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss')
106
107 where
108 ( bug_number = bug_number_value
109 and patch_metadata_key = 'DEFAULT' );
110
111 end insert_ad_pm_patches;
112
113 -- msailoz bug#4956568 Set release name to R12
114 -- Procedure to insert/update in table ad_pm_patches for R12
115 --
116
117 procedure insert_ad_pm_patches
118 (
119 bug_number_value number,
120 aru_update_date_value varchar2,
121 product_abbreviation_value varchar2,
122 product_family_abbv_value varchar2,
123 patch_name_value varchar2,
124 conc_request_id number,
125 bug_description_value varchar2,
126 is_high_priority_flag varchar2,
127 X_infobundle_upload_date varchar2,
128 X_creation_date varchar2,
129 X_last_updated_by number,
130 X_created_by number,
131 X_last_update_date varchar2,
132 is_code_level_flag varchar2,
133 p_patch_type varchar2,
134 p_entity_abbr varchar2,
135 p_entity_baseline varchar2
136 )
137 is
138 err_msg varchar2(200);
139 l_entity_baseline varchar2(50);
140
141 begin
142
143 IF (p_entity_baseline = '' OR p_entity_baseline IS NULL) THEN
144 l_entity_baseline := 'R12';
145 ELSE
146 l_entity_baseline := p_entity_baseline;
147 END IF;
148
149 INSERT INTO ad_pm_patches
150 (
151 patch_metadata_key ,
152 bug_number ,
153 product_abbreviation,
154 product_family_abbreviation,
155 patch_name,
156 upload_run_id ,
157 bug_description ,
158 is_high_priority ,
159 creation_date ,
160 last_updated_by ,
161 created_by ,
162 last_update_date,
163 is_code_level,
164 patch_type ,
165 entity_abbr,
166 baseline,
167 patch_id
168 )
169 SELECT
170 'DEFAULT' ,
171 bug_number_value ,
172 product_abbreviation_value,
173 nvl(product_family_abbv_value,'Not Found'),
174 patch_name_value,
175 conc_request_id ,
176 bug_description_value,
177 is_high_priority_flag,
178 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss'),
179 X_last_updated_by,
180 X_created_by ,
181 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss'),
182 is_code_level_flag,
183 p_patch_type ,
184 p_entity_abbr ,
185 l_entity_baseline,
186 ad_patch_id_s.NEXTVAL
187 FROM DUAL;
188
189 exception
190 when dup_val_on_index then
191 update ad_pm_patches
192 set
193 product_abbreviation = product_abbreviation_value ,
194 product_family_abbreviation = nvl(product_family_abbv_value,'Not Found'),
195 patch_name = patch_name_value ,
196 upload_run_id = conc_request_id ,
197 bug_description = nvl(bug_description,bug_description_value) ,
198 is_high_priority = decode(is_high_priority,'N',is_high_priority_flag,null, is_high_priority_flag, is_high_priority),
199 is_code_level = decode(is_code_level,'N',is_code_level_flag,null, is_code_level_flag, is_code_level),
200 patch_type = decode(patch_type, null, p_patch_type, patch_type),
201 last_updated_by = X_last_updated_by,
202 last_update_date =
203 to_date(X_last_update_date,'yyyy-mm-dd hh24:mi:ss')
204 where
205 ( bug_number = bug_number_value
206 and baseline = l_entity_baseline
207 and patch_metadata_key = 'DEFAULT' );
208
209 end insert_ad_pm_patches;
210
211
212 --
213 -- Procedure to insert/update in ad_pm_product_info
214 --
215
216 procedure insert_ad_pm_product_info
217 (
218 X_product_abbreviation varchar2,
219 X_pseudo_product_flag varchar2,
220 X_product_family_flag varchar2,
221 X_application_short_name varchar2,
222 X_product_name varchar2,
223 X_product_family_abbreviation varchar2,
224 X_product_family_name varchar2,
225 X_aru_update_date varchar2,
226 X_currDate varchar2 ,
227 X_last_updated_by number,
228 X_created_by number
229 )
230 is
231 begin
232 insert into ad_pm_product_info
233 (
234 product_abbreviation ,
235 pseudo_product_flag ,
236 product_family_flag ,
237 application_short_name ,
238 product_name,
239 aru_update_date ,
240 creation_date ,
241 last_updated_by,
242 created_by,
243 last_update_date
244 )
245 values
246 (
247 X_product_abbreviation ,
248 X_pseudo_product_flag ,
249 X_product_family_flag ,
250 X_application_short_name ,
251 X_product_name,
252 to_date(X_aru_update_date,'yyyy-mm-dd hh24:mi:ss')
253 ,to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss'),
254 X_last_updated_by,
255 X_created_by,
256 to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss')
257 );
258 exception
259 when dup_val_on_index then
260 update ad_pm_product_info
261 set pseudo_product_flag = X_pseudo_product_flag ,
262 product_family_flag = X_product_family_flag ,
263 application_short_name = X_application_short_name ,
264 product_name = X_product_name ,
265 aru_update_date
266 = to_date(X_aru_update_date,'yyyy-mm-dd hh24:mi:ss'),
267 last_update_date
268 = to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss'),
269 last_updated_by = X_last_updated_by
270 where product_abbreviation = X_product_abbreviation;
271
275 --
272 end insert_ad_pm_product_info ;
273
274
276 -- Procedure to insert/update in ad_pm_prod_family_map
277 -- Added for Bug# 2814295 to update the new AD Patch Advisor
278 -- table ad_pm_prod_family_map
279
280 procedure insert_ad_pm_prod_family_map
281 (
282 X_product_abbreviation varchar2,
283 X_product_family_abbreviation varchar2,
284 X_aru_update_date varchar2,
285 X_currDate varchar2,
286 X_last_updated_by number,
287 X_created_by number
288 )
289 is
290 begin
291 insert into ad_pm_prod_family_map
292 (
293 product_abbreviation ,
294 product_family_abbreviation,
295 creation_date ,
296 last_updated_by,
297 created_by,
298 last_update_date
299 )
300 values
301 (
302 X_product_abbreviation ,
303 X_product_family_abbreviation,
304 to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss'),
305 X_last_updated_by,
306 X_created_by,
307 to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss')
308 );
309
310 -- do nothing if the info already exists in table
311 -- Combination of PRODUCT_ABBREVIATION AND PRODUCT_FAMILY_ABBREVIATION
312 -- is the primary key for the table AD_PM_PROD_FAMILY_MAP
313 exception
314 when dup_val_on_index then
315 update ad_pm_prod_family_map
316 set
317 last_updated_by = X_last_updated_by ,
318 last_update_date = to_date(X_currdate,'yyyy-mm-dd hh24:mi:ss')
319 where
320 product_abbreviation = X_product_abbreviation
321 and product_family_abbreviation = X_product_family_abbreviation;
322
323 end insert_ad_pm_prod_family_map;
324
325 end ad_pa_insert_package;