[Home] [Help]
PACKAGE BODY: APPS.GL_SUMMARY_ACCOUNT_TYPES_PKG
Source
1 PACKAGE BODY GL_SUMMARY_ACCOUNT_TYPES_PKG as
2 /* $Header: gluacsmb.pls 120.7 2006/08/09 22:01:33 cma ship $ */
3
4 ---
5 --- PUBLIC FUNCTIONS
6 ---
7
8 PROCEDURE update_account_types(coa_id NUMBER,
9 min_ccid_processed NUMBER) IS
10
11
12
13 acct_segcol VARCHAR2(30);
14 acct_vsetid NUMBER;
15 acct_tname VARCHAR2(240);
16 acct_vcolname VARCHAR2(240);
17 acct_attrib VARCHAR2(240);
18 acct_psegcol VARCHAR2(30);
19 acct_pvsetid NUMBER;
20
21 ccid_update VARCHAR2(3000);
22 ccid_cursor INTEGER;
23
24 rows_processed INTEGER;
25
26 position_qualifier INTEGER;
27
28 -- The below cursor evaluates the position of natural account qualifier
29 -- Bug Fix : 2814746
30 -- Bug Fix : 2950238, Modified the logic of the below cursor.
31
32 CURSOR pos_qualifier_curr(par_flex_value_set_id number) IS
33 SELECT rownum, value_attribute_type
34 FROM ( SELECT value_attribute_type
35 FROM fnd_flex_validation_qualifiers
36 WHERE id_flex_code = 'GL#'
37 AND id_flex_application_id = 101
38 AND flex_value_set_id = par_flex_value_set_id
39 ORDER by assignment_date, value_attribute_type ) ;
40
41
42 BEGIN
43
44 -- dbms_output.enable(10000);
45
46 SELECT seg.application_column_name, vs.flex_value_set_id,
47 vs.parent_flex_value_set_id,
48 vt.application_table_name, vt.value_column_name,
49 decode(vt.compiled_attribute_column_name,
50 'NULL', null,
51 vt.compiled_attribute_column_name)
52 INTO acct_segcol, acct_vsetid, acct_pvsetid,
53 acct_tname, acct_vcolname, acct_attrib
54 FROM fnd_flex_validation_tables vt,
55 fnd_flex_value_sets vs,
56 fnd_id_flex_segments seg,
57 fnd_segment_attribute_values qual
58 WHERE qual.application_id = 101
59 AND qual.id_flex_code = 'GL#'
60 AND qual.id_flex_num = coa_id
61 AND qual.segment_attribute_type = 'GL_ACCOUNT'
62 AND qual.attribute_value = 'Y'
63 AND seg.application_id = qual.application_id
64 AND seg.id_flex_code = qual.id_flex_code
65 AND seg.id_flex_num = qual.id_flex_num
66 AND seg.application_column_name = qual.application_column_name
67 AND vs.flex_value_set_id = seg.flex_value_set_id
68 AND vt.flex_value_set_id(+) = vs.flex_value_set_id;
69
70 IF (acct_pvsetid IS NOT NULL) THEN
71 SELECT application_column_name
72 INTO acct_psegcol
73 FROM fnd_id_flex_segments pseg
74 WHERE application_id = 101
75 AND id_flex_code = 'GL#'
76 AND id_flex_num = coa_id
77 AND flex_value_set_id+0 = acct_pvsetid
78 AND segment_num = (SELECT min(segment_num)
79 FROM fnd_id_flex_segments pseg2
80 WHERE application_id = 101
81 AND id_flex_code = 'GL#'
82 AND id_flex_num = coa_id
83 AND flex_value_set_id+0 = acct_pvsetid);
84 ELSE
85 acct_psegcol := NULL;
86 END IF;
87
88 -- fetching the value of natual account type qualifier position
89 -- into position_qualifier ( Bug Fix: 2814746)
90
91 FOR pos_qual_rec in pos_qualifier_curr(acct_vsetid) loop
92 if pos_qual_rec.value_attribute_type = 'GL_ACCOUNT_TYPE'
93 THEN
94 position_qualifier := pos_qual_rec.rownum;
95 exit ;
96 end if;
97 end loop;
98
99
100
101 ccid_update :=
102 'UPDATE gl_code_combinations cc ' ||
103 'SET account_type ';
104
105 -- Bug Fix: 2814746 Replaced the hard coded positions from substr stmt
106 -- compiled_value_attributes column.
107
108 IF (acct_tname IS NULL) THEN
109 ccid_update := ccid_update ||
110 '= (SELECT decode(vs.flex_value, ''T'', ''O'', ' ||
111 'substrb( fnd_global.newline||vs.compiled_value_attributes||fnd_global.newline,
112 instrb( fnd_global.newline||vs.compiled_value_attributes||fnd_global.newline,
113 fnd_global.newline,1,:1
114 )+1, 1
115 )) ' ||
116 'FROM fnd_flex_values vs ' ||
117 'WHERE vs.flex_value_set_id = ' || to_char(acct_vsetid) || ' ' ||
118 'AND vs.flex_value = cc.' || acct_segcol || ' ';
119
120 IF (acct_psegcol IS NOT NULL) THEN
121 ccid_update := ccid_update ||
122 'AND vs.parent_flex_value_low = cc.' || acct_psegcol || ' ),';
123 ELSE
124 ccid_update := ccid_update || '),';
125 END IF;
126
127 ELSE
128 ccid_update := ccid_update ||
129 '= (SELECT decode(cc2.' || acct_segcol || ', ''T'', ''O'', ' ||
130 'decode(vs.rowid, NULL, ';
131
132 IF (acct_attrib IS NULL) THEN
133 ccid_update := ccid_update || '''O'', ';
134 ELSIF (substr(acct_attrib, 1, 1) = '''') THEN
135 ccid_update := ccid_update ||
136 'substrb(fnd_global.newline||' || acct_attrib || '||fnd_global.newline,
137 instrb(fnd_global.newline||' ||acct_attrib || '||fnd_global.newline,
138 fnd_global.newline,1,:2
139 ) +1,
140 1), ';
141 ELSE
142 ccid_update := ccid_update ||
143 'substrb(fnd_global.newline||vt.' || acct_attrib || '||fnd_global.newline,
144 instrb(fnd_global.newline||vt.' || acct_attrib || '||fnd_global.newline,
145 fnd_global.newline,1,:3
146 ) +1,
147 1), ';
148 END IF;
149
150 -- Bug Fix: 2814746 Replaced the hard coded positions from substr stmt
151 -- compiled_value_attributes column.
152
153 ccid_update := ccid_update ||
154 'substrb( fnd_global.newline||vs.compiled_value_attributes||fnd_global.newline,
155 instrb( fnd_global.newline||vs.compiled_value_attributes||fnd_global.newline,
156 fnd_global.newline,1,:4
157 )+1, 1
158 ) )) ' ||
159 'FROM fnd_flex_values vs, gl_code_combinations cc2, ' ||
160 acct_tname || ' vt ' ||
161 'WHERE cc2.rowid = cc.rowid ' ||
162 'AND vs.flex_value_set_id(+)= :5 '||
163 'AND vs.flex_value(+) = cc2.' || acct_segcol || ' ' ||
164 'AND vs.summary_flag(+) = ''Y'' ' ||
165 'AND vt.'||acct_vcolname||'(+) = cc2.'||acct_segcol||'), ';
166 END IF;
167
168 ccid_update := ccid_update ||
169 'last_update_date = sysdate, ' ||
170 'last_updated_by = 1 ' ||
171 'WHERE chart_of_accounts_id = :6 ' ;
172
173 ccid_update := ccid_update ||
174 'AND code_combination_id >= :7 ' ||
175 'AND template_id IS NOT NULL ';
176
177 ccid_cursor := dbms_sql.open_cursor;
178 dbms_sql.parse(ccid_cursor, ccid_update, dbms_sql.v7);
179 IF (acct_tname IS NULL) THEN
180 dbms_sql.bind_variable(ccid_cursor, ':1', position_qualifier);
181 ELSE
182 IF(acct_attrib IS NOT NULL AND substr(acct_attrib, 1, 1) = '''') THEN
183 dbms_sql.bind_variable(ccid_cursor, ':2', position_qualifier);
184 ELSIF(acct_attrib IS NOT NULL) THEN
185 dbms_sql.bind_variable(ccid_cursor, ':3', position_qualifier);
186 END IF;
187 dbms_sql.bind_variable(ccid_cursor, ':4', position_qualifier);
188 dbms_sql.bind_variable(ccid_cursor, ':5', acct_vsetid);
189 END IF;
190 dbms_sql.bind_variable(ccid_cursor, ':6', coa_id);
191 dbms_sql.bind_variable(ccid_cursor, ':7', min_ccid_processed);
192 rows_processed := dbms_sql.execute(ccid_cursor);
193 dbms_sql.close_cursor(ccid_cursor);
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 dbms_sql.close_cursor(ccid_cursor);
198 IF (sqlcode = -1407) THEN
199 RAISE INVALID_COMBINATION;
200 ELSE
201 RAISE;
202 END IF;
203 END update_account_types;
204
205 END GL_SUMMARY_ACCOUNT_TYPES_PKG;