1 PACKAGE cn_formula_common_pkg AUTHID CURRENT_USER AS
2 -- $Header: cnfmcoms.pls 120.3 2008/03/12 03:01:24 rnagired ship $
3
4 --
5 -- Package Body Name
6 -- cn_formula_common_pkg
7 -- Purpose
8 -- This package contains the procedures of calculation engine, some
9 -- of which will be called from each formula packages.
10 -- History
11 -- 3/2/1999 Richard Jin Created
12
13 TYPE mul_input_rec_type is RECORD
14 (input_amount NUMBER,
15 input_string VARCHAR2(30),
16 base_amount NUMBER,
17 amount NUMBER,
18 rate_dim_sequence NUMBER ,
19 tier_sequence NUMBER(15) );
20
21 TYPE mul_input_tbl_type IS TABLE OF mul_input_rec_type INDEX BY BINARY_INTEGER;
22
23 TYPE num_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
24
25 TYPE trx_rec_type is RECORD
26 (commission_line_id NUMBER(15),
27 commission_header_id NUMBER(15),
28 -- reversal_id NUMBER(15),
29 -- reversal_flag VARCHAR2(1),
30 salesrep_id NUMBER(15),
31 srp_plan_assign_id NUMBER(15),
32 quota_id NUMBER(15),
33 credit_type_id NUMBER(15),
34 processed_date DATE,
35 processed_period_id NUMBER(15),
36 pay_period_id NUMBER(15),
37 commission_amount NUMBER,
38 commission_rate NUMBER,
39 rate_tier_id NUMBER,
40 tier_split NUMBER,
41 input_achieved NUMBER,
42 output_achieved NUMBER,
43 perf_achieved NUMBER,
44 posting_status VARCHAR2(30) := fnd_api.g_miss_char,
45 pending_status VARCHAR2(30) := fnd_api.g_miss_char,
46 created_during VARCHAR2(30) := fnd_api.g_miss_char,
47 trx_type VARCHAR2(30) := fnd_api.g_miss_char,
48 error_reason VARCHAR2(150) := fnd_api.g_miss_char,
49 status VARCHAR2(150) := fnd_api.g_miss_char);
50
51 -- Procedure Name
52 -- calc_init
53 -- Scope
54 -- public
55 -- Purpose
56 -- To initialize before going to calculation
57 -- History
58 -- 02-March 1999 Richard Jin Created
59 --
60 PROCEDURE calculate_init( p_srp_plan_assign_id NUMBER,
61 p_salesrep_id NUMBER,
62 p_period_id NUMBER,
63 p_quota_id NUMBER,
64 p_start_date DATE ,
65 p_process_all_flag VARCHAR2,
66 p_intel_calc_flag VARCHAR2,
67 p_calc_type VARCHAR2,
68 p_trx_group_code VARCHAR2,
69 p_itd_flag VARCHAR2,
70 p_rollover_flag VARCHAR2,
71 x_commission_payed_ptd OUT NOCOPY NUMBER ,
72 x_commission_payed_itd OUT NOCOPY NUMBER ,
73 x_input_achieved_ptd OUT NOCOPY num_table_type,
74 x_input_achieved_itd OUT NOCOPY num_table_type,
75 x_output_achieved_ptd OUT NOCOPY NUMBER ,
76 x_output_achieved_itd OUT NOCOPY NUMBER ,
77 x_perf_achieved_ptd OUT NOCOPY NUMBER ,
78 x_perf_achieved_itd OUT NOCOPY NUMBER ,
79 x_select_status_flag OUT NOCOPY VARCHAR2 );
80
81 -- Procedure Name
82 -- calculate_roll
83 -- Scope
84 -- public
85 -- Purpose
86 -- To update cn_srp_period_quotas, cn_srp_per_quota_rc, cn_srp_periods
87 -- after the calculation of a quota is done
88 -- History
89 -- 02-March 1999 Richard Jin Created
90 --
91 PROCEDURE calculate_roll ( p_salesrep_id number,
92 p_period_id number,
93 p_quota_id number,
94 p_srp_plan_assign_id NUMBER,
95 p_calc_type VARCHAR2,
96 p_input_achieved_ptd num_table_type,
97 p_input_achieved_itd num_table_type,
98 p_output_achieved_ptd number,
99 p_output_achieved_itd number,
100 p_perf_achieved_ptd number,
101 p_perf_achieved_itd NUMBER,
102 p_rollover NUMBER) ;
103
104 -- Procedure Name
105 -- get_rates
106 -- Scope
107 -- public
108 -- Purpose
109 -- This is used to figure the commission rate and handle split or
110 -- accumulative option if any.
111 -- History
112 -- 02-March 1999 Richard Jin Created
113 --
114 PROCEDURE get_rates( p_salesrep_id NUMBER ,
115 p_srp_plan_assign_id NUMBER ,
116 p_period_id NUMBER ,
117 p_quota_id NUMBER ,
118 p_split_flag VARCHAR2 ,
119 p_itd_flag VARCHAR2,
120 p_processed_date DATE ,
121 p_number_dim NUMBER ,
122 p_mul_input_tbl IN OUT NOCOPY mul_input_tbl_type,
123 p_calc_formula_id NUMBER,
124 x_rate OUT NOCOPY NUMBER,
125 x_rate_tier_id OUT NOCOPY NUMBER,
126 x_tier_split OUT NOCOPY NUMBER );
127
128 --
129 -- Name
130 -- calculate_batch
131 -- Purpose
132 -- for all payee in marked srp_periods calculate the actual
133 -- commission amounts
134 -- History
135 --
136 -- 02/03/93 Tony Lower Created
137 -- 05/22/95 P Cook Debug. Review the performance of the sql without
138 -- the hints. May be better than with hints.
139 -- 07/10/98 Richard Jin Intelligent and incremental Calculation
140
141 PROCEDURE calculate_batch( p_physical_batch_id NUMBER);
142
143
144 -- Procedure Name
145 -- revert_batch
146 -- Scope
147 -- cn_commission_lines_pkg
148 -- Purpose
149 --
150 -- History
151 -- 10-JUL-98 Richard Jin Created
152 --
153 PROCEDURE revert_batch (p_batch_id cn_process_batches.physical_batch_id%TYPE);
154
155
156 -- Procedure Name
157 -- revert_header_lines
158 -- Scope
159 --
160 -- Purpose
161 -- delete/update derived commission_lines
162 -- History
163 -- 10-JUL-98 Richard Jin Created
164 PROCEDURE revert_header_lines( p_commission_header_id NUMBER, p_revert_state VARCHAR2);
165
166 -- revert commission lines if posted already
167 PROCEDURE revert_posting_line (p_commission_line_id NUMBER) ;
168
169 -- Procedure Name
170 -- get_start_period
171 -- Scope
172 -- public
173 -- Purpose
174 -- This procedure is to get the start period of current interval
175 -- History
176 -- 02-March 1999 Richard Jin Created
177 --
178 FUNCTION get_start_period_id ( p_quota_id NUMBER,
179 p_period_id NUMBER ) RETURN NUMBER;
180
181 -- Procedure Name
182 -- get_end_period
183 -- Scope
184 -- public
185 -- Purpose
186 -- This procedure is to get the end period of current interval
187 -- History
188 -- 02-March 1999 Richard Jin Created
189 --
190 FUNCTION get_end_period_id ( p_quota_id NUMBER,
191 p_period_id NUMBER ) RETURN NUMBER;
192
193 FUNCTION get_quarter_start_period_id(p_quota_id NUMBER, p_period_id NUMBER) RETURN NUMBER;
194 FUNCTION get_quarter_end_period_id(p_quota_id NUMBER, p_period_id NUMBER) RETURN NUMBER;
195
196 --
197 -- Name
198 -- EndOfInterval
199 -- Purpose
200 -- Returns 1 if the specified period is the end of an interval of the
201 -- type listed int he X_Interval string.
202 -- History
203 -- 06/13/95 Created Tony Lower
204 --
205 FUNCTION EndOfInterval ( p_quota_id NUMBER, p_period_id NUMBER )
206 RETURN BOOLEAN ;
207
208
209 --
210 -- Name
211 -- create_update_grp_trx
212 -- Purpose
213 -- create a new group by trx if not existed
214 -- update the existing group by trx if already existed
215 -- if already posted, create a reversal first
216 -- History
217 -- 06/13/95 Created Tony Lower
218 --
219 PROCEDURE create_trx( p_trx_rec IN OUT NOCOPY trx_rec_type);
220
221 --
222 -- Name
223 -- create_update_grp_trx
224 -- Purpose
225 -- create a new group by trx if not existed
226 -- update the existing group by trx if already existed
227 -- if already posted, create a reversal first
228 -- History
229 -- 06/13/95 Created Tony Lower
230 --
231 PROCEDURE update_trx( p_trx_rec_old IN OUT NOCOPY trx_rec_type,
232 p_trx_rec_new IN OUT NOCOPY trx_rec_type);
233
234
235 -- Procedure Name
236 -- handle_reversal_trx
237 -- Scope
238 -- public
239 -- Purpose
240 -- p_commission_header_id will be the commisson_header_id of the reversal trx created
241 -- History
242 -- 10-JUL-98 Richard Jin Created
243 PROCEDURE handle_reversal_trx ( p_commission_header_id NUMBER);
244
245 --
246 -- Name
247 -- populate_factors
248 -- Purpose
249 -- populate the event_factor, payment_factor, quota_factor and payees
250 --
251 -- History
252 -- 07/12/1999 Created Richard Jin
253 --
254 PROCEDURE populate_factors (p_physical_batch_id NUMBER);
255
256 -- Procedure Name
257 -- check_itd_calc_trx
258 -- Scope
259 -- public
260 -- Purpose
261 --
262 -- History
263 -- 02-March 1999 Richard Jin Created
264 FUNCTION check_itd_calc_trx ( p_salesrep_id NUMBER,
265 p_srp_plan_assign_id NUMBER,
266 p_period_id NUMBER,
267 p_quota_id NUMBER ) RETURN BOOLEAN;
268
269 -- Procedure Name
270 -- get_pq_itd_target
271 -- Scope
272 -- public
273 -- Purpose
274 --
275 -- History
276 -- 02-March 1999 Richard Jin Created
277 FUNCTION get_pq_itd_target ( p_period_id NUMBER,
278 p_quota_id NUMBER ) RETURN NUMBER;
279
280 -- Procedure Name
281 -- get_spq_itd_target
282 -- Scope
283 -- public
284 -- Purpose
285 --
286 -- History
287 -- 02-March 1999 Richard Jin Created
288 FUNCTION get_spq_itd_target (p_salesrep_id NUMBER,
289 p_srp_plan_assign_id NUMBER,
290 p_period_id NUMBER,
291 p_quota_id NUMBER ) RETURN NUMBER;
292
293 -- Procedure Name
294 -- get_pq_itd_payment
295 -- Scope
296 -- public
297 -- Purpose
298 --
299 -- History
300 -- 02-March 1999 Richard Jin Created
301 FUNCTION get_pq_itd_payment ( p_period_id NUMBER,
302 p_quota_id NUMBER ) RETURN NUMBER;
303
304 -- Procedure Name
305 -- get_spq_itd_payment
306 -- Scope
307 -- public
308 -- Purpose
309 --
310 -- History
311 -- 02-March 1999 Richard Jin Created
312 FUNCTION get_spq_itd_payment (p_salesrep_id NUMBER,
313 p_srp_plan_assign_id NUMBER,
314 p_period_id NUMBER,
315 p_quota_id NUMBER ) RETURN NUMBER;
316 -- fix for the Bug 6768210 raj reddy
317 FUNCTION EndOfGroupByInterval( p_quota_id NUMBER, p_period_id NUMBER,p_srp_plan_assign_id NUMBER )
318 RETURN BOOLEAN ;
319
320
321 END cn_formula_common_pkg;
[Home] [Help]