[Home] [Help]
PACKAGE BODY: APPS.GCS_DIMENSION_SET_LINES_PKG
Source
1 PACKAGE BODY GCS_DIMENSION_SET_LINES_PKG AS
2 /* $Header: gcsdmslb.pls 120.1 2005/10/30 05:17:31 appldev noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api VARCHAR2(40) := 'gcs.plsql.GCS_DIMENSION_SET_LINES_PKG';
8
9 --
10 -- PUBLIC FUNCTIONS
11 --
12
13 PROCEDURE Assign_Dimension_Combinations(
14 p_errbuf OUT NOCOPY VARCHAR2,
15 p_retcode OUT NOCOPY VARCHAR2,
16 p_dimension_set_id NUMBER)
17 IS
18 fn_name VARCHAR2(30) := 'ASSIGN_DIMENSION_COMBINATIONS';
19
20 l_dimension_set_type VARCHAR2(30);
21 l_col_name VARCHAR2(30);
22 l_num_of_dimensions NUMBER;
23
24 stmt VARCHAR2(5000);
25 insert_clause VARCHAR2(1500);
26 select_clause VARCHAR2(600);
27 from_clause VARCHAR2(700);
28 where_clause VARCHAR2(2000);
29
30 CURSOR src_tgt_dims IS
31 SELECT column_name
32 FROM gcs_dimension_set_dims
33 WHERE dimension_set_id = p_dimension_set_id
34 ORDER BY column_name;
35 BEGIN
36 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
37 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
38 g_api || '.' || fn_name,
39 GCS_UTILITY_PKG.g_module_enter || fn_name ||
40 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
41 END IF;
42 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
43 fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
44
45 -- In case of an error, roll back to this point
46 SAVEPOINT gcs_dms_line_assign_start;
47
48 -- Initialization
49 g_fnd_user_id := fnd_global.user_id;
50 g_fnd_login_id := fnd_global.login_id;
51
52 SELECT set_type_code
53 INTO l_dimension_set_type
54 FROM GCS_DIMENSION_SETS_B
55 WHERE dimension_set_id = p_dimension_set_id;
56
57 l_num_of_dimensions := 0;
58 insert_clause := '';
59 select_clause := '';
60 from_clause := '';
61 where_clause := '';
62
63 -- build the statement parts
64 IF (l_dimension_set_type = 'TARGET_ONLY') THEN
65 GCS_UTILITY_PKG.init_dimension_info;
66 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.FIRST;
67 ELSE
68 OPEN src_tgt_dims;
69 FETCH src_tgt_dims INTO l_col_name;
70 END IF;
71
72 LOOP
73 EXIT WHEN (l_col_name IS NULL);
74
75 -- if getting from g_gcs_dimension_info, skip these dimensions
76 IF (l_col_name IN ('COMPANY_COST_CENTER_ORG_ID',
77 'ENTITY_ID',
78 'INTERCOMPANY_ID')) THEN
79 GOTO next_dim;
80 END IF;
81
82 l_num_of_dimensions := l_num_of_dimensions + 1;
83
84 insert_clause := insert_clause || '
85 SRC_' || l_col_name || ',
86 TGT_' || l_col_name || ',';
87
88 select_clause := select_clause || '
89 grp' || l_num_of_dimensions || '.source_member_id,
90 grp' || l_num_of_dimensions || '.target_member_id,';
91
92 from_clause := from_clause || ',
93 GCS_DIMENSION_SET_GRPS grp' || l_num_of_dimensions;
94
95 where_clause := where_clause || '
96 AND grp' || l_num_of_dimensions || '.dimension_set_id = ds.dimension_set_id
97 AND grp' || l_num_of_dimensions || '.column_name = ''' ||
98 l_col_name || '''';
99
100 <<next_dim>>
101 IF (l_dimension_set_type = 'TARGET_ONLY') THEN
102 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.NEXT(l_col_name);
103 ELSE
104 FETCH src_tgt_dims INTO l_col_name;
105 EXIT WHEN (src_tgt_dims%NOTFOUND);
106 END IF;
107 END LOOP;
108
109 IF (l_dimension_set_type = 'SOURCE_TARGET') THEN
110 CLOSE src_tgt_dims;
111 END IF;
112
113 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
114 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
115 g_api || '.' || fn_name,
116 'Dimension Set Id ' || to_char(p_dimension_set_id) || ': '
117 || to_char(l_num_of_dimensions) || ' dimension(s)');
118 END IF;
119
120 -- Delete existing assignments
121 DELETE FROM GCS_DIMENSION_SET_LINES
122 WHERE dimension_set_id = p_dimension_set_id;
123
124 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
126 g_api || '.' || fn_name,
127 'Deleted ' || to_char(SQL%ROWCOUNT) || ' row(s)');
128 END IF;
129
130 IF (l_num_of_dimensions <= 0) THEN
131 -- no dimensions assigned to the dimension set, exit directly
132 GOTO prog_exit;
133 END IF;
134
135 -- build the insert statement
136 stmt :=
137 'INSERT INTO GCS_DIMENSION_SET_LINES
138 (dimension_set_id,' ||
139 insert_clause || '
140 creation_date, created_by,
141 last_update_date, last_updated_by,
142 last_update_login)
143 SELECT
144 :dim_set_id,' ||
145 select_clause || '
146 sysdate, :user_id,
147 sysdate, :user_id,
148 :login_id
149 FROM
150 GCS_DIMENSION_SETS_B ds' ||
151 from_clause || '
152 WHERE ds.dimension_set_id = :dim_set_id' ||
153 where_clause;
154
155 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
156 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
157 g_api || '.' || fn_name,
158 stmt);
159 END IF;
160
161 -- Insert new assignments
162 EXECUTE IMMEDIATE stmt USING p_dimension_set_id,
163 g_fnd_user_id,
164 g_fnd_user_id,
165 g_fnd_login_id,
166 p_dimension_set_id;
167
168 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
169 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
170 g_api || '.' || fn_name,
171 'Inserted ' || to_char(SQL%ROWCOUNT) || ' row(s)');
172 END IF;
173
174 <<prog_exit>>
175 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
177 g_api || '.' || fn_name,
178 GCS_UTILITY_PKG.g_module_success || fn_name ||
179 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
180 END IF;
181 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
182 fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
187 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
188 g_api || '.' || fn_name,
189 SUBSTR(SQLERRM, 1, 4000));
190 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
191 g_api || '.' || fn_name,
192 GCS_UTILITY_PKG.g_module_failure || fn_name ||
193 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
194 END IF;
195 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
196 fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
197
198 ROLLBACK TO gcs_dms_line_assign_start;
199 p_errbuf := 'GCS_DMSL_UNHANDLED_EXCEPTION';
200 p_retcode := '2';
201 END Assign_Dimension_Combinations;
202
203 END GCS_DIMENSION_SET_LINES_PKG;