1 PACKAGE EGO_Item_PVT AS
2 /* $Header: EGOVITMS.pls 120.8 2007/09/12 13:23:25 srajapar ship $ */
3
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EGOVITMS.pls';
5
6 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_SUCCESS; --'S'
7 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_ERROR; --'E'
8 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_UNEXP_ERROR; --'U'
9
10 -- Define the package global constants to substitute FND_API global variables for missing values
11
12 G_MISS_NUM CONSTANT NUMBER := 9.99E125;
13 G_MISS_CHAR CONSTANT VARCHAR2(1) := CHR(0);
14 G_MISS_DATE CONSTANT DATE := TO_DATE('1','j');
15
16 -- =============================================================================
17 -- Global variables and cursors
18 -- =============================================================================
19
20 --
21 -- Package global tables storing all item business object data
22 -- to be processed by the Item BO procedures.
23 --
24
25 G_Item_Tbl EGO_Item_PUB.Item_Tbl_Type;
26 G_Item_indx BINARY_INTEGER := 0;
27
28 --Added global pl/sql revision tbl
29 G_Revision_Tbl EGO_Item_PUB.Item_Revision_Tbl_Type;
30
31 G_Item_Org_Assignment_Tbl EGO_Item_PUB.Item_Org_Assignment_Tbl_Type;
32 G_Item_Org_indx BINARY_INTEGER := 0;
33
34
35 -- =============================================================================
36 -- Procedures
37 -- =============================================================================
38
39 -- -----------------------------------------------------------------------------
40 -- API Name: Process_Items
41 --
42 -- Description:
43 -- Process (CREATE/UPDATE) a set of items based on data in
44 -- the global pl/sql table.
45 -- -----------------------------------------------------------------------------
46
47 PROCEDURE Process_Items
48 (
49 p_commit IN VARCHAR2 DEFAULT FND_API.g_FALSE
50 , x_return_status OUT NOCOPY VARCHAR2
51 , x_msg_count OUT NOCOPY NUMBER
52 );
53
54 /* **
55 -- -----------------------------------------------------------------------------
56 -- API Name: Process_Item
57 --
58 -- Description:
59 -- Process (CREATE/UPDATE) one item based on data in
60 -- the global pl/sql record.
61 -- -----------------------------------------------------------------------------
62
63 PROCEDURE Process_Item
64 (
65 p_commit IN VARCHAR2 DEFAULT FND_API.g_FALSE
66 , x_return_status OUT NOCOPY VARCHAR2
67 , x_msg_count OUT NOCOPY NUMBER
68 );
69 */
70
71 -- -----------------------------------------------------------------------------
72 -- API Name: Process_Item_Org_Assignments
73 --
74 -- Description:
75 -- Process a list of item assignments to organizations.
76 -- -----------------------------------------------------------------------------
77
78 PROCEDURE Process_Item_Org_Assignments
79 (
80 p_commit IN VARCHAR2 DEFAULT FND_API.g_FALSE
81 , x_return_status OUT NOCOPY VARCHAR2
82 , x_msg_count OUT NOCOPY NUMBER
83 );
84
85 -- -----------------------------------------------------------------------------
86 -- API Name: Seed_Item_Long_Desc_Attr_Group
87 --
88 -- Description:
89 -- Add a row to the User-Defined Attribute Group 'Detailed Descriptions'
90 -- so that the Item Long Description is shown on the Item Detail page.
91 -- This procedure will only add the row if one does not exist already.
92 -- -----------------------------------------------------------------------------
93
94 PROCEDURE Seed_Item_Long_Desc_Attr_Group (
95 p_inventory_item_id IN NUMBER
96 ,p_organization_id IN NUMBER
97 ,p_item_catalog_group_id IN NUMBER
98 ,x_return_status OUT NOCOPY VARCHAR2
99 ,x_errorcode OUT NOCOPY NUMBER
100 ,x_msg_count OUT NOCOPY NUMBER
101 ,x_msg_data OUT NOCOPY VARCHAR2
102 );
103
104 -- -----------------------------------------------------------------------------
105 -- API Name: Seed_Item_Long_Desc_In_Bulk
106 --
107 -- Description:
108 -- Add a row to the User-Defined Attribute Group 'Detailed Descriptions'
109 -- for all newly created items in the set identified by p_set_process_id
110 -- -----------------------------------------------------------------------------
111
112 PROCEDURE Seed_Item_Long_Desc_In_Bulk (
113 p_set_process_id IN NUMBER
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ,x_msg_data OUT NOCOPY VARCHAR2
116 );
117
118 -- -----------------------------------------------------------------------------
119 -- API Name: Process_User_Attrs_For_Item
120 --
121 -- Description:
122 -- Process passed-in User-Defined Attrs data for
123 -- the Item whose Primary Keys are passed in
124 -- -----------------------------------------------------------------------------
125 PROCEDURE Process_User_Attrs_For_Item (
126 p_api_version IN NUMBER
127 ,p_inventory_item_id IN NUMBER
128 ,p_organization_id IN NUMBER
129 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
130 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
131 ,p_change_info_table IN EGO_USER_ATTR_CHANGE_TABLE DEFAULT NULL
132 ,p_entity_id IN NUMBER DEFAULT NULL
133 ,p_entity_index IN NUMBER DEFAULT NULL
134 ,p_entity_code IN VARCHAR2 DEFAULT NULL
135 ,p_debug_level IN NUMBER DEFAULT 0
136 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
137 ,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
138 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
139 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_TRUE
140 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
141 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
142 ,p_do_policy_check IN VARCHAR2 DEFAULT FND_API.G_TRUE
143 ,p_validate_hierarchy IN VARCHAR2 DEFAULT FND_API.G_TRUE
144 ,x_failed_row_id_list OUT NOCOPY VARCHAR2
145 ,x_return_status OUT NOCOPY VARCHAR2
146 ,x_errorcode OUT NOCOPY NUMBER
147 ,x_msg_count OUT NOCOPY NUMBER
148 ,x_msg_data OUT NOCOPY VARCHAR2
149 );
150
151 -- -----------------------------------------------------------------------------
152 -- API Name: Get_User_Attrs_For_Item
153 --
154 -- Description:
155 -- Fetch passed-in User-Defined Attrs data for
156 -- the Item whose Primary Keys are passed in
157 -- -----------------------------------------------------------------------------
158 PROCEDURE Get_User_Attrs_For_Item (
159 p_api_version IN NUMBER
160 ,p_inventory_item_id IN NUMBER
161 ,p_organization_id IN NUMBER
162 ,p_attr_group_request_table IN EGO_ATTR_GROUP_REQUEST_TABLE
163 ,p_entity_id IN NUMBER DEFAULT NULL
164 ,p_entity_index IN NUMBER DEFAULT NULL
165 ,p_entity_code IN VARCHAR2 DEFAULT NULL
166 ,p_debug_level IN NUMBER DEFAULT 0
167 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
168 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
169 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
170 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
171 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
172 ,x_attributes_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_errorcode OUT NOCOPY NUMBER
175 ,x_msg_count OUT NOCOPY NUMBER
176 ,x_msg_data OUT NOCOPY VARCHAR2
177 );
178
179 -- -----------------------------------------------------------------------------
180 -- API Name: Generate_Seq_For_Item_Catalog
181 --
182 -- Description:
183 -- Generates the Item Sequence For Number Generation
184 -- -----------------------------------------------------------------------------
185 PROCEDURE Generate_Seq_For_Item_Catalog (
186 p_item_catalog_group_id IN NUMBER
187 ,p_seq_start_num IN NUMBER
188 ,p_seq_increment_by IN NUMBER
189 ,x_return_status OUT NOCOPY VARCHAR2
190 ,x_errorcode OUT NOCOPY NUMBER
191 ,x_msg_count OUT NOCOPY NUMBER
192 ,x_msg_data OUT NOCOPY VARCHAR2
193 );
194
195 ----------------------------------------------------------------------
196
197
198 -- -----------------------------------------------------------------------------
199 -- API Name: Drop_Sequence_For_Item_Catalog
200 --
201 -- Description:
202 -- Generates the Item Sequence For Number Generation
203 -- -----------------------------------------------------------------------------
204 PROCEDURE Drop_Sequence_For_Item_Catalog (
205 p_item_catalog_seq_name IN VARCHAR2
206 ,x_return_status OUT NOCOPY VARCHAR2
207 ,x_errorcode OUT NOCOPY NUMBER
208 ,x_msg_count OUT NOCOPY NUMBER
209 ,x_msg_data OUT NOCOPY VARCHAR2
210 );
211
212
213 -- -----------------------------------------------------------------------------
214 -- API Name: Process_item_role
215 --
216 -- Description:
217 -- API to manage roles on Items
218 --
219 -- Note: Please refer to EGO_ITEM_PUB.Process_item_role for details
220 --
221 -- -----------------------------------------------------------------------------
222 PROCEDURE Process_item_role
223 (p_api_version IN NUMBER
224 ,p_commit IN VARCHAR2
225 ,p_init_msg_list IN VARCHAR2
226 ,p_transaction_type IN VARCHAR2
227 ,p_inventory_item_id IN NUMBER
228 ,p_item_number IN VARCHAR2
229 ,p_organization_id IN NUMBER
230 ,p_organization_code IN VARCHAR2
231 ,p_role_id IN NUMBER
232 ,p_role_name IN VARCHAR2
233 ,p_instance_type IN VARCHAR2
234 ,p_instance_set_id IN NUMBER
235 ,p_instance_set_name IN VARCHAR2
236 ,p_party_type IN VARCHAR2
237 ,p_party_id IN NUMBER
238 ,p_party_name IN VARCHAR2
239 ,p_start_date IN DATE
240 ,p_end_date IN DATE
241 ,x_grant_guid IN OUT NOCOPY RAW
242 ,x_return_status OUT NOCOPY VARCHAR2
243 ,x_msg_count OUT NOCOPY NUMBER
244 ,x_msg_data OUT NOCOPY VARCHAR2
245 );
246
247 -- -----------------------------------------------------------------------------
248 -- API Name: Process_item_phase_and_status
249 --
250 -- Description:
251 -- API to change the phase and status of item / revision
252 --
253 -- Note: Please refer to EGO_ITEM_PUB.Process_item_phase_and_status for details
254 --
255 -- -----------------------------------------------------------------------------
256 PROCEDURE Process_item_phase_and_status
257 (p_api_version IN NUMBER
258 ,p_commit IN VARCHAR2
259 ,p_init_msg_list IN VARCHAR2
260 ,p_transaction_type IN VARCHAR2
261 ,p_inventory_item_id IN NUMBER
262 ,p_item_number IN VARCHAR2
263 ,p_organization_id IN NUMBER
264 ,p_organization_code IN VARCHAR2
265 ,p_revision_id IN NUMBER
266 ,p_revision IN VARCHAR2
267 ,p_implement_changes IN VARCHAR2
268 ,p_status IN VARCHAR2
269 ,p_effective_date IN DATE
270 ,p_lifecycle_id IN NUMBER
271 ,p_phase_id IN NUMBER
272 ,p_new_effective_date IN DATE
273 ,x_return_status OUT NOCOPY VARCHAR2
274 ,x_msg_count OUT NOCOPY NUMBER
275 ,x_msg_data OUT NOCOPY VARCHAR2
276 );
277
278 -- -----------------------------------------------------------------------------
279 -- API Name: Implement_Item_Pending_Changes
280 --
281 -- Description:
282 -- API to implement the pending changes on the item / revision
283 --
284 -- Note: Please refer to EGO_ITEM_PUB.Implement_Item_Pending_Changes for details
285 --
286 -- -----------------------------------------------------------------------------
287 PROCEDURE Implement_Item_Pending_Changes
288 (p_api_version IN NUMBER
289 ,p_commit IN VARCHAR2
290 ,p_init_msg_list IN VARCHAR2
291 ,p_inventory_item_id IN NUMBER
292 ,p_item_number IN VARCHAR2
293 ,p_organization_id IN NUMBER
294 ,p_organization_code IN VARCHAR2
295 ,p_revision_id IN NUMBER
296 ,p_revision IN VARCHAR2
297 ,x_return_status OUT NOCOPY VARCHAR2
298 ,x_msg_count OUT NOCOPY NUMBER
299 ,x_msg_data OUT NOCOPY VARCHAR2
300 );
301
302
303 -- -----------------------------------------------------------------------------
304 -- Fix for Bug# 4052565.
305 --
306 -- API Name: has_role_on_item
307 --
308 -- Description:
309 -- API to check whether the user has a role on Item or Not
310 -- TRUE if the user has the specified role on the item
311 -- FALSE if the user does not have the specified role on the item
312 --
313 -- -----------------------------------------------------------------------------
314 FUNCTION has_role_on_item (p_function_name IN VARCHAR2
315 ,p_instance_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
316 ,p_inventory_item_id IN NUMBER
317 ,p_item_number IN VARCHAR2
318 ,p_organization_id IN VARCHAR2
319 ,p_organization_name IN VARCHAR2
320 ,p_user_id IN NUMBER
321 ,p_party_id IN NUMBER
322 ,p_set_message IN VARCHAR2
323 ) RETURN BOOLEAN;
324
325
326 -- -----------------------------------------------------------------------------
327 -- Fix for Bug# 3945885.
328 --
329 -- API Name: Get_Seq_Gen_Item_Nums
330 --
331 -- Description:
332 -- API to return a Sequence of Item Numbers, given the Item Catalog Group ID.
333 -- Number of Item Numbers to be generated, is the size of the Org ID table
334 -- passed to the API.
335 -- -----------------------------------------------------------------------------
336 PROCEDURE Get_Seq_Gen_Item_Nums( p_item_catalog_group_id IN NUMBER
337 ,p_org_id_tbl IN DBMS_SQL.VARCHAR2_TABLE
338 ,x_item_num_tbl IN OUT NOCOPY EGO_VARCHAR_TBL_TYPE
339 );
340
341
342 -------------------------------------------------------------------------------------
343 -- API Name: Get_Default_Template_Id --
344 -- --
345 -- Description: This function takes a catalog group ID as a parameter and returns --
346 -- the template ID corresponding to the default template for the specified --
347 -- catalog group. --
348 -- --
349 -- Parameters: p_category_id NUMBER Catalog group ID whose default template --
350 -- is to be returned; if null, return --
351 -- value is null. --
352 -------------------------------------------------------------------------------------
353 FUNCTION Get_Default_Template_Id (
354 p_category_id IN NUMBER
355 ) RETURN NUMBER;
356
357
358 -- -----------------------------------------------------------------------------
359 -- API Name: Validate_Required_Attrs
360 --
361 -- Description:
362 -- Given an Item whose Primary Keys are passed in, find those attributes
363 -- whose values are required but is null for the Item.
364 -- Returns EGO_USER_ATTR_TABLE containing list of required
365 -- attributes information.
366 -- -----------------------------------------------------------------------------
367 --
368 PROCEDURE Validate_Required_Attrs (
369 p_api_version IN NUMBER
370 ,p_inventory_item_id IN NUMBER
371 ,p_organization_id IN NUMBER
372 ,p_revision_id IN NUMBER DEFAULT NULL
373 ,x_attributes_req_table OUT NOCOPY EGO_USER_ATTR_TABLE
374 ,x_return_status OUT NOCOPY VARCHAR2
375 ,x_errorcode OUT NOCOPY NUMBER
376 ,x_msg_count OUT NOCOPY NUMBER
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 );
379
380
381
382
383
384 -------------------------------------------------------------------------------------
385 -- API Name: Generate_Gtin_Tp_Attrs_View --
386 -- --
387 -- Description: This API would create a wrapper trading partner view over the --
388 -- passed in views. The wrapper view shows the final value of an --
389 -- attribute for a item for a trading partner --
390 -- --
391 -- Parameters: p_item_attr_agv_name VARCHAR Name of the Item attr group view --
392 -- Parameters: p_tp_agv_name VARCHAR Name of the trading partner attr --
393 -- group view --
394 -- Parameters: p_item_attr_agv_alias VARCHAR Alias to be used for the item attr --
395 -- group view. --
396 -- Parameters: p_tp_agv_alias VARCHAR Alias to be used for the trading --
397 -- partner attr group view. --
398 -- Parameters: p_final_agv_name VARCHAR Name to be used for the final --
399 -- wrapper view. --
400 -------------------------------------------------------------------------------------
401
402
403
404 PROCEDURE GENERATE_GTIN_TP_ATTRS_VIEW (
405 p_item_attr_agv_name IN VARCHAR2
406 ,p_tp_agv_name IN VARCHAR2
407 ,p_item_attr_agv_alias IN VARCHAR2
408 ,p_tp_agv_alias IN VARCHAR2
409 ,p_final_agv_name IN VARCHAR2
410 ,p_multi_row_ag IN VARCHAR2
411 ,x_return_status OUT NOCOPY VARCHAR2
412 ,x_msg_data OUT NOCOPY VARCHAR2
413 );
414
415
416 -------------------------------------------------------------------------------------
417 -- API Name: Generate_GDSN_Ext_AG_TP_Views --
418 -- --
419 -- Description: This API would process the SBDH extension attr groups and create --
420 -- final views for all the attr groups. --
421 -- --
422 -- Parameters: p_attr_group_name VARCHAR Name of the Item attr group for --
423 -- which view needs to be generated --
424 -- if passed as null all the ag's --
425 -- associated with SBDH extension are --
426 -- processed.
427 -------------------------------------------------------------------------------------
428
429 PROCEDURE Generate_GDSN_Ext_AG_TP_Views (
430 p_attr_group_name IN VARCHAR2 DEFAULT NULL
431 ,ERRBUF OUT NOCOPY VARCHAR2
432 ,RETCODE OUT NOCOPY VARCHAR2
433 );
434
435 -------------------------------------------------------------------------------------
436 -- API Name: process_attribute_defaulting --
437 -- --
438 -- Description: This API would process the SBDH extension attr groups and create --
439 -- final views for all the attr groups. --
440 -- --
441 -- Parameters: p_item_attr_def_tab SYSTEM.EGO_ITEM_ATTR_DEFAULT_TABLE --
442 -- PL/SQL Table which has the Item Details --
443 -------------------------------------------------------------------------------------
444
445 PROCEDURE process_attribute_defaulting(p_item_attr_def_tab IN OUT NOCOPY SYSTEM.EGO_ITEM_ATTR_DEFAULT_TABLE
446 ,p_gdsn_enabled IN VARCHAR2 DEFAULT 'N'
447 ,p_commit IN VARCHAR2
448 ,x_return_status OUT NOCOPY VARCHAR2
449 ,x_msg_data OUT NOCOPY VARCHAR2
450 ,x_msg_count OUT NOCOPY NUMBER) ;
451
452
453 -- -----------------------------------------------------------------------------
454 -- API Name: Get_Related_Class_Codes
455 --
456 -- Description:
457 -- Gets the related classification codes list for a given classification code
458 -- added in Spec by dsakalle for bug 5523366
459 -- -----------------------------------------------------------------------------
460 PROCEDURE Get_Related_Class_Codes (
461 p_classification_code IN VARCHAR2
462 ,p_entity_id IN NUMBER DEFAULT NULL
463 ,p_entity_index IN NUMBER DEFAULT NULL
464 ,p_entity_code IN VARCHAR2 DEFAULT NULL
465 ,x_related_class_codes_list OUT NOCOPY VARCHAR2);
466
467 END EGO_Item_PVT;
[Home] [Help]