[Home] [Help]
PACKAGE BODY: APPS.GL_AFF_AWC_API_PKG
Source
1 PACKAGE BODY GL_AFF_AWC_API_PKG AS
2 /* $Header: gluafawb.pls 120.4 2005/11/09 23:32:08 spala noship $ */
3
4
5
6
7
8 FUNCTION gl_coa_awc_rule(p_subscription_guid IN RAW,
9 p_event IN OUT NOCOPY WF_EVENT_T)
10 RETURN VARCHAR2 IS
11 src_req_id VARCHAR2(15);
12 application_id VARCHAR2(15);
13 id_flex_code VARCHAR2(10);
14 id_flex_num VARCHAR2(15);
15 request_id NUMBER;
16 BEGIN
17 FND_PROFILE.get('CONC_REQUEST_ID', src_req_id);
18
19 -- only necessary when the event is raised directly from the form
20 IF (to_number(src_req_id) <= 0) THEN
21
22 application_id := p_event.GetValueForParameter('APPLICATION_ID');
23 id_flex_code := p_event.GetValueForParameter('ID_FLEX_CODE');
24 id_flex_num := p_event.GetValueForParameter('ID_FLEX_NUM');
25
26 IF (application_id = '101' AND id_flex_code = 'GL#') THEN
27
28 -- ################################################
29
30 -- ADD CODE TO HANDLE TAGS AND CLAUSES TO UPDATE
31 -- THE ADDITIONAL WHERE CLAUSE.......
32
33 -- ################################################
34 -- The delete call has been disbaled otherwise it is
35 -- more unnecessary work to flex compiler.
36 -- AOL team asked us to do this.
37 --GL_AFF_AWC_API_PKG.GL_bs_delete_awc(id_flex_num, 'GL_BALANCING');
38 GL_AFF_AWC_API_PKG.GL_bs_add_awc(id_flex_num, 'GL_BALANCING');
39
40 IF (request_id = 0) THEN
41 WF_CORE.CONTEXT('GL_COA_AWC_PKG', 'gl_coa_awc_rule',
42 p_event.getEventName, p_subscription_guid);
43 WF_EVENT.setErrorInfo(p_event, FND_MESSAGE.get);
44 return 'WARNING';
45 END IF;
46
47 END IF;
48
49 END IF;
50
51 RETURN 'SUCCESS';
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 WF_CORE.CONTEXT('GL_COA_AWC_PKG', 'gl_coa_awc_rule',
56 p_event.getEventName, p_subscription_guid);
57 WF_EVENT.setErrorInfo(p_event, 'ERROR');
58 return 'ERROR';
59 END gl_coa_awc_rule;
60
61
62
63 PROCEDURE gl_bs_add_awc (coa_id IN NUMBER,
64 segment_type IN VARCHAR2)
65 IS
66
67 l_flexfield fnd_flex_key_api.flexfield_type;
68 l_structure fnd_flex_key_api.structure_type;
69 l_segment fnd_flex_key_api.segment_type;
70 l_numof_awc_elements number;
71 l_awc_elements fnd_flex_key_api.awc_elements_type;
72 l_found boolean;
73 l_segment_name VARCHAR2(30);
74 l_flex_value_type VARCHAR2(1);
75 l_tab_val_vs NUMBER;
76 l_flex_value_col_name VARCHAR2(50);
77
78 BEGIN
79 fnd_flex_key_api.set_session_mode('seed_data');
80
81 l_flexfield := fnd_flex_key_api.find_flexfield('SQLGL', 'GL#');
82
83 l_structure := fnd_flex_key_api.find_structure(l_flexfield, coa_id);
84
85 SELECT t1.segment_name, ffvs.validation_type, t1.flex_value_Set_id
86 INTO l_segment_name, l_flex_value_type, l_tab_val_vs
87 FROM fnd_id_flex_segments t1,
88 fnd_segment_attribute_values t2,
89 fnd_flex_value_sets ffvs
90 WHERE t1.application_id = t2.application_id
91 AND t1.id_flex_code = t2.id_flex_code
92 AND t1.id_flex_num = t2.id_flex_num
93 AND t1.application_column_name = t2.application_column_name
94 AND t1.application_id = 101
95 AND t1.id_flex_code = 'GL#'
96 AND t1.id_flex_num = coa_id
97 AND t1.enabled_flag = 'Y'
98 AND t2.segment_attribute_type = NVL(segment_type, 'GL_BALANCING')
99 AND t2.attribute_value = 'Y'
100 AND ffvs.flex_value_set_id = t1.flex_value_set_id;
101
102 IF (l_flex_value_type = 'F') THEN
103
104 SELECT fvt.value_column_name
105 INTO l_flex_value_col_name
106 FROM FND_FLEX_VALIDATION_TABLES fvt
107 WHERE fvt.flex_value_set_id = l_tab_val_vs;
108
109 END IF;
110
111
112
113 l_segment :=
114
115 fnd_flex_key_api.find_segment(l_flexfield, l_structure,l_segment_name);
116
117 -- Get AWCs
118 --
119 fnd_flex_key_api.get_awc_elements(l_flexfield, l_structure, l_segment,
120 l_numof_awc_elements, l_awc_elements);
121 --
122 -- Add a new AWC
123 --
124 l_found := false;
125 FOR i in 1 .. l_numof_awc_elements LOOP
126 IF (l_awc_elements(i).tag = 'GL_COA_BS_TAG') THEN
127 l_found := TRUE;
128 EXIT;
129 END IF;
130 END LOOP;
131
132 IF (NOT l_found) THEN
133
134 IF (l_flex_value_type <> 'F') THEN
135
136 fnd_flex_key_api.add_awc(l_flexfield, l_structure, l_segment,
137 'GL_COA_BS_TAG',
138 'GL_AFF_AWC_API_PKG.'||
139 'gl_valid_flex_values(:$FLEX$.$VDATE$, '||
140 'FND_FLEX_VALUES_VL.Flex_Value) = ''Y''') ;
141
142
143 ELSE
144
145 fnd_flex_key_api.add_awc(l_flexfield, l_structure, l_segment,
146 'GL_COA_BS_TAG',
147 'GL_AFF_AWC_API_PKG.'||
148 'gl_valid_flex_values(:$FLEX$.$VDATE$,'
149 ||l_flex_value_col_name||') = ''Y''');
150
151 END IF;
152 END IF;
153
154 END gl_bs_add_awc;
155
156
157 FUNCTION gl_valid_flex_values (p_valid_date VARCHAR2,
158 p_flex_value VARCHAR2,
159 p_id1 NUMBER DEFAULT NULL,
160 p_char1 VARCHAR2 DEFAULT NULL,
161 p_id2 NUMBER DEFAULT NULL,
162 p_char2 VARCHAR2 DEFAULT NULL,
163 p_id3 NUMBER DEFAULT NULL,
164 p_char3 VARCHAR2 DEFAULT NULL)
165 RETURN VARCHAR2 IS
166
167 l_valid_value VARCHAR2(1) ;
168 l_lgr_id NUMBER ;
169 l_valid_date DATE :=NULL;
170
171 BEGIN
172
173 If ((p_valid_date IS NOT NULL) AND (LENGTH(p_valid_date) >= 19)) THEN
174 l_valid_date := TO_DATE(p_valid_date, 'YYYY/MM/DD HH24:MI:SS');
175 ELSE
176 l_valid_date := NULL;
177 END IF;
178
179 l_lgr_id := GL_GLOBAL.context_ledger_id;
180
181 /* If the GL_GLOBAl package is not being called, then
182 flex value validation should not be enforced and all BSV's are
183 valid.
184 */
185
186 IF (l_lgr_id IS NULL or l_lgr_id = 0) Then
187
188 Return 'Y';
189
190 END IF;
191
192 /* BSV's are not always assigned to ledgers. Therefore we should not
193 enforce BSV assignemnt if there is no BSV flex value set
194 is assigned to a ledger.
195 IF bal_seg_value_option_code column value in GL_LEDGER table is
196 'A' that means all BSV's are valid. If the column is 'I',
197 then some BSV's are valid.
198
199 To avoid multiple SQL checks GL_LEDGERS table is
200 joined to gl_ledger_segment_values table.
201 */
202
203 SELECT DECODE(NVL(gll.bal_seg_value_option_code, 'X'),
204 'I',
205 DECODE(glsv.segment_value, NULL, 'N',p_flex_value,'Y', 'N'),
206 'Y')
207 INTO l_valid_value
208 FROM gl_ledger_segment_values glsv, gl_ledgers gll
209 WHERE gll.ledger_id = l_lgr_id
210 AND gll.ledger_id = glsv.ledger_id (+)
211 AND glsv.segment_type_code (+) = 'B'
212 AND NVL(glsv.status_code (+), 'X') <> 'I'
213 AND NVL(glsv.start_date (+),TO_DATE('1950/01/01','YYYY/MM/DD'))
214 <= NVL(l_valid_date,TO_DATE('9999/12/31','YYYY/MM/DD'))
215 AND NVL(glsv.end_date (+),TO_DATE('9999/12/31','YYYY/MM/DD'))
216 >= NVL(l_valid_date, TO_DATE('1950/01/01','YYYY/MM/DD'))
217 AND glsv.segment_value (+) = p_flex_value;
218
219 RETURN(l_valid_value);
220
221 EXCEPTION
222
223 WHEN NO_DATA_FOUND THEN
224 RETURN('N');
225 END;
226
227 PROCEDURE gl_bs_delete_awc (coa_id IN NUMBER,
228 segment_type IN VARCHAR2)
229 IS
230
231 l_flexfield fnd_flex_key_api.flexfield_type;
232 l_structure fnd_flex_key_api.structure_type;
233 l_segment fnd_flex_key_api.segment_type;
234 l_numof_awc_elements number;
235 l_awc_elements fnd_flex_key_api.awc_elements_type;
236 l_found boolean;
237 l_segment_name VARCHAR2(30);
238
239 BEGIN
240 fnd_flex_key_api.set_session_mode('seed_data');
241
242 l_flexfield := fnd_flex_key_api.find_flexfield('SQLGL', 'GL#');
243
244 l_structure := fnd_flex_key_api.find_structure(l_flexfield, coa_id);
245
246 SELECT t1.segment_name
247 INTO l_segment_name
248 FROM fnd_id_flex_segments t1,
249 fnd_segment_attribute_values t2,
250 fnd_flex_value_sets ffvs
251 WHERE t1.application_id = t2.application_id
252 AND t1.id_flex_code = t2.id_flex_code
253 AND t1.id_flex_num = t2.id_flex_num
254 AND t1.application_column_name = t2.application_column_name
255 AND t1.application_id = 101
256 AND t1.id_flex_code = 'GL#'
257 AND t1.id_flex_num = coa_id
258 AND t1.enabled_flag = 'Y'
259 AND t2.segment_attribute_type = NVL(segment_type, 'GL_BALANCING')
260 AND t2.attribute_value = 'Y'
261 AND ffvs.flex_value_set_id = t1.flex_value_set_id;
262
263 l_segment :=
264 fnd_flex_key_api.find_segment(l_flexfield, l_structure,l_segment_name);
265
266 -- Get AWCs
267 --
268 fnd_flex_key_api.get_awc_elements(l_flexfield, l_structure, l_segment,
269 l_numof_awc_elements, l_awc_elements);
270
271 --
272 -- Delete an old AWC
273 --
274 l_found := false;
275 FOR i in 1 .. l_numof_awc_elements LOOP
276 IF (l_awc_elements(i).tag = 'GL_COA_BS_TAG') THEN
277 l_found := TRUE;
278 EXIT;
279 END IF;
280 END LOOP;
281
282 IF (l_found) THEN
283 fnd_flex_key_api.delete_awc(l_flexfield, l_structure, l_segment,
284 'GL_COA_BS_TAG');
285 END IF;
286
287 END gl_bs_delete_awc;
288
289 --**************************************************************
290
291 END GL_AFF_AWC_API_PKG;