[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.4.12000000.2 2007/06/12 09:50:11 smrsharm 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 BEGIN
788
789 x_return_status := fnd_api.g_ret_sts_success;
790
791 --loop thru line detail table
792 IF p_txn_line_dtl_tbl.COUNT > 0 THEN
793 FOR l_td_ind IN p_txn_line_dtl_tbl.FIRST.. p_txn_line_dtl_tbl.LAST
794 LOOP
795
796 csi_t_vldn_routines_pvt.check_reqd_param(
797 p_value => p_txn_line_dtl_tbl(l_td_ind).sub_type_id,
798 p_param_name => 'p_txn_line_dtl_rec.sub_type_id',
799 p_api_name => 'check_party_integrity');
800
801 BEGIN
802
803 SELECT src_change_owner,
804 non_src_change_owner
805 INTO l_sub_type_rec.src_change_owner,
806 l_sub_type_rec.non_src_change_owner
807 FROM csi_txn_sub_types
808 WHERE sub_type_id = p_txn_line_dtl_tbl(l_td_ind).sub_type_id
809 AND transaction_type_id = p_txn_line_rec.source_transaction_type_id;
810
811 EXCEPTION
812 WHEN no_data_found THEN
813
814 FND_MESSAGE.set_name('CSI','CSI_TXN_SUB_TYPE_ID_INVALID');
815 FND_MESSAGE.set_token('SUB_TYPE_ID',p_txn_line_dtl_tbl(l_td_ind).sub_type_id);
816 FND_MSG_PUB.add;
817 RAISE FND_API.g_exc_error;
818
819 END;
820
821 l_owner_count := 0;
822
823 IF p_party_dtl_tbl.COUNT > 0 THEN
824 FOR l_index IN p_party_dtl_tbl.FIRST .. p_party_dtl_tbl.LAST
825 LOOP
826 IF p_party_dtl_tbl(l_index).txn_line_details_index = l_td_ind
827 THEN
828 IF p_party_dtl_tbl(l_index).relationship_type_code = 'OWNER' THEN
829 l_owner_count := l_owner_count + 1;
830 END IF;
831 END IF;
832
833 END LOOP;
834 END IF;
835
836 IF (l_owner_count > 1) THEN
837 FND_MESSAGE.set_name('CSI','CSI_TXN_MULTIPLE_OWNER');
838 FND_MESSAGE.set_token('INDEX',l_td_ind);
839 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
840 inventory_item_id);
841 FND_MSG_PUB.add;
842 RAISE fnd_api.g_exc_error;
843 END IF;
844
845 IF p_txn_line_dtl_tbl(l_td_ind).source_transaction_flag = 'Y' THEN
846 IF l_sub_type_rec.src_change_owner = 'Y' THEN
847
848 IF (l_owner_count = 0) THEN
849 FND_MESSAGE.set_name('CSI','CSI_TXN_OWNER_NOT_FOUND');
850 FND_MESSAGE.set_token('INDEX',l_td_ind);
851 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
852 inventory_item_id);
853 FND_MSG_PUB.add;
854 RAISE fnd_api.g_exc_error;
855 END IF;
856
857 END IF;
858 ELSE
859 IF l_sub_type_rec.non_src_change_owner = 'Y' THEN
860
861 IF (l_owner_count = 0) THEN
862 FND_MESSAGE.set_name('CSI','CSI_TXN_OWNER_NOT_FOUND');
863 FND_MESSAGE.set_token('INDEX',l_td_ind);
864 FND_MESSAGE.set_token('ITEM_ID', p_txn_line_dtl_tbl(l_td_ind).
865 inventory_item_id);
866 FND_MSG_PUB.add;
867 RAISE fnd_api.g_exc_error;
868 END IF;
869
870 END IF;
871 END IF;
872
873 END LOOP;
874 END IF;
875
876 EXCEPTION
877 WHEN fnd_api.g_exc_error THEN
878 x_return_status := fnd_api.g_ret_sts_error;
879
880 END check_party_integrity;
881
882 /* private routine used within check_rltns_integrity */ -- Modified this routine to address M-M changes
883 procedure get_iir_details(
884 p_sub_obj_id IN NUMBER,
885 p_object_yn IN varchar2,
886 p_iir_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
887 x_src_dtl_count OUT NOCOPY NUMBER,
888 x_return_status OUT NOCOPY varchar2,
889 x_iir_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl)
890 IS
891 l_loc_ind binary_integer;
892 l_comp_of_count number;
893
894 BEGIN
895 l_loc_ind := 0;
896 l_comp_of_count := 0;
897 x_src_dtl_count := 0;
898 x_return_status := fnd_api.g_ret_sts_success;
899
900 IF p_iir_tbl.COUNT > 0 THEN
901 FOR l_ind IN p_iir_tbl.FIRST .. p_iir_tbl.LAST
902 LOOP
903 IF p_object_yn = 'N' THEN
904
905 IF p_iir_tbl(l_ind).subject_id = p_sub_obj_id THEN
906 IF ( p_iir_tbl(l_ind).subject_type = p_iir_tbl(l_ind).object_type
907 AND p_iir_tbl(l_ind).subject_type = 'I' ) THEN --atleast one of them should be 'T'
908
909 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
910 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
911 FND_MSG_PUB.add;
912 x_return_status := fnd_api.g_ret_sts_error;
913 exit;
914 END IF;
915
916 IF p_iir_tbl(l_ind).relationship_type_code = 'COMPONENT-OF' THEN
917 l_comp_of_count := l_comp_of_count + 1;
918 END IF;
919
920 IF p_iir_tbl(l_ind).object_type = 'T' THEN
921 x_src_dtl_count := x_src_dtl_count + 1; -- count to check if a Non-Source atleast has one TLD tied to it
922 END IF;
923
924 l_loc_ind := l_loc_ind + 1;
925 x_iir_tbl(l_loc_ind).csi_inst_relationship_id :=
926 p_iir_tbl(l_ind).csi_inst_relationship_id;
927 x_iir_tbl(l_loc_ind).subject_id :=
928 p_iir_tbl(l_ind).subject_id;
929 x_iir_tbl(l_loc_ind).object_id :=
930 p_iir_tbl(l_ind).object_id;
931 x_iir_tbl(l_loc_ind).relationship_type_code :=
932 p_iir_tbl(l_ind).relationship_type_code;
933 x_iir_tbl(l_loc_ind).subject_type :=
934 p_iir_tbl(l_ind).subject_type;
935 x_iir_tbl(l_loc_ind).object_type :=
936 p_iir_tbl(l_ind).object_type;
937 x_iir_tbl(l_loc_ind).object_index_flag :=
938 p_iir_tbl(l_ind).object_index_flag;
939 x_iir_tbl(l_loc_ind).subject_index_flag :=
940 p_iir_tbl(l_ind).subject_index_flag;
941 END IF;
942 ELSE -- p_object_yn = 'Y'; repeat the same above
943 IF p_iir_tbl(l_ind).object_id = p_sub_obj_id THEN
944
945 IF ( ( p_iir_tbl(l_ind).subject_type not in ('T','I') ) OR
946 ( p_iir_tbl(l_ind).object_type not in ('T','I') ) ) THEN
947 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
948 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
949 FND_MSG_PUB.add;
950 x_return_status := fnd_api.g_ret_sts_error;
951 exit;
952 ELSIF ( ( p_iir_tbl(l_ind).subject_type = p_iir_tbl(l_ind).object_type) AND
953 (p_iir_tbl(l_ind).object_type = 'I' ) ) THEN --atleast one of them should be 'T'
954
955 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
956 FND_MESSAGE.set_token('TXN_DTL_ID',p_sub_obj_id);
957 FND_MSG_PUB.add;
958 x_return_status := fnd_api.g_ret_sts_error;
959 exit;
960 END IF;
961
962 IF p_iir_tbl(l_ind).relationship_type_code = 'COMPONENT-OF' THEN
963 l_comp_of_count := l_comp_of_count + 1;
964 END IF;
965
966 IF p_iir_tbl(l_ind).subject_type = 'T' THEN
967 x_src_dtl_count := x_src_dtl_count + 1; -- count to check if a Non-Source atleast has one TLD tied to it
968 END IF;
969
970 l_loc_ind := l_loc_ind + 1;
971 x_iir_tbl(l_loc_ind).csi_inst_relationship_id :=
972 p_iir_tbl(l_ind).csi_inst_relationship_id;
973 x_iir_tbl(l_loc_ind).subject_id :=
974 p_iir_tbl(l_ind).subject_id;
975 x_iir_tbl(l_loc_ind).object_id :=
976 p_iir_tbl(l_ind).object_id;
977 x_iir_tbl(l_loc_ind).relationship_type_code :=
978 p_iir_tbl(l_ind).relationship_type_code;
979 x_iir_tbl(l_loc_ind).subject_type :=
980 p_iir_tbl(l_ind).subject_type;
981 x_iir_tbl(l_loc_ind).object_type :=
982 p_iir_tbl(l_ind).object_type;
983 x_iir_tbl(l_loc_ind).object_index_flag :=
984 p_iir_tbl(l_ind).object_index_flag;
985 x_iir_tbl(l_loc_ind).subject_index_flag :=
986 p_iir_tbl(l_ind).subject_index_flag;
987 END IF;
988 END IF; --object_yn = 'N'
989 END LOOP;
990
991 IF l_comp_of_count > 1 THEN
992 x_return_status := fnd_api.g_ret_sts_error; -- this status is then used to determine the multiple comp-of condition
993 END IF;
994
995 END IF;
996 END get_iir_details;
997
998 /* this routine makes sure that if a non sourced line detail is passed then
999 it should be tied to its sourced parent using the item relationship link.
1000 The basic assumption there cannot be a non sourced (configuration item)
1001 line detail hanging in without a parent
1002 Plus added additional checks because of M-M changes
1003 */
1004
1005 PROCEDURE check_rltns_integrity(
1006 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1007 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
1008 x_return_status OUT NOCOPY VARCHAR2)
1009 IS
1010 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1011 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1012 l_line_dtl_g_miss csi_t_datastructures_grp.txn_line_detail_rec;
1013 l_iir_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1014 l_return_status VARCHAR2(1);
1015 l_subject_id NUMBER ;
1016 l_object_id NUMBER ;
1017 l_object_type VARCHAR2(30);
1018 l_subject_type VARCHAR2(30);
1019 l_sub_obj_id NUMBER := fnd_api.g_miss_num;
1020 l_object_yn VARCHAR2(1);
1021 l_line_id1 NUMBER;
1022 l_line_id2 NUMBER;
1023 l_src_flag1 VARCHAR2(1);
1024 l_src_flag2 VARCHAR2(1);
1025 l_src_dtl_count number;
1026 l_subject_index_flag varchar2(1);
1027 l_object_index_flag varchar2(1);
1028
1029
1030 BEGIN
1031
1032 l_line_dtl_tbl := p_txn_line_detail_tbl;
1033
1034 IF l_line_dtl_tbl.COUNT > 0
1035 THEN
1036 IF p_txn_ii_rltns_tbl.COUNT > 0
1037 THEN
1038 FOR l_td_ind IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
1039 LOOP
1040 l_object_yn := 'N';
1041 l_sub_obj_id := l_line_dtl_tbl(l_td_ind).txn_line_detail_id;
1042
1043 IF ( l_sub_obj_id = fnd_api.g_miss_num OR l_sub_obj_id = NULL ) THEN
1044 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_DTL_REF');
1045 FND_MESSAGE.set_token('SUBJECT_ID',l_sub_obj_id);
1046 FND_MESSAGE.set_token('OBJECT_ID', NULL);
1047 FND_MSG_PUB.add;
1048 RAISE FND_API.g_exc_error;
1049 END IF;
1050 -- get the corresponding ii_rltns (where subject_id = line detail index / id)
1051
1052 get_iir_details(
1053 p_sub_obj_id => l_sub_obj_id,
1054 p_object_yn => l_object_yn,
1055 p_iir_tbl => p_txn_ii_rltns_tbl,
1056 x_src_dtl_count => l_src_dtl_count,
1057 x_return_status => l_return_status,
1058 x_iir_tbl => l_iir_tbl); -- first call to see if the TLD was ref. as a subject in the rltns.
1059
1060 IF l_iir_tbl.COUNT = 0 AND l_object_yn = 'N' THEN
1061 l_object_yn := 'Y';
1062
1063 get_iir_details(
1064 p_sub_obj_id => l_sub_obj_id,
1065 p_object_yn => l_object_yn,
1066 p_iir_tbl => p_txn_ii_rltns_tbl,
1067 x_src_dtl_count => l_src_dtl_count,
1068 x_return_status => l_return_status,
1069 x_iir_tbl => l_iir_tbl); -- Second call to see if it's ref. as a object
1070
1071 END IF;
1072 IF l_line_dtl_tbl(l_td_ind).source_transaction_flag = 'N' THEN
1073 IF l_src_dtl_count = 0 THEN
1074
1075 FND_MESSAGE.set_name('CSI','CSI_TXN_NON_SRC_AND_NO_RLTN');
1076 FND_MESSAGE.set_token('INDEX',l_sub_obj_id);
1077 FND_MESSAGE.set_token('ITEM_ID',
1078 l_line_dtl_tbl(l_td_ind).inventory_item_id);
1079 FND_MSG_PUB.add;
1080 RAISE FND_API.g_exc_error;
1081 END IF;
1082 END IF; -- source flag chk = 'N'
1083
1084 IF l_iir_tbl.COUNT > 1 THEN
1085 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
1086 FND_MESSAGE.set_name('CSI','CSI_TXN_MULTIPLE_PARENT');
1087 FND_MESSAGE.set_token('INDEX',l_td_ind);
1088 FND_MESSAGE.set_token('ITEM_ID',
1089 l_line_dtl_tbl(l_td_ind).inventory_item_id);
1090 FND_MSG_PUB.add;
1091 RAISE FND_API.g_exc_error;
1092 END IF;
1093 ELSIF l_return_status <> fnd_api.g_ret_sts_success THEN
1094 RAISE FND_API.g_exc_error;
1095 END IF;
1096
1097 END LOOP;
1098 END IF ; --- p_txn_ii_rltns_tbl.count > 0
1099 END IF;
1100
1101 EXCEPTION
1102 WHEN fnd_api.g_exc_error THEN
1103
1104 x_return_status := fnd_api.g_ret_sts_error;
1105
1106 END check_rltns_integrity;
1107
1108
1109 PROCEDURE convert_rltns_index_to_ids(
1110 p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1111 px_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
1112 x_return_status OUT NOCOPY varchar2)
1113 IS
1114 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1115 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1116 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1117
1118 BEGIN
1119 IF p_line_dtl_tbl.COUNT > 0 THEN
1120 FOR l_td_ind IN p_line_dtl_tbl.FIRST..p_line_dtl_tbl.LAST
1121 LOOP
1122 IF px_ii_rltns_tbl.COUNT > 0 THEN
1123 FOR l_ii_ind IN px_ii_rltns_tbl.FIRST..px_ii_rltns_tbl.LAST
1124 LOOP
1125 /* Added the defaulting of values to the index flags for M-M */
1126
1127 IF nvl(px_ii_rltns_tbl(l_ii_ind).subject_index_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1128 px_ii_rltns_tbl(l_ii_ind).subject_index_flag := 'Y';
1129 END IF;
1130
1131 IF nvl(px_ii_rltns_tbl(l_ii_ind).object_index_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1132 px_ii_rltns_tbl(l_ii_ind).object_index_flag := 'Y';
1133 END IF;
1134
1135 IF px_ii_rltns_tbl(l_ii_ind).subject_type = 'T'
1136 ---m-to-m 05/10
1137 AND nvl(px_ii_rltns_tbl(l_ii_ind).subject_index_flag,'Y') = 'Y'
1138 THEN
1139 csi_t_vldn_routines_pvt.get_txn_line_dtl_rec(
1140 p_index_id => px_ii_rltns_tbl(l_ii_ind).subject_id,
1141 p_txn_line_detail_tbl => p_line_dtl_tbl,
1142 x_txn_line_detail_rec => l_line_dtl_rec,
1143 x_return_status => l_return_status);
1144
1145 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1146 FND_MESSAGE.set_name('CSI','CSI_TXN_SUBJECT_INDEX_INVALID');
1147 FND_MESSAGE.set_token('INDEX_ID',px_ii_rltns_tbl(l_ii_ind).
1148 subject_id);
1149 FND_MSG_PUB.add;
1150 RAISE FND_API.g_exc_error;
1151 END IF;
1152
1153 l_ii_rltns_tbl(l_ii_ind).subject_id :=
1154 l_line_dtl_rec.txn_line_detail_id;
1155 END IF;
1156
1157 IF px_ii_rltns_tbl(l_ii_ind).object_type = 'T'
1158 ---m-to-m 05/10
1159 AND nvl(px_ii_rltns_tbl(l_ii_ind).object_index_flag,'Y') = 'Y'
1160 THEN
1161
1162 csi_t_vldn_routines_pvt.get_txn_line_dtl_rec(
1163 p_index_id => px_ii_rltns_tbl(l_ii_ind).object_id,
1164 p_txn_line_detail_tbl => p_line_dtl_tbl,
1165 x_txn_line_detail_rec => l_line_dtl_rec,
1166 x_return_status => l_return_status);
1167
1168
1169 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1170 FND_MESSAGE.set_name('CSI','CSI_TXN_OBJECT_INDEX_INVALID');
1171 FND_MESSAGE.set_token('INDEX_ID',px_ii_rltns_tbl(l_ii_ind).
1172 object_id);
1173 FND_MSG_PUB.add;
1174 RAISE FND_API.g_exc_error;
1175 END IF;
1176
1177 l_ii_rltns_tbl(l_ii_ind).object_id :=
1178 l_line_dtl_rec.txn_line_detail_id;
1179 END IF;
1180
1181 l_ii_rltns_tbl(l_ii_ind).transaction_line_id :=
1182 p_line_dtl_tbl(l_td_ind).transaction_line_id;
1183
1184 END LOOP;
1185
1186 END IF;
1187 END LOOP;
1188
1189 IF px_ii_rltns_tbl.count > 0 THEN
1190 FOR l_tmp_ind in px_ii_rltns_tbl.FIRST..px_ii_rltns_tbl.LAST
1191 LOOP
1192 IF px_ii_rltns_tbl(l_tmp_ind).object_type = 'T'
1193 ---m-to-m 05/10
1194 AND nvl(px_ii_rltns_tbl(l_tmp_ind).object_index_flag,'Y') = 'Y'
1195 THEN
1196 px_ii_rltns_tbl(l_tmp_ind).object_id :=
1197 l_ii_rltns_tbl(l_tmp_ind).object_id;
1198 END IF;
1199
1200 IF px_ii_rltns_tbl(l_tmp_ind).subject_type = 'T'
1201 ---m-to-m 05/10
1202 AND nvl(px_ii_rltns_tbl(l_tmp_ind).subject_index_flag,'Y') = 'Y'
1203 THEN
1204 px_ii_rltns_tbl(l_tmp_ind).subject_id :=
1205 l_ii_rltns_tbl(l_tmp_ind).subject_id;
1206 END IF;
1207
1208 px_ii_rltns_tbl(l_tmp_ind).transaction_line_id :=
1209 l_ii_rltns_tbl(l_tmp_ind).transaction_line_id;
1210
1211 END LOOP;
1212 END IF;
1213
1214 END IF;
1215 EXCEPTION
1216 WHEN fnd_api.g_exc_error THEN
1217 x_return_status := fnd_api.g_ret_sts_error;
1218 END convert_rltns_index_to_ids;
1219
1220
1221 PROCEDURE is_valid_owner_for_create(
1222 p_txn_line_detail_id IN NUMBER,
1223 p_instance_party_id IN NUMBER,
1224 x_return_status OUT NOCOPY VARCHAR2)
1225 IS
1226
1227 l_found VARCHAR2(1);
1228 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
1229 l_csi_instance_id csi_t_party_details.instance_party_id%TYPE;
1230
1231 CURSOR csi_pty_cur(p_csi_instance_id IN NUMBER) IS
1232 SELECT instance_party_id
1233 FROM csi_i_parties
1234 WHERE instance_id = p_csi_instance_id
1235 AND relationship_type_code = 'OWNER';
1236
1237 BEGIN
1238
1239 x_return_status := fnd_api.g_ret_sts_success;
1240
1241 SELECT instance_exists_flag,
1242 instance_id
1243 INTO l_instance_exists_flag,
1244 l_csi_instance_id
1245 FROM csi_t_txn_line_details
1246 WHERE txn_line_detail_id = p_txn_line_detail_id;
1247
1248 /* if an instance is referred, then look if the instance party is referred
1249 if the instance party is referred then skip the chk.
1250 */
1251 IF NVL(l_instance_exists_flag,'N') = 'Y' THEN
1252
1253 IF nvl(p_instance_party_id ,fnd_api.g_miss_num) = fnd_api.g_miss_num
1254 THEN
1255
1256 FOR csi_pty_rec in csi_pty_cur(l_csi_instance_id)
1257 LOOP
1258 x_return_status := fnd_api.g_ret_sts_error;
1259 exit;
1260 END LOOP;
1261
1262 /* we might have to put the logic if the caller is trying to change the
1263 relationship type code from NON OWNER to OWNER (in the else part)
1264 */
1265
1266 END IF;
1267
1268 ELSE
1269
1270 BEGIN
1271
1272 SELECT 'x' INTO l_found
1273 FROM csi_t_party_details
1274 WHERE txn_line_detail_id = p_txn_line_detail_id
1275 AND relationship_type_code = 'OWNER';
1276
1277 x_return_status := fnd_api.g_ret_sts_error;
1278
1279 EXCEPTION
1280 WHEN no_data_found THEN
1281 x_return_status := fnd_api.g_ret_sts_success;
1282
1283 END;
1284
1285 END IF;
1286 END is_valid_owner_for_create;
1287
1288 procedure get_txn_line_dtl_rec(
1289 p_index_id IN NUMBER,
1290 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1291 x_txn_line_detail_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
1292 x_return_status OUT NOCOPY VARCHAR2)
1293 IS
1294 l_found BOOLEAN := FALSE;
1295 -- l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
1296 BEGIN
1297
1298 IF p_txn_line_detail_tbl.COUNT > 0 THEN
1299
1300 FOR l_ind in p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
1301 LOOP
1302
1303 IF l_ind = p_index_id OR
1304 p_txn_line_detail_tbl(l_ind).txn_line_detail_id = p_index_id THEN
1305 l_found := TRUE;
1306 x_txn_line_detail_rec := p_txn_line_detail_tbl(l_ind);
1307 exit;
1308 END IF;
1309 END LOOP;
1310
1311 END IF;
1312
1313 IF l_found THEN
1314 x_return_status := fnd_api.g_ret_sts_success;
1315 ELSE
1316 x_return_status := fnd_api.g_ret_sts_error;
1317 END IF;
1318
1319 END get_txn_line_dtl_rec;
1320
1321
1322 PROCEDURE get_processing_status(
1323 p_level IN varchar2,
1324 p_level_dtl_id IN number,
1325 x_processing_status OUT NOCOPY varchar2,
1326 x_return_status OUT NOCOPY varchar2)
1327 IS
1328
1329 l_txn_line_dtl_id csi_t_txn_line_details.txn_line_detail_id%TYPE;
1330 l_transaction_line_id csi_t_transaction_lines.transaction_line_id%TYPE;
1331 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
1332
1333 BEGIN
1334
1335 IF p_level in ('PARTY', 'II_RLTNS', 'EXT_ATTRIB', 'ORG_ASSGN') THEN
1336
1337 l_txn_line_dtl_id := p_level_dtl_id;
1338
1339 ELSIF p_level = 'PARTY_ACCT' THEN
1340
1341 SELECT txn_line_detail_id
1342 INTO l_txn_line_dtl_id
1343 FROM csi_t_party_details
1344 WHERE txn_party_detail_id = p_level_dtl_id;
1345
1346 END IF;
1347
1348 SELECT transaction_line_id
1349 INTO l_transaction_line_id
1350 FROM csi_t_txn_line_details
1351 WHERE txn_line_detail_id = l_txn_line_dtl_id;
1352
1353 SELECT nvl(processing_status, '#NONE#')
1354 INTO l_processing_status
1355 FROM csi_t_transaction_lines
1356 WHERE transaction_line_id = l_transaction_line_id;
1357
1358 x_processing_status := l_processing_status;
1359 x_return_status := fnd_api.g_ret_sts_success;
1360
1361 EXCEPTION
1362 WHEN no_data_found THEN
1363 x_return_status := fnd_api.g_ret_sts_error;
1364
1365 END get_processing_status ;
1366
1367 PROCEDURE validate_attrib_source_id(
1368 p_attrib_source_table IN varchar2,
1369 p_attrib_source_id IN number,
1370 x_return_status OUT NOCOPY varchar2)
1371 IS
1372 l_found varchar2(1);
1373
1374 BEGIN
1375
1376 IF p_attrib_source_table = 'CSI_I_EXTENDED_ATTRIBS' THEN
1377
1378 SELECT 'X'
1379 INTO l_found
1380 FROM csi_i_extended_attribs
1381 WHERE attribute_id = p_attrib_source_id;
1382
1383 ELSIF p_attrib_source_table = 'CSI_IEA_VALUES' THEN
1384
1385 SELECT 'X'
1386 INTO l_found
1387 FROM csi_iea_values
1388 WHERE attribute_value_id = p_attrib_source_id;
1389
1390 ELSE
1391
1392 FND_MESSAGE.set_name('CSI','CSI_TXN_ATT_SRC_TBL_INVALID');
1393 FND_MESSAGE.set_token('ATT_SRC_TBL',p_attrib_source_table);
1394 FND_MSG_PUB.add;
1395 RAISE FND_API.g_exc_error;
1396
1397 END IF;
1398
1399 x_return_status := fnd_api.g_ret_sts_success;
1400
1401 EXCEPTION
1402 WHEN no_data_found THEN
1403
1404 FND_MESSAGE.set_name('CSI','CSI_TXN_ATT_SRC_ID_INVALID');
1405 FND_MESSAGE.set_token('ATT_SRC_TBL',p_attrib_source_table);
1406 FND_MESSAGE.set_token('ATT_SRC_ID',p_attrib_source_id);
1407 FND_MSG_PUB.add;
1408 x_return_status := fnd_api.g_ret_sts_error;
1409
1410 WHEN fnd_api.g_exc_error THEN
1411
1412 x_return_status := fnd_api.g_ret_sts_error;
1413
1414 END validate_attrib_source_id;
1415
1416 PROCEDURE validate_party_account_id(
1417 p_party_id IN NUMBER,
1418 p_party_account_id IN NUMBER,
1419 x_return_status OUT NOCOPY VARCHAR2)
1420 IS
1421 l_found varchar2(1);
1422 BEGIN
1423
1424 x_return_status := fnd_api.g_ret_sts_success;
1425
1426 SELECT 'X'
1427 INTO l_found
1428 FROM hz_cust_accounts
1429 WHERE party_id = p_party_id
1430 AND cust_account_id = p_party_account_id;
1431
1432 EXCEPTION
1433 WHEN no_data_found THEN
1434
1435 x_return_status := fnd_api.g_ret_sts_error;
1436
1437 END validate_party_account_id;
1438
1439 PROCEDURE get_instance_ref_info(
1440 p_level IN varchar2,
1441 p_level_dtl_id IN number,
1442 x_instance_id OUT NOCOPY varchar2,
1443 x_instance_exists_flag OUT NOCOPY varchar2,
1444 x_return_status OUT NOCOPY varchar2)
1445 IS
1446
1447 l_txn_line_dtl_id csi_t_txn_line_details.txn_line_detail_id%TYPE;
1448
1449 BEGIN
1450
1451 IF p_level in ('PARTY', 'II_RLTNS', 'EXT_ATTRIB', 'ORG_ASSGN') THEN
1452
1453 l_txn_line_dtl_id := p_level_dtl_id;
1454
1455 ELSIF p_level = 'PARTY_ACCT' THEN
1456
1457 SELECT txn_line_detail_id
1458 INTO l_txn_line_dtl_id
1459 FROM csi_t_party_details
1460 WHERE txn_party_detail_id = p_level_dtl_id;
1461
1462 END IF;
1463
1464 SELECT instance_id ,
1465 instance_exists_flag
1466 INTO x_instance_id,
1467 x_instance_exists_flag
1468 FROM csi_t_txn_line_details
1469 WHERE txn_line_detail_id = l_txn_line_dtl_id;
1470
1471 x_return_status := fnd_api.g_ret_sts_success;
1472
1473 EXCEPTION
1474 WHEN no_data_found THEN
1475 x_return_status := fnd_api.g_ret_sts_error;
1476
1477 END get_instance_ref_info;
1478
1479 PROCEDURE get_party_detail_rec(
1480 p_party_detail_id IN number,
1481 x_party_detail_rec OUT NOCOPY csi_t_party_details%rowtype,
1482 x_return_status OUT NOCOPY varchar2)
1483 IS
1484 BEGIN
1485
1486 SELECT *
1487 INTO x_party_detail_rec
1488 FROM csi_t_party_details
1489 WHERE txn_party_detail_id = p_party_detail_id;
1490
1491 x_return_status := fnd_api.g_ret_sts_success;
1492
1493 EXCEPTION
1494 WHEN no_data_found THEN
1495 x_return_status := fnd_api.g_ret_sts_error;
1496 END get_party_detail_rec;
1497
1498
1499 PROCEDURE validate_instance_id(
1500 p_instance_id IN number,
1501 x_return_status OUT NOCOPY varchar2)
1502 IS
1503 l_found VARCHAR2(1);
1504 BEGIN
1505
1506 SELECT 'X'
1507 INTO l_found
1508 FROM csi_item_instances
1509 WHERE instance_id = p_instance_id;
1510 -- AND unexpired condition
1511
1512 x_return_status := fnd_api.g_ret_sts_success;
1513
1514 EXCEPTION
1515 WHEN no_data_found THEN
1516 x_return_status := fnd_api.g_ret_sts_error;
1517
1518 END validate_instance_id;
1519
1520 procedure validate_instance_reference(
1521 p_level IN varchar2,
1522 p_level_dtl_id IN number,
1523 p_level_inst_ref_id IN number,
1524 x_return_status OUT NOCOPY varchar2)
1525 IS
1526 l_instance_id csi_t_txn_line_details.instance_id%TYPE;
1527 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
1528 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1529
1530 l_td_object_id number;
1531 l_inst_object_id number;
1532 BEGIN
1533
1534 get_instance_ref_info(
1535 p_level => p_level,
1536 p_level_dtl_id => p_level_dtl_id,
1537 x_instance_id => l_instance_id,
1538 x_instance_exists_flag => l_instance_exists_flag,
1539 x_return_status => l_return_status);
1540
1541 IF l_return_status = fnd_api.g_ret_sts_success THEN
1542
1543 --IF nvl(l_instance_exists_flag,'N') <> 'Y' THEN
1544 IF l_instance_id is null THEN -- instance is not refernced
1545
1546 IF p_level <> 'II_RLTNS' THEN
1547 --instance reference not allowed
1548 fnd_message.set_name('CSI','CSI_TXN_INST_REF_NOT_ALLOWED');
1549 fnd_message.set_token('LVL',p_level);
1550 fnd_message.set_token('LVL_DTL_ID',p_level_inst_ref_id);
1551 fnd_msg_pub.add;
1552
1553 raise fnd_api.g_exc_error;
1554 END IF;
1555
1556 END IF;
1557
1558 IF p_level = 'PARTY' THEN
1559
1560 validate_instance_party_id(
1561 p_instance_id => l_instance_id,
1562 p_instance_party_id => p_level_inst_ref_id,
1563 x_return_status => l_return_status);
1564
1565 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1566
1567 fnd_message.set_name('CSI','CSI_TXN_INST_PARTY_REF_INVALID');
1568 fnd_message.set_token('INST_ID',l_instance_id);
1569 fnd_message.set_token('INST_PTY_ID',p_level_inst_ref_id);
1570 fnd_msg_pub.add;
1571
1572 RAISE fnd_api.g_exc_error;
1573 END IF;
1574
1575 ELSIF p_level = 'ORG_ASSGN' THEN
1576
1577 validate_instance_ou_id(
1578 p_instance_id => l_instance_id,
1579 p_instance_ou_id => p_level_inst_ref_id,
1580 x_return_status => l_return_status);
1581
1582 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1583 fnd_message.set_name('CSI','CSI_TXN_INST_OU_REF_INVALID');
1584 fnd_message.set_token('INST_ID',l_instance_id);
1585 fnd_message.set_token('INST_OU_ID',p_level_inst_ref_id);
1586 fnd_msg_pub.add;
1587 raise fnd_api.g_exc_error;
1588 END IF;
1589
1590 ELSIF p_level = 'ORG_ATTRIB' THEN
1591 null;
1592 ELSIF p_level = 'II_RLTNS' THEN
1593
1594 -- in IB
1595 -- get the object id(instance) for the instance_relationship_id
1596
1597 -- in TD
1598 -- get the instance reference for the object_id(txn_line_detail_id)
1599 -- compare both the instances
1600
1601 -- if both are not same then raise exception
1602
1603 IF nvl(l_instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1604
1605 l_td_object_id := l_instance_id;
1606
1607 validate_instance_rltns_id(
1608 p_csi_inst_rltns_id => p_level_inst_ref_id,
1609 x_object_id => l_inst_object_id,
1610 x_return_status => l_return_status);
1611
1612 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1613 null;
1614 -- invalid csi_inst_relation_id
1615 RAISE fnd_api.g_exc_error;
1616 END IF;
1617
1618 IF l_td_object_id <> l_inst_object_id THEN
1619 null;
1620 -- cannot change the parent (object id) in IB
1621 RAISE fnd_api.g_exc_error;
1622
1623 END IF;
1624
1625 END IF;
1626
1627 END IF;
1628
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN fnd_api.g_exc_error THEN
1633 x_return_status := fnd_api.g_ret_sts_error;
1634 END validate_instance_reference;
1635
1636 PROCEDURE validate_account_id(
1637 p_account_id in number,
1638 x_return_status OUT NOCOPY varchar2)
1639 IS
1640 l_found varchar2(1);
1641 BEGIN
1642
1643 SELECT 'X' INTO l_found
1644 FROM hz_cust_accounts
1645 WHERE cust_account_id = p_account_id;
1646
1647 x_return_status := fnd_api.g_ret_sts_success;
1648
1649 EXCEPTION
1650 when no_data_found then
1651 x_return_status := fnd_api.g_ret_sts_error;
1652
1653 END validate_account_id;
1654
1655 PROCEDURE validate_site_use_id(
1656 p_account_id IN number,
1657 p_site_use_id IN number,
1658 p_site_use_code IN varchar2,
1659 x_return_status OUT NOCOPY varchar2)
1660 IS
1661
1662 CURSOR site_cur IS
1663 SELECT csu.site_use_id
1664 FROM hz_cust_site_uses_all csu
1665 WHERE csu.site_use_id = p_site_use_id
1666 AND csu.site_use_code = p_site_use_code;
1667
1668 /* BUG # 2159414
1669 CURSOR site_cur IS
1670 SELECT csu.site_use_id
1671 FROM hz_cust_site_uses_all csu ,
1672 hz_cust_acct_sites_all cas
1673 WHERE csu.site_use_id = p_site_use_id
1674 AND csu.site_use_code = p_site_use_code
1675 AND csu.cust_acct_site_id = cas.cust_acct_site_id
1676 AND cas.cust_account_id = p_account_id;
1677 */
1678
1679 l_found BOOLEAN;
1680
1681 BEGIN
1682 l_found := FALSE;
1683
1684 FOR site_rec in site_cur
1685 LOOP
1686 l_found := TRUE;
1687 exit;
1688 END LOOP;
1689
1690 IF l_found THEN
1691 x_return_status := fnd_api.g_ret_sts_success;
1692 ELSE
1693 x_return_status := fnd_api.g_ret_sts_error;
1694 END IF;
1695
1696 END validate_site_use_id;
1697
1698 PROCEDURE get_txn_system_id(
1699 p_txn_systems_index IN number,
1700 p_txn_systems_tbl IN csi_t_datastructures_grp.txn_systems_tbl,
1701 x_txn_system_id OUT NOCOPY number,
1702 x_return_status OUT NOCOPY varchar2)
1703 IS
1704 BEGIN
1705
1706 x_txn_system_id := fnd_api.g_miss_num;
1707
1708 IF nvl(p_txn_systems_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1709 IF p_txn_systems_tbl.COUNT > 0 THEN
1710 FOR l_ind in p_txn_systems_tbl.FIRST .. p_txn_systems_tbl.LAST
1711 LOOP
1712
1713 IF l_ind = p_txn_systems_index THEN
1714 x_txn_system_id := p_txn_systems_tbl(l_ind).transaction_system_id;
1715 exit;
1716 END IF;
1717
1718 END LOOP;
1719 END IF;
1720 END IF;
1721 END get_txn_system_id;
1722
1723 PROCEDURE get_txn_systems_index(
1724 p_txn_system_id IN number,
1725 p_txn_systems_tbl IN csi_t_datastructures_grp.txn_systems_tbl,
1726 x_txn_systems_index OUT NOCOPY number,
1727 x_return_status OUT NOCOPY varchar2)
1728 IS
1729 BEGIN
1730
1731 x_txn_systems_index := fnd_api.g_miss_num;
1732
1733 IF nvl(p_txn_system_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1734 IF p_txn_systems_tbl.COUNT > 0 THEN
1735 FOR l_ind in p_txn_systems_tbl.FIRST .. p_txn_systems_tbl.LAST
1736 LOOP
1737
1738 IF p_txn_systems_tbl(l_ind).transaction_system_id = p_txn_system_id THEN
1739 x_txn_systems_index := l_ind;
1740 exit;
1741 END IF;
1742
1743 END LOOP;
1744 END IF;
1745 END IF;
1746
1747 END get_txn_systems_index;
1748
1749 PROCEDURE validate_contact_flag(
1750 p_contact_flag in varchar2,
1751 x_return_status OUT NOCOPY varchar2)
1752 IS
1753 BEGIN
1754 x_return_status := fnd_api.g_ret_sts_success;
1755 IF p_contact_flag not in ('Y','N') THEN
1756 x_return_status := fnd_api.g_ret_sts_error;
1757 END IF;
1758 END validate_contact_flag;
1759
1760 /* validtion routine for sub_type_id */
1761 PROCEDURE validate_sub_type_id(
1762 p_transaction_line_id IN number,
1763 p_sub_type_id IN number,
1764 x_return_status OUT NOCOPY varchar2)
1765 IS
1766
1767 l_txn_type_id number;
1768
1769 CURSOR sub_type_cur(p_txn_type_id IN number) IS
1770 SELECT 'X'
1771 FROM csi_txn_sub_types
1772 WHERE sub_type_id = p_sub_type_id
1773 AND transaction_type_id = nvl(p_txn_type_id, transaction_type_id);
1774
1775 BEGIN
1776
1777 x_return_status := fnd_api.g_ret_sts_error;
1778
1779 SELECT source_transaction_type_id
1780 INTO l_txn_type_id
1781 FROM csi_t_transaction_lines
1782 WHERE transaction_line_id = p_transaction_line_id;
1783
1784 FOR sub_type_rec in sub_type_cur(l_txn_type_id)
1785 LOOP
1786 x_return_status := fnd_api.g_ret_sts_success;
1787 exit;
1788 END LOOP;
1789
1790
1791 EXCEPTION
1792 WHEN no_data_found THEN
1793 x_return_status := fnd_api.g_ret_sts_error;
1794 END validate_sub_type_id;
1795
1796 PROCEDURE check_duplicate(
1797 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
1798 x_return_status OUT NOCOPY varchar2)
1799 IS
1800 l_td_found char;
1801 BEGIN
1802
1803 SELECT 'x'
1804 INTO l_td_found
1805 FROM csi_t_transaction_lines
1806 WHERE source_transaction_table = p_txn_line_rec.source_transaction_table
1807 AND source_transaction_id = p_txn_line_rec.source_transaction_id;
1808
1809 x_return_status := fnd_api.g_ret_sts_error;
1810
1811 EXCEPTION
1812 WHEN no_data_found THEN
1813 x_return_status := fnd_api.g_ret_sts_success;
1814 END check_duplicate;
1815
1816 PROCEDURE validate_lot_number(
1817 p_inventory_item_id IN number,
1818 p_organization_id IN number,
1819 p_lot_number IN varchar2,
1820 x_return_status OUT NOCOPY varchar2)
1821 IS
1822 l_lot_control_code mtl_system_items.lot_control_code%TYPE;
1823 l_item_name mtl_system_items.segment1%TYPE;
1824 l_found char;
1825 BEGIN
1826
1827 x_return_status := fnd_api.g_ret_sts_success;
1828
1829 -- check whether the item is under lot control or not...
1830
1831 -- 1 - No Control
1832 -- 2 - Full Control
1833
1834 BEGIN
1835 SELECT lot_control_code,
1836 segment1
1837 INTO l_lot_control_code,
1838 l_item_name
1839 FROM mtl_system_items
1840 WHERE inventory_item_id = p_inventory_item_id
1841 AND organization_id = p_organization_id;
1842 EXCEPTION
1843 WHEN no_data_found THEN
1844
1845 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
1846 fnd_message.set_token('INVENTORY_ITEM_ID',p_inventory_item_id);
1847 fnd_message.set_token('INV_ORGANIZATION_ID',p_organization_id);
1848 fnd_msg_pub.add;
1849
1850 RAISE fnd_api.g_exc_error;
1851 END;
1852
1853 IF l_lot_control_code = 2 THEN
1854
1855 BEGIN
1856
1857 SELECT 'X'
1858 INTO l_found
1859 FROM mtl_lot_numbers
1860 WHERE inventory_item_id = p_inventory_item_id
1861 AND lot_number = p_lot_number;
1862
1863 -- AND organization_id = p_organization_id
1864 -- commenting this as from order management the inventory organization
1865 -- is not visible. Only the validation organization is passed
1866
1867 EXCEPTION
1868
1869 WHEN no_data_found THEN
1870 fnd_message.set_name('CSI','CSI_API_INVALID_LOT_NUM');
1871 fnd_message.set_token('LOT_NUMBER',p_lot_number);
1872 fnd_msg_pub.add;
1873 RAISE fnd_api.g_exc_error;
1874
1875 -- adding this because I took of the organization check
1876 WHEN too_many_rows THEN
1877 null;
1878 END;
1879
1880 ELSE
1881 fnd_message.set_name('CSI', 'CSI_API_NOT_LOT_CONTROLLED');
1882 fnd_message.set_token('LOT_NUMBER', l_item_name);
1883 fnd_msg_pub.add;
1884 END IF;
1885
1886 EXCEPTION
1887 WHEN fnd_api.g_exc_error THEN
1888 x_return_status := fnd_api.g_ret_sts_error;
1889 END validate_lot_number;
1890
1891 PROCEDURE validate_serial_number(
1892 p_inventory_item_id IN number,
1893 p_organization_id IN number,
1894 p_serial_number IN varchar2,
1895 x_return_status OUT NOCOPY varchar2)
1896 IS
1897 l_serial_control_code mtl_system_items.serial_number_control_code%TYPE;
1898 l_item_name mtl_system_items.segment1%TYPE;
1899 l_found char;
1900 BEGIN
1901
1902 x_return_status := fnd_api.g_ret_sts_success;
1903
1904 -- check whether the item is serial controlled or not
1905
1906 -- '1' No serial number control
1907 -- '2' Predefined serial numbers
1908 -- '5' Dynamic entry at inventory receipt
1909 -- '6' Dynamic entry at sales order issue
1910
1911 BEGIN
1912 SELECT serial_number_control_code,
1913 segment1
1914 INTO l_serial_control_code,
1915 l_item_name
1916 FROM mtl_system_items
1917 WHERE inventory_item_id = p_inventory_item_id
1918 AND organization_id = p_organization_id;
1919 EXCEPTION
1920
1921 WHEN no_data_found THEN
1922
1923 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
1924 fnd_message.set_token('INVENTORY_ITEM_ID',p_inventory_item_id);
1925 fnd_message.set_token('INV_ORGANIZATION_ID',p_organization_id);
1926 fnd_msg_pub.add;
1927
1928 RAISE fnd_api.g_exc_error;
1929 END;
1930
1931 IF l_serial_control_code <> 1 THEN
1932
1933 BEGIN
1934
1935 SELECT 'X'
1936 INTO l_found
1937 FROM mtl_serial_numbers
1938 WHERE inventory_item_id = p_inventory_item_id
1939 AND serial_number = p_serial_number;
1940
1941 -- AND current_organization_id = p_organization_id
1942 -- commenting this as from order management the inventory organization
1943 -- is not visible. Only the validation organization is passed
1944
1945 EXCEPTION
1946 WHEN no_data_found THEN
1947
1948 fnd_message.set_name('CSI','CSI_API_INVALID_SERIAL_NUM');
1949 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1950 fnd_msg_pub.add;
1951 RAISE fnd_api.g_exc_error;
1952
1953 -- adding this because I took of the organization check
1954 WHEN too_many_rows THEN
1955 null;
1956 END;
1957
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN fnd_api.g_exc_error THEN
1962 x_return_status := fnd_api.g_ret_sts_error;
1963 END validate_serial_number;
1964
1965 /* Added this new routine for M-M Changes */
1966
1967 PROCEDURE validate_txn_rltnshp (
1968 p_txn_line_detail_rec1 IN csi_t_datastructures_grp.txn_line_detail_rec,
1969 p_txn_line_detail_rec2 IN csi_t_datastructures_grp.txn_line_detail_rec,
1970 p_iir_rec IN csi_t_datastructures_grp.txn_ii_rltns_rec,
1971 x_return_status OUT NOCOPY varchar2)
1972 IS
1973
1974 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_txn_rltnshp';
1975 l_line_dtl_rec1 csi_t_datastructures_grp.txn_line_detail_rec;
1976 l_line_dtl_rec2 csi_t_datastructures_grp.txn_line_detail_rec;
1977 l_ii_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
1978 l_subject_id NUMBER;
1979 l_object_id NUMBER;
1980 l_object_type VARCHAR2(30);
1981 l_subject_type VARCHAR2(30);
1982 l_sub_obj_id NUMBER;
1983 l_object_yn VARCHAR2(1);
1984 l_csi_rel_id NUMBER;
1985 l_rel_type VARCHAR2(30);
1986 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1987
1988
1989 CURSOR txn_ii_rltns_cur (c_subject_id IN NUMBER,
1990 c_object_id IN NUMBER ,
1991 c_relationship_type_code IN VARCHAR2)
1992 IS
1993 SELECT txn_relationship_id , object_type, subject_type
1994 FROM csi_t_ii_relationships
1995 WHERE subject_id = c_subject_id
1996 AND object_id = c_object_id
1997 AND relationship_type_code = c_relationship_type_code
1998 AND NVL(active_end_date , SYSDATE) >= SYSDATE ;
1999
2000 l_txn_relationship_id NUMBER := NULL ;
2001 l_sub_type VARCHAR2(30);
2002 l_obj_type VARCHAR2(30);
2003
2004 /* Validations performed :: If both sub and obj are TLD's then
2005 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
2006 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
2007 iii)Sub and Obj have source flag= 'N' -- not allowed
2008 iv) Validate instance references
2009 v) validate the relationship for bus rules if instance is involved
2010 */
2011 BEGIN
2012 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2013 l_line_dtl_rec1 := p_txn_line_detail_rec1;
2014 l_line_dtl_rec2 := p_txn_line_detail_rec2;
2015 l_ii_rltns_rec := p_iir_rec;
2016 l_object_type := l_ii_rltns_rec.object_type;
2017 l_object_id := l_ii_rltns_rec.object_id;
2018 l_subject_type := l_ii_rltns_rec.subject_type;
2019 l_subject_id := l_ii_rltns_rec.subject_id;
2020 l_csi_rel_id := l_ii_rltns_rec.csi_inst_relationship_id;
2021 l_rel_type := l_ii_rltns_rec.relationship_type_code;
2022 x_return_status := fnd_api.g_ret_sts_success ;
2023
2024 IF l_line_dtl_rec1.txn_line_detail_id = fnd_api.g_miss_num THEN
2025 -- call validate_inst_details to validate it in instance context
2026
2027 validate_inst_details (
2028 p_iir_rec => l_ii_rltns_rec,
2029 p_txn_dtl_rec => l_line_dtl_rec2,
2030 x_return_status => l_return_status );
2031
2032 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2033 RAISE fnd_api.g_exc_error;
2034 END IF;
2035
2036 ELSIF l_line_dtl_rec2.txn_line_detail_id = fnd_api.g_miss_num THEN
2037
2038 validate_inst_details (
2039 p_iir_rec => l_ii_rltns_rec,
2040 p_txn_dtl_rec => l_line_dtl_rec1,
2041 x_return_status => l_return_status );
2042
2043 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2044 RAISE fnd_api.g_exc_error;
2045 END IF;
2046 ELSE
2047 /* validate the source txn hdr ID */
2048 ---Bypass this check if the source is Configurator
2049 -- Added for CZ Integration
2050 IF (NVL(l_line_dtl_rec1.config_inst_hdr_id , fnd_api.g_miss_num)
2051 = fnd_api.g_miss_num AND
2052 NVL(l_line_dtl_rec2.config_inst_hdr_id , fnd_api.g_miss_num)
2053 = fnd_api.g_miss_num )
2054 THEN
2055 validate_src_header (
2056 p_txn_line_id1 => l_line_dtl_rec1.transaction_line_id,
2057 p_txn_line_id2 => l_line_dtl_rec2.transaction_line_id,
2058 p_rel_type_code => l_ii_rltns_rec.relationship_type_code,
2059 x_return_status => l_return_status);
2060 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2061 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_REL');
2062 FND_MESSAGE.set_token('TXN_DTL_ID1', l_line_dtl_rec1.txn_line_detail_id);
2063 FND_MESSAGE.set_token('TXN_DTL_ID2', l_line_dtl_rec2.txn_line_detail_id);
2064 FND_MSG_PUB.add;
2065 RAISE fnd_api.g_exc_error;
2066 END IF;
2067 END IF ;
2068
2069 IF ( l_line_dtl_rec1.transaction_line_id = l_line_dtl_rec2.transaction_line_id )
2070 AND l_ii_rltns_rec.relationship_type_code <> 'CONNECTED-TO' THEN
2071 IF l_line_dtl_rec1.source_transaction_flag = l_line_dtl_rec2.source_transaction_flag THEN
2072 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_TXN_REL');
2073 FND_MESSAGE.set_token('TXN_DTL_ID1', l_line_dtl_rec1.txn_line_detail_id);
2074 FND_MESSAGE.set_token('TXN_DTL_ID2', l_line_dtl_rec2.txn_line_detail_id);
2075 FND_MSG_PUB.add;
2076 RAISE fnd_api.g_exc_error;
2077 END IF;
2078 END IF;
2079
2080 END IF;
2081
2082 l_txn_relationship_id := NULL ;
2083 OPEN txn_ii_rltns_cur (l_ii_rltns_rec.subject_id,
2084 l_ii_rltns_rec.object_id,
2085 l_ii_rltns_rec.relationship_type_code) ;
2086
2087 FETCH txn_ii_rltns_cur INTO l_txn_relationship_id ,
2088 l_obj_type,
2089 l_sub_type;
2090 CLOSE txn_ii_rltns_cur ;
2091 IF nvl(l_ii_rltns_rec.txn_relationship_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2092 IF ( l_ii_rltns_rec.object_type = l_obj_type
2093 AND l_ii_rltns_rec.subject_type = l_sub_type) THEN
2094 IF l_txn_relationship_id IS NOT NULL
2095 AND l_ii_rltns_rec.txn_relationship_id <> l_txn_relationship_id
2096 THEN
2097 FND_MESSAGE.set_name('CSI','CSI_TXN_DUP_RLTNS');
2098 FND_MSG_PUB.add;
2099 RAISE fnd_api.g_exc_error;
2100 END IF ;
2101 END IF;
2102 END IF;
2103
2104 EXCEPTION
2105 WHEN fnd_api.g_exc_error THEN
2106 x_return_status := fnd_api.g_ret_sts_error;
2107 END validate_txn_rltnshp ;
2108
2109 /* Added new routine for M-M Changes */
2110
2111 PROCEDURE validate_inst_details (
2112 p_iir_rec IN csi_t_datastructures_grp.txn_ii_rltns_rec,
2113 p_txn_dtl_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
2114 x_return_status OUT NOCOPY varchar2)
2115
2116 IS
2117 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_inst_details';
2118 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
2119 l_txn_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
2120 l_iir_rec csi_ii_relationships%rowtype;
2121 l_subject_id NUMBER;
2122 l_object_id NUMBER;
2123 l_object_type VARCHAR2(30);
2124 l_subject_type VARCHAR2(30);
2125 l_csi_rel_id NUMBER;
2126 l_rel_type VARCHAR2(30);
2127 l_active_end_date DATE;
2128 l_instance_id NUMBER;
2129 l_found VARCHAR2(1) := 'N';
2130 l_loc_type VARCHAR2(30);
2131
2132 BEGIN
2133 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2134 l_line_dtl_rec := p_txn_dtl_rec;
2135 l_txn_rltns_rec := p_iir_rec;
2136 l_object_type := l_txn_rltns_rec.object_type;
2137 l_object_id := l_txn_rltns_rec.object_id;
2138 l_subject_type := l_txn_rltns_rec.subject_type;
2139 l_subject_id := l_txn_rltns_rec.subject_id;
2140 l_csi_rel_id := l_txn_rltns_rec.csi_inst_relationship_id;
2141 l_rel_type := l_txn_rltns_rec.relationship_type_code;
2142 x_return_status := fnd_api.g_ret_sts_success ;
2143
2144 IF l_txn_rltns_rec.subject_type = 'I' THEN
2145 l_instance_id := l_subject_id;
2146 ELSIF l_txn_rltns_rec.object_type = 'I' THEN
2147 l_instance_id := l_object_id;
2148 ELSE
2149 l_instance_id := fnd_api.g_miss_num;
2150 END IF;
2151 IF l_txn_rltns_rec.csi_inst_relationship_id = NULL THEN
2152 l_csi_rel_id := fnd_api.g_miss_num;
2153 END IF;
2154 IF l_txn_rltns_rec.active_end_date = NULL THEN
2155 l_active_end_date := fnd_api.g_miss_date;
2156 END IF;
2157
2158 IF l_csi_rel_id <> fnd_api.g_miss_num THEN
2159
2160 BEGIN
2161
2162 SELECT subject_id,
2163 object_id,
2164 relationship_type_code
2165 INTO l_iir_rec.subject_id,
2166 l_iir_rec.object_id,
2167 l_iir_rec.relationship_type_code
2168 FROM csi_ii_relationships
2169 WHERE relationship_id = l_csi_rel_id
2170 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2171 EXCEPTION
2172 WHEN no_data_found THEN
2173 x_return_status := fnd_api.g_ret_sts_error;
2174 END ;
2175 IF l_iir_rec.relationship_type_code = 'COMPONENT-OF' THEN
2176 IF l_iir_rec.object_id <> l_object_id THEN
2177 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_OPERATION');
2178 FND_MESSAGE.set_token('SUBJECT_ID', l_subject_id);
2179 FND_MESSAGE.set_token('OBJECT_ID' , l_object_id);
2180 FND_MSG_PUB.add;
2181 x_return_status := fnd_api.g_ret_sts_error; -- cannot swap parent in IB
2182 ELSIF (( l_iir_rec.subject_id <> l_subject_id ) OR
2183 ( l_line_dtl_rec.instance_id <> l_subject_id) OR
2184 ( l_active_end_date <> fnd_api.g_miss_date) ) THEN
2185 BEGIN
2186
2187 SELECT location_type_code
2188 INTO l_loc_type
2189 FROM csi_item_instances
2190 WHERE instance_id = l_iir_rec.subject_id -- either one should not be in Inventory
2191 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 x_return_status := fnd_api.g_ret_sts_error; -- unexpected error
2195 END;
2196 IF l_loc_type = 'INVENTORY' THEN
2197 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_OPERATION');
2198 FND_MESSAGE.set_token('SUBJECT_ID', l_subject_id);
2199 FND_MESSAGE.set_token('OBJECT_ID' , l_object_id);
2200 FND_MSG_PUB.add;
2201 x_return_status := fnd_api.g_ret_sts_error; -- this txn is not allowed when in Inventory
2202 END IF;
2203 END IF;
2204 END IF;
2205 ELSIF l_instance_id <> fnd_api.g_miss_num THEN
2206
2207 BEGIN
2208
2209 SELECT 'Y'
2210 INTO l_found
2211 FROM csi_item_instances
2212 WHERE instance_id = l_instance_id
2213 AND sysdate between nvl(active_start_date, sysdate) and nvl(active_end_date,sysdate);
2214 EXCEPTION
2215 WHEN no_data_found THEN
2216 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_INST_REF');
2217 FND_MESSAGE.set_token('INSTANCE_ID', l_instance_id);
2218 FND_MSG_PUB.add;
2219 x_return_status := fnd_api.g_ret_sts_error;
2220 END;
2221
2222 BEGIN
2223 SELECT 'Y'
2224 INTO l_found
2225 FROM CSI_II_RELATIONSHIPS
2226 WHERE relationship_type_code = 'COMPONENT-OF'
2227 AND subject_id = l_instance_id;
2228 EXCEPTION
2229 WHEN no_data_found THEN
2230 l_found := 'N';
2231 END;
2232
2233 IF l_found = 'Y' THEN
2234 IF l_rel_type = 'COMPONENT-OF' THEN
2235 x_return_status := fnd_api.g_ret_sts_error; -- Multiple parents not allowed for 'COMPONENT-OF'
2236 END IF;
2237 END IF;
2238 END IF; -- l_csi_rel_id <> g_miss / null
2239 EXCEPTION
2240 WHEN fnd_api.g_exc_error THEN
2241 x_return_status := fnd_api.g_ret_sts_error;
2242 END validate_inst_details;
2243
2244 /* Added new routine for M-M Changes */
2245
2246 PROCEDURE validate_src_header (
2247 p_txn_line_id1 IN NUMBER,
2248 p_txn_line_id2 IN NUMBER,
2249 p_rel_type_code IN varchar2,
2250 x_return_status OUT NOCOPY varchar2)
2251
2252 IS
2253
2254 l_routine_name CONSTANT VARCHAR2(30) := 'vldn.validate_src_header';
2255 l_txn_hdr_id1 NUMBER;
2256 l_txn_type_id1 NUMBER;
2257 l_txn_hdr_id2 NUMBER;
2258 l_txn_type_id2 NUMBER;
2259 l_query varchar2(200);
2260 l_txn_line_rec csi_t_transaction_lines%rowtype;
2261
2262 BEGIN
2263
2264 csi_t_gen_utility_pvt.add('Begin : '||l_routine_name);
2265 x_return_status := fnd_api.g_ret_sts_success ;
2266 l_query := 'Select source_txn_header_id, source_transaction_type_id '||
2267 'from csi_t_transaction_lines where transaction_line_id = :line_id';
2268 EXECUTE IMMEDIATE l_query
2269 INTO l_txn_hdr_id1 , l_txn_type_id1
2270 USING p_txn_line_id1;
2271
2272 EXECUTE IMMEDIATE l_query
2273 INTO l_txn_hdr_id2 , l_txn_type_id2
2274 USING p_txn_line_id2;
2275
2276 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);
2277
2278 IF l_txn_hdr_id1 is NULL THEN
2279 l_txn_hdr_id1 := fnd_api.g_miss_num;
2280 END IF;
2281 IF l_txn_hdr_id2 is NULL THEN
2282 l_txn_hdr_id2 := fnd_api.g_miss_num;
2283 END IF;
2284 IF ( p_rel_type_code = 'CONNECTED-TO' AND
2285 (l_txn_hdr_id1 = fnd_api.g_miss_num OR l_txn_hdr_id2 = fnd_api.g_miss_num) ) THEN
2286 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_HDR_ID_REQD');
2287 FND_MESSAGE.set_token('TXN_LINE_ID1',p_txn_line_id1);
2288 FND_MESSAGE.set_token('TXN_LINE_ID2',p_txn_line_id2);
2289 FND_MSG_PUB.add;
2290 Raise fnd_api.g_exc_error;
2291 END IF;
2292
2293 IF ((l_txn_hdr_id1 <> l_txn_hdr_id2 ) AND (l_txn_type_id1 = l_txn_type_id2) ) THEN
2294 FND_MESSAGE.set_name('CSI','CSI_TXN_RLT_XSO_NOT_ALLOWED');
2295 FND_MSG_PUB.add;
2296 Raise fnd_api.g_exc_error;
2297 END IF;
2298
2299 EXCEPTION
2300 WHEN fnd_api.g_exc_error THEN
2301 x_return_status := fnd_api.g_ret_sts_error;
2302 END validate_src_header;
2303
2304 -- Added for CZ Integration (Begin)
2305 PROCEDURE check_exists_in_cz(
2306 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl ,
2307 x_return_status OUT NOCOPY VARCHAR2 )
2308 IS
2309 l_config_hdr_id NUMBER ;
2310 l_td_rec csi_t_datastructures_grp.txn_line_detail_rec ;
2311
2312 CURSOR cz_config_dtl_cur (c_config_inst_hdr_id IN NUMBER ,
2313 c_config_inst_rev_num IN NUMBER ,
2314 c_config_inst_item_id IN NUMBER )
2315 IS
2316 SELECT instance_hdr_id
2317 FROM cz_config_items_v
2318 WHERE instance_hdr_id = c_config_inst_hdr_id
2319 AND instance_rev_nbr = c_config_inst_rev_num
2320 AND config_item_id = c_config_inst_item_id ;
2321 BEGIN
2322
2323 x_return_status := fnd_api.g_ret_sts_success;
2324
2325 IF p_txn_line_dtl_tbl.COUNT > 0
2326 THEN
2327 FOR i IN p_txn_line_dtl_tbl.FIRST .. p_txn_line_dtl_tbl.LAST
2328 LOOP
2329 l_config_hdr_id := NULL ;
2330 l_td_rec := p_txn_line_dtl_tbl(i);
2331
2332 OPEN cz_config_dtl_cur (l_td_rec.config_inst_hdr_id,
2333 l_td_rec.config_inst_rev_num,
2334 l_td_rec.config_inst_item_id ) ;
2335 FETCH cz_config_dtl_cur INTO l_config_hdr_id ;
2336 CLOSE cz_config_dtl_cur ;
2337
2338 IF l_config_hdr_id is NULL
2339 THEN
2340 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_INST_KEY');
2341 fnd_message.set_token('INST_HDR_ID',l_td_rec.config_inst_hdr_id);
2342 fnd_message.set_token('INST_REV_NBR',l_td_rec.config_inst_rev_num);
2343 fnd_message.set_token('CONFIG_ITEM_ID',l_td_rec.config_inst_item_id);
2344 fnd_msg_pub.add;
2345 RAISE fnd_api.g_exc_error;
2346 END IF ;
2347 END LOOP ;
2348 END IF ; ---p_txn_line_dtl_tbl.COUNT
2349 EXCEPTION
2350 WHEN fnd_api.g_exc_error
2351 THEN
2352 x_return_status := fnd_api.g_ret_sts_error;
2353 WHEN OTHERS
2354 THEN
2355 x_return_status := fnd_api.g_ret_sts_error ;
2356 END check_exists_in_cz ;
2357
2358
2359 PROCEDURE get_cz_inst_or_tld_id (
2360 p_config_inst_hdr_id IN NUMBER ,
2361 p_config_inst_rev_num IN NUMBER ,
2362 p_config_inst_item_id IN NUMBER ,
2363 x_instance_id OUT NOCOPY NUMBER ,
2364 x_txn_line_detail_id OUT NOCOPY NUMBER ,
2365 x_return_status OUT NOCOPY VARCHAR2)
2366 IS
2367 l_sysdate DATE ;
2368 CURSOR get_inst_id_cur (c_sysdate IN DATE)
2369 IS
2370 SELECT instance_id
2371 FROM csi_item_instances
2372 WHERE config_inst_hdr_id = p_config_inst_hdr_id
2373 AND config_inst_rev_num = p_config_inst_rev_num
2374 AND config_inst_item_id = p_config_inst_item_id
2375 AND trunc(active_start_date) <= c_sysdate
2376 AND ( trunc(active_end_date) > c_sysdate OR
2377 active_end_date is NULL) ;
2378
2379 CURSOR get_tld_id_cur
2380 IS
2381 SELECT txn_line_detail_id
2382 FROM csi_t_txn_line_details
2383 WHERE config_inst_hdr_id = p_config_inst_hdr_id
2384 AND config_inst_rev_num = p_config_inst_rev_num
2385 AND config_inst_item_id = p_config_inst_item_id ;
2386
2387 BEGIN
2388 csi_t_gen_utility_pvt.add('Begin : in get_cz_inst_or_tld_id ');
2389 csi_t_gen_utility_pvt.add('p_config_inst_hdr_id :'||p_config_inst_hdr_id
2390 ||' p_config_inst_rev_num :'|| p_config_inst_rev_num ||
2391 ' p_config_inst_item_id :'|| p_config_inst_item_id);
2392
2393 x_return_status := fnd_api.g_ret_sts_success ;
2394 x_instance_id := NULL ;
2395 x_txn_line_detail_id := NULL ;
2396
2397 --SELECT TRUNC(sysdate) INTO l_sysdate from dual ;
2398 OPEN get_inst_id_cur (l_sysdate) ;
2399 FETCH get_inst_id_cur INTO x_instance_id ;
2400 CLOSE get_inst_id_cur ;
2401
2402 IF x_instance_id IS NULL
2403 THEN
2404 OPEN get_tld_id_cur ;
2405 FETCH get_tld_id_cur INTO x_txn_line_detail_id ;
2406 CLOSE get_tld_id_cur ;
2407 END IF ;
2408
2409 IF x_instance_id IS NULL
2410 AND x_txn_line_detail_id IS NULL
2411 THEN
2412 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_DATA');
2413 fnd_message.set_token('INST_HDR_ID',p_config_inst_hdr_id);
2414 fnd_message.set_token('INST_REV_NBR',p_config_inst_rev_num);
2415 fnd_message.set_token('CONFIG_ITEM_ID',p_config_inst_item_id);
2416 fnd_msg_pub.add;
2417 RAISE fnd_api.g_exc_error;
2418 END IF ;
2419
2420 EXCEPTION
2421 WHEN fnd_api.g_exc_error
2422 THEN
2423 x_return_status := fnd_api.g_ret_sts_error ;
2424 WHEN OTHERS
2425 THEN
2426 x_return_status := fnd_api.g_ret_sts_error ;
2427 END get_cz_inst_or_tld_id ;
2428
2429
2430 PROCEDURE get_cz_txn_line_id (
2431 p_config_session_hdr_id IN NUMBER ,
2432 p_config_session_rev_num IN NUMBER ,
2433 p_config_session_item_id IN NUMBER ,
2434 x_txn_line_id OUT NOCOPY NUMBER ,
2435 x_return_status OUT NOCOPY VARCHAR2)
2436 IS
2437 CURSOR cz_txn_line_cur
2438 IS
2439 SELECT a.transaction_line_id
2440 FROM csi_t_transaction_lines a
2441 WHERE a.config_session_hdr_id = p_config_session_hdr_id
2442 AND a.config_session_rev_num = p_config_session_rev_num
2443 AND a.config_session_item_id = p_config_session_item_id
2444 AND a.source_transaction_table = 'CONFIGURATOR'; --fix for bug 5632296
2445
2446 BEGIN
2447 csi_t_gen_utility_pvt.add('Begin : in get_cz_txn_line_id ');
2448 x_txn_line_id := NULL ;
2449 x_return_status := fnd_api.g_ret_sts_success ;
2450
2451 OPEN cz_txn_line_cur ;
2452 FETCH cz_txn_line_cur INTO x_txn_line_id ;
2453 CLOSE cz_txn_line_cur ;
2454 csi_t_gen_utility_pvt.add('x_txn_line_id :'|| x_txn_line_id);
2455
2456 EXCEPTION
2457 WHEN OTHERS
2458 THEN
2459 x_return_status := fnd_api.g_ret_sts_error ;
2460 END get_cz_txn_line_id;
2461
2462
2463 PROCEDURE check_cz_session_keys (
2464 p_config_session_hdr_id IN NUMBER ,
2465 p_config_session_rev_num IN NUMBER ,
2466 p_config_session_item_id IN NUMBER ,
2467 x_return_status OUT NOCOPY VARCHAR2)
2468 IS
2469 l_config_session_hdr_id NUMBER ;
2470 CURSOR cz_config_cur
2471 IS
2472 SELECT config_hdr_id
2473 FROM cz_config_items_v
2474 WHERE config_hdr_id = p_config_session_hdr_id
2475 AND config_rev_nbr = p_config_session_rev_num
2476 AND config_item_id = p_config_session_item_id ;
2477 BEGIN
2478
2479 x_return_status := fnd_api.g_ret_sts_success ;
2480 l_config_session_hdr_id := NULL ;
2481 OPEN cz_config_cur ;
2482 FETCH cz_config_cur INTO l_config_session_hdr_id;
2483 CLOSE cz_config_cur ;
2484
2485 IF l_config_session_hdr_id is NULL
2486 THEN
2487 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_SESSION_KEY');
2488 fnd_message.set_token('CONFIG_SESSION_HDR_ID',p_config_session_hdr_id);
2489 fnd_message.set_token('CONFIG_SESSION_REV_NUM',p_config_session_rev_num);
2490 fnd_message.set_token('CONFIG_SESSION_ITEM_ID',p_config_session_item_id);
2491 fnd_msg_pub.add;
2492 RAISE fnd_api.g_exc_error;
2493 END IF ;
2494 EXCEPTION
2495 WHEN fnd_api.g_exc_error
2496 THEN
2497 x_return_status := fnd_api.g_ret_sts_error ;
2498 WHEN OTHERS
2499 THEN
2500 x_return_status := fnd_api.g_ret_sts_error ;
2501 END check_cz_session_keys ;
2502
2503
2504 -- Added for CZ Integration (End)
2505
2506 END csi_t_vldn_routines_pvt;