[Home] [Help]
1 PACKAGE xla_control_accounts_pkg AS
2 /* $Header: xlabacta.pkh 120.5 2003/07/09 08:32:11 aquaglia ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_control_accounts_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Control Accounts Package |
13 | |
14 | HISTORY |
15 | 27-AUG-02 A.Quaglia Created |
16 | 10-DEC-02 A. Quaglia Overloaded update_balance_flag with |
17 | p_event_id,p_entity_id,p_application_id |
18 | 12-DEC-02 A. Quaglia update_balance_flag: added parameter |
19 | p_application_id where missing, added |
20 | |
21 +======================================================================*/
22 --
23 -- Public constants
24 --
25 C_CONTROL_BALANCE_FLAG_PENDING CONSTANT VARCHAR2(1) := 'P';
26
27 C_IS_CONTROL_ACCOUNT CONSTANT INTEGER := 0;
28 C_NOT_CONTROL_ACCOUNT CONSTANT INTEGER := 1;
29 C_IS_CONTROL_ACCOUNT_OTHER_APP CONSTANT INTEGER := 2;
30 C_ERR_CONTROL_ACCOUNT CONSTANT INTEGER := 3;
31
32 FUNCTION is_control_account
33 ( p_code_combination_id IN INTEGER
34 ,p_natural_account IN VARCHAR2
35 ,p_ledger_id IN INTEGER
36 ,p_application_id IN INTEGER
37 ) RETURN INTEGER;
38 /*======================================================================+
39 | |
40 | Public Function |
41 | |
42 | |
43 | Description |
44 | ----------- |
45 | |
46 | |
47 | p_code_combination_id can be NULL. |
48 | If p_natural_account is NULL it cannot be |
49 | NULL. |
50 | If p_natural_account is NOT NULL it must be |
51 | NULL. |
52 | |
53 | p_natural_account can be NULL |
54 | If p_code_combination_id is NULL it cannot be |
55 | NULL. |
56 | If p_code_combination_id is NOT NULL it must |
57 | be NULL. |
58 | |
59 | p_ledger_id can be NULL |
60 | If p_natural_account is NOT NULL it cannot be |
61 | NULL. |
62 | If p_code_combination_id is NOT NULL it must |
63 | be NULL. |
64 | |
65 | p_application_id can be NULL. |
66 | |
67 | |
68 | If p_code_combination_id IS NOT NULL it checks whether the code |
69 | combination is flagged as control account. |
70 | |
71 | If p_natural_account is not null it checks whether the account |
72 | is flagged as control account, in the chart of accounts attached |
73 | to the ledger p_ledger_id. |
74 | |
75 | If p_application_id is not null it checks whether the GL JE Source |
76 | Name attached to it in the table XLA_SUBLEDGERS is the one |
77 | associated to code combination or to the account. |
78 | |
79 | |
80 | RETURNS: |
81 | |
82 | C_IS_CONTROL_ACCOUNT |
83 | if the code combination or the natural account is a control |
84 | account. If p_application_id is NOT NULL, it must also be that |
85 | the value of the field JE_SOURCE_NAME in the table XLA_SUBLEDGERS |
86 | for that application_id must match the one for which the control |
87 | account is setup. |
88 | |
89 | C_NOT_CONTROL_ACCOUNT |
90 | if the code combination or the natural account are not flagged as |
91 | control account. |
92 | |
93 | C_IS_CONTROL_ACCOUNT_OTHER_APP |
94 | if the code combination or the natural account are flagged as |
95 | control account but the value of |
96 | the field JE_SOURCE_NAME in the table XLA_SUBLEDGERS for that |
97 | application_id does not match the one for which the control account|
98 | or the code combination is setup. | |
99 | |
100 | C_ERR_CONTROL_ACCOUNT |
101 | in case of inconsistency in the input parameters |
102 | |
103 | |
104 | The fastest response is given for the following pattern of |
105 | parameters (the one the Accounting Program uses): |
106 | p_code_combination_id NOT NULL |
107 | p_application_id NOT NULL |
108 | And within this scenario the most probable case is that |
109 | the code combination is not a control account. |
110 | |
111 | |
112 | Pseudo-code |
113 | ----------- |
114 | |
115 | IF p_code_combination_id IS NOT NULL |
116 | AND p_natural_account IS NULL THEN |
117 | retrieve REFERENCE3 from GL_CODE_COMBINATIONS for the ccid |
118 | and store it in l_qualifier_value |
119 | IF NVL(l_qualifier_value, 'N') = 'N' THEN |
120 | RETURN 1 |
121 | END IF |
122 | --From here on we are sure the ccid is a control one |
123 | IF p_application_id IS NOT NULL THEN |
124 | retrieve JE_SOURCE_NAME into l_je_source from XLA_SUBLEDGERS |
125 | IF l_qualifier_value = l_je_source THEN |
126 | RETURN 0 |
127 | ELSE |
128 | RETURN 2 |
129 | END IF |
130 | ELSE --p_application_id is NULL |
131 | RETURN 0 |
132 | END IF |
133 | |
134 | ELSIF p_natural_account IS NOT NULL THEN |
135 | IF p_ledger_id IS NULL |
136 | THEN |
137 | RETURN 3 |
138 | END IF |
139 | retrieve the chart_of_accounts_id of the ledger |
140 | retrieve the control account segment qualifier value for the |
141 | account and store it in l_qualifier_value |
142 | IF NVL(l_qualifier_value, 'N') IS NULL THEN |
143 | RETURN 1 |
144 | END IF |
145 | --From here on we are sure the account is a control one |
146 | IF p_application_id IS NOT NULL THEN |
147 | retrieve JE_SOURCE_NAME into l_je_source from XLA_SUBLEDGERS |
148 | IF l_qualifier_value = l_je_source THEN |
149 | RETURN 0 |
150 | ELSE |
151 | RETURN 2 |
152 | END IF |
153 | ELSE --p_application_id is NULL |
154 | RETURN 0 |
155 | END IF |
156 | |
157 | ELSE |
158 | RETURN 3 |
159 | |
160 | END IF; |
161 | |
162 | |
163 | |
164 | |
165 | Open issues |
166 | ----------- |
167 | |
168 | MUST SOLVE |
169 | |
170 | - Why is je_source_name in gl_je_source varchar2(25) |
171 | and je_source_name in xla_subledgers varchar2(30)? |
172 | |
173 | - GL must resize REFERENCE3 column in GL_CODE_COMBINATIONS |
174 | table from VARCHAR2(1) to VARCHAR2(25) |
175 | |
176 | - How will it be possible to store the JE Source Name in the |
177 | compiled_value_attributes column of fnd_flex_values table ? |
178 | |
179 | - Error treatment and return values. |
180 | |
181 | - Implementation for p_natural_account NOT NULL not completed |
182 | (waiting feedback from GL). |
183 | |
184 | NICE TO SOLVE |
185 | |
186 | - The Accounting Program could pass the JE_SOURCE_NAME instead of |
187 | the APPLICATION_ID. This would avoid useless accesses to the |
188 | XLA_SUBLEDGERS table for each code combination. |
189 | |
190 | - The Accounting Program already validates the code_combination_id |
191 | through a GL/FND API. But this means that GL_CODE_COMBINATIONS is |
195 | would be avoided. |
192 | already accessed for the same record. If the REFERENCE fields |
193 | could be read during that first access and passed over to this |
194 | function there would be an important performance gain and issue 2 |
196 | If this cannot be done, should we implement a cache for the |
197 | code combinations? |
198 | |
199 +======================================================================*/
200
201 FUNCTION update_balance_flag ( p_application_id IN INTEGER
202 ,p_ae_header_id IN INTEGER
203 ,p_ae_line_num IN INTEGER
204 )
205 RETURN BOOLEAN;
206
207 FUNCTION update_balance_flag ( p_event_id IN INTEGER
208 ,p_entity_id IN INTEGER
209 ,p_application_id IN INTEGER
210 )
211 RETURN BOOLEAN;
212
213
214
215
216 END xla_control_accounts_pkg;|
|
|
|