1 PACKAGE IBE_DSP_HIERARCHY_QUERY_PVT AUTHID CURRENT_USER AS
2 /* $Header: IBEVCHQS.pls 115.16 2003/08/21 20:13:06 abhandar ship $ */
3
4 -- HISTORY
5 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) and Debug (Bug # 2691710) Changes.
6 -- 08/16/03 abhandar added procedure load_section_hierarchy() (bug ##3090284)
7 -- **********************************************************************************************************
8
9 -- Cursor which would return with section related data from
10 -- ibe_dsp_sections_vl view.
11 TYPE SECTION_CSR IS REF CURSOR;
12
13 -- Cursor with data from ibe_dsp_section_items table and mtl_system_items
14 -- table.
15 TYPE SECTIONITEM_ITEM_CSR IS REF CURSOR;
16
17 -- Cursor with data from ibe_dsp_section_items table and ibe_dsp_sections_vl
18 -- view.
19 TYPE SECTIONITEM_SECTION_CSR IS REF CURSOR;
20
21 -- Cursor with data from ibe_dsp_msite_sct_sects table and ibe_dsp_sections_vl
22 -- view.
23 TYPE SECTION_SECTION_CSR IS REF CURSOR;
24
25 -- Cursor with data for lookup_code and meaning for lookup type =
26 -- 'IBE_SECTION_TYPE'
27 TYPE SECTION_TYPE_CSR IS REF CURSOR;
28
29 -- Cursor with data for lookup_code and meaning for lookup type =
30 -- 'IBE_SECTION_STATUS'
31 TYPE SECTION_STATUS_CSR IS REF CURSOR;
32
33 -- Cursor with data for deliverables (logical templates)
34 TYPE DELIVERABLE_CSR IS REF CURSOR;
35
36 -- Cursor with data for display context
37 TYPE DISPLAY_CONTEXT_CSR IS REF CURSOR;
38
39 -- Cursor with data for mini-site
40 TYPE MINI_SITE_CSR IS REF CURSOR;
41
42 -- Cursor with item data
43 TYPE INVENTORY_ITEM_CSR IS REF CURSOR;
44
45 -- Cursor with list of web status types
46 TYPE WEB_STATUS_TYPE_CSR IS REF CURSOR;
47
48 -- added by abhandar
49 -- Cursor with section hierarchy information
50 TYPE SECTION_HGRID_CSR IS REF CURSOR;
51
52 PROCEDURE Load_SectionItems_For_Section
53 (
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
56 p_commit IN VARCHAR2 := FND_API.G_FALSE,
57 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
58 p_section_id IN NUMBER,
59 x_section_csr OUT NOCOPY SECTION_CSR,
60 x_sectionitem_item_csr OUT NOCOPY SECTIONITEM_ITEM_CSR,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2
64 );
65
66 PROCEDURE Load_SectionItems_For_Item
67 (
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
70 p_commit IN VARCHAR2 := FND_API.G_FALSE,
71 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
72 p_inventory_item_id IN NUMBER,
73 p_organization_id IN NUMBER,
74 x_sectionitem_section_csr OUT NOCOPY SECTIONITEM_SECTION_CSR,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2
78 );
79
80 PROCEDURE Load_ChildSections_For_Section
81 (
82 p_api_version IN NUMBER,
83 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
84 p_commit IN VARCHAR2 := FND_API.G_FALSE,
85 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
86 p_section_id IN NUMBER,
87 x_section_csr OUT NOCOPY SECTION_CSR,
88 x_section_section_csr OUT NOCOPY SECTION_SECTION_CSR,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 );
93
94 PROCEDURE Load_Children_For_Section
95 (
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
98 p_commit IN VARCHAR2 := FND_API.G_FALSE,
99 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
100 p_section_id IN NUMBER,
101 x_is_leaf_section OUT NOCOPY VARCHAR2,
102 x_section_path OUT NOCOPY VARCHAR2,
103 x_section_csr OUT NOCOPY SECTION_CSR,
104 x_section_section_csr OUT NOCOPY SECTION_SECTION_CSR,
105 x_sectionitem_item_csr OUT NOCOPY SECTIONITEM_SECTION_CSR,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2
109 );
110
111 PROCEDURE Load_Section_For_Basic_Desc
112 (
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 p_section_id IN NUMBER,
118 x_section_path OUT NOCOPY VARCHAR2,
119 x_section_csr OUT NOCOPY SECTION_CSR,
120 x_section_type_csr OUT NOCOPY SECTION_TYPE_CSR,
121 x_section_status_csr OUT NOCOPY SECTION_STATUS_CSR,
122 x_deliverable_csr OUT NOCOPY DELIVERABLE_CSR,
123 x_display_context_csr OUT NOCOPY DISPLAY_CONTEXT_CSR,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2
127 );
128
129 PROCEDURE Load_Root_Sct_For_Basic_Desc
130 (
131 p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
135 x_section_path OUT NOCOPY VARCHAR2,
136 x_section_csr OUT NOCOPY SECTION_CSR,
137 x_section_type_csr OUT NOCOPY SECTION_TYPE_CSR,
138 x_section_status_csr OUT NOCOPY SECTION_STATUS_CSR,
139 x_deliverable_csr OUT NOCOPY DELIVERABLE_CSR,
140 x_display_context_csr OUT NOCOPY DISPLAY_CONTEXT_CSR,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2
144 );
145
146 PROCEDURE Get_Cand_Incl_MSites_For_Sct
147 (
148 p_api_version IN NUMBER,
149 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
150 p_commit IN VARCHAR2 := FND_API.G_FALSE,
151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
152 p_section_id IN NUMBER,
153 x_section_path OUT NOCOPY VARCHAR2,
154 x_section_csr OUT NOCOPY SECTION_CSR,
155 x_incl_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
156 x_cndt_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2
160 );
161
162 PROCEDURE Load_Items_For_Basic_Desc
163 (
164 p_api_version IN NUMBER,
165 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
166 p_commit IN VARCHAR2 := FND_API.G_FALSE,
167 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
168 p_inventory_item_id IN NUMBER,
169 p_organization_id IN NUMBER,
170 x_inventory_item_csr OUT NOCOPY INVENTORY_ITEM_CSR,
171 x_web_status_type_csr OUT NOCOPY WEB_STATUS_TYPE_CSR,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2
175 );
176
177 PROCEDURE Get_Cand_Incl_MSites_For_Itm
178 (
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
181 p_commit IN VARCHAR2 := FND_API.G_FALSE,
182 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
183 p_inventory_item_id IN NUMBER,
184 p_organization_id IN NUMBER,
185 x_inventory_item_csr OUT NOCOPY INVENTORY_ITEM_CSR,
186 x_incl_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
187 x_cndt_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2
191 );
192 --
193 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Id_List :bug 2936693 :05/09/2003:abhandar
194 ---
195 --
196 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Info_For_Lookup :bug 2936693 :05/09/2003:abhandar
197 ---
198 --
199 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Info_For_Detail_List :bug 2936693 :05/09/2003:abhandar
200 ---
201 --
202 -- Obsoleted and Removed code for the PROCEDURE Get_Section_Id_List :bug 2936693 :05/09/2003 :abhandar
203 ---
204 --
205 -- Obsoleted and Removedcode for the PROCEDURE Get_Section_Info_For_Lookup :bug 2936693 :05/09/2003:abhandar
206 ---
207
208 -- added by abhandar apr/24/2002
209 PROCEDURE Get_Section_Path
210 (
211 p_section_id IN NUMBER,
212 x_section_path OUT NOCOPY VARCHAR2,
213 x_section_name OUT NOCOPY VARCHAR2,
214 x_section_desc OUT NOCOPY VARCHAR2,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2
218 );
219
220 -- added by abhandar 08/16/03 for loading hgrid data
221 PROCEDURE Load_Section_Hierarchy
222 (
223 p_msite_id IN NUMBER,
224 p_section_id IN NUMBER,
225 p_level_number IN NUMBER,
226 x_section_hierarchy_csr OUT NOCOPY SECTION_HGRID_CSR,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2
230 );
231
232 END IBE_DSP_HIERARCHY_QUERY_PVT;
233