1 PACKAGE BODY igr_inftyp_pkg_it_crm_pkg AS
2 /* $Header: IGSRH13B.pls 120.0 2005/06/01 16:25:41 appldev noship $ */
3 PROCEDURE insert_row(
4 x_return_status OUT NOCOPY VARCHAR2,
5 x_msg_count OUT NOCOPY NUMBER,
6 x_msg_data OUT NOCOPY VARCHAR2,
7 x_deliverable_kit_item_id IN OUT NOCOPY NUMBER,
8 x_deliverable_kit_id IN NUMBER,
9 x_deliverable_kit_part_id IN NUMBER
10 ) AS
11 CURSOR c IS SELECT 'X' FROM AMS_P_DELIV_KIT_ITEMS_V WHERE
12 deliverable_kit_part_id = x_deliverable_kit_part_id AND
13 deliverable_kit_id = x_deliverable_kit_id ;
14 l_deliv_kit_item_rec ams_delivkititem_pvt.deliv_kit_item_rec_type;
15 l_tmp_var VARCHAR2(2000);
16 l_var VARCHAR2(1);
17 BEGIN
18 OPEN c; FETCH c INTO l_var; CLOSE c;
19 IF l_var IS NOT NULL THEN
20 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
21 APP_EXCEPTION.RAISE_EXCEPTION;
22 END IF;
23 l_deliv_kit_item_rec.deliverable_kit_id := x_deliverable_kit_id;
24 l_deliv_kit_item_rec.deliverable_kit_part_id := x_deliverable_kit_part_id;
25 l_deliv_kit_item_rec.object_version_number := 1;
26 AMS_DelivKitItem_PUB.Create_DelivKitItem(
27 p_api_version_number => 1.0,
28 x_return_status => x_return_status,
29 x_msg_count => x_msg_count,
30 x_msg_data => x_msg_data,
31 p_Deliv_Kit_Item_rec => l_deliv_kit_item_rec,
32 x_deliv_kit_item_id => x_deliverable_kit_item_id
33 );
34 IF x_return_status IN ('E','U') THEN
35 IF x_msg_count > 1 THEN
36 FOR i IN 1..x_msg_count LOOP
37 l_tmp_var := l_tmp_var || fnd_msg_pub.get(p_encoded => fnd_api.g_false);
38 END LOOP;
39 x_msg_data := l_tmp_var;
40 END IF;
41 END IF;
42 END insert_row;
43
44 PROCEDURE delete_row(
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48 x_deliverable_kit_item_id IN NUMBER
49 ) AS
50 CURSOR c IS SELECT object_version_number,deliverable_kit_id from AMS_DELIV_KIT_ITEMS
51 WHERE deliverable_kit_item_id = x_deliverable_kit_item_id;
52
53 l_object_version_number AMS_DELIV_KIT_ITEMS.object_version_number%TYPE;
54 l_tmp_var VARCHAR2(2000);
55 l_info_type_id ams_deliv_kit_items.deliverable_kit_id%TYPE;
56 l_var NUMBER(10);
57 l_row_id VARCHAR2(25);
58
59 CURSOR c_package_items( l_info_type_id igr_i_ityp_pkgs_v.info_type_id%TYPE ) IS
60 SELECT 1
61 FROM igr_i_ityp_pkgs_v
62 WHERE info_type_id = l_info_type_id;
63
64 CURSOR c_info_details (l_info_type_id igr_i_info_types_v.info_type_id%TYPE) IS
65 SELECT a.*
66 FROM ams_p_deliverables_v a
67 WHERE a.deliverable_id = l_info_type_id;
68
69 CURSOR c_rowid (l_info_type_id igr_i_info_types_v.info_type_id%TYPE) IS
70 SELECT rowid
71 FROM igr_i_pkg_item
72 WHERE package_item_id = l_info_type_id;
73
74 rec_info_details c_info_details%ROWTYPE ;
75
76 BEGIN
77 OPEN c;
78 FETCH c INTO l_object_version_number,l_info_type_id;
79 CLOSE c;
80
81 AMS_DelivKitItem_PUB.Delete_DelivKitItem(
82 p_api_version_number => 1.0,
83 x_return_status => x_return_status,
84 x_msg_count => x_msg_count,
85 x_msg_data => x_msg_data,
86 p_deliv_kit_item_id => x_deliverable_kit_item_id,
87 p_object_version_number => l_object_version_number
88 );
89 IF x_return_status IN ('E','U') THEN
90 IF x_msg_count > 1 THEN
91 FOR i IN 1..x_msg_count LOOP
92 l_tmp_var := l_tmp_var || fnd_msg_pub.get(p_encoded => fnd_api.g_false);
93 END LOOP;
94 x_msg_data := l_tmp_var;
95 END IF;
96 ELSE
97 /* If the Deletion Of the Deliverabl Kit Item is successful And If all The
98 Deliverabl Kit Items (Pkg Item )associated with the Information type are Deleted
99 Then The CRM API will Automatically set the Kit Flag to 'N' which should be
100 reset to 'Y' which is a valid one for Information Type
101 This change was made as part of the Bug 2819945 */
102
103 OPEN c_package_items( l_info_type_id);
104 FETCH c_package_items INTO l_var;
105 IF c_package_items%NOTFOUND THEN
106
107 OPEN c_info_details ( l_info_type_id);
108 FETCH c_info_details INTO rec_info_details ;
109 CLOSE c_info_details;
110
111 OPEN c_rowid ( l_info_type_id);
112 FETCH c_rowid INTO l_row_id ;
113 CLOSE c_rowid;
114
115 IGR_I_PKG_ITEM_CRM_PKG.update_row (
116 x_rowid => l_row_id,
117 x_return_status => x_return_status,
118 x_msg_count => x_msg_count,
119 x_msg_data => x_msg_data,
120 x_package_item_id => rec_info_details.deliverable_id,
121 x_package_item => rec_info_details.deliverable_name,
122 x_description => rec_info_details.description,
123 x_publish_ss_ind => 'N',
124 x_kit_flag => 'Y',
125 x_actual_avail_from_date => IGS_GE_DATE.IGSDATE(rec_info_details.actual_avail_from_date),
126 x_actual_avail_to_date => IGS_GE_DATE.IGSDATE(rec_info_details.actual_avail_to_date)
127 );
128 END IF;
129 CLOSE c_package_items;
130 END IF;
131
132
133 END delete_row;
134 END igr_inftyp_pkg_it_crm_pkg;
[Home] [Help]