1 PACKAGE CN_CALC_FORMULAS_PVT AUTHID CURRENT_USER AS
2 /*$Header: cnvforms.pls 120.4 2006/05/26 00:49:40 jxsingh ship $*/
3 TYPE input_rec_type IS RECORD
4 (formula_input_id CN_FORMULA_INPUTS.FORMULA_INPUT_ID%TYPE := NULL,
5 calc_sql_exp_id CN_FORMULA_INPUTS.CALC_SQL_EXP_ID%TYPE,
6 f_calc_sql_exp_id CN_FORMULA_INPUTS.F_CALC_SQL_EXP_ID%TYPE,
7 rate_dim_sequence CN_FORMULA_INPUTS.RATE_DIM_SEQUENCE%TYPE,
8 calc_exp_name CN_CALC_SQL_EXPS.NAME%TYPE,
9 calc_exp_status CN_CALC_SQL_EXPS.STATUS%TYPE,
10 f_calc_exp_name CN_CALC_SQL_EXPS.NAME%TYPE,
11 f_calc_exp_status CN_CALC_SQL_EXPS.STATUS%TYPE,
12 object_version_number CN_FORMULA_INPUTS.OBJECT_VERSION_NUMBER%TYPE,
13 cumulative_flag CN_FORMULA_INPUTS.CUMULATIVE_FLAG%TYPE,
14 split_flag CN_FORMULA_INPUTS.SPLIT_FLAG%TYPE);
15
16 TYPE rt_assign_rec_type IS RECORD
17 (rt_formula_asgn_id CN_RT_FORMULA_ASGNS.RT_FORMULA_ASGN_ID%TYPE := NULL,
18 rate_schedule_id CN_RT_FORMULA_ASGNS.RATE_SCHEDULE_ID%TYPE,
19 start_date CN_RT_FORMULA_ASGNS.START_DATE%TYPE,
20 end_date CN_RT_FORMULA_ASGNS.END_DATE%TYPE,
21 rate_schedule_name CN_RATE_SCHEDULES.NAME%TYPE,
22 rate_schedule_type CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
23 object_version_number CN_RT_FORMULA_ASGNS.OBJECT_VERSION_NUMBER%TYPE);
24
25 TYPE formula_rec_type IS RECORD
26 (calc_formula_id CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
27 name CN_CALC_FORMULAS.NAME%TYPE,
28 description CN_CALC_FORMULAS.DESCRIPTION%TYPE,
29 formula_type CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
30 formula_status CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
31 trx_group_code CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
32 number_dim CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
33 cumulative_flag CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
34 itd_flag CN_CALC_FORMULAS.ITD_FLAG%TYPE,
35 split_flag CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
36 threshold_all_tier_flag CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
37 modeling_flag CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
38 perf_measure_id CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
39 output_exp_id CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
40 f_output_exp_id CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE,
41 object_version_number CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE);
42
43 TYPE input_tbl_type IS TABLE OF input_rec_type
44 INDEX BY BINARY_INTEGER;
45 TYPE rt_assign_tbl_type IS TABLE OF rt_assign_rec_type
46 INDEX BY BINARY_INTEGER;
47 TYPE parent_expression_tbl_type IS TABLE OF VARCHAR2(30)
48 INDEX BY BINARY_INTEGER;
49 TYPE formula_tbl_type IS TABLE OF formula_rec_type
50 INDEX BY BINARY_INTEGER;
51
52 g_miss_input_tbl input_tbl_type;
53 g_miss_rt_assign_tbl rt_assign_tbl_type;
54
55 -- Notes : Create calculation formula and generate formula packages
56 -- 1) Validate formula name (should be unique)
57 -- 2) Validate the combination of flags (cumulative_flag,
58 -- itd_flag, etc.)
59 -- 3) Validate performance measure, inputs, and output assignment
60 -- 4) Validate rate table assignment (number of dimensions
61 -- should match number of inputs)
62 -- 5) If all validations are passed, generate formula packages
63 -- and return the result in x_formula_status
64 -- (Complete or Incomplete)
65 --
66 -- End of comments
67 PROCEDURE Create_Formula
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_generate_packages IN VARCHAR2 := FND_API.G_TRUE ,
73 p_name IN CN_CALC_FORMULAS.NAME%TYPE,
74 p_description IN CN_CALC_FORMULAS.DESCRIPTION%TYPE
75 := null,
76 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
77 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
78 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
79 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
80 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
81 p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
82 p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
83 p_modeling_flag IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
84 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
85 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
86 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE
87 := NULL,
88 p_input_tbl IN input_tbl_type := g_miss_input_tbl,
89 p_rt_assign_tbl IN rt_assign_tbl_type := g_miss_rt_assign_tbl,
90 --R12 MOAC Changes--Start
91 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --new
92 x_calc_formula_id IN OUT NOCOPY CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE, --changed
93 --R12 MOAC Changes--End
94 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
95 x_return_status OUT NOCOPY VARCHAR2,
96 x_msg_count OUT NOCOPY NUMBER,
97 x_msg_data OUT NOCOPY VARCHAR2);
98
99 -- Notes : Update calculation formula and generate formula packages
100 -- 1) Validate formula name (should be unique)
101 -- 2) Validate the combination of flags (cumulative_flag,
102 -- itd_flag, etc.)
103 -- 3) Validate performance measure, inputs, and output assignment
104 -- 4) Validate rate table assignment (number of dimensions
105 -- should match number of inputs)
106 -- 5) If all validations are passed, generate formula packages
107 -- and return the result in x_formula_status
108 -- (Complete or Incomplete)
109 --
110 -- End of comments
111 PROCEDURE Update_Formula
112 (p_api_version IN NUMBER ,
113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
114 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
116 p_generate_packages IN VARCHAR2 := FND_API.G_TRUE ,
117 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
118 p_name IN CN_CALC_FORMULAS.NAME%TYPE,
119 p_description IN CN_CALC_FORMULAS.DESCRIPTION%TYPE
120 := null,
121 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
122 p_formula_status IN CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
123 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
124 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
125 p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
126 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
127 p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
128 p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
129 p_modeling_flag IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
130 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
131 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
132 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE
133 := NULL,
134 p_input_tbl IN input_tbl_type := g_miss_input_tbl,
135 p_rt_assign_tbl IN rt_assign_tbl_type := g_miss_rt_assign_tbl,
136 --R12 MOAC Changes--Start
137 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --new
138 p_object_version_number IN OUT NOCOPY CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --Changed
139 --R12 MOAC Changes--End
140 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2);
144
145 -- Notes : Delete a formula
146 -- 1) if it is used, it can not be deleted
147 --
148 -- End of comments
149 PROCEDURE Delete_Formula
150 (p_api_version IN NUMBER ,
151 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
152 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
153 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
154 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
155 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --SFP related change
156 --R12 MOAC Changes--Start
157 p_object_version_number IN CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --new
158 --R12 MOAC Changes--End
159 x_return_status OUT NOCOPY VARCHAR2 ,
160 x_msg_count OUT NOCOPY NUMBER ,
161 x_msg_data OUT NOCOPY VARCHAR2 );
162
163
164
165 -- Notes : Generate the PL/SQL packages for the given formula
166 --
167 -- End of comments
168 PROCEDURE generate_formula
169 (p_api_version IN NUMBER ,
170 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
171 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
172 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
173 p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
174 p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE
175 := fnd_api.g_miss_char ,
176 p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE
177 := fnd_api.g_miss_char ,
178 p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE
179 := fnd_api.g_miss_num ,
180 p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE
181 := fnd_api.g_miss_char ,
182 p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE
183 := fnd_api.g_miss_num ,
184 p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE
185 := fnd_api.g_miss_num ,
186 p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE
187 := fnd_api.g_miss_num ,
188 x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
189 --R12 MOAC Changes--Start
190 p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE,
191 --R12 MOAC Changes--End
192 x_return_status OUT NOCOPY VARCHAR2 ,
193 x_msg_count OUT NOCOPY NUMBER ,
194 x_msg_data OUT NOCOPY VARCHAR2 );
195
196
197
198 END CN_CALC_FORMULAS_PVT;
[Home] [Help]