[Home] [Help]
PACKAGE: APPS.AHL_MC_ITEM_COMP_PVT
Source
1 PACKAGE AHL_MC_ITEM_COMP_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVICXS.pls 115.0 2003/08/05 16:33:57 sjayacha noship $ */
3
4 --The Header_Rec_Type record
5
6 TYPE Header_Rec_Type IS RECORD (
7 ITEM_COMPOSITION_ID NUMBER,
8 INVENTORY_ITEM_ID NUMBER,
9 INVENTORY_ITEM_NAME VARCHAR2(2000),
10 INVENTORY_ORG_ID NUMBER,
11 INVENTORY_ORG_CODE VARCHAR2(3) ,
12 INVENTORY_MASTER_ORG_ID NUMBER,
13 DRAFT_FLAG VARCHAR2(1),
14 STATUS_CODE VARCHAR2(30),
15 EFFECTIVE_END_DATE DATE,
16 OBJECT_VERSION_NUMBER NUMBER := 1,
17 ATTRIBUTE_CATEGORY VARCHAR2 (30),
18 ATTRIBUTE1 VARCHAR2 (150),
19 ATTRIBUTE2 VARCHAR2 (150),
20 ATTRIBUTE3 VARCHAR2 (150),
21 ATTRIBUTE4 VARCHAR2 (150),
22 ATTRIBUTE5 VARCHAR2 (150),
23 ATTRIBUTE6 VARCHAR2 (150),
24 ATTRIBUTE7 VARCHAR2 (150),
25 ATTRIBUTE8 VARCHAR2 (150),
26 ATTRIBUTE9 VARCHAR2 (150),
27 ATTRIBUTE10 VARCHAR2 (150),
28 ATTRIBUTE11 VARCHAR2 (150),
29 ATTRIBUTE12 VARCHAR2 (150),
30 ATTRIBUTE13 VARCHAR2 (150),
31 ATTRIBUTE14 VARCHAR2 (150),
32 ATTRIBUTE15 VARCHAR2 (150),
33 OPERATION_FLAG VARCHAR2(1) := NULL
34 );
35
36 --The Detail_Rec_Type record
37
38 TYPE Detail_Rec_Type IS RECORD (
39 ITEM_COMP_DETAIL_ID NUMBER,
40 ITEM_COMPOSITION_ID NUMBER,
41 ITEM_GROUP_ID NUMBER,
42 ITEM_GROUP_NAME VARCHAR2(80),
43 INVENTORY_ITEM_ID NUMBER,
44 INVENTORY_ITEM_NAME VARCHAR2(2000),
45 INVENTORY_ORG_ID NUMBER,
46 INVENTORY_ORG_CODE VARCHAR2(3) ,
47 INVENTORY_MASTER_ORG_ID NUMBER,
48 UOM_CODE VARCHAR2(30),
49 QUANTITY NUMBER,
50 EFFECTIVE_END_DATE DATE,
51 LINK_COMP_DETL_ID NUMBER,
52 OBJECT_VERSION_NUMBER NUMBER := 1,
53 ATTRIBUTE_CATEGORY VARCHAR2 (30),
54 ATTRIBUTE1 VARCHAR2 (150),
55 ATTRIBUTE2 VARCHAR2 (150),
56 ATTRIBUTE3 VARCHAR2 (150),
57 ATTRIBUTE4 VARCHAR2 (150),
58 ATTRIBUTE5 VARCHAR2 (150),
59 ATTRIBUTE6 VARCHAR2 (150),
60 ATTRIBUTE7 VARCHAR2 (150),
61 ATTRIBUTE8 VARCHAR2 (150),
62 ATTRIBUTE9 VARCHAR2 (150),
63 ATTRIBUTE10 VARCHAR2 (150),
64 ATTRIBUTE11 VARCHAR2 (150),
65 ATTRIBUTE12 VARCHAR2 (150),
66 ATTRIBUTE13 VARCHAR2 (150),
67 ATTRIBUTE14 VARCHAR2 (150),
68 ATTRIBUTE15 VARCHAR2 (150),
69 OPERATION_FLAG VARCHAR2(1) := NULL
70 );
71
72 TYPE Det_Tbl_Type is TABLE of Detail_Rec_Type index by BINARY_INTEGER;
73
74 -----------------------------------------
75 -- Declare Procedures for Item Composition --
76 -----------------------------------------
77
78 -- Start of Comments --
79 -- Procedure name : Create_Item_Composition
80 -- Type : Private
81 -- Function : Creates Item Composition for Trackable Items in ahl_item_compositions.
82 -- Also creates item-group and Non-Trackable Item association in ahl_comp_details table.
83 -- Pre-reqs :
84 -- Parameters :
85 -- Standard IN Parameters :
86 -- p_api_version IN NUMBER Required
87 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
88 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
89 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
90 --
91 -- Standard OUT Parameters :
92 -- x_return_status OUT VARCHAR2 Required
93 -- x_msg_count OUT NUMBER Required
94 -- x_msg_data OUT VARCHAR2 Required
95 -- Item Header Composition Record :
96 -- inventory_item_id required.
97 -- inventory_item_name required.
98 -- inventory_org_id required.
99 -- inventory_org_code required.
100 -- operation_flag required to be 'C'.(Create)
101 -- Item Associations Record :
102 -- item_group_id Required. ( If inventory_item_id Non Trackable Item is NUll)
103 -- item_group_name Required.
104 -- inventory_item_id Required. ( If item group is NUll) Item Should be non trackable.
105 -- inventory_item_name Required.
106 -- inventory_org_id Required.
107 -- inventory_org_code Required.
108 -- operation_flag Required to be 'C'.(Create)
109 -- End of Comments --
110
111
112 PROCEDURE Create_Item_Composition(
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
115 p_commit IN VARCHAR2 := FND_API.G_FALSE,
116 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2,
120 p_x_ic_header_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
121 p_x_det_tbl IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
122 );
123
124 -----------------------------------------
125 -- Declare Procedures for Item Composition --
126 -----------------------------------------
127
128 -- Start of Comments --
129 -- Procedure name : Modify_Item_Composition
130 -- Type : Private
131 -- Function : Modifies Item Composition for Trackable Items in ahl_item_compositions.
132 -- Also creates,modifies item-group and Non-Trackable Item association in ahl_comp_details table.
133 -- Pre-reqs :
134 -- Parameters :
135 -- Standard IN Parameters :
136 -- p_api_version IN NUMBER Required
137 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
138 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
139 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
140 --
141 -- Standard OUT Parameters :
142 -- x_return_status OUT VARCHAR2 Required
143 -- x_msg_count OUT NUMBER Required
144 -- x_msg_data OUT VARCHAR2 Required
145 -- Item Header Composition Record :
146 -- inventory_item_id required.
147 -- inventory_item_name required.
148 -- inventory_org_id required.
149 -- inventory_org_code required.
150 -- operation_flag required to be 'M'.(Create)
151 -- Item Associations Record :
152 -- item_group_id Required. ( If inventory_item_id Non Trackable Item is NUll)
153 -- item_group_name Required.
154 -- inventory_item_id Required. ( If item group is NUll) Item Should be non trackable.
155 -- inventory_item_name Required.
156 -- inventory_org_id Required.
157 -- inventory_org_code Required.
158 -- operation_flag Required to be 'C'.(Create)
159 -- End of Comments --
160
161 PROCEDURE Modify_Item_Composition(
162 p_api_version IN NUMBER,
163 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
164 p_commit IN VARCHAR2 := FND_API.G_FALSE,
165 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2,
169 p_x_ic_header_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
170 p_x_det_tbl IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
171 );
172 -----------------------------------------
173 -- Declare Procedures for Item Composition --
174 -----------------------------------------
175
176 -- Start of Comments --
177 -- Procedure name : Delete_Item_Composition
178 -- Type : Private
179 -- Function : Deletes Item Composition for Trackable Items in ahl_item_compositions.
180 -- Also deletes association in ahl_comp_details table.
181 -- Incase of Complete status Item Composition it Expires it.
182 -- Pre-reqs :
183 -- Parameters :
184 -- Standard IN Parameters :
185 -- p_api_version IN NUMBER Required
186 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
187 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
188 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
189 --
190 -- Standard OUT Parameters :
191 -- x_return_status OUT VARCHAR2 Required
192 -- x_msg_count OUT NUMBER Required
193 -- x_msg_data OUT VARCHAR2 Required
194 -- Item Header Composition Record :
195 -- p_item_composition_ID Required
196 -- p_object_version_number Required.
197 -- End of Comments --
198
199 PROCEDURE Delete_Item_Composition (
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
202 p_commit IN VARCHAR2 := FND_API.G_FALSE,
203 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_item_composition_ID IN NUMBER ,
208 p_object_version_number IN NUMBER
209
210 );
211
212 -----------------------------------------
213 -- Declare Procedures for Item Composition --
214 -----------------------------------------
215
216 -- Start of Comments --
217 -- Procedure name : Reopen_Item_Composition
218 -- Type : Private
219 -- Function : Re-Open'ss Item Composition for Trackable Items in ahl_item_compositions.
220 --
221 -- Pre-reqs :
222 -- Parameters :
223 -- Standard IN Parameters :
224 -- p_api_version IN NUMBER Required
225 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
226 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
227 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
228 --
229 -- Standard OUT Parameters :
230 -- x_return_status OUT VARCHAR2 Required
231 -- x_msg_count OUT NUMBER Required
232 -- x_msg_data OUT VARCHAR2 Required
233 -- Item Header Composition Record :
234 -- p_item_composition_ID Required
235 -- p_object_version_number Required.
236 -- End of Comments --
237
238
239 PROCEDURE Reopen_Item_Composition (
240 p_api_version IN NUMBER,
241 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
242 p_commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_item_composition_ID IN NUMBER ,
248 p_object_version_number IN NUMBER
249
250 );
251
252 -----------------------------------------
253 -- Declare Procedures for Item Composition --
254 -----------------------------------------
255
256 -- Start of Comments --
257 -- Procedure name : Create_Item_Comp_Revision
258 -- Type : Private
259 -- Function : Creates new revision of existing Item Composition
260 -- Pre-reqs :
261 -- Parameters :
262 -- Standard IN Parameters :
263 -- p_api_version IN NUMBER Required
264 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
265 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
266 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
267 --
268 -- Standard OUT Parameters :
269 -- x_return_status OUT VARCHAR2 Required
270 -- x_msg_count OUT NUMBER Required
271 -- x_msg_data OUT VARCHAR2 Required
272 -- Item Header Composition Record :
273 -- IN Parameter
274 -- p_item_composition_ID Required
275 -- p_object_version_number Required.
276 -- OUT Parameter
277 -- x_Item_comp_id
278 -- End of Comments --
279
280
281 PROCEDURE Create_Item_Comp_Revision (
282 p_api_version IN NUMBER,
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
286 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
287 P_MODULE_TYPE IN VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_Item_comp_id IN NUMBER,
292 p_object_version_number IN NUMBER,
293 x_Item_comp_id OUT NOCOPY NUMBER
294 );
295 -----------------------------------------
296 -- Declare Procedures for Item Composition --
297 -----------------------------------------
298
299 -- Start of Comments --
300 -- Procedure name : Initiate_Item_Comp_Approval
301 -- Type : Private
302 -- Function : Initiate approval for Item Composition for Trackable Items
303 -- Pre-reqs :
304 -- Parameters :
305 -- Standard IN Parameters :
306 -- p_api_version IN NUMBER Required
307 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
308 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
309 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
310 --
311 -- Standard OUT Parameters :
312 -- x_return_status OUT VARCHAR2 Required
313 -- x_msg_count OUT NUMBER Required
314 -- x_msg_data OUT VARCHAR2 Required
315 -- Item Header Composition Record :
316 -- IN Parameter
317 -- p_item_composition_ID Required
318 -- p_object_version_number Required.
319 -- p_approval_type Required.
320 -- End of Comments --
321
322
323 PROCEDURE Initiate_Item_Comp_Approval (
324 p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
326 p_commit IN VARCHAR2 := FND_API.G_FALSE,
327 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2,
331 p_Item_Composition_id IN NUMBER,
332 p_object_version_number IN NUMBER,
333 p_approval_type IN VARCHAR2
334 );
335 -----------------------------------------
336 -- Declare Procedures for Item Composition --
337 -----------------------------------------
338
339 -- Start of Comments --
340 -- Procedure name : Approve_Item_Composiiton
341 -- Type : Private
342 -- Function : To update the exitinf Item Composition with newly approved (revision of Item Compostiion)
343 -- Used by approval package.
344 -- Pre-reqs :
345 -- Parameters :
346 -- Standard IN Parameters :
347 -- p_api_version IN NUMBER Required
348 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
349 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
350 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
351 --
352 -- Standard OUT Parameters :
353 -- x_return_status OUT VARCHAR2 Required
354 -- x_msg_count OUT NUMBER Required
355 -- x_msg_data OUT VARCHAR2 Required
356 -- IN Parameter
357 -- p_Item_comp_id Required.
358 -- p_object_version_number Required.
359 -- End of Comments --
360
361
362
363 PROCEDURE Approve_Item_Composiiton (
364 p_api_version IN NUMBER,
365 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
366 p_commit IN VARCHAR2 := FND_API.G_FALSE,
367 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
368 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
369 P_MODULE_TYPE IN VARCHAR2,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2,
373 p_appr_status IN VARCHAR2,
374 p_Item_comp_id IN NUMBER,
375 p_object_version_number IN NUMBER);
376
377 End AHL_MC_ITEM_COMP_PVT;