1 PACKAGE ENI_DENORM_HRCHY AS
2 /* $Header: ENIDENHS.pls 120.1 2007/03/13 08:52:26 lparihar ship $ */
3
4 g_delimiter VARCHAR2(5) := '/////';
5 -- This Public Function will return the Default Category Set Associated with
6 -- Product Reporting Functional Area
7 FUNCTION GET_CATEGORY_SET_ID RETURN NUMBER;
8
9 -- This Public Procedure is used to insert records in the Staging Table
10 -- The staging table will be used in the Incremental Load of Denorm Table.
11 -- All the modified/new records in the Product Catalog Hierarchy has to be
12 -- there in the Staging table, which has to be done by calling this procedure.
13 PROCEDURE INSERT_INTO_STAGING(
14 p_object_type IN VARCHAR2,
15 p_object_id IN NUMBER,
16 p_child_id IN NUMBER,
17 p_parent_id IN NUMBER,
18 p_mode_flag IN VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_language_code IN VARCHAR2 DEFAULT NULL);
23
24 -- This Procedure Denormalizes the Product Catalog Hierarchy into Denorm Table
25 -- This accepts the refresh mode as 'FULL' for initial load or 'PARTIAL' for incremental load
26 PROCEDURE LOAD_PRODUCT_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_refresh_mode IN VARCHAR2);
27
28 -- ER: 3154516
29 -- This Public Function will return the last updated date for Product Catalog from de-norm table
30 FUNCTION GET_LAST_CATALOG_UPDATE_DATE RETURN DATE;
31
32 -- ER: 3185516
33 -- This is a wrapper procedure, which will be called whenever there is a change in item assignment.
34 -- This in turn determines whether DBI is installed or not and calls the star pkg if installed.
35 -- Now this procedure will be called from INV forms, instead of them directly calling ENI Star pkg
36 PROCEDURE SYNC_CATEGORY_ASSIGNMENTS(
37 p_api_version NUMBER,
38 p_init_msg_list VARCHAR2 := 'F',
39 p_inventory_item_id NUMBER,
40 p_organization_id NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44 p_category_set_id NUMBER,
45 p_old_category_id NUMBER,
46 p_new_category_id NUMBER);
47
48 -- ER: 3185516
49 -- This is a wrapper procedure, which will be called after import items
50 -- This in calls the star pkg and updates the Item Assignment Flag in De-norm table
51 PROCEDURE SYNC_STAR_ITEMS_FROM_IOI(
52 p_api_version NUMBER,
53 p_init_msg_list VARCHAR2 := 'F',
54 p_set_process_id NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 X_MSG_DATA OUT NOCOPY VARCHAR2);
58
59 FUNCTION split_category_codes(
60 p_str VARCHAR2
61 ,p_level NUMBER
62 ,p_delim VARCHAR2 default g_delimiter) return VARCHAR2;
63
64 -- This Procedure Denormalizes the Product Catalog Hierarchy into a separate denorm table
65 -- [ENI_ICAT_CDENORM_HIERARCHIES]. It is designed to support SBA/OBIEE requirements.
66 -- The program is designed to flatten the hierarchy for levels ranging between 5 and 10.
67 -- The number of levels to denormalize is dynamic and is governed by a profile value.
68 -- Currently it only supports FULL REFRESH.
69 PROCEDURE LOAD_OBIEE_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2);
70
71 END ENI_DENORM_HRCHY;