1 PACKAGE INV_SUB_CG_UTIL AUTHID CURRENT_USER AS
2 /* $Header: INVSBCGS.pls 120.1 2005/06/15 14:46:16 appldev $ */
3
4 /*
5 ** -----------------------------------
6 ** Subinventory record type definition
7 ** -----------------------------------
8 */
9
10 type sub_rec is record
11 (
12 organization_id number
13 ,subinventory varchar2(10)
14 );
15
16 type sub_rec_tbl is table of sub_rec
17 index by binary_integer;
18
19 /*
20 ** -----------------------------------
21 ** Organization record type definition
22 ** -----------------------------------
23 */
24
25 type org_rec is record
26 (
27 organization_id number
28 );
29
30 type org_rec_tbl is table of org_rec
31 index by binary_integer;
32
33 /*
34 ** -------------------------------------------------------------------------
35 ** Function: validate_cg_update
36 ** Description: Checks if cost group can be updated
37 ** Output:
38 ** x_return_status
39 ** return status indicating success, error, unexpected error
40 ** x_msg_count
41 ** number of messages in message list
42 ** x_msg_data
43 ** if the number of messages in message list is 1, contains
44 ** message text
45 ** Input:
46 ** p_cost_group_id
47 ** cost group for which the check has to be made
48 **
49 ** Returns:
50 ** TRUE if cost group can be updated, else FALSE
51 **
52 ** Please use return value to determine if cost group can be updated or not.
53 ** Do not use x_return_status for this purpose as
54 ** . x_return_status could be success and yet cost group not be updated
55 ** . x_return_status is set to error when an error(such as SQL error)
56 ** occurs.
57 ** --------------------------------------------------------------------------
58 */
59
60 function validate_cg_update (
61 x_return_status OUT NOCOPY VARCHAR2
62 , x_msg_count OUT NOCOPY NUMBER
63 , x_msg_data OUT NOCOPY VARCHAR2
64 , p_cost_group_id IN NUMBER) return boolean;
65
66 /*
67 ** -------------------------------------------------------------------------
68 ** Function: validate_cg_delete
69 ** Description: Checks if cost group can be delete
70 ** Output:
71 ** x_return_status
72 ** return status indicating success, error, unexpected error
73 ** x_msg_count
74 ** number of messages in message list
75 ** x_msg_data
76 ** if the number of messages in message list is 1, contains
77 ** message text
78 ** Input:
79 ** p_cost_group_id
80 ** cost group for which the check has to be made
81 **
82 ** Returns:
83 ** TRUE if cost group can be deleted, else FALSE
84 **
85 ** Please use return value to determine if cost group can be deleted or not.
86 ** Do not use x_return_status for this purpose as
87 ** . x_return_status could be success and yet cost group not be deleted
88 ** . x_return_status is set to error when an error(such as SQL error)
89 ** occurs.
90 ** --------------------------------------------------------------------------
91 */
92
93 function validate_cg_delete (
94 x_return_status OUT NOCOPY VARCHAR2
95 , x_msg_count OUT NOCOPY NUMBER
96 , x_msg_data OUT NOCOPY VARCHAR2
97 , p_cost_group_id IN NUMBER
98 , p_organization_id IN NUMBER DEFAULT NULL) return boolean;
99
100 /*
101 ** -------------------------------------------------------------------------
102 ** Procedure: update_sub_accounts
103 ** Description: updates a given subinventory with a given cost group's accounts
104 ** Output:
105 ** x_return_status
106 ** return status indicating success, error, unexpected error
107 ** x_msg_count
108 ** number of messages in message list
109 ** x_msg_data
110 ** if the number of messages in message list is 1, contains
111 ** message text
112 ** Input:
113 ** p_cost_group_id
114 ** cost group whose accounts have to be used to update subinventory
115 ** p_organization_id
116 ** organization to which the to be subinventory belongs
117 ** p_subinventory
118 ** subinventory whose accounts have to be synchronized with those
119 ** of cost group
120 **
121 ** Returns:
122 ** none
123 ** --------------------------------------------------------------------------
124 */
125
126 procedure update_sub_accounts (
127 x_return_status OUT NOCOPY VARCHAR2
128 , x_msg_count OUT NOCOPY NUMBER
129 , x_msg_data OUT NOCOPY VARCHAR2
130 , p_cost_group_id IN NUMBER
131 , p_organization_id IN NUMBER
132 , p_subinventory IN VARCHAR2);
133
134 /*
135 ** -------------------------------------------------------------------------
136 ** Procedure: update_org_accounts
137 ** Description: updates a given organization with a given cost group's accounts
138 ** Output:
139 ** x_return_status
140 ** return status indicating success, error, unexpected error
141 ** x_msg_count
142 ** number of messages in message list
143 ** x_msg_data
144 ** if the number of messages in message list is 1, contains
145 ** message text
146 ** Input:
147 ** p_cost_group_id
148 ** cost group whose accounts have to be used to update organization
149 ** p_organization_id
150 ** organization whose accounts have to be synchronized with those
151 ** of cost group
152 **
153 ** Returns:
154 ** none
155 ** --------------------------------------------------------------------------
156 */
157
158 procedure update_org_accounts (
159 x_return_status OUT NOCOPY VARCHAR2
160 , x_msg_count OUT NOCOPY NUMBER
161 , x_msg_data OUT NOCOPY VARCHAR2
162 , p_cost_group_id IN NUMBER
163 , p_organization_id IN NUMBER);
164
165 /*
166 ** -------------------------------------------------------------------------
167 ** Procedure: get_subs_from_cg
168 ** Description: returns all subinventories that have given cost group as
169 ** default cost group
170 ** Output:
171 ** x_return_status
172 ** return status indicating success, error, unexpected error
173 ** x_msg_count
174 ** number of messages in message list
175 ** x_msg_data
176 ** if the number of messages in message list is 1, contains
177 ** message text
178 ** x_sub_tbl
179 ** table of subinventories that have given cost group as
180 ** default cost group
181 ** x_count
182 ** number of records in x_sub_tbl
183 ** Input:
184 ** p_cost_group_id
185 ** cost group to be checked if default cost group in subinventories
186 ** table
187 **
188 ** Returns:
189 ** none
190 ** --------------------------------------------------------------------------
191 */
192
193 procedure get_subs_from_cg(
194 x_return_status OUT NOCOPY VARCHAR2
195 , x_msg_count OUT NOCOPY NUMBER
196 , x_msg_data OUT NOCOPY VARCHAR2
197 , x_sub_tbl OUT NOCOPY inv_sub_cg_util.sub_rec_tbl
198 , x_count OUT NOCOPY NUMBER
199 , p_cost_group_id IN NUMBER);
200
201 /*
202 ** -------------------------------------------------------------------------
203 ** Procedure: get_orgs_from_cg
204 ** Description: returns all organizations that have given cost group as default
205 ** cost group
206 ** Output:
207 ** x_return_status
208 ** return status indicating success, error, unexpected error
209 ** x_msg_count
210 ** number of messages in message list
211 ** x_msg_data
212 ** if the number of messages in message list is 1, contains
213 ** message text
214 ** x_org_tbl
215 ** table of organizations that have given cost group as default
216 ** cost group
217 ** x_count
218 ** number of records in x_org_tbl
219 ** Input:
220 ** p_cost_group_id
221 ** cost group to be checked if default cost group in organizations
222 ** table
223 **
224 ** Returns:
225 ** none
226 ** --------------------------------------------------------------------------
227 */
228
229 procedure get_orgs_from_cg(
230 x_return_status OUT NOCOPY VARCHAR2
231 , x_msg_count OUT NOCOPY NUMBER
232 , x_msg_data OUT NOCOPY VARCHAR2
233 , x_org_tbl OUT NOCOPY inv_sub_cg_util.org_rec_tbl
234 , x_count OUT NOCOPY NUMBER
235 , p_cost_group_id IN NUMBER);
236
237 /*
238 ** -------------------------------------------------------------------------
239 ** Procedure: get_cg_from_org
240 ** Description: returns default cost group of given organization
241 ** Output:
242 ** x_return_status
243 ** return status indicating success, error, unexpected error
244 ** x_msg_count
245 ** number of messages in message list
246 ** x_msg_data
247 ** if the number of messages in message list is 1, contains
248 ** message text
249 ** Input:
250 ** p_organization_id
251 ** organization whose default cost group has to be found
252 **
253 ** Returns:
254 ** default cost group of organization.
255 ** 0 - no default cost group
256 ** !(0) - default cost group exists
257 ** --------------------------------------------------------------------------
258 */
259
260 function get_cg_from_org(
261 x_return_status OUT NOCOPY VARCHAR2
262 , x_msg_count OUT NOCOPY NUMBER
263 , x_msg_data OUT NOCOPY VARCHAR2
264 , p_organization_id IN NUMBER) return number;
265
266 /*
267 ** -------------------------------------------------------------------------
268 ** Procedure: get_cg_from_sub
269 ** Description: returns default cost group of given subinventory
270 ** Output:
271 ** x_return_status
272 ** return status indicating success, error, unexpected error
273 ** x_msg_count
274 ** number of messages in message list
275 ** x_msg_data
276 ** if the number of messages in message list is 1, contains
277 ** message text
278 ** Input:
279 ** p_organization_id
280 organization to which subinventory belongs
281 ** p_subinventory
282 ** subinventory whose default cost group has to be found
283 **
284 ** Returns:
285 ** default cost group of subinventory.
286 ** 0 - no default cost group
287 ** !(0) - default cost group exists
288 ** --------------------------------------------------------------------------
289 */
290
291 function get_cg_from_sub(
292 x_return_status OUT NOCOPY VARCHAR2
293 , x_msg_count OUT NOCOPY NUMBER
294 , x_msg_data OUT NOCOPY VARCHAR2
295 , p_organization_id IN NUMBER
296 , p_subinventory IN VARCHAR2) return number;
297
298 /*
299 ** -------------------------------------------------------------------------
300 ** Procedure: find_update_subs_accounts
301 ** Description: For a given cost group, all subinventories that have it as a
302 ** default cost group are found and their accounts are
303 ** synchronized with those of the cost group
304 ** Output:
305 ** x_return_status
306 ** return status indicating success, error, unexpected error
307 ** x_msg_count
308 ** number of messages in message list
309 ** x_msg_data
310 ** if the number of messages in message list is 1, contains
311 ** message text
312 ** Input:
313 ** p_cost_group_id
314 ** cost group whose accounts will be used to synchronize with
315 ** accounts of subinventories that have this cost group as
316 ** the default cost group
317 ** Returns:
318 ** none
319 ** --------------------------------------------------------------------------
320 */
321
322 procedure find_update_subs_accounts(
323 x_return_status OUT NOCOPY VARCHAR2
324 , x_msg_count OUT NOCOPY NUMBER
325 , x_msg_data OUT NOCOPY VARCHAR2
326 , p_cost_group_id IN NUMBER);
327
328 /*
329 ** -------------------------------------------------------------------------
330 ** Procedure: find_update_orgs_accounts
331 ** Description: For a given cost group, all organziations that have it as a
332 ** default cost group are found and their accounts are
333 ** synchronized with those of the cost group
334 ** Output:
335 ** x_return_status
336 ** return status indicating success, error, unexpected error
337 ** x_msg_count
338 ** number of messages in message list
339 ** x_msg_data
340 ** if the number of messages in message list is 1, contains
341 ** message text
342 ** Input:
343 ** p_cost_group_id
344 ** cost group whose accounts will be used to synchronize with
345 ** accounts of organziations that have this cost group as
346 ** the default cost group
347 ** Returns:
348 ** none
349 ** --------------------------------------------------------------------------
350 */
351
352 procedure find_update_orgs_accounts(
353 x_return_status OUT NOCOPY VARCHAR2
354 , x_msg_count OUT NOCOPY NUMBER
355 , x_msg_data OUT NOCOPY VARCHAR2
356 , p_cost_group_id IN NUMBER);
357
358 end INV_SUB_CG_UTIL;