[Home] [Help]
PACKAGE BODY: APPS.IGR_I_PKG_ITEM_CRM_PKG
Source
1 PACKAGE BODY IGR_I_PKG_ITEM_CRM_PKG AS
2 /* $Header: IGSRH06B.pls 120.0 2005/06/01 23:06:42 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5
6 PROCEDURE insert_row (
7 x_rowid IN OUT NOCOPY VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 x_package_item_id IN OUT NOCOPY NUMBER,
12 x_package_item IN VARCHAR2,
13 x_description IN VARCHAR2,
14 x_publish_ss_ind IN VARCHAR2,
15 x_kit_flag IN VARCHAR2,
16 x_object_version_number IN NUMBER,
17 x_actual_avail_from_date IN DATE,
18 x_actual_avail_to_date IN DATE,
19 x_mode IN VARCHAR2
20 )AS
21 /*
22 || Created By : [email protected]
23 || Created On : 22-JAN-2003
24 || Purpose : Handles the INSERT DML logic for the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30 l_deliv_rec ams_deliverable_pvt.deliv_rec_type;
31 l_tmp_var VARCHAR2(2000);
32 l_tmp_var1 VARCHAR2(2000);
33 x_last_update_date DATE;
34 x_last_updated_by NUMBER;
35 x_last_update_login NUMBER;
36
37 CURSOR c IS SELECT 'X' FROM AMS_P_DELIVERABLES_V WHERE DELIVERABLE_NAME = x_package_item;
38 l_var VARCHAR2(1);
39
40 BEGIN
41 x_last_update_date := SYSDATE;
42 IF (x_mode = 'I') THEN
43 x_last_updated_by := 1;
44 x_last_update_login := 0;
45 ELSIF (x_mode = 'R') THEN
46 x_last_updated_by := fnd_global.user_id;
47 IF(x_last_updated_by IS NULL) THEN
48 x_last_updated_by := -1;
49 END IF;
50 x_last_update_login := fnd_global.login_id;
51 IF (x_last_update_login IS NULL) THEN
52 x_last_update_login := -1;
53 END IF;
54 ELSE
55 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
56 igs_ge_msg_stack.add;
57 app_exception.raise_exception;
58 END IF;
59 -- The following code would ensure the profiles IGS: AMS Categeory, IGS: AMS Category Sub Type
60 -- are IGS: JTF Resource are set before the user tries to insert a new record.
61 IF(fnd_profile.value('IGR_AMS_DEFAULT_CAT') IS NULL OR fnd_profile.value('IGR_AMS_DEFAULT_SUBCAT') IS NULL
62 OR fnd_profile.value('IGR_JTF_DEFAULT_RESOURCE') IS NULL)THEN
63 fnd_message.set_name('IGS','IGR_NO_DEF_PROF');
64 igs_ge_msg_stack.add;
65 app_exception.raise_exception;
66 END IF;
67 --Test for Uniqueness on Information Type and Package Item associated to the
68 --Information Type.
69 OPEN c; FETCH c INTO l_var;
70 IF(c%FOUND)THEN
71 CLOSE c;
72 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
73 igs_ge_msg_stack.add;
74 app_Exception.Raise_Exception;
75 ELSE
76 CLOSE c;
77 END IF;
78 -- This is being hard coded according to Marketing Implementation Guide.
79 l_deliv_rec.country_id := fnd_profile.value('AMS_SRCGEN_USER_CITY'); -- This is the profile Value of 'AMS: User Country'
80 l_deliv_rec.setup_id := 6001;
81 -- the following are defaulted. wonder y we cant say that the following are hard-coded.
82 l_deliv_rec.language_code := 'US';
83 l_deliv_rec.currency_code := nvl(fnd_profile.value('AMS_DEFAULT_CURR_CODE'),'USD');
84 l_deliv_rec.owner_user_id := fnd_profile.value('IGR_JTF_DEFAULT_RESOURCE');
85 l_deliv_rec.active_flag := 'Y';
86 l_deliv_rec.private_flag := 'N';
87 l_deliv_rec.application_id := 530;
88 l_deliv_rec.category_type_id := fnd_profile.value('IGR_AMS_DEFAULT_CAT');
89 l_deliv_rec.category_sub_type_id := fnd_profile.value('IGR_AMS_DEFAULT_SUBCAT');
90 l_deliv_rec.version := 1;
91
92 l_deliv_rec.can_fulfill_physical_flag := 'Y'; --3036190
93 l_deliv_rec.deliverable_name := x_package_item;
94 l_deliv_rec.actual_avail_from_date := x_actual_avail_from_date;
95 l_deliv_rec.actual_avail_to_date := x_actual_avail_to_date;
96 l_deliv_rec.object_version_number := x_object_version_number;
97 l_deliv_rec.description := x_description;
98 l_deliv_rec.kit_flag := nvl(x_kit_flag,'N');
99 l_deliv_rec.last_update_date := sysdate;
100 l_deliv_rec.last_updated_by := fnd_profile.value('user_id');
101 l_deliv_rec.creation_date := sysdate;
102 l_deliv_rec.created_by := fnd_profile.value('user_id');
103 l_deliv_rec.last_update_login := fnd_profile.value('user_id');
104 l_deliv_rec.forecasted_complete_date := x_actual_avail_from_date;
105
106 AMS_DELIVERABLE_PUB.CREATE_DELIVERABLE (
107 P_API_VERSION_NUMBER => 1.0,
108 P_INIT_MSG_LIST => FND_API.G_TRUE,
109 P_COMMIT => FND_API.G_FALSE,
110 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
111 x_return_status => x_return_status,
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data,
114 p_deliv_rec => l_deliv_rec,
115 x_deliv_id => x_package_item_id
116 ) ;
117
118 IF x_return_status IN ('E','U') THEN
119 IF x_msg_count > 1 THEN
120 FOR i IN 1..x_msg_count LOOP
121 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
122 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
123 END LOOP;
124 x_msg_data := l_tmp_var1;
125 END IF;
126 ELSE
127 IGR_I_PKG_ITEM_Pkg.Insert_Row(
128 X_RowId => x_rowid,
129 X_Package_Item_Id => x_package_item_id,
130 X_Publish_Ss_Ind => x_publish_ss_ind
131 );
132 END IF;
133 END insert_row;
134
135 PROCEDURE lock_row (
136 x_rowid IN VARCHAR2,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 x_package_item_id IN NUMBER,
141 x_publish_ss_ind IN VARCHAR2
142 )AS
143 /*
144 || Created By : [email protected]
145 || Created On : 22-JAN-2003
146 || Purpose : Handles the LOCK mechanism for the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 l_tmp_var VARCHAR2(2000);
153 l_tmp_var1 VARCHAR2(2000);
154
155 CURSOR c_get_obj_num IS SELECT object_version_number from AMS_DELIVERABLES_ALL_B
156 WHERE DELIVERABLE_ID = x_package_item_id;
157 l_object_version_number AMS_DELIVERABLES_ALL_B.object_version_number%TYPE;
158
159 BEGIN
160 OPEN c_get_obj_num; FETCH c_get_obj_num INTO l_object_version_number; CLOSE c_get_obj_num;
161 AMS_DELIVERABLE_PUB.lock_DELIVERABLE(
162 p_api_version_number => 1.0,
163 p_init_msg_list => FND_API.G_FALSE,
164 p_validation_level => FND_API.g_valid_level_full,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 p_deliv_id => x_package_item_id,
169 p_object_version_number => l_object_version_number
170 ) ;
171
172 IF x_return_status IN ('E','U') THEN
173 IF x_msg_count > 1 THEN
174 FOR i IN 1..x_msg_count LOOP
175 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
176 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
177 END LOOP;
178 x_msg_data := l_tmp_var1;
179 END IF;
180 ELSE
181 igr_i_pkg_item_pkg.lock_row(
182 X_RowId => x_rowid,
183 X_Package_Item_Id => x_package_item_id,
184 X_Publish_Ss_Ind => x_publish_ss_ind
185 );
186 END IF;
187 END lock_row;
188
189 PROCEDURE update_row (
190 x_rowid IN VARCHAR2,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2,
194 x_package_item_id IN OUT NOCOPY NUMBER,
195 x_package_item IN VARCHAR2,
196 x_description IN VARCHAR2,
197 x_publish_ss_ind IN VARCHAR2,
198 x_kit_flag IN VARCHAR2,
199 x_actual_avail_from_date IN DATE,
200 x_actual_avail_to_date IN DATE,
201 x_mode IN VARCHAR2
202 )AS
203 /*
204 || Created By : [email protected]
205 || Created On : 22-JAN-2003
206 || Purpose : Handles the UPDATE DML logic for the table.
207 || Known limitations, enhancements or remarks :
208 || Change History :
209 || Who When What
210 || (reverse chronological order - newest change first)
211 */
212 l_deliv_rec ams_deliverable_pvt.deliv_rec_type;
213 l_tmp_var VARCHAR2(2000);
214 l_tmp_var1 VARCHAR2(2000);
215
216 x_last_updated_by NUMBER;
217 x_last_update_login NUMBER;
218
219 CURSOR c_get_obj_num IS SELECT object_version_number from AMS_DELIVERABLES_ALL_B
220 WHERE DELIVERABLE_ID = x_package_item_id;
221 l_object_version_number AMS_DELIVERABLES_ALL_B.object_version_number%TYPE;
222
223 BEGIN
224 IF (X_MODE = 'I') THEN
225 x_last_updated_by := 1;
226 x_last_update_login := 0;
227 ELSIF (x_mode = 'R') THEN
228 x_last_updated_by := fnd_global.user_id;
229 IF x_last_updated_by IS NULL THEN
230 x_last_updated_by := -1;
231 END IF;
232 x_last_update_login := fnd_global.login_id;
233 IF (x_last_update_login IS NULL) THEN
234 x_last_update_login := -1;
235 END IF;
236 ELSE
237 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
238 igs_ge_msg_stack.add;
239 app_exception.raise_exception;
240 END IF;
241 ams_deliverable_pvt.init_deliv_rec(l_deliv_rec);
242 OPEN c_get_obj_num; FETCH c_get_obj_num INTO l_object_version_number; CLOSE c_get_obj_num;
243 l_deliv_rec.deliverable_id := x_package_item_id;
244 l_deliv_rec.deliverable_name := x_package_item;
245 l_deliv_rec.actual_avail_from_date := x_actual_avail_from_date;
246 l_deliv_rec.actual_avail_to_date := x_actual_avail_to_date;
247 -- we dont store the forcasted completed date in oss. so the date is being defaulted to the
248 -- from date value.
249 l_deliv_rec.forecasted_complete_date := x_actual_avail_from_date;
250 l_deliv_rec.description := x_description;
251 l_deliv_rec.kit_flag := nvl(x_kit_flag,'N');
252 l_deliv_rec.object_version_number := l_object_version_number;
253 AMS_DELIVERABLE_PUB.update_DELIVERABLE(
254 P_API_VERSION_NUMBER => 1.0,
255 P_INIT_MSG_LIST => FND_API.G_TRUE,
256 P_COMMIT => FND_API.G_FALSE,
257 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
258 x_return_status => x_return_status,
259 x_msg_count => x_msg_count,
260 x_msg_data => x_msg_data,
261 p_deliv_rec => l_deliv_rec
262 );
263 IF x_return_status IN ('E','U') THEN
264 IF x_msg_count > 1 THEN
265 FOR i IN 1..x_msg_count LOOP
266 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
267 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
268 END LOOP;
269 x_msg_data := l_tmp_var1;
270 END IF;
271 ELSE
272 igr_i_pkg_item_pkg.update_row(
273 X_RowId => x_rowid,
274 X_Package_Item_Id => x_package_item_id,
275 X_Publish_Ss_Ind => x_publish_ss_ind
276 );
277 END IF;
278 END update_row;
279 END IGR_I_PKG_ITEM_CRM_PKG;