[Home] [Help]
PACKAGE BODY: APPS.CSI_T_VLDN_ROUTINES_PVT
Source
1 PACKAGE BODY csi_t_vldn_routines_pvt AS
2 /* $Header: csivtvlb.pls 120.5.12020000.2 2012/07/04 11:12:43 sjawaji ship $ */
3
4 /*-----------------------------------------------------------*/
5 /* Procedure name: Check_Reqd_Param */
6 /* Description : To Check if the reqd parameter is passed */
7 /* Overloading the procedure to handle all the data types */
8 /*-----------------------------------------------------------*/
9
10 PROCEDURE check_reqd_param(
11 p_value IN NUMBER,
12 p_param_name IN VARCHAR2,
13 p_api_name IN VARCHAR2)
14 IS
15 BEGIN
16
17 IF (NVL(p_value,FND_API.g_miss_num) = FND_API.g_miss_num) THEN
18
19 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
20 FND_MESSAGE.set_token('API_NAME',p_api_name);
21 FND_MESSAGE.set_token('MISSING_PARAM',p_param_name);
22 FND_MSG_PUB.add;
23 RAISE FND_API.g_exc_error;
24
25 END IF;
26
27 END Check_Reqd_Param;
28
29 PROCEDURE Check_Reqd_Param(
30 p_value IN VARCHAR2,
31 p_param_name IN VARCHAR2,
32 p_api_name IN VARCHAR2)
33 IS
34 BEGIN
35
36 IF (NVL(p_value,FND_API.g_miss_char) = FND_API.g_miss_char) THEN
37
38 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
39 FND_MESSAGE.set_token('API_NAME',p_api_name);
40 FND_MESSAGE.set_token('MISSING_PARAM',p_param_name);
41 FND_MSG_PUB.add;
42 RAISE FND_API.g_exc_error;
43
44 END IF;
45
46 END Check_Reqd_Param;
47
48 PROCEDURE Check_Reqd_Param(
49 p_value IN DATE,
50 p_param_name IN VARCHAR2,
51 p_api_name IN VARCHAR2)
52 IS
53 BEGIN
54
55 IF (NVL(p_value,FND_API.g_miss_date) = FND_API.g_miss_date) THEN
56
57 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
58 FND_MESSAGE.set_token('API_NAME',p_api_name);
59 FND_MESSAGE.set_token('MISSING_PARAM',p_param_name);
60 FND_MSG_PUB.add;
61 RAISE FND_API.g_exc_error;
62
63 END IF;
64
65 END Check_Reqd_Param;
66
67 /* Validate transaction line id */
68 PROCEDURE validate_transaction_line_id (
69 p_transaction_line_id IN NUMBER,
70 x_transaction_line_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
71 x_return_status OUT NOCOPY VARCHAR2)
72 IS
73 l_found VARCHAR2(1);
74 -- SELECT 'X' INTO l_found -- changed for Mass update R12
75 Cursor txn_line_cur (p_txn_line_id IN Number) is
76 SELECT transaction_line_id,
77 source_transaction_type_id,
78 source_transaction_id,
79 source_transaction_table,
80 source_txn_header_id,
81 processing_status,
82 error_code,
83 error_explanation,
84 config_session_hdr_id,
85 config_session_item_id,
86 config_session_rev_num,
87 config_valid_status
88 FROM csi_t_transaction_lines
89 where transaction_line_id = p_txn_line_id;
90
91 l_txn_line_rec txn_line_cur%rowtype;
92 BEGIN
93 x_return_status := fnd_api.g_ret_sts_success;
94
95 Open txn_line_cur(p_transaction_line_id) ;
96 Fetch txn_line_cur Into l_txn_line_rec;
97
98 IF NOT txn_line_cur%FOUND THEN
99 x_return_status := fnd_api.g_ret_sts_error;
100 END IF;
101
102 Close txn_line_cur;
103
104 x_transaction_line_rec.transaction_line_id := l_txn_line_rec.transaction_line_id;
105 x_transaction_line_rec.source_transaction_type_id := l_txn_line_rec.source_transaction_type_id;
106 x_transaction_line_rec.source_transaction_id := l_txn_line_rec.source_transaction_id;
107 x_transaction_line_rec.source_transaction_table := l_txn_line_rec.source_transaction_table;
108 x_transaction_line_rec.source_txn_header_id := l_txn_line_rec.source_txn_header_id;
109 x_transaction_line_rec.processing_status := l_txn_line_rec.processing_status;
110 x_transaction_line_rec.error_code := l_txn_line_rec.error_code;
111 x_transaction_line_rec.error_explanation := l_txn_line_rec.error_explanation;
112 x_transaction_line_rec.config_session_hdr_id := l_txn_line_rec.config_session_hdr_id;
113 x_transaction_line_rec.config_session_item_id := l_txn_line_rec.config_session_item_id;
114 x_transaction_line_rec.config_session_rev_num := l_txn_line_rec.config_session_rev_num;
115 x_transaction_line_rec.config_valid_status := l_txn_line_rec.config_valid_status;
116
117 EXCEPTION
118 WHEN others THEN
119 x_return_status := fnd_api.g_ret_sts_error;
120 END validate_transaction_line_id;
121
122
123 /* Validate txn_line_detail_id */
124 PROCEDURE validate_txn_line_detail_id(
125 p_txn_line_detail_id IN NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2)
127 IS
128 l_found varchar2(1);
129 BEGIN
130
131 SELECT 'X'
132 INTO l_found
133 FROM csi_t_txn_line_details
134 WHERE txn_line_detail_id = p_txn_line_detail_id;
135
136 x_return_status := fnd_api.g_ret_sts_success;
137
138 EXCEPTION
139 WHEN no_data_found THEN
140 x_return_status := fnd_api.g_ret_sts_error;
141 END validate_txn_line_detail_id;
142
143 /* Validate txn_line_detail_id */ -- Added this overloaded routine for M-M
144 PROCEDURE validate_txn_line_detail_id(
145 p_txn_line_detail_id IN NUMBER,
146 x_txn_line_detail_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
147 x_return_status OUT NOCOPY VARCHAR2)
148 IS
149 Cursor txn_line_dtl_cur (p_txn_line_dtl_id IN Number) IS
150 SELECT txn_line_detail_id,
151 transaction_line_id,
152 source_transaction_flag,
153 instance_id,
154 location_type_code,
155 location_id,
156 active_start_date,
157 active_end_date,
158 changed_instance_id,
159 source_txn_line_detail_id,
160 processing_status,
161 config_inst_hdr_id,
162 config_inst_rev_num,
163 config_inst_item_id
164 FROM csi_t_txn_line_details
165 WHERE txn_line_detail_id = p_txn_line_dtl_id;
166
167 l_txn_line_detail_rec txn_line_dtl_cur%rowtype;
168 BEGIN
169 x_return_status := fnd_api.g_ret_sts_success;
170
171 Open txn_line_dtl_cur (p_txn_line_detail_id);
172 Fetch txn_line_dtl_cur into l_txn_line_detail_rec;
173
174 IF NOT txn_line_dtl_cur%FOUND THEN
175 x_return_status := fnd_api.g_ret_sts_error;
176 END IF;
177
178 Close txn_line_dtl_cur;
179
180 x_txn_line_detail_rec.txn_line_detail_id := l_txn_line_detail_rec.txn_line_detail_id;
181 x_txn_line_detail_rec.transaction_line_id := l_txn_line_detail_rec.transaction_line_id;
182 x_txn_line_detail_rec.source_transaction_flag := l_txn_line_detail_rec.source_transaction_flag;
183 x_txn_line_detail_rec.instance_id := l_txn_line_detail_rec.instance_id;
184 x_txn_line_detail_rec.location_type_code := l_txn_line_detail_rec.location_type_code;
185 x_txn_line_detail_rec.location_id := l_txn_line_detail_rec.location_id;
186 x_txn_line_detail_rec.active_start_date := l_txn_line_detail_rec.active_start_date;
187 x_txn_line_detail_rec.active_end_date := l_txn_line_detail_rec.active_end_date;
188 x_txn_line_detail_rec.changed_instance_id := l_txn_line_detail_rec.changed_instance_id;
189 x_txn_line_detail_rec.source_txn_line_detail_id := l_txn_line_detail_rec.source_txn_line_detail_id;
190 x_txn_line_detail_rec.processing_status := l_txn_line_detail_rec.processing_status;
191 x_txn_line_detail_rec.config_inst_hdr_id := l_txn_line_detail_rec.config_inst_hdr_id;
192 x_txn_line_detail_rec.config_inst_rev_num := l_txn_line_detail_rec.config_inst_rev_num;
193 x_txn_line_detail_rec.config_inst_item_id := l_txn_line_detail_rec.config_inst_item_id;
194
195 EXCEPTION
196 WHEN others THEN
197 x_return_status := fnd_api.g_ret_sts_error;
198 END validate_txn_line_detail_id;
199
200 /* Validate txn_party_detail_id */
201 PROCEDURE validate_txn_party_detail_id(
202 p_txn_party_detail_id IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2)
204 IS
205 l_found varchar2(1);
206 BEGIN
207
208 SELECT 'X'
209 INTO l_found
210 FROM csi_t_party_details
211 WHERE txn_party_detail_id = p_txn_party_detail_id;
212
213 x_return_status := fnd_api.g_ret_sts_success;
214
215 EXCEPTION
216 WHEN no_data_found THEN
217 x_return_status := fnd_api.g_ret_sts_error;
218 WHEN others then
219 x_return_status := fnd_api.g_ret_sts_error;
220 END validate_txn_party_detail_id;
221
222
223 /* validate transaction party account detail id */
224 PROCEDURE validate_txn_acct_detail_id(
225 p_txn_acct_detail_id IN NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2)
227 IS
228 l_found varchar2(1);
229 BEGIN
230
231 SELECT 'X'
232 INTO l_found
233 FROM csi_t_party_accounts
234 WHERE txn_account_detail_id = p_txn_acct_detail_id;
235
236 x_return_status := fnd_api.g_ret_sts_success;
237
238 EXCEPTION
239 WHEN no_data_found THEN
240 x_return_status := fnd_api.g_ret_sts_error;
241 WHEN others then
242 x_return_status := fnd_api.g_ret_sts_error;
243 END validate_txn_acct_detail_id;
244
245
246 /* validate relationship id */
247 PROCEDURE validate_txn_relationship_id(
248 p_txn_relationship_id IN NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2)
250 IS
251 l_found varchar2(1);
252 BEGIN
253
254 SELECT 'X'
255 INTO l_found
256 FROM csi_t_ii_relationships
257 WHERE txn_relationship_id = p_txn_relationship_id;
258
259 x_return_status := fnd_api.g_ret_sts_success;
260
261 EXCEPTION
262 WHEN no_data_found THEN
263 x_return_status := fnd_api.g_ret_sts_error;
264
265 END validate_txn_relationship_id;
266
267 /* validate transaction operating unit_id */
268 PROCEDURE validate_txn_ou_id(
269 p_txn_operating_unit_id IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2)
271 IS
272 l_found varchar2(1);
273 BEGIN
274
275 SELECT 'X'
276 INTO l_found
277 FROM csi_t_org_assignments
278 WHERE txn_operating_unit_id = p_txn_operating_unit_id;
279
280 x_return_status := fnd_api.g_ret_sts_success;
281
282 EXCEPTION
283 WHEN no_data_found THEN
284 x_return_status := fnd_api.g_ret_sts_error;
285
286 END validate_txn_ou_id;
287
288
289 /* validate the transaction attrib detail id */
290 PROCEDURE validate_txn_attrib_detail_id(
291 p_txn_attrib_detail_id IN NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2)
293 IS
294 l_found varchar2(1);
295 BEGIN
296
297 SELECT 'X'
298 INTO l_found
299 FROM csi_t_extend_attribs
300 WHERE txn_attrib_detail_id = p_txn_attrib_detail_id;
301
302 x_return_status := fnd_api.g_ret_sts_success;
303
304 EXCEPTION
305 WHEN no_data_found THEN
306 x_return_status := fnd_api.g_ret_sts_error;
307
308 END validate_txn_attrib_detail_id;
309
310 /* Validate the txn_source_id */
311 PROCEDURE validate_txn_source_id(
312 p_txn_source_name IN VARCHAR2,
313 p_txn_source_id IN NUMBER,
314 x_return_status OUT NOCOPY VARCHAR2)
315 IS
316 l_found VARCHAR2(1);
317 BEGIN
318
319 IF p_txn_source_name = 'ORDER_ENTRY' THEN
320
321 SELECT 'X'
322 INTO l_found
323 FROM oe_order_lines_all
324 WHERE line_id = p_txn_source_id;
325
326 x_return_status := fnd_api.g_ret_sts_success;
327
328 END IF;
329
330 EXCEPTION
331 WHEN no_data_found THEN
332 x_return_status := fnd_api.g_ret_sts_error;
333 WHEN others THEN
334 x_return_status := fnd_api.g_ret_sts_error;
335
336 END validate_txn_source_id;
337
338
339 /* check whether the TD has been converted in to IB */
340 PROCEDURE check_ib_creation(
341 p_transaction_line_id IN NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2)
343 IS
344
345 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
346 l_processed_found BOOLEAN := FALSE;
347
348 CURSOR proc_cur IS
349 SELECT 'X'
350 FROM csi_t_txn_line_details
351 WHERE transaction_line_id = p_transaction_line_id
352 AND (csi_transaction_id is not null
353 OR
354 processing_status = 'PROCESSED');
355
356 BEGIN
357
358 SELECT processing_status
359 INTO l_processing_status
360 FROM csi_t_transaction_lines
361 WHERE transaction_line_id = p_transaction_line_id;
362
363 FOR proc_rec in proc_cur
364 LOOP
365 l_processed_found := TRUE;
366 END LOOP;
367
368 IF (l_processing_status = 'PROCESSED') OR (l_processed_found = TRUE) THEN
369 x_return_status := fnd_api.g_true;
370 ELSE
371 x_return_status := fnd_api.g_false;
372 END IF;
373
374 END check_ib_creation;
375
376
377 /* Validate subject_id */
378 PROCEDURE validate_subject_id(
379 p_subject_id IN NUMBER,
380 p_txn_line_dtl_id IN NUMBER,
381 x_return_status OUT NOCOPY VARCHAR2)
382 IS
383
384 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
385 l_instance_id csi_t_txn_line_details.instance_id%TYPE;
386 l_inventory_item_id csi_t_txn_line_details.inventory_item_id%TYPE;
387 l_inv_organization_id csi_t_txn_line_details.inv_organization_id%TYPE;
388
389 l_subject_id NUMBER;
390
391 BEGIN
392
393 x_return_status := fnd_api.g_ret_sts_success;
394
395 SELECT instance_exists_flag,
396 instance_id,
397 inventory_item_id,
398 inv_organization_id
399 INTO l_instance_exists_flag,
400 l_instance_id,
401 l_inventory_item_id,
402 l_inv_organization_id
403 FROM csi_t_txn_line_details
404 WHERE txn_line_detail_id = p_txn_line_dtl_id;
405
406 IF l_instance_exists_flag = 'Y' THEN
407 l_subject_id := l_instance_id;
408 ELSE
409 l_subject_id := l_inventory_item_id;
410 END IF;
411
412 IF p_subject_id <> l_subject_id THEN
413 x_return_status := fnd_api.g_ret_sts_error;
414 END IF;
415
416 EXCEPTION
417 WHEN no_data_found THEN
418 x_return_status := fnd_api.g_ret_sts_error;
419 END validate_subject_id;
420
421
422 PROCEDURE validate_object_id(
423 p_object_id IN NUMBER,
424 x_return_status OUT NOCOPY varchar2)
425 IS
426 l_found VARCHAR2(1);
427 BEGIN
428
429 SELECT 'X'
430 INTO l_found
431 FROM csi_item_instances
432 WHERE instance_id = p_object_id;
433
434 x_return_status := fnd_api.g_ret_sts_success;
435
436 EXCEPTION
437 WHEN no_data_found THEN
438 x_return_status := fnd_api.g_ret_sts_error;
439 END validate_object_id;
440
441 /* validate relationship type code for item instances */
442 PROCEDURE validate_ii_rltns_type_code(
443 p_rltns_type_code IN VARCHAR2,
444 x_return_status OUT NOCOPY VARCHAR2)
445 IS
446 v_found varchar2(1);
447 BEGIN
448
449 SELECT 'X'
450 INTO v_found
451 FROM csi_ii_relation_types
452 WHERE relationship_type_code = p_rltns_type_code;
453
454 x_return_status := fnd_api.g_ret_sts_success;
455
456 EXCEPTION
457 WHEN no_data_found THEN
458 x_return_status := fnd_api.g_ret_sts_error;
459
460 END validate_ii_rltns_type_code;
461
462 /* validate instance party id from csi_i_parties */
463 PROCEDURE validate_instance_party_id(
464 p_instance_id IN number,
465 p_instance_party_id IN number,
466 x_return_status OUT NOCOPY VARCHAR2)
467 IS
468 v_found varchar2(1);
469 BEGIN
470
471 SELECT 'X'
472 INTO v_found
473 FROM csi_i_parties
474 WHERE instance_id = p_instance_id
475 AND instance_party_id = p_instance_party_id;
476
477 x_return_status := fnd_api.g_ret_sts_success;
478
479 EXCEPTION
480 WHEN no_data_found THEN
481 x_return_status := fnd_api.g_ret_sts_error;
482
483 END validate_instance_party_id;
484
485 PROCEDURE validate_ip_account_id(
486 p_ip_account_id IN number,
487 x_return_status OUT NOCOPY varchar2)
488 IS
489 l_found varchar2(1);
490 BEGIN
491
492 SELECT 'X'
493 INTO l_found
494 FROM csi_ip_accounts
495 WHERE ip_account_id = p_ip_account_id;
496
497 x_return_status := fnd_api.g_ret_sts_success;
498
499 EXCEPTION
500 WHEN no_data_found THEN
501 x_return_status := fnd_api.g_ret_sts_error;
502 END validate_ip_account_id;
503
504 PROCEDURE validate_instance_ou_id(
505 p_instance_id IN number,
506 p_instance_ou_id IN number,
507 x_return_status OUT NOCOPY varchar2)
508 IS
509 l_found varchar2(1);
510 BEGIN
511
512 SELECT 'X'
513 INTO l_found
514 FROM csi_i_org_assignments
515 WHERE instance_id = p_instance_id
516 AND instance_ou_id = p_instance_ou_id;
517
518 x_return_status := fnd_api.g_ret_sts_success;
519
520 EXCEPTION
521 WHEN no_data_found THEN
522 x_return_status := fnd_api.g_ret_sts_error;
523 END validate_instance_ou_id;
524
525 PROCEDURE validate_instance_rltns_id(
526 p_csi_inst_rltns_id IN number,
527 x_object_id OUT NOCOPY number,
528 x_return_status OUT NOCOPY varchar2)
529 IS
530 BEGIN
531
532 SELECT object_id
533 INTO x_object_id
534 FROM csi_ii_relationships
535 WHERE relationship_id = p_csi_inst_rltns_id;
536
537 x_return_status := fnd_api.g_ret_sts_success;
538
539 EXCEPTION
540 WHEN no_data_found THEN
541 x_return_status := fnd_api.g_ret_sts_error;
542
543 END validate_instance_rltns_id;
544
545 /* integrity check for the source transaction */
546 PROCEDURE check_source_integrity(
547 p_validation_level IN varchar2,
548 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
549 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
550 x_return_status OUT NOCOPY VARCHAR2)
551 IS
552
553 l_total_quantity NUMBER;
554 l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
555
556 l_src_item_id mtl_system_items.inventory_item_id%TYPE;
557 l_src_organization_id mtl_parameters.organization_id%TYPE;
558 l_src_quantity NUMBER;
559 l_src_uom_code VARCHAR2(3);
560 l_src_primary_qty NUMBER;
561 l_mo_org_id oe_order_lines_all.org_id%type;
562
563 l_inst_item_id csi_item_instances.inventory_item_id%type;
564 l_inst_organization_id csi_item_instances.inv_organization_id%type;
565 l_inst_quantity csi_item_instances.quantity%type;
566 l_inst_uom_code csi_item_instances.unit_of_measure%type;
567
568 l_dtl_item_id csi_t_txn_line_details.inventory_item_id%type;
569 l_dtl_organization_id csi_t_txn_line_details.inv_organization_id%type;
570 l_dtl_quantity csi_t_txn_line_details.quantity%type;
571 l_dtl_uom_code csi_t_txn_line_details.unit_of_measure%type;
572 l_dtl_primary_qty NUMBER;
573
574 l_src_param_rec csi_t_ui_pvt.txn_source_param_rec;
575 l_src_rec csi_t_ui_pvt.txn_source_rec;
576 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
577 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
578 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
579 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
580 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
581 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
582
583 BEGIN
584
585 l_total_quantity := 0;
586
587
588 l_src_param_rec.standalone_mode := 'Y';
589 l_src_param_rec.source_transaction_type_id := p_txn_line_rec.source_transaction_type_id;
590 l_src_param_rec.source_transaction_table := p_txn_line_rec.source_transaction_table;
591 l_src_param_rec.source_transaction_id := p_txn_line_rec.source_transaction_id;
592
593 csi_t_utilities_pvt.get_source_dtls(
594 p_txn_source_param_rec => l_src_param_rec,
595 x_txn_source_rec => l_src_rec,
596 x_txn_line_rec => l_txn_line_rec,
597 x_txn_line_detail_tbl => l_line_dtl_tbl,
598 x_txn_party_detail_tbl => l_pty_dtl_tbl,
599 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
600 x_txn_org_assgn_tbl => l_org_assgn_tbl,
601 x_return_status => l_return_status);
602
603 IF l_return_status <> fnd_api.g_ret_sts_success THEN
604 RAISE fnd_api.g_exc_error;
605 END IF;
606
607 l_src_organization_id := l_src_rec.organization_id;
608 l_src_item_id := l_src_rec.inventory_item_id;
609 l_src_uom_code := l_src_rec.source_uom;
610 l_src_quantity := l_src_rec.source_quantity;
611 l_primary_uom_code := l_src_rec.primary_uom;
612
613 -- if the source uom is not the primary uom then convert it to primary
614
615 IF l_src_uom_code <> l_primary_uom_code THEN
616
617 csi_t_gen_utility_pvt.add('Converting to Primary UOM.');
618
619 l_src_primary_qty :=
620 inv_convert.inv_um_convert(
621 item_id => l_src_item_id,
622 precision => 6,
623 from_quantity => l_src_quantity,
624 from_unit => l_src_uom_code,
625 to_unit => l_primary_uom_code,
626 from_name => null,
627 to_name => null);
628
629 l_src_quantity := l_src_primary_qty;
630
631 END IF;
632
633 IF p_txn_line_dtl_tbl.COUNT > 0 THEN
634
635 FOR l_index IN p_txn_line_dtl_tbl.FIRST..p_txn_line_dtl_tbl.LAST
636 LOOP
637
638 l_dtl_item_id := p_txn_line_dtl_tbl(l_index).inventory_item_id;
639 l_dtl_organization_id := p_txn_line_dtl_tbl(l_index).
640 inv_organization_id;
641 l_dtl_uom_code := p_txn_line_dtl_tbl(l_index).unit_of_measure;
642 l_dtl_quantity := p_txn_line_dtl_tbl(l_index).quantity;
643
644 /* check source item with the instance or the txn line detail item */
645 IF p_txn_line_dtl_tbl(l_index).instance_exists_flag = 'Y' THEN
646
647 BEGIN
648 --get the instance info like the item, org and quantity
649 SELECT inventory_item_id,
650 inv_master_organization_id,
651 unit_of_measure,
652 quantity
653 INTO l_inst_item_id,
654 l_inst_organization_id,
655 l_inst_uom_code,
656 l_inst_quantity
657 FROM csi_item_instances
658 WHERE instance_id = p_txn_line_dtl_tbl(l_index).instance_id;
659
660 l_dtl_item_id := l_inst_item_id;
661 l_dtl_organization_id := l_inst_organization_id;
662
663 EXCEPTION
664 WHEN no_data_found THEN
665 fnd_message.set_name('CSI', 'CSI_API_INVALID_INSTANCE_ID');
666 fnd_message.set_token('INSTANCE_ID',
667 p_txn_line_dtl_tbl(l_index).instance_id);
668 fnd_msg_pub.add;
669 RAISE fnd_api.g_exc_error;
670 END;
671
672 END IF;
673
674 ---Added (Start) for m-to-m enhancements
675 ---For non Source lines , item id will not match
676 ---with order item , so do the following validation
677 ---for source lines only.
678 IF p_txn_line_dtl_tbl(l_index).source_transaction_flag = 'Y'
679 THEN
680 IF l_dtl_item_id <> l_src_item_id THEN
681 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_ITEM_CHK_FAILED');
682 FND_MESSAGE.set_token('SRC_ITEM_ID',l_src_item_id);
683 FND_MESSAGE.set_token('DTL_ITEM_ID',l_dtl_item_id);
684 FND_MESSAGE.set_token('SRC_NAME',p_txn_line_rec.source_transaction_table);
685 FND_MESSAGE.set_token('SRC_LINE_ID',p_txn_line_rec.source_transaction_id);
686 FND_MSG_PUB.add;
687 RAISE fnd_api.g_exc_error;
688 END IF;
689 END IF ;
690 ---Added (End) for m-to-m enhancements
691
692 /* check the td quantity with the source qty */
693 /* sum up the quantity only if txn detail is source based */
694 IF p_txn_line_dtl_tbl(l_index).source_transaction_flag = 'Y' THEN
695
696 IF l_dtl_uom_code <> l_primary_uom_code
697 THEN
698
699 l_dtl_primary_qty :=
700 inv_convert.inv_um_convert(
701 item_id => l_dtl_item_id,
702 precision => 6,
703 from_quantity => l_dtl_quantity,
704 from_unit => l_dtl_uom_code,
705 to_unit => l_primary_uom_code,
706 from_name => null,
707 to_name => null);
708
709 l_total_quantity := l_total_quantity + l_dtl_primary_qty;
710
711 ELSE
712
713 l_total_quantity := l_total_quantity +
714 p_txn_line_dtl_tbl(l_index).quantity;
715 END IF;
716
717 END IF; -- quantity chk for td with source txn flag - 'Y'
718
719
720 /* check the location_type_code for the source */
721 IF p_txn_line_rec.source_transaction_table = 'OE_ORDER_LINES_ALL' THEN
722
723 IF nvl(p_txn_line_dtl_tbl(l_index).location_id , fnd_api.g_miss_num) <>
724 fnd_api.g_miss_num
725 THEN
726
727 IF p_txn_line_dtl_tbl(l_index).location_type_code <> 'HZ_PARTY_SITES'
728 THEN
729
730 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_LOC_INVALID');
731 FND_MESSAGE.set_token('SRC_NAME',p_txn_line_rec.source_transaction_table);
732 FND_MESSAGE.set_token('LOC_code', p_txn_line_dtl_tbl(l_index).
733 location_type_code);
734
735 FND_MSG_PUB.add;
736 RAISE fnd_api.g_exc_error;
737
738 END IF;
739 END IF;
740 ELSE
741 IF nvl(p_txn_line_dtl_tbl(l_index).location_id, fnd_api.g_miss_num)<>
742 fnd_api.g_miss_num THEN
743 FND_MESSAGE.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
744 FND_MESSAGE.set_token('VALUE', p_txn_line_dtl_tbl(l_index).
745 location_id);
746 FND_MESSAGE.set_token('PARAM','LOCATION_ID');
747 FND_MESSAGE.set_token('REASON','This is not required for '||
748 'this transaction type '||p_txn_line_rec.source_transaction_table);
749 FND_MSG_PUB.add;
750
751 END IF;
752 END IF;
753 END LOOP;
754 -- Added filter criteria for handling RMA cases bug 4244887
755 IF nvl(p_txn_line_rec.source_transaction_type_id,0) <> 53 and l_total_quantity <> l_src_quantity THEN
756
757 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_QTY_CHK_FAILED');
758 FND_MESSAGE.set_token('SRC_LINE_ID',p_txn_line_rec.source_transaction_id);
759 FND_MESSAGE.set_token('SRC_NAME',p_txn_line_rec.source_transaction_table);
760 FND_MESSAGE.set_token('SRC_QTY',l_src_quantity);
761 FND_MESSAGE.set_token('DTL_QTY',l_total_quantity);
762 FND_MSG_PUB.add;
763 IF p_validation_level = fnd_api.g_valid_level_full THEN
764 RAISE fnd_api.g_exc_error;
765 END IF;
766 END IF;
767
768 END IF;
769 EXCEPTION
770 WHEN fnd_api.g_exc_error THEN
771 x_return_status := fnd_api.g_ret_sts_error;
772 END check_source_integrity;
773
774 /* This procedure checks for the validity of the party details supplied */
775 /* Only one owner should be defined for a txn detail record (Instance) */
776 /* Having multiple owner is an error condition */
777
778 PROCEDURE check_party_integrity(
779 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
780 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
781 p_party_dtl_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
782 x_return_status OUT NOCOPY VARCHAR2)
783 IS
784 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
785 l_owner_count number;
786 l_sub_type_rec csi_txn_sub_types%rowtype;
787 l_serial_control_code mtl_system_items.serial_number_control_code%TYPE; -- Added for Bug 13896396
788 BEGIN
789
790 x_return_status := fnd_api.g_ret_sts_success;
791
792 --loop thru line detail table
793 IF p_txn_line_dtl_tbl.COUNT > 0 THEN
794 FOR l_td_ind IN p_txn_line_dtl_tbl.FIRST.. p_txn_line_dtl_tbl.LAST
795 LOOP
796
797 csi_t_vldn_routines_pvt.check_reqd_param(
798 p_value => p_txn_line_dtl_tbl(l_td_ind).sub_type_id,
799 p_param_name => 'p_txn_line_dtl_rec.sub_type_id',
800 p_api_name => 'check_party_integrity');
801
802 BEGIN
803
804 SELECT src_change_owner,
805 non_src_change_owner
806 INTO l_sub_type_rec.src_change_owner,
807 l_sub_type_rec.non_src_change_owner
808 FROM csi_txn_sub_types
809 WHERE sub_type_id = p_txn_line_dtl_tbl(l_td_ind).sub_type_id
810 AND transaction_type_id = p_txn_line_rec.source_transaction_type_id;
811
812 EXCEPTION
813 WHEN no_data_found THEN
814
815 FND_MESSAGE.set_name('CSI','CSI_TXN_SUB_TYPE_ID_INVALID');
816 FND_MESSAGE.set_token('SUB_TYPE_ID',p_txn_line_dtl_tbl(l_td_ind).sub_type_id);
817 FND_MSG_PUB.add;
818 RAISE FND_API.g_exc_error;
819
820 END;
821
822 l_owner_count := 0;
823
824 IF p_party_dtl_tbl.COUNT > 0 THEN
825 FOR l_index IN p_party_dtl_tbl.FIRST .. p_party_dtl_tbl.LAST
826 LOOP
827 IF p_party_dtl_tbl(l_index).txn_line_details_index = l_td_ind
828 THEN
829 IF p_party_dtl_tbl(l_index).relationship_type_code = 'OWNER' THEN
830 l_owner_count := l_owner_count + 1;
831 END IF;
832 END IF;
833
834 END LOOP;
835 END IF;
836
837 IF (l_owner_count > 1) THEN
838 FND_MESSAGE.set_name('CSI','CSI_TXN_MULTIPLE_OWNER');
839 FND_MESSAGE.set_token('INDEX',l_td_ind);
840 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
841 inventory_item_id);
842 FND_MSG_PUB.add;
843 RAISE fnd_api.g_exc_error;
844 END IF;
845
846 -- Added for Bug 13896396
847 BEGIN
848 SELECT serial_number_control_code
849 INTO l_serial_control_code
850 FROM mtl_system_items
851 WHERE inventory_item_id = p_txn_line_dtl_tbl(l_td_ind).inventory_item_id
852 AND organization_id = p_txn_line_dtl_tbl(l_td_ind).INV_ORGANIZATION_ID;
853 EXCEPTION
854
855 WHEN no_data_found THEN
856
857 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
858 fnd_message.set_token('INVENTORY_ITEM_ID',p_txn_line_dtl_tbl(l_td_ind).inventory_item_id);
859 fnd_message.set_token('INV_ORGANIZATION_ID',p_txn_line_dtl_tbl(l_td_ind).INV_ORGANIZATION_ID);
860 fnd_msg_pub.add;
861
862 RAISE fnd_api.g_exc_error;
863 END;
864
865
866 IF (p_txn_line_rec.source_transaction_type_id <> 53 OR (p_txn_line_rec.source_transaction_type_id = 53
867 AND NOT csi_Item_Instance_Vld_pvt.Is_treated_serialized(p_serial_control_code => l_serial_control_code ,
868 p_location_type_code => p_txn_line_dtl_tbl(l_td_ind).location_type_code ,p_transaction_type_id => p_txn_line_rec.source_transaction_type_id)))
869 THEN
870 -- End of Bug 13896396
871
872 IF p_txn_line_dtl_tbl(l_td_ind).source_transaction_flag = 'Y' THEN
873 IF l_sub_type_rec.src_change_owner = 'Y' THEN
874
875 IF (l_owner_count = 0) THEN
876 FND_MESSAGE.set_name('CSI','CSI_TXN_OWNER_NOT_FOUND');
877 FND_MESSAGE.set_token('INDEX',l_td_ind);
878 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
879 inventory_item_id);
880 FND_MSG_PUB.add;
881 RAISE fnd_api.g_exc_error;
882 END IF;
883
884 END IF;
885 ELSE
886 IF l_sub_type_rec.non_src_change_owner = 'Y' THEN
887
888 IF (l_owner_count = 0) THEN
889 FND_MESSAGE.set_name('CSI','CSI_TXN_OWNER_NOT_FOUND');
890 FND_MESSAGE.set_token('INDEX',l_td_ind);
891 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
892 inventory_item_id);
893 FND_MSG_PUB.add;
894 RAISE fnd_api.g_exc_error;
895 END IF;
896
897 END IF;
898 END IF;
899 END IF;
900 END LOOP;
901 END IF;
902
903 EXCEPTION
904 WHEN fnd_api.g_exc_error THEN
905 x_return_status := fnd_api.g_ret_sts_error;
906
907 END check_party_integrity;
908
909 /* private routine used within check_rltns_integrity */ -- Modified this routine to address M-M changes
910 procedure get_iir_details(
911 p_sub_obj_id IN NUMBER,
912 p_object_yn IN varchar2,
913 p_iir_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
914 x_src_dtl_count OUT NOCOPY NUMBER,
915 x_return_status OUT NOCOPY varchar2,
916 x_iir_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl)
917 IS
918 l_loc_ind binary_integer;
919 l_comp_of_count number;
920
921 BEGIN
922 l_loc_ind := 0;
923 l_comp_of_count := 0;
924 x_src_dtl_count := 0;
925 x_return_status := fnd_api.g_ret_sts_success;
926
927 IF p_iir_tbl.COUNT > 0 THEN
928 FOR l_ind IN p_iir_tbl.FIRST .. p_iir_tbl.LAST
929 LOOP
930 IF p_object_yn = 'N' THEN
931
932 IF p_iir_tbl(l_ind).subject_id = p_sub_obj_id THEN
933 IF ( p_iir_tbl(l_ind).subject_type = p_iir_tbl(l_ind).object_type
934 AND p_iir_tbl(l_ind).subject_type = 'I' ) THEN --atleast one of them should be 'T'
935
936 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
937 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
938 FND_MSG_PUB.add;
939 x_return_status := fnd_api.g_ret_sts_error;
940 exit;
941 END IF;
942
943 IF p_iir_tbl(l_ind).relationship_type_code = 'COMPONENT-OF' THEN
944 l_comp_of_count := l_comp_of_count + 1;
945 END IF;
946
947 IF p_iir_tbl(l_ind).object_type = 'T' THEN
948 x_src_dtl_count := x_src_dtl_count + 1; -- count to check if a Non-Source atleast has one TLD tied to it
949 END IF;
950
951 l_loc_ind := l_loc_ind + 1;
952 x_iir_tbl(l_loc_ind).csi_inst_relationship_id :=
953 p_iir_tbl(l_ind).csi_inst_relationship_id;
954 x_iir_tbl(l_loc_ind).subject_id :=
955 p_iir_tbl(l_ind).subject_id;
956 x_iir_tbl(l_loc_ind).object_id :=
957 p_iir_tbl(l_ind).object_id;
958 x_iir_tbl(l_loc_ind).relationship_type_code :=
959 p_iir_tbl(l_ind).relationship_type_code;
960 x_iir_tbl(l_loc_ind).subject_type :=
961 p_iir_tbl(l_ind).subject_type;
962 x_iir_tbl(l_loc_ind).object_type :=
963 p_iir_tbl(l_ind).object_type;
964 x_iir_tbl(l_loc_ind).object_index_flag :=
965 p_iir_tbl(l_ind).object_index_flag;
966 x_iir_tbl(l_loc_ind).subject_index_flag :=
967 p_iir_tbl(l_ind).subject_index_flag;
968 END IF;
969 ELSE -- p_object_yn = 'Y'; repeat the same above
970 IF p_iir_tbl(l_ind).object_id = p_sub_obj_id THEN
971
972 IF ( ( p_iir_tbl(l_ind).subject_type not in ('T','I') ) OR
973 ( p_iir_tbl(l_ind).object_type not in ('T','I') ) ) THEN
974 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
975 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
976 FND_MSG_PUB.add;
977 x_return_status := fnd_api.g_ret_sts_error;
978 exit;
979 ELSIF ( ( p_iir_tbl(l_ind).subject_type = p_iir_tbl(l_ind).object_type) AND
980 (p_iir_tbl(l_ind).object_type = 'I' ) ) THEN --atleast one of them should be 'T'
981
982 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
983 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
984 FND_MSG_PUB.add;
985 x_return_status := fnd_api.g_ret_sts_error;
986 exit;
987 END IF;
988
989 IF p_iir_tbl(l_ind).relationship_type_code = 'COMPONENT-OF' THEN
990 l_comp_of_count := l_comp_of_count + 1;
991 END IF;
992
993 IF p_iir_tbl(l_ind).subject_type = 'T' THEN
994 x_src_dtl_count := x_src_dtl_count + 1; -- count to check if a Non-Source atleast has one TLD tied to it
995 END IF;
996
997 l_loc_ind := l_loc_ind + 1;
998 x_iir_tbl(l_loc_ind).csi_inst_relationship_id :=
999 p_iir_tbl(l_ind).csi_inst_relationship_id;
1000 x_iir_tbl(l_loc_ind).subject_id :=
1001 p_iir_tbl(l_ind).subject_id;
1002 x_iir_tbl(l_loc_ind).object_id :=
1003 p_iir_tbl(l_ind).object_id;
1004 x_iir_tbl(l_loc_ind).relationship_type_code :=
1005 p_iir_tbl(l_ind).relationship_type_code;
1006 x_iir_tbl(l_loc_ind).subject_type :=
1007 p_iir_tbl(l_ind).subject_type;
1008 x_iir_tbl(l_loc_ind).object_type :=
1009 p_iir_tbl(l_ind).object_type;
1010 x_iir_tbl(l_loc_ind).object_index_flag :=
1011 p_iir_tbl(l_ind).object_index_flag;
1012 x_iir_tbl(l_loc_ind).subject_index_flag :=
1013 p_iir_tbl(l_ind).subject_index_flag;
1014 END IF;
1015 END IF; --object_yn = 'N'
1016 END LOOP;
1017
1018 IF l_comp_of_count > 1 THEN
1019 x_return_status := fnd_api.g_ret_sts_error; -- this status is then used to determine the multiple comp-of condition
1020 END IF;
1021
1022 END IF;
1023 END get_iir_details;
1024
1025 /* this routine makes sure that if a non sourced line detail is passed then
1026 it should be tied to its sourced parent using the item relationship link.
1027 The basic assumption there cannot be a non sourced (configuration item)
1028 line detail hanging in without a parent
1029 Plus added additional checks because of M-M changes
1030 */
1031
1032 PROCEDURE check_rltns_integrity(
1033 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1034 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
1035 x_return_status OUT NOCOPY VARCHAR2)
1036 IS
1037 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1038 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1039 l_line_dtl_g_miss csi_t_datastructures_grp.txn_line_detail_rec;
1040 l_iir_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1041 l_return_status VARCHAR2(1);
1042 l_subject_id NUMBER ;
1043 l_object_id NUMBER ;
1044 l_object_type VARCHAR2(30);
1045 l_subject_type VARCHAR2(30);
1046 l_sub_obj_id NUMBER := fnd_api.g_miss_num;
1047 l_object_yn VARCHAR2(1);
1048 l_line_id1 NUMBER;
1049 l_line_id2 NUMBER;
1050 l_src_flag1 VARCHAR2(1);
1051 l_src_flag2 VARCHAR2(1);
1052 l_src_dtl_count number;
1053 l_subject_index_flag varchar2(1);
1054 l_object_index_flag varchar2(1);
1055
1056
1057 BEGIN
1058
1059 l_line_dtl_tbl := p_txn_line_detail_tbl;
1060
1061 IF l_line_dtl_tbl.COUNT > 0
1062 THEN
1063 IF p_txn_ii_rltns_tbl.COUNT > 0
1064 THEN
1065 FOR l_td_ind IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
1066 LOOP
1067 l_object_yn := 'N';
1068 l_sub_obj_id := l_line_dtl_tbl(l_td_ind).txn_line_detail_id;
1069
1070 IF ( l_sub_obj_id = fnd_api.g_miss_num OR l_sub_obj_id = NULL ) THEN
1071 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_DTL_REF');
1072 FND_MESSAGE.set_token('SUBJECT_ID',l_sub_obj_id);
1073 FND_MESSAGE.set_token('OBJECT_ID', NULL);
1074 FND_MSG_PUB.add;
1075 RAISE FND_API.g_exc_error;
1076 END IF;
1077 -- get the corresponding ii_rltns (where subject_id = line detail index / id)
1078
1079 get_iir_details(
1080 p_sub_obj_id => l_sub_obj_id,
1081 p_object_yn => l_object_yn,
1082 p_iir_tbl => p_txn_ii_rltns_tbl,
1083 x_src_dtl_count => l_src_dtl_count,
1084 x_return_status => l_return_status,
1085 x_iir_tbl => l_iir_tbl); -- first call to see if the TLD was ref. as a subject in the rltns.
1086
1087 IF l_iir_tbl.COUNT = 0 AND l_object_yn = 'N' THEN
1088 l_object_yn := 'Y';
1089
1090 get_iir_details(
1091 p_sub_obj_id => l_sub_obj_id,
1092 p_object_yn => l_object_yn,
1093 p_iir_tbl => p_txn_ii_rltns_tbl,
1094 x_src_dtl_count => l_src_dtl_count,
1095 x_return_status => l_return_status,
1096 x_iir_tbl => l_iir_tbl); -- Second call to see if it's ref. as a object
1097
1098 END IF;
1099 IF l_line_dtl_tbl(l_td_ind).source_transaction_flag = 'N' THEN
1100 IF l_src_dtl_count = 0 THEN
1101
1102 FND_MESSAGE.set_name('CSI','CSI_TXN_NON_SRC_AND_NO_RLTN');
1103 FND_MESSAGE.set_token('INDEX',l_sub_obj_id);
1104 FND_MESSAGE.set_token('ITEM_ID',
1105 l_line_dtl_tbl(l_td_ind).inventory_item_id);
1106 FND_MSG_PUB.add;
1107 RAISE FND_API.g_exc_error;
1108 END IF;
1109 END IF; -- source flag chk = 'N'
1110
1111 IF l_iir_tbl.COUNT > 1 THEN
1112 IF l_return_status <> fnd_api.g_ret_sts_success THEN -- status set to error in the get iir routine when Multiple comp - of relationships are found for a given TLD
1113 FND_MESSAGE.set_name('CSI','CSI_TXN_MULTIPLE_PARENT');
1114 FND_MESSAGE.set_token('INDEX',l_td_ind);
1115 FND_MESSAGE.set_token('ITEM_ID',
1116 l_line_dtl_tbl(l_td_ind).inventory_item_id);
1117 FND_MSG_PUB.add;
1118 RAISE FND_API.g_exc_error;
1119 END IF;
1120 ELSIF l_return_status <> fnd_api.g_ret_sts_success THEN
1121 RAISE FND_API.g_exc_error;
1122 END IF;
1123
1124 END LOOP;
1125 END IF ; --- p_txn_ii_rltns_tbl.count > 0
1126 END IF;
1127
1128 EXCEPTION
1129 WHEN fnd_api.g_exc_error THEN
1130
1131 x_return_status := fnd_api.g_ret_sts_error;
1132
1133 END check_rltns_integrity;
1134
1135
1136 PROCEDURE convert_rltns_index_to_ids(
1137 p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1138 px_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
1139 x_return_status OUT NOCOPY varchar2)
1140 IS
1141 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1142 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1143 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1144
1145 BEGIN
1146 IF p_line_dtl_tbl.COUNT > 0 THEN
1147 FOR l_td_ind IN p_line_dtl_tbl.FIRST..p_line_dtl_tbl.LAST
1148 LOOP
1149 IF px_ii_rltns_tbl.COUNT > 0 THEN
1150 FOR l_ii_ind IN px_ii_rltns_tbl.FIRST..px_ii_rltns_tbl.LAST
1151 LOOP
1152 /* Added the defaulting of values to the index flags for M-M */
1153
1154 IF nvl(px_ii_rltns_tbl(l_ii_ind).subject_index_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1155 px_ii_rltns_tbl(l_ii_ind).subject_index_flag := 'Y';
1156 END IF;
1157
1158 IF nvl(px_ii_rltns_tbl(l_ii_ind).object_index_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1159 px_ii_rltns_tbl(l_ii_ind).object_index_flag := 'Y';
1160 END IF;
1161
1162 IF px_ii_rltns_tbl(l_ii_ind).subject_type = 'T'
1163 ---m-to-m 05/10
1164 AND nvl(px_ii_rltns_tbl(l_ii_ind).subject_index_flag,'Y') = 'Y'
1165 THEN
1166 csi_t_vldn_routines_pvt.get_txn_line_dtl_rec(
1167 p_index_id => px_ii_rltns_tbl(l_ii_ind).subject_id,
1168 p_txn_line_detail_tbl => p_line_dtl_tbl,
1169 x_txn_line_detail_rec => l_line_dtl_rec,
1170 x_return_status => l_return_status);
1171
1172 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1173 FND_MESSAGE.set_name('CSI','CSI_TXN_SUBJECT_INDEX_INVALID');
1174 FND_MESSAGE.set_token('INDEX_ID',px_ii_rltns_tbl(l_ii_ind).
1175 subject_id);
1176 FND_MSG_PUB.add;
1177 RAISE FND_API.g_exc_error;
1178 END IF;
1179
1180 l_ii_rltns_tbl(l_ii_ind).subject_id :=
1181 l_line_dtl_rec.txn_line_detail_id;
1182 END IF;
1183
1184 IF px_ii_rltns_tbl(l_ii_ind).object_type = 'T'
1185 ---m-to-m 05/10
1186 AND nvl(px_ii_rltns_tbl(l_ii_ind).object_index_flag,'Y') = 'Y'
1187 THEN
1188
1189 csi_t_vldn_routines_pvt.get_txn_line_dtl_rec(
1190 p_index_id => px_ii_rltns_tbl(l_ii_ind).object_id,
1191 p_txn_line_detail_tbl => p_line_dtl_tbl,
1192 x_txn_line_detail_rec => l_line_dtl_rec,
1193 x_return_status => l_return_status);
1194
1195
1196 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1197 FND_MESSAGE.set_name('CSI','CSI_TXN_OBJECT_INDEX_INVALID');
1198 FND_MESSAGE.set_token('INDEX_ID',px_ii_rltns_tbl(l_ii_ind).
1199 object_id);
1200 FND_MSG_PUB.add;
1201 RAISE FND_API.g_exc_error;
1202 END IF;
1203
1204 l_ii_rltns_tbl(l_ii_ind).object_id :=
1205 l_line_dtl_rec.txn_line_detail_id;
1206 END IF;
1207
1208 l_ii_rltns_tbl(l_ii_ind).transaction_line_id :=
1209 p_line_dtl_tbl(l_td_ind).transaction_line_id;
1210
1211 END LOOP;
1212
1213 END IF;
1214 END LOOP;
1215
1216 IF px_ii_rltns_tbl.count > 0 THEN
1217 FOR l_tmp_ind in px_ii_rltns_tbl.FIRST..px_ii_rltns_tbl.LAST
1218 LOOP
1219 IF px_ii_rltns_tbl(l_tmp_ind).object_type = 'T'
1220 ---m-to-m 05/10
1221 AND nvl(px_ii_rltns_tbl(l_tmp_ind).object_index_flag,'Y') = 'Y'
1222 THEN
1223 px_ii_rltns_tbl(l_tmp_ind).object_id :=
1224 l_ii_rltns_tbl(l_tmp_ind).object_id;
1225 END IF;
1226
1227 IF px_ii_rltns_tbl(l_tmp_ind).subject_type = 'T'
1228 ---m-to-m 05/10
1229 AND nvl(px_ii_rltns_tbl(l_tmp_ind).subject_index_flag,'Y') = 'Y'
1230 THEN
1231 px_ii_rltns_tbl(l_tmp_ind).subject_id :=
1232 l_ii_rltns_tbl(l_tmp_ind).subject_id;
1233 END IF;
1234
1235 px_ii_rltns_tbl(l_tmp_ind).transaction_line_id :=
1236 l_ii_rltns_tbl(l_tmp_ind).transaction_line_id;
1237
1238 END LOOP;
1239 END IF;
1240
1241 END IF;
1242 EXCEPTION
1243 WHEN fnd_api.g_exc_error THEN
1244 x_return_status := fnd_api.g_ret_sts_error;
1245 END convert_rltns_index_to_ids;
1246
1247
1248 PROCEDURE is_valid_owner_for_create(
1249 p_txn_line_detail_id IN NUMBER,
1250 p_instance_party_id IN NUMBER,
1251 x_return_status OUT NOCOPY VARCHAR2)
1252 IS
1253
1254 l_found VARCHAR2(1);
1255 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
1256 l_csi_instance_id csi_t_party_details.instance_party_id%TYPE;
1257
1258 CURSOR csi_pty_cur(p_csi_instance_id IN NUMBER) IS
1259 SELECT instance_party_id
1260 FROM csi_i_parties
1261 WHERE instance_id = p_csi_instance_id
1262 AND relationship_type_code = 'OWNER';
1263
1264 BEGIN
1265
1266 x_return_status := fnd_api.g_ret_sts_success;
1267
1268 SELECT instance_exists_flag,
1269 instance_id
1270 INTO l_instance_exists_flag,
1271 l_csi_instance_id
1272 FROM csi_t_txn_line_details
1273 WHERE txn_line_detail_id = p_txn_line_detail_id;
1274
1275 /* if an instance is referred, then look if the instance party is referred
1276 if the instance party is referred then skip the chk.
1277 */
1278 IF NVL(l_instance_exists_flag,'N') = 'Y' THEN
1279
1280 IF nvl(p_instance_party_id ,fnd_api.g_miss_num) = fnd_api.g_miss_num
1281 THEN
1282
1283 FOR csi_pty_rec in csi_pty_cur(l_csi_instance_id)
1284 LOOP
1285 x_return_status := fnd_api.g_ret_sts_error;
1286 exit;
1287 END LOOP;
1288
1289 /* we might have to put the logic if the caller is trying to change the
1290 relationship type code from NON OWNER to OWNER (in the else part)
1291 */
1292
1293 END IF;
1294
1295 ELSE
1296
1297 BEGIN
1298
1299 SELECT 'x' INTO l_found
1300 FROM csi_t_party_details
1301 WHERE txn_line_detail_id = p_txn_line_detail_id
1302 AND relationship_type_code = 'OWNER';
1303
1304 x_return_status := fnd_api.g_ret_sts_error;
1305
1306 EXCEPTION
1307 WHEN no_data_found THEN
1308 x_return_status := fnd_api.g_ret_sts_success;
1309
1310 END;
1311
1312 END IF;
1313 END is_valid_owner_for_create;
1314
1315 procedure get_txn_line_dtl_rec(
1316 p_index_id IN NUMBER,
1317 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1318 x_txn_line_detail_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
1319 x_return_status OUT NOCOPY VARCHAR2)
1320 IS
1321 l_found BOOLEAN := FALSE;
1322 -- l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1323 BEGIN
1324
1325 IF p_txn_line_detail_tbl.COUNT > 0 THEN
1326
1327 FOR l_ind in p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
1328 LOOP
1329
1330 IF l_ind = p_index_id OR
1331 p_txn_line_detail_tbl(l_ind).txn_line_detail_id = p_index_id THEN
1332 l_found := TRUE;
1333 x_txn_line_detail_rec := p_txn_line_detail_tbl(l_ind);
1334 exit;
1335 END IF;
1336 END LOOP;
1337
1338 END IF;
1339
1340 IF l_found THEN
1341 x_return_status := fnd_api.g_ret_sts_success;
1342 ELSE
1343 x_return_status := fnd_api.g_ret_sts_error;
1344 END IF;
1345
1346 END get_txn_line_dtl_rec;
1347
1348
1349 PROCEDURE get_processing_status(
1350 p_level IN varchar2,
1351 p_level_dtl_id IN number,
1352 x_processing_status OUT NOCOPY varchar2,
1353 x_return_status OUT NOCOPY varchar2)
1354 IS
1355
1356 l_txn_line_dtl_id csi_t_txn_line_details.txn_line_detail_id%TYPE;
1357 l_transaction_line_id csi_t_transaction_lines.transaction_line_id%TYPE;
1358 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
1359
1360 BEGIN
1361
1362 IF p_level in ('PARTY', 'II_RLTNS', 'EXT_ATTRIB', 'ORG_ASSGN') THEN
1363
1364 l_txn_line_dtl_id := p_level_dtl_id;
1365
1366 ELSIF p_level = 'PARTY_ACCT' THEN
1367
1368 SELECT txn_line_detail_id
1369 INTO l_txn_line_dtl_id
1370 FROM csi_t_party_details
1371 WHERE txn_party_detail_id = p_level_dtl_id;
1372
1373 END IF;
1374
1375 SELECT transaction_line_id
1376 INTO l_transaction_line_id
1377 FROM csi_t_txn_line_details
1378 WHERE txn_line_detail_id = l_txn_line_dtl_id;
1379
1380 SELECT nvl(processing_status, '#NONE#')
1381 INTO l_processing_status
1382 FROM csi_t_transaction_lines
1383 WHERE transaction_line_id = l_transaction_line_id;
1384
1385 x_processing_status := l_processing_status;
1386 x_return_status := fnd_api.g_ret_sts_success;
1387
1388 EXCEPTION
1389 WHEN no_data_found THEN
1390 x_return_status := fnd_api.g_ret_sts_error;
1391
1392 END get_processing_status ;
1393
1394 PROCEDURE validate_attrib_source_id(
1395 p_attrib_source_table IN varchar2,
1396 p_attrib_source_id IN number,
1397 x_return_status OUT NOCOPY varchar2)
1398 IS
1399 l_found varchar2(1);
1400
1401 BEGIN
1402
1403 IF p_attrib_source_table = 'CSI_I_EXTENDED_ATTRIBS' THEN
1404
1405 SELECT 'X'
1406 INTO l_found
1407 FROM csi_i_extended_attribs
1408 WHERE attribute_id = p_attrib_source_id;
1409
1410 ELSIF p_attrib_source_table = 'CSI_IEA_VALUES' THEN
1411
1412 SELECT 'X'
1413 INTO l_found
1414 FROM csi_iea_values
1415 WHERE attribute_value_id = p_attrib_source_id;
1416
1417 ELSE
1418
1419 FND_MESSAGE.set_name('CSI','CSI_TXN_ATT_SRC_TBL_INVALID');
1420 FND_MESSAGE.set_token('ATT_SRC_TBL',p_attrib_source_table);
1421 FND_MSG_PUB.add;
1422 RAISE FND_API.g_exc_error;
1423
1424 END IF;
1425
1426 x_return_status := fnd_api.g_ret_sts_success;
1427
1428 EXCEPTION
1429 WHEN no_data_found THEN
1430
1431 FND_MESSAGE.set_name('CSI','CSI_TXN_ATT_SRC_ID_INVALID');
1432 FND_MESSAGE.set_token('ATT_SRC_TBL',p_attrib_source_table);
1433 FND_MESSAGE.set_token('ATT_SRC_ID',p_attrib_source_id);
1434 FND_MSG_PUB.add;
1435 x_return_status := fnd_api.g_ret_sts_error;
1436
1437 WHEN fnd_api.g_exc_error THEN
1438
1439 x_return_status := fnd_api.g_ret_sts_error;
1440
1441 END validate_attrib_source_id;
1442
1443 PROCEDURE validate_party_account_id(
1444 p_party_id IN NUMBER,
1445 p_party_account_id IN NUMBER,
1446 x_return_status OUT NOCOPY VARCHAR2)
1447 IS
1448 l_found varchar2(1);
1449 BEGIN
1450
1451 x_return_status := fnd_api.g_ret_sts_success;
1452
1453 SELECT 'X'
1454 INTO l_found
1455 FROM hz_cust_accounts
1456 WHERE party_id = p_party_id
1457 AND cust_account_id = p_party_account_id;
1458
1459 EXCEPTION
1460 WHEN no_data_found THEN
1461
1462 x_return_status := fnd_api.g_ret_sts_error;
1463
1464 END validate_party_account_id;
1465
1466 PROCEDURE get_instance_ref_info(
1467 p_level IN varchar2,
1468 p_level_dtl_id IN number,
1469 x_instance_id OUT NOCOPY varchar2,
1470 x_instance_exists_flag OUT NOCOPY varchar2,
1471 x_return_status OUT NOCOPY varchar2)
1472 IS
1473
1474 l_txn_line_dtl_id csi_t_txn_line_details.txn_line_detail_id%TYPE;
1475
1476 BEGIN
1477
1478 IF p_level in ('PARTY', 'II_RLTNS', 'EXT_ATTRIB', 'ORG_ASSGN') THEN
1479
1480 l_txn_line_dtl_id := p_level_dtl_id;
1481
1482 ELSIF p_level = 'PARTY_ACCT' THEN
1483
1484 SELECT txn_line_detail_id
1485 INTO l_txn_line_dtl_id
1486 FROM csi_t_party_details
1487 WHERE txn_party_detail_id = p_level_dtl_id;
1488
1489 END IF;
1490
1491 SELECT instance_id ,
1492 instance_exists_flag
1493 INTO x_instance_id,
1494 x_instance_exists_flag
1495 FROM csi_t_txn_line_details
1496 WHERE txn_line_detail_id = l_txn_line_dtl_id;
1497
1498 x_return_status := fnd_api.g_ret_sts_success;
1499
1500 EXCEPTION
1501 WHEN no_data_found THEN
1502 x_return_status := fnd_api.g_ret_sts_error;
1503
1504 END get_instance_ref_info;
1505
1506 PROCEDURE get_party_detail_rec(
1507 p_party_detail_id IN number,
1508 x_party_detail_rec OUT NOCOPY csi_t_party_details%rowtype,
1509 x_return_status OUT NOCOPY varchar2)
1510 IS
1511 BEGIN
1512
1513 SELECT *
1514 INTO x_party_detail_rec
1515 FROM csi_t_party_details
1516 WHERE txn_party_detail_id = p_party_detail_id;
1517
1518 x_return_status := fnd_api.g_ret_sts_success;
1519
1520 EXCEPTION
1521 WHEN no_data_found THEN
1522 x_return_status := fnd_api.g_ret_sts_error;
1523 END get_party_detail_rec;
1524
1525
1526 PROCEDURE validate_instance_id(
1527 p_instance_id IN number,
1528 x_return_status OUT NOCOPY varchar2)
1529 IS
1530 l_found VARCHAR2(1);
1531 BEGIN
1532
1533 SELECT 'X'
1534 INTO l_found
1535 FROM csi_item_instances
1536 WHERE instance_id = p_instance_id;
1537 -- AND unexpired condition
1538
1539 x_return_status := fnd_api.g_ret_sts_success;
1540
1541 EXCEPTION
1542 WHEN no_data_found THEN
1543 x_return_status := fnd_api.g_ret_sts_error;
1544
1545 END validate_instance_id;
1546
1547 procedure validate_instance_reference(
1548 p_level IN varchar2,
1549 p_level_dtl_id IN number,
1550 p_level_inst_ref_id IN number,
1551 x_return_status OUT NOCOPY varchar2)
1552 IS
1553 l_instance_id csi_t_txn_line_details.instance_id%TYPE;
1554 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
1555 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1556
1557 l_td_object_id number;
1558 l_inst_object_id number;
1559 BEGIN
1560
1561 get_instance_ref_info(
1562 p_level => p_level,
1563 p_level_dtl_id => p_level_dtl_id,
1564 x_instance_id => l_instance_id,
1565 x_instance_exists_flag => l_instance_exists_flag,
1566 x_return_status => l_return_status);
1567
1568 IF l_return_status = fnd_api.g_ret_sts_success THEN
1569
1570 --IF nvl(l_instance_exists_flag,'N') <> 'Y' THEN
1571 IF l_instance_id is null THEN -- instance is not refernced
1572
1573 IF p_level <> 'II_RLTNS' THEN
1574 --instance reference not allowed
1575 fnd_message.set_name('CSI','CSI_TXN_INST_REF_NOT_ALLOWED');
1576 fnd_message.set_token('LVL',p_level);
1577 fnd_message.set_token('LVL_DTL_ID',p_level_inst_ref_id);
1578 fnd_msg_pub.add;
1579
1580 raise fnd_api.g_exc_error;
1581 END IF;
1582
1583 END IF;
1584
1585 IF p_level = 'PARTY' THEN
1586
1587 validate_instance_party_id(
1588 p_instance_id => l_instance_id,
1589 p_instance_party_id => p_level_inst_ref_id,
1590 x_return_status => l_return_status);
1591
1592 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1593
1594 fnd_message.set_name('CSI','CSI_TXN_INST_PARTY_REF_INVALID');
1595 fnd_message.set_token('INST_ID',l_instance_id);
1596 fnd_message.set_token('INST_PTY_ID',p_level_inst_ref_id);
1597 fnd_msg_pub.add;
1598
1599 RAISE fnd_api.g_exc_error;
1600 END IF;
1601
1602 ELSIF p_level = 'ORG_ASSGN' THEN
1603
1604 validate_instance_ou_id(
1605 p_instance_id => l_instance_id,
1606 p_instance_ou_id => p_level_inst_ref_id,
1607 x_return_status => l_return_status);
1608
1609 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1610 fnd_message.set_name('CSI','CSI_TXN_INST_OU_REF_INVALID');
1611 fnd_message.set_token('INST_ID',l_instance_id);
1612 fnd_message.set_token('INST_OU_ID',p_level_inst_ref_id);
1613 fnd_msg_pub.add;
1614 raise fnd_api.g_exc_error;
1615 END IF;
1616
1617 ELSIF p_level = 'ORG_ATTRIB' THEN
1618 null;
1619 ELSIF p_level = 'II_RLTNS' THEN
1620
1621 -- in IB
1622 -- get the object id(instance) for the instance_relationship_id
1623
1624 -- in TD
1625 -- get the instance reference for the object_id(txn_line_detail_id)
1626 -- compare both the instances
1627
1628 -- if both are not same then raise exception
1629
1630 IF nvl(l_instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1631
1632 l_td_object_id := l_instance_id;
1633
1634 validate_instance_rltns_id(
1635 p_csi_inst_rltns_id => p_level_inst_ref_id,
1636 x_object_id => l_inst_object_id,
1637 x_return_status => l_return_status);
1638
1639 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1640 null;
1641 -- invalid csi_inst_relation_id
1642 RAISE fnd_api.g_exc_error;
1643 END IF;
1644
1645 IF l_td_object_id <> l_inst_object_id THEN
1646 null;
1647 -- cannot change the parent (object id) in IB
1648 RAISE fnd_api.g_exc_error;
1649
1650 END IF;
1651
1652 END IF;
1653
1654 END IF;
1655
1656 END IF;
1657
1658 EXCEPTION
1659 WHEN fnd_api.g_exc_error THEN
1660 x_return_status := fnd_api.g_ret_sts_error;
1661 END validate_instance_reference;
1662
1663 PROCEDURE validate_account_id(
1664 p_account_id in number,
1665 x_return_status OUT NOCOPY varchar2)
1666 IS
1667 l_found varchar2(1);
1668 BEGIN
1669
1670 SELECT 'X' INTO l_found
1671 FROM hz_cust_accounts
1672 WHERE cust_account_id = p_account_id;
1673
1674 x_return_status := fnd_api.g_ret_sts_success;
1675
1676 EXCEPTION
1677 when no_data_found then
1678 x_return_status := fnd_api.g_ret_sts_error;
1679
1680 END validate_account_id;
1681
1682 PROCEDURE validate_site_use_id(
1683 p_account_id IN number,
1684 p_site_use_id IN number,
1685 p_site_use_code IN varchar2,
1686 x_return_status OUT NOCOPY varchar2)
1687 IS
1688
1689 CURSOR site_cur IS
1690 SELECT csu.site_use_id
1691 FROM hz_cust_site_uses_all csu
1692 WHERE csu.site_use_id = p_site_use_id
1693 AND csu.site_use_code = p_site_use_code;
1694
1695 /* BUG # 2159414
1696 CURSOR site_cur IS
1697 SELECT csu.site_use_id
1698 FROM hz_cust_site_uses_all csu ,
1699 hz_cust_acct_sites_all cas
1700 WHERE csu.site_use_id = p_site_use_id
1701 AND csu.site_use_code = p_site_use_code
1702 AND csu.cust_acct_site_id = cas.cust_acct_site_id
1703 AND cas.cust_account_id = p_account_id;
1704 */
1705
1706 l_found BOOLEAN;
1707
1708 BEGIN
1709 l_found := FALSE;
1710
1711 FOR site_rec in site_cur
1712 LOOP
1713 l_found := TRUE;
1714 exit;
1715 END LOOP;
1716
1717 IF l_found THEN
1718 x_return_status := fnd_api.g_ret_sts_success;
1719 ELSE
1720 x_return_status := fnd_api.g_ret_sts_error;
1721 END IF;
1722
1723 END validate_site_use_id;
1724
1725 PROCEDURE get_txn_system_id(
1726 p_txn_systems_index IN number,
1727 p_txn_systems_tbl IN csi_t_datastructures_grp.txn_systems_tbl,
1728 x_txn_system_id OUT NOCOPY number,
1729 x_return_status OUT NOCOPY varchar2)
1730 IS
1731 BEGIN
1732
1733 x_txn_system_id := fnd_api.g_miss_num;
1734
1735 IF nvl(p_txn_systems_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1736 IF p_txn_systems_tbl.COUNT > 0 THEN
1737 FOR l_ind in p_txn_systems_tbl.FIRST .. p_txn_systems_tbl.LAST
1738 LOOP
1739
1740 IF l_ind = p_txn_systems_index THEN
1741 x_txn_system_id := p_txn_systems_tbl(l_ind).transaction_system_id;
1742 exit;
1743 END IF;
1744
1745 END LOOP;
1746 END IF;
1747 END IF;
1748 END get_txn_system_id;
1749
1750 PROCEDURE get_txn_systems_index(
1751 p_txn_system_id IN number,
1752 p_txn_systems_tbl IN csi_t_datastructures_grp.txn_systems_tbl,
1753 x_txn_systems_index OUT NOCOPY number,
1754 x_return_status OUT NOCOPY varchar2)
1755 IS
1756 BEGIN
1757
1758 x_txn_systems_index := fnd_api.g_miss_num;
1759
1760 IF nvl(p_txn_system_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1761 IF p_txn_systems_tbl.COUNT > 0 THEN
1762 FOR l_ind in p_txn_systems_tbl.FIRST .. p_txn_systems_tbl.LAST
1763 LOOP
1764
1765 IF p_txn_systems_tbl(l_ind).transaction_system_id = p_txn_system_id THEN
1766 x_txn_systems_index := l_ind;
1767 exit;
1768 END IF;
1769
1770 END LOOP;
1771 END IF;
1772 END IF;
1773
1774 END get_txn_systems_index;
1775
1776 PROCEDURE validate_contact_flag(
1777 p_contact_flag in varchar2,
1778 x_return_status OUT NOCOPY varchar2)
1779 IS
1780 BEGIN
1781 x_return_status := fnd_api.g_ret_sts_success;
1782 IF p_contact_flag not in ('Y','N') THEN
1783 x_return_status := fnd_api.g_ret_sts_error;
1784 END IF;
1785 END validate_contact_flag;
1786
1787 /* validtion routine for sub_type_id */
1788 PROCEDURE validate_sub_type_id(
1789 p_transaction_line_id IN number,
1790 p_sub_type_id IN number,
1791 x_return_status OUT NOCOPY varchar2)
1792 IS
1793
1794 l_txn_type_id number;
1795
1796 CURSOR sub_type_cur(p_txn_type_id IN number) IS
1797 SELECT 'X'
1798 FROM csi_txn_sub_types
1799 WHERE sub_type_id = p_sub_type_id
1800 AND transaction_type_id = nvl(p_txn_type_id, transaction_type_id);
1801
1802 BEGIN
1803
1804 x_return_status := fnd_api.g_ret_sts_error;
1805
1806 SELECT source_transaction_type_id
1807 INTO l_txn_type_id
1808 FROM csi_t_transaction_lines
1809 WHERE transaction_line_id = p_transaction_line_id;
1810
1811 FOR sub_type_rec in sub_type_cur(l_txn_type_id)
1812 LOOP
1813 x_return_status := fnd_api.g_ret_sts_success;
1814 exit;
1815 END LOOP;
1816
1817
1818 EXCEPTION
1819 WHEN no_data_found THEN
1820 x_return_status := fnd_api.g_ret_sts_error;
1821 END validate_sub_type_id;
1822
1823 PROCEDURE check_duplicate(
1824 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
1825 x_return_status OUT NOCOPY varchar2)
1826 IS
1827 l_td_found char;
1828 BEGIN
1829
1830 SELECT 'x'
1831 INTO l_td_found
1832 FROM csi_t_transaction_lines
1833 WHERE source_transaction_table = p_txn_line_rec.source_transaction_table
1834 AND source_transaction_id = p_txn_line_rec.source_transaction_id;
1835
1836 x_return_status := fnd_api.g_ret_sts_error;
1837
1838 EXCEPTION
1839 WHEN no_data_found THEN
1840 x_return_status := fnd_api.g_ret_sts_success;
1841 END check_duplicate;
1842
1843 PROCEDURE validate_lot_number(
1844 p_inventory_item_id IN number,
1845 p_organization_id IN number,
1846 p_lot_number IN varchar2,
1847 x_return_status OUT NOCOPY varchar2)
1848 IS
1849 l_lot_control_code mtl_system_items.lot_control_code%TYPE;
1850 l_item_name mtl_system_items.segment1%TYPE;
1851 l_found char;
1852 BEGIN
1853
1854 x_return_status := fnd_api.g_ret_sts_success;
1855
1856 -- check whether the item is under lot control or not...
1857
1858 -- 1 - No Control
1859 -- 2 - Full Control
1860
1861 BEGIN
1862 SELECT lot_control_code,
1863 segment1
1864 INTO l_lot_control_code,
1865 l_item_name
1866 FROM mtl_system_items
1867 WHERE inventory_item_id = p_inventory_item_id
1868 AND organization_id = p_organization_id;
1869 EXCEPTION
1870 WHEN no_data_found THEN
1871
1872 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
1873 fnd_message.set_token('INVENTORY_ITEM_ID',p_inventory_item_id);
1874 fnd_message.set_token('INV_ORGANIZATION_ID',p_organization_id);
1875 fnd_msg_pub.add;
1876
1877 RAISE fnd_api.g_exc_error;
1878 END;
1879
1880 IF l_lot_control_code = 2 THEN
1881
1882 BEGIN
1883
1884 SELECT 'X'
1885 INTO l_found
1886 FROM mtl_lot_numbers
1887 WHERE inventory_item_id = p_inventory_item_id
1888 AND lot_number = p_lot_number;
1889
1890 -- AND organization_id = p_organization_id
1891 -- commenting this as from order management the inventory organization
1892 -- is not visible. Only the validation organization is passed
1893
1894 EXCEPTION
1895
1896 WHEN no_data_found THEN
1897 fnd_message.set_name('CSI','CSI_API_INVALID_LOT_NUM');
1898 fnd_message.set_token('LOT_NUMBER',p_lot_number);
1899 fnd_msg_pub.add;
1900 RAISE fnd_api.g_exc_error;
1901
1902 -- adding this because I took of the organization check
1903 WHEN too_many_rows THEN
1904 null;
1905 END;
1906
1907 ELSE
1908 fnd_message.set_name('CSI', 'CSI_API_NOT_LOT_CONTROLLED');
1909 fnd_message.set_token('LOT_NUMBER', l_item_name);
1910 fnd_msg_pub.add;
1911 END IF;
1912
1913 EXCEPTION
1914 WHEN fnd_api.g_exc_error THEN
1915 x_return_status := fnd_api.g_ret_sts_error;
1916 END validate_lot_number;
1917
1918 PROCEDURE validate_serial_number(
1919 p_inventory_item_id IN number,
1920 p_organization_id IN number,
1921 p_serial_number IN varchar2,
1922 x_return_status OUT NOCOPY varchar2)
1923 IS
1924 l_serial_control_code mtl_system_items.serial_number_control_code%TYPE;
1925 l_item_name mtl_system_items.segment1%TYPE;
1926 l_found char;
1927 BEGIN
1928
1929 x_return_status := fnd_api.g_ret_sts_success;
1930
1931 -- check whether the item is serial controlled or not
1932
1933 -- '1' No serial number control
1934 -- '2' Predefined serial numbers
1935 -- '5' Dynamic entry at inventory receipt
1936 -- '6' Dynamic entry at sales order issue
1937
1938 BEGIN
1939 SELECT serial_number_control_code,
1940 segment1
1941 INTO l_serial_control_code,
1942 l_item_name
1943 FROM mtl_system_items
1944 WHERE inventory_item_id = p_inventory_item_id
1945 AND organization_id = p_organization_id;
1946 EXCEPTION
1947
1948 WHEN no_data_found THEN
1949
1950 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
1951 fnd_message.set_token('INVENTORY_ITEM_ID',p_inventory_item_id);
1952 fnd_message.set_token('INV_ORGANIZATION_ID',p_organization_id);
1953 fnd_msg_pub.add;
1954
1955 RAISE fnd_api.g_exc_error;
1956 END;
1957
1958 IF l_serial_control_code <> 1 THEN
1959
1960 BEGIN
1961
1962 SELECT 'X'
1963 INTO l_found
1964 FROM mtl_serial_numbers
1965 WHERE inventory_item_id = p_inventory_item_id
1966 AND serial_number = p_serial_number;
1967
1968 -- AND current_organization_id = p_organization_id
1969 -- commenting this as from order management the inventory organization
1970 -- is not visible. Only the validation organization is passed
1971
1972 EXCEPTION
1973 WHEN no_data_found THEN
1974
1975 fnd_message.set_name('CSI','CSI_API_INVALID_SERIAL_NUM');
1976 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1977 fnd_msg_pub.add;
1978 RAISE fnd_api.g_exc_error;
1979
1980 -- adding this because I took of the organization check
1981 WHEN too_many_rows THEN
1982 null;
1983 END;
1984
1985 END IF;
1986
1987 EXCEPTION
1988 WHEN fnd_api.g_exc_error THEN
1989 x_return_status := fnd_api.g_ret_sts_error;
1990 END validate_serial_number;
1991
1992 /* Added this new routine for M-M Changes */
1993
1994 PROCEDURE validate_txn_rltnshp (
1995 p_txn_line_detail_rec1 IN csi_t_datastructures_grp.txn_line_detail_rec,
1996 p_txn_line_detail_rec2 IN csi_t_datastructures_grp.txn_line_detail_rec,
1997 p_iir_rec IN csi_t_datastructures_grp.txn_ii_rltns_rec,
1998 x_return_status OUT NOCOPY varchar2)
1999 IS
2000
2001 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_txn_rltnshp';
2002 l_line_dtl_rec1 csi_t_datastructures_grp.txn_line_detail_rec;
2003 l_line_dtl_rec2 csi_t_datastructures_grp.txn_line_detail_rec;
2004 l_ii_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
2005 l_subject_id NUMBER;
2006 l_object_id NUMBER;
2007 l_object_type VARCHAR2(30);
2008 l_subject_type VARCHAR2(30);
2009 l_sub_obj_id NUMBER;
2010 l_object_yn VARCHAR2(1);
2011 l_csi_rel_id NUMBER;
2012 l_rel_type VARCHAR2(30);
2013 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2014
2015
2016 CURSOR txn_ii_rltns_cur (c_subject_id IN NUMBER,
2017 c_object_id IN NUMBER ,
2018 c_relationship_type_code IN VARCHAR2)
2019 IS
2020 SELECT txn_relationship_id , object_type, subject_type
2021 FROM csi_t_ii_relationships
2022 WHERE subject_id = c_subject_id
2023 AND object_id = c_object_id
2024 AND relationship_type_code = c_relationship_type_code
2025 AND NVL(active_end_date , SYSDATE) >= SYSDATE ;
2026
2027 l_txn_relationship_id NUMBER := NULL ;
2028 l_sub_type VARCHAR2(30);
2029 l_obj_type VARCHAR2(30);
2030
2031 /* Validations performed :: If both sub and obj are TLD's then
2032 i) Sub and Obj have source flag = 'Y' , the source hdr id and source txn type ID for both must be same for connected to
2033 ii)One of them have source flag = 'Y' and the other has 'N' -- source txn ID and source txn type ID for both must be same i.e. they should be within the same txn line
2034 iii)Sub and Obj have source flag= 'N' -- not allowed
2035 iv) Validate instance references
2036 v) validate the relationship for bus rules if instance is involved
2037 */
2038 BEGIN
2039 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2040 l_line_dtl_rec1 := p_txn_line_detail_rec1;
2041 l_line_dtl_rec2 := p_txn_line_detail_rec2;
2042 l_ii_rltns_rec := p_iir_rec;
2043 l_object_type := l_ii_rltns_rec.object_type;
2044 l_object_id := l_ii_rltns_rec.object_id;
2045 l_subject_type := l_ii_rltns_rec.subject_type;
2046 l_subject_id := l_ii_rltns_rec.subject_id;
2047 l_csi_rel_id := l_ii_rltns_rec.csi_inst_relationship_id;
2048 l_rel_type := l_ii_rltns_rec.relationship_type_code;
2049 x_return_status := fnd_api.g_ret_sts_success ;
2050
2051 IF l_line_dtl_rec1.txn_line_detail_id = fnd_api.g_miss_num THEN
2052 -- call validate_inst_details to validate it in instance context
2053
2054 validate_inst_details (
2055 p_iir_rec => l_ii_rltns_rec,
2056 p_txn_dtl_rec => l_line_dtl_rec2,
2057 x_return_status => l_return_status );
2058
2059 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2060 RAISE fnd_api.g_exc_error;
2061 END IF;
2062
2063 ELSIF l_line_dtl_rec2.txn_line_detail_id = fnd_api.g_miss_num THEN
2064
2065 validate_inst_details (
2066 p_iir_rec => l_ii_rltns_rec,
2067 p_txn_dtl_rec => l_line_dtl_rec1,
2068 x_return_status => l_return_status );
2069
2070 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2071 RAISE fnd_api.g_exc_error;
2072 END IF;
2073 ELSE
2074 /* validate the source txn hdr ID */
2075 ---Bypass this check if the source is Configurator
2076 -- Added for CZ Integration
2077 IF (NVL(l_line_dtl_rec1.config_inst_hdr_id , fnd_api.g_miss_num)
2078 = fnd_api.g_miss_num AND
2079 NVL(l_line_dtl_rec2.config_inst_hdr_id , fnd_api.g_miss_num)
2080 = fnd_api.g_miss_num )
2081 THEN
2082 validate_src_header (
2083 p_txn_line_id1 => l_line_dtl_rec1.transaction_line_id,
2084 p_txn_line_id2 => l_line_dtl_rec2.transaction_line_id,
2085 p_rel_type_code => l_ii_rltns_rec.relationship_type_code,
2086 x_return_status => l_return_status);
2087 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2088 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_REL');
2089 FND_MESSAGE.set_token('TXN_DTL_ID1', l_line_dtl_rec1.txn_line_detail_id);
2090 FND_MESSAGE.set_token('TXN_DTL_ID2', l_line_dtl_rec2.txn_line_detail_id);
2091 FND_MSG_PUB.add;
2092 RAISE fnd_api.g_exc_error;
2093 END IF;
2094 END IF ;
2095
2096 IF ( l_line_dtl_rec1.transaction_line_id = l_line_dtl_rec2.transaction_line_id )
2097 AND l_ii_rltns_rec.relationship_type_code <> 'CONNECTED-TO' THEN
2098 IF l_line_dtl_rec1.source_transaction_flag = l_line_dtl_rec2.source_transaction_flag THEN
2099 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_REL');
2100 FND_MESSAGE.set_token('TXN_DTL_ID1', l_line_dtl_rec1.txn_line_detail_id);
2101 FND_MESSAGE.set_token('TXN_DTL_ID2', l_line_dtl_rec2.txn_line_detail_id);
2102 FND_MSG_PUB.add;
2103 RAISE fnd_api.g_exc_error;
2104 END IF;
2105 END IF;
2106
2107 END IF;
2108
2109 l_txn_relationship_id := NULL ;
2110 OPEN txn_ii_rltns_cur (l_ii_rltns_rec.subject_id,
2111 l_ii_rltns_rec.object_id,
2112 l_ii_rltns_rec.relationship_type_code) ;
2113
2114 FETCH txn_ii_rltns_cur INTO l_txn_relationship_id ,
2115 l_obj_type,
2116 l_sub_type;
2117 CLOSE txn_ii_rltns_cur ;
2118 IF nvl(l_ii_rltns_rec.txn_relationship_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2119 IF ( l_ii_rltns_rec.object_type = l_obj_type
2120 AND l_ii_rltns_rec.subject_type = l_sub_type) THEN
2121 IF l_txn_relationship_id IS NOT NULL
2122 AND l_ii_rltns_rec.txn_relationship_id <> l_txn_relationship_id
2123 THEN
2124 FND_MESSAGE.set_name('CSI','CSI_TXN_DUP_RLTNS');
2125 FND_MSG_PUB.add;
2126 RAISE fnd_api.g_exc_error;
2127 END IF ;
2128 END IF;
2129 END IF;
2130
2131 EXCEPTION
2132 WHEN fnd_api.g_exc_error THEN
2133 x_return_status := fnd_api.g_ret_sts_error;
2134 END validate_txn_rltnshp ;
2135
2136 /* Added new routine for M-M Changes */
2137
2138 PROCEDURE validate_inst_details (
2139 p_iir_rec IN csi_t_datastructures_grp.txn_ii_rltns_rec,
2140 p_txn_dtl_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
2141 x_return_status OUT NOCOPY varchar2)
2142
2143 IS
2144 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_inst_details';
2145 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
2146 l_txn_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
2147 l_iir_rec csi_ii_relationships%rowtype;
2148 l_subject_id NUMBER;
2149 l_object_id NUMBER;
2150 l_object_type VARCHAR2(30);
2151 l_subject_type VARCHAR2(30);
2152 l_csi_rel_id NUMBER;
2153 l_rel_type VARCHAR2(30);
2154 l_active_end_date DATE;
2155 l_instance_id NUMBER;
2156 l_found VARCHAR2(1) := 'N';
2157 l_loc_type VARCHAR2(30);
2158
2159 BEGIN
2160 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2161 l_line_dtl_rec := p_txn_dtl_rec;
2162 l_txn_rltns_rec := p_iir_rec;
2163 l_object_type := l_txn_rltns_rec.object_type;
2164 l_object_id := l_txn_rltns_rec.object_id;
2165 l_subject_type := l_txn_rltns_rec.subject_type;
2166 l_subject_id := l_txn_rltns_rec.subject_id;
2167 l_csi_rel_id := l_txn_rltns_rec.csi_inst_relationship_id;
2168 l_rel_type := l_txn_rltns_rec.relationship_type_code;
2169 x_return_status := fnd_api.g_ret_sts_success ;
2170
2171 IF l_txn_rltns_rec.subject_type = 'I' THEN
2172 l_instance_id := l_subject_id;
2173 ELSIF l_txn_rltns_rec.object_type = 'I' THEN
2174 l_instance_id := l_object_id;
2175 ELSE
2176 l_instance_id := fnd_api.g_miss_num;
2177 END IF;
2178 IF l_txn_rltns_rec.csi_inst_relationship_id = NULL THEN
2179 l_csi_rel_id := fnd_api.g_miss_num;
2180 END IF;
2181 IF l_txn_rltns_rec.active_end_date = NULL THEN
2182 l_active_end_date := fnd_api.g_miss_date;
2183 END IF;
2184
2185 IF l_csi_rel_id <> fnd_api.g_miss_num THEN
2186
2187 BEGIN
2188
2189 SELECT subject_id,
2190 object_id,
2191 relationship_type_code
2192 INTO l_iir_rec.subject_id,
2193 l_iir_rec.object_id,
2194 l_iir_rec.relationship_type_code
2195 FROM csi_ii_relationships
2196 WHERE relationship_id = l_csi_rel_id
2197 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2198 EXCEPTION
2199 WHEN no_data_found THEN
2200 x_return_status := fnd_api.g_ret_sts_error;
2201 END ;
2202 IF l_iir_rec.relationship_type_code = 'COMPONENT-OF' THEN
2203 IF l_iir_rec.object_id <> l_object_id THEN
2204 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_OPERATION');
2205 FND_MESSAGE.set_token('SUBJECT_ID', l_subject_id);
2206 FND_MESSAGE.set_token('OBJECT_ID' , l_object_id);
2207 FND_MSG_PUB.add;
2208 x_return_status := fnd_api.g_ret_sts_error; -- cannot swap parent in IB
2209 ELSIF (( l_iir_rec.subject_id <> l_subject_id ) OR
2210 ( l_line_dtl_rec.instance_id <> l_subject_id) OR
2211 ( l_active_end_date <> fnd_api.g_miss_date) ) THEN
2212 BEGIN
2213
2214 SELECT location_type_code
2215 INTO l_loc_type
2216 FROM csi_item_instances
2217 WHERE instance_id = l_iir_rec.subject_id -- either one should not be in Inventory
2218 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2219 EXCEPTION
2220 WHEN OTHERS THEN
2221 x_return_status := fnd_api.g_ret_sts_error; -- unexpected error
2222 END;
2223 IF l_loc_type = 'INVENTORY' THEN
2224 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_OPERATION');
2225 FND_MESSAGE.set_token('SUBJECT_ID', l_subject_id);
2226 FND_MESSAGE.set_token('OBJECT_ID' , l_object_id);
2227 FND_MSG_PUB.add;
2228 x_return_status := fnd_api.g_ret_sts_error; -- this txn is not allowed when in Inventory
2229 END IF;
2230 END IF;
2231 END IF;
2232 ELSIF l_instance_id <> fnd_api.g_miss_num THEN
2233
2234 BEGIN
2235
2236 SELECT 'Y'
2237 INTO l_found
2238 FROM csi_item_instances
2239 WHERE instance_id = l_instance_id
2240 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2241 EXCEPTION
2242 WHEN no_data_found THEN
2243 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_INST_REF');
2244 FND_MESSAGE.set_token('INSTANCE_ID', l_instance_id);
2245 FND_MSG_PUB.add;
2246 x_return_status := fnd_api.g_ret_sts_error;
2247 END;
2248
2249 BEGIN
2250 SELECT 'Y'
2251 INTO l_found
2252 FROM CSI_II_RELATIONSHIPS
2253 WHERE relationship_type_code = 'COMPONENT-OF'
2254 AND subject_id = l_instance_id;
2255 EXCEPTION
2256 WHEN no_data_found THEN
2257 l_found := 'N';
2258 END;
2259
2260 IF l_found = 'Y' THEN
2261 IF l_rel_type = 'COMPONENT-OF' THEN
2262 x_return_status := fnd_api.g_ret_sts_error; -- Multiple parents not allowed for 'COMPONENT-OF'
2263 END IF;
2264 END IF;
2265 END IF; -- l_csi_rel_id <> g_miss / null
2266 EXCEPTION
2267 WHEN fnd_api.g_exc_error THEN
2268 x_return_status := fnd_api.g_ret_sts_error;
2269 END validate_inst_details;
2270
2271 /* Added new routine for M-M Changes */
2272
2273 PROCEDURE validate_src_header (
2274 p_txn_line_id1 IN NUMBER,
2275 p_txn_line_id2 IN NUMBER,
2276 p_rel_type_code IN varchar2,
2277 x_return_status OUT NOCOPY varchar2)
2278
2279 IS
2280
2281 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_src_header';
2282 l_txn_hdr_id1 NUMBER;
2283 l_txn_type_id1 NUMBER;
2284 l_txn_hdr_id2 NUMBER;
2285 l_txn_type_id2 NUMBER;
2286 l_query varchar2(200);
2287 l_txn_line_rec csi_t_transaction_lines%rowtype;
2288
2289 BEGIN
2290
2291 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2292 x_return_status := fnd_api.g_ret_sts_success ;
2293 l_query := 'Select source_txn_header_id, source_transaction_type_id '||
2294 'from csi_t_transaction_lines where transaction_line_id = :line_id';
2295 EXECUTE IMMEDIATE l_query
2296 INTO l_txn_hdr_id1 , l_txn_type_id1
2297 USING p_txn_line_id1;
2298
2299 EXECUTE IMMEDIATE l_query
2300 INTO l_txn_hdr_id2 , l_txn_type_id2
2301 USING p_txn_line_id2;
2302
2303 csi_t_gen_utility_pvt.add('In Validate_src_header :'||'header id1'||l_txn_hdr_id1||'header id2'||l_txn_hdr_id2||'relationship type'||p_rel_type_code);
2304
2305 IF l_txn_hdr_id1 is NULL THEN
2306 l_txn_hdr_id1 := fnd_api.g_miss_num;
2307 END IF;
2308 IF l_txn_hdr_id2 is NULL THEN
2309 l_txn_hdr_id2 := fnd_api.g_miss_num;
2310 END IF;
2311 IF ( p_rel_type_code = 'CONNECTED-TO' AND
2312 (l_txn_hdr_id1 = fnd_api.g_miss_num OR l_txn_hdr_id2 = fnd_api.g_miss_num) ) THEN
2313 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_HDR_ID_REQD');
2314 FND_MESSAGE.set_token('TXN_LINE_ID1',p_txn_line_id1);
2315 FND_MESSAGE.set_token('TXN_LINE_ID2',p_txn_line_id2);
2316 FND_MSG_PUB.add;
2317 Raise fnd_api.g_exc_error;
2318 END IF;
2319
2320 IF ((l_txn_hdr_id1 <> l_txn_hdr_id2 ) AND (l_txn_type_id1 = l_txn_type_id2) ) THEN
2321 FND_MESSAGE.set_name('CSI','CSI_TXN_RLT_XSO_NOT_ALLOWED');
2322 FND_MSG_PUB.add;
2323 Raise fnd_api.g_exc_error;
2324 END IF;
2325
2326 EXCEPTION
2327 WHEN fnd_api.g_exc_error THEN
2328 x_return_status := fnd_api.g_ret_sts_error;
2329 END validate_src_header;
2330
2331 -- Added for CZ Integration (Begin)
2332 PROCEDURE check_exists_in_cz(
2333 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl ,
2334 x_return_status OUT NOCOPY VARCHAR2 )
2335 IS
2336 l_config_hdr_id NUMBER ;
2337 l_td_rec csi_t_datastructures_grp.txn_line_detail_rec ;
2338
2339 CURSOR cz_config_dtl_cur (c_config_inst_hdr_id IN NUMBER ,
2340 c_config_inst_rev_num IN NUMBER ,
2341 c_config_inst_item_id IN NUMBER )
2342 IS
2343 SELECT instance_hdr_id
2344 FROM cz_config_items_v
2345 WHERE instance_hdr_id = c_config_inst_hdr_id
2346 AND instance_rev_nbr = c_config_inst_rev_num
2347 AND config_item_id = c_config_inst_item_id ;
2348 BEGIN
2349
2350 x_return_status := fnd_api.g_ret_sts_success;
2351
2352 IF p_txn_line_dtl_tbl.COUNT > 0
2353 THEN
2354 FOR i IN p_txn_line_dtl_tbl.FIRST .. p_txn_line_dtl_tbl.LAST
2355 LOOP
2356 l_config_hdr_id := NULL ;
2357 l_td_rec := p_txn_line_dtl_tbl(i);
2358
2359 OPEN cz_config_dtl_cur (l_td_rec.config_inst_hdr_id,
2360 l_td_rec.config_inst_rev_num,
2361 l_td_rec.config_inst_item_id ) ;
2362 FETCH cz_config_dtl_cur INTO l_config_hdr_id ;
2363 CLOSE cz_config_dtl_cur ;
2364
2365 IF l_config_hdr_id is NULL
2366 THEN
2367 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_INST_KEY');
2368 fnd_message.set_token('INST_HDR_ID',l_td_rec.config_inst_hdr_id);
2369 fnd_message.set_token('INST_REV_NBR',l_td_rec.config_inst_rev_num);
2370 fnd_message.set_token('CONFIG_ITEM_ID',l_td_rec.config_inst_item_id);
2371 fnd_msg_pub.add;
2372 RAISE fnd_api.g_exc_error;
2373 END IF ;
2374 END LOOP ;
2375 END IF ; ---p_txn_line_dtl_tbl.COUNT
2376 EXCEPTION
2377 WHEN fnd_api.g_exc_error
2378 THEN
2379 x_return_status := fnd_api.g_ret_sts_error;
2380 WHEN OTHERS
2381 THEN
2382 x_return_status := fnd_api.g_ret_sts_error ;
2383 END check_exists_in_cz ;
2384
2385
2386 PROCEDURE get_cz_inst_or_tld_id (
2387 p_config_inst_hdr_id IN NUMBER ,
2388 p_config_inst_rev_num IN NUMBER ,
2389 p_config_inst_item_id IN NUMBER ,
2390 x_instance_id OUT NOCOPY NUMBER ,
2391 x_txn_line_detail_id OUT NOCOPY NUMBER ,
2392 x_return_status OUT NOCOPY VARCHAR2)
2393 IS
2394 l_sysdate DATE ;
2395 CURSOR get_inst_id_cur (c_sysdate IN DATE)
2396 IS
2397 SELECT instance_id
2398 FROM csi_item_instances
2399 WHERE config_inst_hdr_id = p_config_inst_hdr_id
2400 AND config_inst_rev_num = p_config_inst_rev_num
2401 AND config_inst_item_id = p_config_inst_item_id
2402 AND trunc(active_start_date) <= c_sysdate
2403 AND ( trunc(active_end_date) > c_sysdate OR
2404 active_end_date is NULL) ;
2405
2406 CURSOR get_tld_id_cur
2407 IS
2408 SELECT txn_line_detail_id
2409 FROM csi_t_txn_line_details
2410 WHERE config_inst_hdr_id = p_config_inst_hdr_id
2411 AND config_inst_rev_num = p_config_inst_rev_num
2412 AND config_inst_item_id = p_config_inst_item_id ;
2413
2414 BEGIN
2415 csi_t_gen_utility_pvt.add('Begin : in get_cz_inst_or_tld_id ');
2416 csi_t_gen_utility_pvt.add('p_config_inst_hdr_id :'||p_config_inst_hdr_id
2417 ||' p_config_inst_rev_num :'|| p_config_inst_rev_num ||
2418 ' p_config_inst_item_id :'|| p_config_inst_item_id);
2419
2420 x_return_status := fnd_api.g_ret_sts_success ;
2421 x_instance_id := NULL ;
2422 x_txn_line_detail_id := NULL ;
2423
2424 --SELECT TRUNC(sysdate) INTO l_sysdate from dual ;
2425 OPEN get_inst_id_cur (l_sysdate) ;
2426 FETCH get_inst_id_cur INTO x_instance_id ;
2427 CLOSE get_inst_id_cur ;
2428
2429 IF x_instance_id IS NULL
2430 THEN
2431 OPEN get_tld_id_cur ;
2432 FETCH get_tld_id_cur INTO x_txn_line_detail_id ;
2433 CLOSE get_tld_id_cur ;
2434 END IF ;
2435
2436 IF x_instance_id IS NULL
2437 AND x_txn_line_detail_id IS NULL
2438 THEN
2439 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_DATA');
2440 fnd_message.set_token('INST_HDR_ID',p_config_inst_hdr_id);
2441 fnd_message.set_token('INST_REV_NBR',p_config_inst_rev_num);
2442 fnd_message.set_token('CONFIG_ITEM_ID',p_config_inst_item_id);
2443 fnd_msg_pub.add;
2444 RAISE fnd_api.g_exc_error;
2445 END IF ;
2446
2447 EXCEPTION
2448 WHEN fnd_api.g_exc_error
2449 THEN
2450 x_return_status := fnd_api.g_ret_sts_error ;
2451 WHEN OTHERS
2452 THEN
2453 x_return_status := fnd_api.g_ret_sts_error ;
2454 END get_cz_inst_or_tld_id ;
2455
2456
2457 PROCEDURE get_cz_txn_line_id (
2458 p_config_session_hdr_id IN NUMBER ,
2459 p_config_session_rev_num IN NUMBER ,
2460 p_config_session_item_id IN NUMBER ,
2461 x_txn_line_id OUT NOCOPY NUMBER ,
2462 x_return_status OUT NOCOPY VARCHAR2)
2463 IS
2464 CURSOR cz_txn_line_cur
2465 IS
2466 SELECT a.transaction_line_id
2467 FROM csi_t_transaction_lines a
2468 WHERE a.config_session_hdr_id = p_config_session_hdr_id
2469 AND a.config_session_rev_num = p_config_session_rev_num
2470 AND a.config_session_item_id = p_config_session_item_id
2471 AND a.source_transaction_table = 'CONFIGURATOR'; --fix for bug 5632296
2472
2473 BEGIN
2474 csi_t_gen_utility_pvt.add('Begin : in get_cz_txn_line_id ');
2475 x_txn_line_id := NULL ;
2476 x_return_status := fnd_api.g_ret_sts_success ;
2477
2478 OPEN cz_txn_line_cur ;
2479 FETCH cz_txn_line_cur INTO x_txn_line_id ;
2480 CLOSE cz_txn_line_cur ;
2481 csi_t_gen_utility_pvt.add('x_txn_line_id :'|| x_txn_line_id);
2482
2483 EXCEPTION
2484 WHEN OTHERS
2485 THEN
2486 x_return_status := fnd_api.g_ret_sts_error ;
2487 END get_cz_txn_line_id;
2488
2489
2490 PROCEDURE check_cz_session_keys (
2491 p_config_session_hdr_id IN NUMBER ,
2492 p_config_session_rev_num IN NUMBER ,
2493 p_config_session_item_id IN NUMBER ,
2494 x_return_status OUT NOCOPY VARCHAR2)
2495 IS
2496 l_config_session_hdr_id NUMBER ;
2497 CURSOR cz_config_cur
2498 IS
2499 SELECT config_hdr_id
2500 FROM cz_config_items_v
2501 WHERE config_hdr_id = p_config_session_hdr_id
2502 AND config_rev_nbr = p_config_session_rev_num
2503 AND config_item_id = p_config_session_item_id ;
2504 BEGIN
2505
2506 x_return_status := fnd_api.g_ret_sts_success ;
2507 l_config_session_hdr_id := NULL ;
2508 OPEN cz_config_cur ;
2509 FETCH cz_config_cur INTO l_config_session_hdr_id;
2510 CLOSE cz_config_cur ;
2511
2512 IF l_config_session_hdr_id is NULL
2513 THEN
2514 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_SESSION_KEY');
2515 fnd_message.set_token('CONFIG_SESSION_HDR_ID',p_config_session_hdr_id);
2516 fnd_message.set_token('CONFIG_SESSION_REV_NUM',p_config_session_rev_num);
2517 fnd_message.set_token('CONFIG_SESSION_ITEM_ID',p_config_session_item_id);
2518 fnd_msg_pub.add;
2519 RAISE fnd_api.g_exc_error;
2520 END IF ;
2521 EXCEPTION
2522 WHEN fnd_api.g_exc_error
2523 THEN
2524 x_return_status := fnd_api.g_ret_sts_error ;
2525 WHEN OTHERS
2526 THEN
2527 x_return_status := fnd_api.g_ret_sts_error ;
2528 END check_cz_session_keys ;
2529
2530
2531 -- Added for CZ Integration (End)
2532
2533 END csi_t_vldn_routines_pvt;