[Home] [Help]
PACKAGE BODY: APPS.CSI_MASS_EDIT_PVT
Source
1 PACKAGE BODY CSI_MASS_EDIT_PVT as
2 /* $Header: csivmeeb.pls 120.10.12010000.2 2008/11/06 20:32:21 mashah ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSI_MASS_EDIT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivmeeb.pls';
6
7 /* local routine to wrap the gen utility debug stuff */
8
9 PROCEDURE debug(
10 p_message IN varchar2)
11 IS
12 BEGIN
13 csi_t_gen_utility_pvt.add(p_message);
14 END debug;
15
16
17 PROCEDURE CREATE_MASS_EDIT_BATCH
18 (
19 p_api_version IN NUMBER,
20 p_commit IN VARCHAR2 := fnd_api.g_false,
21 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
22 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
23 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
24 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
25 px_mass_edit_inst_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_inst_tbl,
26 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
27 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
28 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
29 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
30 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2
34
35 ) IS
36
37 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MASS_EDIT_PVT';
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(2000);
41 l_return_status VARCHAR2(1);
42
43 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
44 l_txn_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
45 l_txn_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
46 l_txn_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
47 l_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
48 l_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
49 l_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
50 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
51 err_indx PLS_INTEGER := 0;
52 l_mass_edit_error_tbl csi_mass_edit_pub.mass_edit_error_tbl;
53 l_error_message VARCHAR2(2000);
54 l_msg_index NUMBER;
55 l_internal_party_id NUMBER := NULL;
56
57
58 CURSOR instance_csr (p_ins_id IN NUMBER) IS
59 SELECT *
60 FROM csi_item_instances
61 WHERE instance_id = p_ins_id;
62
63 l_instance_csr instance_csr%ROWTYPE;
64
65 l_sub_type_id NUMBER;
66 l_instance_party_id NUMBER;
67 l_ip_account_id NUMBER;
68
69 tld_indx PLS_INTEGER := 0;
70 t_p_indx PLS_INTEGER := 0;
71 t_pa_indx PLS_INTEGER := 0;
72 inst_idx PLS_INTEGER := 0;
73 pty_idx PLS_INTEGER := 0;
74 ptyacc_idx PLS_INTEGER := 0;
75 l_source_txn_type_id NUMBER;
76 l_source_txn_table csi_t_transaction_lines.source_transaction_table%type;
77
78
79 Begin
80 -- Standard Start of API savepoint
81 SAVEPOINT CREATE_MASS_EDIT_BATCH_PVT;
82
83 -- Initialize message list if p_init_msg_list is set to TRUE.
84 IF FND_API.To_Boolean( p_init_msg_list ) THEN
85 FND_MSG_PUB.Initialize;
86 END IF;
87
88 -- Initialize API return status to succcess
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 -- Standard call to check for call compatibility.
92 IF NOT
93
94 FND_API.Compatible_API_Call (
95 p_current_version_number => l_api_version,
96 p_caller_version_number => p_api_version,
97 p_api_name => l_api_name,
98 p_pkg_name => g_pkg_name) THEN
99
100 RAISE FND_API.G_Exc_Unexpected_Error;
101
102 END IF;
103 -- main code starts here
104
105 g_entry_id := px_mass_edit_rec.entry_id;
106 g_batch_name := px_mass_edit_rec.name;
107
108 --validate the uniqueness of the batch name
109 validate_batch_name(p_batch_name => px_mass_edit_rec.name,
110 p_api_name => 'CREATE_MASS_EDIT_BATCH',
111 x_mass_edit_error_tbl => l_mass_edit_error_tbl);
112
113 -- Validate that the status of the batch is NOT Processed or Successful
114 IF nvl(px_mass_edit_rec.status_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
115 THEN
116 validate_batch_status(p_batch_id => px_mass_edit_rec.entry_id,
117 x_mass_edit_error_tbl => l_mass_edit_error_tbl);
118 ELSE
119 px_mass_edit_rec.status_code := 'CREATED'; -- Default, if not passed
120 END IF;
121
122 --validate batchtype
123 validate_batch_type(p_batch_type => px_mass_edit_rec.BATCH_TYPE,
124 p_api_name => 'CREATE_MASS_EDIT_BATCH',
125 x_sub_type_id => l_sub_type_id,
126 x_mass_edit_error_tbl => l_mass_edit_error_tbl);
127
128 debug('Sub_type_id= '||l_sub_type_id);
129 debug('px_mass_edit_inst_tbl count: '||px_mass_edit_inst_tbl.count);
130
131 csi_t_gen_utility_pvt.dump_txn_line_rec(
132 p_txn_line_rec => px_txn_line_rec);
133
134 l_source_txn_type_id := px_txn_line_rec.source_transaction_type_id;
135 l_source_txn_table := px_txn_line_rec.source_transaction_table;
136
137 --check if all the txn_line_detail recs have a instance id
138 IF px_mass_edit_inst_tbl.count > 0 THEN
139 FOR i IN px_mass_edit_inst_tbl.FIRST .. px_mass_edit_inst_tbl.LAST
140 LOOP
141 IF NVL(px_mass_edit_inst_tbl(i).instance_id , fnd_api.g_miss_num)
142 = fnd_api.g_miss_num
143 THEN
144 FND_MESSAGE.set_name('CSI','CSI_MU_MISSING_INSTANCE');
145 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name) ;
146 FND_MESSAGE.set_token('TXN_LINE_DETAIL_ID',px_mass_edit_inst_tbl(i).txn_line_detail_id) ;
147 log_mu_error
148 (
149 p_index => 1,
150 p_instance_id => px_mass_edit_inst_tbl(i).instance_id,
151 p_txn_line_detail_id => px_mass_edit_inst_tbl(i).txn_line_detail_id,
152 p_error_code => fnd_api.g_ret_sts_error,
153 x_mass_edit_error_tbl => l_mass_edit_error_tbl
154 );
155 RAISE FND_API.g_exc_error;
156 END IF ;
157 END LOOP ;
158 END IF;
159
160 csi_t_gen_utility_pvt.dump_mass_edit_rec(px_mass_edit_rec);
161
162 -- Insert row in CSI_MASS_EDIT_ENTRIES table and CSI_T_TRANSACITON_LINES AND CSI_T_TRANSACTION_LINE_DETAILS
163
164 CSI_MASS_EDIT_ENTRIES_B_PKG.Insert_Row(
165 px_ENTRY_ID => px_mass_edit_rec.ENTRY_ID,
166 px_TXN_LINE_ID => px_mass_edit_rec.TXN_LINE_ID,
167 px_TXN_LINE_DETAIL_ID => px_mass_edit_rec.TXN_LINE_DETAIL_ID,
168 p_STATUS_CODE => px_mass_edit_rec.STATUS_CODE,
169 p_SCHEDULE_DATE => px_mass_edit_rec.SCHEDULE_DATE,
170 p_START_DATE => px_mass_edit_rec.START_DATE,
171 p_END_DATE => px_mass_edit_rec.END_DATE,
172 p_NAME => px_mass_edit_rec.NAME,
173 p_BATCH_TYPE => px_mass_edit_rec.BATCH_TYPE,
174 p_DESCRIPTION => px_mass_edit_rec.DESCRIPTION,
175 p_CREATED_BY => csi_mass_edit_pub.g_user_id,
176 p_CREATION_DATE => sysdate,
177 p_LAST_UPDATED_BY => csi_mass_edit_pub.g_user_id,
178 p_LAST_UPDATE_DATE => sysdate,
179 p_LAST_UPDATE_LOGIN => csi_mass_edit_pub.g_login_id,
180 p_OBJECT_VERSION_NUMBER => 1.0,
181 p_SYSTEM_CASCADE => px_mass_edit_rec.SYSTEM_CASCADE
182 );
183
184
185 debug('Mass edit entry id: '||px_mass_edit_rec.ENTRY_ID);
186 debug('Transaction line id: '||px_mass_edit_rec.TXN_LINE_ID);
187 debug('Transaction line detail id: '||px_mass_edit_rec.TXN_LINE_DETAIL_ID);
188
189 -- query all instances from the px_mass_edit_inst_tbl into the px_txn_line_detail_tbl
190 IF px_mass_edit_inst_tbl.count > 0 THEN
191 -- Validate the batch first
192 csi_mass_edit_pvt.validate_batch (px_mass_edit_rec,
193 'CRT', -- Create
194 l_mass_edit_error_tbl,
195 l_return_status);
196
197 IF (l_mass_edit_error_tbl.count = 0 OR
198 l_return_status = 'W') THEN
199 -- Build the data and call the Update transaction details API
200
201 FOR inst_idx IN px_mass_edit_inst_tbl.FIRST .. px_mass_edit_inst_tbl.LAST
202 LOOP
203 debug('Instance ID('||inst_idx||'): '||px_mass_edit_inst_tbl(inst_idx).instance_id
204 ||' Active End date: '||px_mass_edit_inst_tbl(inst_idx).active_end_date);
205 OPEN instance_csr (px_mass_edit_inst_tbl(inst_idx).instance_id);
206 FETCH instance_csr INTO l_instance_csr;
207 IF instance_csr%NOTFOUND Then
208 CLOSE instance_csr;
209 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_INSTANCE');
210 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name) ;
211 FND_MESSAGE.set_token('INSTANCE_ID',px_mass_edit_inst_tbl(inst_idx).instance_id) ;
212 log_mu_error
213 (
214 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
215 p_instance_id => px_mass_edit_inst_tbl(inst_idx).instance_id,
216 p_txn_line_detail_id => null,
217 p_error_code => fnd_api.g_ret_sts_error,
218 x_mass_edit_error_tbl => l_mass_edit_error_tbl
219 );
220 Raise fnd_api.g_exc_error;
221 End If;
222 CLOSE instance_csr;
223
224 --add the txn_line_id to all records in the detail rec and default the instance_exists flag to 'Y'
225 l_txn_line_detail_tbl(inst_idx).transaction_line_id := px_mass_edit_rec.TXN_LINE_ID;
226 l_txn_line_detail_tbl(inst_idx).instance_exists_flag := 'Y';
227 l_txn_line_detail_tbl(inst_idx).source_transaction_flag := 'Y';
228 l_txn_line_detail_tbl(inst_idx).sub_type_id := l_sub_type_id;
229 l_txn_line_detail_tbl(inst_idx).instance_id := l_instance_csr.instance_id;
230 l_txn_line_detail_tbl(inst_idx).inventory_item_id := l_instance_csr.inventory_item_id;
231 l_txn_line_detail_tbl(inst_idx).inv_organization_id := l_instance_csr.last_vld_organization_id;
232 l_txn_line_detail_tbl(inst_idx).quantity := l_instance_csr.quantity;
233 l_txn_line_detail_tbl(inst_idx).unit_of_measure := l_instance_csr.unit_of_measure;
234 l_txn_line_detail_tbl(inst_idx).csi_system_id := l_instance_csr.system_id;
235 l_txn_line_detail_tbl(inst_idx).location_type_code := l_instance_csr.location_type_code;
236 l_txn_line_detail_tbl(inst_idx).location_id := l_instance_csr.location_id;
237 l_txn_line_detail_tbl(inst_idx).install_location_type_code := l_instance_csr.install_location_type_code;
238 l_txn_line_detail_tbl(inst_idx).install_location_id := l_instance_csr.install_location_id;
239 l_txn_line_detail_tbl(inst_idx).installation_date := l_instance_csr.install_date;
240 l_txn_line_detail_tbl(inst_idx).active_end_date := l_instance_csr.active_end_date;
241 l_txn_line_detail_tbl(inst_idx).external_reference := l_instance_csr.external_reference;
242 l_txn_line_detail_tbl(inst_idx).instance_status_id := l_instance_csr.instance_status_id;
243 l_txn_line_detail_tbl(inst_idx).serial_number := l_instance_csr.serial_number;
244 l_txn_line_detail_tbl(inst_idx).lot_number := l_instance_csr.lot_number;
245
246 -- Populate the Install Parameters Record
247 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
248 csi_gen_utility_pvt.populate_install_param_rec;
249 END IF;
250
251 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
252
253 -- store the current owner party and account information for all the instances in the batch
254 --query instance_party_id
255 Begin
256 SELECT INSTANCE_PARTY_ID
257 INTO l_instance_party_id
258 FROM CSI_I_PARTIES
259 WHERE INSTANCE_ID = l_instance_csr.instance_id and
260 PARTY_ID = l_instance_csr.owner_party_id and
261 PARTY_SOURCE_TABLE = l_instance_csr.owner_party_source_table and
262 RELATIONSHIP_TYPE_CODE = 'OWNER';
263
264 l_txn_party_detail_tbl(pty_idx).instance_party_id := l_instance_party_id;
265 l_txn_party_detail_tbl(pty_idx).party_source_id := l_instance_csr.owner_party_id;
266 l_txn_party_detail_tbl(pty_idx).party_source_table := l_instance_csr.owner_party_source_table;
267 l_txn_party_detail_tbl(pty_idx).relationship_type_code := 'OWNER';
268 l_txn_party_detail_tbl(pty_idx).contact_flag := 'N';
269 l_txn_party_detail_tbl(pty_idx).txn_line_details_index := inst_idx;
270
271 debug('Checking to see if the Owner Party is an Internal Party from CSI_INSTALL_PARAMETERS');
272 debug('Owner Party ID = '||l_instance_csr.owner_party_id);
273 debug('Internal Party ID = '||l_internal_party_id);
274
275 --query ip_account_id only if the source is HZ
276 IF nvl(l_internal_party_id,99999) <> l_instance_csr.owner_party_id THEN
277
278 IF (l_instance_csr.owner_party_source_table = 'HZ_PARTIES') THEN
279 SELECT IP_ACCOUNT_ID
280 INTO l_ip_account_id
281 FROM CSI_IP_ACCOUNTS
282 WHERE INSTANCE_PARTY_ID = l_instance_party_id AND
283 RELATIONSHIP_TYPE_CODE = 'OWNER' AND
284 PARTY_ACCOUNT_ID = l_instance_csr.owner_party_account_id;
285
286 l_txn_pty_acct_detail_tbl(ptyacc_idx).account_id := l_instance_csr.owner_party_account_id;
287 l_txn_pty_acct_detail_tbl(ptyacc_idx).ip_account_id := l_ip_account_id;
288 l_txn_pty_acct_detail_tbl(ptyacc_idx).relationship_type_code := 'OWNER';
289 l_txn_pty_acct_detail_tbl(ptyacc_idx).txn_party_details_index := pty_idx;
290 ptyacc_idx := ptyacc_idx + 1;
291 END IF;
292 END IF; -- Check for Internal Party ID
293 Exception
294 When No_data_found Then
295 -- there has to be only one record here else an exception
296 FND_MESSAGE.set_name('CSI','CSI_INT_INST_OWNER_MISSING');
297 FND_MESSAGE.set_token('INSTANCE_ID',l_instance_csr.instance_id);
298 log_mu_error
299 (
300 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
301 p_instance_id => l_instance_csr.instance_id,
302 p_txn_line_detail_id => null,
303 p_error_code => fnd_api.g_ret_sts_error,
304 x_mass_edit_error_tbl => l_mass_edit_error_tbl
305 );
306 RAISE fnd_api.g_exc_error;
307 When too_many_rows Then
308 FND_MESSAGE.set_name('CSI','CSI_MANY_INST_OWNER_FOUND');
309 FND_MESSAGE.set_token('INSTANCE_ID',l_instance_csr.instance_id);
310 log_mu_error
311 (
312 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
313 p_instance_id => l_instance_csr.instance_id,
314 p_txn_line_detail_id => null,
315 p_error_code => fnd_api.g_ret_sts_error,
316 x_mass_edit_error_tbl => l_mass_edit_error_tbl
317 );
318 RAISE fnd_api.g_exc_error;
319 When others Then
320 FND_MESSAGE.set_name('CSI','CSI_API_OWNER_OTHERS_EXCEPTION');
321 log_mu_error
322 (
323 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
324 p_instance_id => l_instance_csr.instance_id,
325 p_txn_line_detail_id => null,
326 p_error_code => fnd_api.g_ret_sts_error,
327 x_mass_edit_error_tbl => l_mass_edit_error_tbl
328 );
329 RAISE fnd_api.g_exc_unexpected_error;
330 End;
331
332 pty_idx := pty_idx +1;
333
334 END LOOP ;
335 --assigning the transaction line values from the Mass edit table handler call
336 l_txn_line_rec.transaction_line_id := px_mass_edit_rec.txn_line_id;
337 l_txn_line_rec.source_transaction_id := px_mass_edit_rec.ENTRY_ID;
338 l_txn_line_rec.object_version_number := 1.0;
339
340 IF ( ( nvl(l_source_txn_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
341 AND (nvl(l_source_txn_table,fnd_api.g_miss_char) = fnd_api.g_miss_char ))
342 THEN
343 --Default these values for mass update transactions
344 l_txn_line_rec.source_transaction_type_id := '3'; -- SOURCE_TRANSACTION_TYPE_ID for MASS_EDIT is 3
345 l_txn_line_rec.source_transaction_table := 'CSI_MASS_EDIT_ENTRIES';
346 ELSE -- source_txn_type_id is not null. external caller
347 l_txn_line_rec.source_transaction_type_id := px_txn_line_rec.source_transaction_type_id;
348 l_txn_line_rec.source_transaction_table := px_txn_line_rec.source_transaction_table;
349 END IF;
350
351 tld_indx := nvl(l_txn_line_detail_tbl.LAST,0) + 1;
352 l_txn_line_detail_tbl(tld_indx).txn_line_detail_id := px_mass_edit_rec.txn_line_detail_id;
353 l_txn_line_detail_tbl(tld_indx).transaction_line_id := px_mass_edit_rec.txn_line_id;
354 l_txn_line_detail_tbl(tld_indx).object_version_number := 1.0;
355 -- Just loop through the child tables passed by the caller to reassign the txn_line_details_index on them
356 IF px_txn_party_detail_tbl.count > 0 THEN
357 t_p_indx := nvl(l_txn_party_detail_tbl.LAST,0) + 1;
358 For pc_ind in px_txn_party_detail_tbl.FIRST .. px_txn_party_detail_tbl.LAST LOOP
359 -- since the child tables are always for the dummy line detail and carry the new values, we assign the ID
360 px_txn_party_detail_tbl(pc_ind).txn_line_detail_id := px_mass_edit_rec.txn_line_detail_id;
361 l_txn_party_detail_tbl(t_p_indx) := px_txn_party_detail_tbl(pc_ind);
362
363 -- Transaction Party account details table
364 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
365 t_pa_indx := nvl(l_txn_pty_acct_detail_tbl.LAST,0) + 1;
366 FOR pac_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
367 LOOP
368 IF px_txn_pty_acct_detail_tbl(pac_ind).txn_party_details_index = pc_ind THEN
369 px_txn_pty_acct_detail_tbl(pac_ind).txn_party_details_index := t_p_indx;
370 l_txn_pty_acct_detail_tbl(t_pa_indx) := px_txn_pty_acct_detail_tbl(pac_ind);
371 t_pa_indx := t_pa_indx + 1;
372 END IF;
373 END LOOP;
374 END IF;
375
376 -- Resetting the Transaction Party Contacts table
377 FOR con_ind IN px_txn_party_detail_tbl.FIRST .. px_txn_party_detail_tbl.LAST
378 LOOP
379 IF nvl(px_txn_party_detail_tbl(con_ind).contact_flag,fnd_api.g_miss_char) = 'Y' THEN
380 IF px_txn_party_detail_tbl(con_ind).contact_party_id = pc_ind THEN
381 px_txn_party_detail_tbl(con_ind).contact_party_id := t_p_indx;
382 END IF;
383 END IF;
384 END LOOP;
385
386 t_p_indx := t_p_indx + 1;
387
388 End Loop;
389 END IF;
390
391 IF px_txn_ext_attrib_vals_tbl.count > 0 THEN
392 For ea in px_txn_ext_attrib_vals_tbl.FIRST .. px_txn_ext_attrib_vals_tbl.LAST LOOP
393 -- since the child tables are always for the dummy line detail and carry the new values, we just reassign it
394 l_txn_ext_attrib_vals_tbl(ea).txn_line_detail_id := px_mass_edit_rec.txn_line_detail_id;
395 End Loop;
396 END IF;
397 -- right now we do not process org assignments, txn systems, relationships
398
399 csi_t_txn_details_grp.update_transaction_dtls(
400 p_api_version => p_api_version,
401 p_commit => fnd_api.g_false,
402 p_init_msg_list => p_init_msg_list,
403 p_validation_level => p_validation_level,
404 p_txn_line_rec => l_txn_line_rec,
405 px_txn_line_detail_tbl => l_txn_line_detail_tbl,
406 px_txn_ii_rltns_tbl => l_txn_ii_rltns_tbl,
407 px_txn_party_detail_tbl => l_txn_party_detail_tbl,
408 px_txn_pty_acct_detail_tbl => l_txn_pty_acct_detail_tbl,
409 px_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
410 px_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_vals_tbl,
411 x_return_status => l_return_status,
412 x_msg_count => l_msg_count,
413 x_msg_data => l_msg_data);
414
415 IF l_return_status <> fnd_api.g_ret_sts_success THEN
416 l_msg_index := 1;
417 WHILE l_msg_count > 0 loop
418
419 -- Set Error Table Index
420 err_indx := nvl(l_mass_edit_error_tbl.last,0) + 1;
421
422 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
423 l_mass_edit_error_tbl(err_indx).instance_id := NULL;
424 l_mass_edit_error_tbl(err_indx).entry_id := g_entry_id;
425 l_mass_edit_error_tbl(err_indx).name := g_batch_name;
426 l_mass_edit_error_tbl(err_indx).txn_line_detail_id := NULL;
427 l_mass_edit_error_tbl(err_indx).error_text := l_error_message;
428 l_mass_edit_error_tbl(err_indx).error_code := fnd_api.g_ret_sts_error;
429 l_msg_index := l_msg_index + 1;
430 l_msg_count := l_msg_count - 1;
431 END LOOP;
432 RAISE fnd_api.g_exc_error;
433 ELSE
434 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
435 END IF;
436 ELSE -- validate batch has errors
437 debug('Validate Batch Failed. Pl. check and fix the Errors.');
438 RAISE fnd_api.g_exc_error;
439 END IF; -- check for batch validate
440 END IF; -- px_mass_edit_inst_tbl count > 0
441
442 Debug('CREATE_MASS_EDIT_BATCH API Successfully completed');
443 -- Standard call to get message count and IF count is get message info.
444 FND_MSG_PUB.Count_And_Get
445 (p_count => x_msg_count,
446 p_data => x_msg_data
447 );
448
449 EXCEPTION
450 WHEN FND_API.G_EXC_ERROR THEN
451 ROLLBACK TO CREATE_MASS_EDIT_BATCH_PVT;
452 x_return_status := FND_API.G_RET_STS_ERROR ;
453 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
454 FND_MSG_PUB.Count_And_Get
455 (p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
460 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
461 ROLLBACK TO CREATE_MASS_EDIT_BATCH_PVT;
462 FND_MSG_PUB.Count_And_Get
463 ( p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466 WHEN OTHERS THEN
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
468 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
469 ROLLBACK TO CREATE_MASS_EDIT_BATCH_PVT;
470 IF FND_MSG_PUB.Check_Msg_Level
471 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
472 THEN
473 FND_MSG_PUB.Add_Exc_Msg
474 (G_PKG_NAME ,
475 l_api_name
476 );
477 END IF;
478 FND_MSG_PUB.Count_And_Get
479 (p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482
483 End CREATE_MASS_EDIT_BATCH;
484
485 -- validation_routines
486 --validate the mass update batch ID/name
487 FUNCTION Is_valid_batch (
488 p_batch_name IN VARCHAR2,
489 p_batch_id IN NUMBER,
490 x_mass_edit_rec OUT NOCOPY csi_mass_edit_pub.mass_edit_rec)
491 RETURN BOOLEAN
492 IS
493
494 l_mass_edit_rec csi_mass_edit_entries_vl%rowtype;
495 l_dup_batch_name NUMBER := NULL;
496
497 CURSOR dup_batch_name (p_batch_id IN NUMBER, p_batch_name IN VARCHAR2) IS
498 SELECT 1
499 FROM csi_mass_edit_entries_tl
500 WHERE entry_id <> p_batch_id
501 AND name = p_batch_name;
502
503 BEGIN
504 debug('Batch Name: '||p_batch_name||' Batch ID: '||p_batch_id);
505
506 IF nvl(p_batch_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
507
508 SELECT *
509 INTO l_mass_edit_rec
510 FROM csi_mass_edit_entries_vl
511 WHERE entry_id = p_batch_id;
512
513 -- to validate duplicate batch name in an update scenaio...
514
515 IF nvl(p_batch_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
516 IF p_batch_name <> l_mass_edit_rec.name THEN
517
518 OPEN dup_batch_name (p_batch_id,p_batch_name);
519 FETCH dup_batch_name INTO l_dup_batch_name;
520 CLOSE dup_batch_name;
521
522 IF l_dup_batch_name IS NOT NULL THEN
523 debug('Duplicate Batch Name: '||p_batch_name||' Batch ID: '||p_batch_id);
524 FND_MESSAGE.set_name('CSI','CSI_MU_DUPLICATE_BATCH_NAME');
525 FND_MESSAGE.set_token('BATCH_NAME',p_batch_name) ;
526 Return FALSE;
527 END IF;
528
529 END IF;
530 END IF;
531
532 ELSIF nvl(p_batch_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
533
534 SELECT entry_id
535 ,name
536 ,txn_line_id
537 ,batch_type
538 ,status_code
539 ,schedule_date
540 INTO l_mass_edit_rec.entry_id
541 ,l_mass_edit_rec.name
542 ,l_mass_edit_rec.txn_line_id
543 ,l_mass_edit_rec.batch_type
544 ,l_mass_edit_rec.status_code
545 ,l_mass_edit_rec.schedule_date
546 FROM csi_mass_edit_entries_vl
547 WHERE name = p_batch_name;
548
549 END IF;
550
551 x_mass_edit_rec.entry_id := l_mass_edit_rec.entry_id;
552 x_mass_edit_rec.name := l_mass_edit_rec.name;
553 x_mass_edit_rec.txn_line_id := l_mass_edit_rec.txn_line_id;
554 x_mass_edit_rec.batch_type := l_mass_edit_rec.batch_type ;
555 x_mass_edit_rec.status_code := l_mass_edit_rec.status_code;
556 x_mass_edit_rec.schedule_date := l_mass_edit_rec.schedule_date;
557
558 Select txn_line_detail_id
559 Into x_mass_edit_rec.txn_line_detail_id
560 From csi_t_txn_line_details
561 Where transaction_line_id = l_mass_edit_rec.txn_line_id
562 And instance_id is null; -- there can be ONLY one record with no instance ID(dummy...)
563
564 Return TRUE;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 Return FALSE;
569 END Is_valid_batch;
570
571 --validate the uniqueness of the batch name
572 PROCEDURE validate_batch_name(
573 p_batch_name IN VARCHAR2,
574 p_api_name IN VARCHAR2,
575 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl)
576 IS
577 l_found VARCHAR2(1) := null;
578 BEGIN
579 debug('Validating batch name');
580 csi_t_vldn_routines_pvt.check_reqd_param(
581 p_value => p_batch_name,
582 p_param_name => 'px_mass_edit_rec.name',
583 p_api_name => p_api_name);
584
585 BEGIN
586 SELECT 'X' INTO l_found
587 FROM csi_mass_edit_entries_vl
588 WHERE name = p_batch_name;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592 l_found :=null;
593 END;
594
595 If(l_found is not null) THEN
596 debug('Duplicate batch name');
597 Raise fnd_api.g_exc_error;
598 END IF;
599
600 EXCEPTION
601 WHEN fnd_api.g_exc_error THEN
602 FND_MESSAGE.set_name('CSI','CSI_MU_DUPLICATE_BATCH_NAME');
603 FND_MESSAGE.set_token('BATCH_NAME',p_batch_name) ;
604 log_mu_error
605 (
606 p_index => 1,
607 p_instance_id => null,
608 p_txn_line_detail_id => null,
609 p_error_code => fnd_api.g_ret_sts_error,
610 x_mass_edit_error_tbl => x_mass_edit_error_tbl
611 );
612 Raise;
613 WHEN OTHERS THEN
614 fnd_message.set_name('CSI','CSI_INT_UNEXP_SQL_ERROR');
615 fnd_message.set_token('SQL_ERROR',SQLERRM);
616 log_mu_error
617 (
618 p_index => 1,
619 p_instance_id => null,
620 p_txn_line_detail_id => null,
621 p_error_code => fnd_api.g_ret_sts_error,
622 x_mass_edit_error_tbl => x_mass_edit_error_tbl
623 );
624 Raise;
625 END validate_batch_name;
626
627 --validate batchtype
628 PROCEDURE validate_batch_type(
629 p_batch_type IN VARCHAR2,
630 p_api_name IN VARCHAR2,
631 x_sub_type_id OUT NOCOPY NUMBER ,
632 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl)
633 IS
634
635 l_sub_type_id NUMBER := -1;
636
637 BEGIN
638 debug('Validating batch type ');
639 csi_t_vldn_routines_pvt.check_reqd_param(
640 p_value => p_batch_type,
641 p_param_name => 'px_mass_edit_rec.BATCH_TYPE',
642 p_api_name => p_api_name);
643
644 SELECT sub_type_id
645 INTO l_sub_type_id
646 FROM CSI_TXN_SUB_TYPES
647 WHERE transaction_type_id = 3
648 AND IB_TXN_TYPE_CODE = p_batch_type;
649
650 x_sub_type_id := l_sub_type_id;
651
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_TYPE');
655 FND_MESSAGE.set_token('BATCH_TYPE',p_batch_type) ;
656 log_mu_error
657 (
658 p_index => 1,
659 p_instance_id => null,
660 p_txn_line_detail_id => null,
661 p_error_code => fnd_api.g_ret_sts_error,
662 x_mass_edit_error_tbl => x_mass_edit_error_tbl
663 );
664 Raise;
665 WHEN OTHERS THEN
666 fnd_message.set_name('CSI','CSI_INT_UNEXP_SQL_ERROR');
667 fnd_message.set_token('SQL_ERROR',SQLERRM);
668 log_mu_error
669 (
670 p_index => 1,
671 p_instance_id => null,
672 p_txn_line_detail_id => null,
673 p_error_code => fnd_api.g_ret_sts_error,
674 x_mass_edit_error_tbl => x_mass_edit_error_tbl
675 );
676 Raise;
677 END validate_batch_type;
678
679 PROCEDURE validate_batch_status(
680 p_batch_id IN NUMBER,
681 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl)
682 IS
683
684 l_status csi_mass_edit_entries_b.status_code%type := null;
685
686 BEGIN
687 debug('Validating batch status');
688
689 BEGIN
690
691 SELECT status_code
692 INTO l_status
693 FROM csi_mass_edit_entries_b cmee,
694 csi_lookups cl
695 WHERE cmee.entry_id = p_batch_id
696 AND cmee.status_code = cl.lookup_code
697 AND cl.lookup_type = 'CSI_MU_BATCH_STATUSES';
698
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN
701 l_status :=null;
702 Raise fnd_api.g_exc_unexpected_error;
703 END;
704
705 If l_status in ('SUCCESSFUL','PROCESSING') THEN
706 debug('Invalid Status for this batch - Cannot be Successful or Processed');
707 Raise fnd_api.g_exc_error;
708 END IF;
709
710 EXCEPTION
711 WHEN fnd_api.g_exc_error THEN
712 FND_MESSAGE.set_name('CSI','CSI_MU_BATCH_UPD_DISALLOWED');
713 FND_MESSAGE.set_token('BATCH_STATUS',l_status) ;
714 FND_MESSAGE.set_token('BATCH_NAME',g_batch_name) ;
715 log_mu_error
716 (
717 p_index => 1,
718 p_instance_id => null,
719 p_txn_line_detail_id => null,
720 p_error_code => fnd_api.g_ret_sts_error,
721 x_mass_edit_error_tbl => x_mass_edit_error_tbl
722 );
723 Raise;
724 WHEN fnd_api.g_exc_unexpected_error THEN
725 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_STATUS');
726 FND_MESSAGE.set_token('BATCH_STATUS',l_status) ;
727 FND_MESSAGE.set_token('BATCH_NAME',g_batch_name) ;
728 log_mu_error
729 (
730 p_index => 1,
731 p_instance_id => null,
732 p_txn_line_detail_id => null,
733 p_error_code => fnd_api.g_ret_sts_error,
734 x_mass_edit_error_tbl => x_mass_edit_error_tbl
735 );
736 Raise;
737 WHEN OTHERS THEN
738 fnd_message.set_name('CSI','CSI_INT_UNEXP_SQL_ERROR');
739 fnd_message.set_token('SQL_ERROR',SQLERRM);
740 log_mu_error
741 (
742 p_index => 1,
743 p_instance_id => null,
744 p_txn_line_detail_id => null,
745 p_error_code => fnd_api.g_ret_sts_error,
746 x_mass_edit_error_tbl => x_mass_edit_error_tbl
747 );
748 Raise;
749 END validate_batch_status;
750
751 PROCEDURE log_mu_error (
752 p_index IN NUMBER,
753 p_instance_id IN NUMBER,
754 p_txn_line_detail_id IN NUMBER,
755 p_error_code IN VARCHAR2,
756 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl)
757 IS
758 BEGIN
759 debug('logging an error in Mass edit error table');
760 x_mass_edit_error_tbl(p_index).entry_id := g_entry_id;
761 x_mass_edit_error_tbl(p_index).name := g_batch_name;
762 x_mass_edit_error_tbl(p_index).txn_line_detail_id := p_txn_line_detail_id;
763 x_mass_edit_error_tbl(p_index).instance_id := p_instance_id;
764 x_mass_edit_error_tbl(p_index).error_code := p_error_code;
765 x_mass_edit_error_tbl(p_index).error_text := fnd_message.get;
766 END log_mu_error;
767
768
769 PROCEDURE UPDATE_MASS_EDIT_BATCH (
770 p_api_version IN NUMBER,
771 p_commit IN VARCHAR2 := fnd_api.g_false,
772 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
773 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
774 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
775 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
776 px_mass_edit_inst_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_inst_tbl,
777 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
778 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
779 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
780 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
781 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl,
782 x_return_status OUT NOCOPY VARCHAR2,
783 x_msg_count OUT NOCOPY NUMBER,
784 x_msg_data OUT NOCOPY VARCHAR2) IS
785
786 l_api_version NUMBER := 1.0;
787 l_api_name VARCHAR2(30) := 'UPDATE_MASS_EDIT_BATCH_PVT';
788 l_msg_count NUMBER;
789 l_msg_data VARCHAR2(200);
790 l_return_status VARCHAR2(1);
791 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
792 l_txn_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
793 l_txn_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
794 l_txn_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
795 l_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
796 l_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
797 l_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
798 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
799 l_mass_edit_rec csi_mass_edit_pub.mass_edit_rec;
800 err_indx PLS_INTEGER := 0;
801 tld_idx PLS_INTEGER := 0;
802 inst_idx PLS_INTEGER := 0;
803 pty_idx PLS_INTEGER := 0;
804 ptyacc_idx PLS_INTEGER := 0;
805
806 l_mass_edit_error_tbl csi_mass_edit_pub.mass_edit_error_tbl;
807 l_error_message VARCHAR2(2000);
808 l_msg_index NUMBER;
809 l_sub_type_id NUMBER;
810 l_create_instance_tdls VARCHAR2(1) := 'N';
811 l_instance_party_id NUMBER;
812 l_ip_account_id NUMBER;
813 l_invalid_operation VARCHAR2(1) := 'N';
814 l_internal_party_id NUMBER := NULL;
815
816 CURSOR instance_csr (p_ins_id IN NUMBER) IS
817 SELECT *
818 FROM csi_item_instances
819 WHERE instance_id = p_ins_id;
820
821 l_instance_csr instance_csr%ROWTYPE;
822
823 BEGIN
824 SAVEPOINT UPDATE_MASS_EDIT_PVT;
825
826 -- Initialize message list if p_init_msg_list is set to TRUE.
827 IF FND_API.To_Boolean( p_init_msg_list ) THEN
828 FND_MSG_PUB.Initialize;
829 END IF;
830
831 -- Initialize API return status to succcess
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833
834 -- Standard call to check for call compatibility.
835 IF NOT FND_API.Compatible_API_Call (
836 p_current_version_number => l_api_version,
837 p_caller_version_number => p_api_version,
838 p_api_name => l_api_name,
839 p_pkg_name => g_pkg_name) THEN
840
841 RAISE FND_API.G_Exc_Unexpected_Error;
842 END IF;
843
844 -- Check required parameters
845 csi_t_vldn_routines_pvt.check_reqd_param(
846 p_value => px_mass_edit_rec.entry_id,
847 p_param_name => 'px_mass_edit_rec.entry_id',
848 p_api_name => l_api_name);
849
850 g_entry_id := px_mass_edit_rec.entry_id;
851 g_batch_name := px_mass_edit_rec.name;
852
853 -- Assign the mass edit rec to a local variable so it can be passed to the update row
854 l_mass_edit_rec := px_mass_edit_rec;
855
856 IF ( (nvl(px_mass_edit_rec.entry_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num)
857 OR ( nvl(px_mass_edit_rec.name, fnd_api.g_miss_char) <> fnd_api.g_miss_char )) THEN
858
859 IF NOT Is_valid_batch (p_batch_name => g_batch_name,
860 p_batch_id => g_entry_id,
861 x_mass_edit_rec => px_mass_edit_rec
862 )
863 THEN
864 IF nvl(px_mass_edit_rec.entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
865 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_NAME');
866 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name);
867 ELSE
868 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_ID');
869 FND_MESSAGE.set_token('BATCH_ID',px_mass_edit_rec.entry_id);
870 END IF;
871
872 log_mu_error
873 (
874 p_index => 1,
875 p_instance_id => null,
876 p_txn_line_detail_id => null,
877 p_error_code => fnd_api.g_ret_sts_error,
878 x_mass_edit_error_tbl => l_mass_edit_error_tbl
879 );
880 Raise fnd_api.g_exc_error;
881 END IF;
882 END IF;
883
884 -- Check to see if there is an instance id on the detail record if so fail
885
886 IF nvl(px_txn_line_detail_rec.txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
887 IF nvl(px_txn_line_detail_rec.instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
888 -- trying to provide an instance on the dummy line detail rec for mass update
889 l_invalid_operation := 'Y';
890 FND_MESSAGE.SET_NAME('CSI','CSI_MU_BATCH_INST_ON_DTL_REC');
891 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',px_txn_line_detail_rec.instance_id);
892 FND_MESSAGE.SET_TOKEN('TXN_LINE_DETAIL_ID',px_txn_line_detail_rec.txn_line_detail_id);
893 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name);
894 ELSIF px_txn_line_detail_rec.txn_line_detail_id <> px_mass_edit_rec.txn_line_detail_id THEN
895 l_invalid_operation := 'Y';
896 FND_MESSAGE.SET_NAME('CSI','CSI_MU_BATCH_INVALID_DATA');
897 FND_MESSAGE.SET_TOKEN('TXN_LINE_DETAIL_ID1',px_mass_edit_rec.txn_line_detail_id);
898 FND_MESSAGE.SET_TOKEN('TXN_LINE_DETAIL_ID2',px_txn_line_detail_rec.txn_line_detail_id);
899 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name);
900 END IF;
901 IF l_invalid_operation = 'Y' THEN
902 log_mu_error
903 (
904 p_index => 1,
905 p_instance_id => px_txn_line_detail_rec.instance_id,
906 p_txn_line_detail_id => px_txn_line_detail_rec.txn_line_detail_id,
907 p_error_code => fnd_api.g_ret_sts_error,
908 x_mass_edit_error_tbl => l_mass_edit_error_tbl
909 );
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912 END IF;
913
914 csi_t_gen_utility_pvt.dump_mass_edit_rec(px_mass_edit_rec);
915
916 -- Validate that the status of the batch is NOT Processed or Successful
917 validate_batch_status(p_batch_id => px_mass_edit_rec.entry_id,
918 x_mass_edit_error_tbl => l_mass_edit_error_tbl);
919
920 debug('txn_line_detail_id: '||px_txn_line_detail_rec.txn_line_detail_id);
921 debug('px_txn_party_detail_tbl Count: '||px_txn_party_detail_tbl.count);
922 debug('px_txn_pty_acct_detail_tbl Count: '||px_txn_pty_acct_detail_tbl.count);
923 debug('px_txn_ext_attrib_vals_tbl Count: '||px_txn_ext_attrib_vals_tbl.count);
924
925 IF (px_mass_edit_inst_tbl.count > 0 OR
926 nvl(px_txn_line_detail_rec.txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num) THEN
927 IF (px_txn_party_detail_tbl.count > 0 OR
928 px_txn_pty_acct_detail_tbl.count > 0 OR
929 px_txn_ext_attrib_vals_tbl.count > 0) THEN
930 csi_t_vldn_routines_pvt.check_reqd_param(
931 p_value => px_txn_line_detail_rec.txn_line_detail_id,
932 p_param_name => 'px_txn_line_detail_rec.txn_line_detail_id',
933 p_api_name => l_api_name);
934 END IF;
935
936 -- assign all the required local pl/sql tables for calling the transaction details API
937 l_txn_line_rec := px_txn_line_rec;
938 -- added the IF below for bug 4769442
939 IF nvl(l_txn_line_rec.source_transaction_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
940 THEN
941 --Default this seeded value for mass update transactions
942 l_txn_line_rec.source_transaction_type_id := '3'; -- SOURCE_TRANSACTION_TYPE_ID for MASS_EDIT is 3
943 END IF;
944
945
946 IF nvl(px_txn_line_detail_rec.txn_line_detail_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
947 l_txn_line_detail_tbl(tld_idx) := px_txn_line_detail_rec;
948 l_txn_party_detail_tbl := px_txn_party_detail_tbl;
949 l_txn_pty_acct_detail_tbl := px_txn_pty_acct_detail_tbl;
950 l_txn_ext_attrib_vals_tbl := px_txn_ext_attrib_vals_tbl;
951 tld_idx := nvl(l_txn_line_detail_tbl.last,0) + 1;
952 pty_idx := nvl(l_txn_party_detail_tbl.last,0) + 1;
953 ptyacc_idx := nvl(l_txn_pty_acct_detail_tbl.last,0) + 1;
954 END IF;
955
956 csi_t_gen_utility_pvt.dump_txn_tables(
957 p_ids_or_index_based => 'I',
958 p_line_detail_tbl => l_txn_line_detail_tbl,
959 p_party_detail_tbl => l_txn_party_detail_tbl,
960 p_pty_acct_tbl => l_txn_pty_acct_detail_tbl,
961 p_ii_rltns_tbl => l_txn_ii_rltns_tbl,
962 p_org_assgn_tbl => l_txn_org_assgn_tbl,
963 p_ea_vals_tbl => l_txn_ext_attrib_vals_tbl);
964
965
966 IF px_mass_edit_inst_tbl.count > 0 THEN
967 FOR i IN px_mass_edit_inst_tbl.FIRST .. px_mass_edit_inst_tbl.LAST LOOP
968 IF nvl(px_mass_edit_inst_tbl(i).txn_line_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
969 l_create_instance_tdls := 'Y';
970 validate_batch_type(p_batch_type => px_mass_edit_rec.BATCH_TYPE,
971 p_api_name => 'UPDATE_MASS_EDIT_BATCH',
972 x_sub_type_id => l_sub_type_id,
973 x_mass_edit_error_tbl => l_mass_edit_error_tbl);
974 exit;
975 END IF;
976 END LOOP;
977 END IF;
978
979 debug('l_create_instance_tdls flag: '||l_create_instance_tdls);
980
981 IF l_create_instance_tdls = 'N' THEN
982 csi_mass_edit_pvt.validate_batch (px_mass_edit_rec,
983 'UPD', -- Update
984 l_mass_edit_error_tbl,
985 l_return_status);
986 END IF;
987
988 debug('Done validating batch, no. of errors: '||l_mass_edit_error_tbl.count);
989 debug('Return status: '||l_return_status);
990
991 IF (l_mass_edit_error_tbl.count = 0 OR
992 l_return_status = 'W') THEN
993 IF px_mass_edit_inst_tbl.count > 0 THEN
994 FOR inst_idx IN px_mass_edit_inst_tbl.FIRST .. px_mass_edit_inst_tbl.LAST LOOP
995 IF nvl(px_mass_edit_inst_tbl(inst_idx).txn_line_detail_id, fnd_api.g_miss_num)
996 = fnd_api.g_miss_num
997 THEN
998 debug('px_mass_edit_inst_tbl('||inst_idx||').instance_id = '
999 ||px_mass_edit_inst_tbl(inst_idx).instance_id);
1000 OPEN instance_csr (px_mass_edit_inst_tbl(inst_idx).instance_id);
1001 FETCH instance_csr INTO l_instance_csr;
1002 IF instance_csr%NOTFOUND Then
1003 CLOSE instance_csr;
1004 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_INSTANCE');
1005 FND_MESSAGE.set_token('INSTANCE_ID',px_mass_edit_inst_tbl(inst_idx).instance_id) ;
1006 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name);
1007 log_mu_error
1008 (
1009 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
1010 p_instance_id => px_mass_edit_inst_tbl(inst_idx).instance_id,
1011 p_txn_line_detail_id => null,
1012 p_error_code => fnd_api.g_ret_sts_error,
1013 x_mass_edit_error_tbl => l_mass_edit_error_tbl
1014 );
1015 Raise fnd_api.g_exc_error;
1016 End If;
1017 CLOSE instance_csr;
1018 --add the txn_line_id to all records in the detail rec and default the instance_exists flag to 'Y'
1019 l_txn_line_rec.SOURCE_TRANSACTION_ID := px_mass_edit_rec.entry_id;
1020 l_txn_line_rec.SOURCE_TRANSACTION_TABLE := 'CSI_MASS_EDIT_ENTRIES';
1021 l_txn_line_rec.SOURCE_TRANSACTION_TYPE_ID := 3;
1022 l_txn_line_detail_tbl(tld_idx).transaction_line_id := px_mass_edit_rec.TXN_LINE_ID;
1023 l_txn_line_detail_tbl(tld_idx).instance_exists_flag := 'Y';
1024 l_txn_line_detail_tbl(tld_idx).source_transaction_flag := 'Y';
1025 l_txn_line_detail_tbl(tld_idx).sub_type_id := l_sub_type_id;
1026 l_txn_line_detail_tbl(tld_idx).instance_id := l_instance_csr.instance_id;
1027 l_txn_line_detail_tbl(tld_idx).inventory_item_id := l_instance_csr.inventory_item_id;
1028 l_txn_line_detail_tbl(tld_idx).inv_organization_id := l_instance_csr.last_vld_organization_id;
1029 l_txn_line_detail_tbl(tld_idx).quantity := l_instance_csr.quantity;
1030 l_txn_line_detail_tbl(tld_idx).unit_of_measure := l_instance_csr.unit_of_measure;
1031 l_txn_line_detail_tbl(tld_idx).csi_system_id := l_instance_csr.system_id;
1032 l_txn_line_detail_tbl(tld_idx).location_type_code := l_instance_csr.location_type_code;
1033 l_txn_line_detail_tbl(tld_idx).location_id := l_instance_csr.location_id;
1034 l_txn_line_detail_tbl(tld_idx).install_location_type_code := l_instance_csr.install_location_type_code;
1035 l_txn_line_detail_tbl(tld_idx).install_location_id := l_instance_csr.install_location_id;
1036 l_txn_line_detail_tbl(tld_idx).installation_date := l_instance_csr.install_date;
1037 l_txn_line_detail_tbl(tld_idx).active_end_date := l_instance_csr.active_end_date;
1038 l_txn_line_detail_tbl(tld_idx).external_reference := l_instance_csr.external_reference;
1039 l_txn_line_detail_tbl(tld_idx).instance_status_id := l_instance_csr.instance_status_id;
1040 l_txn_line_detail_tbl(tld_idx).serial_number := l_instance_csr.serial_number;
1041 l_txn_line_detail_tbl(tld_idx).lot_number := l_instance_csr.lot_number;
1042
1043 -- Populate the Install Parameters Record
1044 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
1045 csi_gen_utility_pvt.populate_install_param_rec;
1046 END IF;
1047
1048 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
1049
1050 -- store the current owner party and account information for all the instances in the batch
1051 --query instance_party_id
1052 Begin
1053 SELECT INSTANCE_PARTY_ID
1054 INTO l_instance_party_id
1055 FROM CSI_I_PARTIES
1056 WHERE INSTANCE_ID = l_instance_csr.instance_id and
1057 PARTY_ID = l_instance_csr.owner_party_id and
1058 PARTY_SOURCE_TABLE = l_instance_csr.owner_party_source_table and
1059 RELATIONSHIP_TYPE_CODE = 'OWNER';
1060
1061 l_txn_party_detail_tbl(pty_idx).instance_party_id := l_instance_party_id;
1062 l_txn_party_detail_tbl(pty_idx).party_source_id := l_instance_csr.owner_party_id;
1063 l_txn_party_detail_tbl(pty_idx).party_source_table := l_instance_csr.owner_party_source_table;
1064 l_txn_party_detail_tbl(pty_idx).relationship_type_code := 'OWNER';
1065 l_txn_party_detail_tbl(pty_idx).contact_flag := 'N';
1066 l_txn_party_detail_tbl(pty_idx).txn_line_details_index := tld_idx;
1067
1068 debug('Checking to see if the Owner Party is an Internal Party from CSI_INSTALL_PARAMETERS');
1069 debug('Owner Party ID = '||l_instance_csr.owner_party_id);
1070 debug('Internal Party ID = '||l_internal_party_id);
1071
1072 --query ip_account_id only if the source is HZ
1073 IF nvl(l_internal_party_id,99999) <> l_instance_csr.owner_party_id THEN
1074
1075 IF (l_instance_csr.owner_party_source_table = 'HZ_PARTIES') THEN
1076 SELECT IP_ACCOUNT_ID
1077 INTO l_ip_account_id
1078 FROM CSI_IP_ACCOUNTS
1079 WHERE INSTANCE_PARTY_ID = l_instance_party_id AND
1080 RELATIONSHIP_TYPE_CODE = 'OWNER' AND
1081 PARTY_ACCOUNT_ID = l_instance_csr.owner_party_account_id;
1082
1083 l_txn_pty_acct_detail_tbl(ptyacc_idx).account_id := l_instance_csr.owner_party_account_id;
1084 l_txn_pty_acct_detail_tbl(ptyacc_idx).ip_account_id := l_ip_account_id;
1085 l_txn_pty_acct_detail_tbl(ptyacc_idx).relationship_type_code := 'OWNER';
1086 l_txn_pty_acct_detail_tbl(ptyacc_idx).txn_party_details_index := pty_idx;
1087 ptyacc_idx := ptyacc_idx + 1;
1088 END IF;
1089 END IF; -- Check for Internal Party ID
1090 tld_idx := tld_idx + 1;
1091 EXCEPTION
1092 When No_data_found Then
1093 -- there has to be only one record here else an exception
1094 FND_MESSAGE.set_name('CSI','CSI_INT_INST_OWNER_MISSING');
1095 FND_MESSAGE.set_token('INSTANCE_ID',l_instance_csr.instance_id);
1096 log_mu_error
1097 (
1098 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
1099 p_instance_id => l_instance_csr.instance_id,
1100 p_txn_line_detail_id => null,
1101 p_error_code => fnd_api.g_ret_sts_error,
1102 x_mass_edit_error_tbl => l_mass_edit_error_tbl
1103 );
1104 RAISE fnd_api.g_exc_error;
1105 When too_many_rows Then
1106 FND_MESSAGE.set_name('CSI','CSI_MANY_INST_OWNER_FOUND');
1107 FND_MESSAGE.set_token('INSTANCE_ID',l_instance_csr.instance_id);
1108 log_mu_error
1109 (
1110 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
1111 p_instance_id => l_instance_csr.instance_id,
1112 p_txn_line_detail_id => null,
1113 p_error_code => fnd_api.g_ret_sts_error,
1114 x_mass_edit_error_tbl => l_mass_edit_error_tbl
1115 );
1116 RAISE fnd_api.g_exc_error;
1117 When others Then
1118 FND_MESSAGE.set_name('CSI','CSI_API_OWNER_OTHERS_EXCEPTION');
1119 log_mu_error
1120 (
1121 p_index => nvl(l_mass_edit_error_tbl.last, 0) + 1,
1122 p_instance_id => l_instance_csr.instance_id,
1123 p_txn_line_detail_id => null,
1124 p_error_code => fnd_api.g_ret_sts_error,
1125 x_mass_edit_error_tbl => l_mass_edit_error_tbl
1126 );
1127 RAISE fnd_api.g_exc_unexpected_error;
1128 End;
1129 pty_idx := pty_idx +1;
1130 ELSE -- if line_detail_id = g_miss_num
1131 -- Setting txn line detail, active end date and object version number only for updates
1132 l_txn_line_detail_tbl(tld_idx).txn_line_detail_id :=
1133 px_mass_edit_inst_tbl(inst_idx).txn_line_detail_id;
1134 l_txn_line_detail_tbl(tld_idx).transaction_line_id :=
1135 px_mass_edit_rec.txn_line_id;
1136 l_txn_line_detail_tbl(tld_idx).active_end_date :=
1137 px_mass_edit_inst_tbl(inst_idx).active_end_date;
1138 l_txn_line_detail_tbl(tld_idx).object_version_number :=
1139 px_mass_edit_inst_tbl(inst_idx).object_version_number;
1140
1141 tld_idx := nvl(l_txn_line_detail_tbl.last,0) + 1;
1142 debug('Setting the active and date and object version number and incrementing index for deleted rows - PL/SQL Table Row: '||tld_idx);
1143
1144 END IF;
1145 END LOOP ; -- mass_edit_inst loop
1146 END IF; -- mass_edit_inst.count > 0
1147
1148 -- call the Update API
1149 debug('Calling csi_t_txn_details_grp.update_transaction_dtls API');
1150 csi_t_gen_utility_pvt.dump_txn_line_rec(l_txn_line_rec);
1151 debug('l_txn_line_detail_tbl count: '||l_txn_line_detail_tbl.count);
1152 debug('l_txn_party_detail_tbl count: '||l_txn_party_detail_tbl.count);
1153 debug('l_txn_pty_acct_detail_tbl count: '||l_txn_pty_acct_detail_tbl.count);
1154
1155 csi_t_txn_details_grp.update_transaction_dtls(
1156 p_api_version => p_api_version,
1157 p_commit => fnd_api.g_false,
1158 p_init_msg_list => p_init_msg_list,
1159 p_validation_level => p_validation_level,
1160 p_txn_line_rec => l_txn_line_rec,
1161 px_txn_line_detail_tbl => l_txn_line_detail_tbl,
1162 px_txn_ii_rltns_tbl => l_txn_ii_rltns_tbl,
1163 px_txn_party_detail_tbl => l_txn_party_detail_tbl,
1164 px_txn_pty_acct_detail_tbl => l_txn_pty_acct_detail_tbl,
1165 px_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
1166 px_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_vals_tbl,
1167 x_return_status => l_return_status,
1168 x_msg_count => l_msg_count,
1169 x_msg_data => l_msg_data);
1170
1171 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1172 l_msg_index := 1;
1173 WHILE l_msg_count > 0 loop
1174
1175 -- Set Error Table Index
1176 err_indx := nvl(l_mass_edit_error_tbl.last,0) + 1;
1177
1178 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1179 l_mass_edit_error_tbl(err_indx).instance_id := NULL;
1180 l_mass_edit_error_tbl(err_indx).entry_id := g_entry_id;
1181 l_mass_edit_error_tbl(err_indx).txn_line_detail_id := NULL;
1182 l_mass_edit_error_tbl(err_indx).error_text := l_error_message;
1183 l_mass_edit_error_tbl(err_indx).error_code := fnd_api.g_ret_sts_error;
1184 l_msg_index := l_msg_index + 1;
1185 l_msg_count := l_msg_count - 1;
1186 err_indx := err_indx + 1;
1187 END LOOP;
1188 RAISE fnd_api.g_exc_error;
1189 ELSE
1190 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
1191 END IF;
1192
1193 IF l_create_instance_tdls = 'Y' THEN
1194 debug('Calling validate_batch after updating txn details');
1195 csi_mass_edit_pvt.validate_batch (px_mass_edit_rec,
1196 'UPD', -- Update
1197 l_mass_edit_error_tbl,
1198 l_return_status);
1199
1200 debug('Done validating batch, no. of errors: '||l_mass_edit_error_tbl.count);
1201 debug('Return status: '||l_return_status);
1202
1203 IF (l_return_status = 'E') THEN
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206 END IF;
1207
1208 ELSE -- Validate Batch returned errors and they were errors and warnings
1209 debug('Validate Batch Failed. Pl. check and fix the Errors.');
1210 RAISE FND_API.G_EXC_ERROR;
1211 END IF; -- Check return of Validate Batch
1212 END IF; -- IF mass edit inst tbl.count OR line_detail_rec exists
1213
1214 -- calling table handler at the end to handle cases where only mu rec is updated in a batch
1215 CSI_MASS_EDIT_ENTRIES_B_PKG.update_Row(
1216 p_ENTRY_ID => px_mass_edit_rec.ENTRY_ID,
1217 p_TXN_LINE_ID => px_mass_edit_rec.TXN_LINE_ID,
1218 p_STATUS_CODE => l_mass_edit_rec.STATUS_CODE,
1219 p_SCHEDULE_DATE => l_mass_edit_rec.SCHEDULE_DATE,
1220 p_START_DATE => l_mass_edit_rec.START_DATE,
1221 p_END_DATE => l_mass_edit_rec.END_DATE,
1222 p_NAME => l_mass_edit_rec.NAME,
1223 p_CREATED_BY => fnd_api.g_miss_num,
1224 p_CREATION_DATE => fnd_api.g_miss_date,
1225 p_LAST_UPDATED_BY => csi_mass_edit_pub.g_user_id,
1226 p_LAST_UPDATE_DATE => sysdate,
1227 p_LAST_UPDATE_LOGIN => csi_mass_edit_pub.g_login_id,
1228 p_OBJECT_VERSION_NUMBER => l_mass_edit_rec.OBJECT_VERSION_NUMBER,
1229 p_DESCRIPTION => l_mass_edit_rec.DESCRIPTION,
1230 p_BATCH_TYPE => px_mass_edit_rec.BATCH_TYPE,
1231 p_SYSTEM_CASCADE => l_mass_edit_rec.SYSTEM_CASCADE
1232 );
1233 EXCEPTION
1234 WHEN FND_API.G_EXC_ERROR THEN
1235 ROLLBACK TO update_mass_edit_pvt;
1236 x_return_status := FND_API.G_RET_STS_ERROR ;
1237 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
1238 FND_MSG_PUB.Count_And_Get
1239 (p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1244 ROLLBACK TO update_mass_edit_pvt;
1245
1246 FND_MSG_PUB.Count_And_Get
1247 ( p_count => x_msg_count,
1248 p_data => x_msg_data
1249 );
1250 WHEN OTHERS THEN
1251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1252 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
1253 debug( to_char(SQLCODE)||substr(SQLERRM, 1, 255));
1254 ROLLBACK TO update_mass_edit_pvt;
1255 IF FND_MSG_PUB.Check_Msg_Level
1256 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1257 THEN
1258 FND_MSG_PUB.Add_Exc_Msg
1259 (G_PKG_NAME ,
1260 l_api_name
1261 );
1262 END IF;
1263 FND_MSG_PUB.Count_And_Get
1264 (p_count => x_msg_count,
1265 p_data => x_msg_data
1266 );
1267
1268 END UPDATE_MASS_EDIT_BATCH;
1269
1270 PROCEDURE DELETE_MASS_EDIT_BATCH
1271 (
1272 p_api_version IN NUMBER,
1273 p_commit IN VARCHAR2 := fnd_api.g_false,
1274 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1275 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1276 p_mass_edit_rec IN csi_mass_edit_pub.mass_edit_rec,
1277 x_return_status OUT NOCOPY VARCHAR2,
1278 x_msg_count OUT NOCOPY NUMBER,
1279 x_msg_data OUT NOCOPY VARCHAR2
1280
1281 ) IS
1282
1283 l_api_version NUMBER := 1.0;
1284 l_api_name VARCHAR2(30) := 'DELETE_MASS_EDIT_BATCH';
1285 l_msg_count NUMBER;
1286 l_msg_data VARCHAR2(2000);
1287 l_return_status VARCHAR2(1);
1288 l_mass_edit_rec csi_mass_edit_pub.mass_edit_rec;
1289
1290 BEGIN
1291
1292 -- Standard Start of API savepoint
1293 SAVEPOINT delete_mass_edit_batch_pvt;
1294
1295 -- Initialize message list if p_init_msg_list is set to TRUE.
1296 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1297 FND_MSG_PUB.Initialize;
1298 END IF;
1299
1300 -- Initialize API return status to success
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 -- Standard call to check for call compatibility.
1304 IF NOT
1305 FND_API.Compatible_API_Call (
1306 p_current_version_number => l_api_version,
1307 p_caller_version_number => p_api_version,
1308 p_api_name => l_api_name,
1309 p_pkg_name => g_pkg_name) THEN
1310
1311 RAISE FND_API.G_Exc_Unexpected_Error;
1312 END IF;
1313
1314 -- Validate the Batch
1315 IF ( (nvl(p_mass_edit_rec.entry_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num)
1316 OR ( nvl(p_mass_edit_rec.name, fnd_api.g_miss_char) = fnd_api.g_miss_char ))
1317 THEN
1318
1319 IF NOT Is_valid_batch (p_batch_name => p_mass_edit_rec.name,
1320 p_batch_id => p_mass_edit_rec.entry_id,
1321 x_mass_edit_rec => l_mass_edit_rec
1322 )
1323 THEN
1324 IF nvl(p_mass_edit_rec.entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1325 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_NAME');
1326 FND_MESSAGE.set_token('BATCH_NAME',p_mass_edit_rec.name);
1327 ELSE
1328 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_ID');
1329 FND_MESSAGE.set_token('BATCH_ID',p_mass_edit_rec.entry_id);
1330 END IF;
1331 FND_MSG_PUB.add;
1332 Raise fnd_api.g_exc_error;
1333 END IF;
1334 IF nvl(l_mass_edit_rec.status_code, 'CREATED') = 'PROCESSING' THEN
1335 debug('Cannot Delete a Batch that is being Processed: '||l_mass_edit_rec.status_code);
1336 FND_MESSAGE.set_name('CSI','CSI_MU_BATCH_UPD_DISALLOWED');
1337 FND_MESSAGE.set_token('BATCH_NAME',l_mass_edit_rec.name);
1338 FND_MESSAGE.set_token('BATCH_STATUS',l_mass_edit_rec.status_code) ;
1339 FND_MSG_PUB.add;
1340 Raise fnd_api.g_exc_error;
1341 END IF;
1342 END IF;
1343
1344 csi_t_txn_details_grp.delete_transaction_dtls (
1345 p_api_version => p_api_version,
1346 p_commit => p_commit,
1347 p_init_msg_list => p_init_msg_list,
1348 p_validation_level => p_validation_level,
1349 p_transaction_line_id => l_mass_edit_rec.txn_line_id,
1350 x_return_status => l_return_status,
1351 x_msg_count => l_msg_count,
1352 x_msg_data => l_msg_data
1353 );
1354
1355 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1356 RAISE fnd_api.g_exc_error;
1357 END IF;
1358
1359 -- call table handler to delete mass edit entry
1360
1361 csi_mass_edit_entries_b_pkg.delete_row(
1362 p_entry_id => l_mass_edit_rec.entry_id);
1363
1364 -- call Service contracts API to delete the contracts rules, if any.
1365
1366 OKS_IBINT_PUB.DELETE_BATCH(
1367 p_api_version => 1.0,
1368 p_init_msg_list => 'F',
1369 p_batch_id => l_mass_edit_rec.entry_id,
1370 x_return_status => l_return_status,
1371 x_msg_count => l_msg_count,
1372 x_msg_data => l_msg_data);
1373
1374 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1375 RAISE fnd_api.g_exc_error;
1376 END IF;
1377
1378 -- Standard check of p_commit.
1379 IF FND_API.To_Boolean( p_commit ) THEN
1380 COMMIT WORK;
1381 END IF;
1382
1383 -- Standard call to get message count and IF count is get message info.
1384 FND_MSG_PUB.Count_And_Get
1385 (p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388
1389 EXCEPTION
1390 WHEN FND_API.G_EXC_ERROR THEN
1391
1392 ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
1393 x_return_status := FND_API.G_RET_STS_ERROR ;
1394 FND_MSG_PUB.Count_And_Get (
1395 p_count => x_msg_count,
1396 p_data => x_msg_data);
1397
1398 csi_t_gen_utility_pvt.set_debug_off;
1399
1400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1401
1402 ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1404
1405 FND_MSG_PUB.Count_And_Get(
1406 p_count => x_msg_count,
1407 p_data => x_msg_data);
1408
1409 csi_t_gen_utility_pvt.set_debug_off;
1410
1411 WHEN OTHERS THEN
1412
1413 ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1415
1416 IF FND_MSG_PUB.Check_Msg_Level(
1417 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1418
1419 FND_MSG_PUB.Add_Exc_Msg(
1420 p_pkg_name => G_PKG_NAME,
1421 p_procedure_name => l_api_name);
1422
1423 END IF;
1424
1425 FND_MSG_PUB.Count_And_Get(
1426 p_count => x_msg_count,
1427 p_data => x_msg_data);
1428
1429 csi_t_gen_utility_pvt.set_debug_off;
1430
1431 END Delete_Mass_Edit_Batch;
1432
1433
1434 PROCEDURE GET_MASS_EDIT_DETAILS (
1435 p_api_version IN NUMBER,
1436 p_commit IN VARCHAR2 := fnd_api.g_false,
1437 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1438 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1439 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
1440 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl ,
1441 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
1442 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
1443 x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1444 x_return_status OUT NOCOPY VARCHAR2,
1445 x_msg_count OUT NOCOPY NUMBER ,
1446 x_msg_data OUT NOCOPY VARCHAR2)
1447 IS
1448
1449 l_api_version NUMBER := 1.0;
1450 l_api_name VARCHAR2(30) := 'GET_MASS_EDIT_DETAILS';
1451 l_msg_count NUMBER;
1452 l_msg_data VARCHAR2(2000);
1453 l_return_status VARCHAR2(1);
1454 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec ;
1455 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec ;
1456 x_tmp_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl ;
1457 x_tmp_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl ;
1458 x_tmp_ext_attribs_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl ;
1459 x_tmp_iea_values_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl ;
1460 x_tmp_systems_tbl csi_t_datastructures_grp.txn_systems_tbl ;
1461
1462 BEGIN
1463
1464 -- Standard Start of API savepoint
1465 SAVEPOINT get_mass_edit_details_pvt;
1466
1467 -- Initialize message list if p_init_msg_list is set to TRUE.
1468 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1469 FND_MSG_PUB.Initialize;
1470 END IF;
1471
1472 -- Initialize API return status to success
1473 x_return_status := FND_API.G_RET_STS_SUCCESS;
1474
1475 -- Standard call to check for call compatibility.
1476 IF NOT
1477 FND_API.Compatible_API_Call (
1478 p_current_version_number => l_api_version,
1479 p_caller_version_number => p_api_version,
1480 p_api_name => l_api_name,
1481 p_pkg_name => g_pkg_name) THEN
1482
1483 RAISE FND_API.G_Exc_Unexpected_Error;
1484 END IF;
1485
1486 -- Assign Txn Line query attributes (Get txn details) for the Mass Update
1487
1488 IF px_mass_edit_rec.txn_line_id IS NOT NULL
1489 OR px_mass_edit_rec.txn_line_id <> fnd_api.g_miss_num
1490 THEN
1491 l_txn_line_query_rec.transaction_line_id := px_mass_edit_rec.txn_line_id ;
1492 l_txn_line_detail_query_rec.transaction_line_id := px_mass_edit_rec.txn_line_id ;
1493 END IF ;
1494
1495 IF px_mass_edit_rec.entry_id IS NOT NULL
1496 OR px_mass_edit_rec.entry_id <> fnd_api.g_miss_num
1497 THEN
1498 l_txn_line_query_rec.source_transaction_id := px_mass_edit_rec.entry_id ;
1499 l_txn_line_query_rec.source_transaction_type_id := 3 ;
1500 l_txn_line_query_rec.source_transaction_table := 'CSI_MASS_EDIT_ENTRIES' ;
1501 END IF ;
1502
1503 -- Validate the Batch
1504 IF ( (nvl(px_mass_edit_rec.entry_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num)
1505 OR ( nvl(px_mass_edit_rec.name, fnd_api.g_miss_char) = fnd_api.g_miss_char ))
1506 THEN
1507
1508 IF NOT Is_valid_batch (p_batch_name => px_mass_edit_rec.name,
1509 p_batch_id => px_mass_edit_rec.entry_id,
1510 x_mass_edit_rec => px_mass_edit_rec
1511 )
1512 THEN
1513 IF nvl(px_mass_edit_rec.entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1514 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_NAME');
1515 FND_MESSAGE.set_token('BATCH_NAME',px_mass_edit_rec.name);
1516 ELSE
1517 FND_MESSAGE.set_name('CSI','CSI_MU_INVALID_BATCH_ID');
1518 FND_MESSAGE.set_token('BATCH_ID',px_mass_edit_rec.entry_id);
1519 END IF;
1520
1521 FND_MSG_PUB.add;
1522 Raise fnd_api.g_exc_error;
1523 END IF;
1524 END IF;
1525
1526 csi_t_txn_details_grp.get_transaction_details(
1527 p_api_version => p_api_version,
1528 p_commit => p_commit,
1529 p_init_msg_list => p_init_msg_list,
1530 p_validation_level => p_validation_level,
1531 p_txn_line_query_rec => l_txn_line_query_rec,
1532 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1533 x_txn_line_detail_tbl => x_txn_line_detail_tbl,
1534 p_get_parties_flag => 'Y',
1535 x_txn_party_detail_tbl => x_txn_party_detail_tbl,
1536 p_get_pty_accts_flag => 'Y',
1537 x_txn_pty_acct_detail_tbl => x_txn_pty_acct_detail_tbl,
1538 p_get_ext_attrib_vals_flag => 'Y',
1539 x_txn_ext_attrib_vals_tbl => x_txn_ext_attrib_vals_tbl,
1540 p_get_ii_rltns_flag => 'N',
1541 x_txn_ii_rltns_tbl => x_tmp_ii_rltns_tbl,
1542 p_get_org_assgns_flag => 'N',
1543 x_txn_org_assgn_tbl => x_tmp_org_assgn_tbl,
1544 p_get_csi_attribs_flag => 'N',
1545 x_csi_ext_attribs_tbl => x_tmp_ext_attribs_tbl,
1546 p_get_csi_iea_values_flag => 'N',
1547 x_csi_iea_values_tbl => x_tmp_iea_values_tbl,
1548 p_get_txn_systems_flag => 'N',
1549 x_txn_systems_tbl => x_tmp_systems_tbl,
1550 x_return_status => l_return_status,
1551 x_msg_count => l_msg_count,
1552 x_msg_data => l_msg_data);
1553
1554 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1555 RAISE fnd_api.g_exc_error;
1556 END IF;
1557
1558 -- Standard check of p_commit.
1559 IF FND_API.To_Boolean( p_commit ) THEN
1560 COMMIT WORK;
1561 END IF;
1562
1563 -- Standard call to get message count and IF count is get message info.
1564 FND_MSG_PUB.Count_And_Get
1565 (p_count => x_msg_count,
1566 p_data => x_msg_data
1567 );
1568
1569 EXCEPTION
1570 WHEN FND_API.G_EXC_ERROR THEN
1571
1572 ROLLBACK TO Get_mass_edit_details_pvt;
1573 x_return_status := FND_API.G_RET_STS_ERROR ;
1574 FND_MSG_PUB.Count_And_Get (
1575 p_count => x_msg_count,
1576 p_data => x_msg_data);
1577
1578 csi_t_gen_utility_pvt.set_debug_off;
1579
1580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1581
1582 ROLLBACK TO Get_mass_edit_details_pvt;
1583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1584
1585 FND_MSG_PUB.Count_And_Get(
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588
1589 csi_t_gen_utility_pvt.set_debug_off;
1590
1591 WHEN OTHERS THEN
1592
1593 ROLLBACK TO Get_mass_edit_details_pvt;
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1595
1596 IF FND_MSG_PUB.Check_Msg_Level(
1597 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1598
1599 FND_MSG_PUB.Add_Exc_Msg(
1600 p_pkg_name => G_PKG_NAME,
1601 p_procedure_name => l_api_name);
1602
1603 END IF;
1604
1605 FND_MSG_PUB.Count_And_Get(
1606 p_count => x_msg_count,
1607 p_data => x_msg_data);
1608
1609 csi_t_gen_utility_pvt.set_debug_off;
1610
1611 END Get_mass_edit_details;
1612
1613 PROCEDURE vld_item_instance_active (p_instance_id_tab IN NumTabType,
1614 p_txn_line_detail_id_tab IN NumTabType,
1615 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
1616
1617 e_indx NUMBER := 0;
1618 l_inst_active NUMBER := NULL;
1619
1620
1621 CURSOR inst_active (pc_instance_id IN NUMBER) IS
1622 SELECT 1
1623 FROM csi_item_instances
1624 WHERE instance_id = pc_instance_id
1625 AND (active_end_date is NULL OR
1626 nvl(active_end_date, sysdate+1) > sysdate);
1627
1628 BEGIN
1629 -- Set Error Table Index
1630 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
1631
1632 FOR ind IN 1 .. p_instance_id_tab.COUNT LOOP
1633
1634 OPEN inst_active(p_instance_id_tab(ind));
1635 FETCH inst_active INTO l_inst_active;
1636 CLOSE inst_active;
1637
1638 IF l_inst_active is NULL THEN
1639 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INACTIVE_INSTANCE');
1640 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1641 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
1642 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
1643 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1644 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1645 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
1646 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1647 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1648 e_indx := e_indx + 1;
1649 END IF;
1650
1651 END LOOP;
1652 debug(' Error Table Count when exiting vld_item_instance active is: '||px_mass_edit_error_tbl.count);
1653
1654 END vld_item_instance_active;
1655
1656 PROCEDURE vld_batch_inst_same_owner(p_txn_line_id_tab IN NumTabType,
1657 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
1658
1659 TYPE NumTabType is varray(10000) of number;
1660
1661 l_account_id_tab NumTabType;
1662 l_party_source_id_tab NumTabType;
1663 l_party_source_table_tab Char30TabType;
1664 l_transaction_line_id_tab NumTabType;
1665 l_txn_line_detail_id_tab NumTabType;
1666 l_txn_party_detail_id_tab NumTabType;
1667 l_instance_id_tab NumTabType;
1668 l_old_party_id NUMBER := NULL;
1669 l_old_account_id NUMBER := NULL;
1670 e_indx NUMBER := 0;
1671
1672 MAX_BUFFER_SIZE number := 1000;
1673
1674 CURSOR party_csr (pc_txn_line_id IN NUMBER) IS
1675 SELECT cil.transaction_line_id transaction_line_id
1676 ,cid.party_source_id party_id
1677 ,cid.party_source_table party_source_table
1678 ,cil.txn_line_detail_id txn_line_detail_id
1679 ,cil.instance_id instance_id
1680 ,cid.txn_party_detail_id txn_party_detail_id
1681 FROM csi_t_txn_line_details cil
1682 ,csi_t_party_details cid
1683 WHERE cil.transaction_line_id = pc_txn_line_id
1684 AND cil.instance_id IS NOT NULL
1685 AND cid.txn_line_detail_id = cil.txn_line_detail_id
1686 AND cid.relationship_type_code = 'OWNER';
1687
1688 CURSOR account_csr (pc_txn_line_id IN NUMBER,
1689 pc_txn_party_detail_id IN NUMBER) IS
1690 SELECT cia.account_id
1691 FROM csi_t_txn_line_details cil
1692 ,csi_t_party_details cid
1693 ,csi_t_party_accounts cia
1694 WHERE cil.transaction_line_id = pc_txn_line_id
1695 AND cil.instance_id IS NOT NULL
1696 AND cid.txn_line_detail_id = cil.txn_line_detail_id
1697 AND cid.relationship_type_code = 'OWNER'
1698 AND cid.txn_party_detail_id = cia.txn_party_detail_id
1699 AND cia.txn_party_detail_id = pc_txn_party_detail_id;
1700
1701 BEGIN
1702 -- Set Error Table Index
1703 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
1704
1705 --FOR i IN 1 .. p_txn_line_id_tab.COUNT LOOP
1706
1707 IF p_txn_line_id_tab.COUNT > 0 THEN
1708 debug('Count of Main transaction line id tbl: '||p_txn_line_id_tab.count);
1709 debug('Transaction line id '||p_txn_line_id_tab(1));
1710
1711 OPEN party_csr(p_txn_line_id_tab(1));
1712 LOOP
1713
1714 FETCH party_csr BULK COLLECT
1715 INTO l_transaction_line_id_tab,
1716 l_party_source_id_tab,
1717 l_party_source_table_tab,
1718 l_txn_line_detail_id_tab,
1719 l_instance_id_tab,
1720 l_txn_party_detail_id_tab
1721 LIMIT MAX_BUFFER_SIZE;
1722
1723 FOR ind IN 1 .. l_transaction_line_id_tab.COUNT LOOP
1724 l_old_party_id := l_party_source_id_tab(1);
1725
1726 debug('Count of transaction line id tbl: '||l_transaction_line_id_tab.count);
1727 debug('Old Party ID: '||l_old_party_id);
1728 debug('New Party Source ID: '||l_party_source_id_tab(ind));
1729
1730 IF l_old_party_id <> l_party_source_id_tab(ind) THEN
1731 debug('This instance party id does not match the other instances in the batch: '||l_instance_id_tab(ind));
1732 FND_MESSAGE.SET_NAME('CSI','CSI_MU_BATCH_DIFF_OWNER_PTY');
1733 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1734 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',l_instance_id_tab(ind));
1735 px_mass_edit_error_tbl(e_indx).instance_id := l_instance_id_tab(ind);
1736 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1737 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1738 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := l_txn_line_detail_id_tab(ind);
1739 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1740 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1741 e_indx := e_indx + 1;
1742 END IF;
1743
1744 IF l_party_source_table_tab(ind) = 'HZ_PARTIES' THEN
1745
1746 OPEN account_csr(p_txn_line_id_tab(1),
1747 l_txn_party_detail_id_tab(ind));
1748 LOOP
1749
1750 debug('Party Detail ID for Account: '||l_txn_party_detail_id_tab(ind));
1751 FETCH account_csr BULK COLLECT
1752 INTO l_account_id_tab
1753 LIMIT MAX_BUFFER_SIZE;
1754
1755 IF l_old_account_id IS NULL THEN
1756 l_old_account_id := l_account_id_tab(1);
1757 END IF;
1758
1759 FOR ind IN 1 .. l_account_id_tab.COUNT LOOP
1760
1761 debug('Old Account ID: '||l_old_account_id);
1762 debug('New Account ID: '||l_account_id_tab(ind));
1763
1764 IF l_old_account_id <> l_account_id_tab(ind) THEN
1765 debug(' Account This instance party id does not match the other instances in the batch: '||l_instance_id_tab(ind));
1766 FND_MESSAGE.SET_NAME('CSI','CSI_MU_BATCH_DIFF_OWNER_ACCT');
1767 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1768 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',l_instance_id_tab(ind));
1769 px_mass_edit_error_tbl(e_indx).instance_id := l_instance_id_tab(ind);
1770 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1771 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1772 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := l_txn_line_detail_id_tab(ind);
1773 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1774 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1775 e_indx := e_indx + 1;
1776 END IF;
1777
1778
1779 END LOOP; -- Acct Csr
1780
1781 EXIT when account_csr%NOTFOUND;
1782 END LOOP; -- Account
1783
1784 IF account_csr%ISOPEN THEN
1785 CLOSE account_csr;
1786 END IF;
1787
1788 END IF; -- HZ_PARTIES check
1789
1790
1791 END LOOP; -- Party Csr
1792
1793 EXIT when party_csr%NOTFOUND;
1794 END LOOP; -- Party
1795
1796 IF party_csr%ISOPEN THEN
1797 CLOSE party_csr;
1798 END IF;
1799
1800 -- END LOOP;
1801 END IF;
1802 debug(' Error Table Count when exiting vld_item_instance active is: '||px_mass_edit_error_tbl.count);
1803
1804 END vld_batch_inst_same_owner;
1805
1806 PROCEDURE vld_batch_inst_curr_owner (p_txn_line_id_tab IN NumTabType,
1807 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
1808
1809 l_party_rec_count_tab NumTabType;
1810 l_transaction_line_id_tab NumTabType;
1811 l_owner_party_source_table_tab Char30TabType;
1812 l_owner_party_id_tab NumTabType;
1813 l_owner_party_account_id_tab NumTabType;
1814 l_txn_line_detail_id_tab NumTabType;
1815 l_instance_id_tab NumTabType;
1816 e_indx NUMBER := 0;
1817
1818 MAX_BUFFER_SIZE number := 1000;
1819
1820 CURSOR inst_csr (pc_txn_line_id IN NUMBER) IS
1821 SELECT cil.transaction_line_id transaction_line_id
1822 ,cii.owner_party_source_table owner_party_source_table
1823 ,cii.owner_party_id owner_party_id
1824 ,cii.owner_party_account_id owner_party_account_id
1825 ,cil.txn_line_detail_id txn_line_detail_id
1826 ,cii.instance_id instance_id
1827 FROM csi_t_txn_line_details cil
1828 ,csi_item_instances cii
1829 WHERE cil.transaction_line_id = pc_txn_line_id
1830 AND cil.instance_id IS NOT NULL
1831 AND cil.instance_id = cii.instance_id;
1832
1833 CURSOR party_csr (pc_txn_line_id IN NUMBER) IS
1834 SELECT ctpd.txn_party_detail_id,
1835 ctpd.txn_line_detail_id,
1836 ctpd.party_source_id,
1837 ctpd.party_source_table
1838 FROM csi_t_party_details ctpd
1839 WHERE ctpd.txn_line_detail_id = pc_txn_line_id;
1840
1841 party_rec party_csr%rowtype;
1842
1843 CURSOR acct_csr (pc_txn_party_detail_id IN NUMBER) IS
1844 SELECT ctpa.txn_account_detail_id,
1845 ctpa.txn_party_detail_id,
1846 ctpa.account_id
1847 FROM csi_t_party_accounts ctpa
1848 WHERE ctpa.txn_party_detail_id = pc_txn_party_detail_id;
1849
1850 acct_rec acct_csr%rowtype;
1851
1852 BEGIN
1853 -- Set Error Table Index
1854 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
1855
1856 FOR i IN 1 .. p_txn_line_id_tab.COUNT LOOP
1857
1858 OPEN inst_csr(p_txn_line_id_tab(i));
1859 LOOP
1860
1861 FETCH inst_csr BULK COLLECT
1862 INTO l_transaction_line_id_tab,
1863 l_owner_party_source_table_tab,
1864 l_owner_party_id_tab,
1865 l_owner_party_account_id_tab,
1866 l_txn_line_detail_id_tab,
1867 l_instance_id_tab
1868 LIMIT MAX_BUFFER_SIZE;
1869
1870 FOR ind IN 1 .. l_transaction_line_id_tab.COUNT LOOP
1871
1872 FOR party_rec IN party_csr (l_txn_line_detail_id_tab(ind)) LOOP
1873
1874 IF party_rec.party_source_id <> l_owner_party_id_tab(ind) THEN
1875 -- Raise Error values have changed.
1876 FND_MESSAGE.SET_NAME('CSI','CSI_MU_PTY_DIFF_OWNER_PTY');
1877 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1878 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',l_instance_id_tab(ind));
1879 px_mass_edit_error_tbl(e_indx).instance_id := l_instance_id_tab(ind);
1880 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1881 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1882 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := l_txn_line_detail_id_tab(ind);
1883 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1884 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1885 e_indx := e_indx + 1;
1886 END IF;
1887
1888 FOR acct_rec IN acct_csr (party_rec.txn_party_detail_id) LOOP
1889
1890 IF acct_rec.account_id <> l_owner_party_account_id_tab(ind) THEN
1891 -- Raise Error values have changed.
1892 FND_MESSAGE.SET_NAME('CSI','CSI_MU_ACCT_DIFF_OWNER_ACCTT');
1893 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1894 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',l_instance_id_tab(ind));
1895 px_mass_edit_error_tbl(e_indx).instance_id := l_instance_id_tab(ind);
1896 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1897 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1898 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := l_txn_line_detail_id_tab(ind);
1899 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1900 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1901 e_indx := e_indx + 1;
1902 END IF;
1903
1904 END LOOP; -- acct_csr
1905
1906 END LOOP; -- party_csr
1907
1908 END LOOP;
1909
1910 EXIT when inst_csr%NOTFOUND;
1911 END LOOP; -- inst_csr
1912
1913 IF inst_csr%ISOPEN THEN
1914 CLOSE inst_csr;
1915 END IF;
1916
1917 END LOOP;
1918 debug(' Error Table Count when exiting vld_item_instance active is: '||px_mass_edit_error_tbl.count);
1919
1920 END vld_batch_inst_curr_owner;
1921
1922
1923 PROCEDURE vld_child_inst_location(p_instance_id_tab IN NumTabType,
1924 p_txn_line_detail_id_tab IN NumTabType,
1925 p_instance_usage_code_tab IN Char30TabType,
1926 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
1927
1928 e_indx NUMBER := NULL;
1929 l_object_id NUMBER;
1930 l_parent_found VARCHAR2(1) := 'N';
1931
1932 BEGIN
1933 -- Set Error Table Index
1934 e_indx := nvl(px_mass_edit_error_tbl.count,0) + 1;
1935
1936 FOR i IN 1 .. p_instance_id_tab.COUNT LOOP
1937
1938 -- Get the Parent ID
1939 debug(' Getting top most parent for subject id: '||p_instance_id_tab(i));
1940
1941 csi_ii_relationships_pvt.get_top_most_parent(p_subject_id => p_instance_id_tab(i),
1942 p_rel_type_code => 'COMPONENT-OF',
1943 p_object_id => l_object_id);
1944
1945 debug(' Parent Instance: '||l_object_id);
1946 l_parent_found := 'N';
1947
1948 IF l_object_id <> p_instance_id_tab(i) THEN
1949
1950 FOR ind IN 1 .. p_instance_id_tab.COUNT LOOP
1951 debug(' Comparing: '||p_instance_id_tab(ind)||'-'||l_object_id);
1952 IF p_instance_id_tab(ind) = l_object_id THEN
1953 l_parent_found := 'Y';
1954 exit;
1955 END IF;
1956 END LOOP;
1957
1958 IF l_parent_found = 'N' THEN
1959 -- Log error Parent Must be found
1960 FND_MESSAGE.SET_NAME('CSI','CSI_MU_PARENT_INST_NOT_EXISTS');
1961 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
1962 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(i));
1963 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(i);
1964 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
1965 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
1966 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(i);
1967 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
1968 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
1969 e_indx := e_indx + 1;
1970 END IF;
1971 END IF;
1972 END LOOP;
1973 debug(' Error Table Count when exiting vld_child_inst_location is: '||px_mass_edit_error_tbl.count);
1974
1975 END vld_child_inst_location;
1976
1977 PROCEDURE vld_item_inst_location(p_instance_id_tab IN NumTabType,
1978 p_txn_line_id_tab IN NumTabType,
1979 p_location_type_code_tab IN Char30TabType,
1980 p_location_id_tab IN NumTabType,
1981 p_install_location_id_tab IN NumTabType,
1982 p_instance_status_id_tab IN NumTabType,
1983 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
1984
1985 e_indx NUMBER;
1986
1987 CURSOR dummy_csr(pc_txn_line_id IN NUMBER) IS
1988 SELECT cil.location_id location_id
1989 ,cil.instance_status_id instance_status_id
1990 ,cil.install_location_id install_location_id
1991 ,cil.txn_line_detail_id txn_line_detail_id
1992 FROM csi_t_txn_line_details cil
1993 WHERE cil.transaction_line_id = pc_txn_line_id
1994 AND cil.instance_id IS NULL;
1995
1996 dummy_rec dummy_csr%rowtype;
1997
1998 CURSOR inst_status_csr(pc_instance_status_id IN NUMBER) IS
1999 SELECT terminated_flag
2000 FROM csi_instance_statuses
2001 WHERE instance_status_id = pc_instance_status_id;
2002
2003 inst_status_rec inst_status_csr%rowtype;
2004
2005 BEGIN
2006 -- Set Error Table Index
2007 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
2008
2009 FOR ind IN 1 .. p_instance_id_tab.COUNT LOOP
2010
2011 IF p_location_type_code_tab(ind) NOT IN ('HZ_PARTY_SITES','HZ_LOCATIONS','VENDOR_SITE') THEN
2012
2013 FOR dummy_rec IN dummy_csr (p_txn_line_id_tab(ind)) LOOP
2014
2015 IF p_instance_status_id_tab(ind) IS NOT NULL THEN
2016 -- Check the terminable flag for this instance_status_id
2017 OPEN inst_status_csr(p_instance_status_id_tab(ind));
2018 FETCH inst_status_csr INTO inst_status_rec;
2019 CLOSE inst_status_csr;
2020 END IF;
2021
2022 IF inst_status_rec.terminated_flag = 'Y' THEN
2023 -- Status is Terminable so Log Error
2024 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INVALID_INST_STATUS');
2025 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2026 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2027 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2028 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2029 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2030 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := dummy_rec.txn_line_detail_id;
2031 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2032 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2033 e_indx := e_indx + 1;
2034 END IF;
2035
2036 IF dummy_rec.location_id IS NOT NULL THEN
2037 -- Value cannot be provided log error
2038 FND_MESSAGE.SET_NAME('CSI','CSI_MU_CANNOT_UPD_CURR_LOC');
2039 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2040 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2041 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2042 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2043 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2044 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := dummy_rec.txn_line_detail_id;
2045 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2046 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2047 e_indx := e_indx + 1;
2048 END IF;
2049
2050 IF dummy_rec.install_location_id IS NOT NULL THEN
2051 -- Value cannot be provided so log error
2052 FND_MESSAGE.SET_NAME('CSI','CSI_MU_CANNOT_UPD_INSTALL_LOC');
2053 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2054 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2055 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2056 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2057 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2058 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := dummy_rec.txn_line_detail_id;
2059 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2060 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2061 e_indx := e_indx + 1;
2062 END IF;
2063
2064 END LOOP;
2065
2066 END IF; -- Location Type Code
2067
2068 END LOOP;
2069 debug(' Error Table Count when exiting vld_item_inst_location is: '||px_mass_edit_error_tbl.count);
2070
2071 END vld_item_inst_location;
2072
2073 PROCEDURE vld_term_date(p_txn_line_id_tab IN NumTabType,
2074 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
2075
2076 e_indx NUMBER;
2077
2078 CURSOR dummy_csr(pc_txn_line_id IN NUMBER) IS
2079 SELECT cil.active_end_date active_end_date
2080 ,cil.txn_line_detail_id txn_line_detail_id
2081 FROM csi_t_txn_line_details cil
2082 WHERE cil.transaction_line_id = pc_txn_line_id
2083 AND cil.instance_id IS NULL;
2084
2085 dummy_rec dummy_csr%rowtype;
2086
2087 BEGIN
2088 -- Set Error Table Index
2089 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
2090
2091 FOR dummy_rec IN dummy_csr (p_txn_line_id_tab(1)) LOOP
2092
2093 IF (dummy_rec.active_end_date > sysdate) OR
2094 (nvl(dummy_rec.active_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date) THEN
2095 FND_MESSAGE.SET_NAME('CSI','CSI_MU_TERM_DATE');
2096 FND_MESSAGE.SET_TOKEN('TERMINATION_DATE',dummy_rec.active_end_date);
2097 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2098 FND_MESSAGE.SET_TOKEN('CURR_DATE',sysdate);
2099 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2100 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2101 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := dummy_rec.txn_line_detail_id;
2102 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2103 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2104 e_indx := e_indx + 1;
2105 END IF;
2106
2107 END LOOP;
2108 debug(' Error Table Count when exiting vld_term_date is: '||px_mass_edit_error_tbl.count);
2109
2110 END vld_term_date;
2111
2112 PROCEDURE vld_xfer_date(p_txn_line_id_tab IN NumTabType,
2113 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
2114
2115 e_indx NUMBER := 0;
2116
2117 CURSOR party_csr (pc_txn_line_id IN NUMBER) IS
2118 SELECT cid.active_start_date active_start_date
2119 ,cid.txn_line_detail_id txn_line_detail_id
2120 FROM csi_t_txn_line_details cil
2121 ,csi_t_party_details cid
2122 WHERE cil.transaction_line_id = pc_txn_line_id
2123 AND cil.instance_id IS NULL
2124 AND cid.txn_line_detail_id = cil.txn_line_detail_id
2125 AND cid.relationship_type_code = 'OWNER';
2126
2127 party_rec party_csr%rowtype;
2128
2129 BEGIN
2130 -- Set Error Table Index
2131 e_indx := nvl(px_mass_edit_error_tbl.last,0) + 1;
2132
2133 FOR party_rec IN party_csr (p_txn_line_id_tab(1)) LOOP
2134 IF (party_rec.active_start_date > sysdate) OR
2135 (nvl(party_rec.active_start_date,fnd_api.g_miss_date) = fnd_api.g_miss_date) THEN
2136 FND_MESSAGE.SET_NAME('CSI','CSI_MU_XFER_DATE');
2137 FND_MESSAGE.SET_TOKEN('TRANSFER_DATE',party_rec.active_start_date);
2138 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2139 FND_MESSAGE.SET_TOKEN('CURR_DATE',sysdate);
2140 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2141 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2142 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := party_rec.txn_line_detail_id;
2143 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2144 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2145 e_indx := e_indx + 1;
2146 END IF;
2147
2148 END LOOP;
2149 debug(' Error Table Count when exiting vld_xfer_date is: '||px_mass_edit_error_tbl.count);
2150
2151 END vld_xfer_date;
2152
2153 PROCEDURE check_item_inst_loc_changed(p_txn_line_detail_id_tab IN NumTabType,
2154 p_instance_id_tab IN NumTabType,
2155 p_install_location_id_tab IN NumTabType,
2156 p_location_id_tab IN NumTabType,
2157 p_instance_status_id_tab IN NumTabType,
2158 p_external_reference_tab IN Char30TabType,
2159 p_install_date_tab IN DateTabType,
2160 p_system_id_tab IN NumTabType,
2161 px_mass_edit_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl) IS
2162
2163 e_indx NUMBER := 0;
2164
2165 CURSOR txn_det_csr (pc_txn_line_detail_id IN NUMBER) IS
2166 SELECT cil.install_location_id install_location_id
2167 ,cil.location_id location_id
2168 ,cil.instance_status_id instance_status_id
2169 ,cil.external_reference external_reference
2170 ,cil.installation_date installation_date
2171 ,cil.csi_system_id csi_system_id
2172 FROM csi_t_txn_line_details cil
2173 WHERE cil.txn_line_detail_id = pc_txn_line_detail_id
2174 AND cil.instance_id IS NOT NULL;
2175
2176 txn_det_rec txn_det_csr%rowtype;
2177
2178 BEGIN
2179 -- Set Error Table Index
2180 e_indx := nvl(px_mass_edit_error_tbl.count,0) + 1;
2181
2182 FOR ind IN 1 .. p_txn_line_detail_id_tab.COUNT LOOP
2183
2184 OPEN txn_det_csr(p_txn_line_detail_id_tab(ind));
2185 FETCH txn_det_csr INTO txn_det_rec;
2186 CLOSE txn_det_csr;
2187
2188 IF txn_det_rec.install_location_id <> p_install_location_id_tab(ind) THEN
2189 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INSTALL_LOC_CHANGED');
2190 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2191 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2192 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2193 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2194 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2195 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2196 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2197 px_mass_edit_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2198 e_indx := e_indx + 1;
2199 END IF;
2200
2201 IF txn_det_rec.location_id <> p_location_id_tab(ind) THEN
2202 FND_MESSAGE.SET_NAME('CSI','CSI_MU_CURR_LOCATION_CHANGED');
2203 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2204 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2205 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2206 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2207 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2208 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2209 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2210 px_mass_edit_error_tbl(e_indx).error_code := 'W';
2211 e_indx := e_indx + 1;
2212 END IF;
2213
2214 IF txn_det_rec.instance_status_id <> p_instance_status_id_tab(ind) THEN
2215 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INST_STATUS_CHANGED');
2216 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2217 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2218 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2219 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2220 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2221 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2222 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2223 px_mass_edit_error_tbl(e_indx).error_code := 'W';
2224 e_indx := e_indx + 1;
2225 END IF;
2226
2227 IF txn_det_rec.external_reference <> p_external_reference_tab(ind) THEN
2228 FND_MESSAGE.SET_NAME('CSI','CSI_MU_EXT_REF_CHANGED');
2229 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2230 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2231 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2232 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2233 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2234 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2235 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2236 px_mass_edit_error_tbl(e_indx).error_code := 'W';
2237 e_indx := e_indx + 1;
2238 END IF;
2239
2240 IF txn_det_rec.installation_date <> p_install_date_tab(ind) THEN
2241 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INSTALL_DATE_CHANGED');
2242 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2243 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2244 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2245 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2246 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2247 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2248 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2249 px_mass_edit_error_tbl(e_indx).error_code := 'W';
2250 e_indx := e_indx + 1;
2251 END IF;
2252
2253 IF txn_det_rec.csi_system_id <> p_system_id_tab(ind) THEN
2254 FND_MESSAGE.SET_NAME('CSI','CSI_MU_SYSTEM_CHANGED');
2255 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2256 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id_tab(ind));
2257 px_mass_edit_error_tbl(e_indx).instance_id := p_instance_id_tab(ind);
2258 px_mass_edit_error_tbl(e_indx).entry_id := g_entry_id;
2259 px_mass_edit_error_tbl(e_indx).name := g_batch_name;
2260 px_mass_edit_error_tbl(e_indx).txn_line_detail_id := p_txn_line_detail_id_tab(ind);
2261 px_mass_edit_error_tbl(e_indx).error_text := fnd_message.get;
2262 px_mass_edit_error_tbl(e_indx).error_code := 'W';
2263 e_indx := e_indx + 1;
2264 END IF;
2265
2266 END LOOP;
2267 debug(' Error Table Count when exiting check_item_inst_loc_changed is: '||px_mass_edit_error_tbl.count);
2268
2269 END check_item_inst_loc_changed;
2270
2271 PROCEDURE validate_batch (px_mass_edit_rec IN csi_mass_edit_pub.mass_edit_rec,
2272 p_mode IN VARCHAR2,
2273 x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl,
2274 x_return_status OUT NOCOPY VARCHAR2) IS
2275
2276
2277 -- p_mode Parameters
2278 -- UI = HTML UI
2279 -- CRT = Create Batch
2280 -- UPD = Update Batch
2281 -- CP = Concurrent Process
2282
2283 l_api_version NUMBER := 1.0;
2284 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_BATCH';
2285 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2286 l_msg_count NUMBER;
2287 l_msg_data VARCHAR2(2000);
2288 l_return_status VARCHAR2(1);
2289 l_mass_edit_error_tbl csi_mass_edit_pub.mass_edit_error_tbl;
2290 l_errors_found VARCHAR2(1) := 'N';
2291 l_warnings_found VARCHAR2(1) := 'N';
2292
2293 l_transaction_line_id_tab NumTabType;
2294 l_owner_party_source_table_tab Char30TabType;
2295 l_owner_party_id_tab NumTabType;
2296 l_owner_party_account_id_tab NumTabType;
2297 l_txn_line_detail_id_tab NumTabType;
2298 l_location_id_tab NumTabType;
2299 l_install_location_id_tab NumTabType;
2300 l_instance_status_id_tab NumTabType;
2301 l_external_reference_tab Char30TabType;
2302 l_system_id_tab NumTabType;
2303 l_location_type_code_tab Char30TabType;
2304 l_instance_usage_code_tab Char30TabType;
2305 l_instance_id_tab NumTabType;
2306 l_install_date_tab DateTabType;
2307
2308 MAX_BUFFER_SIZE number := 1000;
2309
2310 CURSOR inst_csr (pc_txn_line_id IN NUMBER) IS
2311 SELECT cil.transaction_line_id transaction_line_id
2312 ,cii.owner_party_source_table owner_party_source_table
2313 ,cii.owner_party_id owner_party_id
2314 ,cii.owner_party_account_id owner_party_account_id
2315 ,cil.txn_line_detail_id txn_line_detail_id
2316 ,cii.location_id location_id
2317 ,cii.install_location_id install_location_id
2318 ,cii.instance_status_id instance_status_id
2319 ,cii.external_reference external_reference
2320 ,cii.system_id system_id
2321 ,cii.location_type_code location_type_code
2322 ,cii.instance_usage_code instance_usage_code
2323 ,cii.instance_id instance_id
2324 ,cii.install_date install_date
2325 FROM csi_t_txn_line_details cil
2326 ,csi_item_instances cii
2327 WHERE cil.transaction_line_id = pc_txn_line_id
2328 AND cil.instance_id IS NOT NULL
2329 AND cil.instance_id = cii.instance_id;
2330
2331 BEGIN
2332
2333 csi_t_gen_utility_pvt.add('In VALIDATE_BATCH');
2334
2335 -- Initialize message list if p_init_msg_list is set to TRUE.
2336 IF FND_API.To_Boolean( l_init_msg_list ) THEN
2337 FND_MSG_PUB.Initialize;
2338 END IF;
2339
2340 -- Initialize API return status to succcess
2341 x_return_status := FND_API.G_RET_STS_SUCCESS;
2342
2343 -- Set the Global Batch ID
2344 g_entry_id := px_mass_edit_rec.entry_id;
2345 g_batch_name := px_mass_edit_rec.name;
2346
2347 OPEN inst_csr (px_mass_edit_rec.txn_line_id);
2348 LOOP
2349
2350 FETCH inst_csr BULK COLLECT
2351 INTO l_transaction_line_id_tab,
2352 l_owner_party_source_table_tab,
2353 l_owner_party_id_tab,
2354 l_owner_party_account_id_tab,
2355 l_txn_line_detail_id_tab,
2356 l_location_id_tab,
2357 l_install_location_id_tab,
2358 l_instance_status_id_tab,
2359 l_external_reference_tab,
2360 l_system_id_tab,
2361 l_location_type_code_tab,
2362 l_instance_usage_code_tab,
2363 l_instance_id_tab,
2364 l_install_date_tab
2365 LIMIT MAX_BUFFER_SIZE;
2366
2367 IF p_mode in ('UI','CRT','UPD','CP') THEN
2368 -- Validate that the instance is active
2369 debug('Executing vld_item_instance_active');
2370 csi_mass_edit_pvt.vld_item_instance_active (l_instance_id_tab,
2371 l_txn_line_detail_id_tab,
2372 l_mass_edit_error_tbl);
2373 END IF;
2374
2375 IF p_mode = 'CP' THEN
2376 debug('Executing vld_batch_inst_curr_owner: '||l_transaction_line_id_tab(1));
2377 -- check current owner
2378 csi_mass_edit_pvt.vld_batch_inst_curr_owner(l_transaction_line_id_tab,
2379 l_mass_edit_error_tbl);
2380 END IF;
2381
2382 IF px_mass_edit_rec.batch_type = 'XFER' THEN
2383 debug('Batch Type is transfer so call vld_batch_inst_same_owner');
2384 IF p_mode in ('UI','CRT','UPD','CP') THEN
2385 debug('Executing vld_batch_inst_same_owner');
2386 -- check owner type and owner
2387 csi_mass_edit_pvt.vld_batch_inst_same_owner(l_transaction_line_id_tab,
2388 l_mass_edit_error_tbl);
2389 END IF;
2390 END IF;
2391
2392 IF p_mode in ('UPD','CP') THEN
2393 debug('Executing vld_child_inst_location');
2394 -- check child instances
2395 csi_mass_edit_pvt.vld_child_inst_location
2396 (l_instance_id_tab,
2397 l_txn_line_detail_id_tab,
2398 l_instance_usage_code_tab,
2399 l_mass_edit_error_tbl);
2400 END IF;
2401
2402 IF p_mode in ('UI','CRT','UPD','CP') THEN
2403 debug('Executing vld_item_inst_locatoin');
2404 -- check instance location
2405 csi_mass_edit_pvt.vld_item_inst_location(l_instance_id_tab,
2406 l_transaction_line_id_tab,
2407 l_location_type_code_tab,
2408 l_location_id_tab,
2409 l_install_location_id_tab,
2410 l_instance_status_id_tab,
2411 l_mass_edit_error_tbl);
2412 END IF;
2413
2414 IF p_mode = 'CP' THEN
2415 debug('Executing check_item_inst_loc_changed');
2416 -- check instance location but throw warning NO error
2417 csi_mass_edit_pvt.check_item_inst_loc_changed(l_txn_line_detail_id_tab,
2418 l_instance_id_tab,
2419 l_install_location_id_tab,
2420 l_location_id_tab,
2421 l_instance_status_id_tab,
2422 l_external_reference_tab,
2423 l_install_date_tab,
2424 l_system_id_tab,
2425 l_mass_edit_error_tbl);
2426 END IF;
2427
2428 IF p_mode = 'CP' AND px_mass_edit_rec.batch_type = 'TRM' THEN
2429 debug('Executing vld_term_date');
2430 csi_mass_edit_pvt.vld_term_date(l_transaction_line_id_tab,
2431 l_mass_edit_error_tbl);
2432 END IF;
2433
2434 IF p_mode = 'CP' AND px_mass_edit_rec.batch_type = 'XFER' THEN
2435 debug('Executing vld_xfer_date');
2436 csi_mass_edit_pvt.vld_xfer_date(l_transaction_line_id_tab,
2437 l_mass_edit_error_tbl);
2438
2439 END IF;
2440
2441 EXIT when inst_csr%NOTFOUND;
2442 END LOOP; -- Inst
2443
2444 IF inst_csr%ISOPEN THEN
2445 CLOSE inst_csr;
2446 END IF;
2447
2448 IF l_mass_edit_error_tbl.count > 0 THEN
2449 debug('Total Number of recs being passed out in the error table from validate batch: '||l_mass_edit_error_tbl.count);
2450 FOR f in l_mass_edit_error_tbl.first .. l_mass_edit_error_tbl.last LOOP
2451 IF (l_mass_edit_error_tbl(f).error_code = fnd_api.g_ret_sts_error AND
2452 l_errors_found = 'N') THEN
2453 l_errors_found := 'Y';
2454 debug('Errors found from validate_batch');
2455 ELSIF (l_mass_edit_error_tbl(f).error_code = 'W' AND
2456 l_warnings_found = 'N') THEN
2457 l_warnings_found := 'Y';
2458 debug('Warnings found from validate_batch');
2459 END IF;
2460 END LOOP;
2461
2462 IF (l_errors_found = 'Y' and l_warnings_found = 'Y' OR
2463 l_errors_found = 'Y' and l_warnings_found = 'N') THEN
2464 debug('Errors found from validate_batch and raising FND_API.G_EXC_ERROR');
2465 RAISE FND_API.G_EXC_ERROR;
2466 ELSIF (l_errors_found = 'N' and l_warnings_found = 'Y') THEN
2467 x_return_status := 'W';
2468 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
2469 END IF;
2470 debug('Return Status from validate_batch: '||x_return_status);
2471 ELSE
2472 x_return_status := fnd_api.g_ret_sts_success;
2473 END IF;
2474
2475 EXCEPTION
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 debug('Encountered FND_API.G_EXC_ERROR in Validate_Batch');
2478 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
2479 x_return_status := FND_API.G_RET_STS_ERROR ;
2480 WHEN OTHERS THEN
2481 debug('Encountered WHEN OTHERS in Validate_Batch');
2482 x_mass_edit_error_tbl := l_mass_edit_error_tbl;
2483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2484
2485 END validate_batch;
2486
2487
2488 /*----------------------------------------------------*/
2489 /* Procedure name: VLD_SYSTEM_ACTIVE */
2490 /* Description : procedure to validate whether the */
2491 /* system is active or not */
2492 /*----------------------------------------------------*/
2493 PROCEDURE VLD_SYSTEM_ACTIVE(
2494 p_system_id IN NUMBER ,
2495 p_txn_line_id IN NUMBER,
2496 p_mu_sys_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_sys_error_tbl)
2497 IS
2498 CURSOR sys_active (p_system_id IN NUMBER) IS
2499 SELECT 1
2500 FROM CSI_SYSTEMS_B
2501 WHERE SYSTEM_ID = p_system_id
2502 AND (end_date_active is NULL OR
2503 nvl(end_date_active, sysdate+1) > sysdate);
2504
2505 l_sys_active NUMBER := NULL;
2506 e_indx NUMBER := 0;
2507
2508 BEGIN
2509
2510 -- Set Error Table Index
2511 e_indx := nvl(p_mu_sys_error_tbl.last,0) + 1;
2512
2513 OPEN sys_active(p_system_id);
2514 FETCH sys_active INTO l_sys_active;
2515 CLOSE sys_active;
2516
2517 IF l_sys_active is NULL THEN
2518 FND_MESSAGE.SET_NAME('CSI','CSI_MU_INACTIVE_SYSTEM');
2519 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2520 FND_MESSAGE.SET_TOKEN('SYSTEM_ID',p_system_id);
2521 p_mu_sys_error_tbl(e_indx).system_id := p_system_id;
2522 p_mu_sys_error_tbl(e_indx).entry_id := g_entry_id;
2523 p_mu_sys_error_tbl(e_indx).batch_name := g_batch_name;
2524 p_mu_sys_error_tbl(e_indx).txn_line_detail_id := p_txn_line_id;
2525 p_mu_sys_error_tbl(e_indx).error_text := fnd_message.get;
2526 p_mu_sys_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2527 e_indx := e_indx + 1;
2528 END IF;
2529
2530 debug('System Error Table Count after VLD_SYSTEM_ACTIVE is: '||p_mu_sys_error_tbl.count);
2531
2532 END VLD_SYSTEM_ACTIVE;
2533
2534
2535 /*----------------------------------------------------*/
2536 /* Procedure name: VLD_SYSTEM_CURRENT_OWNER */
2537 /* Description : procedure to validate current system owner */
2538 /* system is active or not */
2539 /*----------------------------------------------------*/
2540 PROCEDURE VLD_SYSTEM_CURRENT_OWNER(
2541 p_system_id IN NUMBER ,
2542 p_customer_id IN NUMBER,
2543 p_txn_line_id IN NUMBER,
2544 p_mu_sys_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_sys_error_tbl)
2545 IS
2546 CURSOR sys_customer_csr (p_system_id IN NUMBER) IS
2547 SELECT CUSTOMER_ID
2548 FROM CSI_SYSTEMS_B
2549 WHERE SYSTEM_ID = p_system_id;
2550
2551 l_customer_id NUMBER := NULL;
2552 e_indx NUMBER := 0;
2553
2554 BEGIN
2555
2556 -- Set Error Table Index
2557 e_indx := nvl(p_mu_sys_error_tbl.last,0) + 1;
2558
2559 OPEN sys_customer_csr(p_system_id);
2560 FETCH sys_customer_csr INTO l_customer_id;
2561 CLOSE sys_customer_csr;
2562
2563 IF l_customer_id <> p_customer_id THEN
2564 -- Raise Error owner values have changed.
2565 FND_MESSAGE.SET_NAME('CSI','CSI_MU_SYS_CUST_DIFF');
2566 FND_MESSAGE.SET_TOKEN('BATCH_NAME',g_batch_name);
2567 FND_MESSAGE.SET_TOKEN('SYSTEM_ID',p_system_id);
2568 p_mu_sys_error_tbl(e_indx).system_id := p_system_id;
2569 p_mu_sys_error_tbl(e_indx).entry_id := g_entry_id;
2570 p_mu_sys_error_tbl(e_indx).name := g_batch_name;
2571 p_mu_sys_error_tbl(e_indx).txn_line_detail_id := p_txn_line_id;
2572 p_mu_sys_error_tbl(e_indx).error_text := fnd_message.get;
2573 p_mu_sys_error_tbl(e_indx).error_code := fnd_api.g_ret_sts_error;
2574 e_indx := e_indx + 1;
2575 END IF;
2576
2577 debug('System Error Table Count after VLD_SYSTEM_CURRENT_OWNER is: '||p_mu_sys_error_tbl.count);
2578
2579 END VLD_SYSTEM_CURRENT_OWNER;
2580
2581 /*----------------------------------------------------*/
2582 /* Procedure name: VLD_SYSTEM_LOCATION_CHGD */
2583 /* Description : procedure to validate whether the location */
2584 /* and contact info changed */
2585 /*----------------------------------------------------*/
2586 PROCEDURE VLD_SYSTEM_LOCATION_CHGD(
2587 p_system_id IN NUMBER ,
2588 p_txn_line_id IN NUMBER,
2589 p_mu_sys_error_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_sys_error_tbl)
2590 IS
2591 BEGIN
2592
2593 -- Checking whether location id/contact id changed after batch was created
2594 -- This procedure is not implemented for the ER 6031179 as the locations will
2595 -- be cleared. But this is retained for future enhacenments
2596 -- This should check to make sure that location id hasnt changed since
2597 -- the batch was created. If it has changed an e8rror message must be
2598 -- displayed
2599
2600 NULL;
2601
2602
2603 END VLD_SYSTEM_LOCATION_CHGD;
2604
2605
2606 END CSI_MASS_EDIT_PVT;