1 PACKAGE OZF_FUND_ALLOCATIONS_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvalcs.pls 115.5 2004/01/28 14:39:31 kdass noship $*/
3
4 g_max_end_level CONSTANT NUMBER := 99999;
5 /* due to round up problems, sometimes validation fails although the difference is tiny tiny.
6 so any difference less than g_max_ignorable_amount is ignored
7 */
8 g_max_ignorable_amount CONSTANT NUMBER := 0.0000000000000000000000000000001;
9
10 TYPE fact_table_type IS TABLE OF ozf_actmetricfact_Pvt.act_metric_fact_rec_type INDEX BY BINARY_INTEGER;
11 TYPE factid_type IS RECORD (
12 fact_id NUMBER,
13 fact_obj_ver NUMBER,
14 approve_recommend VARCHAR2(1) -- Y: approve recommended amount, N: approve request amount
15 );
16 TYPE factid_table_type IS TABLE OF factid_type INDEX BY BINARY_INTEGER;
17
18 ---------------------------------------------------------------------
19 -- FUNCTION
20 -- get_max_end_level
21 --
22 -- PURPOSE
23 -- returns g_max_end_level
24 -- called by BudgetTopbotAdmEO.java
25 -- HISTORY
26 -- 01/28/04 kdass Created.
27 -- PARAMETERS
28 --
29 ---------------------------------------------------------------------
30 FUNCTION get_max_end_level RETURN NUMBER;
31
32 ---------------------------------------------------------------------
33 -- PROCEDURE
34 -- get_prior_year_sales
35 --
36 -- PURPOSE
37 -- public api to get prior year's total sales amount for one territory node
38 -- called by compute_worksheet and UI worksheet page
39 --
40 -- HISTORY
41 -- 10/16/02 yzhao Created.
42 -- 14/07/03 nkumar Modified.
43 --
44 -- PARAMETERS
45 ---------------------------------------------------------------------
46
47 PROCEDURE get_prior_year_sales(
48 p_hierarchy_id IN NUMBER
49 , p_node_id IN NUMBER
50 , p_basis_year IN NUMBER
51 , p_alloc_id IN NUMBER
52 , x_self_amount OUT NOCOPY NUMBER
53 , x_rollup_amount OUT NOCOPY NUMBER
54 , x_return_status OUT NOCOPY VARCHAR2
55 , x_msg_count OUT NOCOPY NUMBER
56 , x_msg_data OUT NOCOPY VARCHAR2
57 );
58
59
60 ---------------------------------------------------------------------
61 -- PROCEDURE
62 --- create_alloc_hierarchy
63 --
64 -- PURPOSE
65 -- Create allocation worksheet hierarchy.
66 --
67 -- HISTORY
68 -- 05/20/02 yzhao Created.
69 --
70 -- PARAMETERS
71 ---------------------------------------------------------------------
72 PROCEDURE create_alloc_hierarchy(
73 p_api_version IN NUMBER DEFAULT 1.0
74 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
75 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
76 , p_alloc_id IN NUMBER
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_msg_count OUT NOCOPY NUMBER
79 , x_msg_data OUT NOCOPY VARCHAR2
80 );
81
82
83 ----------------------------------------------------------------------------------------
84 -- This Procedure will publish allocation worksheet --
85 -- create draft or active child funds for the allocation. --
86 -- child funds inherit parent funds's market and product eligibity if it's active --
87 -- send notification to child budget owner --
88 -- set published flag for the allocation --
89 ---------------------------------PARAMETERS---------------------------------------------
90 -- p_alloc_id allocation id in ozf_act_metrics_all table --
91 -- p_alloc_status allocation status in ozf_act_metric_facts_all table
92 ----------------------------------------------------------------------------------------
93 Procedure publish_allocation( p_api_version IN NUMBER DEFAULT 1.0
94 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
95 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
96 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
97 , p_alloc_id IN NUMBER
98 , p_alloc_status IN VARCHAR2
99 , p_alloc_obj_ver IN NUMBER
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_msg_count OUT NOCOPY NUMBER
102 , x_msg_data OUT NOCOPY VARCHAR2
103 );
104
105
106 ----------------------------------------------------------------------------------------
107 -- This Procedure will validate an allocation worksheet --
108 -- For each node: --
109 -- Sum(this node and its sibling's allocation amount) <= parent allocation amount - holdback amount
110 -- Sum(child allocation amount) <= this node's allocation amount - holdback amount
111 ----------------------------------------------------------------------------------------
112 ---------------------------------PARAMETERS---------------------------------------------
113 -- p_alloc_id allocation id in ozf_act_metrics_all table --
114 ----------------------------------------------------------------------------------------
115 Procedure validate_worksheet(p_api_version IN NUMBER DEFAULT 1.0,
116 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
117 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
118 p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
119 p_alloc_id IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2
123 );
124
125
126 ----------------------------------------------------------------------------------------
127 -- This Procedure will update an allocation worksheet --
128 -- Public api called by worksheet update and publish button --
129 -- Only called by allocation in 'NEW' OR 'PLANNED' status --
130 -- It first updates fact amount according to the input --
131 -- then if 'cascade' flag is set, cascade changes down the whole hierarchy --
132 -- it also update the corresponding allocation budget's original and holdback amount--
133 -- if base percentage is null in table, set base percentage
134 -- cascade is allowed for recommended amount change only --
135 ----------------------------------------------------------------------------------------
136 ---------------------------------PARAMETERS---------------------------------------------
137 -- p_alloc_id allocation id in ozf_act_metrics_all table --
138 -- p_fact_table table of fact records to be changed --
139 -- required fields are: activity_metric_fact_id, object_version_number, --
140 -- recommend_total_amount, recommend_hb_amount, --
141 -- node_id, level_depth --
142 ----------------------------------------------------------------------------------------
143 Procedure update_worksheet_amount(p_api_version IN NUMBER DEFAULT 1.0,
144 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
145 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
146 p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
147 p_alloc_id IN NUMBER,
148 p_alloc_obj_ver IN NUMBER,
149 p_cascade_flag IN VARCHAR2 DEFAULT 'N',
150 p_fact_table IN fact_table_type,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2
154 );
155
156
157 ---------------------------------------------------------------------
158 -- PROCEDURE
159 --- update_alloc_status
160 --
161 -- PURPOSE
162 -- Update allocation status
163 -- public api called by worksheet page update button
164 --
165 -- HISTORY
166 -- 09/23/02 yzhao Created.
167 --
168 -- PARAMETERS
169 ---------------------------------------------------------------------
170 PROCEDURE update_alloc_status(
171 p_api_version IN NUMBER DEFAULT 1.0
172 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
173 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
174 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
175 , p_alloc_id IN NUMBER
176 , p_alloc_status IN VARCHAR2
177 , p_alloc_obj_ver IN NUMBER
178 , x_return_status OUT NOCOPY VARCHAR2
179 , x_msg_count OUT NOCOPY NUMBER
180 , x_msg_data OUT NOCOPY VARCHAR2
181 );
182
183
184 ----------------------------------------------------------------------------------------
185 -- This Procedure will approve a published allocation called by bottom-up budgeting --
186 -- the approver's fact record must be active to approve its children --
187 -- approve all levels below, or next level only --
188 -- create budget transfer record --
189 -- update child node status as 'ACTIVE' --
190 -- send notification to child budget owner --
191 ----------------------------------------------------------------------------------------
192 ---------------------------------PARAMETERS---------------------------------------------
193 -- p_approver_fact_id the approver's fact id. null means approver is the root budget --
194 -- p_approve_all_flag Y - approve all levels below; N - approve the next level only --
195 -- p_factid_table children fact ids to be approved --
196 ----------------------------------------------------------------------------------------
197 Procedure approve_levels(p_api_version IN NUMBER DEFAULT 1.0,
198 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
199 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
200 p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
201 p_approver_factid IN NUMBER,
202 p_approve_all_flag IN VARCHAR2,
203 p_factid_table IN factid_table_type,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2
207 );
208
209
210 ----------------------------------------------------------------------------------------
211 -- This Procedure will submit user's requested total and holdback amount --
212 -- only allocation in 'PLANNED' or 'REJECTED' status can user submit request --
213 -- update this node allocation status as 'SUBMITTED' --
214 -- send notification to parent budget owner --
215 -- record justificaiton note if any --
216 ----------------------------------------------------------------------------------------
217 ---------------------------------PARAMETERS---------------------------------------------
218 -- p_fact_id fact id --
219 -- p_fact_obj_ver fact object version number --
220 -- p_note justification note if any --
221 ----------------------------------------------------------------------------------------
222 Procedure submit_request(p_api_version IN NUMBER DEFAULT 1.0,
223 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
224 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
225 p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
226 p_fact_id IN NUMBER,
227 p_fact_obj_ver IN NUMBER,
228 p_note IN VARCHAR2 DEFAULT NULL,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2
232 );
233
234
235 ----------------------------------------------------------------------------------------
236 -- This Procedure will reject user's requested total and holdback amount --
237 -- only allocation in 'PLANNED' or 'ACTIVE' status can user reject request --
238 -- called by top or bottom level user --
239 -- update the child node allocation status as 'REJECTED' --
240 -- send notification to child budget owners --
241 ----------------------------------------------------------------------------------------
242 ---------------------------------PARAMETERS---------------------------------------------
243 -- p_rejector_factid rejector's fact id --
244 -- p_factid_table children fact ids to be rejected --
245 ----------------------------------------------------------------------------------------
246 Procedure reject_request(p_api_version IN NUMBER DEFAULT 1.0,
247 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
248 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
249 p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
250 p_rejector_factid IN NUMBER,
251 p_factid_table IN factid_table_type,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2
255 );
256
257 END OZF_Fund_allocations_Pvt;
[Home] [Help]