[Home] [Help]
PACKAGE BODY: APPS.BNE_MAPPING_LINES_PKG
Source
1 package body BNE_MAPPING_LINES_PKG as
2 /* $Header: bnemaplineb.pls 120.2 2005/06/29 03:40:23 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_MAPPING_CODE in VARCHAR2,
8 X_INTERFACE_APP_ID in NUMBER,
9 X_INTERFACE_CODE in VARCHAR2,
10 X_INTERFACE_SEQ_NUM in NUMBER,
11 X_DECODE_FLAG in VARCHAR2,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_SEQUENCE_NUM in NUMBER,
14 X_CONTENT_SEQ_NUM in NUMBER,
15 X_CONTENT_APP_ID in NUMBER,
16 X_CONTENT_CODE in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 cursor C is select ROWID from BNE_MAPPING_LINES
24 where APPLICATION_ID = X_APPLICATION_ID
25 and MAPPING_CODE = X_MAPPING_CODE
26 and INTERFACE_APP_ID = X_INTERFACE_APP_ID
27 and INTERFACE_CODE = X_INTERFACE_CODE
28 and INTERFACE_SEQ_NUM = X_INTERFACE_SEQ_NUM
29 ;
30 begin
31 insert into BNE_MAPPING_LINES (
32 INTERFACE_CODE,
33 INTERFACE_SEQ_NUM,
34 OBJECT_VERSION_NUMBER,
35 CREATED_BY,
36 CREATION_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN,
39 LAST_UPDATE_DATE,
40 SEQUENCE_NUM,
41 INTERFACE_APP_ID,
42 CONTENT_CODE,
43 CONTENT_SEQ_NUM,
44 APPLICATION_ID,
45 MAPPING_CODE,
46 CONTENT_APP_ID,
47 DECODE_FLAG
48 ) values (
49 X_INTERFACE_CODE,
50 X_INTERFACE_SEQ_NUM,
51 X_OBJECT_VERSION_NUMBER,
52 X_CREATED_BY,
53 X_CREATION_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN,
56 X_LAST_UPDATE_DATE,
57 X_SEQUENCE_NUM,
58 X_INTERFACE_APP_ID,
59 X_CONTENT_CODE,
60 X_CONTENT_SEQ_NUM,
61 X_APPLICATION_ID,
62 X_MAPPING_CODE,
63 X_CONTENT_APP_ID,
64 X_DECODE_FLAG
65 );
66
67 open c;
68 fetch c into X_ROWID;
69 if (c%notfound) then
70 close c;
71 raise no_data_found;
72 end if;
73 close c;
74
75 end INSERT_ROW;
76
77 procedure LOCK_ROW (
78 X_APPLICATION_ID in NUMBER,
79 X_MAPPING_CODE in VARCHAR2,
80 X_INTERFACE_APP_ID in NUMBER,
81 X_INTERFACE_CODE in VARCHAR2,
82 X_INTERFACE_SEQ_NUM in NUMBER,
83 X_DECODE_FLAG in VARCHAR2,
84 X_OBJECT_VERSION_NUMBER in NUMBER,
85 X_SEQUENCE_NUM in NUMBER,
86 X_CONTENT_SEQ_NUM in NUMBER,
87 X_CONTENT_APP_ID in NUMBER,
88 X_CONTENT_CODE in VARCHAR2
89 ) is
90 cursor c1 is select
91 OBJECT_VERSION_NUMBER,
92 SEQUENCE_NUM,
93 CONTENT_SEQ_NUM,
94 CONTENT_APP_ID,
95 CONTENT_CODE,
96 DECODE_FLAG
97 from BNE_MAPPING_LINES
98 where APPLICATION_ID = X_APPLICATION_ID
99 and MAPPING_CODE = X_MAPPING_CODE
100 and INTERFACE_APP_ID = X_INTERFACE_APP_ID
101 and INTERFACE_CODE = X_INTERFACE_CODE
102 and INTERFACE_SEQ_NUM = X_INTERFACE_SEQ_NUM
103 for update of APPLICATION_ID nowait;
104 begin
105 for tlinfo in c1 loop
106 if ( (tlinfo.CONTENT_CODE = X_CONTENT_CODE)
107 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
108 AND (tlinfo.SEQUENCE_NUM = X_SEQUENCE_NUM)
109 AND (tlinfo.CONTENT_SEQ_NUM = X_CONTENT_SEQ_NUM)
110 AND (tlinfo.CONTENT_APP_ID = X_CONTENT_APP_ID)
111 AND ((tlinfo.DECODE_FLAG = X_DECODE_FLAG)
112 OR ((tlinfo.DECODE_FLAG is null) AND (X_DECODE_FLAG is null)))
113 ) then
114 null;
115 else
116 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
117 app_exception.raise_exception;
118 end if;
119 end loop;
120 return;
121 end LOCK_ROW;
122
123 procedure UPDATE_ROW (
124 X_APPLICATION_ID in NUMBER,
125 X_MAPPING_CODE in VARCHAR2,
126 X_INTERFACE_APP_ID in NUMBER,
127 X_INTERFACE_CODE in VARCHAR2,
128 X_INTERFACE_SEQ_NUM in NUMBER,
129 X_DECODE_FLAG in VARCHAR2,
130 X_OBJECT_VERSION_NUMBER in NUMBER,
131 X_SEQUENCE_NUM in NUMBER,
132 X_CONTENT_SEQ_NUM in NUMBER,
133 X_CONTENT_APP_ID in NUMBER,
134 X_CONTENT_CODE in VARCHAR2,
135 X_LAST_UPDATE_DATE in DATE,
136 X_LAST_UPDATED_BY in NUMBER,
137 X_LAST_UPDATE_LOGIN in NUMBER
138 ) is
139 begin
140 update BNE_MAPPING_LINES set
141 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
142 INTERFACE_APP_ID = X_INTERFACE_APP_ID,
143 INTERFACE_CODE = X_INTERFACE_CODE,
144 INTERFACE_SEQ_NUM = X_INTERFACE_SEQ_NUM,
145 CONTENT_SEQ_NUM = X_CONTENT_SEQ_NUM,
146 CONTENT_APP_ID = X_CONTENT_APP_ID,
147 CONTENT_CODE = X_CONTENT_CODE,
148 DECODE_FLAG = X_DECODE_FLAG,
149 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
152 where APPLICATION_ID = X_APPLICATION_ID
153 and MAPPING_CODE = X_MAPPING_CODE
154 and SEQUENCE_NUM = X_SEQUENCE_NUM;
155 -- and INTERFACE_APP_ID = X_INTERFACE_APP_ID
156 -- and INTERFACE_CODE = X_INTERFACE_CODE
157 -- and INTERFACE_SEQ_NUM = X_INTERFACE_SEQ_NUM;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162 end UPDATE_ROW;
163
164 procedure DELETE_ROW (
165 X_APPLICATION_ID in NUMBER,
166 X_MAPPING_CODE in VARCHAR2,
167 X_INTERFACE_APP_ID in NUMBER,
168 X_INTERFACE_CODE in VARCHAR2,
169 X_INTERFACE_SEQ_NUM in NUMBER
170 ) is
171 begin
172 delete from BNE_MAPPING_LINES
173 where APPLICATION_ID = X_APPLICATION_ID
174 and MAPPING_CODE = X_MAPPING_CODE
175 and INTERFACE_APP_ID = X_INTERFACE_APP_ID
176 and INTERFACE_CODE = X_INTERFACE_CODE
177 and INTERFACE_SEQ_NUM = X_INTERFACE_SEQ_NUM;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 end DELETE_ROW;
184
185 procedure ADD_LANGUAGE
186 is
187 begin
188 null;
189 end ADD_LANGUAGE;
190
191 --------------------------------------------------------------------------------
192 -- PROCEDURE: LOAD_ROW --
193 -- --
194 -- DESCRIPTION: Load a row into the BNE_MAPPING_LINES entity. --
195 -- This proc is called from the apps loader. --
196 -- --
197 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
198 -- --
199 -- --
200 -- MODIFICATION HISTORY --
201 -- Date Username Description --
202 -- 1-Oct-02 DGROVES CREATED --
203 --------------------------------------------------------------------------------
204 procedure LOAD_ROW(
205 x_mapping_asn in VARCHAR2,
206 x_mapping_code in VARCHAR2,
207 x_interface_asn in VARCHAR2,
208 x_interface_code in VARCHAR2,
209 x_interface_seq_num in VARCHAR2,
210 x_decode_flag in VARCHAR2,
211 x_object_version_number in VARCHAR2,
212 x_sequence_num in VARCHAR2,
213 x_content_asn in VARCHAR2,
214 x_content_code in VARCHAR2,
215 x_content_seq_num in VARCHAR2,
216 x_owner in VARCHAR2,
217 x_last_update_date in VARCHAR2,
218 x_custom_mode in VARCHAR2
219 )
220 is
221 l_app_id number;
222 l_interface_app_id number;
223 l_content_app_id number;
224 l_row_id varchar2(64);
225 f_luby number; -- entity owner in file
226 f_ludate date; -- entity update date in file
227 db_luby number; -- entity owner in db
228 db_ludate date; -- entity update date in db
229 begin
230 -- translate values to IDs
231 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_mapping_asn);
232 l_interface_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_interface_asn);
233 l_content_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_content_asn);
234
235 -- Translate owner to file_last_updated_by
236 f_luby := fnd_load_util.owner_id(x_owner);
237
238 -- Translate char last_update_date to date
239 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
240 begin
241 select LAST_UPDATED_BY, LAST_UPDATE_DATE
242 into db_luby, db_ludate
243 from BNE_MAPPING_LINES
244 where APPLICATION_ID = l_app_id
245 and MAPPING_CODE = x_mapping_code
246 and SEQUENCE_NUM = x_sequence_num;
247 -- bug3510034 - DBI70:INST:FII LDT FILES FAILURE WHILE APPLYING BIS_PF.D
248 -- Use different unique index as the business rule index is not stable enough to update rows on.
249 -- Example: update row 3 to point to a different interface col and regenerate ldt.
250 -- Row is not found by old select (as PK had changed) and we tried the insert below, which
251 -- would fail on the other (sequence_num) index.
252 -- The sequence_num index is therfore seen to be more stable for this type of operation.
253 --and INTERFACE_APP_ID = l_interface_app_id
254 --and INTERFACE_CODE = x_interface_code
255 --and INTERFACE_SEQ_NUM = x_interface_seq_num;
256
257 -- Test for customization and version
258 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
259 db_ludate, x_custom_mode)) then
260 -- Update existing row
261 BNE_MAPPING_LINES_PKG.Update_Row(
262 X_APPLICATION_ID => l_app_id,
263 X_MAPPING_CODE => x_mapping_code,
264 X_INTERFACE_APP_ID => l_interface_app_id,
265 X_INTERFACE_CODE => x_interface_code,
266 X_INTERFACE_SEQ_NUM => x_interface_seq_num,
267 X_DECODE_FLAG => x_decode_flag,
268 X_OBJECT_VERSION_NUMBER => x_object_version_number,
269 X_SEQUENCE_NUM => x_sequence_num,
270 X_CONTENT_SEQ_NUM => x_content_seq_num,
271 X_CONTENT_APP_ID => l_content_app_id,
272 X_CONTENT_CODE => x_content_code,
273 X_LAST_UPDATE_DATE => f_ludate,
274 X_LAST_UPDATED_BY => f_luby,
275 X_LAST_UPDATE_LOGIN => 0
276 );
277 end if;
278 exception
279 when no_data_found then
280 -- Record doesn't exist - insert in all cases
281 BNE_MAPPING_LINES_PKG.Insert_Row(
282 X_ROWID => l_row_id,
283 X_APPLICATION_ID => l_app_id,
284 X_MAPPING_CODE => x_mapping_code,
285 X_INTERFACE_APP_ID => l_interface_app_id,
286 X_INTERFACE_CODE => x_interface_code,
287 X_INTERFACE_SEQ_NUM => x_interface_seq_num,
288 X_DECODE_FLAG => x_decode_flag,
289 X_OBJECT_VERSION_NUMBER => x_object_version_number,
290 X_SEQUENCE_NUM => x_sequence_num,
291 X_CONTENT_SEQ_NUM => x_content_seq_num,
292 X_CONTENT_APP_ID => l_content_app_id,
293 X_CONTENT_CODE => x_content_code,
294 X_CREATION_DATE => f_ludate,
295 X_CREATED_BY => f_luby,
296 X_LAST_UPDATE_DATE => f_ludate,
297 X_LAST_UPDATED_BY => f_luby,
298 X_LAST_UPDATE_LOGIN => 0
299 );
300 end;
301 end LOAD_ROW;
302
303
304
305 end BNE_MAPPING_LINES_PKG;