1 PACKAGE JA_CN_CFS_CALCULATE_PKG AUTHID CURRENT_USER AS
2 --$Header: JACNCCES.pls 120.0.12010000.2 2008/10/28 06:12:13 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNCCES.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package contains the following PL/SQL tables/procedures/functions
14 --| to implement calculation for main part of cash flow statement according
15 --| to data that collected by 'Cash Flow Statement - Data Collection'
16 --| program and stored in JA_CN_CFS_ACTIVITIES_ALL table, and rules defined
17 --| in Cash Flow Statement Assignments form and Calculation window in FSG
18 --| Row Set.
19 --|
20 --| TYPE LIEST
21 --| G_PERIOD_NAME_TBL
22 --|
23 --| PROCEDURE LIST
24 --| Populate_Period_Names
25 --| Populate_Formula
26 --| Categorize_Rows
27 --| Calculate_Row_Amount
28 --| Calculate_Rows_Amount
29 --| Generate_Cfs_Xml
30 --|
31 --| HISTORY
32 --| 14-Mar-2006 Donghai Wang Created
33 --| 29-Aug-2008 Chaoqun Wu CNAO Enhancement
34 --| Updated procedures Calculate_Row_Amount and Calculate_Rows_Amount
35 --| Added BSV parameter for CFS-Generation
36 --| 23-Sep-2008 Chaoqun Wu Fix bug# 7427067
37 --|
38 --+======================================================================*/
39
40 TYPE G_PERIOD_NAME_TBL IS TABLE OF gl_periods.period_name%TYPE
41 INDEX BY BINARY_INTEGER;
42
43 --==========================================================================
44 -- PROCEDURE NAME:
45 --
46 -- Populate_Period_Names Public
47 --
48 -- DESCRIPTION:
49 --
50 -- This procedure is to retrieve period names from gl_periods by the
51 -- parameter 'p_parameter' and the parameter p_balance_type, alternative
52 -- value is 'YTD/QTD/PTD'
53 --
54 -- PARAMETERS:
55 -- In: p_set_of_bks_id Identifier of GL set of book, a required
56 -- parameter for FSG report
57 -- p_period_name GL period Name
58 -- p_balace_type Type of balance, available value is 'YTD/QTD/PTD'.
59 -- a required parameter for FSG report
60 --
61 -- Out: x_period_names Qualified period names for cash flow statement
62 -- calculation
63 --
64 -- DESIGN REFERENCES:
65 -- CNAO_Cashflow_Statement_Generation_TD.doc
66 --
67 -- CHANGE HISTORY:
68 --
69 -- 14-Mar-2006 Donghai Wang Created
70 --
71 --===========================================================================
72
73 PROCEDURE Populate_Period_Names
74 (p_ledger_id IN NUMBER
75 ,p_period_name IN VARCHAR2
76 ,p_balance_type IN VARCHAR2
77 ,x_period_names OUT NOCOPY JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
78 );
79
80 --==========================================================================
81 -- PROCEDURE NAME:
82 --
83 -- Populate_Formula Public
84 --
85 -- DESCRIPTION:
86 --
87 -- For Cash Flow Statement, user would define calculation rule for items
88 -- on 'FSG Rowset' form, one item can be calculated by other items, it
89 -- can have multiple calculation lines. In one calculation line, user can
90 -- define a range for rows from low to high, or specify a specific row.
91 -- Also, rows selected in such calculation rules can be also items that
92 -- are calculated by other items. Just so, it is hard for calculating
93 -- directly. The procedure 'Populate_Formula' is used to convert involved
94 -- calculating items in calculation lines to most detailed items for all
95 -- calculated items, hereinto, most detailed items mean items that are
96 -- directly calculated by FSG account assignments or Cash Flow item assignments.
97 --
98 -- PARAMETERS:
99 -- In: p_axis_set_id Identifier of FSG Row Set
100 --
101 -- DESIGN REFERENCES:
102 -- CNAO_Cashflow_Statement_Generation_TD.doc
103 --
104 -- CHANGE HISTORY:
105 --
106 -- 14-Mar-2006 Donghai Wang Created
107 -- 23-Sep-2008 Chaoqun Wu Fix bug# 7427067
108 --
109 --===========================================================================
110 PROCEDURE Populate_Formula
111 (p_coa IN NUMBER --Fix bug# 7427067
112 ,p_axis_set_id IN NUMBER
113 );
114
115 --==========================================================================
116 -- PROCEDURE NAME:
117 --
118 -- Categorize_Rows Public
119 --
120 -- DESCRIPTION:
121 --
122 -- The 'Categorize_Rows' procedure is to categorize rows in FSG rowsets that
123 -- are defined for Cash Flow statement with the following three types:
124 -- 1. Rows belong to subsidiary part of Cash Flow Statement
125 -- 2. Rows belong to main part of Cash Flow Statement
126 -- 3. Rows that have calculation on FSG rowset form, but those rows
127 -- involved in calculation respectively belong to above the type one
128 -- and the type two.
129 --
130 -- PARAMETERS:
131 -- In: p_legal_entity_id Identifier of legal entity
132 -- p_axis_set_id Identifier of FSG Row Set
133 --
134 -- DESIGN REFERENCES:
135 -- CNAO_Cashflow_Statement_Generation_TD.doc
136 --
137 -- CHANGE HISTORY:
138 --
139 -- 14-Mar-2006 Donghai Wang Created
140 --
141 --===========================================================================
142 PROCEDURE Categorize_Rows
143 (p_coa IN NUMBER
144 ,p_axis_set_id IN NUMBER
145 );
146
147 --==========================================================================
148 -- PROCEDURE NAME:
149 --
150 -- Calculate_Row_Amount Public
151 --
152 -- DESCRIPTION:
153 --
154 -- The procedure 'Calculate_Row_Amount' is used to calculate amount for a
155 -- specific cash flow item in the main part of cash flow statement according
156 -- to assignment in the table 'JA_CN_CFS_ASSIGNMENTS_ALL' and amount of detailed
157 -- cash flow item in the table 'JA_CN_CFS_ACTIVITIES_ALL'.
158 --
159 -- PARAMETERS:
160 -- In: p_legal_entity_id Identifier of legal entity
161 -- p_set_of_bks_id Identifier of GL set of book, a required
162 -- parameter for FSG report
163 -- p_axis_set_id Identifier of FSG Row Set
164 -- p_axis_seq Sequence number of FSG row
165 --
166 -- p_balace_type Type of balance, available value is 'YTD/QTD/PTD'.
167 -- a required parameter for FSG report
168 -- p_period_names Qualified period names for cash flow statement
169 -- calculation
170 -- p_rounding_option Rounding option for amount in Cash Flow statement
171 -- p_internal_trx_flag To indicate if intercompany transactions
172 -- should be involved in amount calculation
173 -- of cash flow statement.
174 --
175 --
176 -- Out: x_amount Amount of cash flow item
177 --
178 -- DESIGN REFERENCES:
179 -- CNAO_Cashflow_Statement_Generation_TD.doc
180 --
181 -- CHANGE HISTORY:
182 --
183 -- 14-Mar-2006 Donghai Wang Created
184 --
185 --===========================================================================
186 PROCEDURE Calculate_Row_Amount
187 (p_legal_entity_id IN NUMBER
188 ,p_ledger_id IN NUMBER
189 ,p_coa IN NUMBER-- ADDED BY LYB
190 ,p_axis_set_id IN NUMBER
191 ,p_axis_seq IN NUMBER
192 ,p_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
193 ,p_rounding_option IN VARCHAR2
194 ,p_balancing_segment_value IN VARCHAR2 --Added for CNAO Enhancement
195 --,p_internal_trx_flag IN VARCHAR2
196 ,x_amount OUT NOCOPY NUMBER
197 );
198
199 --==========================================================================
200 -- PROCEDURE NAME:
201 --
202 -- Calculate_Rows_Amount Public
203 --
204 -- DESCRIPTION:
205 --
206 -- The procedure Calculate_Rows_Amount is used to calculate amount for items
207 -- in the main part of Cash Flow Statement.
208 --
209 -- PARAMETERS:
210 -- In: p_legal_entity_id Identifier of legal entity
211 -- p_set_of_bks_id Identifier of GL set of book, a required
212 -- parameter for FSG report
213 -- p_axis_set_id Identifier of FSG Row Set
214 -- p_period_names Qualified period names for cash flow statement
215 -- calculation
216 -- p_lastyear_period_names Qualified period names in last year for cash
217 -- flow statement calculation
218 -- p_rounding_option Rounding option for amount in Cash Flow statement
219 -- p_internal_trx_flag To indicate if intercompany transactions
220 -- should be involved in amount calculation
221 -- of cash flow statement.
222 --
223 -- DESIGN REFERENCES:
224 -- CNAO_Cashflow_Statement_Generation_TD.doc
225 --
226 -- CHANGE HISTORY:
227 --
228 -- 14-Mar-2006 Donghai Wang Created
229 --
230 --===========================================================================
231 PROCEDURE Calculate_Rows_Amount
232 (p_legal_entity_id IN NUMBER
233 ,p_ledger_id IN NUMBER
234 ,p_coa IN NUMBER
235 ,p_axis_set_id IN NUMBER
236 ,p_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
237 ,p_lastyear_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
238 ,p_rounding_option IN VARCHAR2
239 ,p_segment_override IN VARCHAR2 --added for CNAO Enhancement
240 --,p_internal_trx_flag IN VARCHAR2
241 );
242
243 --==========================================================================
244 -- PROCEDURE NAME:
245 --
246 -- Generate_Cfs_Xml Public
247 --
248 -- DESCRIPTION:
249 --
250 -- The procedure Generate_Cfs_Xml is to generate xml output for main part of
251 -- cash flow statement by following format of FSG xml output.
252 --
253 -- PARAMETERS:
254 -- In: p_legal_entity_id Identifier of legal entity
255 -- p_set_of_bks_id Identifier of GL set of book, a required
256 -- parameter for FSG report
257 -- p_period_name GL period Name
258 -- p_axis_set_id Identifier of FSG Row Set
259 -- p_rounding_option Rounding option for amount in Cash Flow statement
260 -- p_balance_type Type of balance, available value is
261 -- 'YTD/QTD/PTD'. a required parameter for FSG
262 -- report
263 -- p_internal_trx_flag To indicate if intercompany transactions
264 -- should be involved in amount calculation
265 -- of cash flow statement.
266 --
267 -- DESIGN REFERENCES:
268 -- CNAO_Cashflow_Statement_Generation_TD.doc
269 --
270 -- CHANGE HISTORY:
271 --
272 -- 14-Mar-2006 Donghai Wang Created
273 --
274 --===========================================================================
275 PROCEDURE Generate_Cfs_Xml
276 (p_legal_entity_id IN NUMBER
277 ,p_ledger_id IN NUMBER
278 ,p_period_name IN VARCHAR2
279 ,p_axis_set_id IN NUMBER
280 ,p_rounding_option IN VARCHAR2
281 ,p_balance_type IN VARCHAR2
282 --,p_internal_trx_flag IN VARCHAR2
283 ,p_coa IN NUMBER
284 ,p_segment_override IN VARCHAR2 --added for CNAO Enhancement
285 );
286
287 END JA_CN_CFS_CALCULATE_PKG;