[Home] [Help]
PACKAGE BODY: APPS.ALR_LOOKUPS_PKG
Source
1 package body ALR_LOOKUPS_PKG as
2 /* $Header: ALRLKUPB.pls 120.3.12010000.1 2008/07/27 06:58:44 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_LOOKUP_CODE in VARCHAR2,
7 X_MEANING in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from ALR_LOOKUPS
19 where LOOKUP_TYPE = X_LOOKUP_TYPE
20 and LOOKUP_CODE = X_LOOKUP_CODE
21 ;
22 begin
23 insert into ALR_LOOKUPS (
24 LOOKUP_TYPE,
25 LOOKUP_CODE,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 MEANING,
29 ENABLED_FLAG,
30 DESCRIPTION,
31 START_DATE_ACTIVE,
32 END_DATE_ACTIVE,
33 LAST_UPDATE_LOGIN,
34 CREATION_DATE,
35 CREATED_BY
36 ) values (
37 X_LOOKUP_TYPE,
38 X_LOOKUP_CODE,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_MEANING,
42 X_ENABLED_FLAG,
43 X_DESCRIPTION,
44 X_START_DATE_ACTIVE,
45 X_END_DATE_ACTIVE,
46 X_LAST_UPDATE_LOGIN,
47 X_CREATION_DATE,
48 X_CREATED_BY );
49
50 open c;
51 fetch c into X_ROWID;
52 if (c%notfound) then
53 close c;
54 raise no_data_found;
55 end if;
56 close c;
57
58 end INSERT_ROW;
59
60 procedure LOAD_ROW (
61 X_LOOKUP_TYPE in VARCHAR2,
62 X_LOOKUP_CODE in VARCHAR2,
63 X_OWNER in VARCHAR2,
64 X_MEANING in VARCHAR2,
65 X_ENABLED_FLAG in VARCHAR2,
66 X_DESCRIPTION in VARCHAR2,
67 X_START_DATE_ACTIVE in VARCHAR2,
68 X_END_DATE_ACTIVE in VARCHAR2,
69 X_LAST_UPDATE_DATE in VARCHAR2,
70 X_CUSTOM_MODE in VARCHAR2
71 ) is
72 l_user_id number := 0;
73 l_row_id varchar2(64);
74
75 f_luby number; -- entity owner in file
76 f_ludate date; -- entity update date in file
77 db_luby number; -- entity owner in db
78 db_ludate date; -- entity update date in db
79
80
81 begin
82
83 -- Translate owner to file_last_updated_by
84 f_luby := fnd_load_util.owner_id(X_OWNER);
85
86 -- Translate char last_update_date to date
87 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
88
89 select last_updated_by, last_update_date
90 into db_luby, db_ludate
91 from ALR_LOOKUPS
92 where lookup_code = X_LOOKUP_CODE
93 and lookup_type = X_LOOKUP_TYPE;
94
95 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
96 x_custom_mode)) then
97
98
99 ALR_LOOKUPS_PKG.UPDATE_ROW (
100 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
101 X_LOOKUP_CODE => X_LOOKUP_CODE,
102 X_MEANING => X_MEANING,
103 X_ENABLED_FLAG => X_ENABLED_FLAG,
104 X_DESCRIPTION => X_DESCRIPTION,
105 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE,
106 'YYYY/MM/DD HH24:MI:SS'),
107 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,
108 'YYYY/MM/DD HH24:MI:SS'),
109 X_LAST_UPDATE_DATE => f_ludate,
110 X_LAST_UPDATED_BY => f_luby,
111 X_LAST_UPDATE_LOGIN => 0 );
112
113 end if;
114 exception
115 when NO_DATA_FOUND then
116
117 ALR_LOOKUPS_PKG.INSERT_ROW (
118 X_ROWID => l_row_id,
119 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
120 X_LOOKUP_CODE => X_LOOKUP_CODE,
121 X_MEANING => X_MEANING,
122 X_ENABLED_FLAG => X_ENABLED_FLAG,
123 X_DESCRIPTION => X_DESCRIPTION,
124 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE,
125 'YYYY/MM/DD HH24:MI:SS'),
126 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,
127 'YYYY/MM/DD HH24:MI:SS'),
128 X_CREATION_DATE => f_ludate,
129 X_CREATED_BY => f_luby,
130 X_LAST_UPDATE_DATE => f_ludate,
131 X_LAST_UPDATED_BY => f_luby,
132 X_LAST_UPDATE_LOGIN => 0 );
133 end LOAD_ROW;
134
135
136 procedure LOCK_ROW (
137 X_LOOKUP_TYPE in VARCHAR2,
138 X_LOOKUP_CODE in VARCHAR2,
139 X_MEANING in VARCHAR2,
140 X_ENABLED_FLAG in VARCHAR2,
141 X_DESCRIPTION in VARCHAR2,
142 X_START_DATE_ACTIVE in DATE,
143 X_END_DATE_ACTIVE in DATE
144 ) is
145 cursor c1 is select
146 MEANING,
147 ENABLED_FLAG,
148 DESCRIPTION,
149 START_DATE_ACTIVE,
150 END_DATE_ACTIVE,
151 LOOKUP_TYPE,
152 LOOKUP_CODE
153 from ALR_LOOKUPS
154 where LOOKUP_TYPE = X_LOOKUP_TYPE
155 and LOOKUP_CODE = X_LOOKUP_CODE
156 for update of LOOKUP_TYPE nowait;
157 begin
158 for recinfo in c1 loop
159 if ( (recinfo.LOOKUP_TYPE = X_LOOKUP_TYPE)
160 AND (recinfo.LOOKUP_CODE = X_LOOKUP_CODE)
161 AND (recinfo.MEANING = X_MEANING)
162 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
163 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
164 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
165 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
166 OR ((recinfo.START_DATE_ACTIVE is null)
167 AND (X_START_DATE_ACTIVE is null)))
168 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
169 OR ((recinfo.END_DATE_ACTIVE is null)
170 AND (X_END_DATE_ACTIVE is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177 end loop;
178 return;
179 end LOCK_ROW;
180
181 procedure UPDATE_ROW (
182 X_LOOKUP_TYPE in VARCHAR2,
183 X_LOOKUP_CODE in VARCHAR2,
184 X_MEANING in VARCHAR2,
185 X_ENABLED_FLAG in VARCHAR2,
186 X_DESCRIPTION in VARCHAR2,
187 X_START_DATE_ACTIVE in DATE,
188 X_END_DATE_ACTIVE in DATE,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATED_BY in NUMBER,
191 X_LAST_UPDATE_LOGIN in NUMBER
192 ) is
193 begin
194 update ALR_LOOKUPS set
195 MEANING = X_MEANING,
196 ENABLED_FLAG = X_ENABLED_FLAG,
197 DESCRIPTION = X_DESCRIPTION,
198 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
199 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
200 LOOKUP_TYPE = X_LOOKUP_TYPE,
201 LOOKUP_CODE = X_LOOKUP_CODE,
202 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
203 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
205 where LOOKUP_TYPE = X_LOOKUP_TYPE
206 and LOOKUP_CODE = X_LOOKUP_CODE;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211 end UPDATE_ROW;
212
213 procedure DELETE_ROW (
214 X_LOOKUP_TYPE in VARCHAR2,
215 X_LOOKUP_CODE in VARCHAR2
216 ) is
217 begin
218 delete from ALR_LOOKUPS
219 where LOOKUP_TYPE = X_LOOKUP_TYPE
220 and LOOKUP_CODE = X_LOOKUP_CODE;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225
226 end DELETE_ROW;
227
228
229 end ALR_LOOKUPS_PKG;