[Home] [Help]
PACKAGE BODY: APPS.FND_LOOKUP_ASSIGNMENTS_PKG
Source
1 package body FND_LOOKUP_ASSIGNMENTS_PKG as
2 /* $Header: AFLVFLAB.pls 115.0 2004/08/04 21:03:35 stopiwal noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_ASSIGNMENT_ID in NUMBER,
6 X_INSTANCE_PK1_VALUE in VARCHAR2,
7 X_OBJ_NAME in VARCHAR2,
8 X_INSTANCE_PK4_VALUE in VARCHAR2,
9 X_INSTANCE_PK5_VALUE in VARCHAR2,
10 X_DISPLAY_SEQUENCE in NUMBER,
11 X_LOOKUP_CODE in VARCHAR2,
12 X_LOOKUP_TYPE in VARCHAR2,
13 X_INSTANCE_PK2_VALUE in VARCHAR2,
14 X_INSTANCE_PK3_VALUE in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from FND_LOOKUP_ASSIGNMENTS
22 where LOOKUP_ASSIGNMENT_ID = X_LOOKUP_ASSIGNMENT_ID
23 ;
24 begin
25 insert into FND_LOOKUP_ASSIGNMENTS (
26 LOOKUP_ASSIGNMENT_ID,
27 INSTANCE_PK1_VALUE,
28 OBJ_NAME,
29 INSTANCE_PK4_VALUE,
30 INSTANCE_PK5_VALUE,
31 DISPLAY_SEQUENCE,
32 LOOKUP_CODE,
33 LOOKUP_TYPE,
34 INSTANCE_PK2_VALUE,
35 INSTANCE_PK3_VALUE,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_LOOKUP_ASSIGNMENT_ID,
43 X_INSTANCE_PK1_VALUE,
44 X_OBJ_NAME,
45 X_INSTANCE_PK4_VALUE,
46 X_INSTANCE_PK5_VALUE,
47 X_DISPLAY_SEQUENCE,
48 X_LOOKUP_CODE,
49 X_LOOKUP_TYPE,
50 X_INSTANCE_PK2_VALUE,
51 X_INSTANCE_PK3_VALUE,
52 X_CREATION_DATE,
53 X_CREATED_BY,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_LOGIN
57 );
58
59 open c;
60 fetch c into X_ROWID;
61 if (c%notfound) then
62 close c;
63 raise no_data_found;
64 end if;
65 close c;
66
67 end INSERT_ROW;
68
69 procedure LOCK_ROW (
70 X_LOOKUP_ASSIGNMENT_ID in NUMBER,
71 X_INSTANCE_PK1_VALUE in VARCHAR2,
72 X_OBJ_NAME in VARCHAR2,
73 X_INSTANCE_PK4_VALUE in VARCHAR2,
74 X_INSTANCE_PK5_VALUE in VARCHAR2,
75 X_DISPLAY_SEQUENCE in NUMBER,
76 X_LOOKUP_CODE in VARCHAR2,
77 X_LOOKUP_TYPE in VARCHAR2,
78 X_INSTANCE_PK2_VALUE in VARCHAR2,
79 X_INSTANCE_PK3_VALUE in VARCHAR2
80 ) is
81 cursor c is select
82 INSTANCE_PK1_VALUE,
83 OBJ_NAME,
84 INSTANCE_PK4_VALUE,
85 INSTANCE_PK5_VALUE,
86 DISPLAY_SEQUENCE,
87 LOOKUP_CODE,
88 LOOKUP_TYPE,
89 INSTANCE_PK2_VALUE,
90 INSTANCE_PK3_VALUE
91 from FND_LOOKUP_ASSIGNMENTS
92 where LOOKUP_ASSIGNMENT_ID = X_LOOKUP_ASSIGNMENT_ID
93 for update of LOOKUP_ASSIGNMENT_ID nowait;
94 recinfo c%rowtype;
95
96 begin
97 open c;
98 fetch c into recinfo;
99 if (c%notfound) then
100 close c;
101 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
102 app_exception.raise_exception;
103 end if;
104 close c;
105 if ( (recinfo.INSTANCE_PK1_VALUE = X_INSTANCE_PK1_VALUE)
106 AND (recinfo.OBJ_NAME = X_OBJ_NAME)
107 AND ((recinfo.INSTANCE_PK4_VALUE = X_INSTANCE_PK4_VALUE)
108 OR ((recinfo.INSTANCE_PK4_VALUE is null) AND (X_INSTANCE_PK4_VALUE is null)))
109 AND ((recinfo.INSTANCE_PK5_VALUE = X_INSTANCE_PK5_VALUE)
110 OR ((recinfo.INSTANCE_PK5_VALUE is null) AND (X_INSTANCE_PK5_VALUE is null)))
111 AND ((recinfo.DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE)
112 OR ((recinfo.DISPLAY_SEQUENCE is null) AND (X_DISPLAY_SEQUENCE is null)))
113 AND (recinfo.LOOKUP_CODE = X_LOOKUP_CODE)
114 AND (recinfo.LOOKUP_TYPE = X_LOOKUP_TYPE)
115 AND ((recinfo.INSTANCE_PK2_VALUE = X_INSTANCE_PK2_VALUE)
116 OR ((recinfo.INSTANCE_PK2_VALUE is null) AND (X_INSTANCE_PK2_VALUE is null)))
117 AND ((recinfo.INSTANCE_PK3_VALUE = X_INSTANCE_PK3_VALUE)
118 OR ((recinfo.INSTANCE_PK3_VALUE is null) AND (X_INSTANCE_PK3_VALUE is null)))
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125
126 return;
127 end LOCK_ROW;
128
129 procedure UPDATE_ROW (
130 X_LOOKUP_ASSIGNMENT_ID in NUMBER,
131 X_INSTANCE_PK1_VALUE in VARCHAR2,
132 X_OBJ_NAME in VARCHAR2,
133 X_INSTANCE_PK4_VALUE in VARCHAR2,
134 X_INSTANCE_PK5_VALUE in VARCHAR2,
135 X_DISPLAY_SEQUENCE in NUMBER,
136 X_LOOKUP_CODE in VARCHAR2,
137 X_LOOKUP_TYPE in VARCHAR2,
138 X_INSTANCE_PK2_VALUE in VARCHAR2,
139 X_INSTANCE_PK3_VALUE in VARCHAR2,
140 X_LAST_UPDATE_DATE in DATE,
141 X_LAST_UPDATED_BY in NUMBER,
142 X_LAST_UPDATE_LOGIN in NUMBER
143 ) is
144 begin
145 update FND_LOOKUP_ASSIGNMENTS set
146 INSTANCE_PK1_VALUE = X_INSTANCE_PK1_VALUE,
147 OBJ_NAME = X_OBJ_NAME,
148 INSTANCE_PK4_VALUE = X_INSTANCE_PK4_VALUE,
149 INSTANCE_PK5_VALUE = X_INSTANCE_PK5_VALUE,
150 DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE,
151 LOOKUP_CODE = X_LOOKUP_CODE,
152 LOOKUP_TYPE = X_LOOKUP_TYPE,
153 INSTANCE_PK2_VALUE = X_INSTANCE_PK2_VALUE,
154 INSTANCE_PK3_VALUE = X_INSTANCE_PK3_VALUE,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
158 where LOOKUP_ASSIGNMENT_ID = X_LOOKUP_ASSIGNMENT_ID;
159
160 if (sql%notfound) then
161 raise no_data_found;
162 end if;
163
164 end UPDATE_ROW;
165
166 procedure DELETE_ROW (
167 X_LOOKUP_ASSIGNMENT_ID in NUMBER
168 ) is
169 begin
170 delete from FND_LOOKUP_ASSIGNMENTS
171 where LOOKUP_ASSIGNMENT_ID = X_LOOKUP_ASSIGNMENT_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end DELETE_ROW;
177
178 procedure Load_Row (
179 x_lookup_type in varchar2,
180 x_lookup_code in varchar2,
181 x_obj_name in varchar2,
182 x_display_sequence in varchar2,
183 X_INSTANCE_PK1_VALUE in varchar2,
184 X_INSTANCE_PK2_VALUE in varchar2,
185 X_INSTANCE_PK3_VALUE in varchar2,
186 X_INSTANCE_PK4_VALUE in varchar2,
187 X_INSTANCE_PK5_VALUE in varchar2,
188 x_last_update_date in varchar2,
189 x_owner in varchar2,
190 x_custom_mode in varchar2)
191 is
192 row_id varchar2(64);
193 f_luby number; -- entity owner in file
194 f_ludate date; -- entity update date in file
195 db_luby number; -- entity owner in db
196 db_ludate date; -- entity update date in db
197 db_luid number; -- entity key in db
198
199 begin
200
201 -- Translate owner to file_last_updated_by
202 f_luby := fnd_load_util.owner_id(x_owner);
203
204 -- Translate char last_update_date to date
205 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
206
207 -- check the db last update fields for each record in the cursor
208 begin
209 select Lookup_assignment_id, LAST_UPDATED_BY, LAST_UPDATE_DATE
210 into db_luid, db_luby, db_ludate
211 from fnd_lookup_assignments
212 where LOOKUP_TYPE = X_LOOKUP_TYPE
213 and OBJ_NAME = x_obj_name
214 and INSTANCE_PK1_VALUE = X_INSTANCE_PK1_VALUE
215 and LOOKUP_CODE = X_LOOKUP_CODE;
216
217 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
218 db_ludate, X_CUSTOM_MODE)) then
219 Fnd_lookup_assignments_Pkg.Update_Row (
220 X_LOOKUP_ASSIGNMENT_ID => db_luid,
221 X_LOOKUP_TYPE => x_lookup_type,
222 X_LOOKUP_CODE => x_lookup_code,
223 X_OBJ_NAME => x_obj_name,
224 X_INSTANCE_PK1_VALUE => x_instance_pk1_value,
225 X_INSTANCE_PK2_VALUE => x_instance_pk2_value,
226 X_INSTANCE_PK3_VALUE => x_instance_pk3_value,
227 X_INSTANCE_PK4_VALUE => x_instance_pk4_value,
228 X_INSTANCE_PK5_VALUE => x_instance_pk5_value,
229 X_DISPLAY_SEQUENCE => TO_NUMBER(x_display_sequence),
230 X_LAST_UPDATE_DATE => f_ludate,
231 X_LAST_UPDATED_BY => f_luby,
232 X_LAST_UPDATE_LOGIN => 0);
233 end if;
234
235 exception
236 when no_data_found then
237 select FND_LOOKUP_ASSIGNMENTS_S.NEXTVAL
238 into db_luid
239 from dual;
240
241 Fnd_lookup_assignments_Pkg.Insert_Row(
242 X_ROWID => row_id,
243 X_LOOKUP_ASSIGNMENT_ID => db_luid,
244 X_LOOKUP_TYPE => x_lookup_type,
245 X_LOOKUP_CODE => x_lookup_code,
246 X_OBJ_NAME => x_obj_name,
247 X_INSTANCE_PK1_VALUE => x_instance_pk1_value,
248 X_INSTANCE_PK2_VALUE => x_instance_pk2_value,
249 X_INSTANCE_PK3_VALUE => x_instance_pk3_value,
250 X_INSTANCE_PK4_VALUE => x_instance_pk4_value,
251 X_INSTANCE_PK5_VALUE => x_instance_pk5_value,
252 X_DISPLAY_SEQUENCE => TO_NUMBER(x_display_sequence),
253 X_CREATION_DATE => f_ludate,
254 X_CREATED_BY => f_luby,
255 X_LAST_UPDATE_DATE => f_ludate,
256 X_LAST_UPDATED_BY => f_luby,
257 X_LAST_UPDATE_LOGIN => 0);
258
259 end;
260
261 end Load_Row;
262
263 end FND_LOOKUP_ASSIGNMENTS_PKG;