[Home] [Help]
PACKAGE BODY: APPS.FND_LOADER_OPEN_INTERFACE_PKG
Source
1 package body FND_LOADER_OPEN_INTERFACE_PKG as
2 /* $Header: FNDOFACB.pls 120.1 2005/08/31 13:13:36 rsekaran noship $ */
3
4 --
5 -- Procedure
6 -- LOCK_BATCH
7 --
8 -- Purpose
9 -- Check to see if specific batch has been locked by other users, if not, try to lock it.
10 --
11 -- Arguments:
12 -- IN:
13 -- X_batch_id - Batch_id
14 -- OUT:
15 -- X_return_status:
16 -- 0: Success
17 -- 1: Timed out
18 -- 2: Deadlock
19 -- 3: Parameter error
20 -- 4: Do not own lock; cannot convert
21 -- 5: Illegal lockhandle
22 --
23 procedure lock_batch
24 (
25 X_batch_id IN INTEGER,
26 X_return_status OUT NOCOPY INTEGER
27 )
28 is
29 l_lockhandle VARCHAR2(128);
30 l_lock_request_status INTEGER;
31 begin
32 DBMS_LOCK.ALLOCATE_UNIQUE(to_char(X_batch_id),l_lockhandle);
33 X_return_status:=dbms_lock.request(lockhandle=>l_lockhandle,timeout=>1);
34 end;
35
36 --
37 -- Function
38 -- INSERT_BATCH
39 --
40 -- Purpose
41 -- Insert a batch of record into fnd_loader_open_interface table.
42 -- If all parameters are null, this function simply return a new batch_id.
43 --
44 -- Arguments:
45 -- IN:
46 -- X_LCT -- lct file name
47 -- X_LDT -- ldt file name
48 -- X_LOADER_MODE -- UPLOAD,DOWNLOAD,UPLOAD_PARTIAL
49 -- X_ENTITY -- Entity name
50 -- X_PARAMS -- optional parameters
51 --
52 FUNCTION INSERT_BATCH RETURN INTEGER is
53 l_next_seq INTEGER;
54 BEGIN
55 SELECT
56 FND_LOADER_OPEN_INTERFACE_S.nextval
57 INTO
58 l_next_seq
59 FROM
60 dual;
61 return l_next_seq;
62 END;
63
64
65 FUNCTION INSERT_BATCH
66 (
67 X_LCT IN FND_LCT_TAB,
68 X_LDT IN FND_LDT_TAB,
69 X_LOADER_MODE IN FND_LOADER_MODE_TAB,
70 X_ENTITY IN FND_ENTITY_TAB,
71 X_PARAMS IN FND_PARAMS_TAB
72 ) RETURN INTEGER is
73 l_next_seq INTEGER;
74 type l_seq_in_batch_TAB is table of NUMBER index by binary_integer;
75 l_seq_in_batch l_seq_in_batch_TAB;
76 indx integer;
77 BEGIN
78 SELECT
79 FND_LOADER_OPEN_INTERFACE_S.nextval
80 INTO
81 l_next_seq
82 FROM
83 dual;
84
85 for i in 1..X_lct.count loop
86 l_seq_in_batch(i):=i;
87 END loop;
88
89 BEGIN
90 FOR i IN 1..X_lct.count LOOP
91 indx := i; -- Store the index of the data being inserted, in case we need to print the exception message.
92 INSERT INTO FND_LOADER_OPEN_INTERFACE
93 (
94 batch_id,
95 seq_in_batch,
96 lct,
97 ldt,
98 loader_mode,
99 entity,params
100 )
101 VALUES
102 (
103 l_next_seq,
104 l_seq_in_batch(i),
105 X_LCT(i),
106 X_LDT(i),
107 X_LOADER_MODE(i),
108 X_ENTITY(i),
109 X_PARAMS(i)
110 );
111 END LOOP;
112 COMMIT;
113 EXCEPTION
114 WHEN OTHERS THEN
115 raise_application_error(-20101,'INSERT_BATCH : Failed to insert element ' || to_char(indx) || ' of the batch into the table FND_LOADER_OPEN_INTERFACE.',true);
116 end;
117 return l_next_seq;
118 END;
119
120 --
121 -- Procedure
122 -- ADD_ROW_TO_BATCH
123 --
124 -- Purpose
125 -- Add a list of records to fnd_loader_open_interface table with given batch_id
126 --
127 -- Arguments:
128 -- IN:
129 -- X_BATCH_ID -- Batch_id
130 -- X_LCT -- lct name
131 -- X_LDT -- ldt name
132 -- X_LOADER_MODE -- UPLOAD,DOWNLOAD,UPLOAD_PARTIAL
133 -- X_ENTITY -- Entity name
134 -- X_PARAMS -- options parameters.
135 --
136 PROCEDURE ADD_ROW_TO_BATCH
137 (
138 X_BATCH_ID IN INTEGER,
139 X_LCT IN FND_LCT_TAB,
140 X_LDT IN FND_LDT_TAB,
141 X_LOADER_MODE IN FND_LOADER_MODE_TAB,
142 X_ENTITY IN FND_ENTITY_TAB,
143 X_PARAMS IN FND_PARAMS_TAB
144 ) is
145 l_max_seq_in_batch INTEGER;
146 type l_seq_in_batch_TAB is table of NUMBER index by binary_integer;
147 l_seq_in_batch l_seq_in_batch_TAB;
148 indx integer;
149 BEGIN
150
151 SELECT
152 nvl(MAX(SEQ_IN_BATCH),0)
153 INTO
154 l_max_seq_in_batch
155 FROM
156 FND_LOADER_OPEN_INTERFACE
157 WHERE
158 BATCH_ID=X_BATCH_ID;
159
160 FOR i in 1..X_lct.count loop
161 l_seq_in_batch(i):=i+l_max_seq_in_batch;
162 END loop;
163
164
165 BEGIN
166 FOR i IN 1..X_lct.count LOOP
167 indx := i; -- Store the index of the data being inserted, in case we need to print the exception message.
168 INSERT INTO FND_LOADER_OPEN_INTERFACE
169 (
170 batch_id,
171 seq_in_batch,
172 lct,
173 ldt,
174 loader_mode,
175 entity,params
176 )
177 VALUES
178 (
179 X_BATCH_ID,
180 l_seq_in_batch(i),
181 X_LCT(i),
182 X_LDT(i),
183 X_LOADER_MODE(i),
184 X_ENTITY(i),
185 X_PARAMS(i)
186 );
187 END LOOP;
188 COMMIT;
189 EXCEPTION
190 WHEN OTHERS THEN
191 raise_application_error(-20201,'ADD_ROW_TO_BATCH : Failed to add element number ' || to_char(indx) || ' to the batch '|| to_char(X_BATCH_ID),true);
192 END;
193 END ADD_ROW_TO_BATCH;
194
195 --
196 -- Procedure
197 -- ADD_ROW_TO_BATCH
198 --
199 -- Purpose
200 -- Add a record to fnd_loader_open_interface table with given batch_id
201 --
202 -- Arguments:
203 -- IN:
204 -- X_BATCH_ID -- Batch_id
205 -- X_LCT -- lct name
206 -- X_LDT -- ldt name
207 -- X_LOADER_MODE -- UPLOAD,DOWNLOAD,UPLOAD_PARTIAL
208 -- X_ENTITY -- Entity name
209 -- X_PARAMS -- options parameters.
210 --
211 PROCEDURE ADD_ROW_TO_BATCH
212 (
213 X_BATCH_ID IN INTEGER,
214 X_LCT IN VARCHAR2,
215 X_LDT IN VARCHAR2,
216 X_LOADER_MODE IN VARCHAR2,
217 X_ENTITY IN VARCHAR2,
218 X_PARAMS IN VARCHAR2
219 ) is
220 l_max_seq_in_batch INTEGER;
221 BEGIN
222
223 SELECT
224 nvl(MAX(SEQ_IN_BATCH),0)
225 INTO
226 l_max_seq_in_batch
227 FROM
228 FND_LOADER_OPEN_INTERFACE
229 WHERE
230 BATCH_ID=X_BATCH_ID;
231
232 begin
233 INSERT INTO FND_LOADER_OPEN_INTERFACE
234 (
235 batch_id,
236 seq_in_batch,
237 lct,
238 ldt,
239 loader_mode,
240 entity,params
241 )
242 values
243 (
244 X_BATCH_ID,
245 l_max_seq_in_batch + 1,
246 X_LCT,
247 X_LDT,
248 X_LOADER_MODE,
249 X_ENTITY,
250 X_PARAMS
251 );
252 commit;
253 exception
254 when others then
255 raise_application_error(-20301,'ADD_ROW_TO_BATCH : Failed to add the job with lct file: ' || nvl(X_LCT,'NULL') || ' and ldt file : ' ||
256 nvl(X_LDT,'NULL') || ' to the batch ' || TO_CHAR(X_BATCH_ID) ,true);
257 end;
258 END ADD_ROW_TO_BATCH;
259
260 --
261 -- Procedure
262 -- DELETE_BATCH
263 --
264 -- Purpose
265 -- Purge record with specific batch_id from fnd_loader_open_interface table;
266 --
267 -- Arguments
268 -- IN:
269 -- X_BATCH_ID -- Batch_id
270 --
271 PROCEDURE DELETE_BATCH(
272 X_BATCH_ID IN INTEGER
273 ) IS
274 BEGIN
275 BEGIN
276 DELETE FROM
277 fnd_loader_open_interface
278 WHERE
279 batch_id=X_BATCH_ID;
280 commit;
281 EXCEPTION
282 WHEN OTHERS THEN
283 raise_application_error(-20401,'DELETE_BATCH : Failed to delete the batch : ' || to_char(X_BATCH_ID),true);
284 END;
285 END DELETE_BATCH;
286
287 END FND_LOADER_OPEN_INTERFACE_PKG;