1 PACKAGE FUN_BAL_PKG AS
2 /* $Header: funbalpkgs.pls 120.8 2006/09/22 14:43:28 bsilveir noship $ */
3 /*
4 Please read the following before calling this API. This API is used for balancing unbalanced
5 journals. The current release support both General Ledger and SubLedger Accounting.
6 The only public call is journal_balancing procedure.
7
8 Notes:
9 1. The balancing type column in FUN_BAL_RESULT_GT table is used for distinguish between
10 whether a line is being generated using the intercompany balancing rules or intracompany
11 balancing rules. A line is generated using Intercompany balancing rules with type 'E',
12 and it is generated using Intracompany balancing rules with type 'R''. After calling the
13 balancing API, the calling program should retrieve the generated lines and labelled these
14 lines in the correct context when inserting these lines back to the base tables.
15
16 2. The global temporary tables used for balancing are transaction specific, so that this API can
17 be called multiple times within one single session as long as commit/rollback call occurs before
18 the next time this API is called. However, please be aware that balancing data would be lost
19 during commit/rollback, and hence must be retrieived before such calls.
20
21 Assumptions of the Balancing API when being called:
22 1. Each journal itself MUST be balanced by both the entered amounts and accounted amounts
23 at the header level
24 2. Other then the normal required columns, the headers inserted into FUN_BAL_HEADERS_GT table
25 must have a status of 'OK' in order for the balancing API to process the header.
26 3. The lines inserted into FUN_BAL_LINES_GT table must have the generated column set to 'N'.
27 4. The calling program would have to display the errors found in FUN_BAL_ERRORS_GT to the user
28 using their own FND_MESSAGES, as the message details and the context to be displayed to the
29 user are different for different calling programs. The calling probrams can use the
30 FUN_BAL_HEADERS_GT table to figure out which journals contain an error. A journal ended
31 in error would have its status set to 'ERROR'.
32 5. The balancing API is currently using FND logging framework to log any other error, warning
33 or debug messages into FND log. Please note that the standard API parameter x_msg_data
34 from the journal_balancing procedure call does not return any values since it is duplicating
35 with the FND logging.
36 6. If a journal belongs to an ALC ledger, callers of the Balancing API MUST provide the Balancing
37 API with the primary ledger instead of the ALC ledger, and Balancing API would use the
38 primary ledger information to balance the journal. If the ALC ledger is passed in accidentally,
39 unexpected exceptions could happen. Setup data would be derived from the primary ledger.
40
41
42 Possible error codes to be found in fun_bal_errors_gt table are:
43 -------------------------------------------------------------------------
44 Generic
45 =====
46 1. FUN_BSV_INVALID
47 Values populated: error_code, group_id, bal_seg_val
48 Description: BSV given in the journal is not assigned to a ledger nor to any LEs.
49
50 Intercompany
51 =========
52 Note: To_le_id would return NULL for many-to-many intercompany mode
53 1. FUN_INTER_BSV_NOT_ASSIGNED
54 Values populated: error_code, group_id, bal_seg_val
55 2. FUN_INTER_REC_NOT_ASSIGNED
56 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type
57 2. FUN_INTER_REC_NO_DEFAULT
58 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type
59 4. FUN_INTER_REC_NOT_VALID
60 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type, ccid_concat_disp
61 5. FUN_INTER_PAY_NOT_ASSIGNED
62 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type
63 2. FUN_INTER_PAY_NO_DEFAULT
64 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type
65 6. FUN_INTER_PAY_NOT_VALID
66 Values populated: error_code, group_id, from_le_id, to_le_id, ccid, acct_type, ccid_concat_disp
67
68 Intracompany
69 =========
70 1. FUN_INTRA_RULE_NOT_ASSIGNED
71 Values populated: error_code, group_id, template_id, le_id
72 2. FUN_INTRA_NO_CLEARING_BSV
73 Values populated: error_code, group_id, template_id, le_id
74 3. FUN_INTRA_CC_NOT_VALID
75 FUN_INTRA_CC_NOT_CREATED
76 FUN_INTRA_CC_NOT_ACTIVE'
77 Values populated: error_code, group_id, template_id, le_id, dr_bsv, cr_bsv, acct_type,
78 ccid_concat_display
79 If dr_bsv and cr_bsv are the same, it essentially means either default rule or
80 clearing bsv (clearing bsv happens to be the same as the bsv of the line to be balanced)
81 is being used
82 4. FUN_INTRA_OVERRIDE_BSV_ERROR
83 Values poplulated: group_id, clearing_bsv
84 For JPMC, the API follows these rules:
85 If a journal crosses LEs and an Override Clearing Company is provided,
86 the API will fail and that particular journal will not post.
87 If all journal lines are within one LE and an Override Clearing Company
88 that is outside the LE is entered, the API will fail and that particular journal
89 will not post.
90 If all journal lines are within one LE and an Override Clearing Company that
91 is within the same LE is entered, the API will balance the journal using the
92 appropriate Balancing Rules.
93 If all journal lines have BSVs that are not attached to an LE and an Override
94 Clearing Company that is not attached to an LE is entered, the API will
95 balance the journal using the appropriate Balancing Rules.
96
97
98 Temporary Tables for debugging purposes
99 ============================
100 FUN_BAL_LOG_T -- Obsoleted
101 FUN_BAL_HEADERS_T
102 FUN_BAL_LINES_T
103 FUN_BAL_RESULTS_T
104 FUN_BAL_ERRORS_T
105 FUN_BAL_INTER_BSV_MAP_T
106 FUN_BAL_INTRA_BSV_MAP_T
107 FUN_BAL_INTER_LINES_T
108 FUN_BAL_INTRA_LINES_T
109
110 If the debug flag is FND_API.TRUE, the balancing API would commit so that debug data can be
111 saved into temporary tables for debugging purposes.
112
113 Notes:
114 The balancing API does not deal with M-M algorithm due to currency balancing issues
115 For 1-Many and Many-1, all information related to currency would use the detail ones,
116 not the driving balancing segment. These are the only situations where the currency
117 information does not inherit directly from the parent line.
118
119
120 */
121
122 TYPE headers_tab_type IS TABLE OF fun_bal_headers_gt%rowtype;
123 TYPE lines_tab_type IS TABLE OF fun_bal_lines_gt%rowtype;
124 TYPE results_tab_type IS TABLE OF fun_bal_results_gt%rowtype;
125 TYPE errors_tab_type IS TABLE OF fun_bal_errors_gt%rowtype;
126 TYPE inter_le_bsv_map_tab_type IS TABLE OF fun_bal_inter_bsv_map_t%rowtype;
127 TYPE intra_le_bsv_map_tab_type IS TABLE OF fun_bal_intra_bsv_map_t%rowtype;
128 --TYPE le_bsv_map_tab_type IS TABLE OF fun_bal_le_bsv_map_gt%rowtype;
129 TYPE inter_int_tab_type IS TABLE OF fun_bal_inter_int_gt%rowtype;
130 TYPE intra_int_tab_type IS TABLE OF fun_bal_intra_int_gt%rowtype;
131
132 PROCEDURE journal_balancing
133 ( p_api_version IN NUMBER,
134 p_init_msg_list IN VARCHAR2 default null ,
135 p_validation_level IN NUMBER default null ,
136 p_debug IN VARCHAR2 default null ,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 p_product_code IN VARCHAR2 -- Valid values are GL and SLA for this release
141 );
142
143 FUNCTION get_ccid
144 ( ccid IN NUMBER,
145 chart_of_accounts_id IN NUMBER,
146 bal_seg_val IN VARCHAR2,
147 intercompany_seg_val IN VARCHAR2,
148 bal_seg_column_number IN NUMBER,
149 intercompany_column_number IN NUMBER,
150 gl_date IN DATE) RETURN NUMBER;
151
152
153 FUNCTION get_ccid_concat_disp
154 ( ccid IN NUMBER,
155 chart_of_accounts_id IN NUMBER,
156 bal_seg_val IN VARCHAR2,
157 intercompany_seg_val IN VARCHAR2,
158 bal_seg_column_number IN NUMBER,
159 intercompany_column_number IN NUMBER) RETURN VARCHAR2;
160
161 FUNCTION get_segment_index (p_chart_of_accounts_id IN NUMBER,
162 p_segment_type VARCHAR2)
163 RETURN NUMBER;
164
165 /*
166 PROCEDURE debug
167 ( p_message IN VARCHAR2
168 );
169 PROCEDURE update_inter_seg_val;
170 PROCEDURE truncate_tables;
171 */
172 /* Not implemented
173 FUNCTION do_curr_bal RETURN VARCHAR2;
174 FUNCTION do_inter_bal_m_to_m RETURN VARCHAR2;
175 FUNCTION do_intra_bal_m_to_m RETURN VARCHAR2;
176 FUNCTION do_curr_bal_m_to_m RETURN VARCHAR2;
177 */
178
179 /* Obsoleted
180 FUNCTION get_inter_seg_val
181 ( bal_seg_col_name IN VARCHAR2, ccid IN NUMBER) RETURN VARCHAR2;
182 */
183
184 /* Another possible method to perform inserting and commit. This method is currently
185 not preferred because bulk loading can not be utilized. There would be 32 times processing
186 overhead in performing the loop shown below. It might be possible to use the cursor as
187 a table and perform the insertion, but it is not clear what kind of performance we would
188 get out of it.
189 TYPE headers_tab_type IS REF CURSOR RETURN fun_bal_headers_gt%rowtype;
190 TYPE lines_tab_type IS REF CURSOR RETURN fun_bal_lines_gt%rowtype;
191 TYPE results_tab_type IS REF CURSOR RETURN fun_bal_results_gt%rowtype;
192 TYPE errors_tab_type IS REF CURSOR RETURN fun_bal_errors_gt%rowtype;
193 TYPE le_bsv_map_tab_type IS REF CURSOR RETURN fun_bal_le_bsv_map_gt%rowtype;
194 TYPE inter_int_tab_type IS REF CURSOR RETURN fun_bal_inter_int_gt%rowtype;
195 TYPE intra_int_tab_type IS REF CURSOR RETURN fun_bal_intra_int_gt%rowtype;
196 PROCEDURE auto_test1(test_csr IN test_csr_type1) IS
197 test_csr_rec fun_bal_headers_gt%rowtype;
198 PRAGMA AUTONOMOUS_TRANSACTION;
199 BEGIN
200 LOOP
201 FETCH test_csr into test_csr_rec;
202 EXIT WHEN test_csr%NOTFOUND;
203 INSERT INTO fun_bal_headers_gt values test_csr_rec;
204 END LOOP;
205 --INSERT INTO fun_bal_headers_gt SELECT * FROM test_csr;
206 COMMIT;
207 RETURN;
208 END auto_test1;
209
210 */
211
212 /*
213 Reference:
214 GLISTBKB.pls -- File for get_ccid function
215 GLUGST.lpc -- File for getting product schema name and gathering statistics
216 Try selecting the ccid first before creating ccid
217 */
218 /* Additional performance consideratioins
219 1. Create index, statistics
220 2. Problem here: Dynamic SQL for validating ccid
221 3. Problem here: Bind variables/decode for getting templates, accounts, etc.
222 */
223 /* Additional API considerations
224 1. Check whether clearing BSV is valid or not.
225
226 */
227
228 END fun_bal_pkg;
[Home] [Help]