1 package body AK_LOV_RELATIONS_PKG as
2 /* $Header: AKDRLOVB.pls 120.2 2005/09/29 14:00:03 tshort ship $ */
3
4 -- This package is created for inserting, locking and deleting
5 -- rows in the LOV-Target Relationship form.
6 -- Used for the table AK_REGION_LOV_RELATIONS.
7
8 procedure INSERT_ROW (
9 X_ROWID in out NOCOPY VARCHAR2,
10 X_REGION_APPLICATION_ID in NUMBER,
11 X_REGION_CODE in VARCHAR2,
12 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
13 X_ATTRIBUTE_CODE in VARCHAR2,
14 X_LOV_REGION_APPL_ID in NUMBER,
15 X_LOV_REGION_CODE in VARCHAR2,
16 X_LOV_ATTRIBUTE_APPL_ID in NUMBER,
17 X_LOV_ATTRIBUTE_CODE in VARCHAR2,
18 X_BASE_REGION_APPL_ID in NUMBER,
19 X_BASE_REGION_CODE in VARCHAR2,
20 X_BASE_ATTRIBUTE_APPL_ID in NUMBER,
21 X_BASE_ATTRIBUTE_CODE in VARCHAR2,
22 X_DIRECTION_FLAG in VARCHAR2,
23 X_REQUIRED_FLAG in VARCHAR2,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30
31 l_base_region_appl_id number;
32 l_base_region_code varchar2(30);
33
34 cursor C is select ROWID from AK_REGION_LOV_RELATIONS
35 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
36 and REGION_CODE = X_REGION_CODE
37 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
38 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
39 and LOV_REGION_APPL_ID = X_LOV_REGION_APPL_ID
40 and LOV_REGION_CODE = X_LOV_REGION_CODE
41 and BASE_REGION_APPL_ID = l_BASE_REGION_APPL_ID
42 and BASE_REGION_CODE = l_BASE_REGION_CODE
43 and BASE_ATTRIBUTE_APPL_ID = X_BASE_ATTRIBUTE_APPL_ID
44 and BASE_ATTRIBUTE_CODE = X_BASE_ATTRIBUTE_CODE
45 and DIRECTION_FLAG = X_DIRECTION_FLAG;
46
47 begin
48 if X_BASE_REGION_APPL_ID is null then
49 l_BASE_REGION_APPL_ID := X_REGION_APPLICATION_ID;
50 else
51 l_BASE_REGION_APPL_ID := X_BASE_REGION_APPL_ID;
52 end if;
53 if X_BASE_REGION_CODE is null then
54 l_BASE_REGION_CODE := X_REGION_CODE;
55 else
56 l_BASE_REGION_CODE := X_BASE_REGION_CODE;
57 end if;
58 insert into AK_REGION_LOV_RELATIONS (
59 REGION_APPLICATION_ID,
60 REGION_CODE,
61 ATTRIBUTE_APPLICATION_ID,
62 ATTRIBUTE_CODE,
63 LOV_REGION_APPL_ID,
64 LOV_REGION_CODE,
65 LOV_ATTRIBUTE_APPL_ID,
66 LOV_ATTRIBUTE_CODE,
67 BASE_REGION_APPL_ID,
68 BASE_REGION_CODE,
69 BASE_ATTRIBUTE_APPL_ID,
70 BASE_ATTRIBUTE_CODE,
71 DIRECTION_FLAG,
72 REQUIRED_FLAG,
73 LAST_UPDATE_DATE,
74 LAST_UPDATED_BY,
75 CREATION_DATE,
76 CREATED_BY,
77 LAST_UPDATE_LOGIN
78 )
79 values (
80 X_REGION_APPLICATION_ID,
81 X_REGION_CODE,
82 X_ATTRIBUTE_APPLICATION_ID,
83 X_ATTRIBUTE_CODE,
84 X_LOV_REGION_APPL_ID,
85 X_LOV_REGION_CODE ,
86 X_LOV_ATTRIBUTE_APPL_ID,
87 X_LOV_ATTRIBUTE_CODE,
88 l_BASE_REGION_APPL_ID,
89 l_BASE_REGION_CODE,
90 X_BASE_ATTRIBUTE_APPL_ID,
91 X_BASE_ATTRIBUTE_CODE,
92 X_DIRECTION_FLAG,
93 X_REQUIRED_FLAG,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_LOGIN
99 );
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107 end INSERT_ROW;
108
109 procedure DELETE_ROW (
110 X_REGION_APPLICATION_ID in NUMBER,
111 X_REGION_CODE in VARCHAR2,
112 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
113 X_ATTRIBUTE_CODE in VARCHAR2,
114 X_LOV_REGION_APPL_ID in NUMBER,
115 X_LOV_REGION_CODE in VARCHAR2,
116 X_LOV_ATTRIBUTE_APPL_ID in NUMBER,
117 X_LOV_ATTRIBUTE_CODE in VARCHAR2,
118 X_BASE_REGION_APPL_ID in NUMBER,
119 X_BASE_REGION_CODE in VARCHAR2,
120 X_BASE_ATTRIBUTE_APPL_ID in NUMBER,
121 X_BASE_ATTRIBUTE_CODE in VARCHAR2,
122 X_DIRECTION_FLAG in VARCHAR2
123 ) is
124 begin
125 delete from AK_REGION_LOV_RELATIONS
126 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
127 and REGION_CODE = X_REGION_CODE
128 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
129 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
130 and LOV_REGION_APPL_ID = X_LOV_REGION_APPL_ID
131 and LOV_REGION_CODE = X_LOV_REGION_CODE
132 and LOV_ATTRIBUTE_APPL_ID = X_LOV_ATTRIBUTE_APPL_ID
133 and LOV_ATTRIBUTE_CODE = X_LOV_ATTRIBUTE_CODE
134 and BASE_REGION_APPL_ID = nvl(X_BASE_REGION_APPL_ID,BASE_REGION_APPL_ID)
135 and BASE_REGION_CODE = nvl(X_BASE_REGION_CODE,BASE_REGION_CODE)
136 and BASE_ATTRIBUTE_APPL_ID = X_BASE_ATTRIBUTE_APPL_ID
137 and BASE_ATTRIBUTE_CODE = X_BASE_ATTRIBUTE_CODE
138 and DIRECTION_FLAG = X_DIRECTION_FLAG ;
139 if (sql%notfound) then
140 raise no_data_found;
141 end if;
142 end DELETE_ROW;
143
144 procedure LOCK_ROW (
145 X_REGION_APPLICATION_ID in NUMBER,
146 X_REGION_CODE in VARCHAR2,
147 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
148 X_ATTRIBUTE_CODE in VARCHAR2,
149 X_LOV_REGION_APPL_ID in NUMBER,
150 X_LOV_REGION_CODE in VARCHAR2,
151 X_LOV_ATTRIBUTE_APPL_ID in NUMBER,
152 X_LOV_ATTRIBUTE_CODE in VARCHAR2,
153 X_BASE_REGION_APPL_ID in NUMBER,
154 X_BASE_REGION_CODE in VARCHAR2,
155 X_BASE_ATTRIBUTE_APPL_ID in NUMBER,
156 X_BASE_ATTRIBUTE_CODE in VARCHAR2,
157 X_DIRECTION_FLAG in VARCHAR2,
158 X_REQUIRED_FLAG in VARCHAR2
159 ) is
160 cursor c is select
161 REQUIRED_FLAG
162 from AK_REGION_LOV_RELATIONS
163 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
164 and REGION_CODE = X_REGION_CODE
165 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
166 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
167 and LOV_REGION_APPL_ID = X_LOV_REGION_APPL_ID
168 and LOV_REGION_CODE = X_LOV_REGION_CODE
169 and LOV_ATTRIBUTE_APPL_ID = X_LOV_ATTRIBUTE_APPL_ID
170 and LOV_ATTRIBUTE_CODE = X_LOV_ATTRIBUTE_CODE
171 and BASE_REGION_APPL_ID = X_BASE_REGION_APPL_ID
172 and BASE_REGION_CODE = X_BASE_REGION_CODE
173 and BASE_ATTRIBUTE_APPL_ID = X_BASE_ATTRIBUTE_APPL_ID
174 and BASE_ATTRIBUTE_CODE = X_BASE_ATTRIBUTE_CODE
175 and DIRECTION_FLAG = X_DIRECTION_FLAG
176 for update of region_application_id nowait;
177 recinfo c%rowtype;
178 begin
179 open c;
180 fetch c into recinfo;
181 if (c%notfound) then
182 close c;
183 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184 app_exception.raise_exception;
185 end if;
186 close c;
187
188 if ( ((recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
189 OR ((recinfo.REQUIRED_FLAG is null)
190 AND (X_REQUIRED_FLAG is null)))
191 ) then
192 null;
193 else
194 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
195 app_exception.raise_exception;
196 end if;
197
198 return;
199 end LOCK_ROW;
200
201 procedure UPDATE_ROW (
202 X_REGION_APPLICATION_ID in NUMBER,
203 X_REGION_CODE in VARCHAR2,
204 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
205 X_ATTRIBUTE_CODE in VARCHAR2,
206 X_LOV_REGION_APPL_ID in NUMBER,
207 X_LOV_REGION_CODE in VARCHAR2,
208 X_LOV_ATTRIBUTE_APPL_ID in NUMBER,
209 X_LOV_ATTRIBUTE_CODE in VARCHAR2,
210 X_BASE_REGION_APPL_ID in NUMBER,
211 X_BASE_REGION_CODE in VARCHAR2,
212 X_BASE_ATTRIBUTE_APPL_ID in NUMBER,
213 X_BASE_ATTRIBUTE_CODE in VARCHAR2,
214 X_DIRECTION_FLAG in VARCHAR2,
215 X_REQUIRED_FLAG in VARCHAR2,
216 X_LAST_UPDATE_DATE in DATE,
217 X_LAST_UPDATED_BY in NUMBER,
218 X_LAST_UPDATE_LOGIN in NUMBER
219 ) is
220 l_base_region_appl_id number;
221 l_base_region_code varchar2(30);
222 begin
223 if X_BASE_REGION_APPL_ID is null then
224 l_BASE_REGION_APPL_ID := X_REGION_APPLICATION_ID;
225 else
226 l_BASE_REGION_APPL_ID := X_BASE_REGION_APPL_ID;
227 end if;
228 if X_BASE_REGION_CODE is null then
229 l_BASE_REGION_CODE := X_REGION_CODE;
230 else
231 l_BASE_REGION_CODE := X_BASE_REGION_CODE;
232 end if;
233 update ak_region_lov_relations set
234 required_flag = X_REQUIRED_FLAG
235 where region_application_id = X_REGION_APPLICATION_ID
236 and region_code = X_REGION_CODE
237 and attribute_application_id = X_ATTRIBUTE_APPLICATION_ID
238 and attribute_code = X_ATTRIBUTE_CODE
239 and lov_region_appl_id = X_LOV_REGION_APPL_ID
240 and lov_region_code = X_LOV_REGION_CODE
241 and lov_attribute_appl_id = X_LOV_ATTRIBUTE_APPL_ID
242 and lov_attribute_code = X_LOV_ATTRIBUTE_CODE
243 and base_region_appl_id = l_BASE_REGION_APPL_ID
244 and base_region_code = l_BASE_REGION_CODE
245 and base_attribute_appl_id = X_BASE_ATTRIBUTE_APPL_ID
246 and base_attribute_code = X_BASE_ATTRIBUTE_CODE
247 and direction_flag = X_DIRECTION_FLAG;
248 if (sql%notfound) then
249 raise no_data_found;
250 end if;
251 end UPDATE_ROW;
252
253 end AK_LOV_RELATIONS_PKG;
[Home] [Help]