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