[Home] [Help]
PACKAGE BODY: APPS.AMS_ITEM_SECTION_PVT
Source
1 PACKAGE BODY AMS_item_section_PVT as
2 /* $Header: amsvpseb.pls 115.4 2002/05/29 04:15:23 pkm ship $ */
3 -- Start of Comments
4 -- Package name : AMS_item_section_PVT
5 -- Purpose :
6
7 -- History :
8 -- 01/22/2001 musman created.
9 -- 17-MAY-2002 abhola removed references to G_USER_ID
10 -- End of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_item_section_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvmulb.pls';
15
16
17 PROCEDURE create_item_sec_assoc
18 (
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
21 p_commit IN VARCHAR2 := FND_API.g_false,
22 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
23
24 x_return_status OUT VARCHAR2,
25 x_msg_count OUT NUMBER,
26 x_msg_data OUT VARCHAR2,
27
28 p_section_rec IN section_rec_type
29
30 )
31
32 IS
33
34
35 l_api_name CONSTANT VARCHAR2(50) := 'create_item_sec_assoc';
36
37
38 l_section_id_tbl JTF_NUMBER_TABLE ;
39 l_start_date_tbl JTF_DATE_TABLE;
40 l_end_date_tbl JTF_DATE_TABLE;
41
42 l_sort_order_tbl JTF_NUMBER_TABLE :=JTF_NUMBER_TABLE() ;
43 l_association_reason_code_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
44 x_section_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
45
46
47
48 l_api_version NUMBER := p_api_version;
49 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
50 l_commit VARCHAR2(1) := p_commit;
51 l_validation_level NUMBER := p_validation_level;
52
53 l_dup_return_status VARCHAR2(1);
54
55
56 BEGIN
57 -- Standard Start of API savepoint
58 SAVEPOINT Create_Item_Sec_Assoc;
59
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( l_api_version,
62 p_api_version,
63 l_api_name,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 -- Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean( p_init_msg_list )
71 THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74
75 -- Debug Message
76 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
77
78
79 -- Initialize API return status to SUCCESS
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82
83 -- ******************************************************************
84 -- Validate Environment
85 -- ******************************************************************
86 IF FND_GLOBAL.User_Id IS NULL
87 THEN
88 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
89 THEN
90 FND_MESSAGE.Set_Name('AMS', 'USER_PROFILE_MISSING');
91 FND_MSG_PUB.ADD;
92 END IF;
93 RAISE FND_API.G_EXC_ERROR;
94 END IF;
95
96
97 l_section_id_tbl := JTF_NUMBER_TABLE(1);
98 l_section_id_tbl(1) := p_section_rec.section_id;
99
100 AMS_UTILITY_PVT.debug_message(l_api_name || ' the l_section_id_tbl(1):' ||l_section_id_tbl(1));
101 l_start_date_tbl := JTF_DATE_TABLE();
102 l_start_date_tbl.extend();
103 l_start_date_tbl(1) := sysdate; --p_section_rec.start_date
104
105 l_end_date_tbl := JTF_DATE_TABLE();
106 l_end_date_tbl.extend();
107 l_end_date_tbl(1) := p_section_rec.end_date;
108
109 l_association_reason_code_tbl.extend();
110 x_section_id_tbl.extend();
111 l_sort_order_tbl.extend();
112
113 -- Call to IBE Procedure
114 ------------------------
115 Ibe_dsp_hierarchy_setup_pvt.Associate_Sections_To_Item
116 ( p_api_version => l_api_version
117 , p_init_msg_list => l_init_msg_list
118 , p_commit => l_commit
119 , p_validation_level => l_validation_level
120 , p_inventory_item_id => p_section_rec.inventory_item_id
121 , p_organization_id => p_section_rec.organization_id
122 , p_section_ids => l_section_id_tbl
123 , p_start_date_actives => l_start_date_tbl
124 , p_end_date_actives => l_end_date_tbl
125 , p_sort_orders => l_sort_order_tbl
126 , p_association_reason_codes => l_association_reason_code_tbl
127 , x_section_item_ids => x_section_id_tbl
128 , x_duplicate_association_status => l_dup_return_status
129 , x_return_status => x_return_status
130 , x_msg_count => x_msg_count
131 , x_msg_data => x_msg_data
132 );
133
134 IF x_return_status =FND_API.G_RET_STS_UNEXP_ERROR
135 THEN
136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
137
138 ELSIF x_return_status =FND_API.G_RET_STS_ERROR
139 OR l_dup_return_status =FND_API.G_RET_STS_ERROR
140 THEN
141 RAISE FND_API.G_EXC_ERROR;
142 END IF;
143
144
145 -- Standard check for p_commit
146 IF FND_API.to_Boolean( p_commit )
147 THEN
148 COMMIT WORK;
149 END IF;
150
151
152 -- Debug Message
153 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
154
155 -- Standard call to get message count and if count is 1, get message info.
156 FND_MSG_PUB.Count_And_Get
157 (p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160 EXCEPTION
161 WHEN FND_API.G_EXC_ERROR THEN
162 ROLLBACK TO Create_Item_Sec_Assoc;
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 -- Standard call to get message count and if count=1, get the message
165 FND_MSG_PUB.Count_And_Get (
166 p_encoded => FND_API.G_FALSE,
167 p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 ROLLBACK TO Create_Item_Sec_Assoc;
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 -- Standard call to get message count and if count=1, get the message
174 FND_MSG_PUB.Count_And_Get (
175 p_encoded => FND_API.G_FALSE,
176 p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179 WHEN OTHERS THEN
180 ROLLBACK TO Create_Item_Sec_Assoc;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
183 THEN
184 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
185 END IF;
186 -- Standard call to get message count and if count=1, get the message
187 FND_MSG_PUB.Count_And_Get (
188 p_encoded => FND_API.G_FALSE,
189 p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192 END create_item_sec_assoc;
193
194 ---------------------------------------------------------------------
195 -- PROCEDURE
196 -- delete_item_sec_assoc
197 --
198 ---------------------------------------------------------------------
199
200 PROCEDURE Delete_item_sec_assoc
201 (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
204 p_commit IN VARCHAR2 := FND_API.g_false,
205 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
206
207 x_return_status OUT VARCHAR2,
208 x_msg_count OUT NUMBER,
209 x_msg_data OUT VARCHAR2,
210
211 p_section_rec IN section_rec_type
212
213 )
214
215 IS
216 l_api_name CONSTANT VARCHAR2(50) := 'Delete_item_sec_assoc';
217
218 l_inv_id_tbl JTF_NUMBER_TABLE :=JTF_NUMBER_TABLE(1);
219 l_org_id_tbl JTF_NUMBER_TABLE :=JTF_NUMBER_TABLE(1);
220 l_section_id_tbl JTF_NUMBER_TABLE :=JTF_NUMBER_TABLE(1);
221
222 l_api_version NUMBER := p_api_version;
223 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
224 l_commit VARCHAR2(1) := p_commit;
225 l_validation_level NUMBER := p_validation_level;
226
227
228
229 BEGIN
230 -- Standard Start of API savepoint
231 SAVEPOINT Delete_item_sec_assoc;
232
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call ( l_api_version,
235 p_api_version,
236 l_api_name,
237 G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242 -- Initialize message list if p_init_msg_list is set to TRUE.
243 IF FND_API.to_Boolean( p_init_msg_list )
244 THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247
248 -- Debug Message
249 -- AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
250
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255
256 -- ******************************************************************
257 -- Validate Environment
258 -- ******************************************************************
259 IF FND_GLOBAL.User_Id IS NULL
260 THEN
261 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
262 THEN
263 FND_MESSAGE.Set_Name('AMS', 'USER_PROFILE_MISSING');
264 FND_MSG_PUB.ADD;
265 END IF;
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268
269 l_section_id_tbl(1) := p_section_rec.section_id;
270 l_inv_id_tbl(1) := p_section_rec.inventory_item_id;
271 l_org_id_tbl(1) := p_section_rec.organization_id;
272
273 -- Call to IBE Procedure
274 ------------------------
275 Ibe_dsp_hierarchy_setup_pvt.Disassociate_Scts_To_Itms
276 (
277 p_api_version => l_api_version
278 , p_init_msg_list => l_init_msg_list
279 , p_commit => l_commit
280 , p_validation_level => l_validation_level
281 , p_section_ids => l_section_id_tbl
282 , p_inventory_item_ids => l_inv_id_tbl
283 , p_organization_ids => l_org_id_tbl
284 , x_return_status => x_return_status
285 , x_msg_count => x_msg_count
286 , x_msg_data => x_msg_data
287 );
288
289
290 IF x_return_status =FND_API.G_RET_STS_UNEXP_ERROR
291 THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 ELSIF x_return_status =FND_API.G_RET_STS_ERROR
294 THEN
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
296 END IF;
297
298
299 -- Standard check for p_commit
300 IF FND_API.to_Boolean( p_commit )
301 THEN
302 COMMIT WORK;
303 END IF;
304
305
306 -- Debug Message
307 -- AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
308
309 -- Standard call to get message count and if count is 1, get message info.
310 FND_MSG_PUB.Count_And_Get
311 (p_count => x_msg_count,
312 p_data => x_msg_data
313 );
314 EXCEPTION
315 WHEN FND_API.G_EXC_ERROR THEN
316 ROLLBACK TO Delete_item_sec_assoc;
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 -- Standard call to get message count and if count=1, get the message
319 FND_MSG_PUB.Count_And_Get (
320 p_encoded => FND_API.G_FALSE,
321 p_count => x_msg_count,
325 ROLLBACK TO Delete_item_sec_assoc;
322 p_data => x_msg_data
323 );
324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 -- Standard call to get message count and if count=1, get the message
328 FND_MSG_PUB.Count_And_Get (
329 p_encoded => FND_API.G_FALSE,
330 p_count => x_msg_count,
331 p_data => x_msg_data
332 );
333 WHEN OTHERS THEN
334 ROLLBACK TO Delete_item_sec_assoc;
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
337 THEN
338 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
339 END IF;
340 -- Standard call to get message count and if count=1, get the message
341 FND_MSG_PUB.Count_And_Get (
342 p_encoded => FND_API.G_FALSE,
343 p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346 END Delete_item_sec_assoc;
347
348
349
350
351 End AMS_item_section_PVT;