[Home] [Help]
PACKAGE BODY: APPS.BNE_IMPORT_PROGRAMS_PKG
Source
1 package body BNE_IMPORT_PROGRAMS_PKG as
2 /* $Header: bneimpprogb.pls 120.2 2005/06/29 03:39:59 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_INTEGRATOR_CODE in VARCHAR2,
8 X_SEQUENCE_NUM in NUMBER,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_PARENT_SEQ_NUM in NUMBER,
11 X_IMPORT_TYPE in NUMBER,
12 X_IMPORT_PARAM_LIST_APP_ID in NUMBER,
13 X_IMPORT_PARAM_LIST_CODE in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from BNE_IMPORT_PROGRAMS
21 where APPLICATION_ID = X_APPLICATION_ID
22 and INTEGRATOR_CODE = X_INTEGRATOR_CODE
23 and SEQUENCE_NUM = X_SEQUENCE_NUM
24 ;
25 begin
26 insert into BNE_IMPORT_PROGRAMS (
27 APPLICATION_ID,
28 INTEGRATOR_CODE,
29 SEQUENCE_NUM,
30 OBJECT_VERSION_NUMBER,
31 PARENT_SEQ_NUM,
32 IMPORT_TYPE,
33 IMPORT_PARAM_LIST_APP_ID,
34 IMPORT_PARAM_LIST_CODE,
35 CREATED_BY,
36 CREATION_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN,
39 LAST_UPDATE_DATE
40 ) values (
41 X_APPLICATION_ID,
42 X_INTEGRATOR_CODE,
43 X_SEQUENCE_NUM,
44 X_OBJECT_VERSION_NUMBER,
45 X_PARENT_SEQ_NUM,
46 X_IMPORT_TYPE,
47 X_IMPORT_PARAM_LIST_APP_ID,
48 X_IMPORT_PARAM_LIST_CODE,
49 X_CREATED_BY,
50 X_CREATION_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN,
53 X_LAST_UPDATE_DATE
54 );
55
56 open c;
57 fetch c into X_ROWID;
58 if (c%notfound) then
59 close c;
60 raise no_data_found;
61 end if;
62 close c;
63
64 end INSERT_ROW;
65
66 procedure LOCK_ROW (
67 X_APPLICATION_ID in NUMBER,
68 X_INTEGRATOR_CODE in VARCHAR2,
69 X_SEQUENCE_NUM in NUMBER,
70 X_OBJECT_VERSION_NUMBER in NUMBER,
71 X_PARENT_SEQ_NUM in NUMBER,
72 X_IMPORT_TYPE in NUMBER,
73 X_IMPORT_PARAM_LIST_APP_ID in NUMBER,
74 X_IMPORT_PARAM_LIST_CODE in VARCHAR2
75 ) is
76 cursor c1 is select
77 OBJECT_VERSION_NUMBER,
78 PARENT_SEQ_NUM,
79 IMPORT_TYPE,
80 IMPORT_PARAM_LIST_APP_ID,
81 IMPORT_PARAM_LIST_CODE
82 from BNE_IMPORT_PROGRAMS
83 where APPLICATION_ID = X_APPLICATION_ID
84 and INTEGRATOR_CODE = X_INTEGRATOR_CODE
85 and SEQUENCE_NUM = X_SEQUENCE_NUM
86 for update of APPLICATION_ID nowait;
87 begin
88 for tlinfo in c1 loop
89 if ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
90 AND ((tlinfo.PARENT_SEQ_NUM = X_PARENT_SEQ_NUM)
91 OR ((tlinfo.PARENT_SEQ_NUM is null) AND (X_PARENT_SEQ_NUM is null)))
92 AND (tlinfo.IMPORT_TYPE = X_IMPORT_TYPE)
93 AND ((tlinfo.IMPORT_PARAM_LIST_APP_ID = X_IMPORT_PARAM_LIST_APP_ID)
94 OR ((tlinfo.IMPORT_PARAM_LIST_APP_ID is null) AND (X_IMPORT_PARAM_LIST_APP_ID is null)))
95 AND ((tlinfo.IMPORT_PARAM_LIST_CODE = X_IMPORT_PARAM_LIST_CODE)
96 OR ((tlinfo.IMPORT_PARAM_LIST_CODE is null) AND (X_IMPORT_PARAM_LIST_CODE is null)))
97 ) then
98 null;
99 else
100 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
101 app_exception.raise_exception;
102 end if;
103 end loop;
104 return;
105 end LOCK_ROW;
106
107 procedure UPDATE_ROW (
108 X_APPLICATION_ID in NUMBER,
109 X_INTEGRATOR_CODE in VARCHAR2,
110 X_SEQUENCE_NUM in NUMBER,
111 X_OBJECT_VERSION_NUMBER in NUMBER,
112 X_PARENT_SEQ_NUM in NUMBER,
113 X_IMPORT_TYPE in NUMBER,
114 X_IMPORT_PARAM_LIST_APP_ID in NUMBER,
115 X_IMPORT_PARAM_LIST_CODE 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 BNE_IMPORT_PROGRAMS set
122 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
123 PARENT_SEQ_NUM = X_PARENT_SEQ_NUM,
124 IMPORT_TYPE = X_IMPORT_TYPE,
125 IMPORT_PARAM_LIST_APP_ID = X_IMPORT_PARAM_LIST_APP_ID,
126 IMPORT_PARAM_LIST_CODE = X_IMPORT_PARAM_LIST_CODE,
127 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
128 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
129 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
130 where APPLICATION_ID = X_APPLICATION_ID
131 and INTEGRATOR_CODE = X_INTEGRATOR_CODE
132 and SEQUENCE_NUM = X_SEQUENCE_NUM;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137 end UPDATE_ROW;
138
139 procedure DELETE_ROW (
140 X_APPLICATION_ID in NUMBER,
141 X_INTEGRATOR_CODE in VARCHAR2,
142 X_SEQUENCE_NUM in NUMBER
143 ) is
144 begin
145 delete from BNE_IMPORT_PROGRAMS
146 where APPLICATION_ID = X_APPLICATION_ID
147 and INTEGRATOR_CODE = X_INTEGRATOR_CODE
148 and SEQUENCE_NUM = X_SEQUENCE_NUM;
149
150 if (sql%notfound) then
151 raise no_data_found;
152 end if;
153
154 end DELETE_ROW;
155
156 procedure ADD_LANGUAGE
157 is
158 begin
159 null;
160 end ADD_LANGUAGE;
161
162
163 --------------------------------------------------------------------------------
164 -- PROCEDURE: LOAD_ROW --
165 -- --
166 -- DESCRIPTION: Load a row into the BNE_IMPORT_PROGRAMS entity. --
167 -- This proc is called from the apps loader. --
168 -- --
169 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
170 -- --
171 -- --
172 -- MODIFICATION HISTORY --
173 -- Date Username Description --
174 -- 28-May-04 DGROVES CREATED --
175 --------------------------------------------------------------------------------
176 procedure LOAD_ROW(
177 x_integrator_asn in VARCHAR2,
178 x_integrator_code in VARCHAR2,
179 x_sequence_num in VARCHAR2,
180 x_object_version_number in VARCHAR2,
181 x_parent_seq_num in VARCHAR2,
182 x_import_type in VARCHAR2,
183 x_import_param_list_asn in VARCHAR2,
184 x_import_param_code in VARCHAR2,
185 x_owner in VARCHAR2,
186 x_last_update_date in VARCHAR2,
187 x_custom_mode in VARCHAR2
188 )
189 is
190 l_app_id number;
191 l_import_list_app_id number;
192 l_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 begin
198 -- translate values to IDs
199 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_integrator_asn);
200 l_import_list_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_import_param_list_asn);
201
202 -- Translate owner to file_last_updated_by
203 f_luby := fnd_load_util.owner_id(x_owner);
204
205 -- Translate char last_update_date to date
206 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
207 begin
208 select LAST_UPDATED_BY, LAST_UPDATE_DATE
209 into db_luby, db_ludate
210 from BNE_IMPORT_PROGRAMS
211 where APPLICATION_ID = l_app_id
212 and INTEGRATOR_CODE = x_integrator_code
213 and SEQUENCE_NUM = x_sequence_num;
214
215 -- Test for customization and version
216 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
217 db_ludate, x_custom_mode)) then
218 -- Update existing row
219 BNE_IMPORT_PROGRAMS_PKG.Update_Row(
220 X_APPLICATION_ID => l_app_id,
221 X_INTEGRATOR_CODE => x_integrator_code,
222 X_SEQUENCE_NUM => x_sequence_num,
223 X_OBJECT_VERSION_NUMBER => x_object_version_number,
224 X_PARENT_SEQ_NUM => x_parent_seq_num,
225 X_IMPORT_TYPE => x_import_type,
226 X_IMPORT_PARAM_LIST_APP_ID => l_import_list_app_id,
227 X_IMPORT_PARAM_LIST_CODE => x_import_param_code,
228 X_LAST_UPDATE_DATE => f_ludate,
229 X_LAST_UPDATED_BY => f_luby,
230 X_LAST_UPDATE_LOGIN => 0
231 );
232 end if;
233 exception
234 when no_data_found then
235 -- Record doesn't exist - insert in all cases
236 BNE_IMPORT_PROGRAMS_PKG.Insert_Row(
237 X_ROWID => l_row_id,
238 X_APPLICATION_ID => l_app_id,
239 X_INTEGRATOR_CODE => x_integrator_code,
240 X_SEQUENCE_NUM => x_sequence_num,
241 X_OBJECT_VERSION_NUMBER => x_object_version_number,
242 X_PARENT_SEQ_NUM => x_parent_seq_num,
243 X_IMPORT_TYPE => x_import_type,
244 X_IMPORT_PARAM_LIST_APP_ID => l_import_list_app_id,
245 X_IMPORT_PARAM_LIST_CODE => x_import_param_code,
246 X_CREATION_DATE => f_ludate,
247 X_CREATED_BY => f_luby,
248 X_LAST_UPDATE_DATE => f_ludate,
249 X_LAST_UPDATED_BY => f_luby,
250 X_LAST_UPDATE_LOGIN => 0
251 );
252 end;
253 end LOAD_ROW;
254
255 end BNE_IMPORT_PROGRAMS_PKG;