[Home] [Help]
PACKAGE BODY: APPS.GCS_ICM_INTG_PROF_PKG
Source
1 PACKAGE BODY GCS_ICM_INTG_PROF_PKG AS
2 /* $Header: gcsicmpb.pls 120.0 2005/09/30 23:13:05 mikeward noship $ */
3
4 new_line VARCHAR2(4) := '
5 ';
6 g_api VARCHAR2(80) := 'gcs.plsql.GCS_ICM_INTG_PROF_PKG';
7
8
9 --
10 -- Private Procedures
11 --
12
13 PROCEDURE set_single_option_value
14 (p_option_name VARCHAR2,
15 p_option_value VARCHAR2,
16 p_force_update VARCHAR2)
17 IS
18 l_profile_option_id NUMBER;
19 l_application_id NUMBER;
20 BEGIN
21 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
22 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
23 g_api || '.set_single_option_value.begin',
24 '<<Enter>>');
25 END IF;
26
27 SELECT application_id,
28 profile_option_id
29 INTO l_application_id,
30 l_profile_option_id
31 FROM fnd_profile_options
32 WHERE profile_option_name = p_option_name;
33
34 MERGE INTO fnd_profile_option_values pov
35 USING (SELECT 1 FROM DUAL) src
36 ON (pov.application_id = l_application_id AND
37 pov.profile_option_id = l_profile_option_id AND
38 pov.level_id = 10001)
39 WHEN MATCHED THEN
40 UPDATE SET profile_option_value = decode(profile_option_value,
41 NULL, p_option_value,
42 decode(p_force_update,
43 'Y', p_option_value,
44 profile_option_value)),
45 last_update_date = sysdate,
46 last_updated_by = fnd_global.user_id,
47 last_update_login = fnd_global.login_id
48 WHEN NOT MATCHED THEN
49 INSERT(
50 application_id,
51 profile_option_id,
52 level_id,
53 level_value,
54 creation_date,
55 created_by,
56 last_update_date,
57 last_updated_by,
58 last_update_login,
59 profile_option_value)
60 VALUES(
61 l_application_id,
62 l_profile_option_id,
63 10001,
64 0,
65 sysdate,
66 fnd_global.user_id,
67 sysdate,
68 fnd_global.user_id,
69 fnd_global.login_id,
70 p_option_value);
71
72 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
73 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
74 g_api || '.set_single_option_value.end',
75 '<<Exit>>');
76 END IF;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
81 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
82 g_api || '.set_single_option_value.error',
83 SQLERRM);
84 END IF;
85
86 RAISE;
87 END set_single_option_value;
88
89
90 --
91 -- Public Procedures
92 --
93
94 PROCEDURE set_profile_option_values
95 (x_errbuf OUT NOCOPY VARCHAR2,
96 x_retcode OUT NOCOPY VARCHAR2)
97 IS
98 l_vs_id VARCHAR2(100);
99 BEGIN
100 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
101 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
102 g_api || '.set_profile_option_values.begin',
103 '<<Enter>>');
104 END IF;
105
106 SELECT to_char(flex_value_set_id)
107 INTO l_vs_id
108 FROM fnd_flex_value_sets
109 WHERE flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
110
111 set_single_option_value('AMW_FIN_IMPORT_FROM_FSG', 'N', 'Y');
112 set_single_option_value('AMW_SUBSIDIARY_AUDIT_UNIT', l_vs_id, 'N');
113
114 set_single_option_value('AMW_STMNT_SOURCE_VIEW', 'GCS_FIN_STMTS_V', 'N');
115 set_single_option_value('AMW_STMNT_SOURCE_TL_VIEW', 'GCS_FIN_STMT_DTLS_V', 'N');
116 set_single_option_value('AMW_FINITEM_SOURCE_VIEW', 'GCS_FINANCIAL_ITEMS_HIER_V', 'N');
117 set_single_option_value('AMW_FINITEM_SOURCE_TL_VIEW', 'GCS_FINANCIAL_ITEMS_V', 'N');
118 set_single_option_value('AMW_FIN_ITEM_ACC_RELATIONS_VIEW', 'GCS_ITEMS_TO_NAT_ACCTS_V', 'N');
119 set_single_option_value('AMW_ACCOUNT_SOURCE_VIEW', 'GCS_NAT_ACCTS_HIER_V', 'N');
120 set_single_option_value('AMW_ACCOUNT_NAMES_VIEW', 'GCS_NAT_ACCTS_V', 'N');
121
122 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
123 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
124 g_api || '.set_profile_option_values.end',
125 '<<Exit>>');
126 END IF;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
131 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
132 g_api || '.set_profile_option_values.error',
133 SQLERRM);
134 END IF;
135
136 x_errbuf := SQLERRM;
137 x_retcode := '2';
138 END set_profile_option_values;
139
140
141 PROCEDURE launch_key_account_import
142 IS
143 l_request_id NUMBER;
144
145 CURSOR amw_value_set_c IS
146 SELECT flex_value_set_name
147 FROM fnd_flex_value_sets ffv
148 WHERE ffv.flex_value_set_id = to_number(fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT'));
149
150 l_vs_name VARCHAR2(100);
151 BEGIN
152 OPEN amw_value_set_c;
153 FETCH amw_value_set_c INTO l_vs_name;
154 CLOSE amw_value_set_c;
155
156 IF l_vs_name = 'FCH_ICM_ENTITY_VALUE_SET' THEN
157 l_request_id := fnd_request.submit_request
158 (application => 'AMW',
159 program => 'AMWACCTIMP',
160 sub_request => FALSE);
161 END IF;
162 END launch_key_account_import;
163
164
165 PROCEDURE launch_fin_stmt_import
166 IS
167 l_request_id NUMBER;
168
169 l_run_id NUMBER;
170 BEGIN
171 EXECUTE IMMEDIATE
172 'SELECT amw_fin_stmnt_selection_s.nextval ' ||
173 'FROM DUAL'
174 INTO l_run_id;
175
176 EXECUTE IMMEDIATE
177 'INSERT INTO amw_fin_stmnt_selection ' ||
178 '(run_id, ' ||
179 ' financial_statement_id, ' ||
180 ' creation_date, ' ||
181 ' created_by, ' ||
182 ' last_update_date, ' ||
183 ' last_updated_by, ' ||
184 ' last_update_login, ' ||
185 ' security_group_id, ' ||
186 ' object_version_number) ' ||
187 'SELECT :1, ' ||
188 ' hierarchy_id, ' ||
189 ' sysdate, ' ||
190 ' fnd_global.user_id, ' ||
191 ' sysdate, ' ||
192 ' fnd_global.user_id, ' ||
193 ' fnd_global.login_id, ' ||
194 ' null, ' ||
195 ' 1 ' ||
196 'FROM gcs_hierarchies_b ' ||
197 'WHERE certification_flag = ''Y'''
198 USING l_run_id;
199
200 commit;
201
202 l_request_id := fnd_request.submit_request
203 (application => 'AMW',
204 program => 'AMWFSTMTIMP',
205 sub_request => FALSE,
206 argument1 => to_char(l_run_id));
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 null;
211 END launch_fin_stmt_import;
212
213 END GCS_ICM_INTG_PROF_PKG;