1 PACKAGE BODY BOM_UDA_OVERRIDES_PVT AS
2 /* $Header: BOMVATOB.pls 120.0.12010000.4 2009/08/14 22:02:25 trudave noship $ */
3
4
5 PROCEDURE Copy_Comp_UDA_Overrides (p_old_comp_seq NUMBER, p_new_comp_seq NUMBER) IS
6
7 x_return_status VARCHAR2(240);
8 udab_new_base_rec BOM_COMPONENTS_EXT_B%ROWTYPE;
9 udat_rec BOM_COMPONENTS_EXT_TL%ROWTYPE;
10 ctx_rec BOM_COMP_ATTR_CTX_ASSOCS_B%ROWTYPE;
11 new_ctx_rec BOM_COMP_ATTR_CTX_ASSOCS_B%ROWTYPE;
12 l_new_ovr_b_ext_rec BOM_COMPONENTS_EXT_B%ROWTYPE;
13 l_new_ovr_tl_ext_rec BOM_COMPONENTS_EXT_TL%ROWTYPE;
14 TYPE l_ext_b_tbl IS TABLE OF BOM_COMPONENTS_EXT_B%ROWTYPE INDEX BY BINARY_INTEGER;
15 TYPE l_ext_tl_tbl IS TABLE OF BOM_COMPONENTS_EXT_TL%ROWTYPE INDEX BY BINARY_INTEGER;
16 TYPE l_ct_tbl IS TABLE OF BOM_COMP_ATTR_CTX_ASSOCS_B%ROWTYPE INDEX BY BINARY_INTEGER;
17 l_new_ovr_b_ext_tbl l_ext_b_tbl;
18 l_new_ovr_tl_ext_tbl l_ext_tl_tbl;
19 l_new_ctx_tbl l_ct_tbl;
20 l_ag_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
21
22 l_new_ctx_id NUMBER;
23 l_old_ovr_ext_id NUMBER;
24 l_new_ext_id NUMBER;
25 l_data_level_id_comp NUMBER;
26 l_data_level_id_comp_ovr NUMBER;
27 l_data_level_name_comp VARCHAR2(30) := 'COMPONENTS_LEVEL';
28 l_data_level_name_comp_ovr VARCHAR2(30) := 'COMPONENTS_OVR_LEVEL';
29 l_user_id NUMBER := FND_GLOBAL.user_id;
30 l_login_id NUMBER := FND_GLOBAL.login_id;
31 l_extension_id NUMBER;
32 l_dynamic_sql VARCHAR2(32767);
33 l_dynamic_sql_uk VARCHAR2(32767);
34 l_uk_attrs_count NUMBER := 0;
35 l_uk_attrs_counter NUMBER := 0;
36 l_table_index NUMBER := 0;
37 l_db_col_name VARCHAR2(30);
38 l_extension_id_multi NUMBER;
39
40 CURSOR C_DATA_LEVEL(p_data_level_name VARCHAR2) IS
41 SELECT DATA_LEVEL_ID
42 FROM EGO_DATA_LEVEL_B
43 WHERE DATA_LEVEL_NAME = p_data_level_name;
44
45 CURSOR C_FROM_CTX_ROWS (p_comp_seq NUMBER) IS
46 SELECT *
47 FROM BOM_COMP_ATTR_CTX_ASSOCS_B
48 WHERE COMPONENT_SEQUENCE_ID = p_comp_seq;
49
50 CURSOR C_NEW_BASE_ROW (p_bill_seq_id NUMBER, p_comp_seq_id NUMBER, p_attr_grp_id NUMBER) IS
51 SELECT *
52 FROM BOM_COMPONENTS_EXT_B
53 WHERE BILL_SEQUENCE_ID = p_bill_seq_id
54 AND COMPONENT_SEQUENCE_ID = p_comp_seq_id
55 AND ATTR_GROUP_ID = p_attr_grp_id
56 AND DATA_LEVEL_ID = l_data_level_id_comp
57 AND CONTEXT_ID IS NULL;
58
59 CURSOR C_NEW_BASE_ROW_MULTI (p_extension_id NUMBER) IS
60 SELECT *
61 FROM BOM_COMPONENTS_EXT_B
62 WHERE EXTENSION_ID = p_extension_id;
63
64 CURSOR C_OVR_B_ROWS (p_extension_id NUMBER) IS
65 SELECT *
66 FROM BOM_COMPONENTS_EXT_B
67 WHERE EXTENSION_ID = p_extension_id
68 AND DATA_LEVEL_ID = l_data_level_id_comp_ovr
69 AND CONTEXT_ID IS NOT NULL;
70
71 CURSOR C_OVR_TL_ROWS (p_extension_id NUMBER) IS
72 SELECT *
73 FROM BOM_COMPONENTS_EXT_TL
74 WHERE EXTENSION_ID = p_extension_id
75 AND DATA_LEVEL_ID = l_data_level_id_comp_ovr
76 AND CONTEXT_ID IS NOT NULL;
77
78 BEGIN
79
80 --Step 1: Get all rows for this component from context table
81 --Step 2: For each row in ctx, find row in ext table with BillSeq+AG from Cursor and CompSeq=NewCompSeq
82 --Step 2.1: If AG is single row, then only one base row found
83 --Step 2.2: If AG is multi row, then need to find the row with correct unique key
84 --Step 3: Create a new Ctx Id from Sequence
85 --Step 4: Copy override row from OvrExt(step1) of cursor, to a create a new row OvrExt' with new Ctx Id from step 3
86 --Step 5: Copy row in cursor, and set baseExt=NewCompBaseExtId (step 2) and ovrExt = OvrExt' (Step 4)
87
88 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
89
90 FOR c_comp_level IN C_DATA_LEVEL(l_data_level_name_comp) LOOP
91 l_data_level_id_comp := c_comp_level.DATA_LEVEL_ID;
92 END LOOP;
93
94 FOR c_comp_level IN C_DATA_LEVEL(l_data_level_name_comp_ovr) LOOP
95 l_data_level_id_comp_ovr := c_comp_level.DATA_LEVEL_ID;
96 END LOOP;
97
98 ctx_rec := null;
99 FOR c_ctx_rec IN C_FROM_CTX_ROWS(p_old_comp_seq) LOOP
100 ctx_rec := null;
101 ctx_rec := c_ctx_rec ;
102
103 --get the AG type
104 l_ag_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(p_attr_group_id=>c_ctx_rec.ATTR_GROUP_ID);
105
106 --If ag is single row, then only one row is found, which is desired row
107 IF(l_ag_metadata_obj.MULTI_ROW_CODE = 'N') THEN
108 FOR c_base_row IN C_NEW_BASE_ROW(ctx_rec.BILL_SEQUENCE_ID, p_new_comp_seq, ctx_rec.ATTR_GROUP_ID) LOOP --should be only one row
109 udab_new_base_rec := c_base_row; --new base row
110 END LOOP;
111 END IF;
112
113
114 --If ag is multi row, then multiple rows may be present for ag, we need to filter with correct primary key
115 IF(l_ag_metadata_obj.MULTI_ROW_CODE = 'Y') THEN
116 --generate the common sql
117 l_dynamic_sql := ' SELECT NEW.EXTENSION_ID ' ||
118 ' FROM BOM_COMPONENTS_EXT_B NEW, BOM_COMPONENTS_EXT_B OLD ' ||
119 ' WHERE OLD.EXTENSION_ID = :1 ' ||
120 ' AND NEW.BILL_SEQUENCE_ID = OLD.BILL_SEQUENCE_ID ' ||
121 ' AND NEW.ATTR_GROUP_ID = OLD.ATTR_GROUP_ID ' ||
122 ' AND NEW.COMPONENT_SEQUENCE_ID = :2 ' ||
123 ' AND NEW.DATA_LEVEL_ID = :3 ' ||
124 ' AND NEW.CONTEXT_ID IS NULL ' ;
125
126 --now generate uk specific clause
127 l_uk_attrs_count := l_ag_metadata_obj.UNIQUE_KEY_ATTRS_COUNT;
128 l_table_index := l_ag_metadata_obj.attr_metadata_table.FIRST;
129 l_uk_attrs_counter := 0;
130 WHILE (l_table_index <= l_ag_metadata_obj.attr_metadata_table.LAST)
131 LOOP
132 EXIT WHEN (l_uk_attrs_counter = l_uk_attrs_count);
133 -------------------------------------------------
134 -- If we find a UK Attr, add it to our query --
135 -------------------------------------------------
136 IF (l_ag_metadata_obj.attr_metadata_table(l_table_index).UNIQUE_KEY_FLAG = 'Y') THEN
137 l_db_col_name := l_ag_metadata_obj.attr_metadata_table(l_table_index).DATABASE_COLUMN;
138 l_dynamic_sql := l_dynamic_sql || ' AND NEW.' || l_db_col_name || ' = OLD.'|| l_db_col_name;
139 l_uk_attrs_counter := l_uk_attrs_counter + 1;
140 END IF;
141 l_table_index := l_ag_metadata_obj.attr_metadata_table.NEXT(l_table_index);
142 END LOOP;
143
144 EXECUTE IMMEDIATE l_dynamic_sql
145 INTO l_extension_id_multi
146 USING ctx_rec.BASE_EXTENSION_ID, p_new_comp_seq, l_data_level_id_comp;
147
148 --now we have the extension id for new base row, populate pl/sql record with this row
149 FOR c_base_row IN C_NEW_BASE_ROW_MULTI(l_extension_id_multi) LOOP
150 udab_new_base_rec := c_base_row; --new base row
151 END LOOP;
152
153 END IF;
154
155
156 -- FOR c_base_row IN C_NEW_BASE_ROW(ctx_rec.BILL_SEQUENCE_ID, p_new_comp_seq, ctx_rec.ATTR_GROUP_ID) LOOP --should be only one row
157 -- udab_new_base_rec := c_base_row; --new base row
158
159 -- Fix for Bug: 8784328
160 -- commented following declaration and replaced it with following SELECT statement
161 -- l_new_ctx_id := BOM_COMP_ATTR_CTX_ASSOCS_B_S.NEXTVAL;
162
163 SELECT BOM_COMP_ATTR_CTX_ASSOCS_B_S.NEXTVAL
164 INTO l_new_ctx_id
165 FROM dual;
166
167 FOR c_ovr_b_row IN C_OVR_B_ROWS(ctx_rec.EXTENSION_ID) LOOP --should be only one row
168
169 -- Fix for Bug: 8784328
170 -- commented following declaration and replaced it with following SELECT statement
171
172 -- l_new_ext_id := EGO_EXTFWK_S.NEXTVAL;
173
174 SELECT EGO_EXTFWK_S.NEXTVAL
175 INTO l_new_ext_id
176 FROM dual;
177
178 l_new_ovr_b_ext_rec := c_ovr_b_row;
179 l_new_ovr_b_ext_rec.EXTENSION_ID := l_new_ext_id;
180 l_new_ovr_b_ext_rec.COMPONENT_SEQUENCE_ID := p_new_comp_seq;
181 l_new_ovr_b_ext_rec.CONTEXT_ID := l_new_ctx_id;
182 l_new_ovr_b_ext_rec.DATA_LEVEL_ID := l_data_level_id_comp_ovr;
183 l_new_ovr_b_ext_rec.CREATION_DATE := sysdate;
184 l_new_ovr_b_ext_rec.CREATED_BY := l_user_id;
185 l_new_ovr_b_ext_rec.LAST_UPDATE_DATE := sysdate;
186 l_new_ovr_b_ext_rec.LAST_UPDATED_BY := l_user_id;
187 l_new_ovr_b_ext_rec.LAST_UPDATE_LOGIN := l_login_id;
188
189 l_new_ovr_b_ext_tbl(1) := l_new_ovr_b_ext_rec;
190
191 FORALL i in l_new_ovr_b_ext_tbl.FIRST..l_new_ovr_b_ext_tbl.LAST
192 INSERT
193 INTO BOM_COMPONENTS_EXT_B
194 VALUES l_new_ovr_b_ext_tbl(i);
195
196 FOR c_ovr_tl_row IN C_OVR_TL_ROWS(ctx_rec.EXTENSION_ID) LOOP
197 l_new_ovr_tl_ext_rec := c_ovr_tl_row;
198 l_new_ovr_tl_ext_rec.EXTENSION_ID := l_new_ext_id;
199 l_new_ovr_tl_ext_rec.COMPONENT_SEQUENCE_ID := p_new_comp_seq;
200 l_new_ovr_tl_ext_rec.CONTEXT_ID := l_new_ctx_id;
201 l_new_ovr_tl_ext_rec.DATA_LEVEL_ID := l_data_level_id_comp_ovr;
202 l_new_ovr_tl_ext_rec.CREATION_DATE := sysdate;
203 l_new_ovr_tl_ext_rec.CREATED_BY := l_user_id;
204 l_new_ovr_tl_ext_rec.LAST_UPDATE_DATE := sysdate;
205 l_new_ovr_tl_ext_rec.LAST_UPDATED_BY := l_user_id;
206 l_new_ovr_tl_ext_rec.LAST_UPDATE_LOGIN := l_login_id;
207
208 l_new_ovr_tl_ext_tbl(1) := l_new_ovr_tl_ext_rec;
209
210 FORALL i in l_new_ovr_tl_ext_tbl.FIRST..l_new_ovr_tl_ext_tbl.LAST
211 INSERT
212 INTO BOM_COMPONENTS_EXT_TL
213 VALUES l_new_ovr_tl_ext_tbl(i);
214
215 END LOOP; --FOR c_ovr_tl_row IN C_OVR_TL_ROWS(ctx_rec.EXTENSION_ID) LOOP
216
217 END LOOP; --FOR c_ovr_b_row IN C_OVR_B_ROWS(ctx_rec.EXTENSION_ID) LOOP
218
219 --now insert the context row
220 new_ctx_rec := ctx_rec;
221 new_ctx_rec.EXTENSION_ID := l_new_ext_id;
222 new_ctx_rec.BASE_EXTENSION_ID := udab_new_base_rec.EXTENSION_ID;
223 new_ctx_rec.COMPONENT_SEQUENCE_ID := p_new_comp_seq;
224 new_ctx_rec.OBJECT_VERSION_NUMBER := 1;
225 new_ctx_rec.CONTEXT_ID := l_new_ctx_id;
226 new_ctx_rec.CREATION_DATE := sysdate;
227 new_ctx_rec.CREATED_BY := l_user_id;
228 new_ctx_rec.LAST_UPDATE_DATE := sysdate;
229 new_ctx_rec.LAST_UPDATED_BY := l_user_id;
230 new_ctx_rec.LAST_UPDATE_LOGIN := l_login_id;
231
232 l_new_ctx_tbl(1) := new_ctx_rec;
233
234 FORALL i in l_new_ctx_tbl.FIRST..l_new_ctx_tbl.LAST
235 INSERT
236 INTO BOM_COMP_ATTR_CTX_ASSOCS_B
237 VALUES l_new_ctx_tbl(i);
238
239 --END LOOP; --FOR c_base_row IN C_NEW_BASE_ROW(ctx_rec.BILL_SEQUENCE_ID, p_new_comp_seq, ctx_rec.ATTR_GROUP_ID) LOOP
240
241 END LOOP; -- FOR c_ctx_rec IN C_FROM_CTX_ROWS(p_old_comp_seq) LOOP
242
243 EXCEPTION WHEN OTHERS THEN
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245
246 END Copy_Comp_UDA_Overrides;
247
248
249 PROCEDURE Delete_Comp_UDA_Overrides (p_del_comp_seq NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2) IS
251 l_data_level_id_comp_ovr NUMBER;
252 l_data_level_name_comp_ovr VARCHAR2(30) := 'COMPONENTS_OVR_LEVEL';
253
254 CURSOR C_DATA_LEVEL(p_data_level_name VARCHAR2) IS
255 SELECT DATA_LEVEL_ID
256 FROM EGO_DATA_LEVEL_B
257 WHERE DATA_LEVEL_NAME = p_data_level_name;
258
259 BEGIN
260
261 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
262
263 FOR c_comp_level IN C_DATA_LEVEL(l_data_level_name_comp_ovr) LOOP
267 DELETE
264 l_data_level_id_comp_ovr := c_comp_level.DATA_LEVEL_ID;
265 END LOOP;
266
268 FROM BOM_COMPONENTS_EXT_B
269 WHERE COMPONENT_SEQUENCE_ID = p_del_comp_seq
270 AND DATA_LEVEL_ID = l_data_level_id_comp_ovr;
271
272 DELETE
273 FROM BOM_COMPONENTS_EXT_TL
274 WHERE COMPONENT_SEQUENCE_ID = p_del_comp_seq
275 AND DATA_LEVEL_ID = l_data_level_id_comp_ovr;
276
277 DELETE
278 FROM BOM_COMP_ATTR_CTX_ASSOCS_B
279 WHERE COMPONENT_SEQUENCE_ID = p_del_comp_seq;
280
281 EXCEPTION WHEN OTHERS THEN
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283
284 END Delete_Comp_UDA_Overrides;
285
286
287
288 END BOM_UDA_OVERRIDES_PVT;
289
[Home] [Help]