[Home] [Help]
PACKAGE BODY: APPS.AMS_SETUP_TYPES_PKG
Source
1 package body AMS_SETUP_TYPES_PKG as
2 /* $Header: amslsetb.pls 115.11 2003/10/13 17:36:13 asaha ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_SETUP_TYPE_ID in NUMBER,
6 X_DISPLAY_SEQUENCE_NO in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_OBJECT_TYPE in VARCHAR2,
9 X_ACTIVITY_TYPE_CODE in VARCHAR2,
10 X_SETUP_ATTRIBUTE in VARCHAR2,
11 X_MANDATORY_FLAG in VARCHAR2,
12 X_USER_INTERFACE_FUNCTION in VARCHAR2,
13 X_PARENT_FUNCTION_NAME in VARCHAR2,
14 X_FUNCTION_NAME in VARCHAR2,
15 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
16 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
17 X_SHOW_IN_REPORT in VARCHAR2,
18 X_SHOW_IN_CUE_CARD in VARCHAR2,
19 X_COPY_ALLOWED_FLAG in VARCHAR2,
20 X_ESSENTIAL_SEQ_NUM in NUMBER,
21 X_USAGE in VARCHAR2,
22 X_APPLICATION_ID in NUMBER,
23 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30 cursor C is select ROWID from AMS_SETUP_TYPES
31 where SETUP_TYPE_ID = X_SETUP_TYPE_ID;
32 begin
33 insert into AMS_SETUP_TYPES (
34 DISPLAY_SEQUENCE_NO,
35 SETUP_TYPE_ID,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_LOGIN,
41 OBJECT_VERSION_NUMBER,
42 OBJECT_TYPE,
43 ACTIVITY_TYPE_CODE,
44 SETUP_ATTRIBUTE,
45 MANDATORY_FLAG,
46 USER_INTERFACE_FUNCTION,
47 PARENT_FUNCTION_NAME,
48 FUNCTION_NAME,
49 PARENT_SETUP_ATTRIBUTE,
50 PARENT_DISPLAY_SEQUENCE,
51 SHOW_IN_REPORT,
52 SHOW_IN_CUE_CARD,
53 COPY_ALLOWED_FLAG,
54 USAGE,
55 ESSENTIAL_SEQ_NUM,
56 APPLICATION_ID ,
57 RELATED_AK_ATTRIBUTE
58 ) values(
59 X_DISPLAY_SEQUENCE_NO,
60 X_SETUP_TYPE_ID,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_LOGIN,
66 X_OBJECT_VERSION_NUMBER,
67 X_OBJECT_TYPE,
68 X_ACTIVITY_TYPE_CODE,
69 X_SETUP_ATTRIBUTE,
70 X_MANDATORY_FLAG,
71 X_USER_INTERFACE_FUNCTION,
72 X_PARENT_FUNCTION_NAME ,
73 X_FUNCTION_NAME,
74 X_PARENT_SETUP_ATTRIBUTE,
75 X_PARENT_DISPLAY_SEQUENCE,
76 X_SHOW_IN_REPORT,
77 X_SHOW_IN_CUE_CARD,
78 X_COPY_ALLOWED_FLAG,
79 X_USAGE,
80 X_ESSENTIAL_SEQ_NUM,
81 X_APPLICATION_ID,
82 X_RELATED_AK_ATTRIBUTE
83 );
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end INSERT_ROW;
94
95
96 procedure UPDATE_ROW (
97 X_SETUP_TYPE_ID in NUMBER,
98 X_DISPLAY_SEQUENCE_NO in NUMBER,
99 X_OBJECT_VERSION_NUMBER in NUMBER,
100 X_OBJECT_TYPE in VARCHAR2,
101 X_ACTIVITY_TYPE_CODE in VARCHAR2,
102 X_SETUP_ATTRIBUTE in VARCHAR2,
103 X_MANDATORY_FLAG in VARCHAR2,
104 X_USER_INTERFACE_FUNCTION in VARCHAR2,
105 X_PARENT_FUNCTION_NAME in VARCHAR2,
106 X_FUNCTION_NAME in VARCHAR2,
107 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
108 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
109 X_SHOW_IN_REPORT in VARCHAR2,
110 X_SHOW_IN_CUE_CARD in VARCHAR2,
111 X_COPY_ALLOWED_FLAG in VARCHAR2,
112 X_ESSENTIAL_SEQ_NUM in NUMBER,
113 X_USAGE in VARCHAR2,
114 X_APPLICATION_ID in NUMBER,
115 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
116 X_LAST_UPDATE_DATE in DATE,
117 X_LAST_UPDATED_BY in NUMBER,
118 X_LAST_UPDATE_LOGIN in NUMBER
119 ) is
120 begin
121 update AMS_SETUP_TYPES set
122 DISPLAY_SEQUENCE_NO = X_DISPLAY_SEQUENCE_NO,
123 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
124 OBJECT_TYPE = X_OBJECT_TYPE,
125 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
126 SETUP_ATTRIBUTE = X_SETUP_ATTRIBUTE,
127 MANDATORY_FLAG = X_MANDATORY_FLAG,
128 USER_INTERFACE_FUNCTION = X_USER_INTERFACE_FUNCTION,
129 SETUP_TYPE_ID = X_SETUP_TYPE_ID,
130 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
131 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
133 PARENT_FUNCTION_NAME = X_PARENT_FUNCTION_NAME,
134 FUNCTION_NAME = X_FUNCTION_NAME,
135 PARENT_SETUP_ATTRIBUTE = X_PARENT_SETUP_ATTRIBUTE,
136 PARENT_DISPLAY_SEQUENCE = X_PARENT_DISPLAY_SEQUENCE,
137 SHOW_IN_REPORT = X_SHOW_IN_REPORT,
138 SHOW_IN_CUE_CARD = X_SHOW_IN_CUE_CARD,
139 COPY_ALLOWED_FLAG = X_COPY_ALLOWED_FLAG,
140 ESSENTIAL_SEQ_NUM = X_ESSENTIAL_SEQ_NUM,
141 USAGE = X_USAGE,
142 APPLICATION_ID = X_APPLICATION_ID,
143 RELATED_AK_ATTRIBUTE = X_RELATED_AK_ATTRIBUTE
144 where SETUP_TYPE_ID = X_SETUP_TYPE_ID;
145
146 if (sql%notfound) then
147 raise no_data_found;
148 end if;
149 end UPDATE_ROW;
150
151 procedure DELETE_ROW (
152 X_SETUP_TYPE_ID in NUMBER
153 ) is
154 begin
155 delete from AMS_SETUP_TYPES
156 where SETUP_TYPE_ID = X_SETUP_TYPE_ID;
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 end DELETE_ROW;
163
164 procedure LOAD_ROW(
165 X_SETUP_TYPE_ID IN NUMBER DEFAULT NULL,
166 X_DISPLAY_SEQUENCE_NO in NUMBER DEFAULT NULL,
167 X_OBJECT_VERSION_NUMBER in NUMBER DEFAULT NULL,
168 X_OBJECT_TYPE in VARCHAR2 DEFAULT NULL,
169 X_ACTIVITY_TYPE_CODE in VARCHAR2 DEFAULT NULL,
170 X_SETUP_ATTRIBUTE in VARCHAR2 DEFAULT NULL,
171 X_MANDATORY_FLAG in VARCHAR2 DEFAULT NULL,
172 X_USER_INTERFACE_FUNCTION in VARCHAR2 DEFAULT NULL,
173 X_PARENT_FUNCTION_NAME in VARCHAR2,
174 X_FUNCTION_NAME in VARCHAR2,
175 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
176 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
177 X_SHOW_IN_REPORT in VARCHAR2,
178 X_SHOW_IN_CUE_CARD in VARCHAR2,
179 X_COPY_ALLOWED_FLAG in VARCHAR2,
180 X_ESSENTIAL_SEQ_NUM in NUMBER,
181 X_USAGE in VARCHAR2,
182 X_APPLICATION_ID in NUMBER,
183 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
184 X_Owner VARCHAR2
185 )
186 IS
187 l_user_id number := 0;
188 l_obj_verno number;
189 l_dummy_char varchar2(1);
190 l_row_id varchar2(100);
191 l_set_id number;
192
193 cursor c_obj_verno is
194 select object_version_number
195 from AMS_SETUP_TYPES
196 where setup_type_id = X_SETUP_TYPE_ID;
197
198 cursor c_chk_set_exists is
199 select 'x'
200 from AMS_SETUP_TYPES
201 where setup_type_id = X_SETUP_TYPE_ID;
202
203 cursor c_get_setid is
204 select AMS_SETUP_TYPES_S.nextval
205 from dual;
206 BEGIN
207
208 if X_OWNER = 'SEED' then
209 l_user_id := 1;
210 end if;
211
212 open c_chk_set_exists;
213 fetch c_chk_set_exists into l_dummy_char;
214 if c_chk_set_exists%notfound
215 then
216 close c_chk_set_exists;
217 if X_SETUP_TYPE_ID is null
218 then
219 open c_get_setid;
220 fetch c_get_setid into l_set_id;
221 close c_get_setid;
222 else
223 l_set_id := X_SETUP_TYPE_ID;
224 end if;
225 l_obj_verno := 1;
226 AMS_SETUP_TYPES_PKG.INSERT_ROW(
227 X_ROWID => l_row_id,
228 X_SETUP_TYPE_ID => X_SETUP_TYPE_ID,
229 X_DISPLAY_SEQUENCE_NO => X_DISPLAY_SEQUENCE_NO,
230 X_OBJECT_VERSION_NUMBER => l_obj_verno,
231 X_OBJECT_TYPE => X_OBJECT_TYPE,
232 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
233 X_SETUP_ATTRIBUTE => X_SETUP_ATTRIBUTE,
234 X_MANDATORY_FLAG => X_MANDATORY_FLAG,
235 X_USER_INTERFACE_FUNCTION => X_USER_INTERFACE_FUNCTION,
236 X_PARENT_FUNCTION_NAME => X_PARENT_FUNCTION_NAME,
237 X_FUNCTION_NAME => X_FUNCTION_NAME,
238 X_PARENT_SETUP_ATTRIBUTE => X_PARENT_SETUP_ATTRIBUTE,
239 X_PARENT_DISPLAY_SEQUENCE => X_PARENT_DISPLAY_SEQUENCE,
240 X_SHOW_IN_REPORT => X_SHOW_IN_REPORT,
241 X_SHOW_IN_CUE_CARD => X_SHOW_IN_CUE_CARD,
242 X_COPY_ALLOWED_FLAG => X_COPY_ALLOWED_FLAG,
243 X_ESSENTIAL_SEQ_NUM => X_ESSENTIAL_SEQ_NUM,
244 X_USAGE => X_USAGE,
245 X_APPLICATION_ID => X_APPLICATION_ID,
246 X_RELATED_AK_ATTRIBUTE => X_RELATED_AK_ATTRIBUTE,
247 X_CREATION_DATE => SYSDATE,
248 X_CREATED_BY => l_user_id,
249 X_LAST_UPDATE_DATE => SYSDATE,
250 X_LAST_UPDATED_BY => l_user_id,
251 X_LAST_UPDATE_LOGIN => 0
252 );
253 else
254 close c_chk_set_exists;
255 AMS_SETUP_TYPES_PKG.UPDATE_ROW(
256 X_SETUP_TYPE_ID => X_SETUP_TYPE_ID,
257 X_DISPLAY_SEQUENCE_NO => X_DISPLAY_SEQUENCE_NO,
258 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER + 1,
259 X_OBJECT_TYPE => X_OBJECT_TYPE,
260 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
261 X_SETUP_ATTRIBUTE => X_SETUP_ATTRIBUTE,
262 X_MANDATORY_FLAG => X_MANDATORY_FLAG,
263 X_USER_INTERFACE_FUNCTION => X_USER_INTERFACE_FUNCTION,
264 X_PARENT_FUNCTION_NAME => X_PARENT_FUNCTION_NAME,
265 X_FUNCTION_NAME => X_FUNCTION_NAME,
266 X_PARENT_SETUP_ATTRIBUTE => X_PARENT_SETUP_ATTRIBUTE,
267 X_PARENT_DISPLAY_SEQUENCE => X_PARENT_DISPLAY_SEQUENCE,
268 X_SHOW_IN_REPORT => X_SHOW_IN_REPORT,
269 X_SHOW_IN_CUE_CARD => X_SHOW_IN_CUE_CARD,
270 X_COPY_ALLOWED_FLAG => X_COPY_ALLOWED_FLAG,
271 X_USAGE => X_USAGE,
272 X_ESSENTIAL_SEQ_NUM => X_ESSENTIAL_SEQ_NUM,
273 X_APPLICATION_ID => X_APPLICATION_ID,
274 X_RELATED_AK_ATTRIBUTE => X_RELATED_AK_ATTRIBUTE,
275 X_LAST_UPDATE_DATE => SYSDATE,
276 X_LAST_UPDATED_BY => l_user_id,
277 X_LAST_UPDATE_LOGIN => 0
278 );
279
280 end if;
281 END LOAD_ROW;
282
283 end AMS_SETUP_TYPES_PKG;