[Home] [Help]
PACKAGE BODY: APPS.FV_SF133_LOAD
Source
1 PACKAGE BODY fv_sf133_load AS
5
2 --$Header: FVXBEGLB.pls 120.7 2003/12/17 21:21:33 ksriniva ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) := 'fv.plsql.fv_sf133_load.';
6 --
7 --
8 g_set_of_books_id NUMBER;
9 g_load_accounts VARCHAR2(01);
10 --
11 -- ---------- End of Package Level Declaritives -----------------------------
12 -- --------------------------------------------------------------------------
13 PROCEDURE a000_load_tables
14 (errbuf OUT NOCOPY VARCHAR2,
15 retcode OUT NOCOPY NUMBER,
16 set_of_books_id IN NUMBER,
17 load_accounts IN VARCHAR2)
18 --
19 IS
20 l_module_name VARCHAR2(200) := g_module_name || 'a000_load_tables';
21 --
22 -- ------------------------------------
23 -- Work Variables
24 -- ------------------------------------
25 v_boolean BOOLEAN;
26 v_flex_field_nbr NUMBER;
27 v_segment_number NUMBER;
28 v_segment_app_name VARCHAR2(40);
29 v_segment_prompt VARCHAR2(25);
30 v_segment_value_set_name VARCHAR2(40);
31 --
32 v_account_segment_name VARCHAR2(25);
33 --
34 invalid_segment_returned EXCEPTION;
35 --
36 BEGIN
37 -- ------------------------------------
38 retcode := 0;
39 g_set_of_books_id := set_of_books_id;
40 g_load_accounts := UPPER(load_accounts);
41 --
42 -- ------------------------------------
43 -- Clear and Load SF133 Line Definitions
44 -- ------------------------------------
45 DELETE
46 FROM fv_sf133_definitions_lines
47 WHERE set_of_books_id = g_set_of_books_id;
48 --
49
50 INSERT
51 INTO fv_sf133_definitions_lines
52 (sf133_line_id,
53 set_of_books_id,
54 sf133_line_number,
55 sf133_line_label,
56 sf133_line_type_code,
57 sf133_goals_line_number,
58 sf133_report_line_number,
59 sf133_natural_balance_type,
60 sf133_fund_category,
61 created_by,
62 creation_date,
63 last_updated_by,
64 last_update_date,
65 last_update_login)
66 SELECT fv_sf133_line_id_s.NEXTVAL,
67 g_set_of_books_id,
68 load.sf133_line_number,
69 load.sf133_line_label,
70 load.sf133_line_type_code,
71 load.sf133_goals_line_number,
72 load.sf133_report_line_number,
73 load.sf133_natural_balance_type,
74 load.sf133_fund_category,
75 0,
76 SYSDATE,
77 0,
78 SYSDATE,
79 0
80 FROM fv_sf133_load_lines load;
81 --
82 -- ------------------------------------
83 -- Clear and Load SF133 Line Accounts
84 -- ------------------------------------
85 DELETE
86 FROM fv_sf133_definitions_accts
87 WHERE (sf133_line_id)
88 IN
89 (SELECT set_of_books_id
90 FROM fv_sf133_definitions_lines
91 WHERE g_set_of_books_id = set_of_books_id);
92 --
93 IF g_load_accounts = 'Y' THEN
94 -- ------------------------------------
95 -- Determine Account Segment Name
96 -- ------------------------------------
97 SELECT chart_of_accounts_id
98 INTO v_flex_field_nbr
99 FROM gl_sets_of_books
100 WHERE set_of_books_id = g_set_of_books_id;
101 --
102 v_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(101,
103 'GL#',
104 v_flex_field_nbr,
105 'GL_ACCOUNT',
106 v_account_segment_name);
107 IF (v_boolean) THEN
108 -- ------------------------------------
109 -- Load Account Codes for Set of Books
110 -- ------------------------------------
111 v_account_segment_name := UPPER(v_account_segment_name);
112 --
113 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
114 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- ');
115 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- SET OF BOOKS ID ('||G_SET_OF_BOOKS_ID||')'
116 ||' Account Segment ('||v_account_segment_name||')');
117 END IF;
118 --
119 INSERT
120 INTO fv_sf133_definitions_accts
121 (sf133_line_acct_id,
122 sf133_line_id,
123 sf133_balance_type,
124 sf133_additional_info,
125 created_by,
126 creation_date,
127 last_updated_by,
128 last_update_date,
129 last_update_login,
130 segment1,
131 segment2,
132 segment3,
133 segment4,
134 segment5,
135 segment6,
136 segment7,
137 segment8,
138 segment9,
139 segment10,
140 segment11,
141 segment12,
142 segment13,
143 segment14,
144 segment15,
145 segment16,
146 segment17,
147 segment18,
148 segment19,
149 segment20,
150 segment21,
151 segment22,
152 segment23,
153 segment24,
154 segment25,
155 segment26,
156 segment27,
157 segment28,
158 segment29,
162 load.sf133_balance_type,
159 segment30)
160 SELECT fv_sf133_line_acct_id_s.NEXTVAL,
161 line.sf133_line_id,
163 load.sf133_additional_info,
164 0,
165 SYSDATE,
166 0,
167 SYSDATE,
168 0,
169 decode(v_account_segment_name,
170 'SEGMENT1',load.sf133_line_account, NULL),
171 decode(v_account_segment_name,
172 'SEGMENT2',load.sf133_line_account, NULL),
173 decode(v_account_segment_name,
174 'SEGMENT3',load.sf133_line_account, NULL),
175 decode(v_account_segment_name,
176 'SEGMENT4',load.sf133_line_account, NULL),
177 decode(v_account_segment_name,
178 'SEGMENT5',load.sf133_line_account, NULL),
179 decode(v_account_segment_name,
183 decode(v_account_segment_name,
180 'SEGMENT6',load.sf133_line_account, NULL),
181 decode(v_account_segment_name,
182 'SEGMENT7',load.sf133_line_account, NULL),
184 'SEGMENT8',load.sf133_line_account, NULL),
185 decode(v_account_segment_name,
186 'SEGMENT9',load.sf133_line_account, NULL),
187 decode(v_account_segment_name,
188 'SEGMENT10',load.sf133_line_account, NULL),
189 decode(v_account_segment_name,
190 'SEGMENT11',load.sf133_line_account, NULL),
191 decode(v_account_segment_name,
192 'SEGMENT12',load.sf133_line_account, NULL),
193 decode(v_account_segment_name,
194 'SEGMENT13',load.sf133_line_account, NULL),
195 decode(v_account_segment_name,
196 'SEGMENT14',load.sf133_line_account, NULL),
197 decode(v_account_segment_name,
198 'SEGMENT15',load.sf133_line_account, NULL),
199 decode(v_account_segment_name,
200 'SEGMENT16',load.sf133_line_account, NULL),
201 decode(v_account_segment_name,
202 'SEGMENT17',load.sf133_line_account, NULL),
203 decode(v_account_segment_name,
204 'SEGMENT18',load.sf133_line_account, NULL),
205 decode(v_account_segment_name,
206 'SEGMENT19',load.sf133_line_account, NULL),
207 decode(v_account_segment_name,
208 'SEGMENT20',load.sf133_line_account, NULL),
209 decode(v_account_segment_name,
210 'SEGMENT21',load.sf133_line_account, NULL),
211 decode(v_account_segment_name,
212 'SEGMENT22',load.sf133_line_account, NULL),
213 decode(v_account_segment_name,
214 'SEGMENT23',load.sf133_line_account, NULL),
215 decode(v_account_segment_name,
216 'SEGMENT24',load.sf133_line_account, NULL),
217 decode(v_account_segment_name,
218 'SEGMENT25',load.sf133_line_account, NULL),
219 decode(v_account_segment_name,
220 'SEGMENT26',load.sf133_line_account, NULL),
221 decode(v_account_segment_name,
222 'SEGMENT27',load.sf133_line_account, NULL),
223 decode(v_account_segment_name,
224 'SEGMENT28',load.sf133_line_account, NULL),
225 decode(v_account_segment_name,
226 'SEGMENT29',load.sf133_line_account, NULL),
227 decode(v_account_segment_name,
228 'SEGMENT30',load.sf133_line_account, NULL)
229 FROM fv_sf133_definitions_lines line,
230 fv_sf133_load_accts load
231 WHERE line.sf133_line_number = load.sf133_line_number
232 AND line.set_of_books_id = g_set_of_books_id;
233 END IF;
234 --
235 END IF;
236 --
237 -- ------------------------------------
241 --
238 -- Exception Processing
239 -- ------------------------------------
240 EXCEPTION
242 WHEN OTHERS THEN
243 errbuf := sqlerrm;
244 retcode := 2;
245 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
246
247 --
248 END a000_load_tables;
249 --
250 END fv_sf133_load;
251 -- ==========================================================================