[Home] [Help]
PACKAGE BODY: APPS.OE_OIP_UTIL
Source
1 Package body OE_OIP_UTIL As
2 /* $Header: OEXUOIPB.pls 120.0 2005/05/31 23:51:51 appldev noship $ */
3
4 PROCEDURE print_msg( msg in varchar2)
5 is
6 Begin
7
8 if(OE_OIP_UTIL.debug = 1) Then
9 /* Comment because of GSCC standard */
10 /* dbms_output.put_line(msg); */
11 null;
12 end if;
13
14 end;
15
16 PROCEDURE set_Debug (debug_flag in number)
17 is
18 Begin
19 OE_OIP_UTIL.debug := debug_flag;
20 End;
21
22 PROCEDURE DELETE_OIP_AK_PAGE
23 ( p_region_page IN VARCHAR2
24 , p_region_style IN VARCHAR2
25 ) is
26
27 cursor ak_region_pages (x_region_page in varchar2,
28 x_region_style in varchar2) is
29 select region_code
30 from AK_REGIONS
31 where REGION_APPLICATION_ID = 660
32 and REGION_CODE = x_region_page
33 and REGION_STYLE =x_region_style;
34
35 cursor ak_region_lov_rel (x_region_name in varchar2,
36 x_attribute_code in varchar2) is
37 select REGION_APPLICATION_ID,
38 REGION_CODE,
39 ATTRIBUTE_APPLICATION_ID,
40 ATTRIBUTE_CODE,
41 LOV_REGION_APPL_ID,
42 LOV_REGION_CODE,
43 LOV_ATTRIBUTE_APPL_ID,
44 LOV_ATTRIBUTE_CODE,
45 BASE_ATTRIBUTE_APPL_ID,
46 BASE_ATTRIBUTE_CODE,
47 DIRECTION_FLAG
48 from AK_REGION_LOV_RELATIONS
49 where REGION_APPLICATION_ID = 660
50 and REGION_CODE = x_region_name
51 and ATTRIBUTE_CODE = x_attribute_code;
52
53 cursor ak_region_items (x_region_name in varchar2) is
54 select level b,
55 a.region_application_id b2,
56 a.region_code b3,
57 attribute_application_id c1,
58 a.attribute_code c
59 from ak_region_items a
60 where a.region_application_id =660
61 start with a.region_code = x_region_name
62 connect by a.region_code = PRIOR a.nested_region_code
63 order by level desc;
64
65 p_region_appl_id number;
66 p_attr_appl_id number;
67 p_attribute_code varchar2(100);
68 p_region_code varchar2(100);
69 p_region_page_1 varchar2(100);
70 p_level number;
71 mycount number;
72 mycount1 number;
73 mycount2 number;
74 p_style number;
75 p_style_name varchar2(20);
76 v_region_page varchar2(100);
77 v_region_style varchar2(100);
78
79 p_attribute_code_LOV varchar2(100);
80 p_region_code_LOV varchar2(100);
81 p_REGION_APPLICATION_ID NUMBER;
82 p_ATTRIBUTE_APPLICATION_ID NUMBER;
83 p_LOV_REGION_APPL_ID NUMBER;
84 p_LOV_REGION_CODE varchar2(100);
85 p_LOV_ATTRIBUTE_APPL_ID NUMBER;
86 p_LOV_ATTRIBUTE_CODE varchar2(100);
87 p_BASE_ATTRIBUTE_APPL_ID NUMBER;
88 p_BASE_ATTRIBUTE_CODE varchar2(100);
89 p_DIRECTION_FLAG VARCHAR(20);
90 p_ind number;
91
92 error_exception exception ;
93
94 BEGIN
95
96 v_region_style := p_region_style;
97 v_region_page := p_region_page;
98 print_msg('region style ' || p_region_style);
99 print_msg('region page ' || p_region_page);
100
101 /* check if the input region name with input region style is matched */
102
103 OPEN ak_region_pages (v_region_page, v_region_style);
104 FETCH ak_region_pages into p_region_page_1;
105 if ak_region_pages%NOTFOUND then
106 print_msg('WARNING: NO SUCH REGION EXISTS WITH THIS STYLE EXIST');
107 /*
108 raise error_exception;
109 */
110 end if;
111 print_msg('Start getting AK region_items for '|| p_region_page_1);
112
113 /* */
114 /* loop thru each region item in a given region */
115 /* */
116
117 OPEN ak_region_items (p_region_page_1);
118 LOOP
119 FETCH ak_region_items INTO p_level, p_region_appl_id, p_region_code,
120 p_attr_appl_id, p_attribute_code;
121
122 EXIT WHEN ak_region_items%NOTFOUND;
123
124 select count(*)
125 into mycount
126 from AK_REGION_LOV_RELATIONS
127 where REGION_APPLICATION_ID = 660
128 and REGION_CODE = p_region_code
129 and ATTRIBUTE_CODE = p_attribute_code;
130
131 if (mycount = 0) then
132 print_msg('NO AK LOV relationship for region' || p_region_code || '.' || p_attribute_code);
133 else
134 print_msg('Start getting AK LOV relationship for region' || p_region_code || '.' || p_attribute_code);
135
136 /* */
137 /* for each region item, loop through its lov rel. Delete its lov rel */
138 /* before we delete region item */
139 /* */
140
141 OPEN ak_region_lov_rel (p_region_code, p_attribute_code);
142 LOOP
143 FETCH ak_region_lov_rel INTO
144 P_REGION_APPLICATION_ID,
145 P_REGION_CODE_LOV,
146 P_ATTRIBUTE_APPLICATION_ID,
147 P_ATTRIBUTE_CODE_LOV ,
148 P_LOV_REGION_APPL_ID,
149 P_LOV_REGION_CODE,
150 P_LOV_ATTRIBUTE_APPL_ID,
151 P_LOV_ATTRIBUTE_CODE,
152 P_BASE_ATTRIBUTE_APPL_ID,
153 P_BASE_ATTRIBUTE_CODE,
154 P_DIRECTION_FLAG;
155
156 EXIT WHEN ak_region_lov_rel%NOTFOUND;
157
158 print_msg('************************');
159 print_msg('REGION_CODE = ' || P_REGION_CODE_LOV);
160 print_msg('ATTRIBUTE_CODE = ' || P_ATTRIBUTE_CODE_LOV);
161 print_msg('LOV_REGION_CODE = ' || P_LOV_REGION_CODE);
162 print_msg('BASE_ATTRIBUTE_CODE = ' || P_BASE_ATTRIBUTE_CODE );
163 print_msg('DIRECTION_FLAG = ' || P_DIRECTION_FLAG || ' will be deteled');
164
165
166 AK_LOV_RELATIONS_PKG.DELETE_ROW (
167 X_REGION_APPLICATION_ID => P_REGION_APPLICATION_ID,
168 X_REGION_CODE => P_REGION_CODE,
169 X_ATTRIBUTE_APPLICATION_ID => P_ATTRIBUTE_APPLICATION_ID,
170 X_ATTRIBUTE_CODE => P_ATTRIBUTE_CODE ,
171 X_LOV_REGION_APPL_ID => P_LOV_REGION_APPL_ID,
172 X_LOV_REGION_CODE => P_LOV_REGION_CODE,
173 X_LOV_ATTRIBUTE_APPL_ID => P_LOV_ATTRIBUTE_APPL_ID,
174 X_LOV_ATTRIBUTE_CODE => P_LOV_ATTRIBUTE_CODE,
175 X_BASE_ATTRIBUTE_APPL_ID => P_BASE_ATTRIBUTE_APPL_ID,
176 X_BASE_ATTRIBUTE_CODE =>P_BASE_ATTRIBUTE_CODE,
177 X_DIRECTION_FLAG => P_DIRECTION_FLAG);
178 print_msg('REGION_CODE = ' || P_REGION_CODE_LOV);
179 print_msg('ATTRIBUTE_CODE = ' || P_ATTRIBUTE_CODE_LOV);
180 print_msg('LOV_REGION_CODE = ' || P_LOV_REGION_CODE);
181 print_msg('BASE_ATTRIBUTE_CODE = ' || P_BASE_ATTRIBUTE_CODE );
182 print_msg('DIRECTION_FLAG = ' || P_DIRECTION_FLAG || ' will be deteled');
183
184 END LOOP;
185 CLOSE ak_region_lov_rel;
186 end if;
187 print_msg('=================================================================');
188 print_msg('REGION_ITEM = ' || p_region_code || '.' || p_attribute_code || ' will be deleted');
189
190 select count(*)
191 into mycount1
192 from AK_REGION_ITEMS
193 where REGION_APPLICATION_ID = 660
194 and REGION_CODE = p_region_code
195 and ATTRIBUTE_CODE = p_attribute_code;
196
197 if (mycount1 = 0) then
198 print_msg('NO such AK item ' || p_region_code || '.' || p_attribute_code);
199
200 else
201
202 /* real code to delete ak region items */
203
204 AK_REGION_ITEMS_PKG.DELETE_ROW(
205 X_REGION_APPLICATION_ID => p_region_appl_id,
206 X_REGION_CODE => p_region_code,
207 X_ATTRIBUTE_APPLICATION_ID => p_attr_appl_id,
208 X_ATTRIBUTE_CODE => p_attribute_code);
209 print_msg('REGION_ITEM = ' || p_region_code || '.' || p_attribute_code || ' has been deleted');
210
211 end if;
212 END LOOP;
213 print_msg('#################################################################');
214 CLOSE ak_region_items;
215
216 print_msg('=================================================================');
217 print_msg('REGION:' || p_region_page_1 || '(' ||
218 p_style_name || ')' || ' will be deleted');
219
220 select count(*)
221 into mycount2
222 from AK_REGIONS
223 where REGION_APPLICATION_ID = 660
224 and REGION_CODE = p_region_page_1;
225
226 if (mycount2 = 0) then
227 print_msg('NO such AK region ' || p_region_code );
228 else
229 /* real code to delete regions */
230 AK_REGIONS_PKG.DELETE_ROW(
231 X_REGION_APPLICATION_ID =>660,
232 X_REGION_CODE => p_region_page_1);
233 print_msg('REGION:' || p_region_page_1 || '(' ||
234 p_style_name || ')' || ' has be deleted');
235 print_msg('#################################################################');
236 end if;
237
238 print_msg('ak entries are deleted');
239 exception
240 when error_exception then
241 raise_application_error(-20001, sqlerrm);
242
243 when others then
244 raise_application_error(-20001, sqlerrm);
245
246 END DELETE_OIP_AK_PAGE;
247
248 END OE_OIP_UTIL;
249